为 ORM 映射类编写 SELECT 语句¶

关于本文档

本节使用 ORM 映射，首先在 SQLAlchemy Unified Tutorial，如一节所示声明映射类。

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',)
>>> foruser_objinresult.scalars():...     print(f"{user_obj.name} {user_obj.fullname}")spongebob Spongebob Squarepants

选择 ORM 实体和属性¶

select（） 结构接受 ORM 实体，包括映射类以及表示映射列的类级属性，这些属性被转换为 ORM 注释的 FromClause 和 ColumnElement 元素。

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

选择 ORM 实体¶

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

>>> 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 对象，则该元素保留 用户对象：

>>> 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 类中 SELECT 时，它们会根据其类名在每个结果行中命名。在下面的示例中，针对 User 和 Address 的 SELECT 结果行将在名称 User 和 Address 下引用它们：

>>> stmt=select(User,Address).join(User.addresses).order_by(User.id,Address.id)>>> forrowinsession.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.orgsandy sandy@sqlalchemy.orgsandy squirrel@squirrelpower.orgpatrick pat999@aol.comsquidward stentcl@sqlalchemy.org

如果我们想为行中的这些实体分配不同的名称，我们将使用 aliased（） 结构，并使用 aliased.name 参数为它们添加别名，以使用显式名称为它们添加别名：

>>> fromsqlalchemy.ormimportaliased>>> 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

上面的别名形式将在使用 Relationship 在别名目标之间进行联接。

现有的 Select 结构也可能具有 ORM 类和/或 column 表达式添加到其 columns 子句中 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列，如下面的运行时演示所示：

>>> 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 的构造，它允许在结果行中对列表达式集进行分组：

>>> fromsqlalchemy.ormimportBundle>>> stmt=select(...     Bundle("user", User.name, User.fullname),...     Bundle("email", Address.email_address),... ).join_from(User, Address)>>> forrowinsession.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.orgsandy Sandy Cheeks sandy@sqlalchemy.orgsandy Sandy Cheeks squirrel@squirrelpower.orgpatrick Patrick Star pat999@aol.comsquidward Squidward Tentacles stentcl@sqlalchemy.org

Bundle 对于创建轻量级视图和自定义列分组可能很有用。Bundle 也可以被子类化为 order 返回替代数据结构;看 Bundle.create_row_processor（） 为例。

另请参阅

捆

Bundle.create_row_processor（）

选择 ORM 别名¶

如教程使用别名中所述，创建 ORM 实体的 SQL 别名是使用 aliased（） 实现的构造：

>>> fromsqlalchemy.ormimportaliased>>> 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 参数：

>>> fromsqlalchemy.ormimportaliased>>> 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

另请参阅

别名结构是多个用例的核心，包括：

在 ORM 中使用子查询;各部分从 Subqueries 中选择实体，并且加入 Subqueries 将进一步讨论这一点。
控制结果集中实体的名称;看同时选择多个 ORM 实体作为示例
多次加入同一个 ORM 实体;看例如，使用 Relationship 在别名目标之间进行联接。

从文本语句中获取 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 类上使用 ORM 映射的属性：

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

我们现在有一个 ORM 配置的 SQL 结构，正如给定的那样，它可以分别加载 “id”、“name” 和 “fullname” 列。要改用此 SELECT 语句作为完整 User 实体的源，我们可以将这些列链接到启用常规 ORM 使用 Select.from_statement（） 选择构造体方法：

>>> orm_sql=select(User).from_statement(textual_sql)>>> foruser_objinsession.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)>>> foruser_objinsession.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（） 之类的方法的任何 Subquery 结构一起使用，以将 ORM 实体链接到该子查询返回的列;必须有列对应 子查询投递的列与列的关系实体映射到的 URL，这意味着子查询最终需要从这些实体派生，如以下示例所示：

>>> 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)>>> foruser_objinsession.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 实体子查询/CTE - 在 SQLAlchemy Unified 教程中

联接到子查询

从 UNION 和其他集合作中选择实体¶

union（） 和 union_all（） 函数是最常见的 set作，这些作以及其他 set作（如 except_（）、intersect（） 和其他函数传递一个称为 CompoundSelect 的对象，它由多个 选择由 set-operation 关键字连接的构造。可以使用 Select.from_statement（） 从简单的复合选择中选择 ORM 实体方法前面在从文本陈述中获取 ORM 结果中说明。在该方法中，UNION 语句是将要呈现的完整语句，Select.from_statement（） 后不能添加额外的条件用于：

