SqlAlchemy 2.0 中文文档（十六）（5）-阿里云开发者社区

SqlAlchemy 2.0 中文文档（十六）（4）https://developer.aliyun.com/article/1562945

连接到特定子类型或 with_polymorphic()实体

由于with_polymorphic()实体是aliased()的一个特殊情况，为了将多态实体视为连接的目标，特别是在使用relationship()构造作为 ON 子句时，我们使用与常规别名相同的技术，如 Using Relationship to join between aliased targets 中详细描述的，最简洁的方法是使用PropComparator.of_type()。在下面的示例中，我们说明了从父Company实体沿着一对多关系Company.employees进行连接，该关系在 setup 中配置为链接到Employee对象，使用with_polymorphic()实体作为目标：

>>> employee_plus_engineer = with_polymorphic(Employee, [Engineer])
>>> stmt = (
...     select(Company.name, employee_plus_engineer.name)
...     .join(Company.employees.of_type(employee_plus_engineer))
...     .where(
...         or_(
...             employee_plus_engineer.name == "SpongeBob",
...             employee_plus_engineer.Engineer.engineer_info
...             == "Senior Customer Engagement Engineer",
...         )
...     )
... )
>>> for company_name, emp_name in session.execute(stmt):
...     print(f"{company_name} {emp_name}")
SELECT  company.name,  employee.name  AS  name_1
FROM  company  JOIN  (employee  LEFT  OUTER  JOIN  engineer  ON  employee.id  =  engineer.id)  ON  company.id  =  employee.company_id
WHERE  employee.name  =  ?  OR  engineer.engineer_info  =  ?
[...]  ('SpongeBob',  'Senior Customer Engagement Engineer')
Krusty Krab SpongeBob
Krusty Krab Squidward

更直接地，PropComparator.of_type() 也用于任何类型的继承映射，以限制 relationship() 中的连接到 relationship() 的特定子类型。上述查询可以严格按照 Engineer 目标编写如下：

>>> stmt = (
...     select(Company.name, Engineer.name)
...     .join(Company.employees.of_type(Engineer))
...     .where(
...         or_(
...             Engineer.name == "SpongeBob",
...             Engineer.engineer_info == "Senior Customer Engagement Engineer",
...         )
...     )
... )
>>> for company_name, emp_name in session.execute(stmt):
...     print(f"{company_name} {emp_name}")
SELECT  company.name,  employee.name  AS  name_1
FROM  company  JOIN  (employee  JOIN  engineer  ON  employee.id  =  engineer.id)  ON  company.id  =  employee.company_id
WHERE  employee.name  =  ?  OR  engineer.engineer_info  =  ?
[...]  ('SpongeBob',  'Senior Customer Engagement Engineer')
Krusty Krab SpongeBob
Krusty Krab Squidward

如上所示，直接加入到 Engineer 目标，而不是 with_polymorphic(Employee, [Engineer]) 的 “多态可选择项”，具有使用内连接而不是左外连接的有用特性，从 SQL 优化器的角度来看，通常更具性能。

多态子类型的急加载

使用 PropComparator.of_type() 方法的示例见前一节中的 Select.join() 方法，同样可以等效地应用于关系加载器选项，例如 selectinload() 和 joinedload()。

作为基本示例，如果我们希望加载 Company 对象，并且另外急加载 Company.employees 的所有元素，使用 with_polymorphic() 构造针对完整层次结构，我们可以编写如下：

>>> all_employees = with_polymorphic(Employee, "*")
>>> stmt = select(Company).options(selectinload(Company.employees.of_type(all_employees)))
>>> for company in session.scalars(stmt):
...     print(f"company: {company.name}")
...     print(f"employees: {company.employees}")
SELECT  company.id,  company.name
FROM  company
[...]  ()
SELECT  employee.company_id  AS  employee_company_id,  employee.id  AS  employee_id,
employee.name  AS  employee_name,  employee.type  AS  employee_type,  manager.id  AS  manager_id,
manager.manager_name  AS  manager_manager_name,  engineer.id  AS  engineer_id,
engineer.engineer_info  AS  engineer_engineer_info
FROM  employee
LEFT  OUTER  JOIN  manager  ON  employee.id  =  manager.id
LEFT  OUTER  JOIN  engineer  ON  employee.id  =  engineer.id
WHERE  employee.company_id  IN  (?)
[...]  (1,)
company:  Krusty  Krab
employees:  [Manager('Mr. Krabs'),  Engineer('SpongeBob'),  Engineer('Squidward')]

上述查询可直接与前一节中演示的 selectin_polymorphic() 版本进行比较将 selectin_polymorphic() 应用于现有急加载。

