SQLAlchemy 1.3 Documentation
SQLAlchemy ORM
- Object Relational Tutorial
- Mapper Configuration
- Relationship Configuration
- Loading Objects
- Using the Session
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
Inheritance Configuration¶
Declarative supports all three forms of inheritance as intuitively
as possible. The inherits
mapper keyword argument is not needed
as declarative will determine this from the class itself. The various
“polymorphic” keyword arguments are specified using __mapper_args__
.
See also
This section describes some specific details on how the Declarative system interacts with SQLAlchemy ORM inheritance configuration. See Mapping Class Inheritance Hierarchies for a general introduction to inheritance mapping.
Joined Table Inheritance¶
Joined table inheritance is defined as a subclass that defines its own table:
class Person(Base):
__tablename__ = 'people'
id = Column(Integer, primary_key=True)
discriminator = Column('type', String(50))
__mapper_args__ = {'polymorphic_on': discriminator}
class Engineer(Person):
__tablename__ = 'engineers'
__mapper_args__ = {'polymorphic_identity': 'engineer'}
id = Column(Integer, ForeignKey('people.id'), primary_key=True)
primary_language = Column(String(50))
Note that above, the Engineer.id
attribute, since it shares the
same attribute name as the Person.id
attribute, will in fact
represent the people.id
and engineers.id
columns together,
with the “Engineer.id” column taking precedence if queried directly.
To provide the Engineer
class with an attribute that represents
only the engineers.id
column, give it a different attribute name:
class Engineer(Person):
__tablename__ = 'engineers'
__mapper_args__ = {'polymorphic_identity': 'engineer'}
engineer_id = Column('id', Integer, ForeignKey('people.id'),
primary_key=True)
primary_language = Column(String(50))
Single Table Inheritance¶
Single table inheritance is defined as a subclass that does not have
its own table; you just leave out the __table__
and __tablename__
attributes:
class Person(Base):
__tablename__ = 'people'
id = Column(Integer, primary_key=True)
discriminator = Column('type', String(50))
__mapper_args__ = {'polymorphic_on': discriminator}
class Engineer(Person):
__mapper_args__ = {'polymorphic_identity': 'engineer'}
primary_language = Column(String(50))
When the above mappers are configured, the Person
class is mapped
to the people
table before the primary_language
column is
defined, and this column will not be included in its own mapping.
When Engineer
then defines the primary_language
column, the
column is added to the people
table so that it is included in the
mapping for Engineer
and is also part of the table’s full set of
columns. Columns which are not mapped to Person
are also excluded
from any other single or joined inheriting classes using the
exclude_properties
mapper argument. Below, Manager
will have
all the attributes of Person
and Manager
but not the
primary_language
attribute of Engineer
:
class Manager(Person):
__mapper_args__ = {'polymorphic_identity': 'manager'}
golf_swing = Column(String(50))
The attribute exclusion logic is provided by the
exclude_properties
mapper argument, and declarative’s default
behavior can be disabled by passing an explicit exclude_properties
collection (empty or otherwise) to the __mapper_args__
.
Resolving Column Conflicts¶
Note above that the primary_language
and golf_swing
columns
are “moved up” to be applied to Person.__table__
, as a result of their
declaration on a subclass that has no table of its own. A tricky case
comes up when two subclasses want to specify the same column, as below:
class Person(Base):
__tablename__ = 'people'
id = Column(Integer, primary_key=True)
discriminator = Column('type', String(50))
__mapper_args__ = {'polymorphic_on': discriminator}
class Engineer(Person):
__mapper_args__ = {'polymorphic_identity': 'engineer'}
start_date = Column(DateTime)
class Manager(Person):
__mapper_args__ = {'polymorphic_identity': 'manager'}
start_date = Column(DateTime)
Above, the start_date
column declared on both Engineer
and Manager
will result in an error:
sqlalchemy.exc.ArgumentError: Column 'start_date' on class
<class '__main__.Manager'> conflicts with existing
column 'people.start_date'
In a situation like this, Declarative can’t be sure
of the intent, especially if the start_date
columns had, for example,
different types. A situation like this can be resolved by using
declared_attr
to define the Column
conditionally, taking
care to return the existing column via the parent __table__
if it
already exists:
from sqlalchemy.ext.declarative import declared_attr
class Person(Base):
__tablename__ = 'people'
id = Column(Integer, primary_key=True)
discriminator = Column('type', String(50))
__mapper_args__ = {'polymorphic_on': discriminator}
class Engineer(Person):
__mapper_args__ = {'polymorphic_identity': 'engineer'}
@declared_attr
def start_date(cls):
"Start date column, if not present already."
return Person.__table__.c.get('start_date', Column(DateTime))
class Manager(Person):
__mapper_args__ = {'polymorphic_identity': 'manager'}
@declared_attr
def start_date(cls):
"Start date column, if not present already."
return Person.__table__.c.get('start_date', Column(DateTime))
Above, when Manager
is mapped, the start_date
column is
already present on the Person
class. Declarative lets us return
that Column
as a result in this case, where it knows to skip
re-assigning the same column. If the mapping is mis-configured such
that the start_date
column is accidentally re-assigned to a
different table (such as, if we changed Manager
to be joined
inheritance without fixing start_date
), an error is raised which
indicates an existing Column
is trying to be re-assigned to
a different owning Table
.
The same concept can be used with mixin classes (see Mixin and Custom Base Classes):
class Person(Base):
__tablename__ = 'people'
id = Column(Integer, primary_key=True)
discriminator = Column('type', String(50))
__mapper_args__ = {'polymorphic_on': discriminator}
class HasStartDate(object):
@declared_attr
def start_date(cls):
return cls.__table__.c.get('start_date', Column(DateTime))
class Engineer(HasStartDate, Person):
__mapper_args__ = {'polymorphic_identity': 'engineer'}
class Manager(HasStartDate, Person):
__mapper_args__ = {'polymorphic_identity': 'manager'}
The above mixin checks the local __table__
attribute for the column.
Because we’re using single table inheritance, we’re sure that in this case,
cls.__table__
refers to Person.__table__
. If we were mixing joined-
and single-table inheritance, we might want our mixin to check more carefully
if cls.__table__
is really the Table
we’re looking for.
Concrete Table Inheritance¶
Concrete is defined as a subclass which has its own table and sets the
concrete
keyword argument to True
:
class Person(Base):
__tablename__ = 'people'
id = Column(Integer, primary_key=True)
name = Column(String(50))
class Engineer(Person):
__tablename__ = 'engineers'
__mapper_args__ = {'concrete':True}
id = Column(Integer, primary_key=True)
primary_language = Column(String(50))
name = Column(String(50))
Usage of an abstract base class is a little less straightforward as it
requires usage of polymorphic_union()
,
which needs to be created with the Table
objects
before the class is built:
engineers = Table('engineers', Base.metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('primary_language', String(50))
)
managers = Table('managers', Base.metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('golf_swing', String(50))
)
punion = polymorphic_union({
'engineer':engineers,
'manager':managers
}, 'type', 'punion')
class Person(Base):
__table__ = punion
__mapper_args__ = {'polymorphic_on':punion.c.type}
class Engineer(Person):
__table__ = engineers
__mapper_args__ = {'polymorphic_identity':'engineer', 'concrete':True}
class Manager(Person):
__table__ = managers
__mapper_args__ = {'polymorphic_identity':'manager', 'concrete':True}
The helper classes AbstractConcreteBase
and ConcreteBase
provide automation for the above system of creating a polymorphic union.
See the documentation for these helpers as well as the main ORM documentation
on concrete inheritance for details.
See also
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