>>> fromsqlalchemyimportunion_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)>>> foruser_objinsession.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（） 结构中的任何其他映射实体一样使用，包括我们可以根据其导出的列添加 filtering 和 order by criteria：

>>> 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)>>> foruser_objinsession.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 Unified 教程中

连接¶

Select.join（） 和 Select.join_from（） 方法用于针对 SELECT 语句构造 SQL JOIN。

本节将详细介绍这些方法的 ORM 使用案例。有关从 Core 角度使用它们的一般概述，请参阅显式 FROM 子句和 JOIN 在 SQLAlchemy Unified Tutorial 中。

Select.join（） 在 ORM 上下文中对 2.0 样式的使用 queries 在很大程度上等同于（减去遗留用例）的 Query.join（） 方法。

简单关系连接¶

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

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

在上面，对 Select.join（） 的调用 User.addresses 将产生大致相当于以下 SQL 的 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

在上面的示例中，我们将传递给 Select.join（） 作为 “on 子句”，也就是说，它指示应如何构造 JOIN 的 “ON” 部分。

提示

请注意，使用 Select.join（） 从一个实体到另一个实体的 JOIN 会影响 SELECT 语句的 FROM 子句，但不会影响 columns 子句;此示例中的 SELECT 语句将继续仅返回来自 User 实体的行。要同时从 User 和 Address 中 SELECT 列/实体，还必须在 select（） 函数中命名 Address 实体，或者随后使用 Select.add_columns（） 方法。请参阅该部分 Selecting Multiple ORM Entities Simultaneous （同时选择多个 ORM 实体）以获取有关这两种形式的示例。

链接多个连接¶

要构造连接链，可以使用多个 Select.join（） 调用。relationship-bound 属性同时表示联接的左侧和右侧。考虑其他实体 Order 和 Item，其中 User.orders 关系引用 Order 实体，而 Order.items 关系通过关联表order_items引用 Item 实体。两次 Select.join（） 调用将导致第一次从 User 到 Order，第二次从 Order 到 Order 项。但是，由于 Order.items 是多对多关系，则会产生两个单独的 JOIN 元素，总共三个 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（） 方法的顺序仅取决于在我们指示新目标之前，我们想要连接的 “left” 侧需要出现在 FROM 列表中。例如，Select.join（） 不知道如何 join 正确地指定 select(User).join(Order.items).join(User.orders) ，并且会引发错误。在正确的实践中，Select.join（） 方法的调用方式与我们希望 SQL 中的 JOIN 子句的呈现方式一致，并且每个调用都应该代表与前面的内容之间的明确链接。

我们在 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 子句加入 Target¶

第三种调用形式允许显式传递目标实体和 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 的目标以两种不同的方式;然而，这种形式的实用性变得显而易见当联接到别名实体时;请参阅该部分例如，使用 Relationship 在别名目标之间进行联接。

将 Relationship 与自定义 ON 标准相结合¶

由 relationship（） 结构生成的 ON 子句可以通过其他标准进行扩充。这对限制关系路径上特定联接范围的快速方法，以及配置加载器策略等情况，例如 joinedload（） 和 selectinload（） 一起。PropComparator.and_（） method 接受一系列位置化的 SQL 表达式，这些表达式将被连接到 JOIN 的 ON 子句中。例如，如果我们想要 JOIN 从 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（） 等加载器策略。请参阅将条件添加到加载器选项部分。

使用 Relationship 在别名目标之间进行连接¶

当使用 relationship（） 绑定属性构造连接时，以指示 ON 子句，即使用 ON 子句的 Target 联接可以扩展为使用 aliased（） 结构，以指示 SQL 别名作为连接的目标，同时仍然使用 relationship（） 绑定属性来指示 ON 子句，如下例所示，其中 User entity 被两次连接到针对 Address 实体的两个不同的 aliased（） 结构：

>>> 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 语句相同的 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（） 获取该属性 construct 直接构建：

>>> 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（） 结构，但这并不是严格要求的，特别是如果结果中未返回 Join 实体。例如，要从 User 实体改为 Address 实体，其中 Address 实体表示为行限制的子查询，我们首先构造一个 Subquery 对象，然后可以将其用作 Select.join（） 方法的目标：

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

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

>>> address_subq=aliased(Address,subq,name="address")>>> stmt=select(User,address_subq).join(address_subq)>>> forrowinsession.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')

沿 Relationship 路径加入 Subqueries¶

