SqlAlchemy 2.0 中文文档（二）（3）-阿里云开发者社区

SqlAlchemy 2.0 中文文档（二）（2）https://developer.aliyun.com/article/1560324

设置 COLUMNS 和 FROM 子句

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

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

使用核心方法进行按列选择时，从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的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
[...]  ()
>>> user
User(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')

这些方法也可以混合使用，如下所示，我们将 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 表达式。当通过名称引用结果行中的任意 SQL 表达式时，这可能会有所帮助：

>>> from sqlalchemy import func, cast
>>> stmt = select(
...     ("Username: " + user_table.c.name).label("username"),
... ).order_by(user_table.c.name)
>>> with engine.connect() as conn:
...     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: patrick
Username: sandy
Username: spongebob
ROLLBACK

另请参阅

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

使用文本列表达式进行选择

当我们使用select()函数构造一个Select对象时，通常会传递一系列使用表元数据定义的Table和Column对象，或者在使用 ORM 时，我们可能会发送表示表列的 ORM 映射属性。然而，有时也需要在语句内部制造任意 SQL 块，比如常量字符串表达式，或者一些更容易直接写的任意 SQL。

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

>>> from sqlalchemy import text
>>> stmt = select(text("'some phrase'"), user_table.c.name).order_by(user_table.c.name)
>>> with engine.connect() as conn:
...     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，而是明确表示一个“列”，然后可以在子查询和其他表达式中标记和引用：

>>> from sqlalchemy import literal_column
>>> stmt = select(literal_column("'some phrase'").label("p"), user_table.c.name).order_by(
...     user_table.c.name
... )
>>> with engine.connect() as conn:
...     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, patrick
some phrase, sandy
some phrase, spongebob
ROLLBACK

注意，在使用text()或literal_column()时，我们正在编写一个语法上的 SQL 表达式，而不是一个字面值。因此，我们必须包含所需的引号或语法，以便渲染我们想要看到的 SQL。 ### 选择 ORM 实体和列

ORM 实体，如我们的User类以及其上的列映射属性，如User.name，也参与 SQL 表达式语言系统，表示表和列。下面演示了从User实体中选择的示例，这最终呈现的方式与直接使用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
[...]  ()
>>> user
User(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')

这些方法也可以混合使用，如下所示，我们选择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 表达式时，这可能会有所帮助：

>>> from sqlalchemy import func, cast
>>> stmt = select(
...     ("Username: " + user_table.c.name).label("username"),
... ).order_by(user_table.c.name)
>>> with engine.connect() as conn:
...     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: patrick
Username: sandy
Username: spongebob
ROLLBACK

另请参阅

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

使用文本列表达式进行选择

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

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

>>> from sqlalchemy import text
>>> stmt = select(text("'some phrase'"), user_table.c.name).order_by(user_table.c.name)
>>> with engine.connect() as conn:
...     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，而是明确表示一个“列”，然后可以在子查询和其他表达式中进行标记和引用：

>>> from sqlalchemy import literal_column
>>> stmt = select(literal_column("'some phrase'").label("p"), user_table.c.name).order_by(
...     user_table.c.name
... )
>>> with engine.connect() as conn:
...     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, patrick
some phrase, sandy
some phrase, spongebob
ROLLBACK

注意，在使用 text() 或 literal_column() 时，我们正在编写一种句法 SQL 表达式，而不是文字值。因此，我们必须包括所需的引号或语法以获取我们想要看到的 SQL。

WHERE 子句

SQLAlchemy 允许我们通过使用 Column 和类似对象结合标准 Python 运算符来组合 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 实体方面的示例如下所示：

>>> from sqlalchemy import and_, 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 子句和 JOINs

如前所述，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(*) 中选择，我们使用 SQLAlchemy 元素 sqlalchemy.sql.expression.func 来生成 SQL count() 函数：

>>> from sqlalchemy import func
>>> 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 子句。

如果连接的左右目标没有这样的约束，或者有多个约束存在，我们需要直接指定 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()构造，就像在声明映射类的上一节中设置的映射一样。这是一个单独的主题，详细介绍在使用关系连接。

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

设置 ON 子句

之前的 JOIN 示例说明了Select构造可以在两个表之间进行连接并自动产生 ON 子句。这在那些示例中发生是因为user_table和address_table的Table对象包括一个单一的ForeignKeyConstraint定义，该定义用于形成这个 ON 子句。

如果连接的左右目标没有这样的约束，或者存在多个约束，我们需要直接指定 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 实体并使用 relationship() 构造时，还有另一种生成 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

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

提示

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 函数的名称时，它将创建Function的新实例，该函数可以具有任何名称，以及零个或多个要传递给函数的参数，这些参数像所有其他情况一样是 SQL 表达式构造。例如，要针对user_account.id列渲染 SQL COUNT() 函数，我们调用count()名称：

>>> from sqlalchemy import func
>>> 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() 方法的这两个子句。下面我们演示选择用户姓名字段以及地址数量，对于那些拥有多个地址的用户：

>>> with engine.connect() as conn:
...     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 BY 子句中重新说明该表达式，而是使用 COLUMNS 子句中的列名或标签名。通过将名称的字符串文本传递给`Select.order_by()` 或 `Select.group_by()` 方法来实现此形式。传递的文本**不会直接呈现**；而是在上下文中以该表达式名称的形式呈现，并在没有找到匹配项时引发错误。这种形式还可以使用一元修饰符`asc()` 和 `desc()`。
```py
>>> from sqlalchemy import func, 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 
```### 按顺序排列
`ORDER BY` 子句是根据通常基于`Column` 或类似对象的 SQL 表达式构造的。 `Select.order_by()` 方法按位置接受一个或多个这些表达式：
```py
>>> 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 函数的名称时，它将创建Function的新实例，该函数可以具有任何名称，以及零个或多个要传递给函数的参数，就像在所有其他情况下一样，是 SQL 表达式构造。例如，要针对user_account.id列渲染 SQL COUNT()函数，我们调用count()名称：

>>> from sqlalchemy import func
>>> 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()方法。下面我们展示选择用户名称字段以及地址计数，对于那些拥有多个地址的用户：

>>> with engine.connect() as conn:
...     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 BY，而无需在 ORDER BY 或 GROUP BY 子句中重新声明表达式，而是使用 COLUMNS 子句中的列名或标记名称。通过将名称的字符串文本传递给Select.order_by()或Select.group_by()方法来实现这种形式。传递的文本不会直接呈现；相反，在列子句中给定表达式的名称，并在上下文中呈现为该表达式名称，如果找不到匹配项，则会引发错误。一元修饰符asc()和desc()也可以在此形式中使用：

>>> from sqlalchemy import func, 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的对象表示的，它们构成了 Core 中的Alias构造，该构造使用FromClause.alias()方法构建。Alias构造就像Table构造一样，它也有一个Column对象的命名空间，位于Alias.c集合中。下面的 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 等实体。这将在内部生成一个针对原始映射Table对象的Alias对象，同时保持 ORM 功能。下面的 SELECT 从 User 实体中选择所有包含两个特定电子邮件地址的对象：

>>> from sqlalchemy.orm import aliased
>>> 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()。 ### ORM 实体别名

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

>>> from sqlalchemy.orm import aliased
>>> 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() 进行演示的。

子查询和公共表达式

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

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

SQLAlchemy 使用 Subquery 对象来表示子查询，使用 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 子句。

通用表达式（CTEs）

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 实例的对象标识充当对象的语法标识。在 SQL 中将要呈现的名称可以通过将其作为 Select.subquery() 或 Select.cte() 方法的第一个参数传递来提供。

另见

Select.subquery() - 关于子查询的进一步细节

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

SqlAlchemy 2.0 中文文档（二）（4）https://developer.aliyun.com/article/1560331

SqlAlchemy 2.0 中文文档（二）（3）

设置 COLUMNS 和 FROM 子句

选择 ORM 实体和列

从带标签的 SQL 表达式中选择

使用文本列表达式进行选择

从带标签的 SQL 表达式中进行选择

使用文本列表达式进行选择

WHERE 子句

明确的 FROM 子句和 JOINs

设置 ON 子句

OUTER 和 FULL 连接

设置 ON 子句

OUTER 和 FULL 连接

ORDER BY, GROUP BY, HAVING

ORDER BY

带有 GROUP BY / HAVING 的聚合函数

按标签排序或分组

使用别名

ORM 实体别名

子查询和公共表达式

通用表达式（CTEs）

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

SqlAlchemy 2.0 中文文档（二）（3）

设置 COLUMNS 和 FROM 子句

选择 ORM 实体和列

从带标签的 SQL 表达式中选择

使用文本列表达式进行选择

从带标签的 SQL 表达式中进行选择

使用文本列表达式进行选择

WHERE 子句

明确的 FROM 子句和 JOINs

设置 ON 子句

OUTER 和 FULL 连接

设置 ON 子句

OUTER 和 FULL 连接

ORDER BY, GROUP BY, HAVING

ORDER BY

带有 GROUP BY / HAVING 的聚合函数

按标签排序或分组

使用别名

ORM 实体别名

子查询和公共表达式

通用表达式（CTEs）

热门文章

最新文章

相关电子书