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

SqlAlchemy 2.0 中文文档（十五）（1）https://developer.aliyun.com/article/1562979

ORM 查询指南

原文：docs.sqlalchemy.org/en/20/orm/queryguide/index.html

本节概述了使用 SQLAlchemy ORM 发出查询的 2.0 样式用法。

本节的读者应该熟悉 SQLAlchemy 统一教程中的 SQLAlchemy 概述，特别是这里的大部分内容扩展了使用 SELECT 语句的内容。

对于 SQLAlchemy 1.x 的用户

在 SQLAlchemy 2.x 系列中，ORM 的 SQL SELECT 语句是使用与 Core 中相同的select()构造而构建的，然后在Session的上下文中使用Session.execute()方法调用（就像用于 ORM-Enabled INSERT、UPDATE 和 DELETE 语句功能的现在使用的update()和delete()构造一样）。然而，遗留的Query对象，它执行与这些步骤相同的操作，更像是一个“一体化”的对象，仍然作为对这个新系统的薄外观保持可用，以支持在 1.x 系列上构建的应用程序，而无需对所有查询进行全面替换。有关此对象的参考，请参阅 Legacy Query API 部分。

为 ORM 映射类编写 SELECT 语句

选择 ORM 实体和属性

选择 ORM 实体
同时选择多个 ORM 实体
选择单个属性
将选定的属性与包一起分组
选择 ORM 别名
从文本语句中获取 ORM 结果
从子查询中选择实体
从 UNIONs 和其他集合操作中选择实体

连接

简单的关系连接
链接多个连接
连接到目标实体
使用 ON 子句连接到目标的连接（Joins to a Target with an ON Clause）
将关系与自定义 ON 条件组合（Combining Relationship with Custom ON Criteria）
使用 Relationship 在别名目标之间进行连接（Using Relationship to join between aliased targets）
连接到子查询（Joining to Subqueries）
沿关系路径连接到子查询（Joining to Subqueries along Relationship paths）
引用多个实体的子查询（Subqueries that Refer to Multiple Entities）
设置连接中的最左侧 FROM 子句（Setting the leftmost FROM clause in a join）

关系 WHERE 操作符（Relationship WHERE Operators）

EXISTS 表单：has() / any()（EXISTS forms: has() / any()）
关系实例比较操作符（Relationship Instance Comparison Operators）

用于继承映射的写入 SELECT 语句（Writing SELECT statements for Inheritance Mappings）

从基类 vs. 特定子类进行 SELECT（SELECTing from the base class vs. specific sub-classes）
使用 selectin_polymorphic()（使用 selectin_polymorphic()）

将 selectin_polymorphic() 应用于现有的急切加载（Applying selectin_polymorphic() to an existing eager load）
将加载器选项应用于由 selectin_polymorphic 加载的子类（Applying loader options to the subclasses loaded by selectin_polymorphic）
在映射器上配置 selectin_polymorphic()（Configuring selectin_polymorphic() on mappers）

使用 with_polymorphic()（Using with_polymorphic()）

使用 with_polymorphic() 过滤子类属性（Filtering Subclass Attributes with with_polymorphic()）
使用 with_polymorphic 进行别名处理（Using aliasing with with_polymorphic）
在映射器上配置 with_polymorphic()（Configuring with_polymorphic() on mappers）

连接到特定子类型或 with_polymorphic() 实体（Joining to specific sub-types or with_polymorphic() entities）

多态子类型的急切加载（Eager Loading of Polymorphic Subtypes）

单一继承映射的 SELECT 语句（SELECT Statements for Single Inheritance Mappings）

为单一继承优化属性加载（Optimizing Attribute Loads for Single Inheritance）

继承加载 API（Inheritance Loading API）

with_polymorphic()（with_polymorphic()）
selectin_polymorphic()（selectin_polymorphic()）

启用 ORM 的 INSERT、UPDATE 和 DELETE 语句（ORM-Enabled INSERT, UPDATE, and DELETE statements）

ORM 批量 INSERT 语句（ORM Bulk INSERT Statements）

