SQLAlchemy 1.1 Documentation
SQLAlchemy ORM
- Object Relational Tutorial
- Mapper Configuration
- Types of Mappings
- Mapping Columns and Expressions
- Mapping Class Inheritance Hierarchies¶
- Non-Traditional Mappings
- Configuring a Version Counter
- Class Mapping API
- Relationship Configuration
- Loading Objects
- Using the Session
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
Mapping Class Inheritance Hierarchies¶
SQLAlchemy supports three forms of inheritance: single table inheritance, where several types of classes are represented by a single table, concrete table inheritance, where each type of class is represented by independent tables, and joined table inheritance, where the class hierarchy is broken up among dependent tables, each class represented by its own table that only includes those attributes local to that class.
The most common forms of inheritance are single and joined table, while concrete inheritance presents more configurational challenges.
When mappers are configured in an inheritance relationship, SQLAlchemy has the ability to load elements polymorphically, meaning that a single query can return objects of multiple types.
Joined Table Inheritance¶
In joined table inheritance, each class along a particular classes’ list of
parents is represented by a unique table. The total set of attributes for a
particular instance is represented as a join along all tables in its
inheritance path. Here, we first define the Employee
class.
This table will contain a primary key column (or columns), and a column
for each attribute that’s represented by Employee
. In this case it’s just
name
:
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(50))
type = Column(String(50))
__mapper_args__ = {
'polymorphic_identity':'employee',
'polymorphic_on':type
}
The mapped table also has a column called type
. The purpose of
this column is to act as the discriminator, and stores a value
which indicates the type of object represented within the row. The column may
be of any datatype, though string and integer are the most common.
경고
Currently, only one discriminator column may be set, typically on the base-most class in the hierarchy. “Cascading” polymorphic columns are not yet supported.
The discriminator column is only needed if polymorphic loading is desired, as is usually the case. It is not strictly necessary that it be present directly on the base mapped table, and can instead be defined on a derived select statement that’s used when the class is queried; however, this is a much more sophisticated configuration scenario.
The mapping receives additional arguments via the __mapper_args__
dictionary. Here the type
column is explicitly stated as the
discriminator column, and the polymorphic identity of employee
is also given; this is the value that will be
stored in the polymorphic discriminator column for instances of this
class.
We next define Engineer
and Manager
subclasses of Employee
.
Each contains columns that represent the attributes unique to the subclass
they represent. Each table also must contain a primary key column (or
columns), and in most cases a foreign key reference to the parent table:
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
engineer_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity':'engineer',
}
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
manager_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity':'manager',
}
It is standard practice that the same column is used for both the role of primary key as well as foreign key to the parent table, and that the column is also named the same as that of the parent table. However, both of these practices are optional. Separate columns may be used for primary key and parent-relationship, the column may be named differently than that of the parent, and even a custom join condition can be specified between parent and child tables instead of using a foreign key.
Joined inheritance primary keys
One natural effect of the joined table inheritance configuration is that the
identity of any mapped object can be determined entirely from the base table.
This has obvious advantages, so SQLAlchemy always considers the primary key
columns of a joined inheritance class to be those of the base table only.
In other words, the id
columns of both the engineer
and manager
tables are not used to locate
Engineer
or Manager
objects - only the value in
employee.id
is considered. engineer.id
and manager.id
are
still of course critical to the proper operation of the pattern overall as
they are used to locate the joined row, once the parent row has been
determined within a statement.
With the joined inheritance mapping complete, querying against Employee
will return a combination of
Employee
, Engineer
and Manager
objects. Newly saved Engineer
,
Manager
, and Employee
objects will automatically populate the
employee.type
column with engineer
, manager
, or employee
, as
appropriate.
Basic Control of Which Tables are Queried¶
The orm.with_polymorphic()
function and the
with_polymorphic()
method of
Query
affects the specific tables
which the Query
selects from. Normally, a query such as this:
session.query(Employee).all()
...selects only from the employee
table. When loading fresh from the
database, our joined-table setup will query from the parent table only, using
SQL such as this:
SELECT employee.id AS employee_id,
employee.name AS employee_name, employee.type AS employee_type
FROM employee
[]
As attributes are requested from those Employee
objects which are
represented in either the engineer
or manager
child tables, a second
load is issued for the columns in that related row, if the data was not
already loaded. So above, after accessing the objects you’d see further SQL
issued along the lines of:
SELECT manager.id AS manager_id,
manager.manager_data AS manager_manager_data
FROM manager
WHERE ? = manager.id
[5]
SELECT engineer.id AS engineer_id,
engineer.engineer_info AS engineer_engineer_info
FROM engineer
WHERE ? = engineer.id
[2]
This behavior works well when issuing searches for small numbers of items,
such as when using Query.get()
, since the full range of joined tables are not
pulled in to the SQL statement unnecessarily. But when querying a larger span
of rows which are known to be of many types, you may want to actively join to
some or all of the joined tables. The with_polymorphic
feature
provides this.
Telling our query to polymorphically load Engineer
and Manager
objects, we can use the orm.with_polymorphic()
function
to create a new aliased class which represents a select of the base
table combined with outer joins to each of the inheriting tables:
from sqlalchemy.orm import with_polymorphic
eng_plus_manager = with_polymorphic(Employee, [Engineer, Manager])
query = session.query(eng_plus_manager)
The above produces a query which joins the employee
table to both the
engineer
and manager
tables like the following:
query.all()
SELECT employee.id AS employee_id,
engineer.id AS engineer_id,
manager.id AS manager_id,
employee.name AS employee_name,
employee.type AS employee_type,
engineer.engineer_info AS engineer_engineer_info,
manager.manager_data AS manager_manager_data
FROM employee
LEFT OUTER JOIN engineer
ON employee.id = engineer.id
LEFT OUTER JOIN manager
ON employee.id = manager.id
[]
The entity returned by orm.with_polymorphic()
is an AliasedClass
object, which can be used in a Query
like any other alias, including
named attributes for those attributes on the Employee
class. In our
example, eng_plus_manager
becomes the entity that we use to refer to the
three-way outer join above. It also includes namespaces for each class named
in the list of classes, so that attributes specific to those subclasses can be
called upon as well. The following example illustrates calling upon attributes
specific to Engineer
as well as Manager
in terms of eng_plus_manager
:
eng_plus_manager = with_polymorphic(Employee, [Engineer, Manager])
query = session.query(eng_plus_manager).filter(
or_(
eng_plus_manager.Engineer.engineer_info=='x',
eng_plus_manager.Manager.manager_data=='y'
)
)
orm.with_polymorphic()
accepts a single class or
mapper, a list of classes/mappers, or the string '*'
to indicate all
subclasses:
# join to the engineer table
entity = with_polymorphic(Employee, Engineer)
# join to the engineer and manager tables
entity = with_polymorphic(Employee, [Engineer, Manager])
# join to all subclass tables
entity = with_polymorphic(Employee, '*')
# use the 'entity' with a Query object
session.query(entity).all()
It also accepts a third argument selectable
which replaces the automatic
join creation and instead selects directly from the selectable given. This
feature is normally used with “concrete” inheritance, described later, but can
be used with any kind of inheritance setup in the case that specialized SQL
should be used to load polymorphically:
# custom selectable
employee = Employee.__table__
manager = Manager.__table__
engineer = Engineer.__table__
entity = with_polymorphic(
Employee,
[Engineer, Manager],
employee.outerjoin(manager).outerjoin(engineer)
)
# use the 'entity' with a Query object
session.query(entity).all()
Note that if you only need to load a single subtype, such as just the
Engineer
objects, orm.with_polymorphic()
is
not needed since you would query against the Engineer
class directly.
Query.with_polymorphic()
has the same purpose
as orm.with_polymorphic()
, except is not as
flexible in its usage patterns in that it only applies to the first full
mapping, which then impacts all occurrences of that class or the target
subclasses within the Query
. For simple cases it might be
considered to be more succinct:
session.query(Employee).with_polymorphic([Engineer, Manager]).\
filter(or_(Engineer.engineer_info=='w', Manager.manager_data=='q'))
버전 0.8에 추가: orm.with_polymorphic()
, an improved version of
Query.with_polymorphic()
method.
The mapper also accepts with_polymorphic
as a configurational argument so
that the joined-style load will be issued automatically. This argument may be
the string '*'
, a list of classes, or a tuple consisting of either,
followed by a selectable:
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
type = Column(String(20))
__mapper_args__ = {
'polymorphic_on':type,
'polymorphic_identity':'employee',
'with_polymorphic':'*'
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity':'engineer'}
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity':'manager'}
The above mapping will produce a query similar to that of
with_polymorphic('*')
for every query of Employee
objects.
Using orm.with_polymorphic()
or Query.with_polymorphic()
will override the mapper-level with_polymorphic
setting.
-
sqlalchemy.orm.
with_polymorphic
(base, classes, selectable=False, flat=False, polymorphic_on=None, aliased=False, innerjoin=False, _use_mapper_path=False, _existing_alias=None)¶ Produce an
AliasedClass
construct which specifies columns for descendant mappers of the given base.버전 0.8에 추가:
orm.with_polymorphic()
is in addition to the existingQuery
methodQuery.with_polymorphic()
, which has the same purpose but is not as flexible in its usage.Using this method will ensure that each descendant mapper’s tables are included in the FROM clause, and will allow filter() criterion to be used against those tables. The resulting instances will also have those columns already loaded so that no “post fetch” of those columns will be required.
See the examples at Basic Control of Which Tables are Queried.
매개 변수: - base¶ – Base class to be aliased.
- classes¶ – a single class or mapper, or list of
class/mappers, which inherit from the base class.
Alternatively, it may also be the string
'*'
, in which case all descending mapped classes will be added to the FROM clause. - aliased¶ – when True, the selectable will be wrapped in an
alias, that is
(SELECT * FROM <fromclauses>) AS anon_1
. This can be important when using the with_polymorphic() to create the target of a JOIN on a backend that does not support parenthesized joins, such as SQLite and older versions of MySQL. - flat¶ –
- Boolean, will be passed through to the
FromClause.alias()
call so that aliases ofJoin
objects don’t include an enclosing SELECT. This can lead to more efficient queries in many circumstances. A JOIN against a nested JOIN will be rewritten as a JOIN against an aliased SELECT subquery on backends that don’t support this syntax.
Setting
flat
toTrue
implies thealiased
flag is alsoTrue
.버전 0.9.0에 추가.
더 보기
- selectable¶ – a table or select() statement that will
be used in place of the generated FROM clause. This argument is
required if any of the desired classes use concrete table
inheritance, since SQLAlchemy currently cannot generate UNIONs
among tables automatically. If used, the
selectable
argument must represent the full set of tables and columns mapped by every mapped class. Otherwise, the unaccounted mapped columns will result in their table being appended directly to the FROM clause which will usually lead to incorrect results. - polymorphic_on¶ – a column to be used as the “discriminator” column for the given selectable. If not given, the polymorphic_on attribute of the base classes’ mapper will be used, if any. This is useful for mappings that don’t have polymorphic loading behavior by default.
- innerjoin¶ – if True, an INNER JOIN will be used. This should only be specified if querying for one specific subtype only
Advanced Control of Which Tables are Queried¶
The with_polymorphic
functions work fine for
simplistic scenarios. However, direct control of table rendering
is called for, such as the case when one wants to
render to only the subclass table and not the parent table.
This use case can be achieved by using the mapped Table
objects directly. For example, to
query the name of employees with particular criterion:
engineer = Engineer.__table__
manager = Manager.__table__
session.query(Employee.name).\
outerjoin((engineer, engineer.c.employee_id==Employee.employee_id)).\
outerjoin((manager, manager.c.employee_id==Employee.employee_id)).\
filter(or_(Engineer.engineer_info=='w', Manager.manager_data=='q'))
The base table, in this case the “employees” table, isn’t always necessary. A
SQL query is always more efficient with fewer joins. Here, if we wanted to
just load information specific to manager or engineer, we can instruct
Query
to use only those tables. The FROM
clause is determined by
what’s specified in the Session.query()
, Query.filter()
, or
Query.select_from()
methods:
session.query(Manager.manager_data).select_from(manager)
session.query(engineer.c.id).\
filter(engineer.c.engineer_info==manager.c.manager_data)
Creating Joins to Specific Subtypes¶
The of_type()
method is a
helper which allows the construction of joins along
relationship()
paths while narrowing the criterion to
specific subclasses. Suppose the employees
table represents a collection
of employees which are associated with a Company
object. We’ll add a
company_id
column to the employees
table and a new table
companies
:
class Company(Base):
__tablename__ = 'company'
id = Column(Integer, primary_key=True)
name = Column(String(50))
employees = relationship("Employee",
backref='company',
cascade='all, delete-orphan')
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
type = Column(String(20))
company_id = Column(Integer, ForeignKey('company.id'))
__mapper_args__ = {
'polymorphic_on':type,
'polymorphic_identity':'employee',
'with_polymorphic':'*'
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
engineer_info = Column(String(50))
__mapper_args__ = {'polymorphic_identity':'engineer'}
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
manager_data = Column(String(50))
__mapper_args__ = {'polymorphic_identity':'manager'}
When querying from Company
onto the Employee
relationship, the
join()
method as well as the any()
and has()
operators will create
a join from company
to employee
, without including engineer
or
manager
in the mix. If we wish to have criterion which is specifically
against the Engineer
class, we can tell those methods to join or subquery
against the joined table representing the subclass using the
of_type()
operator:
session.query(Company).\
join(Company.employees.of_type(Engineer)).\
filter(Engineer.engineer_info=='someinfo')
A longhand version of this would involve spelling out the full target selectable within a 2-tuple:
employee = Employee.__table__
engineer = Engineer.__table__
session.query(Company).\
join((employee.join(engineer), Company.employees)).\
filter(Engineer.engineer_info=='someinfo')
of_type()
accepts a
single class argument. More flexibility can be achieved either by
joining to an explicit join as above, or by using the orm.with_polymorphic()
function to create a polymorphic selectable:
manager_and_engineer = with_polymorphic(
Employee, [Manager, Engineer],
aliased=True)
session.query(Company).\
join(manager_and_engineer, Company.employees).\
filter(
or_(manager_and_engineer.Engineer.engineer_info=='someinfo',
manager_and_engineer.Manager.manager_data=='somedata')
)
Above, we use the aliased=True
argument with orm.with_polymorhpic()
so that the right hand side of the join between Company
and manager_and_engineer
is converted into an aliased subquery. Some backends, such as SQLite and older
versions of MySQL can’t handle a FROM clause of the following form:
FROM x JOIN (y JOIN z ON <onclause>) ON <onclause>
Using aliased=True
instead renders it more like:
FROM x JOIN (SELECT * FROM y JOIN z ON <onclause>) AS anon_1 ON <onclause>
The above join can also be expressed more succinctly by combining of_type()
with the polymorphic construct:
manager_and_engineer = with_polymorphic(
Employee, [Manager, Engineer],
aliased=True)
session.query(Company).\
join(Company.employees.of_type(manager_and_engineer)).\
filter(
or_(manager_and_engineer.Engineer.engineer_info=='someinfo',
manager_and_engineer.Manager.manager_data=='somedata')
)
The any()
and has()
operators also can be used with
of_type()
when the embedded
criterion is in terms of a subclass:
session.query(Company).\
filter(
Company.employees.of_type(Engineer).
any(Engineer.engineer_info=='someinfo')
).all()
Note that the any()
and has()
are both shorthand for a correlated
EXISTS query. To build one by hand looks like:
session.query(Company).filter(
exists([1],
and_(Engineer.engineer_info=='someinfo',
employees.c.company_id==companies.c.company_id),
from_obj=employees.join(engineers)
)
).all()
The EXISTS subquery above selects from the join of employees
to
engineers
, and also specifies criterion which correlates the EXISTS
subselect back to the parent companies
table.
버전 0.8에 추가: of_type()
accepts
orm.aliased()
and orm.with_polymorphic()
constructs in conjunction
with Query.join()
, any()
and has()
.
Eager Loading of Specific or Polymorphic Subtypes¶
The joinedload()
, subqueryload()
, contains_eager()
and
other loading-related options also support
paths which make use of of_type()
.
Below we load Company
rows while eagerly loading related Engineer
objects, querying the employee
and engineer
tables simultaneously:
session.query(Company).\
options(
subqueryload(Company.employees.of_type(Engineer)).
subqueryload("machines")
)
)
As is the case with Query.join()
, of_type()
also can be used with eager loading and orm.with_polymorphic()
at the same time, so that all sub-attributes of all referenced subtypes
can be loaded:
manager_and_engineer = with_polymorphic(
Employee, [Manager, Engineer],
aliased=True)
session.query(Company).\
options(
joinedload(Company.employees.of_type(manager_and_engineer))
)
)
버전 0.8에 추가: joinedload()
, subqueryload()
, contains_eager()
and related loader options support
paths that are qualified with
of_type()
, supporting
single target types as well as orm.with_polymorphic()
targets.
Another option for the above query is to state the two subtypes separately;
the joinedload()
directive should detect this and create the
above with_polymorphic
construct automatically:
session.query(Company).\
options(
joinedload(Company.employees.of_type(Manager)),
joinedload(Company.employees.of_type(Engineer)),
)
)
버전 1.0에 추가: Eager loaders such as joinedload()
will create a polymorphic
entity when multiple overlapping of_type()
directives are encountered.
Single Table Inheritance¶
Single table inheritance is where the attributes of the base class as well as
all subclasses are represented within a single table. A column is present in
the table for every attribute mapped to the base class and all subclasses; the
columns which correspond to a single subclass are nullable. This configuration
looks much like joined-table inheritance except there’s only one table. In
this case, a type
column is required, as there would be no other way to
discriminate between classes. The table is specified in the base mapper only;
for the inheriting classes, leave their table
parameter blank:
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(50))
manager_data = Column(String(50))
engineer_info = Column(String(50))
type = Column(String(20))
__mapper_args__ = {
'polymorphic_on':type,
'polymorphic_identity':'employee'
}
class Manager(Employee):
__mapper_args__ = {
'polymorphic_identity':'manager'
}
class Engineer(Employee):
__mapper_args__ = {
'polymorphic_identity':'engineer'
}
Note that the mappers for the derived classes Manager and Engineer omit the
__tablename__
, indicating they do not have a mapped table of
their own.
Concrete Table Inheritance¶
This form of inheritance maps each class to a distinct table. As concrete inheritance has a bit more conceptual overhead, first we’ll illustrate what these tables look like as Core table metadata:
employees_table = Table(
'employee', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
)
managers_table = Table(
'manager', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('manager_data', String(50)),
)
engineers_table = Table(
'engineer', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('engineer_info', String(50)),
)
Notice in this case there is no type
column; for polymorphic loading,
additional steps will be needed in order to “manufacture” this information
during a query.
Using classical mapping, we can map our three classes independently without
any relationship between them; the fact that Engineer
and Manager
inherit from Employee
does not have any impact on a classical mapping:
class Employee(object):
pass
class Manager(Employee):
pass
class Engineer(Employee):
pass
mapper(Employee, employees_table)
mapper(Manager, managers_table)
mapper(Engineer, engineers_table)
However when using Declarative, Declarative assumes an inheritance mapping
between the classes because they are already in an inheritance relationship.
So to map our three classes declaratively, we must include the
orm.mapper.concrete
parameter within the __mapper_args__
:
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(50))
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, primary_key=True)
name = Column(String(50))
manager_data = Column(String(50))
__mapper_args__ = {
'concrete': True
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, primary_key=True)
name = Column(String(50))
engineer_info = Column(String(50))
__mapper_args__ = {
'concrete': True
}
Two critical points should be noted:
- We must define all columns explicitly on each subclass, even those of
the same name. A column such as
Employee.name
here is not copied out to the tables mapped byManager
orEngineer
for us. - while the
Engineer
andManager
classes are mapped in an inheritance relationship withEmployee
, they still do not include polymorphic loading.
Concrete Polymorphic Loading¶
To load polymorphically, the orm.mapper.with_polymorphic
argument is required, along
with a selectable indicating how rows should be loaded. Polymorphic loading
is most inefficient with concrete inheritance, so if we do seek this style of
loading, while it is possible it’s less recommended. In the case of concrete
inheritance, it means we must construct a UNION of all three tables.
First illustrating this with classical mapping, SQLAlchemy includes a helper
function to create this UNION called polymorphic_union()
, which
will map all the different columns into a structure of selects with the same
numbers and names of columns, and also generate a virtual type
column for
each subselect. The function is called after all three tables are declared,
and is then combined with the mappers:
from sqlalchemy.orm import polymorphic_union
pjoin = polymorphic_union({
'employee': employees_table,
'manager': managers_table,
'engineer': engineers_table
}, 'type', 'pjoin')
employee_mapper = mapper(Employee, employees_table,
with_polymorphic=('*', pjoin),
polymorphic_on=pjoin.c.type,
polymorphic_identity='employee')
manager_mapper = mapper(Manager, managers_table,
inherits=employee_mapper,
concrete=True,
polymorphic_identity='manager')
engineer_mapper = mapper(Engineer, engineers_table,
inherits=employee_mapper,
concrete=True,
polymorphic_identity='engineer')
Upon select, the polymorphic union produces a query like this:
session.query(Employee).all()
SELECT
pjoin.id AS pjoin_id,
pjoin.name AS pjoin_name,
pjoin.type AS pjoin_type,
pjoin.manager_data AS pjoin_manager_data,
pjoin.engineer_info AS pjoin_engineer_info
FROM (
SELECT
employee.id AS id,
employee.name AS name,
CAST(NULL AS VARCHAR(50)) AS manager_data,
CAST(NULL AS VARCHAR(50)) AS engineer_info,
'employee' AS type
FROM employee
UNION ALL
SELECT
manager.id AS id,
manager.name AS name,
manager.manager_data AS manager_data,
CAST(NULL AS VARCHAR(50)) AS engineer_info,
'manager' AS type
FROM manager
UNION ALL
SELECT
engineer.id AS id,
engineer.name AS name,
CAST(NULL AS VARCHAR(50)) AS manager_data,
engineer.engineer_info AS engineer_info,
'engineer' AS type
FROM engineer
) AS pjoin
The above UNION query needs to manufacture “NULL” columns for each subtable in order to accommodate for those columns that aren’t part of the mapping.
In order to map with concrete inheritance and polymorphic loading using
Declarative, the challenge is to have the polymorphic union ready to go
when the mappings are created. One way to achieve this is to continue to
define the table metadata before the actual mapped classes, and specify
them to each class using __table__
:
class Employee(Base):
__table__ = employee_table
__mapper_args__ = {
'polymorphic_on':pjoin.c.type,
'with_polymorphic': ('*', pjoin),
'polymorphic_identity':'employee'
}
class Engineer(Employee):
__table__ = engineer_table
__mapper_args__ = {'polymorphic_identity':'engineer', 'concrete':True}
class Manager(Employee):
__table__ = manager_table
__mapper_args__ = {'polymorphic_identity':'manager', 'concrete':True}
Using the Declarative Helper Classes¶
Another way is to use a special helper class that takes on the fairly
complicated task of deferring the production of Mapper
objects
until all table metadata has been collected, and the polymorphic union to which
the mappers will be associated will be available. This is available via
the AbstractConcreteBase
and ConcreteBase
classes. For
our example here, we’re using a “concrete” base, e.g. an Employee
row
can exist by itself that is not an Engineer
or a Manager
. The
mapping would look like:
from sqlalchemy.ext.declarative import ConcreteBase
class Employee(ConcreteBase, Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(50))
__mapper_args__ = {
'polymorphic_identity':'employee',
'concrete':True
}
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, primary_key=True)
name = Column(String(50))
manager_data = Column(String(40))
__mapper_args__ = {
'polymorphic_identity':'manager',
'concrete':True
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, primary_key=True)
name = Column(String(50))
engineer_info = Column(String(40))
__mapper_args__ = {
'polymorphic_identity':'engineer',
'concrete':True
}
There is also the option to use a so-called “abstract” base; where we wont
actually have an employee
table at all, and instead will only have
manager
and engineer
tables. The Employee
class will never be
instantiated directly. The change here is that the base mapper is mapped
directly to the “polymorphic union” selectable, which no longer includes
the employee
table. In classical mapping, this is:
from sqlalchemy.orm import polymorphic_union
pjoin = polymorphic_union({
'manager': managers_table,
'engineer': engineers_table
}, 'type', 'pjoin')
employee_mapper = mapper(Employee, pjoin,
with_polymorphic=('*', pjoin),
polymorphic_on=pjoin.c.type)
manager_mapper = mapper(Manager, managers_table,
inherits=employee_mapper,
concrete=True,
polymorphic_identity='manager')
engineer_mapper = mapper(Engineer, engineers_table,
inherits=employee_mapper,
concrete=True,
polymorphic_identity='engineer')
Using the Declarative helpers, the AbstractConcreteBase
helper
can produce this; the mapping would be:
from sqlalchemy.ext.declarative import AbstractConcreteBase
class Employee(AbstractConcreteBase, Base):
pass
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, primary_key=True)
name = Column(String(50))
manager_data = Column(String(40))
__mapper_args__ = {
'polymorphic_identity':'manager',
'concrete':True
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, primary_key=True)
name = Column(String(50))
engineer_info = Column(String(40))
__mapper_args__ = {
'polymorphic_identity':'engineer',
'concrete':True
}
더 보기
Concrete Table Inheritance - in the Declarative reference documentation
Using Relationships with Inheritance¶
Both joined-table and single table inheritance scenarios produce mappings
which are usable in relationship()
functions; that is,
it’s possible to map a parent object to a child object which is polymorphic.
Similarly, inheriting mappers can have relationship()
objects of their own at any level, which are inherited to each child class.
The only requirement for relationships is that there is a table relationship
between parent and child. An example is the following modification to the
joined table inheritance example, which sets a bi-directional relationship
between Employee
and Company
:
employees_table = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('name', String(50)),
Column('company_id', Integer, ForeignKey('companies.company_id'))
)
companies = Table('companies', metadata,
Column('company_id', Integer, primary_key=True),
Column('name', String(50)))
class Company(object):
pass
mapper(Company, companies, properties={
'employees': relationship(Employee, backref='company')
})
Relationships with Concrete Inheritance¶
In a concrete inheritance scenario, mapping relationships is more challenging since the distinct classes do not share a table. In this case, you can establish a relationship from parent to child if a join condition can be constructed from parent to child, if each child table contains a foreign key to the parent:
companies = Table('companies', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)))
employees_table = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('name', String(50)),
Column('company_id', Integer, ForeignKey('companies.id'))
)
managers_table = Table('managers', metadata,
Column('employee_id', Integer, primary_key=True),
Column('name', String(50)),
Column('manager_data', String(50)),
Column('company_id', Integer, ForeignKey('companies.id'))
)
engineers_table = Table('engineers', metadata,
Column('employee_id', Integer, primary_key=True),
Column('name', String(50)),
Column('engineer_info', String(50)),
Column('company_id', Integer, ForeignKey('companies.id'))
)
mapper(Employee, employees_table,
with_polymorphic=('*', pjoin),
polymorphic_on=pjoin.c.type,
polymorphic_identity='employee')
mapper(Manager, managers_table,
inherits=employee_mapper,
concrete=True,
polymorphic_identity='manager')
mapper(Engineer, engineers_table,
inherits=employee_mapper,
concrete=True,
polymorphic_identity='engineer')
mapper(Company, companies, properties={
'employees': relationship(Employee)
})
The big limitation with concrete table inheritance is that
relationship()
objects placed on each concrete mapper do
not propagate to child mappers. If you want to have the same
relationship()
objects set up on all concrete mappers,
they must be configured manually on each. To configure back references in such
a configuration the back_populates
keyword may be used instead of
backref
, such as below where both A(object)
and B(A)
bidirectionally reference C
:
ajoin = polymorphic_union({
'a':a_table,
'b':b_table
}, 'type', 'ajoin')
mapper(A, a_table, with_polymorphic=('*', ajoin),
polymorphic_on=ajoin.c.type, polymorphic_identity='a',
properties={
'some_c':relationship(C, back_populates='many_a')
})
mapper(B, b_table,inherits=A, concrete=True,
polymorphic_identity='b',
properties={
'some_c':relationship(C, back_populates='many_a')
})
mapper(C, c_table, properties={
'many_a':relationship(A, collection_class=set,
back_populates='some_c'),
})
Using Inheritance with Declarative¶
Declarative makes inheritance configuration more intuitive. See the docs at Inheritance Configuration.