Automap

Define an extension to the sqlalchemy.ext.declarative systemwhich automatically generates mapped classes and relationships from a databaseschema, typically though not necessarily one which is reflected.

New in version 0.9.1: Added sqlalchemy.ext.automap.

It is hoped that the AutomapBase system provides a quickand modernized solution to the problem that the very famousSQLSoupalso tries to solve, that of generating a quick and rudimentary objectmodel from an existing database on the fly. By addressing the issue strictlyat the mapper configuration level, and integrating fully with existingDeclarative class techniques, AutomapBase seeks to providea well-integrated approach to the issue of expediently auto-generating ad-hocmappings.

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 howwe 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 theAutomapBase.prepare.reflect parameter indicates that theMetaData.reflect() method will be called on this declarative baseclasses’ MetaData collection; then, each viableTable within the MetaData will get a new mapped classgenerated automatically. The ForeignKeyConstraint objects whichlink the various tables together will be used to produce new, bidirectionalrelationship() objects between classes. The classes and relationshipsfollow along a default naming scheme that we can customize. At this point,our basic mapping consisting of related User and Address classes isready to use in the traditional way.

Note

By viable, we mean that for a table to be mapped, it mustspecify a primary key. Additionally, if the table is detected as beinga pure association table between two other tables, it will not be directlymapped and will instead be configured as a many-to-many table betweenthe mappings for the two referring tables.

Generating Mappings from an Existing MetaData

We can pass a pre-declared MetaData object to automap_base().This object can be constructed in any way, including programmatically, froma serialized file, or from itself being reflected usingMetaData.reflect(). Below we illustrate a combination of reflection andexplicit 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 definedexplicitly, in a way similar to that of the DeferredReflection class.Classes that extend from AutomapBase act like regular declarativeclasses, but are not immediately mapped after their construction, and areinstead mapped when we call AutomapBase.prepare(). TheAutomapBase.prepare() method will make use of the classes we’veestablished based on the table name we use. If our schema contains tablesuser 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 overridingone of the relationship() objects that automap would have created.To do this, we needed to make sure the names match up with what automapwould normally generate, in that the relationship name would beUser.address_collection and the name of the class referred to, fromautomap’s perspective, is called address, even though we are referring toit as Address within our usage of this class.

Overriding Naming Schemes

sqlalchemy.ext.automap is tasked with producing mapped classes andrelationship names based on a schema, which means it has decision points in howthese names are determined. These three decision points are provided usingfunctions which can be passed to the AutomapBase.prepare() method, andare known as classname_for_table(),name_for_scalar_relationship(),and name_for_collection_relationship(). Any or all of thesefunctions are provided as in the example below, where we use a “camel case”scheme for class names and a “pluralizer” for collection names using theInflect 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 calledUser.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 ofrelationship() structures based on foreign keys. The mechanismby which this works for many-to-one and one-to-many relationships is asfollows:

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

• From each ForeignKeyConstraint, the remote Tableobject present is matched up to the class to which it is to be mapped,if any, else it is skipped.

• As the ForeignKeyConstraint we are examining corresponds to areference from the immediate mapped class, the relationship will be set upas a many-to-one referring to the referred class; a correspondingone-to-many backref will be created on the referred class referringto this class.

• If any of the columns that are part of the ForeignKeyConstraintare not nullable (e.g. nullable=False), acascade keyword argumentof all, delete-orphan will be added to the keyword arguments tobe passed to the relationship or backref. If theForeignKeyConstraint reports thatForeignKeyConstraint.ondeleteis set to CASCADE for a not null or SET NULL for a nullableset of columns, the option passive_deletesflag 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 keyconstraints when producing a one-to-many relationship and establisha default cascade of all, delete-orphan if so; additionally,if the constraint specifies ForeignKeyConstraint.ondeleteof CASCADE for non-nullable or SET NULL for nullable columns,the passive_deletes=True option is also added.

• The names of the relationships are determined using theAutomapBase.prepare.name_for_scalar_relationship andAutomapBase.prepare.name_for_collection_relationshipcallable functions. It is important to note that the default relationshipnaming derives the name from the the actual class name. If you’vegiven a particular class an explicit name by declaring it, or specified analternate class naming scheme, that’s the name from which the relationshipname will be derived.

• The classes are inspected for an existing mapped property matching thesenames. 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 relationship.back_populates parameter in order topoint the new relationship to the other side.

• In the usual case where no relationship is on either side,AutomapBase.prepare() produces a relationship() on the“many-to-one” side and matches it to the other using therelationship.backref parameter.

• Production of the relationship() and optionally the backref()is handed off to the AutomapBase.prepare.generate_relationshipfunction, which can be supplied by the end-user in order to augmentthe arguments passed to relationship() or backref() or tomake use of custom implementations of these functions.

