In this article, we will discuss how to check if a table exists in an SQLite database using the sqlite3 module of Python.
In an SQLite database, the names of all the tables are enlisted in the sqlite_master table. So in order to check if a table exists or not we need to check that if the name of the particular table is in the sqlite_master table or not.
In order to perform this task execute the below query and store it in a variable.
SELECT tableName FROM sqlite_master WHERE type=’table’ AND tableName=’STUDENT’;
Then use the fetchall() method on that variable to generate a list of tables containing the name of the that is found. If the list is empty then the table does not exist in the database.
Example: First, let’s connect to the g4gdata.db SQLite database and then create a cursor object. Now using the cursor object we execute some queries to create multiple tables: EMPLOYEE, STUDENT, STAFF. Then we check if the STUDENT and TEACHER table exists in g4gdata.db database or not.
Python3
# import required moduleimport sqlite3# connect to databasecon = sqlite3.connect('g4gdata.db')# create cursor objectcur = con.cursor()# create tablescur.execute( """CREATE TABLE EMPLOYEE(FIRST_NAME VARCHAR(255), LAST_NAME VARCHAR(255),AGE int, SEX CHAR(1), INCOME int);""")print('EMPLOYEE table created')cur.execute( """CREATE TABLE STUDENT(NAME VARCHAR(255),AGE int, SEX CHAR(1));""")print('STUDENT table created')cur.execute( """CREATE TABLE STAFF(NAME VARCHAR(255), INCOME int);""")print('STAFF table created')print()# check if table existsprint('Check if STUDENT table exists in the database:')listOfTables = cur.execute( """SELECT tableName FROM sqlite_master WHERE type='table' AND tableName='STUDENT'; """).fetchall()if listOfTables == []: print('Table not found!')else: print('Table found!')# check if table existsprint('Check if TEACHER table exists in the database:')listOfTables = cur.execute( """SELECT name FROM sqlite_master WHERE type='table' AND name='TEACHER'; """).fetchall()if listOfTables == []: print('Table not found!')else: print('Table found!')# commit changescon.commit()# terminate the connectioncon.close() |
Output:
