算子参考


本节详细介绍了可用于构造 SQL 表达式的运算符的用法。


这些方法以 Operators 和 ColumnOperators 基类。然后，这些方法可用于这些类的后代，包括：


这些运算符首先在教程部分中介绍，包括：


比较运算符


适用于许多数据类型的基本比较，包括数字、字符串、日期和许多其他数据类型：


IN 比较


SQL IN 运算符是 SQLAlchemy 中自己的主题。由于 IN 运算符通常用于固定值列表，因此 SQLAlchemy 的绑定参数强制功能利用了一种特殊形式的 SQL 编译，该格式呈现一个用于编译的临时 SQL 字符串，该字符串在第二步中形成最终的绑定参数列表。换句话说，“它就是好用”。


IN 对值列表的 IN¶


IN 通常可以通过将值列表传递给 ColumnOperators.in_（） 方法来实现：

>>> print(column("x").in_([1, 2, 3]))

x IN (__[POSTCOMPILE_x_1])


特殊绑定形式 __[POSTCOMPILE 在执行时渲染为单个参数，如下所示：

>>> stmt = select(User.id).where(User.id.in_([1, 2, 3]))
>>> result = conn.execute(stmt)

SELECT user_account.id
FROM user_account
WHERE user_account.id IN (?, ?, ?)
[...] (1, 2, 3)


空 IN 表达式


SQLAlchemy 通过呈现不返回任何行的特定于后端的子查询，为空 IN 表达式生成数学上有效的结果。换句话说，“它就是好用”：

>>> stmt = select(User.id).where(User.id.in_([]))
>>> result = conn.execute(stmt)

SELECT user_account.id
FROM user_account
WHERE user_account.id IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
[...] ()


上面的 “empty set” 子查询可以正确泛化，并且也根据 IN 运算符呈现，该运算符保持不变。


不在


“NOT IN” 可通过 ColumnOperators.not_in（） 运算符使用：

>>> print(column("x").not_in([1, 2, 3]))

(x NOT IN (__[POSTCOMPILE_x_1]))


这通常更容易通过使用 ~ 运算符来否定：

>>> print(~column("x").in_([1, 2, 3]))

(x NOT IN (__[POSTCOMPILE_x_1]))


Tuple IN 表达式


元组与元组的比较在 IN 中很常见，因为在其他用例中，当将行与一组潜在的复合主键值匹配时，它适用于这种情况。tuple_（） 结构为 Tuples 比较提供了基本构建块。然后，Tuple.in_（） 运算符接收一个 Tuples 列表：

>>> from sqlalchemy import tuple_
>>> tup = tuple_(column("x", Integer), column("y", Integer))
>>> expr = tup.in_([(1, 2), (3, 4)])
>>> print(expr)

(x, y) IN (__[POSTCOMPILE_param_1])


为了说明渲染的参数：

>>> tup = tuple_(User.id, Address.id)
>>> stmt = select(User.name).join(Address).where(tup.in_([(1, 1), (2, 2)]))
>>> conn.execute(stmt).all()

SELECT user_account.name
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE (user_account.id, address.id) IN (VALUES (?, ?), (?, ?))
[...] (1, 1, 2, 2)

[('spongebob',), ('sandy',)]


子查询输入


最后，ColumnOperators.in_（） 和 ColumnOperators.not_in（） 运算符使用子查询。 该表单提供 Select construct 直接传入，而无需显式转换为 子查询：

>>> print(column("x").in_(select(user_table.c.id)))

x IN (SELECT user_account.id
FROM user_account)


元组按预期工作：

>>> print(
...     tuple_(column("x"), column("y")).in_(
...         select(user_table.c.id, address_table.c.id).join(address_table)
...     )
... )

(x, y) IN (SELECT user_account.id, address.id
FROM user_account JOIN address ON user_account.id = address.user_id)


身份比较


这些运算符涉及对特殊 SQL 值的测试，例如 NULL，布尔常量，例如某些数据库支持的 true 或 false：


  • ColumnOperators.is_（） 中：


    此运算符将准确提供 “x IS y” 的 SQL，最常见的是 “<expr> IS NULL”。NULL 常量最容易使用常规 Python None 获取：

    >>> print(column("x").is_(None))

    x IS NULL


    如果需要，也可以使用 null（） 结构中：

    >>> from sqlalchemy import null
>>> print(column("x").is_(null()))

    x IS NULL


    使用 ColumnOperators.is_（） 重载运算符时，会自动调用 ColumnOperators.__eq___（） 运算符，即 == 与 None 或 null（） 值结合使用。这样，通常不需要使用 ColumnOperators.is_（） 显式地，尤其是在与 dynamic value 一起使用时：

    >>> a = None
>>> print(column("x") == a)

    x IS NULL


    请注意，Python is 运算符不会重载。尽管 Python 提供了重载运算符（如 == 和 ！=）的钩子，但它没有提供任何重新定义 is 的方法。


  • ColumnOperators.is_not（） 中：


    与 ColumnOperators.is_（） 类似，生成 “IS NOT”：

    >>> print(column("x").is_not(None))

    x IS NOT NULL


    同样等价于 ！= None：

    >>> print(column("x") != None)

    x IS NOT NULL


  • ColumnOperators.is_distinct_from() ：


    生成 SQL 不同于：

    >>> print(column("x").is_distinct_from("some value"))

    x IS DISTINCT FROM :x_1


  • ColumnOperators.isnot_distinct_from() ：


    生成 SQL 与以下各项没有区别：

    >>> print(column("x").isnot_distinct_from("some value"))

    x IS NOT DISTINCT FROM :x_1


字符串比较


字符串包含


字符串包含运算符基本上是作为 LIKE 和字符串连接运算符的组合构建的，在大多数后端或 concat（） 等函数上是 ||：


字符串匹配


匹配运算符始终特定于后端，并且可能在不同的数据库上提供不同的行为和结果：


  • ColumnOperators.match（） 中：


    这是一个特定于方言的运算符，它使用底层数据库的 MATCH 功能（如果可用）：

    >>> print(column("x").match("word"))

    x MATCH :x_1


  • ColumnOperators.regexp_match() ：


    此运算符特定于方言。例如，我们可以用 PostgreSQL 方言来说明它：

    >>> from sqlalchemy.dialects import postgresql
>>> print(column("x").regexp_match("word").compile(dialect=postgresql.dialect()))

    x ~ %(x_1)s


    或 MySQL：

    >>> from sqlalchemy.dialects import mysql
>>> print(column("x").regexp_match("word").compile(dialect=mysql.dialect()))

    x REGEXP %s


字符串更改


  • ColumnOperators.concat（） 中：


    字符串连接：

    >>> print(column("x").concat("some string"))

    x || :x_1


    当使用派生自 String 的列表达式时，此运算符可通过 ColumnOperators.__add__（） （即 Python + 运算符）使用：

    >>> print(column("x", String) + "some string")

    x || :x_1


    该运算符将生成适当的特定于数据库的构造，例如在 MySQL 上，它历来是 concat（） SQL 函数：

    >>> print((column("x", String) + "some string").compile(dialect=mysql.dialect()))

    concat(x, %s)


  • ColumnOperators.regexp_replace() ：


    作为 ColumnOperators.regexp（） 的补充，这会为支持它的后端生成 REGEXP REPLACE 等效项：

    >>> print(column("x").regexp_replace("foo", "bar").compile(dialect=postgresql.dialect()))

    REGEXP_REPLACE(x, %(x_1)s, %(x_2)s)


  • ColumnOperators.collate（） 中：


    生成 COLLATE SQL 运算符，该运算符在表达式时提供特定的排序规则：

    >>> print(
...     (column("x").collate("latin1_german2_ci") == "Müller").compile(
...         dialect=mysql.dialect()
...     )
... )

    (x COLLATE latin1_german2_ci) = %s


    要对 Literal 值使用 COLLATE，请使用 literal（） 构造：

    >>> from sqlalchemy import literal
>>> print(
...     (literal("Müller").collate("latin1_german2_ci") == column("x")).compile(
...         dialect=mysql.dialect()
...     )
... )

    (%s COLLATE latin1_german2_ci) = x


算术运算符


按位运算符


位运算符函数提供对 不同的后端，它们应该在兼容的 整数和位字符串等值（例如 PostgreSQL BIT 等）。请注意，这些不是通用的布尔运算符。


2.0.2 版本中的新功能： 添加了用于按位运算的专用运算符。


使用连词和否定


如果我们重复使用 Select.where（） 方法以及类似的方法，例如，会自动应用最常见的连词 “AND” Update.where（） 和 Delete.where（）：

>>> print(
...     select(address_table.c.email_address)
...     .where(user_table.c.name == "squidward")
...     .where(address_table.c.user_id == user_table.c.id)
... )

SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id


Select.where（）、Update.where（） 和 Delete.where（） 也接受具有相同效果的多个表达式：

>>> print(
...     select(address_table.c.email_address).where(
...         user_table.c.name == "squidward",
...         address_table.c.user_id == user_table.c.id,
...     )
... )

SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id


“AND” 连词及其伙伴 “OR” 都可以直接使用 and_（） 和 or_（） 函数获得：

>>> from sqlalchemy import and_, or_
>>> print(
...     select(address_table.c.email_address).where(
...         and_(
...             or_(user_table.c.name == "squidward", user_table.c.name == "sandy"),
...             address_table.c.user_id == user_table.c.id,
...         )
...     )
... )

SELECT address.email_address
FROM address, user_account
WHERE (user_account.name = :name_1 OR user_account.name = :name_2)
AND address.user_id = user_account.id


可以使用 not_（） 函数进行否定。这通常会反转布尔表达式中的运算符：

>>> from sqlalchemy import not_
>>> print(not_(column("x") == 5))

x != :x_1


它还可能在适当时应用关键字，例如 NOT：

>>> from sqlalchemy import Boolean
>>> print(not_(column("x", Boolean)))

NOT x


连词运算符


上述连词函数 and_（）、or_（）、 not_（） 也可用作重载的 Python 运算符：


注意


Python &、|和~运算符在语言中具有较高的优先级;因此，通常必须对本身包含表达式的作数应用括号，如以下示例所示。


  • Operators.__and__（） （Python “&” 运算符）：


    Python二进制&运算符被重载以使其行为与and_（）相同（注意两个作数周围的括号）：

    >>> print((column("x") == 5) & (column("y") == 10))

    x = :x_1 AND y = :y_1


  • Operators.__or__（） （Python “|” 运算符）：


    Python 二进制 | 运算符被重载，其行为与 or_（） 相同（请注意两个作数周围的括号）：

    >>> print((column("x") == 5) | (column("y") == 10))

    x = :x_1 OR y = :y_1


  • Operators.__invert__（） （Python “~” 运算符）：


    Python 二进制 ~ 运算符被重载，其行为与 not_（） 相同，要么反转现有运算符，要么将 NOT 关键字应用于整个表达式：

    >>> print(~(column("x") == 5))

    x != :x_1

    >>> from sqlalchemy import Boolean
>>> print(~column("x", Boolean))

    NOT x