SqlAlchemy 2.0 中文文档(十五)(1)https://developer.aliyun.com/article/1562979
ORM 查询指南
本节概述了使用 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 语句
关于本文档
本节利用了首次在 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