sqlalchemy.ext.automap

Basic Use

The simplest usage is to reflect an existing database into a new model. We create a new .AutomapBase class in a similar manner as to how we create a declarative base class, using .automap_base. We then call .AutomapBase.prepare on the resulting base class, asking it to reflect the schema and produce mappings:

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

Base = automap_base()

# engine, suppose it has two tables 'user' and 'address' set up
engine = create_engine("sqlite:///mydatabase.db")

# reflect the tables
Base.prepare(engine, reflect=True)

# mapped classes are now created with names by default
# matching that of the table name.
User = Base.classes.user
Address = Base.classes.address

session = Session(engine)

# rudimentary relationships are produced
session.add(Address(email_address="foo@bar.com", user=User(name="foo")))
session.commit()

# collection-based relationships are by default named
# "<classname>_collection"
print (u1.address_collection)

Above, calling .AutomapBase.prepare while passing along the :paramref:`.AutomapBase.prepare.reflect` parameter indicates that the _schema.MetaData.reflect method will be called on this declarative base classes' _schema.MetaData collection; then, each viable _schema.Table within the _schema.MetaData will get a new mapped class generated automatically. The _schema.ForeignKeyConstraint objects which link the various tables together will be used to produce new, bidirectional _orm.relationship objects between classes. The classes and relationships follow along a default naming scheme that we can customize. At this point, our basic mapping consisting of related User and Address classes is ready to use in the traditional way.

Generating Mappings from an Existing MetaData

We can pass a pre-declared _schema.MetaData object to .automap_base. This object can be constructed in any way, including programmatically, from a serialized file, or from itself being reflected using _schema.MetaData.reflect. Below we illustrate a combination of reflection and explicit table declaration:

from sqlalchemy import create_engine, MetaData, Table, Column, ForeignKey
from sqlalchemy.ext.automap import automap_base
engine = create_engine("sqlite:///mydatabase.db")

# produce our own MetaData object
metadata = MetaData()

# we can reflect it ourselves from a database, using options
# such as 'only' to limit what tables we look at...
metadata.reflect(engine, only=['user', 'address'])

# ... or just define our own Table objects with it (or combine both)
Table('user_order', metadata,
                Column('id', Integer, primary_key=True),
                Column('user_id', ForeignKey('user.id'))
            )

# we can then produce a set of mappings from this MetaData.
Base = automap_base(metadata=metadata)

# calling prepare() just sets up mapped classes and relationships.
Base.prepare()

# mapped classes are ready
User, Address, Order = Base.classes.user, Base.classes.address,\
    Base.classes.user_order

Specifying Classes Explicitly

The .sqlalchemy.ext.automap extension allows classes to be defined explicitly, in a way similar to that of the .DeferredReflection class. Classes that extend from .AutomapBase act like regular declarative classes, but are not immediately mapped after their construction, and are instead mapped when we call .AutomapBase.prepare. The .AutomapBase.prepare method will make use of the classes we've established based on the table name we use. If our schema contains tables user and address, we can define one or both of the classes to be used:

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

# automap base
Base = automap_base()

# pre-declare User for the 'user' table
class User(Base):
    __tablename__ = 'user'

    # override schema elements like Columns
    user_name = Column('name', String)

    # override relationships too, if desired.
    # we must use the same name that automap would use for the
    # relationship, and also must refer to the class name that automap will
    # generate for "address"
    address_collection = relationship("address", collection_class=set)

# reflect
engine = create_engine("sqlite:///mydatabase.db")
Base.prepare(engine, reflect=True)

# we still have Address generated from the tablename "address",
# but User is the same as Base.classes.User now

Address = Base.classes.address

u1 = session.query(User).first()
print (u1.address_collection)

# the backref is still there:
a1 = session.query(Address).first()
print (a1.user)

Above, one of the more intricate details is that we illustrated overriding one of the _orm.relationship objects that automap would have created. To do this, we needed to make sure the names match up with what automap would normally generate, in that the relationship name would be User.address_collection and the name of the class referred to, from automap's perspective, is called address, even though we are referring to it as Address within our usage of this class.