上一节中所示的子查询形式可以使用 relationship（）绑定属性，使用使用 Relationship 在别名目标之间进行联接。例如，要创建相同的连接，同时确保连接沿着特定 relationship（）中，我们可以使用 PropComparator.of_type（） 方法，将 aliased（） 构造，其中包含作为联接目标的 Subquery 对象：

>>> address_subq=aliased(Address,subq,name="address")>>> stmt=select(User,address_subq).join(User.addresses.of_type(address_subq))>>> forrowinsession.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')

引用多个实体的子查询¶

包含跨多个 ORM 实体的列的子查询可以一次应用于多个 aliased（） 结构，并就每个实体分别在同一个 Select 结构中使用。渲染的 SQL 将继续处理所有此类 aliased（） 构造为相同的子查询，但从 ORM / Python 的角度来看可以引用不同的返回值和对象属性通过使用适当的 aliased（） 构造。

例如，给定一个同时引用 User 和 Address 的子查询：

>>> user_address_subq = (
...     select(User.id, User.name, User.fullname, Address.id, Address.email_address)
...     .join_from(User, Address)
...     .where(Address.email_address.in_(["pat999@aol.com", "squirrel@squirrelpower.org"]))
...     .subquery()
... )

我们可以针对 User 和 Address 中，每个地址都引用同一个对象：

>>> user_alias = aliased(User, user_address_subq, name="user")
>>> address_alias = aliased(Address, user_address_subq, name="address")

从两个实体中选择的 Select 构造将呈现 subquery 一次，但在结果行上下文中可以返回两者的对象 User 和 Address 类：

>>> stmt=select(user_alias,address_alias).where(user_alias.name=="sandy")>>> forrowinsession.execute(stmt):...     print(f"{row.user} {row.address}")SELECT anon_1.id, anon_1.name, anon_1.fullname, anon_1.id_1, anon_1.email_address
FROM (SELECT user_account.id AS id, user_account.name AS name,
user_account.fullname AS fullname, address.id AS id_1,
address.email_address AS email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE address.email_address IN (?, ?)) AS anon_1
WHERE anon_1.name = ?
[...] ('pat999@aol.com', 'squirrel@squirrelpower.org', 'sandy')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='squirrel@squirrelpower.org')

在 join 中设置最左边的 FROM 子句¶

如果当前状态的左侧为 Select 不符合我们想要的连接，可以使用 Select.join_from（） 方法：

>>> stmt=select(Address).join_from(User,User.addresses).where(User.name=="sandy")>>> print(stmt)SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Select.join_from（） 方法接受两个或三个参数，格式为 （<join from>， <onclause>） 或 (<join from>, <join to>, [<onclause>]) ：

>>> stmt=select(Address).join_from(User,Address).where(User.name=="sandy")>>> print(stmt)SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

为 SELECT 设置初始 FROM 子句，以便 Select.join（） 后方也可以使用 Select.select_from（） 方法：

>>> stmt=select(Address).select_from(User).join(Address).where(User.name=="sandy")>>> print(stmt)SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

提示

Select.select_from（） 方法实际上对 FROM 子句中的表顺序没有最终决定权。如果该语句还引用了以不同顺序引用现有表的 Join 构造，则 Join 构造优先。当我们使用像 Select.join（） 这样的方法和 Select.join_from（） 一起创建这样的 Join 对象。因此，我们可以看到 Select.select_from（） 的内容在如下所示的情况下被覆盖：

>>> stmt=select(Address).select_from(User).join(Address.user).where(User.name=="sandy")>>> print(stmt)SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1

在上面，我们看到 FROM 子句是地址 JOIN user_account，即使我们首先声明了 select_from（User）。由于 .join（Address.user） 方法调用，则该语句最终等同于以下内容：

>>> fromsqlalchemy.sqlimportjoin>>>>>> user_table=User.__table__>>> address_table=Address.__table__>>>>>> j=address_table.join(user_table,user_table.c.id==address_table.c.user_id)>>> stmt=(...     select(address_table)...     .select_from(user_table)...     .select_from(j)...     .where(user_table.c.name == "sandy")... )>>> print(stmt)SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1

上面的 Join 构造将作为另一个条目添加到 Select.select_from（） 列表，该列表将取代上一个条目。

关系 WHERE 运算符¶

除了在 Select.join（） 和 Select.join_from（） 方法、 relationship（） 还有助于使用 Select.where（） 方法构造通常用于 WHERE 子句的 SQL 表达式。

EXISTS 形式：has（） / any（）¶

