Check if Table exists - sqlite3 - 2 Python Examples
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.
Python Program
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.
Python Program
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.