使用 SELECT 语句¶

对于 Core 和 ORM，select（） 函数会生成一个 Select 构造，用于所有 SELECT 查询。传递给 Core 中的 Connection.execute（） 等方法，以及 Session.execute（） 中，在 ORM 中的当前交易和结果行，结果行可通过返回的 Result 对象。

ORM 阅读器 - 这里的内容同样适用于 Core 和 ORM 的使用，这里提到了基本的 ORM 变体用例。但是，还有更多特定于 ORM 的功能可用;这些记录在 ORM Querying Guide 中。

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 时，特别是使用 select（） 结构时，它是针对 ORM 实体组合，我们希望使用 Session.execute（） 方法;使用这种方法，我们继续从结果中获取 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 访问访问器，可以直接发送;FROM 子句将被推断为集合由这些列表示的所有 Table 和其他 FromClause 对象：

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

或者，当使用任何 FromClause 中，可以使用字符串名称元组为 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 名称 属性作为行的第一个元素，并将其与第二个元素中的完整 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 Querying Guide 中

从标记的 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 和使用 table 元数据，或者在使用 ORM 时，我们可能会发送表示表列的 ORM 映射属性。但是，有时还需要在语句中制造任意 SQL 块，例如常量字符串表达式，或者只是一些按字面书写速度更快的任意 SQL。

text（） 结构在使用 Transactions 和 DBAPI 实际上可以嵌入到直接选择 construct ，如下所示，我们生成一个硬编码的字符串文本 '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 允许我们编写 SQL 表达式，例如 name = 'squidward' 或 user_id > 10，通过在结合 Column 和类似对象。对于布尔表达式，大多数 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（） method 可以调用任意次数：

>>> 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 的其他元素。

如果我们从特定 Table 中设置单个列在 COLUMNS 子句中，它也将该 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 子句中从 columns 子句中推断出我们想要的方式。我们使用 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（） 的另一个示例是如果我们的 columns 子句没有足够的信息来提供 FROM 子句。例如，从通用 SQL 表达式中执行 SELECT作 count（*） 中，我们使用一个称为 SQL count（） 函数的 SQLAlchemy 元素 sqlalchemy.sql.expression.func 来生成：

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

另请参阅

在 ORM Querying Guide 中设置联接中最左边的 FROM 子句包含有关 Select.select_from（） 和 Select.join（） 中。

设置 ON 子句¶

前面的 JOIN 示例说明了 Select 构造可以在两个表之间联接并自动生成 ON 子句。在这些示例中会出现这种情况，因为 user_table 和 address_tableTable 对象包含一个 ForeignKeyConstraint 定义，用于形成此 ON 子句。

如果 join 的 left 和 right 目标没有这样的 constraint，或者有多个 constraints ，我们需要直接指定 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 提示 - 当使用使用 relationship（） 结构的 ORM 实体时，还有另一种生成 ON 子句的方法，就像上一节中设置的映射一样声明映射类。这是一个完整的主题，在使用关系联接中详细介绍了它。

OUTER 和 FULL 连接¶

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

排序依据，分组依据， HAVING¶

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

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

ORDER BY（订购 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 使用称为 func 的命名空间以开放式方式提供 SQL 函数。这是一个特殊的构造函数对象，当给定特定 SQL 函数的名称时，它将创建新的 Function 实例，该函数可以具有任何名称，以及零个或多个要传递给函数的参数，这些参数与所有其他情况一样，是 SQL 表达式构造。例如，要针对 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 子句的约束。然后，以与 WHERE 子句类似的方式使用 HAVING 子句，不同之处在于它根据聚合值而不是直接行内容过滤掉行。

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

按标签排序或分组¶

一项重要的技术，特别是在某些数据库后端上，是更改为 ORDER BY 或 GROUP BBY 列中已声明的表达式子句，而不在 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 别名来实现这一点，SQL 别名是一种语法，为表或子查询提供替代名称，可以在语句中引用该名称。

