映射类继承层次结构¶

Mapping Class Inheritance Hierarchies

中文

SQLAlchemy 支持三种形式的继承：

单表继承 – 多种类型的类由一个表表示；
具体表继承 – 每种类型的类由独立的表表示；
连接表继承 – 类层次结构被拆分为依赖表。每个类由其自己的表表示，该表仅包括该类本地的那些属性。

最常见的继承形式是单表和连接表，而具体继承在配置上带来更多的挑战。

当映射器在继承关系中配置时，SQLAlchemy 能够多态地加载元素，这意味着单个查询可以返回多种类型的对象。

参见

为继承映射编写 SELECT 语句 - 在 ORM 查询指南中

继承映射配方 - 完整的连接、单表和具体继承示例

英文

SQLAlchemy supports three forms of inheritance:

single table inheritance – several types of classes are represented by a single table;
concrete table inheritance – each type of class is represented by independent tables;
joined table inheritance – 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.

参见

为继承映射编写 SELECT 语句 - in the ORM 查询指南

继承映射配方 - complete examples of joined, single and concrete inheritance

连接表继承¶

Joined Table Inheritance

中文

在连接表继承中，类层次结构中的每个类由一个独特的表表示。查询层次结构中的特定子类将呈现为其继承路径中所有表的 SQL JOIN。如果查询的类是基类，则查询基表，同时可以选择包括其他表或允许子表特定属性稍后加载。

在所有情况下，确定给定行要实例化的最终类是通过在基类上定义的 discriminator 列或 SQL 表达式，该列或表达式将产生一个标量值，该值与特定子类相关联。

在连接继承层次结构中，基类配置了指示多态鉴别列的附加参数，以及可选的基类本身的多态标识符:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


class Base(DeclarativeBase):
    pass


class Employee(Base):
    __tablename__ = "employee"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    type: Mapped[str]

    __mapper_args__ = {
        "polymorphic_identity": "employee",
        "polymorphic_on": "type",
    }

    def __repr__(self):
        return f"{self.__class__.__name__}({self.name!r})"

在上面的示例中，鉴别器是 type 列，它是使用 Mapper.polymorphic_on 参数配置的。此参数接受列导向的表达式，可以是要使用的映射属性的字符串名称，也可以是列表达式对象，例如 Column 或 mapped_column() 构造。

鉴别列将存储一个值，该值指示行中表示的对象类型。该列可以是任何数据类型，但字符串和整数是最常见的。为数据库中特定行应用此列的实际数据值是使用 Mapper.polymorphic_identity 参数指定的，如下所述。

虽然多态鉴别表达式不是严格必要的，但如果需要多态加载，则是必需的。在基表上建立列是实现这一目标的最简单方法，但是非常复杂的继承映射可以使用 SQL 表达式，例如 CASE 表达式，作为多态鉴别器。

备注

目前，整个继承层次结构只能配置一个鉴别列或 SQL 表达式，通常在层次结构中的最基类上。不支持“级联”多态鉴别表达式。

接下来我们定义 Employee 的 Engineer 和 Manager 子类。每个子类包含表示其代表的子类特有属性的列。每个表还必须包含一个主键列（或列），以及到父表的外键引用:

class Engineer(Employee):
    __tablename__ = "engineer"
    id: Mapped[int] = mapped_column(ForeignKey("employee.id"), primary_key=True)
    engineer_name: Mapped[str]

    __mapper_args__ = {
        "polymorphic_identity": "engineer",
    }


class Manager(Employee):
    __tablename__ = "manager"
    id: Mapped[int] = mapped_column(ForeignKey("employee.id"), primary_key=True)
    manager_name: Mapped[str]

    __mapper_args__ = {
        "polymorphic_identity": "manager",
    }

在上面的示例中，每个映射在其映射参数中指定 Mapper.polymorphic_identity 参数。此值填充由基映射器中建立的 Mapper.polymorphic_on 参数指定的列。Mapper.polymorphic_identity 参数在整个层次结构中的每个映射类中应该是唯一的，并且每个映射类只能有一个“标识”；如上所述，不支持某些子类引入第二个标识的“级联”标识。

ORM 使用 Mapper.polymorphic_identity 设置的值来确定在多态加载行时一行属于哪个类。在上面的示例中，表示 Employee 的每一行将在其 type 列中具有值 'employee'；同样，每个 Engineer 将获得值 'engineer'，每个 Manager 将获得值 'manager'。无论继承映射是像连接表继承那样使用不同的连接表，还是像单表继承那样使用一个表，都希望此值被持久化并在查询时可供 ORM 使用。Mapper.polymorphic_identity 参数也适用于具体表继承，但实际上不会持久化；有关详细信息，请参阅具体表继承后面的部分。

在多态设置中，最常见的是在主键本身的同一列或列上建立外键约束，但这不是必需的；可以使与主键不同的列通过外键引用父级。从基表到子类的 JOIN 的构造方式也是直接可定制的，但这很少是必要的。

完成连接继承映射后，针对 Employee 的查询将返回 Employee、Engineer 和 Manager 对象的组合。新保存的 Engineer、Manager 和 Employee 对象将自动使用正确的“鉴别”值填充 employee.type 列，在这种情况下为 "engineer"、"manager" 或 "employee"，视情况而定。

英文

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 discriminator 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:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


class Base(DeclarativeBase):
    pass