另见

将 selectin_polymorphic() 应用于现有急加载 - 演示了与上述相同的等效示例，使用 selectin_polymorphic() 替代 ### 多态子类型的急加载

使用PropComparator.of_type()方法，如前一节中的Select.join()方法所示，也可以等效地应用于关系加载器选项，例如selectinload()和joinedload()。

作为基本示例，如果我们希望加载Company对象，并使用with_polymorphic()构造针对完整层次结构的，同时急切地加载Company.employees的所有元素，我们可以写成：

>>> all_employees = with_polymorphic(Employee, "*")
>>> stmt = select(Company).options(selectinload(Company.employees.of_type(all_employees)))
>>> for company in session.scalars(stmt):
...     print(f"company: {company.name}")
...     print(f"employees: {company.employees}")
SELECT  company.id,  company.name
FROM  company
[...]  ()
SELECT  employee.company_id  AS  employee_company_id,  employee.id  AS  employee_id,
employee.name  AS  employee_name,  employee.type  AS  employee_type,  manager.id  AS  manager_id,
manager.manager_name  AS  manager_manager_name,  engineer.id  AS  engineer_id,
engineer.engineer_info  AS  engineer_engineer_info
FROM  employee
LEFT  OUTER  JOIN  manager  ON  employee.id  =  manager.id
LEFT  OUTER  JOIN  engineer  ON  employee.id  =  engineer.id
WHERE  employee.company_id  IN  (?)
[...]  (1,)
company:  Krusty  Krab
employees:  [Manager('Mr. Krabs'),  Engineer('SpongeBob'),  Engineer('Squidward')]

上述查询可以直接与前一节中将 selectin_polymorphic()应用于现有急切加载中所示的selectin_polymorphic()版本进行比较。

另请参阅

将 selectin_polymorphic()应用于现有急切加载 - 演示了与上述相同的例子，但使用了selectin_polymorphic()代替

单一继承映射的 SELECT 语句

单一表继承设置

本节讨论单一表继承，描述在单一表继承中使用单个表来表示层次结构中的多个类。

查看此部分的 ORM 设置。

与连接继承映射相比，为单一继承映射构造 SELECT 语句通常更简单，因为对于全单一继承层次结构，只有一个表。

无论继承层次结构是全单一继承还是具有混合连接和单一继承，单一继承的 SELECT 语句通过使用附加的 WHERE 条件限制 SELECT 语句来区分对基类和子类的查询。

例如，针对Employee的单一继承示例映射的查询将使用表的简单 SELECT 来加载Manager、Engineer和Employee类型的对象：

>>> stmt = select(Employee).order_by(Employee.id)
>>> for obj in session.scalars(stmt):
...     print(f"{obj}")
BEGIN  (implicit)
SELECT  employee.id,  employee.name,  employee.type
FROM  employee  ORDER  BY  employee.id
[...]  ()
Manager('Mr. Krabs')
Engineer('SpongeBob')
Engineer('Squidward')

当为特定子类发出加载时，将向 SELECT 中添加附加条件以限制行，例如在下面执行针对Engineer实体的 SELECT：

>>> stmt = select(Engineer).order_by(Engineer.id)
>>> objects = session.scalars(stmt).all()
SELECT  employee.id,  employee.name,  employee.type,  employee.engineer_info
FROM  employee
WHERE  employee.type  IN  (?)  ORDER  BY  employee.id
[...]  ('engineer',)
>>> for obj in objects:
...     print(f"{obj}")
Engineer('SpongeBob')
Engineer('Squidward')

优化单一继承的属性加载

单继承映射关于如何 SELECT 子类上的属性的默认行为类似于连接继承的行为，即子类特定的属性默认情况下仍然会发出第二个 SELECT。在下面的示例中，加载了类型为 Manager 的单个 Employee，但是由于请求的类是 Employee，所以 Manager.manager_name 属性默认情况下不会存在，并且在访问时会发出额外的 SELECT：

>>> mr_krabs = session.scalars(select(Employee).where(Employee.name == "Mr. Krabs")).one()
BEGIN  (implicit)
SELECT  employee.id,  employee.name,  employee.type
FROM  employee
WHERE  employee.name  =  ?
[...]  ('Mr. Krabs',)
>>> mr_krabs.manager_name
SELECT  employee.manager_name  AS  employee_manager_name
FROM  employee
WHERE  employee.id  =  ?  AND  employee.type  IN  (?)
[...]  (1,  'manager')
'Eugene H. Krabs'

