使用 SELECT 语句¶

对于 Core 和 ORM，select() 函数生成一个 Select 构造，该构造用于所有 SELECT 查询。传递给 Core 中的 Connection.execute() 和 ORM 中的 Session.execute() 等方法时，将在当前事务中发出 SELECT 语句，并且可以通过返回的 Result 对象获得结果行。

ORM 阅读器 - 此处的内容同样适用于 Core 和 ORM 的使用，并且此处提到了基本的 ORM 变体用例。但是，还有许多特定于 ORM 的功能可用；这些功能在 ORM 查询指南中进行了文档化。

select() SQL 表达式构造¶

select() 构造以与 insert() 相同的方式构建语句，使用生成式方法，其中每个方法都在对象上构建更多状态。与其他 SQL 构造一样，它可以就地字符串化

>>> fromsqlalchemyimportselect>>> stmt=select(user_table).where(user_table.c.name=="spongebob")>>> print(stmt)SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1

同样，与所有其他语句级 SQL 构造一样，要实际运行语句，我们将其传递给执行方法。由于 SELECT 语句返回行，因此我们始终可以迭代结果对象以获取 Row 对象

>>> withengine.connect()asconn:...     for row in conn.execute(stmt):...         print(row)BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',)
(1, 'spongebob', 'Spongebob Squarepants')ROLLBACK

当使用 ORM 时，特别是使用针对 ORM 实体组成的 select() 构造时，我们将希望使用 Session.execute() 方法在 Session 上执行它；使用此方法，我们继续从结果中获取 Row 对象，但是这些行现在能够包含完整的实体，例如 User 类的实例，作为每行中的单个元素

>>> stmt=select(User).where(User.name=="spongebob")>>> withSession(engine)assession:...     for row in session.execute(stmt):...         print(row)BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',)
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)ROLLBACK

以下部分将更详细地讨论 SELECT 构造。

设置 COLUMNS 和 FROM 子句¶

select() 函数接受位置元素，这些元素表示任意数量的 Column 和/或 Table 表达式，以及各种兼容的对象，这些对象被解析为要从中 SELECT 的 SQL 表达式列表，这些表达式将作为结果集中的列返回。在更简单的情况下，这些元素还用于创建 FROM 子句，该子句是从传递的列和类似表的表达式推断出来的

>>> print(select(user_table))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account

要使用 Core 方法从单个列中进行 SELECT，可以从 Table.c 访问器访问 Column 对象，并且可以直接发送；FROM 子句将被推断为由这些列表示的所有 Table 和其他 FromClause 对象的集合

>>> print(select(user_table.c.name,user_table.c.fullname))SELECT user_account.name, user_account.fullname
FROM user_account

或者，当使用任何 FromClause（例如 Table）的 FromClause.c 集合时，可以通过使用字符串名称的元组为 select() 指定多个列

>>> print(select(user_table.c["name","fullname"]))SELECT user_account.name, user_account.fullname
FROM user_account

2.0 版本新增: 为 FromClause.c 集合添加了元组访问功能

选择 ORM 实体和列¶

ORM 实体，例如我们的 User 类以及其上的列映射属性（例如 User.name），也参与 SQL 表达式语言系统，表示表和列。下面说明了从 User 实体进行 SELECT 的示例，最终呈现方式与我们直接使用 user_table 相同

>>> print(select(User))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account

当使用 ORM Session.execute() 方法执行如上语句时，当我们从完整实体（例如 User）而不是 user_table 进行选择时，有一个重要的区别，即实体本身作为每行中的单个元素返回。也就是说，当我们从上述语句中获取行时，由于要获取的内容列表中只有 User 实体，因此我们返回的 Row 对象只有一个元素，其中包含 User 类的实例

>>> row=session.execute(select(User)).first()BEGIN...
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] ()
>>> row(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)

上面的 Row 只有一个元素，表示 User 实体

>>> row[0]
User(id=1, name='spongebob', fullname='Spongebob Squarepants')

实现与上述相同结果的高度推荐的便捷方法是使用 Session.scalars() 方法直接执行语句；此方法将返回一个 ScalarResult 对象，该对象立即传递每行的第一个“列”，在本例中为 User 类的实例

>>> user=session.scalars(select(User)).first()SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] ()
>>> userUser(id=1, name='spongebob', fullname='Spongebob Squarepants')

或者，我们可以通过使用类绑定属性在结果行中选择 ORM 实体的单个列作为不同的元素；当这些属性传递给诸如 select() 之类的构造时，它们将被解析为每个属性表示的 Column 或其他 SQL 表达式

>>> print(select(User.name,User.fullname))SELECT user_account.name, user_account.fullname
FROM user_account

当我们使用 Session.execute() 调用此语句时，我们现在收到每行的元素都具有单独元素的行，每个元素对应于单独的列或其他 SQL 表达式

>>> row=session.execute(select(User.name,User.fullname)).first()SELECT user_account.name, user_account.fullname
FROM user_account
[...] ()
>>> row('spongebob', 'Spongebob Squarepants')

这些方法也可以混合使用，如下所示，我们在行的第一个元素中 SELECT User 实体的 name 属性，并将其与第二个元素中的完整 Address 实体组合在一起

>>> session.execute(...     select(User.name, Address).where(User.id == Address.user_id).order_by(Address.id)... ).all()SELECT user_account.name, address.id, address.email_address, address.user_id
FROM user_account, address
WHERE user_account.id = address.user_id ORDER BY address.id
[...] ()
[('spongebob', Address(id=1, email_address='spongebob@sqlalchemy.org')),('sandy', Address(id=2, email_address='sandy@sqlalchemy.org')),('sandy', Address(id=3, email_address='sandy@squirrelpower.org'))]

有关选择 ORM 实体和列的方法以及转换行的常用方法的更多讨论，请参见选择 ORM 实体和属性。

另请参阅

选择 ORM 实体和属性 - 在 ORM 查询指南中

从标记的 SQL 表达式中选择¶

ColumnElement.label() 方法以及 ORM 属性上提供的同名方法提供了列或表达式的 SQL 标签，使其在结果集中具有特定的名称。当按名称引用结果行中的任意 SQL 表达式时，这会很有帮助

>>> fromsqlalchemyimportfunc,cast>>> stmt=select(...     ("Username: " + user_table.c.name).label("username"),... ).order_by(user_table.c.name)>>> withengine.connect()asconn:...     for row in conn.execute(stmt):...         print(f"{row.username}")BEGIN (implicit)
SELECT ? || user_account.name AS username
FROM user_account ORDER BY user_account.name
[...] ('Username: ',)
Username: patrickUsername: sandyUsername: spongebobROLLBACK

另请参阅

按标签排序或分组 - 我们创建的标签名称也可以在 Select 的 ORDER BY 或 GROUP BY 子句中引用。

使用文本列表达式选择¶

当我们使用 select() 函数构造 Select 对象时，通常会向其传递一系列使用表元数据定义的 Table 和 Column 对象，或者当使用 ORM 时，我们可能会发送表示表列的 ORM 映射属性。但是，有时还需要在语句内部制造任意 SQL 块，例如常量字符串表达式，或者只是更快地以字面形式编写的一些任意 SQL。

