# sql/operators.py # Copyright (C) 2005-2023 the SQLAlchemy authors and contributors # # # This module is part of SQLAlchemy and is released under # the MIT License: https://www.opensource.org/licenses/mit-license.php # This module is part of SQLAlchemy and is released under # the MIT License: https://www.opensource.org/licenses/mit-license.php """Defines operators used in SQL expressions.""" from operator import add from operator import and_ from operator import contains from operator import eq from operator import ge from operator import getitem from operator import gt from operator import inv from operator import le from operator import lshift from operator import lt from operator import mod from operator import mul from operator import ne from operator import neg from operator import or_ from operator import rshift from operator import sub from operator import truediv from .. import util if util.py2k: from operator import div else: div = truediv class Operators(object): """Base of comparison and logical operators. Implements base methods :meth:`~sqlalchemy.sql.operators.Operators.operate` and :meth:`~sqlalchemy.sql.operators.Operators.reverse_operate`, as well as :meth:`~sqlalchemy.sql.operators.Operators.__and__`, :meth:`~sqlalchemy.sql.operators.Operators.__or__`, :meth:`~sqlalchemy.sql.operators.Operators.__invert__`. Usually is used via its most common subclass :class:`.ColumnOperators`. """ __slots__ = () def __and__(self, other): """Implement the ``&`` operator. When used with SQL expressions, results in an AND operation, equivalent to :func:`_expression.and_`, that is:: a & b is equivalent to:: from sqlalchemy import and_ and_(a, b) Care should be taken when using ``&`` regarding operator precedence; the ``&`` operator has the highest precedence. The operands should be enclosed in parenthesis if they contain further sub expressions:: (a == 2) & (b == 4) """ return self.operate(and_, other) def __or__(self, other): """Implement the ``|`` operator. When used with SQL expressions, results in an OR operation, equivalent to :func:`_expression.or_`, that is:: a | b is equivalent to:: from sqlalchemy import or_ or_(a, b) Care should be taken when using ``|`` regarding operator precedence; the ``|`` operator has the highest precedence. The operands should be enclosed in parenthesis if they contain further sub expressions:: (a == 2) | (b == 4) """ return self.operate(or_, other) def __invert__(self): """Implement the ``~`` operator. When used with SQL expressions, results in a NOT operation, equivalent to :func:`_expression.not_`, that is:: ~a is equivalent to:: from sqlalchemy import not_ not_(a) """ return self.operate(inv) def op( self, opstring, precedence=0, is_comparison=False, return_type=None ): """Produce a generic operator function. e.g.:: somecolumn.op("*")(5) produces:: somecolumn * 5 This function can also be used to make bitwise operators explicit. For example:: somecolumn.op('&')(0xff) is a bitwise AND of the value in ``somecolumn``. :param operator: a string which will be output as the infix operator between this element and the expression passed to the generated function. :param precedence: precedence which the database is expected to apply to the operator in SQL expressions. This integer value acts as a hint for the SQL compiler to know when explicit parenthesis should be rendered around a particular operation. A lower number will cause the expression to be parenthesized when applied against another operator with higher precedence. The default value of ``0`` is lower than all operators except for the comma (``,``) and ``AS`` operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators. .. seealso:: :ref:`faq_sql_expression_op_parenthesis` - detailed description of how the SQLAlchemy SQL compiler renders parenthesis :param is_comparison: legacy; if True, the operator will be considered as a "comparison" operator, that is which evaluates to a boolean true/false value, like ``==``, ``>``, etc. This flag is provided so that ORM relationships can establish that the operator is a comparison operator when used in a custom join condition. Using the ``is_comparison`` parameter is superseded by using the :meth:`.Operators.bool_op` method instead; this more succinct operator sets this parameter automatically. In SQLAlchemy 2.0 it will also provide for improved typing support. :param return_type: a :class:`.TypeEngine` class or object that will force the return type of an expression produced by this operator to be of that type. By default, operators that specify :paramref:`.Operators.op.is_comparison` will resolve to :class:`.Boolean`, and those that do not will be of the same type as the left-hand operand. .. seealso:: :meth:`.Operators.bool_op` :ref:`types_operators` :ref:`relationship_custom_operator` """ operator = custom_op(opstring, precedence, is_comparison, return_type) def against(other): return operator(self, other) return against def bool_op(self, opstring, precedence=0): """Return a custom boolean operator. This method is shorthand for calling :meth:`.Operators.op` and passing the :paramref:`.Operators.op.is_comparison` flag with True. A key advantage to using :meth:`.Operators.bool_op` is that when using column constructs, the "boolean" nature of the returned expression will be present for :pep:`484` purposes. .. seealso:: :meth:`.Operators.op` """ return self.op(opstring, precedence=precedence, is_comparison=True) def operate(self, op, *other, **kwargs): r"""Operate on an argument. This is the lowest level of operation, raises :class:`NotImplementedError` by default. Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding :class:`.ColumnOperators` to apply ``func.lower()`` to the left and right side:: class MyComparator(ColumnOperators): def operate(self, op, other, **kwargs): return op(func.lower(self), func.lower(other), **kwargs) :param op: Operator callable. :param \*other: the 'other' side of the operation. Will be a single scalar for most operations. :param \**kwargs: modifiers. These may be passed by special operators such as :meth:`ColumnOperators.contains`. """ raise NotImplementedError(str(op)) def reverse_operate(self, op, other, **kwargs): """Reverse operate on an argument. Usage is the same as :meth:`operate`. """ raise NotImplementedError(str(op)) class custom_op(object): """Represent a 'custom' operator. :class:`.custom_op` is normally instantiated when the :meth:`.Operators.op` or :meth:`.Operators.bool_op` methods are used to create a custom operator callable. The class can also be used directly when programmatically constructing expressions. E.g. to represent the "factorial" operation:: from sqlalchemy.sql import UnaryExpression from sqlalchemy.sql import operators from sqlalchemy import Numeric unary = UnaryExpression(table.c.somecolumn, modifier=operators.custom_op("!"), type_=Numeric) .. seealso:: :meth:`.Operators.op` :meth:`.Operators.bool_op` """ __name__ = "custom_op" def __init__( self, opstring, precedence=0, is_comparison=False, return_type=None, natural_self_precedent=False, eager_grouping=False, ): self.opstring = opstring self.precedence = precedence self.is_comparison = is_comparison self.natural_self_precedent = natural_self_precedent self.eager_grouping = eager_grouping self.return_type = ( return_type._to_instance(return_type) if return_type else None ) def __eq__(self, other): return ( isinstance(other, custom_op) and other._hash_key() == self._hash_key() ) def __hash__(self): return hash(self._hash_key()) def _hash_key(self): return ( self.__class__, self.opstring, self.precedence, self.is_comparison, self.natural_self_precedent, self.eager_grouping, self.return_type._static_cache_key if self.return_type else None, ) def __call__(self, left, right, **kw): return left.operate(self, right, **kw) class ColumnOperators(Operators): """Defines boolean, comparison, and other operators for :class:`_expression.ColumnElement` expressions. By default, all methods call down to :meth:`.operate` or :meth:`.reverse_operate`, passing in the appropriate operator function from the Python builtin ``operator`` module or a SQLAlchemy-specific operator function from :mod:`sqlalchemy.expression.operators`. For example the ``__eq__`` function:: def __eq__(self, other): return self.operate(operators.eq, other) Where ``operators.eq`` is essentially:: def eq(a, b): return a == b The core column expression unit :class:`_expression.ColumnElement` overrides :meth:`.Operators.operate` and others to return further :class:`_expression.ColumnElement` constructs, so that the ``==`` operation above is replaced by a clause construct. .. seealso:: :ref:`types_operators` :attr:`.TypeEngine.comparator_factory` :class:`.ColumnOperators` :class:`.PropComparator` """ __slots__ = () timetuple = None """Hack, allows datetime objects to be compared on the LHS.""" def __lt__(self, other): """Implement the ``<`` operator. In a column context, produces the clause ``a < b``. """ return self.operate(lt, other) def __le__(self, other): """Implement the ``<=`` operator. In a column context, produces the clause ``a <= b``. """ return self.operate(le, other) __hash__ = Operators.__hash__ def __eq__(self, other): """Implement the ``==`` operator. In a column context, produces the clause ``a = b``. If the target is ``None``, produces ``a IS NULL``. """ return self.operate(eq, other) def __ne__(self, other): """Implement the ``!=`` operator. In a column context, produces the clause ``a != b``. If the target is ``None``, produces ``a IS NOT NULL``. """ return self.operate(ne, other) def is_distinct_from(self, other): """Implement the ``IS DISTINCT FROM`` operator. Renders "a IS DISTINCT FROM b" on most platforms; on some such as SQLite may render "a IS NOT b". .. versionadded:: 1.1 """ return self.operate(is_distinct_from, other) def is_not_distinct_from(self, other): """Implement the ``IS NOT DISTINCT FROM`` operator. Renders "a IS NOT DISTINCT FROM b" on most platforms; on some such as SQLite may render "a IS b". .. versionchanged:: 1.4 The ``is_not_distinct_from()`` operator is renamed from ``isnot_distinct_from()`` in previous releases. The previous name remains available for backwards compatibility. .. versionadded:: 1.1 """ return self.operate(is_not_distinct_from, other) # deprecated 1.4; see #5435 isnot_distinct_from = is_not_distinct_from def __gt__(self, other): """Implement the ``>`` operator. In a column context, produces the clause ``a > b``. """ return self.operate(gt, other) def __ge__(self, other): """Implement the ``>=`` operator. In a column context, produces the clause ``a >= b``. """ return self.operate(ge, other) def __neg__(self): """Implement the ``-`` operator. In a column context, produces the clause ``-a``. """ return self.operate(neg) def __contains__(self, other): return self.operate(contains, other) def __getitem__(self, index): """Implement the [] operator. This can be used by some database-specific types such as PostgreSQL ARRAY and HSTORE. """ return self.operate(getitem, index) def __lshift__(self, other): """implement the << operator. Not used by SQLAlchemy core, this is provided for custom operator systems which want to use << as an extension point. """ return self.operate(lshift, other) def __rshift__(self, other): """implement the >> operator. Not used by SQLAlchemy core, this is provided for custom operator systems which want to use >> as an extension point. """ return self.operate(rshift, other) def concat(self, other): """Implement the 'concat' operator. In a column context, produces the clause ``a || b``, or uses the ``concat()`` operator on MySQL. """ return self.operate(concat_op, other) def _rconcat(self, other): """Implement an 'rconcat' operator. this is for internal use at the moment .. versionadded:: 1.4.40 """ return self.reverse_operate(concat_op, other) def like(self, other, escape=None): r"""Implement the ``like`` operator. In a column context, produces the expression:: a LIKE other E.g.:: stmt = select(sometable).\ where(sometable.c.column.like("%foobar%")) :param other: expression to be compared :param escape: optional escape character, renders the ``ESCAPE`` keyword, e.g.:: somecolumn.like("foo/%bar", escape="/") .. seealso:: :meth:`.ColumnOperators.ilike` """ return self.operate(like_op, other, escape=escape) def ilike(self, other, escape=None): r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE. In a column context, produces an expression either of the form:: lower(a) LIKE lower(other) Or on backends that support the ILIKE operator:: a ILIKE other E.g.:: stmt = select(sometable).\ where(sometable.c.column.ilike("%foobar%")) :param other: expression to be compared :param escape: optional escape character, renders the ``ESCAPE`` keyword, e.g.:: somecolumn.ilike("foo/%bar", escape="/") .. seealso:: :meth:`.ColumnOperators.like` """ return self.operate(ilike_op, other, escape=escape) def in_(self, other): """Implement the ``in`` operator. In a column context, produces the clause ``column IN ``. The given parameter ``other`` may be: * A list of literal values, e.g.:: stmt.where(column.in_([1, 2, 3])) In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:: WHERE COL IN (?, ?, ?) * A list of tuples may be provided if the comparison is against a :func:`.tuple_` containing multiple expressions:: from sqlalchemy import tuple_ stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)])) * An empty list, e.g.:: stmt.where(column.in_([])) In this calling form, the expression renders an "empty set" expression. These expressions are tailored to individual backends and are generally trying to get an empty SELECT statement as a subquery. Such as on SQLite, the expression is:: WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1) .. versionchanged:: 1.4 empty IN expressions now use an execution-time generated SELECT subquery in all cases. * A bound parameter, e.g. :func:`.bindparam`, may be used if it includes the :paramref:`.bindparam.expanding` flag:: stmt.where(column.in_(bindparam('value', expanding=True))) In this calling form, the expression renders a special non-SQL placeholder expression that looks like:: WHERE COL IN ([EXPANDING_value]) This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form illustrated earlier. If the statement were executed as:: connection.execute(stmt, {"value": [1, 2, 3]}) The database would be passed a bound parameter for each value:: WHERE COL IN (?, ?, ?) .. versionadded:: 1.2 added "expanding" bound parameters If an empty list is passed, a special "empty list" expression, which is specific to the database in use, is rendered. On SQLite this would be:: WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1) .. versionadded:: 1.3 "expanding" bound parameters now support empty lists * a :func:`_expression.select` construct, which is usually a correlated scalar select:: stmt.where( column.in_( select(othertable.c.y). where(table.c.x == othertable.c.x) ) ) In this calling form, :meth:`.ColumnOperators.in_` renders as given:: WHERE COL IN (SELECT othertable.y FROM othertable WHERE othertable.x = table.x) :param other: a list of literals, a :func:`_expression.select` construct, or a :func:`.bindparam` construct that includes the :paramref:`.bindparam.expanding` flag set to True. """ return self.operate(in_op, other) def not_in(self, other): """implement the ``NOT IN`` operator. This is equivalent to using negation with :meth:`.ColumnOperators.in_`, i.e. ``~x.in_(y)``. In the case that ``other`` is an empty sequence, the compiler produces an "empty not in" expression. This defaults to the expression "1 = 1" to produce true in all cases. The :paramref:`_sa.create_engine.empty_in_strategy` may be used to alter this behavior. .. versionchanged:: 1.4 The ``not_in()`` operator is renamed from ``notin_()`` in previous releases. The previous name remains available for backwards compatibility. .. versionchanged:: 1.2 The :meth:`.ColumnOperators.in_` and :meth:`.ColumnOperators.not_in` operators now produce a "static" expression for an empty IN sequence by default. .. seealso:: :meth:`.ColumnOperators.in_` """ return self.operate(not_in_op, other) # deprecated 1.4; see #5429 notin_ = not_in def not_like(self, other, escape=None): """implement the ``NOT LIKE`` operator. This is equivalent to using negation with :meth:`.ColumnOperators.like`, i.e. ``~x.like(y)``. .. versionchanged:: 1.4 The ``not_like()`` operator is renamed from ``notlike()`` in previous releases. The previous name remains available for backwards compatibility. .. seealso:: :meth:`.ColumnOperators.like` """ return self.operate(notlike_op, other, escape=escape) # deprecated 1.4; see #5435 notlike = not_like def not_ilike(self, other, escape=None): """implement the ``NOT ILIKE`` operator. This is equivalent to using negation with :meth:`.ColumnOperators.ilike`, i.e. ``~x.ilike(y)``. .. versionchanged:: 1.4 The ``not_ilike()`` operator is renamed from ``notilike()`` in previous releases. The previous name remains available for backwards compatibility. .. seealso:: :meth:`.ColumnOperators.ilike` """ return self.operate(notilike_op, other, escape=escape) # deprecated 1.4; see #5435 notilike = not_ilike def is_(self, other): """Implement the ``IS`` operator. Normally, ``IS`` is generated automatically when comparing to a value of ``None``, which resolves to ``NULL``. However, explicit usage of ``IS`` may be desirable if comparing to boolean values on certain platforms. .. seealso:: :meth:`.ColumnOperators.is_not` """ return self.operate(is_, other) def is_not(self, other): """Implement the ``IS NOT`` operator. Normally, ``IS NOT`` is generated automatically when comparing to a value of ``None``, which resolves to ``NULL``. However, explicit usage of ``IS NOT`` may be desirable if comparing to boolean values on certain platforms. .. versionchanged:: 1.4 The ``is_not()`` operator is renamed from ``isnot()`` in previous releases. The previous name remains available for backwards compatibility. .. seealso:: :meth:`.ColumnOperators.is_` """ return self.operate(is_not, other) # deprecated 1.4; see #5429 isnot = is_not def startswith(self, other, **kwargs): r"""Implement the ``startswith`` operator. Produces a LIKE expression that tests against a match for the start of a string value:: column LIKE || '%' E.g.:: stmt = select(sometable).\ where(sometable.c.column.startswith("foobar")) Since the operator uses ``LIKE``, wildcard characters ``"%"`` and ``"_"`` that are present inside the expression will behave like wildcards as well. For literal string values, the :paramref:`.ColumnOperators.startswith.autoescape` flag may be set to ``True`` to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the :paramref:`.ColumnOperators.startswith.escape` parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string. :param other: expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters ``%`` and ``_`` are not escaped by default unless the :paramref:`.ColumnOperators.startswith.autoescape` flag is set to True. :param autoescape: boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of ``"%"``, ``"_"`` and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression. An expression such as:: somecolumn.startswith("foo%bar", autoescape=True) Will render as:: somecolumn LIKE :param || '%' ESCAPE '/' With the value of ``:param`` as ``"foo/%bar"``. :param escape: a character which when given will render with the ``ESCAPE`` keyword to establish that character as the escape character. This character can then be placed preceding occurrences of ``%`` and ``_`` to allow them to act as themselves and not wildcard characters. An expression such as:: somecolumn.startswith("foo/%bar", escape="^") Will render as:: somecolumn LIKE :param || '%' ESCAPE '^' The parameter may also be combined with :paramref:`.ColumnOperators.startswith.autoescape`:: somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True) Where above, the given literal parameter will be converted to ``"foo^%bar^^bat"`` before being passed to the database. .. seealso:: :meth:`.ColumnOperators.endswith` :meth:`.ColumnOperators.contains` :meth:`.ColumnOperators.like` """ return self.operate(startswith_op, other, **kwargs) def endswith(self, other, **kwargs): r"""Implement the 'endswith' operator. Produces a LIKE expression that tests against a match for the end of a string value:: column LIKE '%' || E.g.:: stmt = select(sometable).\ where(sometable.c.column.endswith("foobar")) Since the operator uses ``LIKE``, wildcard characters ``"%"`` and ``"_"`` that are present inside the expression will behave like wildcards as well. For literal string values, the :paramref:`.ColumnOperators.endswith.autoescape` flag may be set to ``True`` to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the :paramref:`.ColumnOperators.endswith.escape` parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string. :param other: expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters ``%`` and ``_`` are not escaped by default unless the :paramref:`.ColumnOperators.endswith.autoescape` flag is set to True. :param autoescape: boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of ``"%"``, ``"_"`` and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression. An expression such as:: somecolumn.endswith("foo%bar", autoescape=True) Will render as:: somecolumn LIKE '%' || :param ESCAPE '/' With the value of ``:param`` as ``"foo/%bar"``. :param escape: a character which when given will render with the ``ESCAPE`` keyword to establish that character as the escape character. This character can then be placed preceding occurrences of ``%`` and ``_`` to allow them to act as themselves and not wildcard characters. An expression such as:: somecolumn.endswith("foo/%bar", escape="^") Will render as:: somecolumn LIKE '%' || :param ESCAPE '^' The parameter may also be combined with :paramref:`.ColumnOperators.endswith.autoescape`:: somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True) Where above, the given literal parameter will be converted to ``"foo^%bar^^bat"`` before being passed to the database. .. seealso:: :meth:`.ColumnOperators.startswith` :meth:`.ColumnOperators.contains` :meth:`.ColumnOperators.like` """ return self.operate(endswith_op, other, **kwargs) def contains(self, other, **kwargs): r"""Implement the 'contains' operator. Produces a LIKE expression that tests against a match for the middle of a string value:: column LIKE '%' || || '%' E.g.:: stmt = select(sometable).\ where(sometable.c.column.contains("foobar")) Since the operator uses ``LIKE``, wildcard characters ``"%"`` and ``"_"`` that are present inside the expression will behave like wildcards as well. For literal string values, the :paramref:`.ColumnOperators.contains.autoescape` flag may be set to ``True`` to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the :paramref:`.ColumnOperators.contains.escape` parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string. :param other: expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters ``%`` and ``_`` are not escaped by default unless the :paramref:`.ColumnOperators.contains.autoescape` flag is set to True. :param autoescape: boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of ``"%"``, ``"_"`` and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression. An expression such as:: somecolumn.contains("foo%bar", autoescape=True) Will render as:: somecolumn LIKE '%' || :param || '%' ESCAPE '/' With the value of ``:param`` as ``"foo/%bar"``. :param escape: a character which when given will render with the ``ESCAPE`` keyword to establish that character as the escape character. This character can then be placed preceding occurrences of ``%`` and ``_`` to allow them to act as themselves and not wildcard characters. An expression such as:: somecolumn.contains("foo/%bar", escape="^") Will render as:: somecolumn LIKE '%' || :param || '%' ESCAPE '^' The parameter may also be combined with :paramref:`.ColumnOperators.contains.autoescape`:: somecolumn.contains("foo%bar^bat", escape="^", autoescape=True) Where above, the given literal parameter will be converted to ``"foo^%bar^^bat"`` before being passed to the database. .. seealso:: :meth:`.ColumnOperators.startswith` :meth:`.ColumnOperators.endswith` :meth:`.ColumnOperators.like` """ return self.operate(contains_op, other, **kwargs) def match(self, other, **kwargs): """Implements a database-specific 'match' operator. :meth:`_sql.ColumnOperators.match` attempts to resolve to a MATCH-like function or operator provided by the backend. Examples include: * PostgreSQL - renders ``x @@ to_tsquery(y)`` * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)`` .. seealso:: :class:`_mysql.match` - MySQL specific construct with additional features. * Oracle - renders ``CONTAINS(x, y)`` * other backends may provide special implementations. * Backends without any special implementation will emit the operator as "MATCH". This is compatible with SQLite, for example. """ return self.operate(match_op, other, **kwargs) def regexp_match(self, pattern, flags=None): """Implements a database-specific 'regexp match' operator. E.g.:: stmt = select(table.c.some_column).where( table.c.some_column.regexp_match('^(b|c)') ) :meth:`_sql.ColumnOperators.regexp_match` attempts to resolve to a REGEXP-like function or operator provided by the backend, however the specific regular expression syntax and flags available are **not backend agnostic**. Examples include: * PostgreSQL - renders ``x ~ y`` or ``x !~ y`` when negated. * Oracle - renders ``REGEXP_LIKE(x, y)`` * SQLite - uses SQLite's ``REGEXP`` placeholder operator and calls into the Python ``re.match()`` builtin. * other backends may provide special implementations. * Backends without any special implementation will emit the operator as "REGEXP" or "NOT REGEXP". This is compatible with SQLite and MySQL, for example. Regular expression support is currently implemented for Oracle, PostgreSQL, MySQL and MariaDB. Partial support is available for SQLite. Support among third-party dialects may vary. :param pattern: The regular expression pattern string or column clause. :param flags: Any regular expression string flags to apply, passed as plain Python string only. These flags are backend specific. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern. When using the ignore case flag 'i' in PostgreSQL, the ignore case regexp match operator ``~*`` or ``!~*`` will be used. .. versionadded:: 1.4 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation error, the "flags" parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the "flags" parameter, as these flags are rendered as literal inline values within SQL expressions. .. seealso:: :meth:`_sql.ColumnOperators.regexp_replace` """ return self.operate(regexp_match_op, pattern, flags=flags) def regexp_replace(self, pattern, replacement, flags=None): """Implements a database-specific 'regexp replace' operator. E.g.:: stmt = select( table.c.some_column.regexp_replace( 'b(..)', 'X\1Y', flags='g' ) ) :meth:`_sql.ColumnOperators.regexp_replace` attempts to resolve to a REGEXP_REPLACE-like function provided by the backend, that usually emit the function ``REGEXP_REPLACE()``. However, the specific regular expression syntax and flags available are **not backend agnostic**. Regular expression replacement support is currently implemented for Oracle, PostgreSQL, MySQL 8 or greater and MariaDB. Support among third-party dialects may vary. :param pattern: The regular expression pattern string or column clause. :param pattern: The replacement string or column clause. :param flags: Any regular expression string flags to apply, passed as plain Python string only. These flags are backend specific. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern. .. versionadded:: 1.4 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation error, the "flags" parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the "flags" parameter, as these flags are rendered as literal inline values within SQL expressions. .. seealso:: :meth:`_sql.ColumnOperators.regexp_match` """ return self.operate( regexp_replace_op, pattern, replacement=replacement, flags=flags ) def desc(self): """Produce a :func:`_expression.desc` clause against the parent object.""" return self.operate(desc_op) def asc(self): """Produce a :func:`_expression.asc` clause against the parent object.""" return self.operate(asc_op) def nulls_first(self): """Produce a :func:`_expression.nulls_first` clause against the parent object. .. versionchanged:: 1.4 The ``nulls_first()`` operator is renamed from ``nullsfirst()`` in previous releases. The previous name remains available for backwards compatibility. """ return self.operate(nulls_first_op) # deprecated 1.4; see #5435 nullsfirst = nulls_first def nulls_last(self): """Produce a :func:`_expression.nulls_last` clause against the parent object. .. versionchanged:: 1.4 The ``nulls_last()`` operator is renamed from ``nullslast()`` in previous releases. The previous name remains available for backwards compatibility. """ return self.operate(nulls_last_op) # deprecated 1.4; see #5429 nullslast = nulls_last def collate(self, collation): """Produce a :func:`_expression.collate` clause against the parent object, given the collation string. .. seealso:: :func:`_expression.collate` """ return self.operate(collate, collation) def __radd__(self, other): """Implement the ``+`` operator in reverse. See :meth:`.ColumnOperators.__add__`. """ return self.reverse_operate(add, other) def __rsub__(self, other): """Implement the ``-`` operator in reverse. See :meth:`.ColumnOperators.__sub__`. """ return self.reverse_operate(sub, other) def __rmul__(self, other): """Implement the ``*`` operator in reverse. See :meth:`.ColumnOperators.__mul__`. """ return self.reverse_operate(mul, other) def __rdiv__(self, other): """Implement the ``/`` operator in reverse. See :meth:`.ColumnOperators.__div__`. """ return self.reverse_operate(div, other) def __rmod__(self, other): """Implement the ``%`` operator in reverse. See :meth:`.ColumnOperators.__mod__`. """ return self.reverse_operate(mod, other) def between(self, cleft, cright, symmetric=False): """Produce a :func:`_expression.between` clause against the parent object, given the lower and upper range. """ return self.operate(between_op, cleft, cright, symmetric=symmetric) def distinct(self): """Produce a :func:`_expression.distinct` clause against the parent object. """ return self.operate(distinct_op) def any_(self): """Produce an :func:`_expression.any_` clause against the parent object. See the documentation for :func:`_sql.any_` for examples. .. note:: be sure to not confuse the newer :meth:`_sql.ColumnOperators.any_` method with its older :class:`_types.ARRAY`-specific counterpart, the :meth:`_types.ARRAY.Comparator.any` method, which a different calling syntax and usage pattern. .. versionadded:: 1.1 """ return self.operate(any_op) def all_(self): """Produce an :func:`_expression.all_` clause against the parent object. See the documentation for :func:`_sql.all_` for examples. .. note:: be sure to not confuse the newer :meth:`_sql.ColumnOperators.all_` method with its older :class:`_types.ARRAY`-specific counterpart, the :meth:`_types.ARRAY.Comparator.all` method, which a different calling syntax and usage pattern. .. versionadded:: 1.1 """ return self.operate(all_op) def __add__(self, other): """Implement the ``+`` operator. In a column context, produces the clause ``a + b`` if the parent object has non-string affinity. If the parent object has a string affinity, produces the concatenation operator, ``a || b`` - see :meth:`.ColumnOperators.concat`. """ return self.operate(add, other) def __sub__(self, other): """Implement the ``-`` operator. In a column context, produces the clause ``a - b``. """ return self.operate(sub, other) def __mul__(self, other): """Implement the ``*`` operator. In a column context, produces the clause ``a * b``. """ return self.operate(mul, other) def __div__(self, other): """Implement the ``/`` operator. In a column context, produces the clause ``a / b``. """ return self.operate(div, other) def __mod__(self, other): """Implement the ``%`` operator. In a column context, produces the clause ``a % b``. """ return self.operate(mod, other) def __truediv__(self, other): """Implement the ``//`` operator. In a column context, produces the clause ``a / b``. """ return self.operate(truediv, other) def __rtruediv__(self, other): """Implement the ``//`` operator in reverse. See :meth:`.ColumnOperators.__truediv__`. """ return self.reverse_operate(truediv, other) _commutative = {eq, ne, add, mul} _comparison = {eq, ne, lt, gt, ge, le} def commutative_op(fn): _commutative.add(fn) return fn def comparison_op(fn): _comparison.add(fn) return fn def from_(): raise NotImplementedError() @comparison_op def function_as_comparison_op(): raise NotImplementedError() def as_(): raise NotImplementedError() def exists(): raise NotImplementedError() def is_true(a): raise NotImplementedError() # 1.4 deprecated; see #5435 istrue = is_true def is_false(a): raise NotImplementedError() # 1.4 deprecated; see #5435 isfalse = is_false @comparison_op def is_distinct_from(a, b): return a.is_distinct_from(b) @comparison_op def is_not_distinct_from(a, b): return a.is_not_distinct_from(b) # deprecated 1.4; see #5435 isnot_distinct_from = is_not_distinct_from @comparison_op def is_(a, b): return a.is_(b) @comparison_op def is_not(a, b): return a.is_not(b) # 1.4 deprecated; see #5429 isnot = is_not def collate(a, b): return a.collate(b) def op(a, opstring, b): return a.op(opstring)(b) @comparison_op def like_op(a, b, escape=None): return a.like(b, escape=escape) @comparison_op def not_like_op(a, b, escape=None): return a.notlike(b, escape=escape) # 1.4 deprecated; see #5435 notlike_op = not_like_op @comparison_op def ilike_op(a, b, escape=None): return a.ilike(b, escape=escape) @comparison_op def not_ilike_op(a, b, escape=None): return a.not_ilike(b, escape=escape) # 1.4 deprecated; see #5435 notilike_op = not_ilike_op @comparison_op def between_op(a, b, c, symmetric=False): return a.between(b, c, symmetric=symmetric) @comparison_op def not_between_op(a, b, c, symmetric=False): return ~a.between(b, c, symmetric=symmetric) # 1.4 deprecated; see #5435 notbetween_op = not_between_op @comparison_op def in_op(a, b): return a.in_(b) @comparison_op def not_in_op(a, b): return a.not_in(b) # 1.4 deprecated; see #5429 notin_op = not_in_op def distinct_op(a): return a.distinct() def any_op(a): return a.any_() def all_op(a): return a.all_() def _escaped_like_impl(fn, other, escape, autoescape): if autoescape: if autoescape is not True: util.warn( "The autoescape parameter is now a simple boolean True/False" ) if escape is None: escape = "/" if not isinstance(other, util.compat.string_types): raise TypeError("String value expected when autoescape=True") if escape not in ("%", "_"): other = other.replace(escape, escape + escape) other = other.replace("%", escape + "%").replace("_", escape + "_") return fn(other, escape=escape) @comparison_op def startswith_op(a, b, escape=None, autoescape=False): return _escaped_like_impl(a.startswith, b, escape, autoescape) @comparison_op def not_startswith_op(a, b, escape=None, autoescape=False): return ~_escaped_like_impl(a.startswith, b, escape, autoescape) # 1.4 deprecated; see #5435 notstartswith_op = not_startswith_op @comparison_op def endswith_op(a, b, escape=None, autoescape=False): return _escaped_like_impl(a.endswith, b, escape, autoescape) @comparison_op def not_endswith_op(a, b, escape=None, autoescape=False): return ~_escaped_like_impl(a.endswith, b, escape, autoescape) # 1.4 deprecated; see #5435 notendswith_op = not_endswith_op @comparison_op def contains_op(a, b, escape=None, autoescape=False): return _escaped_like_impl(a.contains, b, escape, autoescape) @comparison_op def not_contains_op(a, b, escape=None, autoescape=False): return ~_escaped_like_impl(a.contains, b, escape, autoescape) # 1.4 deprecated; see #5435 notcontains_op = not_contains_op @comparison_op def match_op(a, b, **kw): return a.match(b, **kw) @comparison_op def regexp_match_op(a, b, flags=None): return a.regexp_match(b, flags=flags) @comparison_op def not_regexp_match_op(a, b, flags=None): return ~a.regexp_match(b, flags=flags) def regexp_replace_op(a, b, replacement, flags=None): return a.regexp_replace(b, replacement=replacement, flags=flags) @comparison_op def not_match_op(a, b, **kw): return ~a.match(b, **kw) # 1.4 deprecated; see #5429 notmatch_op = not_match_op def comma_op(a, b): raise NotImplementedError() def filter_op(a, b): raise NotImplementedError() def concat_op(a, b): try: concat = a.concat except AttributeError: return b._rconcat(a) else: return concat(b) def desc_op(a): return a.desc() def asc_op(a): return a.asc() def nulls_first_op(a): return a.nulls_first() # 1.4 deprecated; see #5435 nullsfirst_op = nulls_first_op def nulls_last_op(a): return a.nulls_last() # 1.4 deprecated; see #5435 nullslast_op = nulls_last_op def json_getitem_op(a, b): raise NotImplementedError() def json_path_getitem_op(a, b): raise NotImplementedError() def is_comparison(op): return op in _comparison or isinstance(op, custom_op) and op.is_comparison def is_commutative(op): return op in _commutative def is_ordering_modifier(op): return op in (asc_op, desc_op, nulls_first_op, nulls_last_op) def is_natural_self_precedent(op): return ( op in _natural_self_precedent or isinstance(op, custom_op) and op.natural_self_precedent ) _booleans = (inv, is_true, is_false, and_, or_) def is_boolean(op): return is_comparison(op) or op in _booleans _mirror = {gt: lt, ge: le, lt: gt, le: ge} def mirror(op): """rotate a comparison operator 180 degrees. Note this is not the same as negation. """ return _mirror.get(op, op) _associative = _commutative.union([concat_op, and_, or_]).difference([eq, ne]) def is_associative(op): return op in _associative _natural_self_precedent = _associative.union( [getitem, json_getitem_op, json_path_getitem_op] ) """Operators where if we have (a op b) op c, we don't want to parenthesize (a op b). """ _asbool = util.symbol("_asbool", canonical=-10) _smallest = util.symbol("_smallest", canonical=-100) _largest = util.symbol("_largest", canonical=100) _PRECEDENCE = { from_: 15, function_as_comparison_op: 15, any_op: 15, all_op: 15, getitem: 15, json_getitem_op: 15, json_path_getitem_op: 15, mul: 8, truediv: 8, div: 8, mod: 8, neg: 8, add: 7, sub: 7, concat_op: 6, filter_op: 6, match_op: 5, not_match_op: 5, regexp_match_op: 5, not_regexp_match_op: 5, regexp_replace_op: 5, ilike_op: 5, not_ilike_op: 5, like_op: 5, not_like_op: 5, in_op: 5, not_in_op: 5, is_: 5, is_not: 5, eq: 5, ne: 5, is_distinct_from: 5, is_not_distinct_from: 5, gt: 5, lt: 5, ge: 5, le: 5, between_op: 5, not_between_op: 5, distinct_op: 5, inv: 5, is_true: 5, is_false: 5, and_: 3, or_: 2, comma_op: -1, desc_op: 3, asc_op: 3, collate: 4, as_: -1, exists: 0, _asbool: -10, _smallest: _smallest, _largest: _largest, } def is_precedent(operator, against): if operator is against and is_natural_self_precedent(operator): return False else: return _PRECEDENCE.get( operator, getattr(operator, "precedence", _smallest) ) <= _PRECEDENCE.get(against, getattr(against, "precedence", _largest))