Справочник оператора

В этом разделе подробно описано использование операторов, доступных для построения выражений SQL.

Эти методы представлены в терминах базовых классов Operators и ColumnOperators. Затем методы доступны потомкам этих классов, включая:

  • Column объекты

  • ColumnElement объекты в более общем смысле, которые являются корнем всех выражений уровня столбцов языка Core SQL Expression

  • InstrumentedAttribute объекты, которые являются отображаемыми атрибутами уровня ORM.

Операторы впервые представлены в обучающих разделах, включая:

Операторы сравнения

Базовые сравнения, которые применяются ко многим типам данных, включая числа, строки, даты и многие другие:

  • ColumnOperators.__eq__() (оператор Python «==»):

    >>> print(column("x") == 5)
    x = :x_1
  • ColumnOperators.__ne__() (оператор Python «!=»):

    >>> print(column("x") != 5)
    x != :x_1
  • ColumnOperators.__gt__() (оператор Python «>»):

    >>> print(column("x") > 5)
    x > :x_1
  • ColumnOperators.__lt__() (оператор Python «<»):

    >>> print(column("x") < 5)
    x < :x_1
  • ColumnOperators.__ge__() (оператор Python «>=»):

    >>> print(column("x") >= 5)
    x >= :x_1
  • ColumnOperators.__le__() (оператор Python «<=»):

    >>> print(column("x") <= 5)
    x <= :x_1
  • ColumnOperators.between():

    >>> print(column("x").between(5, 10))
    x BETWEEN :x_1 AND :x_2

Сравнения IN

Оператор SQL IN - это отдельная тема в SQLAlchemy. Поскольку оператор IN обычно используется против списка фиксированных значений, функция SQLAlchemy по принуждению связанных параметров использует специальную форму компиляции SQL, которая выдает промежуточную строку SQL для компиляции, которая формируется в окончательный список связанных параметров на втором этапе. Другими словами, «это просто работает».

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) [...] ()

Приведенный выше подзапрос «пустое множество» обобщается корректно и также приводится в терминах оператора 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]))

Выражения типа IN

Сравнение кортежей с кортежами является обычным для IN, так как среди прочих случаев использования учитывает случай, когда сопоставление строк с набором потенциальных составных значений первичного ключа. Конструкция tuple_() обеспечивает базовый строительный блок для сравнения кортежей. Оператор Tuple.in_() получает список кортежей:

>>> 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',)]

Подзапрос IN

Наконец, операторы ColumnOperators.in_() и ColumnOperators.not_in() работают с подзапросами. Форма предусматривает, что конструкция Select передается непосредственно, без явного преобразования в именованный подзапрос:

>>> 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_():

    Этот оператор предоставляет SQL для «x IS y», который чаще всего выглядит как «<expr> IS NULL». Константу NULL проще всего получить с помощью обычного Python None:

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

    SQL NULL также доступен в явном виде, если это необходимо, с помощью конструкции null():

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

    Оператор ColumnOperators.is_() автоматически вызывается при использовании перегруженного оператора ColumnOperators.__eq__(), то есть ==, в сочетании со значением None или null(). Таким образом, обычно нет необходимости использовать ColumnOperators.is_() явно, особенно при использовании с динамическим значением:

    >>> 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 IS DISTINCT FROM:

    >>> print(column("x").is_distinct_from("some value"))
    x IS DISTINCT FROM :x_1
  • ColumnOperators.isnot_distinct_from():

    Выдает SQL IS NOT DISTINCT FROM:

    >>> print(column("x").isnot_distinct_from("some value"))
    x IS NOT DISTINCT FROM :x_1

Сравнение строк

  • ColumnOperators.like():

    >>> print(column("x").like("word"))
    x LIKE :x_1
  • ColumnOperators.ilike():

    Нечувствительный к регистру LIKE использует функцию SQL lower() на общем бэкенде. На бэкенде PostgreSQL она будет использовать ILIKE:

    >>> print(column("x").ilike("word"))
    lower(x) LIKE lower(:x_1)
  • ColumnOperators.notlike():

    >>> print(column("x").notlike("word"))
    x NOT LIKE :x_1
  • ColumnOperators.notilike():

    >>> print(column("x").notilike("word"))
    lower(x) NOT LIKE lower(:x_1)

Сдерживание строк

Операторы сдерживания строк в основном построены как комбинация LIKE и оператора конкатенации строк, который в большинстве бэкендов представляет собой || или иногда функцию типа concat():

  • ColumnOperators.startswith():

    The string containment operators
    >>> print(column("x").startswith("word"))
    x LIKE :x_1 || '%'
  • ColumnOperators.endswith():

    >>> print(column("x").endswith("word"))
    x LIKE '%' || :x_1
  • ColumnOperators.contains():

    >>> print(column("x").contains("word"))
    x LIKE '%' || :x_1 || '%'

Сопоставление строк

Операторы сопоставления всегда зависят от бэкенда и могут давать разное поведение и результаты в разных базах данных:

  • 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

    Этот оператор доступен через ColumnOperators.__add__(), то есть оператор Python +, при работе с выражением столбца, которое является производным от String:

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

    Оператор выдает соответствующую специфическую для базы данных конструкцию, например, в MySQL это исторически была SQL-функция concat():

    >>> 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():

    Производит SQL-оператор COLLATE, который обеспечивает определенные колляции во время выражения:

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

    Чтобы использовать 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

Арифметические операторы

  • ColumnOperators.__add__(), ColumnOperators.__radd__() (оператор Python «+»):

    >>> print(column("x") + 5)
    x + :x_1
    
    >>> print(5 + column("x"))
    :x_1 + x

    Обратите внимание, что если тип данных выражения String или аналогичный, оператор ColumnOperators.__add__() вместо него выдает string concatenation.

  • ColumnOperators.__sub__(), ColumnOperators.__rsub__() (оператор Python «-«):

    >>> print(column("x") - 5)
    x - :x_1
    
    >>> print(5 - column("x"))
    :x_1 - x
  • ColumnOperators.__mul__(), ColumnOperators.__rmul__() (оператор Python «*»):

    >>> print(column("x") * 5)
    x * :x_1
    
    >>> print(5 * column("x"))
    :x_1 * x
  • ColumnOperators.__div__(), ColumnOperators.__rdiv__() (оператор Python «/»):

    >>> print(column("x") / 5)
    x / :x_1
    >>> print(5 / column("x"))
    :x_1 / x
  • ColumnOperators.__mod__(), ColumnOperators.__rmod__() (оператор Python «%»):

    >>> print(column("x") % 5)
    x % :x_1
    >>> print(5 % column("x"))
    :x_1 % x

Использование конъюнкции и отрицания

Самая распространенная связка, «AND», применяется автоматически, если мы неоднократно используем метод Select.where(), а также аналогичные методы, такие как 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

Настройка оператора

TODO

Вернуться на верх