在使用事务和 DBAPI 中介绍的 text() 构造实际上可以直接嵌入到 Select 构造中，如下所示，我们制造了一个硬编码的字符串文字 'some phrase' 并将其嵌入到 SELECT 语句中

>>> fromsqlalchemyimporttext>>> stmt=select(text("'some phrase'"),user_table.c.name).order_by(user_table.c.name)>>> withengine.connect()asconn:...     print(conn.execute(stmt).all())BEGIN (implicit)
SELECT 'some phrase', user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
[('some phrase', 'patrick'), ('some phrase', 'sandy'), ('some phrase', 'spongebob')]ROLLBACK

虽然 text() 构造可以在大多数地方用于注入文字 SQL 短语，但通常我们实际上处理的是每个都代表单个列表达式的文本单元。在这种常见情况下，我们可以使用 literal_column() 构造从我们的文本片段中获得更多功能。此对象类似于 text()，但它不是表示任何形式的任意 SQL，而是显式表示单个“列”，然后可以标记并在子查询和其他表达式中引用

>>> fromsqlalchemyimportliteral_column>>> stmt=select(literal_column("'some phrase'").label("p"),user_table.c.name).order_by(...     user_table.c.name... )>>> withengine.connect()asconn:...     for row in conn.execute(stmt):...         print(f"{row.p}, {row.name}")BEGIN (implicit)
SELECT 'some phrase' AS p, user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
some phrase, patricksome phrase, sandysome phrase, spongebobROLLBACK

请注意，在这两种情况下，当使用 text() 或 literal_column() 时，我们正在编写语法 SQL 表达式，而不是文字值。因此，我们必须包含我们想要看到的呈现的 SQL 所需的任何引号或语法。

WHERE 子句¶

SQLAlchemy 允许我们通过使用标准 Python 运算符与 Column 和类似对象结合使用来组合 SQL 表达式，例如 name = 'squidward' 或 user_id > 10。对于布尔表达式，大多数 Python 运算符（例如 ==、!=、<、>= 等）生成新的 SQL 表达式对象，而不是纯布尔值 True/False

>>> print(user_table.c.name == "squidward")
user_account.name = :name_1

>>> print(address_table.c.user_id > 10)
address.user_id > :user_id_1

我们可以使用这样的表达式通过将结果对象传递给 Select.where() 方法来生成 WHERE 子句

>>> print(select(user_table).where(user_table.c.name=="squidward"))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1

要生成由 AND 连接的多个表达式，可以多次调用 Select.where() 方法

>>> print(...     select(address_table.c.email_address)...     .where(user_table.c.name == "squidward")...     .where(address_table.c.user_id == user_table.c.id)... )SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id

对 Select.where() 的单次调用也接受多个表达式，效果相同

>>> print(...     select(address_table.c.email_address).where(...         user_table.c.name == "squidward",...         address_table.c.user_id == user_table.c.id,...     )... )SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id

“AND” 和 “OR” 连接词都可以直接使用 and_() 和 or_() 函数，如下面的 ORM 实体示例所示

>>> fromsqlalchemyimportand_,or_>>> print(...     select(Address.email_address).where(...         and_(...             or_(User.name == "squidward", User.name == "sandy"),...             Address.user_id == User.id,...         )...     )... )SELECT address.email_address
FROM address, user_account
WHERE (user_account.name = :name_1 OR user_account.name = :name_2)
AND address.user_id = user_account.id

对于针对单个实体的简单“相等”比较，还有一种流行的方法称为 Select.filter_by()，它接受与列键或 ORM 属性名称匹配的关键字参数。它将根据最左侧的 FROM 子句或最后加入的实体进行过滤

>>> print(select(User).filter_by(name="spongebob",fullname="Spongebob Squarepants"))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1 AND user_account.fullname = :fullname_1

另请参阅

运算符参考 - SQLAlchemy 中大多数 SQL 运算符函数的描述

显式的 FROM 子句和 JOIN¶

如前所述，FROM 子句通常基于我们在列子句以及 Select 的其他元素中设置的表达式推断出来的。

如果我们在 COLUMNS 子句中设置来自特定 Table 的单个列，则它也会将该 Table 放入 FROM 子句中

>>> print(select(user_table.c.name))SELECT user_account.name
FROM user_account

如果我们要放入来自两个表的列，那么我们将得到一个逗号分隔的 FROM 子句

>>> print(select(user_table.c.name,address_table.c.email_address))SELECT user_account.name, address.email_address
FROM user_account, address

为了将这两个表 JOIN 在一起，我们通常在 Select 上使用两种方法之一。第一个是 Select.join_from() 方法，它允许我们显式指示 JOIN 的左侧和右侧

>>> print(...     select(user_table.c.name, address_table.c.email_address).join_from(...         user_table, address_table...     )... )SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

另一个是 Select.join() 方法，它仅指示 JOIN 的右侧，左侧是推断出来的

>>> print(select(user_table.c.name,address_table.c.email_address).join(address_table))SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

如果 FROM 子句不是我们希望从列子句中推断出来的方式，我们还可以选择显式地向其中添加元素。我们使用 Select.select_from() 方法来实现此目的，如下所示，我们建立 user_table 作为 FROM 子句中的第一个元素，并使用 Select.join() 建立 address_table 作为第二个元素

>>> print(select(address_table.c.email_address).select_from(user_table).join(address_table))SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

我们可能想要使用 Select.select_from() 的另一个示例是，如果我们的列子句没有足够的信息来提供 FROM 子句。例如，要从常见的 SQL 表达式 count(*) 中进行 SELECT，我们使用一个名为 sqlalchemy.sql.expression.func 的 SQLAlchemy 元素来生成 SQL count() 函数

>>> fromsqlalchemyimportfunc>>> print(select(func.count("*")).select_from(user_table))SELECT count(:count_2) AS count_1
FROM user_account

另请参阅

在 join 中设置最左侧的 FROM 子句 - 在 ORM 查询指南中 - 包含有关 Select.select_from() 和 Select.join() 交互的其他示例和注释。

设置 ON 子句¶

先前的 JOIN 示例说明，Select 构造可以在两个表之间进行 join 并自动生成 ON 子句。在这些示例中发生这种情况是因为 user_table 和 address_table Table 对象包含单个 ForeignKeyConstraint 定义，该定义用于形成此 ON 子句。

如果 join 的左侧和右侧目标没有这样的约束，或者存在多个约束，则我们需要直接指定 ON 子句。Select.join() 和 Select.join_from() 都接受 ON 子句的附加参数，该参数使用与我们在 WHERE 子句中看到的相同的 SQL 表达式机制来声明

>>> print(...     select(address_table.c.email_address)...     .select_from(user_table)...     .join(address_table, user_table.c.id == address_table.c.user_id)... )SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

ORM 提示 - 当使用 ORM 实体时，还有另一种生成 ON 子句的方法，该方法利用 relationship() 构造，例如在上一节声明映射类中设置的映射。这是一个完整的主题，在使用关系进行 Join 中进行了详细介绍。

OUTER 和 FULL join¶

Select.join() 和 Select.join_from() 方法都接受关键字参数 Select.join.isouter 和 Select.join.full，它们将分别呈现 LEFT OUTER JOIN 和 FULL OUTER JOIN

