Check if Table Exists in SQLite using Python

0 0 1 minute read

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()