Python sqlite3 – Check if Table exists

Check if Table Exists in sqlite3 Database

Before running a query on a table, you can check if the table is present in the sqlite3 database or not.

To check if a table exists in Python sqlite3 database, you can query sqlite_master table for table names that match your table name.

SQL Query

The sql query to check if the table with a given name is present in the database or not, is given below.

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';

where table_name in the where clause should be replaced with your table name.

From the results of this query, you can check if there are any rows present in the result. If there is one row in the result, then the table exists.

Examples

1. Check if table exists in sqlite3 database

In our previous article, create table in python sqlite3, we have created a table named students. Now, in this example, we will check if the table exists programmatically.

import sqlite3

conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
			
# Get the count of tables with the name
c.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='students' ''')

# If the count is 1, then table exists
if c.fetchone()[0]==1 : {
	print('Table exists.')
}
			
# Commit the changes to db			
conn.commit()

# Close the connection
conn.close()

Output

Table exists.

2. Check if table exists in sqlite3 database (Negative Scenario)

In this example, we are going to check a negative scenario, where the table named dummy is not present in the sqlite3 database and we are going to verify it programmatically.

import sqlite3

conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
			
# Get the count of tables with the name
c.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='students1' ''')

# If the count is 1, then table exists
if c.fetchone()[0]==1 : 
	print('Table exists.')
else :
	print('Table does not exist.')
			
# Commit the changes to db			
conn.commit()

# Close the connection
conn.close()

Output

Table does not exist.

3. Check if table exists in in-memory(RAM)

If you are checking for the existence of an in-memory (RAM) table, then in the query use sqlite_temp_master instead of sqlite_master. A sample query is given beow.

SELECT name FROM sqlite_temp_master WHERE type='table' AND name='table_name';

Summary

In this tutorial of Python Examples, we learned how to check if a given table exists in sqlite3 database or not.