使用 RETURNING 获取新对象（Getting new objects with RETURNING）
使用异构参数字典
在 ORM 批量插入语句中发送 NULL 值
连接表继承的批量插入
使用 SQL 表达式的 ORM 批量插入
遗留会话批量插入方法
ORM“upsert”语句

按主键进行 ORM 批量更新

为具有多个参数集的 UPDATE 语句禁用按主键进行 ORM 批量更新
用于连接表继承的按主键进行批量更新
遗留会话批量更新方法

使用自定义 WHERE 条件的 ORM UPDATE 和 DELETE

ORM 启用的更新和删除的重要说明和注意事项
选择同步策略
使用 RETURNING 与 UPDATE/DELETE 和自定义 WHERE 条件
使用自定义 WHERE 条件的 UPDATE/DELETE 用于连接表继承
遗留查询方法

列加载选项

限制列延迟加载的列

使用load_only()减少加载的列
使用defer()省略特定列
使用 raiseload 防止延迟加载列

配置映射上的列延迟

使用deferred()为命令式映射器、映射的 SQL 表达式
使用undefer()“急切地”加载延迟列
以组加载延迟列
使用undefer_group()按组取消延迟加载
使用通配符取消延迟加载
配置映射器级别的“raiseload”行为

将任意 SQL 表达式加载到对象上

使用 with_expression() 加载 UNIONs、其他子查询

列加载 API

defer()
deferred()
query_expression()
load_only()
undefer()
undefer_group()
with_expression()

关系加载技巧

关系加载风格摘要
在映射时配置加载器策略
带有加载器选项的关系加载

向加载器选项添加条件
使用 Load.options() 指定子选项

惰性加载

使用 raiseload 防止不必要的惰性加载

连接式急加载

连接式急加载的禅意

选择 IN 加载
子查询急加载
使用何种加载方式？
多态急加载
通配符加载策略

每个实体的通配符加载策略

将显式连接/语句路由到急加载集合

使用 contains_eager() 加载自定义过滤的集合结果

关系加载器 API

contains_eager()
defaultload()
immediateload()
joinedload()
lazyload()
Load
noload()
raiseload()
selectinload()
subqueryload()

查询的 ORM API 特性](api.html)

ORM 加载器选项
ORM 执行选项

填充现有内容
自动刷新
使用每个结果生成器获取大型结果集
身份标记

旧版查询 API

查询对象

查询

ORM 特定的查询构造

为 ORM 映射类编写 SELECT 语句

原文：docs.sqlalchemy.org/en/20/orm/queryguide/select.html

关于本文档

本节利用了首次在 SQLAlchemy 统一教程中展示的 ORM 映射，显示在声明映射类一节中。

查看此页面的 ORM 设置。

SELECT 语句由 select() 函数生成，该函数返回一个 Select 对象。要返回的实体和/或 SQL 表达式（即“columns”子句）按位置传递给该函数。然后，使用其他方法生成完整的语句，例如下面所示的 Select.where() 方法：

>>> from sqlalchemy import select
>>> stmt = select(User).where(User.name == "spongebob")

给定一个完成的 Select 对象，为了在 ORM 中执行并获取行，对象被传递给 Session.execute()，然后返回一个 Result 对象：

>>> result = session.execute(stmt)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account
WHERE  user_account.name  =  ?
[...]  ('spongebob',)
>>> for user_obj in result.scalars():
...     print(f"{user_obj.name} {user_obj.fullname}")
spongebob Spongebob Squarepants

选择 ORM 实体和属性

select() 构造函数接受 ORM 实体，包括映射类以及表示映射列的类级别属性，这些属性在构造时转换为 ORM 注解的 FromClause 和 ColumnElement 元素。

包含 ORM 注解实体的 Select 对象通常使用 Session 对象执行，而不是 Connection 对象，以便 ORM 相关功能生效，包括可以返回 ORM 映射对象的实例。直接使用 Connection 时，结果行仅包含列级数据。

选择 ORM 实体

下面我们从User实体中进行选择，生成一个从User映射到的Table中进行选择的Select：

>>> result = session.execute(select(User).order_by(User.id))
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account  ORDER  BY  user_account.id
[...]  ()

