Operator Reference¶
This section details usage of the operators that are available to construct SQL expressions.
These methods are presented in terms of the Operators
and ColumnOperators
base classes. The methods are then
available on descendants of these classes, including:
Column
objectsColumnElement
objects more generally, which are the root of all Core SQL Expression language column-level expressionsInstrumentedAttribute
objects, which are ORM level mapped attributes.
The operators are first introduced in the tutorial sections, including:
SQLAlchemy Unified Tutorial - unified tutorial in 2.0 style
Object Relational Tutorial - ORM tutorial in 1.x style
SQL Expression Language Tutorial - Core tutorial in 1.x style
Comparison Operators¶
Basic comparisons which apply to many datatypes, including numerics, strings, dates, and many others:
ColumnOperators.__eq__()
(Python “==
” operator):>>> print(column("x") == 5) {printsql}x = :x_1
ColumnOperators.__ne__()
(Python “!=
” operator):>>> print(column("x") != 5) {printsql}x != :x_1
ColumnOperators.__gt__()
(Python “>
” operator):>>> print(column("x") > 5) {printsql}x > :x_1
ColumnOperators.__lt__()
(Python “<
” operator):>>> print(column("x") < 5) {printsql}x < :x_1
ColumnOperators.__ge__()
(Python “>=
” operator):>>> print(column("x") >= 5) {printsql}x >= :x_1
ColumnOperators.__le__()
(Python “<=
” operator):>>> print(column("x") <= 5) {printsql}x <= :x_1
-
>>> print(column("x").between(5, 10)) {printsql}x BETWEEN :x_1 AND :x_2
IN Comparisons¶
The SQL IN operator is a subject all its own in SQLAlchemy. As the IN operator is usually used against a list of fixed values, SQLAlchemy’s feature of bound parameter coercion makes use of a special form of SQL compilation that renders an interim SQL string for compilation that’s formed into the final list of bound parameters in a second step. In other words, “it just works”.
IN against a list of values¶
IN is available most typically by passing a list of
values to the ColumnOperators.in_()
method:
>>> print(column("x").in_([1, 2, 3]))
{printsql}x IN (__[POSTCOMPILE_x_1])
The special bound form __[POSTCOMPILE
is rendered into individual parameters
at execution time, illustrated below:
>>> stmt = select(User.id).where(User.id.in_([1, 2, 3]))
>>> result = conn.execute(stmt)
{execsql}SELECT user_account.id
FROM user_account
WHERE user_account.id IN (?, ?, ?)
[...] (1, 2, 3){stop}
Empty IN Expressions¶
SQLAlchemy produces a mathematically valid result for an empty IN expression by rendering a backend-specific subquery that returns no rows. Again in other words, “it just works”:
>>> stmt = select(User.id).where(User.id.in_([]))
>>> result = conn.execute(stmt)
{execsql}SELECT user_account.id
FROM user_account
WHERE user_account.id IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
[...] ()
The “empty set” subquery above generalizes correctly and is also rendered in terms of the IN operator which remains in place.
NOT IN¶
“NOT IN” is available via the ColumnOperators.not_in()
operator:
>>> print(column("x").not_in([1, 2, 3]))
{printsql}(x NOT IN (__[POSTCOMPILE_x_1]))
This is typically more easily available by negating with the ~
operator:
>>> print(~column("x").in_([1, 2, 3]))
{printsql}(x NOT IN (__[POSTCOMPILE_x_1]))
Tuple IN Expressions¶
Comparison of tuples to tuples is common with IN, as among other use cases
accommodates for the case when matching rows to a set of potential composite
primary key values. The tuple_()
construct provides the basic
building block for tuple comparisons. The Tuple.in_()
operator
then receives a list of tuples:
>>> from sqlalchemy import tuple_
>>> tup = tuple_(column("x", Integer), column("y", Integer))
>>> expr = tup.in_([(1, 2), (3, 4)])
>>> print(expr)
{printsql}(x, y) IN (__[POSTCOMPILE_param_1])
To illustrate the parameters rendered:
>>> tup = tuple_(User.id, Address.id)
>>> stmt = select(User.name).join(Address).where(tup.in_([(1, 1), (2, 2)]))
>>> conn.execute(stmt).all()
{execsql}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){stop}
[('spongebob',), ('sandy',)]
Subquery IN¶
Finally, the ColumnOperators.in_()
and ColumnOperators.not_in()
operators work with subqueries. The form provides that a Select
construct is passed in directly, without any explicit conversion to a named
subquery:
>>> print(column("x").in_(select(user_table.c.id)))
{printsql}x IN (SELECT user_account.id
FROM user_account)
Tuples work as expected:
>>> print(
... tuple_(column("x"), column("y")).in_(
... select(user_table.c.id, address_table.c.id).join(address_table)
... )
... )
{printsql}(x, y) IN (SELECT user_account.id, address.id
FROM user_account JOIN address ON user_account.id = address.user_id)
Identity Comparisons¶
These operators involve testing for special SQL values such as
NULL
, boolean constants such as true
or false
which some
databases support:
-
This operator will provide exactly the SQL for “x IS y”, most often seen as “<expr> IS NULL”. The
NULL
constant is most easily acquired using regular PythonNone
:>>> print(column("x").is_(None)) {printsql}x IS NULL
SQL NULL is also explicitly available, if needed, using the
null()
construct:>>> from sqlalchemy import null >>> print(column("x").is_(null())) {printsql}x IS NULL
The
ColumnOperators.is_()
operator is automatically invoked when using theColumnOperators.__eq__()
overloaded operator, i.e.==
, in conjunction with theNone
ornull()
value. In this way, there’s typically not a need to useColumnOperators.is_()
explicitly, paricularly when used with a dynamic value:>>> a = None >>> print(column("x") == a) {printsql}x IS NULL
Note that the Python
is
operator is not overloaded. Even though Python provides hooks to overload operators such as==
and!=
, it does not provide any way to redefineis
. -
Similar to
ColumnOperators.is_()
, produces “IS NOT”:>>> print(column("x").is_not(None)) {printsql}x IS NOT NULL
Is similarly equivalent to
!= None
:>>> print(column("x") != None) {printsql}x IS NOT NULL
ColumnOperators.is_distinct_from()
:Produces SQL IS DISTINCT FROM:
>>> print(column("x").is_distinct_from("some value")) {printsql}x IS DISTINCT FROM :x_1
ColumnOperators.isnot_distinct_from()
:Produces SQL IS NOT DISTINCT FROM:
>>> print(column("x").isnot_distinct_from("some value")) {printsql}x IS NOT DISTINCT FROM :x_1
String Comparisons¶
-
>>> print(column("x").like("word")) {printsql}x LIKE :x_1
-
Case insensitive LIKE makes use of the SQL
lower()
function on a generic backend. On the PostgreSQL backend it will useILIKE
:>>> print(column("x").ilike("word")) {printsql}lower(x) LIKE lower(:x_1)
-
>>> print(column("x").notlike("word")) {printsql}x NOT LIKE :x_1
-
>>> print(column("x").notilike("word")) {printsql}lower(x) NOT LIKE lower(:x_1)
String Containment¶
String containment operators are basically built as a combination of
LIKE and the string concatenation operator, which is ||
on most
backends or sometimes a function like concat()
:
-
>>> print(column("x").startswith("word")) {printsql}x LIKE :x_1 || '%'
-
>>> print(column("x").endswith("word")) {printsql}x LIKE '%' || :x_1
-
>>> print(column("x").contains("word")) {printsql}x LIKE '%' || :x_1 || '%'
String matching¶
Matching operators are always backend-specific and may provide different behaviors and results on different databases:
-
This is a dialect-specific operator that makes use of the MATCH feature of the underlying database, if available:
>>> print(column("x").match("word")) {printsql}x MATCH :x_1
ColumnOperators.regexp_match()
:This operator is dialect specific. We can illustrate it in terms of for example the PostgreSQL dialect:
>>> from sqlalchemy.dialects import postgresql >>> print(column("x").regexp_match("word").compile(dialect=postgresql.dialect())) {printsql}x ~ %(x_1)s
Or MySQL:
>>> from sqlalchemy.dialects import mysql >>> print(column("x").regexp_match("word").compile(dialect=mysql.dialect())) {printsql}x REGEXP %s
String Alteration¶
-
String concatenation:
>>> print(column("x").concat("some string")) {printsql}x || :x_1
This operator is available via
ColumnOperators.__add__()
, that is, the Python+
operator, when working with a column expression that derives fromString
:>>> print(column("x", String) + "some string") {printsql}x || :x_1
The operator will produce the appropriate database-specific construct, such as on MySQL it’s historically been the
concat()
SQL function:>>> print((column("x", String) + "some string").compile(dialect=mysql.dialect())) {printsql}concat(x, %s)
ColumnOperators.regexp_replace()
:Complementary to
ColumnOperators.regexp()
this produces REGEXP REPLACE equivalent for the backends which support it:>>> print(column("x").regexp_replace("foo", "bar").compile(dialect=postgresql.dialect())) {printsql}REGEXP_REPLACE(x, %(x_1)s, %(x_2)s)
-
Produces the COLLATE SQL operator which provides for specific collations at expression time:
>>> print( ... (column("x").collate("latin1_german2_ci") == "Müller").compile( ... dialect=mysql.dialect() ... ) ... ) {printsql}(x COLLATE latin1_german2_ci) = %s
To use COLLATE against a literal value, use the
literal()
construct:>>> from sqlalchemy import literal >>> print( ... (literal("Müller").collate("latin1_german2_ci") == column("x")).compile( ... dialect=mysql.dialect() ... ) ... ) {printsql}(%s COLLATE latin1_german2_ci) = x
Arithmetic Operators¶
ColumnOperators.__add__()
,ColumnOperators.__radd__()
(Python “+
” operator):>>> print(column("x") + 5) {printsql}x + :x_1{stop} >>> print(5 + column("x")) {printsql}:x_1 + x{stop}
Note that when the datatype of the expression is
String
or similar, theColumnOperators.__add__()
operator instead produces string concatenation.ColumnOperators.__sub__()
,ColumnOperators.__rsub__()
(Python “-
” operator):>>> print(column("x") - 5) {printsql}x - :x_1{stop} >>> print(5 - column("x")) {printsql}:x_1 - x{stop}
ColumnOperators.__mul__()
,ColumnOperators.__rmul__()
(Python “*
” operator):>>> print(column("x") * 5) {printsql}x * :x_1{stop} >>> print(5 * column("x")) {printsql}:x_1 * x{stop}
ColumnOperators.__truediv__()
,ColumnOperators.__rtruediv__()
(Python “/
” operator). This is the Pythontruediv
operator, which will ensure integer true division occurs:>>> print(column("x") / 5) {printsql}x / CAST(:x_1 AS NUMERIC){stop} >>> print(5 / column("x")) {printsql}:x_1 / CAST(x AS NUMERIC){stop}
Changed in version 2.0: The Python
/
operator now ensures integer true division takes placeColumnOperators.__floordiv__()
,ColumnOperators.__rfloordiv__()
(Python “//
” operator). This is the Pythonfloordiv
operator, which will ensure floor division occurs. For the default backend as well as backends such as PostgreSQL, the SQL/
operator normally behaves this way for integer values:>>> print(column("x") // 5) {printsql}x / :x_1{stop} >>> print(5 // column("x", Integer)) {printsql}:x_1 / x{stop}
For backends that don’t use floor division by default, or when used with numeric values, the FLOOR() function is used to ensure floor division:
>>> print(column("x") // 5.5) {printsql}FLOOR(x / :x_1){stop} >>> print(5 // column("x", Numeric)) {printsql}FLOOR(:x_1 / x){stop}
New in version 2.0: Support for FLOOR division
ColumnOperators.__mod__()
,ColumnOperators.__rmod__()
(Python “%
” operator):>>> print(column("x") % 5) {printsql}x % :x_1{stop} >>> print(5 % column("x")) {printsql}:x_1 % x{stop}
Bitwise Operators¶
Bitwise operator functions provide uniform access to bitwise operators across
different backends, which are expected to operate on compatible
values such as integers and bit-strings (e.g. PostgreSQL
BIT
and similar). Note that these are not general
boolean operators.
New in version 2.0.2: Added dedicated operators for bitwise operations.
ColumnOperators.bitwise_not()
,bitwise_not()
. Available as a column-level method, producing a bitwise NOT clause against a parent object:>>> print(column("x").bitwise_not()) ~x
This operator is also available as a column-expression-level method, applying bitwise NOT to an individual column expression:
>>> from sqlalchemy import bitwise_not >>> print(bitwise_not(column("x"))) ~x
ColumnOperators.bitwise_and()
produces bitwise AND:>>> print(column("x").bitwise_and(5)) x & :x_1
ColumnOperators.bitwise_or()
produces bitwise OR:>>> print(column("x").bitwise_or(5)) x | :x_1
ColumnOperators.bitwise_xor()
produces bitwise XOR:>>> print(column("x").bitwise_xor(5)) x ^ :x_1
For PostgreSQL dialects, “#” is used to represent bitwise XOR; this emits automatically when using one of these backends:
>>> from sqlalchemy.dialects import postgresql >>> print(column("x").bitwise_xor(5).compile(dialect=postgresql.dialect())) x # %(x_1)s
ColumnOperators.bitwise_rshift()
,ColumnOperators.bitwise_lshift()
produce bitwise shift operators:>>> print(column("x").bitwise_rshift(5)) x >> :x_1 >>> print(column("x").bitwise_lshift(5)) x << :x_1
Using Conjunctions and Negations¶
The most common conjunction, “AND”, is automatically applied if we make repeated use of the Select.where()
method, as well as similar methods such as
Update.where()
and 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)
... )
{printsql}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()
and Delete.where()
also accept multiple expressions with the same effect:
>>> print(
... select(address_table.c.email_address).where(
... user_table.c.name == "squidward",
... address_table.c.user_id == user_table.c.id,
... )
... )
{printsql}SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id
The “AND” conjunction, as well as its partner “OR”, are both available directly using the and_()
and or_()
functions:
>>> 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,
... )
... )
... )
{printsql}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
A negation is available using the not_()
function. This will
typically invert the operator in a boolean expression:
>>> from sqlalchemy import not_
>>> print(not_(column("x") == 5))
{printsql}x != :x_1
It also may apply a keyword such as NOT
when appropriate:
>>> from sqlalchemy import Boolean
>>> print(not_(column("x", Boolean)))
{printsql}NOT x
Conjunction Operators¶
The above conjunction functions and_()
, or_()
,
not_()
are also available as overloaded Python operators:
Note
The Python &
, |
and ~
operators take high precedence
in the language; as a result, parenthesis must usually be applied
for operands that themselves contain expressions, as indicated in the
examples below.
Operators.__and__()
(Python “&
” operator):The Python binary
&
operator is overloaded to behave the same asand_()
(note parenthesis around the two operands):>>> print((column("x") == 5) & (column("y") == 10)) {printsql}x = :x_1 AND y = :y_1
Operators.__or__()
(Python “|
” operator):The Python binary
|
operator is overloaded to behave the same asor_()
(note parenthesis around the two operands):>>> print((column("x") == 5) | (column("y") == 10)) {printsql}x = :x_1 OR y = :y_1
Operators.__invert__()
(Python “~
” operator):The Python binary
~
operator is overloaded to behave the same asnot_()
, either inverting the existing operator, or applying theNOT
keyword to the expression as a whole:>>> print(~(column("x") == 5)) {printsql}x != :x_1{stop} >>> from sqlalchemy import Boolean >>> print(~column("x", Boolean)) {printsql}NOT x{stop}