在 SQLAlchemy 表达式语言中，这些 “名称” 由 FromClause 对象（称为 Alias 构造），它是在 Core 中使用 FromClause.alias（） 构造的方法。Alias 结构就像一个 Table 构造，因为它还具有 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 从 用户实体包含两个特定电子邮件地址的所有对象：

>>> 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（） 演示的在 Using Relationship to join between aliased targets（使用关系在别名目标之间联接）中。

子查询和 CTE¶

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

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

SQLAlchemy 使用 Subquery 对象来表示子查询，使用 CTE 来表示 CTE，通常从 Select.subquery（） 和 Select.cte（） 方法。任一对象都可以用作较大 select（） 结构。

我们可以构造一个子查询，它将从地址表中选择聚合行数（聚合函数和 GROUP BY 之前在使用 GROUP BY / HAVIN 聚合函数中介绍过）：

>>> 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，该 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（） 方法的调用更改为使用 相反，我们可以以相同的方式将结果对象用作 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 的对象标识实例在渲染时用作对象的语法标识。将在 SQL 中呈现的名称可以通过将其作为 Select.subquery（） 或 Select.cte（） 方法的第一个参数。

另请参阅

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

Select.cte（） - CTE 示例，包括如何使用递归以及面向 DML 的 CTE

ORM 实体子查询/CTE¶

在 ORM 中，aliased（） 结构可用于将 ORM 实体（例如我们的 User 或 Address 类）与任何 FromClause 相关联表示行源的概念。上一节 ORM 实体别名说明了如何使用 aliased（） 将映射的类与其映射的 Table 的 Alias 相关联。在这里，我们说明了 aliased（） 对 Subquery 和 CTE 执行相同的作针对 Select 结构生成，该结构最终派生自同一映射的 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

下面是另一个示例，它与 API 完全相同，只是它使用了 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 Querying Guide 中

标量和相关子查询¶

标量子查询是恰好返回零行或一行和一列的子查询。然后，该子查询将用于封闭 SELECT 语句的 COLUMNS 或 WHERE 子句中，它与常规子查询的不同之处在于它不在 FROM 子句中使用。相关子查询是引用封闭 SELECT 语句中的表的标量子查询。

SQLAlchemy 使用 ScalarSelect 结构，它是 ColumnElement 表达式层次结构，与由 Subquery 构造表示的常规子查询相反，后者位于 FromClause 层次结构中。

标量子查询通常（但并非必须）与聚合函数一起使用，之前在使用 GROUP BY / HAVIN 聚合函数中介绍过。标量子查询通过使用 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

尽管标量子查询本身同时呈现 user_account 和 address 在其 FROM 子句中，当将其嵌入到处理 user_account表中，user_account 表会自动 correlated，这意味着它不会在子查询的 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 语句引用 table1 JOIN (SELECT ...) AS subquery 在其 FROM 子句中，右侧的子查询不能引用左侧的 “table1” 表达式;correlation 只能引用一个表，该表是另一个 SELECT 的一部分，该 SELECT 完全包含此 SELECT。LATERAL 关键字允许我们扭转这种行为，并允许从右侧 JOIN 进行关联。

SQLAlchemy 使用 Select.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 的右侧是一个子查询，它与 user_account 联接左侧的表。

当使用 Select.lateral（） 时， Select.correlate（） 和 Select.correlate_except（） 方法应用于 横向结构也是如此。

另请参阅

侧面的

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（） 的 API 中。这些函数都接受任意数量的 sub-selectable，这些子 selectables 通常是 Select 构件，但也可能是现有合成。

这些函数生成的构造是 CompoundSelect，其使用方式与 Select 构造相同，只是它的方法较少。由 例如，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

要将 CompoundSelect 用作子查询，就像 Select 一样它提供了一个 SelectBase.subquery（） 方法，该方法将生成一个具有 FromClause.c 的 Subquery 对象可以在封闭的 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

从 Unions 中选择 ORM 实体¶