当从 ORM 实体中进行选择时，实体本身作为具有单个元素的行返回结果，而不是一系列单独的列；例如上面，Result返回仅在每行具有单个元素的Row对象，该元素保留着一个User对象：

>>> result.all()
[(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),),
 (User(id=2, name='sandy', fullname='Sandy Cheeks'),),
 (User(id=3, name='patrick', fullname='Patrick Star'),),
 (User(id=4, name='squidward', fullname='Squidward Tentacles'),),
 (User(id=5, name='ehkrabs', fullname='Eugene H. Krabs'),)]

当选择包含 ORM 实体的单元素行列表时，通常会跳过生成Row对象，而是直接接收 ORM 实体。最简单的方法是使用Session.scalars()方法来执行，而不是Session.execute()方法，这样就会返回一个ScalarResult对象，该对象产生单个元素而不是行：

>>> session.scalars(select(User).order_by(User.id)).all()
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account  ORDER  BY  user_account.id
[...]  ()
[User(id=1, name='spongebob', fullname='Spongebob Squarepants'),
 User(id=2, name='sandy', fullname='Sandy Cheeks'),
 User(id=3, name='patrick', fullname='Patrick Star'),
 User(id=4, name='squidward', fullname='Squidward Tentacles'),
 User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')]

调用Session.scalars()方法相当于调用Session.execute()来接收一个Result对象，然后调用Result.scalars()来接收一个ScalarResult对象。###同时选择多个 ORM 实体

select()函数一次接受任意数量的 ORM 类和/或列表达式，包括可以请求多个 ORM 类的情况。当从多个 ORM 类中选择时，它们在每个结果行中根据其类名命名。在下面的示例中，针对User和Address进行 SELECT 的结果行将以User和Address的名称引用它们：

>>> stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)
>>> for row in session.execute(stmt):
...     print(f"{row.User.name} {row.Address.email_address}")
SELECT  user_account.id,  user_account.name,  user_account.fullname,
address.id  AS  id_1,  address.user_id,  address.email_address
FROM  user_account  JOIN  address  ON  user_account.id  =  address.user_id
ORDER  BY  user_account.id,  address.id
[...]  ()
spongebob spongebob@sqlalchemy.org
sandy sandy@sqlalchemy.org
sandy squirrel@squirrelpower.org
patrick pat999@aol.com
squidward stentcl@sqlalchemy.org

如果我们想要为这些实体在行中分配不同的名称，我们将使用aliased()构造，使用aliased.name参数将它们别名为显式名称：

>>> from sqlalchemy.orm import aliased
>>> user_cls = aliased(User, name="user_cls")
>>> email_cls = aliased(Address, name="email")
>>> stmt = (
...     select(user_cls, email_cls)
...     .join(user_cls.addresses.of_type(email_cls))
...     .order_by(user_cls.id, email_cls.id)
... )
>>> row = session.execute(stmt).first()
SELECT  user_cls.id,  user_cls.name,  user_cls.fullname,
email.id  AS  id_1,  email.user_id,  email.email_address
FROM  user_account  AS  user_cls  JOIN  address  AS  email
ON  user_cls.id  =  email.user_id  ORDER  BY  user_cls.id,  email.id
[...]  ()
>>> print(f"{row.user_cls.name}  {row.email.email_address}")
spongebob spongebob@sqlalchemy.org

上述别名形式在使用关系连接别名目标中进一步讨论。

可以使用Select构造来向其列子句添加 ORM 类和/或列表达式，方法是使用Select.add_columns()方法。我们也可以使用这种形式来生成上述语句：

>>> stmt = (
...     select(User).join(User.addresses).add_columns(Address).order_by(User.id, Address.id)
... )
>>> print(stmt)
SELECT  user_account.id,  user_account.name,  user_account.fullname,
address.id  AS  id_1,  address.user_id,  address.email_address
FROM  user_account  JOIN  address  ON  user_account.id  =  address.user_id
ORDER  BY  user_account.id,  address.id

选择单个属性

