SQL 表达式¶

如何将 SQL 表达式渲染为字符串，并可能内联绑定参数？¶

SQLAlchemy Core 语句对象或表达式片段，以及 ORM Query 对象的“字符串化”，在大多数简单情况下，就像使用 str() 内置函数一样简单，如下所示，当与 print 函数一起使用时（请注意，如果我们不显式使用 Python print 函数，它也会自动调用 str()）

>>> fromsqlalchemyimporttable,column,select>>> t=table("my_table",column("x"))>>> statement=select(t)>>> print(str(statement))SELECT my_table.x
FROM my_table

str() 内置函数或等效函数可以对 ORM Query 对象以及任何语句（例如 select()、insert() 等）以及任何表达式片段调用，例如

>>> fromsqlalchemyimportcolumn>>> print(column("x")=="some value")x = :x_1

为特定数据库字符串化¶

当我们要字符串化的语句或片段包含具有数据库特定字符串格式的元素，或者当它包含仅在某种数据库中可用的元素时，会出现复杂情况。在这些情况下，我们可能会得到一个字符串化的语句，其语法对于我们目标数据库而言不正确，或者操作可能会引发 UnsupportedCompilationError 异常。在这些情况下，必须使用 ClauseElement.compile() 方法字符串化语句，同时传递表示目标数据库的 Engine 或 Dialect 对象。例如，如果我们有一个 MySQL 数据库引擎，我们可以根据 MySQL 方言字符串化语句

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://scott:tiger@localhost/test")
print(statement.compile(engine))

更直接地，无需构建 Engine 对象，我们可以直接实例化 Dialect 对象，如下所示，我们使用 PostgreSQL 方言

from sqlalchemy.dialects import postgresql

print(statement.compile(dialect=postgresql.dialect()))

请注意，可以使用 create_engine() 本身以及虚拟 URL 来组装任何方言，然后访问 Engine.dialect 属性，例如，如果我们想要 psycopg2 的方言对象

e = create_engine("postgresql+psycopg2://")
psycopg2_dialect = e.dialect

当给定 ORM Query 对象时，为了访问 ClauseElement.compile() 方法，我们只需要先访问 Query.statement 访问器

statement = query.statement
print(statement.compile(someengine))

内联渲染绑定参数¶

警告

永远不要将这些技术与从不受信任的输入（例如来自 Web 表单或其他用户输入应用程序）接收的字符串内容一起使用。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 字符串可能很有用。

上述方法存在一些注意事项，即它仅支持基本类型（例如 ints 和 strings），此外，如果直接使用没有预设值的 bindparam()，它也无法字符串化。以下详细介绍了无条件字符串化所有参数的方法。

提示

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

当正常使用 DBAPI 时，正在使用的 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()，它们提供对其文字渲染功能的访问。要使用此类功能，请在不使用 literal_binds 的情况下渲染 SQL 字符串，并通过 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，我们可以根据 SQLite 渲染上面的语句，方法是同时使用 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 构造时，它以中间状态渲染，然后在语句执行时当实际已知值传递时进一步处理。“Expanding” 参数默认用于 ColumnOperators.in_() 表达式，以便可以独立于传递给 ColumnOperators.in_() 特定调用的实际值列表安全地缓存 SQL 字符串

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

要使用实际绑定参数符号渲染 IN 子句，请将 render_postcompile=True 标志与 ClauseElement.compile() 一起使用

>>> 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，因此对于具有简单 ints/strings 的语句，可以直接字符串化它们

# 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]))>>> importre>>> e=create_engine("sqlite+pysqlite://")>>> compiled=stmt.compile(e,compile_kwargs={"render_postcompile":True})>>> params=(repr(compiled.params[name])fornameincompiled.positiontup)>>> print(re.sub(r"\?",lambdam: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

当 SQLAlchemy 将 SQL 语句传递给底层 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）等数据库的默认编译器将具有此百分号转义行为

>>> fromsqlalchemyimporttable,column>>> fromsqlalchemy.dialectsimportpostgresql>>> 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)

就目前而言，尚不清楚只要我们基于运算符优先级和结合性进行括号化，如果真的有一种方法可以为没有给定优先级的通用运算符自动进行括号化，并且在所有情况下都有效，因为有时您希望自定义操作符的优先级低于其他运算符，有时您希望它更高。

如果上面的“二进制”表达式在调用 op() 时强制使用 self_group() 方法，假设左侧的复合表达式始终可以无害地进行括号化，则可能是可能的。也许可以在某个时候进行此更改，但是目前，保持括号化规则在内部更加一致似乎是更安全的方法。

SQLAlchemy 2.0 文档

SQLAlchemy 2.0 文档

常见问题解答

项目版本