Operator Reference

These methods are presented in terms of the and base classes. The methods are then available on descendants of these classes, including:

• Column objects

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

• - unified tutorial in 2.0 style

• - ORM tutorial in 1.x style

• - Core tutorial in 1.x style

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

• (Python “==” operator):

• ColumnOperators.__ne__() (Python “!=” operator):

  >>> print(column('x') != 5)
  x != :x_1

• (Python “>” operator):

  >>> print(column('x') > 5)
  x > :x_1

• ColumnOperators.__lt__() (Python “<” operator):

  >>> print(column('x') < 5)
  x < :x_1

• (Python “>=” operator):

  >>> print(column('x') >= 5)
  x >= :x_1

• ColumnOperators.__le__() (Python “<=” operator):

  >>> print(column('x') <= 5)
  x <= :x_1

• :

  >>> print(column('x').between(5, 10))
  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 is available most typically by passing a list of values to the method:

>>> print(column('x').in_([1, 2, 3]))
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)
SELECT user_account.id
FROM user_account
WHERE user_account.id IN (?, ?, ?)
[...] (1, 2, 3)

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)
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” is available via the ColumnOperators.not_in() operator:

>>> print(column('x').not_in([1, 2, 3]))
x NOT IN ([POSTCOMPILE_x_1])

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

>>> print(~column('x').in_([1, 2, 3]))
x NOT IN ([POSTCOMPILE_x_1])

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

Finally, the ColumnOperators.in_() and operators work with subqueries. The form provides that a Select construct is passed in directly, without any explicit conversion to a named subquery:

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)
...     )
... )
(x, y) IN (SELECT user_account.id, address.id
FROM user_account JOIN address ON user_account.id = address.user_id)

Identity Comparisons

• 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))
  x IS NULL

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

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

  >>> a = None
  >>> print(column('x') == a)
  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 redefine is.

• ColumnOperators.is_not():

  Similar to , produces “IS NOT”:

  >>> print(column('x').is_not(None))
  x IS NOT NULL

  Is similarly equivalent to != None:

  >>> print(column('x') != None)
  x IS NOT NULL

• ColumnOperators.is_distinct_from():

  Produces SQL IS DISTINCT FROM:

  >>> print(column('x').is_distinct_from('some value'))
  x IS DISTINCT FROM :x_1

• :

  Produces 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

• :

  Case insensitive LIKE makes use of the SQL lower() function on a generic backend. On the PostgreSQL backend it will use ILIKE:

  >>> print(column('x').ilike('word'))
  lower(x) LIKE lower(:x_1)

• ColumnOperators.notlike():

  >>> print(column('x').notlike('word'))
  x NOT LIKE :x_1

• :

  >>> print(column('x').notilike('word'))
  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():

• :

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

• ColumnOperators.endswith():

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

• :

  >>> print(column('x').contains('word'))
  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'))
  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()))
  x ~ %(x_1)s

  Or MySQL:

  >>> from sqlalchemy.dialects import mysql
  >>> print(column('x').regexp_match('word').compile(dialect=mysql.dialect()))
  x REGEXP %s

• String concatenation:

  >>> print(column('x').concat("some string"))
  x || :x_1

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

  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)

• :

  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()))
  (x COLLATE latin1_german2_ci) = %s

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

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

Arithmetic Operators

• , ColumnOperators.__rsub__() (Python “-” operator):

  >>> print(column('x') - 5)
  x - :x_1
  >>> print(5 - column('x'))
  

• , ColumnOperators.__rmul__() (Python “*” operator):

  >>> print(column('x') * 5)
  x * :x_1
  >>> print(5 * column('x'))
  :x_1 * x

• , ColumnOperators.__rdiv__() (Python “/” operator):

  >>> print(column('x') / 5)
  x / :x_1
  >>> print(5 / column('x'))
  :x_1 / x

• , ColumnOperators.__rmod__() (Python “%” operator):

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

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 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)
...    )
SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id

, Update.where() and 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
...        )
...    )
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 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
...         )
...     )
... )
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))
x != :x_1

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

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

The above conjunction functions , or_(), 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 (note parenthesis around the two operands):

  >>> print((column('x') == 5) & (column('y') == 10))
  x = :x_1 AND y = :y_1

• Operators.__or__() (Python “|” operator):

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

  >>> print((column('x') == 5) | (column('y') == 10))
  x = :x_1 OR y = :y_1

• Operators.__invert__() (Python “~” operator):

Operator Customization

TODO