映射类上的属性，如User.name和Address.email_address，当传递给select()时，可以像Column或其他 SQL 表达式对象一样使用。针对特定列创建一个select()将返回Row对象，而不是像User或Address对象那样的实体。每个Row将单独表示每一列：

>>> result = session.execute(
...     select(User.name, Address.email_address)
...     .join(User.addresses)
...     .order_by(User.id, Address.id)
... )
SELECT  user_account.name,  address.email_address
FROM  user_account  JOIN  address  ON  user_account.id  =  address.user_id
ORDER  BY  user_account.id,  address.id
[...]  ()

上述语句返回具有name和email_address列的Row对象，如下所示的运行时演示：

>>> for row in result:
...     print(f"{row.name}  {row.email_address}")
spongebob  spongebob@sqlalchemy.org
sandy  sandy@sqlalchemy.org
sandy  squirrel@squirrelpower.org
patrick  pat999@aol.com
squidward  stentcl@sqlalchemy.org

使用 Bundle 对选定属性进行分组

Bundle 构造是一个可扩展的仅限 ORM 的构造，允许将列表达式集合分组在结果行中：

>>> from sqlalchemy.orm import Bundle
>>> stmt = select(
...     Bundle("user", User.name, User.fullname),
...     Bundle("email", Address.email_address),
... ).join_from(User, Address)
>>> for row in session.execute(stmt):
...     print(f"{row.user.name} {row.user.fullname} {row.email.email_address}")
SELECT  user_account.name,  user_account.fullname,  address.email_address
FROM  user_account  JOIN  address  ON  user_account.id  =  address.user_id
[...]  ()
spongebob Spongebob Squarepants spongebob@sqlalchemy.org
sandy Sandy Cheeks sandy@sqlalchemy.org
sandy Sandy Cheeks squirrel@squirrelpower.org
patrick Patrick Star pat999@aol.com
squidward Squidward Tentacles stentcl@sqlalchemy.org

Bundle 可能对创建轻量级视图和自定义列分组很有用。Bundle 也可以被子类化以返回替代数据结构；参见Bundle.create_row_processor() 以获取示例。

另请参见

Bundle

Bundle.create_row_processor() ### 选择 ORM 别名

如在使用别名的教程中讨论的那样，要创建 ORM 实体的 SQL 别名，可以使用针对映射类的aliased()构造实现：

>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User)
>>> print(select(u1).order_by(u1.id))
SELECT  user_account_1.id,  user_account_1.name,  user_account_1.fullname
FROM  user_account  AS  user_account_1  ORDER  BY  user_account_1.id

与使用Table.alias()时的情况一样，SQL 别名是匿名命名的。对于从具有显式名称的行中选择实体的情况，也可以传递aliased.name参数：

>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User, name="u1")
>>> stmt = select(u1).order_by(u1.id)
>>> row = session.execute(stmt).first()
SELECT  u1.id,  u1.name,  u1.fullname
FROM  user_account  AS  u1  ORDER  BY  u1.id
[...]  ()
>>> print(f"{row.u1.name}")
spongebob

另见

aliased 构造在几种情况下都很重要，包括：

使用 ORM 的子查询；章节从子查询中选择实体和加入子查询进一步讨论了这一点。
控制结果集中实体的名称；参见同时选择多个 ORM 实体以查看示例
多次连接到相同的 ORM 实体；参见使用关系连接到别名目标以查看示例。###从文本语句获取 ORM 结果

ORM 支持从其他来源的 SELECT 语句加载实体。典型的用例是文本 SELECT 语句，在 SQLAlchemy 中使用text()构造表示。可以使用text()构造增强关于该语句将加载的 ORM 映射列的信息；然后可以将其与 ORM 实体本身关联，以便基于此语句加载 ORM 对象。

给定一个文本 SQL 语句，我们希望从中加载：

>>> from sqlalchemy import text
>>> textual_sql = text("SELECT id, name, fullname FROM user_account ORDER BY id")

通过使用TextClause.columns()方法，我们可以为语句添加列信息；当调用此方法时，TextClause对象被转换为一个TextualSelect对象，该对象扮演的角色类似于Select构造。TextClause.columns()方法通常传递Column对象或等效对象，在这种情况下，我们可以直接使用User类上映射的属性：

