SQLAlchemy 1.3 Documentation
SQLAlchemy ORM
- Object Relational Tutorial
- Mapper Configuration
- Relationship Configuration
- Loading Objects
- Using the Session
- Events and Internals
- ORM Extensions
- Association Proxy
- Automap¶
- Baked Queries
- Declarative
- Mutation Tracking
- Ordering List
- Horizontal Sharding
- Hybrid Attributes
- Indexable
- Alternate Class Instrumentation
- ORM Examples
Project Versions
Automap¶
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
.
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.
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
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.
Note
By viable, we mean that for a table to be mapped, it must specify a primary key. Additionally, if the table is detected as being a pure association table between two other tables, it will not be directly mapped and will instead be configured as a many-to-many table between the 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, 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
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 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¶
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
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 forForeignKeyConstraint
objects.From each
ForeignKeyConstraint
, the remoteTable
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 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.If any of the columns that are part of the
ForeignKeyConstraint
are not nullable (e.g.nullable=False
), arelationship.cascade
keyword argument ofall, delete-orphan
will be added to the keyword arguments to be passed to the relationship or backref. If theForeignKeyConstraint
reports thatForeignKeyConstraint.ondelete
is set toCASCADE
for a not null orSET NULL
for a nullable set of columns, the optionrelationship.passive_deletes
flag is set toTrue
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 specifiesForeignKeyConstraint.ondelete
ofCASCADE
for non-nullable orSET NULL
for nullable columns, thepassive_deletes=True
option is also added.The names of the relationships are determined using the
AutomapBase.prepare.name_for_scalar_relationship
andAutomapBase.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 therelationship.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 arelationship()
on the “many-to-one” side and matches it to the other using therelationship.backref
parameter.Production of the
relationship()
and optionally thebackref()
is handed off to theAutomapBase.prepare.generate_relationship
function, which can be supplied by the end-user in order to augment the arguments passed torelationship()
orbackref()
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 generate_relationship()
function to return
the object, after augmenting the given keyword dictionary with our own
arguments.
Below is an illustration of how to send
relationship.cascade
and
relationship.passive_deletes
options 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¶
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 forForeignKeyConstraint
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 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 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 structures and existing attributes will be maintained.
Relationships with Inheritance¶
automap
will not generate any relationships between
two classes that are in an inheritance relationship. That is, with two
classes 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 a
relationship, but to establish joined inheritance between the two classes.
Note that this means automap will not generate any relationships
for foreign keys that link from a subclass to a superclass. If a mapping
has actual relationships from subclass to superclass as well, those
need to be explicit. Below, as we have two separate foreign keys
from Engineer
to Employee
, we need to set up both the relationship
we want as well as the inherit_condition
, as these are not things
SQLAlchemy 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 of
classname_for_table()
, name_for_scalar_relationship()
,
and name_for_collection_relationship()
as needed. For example, if
automap is attempting to name a many-to-one relationship the same as an
existing column, an alternate convention can be conditionally selected. Given
a 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 named
table_a
; it will then automap a relationship onto the class for table_b
with the same name as this related class, e.g. table_a
. This
relationship 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 columns that are mapped can be modified using the technique described at Naming Columns Distinctly from Attribute Names, by assigning the column explicitly to 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 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.
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.
API Reference¶
Object Name | Description |
---|---|
automap_base([declarative_base], **kw) |
Produce a declarative automap base. |
Base class for an “automap” schema. |
|
classname_for_table(base, tablename, table) |
Return the class name that should be used, given the name of a table. |
generate_relationship(base, direction, return_fn, attrname, ..., **kw) |
Generate a |
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. |
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. |
- function sqlalchemy.ext.automap.automap_base(declarative_base=None, **kw)¶
Produce a declarative automap base.
This function produces a new base class that is a product of the
AutomapBase
class as well a declarative base produced bydeclarative_base()
.All parameters other than
declarative_base
are keyword arguments that are passed directly to thedeclarative_base()
function.- Parameters:
declarative_base¶ – an existing class produced by
declarative_base()
. When this is passed, the function no longer invokesdeclarative_base()
itself, and all other keyword arguments are ignored.**kw¶ – keyword arguments are passed along to
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 thedeclarative_base()
function. In practice, theAutomapBase
class is always used as a mixin along with an actual declarative base.A new subclassable
AutomapBase
is typically instantiated using theautomap_base()
function.See also
-
attribute
sqlalchemy.ext.automap.AutomapBase.
classes = None¶ An instance of
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
sqlalchemy.ext.automap.AutomapBase.
prepare(engine=None, reflect=False, schema=None, classname_for_table=<function classname_for_table>, collection_class=<class 'list'>, name_for_scalar_relationship=<function name_for_scalar_relationship>, name_for_collection_relationship=<function name_for_collection_relationship>, generate_relationship=<function generate_relationship>)¶ Extract mapped classes and relationships from the
MetaData
and perform mappings.- Parameters:
engine¶ – an
Engine
orConnection
with which to perform schema reflection, if specified. If theAutomapBase.prepare.reflect
argument is False, this object is not used.reflect¶ – if True, the
MetaData.reflect()
method is called on theMetaData
associated with thisAutomapBase
. TheEngine
passed viaAutomapBase.prepare.engine
will be used to perform the reflection if present; else, theMetaData
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()
andbackref()
constructs. Defaults togenerate_relationship()
.collection_class¶ – the Python collection class that will be used when a new
relationship()
object is created that represents a collection. Defaults tolist
.schema¶ –
When present in conjunction with the
AutomapBase.prepare.reflect
flag, is passed toMetaData.reflect()
to indicate the primary schema where tables should be reflected from. When omitted, the default schema in use by the database connection is used.New in version 1.1.
-
attribute
- function 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:
return str(tablename)
Alternate implementations can be specified using the
AutomapBase.prepare.classname_for_table
parameter.- Parameters:
- Returns:
a string class name.
Note
In Python 2, the string used for the class name must be a non-Unicode object, e.g. a
str()
object. The.name
attribute ofTable
is typically a Python unicode subclass, so thestr()
function should be applied to this name, after accounting for any non-ASCII characters.
- function 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:
return referred_cls.__name__.lower()
Alternate implementations can be specified using the
AutomapBase.prepare.name_for_scalar_relationship
parameter.- 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 being inspected to produce this relationship.
- function 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.- 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 being inspected to produce this relationship.
- function sqlalchemy.ext.automap.generate_relationship(base, direction, return_fn, attrname, local_cls, referred_cls, **kw)¶
Generate a
relationship()
orbackref()
on behalf of two mapped classes.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)
- Parameters:
base¶ – the
AutomapBase
class doing the prepare.direction¶ – indicate the “direction” of the relationship; this will be one of
ONETOMANY
,MANYTOONE
,MANYTOMANY
.return_fn¶ – the function that is used by default to create the relationship. This will be either
relationship()
orbackref()
. Thebackref()
function’s result will be used to produce a newrelationship()
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.attrname¶ – the attribute name to which this relationship is being assigned. If the value of
generate_relationship.return_fn
is thebackref()
function, then this name is the name that is being assigned to the backref.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.
- Returns:
a
relationship()
orbackref()
construct, as dictated by thegenerate_relationship.return_fn
parameter.
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Sat 06 Jan 2024 12:16:14 PM