前面的示例说明了如何构造给定两个 Table 对象返回数据库行。如果我们想使用 UNION 或其他 set作来选择然后作为 ORM 对象接收的行，可以使用两种方法。在这两种情况下，我们首先构造一个 select（） 或 CompoundSelect 对象，它表示我们想要的 SELECT / UNION / etc 语句执行;此语句应针对目标编写 ORM 实体或其底层映射的 Table 对象：

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

对于尚未嵌套在子查询中，这些通常可以在 ORM 对象获取上下文中使用 Select.from_statement（） 方法。使用这种方法， 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.subquery（） 将 CompoundSelect 结构组织成一个子查询，然后使用 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 Querying Guide 中

EXISTS 子查询¶

SQL EXISTS 关键字是一个运算符，与标量子查询一起使用，以返回布尔值 true 或 false，具体取决于 SELECT 语句将返回一行。 SQLAlchemy 包括 ScalarSelect 对象，它将生成一个 EXISTS 子查询，最方便地使用 SelectBase.exists（） 方法。下面我们生成一个 EXISTS，以便我们可以返回 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 / HAVANG 聚合函数时，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 方言的 SQL 生成与 now 函数的 Oracle Database 方言：

>>> 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 函数的“返回类型”。

通常出于调试目的，可以通过引用 Function.type 来访问任何 SQL 函数的 SQL 返回类型属性;这将为少数极其常见的 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 的 JSON 格式。某些类的函数返回整行而不是列值，其中需要引用特定列;此类函数称为表值函数。

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

要将特定类型应用于我们正在创建的函数，我们使用 Function.type_ 参数;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）和字符串函数（如 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()

日期和时间函数通常对应于 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 这样的简单函数，问题通常并不严重，因为 string values 可能是从数据库接收的，而 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 或 数组
需要对结果值进行处理，其中可能包括 DateTime、Boolean、Enum 或特殊数据类型，例如 JSON、 ARRAY的 ARRAY 中。

高级 SQL 函数技术¶

以下小节说明了可以使用 SQL 函数完成的更多作。虽然这些技术比基本的 SQL 函数使用更不常见且更先进，但它们仍然非常受欢迎，这主要是由于 PostgreSQL 强调更复杂的函数形式，包括在 JSON 数据中流行的表值和列值形式。

使用窗口函数¶

窗口函数是 SQL 聚合函数的一种特殊用途，它在处理各个结果行时计算组中返回的行的聚合值。而像 MAX（） 这样的函数会给你使用相同函数的一组行中列的最大值因为 “window function” 会给你每行的最高值， 截至该行。

在 SQL 中，窗口函数允许指定应应用函数的行，“partition”值（考虑不同行子集上的窗口）以及“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 子句。我们也可以使用 ORDER BY 子句，FunctionElement.over.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（） method 仅适用于实际上是 aggregate 的 SQL 函数功能;虽然 Over 构造将愉快地为给定的任何 SQL 函数呈现自身，但如果函数本身不是 SQL 聚合函数，则数据库将拒绝表达式。

特殊修饰符 WITHIN GROUP， FILTER¶

“WITHIN GROUP” SQL 语法与 “ordered set” 或 “hypothetical set” 聚合函数结合使用。常见的“有序集”函数包括 percentile_cont（） 和 rank（） 的 Normal。 SQLAlchemy 包括内置实现 秩、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 也支持某些形式的表值函数。

另请参阅

Table values， Table and Column valued functions， Row 和 Tuple objects - 在 PostgreSQL 文档中。

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

SQLAlchemy 提供了 FunctionElement.table_valued() 作为基本的 “table valued function” 结构，它将 func 对象转换为包含一系列命名列，基于按位置传递的字符串名称。这将返回一个 TableValuedAlias 对象，该对象是启用函数的 别名结构，可用作使用别名中介绍的任何其他 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 文档中 - 本节将详细介绍其他语法，例如已知适用于 PostgreSQL 的特殊列派生和“WITH ORDINALITY”。

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

