SqlAlchemy 2.0 中文文档（五十三）（5）-阿里云开发者社区

SqlAlchemy 2.0 中文文档（五十三）（4）https://developer.aliyun.com/article/1563175

将绑定参数内联渲染

警告

永远不要使用这些技术处理来自不受信任输入的字符串内容，比如来自网络表单或其他用户输入应用程序。SQLAlchemy 将 Python 值强制转换为直接的 SQL 字符串值的能力不安全且不验证传递的数据类型。在针对关系数据库进行非 DDL SQL 语句的编程调用时，始终使用绑定参数。

上述形式将渲染传递给 Python DBAPI 的 SQL 语句，其中包括绑定参数不会内联渲染。SQLAlchemy 通常不会字符串化绑定参数，因为这由 Python DBAPI 适当处理，更不用说绕过绑定参数可能是现代 Web 应用程序中被广泛利用的安全漏洞之一。SQLAlchemy 在某些情况下（如发出 DDL）有限地执行此字符串化。为了访问此功能，可以使用传递给 compile_kwargs 的 literal_binds 标志：

from sqlalchemy.sql import table, column, select
t = table("t", column("x"))
s = select(t).where(t.c.x == 5)
# **do not use** with untrusted input!!!
print(s.compile(compile_kwargs={"literal_binds": True}))
# to render for a specific dialect
print(s.compile(dialect=dialect, compile_kwargs={"literal_binds": True}))
# or if you have an Engine, pass as first argument
print(s.compile(some_engine, compile_kwargs={"literal_binds": True}))

此功能主要用于日志记录或调试目的，其中查询的原始 SQL 字符串可能会证明有用。

上述方法的注意事项是，它仅支持基本类型，如整数和字符串，而且如果直接使用没有预设值的 bindparam()，它也无法将其字符串化。在下面详细描述了无条件字符串化所有参数的方法。

提示

SQLAlchemy 不支持所有数据类型的完全字符串化的原因有三：

当正常使用 DBAPI 时，已经支持此功能。SQLAlchemy 项目不能被要求为所有后端的每种数据类型复制此功能，因为这是多余的工作，还会产生重大的测试和持续支持开销。
对于特定数据库，将边界参数内联化字符串化建议使用实际将这些完全字符串化的语句传递给数据库执行。这是不必要且不安全的，SQLAlchemy 不希望以任何方式鼓励这种用法。
字面值渲染领域是最有可能报告安全问题的领域。SQLAlchemy 尽量使安全参数字符串化领域成为 DBAPI 驱动程序的问题，其中每个 DBAPI 的具体情况都可以得到适当和安全地处理。

由于 SQLAlchemy 故意不支持对字面值的完全字符串化，因此在特定调试场景中进行这样的技术包括以下内容。例如，我们将使用 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_key=True)
    data = Column(UUID)
stmt = select(A).where(A.data == uuid.uuid4())

针对以上模型和语句将比较一列与单个 UUID 值的情况，使用内联值对该语句进行字符串化的选项包括：

一些 DBAPI（如 psycopg2）支持像 mogrify() 这样的辅助函数，提供对它们的字面值渲染功能的访问。要使用这些特性，渲染 SQL 字符串时不要使用 literal_binds，而是通过 SQLCompiler.params 访问器分别传递参数：

e = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")
with e.connect() as conn:
    cursor = conn.connection.cursor()
    compiled = stmt.compile(e)
    print(cursor.mogrify(str(compiled), compiled.params))

上述代码将产生 psycopg2 的原始字节字符串：

b"SELECT a.id, a.data \nFROM a \nWHERE a.data = 'a511b0fc-76da-4c47-a4b4-716a8189b7ac'::uuid"

直接将 SQLCompiler.params 渲染到语句中，使用目标 DBAPI 的适当 paramstyle。例如，psycopg2 DBAPI 使用命名的 pyformat 样式。 render_postcompile 的含义将在下一节中讨论。 警告：这不安全，请不要使用不受信任的输入：

e = create_engine("postgresql+psycopg2://")
# will use pyformat style, i.e. %(paramname)s for param
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

另一个示例使用了位置参数风格，如 qmark，我们还可以使用 SQLCompiler.positiontup 集合与 SQLCompiler.params 结合使用，以便按编译后的语句中的位置顺序检索参数：

import re
e = create_engine("sqlite+pysqlite://")
# will use qmark style, i.e. ? for param
compiled = stmt.compile(e, compile_kwargs={"render_postcompile": True})
# params in positional order
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')

当存在用户定义的标志时，使用自定义 SQL 构造和编译扩展扩展以自定义方式渲染 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_recipe=False, **kw):
    if not use_my_literal_recipe:
        # use normal bindparam processing
        return compiler.visit_bindparam(element, **kw)
    # if use_my_literal_recipe was passed to compiler_kwargs,
    # render the value directly
    return repr(element.value)
e = create_engine("postgresql+psycopg2://")
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')

对于内置于模型或语句中的特定类型字符串化，可以使用 TypeDecorator 类来使用 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')

