diff options
Diffstat (limited to '.venv/lib/python3.12/site-packages/sqlalchemy/sql/operators.py')
| -rw-r--r-- | .venv/lib/python3.12/site-packages/sqlalchemy/sql/operators.py | 2623 |
1 files changed, 2623 insertions, 0 deletions
diff --git a/.venv/lib/python3.12/site-packages/sqlalchemy/sql/operators.py b/.venv/lib/python3.12/site-packages/sqlalchemy/sql/operators.py new file mode 100644 index 00000000..d5f876cb --- /dev/null +++ b/.venv/lib/python3.12/site-packages/sqlalchemy/sql/operators.py @@ -0,0 +1,2623 @@ +# sql/operators.py +# Copyright (C) 2005-2025 the SQLAlchemy authors and contributors +# <see AUTHORS file> +# +# 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 __future__ import annotations + +from enum import IntEnum +from operator import add as _uncast_add +from operator import and_ as _uncast_and_ +from operator import contains as _uncast_contains +from operator import eq as _uncast_eq +from operator import floordiv as _uncast_floordiv +from operator import ge as _uncast_ge +from operator import getitem as _uncast_getitem +from operator import gt as _uncast_gt +from operator import inv as _uncast_inv +from operator import le as _uncast_le +from operator import lshift as _uncast_lshift +from operator import lt as _uncast_lt +from operator import mod as _uncast_mod +from operator import mul as _uncast_mul +from operator import ne as _uncast_ne +from operator import neg as _uncast_neg +from operator import or_ as _uncast_or_ +from operator import rshift as _uncast_rshift +from operator import sub as _uncast_sub +from operator import truediv as _uncast_truediv +import typing +from typing import Any +from typing import Callable +from typing import cast +from typing import Dict +from typing import Generic +from typing import Optional +from typing import overload +from typing import Set +from typing import Tuple +from typing import Type +from typing import TYPE_CHECKING +from typing import TypeVar +from typing import Union + +from .. import exc +from .. import util +from ..util.typing import Literal +from ..util.typing import Protocol + +if typing.TYPE_CHECKING: + from ._typing import ColumnExpressionArgument + from .cache_key import CacheConst + from .elements import ColumnElement + from .type_api import TypeEngine + +_T = TypeVar("_T", bound=Any) +_FN = TypeVar("_FN", bound=Callable[..., Any]) + + +class OperatorType(Protocol): + """describe an op() function.""" + + __slots__ = () + + __name__: str + + @overload + def __call__( + self, + left: ColumnExpressionArgument[Any], + right: Optional[Any] = None, + *other: Any, + **kwargs: Any, + ) -> ColumnElement[Any]: ... + + @overload + def __call__( + self, + left: Operators, + right: Optional[Any] = None, + *other: Any, + **kwargs: Any, + ) -> Operators: ... + + def __call__( + self, + left: Any, + right: Optional[Any] = None, + *other: Any, + **kwargs: Any, + ) -> Operators: ... + + +add = cast(OperatorType, _uncast_add) +and_ = cast(OperatorType, _uncast_and_) +contains = cast(OperatorType, _uncast_contains) +eq = cast(OperatorType, _uncast_eq) +floordiv = cast(OperatorType, _uncast_floordiv) +ge = cast(OperatorType, _uncast_ge) +getitem = cast(OperatorType, _uncast_getitem) +gt = cast(OperatorType, _uncast_gt) +inv = cast(OperatorType, _uncast_inv) +le = cast(OperatorType, _uncast_le) +lshift = cast(OperatorType, _uncast_lshift) +lt = cast(OperatorType, _uncast_lt) +mod = cast(OperatorType, _uncast_mod) +mul = cast(OperatorType, _uncast_mul) +ne = cast(OperatorType, _uncast_ne) +neg = cast(OperatorType, _uncast_neg) +or_ = cast(OperatorType, _uncast_or_) +rshift = cast(OperatorType, _uncast_rshift) +sub = cast(OperatorType, _uncast_sub) +truediv = cast(OperatorType, _uncast_truediv) + + +class Operators: + """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: Any) -> Operators: + """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: Any) -> Operators: + """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) -> Operators: + """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: str, + precedence: int = 0, + is_comparison: bool = False, + return_type: Optional[ + Union[Type[TypeEngine[Any]], TypeEngine[Any]] + ] = None, + python_impl: Optional[Callable[..., Any]] = None, + ) -> Callable[[Any], Operators]: + """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 opstring: 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, but also provides + correct :pep:`484` typing support as the returned object will + express a "boolean" datatype, i.e. ``BinaryExpression[bool]``. + + :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. + + :param python_impl: an optional Python function that can evaluate + two Python values in the same way as this operator works when + run on the database server. Useful for in-Python SQL expression + evaluation functions, such as for ORM hybrid attributes, and the + ORM "evaluator" used to match objects in a session after a multi-row + update or delete. + + e.g.:: + + >>> expr = column("x").op("+", python_impl=lambda a, b: a + b)("y") + + The operator for the above expression will also work for non-SQL + left and right objects:: + + >>> expr.operator(5, 10) + 15 + + .. versionadded:: 2.0 + + + .. seealso:: + + :meth:`.Operators.bool_op` + + :ref:`types_operators` + + :ref:`relationship_custom_operator` + + """ + operator = custom_op( + opstring, + precedence, + is_comparison, + return_type, + python_impl=python_impl, + ) + + def against(other: Any) -> Operators: + return operator(self, other) + + return against + + def bool_op( + self, + opstring: str, + precedence: int = 0, + python_impl: Optional[Callable[..., Any]] = None, + ) -> Callable[[Any], Operators]: + """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, + python_impl=python_impl, + ) + + def operate( + self, op: OperatorType, *other: Any, **kwargs: Any + ) -> Operators: + 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)) + + __sa_operate__ = operate + + def reverse_operate( + self, op: OperatorType, other: Any, **kwargs: Any + ) -> Operators: + """Reverse operate on an argument. + + Usage is the same as :meth:`operate`. + + """ + raise NotImplementedError(str(op)) + + +class custom_op(OperatorType, Generic[_T]): + """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` + + """ # noqa: E501 + + __name__ = "custom_op" + + __slots__ = ( + "opstring", + "precedence", + "is_comparison", + "natural_self_precedent", + "eager_grouping", + "return_type", + "python_impl", + ) + + def __init__( + self, + opstring: str, + precedence: int = 0, + is_comparison: bool = False, + return_type: Optional[ + Union[Type[TypeEngine[_T]], TypeEngine[_T]] + ] = None, + natural_self_precedent: bool = False, + eager_grouping: bool = False, + python_impl: Optional[Callable[..., Any]] = None, + ): + 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 + ) + self.python_impl = python_impl + + def __eq__(self, other: Any) -> bool: + return ( + isinstance(other, custom_op) + and other._hash_key() == self._hash_key() + ) + + def __hash__(self) -> int: + return hash(self._hash_key()) + + def _hash_key(self) -> Union[CacheConst, Tuple[Any, ...]]: + 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, + ) + + @overload + def __call__( + self, + left: ColumnExpressionArgument[Any], + right: Optional[Any] = None, + *other: Any, + **kwargs: Any, + ) -> ColumnElement[Any]: ... + + @overload + def __call__( + self, + left: Operators, + right: Optional[Any] = None, + *other: Any, + **kwargs: Any, + ) -> Operators: ... + + def __call__( + self, + left: Any, + right: Optional[Any] = None, + *other: Any, + **kwargs: Any, + ) -> Operators: + if hasattr(left, "__sa_operate__"): + return left.operate(self, right, *other, **kwargs) # type: ignore + elif self.python_impl: + return self.python_impl(left, right, *other, **kwargs) # type: ignore # noqa: E501 + else: + raise exc.InvalidRequestError( + f"Custom operator {self.opstring!r} can't be used with " + "plain Python objects unless it includes the " + "'python_impl' parameter." + ) + + +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: Literal[None] = None + """Hack, allows datetime objects to be compared on the LHS.""" + + if typing.TYPE_CHECKING: + + def operate( + self, op: OperatorType, *other: Any, **kwargs: Any + ) -> ColumnOperators: ... + + def reverse_operate( + self, op: OperatorType, other: Any, **kwargs: Any + ) -> ColumnOperators: ... + + def __lt__(self, other: Any) -> ColumnOperators: + """Implement the ``<`` operator. + + In a column context, produces the clause ``a < b``. + + """ + return self.operate(lt, other) + + def __le__(self, other: Any) -> ColumnOperators: + """Implement the ``<=`` operator. + + In a column context, produces the clause ``a <= b``. + + """ + return self.operate(le, other) + + # ColumnOperators defines an __eq__ so it must explicitly declare also + # an hash or it's set to None by python: + # https://docs.python.org/3/reference/datamodel.html#object.__hash__ + if TYPE_CHECKING: + + def __hash__(self) -> int: ... + + else: + __hash__ = Operators.__hash__ + + def __eq__(self, other: Any) -> ColumnOperators: # type: ignore[override] + """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: Any) -> ColumnOperators: # type: ignore[override] + """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: Any) -> ColumnOperators: + """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". + + """ + return self.operate(is_distinct_from, other) + + def is_not_distinct_from(self, other: Any) -> ColumnOperators: + """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. + + """ + return self.operate(is_not_distinct_from, other) + + # deprecated 1.4; see #5435 + if TYPE_CHECKING: + + def isnot_distinct_from(self, other: Any) -> ColumnOperators: ... + + else: + isnot_distinct_from = is_not_distinct_from + + def __gt__(self, other: Any) -> ColumnOperators: + """Implement the ``>`` operator. + + In a column context, produces the clause ``a > b``. + + """ + return self.operate(gt, other) + + def __ge__(self, other: Any) -> ColumnOperators: + """Implement the ``>=`` operator. + + In a column context, produces the clause ``a >= b``. + + """ + return self.operate(ge, other) + + def __neg__(self) -> ColumnOperators: + """Implement the ``-`` operator. + + In a column context, produces the clause ``-a``. + + """ + return self.operate(neg) + + def __contains__(self, other: Any) -> ColumnOperators: + return self.operate(contains, other) + + def __getitem__(self, index: Any) -> ColumnOperators: + """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: Any) -> ColumnOperators: + """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: Any) -> ColumnOperators: + """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: Any) -> ColumnOperators: + """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: Any) -> ColumnOperators: + """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: Any, escape: Optional[str] = None + ) -> ColumnOperators: + r"""Implement the ``like`` operator. + + In a column context, produces the expression: + + .. sourcecode:: sql + + 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: Any, escape: Optional[str] = None + ) -> ColumnOperators: + r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE. + + In a column context, produces an expression either of the form: + + .. sourcecode:: sql + + lower(a) LIKE lower(other) + + Or on backends that support the ILIKE operator: + + .. sourcecode:: sql + + 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` + + """ # noqa: E501 + return self.operate(ilike_op, other, escape=escape) + + def bitwise_xor(self, other: Any) -> ColumnOperators: + """Produce a bitwise XOR operation, typically via the ``^`` + operator, or ``#`` for PostgreSQL. + + .. versionadded:: 2.0.2 + + .. seealso:: + + :ref:`operators_bitwise` + + """ + + return self.operate(bitwise_xor_op, other) + + def bitwise_or(self, other: Any) -> ColumnOperators: + """Produce a bitwise OR operation, typically via the ``|`` + operator. + + .. versionadded:: 2.0.2 + + .. seealso:: + + :ref:`operators_bitwise` + + """ + + return self.operate(bitwise_or_op, other) + + def bitwise_and(self, other: Any) -> ColumnOperators: + """Produce a bitwise AND operation, typically via the ``&`` + operator. + + .. versionadded:: 2.0.2 + + .. seealso:: + + :ref:`operators_bitwise` + + """ + + return self.operate(bitwise_and_op, other) + + def bitwise_not(self) -> ColumnOperators: + """Produce a bitwise NOT operation, typically via the ``~`` + operator. + + .. versionadded:: 2.0.2 + + .. seealso:: + + :ref:`operators_bitwise` + + """ + + return self.operate(bitwise_not_op) + + def bitwise_lshift(self, other: Any) -> ColumnOperators: + """Produce a bitwise LSHIFT operation, typically via the ``<<`` + operator. + + .. versionadded:: 2.0.2 + + .. seealso:: + + :ref:`operators_bitwise` + + """ + + return self.operate(bitwise_lshift_op, other) + + def bitwise_rshift(self, other: Any) -> ColumnOperators: + """Produce a bitwise RSHIFT operation, typically via the ``>>`` + operator. + + .. versionadded:: 2.0.2 + + .. seealso:: + + :ref:`operators_bitwise` + + """ + + return self.operate(bitwise_rshift_op, other) + + def in_(self, other: Any) -> ColumnOperators: + """Implement the ``in`` operator. + + In a column context, produces the clause ``column IN <other>``. + + 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: + + .. sourcecode:: sql + + 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: + + .. sourcecode:: sql + + 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: + + .. sourcecode:: sql + + 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: + + .. sourcecode:: sql + + 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: + + .. sourcecode:: sql + + 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: + + .. sourcecode:: sql + + 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. + + """ # noqa: E501 + return self.operate(in_op, other) + + def not_in(self, other: Any) -> ColumnOperators: + """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 + if TYPE_CHECKING: + + def notin_(self, other: Any) -> ColumnOperators: ... + + else: + notin_ = not_in + + def not_like( + self, other: Any, escape: Optional[str] = None + ) -> ColumnOperators: + """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(not_like_op, other, escape=escape) + + # deprecated 1.4; see #5435 + if TYPE_CHECKING: + + def notlike( + self, other: Any, escape: Optional[str] = None + ) -> ColumnOperators: ... + + else: + notlike = not_like + + def not_ilike( + self, other: Any, escape: Optional[str] = None + ) -> ColumnOperators: + """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(not_ilike_op, other, escape=escape) + + # deprecated 1.4; see #5435 + if TYPE_CHECKING: + + def notilike( + self, other: Any, escape: Optional[str] = None + ) -> ColumnOperators: ... + + else: + notilike = not_ilike + + def is_(self, other: Any) -> ColumnOperators: + """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: Any) -> ColumnOperators: + """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 + if TYPE_CHECKING: + + def isnot(self, other: Any) -> ColumnOperators: ... + + else: + isnot = is_not + + def startswith( + self, + other: Any, + escape: Optional[str] = None, + autoescape: bool = False, + ) -> ColumnOperators: + r"""Implement the ``startswith`` operator. + + Produces a LIKE expression that tests against a match for the start + of a string value: + + .. sourcecode:: sql + + column LIKE <other> || '%' + + E.g.:: + + stmt = select(sometable).where(sometable.c.column.startswith("foobar")) + + Since the operator uses ``LIKE``, wildcard characters + ``"%"`` and ``"_"`` that are present inside the <other> 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: + + .. sourcecode:: sql + + 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: + + .. sourcecode:: sql + + 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` + + """ # noqa: E501 + return self.operate( + startswith_op, other, escape=escape, autoescape=autoescape + ) + + def istartswith( + self, + other: Any, + escape: Optional[str] = None, + autoescape: bool = False, + ) -> ColumnOperators: + r"""Implement the ``istartswith`` operator, e.g. case insensitive + version of :meth:`.ColumnOperators.startswith`. + + Produces a LIKE expression that tests against an insensitive + match for the start of a string value: + + .. sourcecode:: sql + + lower(column) LIKE lower(<other>) || '%' + + E.g.:: + + stmt = select(sometable).where(sometable.c.column.istartswith("foobar")) + + Since the operator uses ``LIKE``, wildcard characters + ``"%"`` and ``"_"`` that are present inside the <other> expression + will behave like wildcards as well. For literal string + values, the :paramref:`.ColumnOperators.istartswith.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.istartswith.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.istartswith.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.istartswith("foo%bar", autoescape=True) + + Will render as: + + .. sourcecode:: sql + + lower(somecolumn) LIKE lower(: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.istartswith("foo/%bar", escape="^") + + Will render as: + + .. sourcecode:: sql + + lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^' + + The parameter may also be combined with + :paramref:`.ColumnOperators.istartswith.autoescape`:: + + somecolumn.istartswith("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` + """ # noqa: E501 + return self.operate( + istartswith_op, other, escape=escape, autoescape=autoescape + ) + + def endswith( + self, + other: Any, + escape: Optional[str] = None, + autoescape: bool = False, + ) -> ColumnOperators: + r"""Implement the 'endswith' operator. + + Produces a LIKE expression that tests against a match for the end + of a string value: + + .. sourcecode:: sql + + column LIKE '%' || <other> + + E.g.:: + + stmt = select(sometable).where(sometable.c.column.endswith("foobar")) + + Since the operator uses ``LIKE``, wildcard characters + ``"%"`` and ``"_"`` that are present inside the <other> 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: + + .. sourcecode:: sql + + 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: + + .. sourcecode:: sql + + 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` + + """ # noqa: E501 + return self.operate( + endswith_op, other, escape=escape, autoescape=autoescape + ) + + def iendswith( + self, + other: Any, + escape: Optional[str] = None, + autoescape: bool = False, + ) -> ColumnOperators: + r"""Implement the ``iendswith`` operator, e.g. case insensitive + version of :meth:`.ColumnOperators.endswith`. + + Produces a LIKE expression that tests against an insensitive match + for the end of a string value: + + .. sourcecode:: sql + + lower(column) LIKE '%' || lower(<other>) + + E.g.:: + + stmt = select(sometable).where(sometable.c.column.iendswith("foobar")) + + Since the operator uses ``LIKE``, wildcard characters + ``"%"`` and ``"_"`` that are present inside the <other> expression + will behave like wildcards as well. For literal string + values, the :paramref:`.ColumnOperators.iendswith.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.iendswith.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.iendswith.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.iendswith("foo%bar", autoescape=True) + + Will render as: + + .. sourcecode:: sql + + lower(somecolumn) LIKE '%' || lower(: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.iendswith("foo/%bar", escape="^") + + Will render as: + + .. sourcecode:: sql + + lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^' + + The parameter may also be combined with + :paramref:`.ColumnOperators.iendswith.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.endswith` + """ # noqa: E501 + return self.operate( + iendswith_op, other, escape=escape, autoescape=autoescape + ) + + def contains(self, other: Any, **kw: Any) -> ColumnOperators: + r"""Implement the 'contains' operator. + + Produces a LIKE expression that tests against a match for the middle + of a string value: + + .. sourcecode:: sql + + column LIKE '%' || <other> || '%' + + E.g.:: + + stmt = select(sometable).where(sometable.c.column.contains("foobar")) + + Since the operator uses ``LIKE``, wildcard characters + ``"%"`` and ``"_"`` that are present inside the <other> 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: + + .. sourcecode:: sql + + 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: + + .. sourcecode:: sql + + 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` + + + """ # noqa: E501 + return self.operate(contains_op, other, **kw) + + def icontains(self, other: Any, **kw: Any) -> ColumnOperators: + r"""Implement the ``icontains`` operator, e.g. case insensitive + version of :meth:`.ColumnOperators.contains`. + + Produces a LIKE expression that tests against an insensitive match + for the middle of a string value: + + .. sourcecode:: sql + + lower(column) LIKE '%' || lower(<other>) || '%' + + E.g.:: + + stmt = select(sometable).where(sometable.c.column.icontains("foobar")) + + Since the operator uses ``LIKE``, wildcard characters + ``"%"`` and ``"_"`` that are present inside the <other> expression + will behave like wildcards as well. For literal string + values, the :paramref:`.ColumnOperators.icontains.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.icontains.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.icontains.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.icontains("foo%bar", autoescape=True) + + Will render as: + + .. sourcecode:: sql + + lower(somecolumn) LIKE '%' || lower(: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.icontains("foo/%bar", escape="^") + + Will render as: + + .. sourcecode:: sql + + lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^' + + The parameter may also be combined with + :paramref:`.ColumnOperators.contains.autoescape`:: + + somecolumn.icontains("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.contains` + + """ # noqa: E501 + return self.operate(icontains_op, other, **kw) + + def match(self, other: Any, **kwargs: Any) -> ColumnOperators: + """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 @@ plainto_tsquery(y)`` + + .. versionchanged:: 2.0 ``plainto_tsquery()`` is used instead + of ``to_tsquery()`` for PostgreSQL now; for compatibility with + other forms, see :ref:`postgresql_match`. + + + * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)`` + + .. seealso:: + + :class:`_mysql.match` - MySQL specific construct with + additional features. + + * Oracle Database - 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: Any, flags: Optional[str] = None + ) -> ColumnOperators: + """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 Database - 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 + Database, 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: Any, replacement: Any, flags: Optional[str] = None + ) -> ColumnOperators: + """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 Database, 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) -> ColumnOperators: + """Produce a :func:`_expression.desc` clause against the + parent object.""" + return self.operate(desc_op) + + def asc(self) -> ColumnOperators: + """Produce a :func:`_expression.asc` clause against the + parent object.""" + return self.operate(asc_op) + + def nulls_first(self) -> ColumnOperators: + """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 + if TYPE_CHECKING: + + def nullsfirst(self) -> ColumnOperators: ... + + else: + nullsfirst = nulls_first + + def nulls_last(self) -> ColumnOperators: + """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 + if TYPE_CHECKING: + + def nullslast(self) -> ColumnOperators: ... + + else: + nullslast = nulls_last + + def collate(self, collation: str) -> ColumnOperators: + """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: Any) -> ColumnOperators: + """Implement the ``+`` operator in reverse. + + See :meth:`.ColumnOperators.__add__`. + + """ + return self.reverse_operate(add, other) + + def __rsub__(self, other: Any) -> ColumnOperators: + """Implement the ``-`` operator in reverse. + + See :meth:`.ColumnOperators.__sub__`. + + """ + return self.reverse_operate(sub, other) + + def __rmul__(self, other: Any) -> ColumnOperators: + """Implement the ``*`` operator in reverse. + + See :meth:`.ColumnOperators.__mul__`. + + """ + return self.reverse_operate(mul, other) + + def __rmod__(self, other: Any) -> ColumnOperators: + """Implement the ``%`` operator in reverse. + + See :meth:`.ColumnOperators.__mod__`. + + """ + return self.reverse_operate(mod, other) + + def between( + self, cleft: Any, cright: Any, symmetric: bool = False + ) -> ColumnOperators: + """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) -> ColumnOperators: + """Produce a :func:`_expression.distinct` clause against the + parent object. + + """ + return self.operate(distinct_op) + + def any_(self) -> ColumnOperators: + """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 the **legacy** + version of this method, the :meth:`_types.ARRAY.Comparator.any` + method that's specific to :class:`_types.ARRAY`, which uses a + different calling style. + + """ + return self.operate(any_op) + + def all_(self) -> ColumnOperators: + """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 the **legacy** + version of this method, the :meth:`_types.ARRAY.Comparator.all` + method that's specific to :class:`_types.ARRAY`, which uses a + different calling style. + + """ + return self.operate(all_op) + + def __add__(self, other: Any) -> ColumnOperators: + """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: Any) -> ColumnOperators: + """Implement the ``-`` operator. + + In a column context, produces the clause ``a - b``. + + """ + return self.operate(sub, other) + + def __mul__(self, other: Any) -> ColumnOperators: + """Implement the ``*`` operator. + + In a column context, produces the clause ``a * b``. + + """ + return self.operate(mul, other) + + def __mod__(self, other: Any) -> ColumnOperators: + """Implement the ``%`` operator. + + In a column context, produces the clause ``a % b``. + + """ + return self.operate(mod, other) + + def __truediv__(self, other: Any) -> ColumnOperators: + """Implement the ``/`` operator. + + In a column context, produces the clause ``a / b``, and + considers the result type to be numeric. + + .. versionchanged:: 2.0 The truediv operator against two integers + is now considered to return a numeric value. Behavior on specific + backends may vary. + + """ + return self.operate(truediv, other) + + def __rtruediv__(self, other: Any) -> ColumnOperators: + """Implement the ``/`` operator in reverse. + + See :meth:`.ColumnOperators.__truediv__`. + + """ + return self.reverse_operate(truediv, other) + + def __floordiv__(self, other: Any) -> ColumnOperators: + """Implement the ``//`` operator. + + In a column context, produces the clause ``a / b``, + which is the same as "truediv", but considers the result + type to be integer. + + .. versionadded:: 2.0 + + """ + return self.operate(floordiv, other) + + def __rfloordiv__(self, other: Any) -> ColumnOperators: + """Implement the ``//`` operator in reverse. + + See :meth:`.ColumnOperators.__floordiv__`. + + """ + return self.reverse_operate(floordiv, other) + + +_commutative: Set[Any] = {eq, ne, add, mul} +_comparison: Set[Any] = {eq, ne, lt, gt, ge, le} + + +def _operator_fn(fn: Callable[..., Any]) -> OperatorType: + return cast(OperatorType, fn) + + +def commutative_op(fn: _FN) -> _FN: + _commutative.add(fn) + return fn + + +def comparison_op(fn: _FN) -> _FN: + _comparison.add(fn) + return fn + + +@_operator_fn +def from_() -> Any: + raise NotImplementedError() + + +@_operator_fn +@comparison_op +def function_as_comparison_op() -> Any: + raise NotImplementedError() + + +@_operator_fn +def as_() -> Any: + raise NotImplementedError() + + +@_operator_fn +def exists() -> Any: + raise NotImplementedError() + + +@_operator_fn +def is_true(a: Any) -> Any: + raise NotImplementedError() + + +# 1.4 deprecated; see #5435 +if TYPE_CHECKING: + + @_operator_fn + def istrue(a: Any) -> Any: ... + +else: + istrue = is_true + + +@_operator_fn +def is_false(a: Any) -> Any: + raise NotImplementedError() + + +# 1.4 deprecated; see #5435 +if TYPE_CHECKING: + + @_operator_fn + def isfalse(a: Any) -> Any: ... + +else: + isfalse = is_false + + +@comparison_op +@_operator_fn +def is_distinct_from(a: Any, b: Any) -> Any: + return a.is_distinct_from(b) + + +@comparison_op +@_operator_fn +def is_not_distinct_from(a: Any, b: Any) -> Any: + return a.is_not_distinct_from(b) + + +# deprecated 1.4; see #5435 +if TYPE_CHECKING: + + @_operator_fn + def isnot_distinct_from(a: Any, b: Any) -> Any: ... + +else: + isnot_distinct_from = is_not_distinct_from + + +@comparison_op +@_operator_fn +def is_(a: Any, b: Any) -> Any: + return a.is_(b) + + +@comparison_op +@_operator_fn +def is_not(a: Any, b: Any) -> Any: + return a.is_not(b) + + +# 1.4 deprecated; see #5429 +if TYPE_CHECKING: + + @_operator_fn + def isnot(a: Any, b: Any) -> Any: ... + +else: + isnot = is_not + + +@_operator_fn +def collate(a: Any, b: Any) -> Any: + return a.collate(b) + + +@_operator_fn +def op(a: Any, opstring: str, b: Any) -> Any: + return a.op(opstring)(b) + + +@comparison_op +@_operator_fn +def like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: + return a.like(b, escape=escape) + + +@comparison_op +@_operator_fn +def not_like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: + return a.notlike(b, escape=escape) + + +# 1.4 deprecated; see #5435 +if TYPE_CHECKING: + + @_operator_fn + def notlike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ... + +else: + notlike_op = not_like_op + + +@comparison_op +@_operator_fn +def ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: + return a.ilike(b, escape=escape) + + +@comparison_op +@_operator_fn +def not_ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: + return a.not_ilike(b, escape=escape) + + +# 1.4 deprecated; see #5435 +if TYPE_CHECKING: + + @_operator_fn + def notilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ... + +else: + notilike_op = not_ilike_op + + +@comparison_op +@_operator_fn +def between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any: + return a.between(b, c, symmetric=symmetric) + + +@comparison_op +@_operator_fn +def not_between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any: + return ~a.between(b, c, symmetric=symmetric) + + +# 1.4 deprecated; see #5435 +if TYPE_CHECKING: + + @_operator_fn + def notbetween_op( + a: Any, b: Any, c: Any, symmetric: bool = False + ) -> Any: ... + +else: + notbetween_op = not_between_op + + +@comparison_op +@_operator_fn +def in_op(a: Any, b: Any) -> Any: + return a.in_(b) + + +@comparison_op +@_operator_fn +def not_in_op(a: Any, b: Any) -> Any: + return a.not_in(b) + + +# 1.4 deprecated; see #5429 +if TYPE_CHECKING: + + @_operator_fn + def notin_op(a: Any, b: Any) -> Any: ... + +else: + notin_op = not_in_op + + +@_operator_fn +def distinct_op(a: Any) -> Any: + return a.distinct() + + +@_operator_fn +def any_op(a: Any) -> Any: + return a.any_() + + +@_operator_fn +def all_op(a: Any) -> Any: + return a.all_() + + +def _escaped_like_impl( + fn: Callable[..., Any], other: Any, escape: Optional[str], autoescape: bool +) -> Any: + 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, str): + 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 +@_operator_fn +def startswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return _escaped_like_impl(a.startswith, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def not_startswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return ~_escaped_like_impl(a.startswith, b, escape, autoescape) + + +# 1.4 deprecated; see #5435 +if TYPE_CHECKING: + + @_operator_fn + def notstartswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False + ) -> Any: ... + +else: + notstartswith_op = not_startswith_op + + +@comparison_op +@_operator_fn +def istartswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return _escaped_like_impl(a.istartswith, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def not_istartswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return ~_escaped_like_impl(a.istartswith, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def endswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return _escaped_like_impl(a.endswith, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def not_endswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return ~_escaped_like_impl(a.endswith, b, escape, autoescape) + + +# 1.4 deprecated; see #5435 +if TYPE_CHECKING: + + @_operator_fn + def notendswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False + ) -> Any: ... + +else: + notendswith_op = not_endswith_op + + +@comparison_op +@_operator_fn +def iendswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return _escaped_like_impl(a.iendswith, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def not_iendswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return ~_escaped_like_impl(a.iendswith, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def contains_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return _escaped_like_impl(a.contains, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def not_contains_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return ~_escaped_like_impl(a.contains, b, escape, autoescape) + + +# 1.4 deprecated; see #5435 +if TYPE_CHECKING: + + @_operator_fn + def notcontains_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False + ) -> Any: ... + +else: + notcontains_op = not_contains_op + + +@comparison_op +@_operator_fn +def icontains_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return _escaped_like_impl(a.icontains, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def not_icontains_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return ~_escaped_like_impl(a.icontains, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def match_op(a: Any, b: Any, **kw: Any) -> Any: + return a.match(b, **kw) + + +@comparison_op +@_operator_fn +def regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any: + return a.regexp_match(b, flags=flags) + + +@comparison_op +@_operator_fn +def not_regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any: + return ~a.regexp_match(b, flags=flags) + + +@_operator_fn +def regexp_replace_op( + a: Any, b: Any, replacement: Any, flags: Optional[str] = None +) -> Any: + return a.regexp_replace(b, replacement=replacement, flags=flags) + + +@comparison_op +@_operator_fn +def not_match_op(a: Any, b: Any, **kw: Any) -> Any: + return ~a.match(b, **kw) + + +# 1.4 deprecated; see #5429 +if TYPE_CHECKING: + + @_operator_fn + def notmatch_op(a: Any, b: Any, **kw: Any) -> Any: ... + +else: + notmatch_op = not_match_op + + +@_operator_fn +def comma_op(a: Any, b: Any) -> Any: + raise NotImplementedError() + + +@_operator_fn +def filter_op(a: Any, b: Any) -> Any: + raise NotImplementedError() + + +@_operator_fn +def concat_op(a: Any, b: Any) -> Any: + try: + concat = a.concat + except AttributeError: + return b._rconcat(a) + else: + return concat(b) + + +@_operator_fn +def desc_op(a: Any) -> Any: + return a.desc() + + +@_operator_fn +def asc_op(a: Any) -> Any: + return a.asc() + + +@_operator_fn +def nulls_first_op(a: Any) -> Any: + return a.nulls_first() + + +# 1.4 deprecated; see #5435 +if TYPE_CHECKING: + + @_operator_fn + def nullsfirst_op(a: Any) -> Any: ... + +else: + nullsfirst_op = nulls_first_op + + +@_operator_fn +def nulls_last_op(a: Any) -> Any: + return a.nulls_last() + + +# 1.4 deprecated; see #5435 +if TYPE_CHECKING: + + @_operator_fn + def nullslast_op(a: Any) -> Any: ... + +else: + nullslast_op = nulls_last_op + + +@_operator_fn +def json_getitem_op(a: Any, b: Any) -> Any: + raise NotImplementedError() + + +@_operator_fn +def json_path_getitem_op(a: Any, b: Any) -> Any: + raise NotImplementedError() + + +@_operator_fn +def bitwise_xor_op(a: Any, b: Any) -> Any: + return a.bitwise_xor(b) + + +@_operator_fn +def bitwise_or_op(a: Any, b: Any) -> Any: + return a.bitwise_or(b) + + +@_operator_fn +def bitwise_and_op(a: Any, b: Any) -> Any: + return a.bitwise_and(b) + + +@_operator_fn +def bitwise_not_op(a: Any) -> Any: + return a.bitwise_not() + + +@_operator_fn +def bitwise_lshift_op(a: Any, b: Any) -> Any: + return a.bitwise_lshift(b) + + +@_operator_fn +def bitwise_rshift_op(a: Any, b: Any) -> Any: + return a.bitwise_rshift(b) + + +def is_comparison(op: OperatorType) -> bool: + return op in _comparison or isinstance(op, custom_op) and op.is_comparison + + +def is_commutative(op: OperatorType) -> bool: + return op in _commutative + + +def is_ordering_modifier(op: OperatorType) -> bool: + return op in (asc_op, desc_op, nulls_first_op, nulls_last_op) + + +def is_natural_self_precedent(op: OperatorType) -> bool: + 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: OperatorType) -> bool: + return is_comparison(op) or op in _booleans + + +_mirror = {gt: lt, ge: le, lt: gt, le: ge} + + +def mirror(op: OperatorType) -> OperatorType: + """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: OperatorType) -> bool: + return op in _associative + + +def is_order_by_modifier(op: Optional[OperatorType]) -> bool: + return op in _order_by_modifier + + +_order_by_modifier = {desc_op, asc_op, nulls_first_op, nulls_last_op} + +_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). + +""" + + +@_operator_fn +def _asbool(a: Any) -> Any: + raise NotImplementedError() + + +class _OpLimit(IntEnum): + _smallest = -100 + _largest = 100 + + +_PRECEDENCE: Dict[OperatorType, int] = { + 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, + floordiv: 8, + mod: 8, + neg: 8, + bitwise_not_op: 8, + add: 7, + sub: 7, + bitwise_xor_op: 7, + bitwise_or_op: 7, + bitwise_and_op: 7, + bitwise_lshift_op: 7, + bitwise_rshift_op: 7, + filter_op: 6, + concat_op: 5, + 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, +} + + +def is_precedent( + operator: OperatorType, against: Optional[OperatorType] +) -> bool: + if operator is against and is_natural_self_precedent(operator): + return False + elif against is None: + return True + else: + return bool( + _PRECEDENCE.get( + operator, getattr(operator, "precedence", _OpLimit._smallest) + ) + <= _PRECEDENCE.get( + against, getattr(against, "precedence", _OpLimit._largest) + ) + ) |