Custom Relationship Arguments

The AutomapBase.prepare.generate_relationship hook can be usedto add parameters to relationships. For most cases, we can make use of theexisting automap.generate_relationship() function to returnthe object, after augmenting the given keyword dictionary with our ownarguments.

Below is an illustration of how to sendrelationship.cascade andrelationship.passive_deletesoptions along to all one-to-many relationships:

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)

Many-to-Many relationships

sqlalchemy.ext.automap will generate many-to-many relationships, e.g.those which contain a secondary argument. The process for producing theseis as follows:

• A given Table is examined for ForeignKeyConstraintobjects, before any mapped class has been assigned to it.

• If the table contains two and exactly two ForeignKeyConstraintobjects, and all columns within this table are members of these twoForeignKeyConstraint objects, the table is assumed to be a“secondary” table, and will not be mapped directly.

• The two (or one, for self-referential) external tables to which theTable refers to are matched to the classes to which they will bemapped, if any.

• If mapped classes for both sides are located, a many-to-many bi-directionalrelationship() / backref() pair is created between the twoclasses.

• The override logic for many-to-many works the same as that of one-to-many/many-to-one; the generate_relationship() function is called uponto generate the structures and existing attributes will be maintained.

Relationships with Inheritance

sqlalchemy.ext.automap will not generate any relationships betweentwo classes that are in an inheritance relationship. That is, with twoclasses given as follows:

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',
    }

The foreign key from Engineer to Employee is used not for arelationship, but to establish joined inheritance between the two classes.

Note that this means automap will not generate any relationshipsfor foreign keys that link from a subclass to a superclass. If a mappinghas actual relationships from subclass to superclass as well, thoseneed to be explicit. Below, as we have two separate foreign keysfrom Engineer to Employee, we need to set up both the relationshipwe want as well as the inherit_condition, as these are not thingsSQLAlchemy can guess:

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
    }

Handling Simple Naming Conflicts

In the case of naming conflicts during mapping, override any ofclassname_for_table(), name_for_scalar_relationship(),and name_for_collection_relationship() as needed. For example, ifautomap is attempting to name a many-to-one relationship the same as anexisting column, an alternate convention can be conditionally selected. Givena schema:

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)
);

The above schema will first automap the table_a table as a class namedtable_a; it will then automap a relationship onto the class for table_bwith the same name as this related class, e.g. table_a. Thisrelationship name conflicts with the mapping column table_b.table_a,and will emit an error on mapping.

We can resolve this conflict by using an underscore as follows:

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)

Alternatively, we can change the name on the column side. The columnsthat are mapped can be modified using the technique described atNaming Columns Distinctly from Attribute Names, by assigning the column explicitlyto a new name:

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 makeuse of any collection of Table objects within a MetaDatacollection. From this, it follows that automap can also be usedgenerate missing relationships given an otherwise complete model that fullydefines 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, theForeignKey which we defined on Address.user_id allowed abidirectional relationship pair Address.user andUser.address_collection to be generated on the mapped classes.

Note that when subclassing AutomapBase,the AutomapBase.prepare() method is required; if not called, the classeswe’ve declared are in an un-mapped state.

API Reference

• sqlalchemy.ext.automap.automapbase(_declarative_base=None, **kw)
• Produce a declarative automap base.

This function produces a new base class that is a product of theAutomapBase class as well a declarative base produced bydeclarative.declarative_base().

All parameters other than declarative_base are keyword argumentsthat are passed directly to the declarative.declarative_base()function.

• Parameters

• • declarative_base – an existing class produced bydeclarative.declarative_base(). When this is passed, the functionno longer invokes declarative.declarative_base() itself, and allother keyword arguments are ignored.

  • **kw – keyword arguments are passed along todeclarative.declarative_base().

• class sqlalchemy.ext.automap.AutomapBase
• Base class for an “automap” schema.

The AutomapBase class can be compared to the “declarative base”class that is produced by the declarative.declarative_base()function. In practice, the AutomapBase class is always usedas a mixin along with an actual declarative base.

A new subclassable AutomapBase is typically instantiatedusing the automap_base() function.

See also

Automap

• classes = None
• An instance of util.Properties containing classes.

This object behaves much like the .c collection on a table. Classesare present under the name they were given, e.g.:

Base = automap_base()
Base.prepare(engine=some_engine, reflect=True)
 
User, Address = Base.classes.User, Base.classes.Address

• classmethod prepare(engine=None, reflect=False, schema=None, classname_for_table=, collection_class=, name_for_scalar_relationship=, name_for_collection_relationship=, generate_relationship=)

