Charles Lavery

SQLAlchemy Reflecting Tables to Declarative

SQLAlchemy has reflection support. A bound MetaData object can reflect all tables in a database to Table objects. These can be attached to declarative ORM objects. This example shows a small function for doing this automatically and importing the reflected classes into the global namespace. Useful for quickly reflecting a database and performing maintenance in the declarative style.

"""Example for reflecting database tables to ORM objects

This script creates classes for each table reflected
from the database.

Note: The class names are imported to the global namespace using
the same name as the tables. This is useful for quick utility scripts.
A better solution for production code would be to return a dict
of reflected ORM objects.
"""

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base


def reflect_all_tables_to_declarative(uri):
    """Reflects all tables to declaratives

    Given a valid engine URI and declarative_base base class
    reflects all tables and imports them to the global namespace.

    Returns a session object bound to the engine created.
    """

    # create an unbound base our objects will inherit from
    Base = declarative_base()

    engine = create_engine(uri)
    metadata = MetaData(bind=engine)
    Base.metadata = metadata

    g = globals()

    metadata.reflect()

    for tablename, tableobj in metadata.tables.items():
        g[tablename] = type(str(tablename), (Base,), {'__table__' : tableobj })
        print("Reflecting {0}".format(tablename))

    Session = sessionmaker(bind=engine)
    return Session()


# set to database credentials/host
CONNECTION_URI = "postgres://..."

session = reflect_all_tables_to_declarative(CONNECTION_URI)

# do something with the session and the orm objects
results = session.query(some_table_name).all()