为 ORM 映射类编写 SELECT 语句¶

关于本文档

本节使用了 SQLAlchemy 统一教程中首次说明的 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',)
>>> foruser_objinresult.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 实体中选择，生成一个 Select，该 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 对象，每个行只有一个元素，该元素保存一个 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 类中 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

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

现有的 Select 构造也可以使用 Select.add_columns() 方法将 ORM 类和/或列表达式添加到其列子句中。我们也可以使用这种形式生成与上面相同的语句

>>> 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，可以像 Column 或其他 SQL 表达式对象一样使用，当传递给 select() 时。创建针对特定列的 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 的构造，允许在结果行中对列表达式集进行分组

>>> 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 也可以被子类化以返回备用数据结构；有关示例，请参见 Bundle.create_row_processor()。

另请参阅

Bundle

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

另请参阅

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 类上的 ORM 映射属性

>>> 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)>>> 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 实体链接到该子查询返回的列；子查询提供的列与实体映射到的列之间必须存在列对应关系，这意味着子查询最终需要从这些实体派生，如下例所示

>>> 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 统一教程中

连接到子查询

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

union() 和 union_all() 函数是最常见的集合操作，它们与其他集合操作（如 except_()、intersect() 等）一起提供一个名为 CompoundSelect 的对象，该对象由多个通过集合操作关键字连接的 Select 构造组成。可以使用 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() 构造中的任何其他映射实体一样使用它，包括我们可以根据其导出的列添加过滤和排序条件

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

另请参阅

从 Unions 中选择 ORM 实体 - 在 SQLAlchemy 统一教程中

连接¶

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

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

在 ORM 上下文中，对于 2.0 风格查询，Select.join() 的用法在很大程度上等同于 Query.join() 方法在 1.x 风格查询中的用法，减去旧版用例。

简单关系连接¶

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

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

在上面的代码中，调用 Select.join() 沿着 User.addresses 将导致 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() 的 User.addresses 称为“on 子句”，也就是说，它指示应该如何构造 JOIN 的 “ON” 部分。

提示

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

链接多个连接¶

要构建连接链，可以使用多个 Select.join() 调用。关系绑定属性同时暗示了连接的左侧和右侧。考虑额外的实体 Order 和 Item，其中 User.orders 关系引用 Order 实体，而 Order.items 关系通过关联表 order_items 引用 Item 实体。两个 Select.join() 调用将首先导致从 User 到 Order 的 JOIN，然后是从 Order 到 Item 的第二个 JOIN。但是，由于 Order.items 是一个 many to many 关系，它会导致两个单独的 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() 将不知道如何正确连接，并且会引发错误。在正确的实践中，Select.join() 方法的调用方式应与我们希望在 SQL 中呈现 JOIN 子句的方式一致，并且每次调用都应表示与先前内容的清晰链接。

我们在 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 clause”。如果两个映射的 Table 构造之间没有设置 ForeignKeyConstraint，或者它们之间存在多个 ForeignKeyConstraint 链接，以至于要使用的适当约束不明确，则此调用形式最终将引发错误。

注意

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

连接到带有 ON 子句的目标¶

第三种调用形式允许显式传递目标实体和 ON 子句。以下示例包含作为 ON 子句的 SQL 表达式。

>>> 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() 绑定属性，就像在 Simple Relationship Joins 中使用的方式一样。

>>> 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 部分。

将关系与自定义 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() 绑定属性构造连接以指示 ON 子句时，Joins to a Target with an ON Clause 中说明的两参数语法可以扩展为与 aliased() 构造一起使用，以指示 SQL 别名作为连接的目标，同时仍然使用 relationship() 绑定属性来指示 ON 子句，如下例所示，其中 User 实体两次连接到针对 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() 构造中获得。

>>> 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() 方法的目标。

>>> 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)>>> 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() 绑定属性以更高的特异性表达，使用 Using Relationship to join between aliased targets 中指示的形式之一。例如，要创建相同的连接，同时确保连接沿着特定的 relationship() 进行，我们可以使用 PropComparator.of_type() 方法，传递包含作为连接目标的 Subquery 对象的 aliased() 构造。

>>> 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 创建 aliased() 构造，它们都引用相同的对象。

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

从两个实体中选择的 Select 构造将渲染一次子查询，但在结果行上下文中可以同时返回 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')

在连接中设置最左侧的 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 子句是 address 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() 构造之外，relationship() 还在帮助构造 SQL 表达式方面发挥作用，这些表达式通常用于 WHERE 子句中，使用 Select.where() 方法。

EXISTS 形式：has() / any()¶

Exists 构造首先在 SQLAlchemy Unified Tutorial 的 EXISTS subqueries 部分中介绍。此对象用于呈现 SQL EXISTS 关键字以及标量子查询。relationship() 构造提供了一些辅助方法，可用于根据关系生成一些常见的 EXISTS 样式查询。

对于一对多关系，例如 User.addresses，可以使用 PropComparator.any() 生成针对 address 表的 EXISTS，该表与 user_account 表相关联。此方法接受可选的 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() 绑定属性，这可以从给定的 persistent（或较不常见的 detached）对象实例中解包适当的属性值，并根据目标 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

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

>>> 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 子句的目标¶

将关系与自定义 ON 条件结合使用¶

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

连接到子查询¶

沿关系路径连接到子查询¶

引用多个实体的子查询¶

在连接中设置最左侧的 FROM 子句¶

关系 WHERE 运算符¶

EXISTS 形式：has() / any()¶

关系实例比较运算符¶