>>> textual_sql = textual_sql.columns(User.id, User.name, User.fullname)

现在我们有了一个经过 ORM 配置的 SQL 构造，按照给定的方式，可以单独加载“id”、“name”和“fullname”列。要将此 SELECT 语句用作完整User实体的源，则可以使用Select.from_statement()方法将这些列链接到常规的 ORM 启用的Select构造中：

>>> orm_sql = select(User).from_statement(textual_sql)
>>> for user_obj in session.execute(orm_sql).scalars():
...     print(user_obj)
SELECT  id,  name,  fullname  FROM  user_account  ORDER  BY  id
[...]  ()
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

同一个TextualSelect对象也可以使用TextualSelect.subquery()方法转换为子查询，并使用aliased()构造将其链接到User实体，方式与下面讨论的从子查询中选择实体类似：

>>> orm_subquery = aliased(User, textual_sql.subquery())
>>> stmt = select(orm_subquery)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
SELECT  anon_1.id,  anon_1.name,  anon_1.fullname
FROM  (SELECT  id,  name,  fullname  FROM  user_account  ORDER  BY  id)  AS  anon_1
[...]  ()
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

直接使用TextualSelect与Select.from_statement()相比，使用aliased()的区别在于，在前一种情况下，生成的 SQL 中不会产生子查询。在某些情景下，这样做从性能或复杂性的角度来看可能是有利的。 ### 从子查询中选择实体

在前一节讨论的aliased()构造中，可以与任何来自诸如Select.subquery()之类的方法的Subuqery构造一起使用，以将 ORM 实体链接到该子查询返回的列；子查询返回的列与实体映射的列之间必须存在列对应关系，这意味着子查询最终需要来自这些实体，就像下面的示例中一样：

>>> inner_stmt = select(User).where(User.id < 7).order_by(User.id)
>>> subq = inner_stmt.subquery()
>>> aliased_user = aliased(User, subq)
>>> stmt = select(aliased_user)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
  SELECT  anon_1.id,  anon_1.name,  anon_1.fullname
FROM  (SELECT  user_account.id  AS  id,  user_account.name  AS  name,  user_account.fullname  AS  fullname
FROM  user_account
WHERE  user_account.id  <  ?  ORDER  BY  user_account.id)  AS  anon_1
[generated  in  ...]  (7,)
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

另请参见

ORM 实体子查询/CTEs - 在 SQLAlchemy 统一教程中

加入到子查询 ### 从 UNION 和其他集合操作中选择实体

union()和union_all() 函数是最常见的集合操作之一，与except_()、intersect()等其他集合操作一起，它们生成一个称为CompoundSelect的对象，该对象由多个使用集合操作关键字连接的Select构造组成。ORM 实体可以使用Select.from_statement()方法从简单的复合选择中选择，该方法如在从文本语句中获取 ORM 结果中所示。在这种方法中，UNION 语句是将呈现的完整语句，不能在使用Select.from_statement()之后添加额外的条件：

>>> from sqlalchemy import union_all
>>> u = union_all(
...     select(User).where(User.id < 2), select(User).where(User.id == 3)
... ).order_by(User.id)
>>> stmt = select(User).from_statement(u)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account
WHERE  user_account.id  <  ?  UNION  ALL  SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account
WHERE  user_account.id  =  ?  ORDER  BY  id
[generated  in  ...]  (2,  3)
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')

CompoundSelect构造可以更灵活地在查询中使用，可以通过将其组织成子查询并使用aliased()将其链接到 ORM 实体来进一步修改，如在从子查询中选择实体中所示。在下面的示例中，我们首先使用CompoundSelect.subquery()创建 UNION ALL 语句的子查询，然后将其打包到aliased()构造中，在其中可以像其他映射实体一样在select()构造中使用，包括我们可以基于其导出的列添加过滤和排序条件：

