拓冰网站定制 
SQLAlchemy SQL 表达式字符串化全指南语句编译、内联绑定参数与括号规则【免费下载链接】sqlalchemyThe Database Toolkit for Python项目地址: https://gitcode.com/gh_mirrors/sq/sqlalchemySQLAlchemy 的 FAQdoc/build/faq/sqlexpressions.rst集中回答了围绕 SQL 表达式字符串化stringification的系列高频问题如何把 Core 语句或 ORMQuery渲染成 SQL 字符串、如何在特定数据库方言下编译、如何将绑定参数内联进 SQLliteral_binds与render_postcompile、为何字符串化时百分号会被翻倍以及自定义运算符op()的括号规则。读完本文你将掌握利用str()、ClauseElement.compile()与各类compile_kwargs精确控制 SQL 渲染的全部技法并能安全地将其用于日志与调试场景。一、用str()直接渲染 SQL 字符串绝大多数简单场景下把 SQLAlchemy Core 语句对象、表达式片段乃至 ORM 的Query对象转换为 SQL 字符串直接使用 Python 内置的str()即可print()函数本身也会隐式调用str() from sqlalchemy import table, column, select t table(my_table, column(x)) statement select(t) print(str(statement)) SELECT my_table.x FROM my_table同样的方式也适用于 ORMQuery对象以及select()、insert()等任何语句对象。哪怕是单个表达式片段也一样 from sqlalchemy import column print(column(x) some value) x :x_1注意str()的渲染结果是“面向通用 SQL”的绑定参数以:name形式保留不会被内联为字面值。从源码结构看这对应CompilerElement.compile()lib/sqlalchemy/sql/elements.py在没有传入bind与dialect时使用默认方言stringify_dialect default进行编译的路径。1.1 默认字符串化的局限当语句中包含“数据库特有”的格式或仅在特定数据库可用的元素例如某个方言专有的类型或函数时str()可能产生错误语法甚至抛出UnsupportedCompilationError异常。此时就必须借助ClauseElement.compile()方法并显式传入目标数据库的Engine或Dialect对象。二、面向特定数据库方言的字符串化2.1 通过Engine传入方言如果你手头已有一个 MySQL 引擎可以直接让语句按 MySQL 方言编译from sqlalchemy import create_engine engine create_engine(mysqlpymysql://scott:tigerlocalhost/test) print(statement.compile(engine))2.2 直接实例化Dialect对象不想构造Engine时可以更直接地实例化一个方言对象例如 PostgreSQL 方言from sqlalchemy.dialects import postgresql print(statement.compile(dialectpostgresql.dialect()))任何方言都可以用create_engine配合一个“哑 URL”组装出来再通过Engine.dialect属性取用。例如想要一个 psycopg2 方言对象e create_engine(postgresqlpsycopg2://) psycopg2_dialect e.dialect2.3 ORMQuery对象如何编译Query本身没有compile()方法需要先通过Query.statement访问器拿到其背后的语句再调用编译方法statement query.statement print(statement.compile(someengine))2.4 编译方法的行为要点从 elements.py 的实现看compile(bindNone, dialectNone, **kw)的逻辑是若未显式传dialect则从bindEngine或Connection取bind.dialect若两者都未提供则退回默认方言dialect参数优先级高于bind参数额外关键字参数含compile_kwargs字典会一路传给编译器的各 visit 方法。返回值是Compiled对象对其调用str()得到 SQL 字符串其params访问器则给出绑定参数名与值的字典。三、将绑定参数内联渲染literal_binds上面的各种形式渲染出的 SQL都保留了绑定参数占位符如:x_1因为在正常执行流程中参数由 Python DBAPI 负责替换。SQLAlchemy 出于安全考虑默认不做参数内联——绕过绑定参数几乎是现代 Web 应用中最普遍的安全漏洞。3.1literal_binds的使用方式compile()支持通过compile_kwargs字典传入literal_binds标志从而把参数值直接写进 SQLfrom sqlalchemy.sql import table, column, select t table(t, column(x)) s select(t).where(t.c.x 5) # **不要**用于不可信输入!!! print(s.compile(compile_kwargs{literal_binds: True})) # 面向特定方言渲染 print(s.compile(dialectdialect, compile_kwargs{literal_binds: True})) # 或者如果已有 Engine作为第一个参数传入 print(s.compile(some_engine, compile_kwargs{literal_binds: True}))此功能主要服务于日志与调试帮助开发者直接看到带值的原始 SQL。3.2 严格的安全警告官方文档给出明确警告绝不应对来自 Web 表单或其他用户输入应用的不可信字符串内容使用上述技术。SQLAlchemy 把 Python 值强转为直接 SQL 字符串值的设施对不可信输入不安全也不校验传入数据的类型。对关系型数据库编程式执行非 DDL 语句时永远使用绑定参数。3.3 为什么 SQLAlchemy 不支持全量类型的内联官方 FAQ 给出了三点理由可视为设计哲学DBAPI 已承担该职责参数替换本来就是所用 DBAPI 在正常使用路径下已经实现的功能SQLAlchemy 没必要为每个后端、每个数据类型重复实现那只会带来冗余工作与持续的测试、维护开销。避免诱导错误用法为特定数据库做参数内联的字符串化暗示用户可以把这些完整字符串直接发给数据库执行。这既不必要也不安全SQLAlchemy 不希望以任何方式鼓励这种行为。安全风险集中区字面值渲染是最容易滋生安全问题的地方。SQLAlchemy 倾向于把“安全的参数字符串化”交给各 DBAPI 驱动去处理因为每种 DBAPI 的参数细节只有驱动自己最清楚也最能在安全的前提下处理妥当。3.4 现有限制literal_binds目前只支持基本类型如 int、str如果使用一个没有预置值的bindparam()也无法将其字符串化。无条件字符串化所有参数的方法见下文各节。3.5 源码视角literal_binds 是如何工作的在 compiler.py 的SQLCompiler.visit_bindparam()中可以看到当literal_bindsTrue时直接走render_literal_bindparam()分支把参数值渲染为字面量而非占位符render_literal_bindparam()compiler.py取bindparam.effective_value再交给render_literal_value()render_literal_value()compiler.py通过类型的_cached_literal_processor(dialect)取得字面量处理器完成渲染若值为None且类型不允许求值为 NULL则渲染为 SQL 的NULL。这也解释了为何复杂类型如 UUID用literal_binds往往得不到理想结果——它依赖类型自身的字面量处理器。四、无法内联时的四种调试级字符串化方案以 PostgreSQL 的UUID类型为例先构造一个模型与语句import uuid from sqlalchemy import Column from sqlalchemy import create_engine from sqlalchemy import Integer from sqlalchemy import select from sqlalchemy.dialects.postgresql import UUID from sqlalchemy.orm import declarative_base Base declarative_base() class A(Base): __tablename__ a id Column(Integer, primary_keyTrue) data Column(UUID) stmt select(A).where(A.data uuid.uuid4())针对这类“列与单个 UUID 值比较”的语句官方 FAQ 提供了四种把值内联进字符串的方案4.1 方案一借助 DBAPI 自带的字面量能力mogrify部分 DBAPI如 psycopg2提供类似mogrify()的辅助函数可访问驱动自己的字面量渲染功能。做法是不使用literal_binds渲染 SQL然后把参数通过SQLCompiler.params访问器单独传给驱动e create_engine(postgresqlpsycopg2://scott:tigerlocalhost/test) with e.connect() as conn: cursor conn.connection.cursor() compiled stmt.compile(e) print(cursor.mogrify(str(compiled), compiled.params))上述代码将产生 psycopg2 的原始字节串bSELECT a.id, a.data \nFROM a \nWHERE a.data a511b0fc-76da-4c47-a4b4-716a8189b7ac::uuid从源码看Compiled.params属性compiler.py直接暴露编译结果中的参数名到值的映射配合驱动自身的格式化能力即可得到带类型的完整字面量。4.2 方案二按 DBAPI 的 paramstyle 手工拼接先了解目标 DBAPI 的 paramstylepsycopg2 使用命名的pyformat风格参数写作%(paramname)sSQLite 使用位置风格的qmark参数写作?。psycopg2pyformat 风格e create_engine(postgresqlpsycopg2://) # 将使用 pyformat 风格即参数写作 %(paramname)s compiled stmt.compile(e, compile_kwargs{render_postcompile: True}) print(str(compiled) % compiled.params)这会产出一个“不可执行、但适合调试”的字符串SELECT a.id, a.data FROM a WHERE a.data 9eec1209-50b4-4253-b74b-f82461ed80c1说明render_postcompile的含义将在下一节详述。警告这种方式不安全不要对不可信输入使用。SQLiteqmark 位置风格位置风格没有名字需要借助SQLCompiler.positiontup集合按编译顺序保存的参数名列表compiler.py配合params取得按位置排序的参数值再用正则把?逐个替换import re e create_engine(sqlitepysqlite://) # 将使用 qmark 风格即参数写作 ? compiled stmt.compile(e, compile_kwargs{render_postcompile: True}) # 按位置顺序取参数 params (repr(compiled.params[name]) for name in compiled.positiontup) print(re.sub(r\?, lambda m: next(params), str(compiled)))输出SELECT a.id, a.data FROM a WHERE a.data UUID(1bd70375-db17-4d8c-94f1-fc2ef3aada26)4.3 方案三用sqlalchemy.ext.compiler定制BindParameter渲染利用 sqlalchemy.ext.compiler 扩展可以在自定义标志出现时用自定义方式渲染BindParameter对象。该标志和其他标志一样通过compile_kwargs字典传递from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import BindParameter compiles(BindParameter) def _render_literal_bindparam(element, compiler, use_my_literal_recipeFalse, **kw): if not use_my_literal_recipe: # 使用正常的 bindparam 处理 return compiler.visit_bindparam(element, **kw) # 如果 use_my_literal_recipe 被传入 compile_kwargs则直接渲染值 return repr(element.value) e create_engine(postgresqlpsycopg2://) print(stmt.compile(e, compile_kwargs{use_my_literal_recipe: True}))输出SELECT a.id, a.data FROM a WHERE a.data UUID(47b154cd-36b2-42ae-9718-888629ab9857)此方案把“何时内联”的决策权完全交给你可以在回调里判断use_my_literal_recipe标志未设置时退回标准的visit_bindparam处理从而不破坏常规编译路径。4.4 方案四用TypeDecorator.process_literal_param做类型级定制如果希望“内联渲染规则”内建于模型或语句本身可以自定义TypeDecorator子类并重写process_literal_param()from sqlalchemy import TypeDecorator class UUIDStringify(TypeDecorator): impl UUID def process_literal_param(self, value, dialect): return repr(value)该类型需要显式用于模型或在本语句内通过type_coerce()局部使用from sqlalchemy import type_coerce stmt select(A).where(type_coerce(A.data, UUIDStringify) uuid.uuid4()) print(stmt.compile(e, compile_kwargs{literal_binds: True}))输出同样的形式SELECT a.id, a.data FROM a WHERE a.data UUID(47b154cd-36b2-42ae-9718-888629ab9857)从 type_api.py 的源码可以看到process_literal_param()在SQL 编译阶段被调用接收一个具体 Python 值并返回要渲染进输出字符串的文本它与执行阶段处理实际参数值的process_bind_param()是两回事二者职责不同不可混淆。五、渲染 POSTCOMPILE 扩展参数为绑定参数SQLAlchemy 有一种“延迟求值”的绑定参数变体即BindParameter的expanding属性见 elements.py 中bindparam()的实现编译 SQL 构造时它先渲染为一种中间状态待真正执行、传入实际值时才进一步展开。默认情况下ColumnOperators.in_()就使用扩展参数这样 SQL 字符串可以独立于每次调用传入的具体列表而被安全缓存。 stmt select(A).where(A.id.in_([1, 2, 3]))若要把IN子句渲染成真实的绑定参数符号可在compile()时使用render_postcompileTrue e create_engine(postgresqlpsycopg2://) print(stmt.compile(e, compile_kwargs{render_postcompile: True})) SELECT a.id, a.data FROM a WHERE a.id IN (%(id_1_1)s, %(id_1_2)s, %(id_1_3)s)5.1literal_binds隐含开启render_postcompile对于只含 int/str 等基本类型的语句literal_binds会自动把render_postcompile置为 True因此可以直接字符串化# literal_binds 隐含 render_postcompile print(stmt.compile(e, compile_kwargs{literal_binds: True})) SELECT a.id, a.data FROM a WHERE a.id IN (1, 2, 3)5.2 与params/positiontup的兼容SQLCompiler.params与SQLCompiler.positiontup同样兼容render_postcompile因此上一节中“渲染内联绑定参数”的配方在扩展参数场景下完全可用。例如 SQLite 的位置形式 u1, u2, u3 uuid.uuid4(), uuid.uuid4(), uuid.uuid4() stmt select(A).where(A.data.in_([u1, u2, u3])) import re e create_engine(sqlitepysqlite://) compiled stmt.compile(e, compile_kwargs{render_postcompile: True}) params (repr(compiled.params[name]) for name in compiled.positiontup) print(re.sub(r\?, lambda m: next(params), str(compiled))) SELECT a.id, a.data FROM a WHERE a.data IN (UUID(aa1944d6-9a5a-45d5-b8da-0ba1ef0a4f38), UUID(a81920e6-15e2-4392-8a3c-d775ffa9ccd2), UUID(b5574cdb-ff9b-49a3-be52-dbc89f087bfa))5.3 源码视角POSTCOMPILE 如何工作在 compiler.py 的visit_bindparam()中可以看到render_postcompile的处理当参数属于expanding或literal_execute时编译器标记self._render_postcompile True并通过bindparam_string()输出__[POSTCOMPILE_...]形式的中间占位符而_literal_execute_expanding_parameter_literal_binds()compiler.py负责在literal_binds模式下把扩展参数展开为多个字面量。positiontup则在编译过程中被填充为按出现顺序排列的参数名列表供params访问器按位置取值。六、必须牢记的安全边界官方 FAQ 对所有“绕过绑定参数、把字面值写进 SQL”的配方给出了统一的红色警告上述代码只有在满足以下全部条件时才可以使用仅用于调试目的字符串不会发给生产数据库执行只针对本地、可信输入。这些字符串化字面值的技术在任何意义上都不安全绝不应针对生产数据库使用。它们存在的意义是帮助开发者查看 SQL 形态、排查问题而不是替代参数化查询。七、字符串化时百分号为何被翻倍许多 DBAPI 使用pyformat或format风格的 paramstyle其语法必然涉及百分号。这类 DBAPI 通常要求语句中其他用途的百分号以双写形式即转义出现例如SELECT a, b FROM some_table WHERE a %s AND c %s AND num %% modulus 0SQLAlchemy 把语句交给 DBAPI 时绑定参数的替换机制与 Python 字符串插值运算符%一致许多 DBAPI 甚至直接使用该运算符。上面的语句替换绑定参数后就变成SELECT a, b FROM some_table WHERE a 5 AND c 10 AND num % modulus 0默认编译器对 PostgreSQL默认 DBAPI 为 psycopg2、MySQL默认 DBAPI 为 mysqlclient等数据库就带有这种百分号转义行为 from sqlalchemy import table, column from sqlalchemy.dialects import postgresql t table(my_table, column(value % one), column(value % two)) print(t.select().compile(dialectpostgresql.dialect())) SELECT my_table.value %% one, my_table.value %% two FROM my_table7.1 移除百分号的两种办法如果使用此类方言、又想要不含绑定参数符号的“非 DBAPI”语句一种快捷方式是直接用 Python 的%运算符替换入一组空参数 strstmt str(t.select().compile(dialectpostgresql.dialect())) print(strstmt % ()) SELECT my_table.value % one, my_table.value % two FROM my_table另一种是给方言设置不同的参数风格所有Dialect实现都接受paramstyle参数编译器将据此改用指定风格。下面把非常通用的named风格设进编译所用方言百分号在编译后的 SQL 中就不再具有特殊含义、也不会再被转义 print(t.select().compile(dialectpostgresql.dialect(paramstylenamed))) SELECT my_table.value % one, my_table.value % two FROM my_table八、op()自定义运算符的括号问题Operators.op()方法允许创建 SQLAlchemy 未知的自定义数据库运算符 print(column(q).op(-)(column(p))) q - p但当它出现在复合表达式的右侧时括号不会按预期生成 print((column(q1) column(q2)).op(-)(column(p))) q1 q2 - p这里我们大概率期望的是(q1 q2) - p。8.1 用 precedence 参数解决解决方案是设置运算符的优先级Operators.op.precedence参数取一个较大数值即可。100 是最大值而 SQLAlchemy 现有运算符使用的最高数值目前为 15 print((column(q1) column(q2)).op(-, precedence100)(column(p))) (q1 q2) - p8.2 用 self_group() 强制加括号对于二元表达式即有左右操作数和运算符的表达式通常也可以用ColumnElement.self_group()强制加括号 print((column(q1) column(q2)).self_group().op(-)(column(p))) (q1 q2) - p8.3 括号规则为何如此设计很多数据库对“过多的括号”或“位置别扭的括号”会报错所以 SQLAlchemy 不按分组关系生成括号而是依据运算符优先级与是否已知为可结合来决定最小化地生成括号。否则像column(a) column(b) column(c) column(d)会被渲染成(((a AND b) AND c) AND d)——虽然没错但既啰嗦又容易被用户当成 bug 上报。而另一些场景下如column(q, ARRAY(Integer, dimensions2))[5][6]会产生((q[5])[6])这类更可能让数据库困惑、至少损害可读性的结果。还有一些边缘情况会得到(x) 7数据库同样不喜欢。因此加括号不是“无脑套括号”而是以运算符优先级与结合性为依据判断分组。Operators.op()的 precedence 默认值为0。8.4 如果把默认优先级设为 100 会怎样官方 FAQ 讨论了一个思想实验假如把op.precedence默认值设为 100即最高值结果如何看下面两组对比等价的两组左操作数为复合表达式时括号可有可无 print((column(q) - column(y)).op(, precedence100)(column(z))) (q - y) z print((column(q) - column(y)).op()(column(z))) q - y z不等价的两组右操作数为复合表达式时括号位置决定了语义 print(column(q) - column(y).op(, precedence100)(column(z))) q - y z print(column(q) - column(y).op()(column(z))) q - (y z)结论是只要基于优先级与结合性做括号化对于一个没有给定优先级的通用运算符很难找到一种在所有情况下都自动正确的加括号策略——因为你有时希望自定义运算符比其他运算符优先级更低有时又希望它更高。FAQ 也提到或许未来可以让op()在左侧是复合表达式时强制调用self_group()因为“复合表达式出现在左侧时总能无害地加括号”但目前保持括号规则内部一致仍是更稳妥的做法。延伸阅读运算符的括号规则在 Operator Reference 的 operators_parentheses 一节中有系统阐述。九、小结何时用哪种方案场景推荐方案简单语句、无方言差异str(statement)含方言特有元素statement.compile(engine)或statement.compile(dialect...)日志/调试需要看到 int/str 值compile_kwargs{literal_binds: True}IN (...)等扩展参数要展开compile_kwargs{render_postcompile: True}复杂类型UUID 等需要带类型字面量DBAPImogrify()、paramstyle拼接、ext.compiler定制、TypeDecorator.process_literal_param编译结果中出现%%换用paramstylenamed或用strstmt % ()清理自定义运算符括号不对调高op(precedence...)或使用self_group()始终牢记所有把字面值内联进 SQL 的技法都仅限调试、仅限本地可信输入、绝不面向生产数据库。正确理解str()、compile()、literal_binds、render_postcompile、params/positiontup以及op()括号规则就能在日志记录、SQL 审查与问题排查中游刃有余。想深入了解实现细节可继续阅读 compiler.py 的SQLCompiler类、elements.py 的CompilerElement.compile()以及 type_api.py 的TypeDecorator一节。【免费下载链接】sqlalchemyThe Database Toolkit for Python项目地址: https://gitcode.com/gh_mirrors/sq/sqlalchemy创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 SQLAlchemy SQL 表达式字符串化全指南：语句编译、内联绑定参数与括号规则
SQLAlchemy SQL 表达式字符串化全指南语句编译、内联绑定参数与括号规则【免费下载链接】sqlalchemyThe Database Toolkit for Python项目地址: https://gitcode.com/gh_mirrors/sq/sqlalchemySQLAlchemy 的 FAQdoc/build/faq/sqlexpressions.rst集中回答了围绕 SQL 表达式字符串化stringification的系列高频问题如何把 Core 语句或 ORMQuery渲染成 SQL 字符串、如何在特定数据库方言下编译、如何将绑定参数内联进 SQLliteral_binds与render_postcompile、为何字符串化时百分号会被翻倍以及自定义运算符op()的括号规则。读完本文你将掌握利用str()、ClauseElement.compile()与各类compile_kwargs精确控制 SQL 渲染的全部技法并能安全地将其用于日志与调试场景。一、用str()直接渲染 SQL 字符串绝大多数简单场景下把 SQLAlchemy Core 语句对象、表达式片段乃至 ORM 的Query对象转换为 SQL 字符串直接使用 Python 内置的str()即可print()函数本身也会隐式调用str() from sqlalchemy import table, column, select t table(my_table, column(x)) statement select(t) print(str(statement)) SELECT my_table.x FROM my_table同样的方式也适用于 ORMQuery对象以及select()、insert()等任何语句对象。哪怕是单个表达式片段也一样 from sqlalchemy import column print(column(x) some value) x :x_1注意str()的渲染结果是“面向通用 SQL”的绑定参数以:name形式保留不会被内联为字面值。从源码结构看这对应CompilerElement.compile()lib/sqlalchemy/sql/elements.py在没有传入bind与dialect时使用默认方言stringify_dialect default进行编译的路径。1.1 默认字符串化的局限当语句中包含“数据库特有”的格式或仅在特定数据库可用的元素例如某个方言专有的类型或函数时str()可能产生错误语法甚至抛出UnsupportedCompilationError异常。此时就必须借助ClauseElement.compile()方法并显式传入目标数据库的Engine或Dialect对象。二、面向特定数据库方言的字符串化2.1 通过Engine传入方言如果你手头已有一个 MySQL 引擎可以直接让语句按 MySQL 方言编译from sqlalchemy import create_engine engine create_engine(mysqlpymysql://scott:tigerlocalhost/test) print(statement.compile(engine))2.2 直接实例化Dialect对象不想构造Engine时可以更直接地实例化一个方言对象例如 PostgreSQL 方言from sqlalchemy.dialects import postgresql print(statement.compile(dialectpostgresql.dialect()))任何方言都可以用create_engine配合一个“哑 URL”组装出来再通过Engine.dialect属性取用。例如想要一个 psycopg2 方言对象e create_engine(postgresqlpsycopg2://) psycopg2_dialect e.dialect2.3 ORMQuery对象如何编译Query本身没有compile()方法需要先通过Query.statement访问器拿到其背后的语句再调用编译方法statement query.statement print(statement.compile(someengine))2.4 编译方法的行为要点从 elements.py 的实现看compile(bindNone, dialectNone, **kw)的逻辑是若未显式传dialect则从bindEngine或Connection取bind.dialect若两者都未提供则退回默认方言dialect参数优先级高于bind参数额外关键字参数含compile_kwargs字典会一路传给编译器的各 visit 方法。返回值是Compiled对象对其调用str()得到 SQL 字符串其params访问器则给出绑定参数名与值的字典。三、将绑定参数内联渲染literal_binds上面的各种形式渲染出的 SQL都保留了绑定参数占位符如:x_1因为在正常执行流程中参数由 Python DBAPI 负责替换。SQLAlchemy 出于安全考虑默认不做参数内联——绕过绑定参数几乎是现代 Web 应用中最普遍的安全漏洞。3.1literal_binds的使用方式compile()支持通过compile_kwargs字典传入literal_binds标志从而把参数值直接写进 SQLfrom sqlalchemy.sql import table, column, select t table(t, column(x)) s select(t).where(t.c.x 5) # **不要**用于不可信输入!!! print(s.compile(compile_kwargs{literal_binds: True})) # 面向特定方言渲染 print(s.compile(dialectdialect, compile_kwargs{literal_binds: True})) # 或者如果已有 Engine作为第一个参数传入 print(s.compile(some_engine, compile_kwargs{literal_binds: True}))此功能主要服务于日志与调试帮助开发者直接看到带值的原始 SQL。3.2 严格的安全警告官方文档给出明确警告绝不应对来自 Web 表单或其他用户输入应用的不可信字符串内容使用上述技术。SQLAlchemy 把 Python 值强转为直接 SQL 字符串值的设施对不可信输入不安全也不校验传入数据的类型。对关系型数据库编程式执行非 DDL 语句时永远使用绑定参数。3.3 为什么 SQLAlchemy 不支持全量类型的内联官方 FAQ 给出了三点理由可视为设计哲学DBAPI 已承担该职责参数替换本来就是所用 DBAPI 在正常使用路径下已经实现的功能SQLAlchemy 没必要为每个后端、每个数据类型重复实现那只会带来冗余工作与持续的测试、维护开销。避免诱导错误用法为特定数据库做参数内联的字符串化暗示用户可以把这些完整字符串直接发给数据库执行。这既不必要也不安全SQLAlchemy 不希望以任何方式鼓励这种行为。安全风险集中区字面值渲染是最容易滋生安全问题的地方。SQLAlchemy 倾向于把“安全的参数字符串化”交给各 DBAPI 驱动去处理因为每种 DBAPI 的参数细节只有驱动自己最清楚也最能在安全的前提下处理妥当。3.4 现有限制literal_binds目前只支持基本类型如 int、str如果使用一个没有预置值的bindparam()也无法将其字符串化。无条件字符串化所有参数的方法见下文各节。3.5 源码视角literal_binds 是如何工作的在 compiler.py 的SQLCompiler.visit_bindparam()中可以看到当literal_bindsTrue时直接走render_literal_bindparam()分支把参数值渲染为字面量而非占位符render_literal_bindparam()compiler.py取bindparam.effective_value再交给render_literal_value()render_literal_value()compiler.py通过类型的_cached_literal_processor(dialect)取得字面量处理器完成渲染若值为None且类型不允许求值为 NULL则渲染为 SQL 的NULL。这也解释了为何复杂类型如 UUID用literal_binds往往得不到理想结果——它依赖类型自身的字面量处理器。四、无法内联时的四种调试级字符串化方案以 PostgreSQL 的UUID类型为例先构造一个模型与语句import uuid from sqlalchemy import Column from sqlalchemy import create_engine from sqlalchemy import Integer from sqlalchemy import select from sqlalchemy.dialects.postgresql import UUID from sqlalchemy.orm import declarative_base Base declarative_base() class A(Base): __tablename__ a id Column(Integer, primary_keyTrue) data Column(UUID) stmt select(A).where(A.data uuid.uuid4())针对这类“列与单个 UUID 值比较”的语句官方 FAQ 提供了四种把值内联进字符串的方案4.1 方案一借助 DBAPI 自带的字面量能力mogrify部分 DBAPI如 psycopg2提供类似mogrify()的辅助函数可访问驱动自己的字面量渲染功能。做法是不使用literal_binds渲染 SQL然后把参数通过SQLCompiler.params访问器单独传给驱动e create_engine(postgresqlpsycopg2://scott:tigerlocalhost/test) with e.connect() as conn: cursor conn.connection.cursor() compiled stmt.compile(e) print(cursor.mogrify(str(compiled), compiled.params))上述代码将产生 psycopg2 的原始字节串bSELECT a.id, a.data \nFROM a \nWHERE a.data a511b0fc-76da-4c47-a4b4-716a8189b7ac::uuid从源码看Compiled.params属性compiler.py直接暴露编译结果中的参数名到值的映射配合驱动自身的格式化能力即可得到带类型的完整字面量。4.2 方案二按 DBAPI 的 paramstyle 手工拼接先了解目标 DBAPI 的 paramstylepsycopg2 使用命名的pyformat风格参数写作%(paramname)sSQLite 使用位置风格的qmark参数写作?。psycopg2pyformat 风格e create_engine(postgresqlpsycopg2://) # 将使用 pyformat 风格即参数写作 %(paramname)s compiled stmt.compile(e, compile_kwargs{render_postcompile: True}) print(str(compiled) % compiled.params)这会产出一个“不可执行、但适合调试”的字符串SELECT a.id, a.data FROM a WHERE a.data 9eec1209-50b4-4253-b74b-f82461ed80c1说明render_postcompile的含义将在下一节详述。警告这种方式不安全不要对不可信输入使用。SQLiteqmark 位置风格位置风格没有名字需要借助SQLCompiler.positiontup集合按编译顺序保存的参数名列表compiler.py配合params取得按位置排序的参数值再用正则把?逐个替换import re e create_engine(sqlitepysqlite://) # 将使用 qmark 风格即参数写作 ? compiled stmt.compile(e, compile_kwargs{render_postcompile: True}) # 按位置顺序取参数 params (repr(compiled.params[name]) for name in compiled.positiontup) print(re.sub(r\?, lambda m: next(params), str(compiled)))输出SELECT a.id, a.data FROM a WHERE a.data UUID(1bd70375-db17-4d8c-94f1-fc2ef3aada26)4.3 方案三用sqlalchemy.ext.compiler定制BindParameter渲染利用 sqlalchemy.ext.compiler 扩展可以在自定义标志出现时用自定义方式渲染BindParameter对象。该标志和其他标志一样通过compile_kwargs字典传递from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import BindParameter compiles(BindParameter) def _render_literal_bindparam(element, compiler, use_my_literal_recipeFalse, **kw): if not use_my_literal_recipe: # 使用正常的 bindparam 处理 return compiler.visit_bindparam(element, **kw) # 如果 use_my_literal_recipe 被传入 compile_kwargs则直接渲染值 return repr(element.value) e create_engine(postgresqlpsycopg2://) print(stmt.compile(e, compile_kwargs{use_my_literal_recipe: True}))输出SELECT a.id, a.data FROM a WHERE a.data UUID(47b154cd-36b2-42ae-9718-888629ab9857)此方案把“何时内联”的决策权完全交给你可以在回调里判断use_my_literal_recipe标志未设置时退回标准的visit_bindparam处理从而不破坏常规编译路径。4.4 方案四用TypeDecorator.process_literal_param做类型级定制如果希望“内联渲染规则”内建于模型或语句本身可以自定义TypeDecorator子类并重写process_literal_param()from sqlalchemy import TypeDecorator class UUIDStringify(TypeDecorator): impl UUID def process_literal_param(self, value, dialect): return repr(value)该类型需要显式用于模型或在本语句内通过type_coerce()局部使用from sqlalchemy import type_coerce stmt select(A).where(type_coerce(A.data, UUIDStringify) uuid.uuid4()) print(stmt.compile(e, compile_kwargs{literal_binds: True}))输出同样的形式SELECT a.id, a.data FROM a WHERE a.data UUID(47b154cd-36b2-42ae-9718-888629ab9857)从 type_api.py 的源码可以看到process_literal_param()在SQL 编译阶段被调用接收一个具体 Python 值并返回要渲染进输出字符串的文本它与执行阶段处理实际参数值的process_bind_param()是两回事二者职责不同不可混淆。五、渲染 POSTCOMPILE 扩展参数为绑定参数SQLAlchemy 有一种“延迟求值”的绑定参数变体即BindParameter的expanding属性见 elements.py 中bindparam()的实现编译 SQL 构造时它先渲染为一种中间状态待真正执行、传入实际值时才进一步展开。默认情况下ColumnOperators.in_()就使用扩展参数这样 SQL 字符串可以独立于每次调用传入的具体列表而被安全缓存。 stmt select(A).where(A.id.in_([1, 2, 3]))若要把IN子句渲染成真实的绑定参数符号可在compile()时使用render_postcompileTrue e create_engine(postgresqlpsycopg2://) print(stmt.compile(e, compile_kwargs{render_postcompile: True})) SELECT a.id, a.data FROM a WHERE a.id IN (%(id_1_1)s, %(id_1_2)s, %(id_1_3)s)5.1literal_binds隐含开启render_postcompile对于只含 int/str 等基本类型的语句literal_binds会自动把render_postcompile置为 True因此可以直接字符串化# literal_binds 隐含 render_postcompile print(stmt.compile(e, compile_kwargs{literal_binds: True})) SELECT a.id, a.data FROM a WHERE a.id IN (1, 2, 3)5.2 与params/positiontup的兼容SQLCompiler.params与SQLCompiler.positiontup同样兼容render_postcompile因此上一节中“渲染内联绑定参数”的配方在扩展参数场景下完全可用。例如 SQLite 的位置形式 u1, u2, u3 uuid.uuid4(), uuid.uuid4(), uuid.uuid4() stmt select(A).where(A.data.in_([u1, u2, u3])) import re e create_engine(sqlitepysqlite://) compiled stmt.compile(e, compile_kwargs{render_postcompile: True}) params (repr(compiled.params[name]) for name in compiled.positiontup) print(re.sub(r\?, lambda m: next(params), str(compiled))) SELECT a.id, a.data FROM a WHERE a.data IN (UUID(aa1944d6-9a5a-45d5-b8da-0ba1ef0a4f38), UUID(a81920e6-15e2-4392-8a3c-d775ffa9ccd2), UUID(b5574cdb-ff9b-49a3-be52-dbc89f087bfa))5.3 源码视角POSTCOMPILE 如何工作在 compiler.py 的visit_bindparam()中可以看到render_postcompile的处理当参数属于expanding或literal_execute时编译器标记self._render_postcompile True并通过bindparam_string()输出__[POSTCOMPILE_...]形式的中间占位符而_literal_execute_expanding_parameter_literal_binds()compiler.py负责在literal_binds模式下把扩展参数展开为多个字面量。positiontup则在编译过程中被填充为按出现顺序排列的参数名列表供params访问器按位置取值。六、必须牢记的安全边界官方 FAQ 对所有“绕过绑定参数、把字面值写进 SQL”的配方给出了统一的红色警告上述代码只有在满足以下全部条件时才可以使用仅用于调试目的字符串不会发给生产数据库执行只针对本地、可信输入。这些字符串化字面值的技术在任何意义上都不安全绝不应针对生产数据库使用。它们存在的意义是帮助开发者查看 SQL 形态、排查问题而不是替代参数化查询。七、字符串化时百分号为何被翻倍许多 DBAPI 使用pyformat或format风格的 paramstyle其语法必然涉及百分号。这类 DBAPI 通常要求语句中其他用途的百分号以双写形式即转义出现例如SELECT a, b FROM some_table WHERE a %s AND c %s AND num %% modulus 0SQLAlchemy 把语句交给 DBAPI 时绑定参数的替换机制与 Python 字符串插值运算符%一致许多 DBAPI 甚至直接使用该运算符。上面的语句替换绑定参数后就变成SELECT a, b FROM some_table WHERE a 5 AND c 10 AND num % modulus 0默认编译器对 PostgreSQL默认 DBAPI 为 psycopg2、MySQL默认 DBAPI 为 mysqlclient等数据库就带有这种百分号转义行为 from sqlalchemy import table, column from sqlalchemy.dialects import postgresql t table(my_table, column(value % one), column(value % two)) print(t.select().compile(dialectpostgresql.dialect())) SELECT my_table.value %% one, my_table.value %% two FROM my_table7.1 移除百分号的两种办法如果使用此类方言、又想要不含绑定参数符号的“非 DBAPI”语句一种快捷方式是直接用 Python 的%运算符替换入一组空参数 strstmt str(t.select().compile(dialectpostgresql.dialect())) print(strstmt % ()) SELECT my_table.value % one, my_table.value % two FROM my_table另一种是给方言设置不同的参数风格所有Dialect实现都接受paramstyle参数编译器将据此改用指定风格。下面把非常通用的named风格设进编译所用方言百分号在编译后的 SQL 中就不再具有特殊含义、也不会再被转义 print(t.select().compile(dialectpostgresql.dialect(paramstylenamed))) SELECT my_table.value % one, my_table.value % two FROM my_table八、op()自定义运算符的括号问题Operators.op()方法允许创建 SQLAlchemy 未知的自定义数据库运算符 print(column(q).op(-)(column(p))) q - p但当它出现在复合表达式的右侧时括号不会按预期生成 print((column(q1) column(q2)).op(-)(column(p))) q1 q2 - p这里我们大概率期望的是(q1 q2) - p。8.1 用 precedence 参数解决解决方案是设置运算符的优先级Operators.op.precedence参数取一个较大数值即可。100 是最大值而 SQLAlchemy 现有运算符使用的最高数值目前为 15 print((column(q1) column(q2)).op(-, precedence100)(column(p))) (q1 q2) - p8.2 用 self_group() 强制加括号对于二元表达式即有左右操作数和运算符的表达式通常也可以用ColumnElement.self_group()强制加括号 print((column(q1) column(q2)).self_group().op(-)(column(p))) (q1 q2) - p8.3 括号规则为何如此设计很多数据库对“过多的括号”或“位置别扭的括号”会报错所以 SQLAlchemy 不按分组关系生成括号而是依据运算符优先级与是否已知为可结合来决定最小化地生成括号。否则像column(a) column(b) column(c) column(d)会被渲染成(((a AND b) AND c) AND d)——虽然没错但既啰嗦又容易被用户当成 bug 上报。而另一些场景下如column(q, ARRAY(Integer, dimensions2))[5][6]会产生((q[5])[6])这类更可能让数据库困惑、至少损害可读性的结果。还有一些边缘情况会得到(x) 7数据库同样不喜欢。因此加括号不是“无脑套括号”而是以运算符优先级与结合性为依据判断分组。Operators.op()的 precedence 默认值为0。8.4 如果把默认优先级设为 100 会怎样官方 FAQ 讨论了一个思想实验假如把op.precedence默认值设为 100即最高值结果如何看下面两组对比等价的两组左操作数为复合表达式时括号可有可无 print((column(q) - column(y)).op(, precedence100)(column(z))) (q - y) z print((column(q) - column(y)).op()(column(z))) q - y z不等价的两组右操作数为复合表达式时括号位置决定了语义 print(column(q) - column(y).op(, precedence100)(column(z))) q - y z print(column(q) - column(y).op()(column(z))) q - (y z)结论是只要基于优先级与结合性做括号化对于一个没有给定优先级的通用运算符很难找到一种在所有情况下都自动正确的加括号策略——因为你有时希望自定义运算符比其他运算符优先级更低有时又希望它更高。FAQ 也提到或许未来可以让op()在左侧是复合表达式时强制调用self_group()因为“复合表达式出现在左侧时总能无害地加括号”但目前保持括号规则内部一致仍是更稳妥的做法。延伸阅读运算符的括号规则在 Operator Reference 的 operators_parentheses 一节中有系统阐述。九、小结何时用哪种方案场景推荐方案简单语句、无方言差异str(statement)含方言特有元素statement.compile(engine)或statement.compile(dialect...)日志/调试需要看到 int/str 值compile_kwargs{literal_binds: True}IN (...)等扩展参数要展开compile_kwargs{render_postcompile: True}复杂类型UUID 等需要带类型字面量DBAPImogrify()、paramstyle拼接、ext.compiler定制、TypeDecorator.process_literal_param编译结果中出现%%换用paramstylenamed或用strstmt % ()清理自定义运算符括号不对调高op(precedence...)或使用self_group()始终牢记所有把字面值内联进 SQL 的技法都仅限调试、仅限本地可信输入、绝不面向生产数据库。正确理解str()、compile()、literal_binds、render_postcompile、params/positiontup以及op()括号规则就能在日志记录、SQL 审查与问题排查中游刃有余。想深入了解实现细节可继续阅读 compiler.py 的SQLCompiler类、elements.py 的CompilerElement.compile()以及 type_api.py 的TypeDecorator一节。【免费下载链接】sqlalchemyThe Database Toolkit for Python项目地址: https://gitcode.com/gh_mirrors/sq/sqlalchemy创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考