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:
… [Trackback]
[…] Find More on to that Topic: geeksforgeeks.org/check-if-table-exists-in-sqlite-using-python/ […]
… [Trackback]
[…] Find More on to that Topic: geeksforgeeks.org/check-if-table-exists-in-sqlite-using-python/ […]