Check if Table Exists in SQLite using Python

Last Updated : 26 Jul, 2021

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 module import sqlite3 # connect to database con = sqlite3.connect('g4gdata.db') # create cursor object cur = con.cursor() # create tables cur.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 exists print('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 exists print('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 changes con.commit() # terminate the connection con.close()