>>> print(select(user_table).join(address_table,isouter=True))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account LEFT OUTER JOIN address ON user_account.id = address.user_id
>>> print(select(user_table).join(address_table,full=True))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account FULL OUTER JOIN address ON user_account.id = address.user_id

还有一个方法 Select.outerjoin()，它等效于使用 .join(..., isouter=True)。

提示

SQL 也有 “RIGHT OUTER JOIN”。SQLAlchemy 不会直接呈现它；而是反转表的顺序并使用 “LEFT OUTER JOIN”。

ORDER BY, GROUP BY, HAVING¶

SELECT SQL 语句包含一个名为 ORDER BY 的子句，该子句用于在给定的排序中返回选定的行。

GROUP BY 子句的构造方式与 ORDER BY 子句类似，其目的是将选定的行细分为特定的组，可以在这些组上调用聚合函数。HAVING 子句通常与 GROUP BY 一起使用，并且与 WHERE 子句的形式类似，不同之处在于它应用于组内使用的聚合函数。

ORDER BY¶

ORDER BY 子句是根据 SQL 表达式构造（通常基于 Column 或类似对象）构造的。Select.order_by() 方法接受一个或多个位置表达式

>>> print(select(user_table).order_by(user_table.c.name))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.name

升序/降序可从 ColumnElement.asc() 和 ColumnElement.desc() 修饰符获得，这些修饰符也从 ORM 绑定的属性中获得

>>> print(select(User).order_by(User.fullname.desc()))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.fullname DESC

上面的语句将产生按 user_account.fullname 列降序排序的行。

带有 GROUP BY / HAVING 的聚合函数¶

在 SQL 中，聚合函数允许将多行中的列表达式聚合在一起以生成单个结果。示例包括计数、计算平均值以及查找一组值中的最大值或最小值。

SQLAlchemy 通过开放式的方式为 SQL 函数提供支持，使用名为 func 的命名空间。这是一个特殊的构造器对象，当给定特定 SQL 函数的名称（可以是任何名称）以及零个或多个传递给函数的参数（与所有其他情况一样，都是 SQL 表达式构造）时，它将创建 Function 的新实例。例如，要针对 user_account.id 列呈现 SQL COUNT() 函数，我们调用 count() 名称

>>> fromsqlalchemyimportfunc>>> count_fn=func.count(user_table.c.id)>>> print(count_fn)count(user_account.id)

SQL 函数将在本教程后面的使用 SQL 函数中更详细地介绍。

当在 SQL 中使用聚合函数时，GROUP BY 子句至关重要，因为它允许将行划分为组，聚合函数将分别应用于每个组。当在 SELECT 语句的 COLUMNS 子句中请求非聚合列时，SQL 要求这些列都受 GROUP BY 子句的约束，可以直接约束，也可以基于主键关联间接约束。然后，HAVING 子句的使用方式与 WHERE 子句类似，不同之处在于它基于聚合值而不是直接行内容来过滤行。

SQLAlchemy 使用 Select.group_by() 和 Select.having() 方法来提供这两个子句。下面我们演示了选择用户名字段以及地址计数，适用于拥有多个地址的用户

>>> withengine.connect()asconn:...     result = conn.execute(...         select(User.name, func.count(Address.id).label("count"))...         .join(Address)...         .group_by(User.name)...         .having(func.count(Address.id) > 1)...     )...     print(result.all())BEGIN (implicit)
SELECT user_account.name, count(address.id) AS count
FROM user_account JOIN address ON user_account.id = address.user_id GROUP BY user_account.name
HAVING count(address.id) > ?
[...] (1,)
[('sandy', 2)]ROLLBACK

按标签排序或分组¶

一个重要的技术，特别是在某些数据库后端上，是能够按已在 columns 子句中声明的表达式进行 ORDER BY 或 GROUP BY，而无需在 ORDER BY 或 GROUP BY 子句中重新声明该表达式，而是使用来自 COLUMNS 子句的列名或标签名。通过将名称的字符串文本传递给 Select.order_by() 或 Select.group_by() 方法，可以使用这种形式。传递的文本**不会直接呈现**；相反，在 columns 子句中给表达式的名称将在上下文中呈现为该表达式名称，如果找不到匹配项，则会引发错误。一元修饰符 asc() 和 desc() 也可以以这种形式使用

>>> fromsqlalchemyimportfunc,desc>>> stmt=(...     select(Address.user_id, func.count(Address.id).label("num_addresses"))...     .group_by("user_id")...     .order_by("user_id", desc("num_addresses"))... )>>> print(stmt)SELECT address.user_id, count(address.id) AS num_addresses
FROM address GROUP BY address.user_id ORDER BY address.user_id, num_addresses DESC

使用别名¶

现在我们正在从多个表进行选择并使用连接，我们很快就会遇到需要在语句的 FROM 子句中多次引用同一表的情况。我们使用 SQL **别名**来完成此操作，这是一种语法，它为表或子查询提供备用名称，可以从该名称在语句中引用它。

在 SQLAlchemy 表达式语言中，这些“名称”由 FromClause 对象表示，称为 Alias 构造，它在 Core 中使用 FromClause.alias() 方法构造。 Alias 构造就像 Table 构造一样，因为它在 Alias.c 集合中也具有 Column 对象的命名空间。例如，下面的 SELECT 语句返回所有唯一的用户名称对

>>> user_alias_1=user_table.alias()>>> user_alias_2=user_table.alias()>>> print(...     select(user_alias_1.c.name, user_alias_2.c.name).join_from(...         user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id...     )... )SELECT user_account_1.name, user_account_2.name AS name_1
FROM user_account AS user_account_1
JOIN user_account AS user_account_2 ON user_account_1.id > user_account_2.id

ORM 实体别名¶

与 FromClause.alias() 方法等效的 ORM 是 ORM aliased() 函数，它可以应用于实体，例如 User 和 Address。这会生成一个 Alias 对象，该对象在内部针对原始映射的 Table 对象，同时保持 ORM 功能。下面的 SELECT 从 User 实体中选择包含两个特定电子邮件地址的所有对象