class Employee(Base):
    __tablename__ = "employee"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    type: Mapped[str]

    __mapper_args__ = {
        "polymorphic_identity": "employee",
        "polymorphic_on": "type",
    }

    def __repr__(self):
        return f"{self.__class__.__name__}({self.name!r})"

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 Column 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 Mapper.polymorphic_identity 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.

备注

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:

class Engineer(Employee):
    __tablename__ = "engineer"
    id: Mapped[int] = mapped_column(ForeignKey("employee.id"), primary_key=True)
    engineer_name: Mapped[str]

    __mapper_args__ = {
        "polymorphic_identity": "engineer",
    }


class Manager(Employee):
    __tablename__ = "manager"
    id: Mapped[int] = mapped_column(ForeignKey("employee.id"), primary_key=True)
    manager_name: Mapped[str]

    __mapper_args__ = {
        "polymorphic_identity": "manager",
    }

In the above example, each mapping specifies the Mapper.polymorphic_identity parameter within its mapper arguments. This value populates the column designated by the Mapper.polymorphic_on 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 Mapper.polymorphic_identity 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 column; 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.

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 with Joined Inheritance

中文

完全支持连接表继承的关系。涉及连接继承类的关系应针对与外键约束对应的层次结构中的类；如下所示，由于 employee 表有一个指向 company 表的外键约束，因此关系在 Company 和 Employee 之间设置:

from __future__ import annotations

from sqlalchemy.orm import relationship


class Company(Base):
    __tablename__ = "company"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    employees: Mapped[List[Employee]] = relationship(back_populates="company")


class Employee(Base):
    __tablename__ = "employee"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    type: Mapped[str]
    company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))
    company: Mapped[Company] = relationship(back_populates="employees")

    __mapper_args__ = {
        "polymorphic_identity": "employee",
        "polymorphic_on": "type",
    }


class Manager(Employee): ...


class Engineer(Employee): ...

如果外键约束在对应于子类的表上，则关系应针对该子类。在以下示例中，从 manager 到 company 有一个外键约束，因此关系在 Manager 和 Company 类之间建立:

class Company(Base):
    __tablename__ = "company"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    managers: Mapped[List[Manager]] = relationship(back_populates="company")


class Employee(Base):
    __tablename__ = "employee"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    type: Mapped[str]

    __mapper_args__ = {
        "polymorphic_identity": "employee",
        "polymorphic_on": "type",
    }


class Manager(Employee):
    __tablename__ = "manager"
    id: Mapped[int] = mapped_column(ForeignKey("employee.id"), primary_key=True)
    manager_name: Mapped[str]

    company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))
    company: Mapped[Company] = relationship(back_populates="managers")

    __mapper_args__ = {
        "polymorphic_identity": "manager",
    }


class Engineer(Employee): ...

在上面， Manager 类将具有 Manager.company 属性；Company 将具有 Company.managers 属性，该属性始终加载针对 employee 和 manager 表的连接。

英文

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:

from __future__ import annotations

from sqlalchemy.orm import relationship


class Company(Base):
    __tablename__ = "company"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    employees: Mapped[List[Employee]] = relationship(back_populates="company")


class Employee(Base):
    __tablename__ = "employee"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    type: Mapped[str]
    company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))
    company: Mapped[Company] = relationship(back_populates="employees")

    __mapper_args__ = {
        "polymorphic_identity": "employee",
        "polymorphic_on": "type",
    }


class Manager(Employee): ...


class Engineer(Employee): ...

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:

class Company(Base):
    __tablename__ = "company"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    managers: Mapped[List[Manager]] = relationship(back_populates="company")


class Employee(Base):
    __tablename__ = "employee"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    type: Mapped[str]

    __mapper_args__ = {
        "polymorphic_identity": "employee",
        "polymorphic_on": "type",
    }


class Manager(Employee):
    __tablename__ = "manager"
    id: Mapped[int] = mapped_column(ForeignKey("employee.id"), primary_key=True)
    manager_name: Mapped[str]

    company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))
    company: Mapped[Company] = relationship(back_populates="managers")

    __mapper_args__ = {
        "polymorphic_identity": "manager",
    }


class Engineer(Employee): ...

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

中文

有关继承加载技术的背景知识，请参阅部分为继承映射编写 SELECT 语句，包括在映射器配置时和查询时要查询的表的配置。

英文

See the section 为继承映射编写 SELECT 语句 for background on inheritance loading techniques, including configuration of tables to be queried both at mapper configuration time as well as query time.

映射类继承层次结构¶

连接表继承¶

具有连接继承的关系¶

加载连接继承映射¶

单表继承¶

使用 `use_existing_column` 解决列冲突¶

具有单表继承的关系¶

使用 `polymorphic_abstract` 构建更深层次的层次结构¶

加载单继承映射¶

具体表继承¶

具体多态加载配置¶

抽象具体类¶

经典和半经典具体多态配置¶

具有具体继承的关系¶

加载具体继承映射¶

映射类继承层次结构¶

连接表继承¶

具有连接继承的关系¶

加载连接继承映射¶

单表继承¶

使用 use_existing_column 解决列冲突¶

具有单表继承的关系¶

使用 polymorphic_abstract 构建更深层次的层次结构¶

加载单继承映射¶

具体表继承¶

具体多态加载配置¶

抽象具体类¶

经典和半经典具体多态配置¶

具有具体继承的关系¶

加载具体继承映射¶

使用 `use_existing_column` 解决列冲突¶

使用 `polymorphic_abstract` 构建更深层次的层次结构¶