SQLAlchemy Column Elements and ExpressionsSQL 表达式 API 完全指南【免费下载链接】sqlalchemyThe Database Toolkit for Python项目地址: https://gitcode.com/gh_mirrors/sq/sqlalchemy本篇技术指南以 SQLAlchemy 官方文档 sqlelement.rst 为骨架系统讲解表达式 APIExpression API的类层次、全部构造器函数、核心列表达式类与类型检查工具并结合当前仓库源码lib/sqlalchemy/sql/_elements_constructors.py、lib/sqlalchemy/sql/elements.py与测试用例深入每一处构造器的底层实现与典型用法。读完本文你将掌握从bindparam、case、cast到over、within_group的完整列表达式工具箱能够在 SELECT、WHERE、ORDER BY、窗口函数等场景中熟练组装 SQL 语句。表达式 API 概览从 ClauseElement 到 ColumnElementSQLAlchemy 的表达式 APIExpression API由一系列类组成每一个类都对应 SQL 字符串中的一个特定词法元素。将这些类组合成更大的结构后就形成了一条语句构造体statement construct它可以被编译compiled成能直接传给数据库的字符串表示。这些类被组织成一条以最基础类 ClauseElement 为根的分层结构文档原文见 sqlelement.rst 开头部分。两个关键子类分别是ColumnElement代表任何基于列的 SQL 表达式角色例如 SELECT 的 columns 子句、WHERE 子句以及 ORDER BY 子句中的列表达式。它在 lib/sqlalchemy/sql/elements.py 中定义是Column、SQL 函数、绑定参数、字面量表达式、NULL关键字等一切表达式单元的终极基类。FromClause代表放置在 SELECT 语句 FROM 子句中的令牌token例如Table、Subquery等。从源码看ColumnElement继承了十余个角色类roles.ColumnArgumentOrKeyRole、roles.WhereHavingRole、roles.OrderByRole等这正是 SQLAlchemy 的角色强制转换coercion机制的来源任何接受SQL 表达式作为参数的函数都遵循如下强制转换规则普通 Python 字面量字符串、整数、浮点数、布尔、日期时间、Decimal等会被强制转换为字面量绑定值即生成一个内嵌该值的 BindParameter它是ColumnElement的子类最终在执行时作为参数化值传给 DBAPI。任何带有__clause_element__()访问器的特殊对象典型如 ORM 层构造会被转换为 Core 表达式对象ORM 映射类与映射属性正是通过它进入 Core 世界的。Python 的None通常被解释为NULL在 Core 中产生 null() 的实例。此外ColumnElement重载了 Python 运算符、!、、等以模拟 SQL 操作。例如两个ColumnClause对象用相加会产生一个 BinaryExpression from sqlalchemy.sql import column column(a) column(b) sqlalchemy.sql.expression.BinaryExpression object at ... print(column(a) column(b)) a bBinaryExpression与ColumnClause都是ColumnElement的子类这种Python 运算符即 SQL 运算符的设计是整条表达式链路的根基。列表达式的基础构造器Foundational Constructors以下独立函数都可以直接从sqlalchemy命名空间导入用于搭建 SQLAlchemy 表达式语言构造。它们的实现集中在 lib/sqlalchemy/sql/_elements_constructors.py。bindparam绑定参数bindparam() 返回一个 BindParameter 实例——一个表示 SQL 表达式中占位符的ColumnElement其值在语句真正对数据库连接执行时才被提供。它不仅能充当等待填充的占位符还能携带最终要在表达式阶段使用的实际值从而表示那些不安全、不应直接渲染进 SQL 语句、而应作为值传给 DBAPI 做正确转义与类型安全处理的数值。典型使用方式是显式延迟提供参数from sqlalchemy import bindparam stmt select(users_table).where( users_table.c.name bindparam(username) ) # 渲染为SELECT id, name FROM user WHERE name :username result connection.execute(stmt, {username: wendy})显式使用bindparam的常见场景是构造需要多次调用、且每次 WHERE 条件变化的 UPDATE/DELETE 语句stmt ( users_table.update() .where(user_table.c.name bindparam(username)) .values(fullnamebindparam(fullname)) ) connection.execute( stmt, [ {username: wendy, fullname: Wendy Smith}, {username: jack, fullname: Jack Jones}, ], )更常见的是 Core 表达式系统会隐式地广泛使用bindparam几乎所有 SQL 表达式函数收到的 Python 字面量都会被强制转换为固定的bindparam构造。例如users_table.c.name Wendy会产生一个BinaryExpression其右侧就是一个代表字面量的BindParameterprint(repr(expr.right)) # BindParameter(%(4327771088 name)s, Wendy, type_String())最终渲染出的 SQL 类似user.name :name_1真正的字符串Wendy并不出现在渲染串中而是被携带到语句执行阶段。bindparam的完整参数均来自源码 docstring见 lib/sqlalchemy/sql/_elements_constructors.py参数默认值说明key必填绑定参数的名称用于生成 SQL 中的命名占位符编译期间若与其他同名BindParameter冲突或长度超限会被修改。省略时生成匿名名称valueNone参数的初始值执行时若没有为该参数名提供其他值则作为传给 DBAPI 的值type_None可选TypeEngine不传时根据值自动推断如str/int/bool映射到String/Integer/Boolean。类型会在值传给数据库前做预处理如 SQLite 上的日期时间字符串化uniqueFalse为True时若表达式中已存在同名参数则修改本参数的键名。通常用于内部生成匿名绑定表达式required自动为True时执行时必须提供值未显式指定时若未传value和callable_则默认为True否则默认为FalsequoteNone为True时该参数名需要加引号且当前未知为保留字目前仅适用于 Oracle 后端callable_None代替value的可调用对象在语句执行时调用以确定最终值适用于创建构造时无法确定绑定值的场景expandingFalse为True时作为展开参数处理值应为序列而非标量SQL 语句按每次执行进行变换为序列开辟可变数量的参数槽从而允许IN子句使用语句缓存。注意不支持executemany风格参数集isoutparamFalse为True时按存储过程OUT参数处理适用于 Oracle 等支持 OUT 参数的后端literal_executeFalse为True时编译阶段渲染特殊POSTCOMPILE令牌语句执行时把参数最终值直接渲染进 SQL 而非放进参数字典。主要用于 LIMIT/OFFSET 这类 DBAPI 驱动无法容纳绑定参数的场景同时保持构造在编译层可缓存1.4 新增and_ / or_ / not_逻辑组合and_() 生成由AND连接的合取表达式from sqlalchemy import and_ stmt select(users_table).where( and_(users_table.c.name wendy, users_table.c.enrolled True) )and_也可用 Python运算符表达注意复合表达式需加括号以符合 Python 运算符优先级stmt select(users_table).where( (users_table.c.name wendy) (users_table.c.enrolled True) )and_在部分场景下是隐式的例如对Select.where连续调用多次每次的语句会被and_合并。一个重要的实践细节and_至少要传一个位置参数才算合法零参数的and_是歧义的1.4 起已弃用。要生成空的或动态拼接的and_表达式应以true()或直接True作为默认元素from sqlalchemy import true criteria and_(true(), *expressions)当没有其他表达式时上述表达式在后端上编译为true或1 1有表达式时true()会被忽略因为它不影响含其他元素的 AND 结果。对应的 or_() 语义完全对称只是以OR连接、以|运算符表达、默认元素使用false()或False——动态拼接的空or_应写成or_(false(), *expressions)。not_() 返回给定子句的否定即NOT(clause)。~运算符在所有ColumnElement子类上也被重载以产生相同结果。从源码看其实现为coercions.expect(roles.ExpressionElementRole, clause).__invert__()先强制转换参数再取反。caseCASE 条件表达式case() 产生一个CASE表达式返回 Case 实例。SQL 中的CASE构造类似于其他语言中的if/then。常规形式是传入一系列 (条件, 结果) 二元组from sqlalchemy import case stmt select(users_table).where( case( (users_table.c.name wendy, W), (users_table.c.name jack, J), else_E, ) )渲染结果类似SELECT id, name FROM user WHERE CASE WHEN (name :name_1) THEN :param_1 WHEN (name :name_2) THEN :param_2 ELSE :param_3 END当需要对同一父列做多个等值比较时case还提供简写格式通过value参数传入被比较的列表达式whens以字典形式传入候选值 → 结果值映射stmt select(users_table).where( case({wendy: W, jack: J}, valueusers_table.c.name, else_E) )whens中的结果值与else_值会从 Python 字面量强制转换为bindparam构造如果想渲染成内联常量表达式则应使用 literal_column()from sqlalchemy import case, literal_column case( (orderline.c.qty 100, literal_column(greaterthan100)), (orderline.c.qty 10, literal_column(greaterthan10)), else_literal_column(lessthan10), )case参数说明见源码 lib/sqlalchemy/sql/_elements_constructors.py*whens两种形式。其一为多个位置参数二元组(SQL 表达式, 结果值)1.4 起支持位置传参其二为 Python 字典比较值 → 结果值此形式必须配合value使用比较采用运算符。value可选的 SQL 表达式作为字典候选值的固定比较点。else_所有when表达式都不为真时的结果表达式省略时多数数据库返回 NULL。cast / try_cast / type_coerce类型转换三兄弟cast() 产生一个CAST表达式返回 Cast 实例并承担两个职能其一在生成的 SQL 中渲染CAST其二在 Python 侧把给定类型TypeEngine类或实例关联到列表达式上使表达式获得该类型对应的运算符行为、绑定值处理与结果行处理行为from sqlalchemy import cast, Numeric stmt select(cast(product_table.c.unit_price, Numeric(10, 4))) # SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM producttry_cast() 为支持的后端产生TRY_CAST表达式——一种对不可转换值返回 NULL 的CAST。在当前仓库中该构造仅由 SQL Server 方言支持用于其他内置后端会抛出CompileError第三方后端亦可支持。它既可以从sqlalchemy导入也可以从sqlalchemy.dialects.mssql导入from sqlalchemy import select, try_cast, Numeric stmt select(try_cast(product_table.c.unit_price, Numeric(10, 4))) # 在 SQL Server 上渲染为 # SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4)) FROM product_tabletype_coerce() 是cast的替代方案它只把表达式与特定类型关联不渲染CAST。例如把字符串日期列的类型处理器应用到结果行上from sqlalchemy import type_coerce stmt select(type_coerce(log_table.date_string, StringDateTime())) # SELECT date_string AS date_string FROM log需要注意type_coerce不渲染任何自身 SQL 语法不隐含加括号。在运算符上下文中若需要 CAST 通常自带的括号须显式调用.self_group() some_integer column(someint, Integer) some_string column(somestr, String) expr type_coerce(some_integer 5, String) some_string print(expr) someint :someint_1 || somestr expr type_coerce(some_integer 5, String).self_group() some_string print(expr) (someint :someint_1) || somestrcolumn轻量级列表达式column() 产生 ColumnClause 对象它是schema.Column的轻量级类比仅凭名称即可创建from sqlalchemy import column id, name column(id), column(name) stmt select(id, name).select_from(user) # SELECT id, name FROM usercolumn处理的文本被假定为数据库列名若字符串包含混合大小写、特殊字符或匹配目标后端的保留字列表达式将按后端确定的引号行为渲染。要产生完全不做任何引号处理的文本 SQL 表达式应改用literal_column或将is_literalTrue传给column。完整的 SQL 语句则最好交给 text()。column还可以与轻量级的table函数组合以极少的样板代码搭建一个可用的表构造from sqlalchemy import table, column, select user table( user, column(id), column(name), column(description), ) stmt select(user.c.description).where(user.c.name wendy)这种column/table构造是临时性的不与任何MetaData、DDL 或事件关联这一点与schema.Table不同。column的参数包括text元素文本、type关联的类型与is_literal为True时不应用任何引号规则直接输出。literal_column()本质上就是以is_literalTrue调用column。text / tstring纯文本与模板字符串 SQLtext() 直接构造一个 TextClause表示一段文本 SQL 字符串。相比裸字符串它提供后端无关的绑定参数支持、按语句执行的执行选项、绑定参数与结果列的类型化行为from sqlalchemy import text t text(SELECT * FROM users) result connection.execute(t)绑定参数按名称用:name格式指定t text(SELECT * FROM users WHERE id:user_id) result connection.execute(t, {user_id: 12})若 SQL 语句中需要按字面含义出现冒号例如内联字符串中使用反斜杠转义text(rSELECT * FROM users WHERE name\:username)。TextClause还提供bindparams()与columns()方法分别用于指定绑定参数细节与返回值列的名称和类型t ( text(SELECT * FROM users WHERE id:user_id) .bindparams(user_id7) .columns(idInteger, nameString) ) for id, name in connection.execute(t): print(id, name)text既可用于在更大的查询中指定片段如 SELECT 的 WHERE 子句也可用于构造完整独立的语句Executable对象可直接传给.execute()。tstring() 是 2.1 新增的构造仓库中还配套了 test/sql/test_tstrings_py314.py 与 test/orm/test_tstring_py314.py 两组测试用于构造 TString 子句——一种使用 Python 3.14 t-string模板字符串的 SQL 模板。它自动处理 Python 值与 SQLAlchemy 表达式的插值无需像text那样手动指定绑定参数from sqlalchemy import tstring a 5 b 10 stmt tstring(tselect {a}, {b}) result connection.execute(stmt)插值规则为模板的纯字符串部分直接渲染为 SQLSQLAlchemy 表达式列、函数等作为子句元素嵌入普通 Python 值自动包装为literal()。例如# Python 值成为绑定参数 user_id 42 stmt tstring(tSELECT * FROM users WHERE id {user_id}) # 渲染为SELECT * FROM users WHERE id :param_1 # SQLAlchemy 表达式被嵌入 stmt tstring(tSELECT {column(q)} FROM {table(t)}) # 渲染为SELECT q FROM t # 用 literal() 为绑定值施加显式 SQL 类型 some_json {foo: bar} stmt tstring(tSELECT {literal(some_json, JSON)})与text一样tstring也支持通过columns()方法指定返回列及类型from sqlalchemy import tstring, column, Integer, String stmt tstring(tSELECT id, name FROM users).columns( column(id, Integer), column(name, String) )测试文件 test/sql/test_tstrings_py314.py 覆盖了字面量插值、列插值、算术表达式、子查询嵌套执行、JSON 字面量执行、语句缓存键等行为是深入理解tstring边界的最佳参考。其他基础构造器literal()返回绑定到绑定参数上的字面量子句。当非ClauseElement对象字符串、整数、日期等与ColumnElement子类如schema.Column比较时SQLAlchemy 会自动创建字面量子句此函数用于强制生成字面量子句。参数value为任意 Python 对象type_为可选的TypeEngineliteral_execute2.0 新增为True时引擎会在执行时尝试把绑定值直接渲染进 SQL 而非作为参数值。literal_column()产生一个照原样渲染、不做引号处理的文本列表达式。extract()返回 Extract 构造通常以extract以及func.extract形式使用。参数field作为字面 SQL 字符串使用切勿向该字符串传入不可信输入expr为EXTRACT表达式的右侧。例如select(...).where(extract(YEAR, logged_table.c.date_created) 2021)。distinct()对单个列表达式施加一元DISTINCT典型用于聚合函数内部如select(users_table.c.id, func.count(distinct(users_table.c.name)))渲染为SELECT user.id, count(DISTINCT user.name) FROM user。注意它与Select.distinct()不同——后者对整个结果集施加SELECT DISTINCT。null()、true()、false()返回常量Null、True_、False_构造。不支持 true/false 常量的后端会渲染成针对 1 或 0 的表达式如WHERE 0 1。true()/false()在and_/or_合取中具备短路行为or_(t.c.x 5, true())渲染为WHERE trueand_(t.c.x 5, false())渲染为WHERE false。bitwise_not()产生一元按位 NOT 子句2.0.2 新增通常通过~运算符触发注意与布尔否定not_区分。outparam()为支持 OUT 参数的数据库如 Oracle创建存储过程 OUT 参数。输出值可从CursorResult.out_parameters属性取得。from_dml_column()2.1 新增的占位符可在编译后的 INSERT/UPDATE 表达式中引用应用到另一列上的 SQL 表达式或值允许自动复制赋给其他列的表达式。典型用于 ORM hybrid 等基于事件的钩子参见仓库中 test/ext/test_hybrid.py 的使用 stmt t.insert().values(xfunc.foobar(3), yfrom_dml_column(t.c.x) 5) print(stmt) INSERT INTO t (x, y) VALUES (foobar(:foobar_1), (foobar(:foobar_1) :param_1))lambda_stmt()从 SQLAlchemy 1.4 起支持的延迟求值语句构造它把语句生成逻辑封装进 Python lambda仅在必要时才真正编译从而在允许语句变化的同时保持构造级缓存。列表达式的修饰构造器Modifier Constructors文档中列出的这一组函数更常见的调用方式是通过任何ColumnElement构造上的方法。例如 label() 通常通过ColumnElement.label()方法调用。它们多数在源码中采用若参数是ColumnOperators则转调其同名方法否则创建底层表达式的实现模式。函数对应方法说明与示例渲染asc()ColumnElement.asc()升序ORDER BY如order_by(asc(users_table.c.name))→ORDER BY name ASCdesc()ColumnElement.desc()降序ORDER BY→ORDER BY name DESCnulls_first()ColumnElement.nulls_first()修饰asc/desc的NULLS FIRST如order_by(nulls_first(desc(users_table.c.name)))→ORDER BY name DESC NULLS FIRST。1.4 起由nullsfirst更名旧名仍保留2.0.5 恢复了缺失的旧符号nulls_last()ColumnElement.nulls_last()NULLS LAST修饰旧名nullslast同样保留between()ColumnElement.between()BETWEEN谓词between(users_table.c.id, 5, 7)→WHERE id BETWEEN :id_1 AND :id_2。三个参数含左侧列都会从 Python 标量强制转换因此between(5, 3, 7)也合法。symmetricTrue时渲染BETWEEN SYMMETRIC并非所有数据库支持collate()ColumnElement.collate()渲染expression COLLATE collation如collate(mycolumn, utf8_bin)。collation_schema2.1 新增用于支持 schema 限定的排序规则目前为 PostgreSQL排序规则名若为大小写敏感标识符含大写字符会被加引号label()ColumnElement.label()改变 SELECT columns 子句中元素的名称通常通过AS关键字返回Label对象参数为name与elementall_() / any_()ColumnElement.all_()/ColumnElement.any_()产生 ALL/ANY 表达式。PostgreSQL 上作用于ARRAY类型MySQL 上可作用于子查询5 all_(mytable.c.somearray)→5 ALL (somearray)。与 Python 序列比较时先用literal()包装是否可适配数组参数取决于 DBAPI。它们具有操作数翻转行为all_(mytable.c.column) 5会渲染5 ALL (column)与None比较时不会像常规那样渲染ISover()FunctionElement.over()产生Over对象窗口函数详见下文within_group()FunctionElement.within_group()产生WithinGroup对象有序集合聚合详见下文funcfilter()FunctionElement.filter()对聚合与窗口函数产生FunctionFilterfuncfilter(func.count(1), MyClass.name some name)→COUNT(1) FILTER (WHERE myclass.name some name)aggregate_order_by()FunctionElement.aggregate_order_by()产生AggregateOrderBy对象用于array_agg、group_concat、json_agg等在 PostgreSQL/MySQL/MariaDB/SQLite 上支持内嵌ORDER BY参数的聚合函数。在 Oracle 与 SQL Server 上编译使用WithinGroup形式窗口函数与有序聚合over / within_group / aggregate_order_byover() 通常通过FunctionElement.over()调用func.row_number().over(order_bymytable.c.some_column) # ROW_NUMBER() OVER(ORDER BY some_column)窗口范围可通过range_、rows、groups三个互斥参数指定每个参数接受一个由整数与None组成的二元组None表示unbounded无界0表示current row当前行负整数表示preceding前驱正整数表示following后继func.row_number().over(order_bymy_table.c.some_column, range_(None, 0)) # ROW_NUMBER() OVER(ORDER BY some_column # RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) func.row_number().over(order_byx, range_(-5, 10)) # RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING func.row_number().over(order_byx, rows(None, 0)) # ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW func.row_number().over(order_byx, range_(-2, None)) # RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING func.row_number().over(order_byx, range_(1, 3)) # RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING func.row_number().over(order_byx, groups(1, 3)) # GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWINGover的完整参数为elementFunctionElement、WithinGroup或其他兼容构造、partition_by/order_by列元素、字符串或此类列表、range_/rows/groups互斥的窗口框架、exclude2.1 新增取值CURRENT ROW、GROUP、TIES或NO OTHERS渲染窗口框架内的 EXCLUDE 子句且要求必须同时指定rows/range_/groups之一。当排序列类型不是整数时可改用 FrameClause 直接指定框架边界2.1 起支持非整数范围类型from datetime import timedelta from sqlalchemy import FrameClause, FrameClauseType func.sum(my_table.c.amount).over( order_bymy_table.c.date, range_FrameClause( starttimedelta(days7), endNone, start_frame_typeFrameClauseType.PRECEDING, end_frame_typeFrameClauseType.UNBOUNDED, ), )within_group() 用于有序集合聚合与假设集合聚合函数percentile_cont、rank、dense_rank等这类特性通常由 Oracle、Microsoft SQL Server 使用stmt select( func.percentile_cont(0.5).within_group(department.c.salary.desc()), ) # SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY department.salary DESC)对于 PostgreSQL、MySQL/MariaDB、SQLite 以及 Oracle 和 SQL Server 上聚合函数的一般性 ORDER BYaggregate_order_by() 提供了更通用的途径它只在需要WITHIN GROUP的后端Oracle、SQL Server上编译为该形式在 PostgreSQL 上则固定编译为内嵌ORDER BY。2.1 起该函数从 PostgreSQL 专用函数泛化为Function上的后端无关方法stmt select( func.array_agg(department.c.code).aggregate_order_by( department.c.code.desc() ), ) # SELECT array_agg(department.code ORDER BY department.code DESC) # AS array_agg_1 FROM department在 PostgreSQL 上对于要求使用WITHIN GROUP的集合聚合函数应显式使用within_group()。对于后端无关的字符串聚合可使用 aggregate_strings() 的order_by参数。列表达式类速查核心类与其定位文档中列出的类均可由上述基础/修饰构造器生成源码集中在 lib/sqlalchemy/sql/elements.py类源码位置定位ColumnElementelements.py#L1287所有列导向 SQL 表达式的基类承载运算符重载与强制转换协议BindParameterelements.py#L2017绑定参数占位符由bindparam/literal产生ColumnClauseelements.py#L5351轻量级列表达式由column产生TextClauseelements.py#L2545文本 SQL 子句由text产生BinaryExpressionelements.py#L4167二元运算表达式如col value、a bUnaryExpressionelements.py#L3890一元运算表达式如DISTINCT、~、NULLS FIRST/LASTClauseListelements.py#L3007子句列表Tuple也继承自它Tupleelements.py#L3508由tuple_产生主要用于复合 IN 构造Caseelements.py#L3585CASE 表达式Castelements.py#L3682CAST 表达式TryCastelements.py#L3742TRY_CAST 表达式SQL ServerTypeCoerceelements.py#L3758仅 Python 侧类型关联不渲染 SQLExtractelements.py#L3822EXTRACT 表达式Overelements.py#L4520窗口函数 OVERFrameClauseelements.py#L4641窗口框架边界2.1 起支持非整数范围AggregateOrderByelements.py#L4764内嵌 ORDER BY 的聚合WithinGroupelements.py#L4878WITHIN GROUP 有序集合聚合FunctionFilterelements.py#L4909FILTER 子句Labelelements.py#L5154列别名ASNull / True_ / False_elements.py常量表达式均为单例SingletonConstantquoted_nameelements.py#L5697字符串子类携带quote属性表示是否无条件加引号ColumnCollectionbase.py#L1781列名到列表达式的映射集合如table.c、TextClause.columns返回类型ColumnExpressionArgument2.0.13 引入的列表达式参数类型文档还定义了类型别名ColumnExpressionArgument2.0.13 新增用于列类表达式——典型代表单个 SQL 列表达式包括ColumnElement以及带有__clause_element__()方法的 ORM 映射属性。它是类型检查层面理解哪些参数可当作列表达式传入的关键标注。tuple_复合 IN 构造tuple_() 返回Tuple主要用途是配合ColumnOperators.in_()产生复合 IN 构造from sqlalchemy import tuple_ tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])从源码 docstring 的警告看复合 IN 构造并非所有后端都支持目前已知在 PostgreSQL、MySQL 与 SQLite 上可用不支持的数据库在调用时会抛出DBAPIError的子类。OperatorClass 与运算符协议文档同时列出了Operators、ColumnOperators 与OperatorClasscustom_op。从 lib/sqlalchemy/sql/operators.py 可以看到ColumnOperators定义了丰富的方法集比较类__eq__/__ne__/__lt__/__gt__/is_distinct_from/is_not_distinct_from、字符串类like/ilike/not_like/startswith/endswith/contains/concat、集合类in_/not_in、空值类is_/is_not、位运算类bitwise_and/bitwise_or/bitwise_xor/bitwise_not、排序类asc/desc/nulls_first/nulls_last、between、any_/all_以及collate等。所有这些都通过operate()/reverse_operate()协议路由到具体的运算符实现这也是表达式类能以统一方式支持不同方言编译的原因。列表达式类型检查工具Typing Utilities文档的最后一个部分列出两个用于改进类型检查器type checker支持的独立工具函数sqlalchemy.NotNullable与sqlalchemy.Nullable。它们与 2.0 引入的typed expression体系SQLColumnExpression、ColumnExpressionArgument等类型别名配合使用帮助 mypy、pyright 等工具在类型层面区分可空列表达式与非空列表达式从而在 ORM 与 Core 混合编程中获得更精确的静态类型推导。仓库中 test/typing/plain_files 目录下的众多类型测试文件如 test/typing/plain_files/orm/mapped_column.py演示了这些类型工具在真实代码中的用法。从文档到源码一张完整的表达式全景图综合文档 doc/build/core/sqlelement.rst 与源码可以归纳出 SQLAlchemy 列表达式体系的三层结构构造器层lib/sqlalchemy/sql/_elements_constructors.py、lib/sqlalchemy/sql/elements.py 中的literal/literal_column、lib/sqlalchemy/sql/lambdas.py 中的lambda_stmt面向用户的入口函数负责参数校验与操作数翻转等便捷语义。表达式类层lib/sqlalchemy/sql/elements.py每种 SQL 词法元素的类实现包含各自的__visit_name__与编译钩子。运算符层lib/sqlalchemy/sql/operators.pyOperators/ColumnOperators/OperatorClass定义方法集通过operate/reverse_operate统一路由。测试方面test/sql 目录下的test_compiler.py、test/sql/test_functions.py、test/sql/test_tstrings_py314.py 以及方言测试如 test/dialect/postgresql/test_compiler.py、test/dialect/mssql/test_compiler.py对本文涉及的构造器进行了大量编译级断言是验证各表达式渲染行为的第一手资料。掌握本文的构造器清单与底层类结构后你可以直接查阅这些源码与测试文件进一步理解每个表达式在特定方言下的编译细节。【免费下载链接】sqlalchemyThe Database Toolkit for Python项目地址: https://gitcode.com/gh_mirrors/sq/sqlalchemy创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 SQLAlchemy Column Elements and Expressions：SQL 表达式 API 完全指南
SQLAlchemy Column Elements and ExpressionsSQL 表达式 API 完全指南【免费下载链接】sqlalchemyThe Database Toolkit for Python项目地址: https://gitcode.com/gh_mirrors/sq/sqlalchemy本篇技术指南以 SQLAlchemy 官方文档 sqlelement.rst 为骨架系统讲解表达式 APIExpression API的类层次、全部构造器函数、核心列表达式类与类型检查工具并结合当前仓库源码lib/sqlalchemy/sql/_elements_constructors.py、lib/sqlalchemy/sql/elements.py与测试用例深入每一处构造器的底层实现与典型用法。读完本文你将掌握从bindparam、case、cast到over、within_group的完整列表达式工具箱能够在 SELECT、WHERE、ORDER BY、窗口函数等场景中熟练组装 SQL 语句。表达式 API 概览从 ClauseElement 到 ColumnElementSQLAlchemy 的表达式 APIExpression API由一系列类组成每一个类都对应 SQL 字符串中的一个特定词法元素。将这些类组合成更大的结构后就形成了一条语句构造体statement construct它可以被编译compiled成能直接传给数据库的字符串表示。这些类被组织成一条以最基础类 ClauseElement 为根的分层结构文档原文见 sqlelement.rst 开头部分。两个关键子类分别是ColumnElement代表任何基于列的 SQL 表达式角色例如 SELECT 的 columns 子句、WHERE 子句以及 ORDER BY 子句中的列表达式。它在 lib/sqlalchemy/sql/elements.py 中定义是Column、SQL 函数、绑定参数、字面量表达式、NULL关键字等一切表达式单元的终极基类。FromClause代表放置在 SELECT 语句 FROM 子句中的令牌token例如Table、Subquery等。从源码看ColumnElement继承了十余个角色类roles.ColumnArgumentOrKeyRole、roles.WhereHavingRole、roles.OrderByRole等这正是 SQLAlchemy 的角色强制转换coercion机制的来源任何接受SQL 表达式作为参数的函数都遵循如下强制转换规则普通 Python 字面量字符串、整数、浮点数、布尔、日期时间、Decimal等会被强制转换为字面量绑定值即生成一个内嵌该值的 BindParameter它是ColumnElement的子类最终在执行时作为参数化值传给 DBAPI。任何带有__clause_element__()访问器的特殊对象典型如 ORM 层构造会被转换为 Core 表达式对象ORM 映射类与映射属性正是通过它进入 Core 世界的。Python 的None通常被解释为NULL在 Core 中产生 null() 的实例。此外ColumnElement重载了 Python 运算符、!、、等以模拟 SQL 操作。例如两个ColumnClause对象用相加会产生一个 BinaryExpression from sqlalchemy.sql import column column(a) column(b) sqlalchemy.sql.expression.BinaryExpression object at ... print(column(a) column(b)) a bBinaryExpression与ColumnClause都是ColumnElement的子类这种Python 运算符即 SQL 运算符的设计是整条表达式链路的根基。列表达式的基础构造器Foundational Constructors以下独立函数都可以直接从sqlalchemy命名空间导入用于搭建 SQLAlchemy 表达式语言构造。它们的实现集中在 lib/sqlalchemy/sql/_elements_constructors.py。bindparam绑定参数bindparam() 返回一个 BindParameter 实例——一个表示 SQL 表达式中占位符的ColumnElement其值在语句真正对数据库连接执行时才被提供。它不仅能充当等待填充的占位符还能携带最终要在表达式阶段使用的实际值从而表示那些不安全、不应直接渲染进 SQL 语句、而应作为值传给 DBAPI 做正确转义与类型安全处理的数值。典型使用方式是显式延迟提供参数from sqlalchemy import bindparam stmt select(users_table).where( users_table.c.name bindparam(username) ) # 渲染为SELECT id, name FROM user WHERE name :username result connection.execute(stmt, {username: wendy})显式使用bindparam的常见场景是构造需要多次调用、且每次 WHERE 条件变化的 UPDATE/DELETE 语句stmt ( users_table.update() .where(user_table.c.name bindparam(username)) .values(fullnamebindparam(fullname)) ) connection.execute( stmt, [ {username: wendy, fullname: Wendy Smith}, {username: jack, fullname: Jack Jones}, ], )更常见的是 Core 表达式系统会隐式地广泛使用bindparam几乎所有 SQL 表达式函数收到的 Python 字面量都会被强制转换为固定的bindparam构造。例如users_table.c.name Wendy会产生一个BinaryExpression其右侧就是一个代表字面量的BindParameterprint(repr(expr.right)) # BindParameter(%(4327771088 name)s, Wendy, type_String())最终渲染出的 SQL 类似user.name :name_1真正的字符串Wendy并不出现在渲染串中而是被携带到语句执行阶段。bindparam的完整参数均来自源码 docstring见 lib/sqlalchemy/sql/_elements_constructors.py参数默认值说明key必填绑定参数的名称用于生成 SQL 中的命名占位符编译期间若与其他同名BindParameter冲突或长度超限会被修改。省略时生成匿名名称valueNone参数的初始值执行时若没有为该参数名提供其他值则作为传给 DBAPI 的值type_None可选TypeEngine不传时根据值自动推断如str/int/bool映射到String/Integer/Boolean。类型会在值传给数据库前做预处理如 SQLite 上的日期时间字符串化uniqueFalse为True时若表达式中已存在同名参数则修改本参数的键名。通常用于内部生成匿名绑定表达式required自动为True时执行时必须提供值未显式指定时若未传value和callable_则默认为True否则默认为FalsequoteNone为True时该参数名需要加引号且当前未知为保留字目前仅适用于 Oracle 后端callable_None代替value的可调用对象在语句执行时调用以确定最终值适用于创建构造时无法确定绑定值的场景expandingFalse为True时作为展开参数处理值应为序列而非标量SQL 语句按每次执行进行变换为序列开辟可变数量的参数槽从而允许IN子句使用语句缓存。注意不支持executemany风格参数集isoutparamFalse为True时按存储过程OUT参数处理适用于 Oracle 等支持 OUT 参数的后端literal_executeFalse为True时编译阶段渲染特殊POSTCOMPILE令牌语句执行时把参数最终值直接渲染进 SQL 而非放进参数字典。主要用于 LIMIT/OFFSET 这类 DBAPI 驱动无法容纳绑定参数的场景同时保持构造在编译层可缓存1.4 新增and_ / or_ / not_逻辑组合and_() 生成由AND连接的合取表达式from sqlalchemy import and_ stmt select(users_table).where( and_(users_table.c.name wendy, users_table.c.enrolled True) )and_也可用 Python运算符表达注意复合表达式需加括号以符合 Python 运算符优先级stmt select(users_table).where( (users_table.c.name wendy) (users_table.c.enrolled True) )and_在部分场景下是隐式的例如对Select.where连续调用多次每次的语句会被and_合并。一个重要的实践细节and_至少要传一个位置参数才算合法零参数的and_是歧义的1.4 起已弃用。要生成空的或动态拼接的and_表达式应以true()或直接True作为默认元素from sqlalchemy import true criteria and_(true(), *expressions)当没有其他表达式时上述表达式在后端上编译为true或1 1有表达式时true()会被忽略因为它不影响含其他元素的 AND 结果。对应的 or_() 语义完全对称只是以OR连接、以|运算符表达、默认元素使用false()或False——动态拼接的空or_应写成or_(false(), *expressions)。not_() 返回给定子句的否定即NOT(clause)。~运算符在所有ColumnElement子类上也被重载以产生相同结果。从源码看其实现为coercions.expect(roles.ExpressionElementRole, clause).__invert__()先强制转换参数再取反。caseCASE 条件表达式case() 产生一个CASE表达式返回 Case 实例。SQL 中的CASE构造类似于其他语言中的if/then。常规形式是传入一系列 (条件, 结果) 二元组from sqlalchemy import case stmt select(users_table).where( case( (users_table.c.name wendy, W), (users_table.c.name jack, J), else_E, ) )渲染结果类似SELECT id, name FROM user WHERE CASE WHEN (name :name_1) THEN :param_1 WHEN (name :name_2) THEN :param_2 ELSE :param_3 END当需要对同一父列做多个等值比较时case还提供简写格式通过value参数传入被比较的列表达式whens以字典形式传入候选值 → 结果值映射stmt select(users_table).where( case({wendy: W, jack: J}, valueusers_table.c.name, else_E) )whens中的结果值与else_值会从 Python 字面量强制转换为bindparam构造如果想渲染成内联常量表达式则应使用 literal_column()from sqlalchemy import case, literal_column case( (orderline.c.qty 100, literal_column(greaterthan100)), (orderline.c.qty 10, literal_column(greaterthan10)), else_literal_column(lessthan10), )case参数说明见源码 lib/sqlalchemy/sql/_elements_constructors.py*whens两种形式。其一为多个位置参数二元组(SQL 表达式, 结果值)1.4 起支持位置传参其二为 Python 字典比较值 → 结果值此形式必须配合value使用比较采用运算符。value可选的 SQL 表达式作为字典候选值的固定比较点。else_所有when表达式都不为真时的结果表达式省略时多数数据库返回 NULL。cast / try_cast / type_coerce类型转换三兄弟cast() 产生一个CAST表达式返回 Cast 实例并承担两个职能其一在生成的 SQL 中渲染CAST其二在 Python 侧把给定类型TypeEngine类或实例关联到列表达式上使表达式获得该类型对应的运算符行为、绑定值处理与结果行处理行为from sqlalchemy import cast, Numeric stmt select(cast(product_table.c.unit_price, Numeric(10, 4))) # SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM producttry_cast() 为支持的后端产生TRY_CAST表达式——一种对不可转换值返回 NULL 的CAST。在当前仓库中该构造仅由 SQL Server 方言支持用于其他内置后端会抛出CompileError第三方后端亦可支持。它既可以从sqlalchemy导入也可以从sqlalchemy.dialects.mssql导入from sqlalchemy import select, try_cast, Numeric stmt select(try_cast(product_table.c.unit_price, Numeric(10, 4))) # 在 SQL Server 上渲染为 # SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4)) FROM product_tabletype_coerce() 是cast的替代方案它只把表达式与特定类型关联不渲染CAST。例如把字符串日期列的类型处理器应用到结果行上from sqlalchemy import type_coerce stmt select(type_coerce(log_table.date_string, StringDateTime())) # SELECT date_string AS date_string FROM log需要注意type_coerce不渲染任何自身 SQL 语法不隐含加括号。在运算符上下文中若需要 CAST 通常自带的括号须显式调用.self_group() some_integer column(someint, Integer) some_string column(somestr, String) expr type_coerce(some_integer 5, String) some_string print(expr) someint :someint_1 || somestr expr type_coerce(some_integer 5, String).self_group() some_string print(expr) (someint :someint_1) || somestrcolumn轻量级列表达式column() 产生 ColumnClause 对象它是schema.Column的轻量级类比仅凭名称即可创建from sqlalchemy import column id, name column(id), column(name) stmt select(id, name).select_from(user) # SELECT id, name FROM usercolumn处理的文本被假定为数据库列名若字符串包含混合大小写、特殊字符或匹配目标后端的保留字列表达式将按后端确定的引号行为渲染。要产生完全不做任何引号处理的文本 SQL 表达式应改用literal_column或将is_literalTrue传给column。完整的 SQL 语句则最好交给 text()。column还可以与轻量级的table函数组合以极少的样板代码搭建一个可用的表构造from sqlalchemy import table, column, select user table( user, column(id), column(name), column(description), ) stmt select(user.c.description).where(user.c.name wendy)这种column/table构造是临时性的不与任何MetaData、DDL 或事件关联这一点与schema.Table不同。column的参数包括text元素文本、type关联的类型与is_literal为True时不应用任何引号规则直接输出。literal_column()本质上就是以is_literalTrue调用column。text / tstring纯文本与模板字符串 SQLtext() 直接构造一个 TextClause表示一段文本 SQL 字符串。相比裸字符串它提供后端无关的绑定参数支持、按语句执行的执行选项、绑定参数与结果列的类型化行为from sqlalchemy import text t text(SELECT * FROM users) result connection.execute(t)绑定参数按名称用:name格式指定t text(SELECT * FROM users WHERE id:user_id) result connection.execute(t, {user_id: 12})若 SQL 语句中需要按字面含义出现冒号例如内联字符串中使用反斜杠转义text(rSELECT * FROM users WHERE name\:username)。TextClause还提供bindparams()与columns()方法分别用于指定绑定参数细节与返回值列的名称和类型t ( text(SELECT * FROM users WHERE id:user_id) .bindparams(user_id7) .columns(idInteger, nameString) ) for id, name in connection.execute(t): print(id, name)text既可用于在更大的查询中指定片段如 SELECT 的 WHERE 子句也可用于构造完整独立的语句Executable对象可直接传给.execute()。tstring() 是 2.1 新增的构造仓库中还配套了 test/sql/test_tstrings_py314.py 与 test/orm/test_tstring_py314.py 两组测试用于构造 TString 子句——一种使用 Python 3.14 t-string模板字符串的 SQL 模板。它自动处理 Python 值与 SQLAlchemy 表达式的插值无需像text那样手动指定绑定参数from sqlalchemy import tstring a 5 b 10 stmt tstring(tselect {a}, {b}) result connection.execute(stmt)插值规则为模板的纯字符串部分直接渲染为 SQLSQLAlchemy 表达式列、函数等作为子句元素嵌入普通 Python 值自动包装为literal()。例如# Python 值成为绑定参数 user_id 42 stmt tstring(tSELECT * FROM users WHERE id {user_id}) # 渲染为SELECT * FROM users WHERE id :param_1 # SQLAlchemy 表达式被嵌入 stmt tstring(tSELECT {column(q)} FROM {table(t)}) # 渲染为SELECT q FROM t # 用 literal() 为绑定值施加显式 SQL 类型 some_json {foo: bar} stmt tstring(tSELECT {literal(some_json, JSON)})与text一样tstring也支持通过columns()方法指定返回列及类型from sqlalchemy import tstring, column, Integer, String stmt tstring(tSELECT id, name FROM users).columns( column(id, Integer), column(name, String) )测试文件 test/sql/test_tstrings_py314.py 覆盖了字面量插值、列插值、算术表达式、子查询嵌套执行、JSON 字面量执行、语句缓存键等行为是深入理解tstring边界的最佳参考。其他基础构造器literal()返回绑定到绑定参数上的字面量子句。当非ClauseElement对象字符串、整数、日期等与ColumnElement子类如schema.Column比较时SQLAlchemy 会自动创建字面量子句此函数用于强制生成字面量子句。参数value为任意 Python 对象type_为可选的TypeEngineliteral_execute2.0 新增为True时引擎会在执行时尝试把绑定值直接渲染进 SQL 而非作为参数值。literal_column()产生一个照原样渲染、不做引号处理的文本列表达式。extract()返回 Extract 构造通常以extract以及func.extract形式使用。参数field作为字面 SQL 字符串使用切勿向该字符串传入不可信输入expr为EXTRACT表达式的右侧。例如select(...).where(extract(YEAR, logged_table.c.date_created) 2021)。distinct()对单个列表达式施加一元DISTINCT典型用于聚合函数内部如select(users_table.c.id, func.count(distinct(users_table.c.name)))渲染为SELECT user.id, count(DISTINCT user.name) FROM user。注意它与Select.distinct()不同——后者对整个结果集施加SELECT DISTINCT。null()、true()、false()返回常量Null、True_、False_构造。不支持 true/false 常量的后端会渲染成针对 1 或 0 的表达式如WHERE 0 1。true()/false()在and_/or_合取中具备短路行为or_(t.c.x 5, true())渲染为WHERE trueand_(t.c.x 5, false())渲染为WHERE false。bitwise_not()产生一元按位 NOT 子句2.0.2 新增通常通过~运算符触发注意与布尔否定not_区分。outparam()为支持 OUT 参数的数据库如 Oracle创建存储过程 OUT 参数。输出值可从CursorResult.out_parameters属性取得。from_dml_column()2.1 新增的占位符可在编译后的 INSERT/UPDATE 表达式中引用应用到另一列上的 SQL 表达式或值允许自动复制赋给其他列的表达式。典型用于 ORM hybrid 等基于事件的钩子参见仓库中 test/ext/test_hybrid.py 的使用 stmt t.insert().values(xfunc.foobar(3), yfrom_dml_column(t.c.x) 5) print(stmt) INSERT INTO t (x, y) VALUES (foobar(:foobar_1), (foobar(:foobar_1) :param_1))lambda_stmt()从 SQLAlchemy 1.4 起支持的延迟求值语句构造它把语句生成逻辑封装进 Python lambda仅在必要时才真正编译从而在允许语句变化的同时保持构造级缓存。列表达式的修饰构造器Modifier Constructors文档中列出的这一组函数更常见的调用方式是通过任何ColumnElement构造上的方法。例如 label() 通常通过ColumnElement.label()方法调用。它们多数在源码中采用若参数是ColumnOperators则转调其同名方法否则创建底层表达式的实现模式。函数对应方法说明与示例渲染asc()ColumnElement.asc()升序ORDER BY如order_by(asc(users_table.c.name))→ORDER BY name ASCdesc()ColumnElement.desc()降序ORDER BY→ORDER BY name DESCnulls_first()ColumnElement.nulls_first()修饰asc/desc的NULLS FIRST如order_by(nulls_first(desc(users_table.c.name)))→ORDER BY name DESC NULLS FIRST。1.4 起由nullsfirst更名旧名仍保留2.0.5 恢复了缺失的旧符号nulls_last()ColumnElement.nulls_last()NULLS LAST修饰旧名nullslast同样保留between()ColumnElement.between()BETWEEN谓词between(users_table.c.id, 5, 7)→WHERE id BETWEEN :id_1 AND :id_2。三个参数含左侧列都会从 Python 标量强制转换因此between(5, 3, 7)也合法。symmetricTrue时渲染BETWEEN SYMMETRIC并非所有数据库支持collate()ColumnElement.collate()渲染expression COLLATE collation如collate(mycolumn, utf8_bin)。collation_schema2.1 新增用于支持 schema 限定的排序规则目前为 PostgreSQL排序规则名若为大小写敏感标识符含大写字符会被加引号label()ColumnElement.label()改变 SELECT columns 子句中元素的名称通常通过AS关键字返回Label对象参数为name与elementall_() / any_()ColumnElement.all_()/ColumnElement.any_()产生 ALL/ANY 表达式。PostgreSQL 上作用于ARRAY类型MySQL 上可作用于子查询5 all_(mytable.c.somearray)→5 ALL (somearray)。与 Python 序列比较时先用literal()包装是否可适配数组参数取决于 DBAPI。它们具有操作数翻转行为all_(mytable.c.column) 5会渲染5 ALL (column)与None比较时不会像常规那样渲染ISover()FunctionElement.over()产生Over对象窗口函数详见下文within_group()FunctionElement.within_group()产生WithinGroup对象有序集合聚合详见下文funcfilter()FunctionElement.filter()对聚合与窗口函数产生FunctionFilterfuncfilter(func.count(1), MyClass.name some name)→COUNT(1) FILTER (WHERE myclass.name some name)aggregate_order_by()FunctionElement.aggregate_order_by()产生AggregateOrderBy对象用于array_agg、group_concat、json_agg等在 PostgreSQL/MySQL/MariaDB/SQLite 上支持内嵌ORDER BY参数的聚合函数。在 Oracle 与 SQL Server 上编译使用WithinGroup形式窗口函数与有序聚合over / within_group / aggregate_order_byover() 通常通过FunctionElement.over()调用func.row_number().over(order_bymytable.c.some_column) # ROW_NUMBER() OVER(ORDER BY some_column)窗口范围可通过range_、rows、groups三个互斥参数指定每个参数接受一个由整数与None组成的二元组None表示unbounded无界0表示current row当前行负整数表示preceding前驱正整数表示following后继func.row_number().over(order_bymy_table.c.some_column, range_(None, 0)) # ROW_NUMBER() OVER(ORDER BY some_column # RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) func.row_number().over(order_byx, range_(-5, 10)) # RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING func.row_number().over(order_byx, rows(None, 0)) # ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW func.row_number().over(order_byx, range_(-2, None)) # RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING func.row_number().over(order_byx, range_(1, 3)) # RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING func.row_number().over(order_byx, groups(1, 3)) # GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWINGover的完整参数为elementFunctionElement、WithinGroup或其他兼容构造、partition_by/order_by列元素、字符串或此类列表、range_/rows/groups互斥的窗口框架、exclude2.1 新增取值CURRENT ROW、GROUP、TIES或NO OTHERS渲染窗口框架内的 EXCLUDE 子句且要求必须同时指定rows/range_/groups之一。当排序列类型不是整数时可改用 FrameClause 直接指定框架边界2.1 起支持非整数范围类型from datetime import timedelta from sqlalchemy import FrameClause, FrameClauseType func.sum(my_table.c.amount).over( order_bymy_table.c.date, range_FrameClause( starttimedelta(days7), endNone, start_frame_typeFrameClauseType.PRECEDING, end_frame_typeFrameClauseType.UNBOUNDED, ), )within_group() 用于有序集合聚合与假设集合聚合函数percentile_cont、rank、dense_rank等这类特性通常由 Oracle、Microsoft SQL Server 使用stmt select( func.percentile_cont(0.5).within_group(department.c.salary.desc()), ) # SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY department.salary DESC)对于 PostgreSQL、MySQL/MariaDB、SQLite 以及 Oracle 和 SQL Server 上聚合函数的一般性 ORDER BYaggregate_order_by() 提供了更通用的途径它只在需要WITHIN GROUP的后端Oracle、SQL Server上编译为该形式在 PostgreSQL 上则固定编译为内嵌ORDER BY。2.1 起该函数从 PostgreSQL 专用函数泛化为Function上的后端无关方法stmt select( func.array_agg(department.c.code).aggregate_order_by( department.c.code.desc() ), ) # SELECT array_agg(department.code ORDER BY department.code DESC) # AS array_agg_1 FROM department在 PostgreSQL 上对于要求使用WITHIN GROUP的集合聚合函数应显式使用within_group()。对于后端无关的字符串聚合可使用 aggregate_strings() 的order_by参数。列表达式类速查核心类与其定位文档中列出的类均可由上述基础/修饰构造器生成源码集中在 lib/sqlalchemy/sql/elements.py类源码位置定位ColumnElementelements.py#L1287所有列导向 SQL 表达式的基类承载运算符重载与强制转换协议BindParameterelements.py#L2017绑定参数占位符由bindparam/literal产生ColumnClauseelements.py#L5351轻量级列表达式由column产生TextClauseelements.py#L2545文本 SQL 子句由text产生BinaryExpressionelements.py#L4167二元运算表达式如col value、a bUnaryExpressionelements.py#L3890一元运算表达式如DISTINCT、~、NULLS FIRST/LASTClauseListelements.py#L3007子句列表Tuple也继承自它Tupleelements.py#L3508由tuple_产生主要用于复合 IN 构造Caseelements.py#L3585CASE 表达式Castelements.py#L3682CAST 表达式TryCastelements.py#L3742TRY_CAST 表达式SQL ServerTypeCoerceelements.py#L3758仅 Python 侧类型关联不渲染 SQLExtractelements.py#L3822EXTRACT 表达式Overelements.py#L4520窗口函数 OVERFrameClauseelements.py#L4641窗口框架边界2.1 起支持非整数范围AggregateOrderByelements.py#L4764内嵌 ORDER BY 的聚合WithinGroupelements.py#L4878WITHIN GROUP 有序集合聚合FunctionFilterelements.py#L4909FILTER 子句Labelelements.py#L5154列别名ASNull / True_ / False_elements.py常量表达式均为单例SingletonConstantquoted_nameelements.py#L5697字符串子类携带quote属性表示是否无条件加引号ColumnCollectionbase.py#L1781列名到列表达式的映射集合如table.c、TextClause.columns返回类型ColumnExpressionArgument2.0.13 引入的列表达式参数类型文档还定义了类型别名ColumnExpressionArgument2.0.13 新增用于列类表达式——典型代表单个 SQL 列表达式包括ColumnElement以及带有__clause_element__()方法的 ORM 映射属性。它是类型检查层面理解哪些参数可当作列表达式传入的关键标注。tuple_复合 IN 构造tuple_() 返回Tuple主要用途是配合ColumnOperators.in_()产生复合 IN 构造from sqlalchemy import tuple_ tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])从源码 docstring 的警告看复合 IN 构造并非所有后端都支持目前已知在 PostgreSQL、MySQL 与 SQLite 上可用不支持的数据库在调用时会抛出DBAPIError的子类。OperatorClass 与运算符协议文档同时列出了Operators、ColumnOperators 与OperatorClasscustom_op。从 lib/sqlalchemy/sql/operators.py 可以看到ColumnOperators定义了丰富的方法集比较类__eq__/__ne__/__lt__/__gt__/is_distinct_from/is_not_distinct_from、字符串类like/ilike/not_like/startswith/endswith/contains/concat、集合类in_/not_in、空值类is_/is_not、位运算类bitwise_and/bitwise_or/bitwise_xor/bitwise_not、排序类asc/desc/nulls_first/nulls_last、between、any_/all_以及collate等。所有这些都通过operate()/reverse_operate()协议路由到具体的运算符实现这也是表达式类能以统一方式支持不同方言编译的原因。列表达式类型检查工具Typing Utilities文档的最后一个部分列出两个用于改进类型检查器type checker支持的独立工具函数sqlalchemy.NotNullable与sqlalchemy.Nullable。它们与 2.0 引入的typed expression体系SQLColumnExpression、ColumnExpressionArgument等类型别名配合使用帮助 mypy、pyright 等工具在类型层面区分可空列表达式与非空列表达式从而在 ORM 与 Core 混合编程中获得更精确的静态类型推导。仓库中 test/typing/plain_files 目录下的众多类型测试文件如 test/typing/plain_files/orm/mapped_column.py演示了这些类型工具在真实代码中的用法。从文档到源码一张完整的表达式全景图综合文档 doc/build/core/sqlelement.rst 与源码可以归纳出 SQLAlchemy 列表达式体系的三层结构构造器层lib/sqlalchemy/sql/_elements_constructors.py、lib/sqlalchemy/sql/elements.py 中的literal/literal_column、lib/sqlalchemy/sql/lambdas.py 中的lambda_stmt面向用户的入口函数负责参数校验与操作数翻转等便捷语义。表达式类层lib/sqlalchemy/sql/elements.py每种 SQL 词法元素的类实现包含各自的__visit_name__与编译钩子。运算符层lib/sqlalchemy/sql/operators.pyOperators/ColumnOperators/OperatorClass定义方法集通过operate/reverse_operate统一路由。测试方面test/sql 目录下的test_compiler.py、test/sql/test_functions.py、test/sql/test_tstrings_py314.py 以及方言测试如 test/dialect/postgresql/test_compiler.py、test/dialect/mssql/test_compiler.py对本文涉及的构造器进行了大量编译级断言是验证各表达式渲染行为的第一手资料。掌握本文的构造器清单与底层类结构后你可以直接查阅这些源码与测试文件进一步理解每个表达式在特定方言下的编译细节。【免费下载链接】sqlalchemyThe Database Toolkit for Python项目地址: https://gitcode.com/gh_mirrors/sq/sqlalchemy创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考