Check If a Table Exists – Python SQLite3

Hey readers! In this article we will be studying how we can check if a table exists using SQLite3. This is all gonna easy for us because we will be using Python and its built-in module SQLite3. So, let us go for it.

Please note: You should be familiar with SQLite3 and SQL commands.

Also read: How to insert multiple records in Sqlite3 database

What’s covered in this article?

1. Creating a database.
2. Adding some data to it.
3. Purposely deleting the table.
4. Creating a program to check if a table exists in Python

Creating a database using Python SQLite3

In this section we shall create a sample database namely company and add an employee table to it. This table contains the basic info about employees in that company. Make sure you create a new Working Directory that holds all the stuff.

Code:

import sqlite3

connection = sqlite3.connect('databases/company.db') # file path

# create a cursor object from the cursor class
cur = connection.cursor()

cur.execute('''
   CREATE TABLE employee(
       emp_id integer,
       name text,
       designation text,
       email text
       )''')

print("Database created successfully!!!")
# committing our connection
connection.commit()

# close our connection
connection.close()

Output:

Database created successfully!!!

This adds a “company.db” file in the databases folder. This file contains our employee table. It’s an empty table, so let us add some data to it.

Adding Data to a Table using Python SQLite3

Using the “executemany()” function we can insert multiple records at once in our table. So, we will be using the same here:

Code:

import sqlite3

connection = sqlite3.connect('databases/company.db') # file path

cur = connection.cursor()

# creating a list of items

records = [(100, 'Arvind Sharma', 'Software Engineer', '[email protected]'),
           (102, 'Neha Thakur', 'Project Manager', '[email protected]'),
           (103, 'Pavitra Patil', 'Database Engineer', '[email protected]')]

cur.executemany("INSERT INTO employee VALUES (?,?,?,?)", records)


print('Data added successfully!!!')
connection.commit()

# close our connection
connection.close()

Output:

Data added successfully!!!

So, these are the records we just added through a Python script.

Purposely deleting the table

Now we will purposely delete the table. We use the default SQL’s DROP TABLE command.

Code:

import sqlite3
connection = sqlite3.connect('databases/company.db')
connection.execute("DROP TABLE employee")
print("Your table has been deleted!!!")
connection.close()

Output:

Your table has been deleted!!!

Check if a table exists using Python SQLite3

Now, to check whether the table exists or not. We need to write a code that will try to locate that table and if not found it should return a message like: “Table not found!!”. For this purpose the fetchall() function is useful. This enables us to retrieve/access all the information a table contains in SQL. This returns a list of all the info it gets.

Logic:

1. The SELECT * FROM table name command tries to retrieve the whole table from the database.
2. If the table exists it will store it in a list called as data_list using the fetchall() function.
3. If the data exists it will store it in a list.
4. If no table exists then it will throw OperationalError from the sqlite3 module.
5. Handle it through except block and then print a message “no such table: table_name”.

Code:

import sqlite3

connection = sqlite3.connect('databases/company.db')

cur = connection.cursor() 

try:
    cur.execute("SELECT * FROM employee")
    
    # storing the data in a list
    data_list = cur.fetchall() 
    print('NAME' + '\t\tEMAIL')
    print('--------' + '\t\t-------------')
    for item in items:
        print(item[0] + ' | ' + item[1] + '\t' + item[2])   
        
except sqlite3.OperationalError:
    print("No such table: employee")
    
connection.commit()
connection.close()

Output:

No such table: employee

So, in this way we can detect whether a table in a particular table exists in a database or not.

Conclusion

In this way, we conclude this article here. I hope the reader has got some knowledge about how to use databases using SQLite3. This may prove a great helping hand to someone who is new to DBs.