Exists 构造最初是在 SQLAlchemy Unified Tutorial 在 EXISTS 子查询部分中。此对象用于将 SQL EXISTS 关键字与标量子查询一起呈现。relationship（） 结构提供了一些辅助方法，这些方法可用于根据关系生成一些常见的 EXISTS 查询样式。

对于一对多关系（如 User.addresses），可以使用 PropComparator.any（） 针对 address 表生成与 user_account 表关联的 EXISTS。此方法接受可选的 WHERE 条件来限制子查询匹配的行：

>>> stmt=select(User.fullname).where(...     User.addresses.any(Address.email_address == "squirrel@squirrelpower.org")... )>>> session.execute(stmt).all()SELECT user_account.fullname
FROM user_account
WHERE EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id AND address.email_address = ?)
[...] ('squirrel@squirrelpower.org',)
[('Sandy Cheeks',)]

由于 EXISTS 往往对于否定查找更有效，因此常见的查询是查找不存在相关实体的实体。这句话简洁明了地使用了 ~User.addresses.any（） 等短语来选择没有相关 Address 行的 User 实体：

>>> stmt=select(User.fullname).where(~User.addresses.any())>>> session.execute(stmt).all()SELECT user_account.fullname
FROM user_account
WHERE NOT (EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id))
[...] ()
[('Eugene H. Krabs',)]

PropComparator.has（） 方法的工作方式与 PropComparator.any（）来访问，不同之处在于它用于多对一关系，例如，如果我们想找到属于 “sandy” 的所有 Address 对象：

>>> stmt=select(Address.email_address).where(Address.user.has(User.name=="sandy"))>>> session.execute(stmt).all()SELECT address.email_address
FROM address
WHERE EXISTS (SELECT 1
FROM user_account
WHERE user_account.id = address.user_id AND user_account.name = ?)
[...] ('sandy',)
[('sandy@sqlalchemy.org',), ('squirrel@squirrelpower.org',)]

关系实例比较运算符¶

relationship（） 绑定属性还提供了一些 SQL 构造实现，这些实现旨在根据相关对象的特定实例过滤 relationship（） 绑定的属性，它可以从给定的持久（或不太常见的分离）对象实例中解包适当的属性值，并根据目标 relationship（）构造 WHERE 条件。

多对一等于比较 - 可以将特定对象实例与多对一关系进行比较，以选择目标实体的外键与给定对象的主键值匹配的行：

>>> user_obj=session.get(User,1)SELECT ...
>>> print(select(Address).where(Address.user==user_obj))SELECT address.id, address.user_id, address.email_address
FROM address
WHERE :param_1 = address.user_id

多对一不等于比较 - 也可以使用不等于运算符：

>>> print(select(Address).where(Address.user!=user_obj))SELECT address.id, address.user_id, address.email_address
FROM address
WHERE address.user_id != :user_id_1 OR address.user_id IS NULL

object 包含在一对多集合中 - 这本质上是 “equals” 比较的一对多版本，选择主键等于相关对象中外键值的行：

>>> address_obj=session.get(Address,1)SELECT ...
>>> print(select(User).where(User.addresses.contains(address_obj)))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = :param_1

从一对多的角度来看，对象具有特定的父级 - 该 with_parent（） 函数生成一个比较，返回给定父级引用的行，这与在多对一端使用 == 运算符基本相同：

>>> fromsqlalchemy.ormimportwith_parent>>> print(select(Address).where(with_parent(user_obj,User.addresses)))SELECT address.id, address.user_id, address.email_address
FROM address
WHERE :param_1 = address.user_id

SQLAlchemy 2.0 文档

SQLAlchemy 2.0 文档

SQLAlchemy ORM

为 ORM 映射类编写 SELECT 语句¶

选择 ORM 实体和属性¶

选择 ORM 实体¶

同时选择多个 ORM 实体¶

选择单个属性¶

使用 Bundle 对所选属性进行分组¶

选择 ORM 别名¶

从文本语句中获取 ORM 结果¶

从子查询中选择实体¶

从 UNION 和其他集合作中选择实体¶

连接¶

简单关系连接¶

链接多个连接¶

加入目标实体¶

使用 ON 子句加入 Target¶

将 Relationship 与自定义 ON 标准相结合¶

使用 Relationship 在别名目标之间进行连接¶

加入子查询¶

沿 Relationship 路径加入 Subqueries¶

引用多个实体的子查询¶

在 join 中设置最左边的 FROM 子句¶

关系 WHERE 运算符¶

EXISTS 形式：has（） / any（）¶

关系实例比较运算符¶