>>> subq = union_all(
...     select(User).where(User.id < 2), select(User).where(User.id == 3)
... ).subquery()
>>> user_alias = aliased(User, subq)
>>> stmt = select(user_alias).order_by(user_alias.id)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
SELECT  anon_1.id,  anon_1.name,  anon_1.fullname
FROM  (SELECT  user_account.id  AS  id,  user_account.name  AS  name,  user_account.fullname  AS  fullname
FROM  user_account
WHERE  user_account.id  <  ?  UNION  ALL  SELECT  user_account.id  AS  id,  user_account.name  AS  name,  user_account.fullname  AS  fullname
FROM  user_account
WHERE  user_account.id  =  ?)  AS  anon_1  ORDER  BY  anon_1.id
[generated  in  ...]  (2,  3)
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')

另请参阅

从联合中选择 ORM 实体 - 在 SQLAlchemy 统一教程中##连接

Select.join()和Select.join_from()方法用于构建针对 SELECT 语句的 SQL JOINs。

本节将详细介绍这些方法的 ORM 用例。有关从核心角度使用它们的通用概述，请参阅明确的 FROM 子句和 JOINs 中的 SQLAlchemy 统一教程。

在 ORM 上下文中使用Select.join()进行 2.0 风格查询的用法大致相同，除了遗留用例外，与 1.x 风格查询中的Query.join()方法的用法相似。

简单的关系连接

考虑两个类User和Address之间的映射，其中关系User.addresses表示与每个User关联的Address对象的集合。 Select.join()的最常见用法是沿着这个关系创建 JOIN，使用User.addresses属性作为指示器来指示这应该如何发生：

>>> stmt = select(User).join(User.addresses)

在上文中，对User.addresses的Select.join()调用将导致 SQL 大致等效于：

>>> print(stmt)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account  JOIN  address  ON  user_account.id  =  address.user_id

在上面的示例中，我们将User.addresses称为传递给Select.join()的“on clause”，即它指示如何构造 JOIN 语句中的“ON”部分。

Tip

注意，使用Select.join()从一个实体连接到另一个实体会影响 SELECT 语句的 FROM 子句，但不会影响列子句；此示例中的 SELECT 语句将继续只返回User实体的行。要同时从User和Address选择列/实体，必须在select()函数中命名Address实体，或者使用Select.add_columns()方法在之后将其添加到Select构造中。请参阅同时选择多个 ORM 实体部分以了解这两种形式的示例。

链式多重连接

要构建一系列连接，可以使用多个Select.join()调用。关系绑定属性一次暗示了连接的左侧和右侧。考虑额外的实体Order和Item，其中User.orders关系引用了Order实体，而Order.items关系通过关联表order_items引用了Item实体。两个Select.join()调用将首先从User到Order进行连接，然后从Order到Item进行第二次连接。但是，由于Order.items是多对多关系，它导致两个单独的 JOIN 元素，总共在生成的 SQL 中有三个 JOIN 元素：

>>> stmt = select(User).join(User.orders).join(Order.items)
>>> print(stmt)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account
JOIN  user_order  ON  user_account.id  =  user_order.user_id
JOIN  order_items  AS  order_items_1  ON  user_order.id  =  order_items_1.order_id
JOIN  item  ON  item.id  =  order_items_1.item_id

每次调用Select.join()方法的顺序只有在我们想要连接的“左”侧需要在 FROM 列表中出现时才有意义；如果我们指定select(User).join(Order.items).join(User.orders)，则Select.join()将不知道如何正确连接，并引发错误。在正确的做法中，应以使 JOIN 子句在 SQL 中呈现方式对齐的方式调用Select.join()方法，并且每次调用应表示从之前的内容清晰地链接过来。

我们在 FROM 子句中目标的所有元素仍然可以作为继续连接 FROM 的潜在点。例如，我们可以继续添加其他元素来连接 FROM 上面的User实体，例如在连接链中添加User.addresses关系：

>>> stmt = select(User).join(User.orders).join(Order.items).join(User.addresses)
>>> print(stmt)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account
JOIN  user_order  ON  user_account.id  =  user_order.user_id
JOIN  order_items  AS  order_items_1  ON  user_order.id  =  order_items_1.order_id
JOIN  item  ON  item.id  =  order_items_1.item_id
JOIN  address  ON  user_account.id  =  address.user_id

