Skip to content
Related Articles

Related Articles

Improve Article

Check if Table Exists in SQLite using Python

  • Last Updated : 08 May, 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 cusor 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()

Output:

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course




My Personal Notes arrow_drop_up
Recommended Articles
Page :