要改变这种行为，对于单继承，与连接继承加载中使用的相同的一般概念也适用于急切地加载这些额外属性，包括使用 selectin_polymorphic() 选项以及 with_polymorphic() 选项，后者只是简单地包含了额外的列，并且从 SQL 视角来看对于单继承映射更为高效：

>>> employees = with_polymorphic(Employee, "*")
>>> stmt = select(employees).order_by(employees.id)
>>> objects = session.scalars(stmt).all()
BEGIN  (implicit)
SELECT  employee.id,  employee.name,  employee.type,
employee.manager_name,  employee.engineer_info
FROM  employee  ORDER  BY  employee.id
[...]  ()
>>> for obj in objects:
...     print(f"{obj}")
Manager('Mr. Krabs')
Engineer('SpongeBob')
Engineer('Squidward')
>>> objects[0].manager_name
'Eugene H. Krabs'

由于加载单继承子类映射的开销通常很小，因此建议单继承映射在那些预计其特定子类属性加载是常见的子类中包含 Mapper.polymorphic_load 参数，并将其设置为 "inline"。下面是一个修改后的示例，说明了这个设置的一个示例：

>>> class Base(DeclarativeBase):
...     pass
>>> class Employee(Base):
...     __tablename__ = "employee"
...     id: Mapped[int] = mapped_column(primary_key=True)
...     name: Mapped[str]
...     type: Mapped[str]
...
...     def __repr__(self):
...         return f"{self.__class__.__name__}({self.name!r})"
...
...     __mapper_args__ = {
...         "polymorphic_identity": "employee",
...         "polymorphic_on": "type",
...     }
>>> class Manager(Employee):
...     manager_name: Mapped[str] = mapped_column(nullable=True)
...     __mapper_args__ = {
...         "polymorphic_identity": "manager",
...         "polymorphic_load": "inline",
...     }
>>> class Engineer(Employee):
...     engineer_info: Mapped[str] = mapped_column(nullable=True)
...     __mapper_args__ = {
...         "polymorphic_identity": "engineer",
...         "polymorphic_load": "inline",
...     }

有了上面的映射，Manager 和 Engineer 类将自动在针对 Employee 实体的 SELECT 语句中包含它们的列：

>>> print(select(Employee))
SELECT  employee.id,  employee.name,  employee.type,
employee.manager_name,  employee.engineer_info
FROM  employee

优化单继承属性加载

>>> mr_krabs = session.scalars(select(Employee).where(Employee.name == "Mr. Krabs")).one()
BEGIN  (implicit)
SELECT  employee.id,  employee.name,  employee.type
FROM  employee
WHERE  employee.name  =  ?
[...]  ('Mr. Krabs',)
>>> mr_krabs.manager_name
SELECT  employee.manager_name  AS  employee_manager_name
FROM  employee
WHERE  employee.id  =  ?  AND  employee.type  IN  (?)
[...]  (1,  'manager')
'Eugene H. Krabs'

>>> employees = with_polymorphic(Employee, "*")
>>> stmt = select(employees).order_by(employees.id)
>>> objects = session.scalars(stmt).all()
BEGIN  (implicit)
SELECT  employee.id,  employee.name,  employee.type,
employee.manager_name,  employee.engineer_info
FROM  employee  ORDER  BY  employee.id
[...]  ()
>>> for obj in objects:
...     print(f"{obj}")
Manager('Mr. Krabs')
Engineer('SpongeBob')
Engineer('Squidward')
>>> objects[0].manager_name
'Eugene H. Krabs'

由于加载单继承子类映射的开销通常很小，因此建议在那些预计其特定子类属性的加载是常见的单继承映射中，将Mapper.polymorphic_load参数设置为"inline"。下面是一个示例，演示了 setup 如何修改以包含此选项：

>>> class Base(DeclarativeBase):
...     pass
>>> class Employee(Base):
...     __tablename__ = "employee"
...     id: Mapped[int] = mapped_column(primary_key=True)
...     name: Mapped[str]
...     type: Mapped[str]
...
...     def __repr__(self):
...         return f"{self.__class__.__name__}({self.name!r})"
...
...     __mapper_args__ = {
...         "polymorphic_identity": "employee",
...         "polymorphic_on": "type",
...     }
>>> class Manager(Employee):
...     manager_name: Mapped[str] = mapped_column(nullable=True)
...     __mapper_args__ = {
...         "polymorphic_identity": "manager",
...         "polymorphic_load": "inline",
...     }
>>> class Engineer(Employee):
...     engineer_info: Mapped[str] = mapped_column(nullable=True)
...     __mapper_args__ = {
...         "polymorphic_identity": "engineer",
...         "polymorphic_load": "inline",
...     }