连接到目标实体

第二种形式的Select.join()允许任何映射实体或核心可选择的构造作为目标。在这种用法中，Select.join()将尝试推断JOIN 的 ON 子句，使用两个实体之间的自然外键关系：

>>> stmt = select(User).join(Address)
>>> print(stmt)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account  JOIN  address  ON  user_account.id  =  address.user_id

在上述调用形式中，Select.join()被调用以自动推断“on 子句”。如果两个映射的Table构造之间没有ForeignKeyConstraint设置，或者存在多个使适当约束使用变得模糊的 ForeignKeyConstraint 链接时，此调用形式最终会引发错误。

注意

当使用 Select.join()或 Select.join_from() 而不指示 ON 子句时，ORM 配置的relationship()构造不会被考虑。只有在尝试推断 JOIN 的 ON 子句时，才会查阅映射的Table对象级别上的实体之间配置的ForeignKeyConstraint关系。

连接到具有 ON 子句的目标

第三种调用形式允许目标实体以及 ON 子句都明确传递。包含 SQL 表达式作为 ON 子句的示例如下：

>>> stmt = select(User).join(Address, User.id == Address.user_id)
>>> print(stmt)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account  JOIN  address  ON  user_account.id  =  address.user_id

基于表达式的 ON 子句也可以是一个relationship()-绑定属性，就像在简单关系连接中使用的那样：

>>> stmt = select(User).join(Address, User.addresses)
>>> print(stmt)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account  JOIN  address  ON  user_account.id  =  address.user_id

上面的例子看起来是多余的，因为它以两种不同的方式指示了 Address 的目标；然而，当连接到别名实体时，这种形式的效用就变得明显了；请参阅 Using Relationship to join between aliased targets 部分以查看示例。### 结合 Relationship 与自定义 ON 条件

relationship() 构造生成的 ON 子句可能会通过附加条件进行增强。这对于快速限制特定连接的范围以及配置加载器策略（如 joinedload() 和 selectinload()）等情况非常有用。PropComparator.and_() 方法按位置接受一系列 SQL 表达式，这些表达式将通过 AND 连接到 JOIN 的 ON 子句。例如，如果我们想要从 User 连接到 Address，但也只限制 ON 条件为特定的电子邮件地址：

>>> stmt = select(User.fullname).join(
...     User.addresses.and_(Address.email_address == "squirrel@squirrelpower.org")
... )
>>> session.execute(stmt).all()
SELECT  user_account.fullname
FROM  user_account
JOIN  address  ON  user_account.id  =  address.user_id  AND  address.email_address  =  ?
[...]  ('squirrel@squirrelpower.org',)
[('Sandy Cheeks',)]

另见

PropComparator.and_() 方法也适用于加载器策略，如 joinedload() 和 selectinload()。请参阅 Adding Criteria to loader options 部分。### 使用 Relationship 在别名目标之间进行连接

在使用 relationship()-绑定属性指示 ON 子句构建连接时，可以将 Joins to a Target with an ON Clause 中说明的两个参数语法扩展到与 aliased() 构造一起使用，以指示 SQL 别名作为连接的目标，同时仍然利用 relationship()-绑定属性指示 ON 子句，如下例所示，其中 User 实体两次与两个不同的 aliased() 构造连接到 Address 实体：

>>> address_alias_1 = aliased(Address)
>>> address_alias_2 = aliased(Address)
>>> stmt = (
...     select(User)
...     .join(address_alias_1, User.addresses)
...     .where(address_alias_1.email_address == "patrick@aol.com")
...     .join(address_alias_2, User.addresses)
...     .where(address_alias_2.email_address == "patrick@gmail.com")
... )
>>> print(stmt)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account
JOIN  address  AS  address_1  ON  user_account.id  =  address_1.user_id
JOIN  address  AS  address_2  ON  user_account.id  =  address_2.user_id
WHERE  address_1.email_address  =  :email_address_1
AND  address_2.email_address  =  :email_address_2

