SqlAlchemy 2.0 中文文档（二）（4）https://developer.aliyun.com/article/1560331
高级 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) ... ) >>> with engine.connect() as conn: ... 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) ... ) >>> with engine.connect() as conn: ... 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 语法与“ordered set”或“假设性集合”聚合函数一起使用。常见的“ordered set”函数包括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) ... ) >>> with engine.connect() as conn: ... 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 和 SQL Server 的支持。 另请参阅 表值、表值和列值函数、行和元组对象 - 在 PostgreSQL 文档中。 虽然许多数据库支持表值函数和其他特殊形式，但 PostgreSQL 往往是对这些功能需求最多的地方。有关 PostgreSQL 语法的其他示例以及其他功能，请参见本节。 SQLAlchemy 提供了 `FunctionElement.table_valued()` 方法作为基本的“表值函数”构造，它将一个 `func` 对象转换为一个包含一系列命名列的 FROM 子句，这些命名是按位置传递的字符串名称。这将返回一个 `TableValuedAlias` 对象，它是一个启用函数的 `Alias` 构造，可以像其他 FROM 子句一样使用，如 Using Aliases 中介绍的那样。下面我们演示了 `json_each()` 函数，虽然它在 PostgreSQL 上很常见，但也被现代版本的 SQLite 支持： ```py >>> onetwothree = func.json_each('["one", "two", "three"]').table_valued("value") >>> stmt = select(onetwothree).where(onetwothree.c.value.in_(["two", "three"])) >>> with engine.connect() as conn: ... 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 支持的一种特殊语法是在 FROM 子句中引用函数，然后在 SELECT 语句或其他列表达式上下文中将其自身作为单个列传递。PostgreSQL 在诸如json_array_elements()、json_object_keys()、json_each_text()、json_each()等函数中广泛使用此语法。
SQLAlchemy 将此称为“列值”函数，并通过将FunctionElement.column_valued()修饰符应用于Function构造来使用：
>>> from sqlalchemy import select, 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 方言支持，可用于自定义 SQL 函数：
>>> from sqlalchemy.dialects import oracle >>> 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 返回类型”，指的是在数据库端 SQL 表达式的上下文中函数返回的 SQL 值的类型，而不是 Python 函数的“返回类型”。
任何 SQL 函数的 SQL 返回类型可以通过引用Function.type属性来访问，通常用于调试目的：
>>> func.now().type DateTime()
当在更大表达式的上下文中使用函数表达式时，这些 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()
- vs.:
>>> 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) ... ) >>> with engine.connect() as conn: ... 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) ... ) >>> with engine.connect() as conn: ... 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) ... ) >>> with engine.connect() as conn: ... 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 和数组导向的函数以及诸如 `generate_series()` 等函数，表值函数在 FROM 子句中指定，然后被引用为表，有时甚至被引用为列。这种形式的函数在 PostgreSQL 数据库中非常突出，但某些形式的表值函数也受到 SQLite、Oracle 和 SQL Server 的支持。 另请参阅 表值、表和列值函数、行和元组对象 - 在 PostgreSQL 文档中。 虽然许多数据库支持表值和其他特殊形式，但 PostgreSQL 往往是这些特性需求最大的地方。有关 PostgreSQL 语法的其他示例以及其他功能，请参阅本节。 SQLAlchemy 提供了 `FunctionElement.table_valued()` 方法作为基本的“表值函数”构造，它将 `func` 对象转换为包含一系列命名列的 FROM 子句，基于传递的字符串名称位置。这将返回一个 `TableValuedAlias` 对象，这是一个启用了函数的 `Alias` 构造，可以像介绍中的使用别名那样用作任何其他 FROM 子句。下面我们举例说明 `json_each()` 函数，尽管在 PostgreSQL 上很常见，但现代版本的 SQLite 也支持它： ```py >>> onetwothree = func.json_each('["one", "two", "three"]').table_valued("value") >>> stmt = select(onetwothree).where(onetwothree.c.value.in_(["two", "three"])) >>> with engine.connect() as conn: ... 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 支持的一种特殊语法是在 FROM 子句中引用函数，然后将其自身作为单个列提供给 SELECT 语句或其他列表达式上下文中。 PostgreSQL 非常善于使用此语法，用于诸如json_array_elements()、json_object_keys()、json_each_text()、json_each()等函数。
SQLAlchemy 将此称为“列值”函数，并通过将FunctionElement.column_valued()修改器应用于Function构造来使用：
>>> from sqlalchemy import select, 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 方言支持，可用于自定义 SQL 函数：
>>> from sqlalchemy.dialects import oracle >>> 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 聚合函数的特殊用法，它计算在处理单个结果行时返回的行中的聚合值。而像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) ... ) >>> with engine.connect() as conn: ... 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) ... ) >>> with engine.connect() as conn: ... 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) ... ) >>> with engine.connect() as conn: ... 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 和 SQL Server 支持。
另请参见
Table values, Table and Column valued functions, Row and Tuple objects - 在 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"])) >>> with engine.connect() as conn: ... 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 支持的一个特殊语法是在 FROM 子句中引用函数，然后在 SELECT 语句或其他列表达式上下文的列子句中将其自身作为单列传递。PostgreSQL 在诸如 json_array_elements()、json_object_keys()、json_each_text()、json_each() 等函数中广泛使用此语法。
SQLAlchemy 将此称为“列值”函数，并可通过将 FunctionElement.column_valued() 修饰符应用于 Function 构造来使用：
>>> from sqlalchemy import select, 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 方言也支持“列值”形式，可用于自定义 SQL 函数：
>>> from sqlalchemy.dialects import oracle >>> 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)：
>>> from sqlalchemy import cast >>> stmt = select(cast(user_table.c.id, String)) >>> with engine.connect() as conn: ... 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()转换为JSON的字符串表达式将获得 JSON 下标和比较运算符，例如：
>>> from sqlalchemy import JSON >>> print(cast("{'a': 'b'}", JSON)["a"]) CAST(:param_1 AS JSON)[:param_2]
type_coerce() - 一个仅限 Python 的“转换”
有时候，需要让 SQLAlchemy 知道表达式的数据类型，出于上述所有原因，但不要在 SQL 端渲染 CAST 表达式本身，因为它可能会干扰已经在没有它的情况下正常工作的 SQL 操作。对于这种相当常见的用例，还有另一个函数type_coerce()，它与cast()密切相关，它设置一个 Python 表达式为具有特定 SQL 数据库类型，但不会在数据库端渲染 CAST 关键字或数据类型。当处理JSON数据类型时，type_coerce()特别重要，它通常与不同平台上的面向字符串的数据类型有着错综复杂的关系，甚至可能不是一个显式的数据类型，比如在 SQLite 和 MariaDB 上。下面，我们使用type_coerce()将 Python 结构传递为 JSON 字符串到 MySQL 的 JSON 函数之一：
>>> import json >>> from sqlalchemy import JSON >>> from sqlalchemy import type_coerce >>> from sqlalchemy.dialects import mysql >>> 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"'）被渲染。
type_coerce() - 一个仅限 Python 的“强制转换”
有时候，需要让 SQLAlchemy 了解表达式的数据类型，出于上述所有原因，但不要在 SQL 端渲染 CAST 表达式本身，因为这可能会干扰已经可以在没有它的情况下工作的 SQL 操作。对于这种相当常见的用例，有另一个函数type_coerce()，它与cast()密切相关，它设置一个 Python 表达式为具有特定 SQL 数据库类型，但不在数据库端渲染 CAST 关键字或数据类型。当处理JSON数据类型时，type_coerce()尤为重要，它通常与不同平台上的字符串导向数据类型有复杂的关系，甚至可能不是显式数据类型，例如在 SQLite 和 MariaDB 上。在下面的例子中，我们使用type_coerce()将 Python 结构传递为 JSON 字符串到 MySQL 的一个 JSON 函数中：
>>> import json >>> from sqlalchemy import JSON >>> from sqlalchemy import type_coerce >>> from sqlalchemy.dialects import mysql >>> 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"'）被渲染。
cle 支持的一个特殊语法是在 FROM 子句中引用函数，然后在 SELECT 语句或其他列表达式上下文的列子句中将其自身作为单列传递。PostgreSQL 在诸如 json_array_elements()、json_object_keys()、json_each_text()、json_each() 等函数中广泛使用此语法。
SQLAlchemy 将此称为“列值”函数，并可通过将 FunctionElement.column_valued() 修饰符应用于 Function 构造来使用：
>>> from sqlalchemy import select, 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 方言也支持“列值”形式，可用于自定义 SQL 函数：
>>> from sqlalchemy.dialects import oracle >>> 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)：
>>> from sqlalchemy import cast >>> stmt = select(cast(user_table.c.id, String)) >>> with engine.connect() as conn: ... 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()转换为JSON的字符串表达式将获得 JSON 下标和比较运算符，例如：
>>> from sqlalchemy import JSON >>> print(cast("{'a': 'b'}", JSON)["a"]) CAST(:param_1 AS JSON)[:param_2]
type_coerce() - 一个仅限 Python 的“转换”
有时候，需要让 SQLAlchemy 知道表达式的数据类型，出于上述所有原因，但不要在 SQL 端渲染 CAST 表达式本身，因为它可能会干扰已经在没有它的情况下正常工作的 SQL 操作。对于这种相当常见的用例，还有另一个函数type_coerce()，它与cast()密切相关，它设置一个 Python 表达式为具有特定 SQL 数据库类型，但不会在数据库端渲染 CAST 关键字或数据类型。当处理JSON数据类型时，type_coerce()特别重要，它通常与不同平台上的面向字符串的数据类型有着错综复杂的关系，甚至可能不是一个显式的数据类型，比如在 SQLite 和 MariaDB 上。下面，我们使用type_coerce()将 Python 结构传递为 JSON 字符串到 MySQL 的 JSON 函数之一：
>>> import json >>> from sqlalchemy import JSON >>> from sqlalchemy import type_coerce >>> from sqlalchemy.dialects import mysql >>> 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"'）被渲染。
type_coerce() - 一个仅限 Python 的“强制转换”
有时候，需要让 SQLAlchemy 了解表达式的数据类型，出于上述所有原因，但不要在 SQL 端渲染 CAST 表达式本身，因为这可能会干扰已经可以在没有它的情况下工作的 SQL 操作。对于这种相当常见的用例，有另一个函数type_coerce()，它与cast()密切相关，它设置一个 Python 表达式为具有特定 SQL 数据库类型，但不在数据库端渲染 CAST 关键字或数据类型。当处理JSON数据类型时，type_coerce()尤为重要，它通常与不同平台上的字符串导向数据类型有复杂的关系，甚至可能不是显式数据类型，例如在 SQLite 和 MariaDB 上。在下面的例子中，我们使用type_coerce()将 Python 结构传递为 JSON 字符串到 MySQL 的一个 JSON 函数中：
>>> import json >>> from sqlalchemy import JSON >>> from sqlalchemy import type_coerce >>> from sqlalchemy.dialects import mysql >>> 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"'）被渲染。
