SQLAlchemy: Get a list of all tables

Introduction

SQLAlchemy is a widely-used Object-Relational Mapping (ORM) library for Python, allowing developers to interact with databases in a more intuitive way. This tutorial provides a comprehensive guide on how to retrieve a list of all tables in a database using SQLAlchemy, catering to different use cases with progressive complexity.

Before diving into the code examples, ensure you have the following prerequisites met:

• Python installed on your system.
• SQLAlchemy library installed (You can install it using pip install SQLAlchemy).
• A database with some tables created that SQLAlchemy can connect to.

Basic Example: Using engine.table_names()

To start with the basics, you can use the table_names() function of the SQLAlchemy engine to get a list of table names without reflecting any metadata:

from sqlalchemy import create_engine
engine = create_engine('sqlite:///yourdatabase.db')
tables = engine.table_names()
print(tables)

This will print out a list of all the table names in the specified database.

Using MetaData and Inspector

For a slightly more advanced scenario, where you want more control and to work with metadata:

from sqlalchemy import create_engine
from sqlalchemy.engine.reflection import Inspector

engine = create_engine('sqlite:///yourdatabase.db')
inspector = Inspector.from_engine(engine)
table_names = inspector.get_table_names()
print(table_names)

This prints a list of all table names using the Inspector which provides more introspection capabilities than the basic table_names() function.

Reflecting MetaData

When you need information about the schema as well as the table names, reflecting the MetaData is the way to go:

from sqlalchemy import create_engine, MetaData

engine = create_engine('sqlite:///yourdatabase.db')
metadata = MetaData()
metadata.reflect(bind=engine)
for table in metadata.sorted_tables:
    print(table.name)

This code snippet uses the MetaData object to reflect all the tables and then iterate over sorted_tables to get each table’s name.

Querying with Session and Automap

If you are using declarative base and sessions to interact with your database, you can still get all table names:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

Base = automap_base()
engine = create_engine('sqlite:///yourdatabase.db')
Base.prepare(engine, reflect=True)
session = Session(engine)

for cls in Base.classes:
    print(cls.__table__.name)

This example assumes you are working with a declaratively mapped class. By reflecting the database schema into the Base, SQLAlchemy provides access to all table names associated with each class.

Using SQLAlchemy Core

For users who prefer using the SQL Expression Language (part of SQLAlchemy Core), you can extract table names like this:

from sqlalchemy import create_engine, MetaData

engine = create_engine('sqlite:///yourdatabase.db')
metadata = MetaData(bind=engine)
metadata.reflect()
for table in metadata.tables.keys():
    print(table)

This is similar to the reflection method but using Core’s way of managing table information.

Handling Schemas in Multi-Tenant Systems

In a multi-tenant system where you might have multiple schemas, fetching table names requires specifying the schema:

inspector = Inspector.from_engine(engine)
for schema in inspector.get_schema_names():
    print(f'Tables in schema {schema}:')
    for table_name in inspector.get_table_names(schema=schema):
        print(table_name)

The above code will list tables in each schema found within your database.

Conclusion

Throughout this tutorial, you have seen how to list all tables in a database with SQLAlchemy from basic to advanced methods. Whether for debugging, migrations, or schema validation, being able to list tables programmatically is a helpful skill for any Python developer working with databases. Remember to choose the method that best fits your current setup and requirements.