How to use Python and SQLAlchemy to get a list of all table names in a SQLite database ?

Using SQLAlchemy and Python, you can get a list of all the tables in a SQLite database.

Import SQLAlchemy

First, import the necessary modules: ‘SQLAlchemy’. This will allow you to use the SQLAlchemy library and connect to a Sqlite database.

import sqlalchemy
import pkg_resources

pkg_resources.get_distribution("sqlalchemy").version

Create an instance of the SQLAlchemy Engine class

The first step is to create an engine object, which will connect your program with the database.

engine = sqlalchemy.create_engine('sqlite:///database_name.db')

For example using the chinook.db databse:

engine = sqlalchemy.create_engine('sqlite:///chinook.db')

Get a list of all table names using get_table_names

After that, use the Inspector class provided by the SQLAlchemy library to inspect the database. You will be able to access information about the tables, columns and constraints from this inspector. Finally, use the inspector's get_table_names method to get a list of all the table names in the database. You can then loop through this list and access or manipulate each table as needed.

 engine.table_names()

returns here

['CUSTOMER', 'PRODUCT', 'albums', 'artists', 'customers', 'employees', 'genres', 'invoice_items', 'invoices', 'media_types', 'playlist_track', 'playlists', 'sqlite_sequence', 'sqlite_stat1', 'tracks']

Extract table names from MetaData

Another solution

META_DATA = sqlalchemy.MetaData(bind=engine)
META_DATA.reflect()

META_DATA.tables.keys()

returns

dict_keys(['CUSTOMER', 'PRODUCT', 'albums', 'artists', 'customers', 'employees', 'genres', 'invoice_items', 'invoices', 'tracks', 'media_types', 'playlist_track', 'playlists', 'sqlite_sequence', 'sqlite_stat1'])

Check if table exists

The most robust way of doing this is by using the 'has_table()', which returns a Boolean value indicating whether or not that table has been created already in your database. An example of this is shown below:

sqlalchemy.inspect(engine).has_table("customers")

If the value returned by 'exists' is False, then the table does not exist in your database:

True

While

sqlalchemy.inspect(engine).has_table("shope")

returns

False

References