PostgreSQL 和 Oracle Database 支持的一种特殊语法是在 FROM 子句中引用函数，然后该函数在 SELECT 语句或其他列表达式上下文的 columns 子句中将自身作为单个列传递。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 dialects 也支持“column valued”形式，可用于自定义 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（） 函数提供。此函数接受列表达式和数据类型 object 作为参数，如下所示，我们生成一个 SQL 表达式 CAST(user_account.id AS VARCHAR) 从 user_table.c.id 列对象中：

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

cast（） 函数不仅呈现 SQL CAST 语法，还生成一个 SQLAlchemy 列表达式，该表达式也将在 Python 端充当给定的数据类型。一个字符串表达式，该表达式是 cast（） to JSON 将获得 JSON 下标和比较运算符，例如：

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

type_coerce（） - 仅限 Python 的 “转换”¶

有时，出于上述所有原因，需要让 SQLAlchemy 知道表达式的数据类型，但不在 SQL 端呈现 CAST 表达式本身，这可能会干扰已经在没有它的情况下工作的 SQL作。对于这个相当常见的用例，还有另一个函数 type_coerce（） 与 cast（）的 SQL 表达式，因为它将 Python 表达式设置为具有特定的 SQL 数据库类型，但不在数据库端呈现 CAST 关键字或数据类型。type_coerce（） 在处理 JSON 数据类型时尤为重要，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 Unified 教程

使用 SELECT 语句¶

select（） SQL 表达式结构¶

设置 COLUMNS 和 FROM 子句¶

选择 ORM 实体和列¶

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

使用文本列表达式选择¶

WHERE 子句¶

显式 FROM 子句和 JOIN¶

设置 ON 子句¶

OUTER 和 FULL 连接¶

排序依据，分组依据， HAVING¶

ORDER BY（订购 BY）¶

使用 GROUP BY / HAVING 聚合函数¶

按标签排序或分组¶

使用别名¶

ORM 实体别名¶

子查询和 CTE¶

公共表表达式（CTE）¶

ORM 实体子查询/CTE¶

标量和相关子查询¶

LATERAL 关联¶

UNION、UNION ALL 和其他集合作¶

从 Unions 中选择 ORM 实体¶

EXISTS 子查询¶

使用 SQL 函数¶

函数具有返回类型¶

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

高级 SQL 函数技术¶

使用窗口函数¶

特殊修饰符 WITHIN GROUP， FILTER¶

表值函数¶

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

数据转换和类型强制¶

type_coerce（） - 仅限 Python 的 “转换”¶

SQLAlchemy 2.0 文档

SQLAlchemy 2.0 文档

SQLAlchemy Unified 教程

使用 SELECT 语句¶

select（） SQL 表达式结构¶

设置 COLUMNS 和 FROM 子句¶

选择 ORM 实体和列¶

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

使用文本列表达式选择¶

WHERE 子句¶

显式 FROM 子句和 JOIN¶

设置 ON 子句¶

OUTER 和 FULL 连接¶

排序依据， 分组依据， HAVING¶

ORDER BY（订购 BY）¶

使用 GROUP BY / HAVING 聚合函数¶

按标签排序或分组¶

使用别名¶

ORM 实体别名¶

子查询和 CTE¶

公共表表达式 （CTE）¶

ORM 实体子查询/CTE¶

标量和相关子查询¶

LATERAL 关联¶

UNION、UNION ALL 和其他集合作¶

从 Unions 中选择 ORM 实体¶

EXISTS 子查询¶

使用 SQL 函数¶

函数具有返回类型¶

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

高级 SQL 函数技术¶

使用窗口函数¶

特殊修饰符 WITHIN GROUP， FILTER¶

表值函数¶

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

数据转换和类型强制¶

type_coerce（） - 仅限 Python 的 “转换”¶

排序依据，分组依据， HAVING¶

公共表表达式（CTE）¶