Check if a Table Exists in SQLite using Python

One of the strengths of Python is its ability to work seamlessly with a variety of databases, including SQLite. SQLite is a lightweight relational database management system that is often used for embedded systems and small-scale applications. Unlike larger databases like MySQL or PostgreSQL, SQLite doesn't require a separate server process, and data is stored in a single file on disk.

To use SQLite with Python, you'll need to install the sqlite3 module, which comes bundled with most Python installations. Once you've done that, you can create a connection to your SQLite database and start querying it using SQL commands.

In this tutorial, we will look at how to check if a table exists in SQLite using Python. We will explore two different methods: one using raw SQL queries, and another using the built-in SQLite module in Python.

Creating an SQLite Database Using Python

Before going deep dive, we should create a new SQLite database. Here's an example of how to create a new SQLite database using Python −

Example

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Create table
c.execute('''CREATE TABLE stocks
   (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# Close the connection
conn.close()

In this example, we create a new SQLite database called example.db, create a new table called stocks, insert a row of data into the table, commit the changes, and then close the connection.

There are many other operations you can perform with SQLite and Python, such as selecting data from a table, updating records, and deleting rows.

Let’s see various methods to check if a table exists in SQLite using Python.

Method 1: Using raw SQL queries

The first method involves executing a raw SQL query to check if the table exists. Here is the code to do that −

Example

import sqlite3

# create a connection to the database
conn = sqlite3.connect('example.db')

# create a cursor object to execute queries
cursor = conn.cursor()

# execute the query to check if the table exists
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='stocks'")

# fetch the result
result = cursor.fetchone()

# check if the result is not None, meaning the table exists
if result is not None:
   print("Table exists")
else:
   print("Table does not exist")

Output

After implementing the above lines of code, you will get the following output −

Table exists

Explanation

Let's break down the code above. First, we create a connection to the SQLite database using the connect() method from the sqlite3 module. In this example, we assume that the database file is named example.db.

Next, we create a cursor object using the cursor() method on the connection object. The cursor is used to execute SQL queries on the database.

We then execute the SQL query to check if the table exists. This query selects the name of all tables in the database where the type is 'table' and the name is equal to 'stocks'. If the table exists, this query will return a row with the name of the table. If the table does not exist, the query will return an empty result set.

We fetch the result of the query using the fetchone() method on the cursor object. This method returns the next row of the result set as a tuple, or None if there are no more rows.

Finally, we check if the result is not None, meaning the table exists. If the result is None, we print a message indicating that the table does not exist.

Method 2: Using the built-in SQLite module in Python

The second method involves using the built-in SQLite module in Python to check if the table exists. Here is the code to do that −

Example

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# get the table information using PRAGMA
cursor.execute("PRAGMA table_info(stocks)")

# fetch the result
result = cursor.fetchone()

# check if the result is not None, meaning the table exists
if result is not None:
   print("Table exists")
else:
   print("Table does not exist")

Output

After implementing the above lines of code, you will get the following output −

Table exists

Explanation

In this method, we first create a connection to the SQLite database using the connect() method from the sqlite3 module.

We then get the cursor object using the cursor() method on the connection object, just like in the previous method.

Next, we execute the SQL command PRAGMA table_info(stocks). This command returns information about the columns in the specified table. If the table does exist, this command will return a result set with information about the columns in the table. If the table does not exist, the command will return an empty result set.

We fetch the result of the command using the fetchone() method on the cursor object. This method returns the next row of the result set as a tuple, or None if there are no more rows.

Finally, we check if the result is not None, meaning the table exists. If the result is None, we print a message indicating that the table does not exist.

Comparing the two methods, we can see that the first method uses a more generic SQL query to check if the table exists, while the second method uses a SQLite-specific command (PRAGMA table_info()) to get information about the table. Both methods are equally effective, so the choice between them depends on personal preference and the specific requirements of your application.

Handling Exceptions

It's important to note that both methods above assume that the table name and database file exist and will raise an exception if they don't. To handle this, we can wrap the code in a try-except block and catch the sqlite3.OperationalError exception that is raised if the table or database file does not exist.

Example

Here's an example of how to handle exceptions in the first method −

import sqlite3

try:
   conn = sqlite3.connect('example.db')
   cursor = conn.cursor()
   cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='stocks'")
   result = cursor.fetchone()
   if result is not None:
      print("Table exists")
   else:
      print("Table does not exist")
except sqlite3.OperationalError:
   print("Error: Table or database file does not exist")

Output

After implementing the above lines of code, you will get the following output −

Table exists

Example

And here's an example of how to handle exceptions in the second method −

import sqlite3

try:
   conn = sqlite3.connect('example.db')
   cursor = conn.cursor()
   cursor.execute("PRAGMA table_info(stocks)")
   result = cursor.fetchone()
   if result is not None:
      print("Table exists")
   else:
      print("Table does not exist")
except sqlite3.OperationalError:
   print("Error: Table or database file does not exist")

Output

After implementing the above lines of code, you will get the following output −

Table exists

In both the cases, if the table or database file does not exist, the sqlite3.OperationalError exception will be caught, and the code will print an error message.

Conclusion

In this tutorial, we've looked at two methods for checking if a table exists in SQLite using Python. The first method uses a raw SQL query to select the name of the table from the sqlite_master table, while the second method uses the SQLite-specific PRAGMA table_info() command to get information about the columns in the table.

Both methods are effective, and the choice between them depends on personal preference and the specific requirements of your application.