Справочник оператора¶
В этом разделе подробно описано использование операторов, доступных для построения выражений SQL.
Эти методы представлены в терминах базовых классов Operators
и ColumnOperators
. Затем методы доступны потомкам этих классов, включая:
Column
объектыColumnElement
объекты в более общем смысле, которые являются корнем всех выражений уровня столбцов языка Core SQL ExpressionInstrumentedAttribute
объекты, которые являются отображаемыми атрибутами уровня ORM.
Операторы впервые представлены в обучающих разделах, включая:
Самоучитель SQLAlchemy 1.4 / 2.0 - единый учебник в 2.0 style
Объектно-реляционный учебник (API 1.x) - Учебник по ORM в 1.x style
Учебник по языку выражений SQL (API 1.x) - Основной учебник в 1.x style
Операторы сравнения¶
Базовые сравнения, которые применяются ко многим типам данных, включая числа, строки, даты и многие другие:
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
проще всего получить с помощью обычного PythonNone
:>>> 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