SQLAlchemy 0.9 Documentation
Define an extension to the sqlalchemy.ext.declarative system which automatically generates mapped classes and relationships from a database schema, typically though not necessarily one which is reflected.
New in version 0.9.1: Added sqlalchemy.ext.automap.
The sqlalchemy.ext.automap extension should be considered experimental as of 0.9.1. Featureset and API stability is not guaranteed at this time.
It is hoped that the AutomapBase system provides a quick and modernized solution to the problem that the very famous SQLSoup also tries to solve, that of generating a quick and rudimentary object model from an existing database on the fly. By addressing the issue strictly at the mapper configuration level, and integrating fully with existing Declarative class techniques, AutomapBase seeks to provide a well-integrated approach to the issue of expediently auto-generating ad-hoc mappings.
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="email@example.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 AutomapBase.prepare.reflect parameter indicates that the MetaData.reflect() method will be called on this declarative base classes’ MetaData collection; then, each viable Table within the MetaData will get a new mapped class generated automatically. The ForeignKeyConstraint objects which link the various tables together will be used to produce new, bidirectional 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 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 MetaData.reflect(). Below we illustrate a combination of reflection and explicit table declaration:
from sqlalchemy import create_engine, MetaData, Table, Column, ForeignKey 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 Explcitly¶
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 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.upper() + \ re.sub(r'_(\w)', 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 = referred_name.lower() + \ re.sub(r'\W', 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="firstname.lastname@example.org")])
The vast majority of what automap accomplishes is the generation of relationship() structures based on foreign keys. The mechanism by which this works for many-to-one and one-to-many relationships is as follows:
- A given Table, known to be mapped to a particular class, is examined for ForeignKeyConstraint objects.
- From each ForeignKeyConstraint, the remote Table object 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 correponds 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.
- The names of the relationships are determined using the AutomapBase.prepare.name_for_scalar_relationship and 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.
- 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 relationship.back_populates parameter in order to point 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 the relationship.backref parameter.
- Production of the relationship() and optionally the backref() is handed off to the AutomapBase.prepare.generate_relationship function, which can be supplied by the end-user in order to augment the arguments passed to relationship() or backref() or to make use of custom implementations of these functions.
Custom Relationship Arguments¶
The AutomapBase.prepare.generate_relationship hook can be used to add parameters to relationships. For most cases, we can make use of the existing automap.generate_relationship() function to return the object, after augmenting the given keyword dictionary with our own arguments.
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)
sqlalchemy.ext.automap will generate many-to-many relationships, e.g. those which contain a secondary argument. The process for producing these is as follows:
- A given Table is examined for ForeignKeyConstraint objects, before any mapped class has been assigned to it.
- If the table contains two and exactly two ForeignKeyConstraint objects, and all columns within this table are members of these two ForeignKeyConstraint 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 the Table refers to are matched to the classes to which they will be mapped, if any.
- If mapped classes for both sides are located, a many-to-many bi-directional relationship() / backref() pair is created between the two classes.
- 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 upon to generate the strucures and existing attributes will be maintained.
Using Automap with Explicit Declarations¶
As noted previously, automap has no dependency on reflection, and can make use of any collection of Table objects within a 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 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.
- sqlalchemy.ext.automap.automap_base(declarative_base=None, **kw)¶
Produce a declarative automap base.
All parameters other than declarative_base are keyword arguments that are passed directly to the declarative.declarative_base() function.
- 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 used as a mixin along with an actual declarative base.
- classes = None¶
An instance of util.Properties containing classes.
This object behaves much like the .c collection on a table. Classes are 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, classname_for_table=<function classname_for_table at 0xae5dc80>, collection_class=<type 'list'>, name_for_scalar_relationship=<function name_for_scalar_relationship at 0xbeab0c8>, name_for_collection_relationship=<function name_for_collection_relationship at 0xbeab140>, generate_relationship=<function generate_relationship at 0xbeab1b8>)¶
Extract mapped classes and relationships from the MetaData and perform mappings.
- engine¶ – an Engine or Connection with which to 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 called on the MetaData associated with this AutomapBase. The Engine passed via AutomapBase.prepare.engine will be used to perform the reflection if present; else, the MetaData should already be bound to some engine else the operation will fail.
- classname_for_table¶ – callable function which will be used to produce new class names, given a table name. Defaults to classname_for_table().
- name_for_scalar_relationship¶ – callable function which will be used to produce relationship names for scalar relationships. Defaults to name_for_scalar_relationship().
- name_for_collection_relationship¶ – callable function which will be used to produce relationship names for collection-oriented relationships. Defaults to name_for_collection_relationship().
- generate_relationship¶ – callable function which will be used to actually generate relationship() and backref() constructs. Defaults to generate_relationship().
- collection_class¶ – the Python collection class that will be used when a new relationship() object is created that represents a collection. Defaults to list.
- sqlalchemy.ext.automap.classname_for_table(base, tablename, table)¶
Return the class name that should be used, given the name of a table.
The default implementation is:
Alternate implementations can be specified using the AutomapBase.prepare.classname_for_table parameter.
a string class name.
In Python 2, the string used for the class name must be a non-Unicode object, e.g. a str() object. The .name attribute of Table is typically a Python unicode subclass, so the str() function should be applied to this name, after accounting for any non-ASCII characters.
- sqlalchemy.ext.automap.name_for_scalar_relationship(base, local_cls, referred_cls, constraint)¶
Return the attribute name that should be used to refer from one class to another, for a scalar object reference.
The default implementation is:
Alternate implementations can be specified using the AutomapBase.prepare.name_for_scalar_relationship parameter.
- sqlalchemy.ext.automap.name_for_collection_relationship(base, local_cls, referred_cls, constraint)¶
Return the attribute name that should be used to refer from one class to another, for a collection reference.
The default implementation is:
return referred_cls.__name__.lower() + "_collection"
Alternate implementations can be specified using the AutomapBase.prepare.name_for_collection_relationship parameter.
- sqlalchemy.ext.automap.generate_relationship(base, direction, return_fn, attrname, local_cls, referred_cls, **kw)¶
An alternate implementation of this function can be specified using the AutomapBase.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)
- base¶ – the AutomapBase class doing the prepare.
- direction¶ – indicate the “direction” of the relationship; this will be one of ONETOMANY, MANYTOONE, MANYTOONE.
- return_fn¶ – the function that is used by default to create the relationship. This will be either relationship() or backref(). The backref() function’s result will be used to produce a new relationship() in a second step, so it is critical that user-defined implementations correctly differentiate between the two functions, if a custom relationship function is being used.
- local_cls¶ – the “local” class to which this relationship or backref will be locally present.
- referred_cls¶ – the “referred” class to which the relationship or backref refers to.
- **kw¶ – all additional keyword arguments are passed along to the function.
the attribute name to which this relationship is being assigned. If the value of generate_relationship.return_fn is the backref() function, then this name is the name that is being assigned to the backref.