Check if Table exists - sqlite3 - 2 Python Examples

Check if Table Exists in sqlite3 Database

Before running a query on a table, it's useful to check whether the table exists in the sqlite3 database. This can prevent errors during execution, especially when working with dynamic databases.

To check if a table exists in Python sqlite3, query the sqlite_master table for table names matching your table name.

SQL Query

The SQL query to check if a table with a given name exists in the database is as follows:

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

Here, replace table_name with the name of your table. If the query returns any rows, the table exists; if it returns no rows, the table doesn't exist.

Examples

1. Check if Table Exists in sqlite3 Database

In this example, we will check if a table named students exists in the mysqlite.db database. This table was created in a previous tutorial on creating tables in sqlite3.

Python Program

import sqlite3

# Establish a connection to the database
conn = sqlite3.connect('mysqlite.db')

# Create a cursor object
c = conn.cursor()

# Query to check if the table exists
c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' AND name='students' ''')

# If the count is 1, the table exists
if c.fetchone()[0] == 1:
    print('Table exists.')
else:
    print('Table does not exist.')

# Commit changes and close the connection
conn.commit()
conn.close()

Explanation:

  1. sqlite3.connect('mysqlite.db'): Connects to the mysqlite.db database.
  2. cursor(): Creates a cursor object to execute SQL queries.
  3. SELECT count(name): Checks the sqlite_master table to count how many tables with the name 'students' exist.
  4. fetchone()[0]: Retrieves the count (1 if the table exists, 0 otherwise).
  5. conn.commit() and conn.close(): Saves any changes and closes the connection.

Output

Table exists.

2. Check if Table Exists in sqlite3 Database (Negative Scenario)

In this scenario, we will check if a table named dummy exists. Since the table does not exist, the program will print that the table does not exist.

Python Program

import sqlite3

# Establish a connection to the database
conn = sqlite3.connect('mysqlite.db')

# Create a cursor object
c = conn.cursor()

# Query to check if the table exists
c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' AND name='dummy' ''')

# If the count is 1, the table exists
if c.fetchone()[0] == 1:
    print('Table exists.')
else:
    print('Table does not exist.')

# Commit changes and close the connection
conn.commit()
conn.close()

Explanation:

  1. This program works similarly to the previous one but checks for a non-existing table (dummy).
  2. The output will indicate that the table does not exist, as the count will be 0.

Output

Table does not exist.

3. Check if Table Exists in In-Memory (RAM) Database

In-memory databases, which reside only in RAM, have a slightly different behavior. To check if a table exists in an in-memory database, use the sqlite_temp_master table instead of sqlite_master.

SQL Query

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

In the query, replace table_name with the name of your table. This will check if the table exists in the temporary in-memory database.

Summary

In this tutorial, we learned how to check if a given table exists in an sqlite3 database. We demonstrated how to handle both positive and negative scenarios, as well as how to work with in-memory databases. This method is useful for validating the existence of a table before executing further operations on it, thus ensuring the robustness of your database operations in Python.

Python Libraries