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 objects

  • ColumnElement objects more generally, which are the root of all Core SQL Expression language column-level expressions

  • InstrumentedAttribute objects, which are ORM level mapped attributes.

The operators are first introduced in the tutorial sections, including:

Comparison Operators

Basic comparisons which apply to many datatypes, including numerics, strings, dates, and many others:

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

xIN(__[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)

SELECTuser_account.id
FROMuser_account
WHEREuser_account.idIN(?,?,?)
[...](1,2,3)

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)

SELECTuser_account.id
FROMuser_account
WHEREuser_account.idIN(SELECT1FROM(SELECT1)WHERE1!=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]))

(xNOTIN(__[POSTCOMPILE_x_1]))

This is typically more easily available by negating with the ~ operator:

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

(xNOTIN(__[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:

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

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

SELECTuser_account.name
FROMuser_accountJOINaddressONuser_account.id=address.user_id
WHERE(user_account.id,address.id)IN(VALUES(?,?),(?,?))
[...](1,1,2,2)

[('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)))

xIN(SELECTuser_account.id
FROMuser_account)

Tuples work as expected:

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

(x,y)IN(SELECTuser_account.id,address.id
FROMuser_accountJOINaddressONuser_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:

  • ColumnOperators.is_():

    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 Python None:

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

    xISNULL

    SQL NULL is also explicitly available, if needed, using the null() construct:

    >>> fromsqlalchemyimport null
>>> print(column("x").is_(null()))

    xISNULL

    The ColumnOperators.is_() operator is automatically invoked when using the ColumnOperators.__eq__() overloaded operator, i.e. ==, in conjunction with the None or null() value. In this way, there’s typically not a need to use ColumnOperators.is_() explicitly, particularly when used with a dynamic value:

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

    xISNULL

    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 redefine is.

  • ColumnOperators.is_not():

    Similar to ColumnOperators.is_(), produces "IS NOT":

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

    xISNOTNULL

    Is similarly equivalent to != None:

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

    xISNOTNULL

  • ColumnOperators.is_distinct_from():

    Produces SQL IS DISTINCT FROM:

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

    xISDISTINCTFROM:x_1

  • ColumnOperators.isnot_distinct_from():

    Produces SQL IS NOT DISTINCT FROM:

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

    xISNOTDISTINCTFROM:x_1

String Comparisons

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

String matching

Matching operators are always backend-specific and may provide different behaviors and results on different databases:

  • ColumnOperators.match():

    This is a dialect-specific operator that makes use of the MATCH feature of the underlying database, if available:

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

    xMATCH:x_1

  • ColumnOperators.regexp_match():

    This operator is dialect specific. We can illustrate it in terms of for example the PostgreSQL dialect:

    >>> fromsqlalchemy.dialectsimport postgresql
>>> print(column("x").regexp_match("word").compile(dialect=postgresql.dialect()))

    x~%(x_1)s

    Or MySQL:

    >>> fromsqlalchemy.dialectsimport mysql
>>> print(column("x").regexp_match("word").compile(dialect=mysql.dialect()))

    xREGEXP%s

String Alteration

  • ColumnOperators.concat():

    String concatenation:

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

    x||:x_1

    This operator is available via ColumnOperators.__add__(), that is, the Python + operator, when working with a column expression that derives from String:

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

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

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

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

  • ColumnOperators.collate():

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

    (xCOLLATElatin1_german2_ci)=%s

    To use COLLATE against a literal value, use the literal() construct:

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

    (%sCOLLATElatin1_german2_ci)=x

Arithmetic Operators

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.

Added 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:

    >>> fromsqlalchemyimport 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:

    >>> fromsqlalchemy.dialectsimport 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)
... )

SELECTaddress.email_address
FROMaddress,user_account
WHEREuser_account.name=:name_1ANDaddress.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,
... )
... )

SELECTaddress.email_address
FROMaddress,user_account
WHEREuser_account.name=:name_1ANDaddress.user_id=user_account.id

The "AND" conjunction, as well as its partner "OR", are both available directly using the and_() and or_() functions:

>>> fromsqlalchemyimport 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,
... )
... )
... )

SELECTaddress.email_address
FROMaddress,user_account
WHERE(user_account.name=:name_1ORuser_account.name=:name_2)
ANDaddress.user_id=user_account.id

A negation is available using the not_() function. This will typically invert the operator in a boolean expression:

>>> fromsqlalchemyimport not_
>>> print(not_(column("x") == 5))

x!=:x_1

It also may apply a keyword such as NOT when appropriate:

>>> fromsqlalchemyimport Boolean
>>> print(not_(column("x", Boolean)))

NOTx

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 as and_() (note parenthesis around the two operands):

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

    x=:x_1ANDy=:y_1

  • Operators.__or__() (Python "|" operator):

    The Python binary | operator is overloaded to behave the same as or_() (note parenthesis around the two operands):

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

    x=:x_1ORy=:y_1

  • Operators.__invert__() (Python "~" operator):

    The Python binary ~ operator is overloaded to behave the same as not_(), either inverting the existing operator, or applying the NOT keyword to the expression as a whole:

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

    x!=:x_1

    >>> fromsqlalchemyimport Boolean
>>> print(~column("x", Boolean))

    NOTx

Parentheses and Grouping

Parenthesization of expressions is rendered based on operator precedence, not the placement of parentheses in Python code, since there is no means of detecting parentheses from interpreted Python expressions. So an expression like:

>>> expr = or_(
... User.name == "squidward", and_(Address.user_id == User.id, User.name == "sandy")
... )

won’t include parentheses, because the AND operator takes natural precedence over OR:

>>> print(expr)
user_account.name = :name_1 OR address.user_id = user_account.id AND user_account.name = :name_2

Whereas this one, where OR would otherwise not be evaluated before the AND, does:

>>> expr = and_(
... Address.user_id == User.id, or_(User.name == "squidward", User.name == "sandy")
... )
>>> print(expr)
address.user_id = user_account.id AND (user_account.name = :name_1 OR user_account.name = :name_2)

The same behavior takes effect for math operators. In the parenthesized Python expression below, the multiplication operator naturally takes precedence over the addition operator, therefore the SQL will not include parentheses:

>>> print(column("q") + (column("x") * column("y")))

q+x*y

Whereas this one, where the addition operator would not otherwise occur before the multiplication operator, does get parentheses:

>>> print(column("q") * (column("x") + column("y")))

q*(x+y)

More background on this is in the FAQ at Why are the parentheses rules like this?.