>>> fromsqlalchemy.ormimportaliased>>> address_alias_1=aliased(Address)>>> address_alias_2=aliased(Address)>>> print(...     select(User)...     .join_from(User, address_alias_1)...     .where(address_alias_1.email_address == "patrick@aol.com")...     .join_from(User, 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

提示

正如设置 ON 子句中提到的，ORM 提供了另一种使用 relationship() 构造进行连接的方法。上面使用别名的示例在使用 relationship() 的使用 Relationship 在别名目标之间连接中进行了演示。

子查询和 CTE¶

SQL 中的子查询是一个 SELECT 语句，它在括号内呈现，并放置在封闭语句（通常是 SELECT 语句，但不一定是）的上下文中。

本节将介绍所谓的“非标量”子查询，它通常放置在封闭 SELECT 的 FROM 子句中。我们还将介绍公用表表达式或 CTE，它以类似于子查询的方式使用，但包含其他功能。

SQLAlchemy 使用 Subquery 对象来表示子查询，使用 CTE 来表示 CTE，通常分别从 Select.subquery() 和 Select.cte() 方法获得。这两个对象都可以用作较大 select() 构造中的 FROM 元素。

我们可以构造一个 Subquery，它将从 address 表中选择行的聚合计数（聚合函数和 GROUP BY 在之前的带有 GROUP BY / HAVING 的聚合函数中介绍过）

>>> subq = (
...     select(func.count(address_table.c.id).label("count"), address_table.c.user_id)
...     .group_by(address_table.c.user_id)
...     .subquery()
... )

单独对子查询进行字符串化，而不将其嵌入到另一个 Select 或其他语句中，会生成不带任何封闭括号的纯 SELECT 语句

>>> print(subq)SELECT count(address.id) AS count, address.user_id
FROM address GROUP BY address.user_id

Subquery 对象的行为类似于任何其他 FROM 对象，例如 Table，值得注意的是，它包括它选择的列的 Subquery.c 命名空间。我们可以使用此命名空间来引用 user_id 列以及我们的自定义标记 count 表达式

>>> print(select(subq.c.user_id,subq.c.count))SELECT anon_1.user_id, anon_1.count
FROM (SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id) AS anon_1

通过包含在 subq 对象中的行选择，我们可以将该对象应用于更大的 Select，它将数据连接到 user_account 表

>>> stmt=select(user_table.c.name,user_table.c.fullname,subq.c.count).join_from(...     user_table, subq... )>>> print(stmt)SELECT user_account.name, user_account.fullname, anon_1.count
FROM user_account JOIN (SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id) AS anon_1 ON user_account.id = anon_1.user_id

为了从 user_account 连接到 address，我们使用了 Select.join_from() 方法。正如之前所说明的，此连接的 ON 子句再次基于外键约束**推断**。即使 SQL 子查询本身没有任何约束，SQLAlchemy 也可以根据列上表示的约束采取操作，方法是确定 subq.c.user_id 列**派生**自 address_table.c.user_id 列，后者确实表达了返回到 user_table.c.id 列的外键关系，然后用于生成 ON 子句。

公用表表达式 (CTE)¶

在 SQLAlchemy 中使用 CTE 构造实际上与使用 Subquery 构造的方式相同。通过将 Select.subquery() 方法的调用更改为使用 Select.cte()，我们可以以相同的方式将结果对象用作 FROM 元素，但呈现的 SQL 是非常不同的公用表表达式语法

>>> subq=(...     select(func.count(address_table.c.id).label("count"), address_table.c.user_id)...     .group_by(address_table.c.user_id)...     .cte()... )>>> stmt=select(user_table.c.name,user_table.c.fullname,subq.c.count).join_from(...     user_table, subq... )>>> print(stmt)WITH anon_1 AS
(SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id)
 SELECT user_account.name, user_account.fullname, anon_1.count
FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id

CTE 构造还具有以“递归”样式使用的能力，并且在更复杂的情况下，可以从 INSERT、UPDATE 或 DELETE 语句的 RETURNING 子句中组成。 CTE 的文档字符串包含有关这些其他模式的详细信息。

在这两种情况下，子查询和 CTE 都在 SQL 级别使用“匿名”名称命名。在 Python 代码中，我们根本不需要提供这些名称。 Subquery 或 CTE 实例的对象标识充当呈现时对象的语法标识。可以通过将其作为 Select.subquery() 或 Select.cte() 方法的第一个参数传递来提供将在 SQL 中呈现的名称。

另请参阅

Select.subquery() - 有关子查询的更多详细信息

Select.cte() - CTE 的示例，包括如何使用 RECURSIVE 以及面向 DML 的 CTE

ORM 实体子查询/CTE¶

在 ORM 中，aliased() 构造可用于将 ORM 实体（例如我们的 User 或 Address 类）与表示行源的任何 FromClause 概念相关联。前面的章节 ORM 实体别名说明了如何使用 aliased() 将映射类与其映射 Table 的 Alias 相关联。这里我们说明 aliased() 对 Subquery 和针对 Select 构造生成的 CTE 执行相同的操作，最终都派生自同一映射的 Table。

下面是将 aliased() 应用于 Subquery 构造的示例，以便可以从其行中提取 ORM 实体。结果显示了一系列 User 和 Address 对象，其中每个 Address 对象的数据最终来自针对 address 表的子查询，而不是直接来自该表

>>> subq=select(Address).where(~Address.email_address.like("%@aol.com")).subquery()>>> address_subq=aliased(Address,subq)>>> stmt=(...     select(User, address_subq)...     .join_from(User, address_subq)...     .order_by(User.id, address_subq.id)... )>>> withSession(engine)assession:...     for user, address in session.execute(stmt):...         print(f"{user} {address}")BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.email_address, anon_1.user_id
FROM user_account JOIN
(SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE address.email_address NOT LIKE ?) AS anon_1 ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.id
[...] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')ROLLBACK

下面是另一个示例，它完全相同，只是使用了 CTE 构造

>>> cte_obj=select(Address).where(~Address.email_address.like("%@aol.com")).cte()>>> address_cte=aliased(Address,cte_obj)>>> stmt=(...     select(User, address_cte)...     .join_from(User, address_cte)...     .order_by(User.id, address_cte.id)... )>>> withSession(engine)assession:...     for user, address in session.execute(stmt):...         print(f"{user} {address}")BEGIN (implicit)
WITH anon_1 AS
(SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE address.email_address NOT LIKE ?)
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.email_address, anon_1.user_id
FROM user_account
JOIN anon_1 ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.id
[...] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')ROLLBACK

另请参阅

从子查询中选择实体 - 在 ORM 查询指南中

标量子查询和相关子查询¶

标量子查询是返回零行或一行且仅返回一列的子查询。然后，子查询在封闭 SELECT 语句的 COLUMNS 或 WHERE 子句中使用，并且与常规子查询不同，因为它不在 FROM 子句中使用。相关子查询是指封闭 SELECT 语句中的表的标量子查询。

SQLAlchemy 使用 ScalarSelect 构造表示标量子查询，它是 ColumnElement 表达式层次结构的一部分，而常规子查询由 Subquery 构造表示，它在 FromClause 层次结构中。

标量子查询通常（但不一定）与聚合函数一起使用，聚合函数在之前的带有 GROUP BY / HAVING 的聚合函数中介绍过。标量子查询通过使用 Select.scalar_subquery() 方法显式指示，如下所示。当自身进行字符串化时，它的默认字符串形式呈现为从两个表选择的普通 SELECT 语句

>>> subq=(...     select(func.count(address_table.c.id))...     .where(user_table.c.id == address_table.c.user_id)...     .scalar_subquery()... )>>> print(subq)(SELECT count(address.id) AS count_1
FROM address, user_account
WHERE user_account.id = address.user_id)

上面的 subq 对象现在属于 ColumnElement SQL 表达式层次结构，因为它可能像任何其他列表达式一样使用

>>> print(subq==5)(SELECT count(address.id) AS count_1
FROM address, user_account
WHERE user_account.id = address.user_id) = :param_1

尽管标量子查询本身在自身进行字符串化时会在其 FROM 子句中呈现 user_account 和 address，但在将其嵌入到处理 user_account 表的封闭 select() 构造中时，user_account 表会自动**关联**，这意味着它不会在子查询的 FROM 子句中呈现

>>> stmt=select(user_table.c.name,subq.label("address_count"))>>> print(stmt)SELECT user_account.name, (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id) AS address_count
FROM user_account

简单的相关子查询通常会执行所需的操作。但是，在相关性不明确的情况下，SQLAlchemy 会让我们知道需要更清晰的说明

>>> stmt = (
...     select(
...         user_table.c.name,
...         address_table.c.email_address,
...         subq.label("address_count"),
...     )
...     .join_from(user_table, address_table)
...     .order_by(user_table.c.id, address_table.c.id)
... )
>>> print(stmt)
Traceback (most recent call last):
...
InvalidRequestError: Select statement '<... Select object at ...>' returned
no FROM clauses due to auto-correlation; specify correlate(<tables>) to
control correlation manually.

为了指定 user_table 是我们要关联的表，我们使用 ScalarSelect.correlate() 或 ScalarSelect.correlate_except() 方法来指定

>>> subq = (
...     select(func.count(address_table.c.id))
...     .where(user_table.c.id == address_table.c.user_id)
...     .scalar_subquery()
...     .correlate(user_table)
... )

然后，该语句可以像任何其他语句一样返回此列的数据

>>> withengine.connect()asconn:...     result = conn.execute(...         select(...             user_table.c.name,...             address_table.c.email_address,...             subq.label("address_count"),...         )...         .join_from(user_table, address_table)...         .order_by(user_table.c.id, address_table.c.id)...     )...     print(result.all())BEGIN (implicit)
SELECT user_account.name, address.email_address, (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id) AS address_count
FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id
[...] ()
[('spongebob', 'spongebob@sqlalchemy.org', 1), ('sandy', 'sandy@sqlalchemy.org', 2), ('sandy', 'sandy@squirrelpower.org', 2)]ROLLBACK

LATERAL 相关性¶

LATERAL 相关性是 SQL 相关性的一个特殊子类别，它允许可选择单元引用单个 FROM 子句中的另一个可选择单元。这是一个非常特殊的用例，虽然是 SQL 标准的一部分，但已知仅受最新版本的 PostgreSQL 支持。

通常，如果 SELECT 语句在其 FROM 子句中引用 table1 JOIN (SELECT ...) AS subquery，则右侧的子查询可能不会引用左侧的“table1”表达式；相关性可能仅引用完全封闭此 SELECT 的另一个 SELECT 的一部分表。 LATERAL 关键字允许我们扭转此行为，并允许从右侧 JOIN 进行相关。

SQLAlchemy 使用 Select.lateral() 方法来支持此功能，该方法创建一个名为 Lateral 的对象。 Lateral 与 Subquery 和 Alias 属于同一系列，但当构造添加到封闭 SELECT 的 FROM 子句时，它也包括相关性行为。以下示例说明了一个使用 LATERAL 的 SQL 查询，它选择“用户帐户/电子邮件地址计数”数据，如上一节所述

>>> subq=(...     select(...         func.count(address_table.c.id).label("address_count"),...         address_table.c.email_address,...         address_table.c.user_id,...     )...     .where(user_table.c.id == address_table.c.user_id)...     .lateral()... )>>> stmt=(...     select(user_table.c.name, subq.c.address_count, subq.c.email_address)...     .join_from(user_table, subq)...     .order_by(user_table.c.id, subq.c.email_address)... )>>> print(stmt)SELECT user_account.name, anon_1.address_count, anon_1.email_address
FROM user_account
JOIN LATERAL (SELECT count(address.id) AS address_count,
address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE user_account.id = address.user_id) AS anon_1
ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.email_address

上面，JOIN 的右侧是一个子查询，它与 JOIN 左侧的 user_account 表相关联。

当使用 Select.lateral() 时，Select.correlate() 和 Select.correlate_except() 方法的行为也应用于 Lateral 构造。

另请参阅

Lateral

Select.lateral()

UNION、UNION ALL 和其他集合操作¶

在 SQL 中，可以使用 UNION 或 UNION ALL SQL 操作将 SELECT 语句合并在一起，这将生成一个或多个语句一起生成的所有行的集合。其他集合操作（例如 INTERSECT [ALL] 和 EXCEPT [ALL]）也是可能的。

SQLAlchemy 的 Select 构造使用诸如 union()、intersect() 和 except_() 以及“all”对应项 union_all()、intersect_all() 和 except_all() 等函数支持这种性质的组合。这些函数都接受任意数量的子可选择项，这些子可选择项通常是 Select 构造，但也可能是现有的组合。

这些函数产生的构造是 CompoundSelect，它的使用方式与 Select 构造类似，但方法较少。CompoundSelect 例如，通过 union_all() 产生，可以直接使用 Connection.execute() 调用。

>>> fromsqlalchemyimportunion_all>>> stmt1=select(user_table).where(user_table.c.name=="sandy")>>> stmt2=select(user_table).where(user_table.c.name=="spongebob")>>> u=union_all(stmt1,stmt2)>>> withengine.connect()asconn:...     result = conn.execute(u)...     print(result.all())BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[generated in ...] ('sandy', 'spongebob')
[(2, 'sandy', 'Sandy Cheeks'), (1, 'spongebob', 'Spongebob Squarepants')]ROLLBACK

要像 Select 一样将 CompoundSelect 用作子查询，它提供了 SelectBase.subquery() 方法，该方法将生成一个 Subquery 对象，其中包含一个 FromClause.c 集合，可以在封闭的 select() 中引用。

>>> u_subq=u.subquery()>>> stmt=(...     select(u_subq.c.name, address_table.c.email_address)...     .join_from(address_table, u_subq)...     .order_by(u_subq.c.name, address_table.c.email_address)... )>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     print(result.all())BEGIN (implicit)
SELECT anon_1.name, address.email_address
FROM address JOIN
  (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
  FROM user_account
  WHERE user_account.name = ?
UNION ALL
  SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
  FROM user_account
  WHERE user_account.name = ?)
AS anon_1 ON anon_1.id = address.user_id
ORDER BY anon_1.name, address.email_address
[generated in ...] ('sandy', 'spongebob')
[('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org'), ('spongebob', 'spongebob@sqlalchemy.org')]ROLLBACK

从 UNION 中选择 ORM 实体¶

前面的示例说明了如何使用两个 Table 对象构造 UNION，然后返回数据库行。如果我们想使用 UNION 或其他集合操作来选择行，然后将这些行作为 ORM 对象接收，则可以使用两种方法。在这两种情况下，我们首先构造一个 select() 或 CompoundSelect 对象，该对象表示我们想要执行的 SELECT / UNION / 等语句；此语句应针对目标 ORM 实体或其底层映射的 Table 对象进行组合。

>>> stmt1 = select(User).where(User.name == "sandy")
>>> stmt2 = select(User).where(User.name == "spongebob")
>>> u = union_all(stmt1, stmt2)

对于不嵌套在子查询中的简单 SELECT 与 UNION，通常可以通过使用 Select.from_statement() 方法在 ORM 对象获取上下文中使用它们。使用此方法，UNION 语句表示整个查询；在使用 Select.from_statement() 后，不能添加其他条件。

>>> orm_stmt=select(User).from_statement(u)>>> withSession(engine)assession:...     for obj in session.execute(orm_stmt).scalars():...         print(obj)BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[generated in ...] ('sandy', 'spongebob')
User(id=2, name='sandy', fullname='Sandy Cheeks')User(id=1, name='spongebob', fullname='Spongebob Squarepants')ROLLBACK

要以更灵活的方式将 UNION 或其他集合相关构造用作实体相关组件，可以使用 CompoundSelect 构造，通过 CompoundSelect.subquery() 将其组织成子查询，然后使用 aliased() 函数链接到 ORM 对象。这与 ORM 实体子查询/CTE 中介绍的方式相同，首先为我们期望的实体创建一个临时的“映射”到子查询，然后从这个新实体中选择，就像它是任何其他映射类一样。在下面的示例中，我们能够在 UNION 本身之外添加额外的条件，例如 ORDER BY，因为我们可以按子查询导出的列进行过滤或排序。

>>> user_alias=aliased(User,u.subquery())>>> orm_stmt=select(user_alias).order_by(user_alias.id)>>> withSession(engine)assession:...     for obj in session.execute(orm_stmt).scalars():...         print(obj)BEGIN (implicit)
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.name = ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.name = ?) AS anon_1 ORDER BY anon_1.id
[generated in ...] ('sandy', 'spongebob')
User(id=1, name='spongebob', fullname='Spongebob Squarepants')User(id=2, name='sandy', fullname='Sandy Cheeks')ROLLBACK

另请参阅

从 UNION 和其他集合运算中选择实体 - 在 ORM 查询指南中。

EXISTS 子查询¶

SQL EXISTS 关键字是一个运算符，与标量子查询一起使用，以返回布尔值 true 或 false，具体取决于 SELECT 语句是否会返回行。SQLAlchemy 包含 ScalarSelect 对象的一个变体，称为 Exists，它将生成一个 EXISTS 子查询，并且最方便的使用 SelectBase.exists() 方法生成。下面我们生成一个 EXISTS，以便我们可以返回在 address 中有多行相关行的 user_account 行。

>>> subq=(...     select(func.count(address_table.c.id))...     .where(user_table.c.id == address_table.c.user_id)...     .group_by(address_table.c.user_id)...     .having(func.count(address_table.c.id) > 1)... ).exists()>>> withengine.connect()asconn:...     result = conn.execute(select(user_table.c.name).where(subq))...     print(result.all())BEGIN (implicit)
SELECT user_account.name
FROM user_account
WHERE EXISTS (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id GROUP BY address.user_id
HAVING count(address.id) > ?)
[...] (1,)
[('sandy',)]ROLLBACK

EXISTS 构造通常用作否定，例如 NOT EXISTS，因为它提供了一种 SQL 有效的形式来查找相关表没有行的行。下面我们选择没有电子邮件地址的用户名；请注意第二个 WHERE 子句中使用的二元否定运算符 (~)。

>>> subq=(...     select(address_table.c.id).where(user_table.c.id == address_table.c.user_id)... ).exists()>>> withengine.connect()asconn:...     result = conn.execute(select(user_table.c.name).where(~subq))...     print(result.all())BEGIN (implicit)
SELECT user_account.name
FROM user_account
WHERE NOT (EXISTS (SELECT address.id
FROM address
WHERE user_account.id = address.user_id))
[...] ()
[('patrick',)]ROLLBACK

使用 SQL 函数¶

在本节前面使用 GROUP BY / HAVING 的聚合函数中首次介绍，func 对象充当创建新 Function 对象的工厂，当在像 select() 这样的构造中使用时，会生成一个 SQL 函数显示，通常由名称、一些括号（虽然不总是）以及可能的参数组成。典型的 SQL 函数示例包括：

count() 函数，一个聚合函数，用于计算返回的行数。

>>> print(select(func.count()).select_from(user_table))SELECT count(*) AS count_1
FROM user_account

lower() 函数，一个字符串函数，用于将字符串转换为小写。

>>> print(select(func.lower("A String With Much UPPERCASE")))SELECT lower(:lower_2) AS lower_1

now() 函数，用于提供当前日期和时间；由于这是一个常用函数，SQLAlchemy 知道如何为每个后端以不同的方式渲染它，在 SQLite 的情况下使用 CURRENT_TIMESTAMP 函数。

>>> stmt=select(func.now())>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     print(result.all())BEGIN (implicit)
SELECT CURRENT_TIMESTAMP AS now_1
[...] ()
[(datetime.datetime(...),)]
ROLLBACK

由于大多数数据库后端都有数十甚至数百个不同的 SQL 函数，func 尝试尽可能宽松地接受它。从此命名空间访问的任何名称都会自动被视为 SQL 函数，并将以通用方式渲染。

>>> print(select(func.some_crazy_function(user_table.c.name,17)))SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1
FROM user_account

同时，一小部分极其常见的 SQL 函数，例如 count、now、max、concat，包括它们自己的预打包版本，这些版本提供正确的类型信息，以及在某些情况下特定于后端的 SQL 生成。下面的示例对比了 PostgreSQL 方言和 Oracle Database 方言对于 now 函数发生的 SQL 生成。

>>> fromsqlalchemy.dialectsimportpostgresql>>> print(select(func.now()).compile(dialect=postgresql.dialect()))SELECT now() AS now_1
>>> fromsqlalchemy.dialectsimportoracle>>> print(select(func.now()).compile(dialect=oracle.dialect()))SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL

函数具有返回类型¶

由于函数是列表达式，它们也具有 SQL 数据类型，用于描述生成的 SQL 表达式的数据类型。我们在这里将这些类型称为“SQL 返回类型”，指的是在数据库端 SQL 表达式的上下文中函数返回的 SQL 值的类型，而不是 Python 函数的“返回类型”。

任何 SQL 函数的 SQL 返回类型都可以访问，通常用于调试目的，方法是引用 Function.type 属性；对于极少数极其常见的 SQL 函数，这将是预先配置的，但对于大多数 SQL 函数，如果未另行指定，则为“null”数据类型。

>>> # pre-configured SQL function (only a few dozen of these)
>>> func.now().type
DateTime()

>>> # arbitrary SQL function (all other SQL functions)
>>> func.run_some_calculation().type
NullType()

当在更大的表达式的上下文中使用函数表达式时，这些 SQL 返回类型非常重要；也就是说，当表达式的数据类型类似于 Integer 或 Numeric 时，数学运算符的效果会更好，JSON 访问器为了工作需要使用诸如 JSON 之类的类型。某些类别的函数返回整个行而不是列值，在需要引用特定列的情况下；此类函数称为表值函数。

当执行语句并获取返回行时，函数的 SQL 返回类型也可能很重要，对于 SQLAlchemy 必须应用结果集处理的情况。一个主要的例子是 SQLite 上的日期相关函数，其中 SQLAlchemy 的 DateTime 和相关数据类型承担了将字符串值转换为 Python datetime() 对象（当接收结果行时）的角色。

要将特定类型应用于我们正在创建的函数，我们使用 Function.type_ 参数传递它；类型参数可以是 TypeEngine 类或实例。在下面的示例中，我们传递 JSON 类来生成 PostgreSQL json_object() 函数，注意 SQL 返回类型将是 JSON 类型。

>>> from sqlalchemy import JSON
>>> function_expr = func.json_object('{a, 1, b, "def", c, 3.5}', type_=JSON)

通过使用 JSON 数据类型创建我们的 JSON 函数，SQL 表达式对象就具有了 JSON 相关的功能，例如访问元素。

>>> stmt=select(function_expr["def"])>>> print(stmt)SELECT json_object(:json_object_1)[:json_object_2] AS anon_1

内置函数具有预配置的返回类型¶

对于常见的聚合函数，如 count、max、min 以及极少数的日期函数，如 now 和字符串函数，如 concat，SQL 返回类型已适当设置，有时基于用法。max 函数和类似的聚合过滤函数将根据给定的参数设置 SQL 返回类型。

>>> m1 = func.max(Column("some_int", Integer))
>>> m1.type
Integer()

>>> m2 = func.max(Column("some_str", String))
>>> m2.type
String()

日期和时间函数通常对应于 DateTime、Date 或 Time 描述的 SQL 表达式。

>>> func.now().type
DateTime()
>>> func.current_date().type
Date()

已知的字符串函数（如 concat）会知道 SQL 表达式将是 String 类型。

>>> func.concat("x", "y").type
String()

但是，对于绝大多数 SQL 函数，SQLAlchemy 在其非常小的已知函数列表中没有显式地包含它们。例如，虽然通常使用 SQL 函数 func.lower() 和 func.upper() 来转换字符串的大小写没有问题，但 SQLAlchemy 实际上并不知道这些函数，因此它们具有“null” SQL 返回类型。

>>> func.upper("lowercase").type
NullType()

对于像 upper 和 lower 这样的简单函数，这个问题通常并不重要，因为可以从数据库接收字符串值，而无需 SQLAlchemy 端的任何特殊类型处理，并且 SQLAlchemy 的类型强制规则通常可以正确地猜测意图；例如，Python + 运算符将基于查看表达式的两侧被正确地解释为字符串连接运算符。

>>> print(select(func.upper("lowercase")+" suffix"))SELECT upper(:upper_1) || :upper_2 AS anon_1

总的来说，可能需要 Function.type_ 参数的情况是：

该函数还不是 SQLAlchemy 内置函数；这可以通过创建函数并观察 Function.type 属性来证明，即：
```
>>> func.count().type
Integer()
```
与
```
>>> func.json_object('{"a", "b"}').type
NullType()
```
需要支持函数感知的表达式；这最常指的是与数据类型相关的特殊运算符，例如 JSON 或 ARRAY。
需要结果值处理，这可能包括诸如 DateTime、Boolean、Enum 或再次是特殊数据类型（如 JSON、ARRAY）。

高级 SQL 函数技术¶

以下小节说明了可以使用 SQL 函数完成的更多操作。虽然这些技术不如基本的 SQL 函数使用那样常见和高级，但它们仍然非常流行，这很大程度上是由于 PostgreSQL 强调更复杂的函数形式，包括表值和列值形式，这些形式在 JSON 数据中很受欢迎。

使用窗口函数¶

窗口函数是 SQL 聚合函数的一种特殊用法，它在处理各个结果行的同时，计算组中返回的行上的聚合值。像 MAX() 这样的函数会给出您一组行中列的最高值，而将同一函数用作“窗口函数”将为您提供每行的最高值，截至该行。

在 SQL 中，窗口函数允许指定应在其上应用函数的行、“分区”值（用于考虑不同子集行的窗口）以及“order by”表达式，该表达式重要地指示应将行应用于聚合函数的顺序。

在 SQLAlchemy 中，由 func 命名空间生成的所有 SQL 函数都包含一个方法 FunctionElement.over()，它授予窗口函数或“OVER”语法；生成的构造是 Over 构造。

与窗口函数一起使用的常用函数是 row_number() 函数，它只是计算行数。我们可以根据用户名对该行计数进行分区，以对各个用户的电子邮件地址进行编号。

>>> stmt=(...     select(...         func.row_number().over(partition_by=user_table.c.name),...         user_table.c.name,...         address_table.c.email_address,...     )...     .select_from(user_table)...     .join(address_table)... )>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     print(result.all())BEGIN (implicit)
SELECT row_number() OVER (PARTITION BY user_account.name) AS anon_1,
user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
[(1, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (1, 'spongebob', 'spongebob@sqlalchemy.org')]ROLLBACK

上面，使用了 FunctionElement.over.partition_by 参数，以便在 OVER 子句中渲染 PARTITION BY 子句。我们还可以使用 FunctionElement.over.order_by 来使用 ORDER BY 子句。

>>> stmt=(...     select(...         func.count().over(order_by=user_table.c.name),...         user_table.c.name,...         address_table.c.email_address,...     )...     .select_from(user_table)...     .join(address_table)... )>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     print(result.all())BEGIN (implicit)
SELECT count(*) OVER (ORDER BY user_account.name) AS anon_1,
user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
[(2, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (3, 'spongebob', 'spongebob@sqlalchemy.org')]ROLLBACK

窗口函数的其他选项包括范围的使用；有关更多示例，请参见 over()。

提示

重要的是要注意，FunctionElement.over() 方法仅适用于那些实际上是聚合函数的 SQL 函数；虽然 Over 构造会愉快地为给定的任何 SQL 函数渲染自身，但如果函数本身不是 SQL 聚合函数，则数据库将拒绝该表达式。

特殊修饰符 WITHIN GROUP、FILTER¶

“WITHIN GROUP”SQL 语法与“有序集”或“假设集”聚合函数结合使用。常见的“有序集”函数包括 percentile_cont() 和 rank()。SQLAlchemy 包括内置实现 rank、dense_rank、mode、percentile_cont 和 percentile_disc，它们都包含 FunctionElement.within_group() 方法。

>>> print(...     func.unnest(...         func.percentile_disc([0.25, 0.5, 0.75, 1]).within_group(user_table.c.name)...     )... )unnest(percentile_disc(:percentile_disc_1) WITHIN GROUP (ORDER BY user_account.name))

某些后端支持“FILTER”，以将聚合函数的范围限制为特定子集行，与返回的总行范围相比，可以使用 FunctionElement.filter() 方法获得。

>>> stmt=(...     select(...         func.count(address_table.c.email_address).filter(user_table.c.name == "sandy"),...         func.count(address_table.c.email_address).filter(...             user_table.c.name == "spongebob"...         ),...     )...     .select_from(user_table)...     .join(address_table)... )>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     print(result.all())BEGIN (implicit)
SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1,
count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ('sandy', 'spongebob')
[(2, 1)]ROLLBACK

表值函数¶

表值 SQL 函数支持包含命名子元素的标量表示形式。表值函数通常用于 JSON 和 ARRAY 导向的函数以及像 generate_series() 这样的函数，在 FROM 子句中指定，然后作为表引用，有时甚至作为列引用。这种形式的函数在 PostgreSQL 数据库中很突出，但是 SQLite、Oracle Database 和 SQL Server 也支持某些形式的表值函数。

另请参阅

表值，表值和列值函数，行和元组对象 - 在 PostgreSQL 文档中。

虽然许多数据库都支持表值和其他特殊形式，但 PostgreSQL 往往是对这些功能需求最多的地方。请参阅本节，了解 PostgreSQL 语法的其他示例以及其他功能。

SQLAlchemy 提供了 FunctionElement.table_valued() 方法作为基本的“表值函数”构造，它将 func 对象转换为包含一系列命名列的 FROM 子句，基于按位置传递的字符串名称。这返回一个 TableValuedAlias 对象，它是一个启用函数的 Alias 构造，可以像使用别名中介绍的任何其他 FROM 子句一样使用。下面我们说明 json_each() 函数，虽然在 PostgreSQL 上很常见，但也受现代版本的 SQLite 支持。

>>> onetwothree=func.json_each('["one", "two", "three"]').table_valued("value")>>> stmt=select(onetwothree).where(onetwothree.c.value.in_(["two","three"]))>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     result.all()BEGIN (implicit)
SELECT anon_1.value
FROM json_each(?) AS anon_1
WHERE anon_1.value IN (?, ?)
[...] ('["one", "two", "three"]', 'two', 'three')
[('two',), ('three',)]ROLLBACK

上面，我们使用了 SQLite 和 PostgreSQL 支持的 json_each() JSON 函数来生成一个表值表达式，其中包含一个名为 value 的列，然后选择了它的三行中的两行。

另请参阅

表值函数 - 在 PostgreSQL 文档中 - 本节将详细介绍其他语法，例如特殊列派生和“WITH ORDINALITY”，这些语法已知与 PostgreSQL 一起使用。

列值函数 - 作为标量列的表值函数¶

PostgreSQL 和 Oracle Database 支持的一种特殊语法是在 FROM 子句中引用函数，然后在 SELECT 语句或其他列表达式上下文的列子句中将其作为单列传递。PostgreSQL 大量使用此语法用于诸如 json_array_elements()、json_object_keys()、json_each_text()、json_each() 等函数。

SQLAlchemy 将其称为“列值”函数，可通过将 FunctionElement.column_valued() 修饰符应用于 Function 构造来获得。

>>> fromsqlalchemyimportselect,func>>> stmt=select(func.json_array_elements('["one", "two"]').column_valued("x"))>>> print(stmt)SELECT x
FROM json_array_elements(:json_array_elements_1) AS x

Oracle Database 方言也支持“列值”形式，它可用于自定义 SQL 函数。

>>> fromsqlalchemy.dialectsimportoracle>>> stmt=select(func.scalar_strings(5).column_valued("s"))>>> print(stmt.compile(dialect=oracle.dialect()))SELECT s.COLUMN_VALUE
FROM TABLE (scalar_strings(:scalar_strings_1)) s

另请参阅

列值函数 - 在 PostgreSQL 文档中。

数据类型转换和类型强制转换¶

在 SQL 中，我们经常需要显式地指示表达式的数据类型，要么告诉数据库在其他情况下不明确的表达式中期望的类型，要么在某些情况下当我们想要将 SQL 表达式的隐含数据类型转换为其他类型时。SQL CAST 关键字用于此任务，在 SQLAlchemy 中，它由 cast() 函数提供。此函数接受列表达式和数据类型对象作为参数，如下所示，我们从 user_table.c.id 列对象生成 SQL 表达式 CAST(user_account.id AS VARCHAR)。

>>> fromsqlalchemyimportcast>>> stmt=select(cast(user_table.c.id,String))>>> withengine.connect()asconn:...     result = conn.execute(stmt)...     result.all()BEGIN (implicit)
SELECT CAST(user_account.id AS VARCHAR) AS id
FROM user_account
[...] ()
[('1',), ('2',), ('3',)]ROLLBACK

The cast() 函数不仅渲染 SQL CAST 语法，它还会生成一个 SQLAlchemy 列表达式，该表达式在 Python 端也将充当给定的数据类型。例如，一个字符串表达式通过 cast() 转换为 JSON 后，将获得 JSON 下标和比较运算符。

>>> fromsqlalchemyimportJSON>>> print(cast("{'a': 'b'}",JSON)["a"])CAST(:param_1 AS JSON)[:param_2]

type_coerce() - 仅限 Python 的 “cast”¶

有时需要让 SQLAlchemy 知道表达式的数据类型，出于上述所有原因，但不在 SQL 端渲染 CAST 表达式本身，因为它可能会干扰已经可以正常工作的 SQL 操作。对于这种相当常见的用例，还有另一个函数 type_coerce()，它与 cast() 密切相关，因为它将 Python 表达式设置为具有特定的 SQL 数据库类型，但不会在数据库端渲染 CAST 关键字或数据类型。type_coerce() 在处理 JSON 数据类型时尤为重要，这种数据类型通常与不同平台上的面向字符串的数据类型具有复杂的关系，甚至可能不是显式的数据类型，例如在 SQLite 和 MariaDB 上。下面，我们使用 type_coerce() 将 Python 结构作为 JSON 字符串传递到 MySQL 的 JSON 函数之一中。

>>> importjson>>> fromsqlalchemyimportJSON>>> fromsqlalchemyimporttype_coerce>>> fromsqlalchemy.dialectsimportmysql>>> s=select(type_coerce({"some_key":{"foo":"bar"}},JSON)["some_key"])>>> print(s.compile(dialect=mysql.dialect()))SELECT JSON_EXTRACT(%s, %s) AS anon_1

上面，之所以调用了 MySQL 的 JSON_EXTRACT SQL 函数，是因为我们使用了 type_coerce() 来指示我们的 Python 字典应被视为 JSON。Python 的 __getitem__ 运算符，在本例中为 ['some_key']，因此变得可用，并允许渲染 JSON_EXTRACT 路径表达式（未显示，但在本例中，它最终将是 '$."some_key"'）。

SQLAlchemy 2.0 文档

SQLAlchemy 2.0 文档

SQLAlchemy 统一教程

项目版本

使用 SELECT 语句¶

select() SQL 表达式构造¶

设置 COLUMNS 和 FROM 子句¶

选择 ORM 实体和列¶

从标记的 SQL 表达式中选择¶

使用文本列表达式选择¶

WHERE 子句¶

显式的 FROM 子句和 JOIN¶

设置 ON 子句¶

OUTER 和 FULL join¶

ORDER BY, GROUP BY, HAVING¶

ORDER BY¶

带有 GROUP BY / HAVING 的聚合函数¶

按标签排序或分组¶

使用别名¶

ORM 实体别名¶

子查询和 CTE¶

公用表表达式 (CTE)¶

ORM 实体子查询/CTE¶

标量子查询和相关子查询¶

LATERAL 相关性¶

UNION、UNION ALL 和其他集合操作¶

从 UNION 中选择 ORM 实体¶

EXISTS 子查询¶

使用 SQL 函数¶

函数具有返回类型¶

内置函数具有预配置的返回类型¶

高级 SQL 函数技术¶

使用窗口函数¶

特殊修饰符 WITHIN GROUP、FILTER¶

表值函数¶

列值函数 - 作为标量列的表值函数¶

数据类型转换和类型强制转换¶

type_coerce() - 仅限 Python 的 “cast”¶