• Extract mapped classes and relationships from the MetaData andperform mappings.

  • Parameters

  • • engine – an Engine or Connection with whichto perform schema reflection, if specified.If the AutomapBase.prepare.reflect argument is False,this object is not used.

    • reflect – if True, the MetaData.reflect() method is calledon the MetaData associated with this AutomapBase.The Engine passed viaAutomapBase.prepare.engine will be used to perform thereflection if present; else, the MetaData should already bebound to some engine else the operation will fail.

    • classname_for_table – callable function which will be used toproduce new class names, given a table name. Defaults toclassname_for_table().

    • name_for_scalar_relationship – callable function which will beused to produce relationship names for scalar relationships. Defaultsto name_for_scalar_relationship().

    • name_for_collection_relationship – callable function which willbe used to produce relationship names for collection-orientedrelationships. Defaults to name_for_collection_relationship().

    • generate_relationship – callable function which will be used toactually generate relationship() and backref()constructs. Defaults to generate_relationship().

    • collection_class – the Python collection class that will be usedwhen a new relationship() object is created that represents acollection. Defaults to list.

    • schema –

When present in conjunction with theAutomapBase.prepare.reflect flag, is passed toMetaData.reflect() to indicate the primary schema where tablesshould be reflected from. When omitted, the default schema in useby the database connection is used.

New in version 1.1.

• sqlalchemy.ext.automap.classnamefor_table(_base, tablename, table)
• Return the class name that should be used, given the nameof a table.

The default implementation is:

return str(tablename)

Alternate implementations can be specified using theAutomapBase.prepare.classname_for_tableparameter.

• Parameters

• • base – the AutomapBase class doing the prepare.

  • tablename – string name of the Table.

  • table – the Table object itself.

• Returns

a string class name.

Note

In Python 2, the string used for the class name must be anon-Unicode object, e.g. a str() object. The .name attributeof Table is typically a Python unicode subclass, so thestr() function should be applied to this name, after accounting forany non-ASCII characters.

• sqlalchemy.ext.automap.namefor_scalar_relationship(_base, local_cls, referred_cls, constraint)
• Return the attribute name that should be used to refer from oneclass to another, for a scalar object reference.

The default implementation is:

return referred_cls.__name__.lower()

Alternate implementations can be specified using theAutomapBase.prepare.name_for_scalar_relationshipparameter.

• Parameters

• • base – the AutomapBase class doing the prepare.

  • local_cls – the class to be mapped on the local side.

  • referred_cls – the class to be mapped on the referring side.

  • constraint – the ForeignKeyConstraint that is beinginspected to produce this relationship.

• sqlalchemy.ext.automap.namefor_collection_relationship(_base, local_cls, referred_cls, constraint)
• Return the attribute name that should be used to refer from oneclass to another, for a collection reference.

The default implementation is:

return referred_cls.__name__.lower() + "_collection"

Alternate implementationscan be specified using theAutomapBase.prepare.name_for_collection_relationshipparameter.

• Parameters

• • base – the AutomapBase class doing the prepare.

  • local_cls – the class to be mapped on the local side.

  • referred_cls – the class to be mapped on the referring side.

  • constraint – the ForeignKeyConstraint that is beinginspected to produce this relationship.

• sqlalchemy.ext.automap.generaterelationship(_base, direction, return_fn, attrname, local_cls, referred_cls, **kw)
• Generate a relationship() or backref() on behalf of twomapped classes.

An alternate implementation of this function can be specified using theAutomapBase.prepare.generate_relationship parameter.

The default implementation of this function is as follows:

if return_fn is backref:
    return return_fn(attrname, **kw)
elif return_fn is relationship:
    return return_fn(referred_cls, **kw)
else:
    raise TypeError("Unknown relationship function: %s" % return_fn)

• Parameters

• • base – the AutomapBase class doing the prepare.

  • direction – indicate the “direction” of the relationship; this willbe one of ONETOMANY, MANYTOONE, MANYTOMANY.

  • return_fn – the function that is used by default to create therelationship. This will be either relationship() orbackref(). The backref() function’s result will be used toproduce a new relationship() in a second step, so it is criticalthat user-defined implementations correctly differentiate between the twofunctions, if a custom relationship function is being used.

  • attrname – the attribute name to which this relationship is beingassigned. If the value of generate_relationship.return_fn isthe backref() function, then this name is the name that is beingassigned to the backref.

  • local_cls – the “local” class to which this relationship or backrefwill be locally present.

  • referred_cls – the “referred” class to which the relationship orbackref refers to.

  • **kw – all additional keyword arguments are passed along to thefunction.

• Returns

• a relationship() or backref() construct, as dictatedby the generate_relationship.return_fn parameter.