Overriding Naming Schemes

.sqlalchemy.ext.automap is tasked with producing mapped classes and relationship names based on a schema, which means it has decision points in how these names are determined. These three decision points are provided using functions which can be passed to the .AutomapBase.prepare method, and are known as .classname_for_table, .name_for_scalar_relationship, and .name_for_collection_relationship. Any or all of these functions are provided as in the example below, where we use a "camel case" scheme for class names and a "pluralizer" for collection names using the Inflect package:

import re
import inflect

def camelize_classname(base, tablename, table):
    "Produce a 'camelized' class name, e.g. "
    "'words_and_underscores' -> 'WordsAndUnderscores'"

    return str(tablename[0].upper() + \
            re.sub(r'_([a-z])', lambda m: m.group(1).upper(), tablename[1:]))

_pluralizer = inflect.engine()
def pluralize_collection(base, local_cls, referred_cls, constraint):
    "Produce an 'uncamelized', 'pluralized' class name, e.g. "
    "'SomeTerm' -> 'some_terms'"

    referred_name = referred_cls.__name__
    uncamelized = re.sub(r'[A-Z]',
                         lambda m: "_%s" % m.group(0).lower(),
                         referred_name)[1:]
    pluralized = _pluralizer.plural(uncamelized)
    return pluralized

from sqlalchemy.ext.automap import automap_base

Base = automap_base()

engine = create_engine("sqlite:///mydatabase.db")

Base.prepare(engine, reflect=True,
            classname_for_table=camelize_classname,
            name_for_collection_relationship=pluralize_collection
    )

From the above mapping, we would now have classes User and Address, where the collection from User to Address is called User.addresses:

User, Address = Base.classes.User, Base.classes.Address

u1 = User(addresses=[Address(email="foo@bar.com")])

Relationship Detection

The vast majority of what automap accomplishes is the generation of _orm.relationship structures based on foreign keys. The mechanism by which this works for many-to-one and one-to-many relationships is as follows:

1. A given _schema.Table, known to be mapped to a particular class, is examined for _schema.ForeignKeyConstraint objects.

2. From each _schema.ForeignKeyConstraint, the remote _schema.Table object present is matched up to the class to which it is to be mapped, if any, else it is skipped.

3. As the _schema.ForeignKeyConstraint we are examining corresponds to a reference from the immediate mapped class, the relationship will be set up as a many-to-one referring to the referred class; a corresponding one-to-many backref will be created on the referred class referring to this class.

4. If any of the columns that are part of the _schema.ForeignKeyConstraint are not nullable (e.g. nullable=False), a :paramref:`_orm.relationship.cascade` keyword argument of all, delete-orphan will be added to the keyword arguments to be passed to the relationship or backref. If the _schema.ForeignKeyConstraint reports that :paramref:`_schema.ForeignKeyConstraint.ondelete` is set to CASCADE for a not null or SET NULL for a nullable set of columns, the option :paramref:`_orm.relationship.passive_deletes` flag is set to True in the set of relationship keyword arguments. Note that not all backends support reflection of ON DELETE.

   New in version 1.0.0: - automap will detect non-nullable foreign key constraints when producing a one-to-many relationship and establish a default cascade of all, delete-orphan if so; additionally, if the constraint specifies :paramref:`_schema.ForeignKeyConstraint.ondelete` of CASCADE for non-nullable or SET NULL for nullable columns, the passive_deletes=True option is also added.

5. The names of the relationships are determined using the :paramref:`.AutomapBase.prepare.name_for_scalar_relationship` and :paramref:`.AutomapBase.prepare.name_for_collection_relationship` callable functions. It is important to note that the default relationship naming derives the name from the the actual class name. If you've given a particular class an explicit name by declaring it, or specified an alternate class naming scheme, that's the name from which the relationship name will be derived.

6. The classes are inspected for an existing mapped property matching these names. If one is detected on one side, but none on the other side, .AutomapBase attempts to create a relationship on the missing side, then uses the :paramref:`_orm.relationship.back_populates` parameter in order to point the new relationship to the other side.