可以使用修饰符 PropComparator.of_type() 来更简洁地表达相同的模式，该修饰符可应用于与 relationship() 绑定的属性，一次性传递目标实体以指示一步中的目标。下面的示例使用 PropComparator.of_type() 来生成与刚刚展示的相同的 SQL 语句：

>>> print(
...     select(User)
...     .join(User.addresses.of_type(address_alias_1))
...     .where(address_alias_1.email_address == "patrick@aol.com")
...     .join(User.addresses.of_type(address_alias_2))
...     .where(address_alias_2.email_address == "patrick@gmail.com")
... )
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account
JOIN  address  AS  address_1  ON  user_account.id  =  address_1.user_id
JOIN  address  AS  address_2  ON  user_account.id  =  address_2.user_id
WHERE  address_1.email_address  =  :email_address_1
AND  address_2.email_address  =  :email_address_2

要利用 relationship() 来构建来自别名实体的连接，直接从 aliased() 构造中获取属性即可：

>>> user_alias_1 = aliased(User)
>>> print(select(user_alias_1.name).join(user_alias_1.addresses))
SELECT  user_account_1.name
FROM  user_account  AS  user_account_1
JOIN  address  ON  user_account_1.id  =  address.user_id 
```### 加入到子查询
连接的目标可以是任何“可选择”的实体，包括子查询。在使用 ORM 时，通常将这些目标陈述为 `aliased()` 构造的术语，但这不是严格要求的，特别是如果连接的实体不在结果中返回。例如，要从 `User` 实体连接到 `Address` 实体，其中 `Address` 实体表示为行限制的子查询，我们首先使用 `Select.subquery()` 构造了一个 `Subquery` 对象，然后可以将其用作 `Select.join()` 方法的目标：
```py
>>> subq = select(Address).where(Address.email_address == "pat999@aol.com").subquery()
>>> stmt = select(User).join(subq, User.id == subq.c.user_id)
>>> print(stmt)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account
JOIN  (SELECT  address.id  AS  id,
address.user_id  AS  user_id,  address.email_address  AS  email_address
FROM  address
WHERE  address.email_address  =  :email_address_1)  AS  anon_1
ON  user_account.id  =  anon_1.user_id

上述 SELECT 语句在通过 Session.execute() 调用时，将返回包含 User 实体但不包含 Address 实体的行。为了将 Address 实体包含到将在结果集中返回的实体集合中，我们对 Address 实体和 Subquery 对象构造了一个 aliased() 对象。我们还可能希望对 aliased() 构造应用一个名称，如下面使用的 "address"，这样我们就可以在结果行中按名称引用它：

>>> address_subq = aliased(Address, subq, name="address")
>>> stmt = select(User, address_subq).join(address_subq)
>>> for row in session.execute(stmt):
...     print(f"{row.User} {row.address}")
SELECT  user_account.id,  user_account.name,  user_account.fullname,
anon_1.id  AS  id_1,  anon_1.user_id,  anon_1.email_address
FROM  user_account
JOIN  (SELECT  address.id  AS  id,
address.user_id  AS  user_id,  address.email_address  AS  email_address
FROM  address
WHERE  address.email_address  =  ?)  AS  anon_1  ON  user_account.id  =  anon_1.user_id
[...]  ('pat999@aol.com',)
User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')

SqlAlchemy 2.0 中文文档（十五）（3）https://developer.aliyun.com/article/1562981

SqlAlchemy 2.0 中文文档（十五）（2）

ORM 查询指南

为 ORM 映射类编写 SELECT 语句

选择 ORM 实体和属性

选择 ORM 实体

选择单个属性

使用 Bundle 对选定属性进行分组

简单的关系连接

链式多重连接

连接到目标实体

连接到具有 ON 子句的目标

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

SqlAlchemy 2.0 中文文档（十五）（2）

ORM 查询指南

为 ORM 映射类编写 SELECT 语句

选择 ORM 实体和属性

选择 ORM 实体

选择单个属性

使用 Bundle 对选定属性进行分组

简单的关系连接

链式多重连接

连接到目标实体

连接到具有 ON 子句的目标

热门文章

最新文章

相关电子书