将 “POSTCOMPILE” 参数呈现为绑定参数

SQLAlchemy 包含一个称为BindParameter.expanding的绑定参数变体，这是一个“延迟评估”的参数，当编译 SQL 结构时以中间状态呈现，然后在语句执行时进一步处理，当实际已知值被传递时。 “扩展”参数默认用于ColumnOperators.in_()表达式，以便 SQL 字符串可以安全地独立于传递给ColumnOperators.in_()的特定调用的实际值被缓存：

>>> stmt = select(A).where(A.id.in_([1, 2, 3]))

要使用实际的绑定参数符号呈现 IN 子句，请在ClauseElement.compile()中使用render_postcompile=True标志：

>>> e = create_engine("postgresql+psycopg2://")
>>> 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)

在先前有关渲染绑定参数的部分中描述的literal_binds标志会自动将render_postcompile设置为 True，因此对于具有简单 int/字符串的语句，可以直接将它们字符串化：

# render_postcompile is implied by literal_binds
>>> print(stmt.compile(e, compile_kwargs={"literal_binds": True}))
SELECT  a.id,  a.data
FROM  a
WHERE  a.id  IN  (1,  2,  3)

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("sqlite+pysqlite://")
>>> 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'))

警告

请记住，所有上述字符串化文字值的代码示例，当将语句发送到数据库时绕过绑定参数的使用，只能在以下情况下使用：

仅用于调试目的
该字符串不应传递给实时生产数据库
仅限于本地，可信任的输入

上述用于将文字值字符串化的方法在任何情况下都不安全，绝对不应该用于生产数据库。

为什么在将 SQL 语句字符串化时百分号会被加倍？

许多 DBAPI 实现采用pyformat或format paramstyle，这在其语法中必然涉及百分号。这样做的大多数 DBAPI 都希望在使用的语句的字符串形式中，用于其他目的的百分号被双倍化（即转义），例如：

SELECT  a,  b  FROM  some_table  WHERE  a  =  %s  AND  c  =  %s  AND  num  %%  modulus  =  0

当 SQL 语句通过 SQLAlchemy 传递给底层 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(dialect=postgresql.dialect()))
SELECT  my_table."value %% one",  my_table."value %% two"
FROM  my_table

当使用这样的方言时，如果需要不包含绑定参数符号的非 DBAPI 语句，一种快速删除百分号的方法是直接使用 Python 的%运算符替换一个空的参数集：

>>> strstmt = str(t.select().compile(dialect=postgresql.dialect()))
>>> print(strstmt % ())
SELECT  my_table."value % one",  my_table."value % two"
FROM  my_table

另一种方法是在使用的方言上设置不同的参数样式；所有Dialect实现都接受一个paramstyle参数，该参数将导致该方言的编译器使用给定的参数样式。下面，非常常见的named参数样式在用于编译的方言中设置，以便百分号在 SQL 的编译形式中不再重要，并且不再被转义：

>>> print(t.select().compile(dialect=postgresql.dialect(paramstyle="named")))
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。

对于这种情况的解决方案是设置运算符的优先级，使用Operators.op.precedence参数，设置为一个高数字，其中 100 是最大值，当前任何 SQLAlchemy 运算符使用的最高数字是 15：

>>> print((column("q1") + column("q2")).op("->", precedence=100)(column("p")))
(q1  +  q2)  ->  p

我们还可以通常通过使用ColumnElement.self_group()方法强制在二元表达式（例如具有左/右操作数和运算符的表达式）周围加上括号：

>>> print((column("q1") + column("q2")).self_group().op("->")(column("p")))
(q1  +  q2)  ->  p

为什么括号的规则是这样的？

当存在过多的括号或者括号处于数据库不期望的不寻常位置时，许多数据库会报错，因此 SQLAlchemy 不基于分组生成括号，它使用操作符优先级以及如果操作符已知是可结合的，则生成最小的括号。否则，表达式如下：

column("a") & column("b") & column("c") & column("d")

将产生：

(((a  AND  b)  AND  c)  AND  d)

这样做可能会让人们感到不爽（并被报告为错误）。在其他情况下，它会导致更容易让数据库混淆或至少降低可读性，比如：

column("q", ARRAY(Integer, dimensions=2))[5][6]

将产生：

((q[5])[6])

还有一些边界情况，我们会得到像"(x) = 7"这样的东西，数据库真的不喜欢这样。因此，括号化不是简单地添加括号，而是使用运算符优先级和结合性来确定分组。

对于Operators.op()，优先级的值默认为零。

如果我们将Operators.op.precedence的值默认为 100，即最高值，会怎样呢？然后这个表达式会多加括号，但除此之外还是可以的，也就是说，这两个表达式是等价的：

>>> print((column("q") - column("y")).op("+", precedence=100)(column("z")))
(q  -  y)  +  z
>>> print((column("q") - column("y")).op("+")(column("z")))
q  -  y  +  z

但是这两种情况不是：