7. In the usual case where no relationship is on either side, .AutomapBase.prepare produces a _orm.relationship on the "many-to-one" side and matches it to the other using the :paramref:`_orm.relationship.backref` parameter.

8. Production of the _orm.relationship and optionally the .backref is handed off to the :paramref:`.AutomapBase.prepare.generate_relationship` function, which can be supplied by the end-user in order to augment the arguments passed to _orm.relationship or .backref or to make use of custom implementations of these functions.

from sqlalchemy.ext.automap import generate_relationship

def _gen_relationship(base, direction, return_fn,
                                attrname, local_cls, referred_cls, **kw):
    if direction is interfaces.ONETOMANY:
        kw['cascade'] = 'all, delete-orphan'
        kw['passive_deletes'] = True
    # make use of the built-in function to actually return
    # the result.
    return generate_relationship(base, direction, return_fn,
                                 attrname, local_cls, referred_cls, **kw)

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

# automap base
Base = automap_base()

engine = create_engine("sqlite:///mydatabase.db")
Base.prepare(engine, reflect=True,
            generate_relationship=_gen_relationship)

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String(50))
    __mapper_args__ = {
         'polymorphic_identity':'employee', 'polymorphic_on': type
    }

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee', 'polymorphic_on':type
    }

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    favorite_employee_id = Column(Integer, ForeignKey('employee.id'))

    favorite_employee = relationship(Employee,
                                     foreign_keys=favorite_employee_id)

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
        'inherit_condition': id == Employee.id
    }

CREATE TABLE table_a (
    id INTEGER PRIMARY KEY
);

CREATE TABLE table_b (
    id INTEGER PRIMARY KEY,
    table_a INTEGER,
    FOREIGN KEY(table_a) REFERENCES table_a(id)
);

def name_for_scalar_relationship(base, local_cls, referred_cls, constraint):
    name = referred_cls.__name__.lower()
    local_table = local_cls.__table__
    if name in local_table.columns:
        newname = name + "_"
        warnings.warn(
            "Already detected name %s present.  using %s" %
            (name, newname))
        return newname
    return name


Base.prepare(engine, reflect=True,
    name_for_scalar_relationship=name_for_scalar_relationship)

Base = automap_base()

class TableB(Base):
    __tablename__ = 'table_b'
    _table_a = Column('table_a', ForeignKey('table_a.id'))

Base.prepare(engine, reflect=True)

Using Automap with Explicit Declarations

As noted previously, automap has no dependency on reflection, and can make use of any collection of _schema.Table objects within a _schema.MetaData collection. From this, it follows that automap can also be used generate missing relationships given an otherwise complete model that fully defines table metadata:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import Column, Integer, String, ForeignKey

Base = automap_base()

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)

class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(ForeignKey('user.id'))

# produce relationships
Base.prepare()

# mapping is complete, with "address_collection" and
# "user" relationships
a1 = Address(email='u1')
a2 = Address(email='u2')
u1 = User(address_collection=[a1, a2])
assert a1.user is u1

Above, given mostly complete User and Address mappings, the _schema.ForeignKey which we defined on Address.user_id allowed a bidirectional relationship pair Address.user and User.address_collection to be generated on the mapped classes.

Note that when subclassing .AutomapBase, the .AutomapBase.prepare method is required; if not called, the classes we've declared are in an un-mapped state.

Intercepting Column Definitions

The _schema.MetaData and _schema.Table objects support an event hook _events.DDLEvents.column_reflect that may be used to intercept the information reflected about a database column before the _schema.Column object is constructed. For example if we wanted to map columns using a naming convention such as "attr_<columnname>", the event could be applied as:

@event.listens_for(Base.metadata, "column_reflect")
def column_reflect(inspector, table, column_info):
    # set column.key = "attr_<lower_case_name>"
    column_info['key'] = "attr_%s" % column_info['name'].lower()

# run reflection
Base.prepare(engine, reflect=True)