Mapping Class Inheritance Hierarchies

    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.

    See also

    - in the ORM Querying Guide

    - complete examples of joined, single and concrete inheritance

    In joined table inheritance, each class along a hierarchy of classes is represented by a distinct table. Querying for a particular subclass in the hierarchy will render as a SQL JOIN along all tables in its inheritance path. If the queried class is the base class, the base table is queried instead, with options to include other tables at the same time or to allow attributes specific to sub-tables to load later.

    In all cases, the ultimate class to instantiate for a given row is determined by a column or SQL expression, defined on the base class, which will yield a scalar value that is associated with a particular subclass.

    The base class in a joined inheritance hierarchy is configured with additional arguments that will indicate to the polymorphic discriminator column, and optionally a polymorphic identifier for the base class itself:

    In the above example, the discriminator is the type column, whichever is configured using the Mapper.polymorphic_on parameter. This parameter accepts a column-oriented expression, specified either as a string name of the mapped attribute to use or as a column expression object such as or mapped_column() construct.

    The discriminator column will store 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. The actual data value to be applied to this column for a particular row in the database is specified using the parameter, described below.

    While a polymorphic discriminator expression is not strictly necessary, it is required if polymorphic loading is desired. Establishing a column on the base table is the easiest way to achieve this, however very sophisticated inheritance mappings may make use of SQL expressions, such as a CASE expression, as the polymorphic discriminator.

    Note

    Currently, only one discriminator column or SQL expression may be configured for the entire inheritance hierarchy, typically on the base- most class in the hierarchy. “Cascading” polymorphic discriminator expressions are not yet supported.

    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), as well as a foreign key reference to the parent table:

    1. class Engineer(Employee):
    2.     __tablename__ = "engineer"
    3.     id: Mapped[int] = mapped_column(ForeignKey("employee.id"), primary_key=True)
    4.     engineer_name: Mapped[str]
    6.     __mapper_args__ = {
    7.         "polymorphic_identity": "engineer",
    8.     }
    11. class Manager(Employee):
    12.     __tablename__ = "manager"
    13.     id: Mapped[int] = mapped_column(ForeignKey("employee.id"), primary_key=True)
    14.     manager_name: Mapped[str]
    16.     __mapper_args__ = {
    17.         "polymorphic_identity": "manager",
    18.     }

    In the above example, each mapping specifies the Mapper.polymorphic_identity parameter within its mapper arguments. This value populates the column designated by the parameter established on the base mapper. The Mapper.polymorphic_identity parameter should be unique to each mapped class across the whole hierarchy, and there should only be one “identity” per mapped class; as noted above, “cascading” identities where some subclasses introduce a second identity are not supported.

    The ORM uses the value set up by in order to determine which class a row belongs towards when loading rows polymorphically. In the example above, every row which represents an Employee will have the value 'employee' in its type row; similarly, every Engineer will get the value 'engineer', and each Manager will get the value 'manager'. Regardless of whether the inheritance mapping uses distinct joined tables for subclasses as in joined table inheritance, or all one table as in single table inheritance, this value is expected to be persisted and available to the ORM when querying. The Mapper.polymorphic_identity parameter also applies to concrete table inheritance, but is not actually persisted; see the later section at for details.

    In a polymorphic setup, it is most common that the foreign key constraint is established on the same column or columns as the primary key itself, however this is not required; a column distinct from the primary key may also be made to refer to the parent via foreign key. The way that a JOIN is constructed from the base table to subclasses is also directly customizable, however this is rarely necessary.

    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 rows in the base table alone. 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 the correct “discriminator” value in this case "engineer", "manager", or "employee", as appropriate.

    Relationships are fully supported with joined table inheritance. The relationship involving a joined-inheritance class should target the class in the hierarchy that also corresponds to the foreign key constraint; below, as the employee table has a foreign key constraint back to the company table, the relationships are set up between Company and Employee:

    1. from __future__ import annotations
    3. from sqlalchemy.orm import relationship
    6. class Company(Base):
    7.     __tablename__ = "company"
    8.     id: Mapped[int] = mapped_column(primary_key=True)
    9.     name: Mapped[str]
    10.     employees: Mapped[list[Employee]] = relationship(back_populates="company")
    13. class Employee(Base):
    14.     __tablename__ = "employee"
    15.     id: Mapped[int] = mapped_column(primary_key=True)
    16.     name: Mapped[str]
    17.     type: Mapped[str]
    18.     company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))
    19.     company: Mapped[Company] = relationship(back_populates="employees")
    21.     __mapper_args__ = {
    22.         "polymorphic_identity": "employee",
    23.         "polymorphic_on": "type",
    24.     }
    27. class Manager(Employee):
    28.     ...
    31. class Engineer(Employee):
    32.     ...

    If the foreign key constraint is on a table corresponding to a subclass, the relationship should target that subclass instead. In the example below, there is a foreign key constraint from manager to company, so the relationships are established between the Manager and Company classes:

    1. class Company(Base):
    2.     __tablename__ = "company"
    3.     id: Mapped[int] = mapped_column(primary_key=True)
    4.     name: Mapped[str]
    5.     managers: Mapped[list[Manager]] = relationship(back_populates="company")
    8. class Employee(Base):
    9.     __tablename__ = "employee"
    10.     id: Mapped[int] = mapped_column(primary_key=True)
    11.     name: Mapped[str]
    12.     type: Mapped[str]
    14.     __mapper_args__ = {
    15.         "polymorphic_identity": "employee",
    16.         "polymorphic_on": "type",
    17.     }
    20. class Manager(Employee):
    21.     __tablename__ = "manager"
    22.     id: Mapped[int] = mapped_column(ForeignKey("employee.id"), primary_key=True)
    23.     manager_name: Mapped[str]
    25.     company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))
    26.     company: Mapped[Company] = relationship(back_populates="managers")
    28.     __mapper_args__ = {
    29.         "polymorphic_identity": "manager",
    30.     }
    33. class Engineer(Employee):
    34.     ...

    Above, the Manager class will have a Manager.company attribute; Company will have a Company.managers attribute that always loads against a join of the employee and manager tables together.

    Loading Joined Inheritance Mappings

    See the sections Writing SELECT statements for Inheritance Mappings and for background on inheritance loading techniques, including configuration of tables to be queried both at mapper configuration time as well as query time.

    Single table inheritance represents all attributes of all subclasses within a single table. A particular subclass that has attributes unique to that class will persist them within columns in the table that are otherwise NULL if the row refers to a different kind of object.

    Querying for a particular subclass in the hierarchy will render as a SELECT against the base table, which will include a WHERE clause that limits rows to those with a particular value or values present in the discriminator column or expression.

    Single table inheritance has the advantage of simplicity compared to joined table inheritance; queries are much more efficient as only one table needs to be involved in order to load objects of every represented class.

    Single-table inheritance configuration looks much like joined-table inheritance, except only the base class specifies __tablename__. A discriminator column is also required on the base table so that classes can be differentiated from each other.

    Even though subclasses share the base table for all of their attributes, when using Declarative, objects may still be specified on subclasses, indicating that the column is to be mapped only to that subclass; the mapped_column will be applied to the same base object:

    1. class Employee(Base):
    2.     __tablename__ = "employee"
    3.     id: Mapped[int] = mapped_column(primary_key=True)
    4.     name: Mapped[str]
    5.     type: Mapped[str]
    7.     __mapper_args__ = {
    8.         "polymorphic_on": "type",
    9.         "polymorphic_identity": "employee",
    10.     }
    13. class Manager(Employee):
    14.     manager_data: Mapped[str] = mapped_column(nullable=True)
    16.     __mapper_args__ = {
    17.         "polymorphic_identity": "manager",
    18.     }
    21. class Engineer(Employee):
    22.     engineer_info: Mapped[str] = mapped_column(nullable=True)
    24.     __mapper_args__ = {
    25.         "polymorphic_identity": "engineer",
    26.     }

    Resolving Column Conflicts with use_existing_column

    Note in the previous section that the manager_name and engineer_info columns are “moved up” to be applied to Employee.__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:

    1. from datetime import datetime
    4. class Employee(Base):
    5.     __tablename__ = "employee"
    6.     id: Mapped[int] = mapped_column(primary_key=True)
    7.     name: Mapped[str]
    8.     type: Mapped[str]
    10.     __mapper_args__ = {
    11.         "polymorphic_on": "type",
    12.         "polymorphic_identity": "employee",
    13.     }
    16. class Engineer(Employee):
    17.     __mapper_args__ = {
    18.         "polymorphic_identity": "engineer",
    19.     }
    20.     start_date: Mapped[datetime] = mapped_column(nullable=True)
    23. class Manager(Employee):
    24.     __mapper_args__ = {
    25.         "polymorphic_identity": "manager",
    26.     }
    27.     start_date: Mapped[datetime] = mapped_column(nullable=True)

    Above, the start_date column declared on both Engineer and Manager will result in an error:

    1. sqlalchemy.exc.ArgumentError: Column 'start_date' on class Manager conflicts
    2. with existing column 'employee.start_date'.  If using Declarative,
    3. consider using the use_existing_column parameter of mapped_column() to
    4. resolve conflicts.

    The above scenario presents an ambiguity to the Declarative mapping system that may be resolved by using the parameter on mapped_column(), which instructs to look on the inheriting superclass present and use the column that’s already mapped, if already present, else to map a new column:

    1. from sqlalchemy import DateTime
    4. class Employee(Base):
    5.     __tablename__ = "employee"
    6.     id: Mapped[int] = mapped_column(primary_key=True)
    7.     name: Mapped[str]
    8.     type: Mapped[str]
    10.     __mapper_args__ = {
    11.         "polymorphic_on": "type",
    12.         "polymorphic_identity": "employee",
    13.     }
    16. class Engineer(Employee):
    17.     __mapper_args__ = {
    18.         "polymorphic_identity": "engineer",
    19.     }
    21.     start_date: Mapped[datetime] = mapped_column(
    22.         nullable=True, use_existing_column=True
    23.     )
    26. class Manager(Employee):
    27.     __mapper_args__ = {
    28.         "polymorphic_identity": "manager",
    29.     }
    31.     start_date: Mapped[datetime] = mapped_column(
    32.         nullable=True, use_existing_column=True
    33.     )

    Above, when Manager is mapped, the start_date column is already present on the Employee class, having been provided by the Engineer mapping already. The mapped_column.use_existing_column parameter indicates to that it should look for the requested Column on the mapped for Employee first, and if present, maintain that existing mapping. If not present, mapped_column() will map the column normally, adding it as one of the columns in the referred towards by the Employee superclass.

    New in version 2.0.0b4: - Added mapped_column.use_existing_column, which provides a 2.0-compatible means of mapping a column on an inheriting subclass conditionally. The previous approach which combines with a lookup on the parent .__table__ continues to function as well, but lacks PEP 484 typing support.

    A similar concept can be used with mixin classes (see ) to define a particular series of columns and/or other mapped attributes from a reusable mixin class:

    1. class Employee(Base):
    2.     __tablename__ = "employee"
    3.     id: Mapped[int] = mapped_column(primary_key=True)
    4.     name: Mapped[str]
    5.     type: Mapped[str]
    7.     __mapper_args__ = {
    8.         "polymorphic_on": type,
    9.         "polymorphic_identity": "employee",
    10.     }
    13. class HasStartDate:
    14.     start_date: Mapped[datetime] = mapped_column(
    15.         nullable=True, use_existing_column=True
    16.     )
    19. class Engineer(HasStartDate, Employee):
    20.     __mapper_args__ = {
    21.         "polymorphic_identity": "engineer",
    22.     }
    25. class Manager(HasStartDate, Employee):
    26.     __mapper_args__ = {
    27.         "polymorphic_identity": "manager",
    28.     }

    Relationships are fully supported with single table inheritance. Configuration is done in the same manner as that of joined inheritance; a foreign key attribute should be on the same class that’s the “foreign” side of the relationship:

    Also, like the case of joined inheritance, we can create relationships that involve a specific subclass. When queried, the SELECT statement will include a WHERE clause that limits the class selection to that subclass or subclasses:

    1. class Company(Base):
    2.     __tablename__ = "company"
    3.     id: Mapped[int] = mapped_column(primary_key=True)
    4.     name: Mapped[str]
    5.     managers: Mapped[list[Manager]] = relationship(back_populates="company")
    8. class Employee(Base):
    10.     name: Mapped[str]
    11.     type: Mapped[str]
    13.     __mapper_args__ = {
    14.         "polymorphic_identity": "employee",
    15.         "polymorphic_on": "type",
    16.     }
    19. class Manager(Employee):
    20.     manager_name: Mapped[str] = mapped_column(nullable=True)
    22.     company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))
    23.     company: Mapped[Company] = relationship(back_populates="managers")
    25.     __mapper_args__ = {
    26.         "polymorphic_identity": "manager",
    27.     }
    30. class Engineer(Employee):
    31.     engineer_info: Mapped[str] = mapped_column(nullable=True)
    33.     __mapper_args__ = {
    34.         "polymorphic_identity": "engineer",
    35.     }

    Above, the Manager class will have a Manager.company attribute; Company will have a Company.managers attribute that always loads against the employee with an additional WHERE clause that limits rows to those with type = 'manager'.

    Building Deeper Hierarchies with polymorphic_abstract

    New in version 2.0.

    When building any kind of inheritance hierarchy, a mapped class may include the Mapper.polymorphic_abstract parameter set to True, which indicates that the class should be mapped normally, however would not expect to be instantiated directly and would not include a . Subclasses may then be declared as subclasses of this mapped class, which themselves can include a Mapper.polymorphic_identity and therefore be used normally. This allows a series of subclasses to be referenced at once by a common base class which is considered to be “abstract” within the hierarchy, both in queries as well as in declarations. This use differs from the use of the __abstract__ attribute with Declarative, which leaves the target class entirely unmapped and thus not usable as a mapped class by itself. may be applied to any class or classes at any level in the hierarchy, including on multiple levels at once.

    As an example, suppose Manager and Principal were both to be classified against a superclass Executive, and Engineer and Sysadmin were classified against a superclass Technologist. Neither Executive or Technologist is ever instantiated, therefore have no Mapper.polymorphic_identity. These classes can be configured using as follows:

    1. class Employee(Base):
    2.     __tablename__ = "employee"
    3.     id: Mapped[int] = mapped_column(primary_key=True)
    4.     name: Mapped[str]
    5.     type: Mapped[str]
    7.     __mapper_args__ = {
    8.         "polymorphic_identity": "employee",
    9.         "polymorphic_on": "type",
    10.     }
    13. class Executive(Employee):
    14.     """An executive of the company"""
    16.     executive_background: Mapped[str] = mapped_column(nullable=True)
    18.     __mapper_args__ = {"polymorphic_abstract": True}
    21. class Technologist(Employee):
    22.     """An employee who works with technology"""
    24.     competencies: Mapped[str] = mapped_column(nullable=True)
    26.     __mapper_args__ = {"polymorphic_abstract": True}
    29. class Manager(Executive):
    30.     """a manager"""
    32.     __mapper_args__ = {"polymorphic_identity": "manager"}
    35. class Principal(Executive):
    36.     """a principal of the company"""
    38.     __mapper_args__ = {"polymorphic_identity": "principal"}
    41. class Engineer(Technologist):
    42.     """an engineer"""
    44.     __mapper_args__ = {"polymorphic_identity": "engineer"}
    47. class SysAdmin(Technologist):
    48.     """a systems administrator"""
    50.     __mapper_args__ = {"polymorphic_identity": "engineer"}

    In the above example, the new classes Technologist and Executive are ordinary mapped classes, and also indicate new columns to be added to the superclass called executive_background and competencies. However, they both lack a setting for Mapper.polymorphic_identity; this is because it’s not expected that Technologist or Executive would ever be instantiated directly; we’d always have one of Manager, Principal, Engineer or SysAdmin. We can however query for Principal and Technologist roles, as well as have them be targets of . The example below demonstrates a SELECT statement for Technologist objects:

    1. session.scalars(select(Technologist)).all()
    2. SELECT employee.id, employee.name, employee.type, employee.competencies
    3. FROM employee
    4. WHERE employee.type IN (?, ?)
    5. [...] ('engineer', 'sysadmin')

    The Technologist and Executive abstract mapped classes may also be made the targets of relationship() mappings, like any other mapped class. We can extend the above example to include Company, with separate collections Company.technologists and Company.principals:

    1. class Company(Base):
    2.     __tablename__ = "company"
    3.     id = Column(Integer, primary_key=True)
    5.     executives: Mapped[List[Executive]] = relationship()
    6.     technologists: Mapped[List[Technologist]] = relationship()
    9. class Employee(Base):
    10.     __tablename__ = "employee"
    11.     id: Mapped[int] = mapped_column(primary_key=True)
    13.     # foreign key to "company.id" is added
    14.     company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))
    16.     # rest of mapping is the same
    17.     name: Mapped[str]
    18.     type: Mapped[str]
    20.     __mapper_args__ = {
    21.         "polymorphic_on": "type",
    22.     }
    25. # Executive, Technologist, Manager, Principal, Engineer, SysAdmin
    26. # classes from previous example would follow here unchanged

    Using the above mapping we can use joins and relationship loading techniques across Company.technologists and Company.executives individually:

    1. session.scalars(
    2.     select(Company)
    3.     .join(Company.technologists)
    4.     .where(Technologist.competency.ilike("%java%"))
    5.     .options(selectinload(Company.executives))
    6. ).all()
    7. SELECT company.id
    8. FROM company JOIN employee ON company.id = employee.company_id AND employee.type IN (?, ?)
    9. WHERE lower(employee.competencies) LIKE lower(?)
    10. [...] ('engineer', 'sysadmin', '%java%')
    12. SELECT employee.company_id AS employee_company_id, employee.id AS employee_id,
    13. employee.name AS employee_name, employee.type AS employee_type,
    14. employee.executive_background AS employee_executive_background
    15. FROM employee
    16. WHERE employee.company_id IN (?) AND employee.type IN (?, ?)
    17. [...] (1, 'manager', 'principal')

    See also

    - Declarative parameter which allows a Declarative class to be completely un-mapped within a hierarchy, while still extending from a mapped superclass.

    Loading Single Inheritance Mappings

    The loading techniques for single-table inheritance are mostly identical to those used for joined-table inheritance, and a high degree of abstraction is provided between these two mapping types such that it is easy to switch between them as well as to intermix them in a single hierarchy (just omit __tablename__ from whichever subclasses are to be single-inheriting). See the sections and SELECT Statements for Single Inheritance Mappings for documentation on inheritance loading techniques, including configuration of classes to be queried both at mapper configuration time as well as query time.

    Concrete inheritance maps each subclass to its own distinct table, each of which contains all columns necessary to produce an instance of that class. A concrete inheritance configuration by default queries non-polymorphically; a query for a particular class will only query that class’ table and only return instances of that class. Polymorphic loading of concrete classes is enabled by configuring within the mapper a special SELECT that typically is produced as a UNION of all the tables.

    Warning

    Concrete table inheritance is much more complicated than joined or single table inheritance, and is much more limited in functionality especially pertaining to using it with relationships, eager loading, and polymorphic loading. When used polymorphically it produces very large queries with UNIONS that won’t perform as well as simple joins. It is strongly advised that if flexibility in relationship loading and polymorphic loading is required, that joined or single table inheritance be used if at all possible. If polymorphic loading isn’t required, then plain non-inheriting mappings can be used if each class refers to its own table completely.

    Whereas joined and single table inheritance are fluent in “polymorphic” loading, it is a more awkward affair in concrete inheritance. For this reason, concrete inheritance is more appropriate when polymorphic loading is not required. Establishing relationships that involve concrete inheritance classes is also more awkward.

    To establish a class as using concrete inheritance, add the Mapper.concrete parameter within the __mapper_args__. This indicates to Declarative as well as the mapping that the superclass table should not be considered as part of the mapping:

    1. class Employee(Base):
    2.     __tablename__ = "employee"
    4.     id = mapped_column(Integer, primary_key=True)
    5.     name = mapped_column(String(50))
    8. class Manager(Employee):
    9.     __tablename__ = "manager"
    11.     id = mapped_column(Integer, primary_key=True)
    12.     name = mapped_column(String(50))
    13.     manager_data = mapped_column(String(50))
    15.     __mapper_args__ = {
    16.         "concrete": True,
    17.     }
    20. class Engineer(Employee):
    21.     __tablename__ = "engineer"
    23.     id = mapped_column(Integer, primary_key=True)
    24.     name = mapped_column(String(50))
    25.     engineer_info = mapped_column(String(50))
    27.     __mapper_args__ = {
    28.         "concrete": True,
    29.     }

    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 by Manager or Engineer for us.

    • while the Engineer and Manager classes are mapped in an inheritance relationship with Employee, they still do not include polymorphic loading. Meaning, if we query for Employee objects, the manager and engineer tables are not queried at all.

    Polymorphic loading with concrete inheritance requires that a specialized SELECT is configured against each base class that should have polymorphic loading. This SELECT needs to be capable of accessing all the mapped tables individually, and is typically a UNION statement that is constructed using a SQLAlchemy helper polymorphic_union().

    As discussed in , mapper inheritance configurations of any type can be configured to load from a special selectable by default using the Mapper.with_polymorphic argument. Current public API requires that this argument is set on a when it is first constructed.

    However, in the case of Declarative, both the mapper and the Table that is mapped are created at once, the moment the mapped class is defined. This means that the argument cannot be provided yet, since the Table objects that correspond to the subclasses haven’t yet been defined.

    Using , we can set up our concrete mapping in almost the same way as we do other forms of inheritance mappings:

    1. from sqlalchemy.ext.declarative import ConcreteBase
    2. from sqlalchemy.orm import DeclarativeBase
    5. class Base(DeclarativeBase):
    6.     pass
    9. class Employee(ConcreteBase, Base):
    10.     __tablename__ = "employee"
    11.     id = mapped_column(Integer, primary_key=True)
    12.     name = mapped_column(String(50))
    14.     __mapper_args__ = {
    15.         "polymorphic_identity": "employee",
    16.         "concrete": True,
    17.     }
    20. class Manager(Employee):
    21.     __tablename__ = "manager"
    22.     id = mapped_column(Integer, primary_key=True)
    23.     name = mapped_column(String(50))
    24.     manager_data = mapped_column(String(40))
    26.     __mapper_args__ = {
    27.         "polymorphic_identity": "manager",
    28.         "concrete": True,
    29.     }
    32. class Engineer(Employee):
    33.     __tablename__ = "engineer"
    34.     id = mapped_column(Integer, primary_key=True)
    35.     name = mapped_column(String(50))
    36.     engineer_info = mapped_column(String(40))
    38.     __mapper_args__ = {
    39.         "polymorphic_identity": "engineer",
    40.         "concrete": True,
    41.     }

    Above, Declarative sets up the polymorphic selectable for the Employee class at mapper “initialization” time; this is the late-configuration step for mappers that resolves other dependent mappers. The ConcreteBase helper uses the function to create a UNION of all concrete-mapped tables after all the other classes are set up, and then configures this statement with the already existing base-class mapper.

    Upon select, the polymorphic union produces a query like this:

    1. session.scalars(select(Employee)).all()
    2. SELECT
    3.     pjoin.id,
    4.     pjoin.name,
    5.     pjoin.type,
    6.     pjoin.manager_data,
    7.     pjoin.engineer_info
    8. FROM (
    9.     SELECT
    10.         employee.id AS id,
    11.         employee.name AS name,
    12.         CAST(NULL AS VARCHAR(40)) AS manager_data,
    13.         CAST(NULL AS VARCHAR(40)) AS engineer_info,
    14.         'employee' AS type
    15.     FROM employee
    16.     UNION ALL
    17.     SELECT
    18.         manager.id AS id,
    19.         manager.name AS name,
    20.         manager.manager_data AS manager_data,
    21.         CAST(NULL AS VARCHAR(40)) AS engineer_info,
    22.         'manager' AS type
    23.     FROM manager
    24.     UNION ALL
    25.     SELECT
    26.         engineer.id AS id,
    27.         engineer.name AS name,
    28.         CAST(NULL AS VARCHAR(40)) AS manager_data,
    29.         engineer.engineer_info AS engineer_info,
    30.     FROM engineer

    The above UNION query needs to manufacture “NULL” columns for each subtable in order to accommodate for those columns that aren’t members of that particular subclass.

    See also

    ConcreteBase

    Abstract Concrete Classes

    The concrete mappings illustrated thus far show both the subclasses as well as the base class mapped to individual tables. In the concrete inheritance use case, it is common that the base class is not represented within the database, only the subclasses. In other words, the base class is “abstract”.

    Normally, when one would like to map two different subclasses to individual tables, and leave the base class unmapped, this can be achieved very easily. When using Declarative, just declare the base class with the __abstract__ indicator:

    Above, we are not actually making use of SQLAlchemy’s inheritance mapping facilities; we can load and persist instances of Manager and Engineer normally. The situation changes however when we need to query polymorphically, that is, we’d like to emit select(Employee) and get back a collection of Manager and Engineer instances. This brings us back into the domain of concrete inheritance, and we must build a special mapper against Employee in order to achieve this.

    To modify our concrete inheritance example to illustrate an “abstract” base that is capable of polymorphic loading, we will have only an engineer and a manager table and no employee table, however the Employee mapper will be mapped directly to the “polymorphic union”, rather than specifying it locally to the Mapper.with_polymorphic parameter.

    To help with this, Declarative offers a variant of the class called AbstractConcreteBase which achieves this automatically:

    1. from sqlalchemy.ext.declarative import AbstractConcreteBase
    2. from sqlalchemy.orm import DeclarativeBase
    5. class Base(DeclarativeBase):
    6.     pass
    9. class Employee(AbstractConcreteBase, Base):
    10.     strict_attrs = True
    12.     name = mapped_column(String(50))
    15. class Manager(Employee):
    16.     __tablename__ = "manager"
    17.     id = mapped_column(Integer, primary_key=True)
    18.     name = mapped_column(String(50))
    19.     manager_data = mapped_column(String(40))
    21.     __mapper_args__ = {
    22.         "polymorphic_identity": "manager",
    23.         "concrete": True,
    24.     }
    27. class Engineer(Employee):
    28.     __tablename__ = "engineer"
    29.     id = mapped_column(Integer, primary_key=True)
    30.     name = mapped_column(String(50))
    31.     engineer_info = mapped_column(String(40))
    33.     __mapper_args__ = {
    34.         "polymorphic_identity": "engineer",
    35.         "concrete": True,
    36.     }
    39. Base.registry.configure()

    Above, the method is invoked, which will trigger the Employee class to be actually mapped; before the configuration step, the class has no mapping as the sub-tables which it will query from have not yet been defined. This process is more complex than that of ConcreteBase, in that the entire mapping of the base class must be delayed until all the subclasses have been declared. With a mapping like the above, only instances of Manager and Engineer may be persisted; querying against the Employee class will always produce Manager and Engineer objects.

    Using the above mapping, queries can be produced in terms of the Employee class and any attributes that are locally declared upon it, such as the Employee.name:

    1. >>> stmt = select(Employee).where(Employee.name == "n1")
    2. >>> print(stmt)
    3. SELECT pjoin.id, pjoin.name, pjoin.type, pjoin.manager_data, pjoin.engineer_info
    4. FROM (
    5.   SELECT engineer.id AS id, engineer.name AS name, engineer.engineer_info AS engineer_info,
    6.   CAST(NULL AS VARCHAR(40)) AS manager_data, 'engineer' AS type
    7.   FROM engineer
    8.   UNION ALL
    9.   SELECT manager.id AS id, manager.name AS name, CAST(NULL AS VARCHAR(40)) AS engineer_info,
    10.   manager.manager_data AS manager_data, 'manager' AS type
    11.   FROM manager
    12. ) AS pjoin
    13. WHERE pjoin.name = :name_1

    The parameter indicates that the Employee class should directly map only those attributes which are local to the Employee class, in this case the Employee.name attribute. Other attributes such as Manager.manager_data and Engineer.engineer_info are present only on their corresponding subclass. When AbstractConcreteBase.strict_attrs is not set, then all subclass attributes such as Manager.manager_data and Engineer.engineer_info get mapped onto the base Employee class. This is a legacy mode of use which may be more convenient for querying but has the effect that all subclasses share the full set of attributes for the whole hierarchy; in the above example, not using would have the effect of generating non-useful Engineer.manager_name and Manager.engineer_info attributes.

    New in version 2.0: Added AbstractConcreteBase.strict_attrs parameter to which produces a cleaner mapping; the default is False to allow legacy mappings to continue working as they did in 1.x versions.

    See also

    AbstractConcreteBase

    Classical and Semi-Classical Concrete Polymorphic Configuration

    The Declarative configurations illustrated with ConcreteBase and are equivalent to two other forms of configuration that make use of polymorphic_union() explicitly. These configurational forms make use of the object explicitly so that the “polymorphic union” can be created first, then applied to the mappings. These are illustrated here to clarify the role of the polymorphic_union() function in terms of mapping.

    A semi-classical mapping for example makes use of Declarative, but establishes the objects separately:

    1. metadata_obj = Base.metadata
    3. employees_table = Table(
    4.     "employee",
    5.     metadata_obj,
    6.     Column("id", Integer, primary_key=True),
    7.     Column("name", String(50)),
    8. )
    10. managers_table = Table(
    11.     "manager",
    12.     metadata_obj,
    13.     Column("id", Integer, primary_key=True),
    14.     Column("name", String(50)),
    15.     Column("manager_data", String(50)),
    16. )
    18. engineers_table = Table(
    19.     "engineer",
    20.     metadata_obj,
    21.     Column("id", Integer, primary_key=True),
    22.     Column("name", String(50)),
    23.     Column("engineer_info", String(50)),
    24. )

    Next, the UNION is produced using polymorphic_union():

    1. from sqlalchemy.orm import polymorphic_union
    3. pjoin = polymorphic_union(
    4.     {
    5.         "employee": employees_table,
    6.         "manager": managers_table,
    7.         "engineer": engineers_table,
    8.     },
    9.     "type",
    10.     "pjoin",
    11. )

    With the above objects, the mappings can be produced using “semi-classical” style, where we use Declarative in conjunction with the __table__ argument; our polymorphic union above is passed via __mapper_args__ to the Mapper.with_polymorphic parameter:

    1. class Employee(Base):
    2.     __table__ = employee_table
    3.     __mapper_args__ = {
    4.         "polymorphic_on": pjoin.c.type,
    5.         "with_polymorphic": ("*", pjoin),
    6.         "polymorphic_identity": "employee",
    7.     }
    10. class Engineer(Employee):
    11.     __table__ = engineer_table
    12.     __mapper_args__ = {
    13.         "polymorphic_identity": "engineer",
    14.         "concrete": True,
    15.     }
    18. class Manager(Employee):
    19.     __table__ = manager_table
    20.     __mapper_args__ = {
    21.         "polymorphic_identity": "manager",
    22.         "concrete": True,
    23.     }

    Alternatively, the same objects can be used in fully “classical” style, without using Declarative at all. A constructor similar to that supplied by Declarative is illustrated:

    1. class Employee:
    2.     def __init__(self, **kw):
    3.         for k in kw:
    4.             setattr(self, k, kw[k])
    7. class Manager(Employee):
    8.     pass
    11. class Engineer(Employee):
    12.     pass
    15. employee_mapper = mapper_registry.map_imperatively(
    16.     Employee,
    17.     pjoin,
    18.     with_polymorphic=("*", pjoin),
    19.     polymorphic_on=pjoin.c.type,
    20. )
    21. manager_mapper = mapper_registry.map_imperatively(
    22.     Manager,
    23.     managers_table,
    24.     inherits=employee_mapper,
    25.     concrete=True,
    26.     polymorphic_identity="manager",
    27. )
    28. engineer_mapper = mapper_registry.map_imperatively(
    29.     Engineer,
    30.     engineers_table,
    31.     inherits=employee_mapper,
    32.     concrete=True,
    33.     polymorphic_identity="engineer",
    34. )

    The “abstract” example can also be mapped using “semi-classical” or “classical” style. The difference is that instead of applying the “polymorphic union” to the Mapper.with_polymorphic parameter, we apply it directly as the mapped selectable on our basemost mapper. The semi-classical mapping is illustrated below:

    1. from sqlalchemy.orm import polymorphic_union
    3. pjoin = polymorphic_union(
    4.     {
    5.         "manager": managers_table,
    6.         "engineer": engineers_table,
    7.     },
    8.     "type",
    9.     "pjoin",
    10. )
    13. class Employee(Base):
    14.     __table__ = pjoin
    15.     __mapper_args__ = {
    16.         "polymorphic_on": pjoin.c.type,
    17.         "with_polymorphic": "*",
    18.         "polymorphic_identity": "employee",
    19.     }
    22. class Engineer(Employee):
    23.     __table__ = engineer_table
    24.     __mapper_args__ = {
    25.         "polymorphic_identity": "engineer",
    26.         "concrete": True,
    27.     }
    30. class Manager(Employee):
    31.     __table__ = manager_table
    32.     __mapper_args__ = {
    33.         "polymorphic_identity": "manager",
    34.         "concrete": True,
    35.     }

    Above, we use in the same manner as before, except that we omit the employee table.

    See also

    Imperative Mapping - background information on imperative, or “classical” mappings

    In a concrete inheritance scenario, mapping relationships is challenging since the distinct classes do not share a table. If the relationships only involve specific classes, such as a relationship between Company in our previous examples and Manager, special steps aren’t needed as these are just two related tables.

    However, if Company is to have a one-to-many relationship to Employee, indicating that the collection may include both Engineer and Manager objects, that implies that Employee must have polymorphic loading capabilities and also that each table to be related must have a foreign key back to the company table. An example of such a configuration is as follows:

    1. from sqlalchemy.ext.declarative import ConcreteBase
    4. class Company(Base):
    5.     __tablename__ = "company"
    6.     id = mapped_column(Integer, primary_key=True)
    7.     name = mapped_column(String(50))
    8.     employees = relationship("Employee")
    11. class Employee(ConcreteBase, Base):
    12.     __tablename__ = "employee"
    13.     id = mapped_column(Integer, primary_key=True)
    14.     name = mapped_column(String(50))
    15.     company_id = mapped_column(ForeignKey("company.id"))
    17.     __mapper_args__ = {
    18.         "polymorphic_identity": "employee",
    19.         "concrete": True,
    20.     }
    23. class Manager(Employee):
    24.     __tablename__ = "manager"
    25.     id = mapped_column(Integer, primary_key=True)
    26.     name = mapped_column(String(50))
    27.     manager_data = mapped_column(String(40))
    28.     company_id = mapped_column(ForeignKey("company.id"))
    30.     __mapper_args__ = {
    31.         "polymorphic_identity": "manager",
    32.         "concrete": True,
    33.     }
    36. class Engineer(Employee):
    37.     __tablename__ = "engineer"
    38.     id = mapped_column(Integer, primary_key=True)
    39.     name = mapped_column(String(50))
    40.     engineer_info = mapped_column(String(40))
    41.     company_id = mapped_column(ForeignKey("company.id"))
    43.     __mapper_args__ = {
    44.         "polymorphic_identity": "engineer",
    45.     }

    The next complexity with concrete inheritance and relationships involves when we’d like one or all of Employee, Manager and Engineer to themselves refer back to Company. For this case, SQLAlchemy has special behavior in that a relationship() placed on Employee which links to Company does not work against the Manager and Engineer classes, when exercised at the instance level. Instead, a distinct must be applied to each class. In order to achieve bi-directional behavior in terms of three separate relationships which serve as the opposite of Company.employees, the relationship.back_populates parameter is used between each of the relationships:

    Loading Concrete Inheritance Mappings

    The options for loading with concrete inheritance are limited; generally, if polymorphic loading is configured on the mapper using one of the declarative concrete mixins, it can’t be modified at query time in current SQLAlchemy versions. Normally, the with_polymorphic() function would be able to override the style of loading used by concrete, however due to current limitations this is not yet supported.