有了上面的映射，Manager和Engineer类的列将自动包含在针对Employee实体的 SELECT 语句中：

>>> print(select(Employee))
SELECT  employee.id,  employee.name,  employee.type,
employee.manager_name,  employee.engineer_info
FROM  employee

继承加载 API

对象名称	描述
selectin_polymorphic(base_cls, classes)	指示应针对特定子类的所有属性进行急切加载。
with_polymorphic(base, classes[, selectable, flat, …])	产生一个`AliasedClass`构造，该构造指定了给定基类的后代映射器的列。

function sqlalchemy.orm.with_polymorphic(base: Type[_O] | Mapper[_O], classes: Literal['*'] | Iterable[Type[Any]], selectable: Literal[False, None] | FromClause = False, flat: bool = False, polymorphic_on: ColumnElement[Any] | None = None, aliased: bool = False, innerjoin: bool = False, adapt_on_names: bool = False, _use_mapper_path: bool = False) → AliasedClass[_O]

产生一个AliasedClass构造，该构造指定了给定基类的后代映射器的列。

使用这种方法将确保每个子类映射器的表都包含在 FROM 子句中，并允许对这些表使用 filter()条件。结果实例也将已经加载了那些列，因此不需要对这些列进行“后获取”。

请参阅

使用 with_polymorphic() - with_polymorphic()的全面讨论。

参数：

base – 要别名化的基类。
classes – 单个类或映射器，或者继承自基类的类/映射器列表。或者，它也可以是字符串'*'，在这种情况下，所有下降的映射类将被添加到 FROM 子句中。
aliased – 当为 True 时，可选择的将被别名。对于 JOIN，这意味着 JOIN 将从子查询中 SELECT，除非设置了with_polymorphic.flat标志为 True，这对于更简单的用例是推荐的。
flat – 布尔值，将被传递到 FromClause.alias() 调用，以便 Join 对象的别名将别名为加入内的各个表，而不是创建子查询。这通常受到所有现代数据库的支持，关于右嵌套连接，通常会生成更有效的查询。只要生成的 SQL 有效，建议设置此标志。
selectable –
将用于替代生成的 FROM 子句的表或子查询。如果所需的任何类使用具体表继承，这个参数是必需的，因为 SQLAlchemy 目前无法自动在表之间生成 UNION。如果使用，selectable 参数必须表示每个映射类映射的所有表和列的完整集。否则，未解释的映射列将直接附加到 FROM 子句，这通常会导致结果不正确。
当将其保留在默认值 False 时，分配给基本 mapper 的多态可选择将用于选择行。但是，它也可以传递为 None，这将绕过配置的多态可选择，而是为给定的目标类构造一个临时选择; 对于联接表继承，这将是包含所有目标映射器及其子类的联接。
polymorphic_on – 作为给定可选择对象的“鉴别器”列使用的列。如果未给出，则将使用基类的 mapper 的 polymorphic_on 属性（如果有）。这对于默认不具有多态加载行为的映射非常有用。
innerjoin – 如果为 True，将使用 INNER JOIN。只有在仅查询一个特定子类型时才应指定此选项
adapt_on_names –
将 aliased.adapt_on_names 参数传递给别名对象。在给定可选择对象与现有映射可选择对象不直接相关的情况下，这可能会很有用。
新版本 1.4.33 中新增。

function sqlalchemy.orm.selectin_polymorphic(base_cls: _EntityType[Any], classes: Iterable[Type[Any]]) → _AbstractLoad

表示应该对子类特定的所有属性进行急切加载。

这使用了一个额外的 SELECT 与所有匹配的主键值进行 IN 操作，它是对 mapper.polymorphic_load 参数上的 "selectin" 设置的每个查询的类似。

新版本 1.2 中新增。

另请参见

使用 selectin_polymorphic()

| — |
| selectin_polymorphic(base_cls, classes) | 指示应针对特定子类的所有属性进行急切加载。 |
| with_polymorphic(base, classes[, selectable, flat, …]) | 产生一个AliasedClass构造，该构造指定了给定基类的后代映射器的列。 |

function sqlalchemy.orm.with_polymorphic(base: Type[_O] | Mapper[_O], classes: Literal['*'] | Iterable[Type[Any]], selectable: Literal[False, None] | FromClause = False, flat: bool = False, polymorphic_on: ColumnElement[Any] | None = None, aliased: bool = False, innerjoin: bool = False, adapt_on_names: bool = False, _use_mapper_path: bool = False) → AliasedClass[_O]