Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/operators.py
2# Copyright (C) 2005-2025 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8# This module is part of SQLAlchemy and is released under
9# the MIT License: https://www.opensource.org/licenses/mit-license.php
11"""Defines operators used in SQL expressions."""
13from __future__ import annotations
15from enum import IntEnum
16from operator import add as _uncast_add
17from operator import and_ as _uncast_and_
18from operator import contains as _uncast_contains
19from operator import eq as _uncast_eq
20from operator import floordiv as _uncast_floordiv
21from operator import ge as _uncast_ge
22from operator import getitem as _uncast_getitem
23from operator import gt as _uncast_gt
24from operator import inv as _uncast_inv
25from operator import le as _uncast_le
26from operator import lshift as _uncast_lshift
27from operator import lt as _uncast_lt
28from operator import mod as _uncast_mod
29from operator import mul as _uncast_mul
30from operator import ne as _uncast_ne
31from operator import neg as _uncast_neg
32from operator import or_ as _uncast_or_
33from operator import rshift as _uncast_rshift
34from operator import sub as _uncast_sub
35from operator import truediv as _uncast_truediv
36import typing
37from typing import Any
38from typing import Callable
39from typing import cast
40from typing import Dict
41from typing import Generic
42from typing import Optional
43from typing import overload
44from typing import Set
45from typing import Tuple
46from typing import Type
47from typing import TYPE_CHECKING
48from typing import TypeVar
49from typing import Union
51from .. import exc
52from .. import util
53from ..util.typing import Literal
54from ..util.typing import Protocol
56if typing.TYPE_CHECKING:
57 from ._typing import ColumnExpressionArgument
58 from .cache_key import CacheConst
59 from .elements import ColumnElement
60 from .type_api import TypeEngine
62_T = TypeVar("_T", bound=Any)
63_FN = TypeVar("_FN", bound=Callable[..., Any])
66class OperatorType(Protocol):
67 """describe an op() function."""
69 __slots__ = ()
71 __name__: str
73 @overload
74 def __call__(
75 self,
76 left: ColumnExpressionArgument[Any],
77 right: Optional[Any] = None,
78 *other: Any,
79 **kwargs: Any,
80 ) -> ColumnElement[Any]: ...
82 @overload
83 def __call__(
84 self,
85 left: Operators,
86 right: Optional[Any] = None,
87 *other: Any,
88 **kwargs: Any,
89 ) -> Operators: ...
91 def __call__(
92 self,
93 left: Any,
94 right: Optional[Any] = None,
95 *other: Any,
96 **kwargs: Any,
97 ) -> Operators: ...
100add = cast(OperatorType, _uncast_add)
101and_ = cast(OperatorType, _uncast_and_)
102contains = cast(OperatorType, _uncast_contains)
103eq = cast(OperatorType, _uncast_eq)
104floordiv = cast(OperatorType, _uncast_floordiv)
105ge = cast(OperatorType, _uncast_ge)
106getitem = cast(OperatorType, _uncast_getitem)
107gt = cast(OperatorType, _uncast_gt)
108inv = cast(OperatorType, _uncast_inv)
109le = cast(OperatorType, _uncast_le)
110lshift = cast(OperatorType, _uncast_lshift)
111lt = cast(OperatorType, _uncast_lt)
112mod = cast(OperatorType, _uncast_mod)
113mul = cast(OperatorType, _uncast_mul)
114ne = cast(OperatorType, _uncast_ne)
115neg = cast(OperatorType, _uncast_neg)
116or_ = cast(OperatorType, _uncast_or_)
117rshift = cast(OperatorType, _uncast_rshift)
118sub = cast(OperatorType, _uncast_sub)
119truediv = cast(OperatorType, _uncast_truediv)
122class Operators:
123 """Base of comparison and logical operators.
125 Implements base methods
126 :meth:`~sqlalchemy.sql.operators.Operators.operate` and
127 :meth:`~sqlalchemy.sql.operators.Operators.reverse_operate`, as well as
128 :meth:`~sqlalchemy.sql.operators.Operators.__and__`,
129 :meth:`~sqlalchemy.sql.operators.Operators.__or__`,
130 :meth:`~sqlalchemy.sql.operators.Operators.__invert__`.
132 Usually is used via its most common subclass
133 :class:`.ColumnOperators`.
135 """
137 __slots__ = ()
139 def __and__(self, other: Any) -> Operators:
140 """Implement the ``&`` operator.
142 When used with SQL expressions, results in an
143 AND operation, equivalent to
144 :func:`_expression.and_`, that is::
146 a & b
148 is equivalent to::
150 from sqlalchemy import and_
152 and_(a, b)
154 Care should be taken when using ``&`` regarding
155 operator precedence; the ``&`` operator has the highest precedence.
156 The operands should be enclosed in parenthesis if they contain
157 further sub expressions::
159 (a == 2) & (b == 4)
161 """
162 return self.operate(and_, other)
164 def __or__(self, other: Any) -> Operators:
165 """Implement the ``|`` operator.
167 When used with SQL expressions, results in an
168 OR operation, equivalent to
169 :func:`_expression.or_`, that is::
171 a | b
173 is equivalent to::
175 from sqlalchemy import or_
177 or_(a, b)
179 Care should be taken when using ``|`` regarding
180 operator precedence; the ``|`` operator has the highest precedence.
181 The operands should be enclosed in parenthesis if they contain
182 further sub expressions::
184 (a == 2) | (b == 4)
186 """
187 return self.operate(or_, other)
189 def __invert__(self) -> Operators:
190 """Implement the ``~`` operator.
192 When used with SQL expressions, results in a
193 NOT operation, equivalent to
194 :func:`_expression.not_`, that is::
196 ~a
198 is equivalent to::
200 from sqlalchemy import not_
202 not_(a)
204 """
205 return self.operate(inv)
207 def op(
208 self,
209 opstring: str,
210 precedence: int = 0,
211 is_comparison: bool = False,
212 return_type: Optional[
213 Union[Type[TypeEngine[Any]], TypeEngine[Any]]
214 ] = None,
215 python_impl: Optional[Callable[..., Any]] = None,
216 ) -> Callable[[Any], Operators]:
217 """Produce a generic operator function.
219 e.g.::
221 somecolumn.op("*")(5)
223 produces::
225 somecolumn * 5
227 This function can also be used to make bitwise operators explicit. For
228 example::
230 somecolumn.op("&")(0xFF)
232 is a bitwise AND of the value in ``somecolumn``.
234 :param opstring: a string which will be output as the infix operator
235 between this element and the expression passed to the
236 generated function.
238 :param precedence: precedence which the database is expected to apply
239 to the operator in SQL expressions. This integer value acts as a hint
240 for the SQL compiler to know when explicit parenthesis should be
241 rendered around a particular operation. A lower number will cause the
242 expression to be parenthesized when applied against another operator
243 with higher precedence. The default value of ``0`` is lower than all
244 operators except for the comma (``,``) and ``AS`` operators. A value
245 of 100 will be higher or equal to all operators, and -100 will be
246 lower than or equal to all operators.
248 .. seealso::
250 :ref:`faq_sql_expression_op_parenthesis` - detailed description
251 of how the SQLAlchemy SQL compiler renders parenthesis
253 :param is_comparison: legacy; if True, the operator will be considered
254 as a "comparison" operator, that is which evaluates to a boolean
255 true/false value, like ``==``, ``>``, etc. This flag is provided
256 so that ORM relationships can establish that the operator is a
257 comparison operator when used in a custom join condition.
259 Using the ``is_comparison`` parameter is superseded by using the
260 :meth:`.Operators.bool_op` method instead; this more succinct
261 operator sets this parameter automatically, but also provides
262 correct :pep:`484` typing support as the returned object will
263 express a "boolean" datatype, i.e. ``BinaryExpression[bool]``.
265 :param return_type: a :class:`.TypeEngine` class or object that will
266 force the return type of an expression produced by this operator
267 to be of that type. By default, operators that specify
268 :paramref:`.Operators.op.is_comparison` will resolve to
269 :class:`.Boolean`, and those that do not will be of the same
270 type as the left-hand operand.
272 :param python_impl: an optional Python function that can evaluate
273 two Python values in the same way as this operator works when
274 run on the database server. Useful for in-Python SQL expression
275 evaluation functions, such as for ORM hybrid attributes, and the
276 ORM "evaluator" used to match objects in a session after a multi-row
277 update or delete.
279 e.g.::
281 >>> expr = column("x").op("+", python_impl=lambda a, b: a + b)("y")
283 The operator for the above expression will also work for non-SQL
284 left and right objects::
286 >>> expr.operator(5, 10)
287 15
289 .. versionadded:: 2.0
292 .. seealso::
294 :meth:`.Operators.bool_op`
296 :ref:`types_operators`
298 :ref:`relationship_custom_operator`
300 """
301 operator = custom_op(
302 opstring,
303 precedence,
304 is_comparison,
305 return_type,
306 python_impl=python_impl,
307 )
309 def against(other: Any) -> Operators:
310 return operator(self, other)
312 return against
314 def bool_op(
315 self,
316 opstring: str,
317 precedence: int = 0,
318 python_impl: Optional[Callable[..., Any]] = None,
319 ) -> Callable[[Any], Operators]:
320 """Return a custom boolean operator.
322 This method is shorthand for calling
323 :meth:`.Operators.op` and passing the
324 :paramref:`.Operators.op.is_comparison`
325 flag with True. A key advantage to using :meth:`.Operators.bool_op`
326 is that when using column constructs, the "boolean" nature of the
327 returned expression will be present for :pep:`484` purposes.
329 .. seealso::
331 :meth:`.Operators.op`
333 """
334 return self.op(
335 opstring,
336 precedence=precedence,
337 is_comparison=True,
338 python_impl=python_impl,
339 )
341 def operate(
342 self, op: OperatorType, *other: Any, **kwargs: Any
343 ) -> Operators:
344 r"""Operate on an argument.
346 This is the lowest level of operation, raises
347 :class:`NotImplementedError` by default.
349 Overriding this on a subclass can allow common
350 behavior to be applied to all operations.
351 For example, overriding :class:`.ColumnOperators`
352 to apply ``func.lower()`` to the left and right
353 side::
355 class MyComparator(ColumnOperators):
356 def operate(self, op, other, **kwargs):
357 return op(func.lower(self), func.lower(other), **kwargs)
359 :param op: Operator callable.
360 :param \*other: the 'other' side of the operation. Will
361 be a single scalar for most operations.
362 :param \**kwargs: modifiers. These may be passed by special
363 operators such as :meth:`ColumnOperators.contains`.
366 """
367 raise NotImplementedError(str(op))
369 __sa_operate__ = operate
371 def reverse_operate(
372 self, op: OperatorType, other: Any, **kwargs: Any
373 ) -> Operators:
374 """Reverse operate on an argument.
376 Usage is the same as :meth:`operate`.
378 """
379 raise NotImplementedError(str(op))
382class custom_op(OperatorType, Generic[_T]):
383 """Represent a 'custom' operator.
385 :class:`.custom_op` is normally instantiated when the
386 :meth:`.Operators.op` or :meth:`.Operators.bool_op` methods
387 are used to create a custom operator callable. The class can also be
388 used directly when programmatically constructing expressions. E.g.
389 to represent the "factorial" operation::
391 from sqlalchemy.sql import UnaryExpression
392 from sqlalchemy.sql import operators
393 from sqlalchemy import Numeric
395 unary = UnaryExpression(
396 table.c.somecolumn, modifier=operators.custom_op("!"), type_=Numeric
397 )
399 .. seealso::
401 :meth:`.Operators.op`
403 :meth:`.Operators.bool_op`
405 """ # noqa: E501
407 __name__ = "custom_op"
409 __slots__ = (
410 "opstring",
411 "precedence",
412 "is_comparison",
413 "natural_self_precedent",
414 "eager_grouping",
415 "return_type",
416 "python_impl",
417 )
419 def __init__(
420 self,
421 opstring: str,
422 precedence: int = 0,
423 is_comparison: bool = False,
424 return_type: Optional[
425 Union[Type[TypeEngine[_T]], TypeEngine[_T]]
426 ] = None,
427 natural_self_precedent: bool = False,
428 eager_grouping: bool = False,
429 python_impl: Optional[Callable[..., Any]] = None,
430 ):
431 self.opstring = opstring
432 self.precedence = precedence
433 self.is_comparison = is_comparison
434 self.natural_self_precedent = natural_self_precedent
435 self.eager_grouping = eager_grouping
436 self.return_type = (
437 return_type._to_instance(return_type) if return_type else None
438 )
439 self.python_impl = python_impl
441 def __eq__(self, other: Any) -> bool:
442 return (
443 isinstance(other, custom_op)
444 and other._hash_key() == self._hash_key()
445 )
447 def __hash__(self) -> int:
448 return hash(self._hash_key())
450 def _hash_key(self) -> Union[CacheConst, Tuple[Any, ...]]:
451 return (
452 self.__class__,
453 self.opstring,
454 self.precedence,
455 self.is_comparison,
456 self.natural_self_precedent,
457 self.eager_grouping,
458 self.return_type._static_cache_key if self.return_type else None,
459 )
461 @overload
462 def __call__(
463 self,
464 left: ColumnExpressionArgument[Any],
465 right: Optional[Any] = None,
466 *other: Any,
467 **kwargs: Any,
468 ) -> ColumnElement[Any]: ...
470 @overload
471 def __call__(
472 self,
473 left: Operators,
474 right: Optional[Any] = None,
475 *other: Any,
476 **kwargs: Any,
477 ) -> Operators: ...
479 def __call__(
480 self,
481 left: Any,
482 right: Optional[Any] = None,
483 *other: Any,
484 **kwargs: Any,
485 ) -> Operators:
486 if hasattr(left, "__sa_operate__"):
487 return left.operate(self, right, *other, **kwargs) # type: ignore
488 elif self.python_impl:
489 return self.python_impl(left, right, *other, **kwargs) # type: ignore # noqa: E501
490 else:
491 raise exc.InvalidRequestError(
492 f"Custom operator {self.opstring!r} can't be used with "
493 "plain Python objects unless it includes the "
494 "'python_impl' parameter."
495 )
498class ColumnOperators(Operators):
499 """Defines boolean, comparison, and other operators for
500 :class:`_expression.ColumnElement` expressions.
502 By default, all methods call down to
503 :meth:`.operate` or :meth:`.reverse_operate`,
504 passing in the appropriate operator function from the
505 Python builtin ``operator`` module or
506 a SQLAlchemy-specific operator function from
507 :mod:`sqlalchemy.expression.operators`. For example
508 the ``__eq__`` function::
510 def __eq__(self, other):
511 return self.operate(operators.eq, other)
513 Where ``operators.eq`` is essentially::
515 def eq(a, b):
516 return a == b
518 The core column expression unit :class:`_expression.ColumnElement`
519 overrides :meth:`.Operators.operate` and others
520 to return further :class:`_expression.ColumnElement` constructs,
521 so that the ``==`` operation above is replaced by a clause
522 construct.
524 .. seealso::
526 :ref:`types_operators`
528 :attr:`.TypeEngine.comparator_factory`
530 :class:`.ColumnOperators`
532 :class:`.PropComparator`
534 """
536 __slots__ = ()
538 timetuple: Literal[None] = None
539 """Hack, allows datetime objects to be compared on the LHS."""
541 if typing.TYPE_CHECKING:
543 def operate(
544 self, op: OperatorType, *other: Any, **kwargs: Any
545 ) -> ColumnOperators: ...
547 def reverse_operate(
548 self, op: OperatorType, other: Any, **kwargs: Any
549 ) -> ColumnOperators: ...
551 def __lt__(self, other: Any) -> ColumnOperators:
552 """Implement the ``<`` operator.
554 In a column context, produces the clause ``a < b``.
556 """
557 return self.operate(lt, other)
559 def __le__(self, other: Any) -> ColumnOperators:
560 """Implement the ``<=`` operator.
562 In a column context, produces the clause ``a <= b``.
564 """
565 return self.operate(le, other)
567 # ColumnOperators defines an __eq__ so it must explicitly declare also
568 # an hash or it's set to None by python:
569 # https://docs.python.org/3/reference/datamodel.html#object.__hash__
570 if TYPE_CHECKING:
572 def __hash__(self) -> int: ...
574 else:
575 __hash__ = Operators.__hash__
577 def __eq__(self, other: Any) -> ColumnOperators: # type: ignore[override]
578 """Implement the ``==`` operator.
580 In a column context, produces the clause ``a = b``.
581 If the target is ``None``, produces ``a IS NULL``.
583 """
584 return self.operate(eq, other)
586 def __ne__(self, other: Any) -> ColumnOperators: # type: ignore[override]
587 """Implement the ``!=`` operator.
589 In a column context, produces the clause ``a != b``.
590 If the target is ``None``, produces ``a IS NOT NULL``.
592 """
593 return self.operate(ne, other)
595 def is_distinct_from(self, other: Any) -> ColumnOperators:
596 """Implement the ``IS DISTINCT FROM`` operator.
598 Renders "a IS DISTINCT FROM b" on most platforms;
599 on some such as SQLite may render "a IS NOT b".
601 """
602 return self.operate(is_distinct_from, other)
604 def is_not_distinct_from(self, other: Any) -> ColumnOperators:
605 """Implement the ``IS NOT DISTINCT FROM`` operator.
607 Renders "a IS NOT DISTINCT FROM b" on most platforms;
608 on some such as SQLite may render "a IS b".
610 .. versionchanged:: 1.4 The ``is_not_distinct_from()`` operator is
611 renamed from ``isnot_distinct_from()`` in previous releases.
612 The previous name remains available for backwards compatibility.
614 """
615 return self.operate(is_not_distinct_from, other)
617 # deprecated 1.4; see #5435
618 if TYPE_CHECKING:
620 def isnot_distinct_from(self, other: Any) -> ColumnOperators: ...
622 else:
623 isnot_distinct_from = is_not_distinct_from
625 def __gt__(self, other: Any) -> ColumnOperators:
626 """Implement the ``>`` operator.
628 In a column context, produces the clause ``a > b``.
630 """
631 return self.operate(gt, other)
633 def __ge__(self, other: Any) -> ColumnOperators:
634 """Implement the ``>=`` operator.
636 In a column context, produces the clause ``a >= b``.
638 """
639 return self.operate(ge, other)
641 def __neg__(self) -> ColumnOperators:
642 """Implement the ``-`` operator.
644 In a column context, produces the clause ``-a``.
646 """
647 return self.operate(neg)
649 def __contains__(self, other: Any) -> ColumnOperators:
650 return self.operate(contains, other)
652 def __getitem__(self, index: Any) -> ColumnOperators:
653 """Implement the [] operator.
655 This can be used by some database-specific types
656 such as PostgreSQL ARRAY and HSTORE.
658 """
659 return self.operate(getitem, index)
661 def __lshift__(self, other: Any) -> ColumnOperators:
662 """implement the << operator.
664 Not used by SQLAlchemy core, this is provided
665 for custom operator systems which want to use
666 << as an extension point.
667 """
668 return self.operate(lshift, other)
670 def __rshift__(self, other: Any) -> ColumnOperators:
671 """implement the >> operator.
673 Not used by SQLAlchemy core, this is provided
674 for custom operator systems which want to use
675 >> as an extension point.
676 """
677 return self.operate(rshift, other)
679 def concat(self, other: Any) -> ColumnOperators:
680 """Implement the 'concat' operator.
682 In a column context, produces the clause ``a || b``,
683 or uses the ``concat()`` operator on MySQL.
685 """
686 return self.operate(concat_op, other)
688 def _rconcat(self, other: Any) -> ColumnOperators:
689 """Implement an 'rconcat' operator.
691 this is for internal use at the moment
693 .. versionadded:: 1.4.40
695 """
696 return self.reverse_operate(concat_op, other)
698 def like(
699 self, other: Any, escape: Optional[str] = None
700 ) -> ColumnOperators:
701 r"""Implement the ``like`` operator.
703 In a column context, produces the expression:
705 .. sourcecode:: sql
707 a LIKE other
709 E.g.::
711 stmt = select(sometable).where(sometable.c.column.like("%foobar%"))
713 :param other: expression to be compared
714 :param escape: optional escape character, renders the ``ESCAPE``
715 keyword, e.g.::
717 somecolumn.like("foo/%bar", escape="/")
719 .. seealso::
721 :meth:`.ColumnOperators.ilike`
723 """
724 return self.operate(like_op, other, escape=escape)
726 def ilike(
727 self, other: Any, escape: Optional[str] = None
728 ) -> ColumnOperators:
729 r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE.
731 In a column context, produces an expression either of the form:
733 .. sourcecode:: sql
735 lower(a) LIKE lower(other)
737 Or on backends that support the ILIKE operator:
739 .. sourcecode:: sql
741 a ILIKE other
743 E.g.::
745 stmt = select(sometable).where(sometable.c.column.ilike("%foobar%"))
747 :param other: expression to be compared
748 :param escape: optional escape character, renders the ``ESCAPE``
749 keyword, e.g.::
751 somecolumn.ilike("foo/%bar", escape="/")
753 .. seealso::
755 :meth:`.ColumnOperators.like`
757 """ # noqa: E501
758 return self.operate(ilike_op, other, escape=escape)
760 def bitwise_xor(self, other: Any) -> ColumnOperators:
761 """Produce a bitwise XOR operation, typically via the ``^``
762 operator, or ``#`` for PostgreSQL.
764 .. versionadded:: 2.0.2
766 .. seealso::
768 :ref:`operators_bitwise`
770 """
772 return self.operate(bitwise_xor_op, other)
774 def bitwise_or(self, other: Any) -> ColumnOperators:
775 """Produce a bitwise OR operation, typically via the ``|``
776 operator.
778 .. versionadded:: 2.0.2
780 .. seealso::
782 :ref:`operators_bitwise`
784 """
786 return self.operate(bitwise_or_op, other)
788 def bitwise_and(self, other: Any) -> ColumnOperators:
789 """Produce a bitwise AND operation, typically via the ``&``
790 operator.
792 .. versionadded:: 2.0.2
794 .. seealso::
796 :ref:`operators_bitwise`
798 """
800 return self.operate(bitwise_and_op, other)
802 def bitwise_not(self) -> ColumnOperators:
803 """Produce a bitwise NOT operation, typically via the ``~``
804 operator.
806 .. versionadded:: 2.0.2
808 .. seealso::
810 :ref:`operators_bitwise`
812 """
814 return self.operate(bitwise_not_op)
816 def bitwise_lshift(self, other: Any) -> ColumnOperators:
817 """Produce a bitwise LSHIFT operation, typically via the ``<<``
818 operator.
820 .. versionadded:: 2.0.2
822 .. seealso::
824 :ref:`operators_bitwise`
826 """
828 return self.operate(bitwise_lshift_op, other)
830 def bitwise_rshift(self, other: Any) -> ColumnOperators:
831 """Produce a bitwise RSHIFT operation, typically via the ``>>``
832 operator.
834 .. versionadded:: 2.0.2
836 .. seealso::
838 :ref:`operators_bitwise`
840 """
842 return self.operate(bitwise_rshift_op, other)
844 def in_(self, other: Any) -> ColumnOperators:
845 """Implement the ``in`` operator.
847 In a column context, produces the clause ``column IN <other>``.
849 The given parameter ``other`` may be:
851 * A list of literal values,
852 e.g.::
854 stmt.where(column.in_([1, 2, 3]))
856 In this calling form, the list of items is converted to a set of
857 bound parameters the same length as the list given:
859 .. sourcecode:: sql
861 WHERE COL IN (?, ?, ?)
863 * A list of tuples may be provided if the comparison is against a
864 :func:`.tuple_` containing multiple expressions::
866 from sqlalchemy import tuple_
868 stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
870 * An empty list,
871 e.g.::
873 stmt.where(column.in_([]))
875 In this calling form, the expression renders an "empty set"
876 expression. These expressions are tailored to individual backends
877 and are generally trying to get an empty SELECT statement as a
878 subquery. Such as on SQLite, the expression is:
880 .. sourcecode:: sql
882 WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
884 .. versionchanged:: 1.4 empty IN expressions now use an
885 execution-time generated SELECT subquery in all cases.
887 * A bound parameter, e.g. :func:`.bindparam`, may be used if it
888 includes the :paramref:`.bindparam.expanding` flag::
890 stmt.where(column.in_(bindparam("value", expanding=True)))
892 In this calling form, the expression renders a special non-SQL
893 placeholder expression that looks like:
895 .. sourcecode:: sql
897 WHERE COL IN ([EXPANDING_value])
899 This placeholder expression is intercepted at statement execution
900 time to be converted into the variable number of bound parameter
901 form illustrated earlier. If the statement were executed as::
903 connection.execute(stmt, {"value": [1, 2, 3]})
905 The database would be passed a bound parameter for each value:
907 .. sourcecode:: sql
909 WHERE COL IN (?, ?, ?)
911 .. versionadded:: 1.2 added "expanding" bound parameters
913 If an empty list is passed, a special "empty list" expression,
914 which is specific to the database in use, is rendered. On
915 SQLite this would be:
917 .. sourcecode:: sql
919 WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
921 .. versionadded:: 1.3 "expanding" bound parameters now support
922 empty lists
924 * a :func:`_expression.select` construct, which is usually a
925 correlated scalar select::
927 stmt.where(
928 column.in_(select(othertable.c.y).where(table.c.x == othertable.c.x))
929 )
931 In this calling form, :meth:`.ColumnOperators.in_` renders as given:
933 .. sourcecode:: sql
935 WHERE COL IN (SELECT othertable.y
936 FROM othertable WHERE othertable.x = table.x)
938 :param other: a list of literals, a :func:`_expression.select`
939 construct, or a :func:`.bindparam` construct that includes the
940 :paramref:`.bindparam.expanding` flag set to True.
942 """ # noqa: E501
943 return self.operate(in_op, other)
945 def not_in(self, other: Any) -> ColumnOperators:
946 """implement the ``NOT IN`` operator.
948 This is equivalent to using negation with
949 :meth:`.ColumnOperators.in_`, i.e. ``~x.in_(y)``.
951 In the case that ``other`` is an empty sequence, the compiler
952 produces an "empty not in" expression. This defaults to the
953 expression "1 = 1" to produce true in all cases. The
954 :paramref:`_sa.create_engine.empty_in_strategy` may be used to
955 alter this behavior.
957 .. versionchanged:: 1.4 The ``not_in()`` operator is renamed from
958 ``notin_()`` in previous releases. The previous name remains
959 available for backwards compatibility.
961 .. versionchanged:: 1.2 The :meth:`.ColumnOperators.in_` and
962 :meth:`.ColumnOperators.not_in` operators
963 now produce a "static" expression for an empty IN sequence
964 by default.
966 .. seealso::
968 :meth:`.ColumnOperators.in_`
970 """
971 return self.operate(not_in_op, other)
973 # deprecated 1.4; see #5429
974 if TYPE_CHECKING:
976 def notin_(self, other: Any) -> ColumnOperators: ...
978 else:
979 notin_ = not_in
981 def not_like(
982 self, other: Any, escape: Optional[str] = None
983 ) -> ColumnOperators:
984 """implement the ``NOT LIKE`` operator.
986 This is equivalent to using negation with
987 :meth:`.ColumnOperators.like`, i.e. ``~x.like(y)``.
989 .. versionchanged:: 1.4 The ``not_like()`` operator is renamed from
990 ``notlike()`` in previous releases. The previous name remains
991 available for backwards compatibility.
993 .. seealso::
995 :meth:`.ColumnOperators.like`
997 """
998 return self.operate(not_like_op, other, escape=escape)
1000 # deprecated 1.4; see #5435
1001 if TYPE_CHECKING:
1003 def notlike(
1004 self, other: Any, escape: Optional[str] = None
1005 ) -> ColumnOperators: ...
1007 else:
1008 notlike = not_like
1010 def not_ilike(
1011 self, other: Any, escape: Optional[str] = None
1012 ) -> ColumnOperators:
1013 """implement the ``NOT ILIKE`` operator.
1015 This is equivalent to using negation with
1016 :meth:`.ColumnOperators.ilike`, i.e. ``~x.ilike(y)``.
1018 .. versionchanged:: 1.4 The ``not_ilike()`` operator is renamed from
1019 ``notilike()`` in previous releases. The previous name remains
1020 available for backwards compatibility.
1022 .. seealso::
1024 :meth:`.ColumnOperators.ilike`
1026 """
1027 return self.operate(not_ilike_op, other, escape=escape)
1029 # deprecated 1.4; see #5435
1030 if TYPE_CHECKING:
1032 def notilike(
1033 self, other: Any, escape: Optional[str] = None
1034 ) -> ColumnOperators: ...
1036 else:
1037 notilike = not_ilike
1039 def is_(self, other: Any) -> ColumnOperators:
1040 """Implement the ``IS`` operator.
1042 Normally, ``IS`` is generated automatically when comparing to a
1043 value of ``None``, which resolves to ``NULL``. However, explicit
1044 usage of ``IS`` may be desirable if comparing to boolean values
1045 on certain platforms.
1047 .. seealso:: :meth:`.ColumnOperators.is_not`
1049 """
1050 return self.operate(is_, other)
1052 def is_not(self, other: Any) -> ColumnOperators:
1053 """Implement the ``IS NOT`` operator.
1055 Normally, ``IS NOT`` is generated automatically when comparing to a
1056 value of ``None``, which resolves to ``NULL``. However, explicit
1057 usage of ``IS NOT`` may be desirable if comparing to boolean values
1058 on certain platforms.
1060 .. versionchanged:: 1.4 The ``is_not()`` operator is renamed from
1061 ``isnot()`` in previous releases. The previous name remains
1062 available for backwards compatibility.
1064 .. seealso:: :meth:`.ColumnOperators.is_`
1066 """
1067 return self.operate(is_not, other)
1069 # deprecated 1.4; see #5429
1070 if TYPE_CHECKING:
1072 def isnot(self, other: Any) -> ColumnOperators: ...
1074 else:
1075 isnot = is_not
1077 def startswith(
1078 self,
1079 other: Any,
1080 escape: Optional[str] = None,
1081 autoescape: bool = False,
1082 ) -> ColumnOperators:
1083 r"""Implement the ``startswith`` operator.
1085 Produces a LIKE expression that tests against a match for the start
1086 of a string value:
1088 .. sourcecode:: sql
1090 column LIKE <other> || '%'
1092 E.g.::
1094 stmt = select(sometable).where(sometable.c.column.startswith("foobar"))
1096 Since the operator uses ``LIKE``, wildcard characters
1097 ``"%"`` and ``"_"`` that are present inside the <other> expression
1098 will behave like wildcards as well. For literal string
1099 values, the :paramref:`.ColumnOperators.startswith.autoescape` flag
1100 may be set to ``True`` to apply escaping to occurrences of these
1101 characters within the string value so that they match as themselves
1102 and not as wildcard characters. Alternatively, the
1103 :paramref:`.ColumnOperators.startswith.escape` parameter will establish
1104 a given character as an escape character which can be of use when
1105 the target expression is not a literal string.
1107 :param other: expression to be compared. This is usually a plain
1108 string value, but can also be an arbitrary SQL expression. LIKE
1109 wildcard characters ``%`` and ``_`` are not escaped by default unless
1110 the :paramref:`.ColumnOperators.startswith.autoescape` flag is
1111 set to True.
1113 :param autoescape: boolean; when True, establishes an escape character
1114 within the LIKE expression, then applies it to all occurrences of
1115 ``"%"``, ``"_"`` and the escape character itself within the
1116 comparison value, which is assumed to be a literal string and not a
1117 SQL expression.
1119 An expression such as::
1121 somecolumn.startswith("foo%bar", autoescape=True)
1123 Will render as:
1125 .. sourcecode:: sql
1127 somecolumn LIKE :param || '%' ESCAPE '/'
1129 With the value of ``:param`` as ``"foo/%bar"``.
1131 :param escape: a character which when given will render with the
1132 ``ESCAPE`` keyword to establish that character as the escape
1133 character. This character can then be placed preceding occurrences
1134 of ``%`` and ``_`` to allow them to act as themselves and not
1135 wildcard characters.
1137 An expression such as::
1139 somecolumn.startswith("foo/%bar", escape="^")
1141 Will render as:
1143 .. sourcecode:: sql
1145 somecolumn LIKE :param || '%' ESCAPE '^'
1147 The parameter may also be combined with
1148 :paramref:`.ColumnOperators.startswith.autoescape`::
1150 somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
1152 Where above, the given literal parameter will be converted to
1153 ``"foo^%bar^^bat"`` before being passed to the database.
1155 .. seealso::
1157 :meth:`.ColumnOperators.endswith`
1159 :meth:`.ColumnOperators.contains`
1161 :meth:`.ColumnOperators.like`
1163 """ # noqa: E501
1164 return self.operate(
1165 startswith_op, other, escape=escape, autoescape=autoescape
1166 )
1168 def istartswith(
1169 self,
1170 other: Any,
1171 escape: Optional[str] = None,
1172 autoescape: bool = False,
1173 ) -> ColumnOperators:
1174 r"""Implement the ``istartswith`` operator, e.g. case insensitive
1175 version of :meth:`.ColumnOperators.startswith`.
1177 Produces a LIKE expression that tests against an insensitive
1178 match for the start of a string value:
1180 .. sourcecode:: sql
1182 lower(column) LIKE lower(<other>) || '%'
1184 E.g.::
1186 stmt = select(sometable).where(sometable.c.column.istartswith("foobar"))
1188 Since the operator uses ``LIKE``, wildcard characters
1189 ``"%"`` and ``"_"`` that are present inside the <other> expression
1190 will behave like wildcards as well. For literal string
1191 values, the :paramref:`.ColumnOperators.istartswith.autoescape` flag
1192 may be set to ``True`` to apply escaping to occurrences of these
1193 characters within the string value so that they match as themselves
1194 and not as wildcard characters. Alternatively, the
1195 :paramref:`.ColumnOperators.istartswith.escape` parameter will
1196 establish a given character as an escape character which can be of
1197 use when the target expression is not a literal string.
1199 :param other: expression to be compared. This is usually a plain
1200 string value, but can also be an arbitrary SQL expression. LIKE
1201 wildcard characters ``%`` and ``_`` are not escaped by default unless
1202 the :paramref:`.ColumnOperators.istartswith.autoescape` flag is
1203 set to True.
1205 :param autoescape: boolean; when True, establishes an escape character
1206 within the LIKE expression, then applies it to all occurrences of
1207 ``"%"``, ``"_"`` and the escape character itself within the
1208 comparison value, which is assumed to be a literal string and not a
1209 SQL expression.
1211 An expression such as::
1213 somecolumn.istartswith("foo%bar", autoescape=True)
1215 Will render as:
1217 .. sourcecode:: sql
1219 lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/'
1221 With the value of ``:param`` as ``"foo/%bar"``.
1223 :param escape: a character which when given will render with the
1224 ``ESCAPE`` keyword to establish that character as the escape
1225 character. This character can then be placed preceding occurrences
1226 of ``%`` and ``_`` to allow them to act as themselves and not
1227 wildcard characters.
1229 An expression such as::
1231 somecolumn.istartswith("foo/%bar", escape="^")
1233 Will render as:
1235 .. sourcecode:: sql
1237 lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^'
1239 The parameter may also be combined with
1240 :paramref:`.ColumnOperators.istartswith.autoescape`::
1242 somecolumn.istartswith("foo%bar^bat", escape="^", autoescape=True)
1244 Where above, the given literal parameter will be converted to
1245 ``"foo^%bar^^bat"`` before being passed to the database.
1247 .. seealso::
1249 :meth:`.ColumnOperators.startswith`
1250 """ # noqa: E501
1251 return self.operate(
1252 istartswith_op, other, escape=escape, autoescape=autoescape
1253 )
1255 def endswith(
1256 self,
1257 other: Any,
1258 escape: Optional[str] = None,
1259 autoescape: bool = False,
1260 ) -> ColumnOperators:
1261 r"""Implement the 'endswith' operator.
1263 Produces a LIKE expression that tests against a match for the end
1264 of a string value:
1266 .. sourcecode:: sql
1268 column LIKE '%' || <other>
1270 E.g.::
1272 stmt = select(sometable).where(sometable.c.column.endswith("foobar"))
1274 Since the operator uses ``LIKE``, wildcard characters
1275 ``"%"`` and ``"_"`` that are present inside the <other> expression
1276 will behave like wildcards as well. For literal string
1277 values, the :paramref:`.ColumnOperators.endswith.autoescape` flag
1278 may be set to ``True`` to apply escaping to occurrences of these
1279 characters within the string value so that they match as themselves
1280 and not as wildcard characters. Alternatively, the
1281 :paramref:`.ColumnOperators.endswith.escape` parameter will establish
1282 a given character as an escape character which can be of use when
1283 the target expression is not a literal string.
1285 :param other: expression to be compared. This is usually a plain
1286 string value, but can also be an arbitrary SQL expression. LIKE
1287 wildcard characters ``%`` and ``_`` are not escaped by default unless
1288 the :paramref:`.ColumnOperators.endswith.autoescape` flag is
1289 set to True.
1291 :param autoescape: boolean; when True, establishes an escape character
1292 within the LIKE expression, then applies it to all occurrences of
1293 ``"%"``, ``"_"`` and the escape character itself within the
1294 comparison value, which is assumed to be a literal string and not a
1295 SQL expression.
1297 An expression such as::
1299 somecolumn.endswith("foo%bar", autoescape=True)
1301 Will render as:
1303 .. sourcecode:: sql
1305 somecolumn LIKE '%' || :param ESCAPE '/'
1307 With the value of ``:param`` as ``"foo/%bar"``.
1309 :param escape: a character which when given will render with the
1310 ``ESCAPE`` keyword to establish that character as the escape
1311 character. This character can then be placed preceding occurrences
1312 of ``%`` and ``_`` to allow them to act as themselves and not
1313 wildcard characters.
1315 An expression such as::
1317 somecolumn.endswith("foo/%bar", escape="^")
1319 Will render as:
1321 .. sourcecode:: sql
1323 somecolumn LIKE '%' || :param ESCAPE '^'
1325 The parameter may also be combined with
1326 :paramref:`.ColumnOperators.endswith.autoescape`::
1328 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
1330 Where above, the given literal parameter will be converted to
1331 ``"foo^%bar^^bat"`` before being passed to the database.
1333 .. seealso::
1335 :meth:`.ColumnOperators.startswith`
1337 :meth:`.ColumnOperators.contains`
1339 :meth:`.ColumnOperators.like`
1341 """ # noqa: E501
1342 return self.operate(
1343 endswith_op, other, escape=escape, autoescape=autoescape
1344 )
1346 def iendswith(
1347 self,
1348 other: Any,
1349 escape: Optional[str] = None,
1350 autoescape: bool = False,
1351 ) -> ColumnOperators:
1352 r"""Implement the ``iendswith`` operator, e.g. case insensitive
1353 version of :meth:`.ColumnOperators.endswith`.
1355 Produces a LIKE expression that tests against an insensitive match
1356 for the end of a string value:
1358 .. sourcecode:: sql
1360 lower(column) LIKE '%' || lower(<other>)
1362 E.g.::
1364 stmt = select(sometable).where(sometable.c.column.iendswith("foobar"))
1366 Since the operator uses ``LIKE``, wildcard characters
1367 ``"%"`` and ``"_"`` that are present inside the <other> expression
1368 will behave like wildcards as well. For literal string
1369 values, the :paramref:`.ColumnOperators.iendswith.autoescape` flag
1370 may be set to ``True`` to apply escaping to occurrences of these
1371 characters within the string value so that they match as themselves
1372 and not as wildcard characters. Alternatively, the
1373 :paramref:`.ColumnOperators.iendswith.escape` parameter will establish
1374 a given character as an escape character which can be of use when
1375 the target expression is not a literal string.
1377 :param other: expression to be compared. This is usually a plain
1378 string value, but can also be an arbitrary SQL expression. LIKE
1379 wildcard characters ``%`` and ``_`` are not escaped by default unless
1380 the :paramref:`.ColumnOperators.iendswith.autoescape` flag is
1381 set to True.
1383 :param autoescape: boolean; when True, establishes an escape character
1384 within the LIKE expression, then applies it to all occurrences of
1385 ``"%"``, ``"_"`` and the escape character itself within the
1386 comparison value, which is assumed to be a literal string and not a
1387 SQL expression.
1389 An expression such as::
1391 somecolumn.iendswith("foo%bar", autoescape=True)
1393 Will render as:
1395 .. sourcecode:: sql
1397 lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '/'
1399 With the value of ``:param`` as ``"foo/%bar"``.
1401 :param escape: a character which when given will render with the
1402 ``ESCAPE`` keyword to establish that character as the escape
1403 character. This character can then be placed preceding occurrences
1404 of ``%`` and ``_`` to allow them to act as themselves and not
1405 wildcard characters.
1407 An expression such as::
1409 somecolumn.iendswith("foo/%bar", escape="^")
1411 Will render as:
1413 .. sourcecode:: sql
1415 lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^'
1417 The parameter may also be combined with
1418 :paramref:`.ColumnOperators.iendswith.autoescape`::
1420 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
1422 Where above, the given literal parameter will be converted to
1423 ``"foo^%bar^^bat"`` before being passed to the database.
1425 .. seealso::
1427 :meth:`.ColumnOperators.endswith`
1428 """ # noqa: E501
1429 return self.operate(
1430 iendswith_op, other, escape=escape, autoescape=autoescape
1431 )
1433 def contains(self, other: Any, **kw: Any) -> ColumnOperators:
1434 r"""Implement the 'contains' operator.
1436 Produces a LIKE expression that tests against a match for the middle
1437 of a string value:
1439 .. sourcecode:: sql
1441 column LIKE '%' || <other> || '%'
1443 E.g.::
1445 stmt = select(sometable).where(sometable.c.column.contains("foobar"))
1447 Since the operator uses ``LIKE``, wildcard characters
1448 ``"%"`` and ``"_"`` that are present inside the <other> expression
1449 will behave like wildcards as well. For literal string
1450 values, the :paramref:`.ColumnOperators.contains.autoescape` flag
1451 may be set to ``True`` to apply escaping to occurrences of these
1452 characters within the string value so that they match as themselves
1453 and not as wildcard characters. Alternatively, the
1454 :paramref:`.ColumnOperators.contains.escape` parameter will establish
1455 a given character as an escape character which can be of use when
1456 the target expression is not a literal string.
1458 :param other: expression to be compared. This is usually a plain
1459 string value, but can also be an arbitrary SQL expression. LIKE
1460 wildcard characters ``%`` and ``_`` are not escaped by default unless
1461 the :paramref:`.ColumnOperators.contains.autoescape` flag is
1462 set to True.
1464 :param autoescape: boolean; when True, establishes an escape character
1465 within the LIKE expression, then applies it to all occurrences of
1466 ``"%"``, ``"_"`` and the escape character itself within the
1467 comparison value, which is assumed to be a literal string and not a
1468 SQL expression.
1470 An expression such as::
1472 somecolumn.contains("foo%bar", autoescape=True)
1474 Will render as:
1476 .. sourcecode:: sql
1478 somecolumn LIKE '%' || :param || '%' ESCAPE '/'
1480 With the value of ``:param`` as ``"foo/%bar"``.
1482 :param escape: a character which when given will render with the
1483 ``ESCAPE`` keyword to establish that character as the escape
1484 character. This character can then be placed preceding occurrences
1485 of ``%`` and ``_`` to allow them to act as themselves and not
1486 wildcard characters.
1488 An expression such as::
1490 somecolumn.contains("foo/%bar", escape="^")
1492 Will render as:
1494 .. sourcecode:: sql
1496 somecolumn LIKE '%' || :param || '%' ESCAPE '^'
1498 The parameter may also be combined with
1499 :paramref:`.ColumnOperators.contains.autoescape`::
1501 somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
1503 Where above, the given literal parameter will be converted to
1504 ``"foo^%bar^^bat"`` before being passed to the database.
1506 .. seealso::
1508 :meth:`.ColumnOperators.startswith`
1510 :meth:`.ColumnOperators.endswith`
1512 :meth:`.ColumnOperators.like`
1515 """ # noqa: E501
1516 return self.operate(contains_op, other, **kw)
1518 def icontains(self, other: Any, **kw: Any) -> ColumnOperators:
1519 r"""Implement the ``icontains`` operator, e.g. case insensitive
1520 version of :meth:`.ColumnOperators.contains`.
1522 Produces a LIKE expression that tests against an insensitive match
1523 for the middle of a string value:
1525 .. sourcecode:: sql
1527 lower(column) LIKE '%' || lower(<other>) || '%'
1529 E.g.::
1531 stmt = select(sometable).where(sometable.c.column.icontains("foobar"))
1533 Since the operator uses ``LIKE``, wildcard characters
1534 ``"%"`` and ``"_"`` that are present inside the <other> expression
1535 will behave like wildcards as well. For literal string
1536 values, the :paramref:`.ColumnOperators.icontains.autoescape` flag
1537 may be set to ``True`` to apply escaping to occurrences of these
1538 characters within the string value so that they match as themselves
1539 and not as wildcard characters. Alternatively, the
1540 :paramref:`.ColumnOperators.icontains.escape` parameter will establish
1541 a given character as an escape character which can be of use when
1542 the target expression is not a literal string.
1544 :param other: expression to be compared. This is usually a plain
1545 string value, but can also be an arbitrary SQL expression. LIKE
1546 wildcard characters ``%`` and ``_`` are not escaped by default unless
1547 the :paramref:`.ColumnOperators.icontains.autoescape` flag is
1548 set to True.
1550 :param autoescape: boolean; when True, establishes an escape character
1551 within the LIKE expression, then applies it to all occurrences of
1552 ``"%"``, ``"_"`` and the escape character itself within the
1553 comparison value, which is assumed to be a literal string and not a
1554 SQL expression.
1556 An expression such as::
1558 somecolumn.icontains("foo%bar", autoescape=True)
1560 Will render as:
1562 .. sourcecode:: sql
1564 lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '/'
1566 With the value of ``:param`` as ``"foo/%bar"``.
1568 :param escape: a character which when given will render with the
1569 ``ESCAPE`` keyword to establish that character as the escape
1570 character. This character can then be placed preceding occurrences
1571 of ``%`` and ``_`` to allow them to act as themselves and not
1572 wildcard characters.
1574 An expression such as::
1576 somecolumn.icontains("foo/%bar", escape="^")
1578 Will render as:
1580 .. sourcecode:: sql
1582 lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^'
1584 The parameter may also be combined with
1585 :paramref:`.ColumnOperators.contains.autoescape`::
1587 somecolumn.icontains("foo%bar^bat", escape="^", autoescape=True)
1589 Where above, the given literal parameter will be converted to
1590 ``"foo^%bar^^bat"`` before being passed to the database.
1592 .. seealso::
1594 :meth:`.ColumnOperators.contains`
1596 """ # noqa: E501
1597 return self.operate(icontains_op, other, **kw)
1599 def match(self, other: Any, **kwargs: Any) -> ColumnOperators:
1600 """Implements a database-specific 'match' operator.
1602 :meth:`_sql.ColumnOperators.match` attempts to resolve to
1603 a MATCH-like function or operator provided by the backend.
1604 Examples include:
1606 * PostgreSQL - renders ``x @@ plainto_tsquery(y)``
1608 .. versionchanged:: 2.0 ``plainto_tsquery()`` is used instead
1609 of ``to_tsquery()`` for PostgreSQL now; for compatibility with
1610 other forms, see :ref:`postgresql_match`.
1613 * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)``
1615 .. seealso::
1617 :class:`_mysql.match` - MySQL specific construct with
1618 additional features.
1620 * Oracle Database - renders ``CONTAINS(x, y)``
1621 * other backends may provide special implementations.
1622 * Backends without any special implementation will emit
1623 the operator as "MATCH". This is compatible with SQLite, for
1624 example.
1626 """
1627 return self.operate(match_op, other, **kwargs)
1629 def regexp_match(
1630 self, pattern: Any, flags: Optional[str] = None
1631 ) -> ColumnOperators:
1632 """Implements a database-specific 'regexp match' operator.
1634 E.g.::
1636 stmt = select(table.c.some_column).where(
1637 table.c.some_column.regexp_match("^(b|c)")
1638 )
1640 :meth:`_sql.ColumnOperators.regexp_match` attempts to resolve to
1641 a REGEXP-like function or operator provided by the backend, however
1642 the specific regular expression syntax and flags available are
1643 **not backend agnostic**.
1645 Examples include:
1647 * PostgreSQL - renders ``x ~ y`` or ``x !~ y`` when negated.
1648 * Oracle Database - renders ``REGEXP_LIKE(x, y)``
1649 * SQLite - uses SQLite's ``REGEXP`` placeholder operator and calls into
1650 the Python ``re.match()`` builtin.
1651 * other backends may provide special implementations.
1652 * Backends without any special implementation will emit
1653 the operator as "REGEXP" or "NOT REGEXP". This is compatible with
1654 SQLite and MySQL, for example.
1656 Regular expression support is currently implemented for Oracle
1657 Database, PostgreSQL, MySQL and MariaDB. Partial support is available
1658 for SQLite. Support among third-party dialects may vary.
1660 :param pattern: The regular expression pattern string or column
1661 clause.
1662 :param flags: Any regular expression string flags to apply, passed as
1663 plain Python string only. These flags are backend specific.
1664 Some backends, like PostgreSQL and MariaDB, may alternatively
1665 specify the flags as part of the pattern.
1666 When using the ignore case flag 'i' in PostgreSQL, the ignore case
1667 regexp match operator ``~*`` or ``!~*`` will be used.
1669 .. versionadded:: 1.4
1671 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation
1672 error, the "flags" parameter previously accepted SQL expression
1673 objects such as column expressions in addition to plain Python
1674 strings. This implementation did not work correctly with caching
1675 and was removed; strings only should be passed for the "flags"
1676 parameter, as these flags are rendered as literal inline values
1677 within SQL expressions.
1679 .. seealso::
1681 :meth:`_sql.ColumnOperators.regexp_replace`
1684 """
1685 return self.operate(regexp_match_op, pattern, flags=flags)
1687 def regexp_replace(
1688 self, pattern: Any, replacement: Any, flags: Optional[str] = None
1689 ) -> ColumnOperators:
1690 """Implements a database-specific 'regexp replace' operator.
1692 E.g.::
1694 stmt = select(
1695 table.c.some_column.regexp_replace("b(..)", "X\1Y", flags="g")
1696 )
1698 :meth:`_sql.ColumnOperators.regexp_replace` attempts to resolve to
1699 a REGEXP_REPLACE-like function provided by the backend, that
1700 usually emit the function ``REGEXP_REPLACE()``. However,
1701 the specific regular expression syntax and flags available are
1702 **not backend agnostic**.
1704 Regular expression replacement support is currently implemented for
1705 Oracle Database, PostgreSQL, MySQL 8 or greater and MariaDB. Support
1706 among third-party dialects may vary.
1708 :param pattern: The regular expression pattern string or column
1709 clause.
1710 :param pattern: The replacement string or column clause.
1711 :param flags: Any regular expression string flags to apply, passed as
1712 plain Python string only. These flags are backend specific.
1713 Some backends, like PostgreSQL and MariaDB, may alternatively
1714 specify the flags as part of the pattern.
1716 .. versionadded:: 1.4
1718 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation
1719 error, the "flags" parameter previously accepted SQL expression
1720 objects such as column expressions in addition to plain Python
1721 strings. This implementation did not work correctly with caching
1722 and was removed; strings only should be passed for the "flags"
1723 parameter, as these flags are rendered as literal inline values
1724 within SQL expressions.
1727 .. seealso::
1729 :meth:`_sql.ColumnOperators.regexp_match`
1731 """
1732 return self.operate(
1733 regexp_replace_op,
1734 pattern,
1735 replacement=replacement,
1736 flags=flags,
1737 )
1739 def desc(self) -> ColumnOperators:
1740 """Produce a :func:`_expression.desc` clause against the
1741 parent object."""
1742 return self.operate(desc_op)
1744 def asc(self) -> ColumnOperators:
1745 """Produce a :func:`_expression.asc` clause against the
1746 parent object."""
1747 return self.operate(asc_op)
1749 def nulls_first(self) -> ColumnOperators:
1750 """Produce a :func:`_expression.nulls_first` clause against the
1751 parent object.
1753 .. versionchanged:: 1.4 The ``nulls_first()`` operator is
1754 renamed from ``nullsfirst()`` in previous releases.
1755 The previous name remains available for backwards compatibility.
1756 """
1757 return self.operate(nulls_first_op)
1759 # deprecated 1.4; see #5435
1760 if TYPE_CHECKING:
1762 def nullsfirst(self) -> ColumnOperators: ...
1764 else:
1765 nullsfirst = nulls_first
1767 def nulls_last(self) -> ColumnOperators:
1768 """Produce a :func:`_expression.nulls_last` clause against the
1769 parent object.
1771 .. versionchanged:: 1.4 The ``nulls_last()`` operator is
1772 renamed from ``nullslast()`` in previous releases.
1773 The previous name remains available for backwards compatibility.
1774 """
1775 return self.operate(nulls_last_op)
1777 # deprecated 1.4; see #5429
1778 if TYPE_CHECKING:
1780 def nullslast(self) -> ColumnOperators: ...
1782 else:
1783 nullslast = nulls_last
1785 def collate(self, collation: str) -> ColumnOperators:
1786 """Produce a :func:`_expression.collate` clause against
1787 the parent object, given the collation string.
1789 .. seealso::
1791 :func:`_expression.collate`
1793 """
1794 return self.operate(collate, collation)
1796 def __radd__(self, other: Any) -> ColumnOperators:
1797 """Implement the ``+`` operator in reverse.
1799 See :meth:`.ColumnOperators.__add__`.
1801 """
1802 return self.reverse_operate(add, other)
1804 def __rsub__(self, other: Any) -> ColumnOperators:
1805 """Implement the ``-`` operator in reverse.
1807 See :meth:`.ColumnOperators.__sub__`.
1809 """
1810 return self.reverse_operate(sub, other)
1812 def __rmul__(self, other: Any) -> ColumnOperators:
1813 """Implement the ``*`` operator in reverse.
1815 See :meth:`.ColumnOperators.__mul__`.
1817 """
1818 return self.reverse_operate(mul, other)
1820 def __rmod__(self, other: Any) -> ColumnOperators:
1821 """Implement the ``%`` operator in reverse.
1823 See :meth:`.ColumnOperators.__mod__`.
1825 """
1826 return self.reverse_operate(mod, other)
1828 def between(
1829 self, cleft: Any, cright: Any, symmetric: bool = False
1830 ) -> ColumnOperators:
1831 """Produce a :func:`_expression.between` clause against
1832 the parent object, given the lower and upper range.
1834 """
1835 return self.operate(between_op, cleft, cright, symmetric=symmetric)
1837 def distinct(self) -> ColumnOperators:
1838 """Produce a :func:`_expression.distinct` clause against the
1839 parent object.
1841 """
1842 return self.operate(distinct_op)
1844 def any_(self) -> ColumnOperators:
1845 """Produce an :func:`_expression.any_` clause against the
1846 parent object.
1848 See the documentation for :func:`_sql.any_` for examples.
1850 .. note:: be sure to not confuse the newer
1851 :meth:`_sql.ColumnOperators.any_` method with the **legacy**
1852 version of this method, the :meth:`_types.ARRAY.Comparator.any`
1853 method that's specific to :class:`_types.ARRAY`, which uses a
1854 different calling style.
1856 """
1857 return self.operate(any_op)
1859 def all_(self) -> ColumnOperators:
1860 """Produce an :func:`_expression.all_` clause against the
1861 parent object.
1863 See the documentation for :func:`_sql.all_` for examples.
1865 .. note:: be sure to not confuse the newer
1866 :meth:`_sql.ColumnOperators.all_` method with the **legacy**
1867 version of this method, the :meth:`_types.ARRAY.Comparator.all`
1868 method that's specific to :class:`_types.ARRAY`, which uses a
1869 different calling style.
1871 """
1872 return self.operate(all_op)
1874 def __add__(self, other: Any) -> ColumnOperators:
1875 """Implement the ``+`` operator.
1877 In a column context, produces the clause ``a + b``
1878 if the parent object has non-string affinity.
1879 If the parent object has a string affinity,
1880 produces the concatenation operator, ``a || b`` -
1881 see :meth:`.ColumnOperators.concat`.
1883 """
1884 return self.operate(add, other)
1886 def __sub__(self, other: Any) -> ColumnOperators:
1887 """Implement the ``-`` operator.
1889 In a column context, produces the clause ``a - b``.
1891 """
1892 return self.operate(sub, other)
1894 def __mul__(self, other: Any) -> ColumnOperators:
1895 """Implement the ``*`` operator.
1897 In a column context, produces the clause ``a * b``.
1899 """
1900 return self.operate(mul, other)
1902 def __mod__(self, other: Any) -> ColumnOperators:
1903 """Implement the ``%`` operator.
1905 In a column context, produces the clause ``a % b``.
1907 """
1908 return self.operate(mod, other)
1910 def __truediv__(self, other: Any) -> ColumnOperators:
1911 """Implement the ``/`` operator.
1913 In a column context, produces the clause ``a / b``, and
1914 considers the result type to be numeric.
1916 .. versionchanged:: 2.0 The truediv operator against two integers
1917 is now considered to return a numeric value. Behavior on specific
1918 backends may vary.
1920 """
1921 return self.operate(truediv, other)
1923 def __rtruediv__(self, other: Any) -> ColumnOperators:
1924 """Implement the ``/`` operator in reverse.
1926 See :meth:`.ColumnOperators.__truediv__`.
1928 """
1929 return self.reverse_operate(truediv, other)
1931 def __floordiv__(self, other: Any) -> ColumnOperators:
1932 """Implement the ``//`` operator.
1934 In a column context, produces the clause ``a / b``,
1935 which is the same as "truediv", but considers the result
1936 type to be integer.
1938 .. versionadded:: 2.0
1940 """
1941 return self.operate(floordiv, other)
1943 def __rfloordiv__(self, other: Any) -> ColumnOperators:
1944 """Implement the ``//`` operator in reverse.
1946 See :meth:`.ColumnOperators.__floordiv__`.
1948 """
1949 return self.reverse_operate(floordiv, other)
1952_commutative: Set[Any] = {eq, ne, add, mul}
1953_comparison: Set[Any] = {eq, ne, lt, gt, ge, le}
1956def _operator_fn(fn: Callable[..., Any]) -> OperatorType:
1957 return cast(OperatorType, fn)
1960def commutative_op(fn: _FN) -> _FN:
1961 _commutative.add(fn)
1962 return fn
1965def comparison_op(fn: _FN) -> _FN:
1966 _comparison.add(fn)
1967 return fn
1970@_operator_fn
1971def from_() -> Any:
1972 raise NotImplementedError()
1975@_operator_fn
1976@comparison_op
1977def function_as_comparison_op() -> Any:
1978 raise NotImplementedError()
1981@_operator_fn
1982def as_() -> Any:
1983 raise NotImplementedError()
1986@_operator_fn
1987def exists() -> Any:
1988 raise NotImplementedError()
1991@_operator_fn
1992def is_true(a: Any) -> Any:
1993 raise NotImplementedError()
1996# 1.4 deprecated; see #5435
1997if TYPE_CHECKING:
1999 @_operator_fn
2000 def istrue(a: Any) -> Any: ...
2002else:
2003 istrue = is_true
2006@_operator_fn
2007def is_false(a: Any) -> Any:
2008 raise NotImplementedError()
2011# 1.4 deprecated; see #5435
2012if TYPE_CHECKING:
2014 @_operator_fn
2015 def isfalse(a: Any) -> Any: ...
2017else:
2018 isfalse = is_false
2021@comparison_op
2022@_operator_fn
2023def is_distinct_from(a: Any, b: Any) -> Any:
2024 return a.is_distinct_from(b)
2027@comparison_op
2028@_operator_fn
2029def is_not_distinct_from(a: Any, b: Any) -> Any:
2030 return a.is_not_distinct_from(b)
2033# deprecated 1.4; see #5435
2034if TYPE_CHECKING:
2036 @_operator_fn
2037 def isnot_distinct_from(a: Any, b: Any) -> Any: ...
2039else:
2040 isnot_distinct_from = is_not_distinct_from
2043@comparison_op
2044@_operator_fn
2045def is_(a: Any, b: Any) -> Any:
2046 return a.is_(b)
2049@comparison_op
2050@_operator_fn
2051def is_not(a: Any, b: Any) -> Any:
2052 return a.is_not(b)
2055# 1.4 deprecated; see #5429
2056if TYPE_CHECKING:
2058 @_operator_fn
2059 def isnot(a: Any, b: Any) -> Any: ...
2061else:
2062 isnot = is_not
2065@_operator_fn
2066def collate(a: Any, b: Any) -> Any:
2067 return a.collate(b)
2070@_operator_fn
2071def op(a: Any, opstring: str, b: Any) -> Any:
2072 return a.op(opstring)(b)
2075@comparison_op
2076@_operator_fn
2077def like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2078 return a.like(b, escape=escape)
2081@comparison_op
2082@_operator_fn
2083def not_like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2084 return a.notlike(b, escape=escape)
2087# 1.4 deprecated; see #5435
2088if TYPE_CHECKING:
2090 @_operator_fn
2091 def notlike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ...
2093else:
2094 notlike_op = not_like_op
2097@comparison_op
2098@_operator_fn
2099def ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2100 return a.ilike(b, escape=escape)
2103@comparison_op
2104@_operator_fn
2105def not_ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2106 return a.not_ilike(b, escape=escape)
2109# 1.4 deprecated; see #5435
2110if TYPE_CHECKING:
2112 @_operator_fn
2113 def notilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ...
2115else:
2116 notilike_op = not_ilike_op
2119@comparison_op
2120@_operator_fn
2121def between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any:
2122 return a.between(b, c, symmetric=symmetric)
2125@comparison_op
2126@_operator_fn
2127def not_between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any:
2128 return ~a.between(b, c, symmetric=symmetric)
2131# 1.4 deprecated; see #5435
2132if TYPE_CHECKING:
2134 @_operator_fn
2135 def notbetween_op(
2136 a: Any, b: Any, c: Any, symmetric: bool = False
2137 ) -> Any: ...
2139else:
2140 notbetween_op = not_between_op
2143@comparison_op
2144@_operator_fn
2145def in_op(a: Any, b: Any) -> Any:
2146 return a.in_(b)
2149@comparison_op
2150@_operator_fn
2151def not_in_op(a: Any, b: Any) -> Any:
2152 return a.not_in(b)
2155# 1.4 deprecated; see #5429
2156if TYPE_CHECKING:
2158 @_operator_fn
2159 def notin_op(a: Any, b: Any) -> Any: ...
2161else:
2162 notin_op = not_in_op
2165@_operator_fn
2166def distinct_op(a: Any) -> Any:
2167 return a.distinct()
2170@_operator_fn
2171def any_op(a: Any) -> Any:
2172 return a.any_()
2175@_operator_fn
2176def all_op(a: Any) -> Any:
2177 return a.all_()
2180def _escaped_like_impl(
2181 fn: Callable[..., Any], other: Any, escape: Optional[str], autoescape: bool
2182) -> Any:
2183 if autoescape:
2184 if autoescape is not True:
2185 util.warn(
2186 "The autoescape parameter is now a simple boolean True/False"
2187 )
2188 if escape is None:
2189 escape = "/"
2191 if not isinstance(other, str):
2192 raise TypeError("String value expected when autoescape=True")
2194 if escape not in ("%", "_"):
2195 other = other.replace(escape, escape + escape)
2197 other = other.replace("%", escape + "%").replace("_", escape + "_")
2199 return fn(other, escape=escape)
2202@comparison_op
2203@_operator_fn
2204def startswith_op(
2205 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2206) -> Any:
2207 return _escaped_like_impl(a.startswith, b, escape, autoescape)
2210@comparison_op
2211@_operator_fn
2212def not_startswith_op(
2213 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2214) -> Any:
2215 return ~_escaped_like_impl(a.startswith, b, escape, autoescape)
2218# 1.4 deprecated; see #5435
2219if TYPE_CHECKING:
2221 @_operator_fn
2222 def notstartswith_op(
2223 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2224 ) -> Any: ...
2226else:
2227 notstartswith_op = not_startswith_op
2230@comparison_op
2231@_operator_fn
2232def istartswith_op(
2233 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2234) -> Any:
2235 return _escaped_like_impl(a.istartswith, b, escape, autoescape)
2238@comparison_op
2239@_operator_fn
2240def not_istartswith_op(
2241 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2242) -> Any:
2243 return ~_escaped_like_impl(a.istartswith, b, escape, autoescape)
2246@comparison_op
2247@_operator_fn
2248def endswith_op(
2249 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2250) -> Any:
2251 return _escaped_like_impl(a.endswith, b, escape, autoescape)
2254@comparison_op
2255@_operator_fn
2256def not_endswith_op(
2257 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2258) -> Any:
2259 return ~_escaped_like_impl(a.endswith, b, escape, autoescape)
2262# 1.4 deprecated; see #5435
2263if TYPE_CHECKING:
2265 @_operator_fn
2266 def notendswith_op(
2267 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2268 ) -> Any: ...
2270else:
2271 notendswith_op = not_endswith_op
2274@comparison_op
2275@_operator_fn
2276def iendswith_op(
2277 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2278) -> Any:
2279 return _escaped_like_impl(a.iendswith, b, escape, autoescape)
2282@comparison_op
2283@_operator_fn
2284def not_iendswith_op(
2285 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2286) -> Any:
2287 return ~_escaped_like_impl(a.iendswith, b, escape, autoescape)
2290@comparison_op
2291@_operator_fn
2292def contains_op(
2293 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2294) -> Any:
2295 return _escaped_like_impl(a.contains, b, escape, autoescape)
2298@comparison_op
2299@_operator_fn
2300def not_contains_op(
2301 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2302) -> Any:
2303 return ~_escaped_like_impl(a.contains, b, escape, autoescape)
2306# 1.4 deprecated; see #5435
2307if TYPE_CHECKING:
2309 @_operator_fn
2310 def notcontains_op(
2311 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2312 ) -> Any: ...
2314else:
2315 notcontains_op = not_contains_op
2318@comparison_op
2319@_operator_fn
2320def icontains_op(
2321 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2322) -> Any:
2323 return _escaped_like_impl(a.icontains, b, escape, autoescape)
2326@comparison_op
2327@_operator_fn
2328def not_icontains_op(
2329 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2330) -> Any:
2331 return ~_escaped_like_impl(a.icontains, b, escape, autoescape)
2334@comparison_op
2335@_operator_fn
2336def match_op(a: Any, b: Any, **kw: Any) -> Any:
2337 return a.match(b, **kw)
2340@comparison_op
2341@_operator_fn
2342def regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any:
2343 return a.regexp_match(b, flags=flags)
2346@comparison_op
2347@_operator_fn
2348def not_regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any:
2349 return ~a.regexp_match(b, flags=flags)
2352@_operator_fn
2353def regexp_replace_op(
2354 a: Any, b: Any, replacement: Any, flags: Optional[str] = None
2355) -> Any:
2356 return a.regexp_replace(b, replacement=replacement, flags=flags)
2359@comparison_op
2360@_operator_fn
2361def not_match_op(a: Any, b: Any, **kw: Any) -> Any:
2362 return ~a.match(b, **kw)
2365# 1.4 deprecated; see #5429
2366if TYPE_CHECKING:
2368 @_operator_fn
2369 def notmatch_op(a: Any, b: Any, **kw: Any) -> Any: ...
2371else:
2372 notmatch_op = not_match_op
2375@_operator_fn
2376def comma_op(a: Any, b: Any) -> Any:
2377 raise NotImplementedError()
2380@_operator_fn
2381def filter_op(a: Any, b: Any) -> Any:
2382 raise NotImplementedError()
2385@_operator_fn
2386def concat_op(a: Any, b: Any) -> Any:
2387 try:
2388 concat = a.concat
2389 except AttributeError:
2390 return b._rconcat(a)
2391 else:
2392 return concat(b)
2395@_operator_fn
2396def desc_op(a: Any) -> Any:
2397 return a.desc()
2400@_operator_fn
2401def asc_op(a: Any) -> Any:
2402 return a.asc()
2405@_operator_fn
2406def nulls_first_op(a: Any) -> Any:
2407 return a.nulls_first()
2410# 1.4 deprecated; see #5435
2411if TYPE_CHECKING:
2413 @_operator_fn
2414 def nullsfirst_op(a: Any) -> Any: ...
2416else:
2417 nullsfirst_op = nulls_first_op
2420@_operator_fn
2421def nulls_last_op(a: Any) -> Any:
2422 return a.nulls_last()
2425# 1.4 deprecated; see #5435
2426if TYPE_CHECKING:
2428 @_operator_fn
2429 def nullslast_op(a: Any) -> Any: ...
2431else:
2432 nullslast_op = nulls_last_op
2435@_operator_fn
2436def json_getitem_op(a: Any, b: Any) -> Any:
2437 raise NotImplementedError()
2440@_operator_fn
2441def json_path_getitem_op(a: Any, b: Any) -> Any:
2442 raise NotImplementedError()
2445@_operator_fn
2446def bitwise_xor_op(a: Any, b: Any) -> Any:
2447 return a.bitwise_xor(b)
2450@_operator_fn
2451def bitwise_or_op(a: Any, b: Any) -> Any:
2452 return a.bitwise_or(b)
2455@_operator_fn
2456def bitwise_and_op(a: Any, b: Any) -> Any:
2457 return a.bitwise_and(b)
2460@_operator_fn
2461def bitwise_not_op(a: Any) -> Any:
2462 return a.bitwise_not()
2465@_operator_fn
2466def bitwise_lshift_op(a: Any, b: Any) -> Any:
2467 return a.bitwise_lshift(b)
2470@_operator_fn
2471def bitwise_rshift_op(a: Any, b: Any) -> Any:
2472 return a.bitwise_rshift(b)
2475def is_comparison(op: OperatorType) -> bool:
2476 return op in _comparison or isinstance(op, custom_op) and op.is_comparison
2479def is_commutative(op: OperatorType) -> bool:
2480 return op in _commutative
2483def is_ordering_modifier(op: OperatorType) -> bool:
2484 return op in (asc_op, desc_op, nulls_first_op, nulls_last_op)
2487def is_natural_self_precedent(op: OperatorType) -> bool:
2488 return (
2489 op in _natural_self_precedent
2490 or isinstance(op, custom_op)
2491 and op.natural_self_precedent
2492 )
2495_booleans = (inv, is_true, is_false, and_, or_)
2498def is_boolean(op: OperatorType) -> bool:
2499 return is_comparison(op) or op in _booleans
2502_mirror = {gt: lt, ge: le, lt: gt, le: ge}
2505def mirror(op: OperatorType) -> OperatorType:
2506 """rotate a comparison operator 180 degrees.
2508 Note this is not the same as negation.
2510 """
2511 return _mirror.get(op, op)
2514_associative = _commutative.union([concat_op, and_, or_]).difference([eq, ne])
2517def is_associative(op: OperatorType) -> bool:
2518 return op in _associative
2521def is_order_by_modifier(op: Optional[OperatorType]) -> bool:
2522 return op in _order_by_modifier
2525_order_by_modifier = {desc_op, asc_op, nulls_first_op, nulls_last_op}
2527_natural_self_precedent = _associative.union(
2528 [getitem, json_getitem_op, json_path_getitem_op]
2529)
2530"""Operators where if we have (a op b) op c, we don't want to
2531parenthesize (a op b).
2533"""
2536@_operator_fn
2537def _asbool(a: Any) -> Any:
2538 raise NotImplementedError()
2541class _OpLimit(IntEnum):
2542 _smallest = -100
2543 _largest = 100
2546_PRECEDENCE: Dict[OperatorType, int] = {
2547 from_: 15,
2548 function_as_comparison_op: 15,
2549 any_op: 15,
2550 all_op: 15,
2551 getitem: 15,
2552 json_getitem_op: 15,
2553 json_path_getitem_op: 15,
2554 mul: 8,
2555 truediv: 8,
2556 floordiv: 8,
2557 mod: 8,
2558 neg: 8,
2559 bitwise_not_op: 8,
2560 add: 7,
2561 sub: 7,
2562 bitwise_xor_op: 7,
2563 bitwise_or_op: 7,
2564 bitwise_and_op: 7,
2565 bitwise_lshift_op: 7,
2566 bitwise_rshift_op: 7,
2567 filter_op: 6,
2568 concat_op: 5,
2569 match_op: 5,
2570 not_match_op: 5,
2571 regexp_match_op: 5,
2572 not_regexp_match_op: 5,
2573 regexp_replace_op: 5,
2574 ilike_op: 5,
2575 not_ilike_op: 5,
2576 like_op: 5,
2577 not_like_op: 5,
2578 in_op: 5,
2579 not_in_op: 5,
2580 is_: 5,
2581 is_not: 5,
2582 eq: 5,
2583 ne: 5,
2584 is_distinct_from: 5,
2585 is_not_distinct_from: 5,
2586 gt: 5,
2587 lt: 5,
2588 ge: 5,
2589 le: 5,
2590 between_op: 5,
2591 not_between_op: 5,
2592 distinct_op: 5,
2593 inv: 5,
2594 is_true: 5,
2595 is_false: 5,
2596 and_: 3,
2597 or_: 2,
2598 comma_op: -1,
2599 desc_op: 3,
2600 asc_op: 3,
2601 collate: 4,
2602 as_: -1,
2603 exists: 0,
2604 _asbool: -10,
2605}
2608def is_precedent(
2609 operator: OperatorType, against: Optional[OperatorType]
2610) -> bool:
2611 if operator is against and is_natural_self_precedent(operator):
2612 return False
2613 elif against is None:
2614 return True
2615 else:
2616 return bool(
2617 _PRECEDENCE.get(
2618 operator, getattr(operator, "precedence", _OpLimit._smallest)
2619 )
2620 <= _PRECEDENCE.get(
2621 against, getattr(against, "precedence", _OpLimit._largest)
2622 )
2623 )