How to list tables using SQLite3 in Python ?

Last Updated : 08 Sep, 2022

In this article, we will discuss how to list all the tables in the SQLite database using Python. Here, we will use the already created database table from SQLite. We will also learn exception handling during connecting to our database.

Database Used:

Steps to Fetch all tables using SQLite3 in Python

1. Creating a connection object using connect() method,

sqliteConnection = sqlite3.connect('SQLite_Retrieving_data.db')

2. Created one SQLite query with which we will search a list of all tables which are present inside the sqlite3 database.

sql_query = """SELECT name FROM sqlite_master  
  WHERE type='table';"""

3. Using Connection Object, we are creating a cursor object.

cursor = sqliteConnection.cursor()

4. Using execute() methods, we will execute the above SQL query.

cursor.execute(sql_query)

5. Finally, We will print a list of all tables which are present inside the sqlite3 database.

print(cursor.fetchall())

Below is the implementation

Python3

# Importing Sqlite3 Module
import sqlite3

try:
    
    # Making a connection between sqlite3 
    # database and Python Program
    sqliteConnection = sqlite3.connect('SQLite_Retrieving_data.db')
    
    # If sqlite3 makes a connection with python
    # program then it will print "Connected to SQLite"
    # Otherwise it will show errors
    print("Connected to SQLite")

    # Getting all tables from sqlite_master
    sql_query = """SELECT name FROM sqlite_master 
    WHERE type='table';"""

    # Creating cursor object using connection object
    cursor = sqliteConnection.cursor()
    
    # executing our sql query
    cursor.execute(sql_query)
    print("List of tables\n")
    
    # printing all tables list
    print(cursor.fetchall())

except sqlite3.Error as error:
    print("Failed to execute the above query", error)
    
finally:
  
    # Inside Finally Block, If connection is
    # open, we need to close it
    if sqliteConnection:
        
        # using close() method, we will close 
        # the connection
        sqliteConnection.close()
        
        # After closing connection object, we 
        # will print "the sqlite connection is 
        # closed"
        print("the sqlite connection is closed")