>>> print(column("q") - column("y").op("+", precedence=100)(column("z")))
q  -  y  +  z
>>> print(column("q") - column("y").op("+")(column("z")))
q  -  (y  +  z)

目前来看，只要我们根据运算符的优先级和结合性进行括号化，如果真的有一种方法可以自动为没有给定优先级的通用运算符进行括号化，从而在所有情况下都能正常工作，这还不清楚，因为有时您希望自定义的运算符具有比其他运算符更低的优先级，有时您希望它更高。

如果上面的“binary”表达式强制在调用op()时使用self_group()方法，假设左侧的复合表达式总是可以无害地加上括号，那么这种可能性是存在的。也许这种改变以后可以实现，但是目前来看，保持括号规则在内部更一致似乎是更安全的方法。

为什么括号规则会是这样？

当括号过多或者括号出现在它们不期望的不寻常位置时，许多数据库会抛出错误，因此 SQLAlchemy 不基于分组生成括号，而是使用运算符优先级，如果运算符已知为结合性，那么会尽量生成最少的括号。否则，表达式如下：

column("a") & column("b") & column("c") & column("d")

会产生：

(((a  AND  b)  AND  c)  AND  d)

这是可以的，但可能会让人们感到烦恼（并报告为错误）。在其他情况下，它会导致更容易让数据库混淆，或者至少影响可读性，比如：

column("q", ARRAY(Integer, dimensions=2))[5][6]

会产生：

((q[5])[6])

对于Operators.op()，优先级的值默认为零。

>>> print((column("q") - column("y")).op("+", precedence=100)(column("z")))
(q  -  y)  +  z
>>> print((column("q") - column("y")).op("+")(column("z")))
q  -  y  +  z

但是这两种情况不是：

>>> print(column("q") - column("y").op("+", precedence=100)(column("z")))
q  -  y  +  z
>>> print(column("q") - column("y").op("+")(column("z")))
q  -  (y  +  z)

现在，尚不清楚只要我们基于操作符优先级和结合性进行括号化，是否真的有一种方法可以自动为没有给定优先级的通用运算符添加括号，以便在所有情况下都能正常工作，因为有时您希望自定义操作符的优先级低于其他操作符，有时您希望它更高。

也许，如果上面的“二元”表达式在调用op()时强制使用了self_group()方法，假设左侧的复合表达式总是可以无害地加括号。也许这种改变可以在某个时候实现，然而就目前而言，保持括号规则更加内部一致似乎是更安全的做法。

(((a  AND  b)  AND  c)  AND  d)

这样做可能会让人们感到不爽（并被报告为错误）。在其他情况下，它会导致更容易让数据库混淆或至少降低可读性，比如：

column("q", ARRAY(Integer, dimensions=2))[5][6]

将产生：

((q[5])[6])

对于Operators.op()，优先级的值默认为零。

>>> print((column("q") - column("y")).op("+", precedence=100)(column("z")))
(q  -  y)  +  z
>>> print((column("q") - column("y")).op("+")(column("z")))
q  -  y  +  z

但是这两种情况不是：

>>> print(column("q") - column("y").op("+", precedence=100)(column("z")))
q  -  y  +  z
>>> print(column("q") - column("y").op("+")(column("z")))
q  -  (y  +  z)

为什么括号规则会是这样？

column("a") & column("b") & column("c") & column("d")

会产生：

(((a  AND  b)  AND  c)  AND  d)

这是可以的，但可能会让人们感到烦恼（并报告为错误）。在其他情况下，它会导致更容易让数据库混淆，或者至少影响可读性，比如：

column("q", ARRAY(Integer, dimensions=2))[5][6]

会产生：

((q[5])[6])

对于Operators.op()，优先级的值默认为零。

>>> print((column("q") - column("y")).op("+", precedence=100)(column("z")))
(q  -  y)  +  z
>>> print((column("q") - column("y")).op("+")(column("z")))
q  -  y  +  z

但是这两种情况不是：

>>> print(column("q") - column("y").op("+", precedence=100)(column("z")))
q  -  y  +  z
>>> print(column("q") - column("y").op("+")(column("z")))
q  -  (y  +  z)

SqlAlchemy 2.0 中文文档（五十三）（5）

将绑定参数内联渲染

将 “POSTCOMPILE” 参数呈现为绑定参数

为什么在将 SQL 语句字符串化时百分号会被加倍？

我正在使用 op()生成自定义运算符，但我的括号没出来正确

为什么括号的规则是这样的？

为什么括号规则会是这样？

为什么括号规则会是这样？

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

SqlAlchemy 2.0 中文文档（五十三）（5）

将绑定参数内联渲染

将 “POSTCOMPILE” 参数呈现为绑定参数

为什么在将 SQL 语句字符串化时百分号会被加倍？

我正在使用 op()生成自定义运算符，但我的括号没出来正确

为什么括号的规则是这样的？

为什么括号规则会是这样？

为什么括号规则会是这样？

热门文章

最新文章

相关电子书