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 matmul as _uncast_matmul
29from operator import mod as _uncast_mod
30from operator import mul as _uncast_mul
31from operator import ne as _uncast_ne
32from operator import neg as _uncast_neg
33from operator import or_ as _uncast_or_
34from operator import pow as _uncast_pow
35from operator import rshift as _uncast_rshift
36from operator import sub as _uncast_sub
37from operator import truediv as _uncast_truediv
38import typing
39from typing import Any
40from typing import Callable
41from typing import cast
42from typing import Dict
43from typing import Generic
44from typing import Literal
45from typing import Optional
46from typing import overload
47from typing import Protocol
48from typing import Set
49from typing import Tuple
50from typing import Type
51from typing import TYPE_CHECKING
52from typing import TypeVar
53from typing import Union
55from .. import exc
56from .. import util
58if typing.TYPE_CHECKING:
59 from ._typing import ColumnExpressionArgument
60 from .cache_key import CacheConst
61 from .elements import ColumnElement
62 from .type_api import TypeEngine
64_T = TypeVar("_T", bound=Any)
65_FN = TypeVar("_FN", bound=Callable[..., Any])
68class OperatorType(Protocol):
69 """describe an op() function."""
71 __slots__ = ()
73 __name__: str
75 @overload
76 def __call__(
77 self,
78 left: ColumnExpressionArgument[Any],
79 right: Optional[Any] = None,
80 *other: Any,
81 **kwargs: Any,
82 ) -> ColumnElement[Any]: ...
84 @overload
85 def __call__(
86 self,
87 left: Operators,
88 right: Optional[Any] = None,
89 *other: Any,
90 **kwargs: Any,
91 ) -> Operators: ...
93 def __call__(
94 self,
95 left: Any,
96 right: Optional[Any] = None,
97 *other: Any,
98 **kwargs: Any,
99 ) -> Operators: ...
102add = cast(OperatorType, _uncast_add)
103and_ = cast(OperatorType, _uncast_and_)
104contains = cast(OperatorType, _uncast_contains)
105eq = cast(OperatorType, _uncast_eq)
106floordiv = cast(OperatorType, _uncast_floordiv)
107ge = cast(OperatorType, _uncast_ge)
108getitem = cast(OperatorType, _uncast_getitem)
109gt = cast(OperatorType, _uncast_gt)
110inv = cast(OperatorType, _uncast_inv)
111le = cast(OperatorType, _uncast_le)
112lshift = cast(OperatorType, _uncast_lshift)
113lt = cast(OperatorType, _uncast_lt)
114matmul = cast(OperatorType, _uncast_matmul)
115mod = cast(OperatorType, _uncast_mod)
116mul = cast(OperatorType, _uncast_mul)
117ne = cast(OperatorType, _uncast_ne)
118neg = cast(OperatorType, _uncast_neg)
119or_ = cast(OperatorType, _uncast_or_)
120pow_ = cast(OperatorType, _uncast_pow)
121rshift = cast(OperatorType, _uncast_rshift)
122sub = cast(OperatorType, _uncast_sub)
123truediv = cast(OperatorType, _uncast_truediv)
126class Operators:
127 """Base of comparison and logical operators.
129 Implements base methods
130 :meth:`~sqlalchemy.sql.operators.Operators.operate` and
131 :meth:`~sqlalchemy.sql.operators.Operators.reverse_operate`, as well as
132 :meth:`~sqlalchemy.sql.operators.Operators.__and__`,
133 :meth:`~sqlalchemy.sql.operators.Operators.__or__`,
134 :meth:`~sqlalchemy.sql.operators.Operators.__invert__`.
136 Usually is used via its most common subclass
137 :class:`.ColumnOperators`.
139 """
141 __slots__ = ()
143 def __and__(self, other: Any) -> Operators:
144 """Implement the ``&`` operator.
146 When used with SQL expressions, results in an
147 AND operation, equivalent to
148 :func:`_expression.and_`, that is::
150 a & b
152 is equivalent to::
154 from sqlalchemy import and_
156 and_(a, b)
158 Care should be taken when using ``&`` regarding
159 operator precedence; the ``&`` operator has the highest precedence.
160 The operands should be enclosed in parenthesis if they contain
161 further sub expressions::
163 (a == 2) & (b == 4)
165 """
166 return self.operate(and_, other)
168 def __or__(self, other: Any) -> Operators:
169 """Implement the ``|`` operator.
171 When used with SQL expressions, results in an
172 OR operation, equivalent to
173 :func:`_expression.or_`, that is::
175 a | b
177 is equivalent to::
179 from sqlalchemy import or_
181 or_(a, b)
183 Care should be taken when using ``|`` regarding
184 operator precedence; the ``|`` operator has the highest precedence.
185 The operands should be enclosed in parenthesis if they contain
186 further sub expressions::
188 (a == 2) | (b == 4)
190 """
191 return self.operate(or_, other)
193 def __invert__(self) -> Operators:
194 """Implement the ``~`` operator.
196 When used with SQL expressions, results in a
197 NOT operation, equivalent to
198 :func:`_expression.not_`, that is::
200 ~a
202 is equivalent to::
204 from sqlalchemy import not_
206 not_(a)
208 """
209 return self.operate(inv)
211 def op(
212 self,
213 opstring: str,
214 precedence: int = 0,
215 is_comparison: bool = False,
216 return_type: Optional[
217 Union[Type[TypeEngine[Any]], TypeEngine[Any]]
218 ] = None,
219 python_impl: Optional[Callable[..., Any]] = None,
220 ) -> Callable[[Any], Operators]:
221 """Produce a generic operator function.
223 e.g.::
225 somecolumn.op("*")(5)
227 produces::
229 somecolumn * 5
231 This function can also be used to make bitwise operators explicit. For
232 example::
234 somecolumn.op("&")(0xFF)
236 is a bitwise AND of the value in ``somecolumn``.
238 :param opstring: a string which will be output as the infix operator
239 between this element and the expression passed to the
240 generated function.
242 :param precedence: precedence which the database is expected to apply
243 to the operator in SQL expressions. This integer value acts as a hint
244 for the SQL compiler to know when explicit parenthesis should be
245 rendered around a particular operation. A lower number will cause the
246 expression to be parenthesized when applied against another operator
247 with higher precedence. The default value of ``0`` is lower than all
248 operators except for the comma (``,``) and ``AS`` operators. A value
249 of 100 will be higher or equal to all operators, and -100 will be
250 lower than or equal to all operators.
252 .. seealso::
254 :ref:`faq_sql_expression_op_parenthesis` - detailed description
255 of how the SQLAlchemy SQL compiler renders parenthesis
257 :param is_comparison: legacy; if True, the operator will be considered
258 as a "comparison" operator, that is which evaluates to a boolean
259 true/false value, like ``==``, ``>``, etc. This flag is provided
260 so that ORM relationships can establish that the operator is a
261 comparison operator when used in a custom join condition.
263 Using the ``is_comparison`` parameter is superseded by using the
264 :meth:`.Operators.bool_op` method instead; this more succinct
265 operator sets this parameter automatically, but also provides
266 correct :pep:`484` typing support as the returned object will
267 express a "boolean" datatype, i.e. ``BinaryExpression[bool]``.
269 :param return_type: a :class:`.TypeEngine` class or object that will
270 force the return type of an expression produced by this operator
271 to be of that type. By default, operators that specify
272 :paramref:`.Operators.op.is_comparison` will resolve to
273 :class:`.Boolean`, and those that do not will be of the same
274 type as the left-hand operand.
276 :param python_impl: an optional Python function that can evaluate
277 two Python values in the same way as this operator works when
278 run on the database server. Useful for in-Python SQL expression
279 evaluation functions, such as for ORM hybrid attributes, and the
280 ORM "evaluator" used to match objects in a session after a multi-row
281 update or delete.
283 e.g.::
285 >>> expr = column("x").op("+", python_impl=lambda a, b: a + b)("y")
287 The operator for the above expression will also work for non-SQL
288 left and right objects::
290 >>> expr.operator(5, 10)
291 15
293 .. versionadded:: 2.0
296 .. seealso::
298 :meth:`.Operators.bool_op`
300 :ref:`types_operators`
302 :ref:`relationship_custom_operator`
304 """
305 operator = custom_op(
306 opstring,
307 precedence,
308 is_comparison,
309 return_type,
310 python_impl=python_impl,
311 )
313 def against(other: Any) -> Operators:
314 return operator(self, other)
316 return against
318 def bool_op(
319 self,
320 opstring: str,
321 precedence: int = 0,
322 python_impl: Optional[Callable[..., Any]] = None,
323 ) -> Callable[[Any], Operators]:
324 """Return a custom boolean operator.
326 This method is shorthand for calling
327 :meth:`.Operators.op` and passing the
328 :paramref:`.Operators.op.is_comparison`
329 flag with True. A key advantage to using :meth:`.Operators.bool_op`
330 is that when using column constructs, the "boolean" nature of the
331 returned expression will be present for :pep:`484` purposes.
333 .. seealso::
335 :meth:`.Operators.op`
337 """
338 return self.op(
339 opstring,
340 precedence=precedence,
341 is_comparison=True,
342 python_impl=python_impl,
343 )
345 def operate(
346 self, op: OperatorType, *other: Any, **kwargs: Any
347 ) -> Operators:
348 r"""Operate on an argument.
350 This is the lowest level of operation, raises
351 :class:`NotImplementedError` by default.
353 Overriding this on a subclass can allow common
354 behavior to be applied to all operations.
355 For example, overriding :class:`.ColumnOperators`
356 to apply ``func.lower()`` to the left and right
357 side::
359 class MyComparator(ColumnOperators):
360 def operate(self, op, other, **kwargs):
361 return op(func.lower(self), func.lower(other), **kwargs)
363 :param op: Operator callable.
364 :param \*other: the 'other' side of the operation. Will
365 be a single scalar for most operations.
366 :param \**kwargs: modifiers. These may be passed by special
367 operators such as :meth:`ColumnOperators.contains`.
370 """
371 raise NotImplementedError(str(op))
373 __sa_operate__ = operate
375 def reverse_operate(
376 self, op: OperatorType, other: Any, **kwargs: Any
377 ) -> Operators:
378 """Reverse operate on an argument.
380 Usage is the same as :meth:`operate`.
382 """
383 raise NotImplementedError(str(op))
386class custom_op(OperatorType, Generic[_T]):
387 """Represent a 'custom' operator.
389 :class:`.custom_op` is normally instantiated when the
390 :meth:`.Operators.op` or :meth:`.Operators.bool_op` methods
391 are used to create a custom operator callable. The class can also be
392 used directly when programmatically constructing expressions. E.g.
393 to represent the "factorial" operation::
395 from sqlalchemy.sql import UnaryExpression
396 from sqlalchemy.sql import operators
397 from sqlalchemy import Numeric
399 unary = UnaryExpression(
400 table.c.somecolumn, modifier=operators.custom_op("!"), type_=Numeric
401 )
403 .. seealso::
405 :meth:`.Operators.op`
407 :meth:`.Operators.bool_op`
409 """ # noqa: E501
411 __name__ = "custom_op"
413 __slots__ = (
414 "opstring",
415 "precedence",
416 "is_comparison",
417 "natural_self_precedent",
418 "eager_grouping",
419 "return_type",
420 "python_impl",
421 )
423 def __init__(
424 self,
425 opstring: str,
426 precedence: int = 0,
427 is_comparison: bool = False,
428 return_type: Optional[
429 Union[Type[TypeEngine[_T]], TypeEngine[_T]]
430 ] = None,
431 natural_self_precedent: bool = False,
432 eager_grouping: bool = False,
433 python_impl: Optional[Callable[..., Any]] = None,
434 ):
435 self.opstring = opstring
436 self.precedence = precedence
437 self.is_comparison = is_comparison
438 self.natural_self_precedent = natural_self_precedent
439 self.eager_grouping = eager_grouping
440 self.return_type = (
441 return_type._to_instance(return_type) if return_type else None
442 )
443 self.python_impl = python_impl
445 def __eq__(self, other: Any) -> bool:
446 return (
447 isinstance(other, custom_op)
448 and other._hash_key() == self._hash_key()
449 )
451 def __hash__(self) -> int:
452 return hash(self._hash_key())
454 def _hash_key(self) -> Union[CacheConst, Tuple[Any, ...]]:
455 return (
456 self.__class__,
457 self.opstring,
458 self.precedence,
459 self.is_comparison,
460 self.natural_self_precedent,
461 self.eager_grouping,
462 self.return_type._static_cache_key if self.return_type else None,
463 )
465 @overload
466 def __call__(
467 self,
468 left: ColumnExpressionArgument[Any],
469 right: Optional[Any] = None,
470 *other: Any,
471 **kwargs: Any,
472 ) -> ColumnElement[Any]: ...
474 @overload
475 def __call__(
476 self,
477 left: Operators,
478 right: Optional[Any] = None,
479 *other: Any,
480 **kwargs: Any,
481 ) -> Operators: ...
483 def __call__(
484 self,
485 left: Any,
486 right: Optional[Any] = None,
487 *other: Any,
488 **kwargs: Any,
489 ) -> Operators:
490 if hasattr(left, "__sa_operate__"):
491 return left.operate(self, right, *other, **kwargs) # type: ignore
492 elif self.python_impl:
493 return self.python_impl(left, right, *other, **kwargs) # type: ignore # noqa: E501
494 else:
495 raise exc.InvalidRequestError(
496 f"Custom operator {self.opstring!r} can't be used with "
497 "plain Python objects unless it includes the "
498 "'python_impl' parameter."
499 )
502class OrderingOperators(Operators):
503 """defines ORDER BY operators, which can operate as single expressions
504 or comma-separated lists
506 """
508 __slots__ = ()
510 if typing.TYPE_CHECKING:
512 def operate(
513 self, op: OperatorType, *other: Any, **kwargs: Any
514 ) -> OrderingOperators: ...
516 def reverse_operate(
517 self, op: OperatorType, other: Any, **kwargs: Any
518 ) -> OrderingOperators: ...
520 def desc(self) -> OrderingOperators:
521 """Produce a :func:`_expression.desc` clause against the
522 parent object."""
523 return self.operate(desc_op)
525 def asc(self) -> OrderingOperators:
526 """Produce a :func:`_expression.asc` clause against the
527 parent object."""
528 return self.operate(asc_op)
530 def nulls_first(self) -> OrderingOperators:
531 """Produce a :func:`_expression.nulls_first` clause against the
532 parent object.
534 .. versionchanged:: 1.4 The ``nulls_first()`` operator is
535 renamed from ``nullsfirst()`` in previous releases.
536 The previous name remains available for backwards compatibility.
537 """
538 return self.operate(nulls_first_op)
540 # deprecated 1.4; see #5435
541 if TYPE_CHECKING:
543 def nullsfirst(self) -> OrderingOperators: ...
545 else:
546 nullsfirst = nulls_first
548 def nulls_last(self) -> OrderingOperators:
549 """Produce a :func:`_expression.nulls_last` clause against the
550 parent object.
552 .. versionchanged:: 1.4 The ``nulls_last()`` operator is
553 renamed from ``nullslast()`` in previous releases.
554 The previous name remains available for backwards compatibility.
555 """
556 return self.operate(nulls_last_op)
558 # deprecated 1.4; see #5429
559 if TYPE_CHECKING:
561 def nullslast(self) -> OrderingOperators: ...
563 else:
564 nullslast = nulls_last
567class ColumnOperators(OrderingOperators):
568 """Defines boolean, comparison, and other operators for
569 :class:`_expression.ColumnElement` expressions.
571 By default, all methods call down to
572 :meth:`.operate` or :meth:`.reverse_operate`,
573 passing in the appropriate operator function from the
574 Python builtin ``operator`` module or
575 a SQLAlchemy-specific operator function from
576 :mod:`sqlalchemy.expression.operators`. For example
577 the ``__eq__`` function::
579 def __eq__(self, other):
580 return self.operate(operators.eq, other)
582 Where ``operators.eq`` is essentially::
584 def eq(a, b):
585 return a == b
587 The core column expression unit :class:`_expression.ColumnElement`
588 overrides :meth:`.Operators.operate` and others
589 to return further :class:`_expression.ColumnElement` constructs,
590 so that the ``==`` operation above is replaced by a clause
591 construct.
593 .. seealso::
595 :ref:`types_operators`
597 :attr:`.TypeEngine.comparator_factory`
599 :class:`.ColumnOperators`
601 :class:`.PropComparator`
603 """
605 __slots__ = ()
607 timetuple: Literal[None] = None
608 """Hack, allows datetime objects to be compared on the LHS."""
610 if typing.TYPE_CHECKING:
612 def operate(
613 self, op: OperatorType, *other: Any, **kwargs: Any
614 ) -> ColumnOperators: ...
616 def reverse_operate(
617 self, op: OperatorType, other: Any, **kwargs: Any
618 ) -> ColumnOperators: ...
620 def __lt__(self, other: Any) -> ColumnOperators:
621 """Implement the ``<`` operator.
623 In a column context, produces the clause ``a < b``.
625 """
626 return self.operate(lt, other)
628 def __le__(self, other: Any) -> ColumnOperators:
629 """Implement the ``<=`` operator.
631 In a column context, produces the clause ``a <= b``.
633 """
634 return self.operate(le, other)
636 # ColumnOperators defines an __eq__ so it must explicitly declare also
637 # an hash or it's set to None by python:
638 # https://docs.python.org/3/reference/datamodel.html#object.__hash__
639 if TYPE_CHECKING:
641 def __hash__(self) -> int: ...
643 else:
644 __hash__ = Operators.__hash__
646 def __eq__(self, other: Any) -> ColumnOperators: # type: ignore[override]
647 """Implement the ``==`` operator.
649 In a column context, produces the clause ``a = b``.
650 If the target is ``None``, produces ``a IS NULL``.
652 """
653 return self.operate(eq, other)
655 def __ne__(self, other: Any) -> ColumnOperators: # type: ignore[override]
656 """Implement the ``!=`` operator.
658 In a column context, produces the clause ``a != b``.
659 If the target is ``None``, produces ``a IS NOT NULL``.
661 """
662 return self.operate(ne, other)
664 def is_distinct_from(self, other: Any) -> ColumnOperators:
665 """Implement the ``IS DISTINCT FROM`` operator.
667 Renders "a IS DISTINCT FROM b" on most platforms;
668 on some such as SQLite may render "a IS NOT b".
670 """
671 return self.operate(is_distinct_from, other)
673 def is_not_distinct_from(self, other: Any) -> ColumnOperators:
674 """Implement the ``IS NOT DISTINCT FROM`` operator.
676 Renders "a IS NOT DISTINCT FROM b" on most platforms;
677 on some such as SQLite may render "a IS b".
679 .. versionchanged:: 1.4 The ``is_not_distinct_from()`` operator is
680 renamed from ``isnot_distinct_from()`` in previous releases.
681 The previous name remains available for backwards compatibility.
683 """
684 return self.operate(is_not_distinct_from, other)
686 # deprecated 1.4; see #5435
687 if TYPE_CHECKING:
689 def isnot_distinct_from(self, other: Any) -> ColumnOperators: ...
691 else:
692 isnot_distinct_from = is_not_distinct_from
694 def __gt__(self, other: Any) -> ColumnOperators:
695 """Implement the ``>`` operator.
697 In a column context, produces the clause ``a > b``.
699 """
700 return self.operate(gt, other)
702 def __ge__(self, other: Any) -> ColumnOperators:
703 """Implement the ``>=`` operator.
705 In a column context, produces the clause ``a >= b``.
707 """
708 return self.operate(ge, other)
710 def __neg__(self) -> ColumnOperators:
711 """Implement the ``-`` operator.
713 In a column context, produces the clause ``-a``.
715 """
716 return self.operate(neg)
718 def __contains__(self, other: Any) -> ColumnOperators:
719 return self.operate(contains, other)
721 def __getitem__(self, index: Any) -> ColumnOperators:
722 """Implement the [] operator.
724 This can be used by some database-specific types
725 such as PostgreSQL ARRAY and HSTORE.
727 """
728 return self.operate(getitem, index)
730 def __lshift__(self, other: Any) -> ColumnOperators:
731 """Implement the ``<<`` operator.
733 Not used by SQLAlchemy core, this is provided
734 for custom operator systems which want to use
735 << as an extension point.
736 """
737 return self.operate(lshift, other)
739 def __rlshift__(self, other: Any) -> ColumnOperators:
740 """Implement the ``<<`` operator in reverse.
742 Not used by SQLAlchemy core, this is provided
743 for custom operator systems which want to use
744 << as an extension point.
745 """
746 return self.reverse_operate(lshift, other)
748 def __rshift__(self, other: Any) -> ColumnOperators:
749 """Implement the ``>>`` operator.
751 Not used by SQLAlchemy core, this is provided
752 for custom operator systems which want to use
753 >> as an extension point.
754 """
755 return self.operate(rshift, other)
757 def __rrshift__(self, other: Any) -> ColumnOperators:
758 """Implement the ``>>`` operator in reverse.
760 Not used by SQLAlchemy core, this is provided
761 for custom operator systems which want to use
762 >> as an extension point.
763 """
764 return self.reverse_operate(rshift, other)
766 def __matmul__(self, other: Any) -> ColumnOperators:
767 """Implement the ``@`` operator.
769 Not used by SQLAlchemy core, this is provided
770 for custom operator systems which want to use
771 @ as an extension point.
772 """
773 return self.operate(matmul, other)
775 def __rmatmul__(self, other: Any) -> ColumnOperators:
776 """Implement the ``@`` operator in reverse.
778 Not used by SQLAlchemy core, this is provided
779 for custom operator systems which want to use
780 @ as an extension point.
781 """
782 return self.reverse_operate(matmul, other)
784 def concat(self, other: Any) -> ColumnOperators:
785 """Implement the 'concat' operator.
787 In a column context, produces the clause ``a || b``,
788 or uses the ``concat()`` operator on MySQL.
790 """
791 return self.operate(concat_op, other)
793 def _rconcat(self, other: Any) -> ColumnOperators:
794 """Implement an 'rconcat' operator.
796 this is for internal use at the moment
798 .. versionadded:: 1.4.40
800 """
801 return self.reverse_operate(concat_op, other)
803 def like(
804 self, other: Any, escape: Optional[str] = None
805 ) -> ColumnOperators:
806 r"""Implement the ``like`` operator.
808 In a column context, produces the expression:
810 .. sourcecode:: sql
812 a LIKE other
814 E.g.::
816 stmt = select(sometable).where(sometable.c.column.like("%foobar%"))
818 :param other: expression to be compared
819 :param escape: optional escape character, renders the ``ESCAPE``
820 keyword, e.g.::
822 somecolumn.like("foo/%bar", escape="/")
824 .. seealso::
826 :meth:`.ColumnOperators.ilike`
828 """
829 return self.operate(like_op, other, escape=escape)
831 def ilike(
832 self, other: Any, escape: Optional[str] = None
833 ) -> ColumnOperators:
834 r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE.
836 In a column context, produces an expression either of the form:
838 .. sourcecode:: sql
840 lower(a) LIKE lower(other)
842 Or on backends that support the ILIKE operator:
844 .. sourcecode:: sql
846 a ILIKE other
848 E.g.::
850 stmt = select(sometable).where(sometable.c.column.ilike("%foobar%"))
852 :param other: expression to be compared
853 :param escape: optional escape character, renders the ``ESCAPE``
854 keyword, e.g.::
856 somecolumn.ilike("foo/%bar", escape="/")
858 .. seealso::
860 :meth:`.ColumnOperators.like`
862 """ # noqa: E501
863 return self.operate(ilike_op, other, escape=escape)
865 def bitwise_xor(self, other: Any) -> ColumnOperators:
866 """Produce a bitwise XOR operation, typically via the ``^``
867 operator, or ``#`` for PostgreSQL.
869 .. versionadded:: 2.0.2
871 .. seealso::
873 :ref:`operators_bitwise`
875 """
877 return self.operate(bitwise_xor_op, other)
879 def bitwise_or(self, other: Any) -> ColumnOperators:
880 """Produce a bitwise OR operation, typically via the ``|``
881 operator.
883 .. versionadded:: 2.0.2
885 .. seealso::
887 :ref:`operators_bitwise`
889 """
891 return self.operate(bitwise_or_op, other)
893 def bitwise_and(self, other: Any) -> ColumnOperators:
894 """Produce a bitwise AND operation, typically via the ``&``
895 operator.
897 .. versionadded:: 2.0.2
899 .. seealso::
901 :ref:`operators_bitwise`
903 """
905 return self.operate(bitwise_and_op, other)
907 def bitwise_not(self) -> ColumnOperators:
908 """Produce a bitwise NOT operation, typically via the ``~``
909 operator.
911 .. versionadded:: 2.0.2
913 .. seealso::
915 :ref:`operators_bitwise`
917 """
919 return self.operate(bitwise_not_op)
921 def bitwise_lshift(self, other: Any) -> ColumnOperators:
922 """Produce a bitwise LSHIFT operation, typically via the ``<<``
923 operator.
925 .. versionadded:: 2.0.2
927 .. seealso::
929 :ref:`operators_bitwise`
931 """
933 return self.operate(bitwise_lshift_op, other)
935 def bitwise_rshift(self, other: Any) -> ColumnOperators:
936 """Produce a bitwise RSHIFT operation, typically via the ``>>``
937 operator.
939 .. versionadded:: 2.0.2
941 .. seealso::
943 :ref:`operators_bitwise`
945 """
947 return self.operate(bitwise_rshift_op, other)
949 def in_(self, other: Any) -> ColumnOperators:
950 """Implement the ``in`` operator.
952 In a column context, produces the clause ``column IN <other>``.
954 The given parameter ``other`` may be:
956 * A list of literal values,
957 e.g.::
959 stmt.where(column.in_([1, 2, 3]))
961 In this calling form, the list of items is converted to a set of
962 bound parameters the same length as the list given:
964 .. sourcecode:: sql
966 WHERE COL IN (?, ?, ?)
968 * A list of tuples may be provided if the comparison is against a
969 :func:`.tuple_` containing multiple expressions::
971 from sqlalchemy import tuple_
973 stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
975 * An empty list,
976 e.g.::
978 stmt.where(column.in_([]))
980 In this calling form, the expression renders an "empty set"
981 expression. These expressions are tailored to individual backends
982 and are generally trying to get an empty SELECT statement as a
983 subquery. Such as on SQLite, the expression is:
985 .. sourcecode:: sql
987 WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
989 .. versionchanged:: 1.4 empty IN expressions now use an
990 execution-time generated SELECT subquery in all cases.
992 * A bound parameter, e.g. :func:`.bindparam`, may be used if it
993 includes the :paramref:`.bindparam.expanding` flag::
995 stmt.where(column.in_(bindparam("value", expanding=True)))
997 In this calling form, the expression renders a special non-SQL
998 placeholder expression that looks like:
1000 .. sourcecode:: sql
1002 WHERE COL IN ([EXPANDING_value])
1004 This placeholder expression is intercepted at statement execution
1005 time to be converted into the variable number of bound parameter
1006 form illustrated earlier. If the statement were executed as::
1008 connection.execute(stmt, {"value": [1, 2, 3]})
1010 The database would be passed a bound parameter for each value:
1012 .. sourcecode:: sql
1014 WHERE COL IN (?, ?, ?)
1016 If an empty list is passed, a special "empty list" expression,
1017 which is specific to the database in use, is rendered. On
1018 SQLite this would be:
1020 .. sourcecode:: sql
1022 WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
1024 * a :func:`_expression.select` construct, which is usually a
1025 correlated scalar select::
1027 stmt.where(
1028 column.in_(select(othertable.c.y).where(table.c.x == othertable.c.x))
1029 )
1031 In this calling form, :meth:`.ColumnOperators.in_` renders as given:
1033 .. sourcecode:: sql
1035 WHERE COL IN (SELECT othertable.y
1036 FROM othertable WHERE othertable.x = table.x)
1038 :param other: a list of literals, a :func:`_expression.select`
1039 construct, or a :func:`.bindparam` construct that includes the
1040 :paramref:`.bindparam.expanding` flag set to True.
1042 """ # noqa: E501
1043 return self.operate(in_op, other)
1045 def not_in(self, other: Any) -> ColumnOperators:
1046 """implement the ``NOT IN`` operator.
1048 This is equivalent to using negation with
1049 :meth:`.ColumnOperators.in_`, i.e. ``~x.in_(y)``.
1051 In the case that ``other`` is an empty sequence, the compiler
1052 produces an "empty not in" expression. This defaults to the
1053 expression "1 = 1" to produce true in all cases. The
1054 :paramref:`_sa.create_engine.empty_in_strategy` may be used to
1055 alter this behavior.
1057 .. versionchanged:: 1.4 The ``not_in()`` operator is renamed from
1058 ``notin_()`` in previous releases. The previous name remains
1059 available for backwards compatibility.
1061 .. seealso::
1063 :meth:`.ColumnOperators.in_`
1065 """
1066 return self.operate(not_in_op, other)
1068 # deprecated 1.4; see #5429
1069 if TYPE_CHECKING:
1071 def notin_(self, other: Any) -> ColumnOperators: ...
1073 else:
1074 notin_ = not_in
1076 def not_like(
1077 self, other: Any, escape: Optional[str] = None
1078 ) -> ColumnOperators:
1079 """implement the ``NOT LIKE`` operator.
1081 This is equivalent to using negation with
1082 :meth:`.ColumnOperators.like`, i.e. ``~x.like(y)``.
1084 .. versionchanged:: 1.4 The ``not_like()`` operator is renamed from
1085 ``notlike()`` in previous releases. The previous name remains
1086 available for backwards compatibility.
1088 .. seealso::
1090 :meth:`.ColumnOperators.like`
1092 """
1093 return self.operate(not_like_op, other, escape=escape)
1095 # deprecated 1.4; see #5435
1096 if TYPE_CHECKING:
1098 def notlike(
1099 self, other: Any, escape: Optional[str] = None
1100 ) -> ColumnOperators: ...
1102 else:
1103 notlike = not_like
1105 def not_ilike(
1106 self, other: Any, escape: Optional[str] = None
1107 ) -> ColumnOperators:
1108 """implement the ``NOT ILIKE`` operator.
1110 This is equivalent to using negation with
1111 :meth:`.ColumnOperators.ilike`, i.e. ``~x.ilike(y)``.
1113 .. versionchanged:: 1.4 The ``not_ilike()`` operator is renamed from
1114 ``notilike()`` in previous releases. The previous name remains
1115 available for backwards compatibility.
1117 .. seealso::
1119 :meth:`.ColumnOperators.ilike`
1121 """
1122 return self.operate(not_ilike_op, other, escape=escape)
1124 # deprecated 1.4; see #5435
1125 if TYPE_CHECKING:
1127 def notilike(
1128 self, other: Any, escape: Optional[str] = None
1129 ) -> ColumnOperators: ...
1131 else:
1132 notilike = not_ilike
1134 def is_(self, other: Any) -> ColumnOperators:
1135 """Implement the ``IS`` operator.
1137 Normally, ``IS`` is generated automatically when comparing to a
1138 value of ``None``, which resolves to ``NULL``. However, explicit
1139 usage of ``IS`` may be desirable if comparing to boolean values
1140 on certain platforms.
1142 .. seealso:: :meth:`.ColumnOperators.is_not`
1144 """
1145 return self.operate(is_, other)
1147 def is_not(self, other: Any) -> ColumnOperators:
1148 """Implement the ``IS NOT`` operator.
1150 Normally, ``IS NOT`` is generated automatically when comparing to a
1151 value of ``None``, which resolves to ``NULL``. However, explicit
1152 usage of ``IS NOT`` may be desirable if comparing to boolean values
1153 on certain platforms.
1155 .. versionchanged:: 1.4 The ``is_not()`` operator is renamed from
1156 ``isnot()`` in previous releases. The previous name remains
1157 available for backwards compatibility.
1159 .. seealso:: :meth:`.ColumnOperators.is_`
1161 """
1162 return self.operate(is_not, other)
1164 # deprecated 1.4; see #5429
1165 if TYPE_CHECKING:
1167 def isnot(self, other: Any) -> ColumnOperators: ...
1169 else:
1170 isnot = is_not
1172 def startswith(
1173 self,
1174 other: Any,
1175 escape: Optional[str] = None,
1176 autoescape: bool = False,
1177 ) -> ColumnOperators:
1178 r"""Implement the ``startswith`` operator.
1180 Produces a LIKE expression that tests against a match for the start
1181 of a string value:
1183 .. sourcecode:: sql
1185 column LIKE <other> || '%'
1187 E.g.::
1189 stmt = select(sometable).where(sometable.c.column.startswith("foobar"))
1191 Since the operator uses ``LIKE``, wildcard characters
1192 ``"%"`` and ``"_"`` that are present inside the <other> expression
1193 will behave like wildcards as well. For literal string
1194 values, the :paramref:`.ColumnOperators.startswith.autoescape` flag
1195 may be set to ``True`` to apply escaping to occurrences of these
1196 characters within the string value so that they match as themselves
1197 and not as wildcard characters. Alternatively, the
1198 :paramref:`.ColumnOperators.startswith.escape` parameter will establish
1199 a given character as an escape character which can be of use when
1200 the target expression is not a literal string.
1202 :param other: expression to be compared. This is usually a plain
1203 string value, but can also be an arbitrary SQL expression. LIKE
1204 wildcard characters ``%`` and ``_`` are not escaped by default unless
1205 the :paramref:`.ColumnOperators.startswith.autoescape` flag is
1206 set to True.
1208 :param autoescape: boolean; when True, establishes an escape character
1209 within the LIKE expression, then applies it to all occurrences of
1210 ``"%"``, ``"_"`` and the escape character itself within the
1211 comparison value, which is assumed to be a literal string and not a
1212 SQL expression.
1214 An expression such as::
1216 somecolumn.startswith("foo%bar", autoescape=True)
1218 Will render as:
1220 .. sourcecode:: sql
1222 somecolumn LIKE :param || '%' ESCAPE '/'
1224 With the value of ``:param`` as ``"foo/%bar"``.
1226 :param escape: a character which when given will render with the
1227 ``ESCAPE`` keyword to establish that character as the escape
1228 character. This character can then be placed preceding occurrences
1229 of ``%`` and ``_`` to allow them to act as themselves and not
1230 wildcard characters.
1232 An expression such as::
1234 somecolumn.startswith("foo/%bar", escape="^")
1236 Will render as:
1238 .. sourcecode:: sql
1240 somecolumn LIKE :param || '%' ESCAPE '^'
1242 The parameter may also be combined with
1243 :paramref:`.ColumnOperators.startswith.autoescape`::
1245 somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
1247 Where above, the given literal parameter will be converted to
1248 ``"foo^%bar^^bat"`` before being passed to the database.
1250 .. seealso::
1252 :meth:`.ColumnOperators.endswith`
1254 :meth:`.ColumnOperators.contains`
1256 :meth:`.ColumnOperators.like`
1258 """ # noqa: E501
1259 return self.operate(
1260 startswith_op, other, escape=escape, autoescape=autoescape
1261 )
1263 def istartswith(
1264 self,
1265 other: Any,
1266 escape: Optional[str] = None,
1267 autoescape: bool = False,
1268 ) -> ColumnOperators:
1269 r"""Implement the ``istartswith`` operator, e.g. case insensitive
1270 version of :meth:`.ColumnOperators.startswith`.
1272 Produces a LIKE expression that tests against an insensitive
1273 match for the start of a string value:
1275 .. sourcecode:: sql
1277 lower(column) LIKE lower(<other>) || '%'
1279 E.g.::
1281 stmt = select(sometable).where(sometable.c.column.istartswith("foobar"))
1283 Since the operator uses ``LIKE``, wildcard characters
1284 ``"%"`` and ``"_"`` that are present inside the <other> expression
1285 will behave like wildcards as well. For literal string
1286 values, the :paramref:`.ColumnOperators.istartswith.autoescape` flag
1287 may be set to ``True`` to apply escaping to occurrences of these
1288 characters within the string value so that they match as themselves
1289 and not as wildcard characters. Alternatively, the
1290 :paramref:`.ColumnOperators.istartswith.escape` parameter will
1291 establish a given character as an escape character which can be of
1292 use when the target expression is not a literal string.
1294 :param other: expression to be compared. This is usually a plain
1295 string value, but can also be an arbitrary SQL expression. LIKE
1296 wildcard characters ``%`` and ``_`` are not escaped by default unless
1297 the :paramref:`.ColumnOperators.istartswith.autoescape` flag is
1298 set to True.
1300 :param autoescape: boolean; when True, establishes an escape character
1301 within the LIKE expression, then applies it to all occurrences of
1302 ``"%"``, ``"_"`` and the escape character itself within the
1303 comparison value, which is assumed to be a literal string and not a
1304 SQL expression.
1306 An expression such as::
1308 somecolumn.istartswith("foo%bar", autoescape=True)
1310 Will render as:
1312 .. sourcecode:: sql
1314 lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/'
1316 With the value of ``:param`` as ``"foo/%bar"``.
1318 :param escape: a character which when given will render with the
1319 ``ESCAPE`` keyword to establish that character as the escape
1320 character. This character can then be placed preceding occurrences
1321 of ``%`` and ``_`` to allow them to act as themselves and not
1322 wildcard characters.
1324 An expression such as::
1326 somecolumn.istartswith("foo/%bar", escape="^")
1328 Will render as:
1330 .. sourcecode:: sql
1332 lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^'
1334 The parameter may also be combined with
1335 :paramref:`.ColumnOperators.istartswith.autoescape`::
1337 somecolumn.istartswith("foo%bar^bat", escape="^", autoescape=True)
1339 Where above, the given literal parameter will be converted to
1340 ``"foo^%bar^^bat"`` before being passed to the database.
1342 .. seealso::
1344 :meth:`.ColumnOperators.startswith`
1345 """ # noqa: E501
1346 return self.operate(
1347 istartswith_op, other, escape=escape, autoescape=autoescape
1348 )
1350 def endswith(
1351 self,
1352 other: Any,
1353 escape: Optional[str] = None,
1354 autoescape: bool = False,
1355 ) -> ColumnOperators:
1356 r"""Implement the 'endswith' operator.
1358 Produces a LIKE expression that tests against a match for the end
1359 of a string value:
1361 .. sourcecode:: sql
1363 column LIKE '%' || <other>
1365 E.g.::
1367 stmt = select(sometable).where(sometable.c.column.endswith("foobar"))
1369 Since the operator uses ``LIKE``, wildcard characters
1370 ``"%"`` and ``"_"`` that are present inside the <other> expression
1371 will behave like wildcards as well. For literal string
1372 values, the :paramref:`.ColumnOperators.endswith.autoescape` flag
1373 may be set to ``True`` to apply escaping to occurrences of these
1374 characters within the string value so that they match as themselves
1375 and not as wildcard characters. Alternatively, the
1376 :paramref:`.ColumnOperators.endswith.escape` parameter will establish
1377 a given character as an escape character which can be of use when
1378 the target expression is not a literal string.
1380 :param other: expression to be compared. This is usually a plain
1381 string value, but can also be an arbitrary SQL expression. LIKE
1382 wildcard characters ``%`` and ``_`` are not escaped by default unless
1383 the :paramref:`.ColumnOperators.endswith.autoescape` flag is
1384 set to True.
1386 :param autoescape: boolean; when True, establishes an escape character
1387 within the LIKE expression, then applies it to all occurrences of
1388 ``"%"``, ``"_"`` and the escape character itself within the
1389 comparison value, which is assumed to be a literal string and not a
1390 SQL expression.
1392 An expression such as::
1394 somecolumn.endswith("foo%bar", autoescape=True)
1396 Will render as:
1398 .. sourcecode:: sql
1400 somecolumn LIKE '%' || :param ESCAPE '/'
1402 With the value of ``:param`` as ``"foo/%bar"``.
1404 :param escape: a character which when given will render with the
1405 ``ESCAPE`` keyword to establish that character as the escape
1406 character. This character can then be placed preceding occurrences
1407 of ``%`` and ``_`` to allow them to act as themselves and not
1408 wildcard characters.
1410 An expression such as::
1412 somecolumn.endswith("foo/%bar", escape="^")
1414 Will render as:
1416 .. sourcecode:: sql
1418 somecolumn LIKE '%' || :param ESCAPE '^'
1420 The parameter may also be combined with
1421 :paramref:`.ColumnOperators.endswith.autoescape`::
1423 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
1425 Where above, the given literal parameter will be converted to
1426 ``"foo^%bar^^bat"`` before being passed to the database.
1428 .. seealso::
1430 :meth:`.ColumnOperators.startswith`
1432 :meth:`.ColumnOperators.contains`
1434 :meth:`.ColumnOperators.like`
1436 """ # noqa: E501
1437 return self.operate(
1438 endswith_op, other, escape=escape, autoescape=autoescape
1439 )
1441 def iendswith(
1442 self,
1443 other: Any,
1444 escape: Optional[str] = None,
1445 autoescape: bool = False,
1446 ) -> ColumnOperators:
1447 r"""Implement the ``iendswith`` operator, e.g. case insensitive
1448 version of :meth:`.ColumnOperators.endswith`.
1450 Produces a LIKE expression that tests against an insensitive match
1451 for the end of a string value:
1453 .. sourcecode:: sql
1455 lower(column) LIKE '%' || lower(<other>)
1457 E.g.::
1459 stmt = select(sometable).where(sometable.c.column.iendswith("foobar"))
1461 Since the operator uses ``LIKE``, wildcard characters
1462 ``"%"`` and ``"_"`` that are present inside the <other> expression
1463 will behave like wildcards as well. For literal string
1464 values, the :paramref:`.ColumnOperators.iendswith.autoescape` flag
1465 may be set to ``True`` to apply escaping to occurrences of these
1466 characters within the string value so that they match as themselves
1467 and not as wildcard characters. Alternatively, the
1468 :paramref:`.ColumnOperators.iendswith.escape` parameter will establish
1469 a given character as an escape character which can be of use when
1470 the target expression is not a literal string.
1472 :param other: expression to be compared. This is usually a plain
1473 string value, but can also be an arbitrary SQL expression. LIKE
1474 wildcard characters ``%`` and ``_`` are not escaped by default unless
1475 the :paramref:`.ColumnOperators.iendswith.autoescape` flag is
1476 set to True.
1478 :param autoescape: boolean; when True, establishes an escape character
1479 within the LIKE expression, then applies it to all occurrences of
1480 ``"%"``, ``"_"`` and the escape character itself within the
1481 comparison value, which is assumed to be a literal string and not a
1482 SQL expression.
1484 An expression such as::
1486 somecolumn.iendswith("foo%bar", autoescape=True)
1488 Will render as:
1490 .. sourcecode:: sql
1492 lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '/'
1494 With the value of ``:param`` as ``"foo/%bar"``.
1496 :param escape: a character which when given will render with the
1497 ``ESCAPE`` keyword to establish that character as the escape
1498 character. This character can then be placed preceding occurrences
1499 of ``%`` and ``_`` to allow them to act as themselves and not
1500 wildcard characters.
1502 An expression such as::
1504 somecolumn.iendswith("foo/%bar", escape="^")
1506 Will render as:
1508 .. sourcecode:: sql
1510 lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^'
1512 The parameter may also be combined with
1513 :paramref:`.ColumnOperators.iendswith.autoescape`::
1515 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
1517 Where above, the given literal parameter will be converted to
1518 ``"foo^%bar^^bat"`` before being passed to the database.
1520 .. seealso::
1522 :meth:`.ColumnOperators.endswith`
1523 """ # noqa: E501
1524 return self.operate(
1525 iendswith_op, other, escape=escape, autoescape=autoescape
1526 )
1528 def contains(self, other: Any, **kw: Any) -> ColumnOperators:
1529 r"""Implement the 'contains' operator.
1531 Produces a LIKE expression that tests against a match for the middle
1532 of a string value:
1534 .. sourcecode:: sql
1536 column LIKE '%' || <other> || '%'
1538 E.g.::
1540 stmt = select(sometable).where(sometable.c.column.contains("foobar"))
1542 Since the operator uses ``LIKE``, wildcard characters
1543 ``"%"`` and ``"_"`` that are present inside the <other> expression
1544 will behave like wildcards as well. For literal string
1545 values, the :paramref:`.ColumnOperators.contains.autoescape` flag
1546 may be set to ``True`` to apply escaping to occurrences of these
1547 characters within the string value so that they match as themselves
1548 and not as wildcard characters. Alternatively, the
1549 :paramref:`.ColumnOperators.contains.escape` parameter will establish
1550 a given character as an escape character which can be of use when
1551 the target expression is not a literal string.
1553 :param other: expression to be compared. This is usually a plain
1554 string value, but can also be an arbitrary SQL expression. LIKE
1555 wildcard characters ``%`` and ``_`` are not escaped by default unless
1556 the :paramref:`.ColumnOperators.contains.autoescape` flag is
1557 set to True.
1559 :param autoescape: boolean; when True, establishes an escape character
1560 within the LIKE expression, then applies it to all occurrences of
1561 ``"%"``, ``"_"`` and the escape character itself within the
1562 comparison value, which is assumed to be a literal string and not a
1563 SQL expression.
1565 An expression such as::
1567 somecolumn.contains("foo%bar", autoescape=True)
1569 Will render as:
1571 .. sourcecode:: sql
1573 somecolumn LIKE '%' || :param || '%' ESCAPE '/'
1575 With the value of ``:param`` as ``"foo/%bar"``.
1577 :param escape: a character which when given will render with the
1578 ``ESCAPE`` keyword to establish that character as the escape
1579 character. This character can then be placed preceding occurrences
1580 of ``%`` and ``_`` to allow them to act as themselves and not
1581 wildcard characters.
1583 An expression such as::
1585 somecolumn.contains("foo/%bar", escape="^")
1587 Will render as:
1589 .. sourcecode:: sql
1591 somecolumn LIKE '%' || :param || '%' ESCAPE '^'
1593 The parameter may also be combined with
1594 :paramref:`.ColumnOperators.contains.autoescape`::
1596 somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
1598 Where above, the given literal parameter will be converted to
1599 ``"foo^%bar^^bat"`` before being passed to the database.
1601 .. seealso::
1603 :meth:`.ColumnOperators.startswith`
1605 :meth:`.ColumnOperators.endswith`
1607 :meth:`.ColumnOperators.like`
1610 """ # noqa: E501
1611 return self.operate(contains_op, other, **kw)
1613 def icontains(self, other: Any, **kw: Any) -> ColumnOperators:
1614 r"""Implement the ``icontains`` operator, e.g. case insensitive
1615 version of :meth:`.ColumnOperators.contains`.
1617 Produces a LIKE expression that tests against an insensitive match
1618 for the middle of a string value:
1620 .. sourcecode:: sql
1622 lower(column) LIKE '%' || lower(<other>) || '%'
1624 E.g.::
1626 stmt = select(sometable).where(sometable.c.column.icontains("foobar"))
1628 Since the operator uses ``LIKE``, wildcard characters
1629 ``"%"`` and ``"_"`` that are present inside the <other> expression
1630 will behave like wildcards as well. For literal string
1631 values, the :paramref:`.ColumnOperators.icontains.autoescape` flag
1632 may be set to ``True`` to apply escaping to occurrences of these
1633 characters within the string value so that they match as themselves
1634 and not as wildcard characters. Alternatively, the
1635 :paramref:`.ColumnOperators.icontains.escape` parameter will establish
1636 a given character as an escape character which can be of use when
1637 the target expression is not a literal string.
1639 :param other: expression to be compared. This is usually a plain
1640 string value, but can also be an arbitrary SQL expression. LIKE
1641 wildcard characters ``%`` and ``_`` are not escaped by default unless
1642 the :paramref:`.ColumnOperators.icontains.autoescape` flag is
1643 set to True.
1645 :param autoescape: boolean; when True, establishes an escape character
1646 within the LIKE expression, then applies it to all occurrences of
1647 ``"%"``, ``"_"`` and the escape character itself within the
1648 comparison value, which is assumed to be a literal string and not a
1649 SQL expression.
1651 An expression such as::
1653 somecolumn.icontains("foo%bar", autoescape=True)
1655 Will render as:
1657 .. sourcecode:: sql
1659 lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '/'
1661 With the value of ``:param`` as ``"foo/%bar"``.
1663 :param escape: a character which when given will render with the
1664 ``ESCAPE`` keyword to establish that character as the escape
1665 character. This character can then be placed preceding occurrences
1666 of ``%`` and ``_`` to allow them to act as themselves and not
1667 wildcard characters.
1669 An expression such as::
1671 somecolumn.icontains("foo/%bar", escape="^")
1673 Will render as:
1675 .. sourcecode:: sql
1677 lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^'
1679 The parameter may also be combined with
1680 :paramref:`.ColumnOperators.contains.autoescape`::
1682 somecolumn.icontains("foo%bar^bat", escape="^", autoescape=True)
1684 Where above, the given literal parameter will be converted to
1685 ``"foo^%bar^^bat"`` before being passed to the database.
1687 .. seealso::
1689 :meth:`.ColumnOperators.contains`
1691 """ # noqa: E501
1692 return self.operate(icontains_op, other, **kw)
1694 def match(self, other: Any, **kwargs: Any) -> ColumnOperators:
1695 """Implements a database-specific 'match' operator.
1697 :meth:`_sql.ColumnOperators.match` attempts to resolve to
1698 a MATCH-like function or operator provided by the backend.
1699 Examples include:
1701 * PostgreSQL - renders ``x @@ plainto_tsquery(y)``
1703 .. versionchanged:: 2.0 ``plainto_tsquery()`` is used instead
1704 of ``to_tsquery()`` for PostgreSQL now; for compatibility with
1705 other forms, see :ref:`postgresql_match`.
1708 * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)``
1710 .. seealso::
1712 :class:`_mysql.match` - MySQL specific construct with
1713 additional features.
1715 * Oracle Database - renders ``CONTAINS(x, y)``
1716 * other backends may provide special implementations.
1717 * Backends without any special implementation will emit
1718 the operator as "MATCH". This is compatible with SQLite, for
1719 example.
1721 """
1722 return self.operate(match_op, other, **kwargs)
1724 def regexp_match(
1725 self, pattern: Any, flags: Optional[str] = None
1726 ) -> ColumnOperators:
1727 """Implements a database-specific 'regexp match' operator.
1729 E.g.::
1731 stmt = select(table.c.some_column).where(
1732 table.c.some_column.regexp_match("^(b|c)")
1733 )
1735 :meth:`_sql.ColumnOperators.regexp_match` attempts to resolve to
1736 a REGEXP-like function or operator provided by the backend, however
1737 the specific regular expression syntax and flags available are
1738 **not backend agnostic**.
1740 Examples include:
1742 * PostgreSQL - renders ``x ~ y`` or ``x !~ y`` when negated.
1743 * Oracle Database - renders ``REGEXP_LIKE(x, y)``
1744 * SQLite - uses SQLite's ``REGEXP`` placeholder operator and calls into
1745 the Python ``re.match()`` builtin.
1746 * other backends may provide special implementations.
1747 * Backends without any special implementation will emit
1748 the operator as "REGEXP" or "NOT REGEXP". This is compatible with
1749 SQLite and MySQL, for example.
1751 Regular expression support is currently implemented for Oracle
1752 Database, PostgreSQL, MySQL and MariaDB. Partial support is available
1753 for SQLite. Support among third-party dialects may vary.
1755 :param pattern: The regular expression pattern string or column
1756 clause.
1757 :param flags: Any regular expression string flags to apply, passed as
1758 plain Python string only. These flags are backend specific.
1759 Some backends, like PostgreSQL and MariaDB, may alternatively
1760 specify the flags as part of the pattern.
1761 When using the ignore case flag 'i' in PostgreSQL, the ignore case
1762 regexp match operator ``~*`` or ``!~*`` will be used.
1764 .. versionadded:: 1.4
1766 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation
1767 error, the "flags" parameter previously accepted SQL expression
1768 objects such as column expressions in addition to plain Python
1769 strings. This implementation did not work correctly with caching
1770 and was removed; strings only should be passed for the "flags"
1771 parameter, as these flags are rendered as literal inline values
1772 within SQL expressions.
1774 .. seealso::
1776 :meth:`_sql.ColumnOperators.regexp_replace`
1779 """
1780 return self.operate(regexp_match_op, pattern, flags=flags)
1782 def regexp_replace(
1783 self, pattern: Any, replacement: Any, flags: Optional[str] = None
1784 ) -> ColumnOperators:
1785 """Implements a database-specific 'regexp replace' operator.
1787 E.g.::
1789 stmt = select(
1790 table.c.some_column.regexp_replace("b(..)", "X\1Y", flags="g")
1791 )
1793 :meth:`_sql.ColumnOperators.regexp_replace` attempts to resolve to
1794 a REGEXP_REPLACE-like function provided by the backend, that
1795 usually emit the function ``REGEXP_REPLACE()``. However,
1796 the specific regular expression syntax and flags available are
1797 **not backend agnostic**.
1799 Regular expression replacement support is currently implemented for
1800 Oracle Database, PostgreSQL, MySQL 8 or greater and MariaDB. Support
1801 among third-party dialects may vary.
1803 :param pattern: The regular expression pattern string or column
1804 clause.
1805 :param pattern: The replacement string or column clause.
1806 :param flags: Any regular expression string flags to apply, passed as
1807 plain Python string only. These flags are backend specific.
1808 Some backends, like PostgreSQL and MariaDB, may alternatively
1809 specify the flags as part of the pattern.
1811 .. versionadded:: 1.4
1813 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation
1814 error, the "flags" parameter previously accepted SQL expression
1815 objects such as column expressions in addition to plain Python
1816 strings. This implementation did not work correctly with caching
1817 and was removed; strings only should be passed for the "flags"
1818 parameter, as these flags are rendered as literal inline values
1819 within SQL expressions.
1822 .. seealso::
1824 :meth:`_sql.ColumnOperators.regexp_match`
1826 """
1827 return self.operate(
1828 regexp_replace_op,
1829 pattern,
1830 replacement=replacement,
1831 flags=flags,
1832 )
1834 def collate(self, collation: str) -> ColumnOperators:
1835 """Produce a :func:`_expression.collate` clause against
1836 the parent object, given the collation string.
1838 .. seealso::
1840 :func:`_expression.collate`
1842 """
1843 return self.operate(collate, collation)
1845 def __radd__(self, other: Any) -> ColumnOperators:
1846 """Implement the ``+`` operator in reverse.
1848 See :meth:`.ColumnOperators.__add__`.
1850 """
1851 return self.reverse_operate(add, other)
1853 def __rsub__(self, other: Any) -> ColumnOperators:
1854 """Implement the ``-`` operator in reverse.
1856 See :meth:`.ColumnOperators.__sub__`.
1858 """
1859 return self.reverse_operate(sub, other)
1861 def __rmul__(self, other: Any) -> ColumnOperators:
1862 """Implement the ``*`` operator in reverse.
1864 See :meth:`.ColumnOperators.__mul__`.
1866 """
1867 return self.reverse_operate(mul, other)
1869 def __rmod__(self, other: Any) -> ColumnOperators:
1870 """Implement the ``%`` operator in reverse.
1872 See :meth:`.ColumnOperators.__mod__`.
1874 """
1875 return self.reverse_operate(mod, other)
1877 def between(
1878 self, cleft: Any, cright: Any, symmetric: bool = False
1879 ) -> ColumnOperators:
1880 """Produce a :func:`_expression.between` clause against
1881 the parent object, given the lower and upper range.
1883 """
1884 return self.operate(between_op, cleft, cright, symmetric=symmetric)
1886 def distinct(self) -> ColumnOperators:
1887 """Produce a :func:`_expression.distinct` clause against the
1888 parent object.
1890 """
1891 return self.operate(distinct_op)
1893 def any_(self) -> ColumnOperators:
1894 """Produce an :func:`_expression.any_` clause against the
1895 parent object.
1897 See the documentation for :func:`_sql.any_` for examples.
1899 .. note:: be sure to not confuse the newer
1900 :meth:`_sql.ColumnOperators.any_` method with the **legacy**
1901 version of this method, the :meth:`_types.ARRAY.Comparator.any`
1902 method that's specific to :class:`_types.ARRAY`, which uses a
1903 different calling style.
1905 """
1906 return self.operate(any_op)
1908 def all_(self) -> ColumnOperators:
1909 """Produce an :func:`_expression.all_` clause against the
1910 parent object.
1912 See the documentation for :func:`_sql.all_` for examples.
1914 .. note:: be sure to not confuse the newer
1915 :meth:`_sql.ColumnOperators.all_` method with the **legacy**
1916 version of this method, the :meth:`_types.ARRAY.Comparator.all`
1917 method that's specific to :class:`_types.ARRAY`, which uses a
1918 different calling style.
1920 """
1921 return self.operate(all_op)
1923 def __add__(self, other: Any) -> ColumnOperators:
1924 """Implement the ``+`` operator.
1926 In a column context, produces the clause ``a + b``
1927 if the parent object has non-string affinity.
1928 If the parent object has a string affinity,
1929 produces the concatenation operator, ``a || b`` -
1930 see :meth:`.ColumnOperators.concat`.
1932 """
1933 return self.operate(add, other)
1935 def __sub__(self, other: Any) -> ColumnOperators:
1936 """Implement the ``-`` operator.
1938 In a column context, produces the clause ``a - b``.
1940 """
1941 return self.operate(sub, other)
1943 def __mul__(self, other: Any) -> ColumnOperators:
1944 """Implement the ``*`` operator.
1946 In a column context, produces the clause ``a * b``.
1948 """
1949 return self.operate(mul, other)
1951 def __mod__(self, other: Any) -> ColumnOperators:
1952 """Implement the ``%`` operator.
1954 In a column context, produces the clause ``a % b``.
1956 """
1957 return self.operate(mod, other)
1959 def __truediv__(self, other: Any) -> ColumnOperators:
1960 """Implement the ``/`` operator.
1962 In a column context, produces the clause ``a / b``, and
1963 considers the result type to be numeric.
1965 .. versionchanged:: 2.0 The truediv operator against two integers
1966 is now considered to return a numeric value. Behavior on specific
1967 backends may vary.
1969 """
1970 return self.operate(truediv, other)
1972 def __rtruediv__(self, other: Any) -> ColumnOperators:
1973 """Implement the ``/`` operator in reverse.
1975 See :meth:`.ColumnOperators.__truediv__`.
1977 """
1978 return self.reverse_operate(truediv, other)
1980 def __floordiv__(self, other: Any) -> ColumnOperators:
1981 """Implement the ``//`` operator.
1983 In a column context, produces the clause ``a / b``,
1984 which is the same as "truediv", but considers the result
1985 type to be integer.
1987 .. versionadded:: 2.0
1989 """
1990 return self.operate(floordiv, other)
1992 def __rfloordiv__(self, other: Any) -> ColumnOperators:
1993 """Implement the ``//`` operator in reverse.
1995 See :meth:`.ColumnOperators.__floordiv__`.
1997 """
1998 return self.reverse_operate(floordiv, other)
2000 def __pow__(self, other: Any) -> ColumnOperators:
2001 """Implement the ``**`` operator.
2003 In a column context, produces the clause ``pow(a, b)``, or a similar
2004 dialect-specific expression.
2006 .. versionadded:: 2.1
2008 """
2009 return self.operate(pow_, other)
2011 def __rpow__(self, other: Any) -> ColumnOperators:
2012 """Implement the ``**`` operator in reverse.
2014 .. seealso::
2016 :meth:`.ColumnOperators.__pow__`.
2018 .. versionadded:: 2.1
2020 """
2021 return self.reverse_operate(pow_, other)
2024_commutative: Set[Any] = {eq, ne, add, mul}
2025_comparison: Set[Any] = {eq, ne, lt, gt, ge, le}
2028def _operator_fn(fn: Callable[..., Any]) -> OperatorType:
2029 return cast(OperatorType, fn)
2032def commutative_op(fn: _FN) -> _FN:
2033 _commutative.add(fn)
2034 return fn
2037def comparison_op(fn: _FN) -> _FN:
2038 _comparison.add(fn)
2039 return fn
2042@_operator_fn
2043def from_() -> Any:
2044 raise NotImplementedError()
2047@_operator_fn
2048@comparison_op
2049def function_as_comparison_op() -> Any:
2050 raise NotImplementedError()
2053@_operator_fn
2054def as_() -> Any:
2055 raise NotImplementedError()
2058@_operator_fn
2059def exists() -> Any:
2060 raise NotImplementedError()
2063@_operator_fn
2064def is_true(a: Any) -> Any:
2065 raise NotImplementedError()
2068# 1.4 deprecated; see #5435
2069if TYPE_CHECKING:
2071 @_operator_fn
2072 def istrue(a: Any) -> Any: ...
2074else:
2075 istrue = is_true
2078@_operator_fn
2079def is_false(a: Any) -> Any:
2080 raise NotImplementedError()
2083# 1.4 deprecated; see #5435
2084if TYPE_CHECKING:
2086 @_operator_fn
2087 def isfalse(a: Any) -> Any: ...
2089else:
2090 isfalse = is_false
2093@comparison_op
2094@_operator_fn
2095def is_distinct_from(a: Any, b: Any) -> Any:
2096 return a.is_distinct_from(b)
2099@comparison_op
2100@_operator_fn
2101def is_not_distinct_from(a: Any, b: Any) -> Any:
2102 return a.is_not_distinct_from(b)
2105# deprecated 1.4; see #5435
2106if TYPE_CHECKING:
2108 @_operator_fn
2109 def isnot_distinct_from(a: Any, b: Any) -> Any: ...
2111else:
2112 isnot_distinct_from = is_not_distinct_from
2115@comparison_op
2116@_operator_fn
2117def is_(a: Any, b: Any) -> Any:
2118 return a.is_(b)
2121@comparison_op
2122@_operator_fn
2123def is_not(a: Any, b: Any) -> Any:
2124 return a.is_not(b)
2127# 1.4 deprecated; see #5429
2128if TYPE_CHECKING:
2130 @_operator_fn
2131 def isnot(a: Any, b: Any) -> Any: ...
2133else:
2134 isnot = is_not
2137@_operator_fn
2138def collate(a: Any, b: Any) -> Any:
2139 return a.collate(b)
2142@_operator_fn
2143def op(a: Any, opstring: str, b: Any) -> Any:
2144 return a.op(opstring)(b)
2147@comparison_op
2148@_operator_fn
2149def like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2150 return a.like(b, escape=escape)
2153@comparison_op
2154@_operator_fn
2155def not_like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2156 return a.notlike(b, escape=escape)
2159# 1.4 deprecated; see #5435
2160if TYPE_CHECKING:
2162 @_operator_fn
2163 def notlike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ...
2165else:
2166 notlike_op = not_like_op
2169@comparison_op
2170@_operator_fn
2171def ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2172 return a.ilike(b, escape=escape)
2175@comparison_op
2176@_operator_fn
2177def not_ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2178 return a.not_ilike(b, escape=escape)
2181# 1.4 deprecated; see #5435
2182if TYPE_CHECKING:
2184 @_operator_fn
2185 def notilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ...
2187else:
2188 notilike_op = not_ilike_op
2191@comparison_op
2192@_operator_fn
2193def between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any:
2194 return a.between(b, c, symmetric=symmetric)
2197@comparison_op
2198@_operator_fn
2199def not_between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any:
2200 return ~a.between(b, c, symmetric=symmetric)
2203# 1.4 deprecated; see #5435
2204if TYPE_CHECKING:
2206 @_operator_fn
2207 def notbetween_op(
2208 a: Any, b: Any, c: Any, symmetric: bool = False
2209 ) -> Any: ...
2211else:
2212 notbetween_op = not_between_op
2215@comparison_op
2216@_operator_fn
2217def in_op(a: Any, b: Any) -> Any:
2218 return a.in_(b)
2221@comparison_op
2222@_operator_fn
2223def not_in_op(a: Any, b: Any) -> Any:
2224 return a.not_in(b)
2227# 1.4 deprecated; see #5429
2228if TYPE_CHECKING:
2230 @_operator_fn
2231 def notin_op(a: Any, b: Any) -> Any: ...
2233else:
2234 notin_op = not_in_op
2237@_operator_fn
2238def distinct_op(a: Any) -> Any:
2239 return a.distinct()
2242@_operator_fn
2243def any_op(a: Any) -> Any:
2244 return a.any_()
2247@_operator_fn
2248def all_op(a: Any) -> Any:
2249 return a.all_()
2252def _escaped_like_impl(
2253 fn: Callable[..., Any], other: Any, escape: Optional[str], autoescape: bool
2254) -> Any:
2255 if autoescape:
2256 if autoescape is not True:
2257 util.warn(
2258 "The autoescape parameter is now a simple boolean True/False"
2259 )
2260 if escape is None:
2261 escape = "/"
2263 if not isinstance(other, str):
2264 raise TypeError("String value expected when autoescape=True")
2266 if escape not in ("%", "_"):
2267 other = other.replace(escape, escape + escape)
2269 other = other.replace("%", escape + "%").replace("_", escape + "_")
2271 return fn(other, escape=escape)
2274@comparison_op
2275@_operator_fn
2276def startswith_op(
2277 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2278) -> Any:
2279 return _escaped_like_impl(a.startswith, b, escape, autoescape)
2282@comparison_op
2283@_operator_fn
2284def not_startswith_op(
2285 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2286) -> Any:
2287 return ~_escaped_like_impl(a.startswith, b, escape, autoescape)
2290# 1.4 deprecated; see #5435
2291if TYPE_CHECKING:
2293 @_operator_fn
2294 def notstartswith_op(
2295 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2296 ) -> Any: ...
2298else:
2299 notstartswith_op = not_startswith_op
2302@comparison_op
2303@_operator_fn
2304def istartswith_op(
2305 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2306) -> Any:
2307 return _escaped_like_impl(a.istartswith, b, escape, autoescape)
2310@comparison_op
2311@_operator_fn
2312def not_istartswith_op(
2313 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2314) -> Any:
2315 return ~_escaped_like_impl(a.istartswith, b, escape, autoescape)
2318@comparison_op
2319@_operator_fn
2320def endswith_op(
2321 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2322) -> Any:
2323 return _escaped_like_impl(a.endswith, b, escape, autoescape)
2326@comparison_op
2327@_operator_fn
2328def not_endswith_op(
2329 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2330) -> Any:
2331 return ~_escaped_like_impl(a.endswith, b, escape, autoescape)
2334# 1.4 deprecated; see #5435
2335if TYPE_CHECKING:
2337 @_operator_fn
2338 def notendswith_op(
2339 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2340 ) -> Any: ...
2342else:
2343 notendswith_op = not_endswith_op
2346@comparison_op
2347@_operator_fn
2348def iendswith_op(
2349 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2350) -> Any:
2351 return _escaped_like_impl(a.iendswith, b, escape, autoescape)
2354@comparison_op
2355@_operator_fn
2356def not_iendswith_op(
2357 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2358) -> Any:
2359 return ~_escaped_like_impl(a.iendswith, b, escape, autoescape)
2362@comparison_op
2363@_operator_fn
2364def contains_op(
2365 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2366) -> Any:
2367 return _escaped_like_impl(a.contains, b, escape, autoescape)
2370@comparison_op
2371@_operator_fn
2372def not_contains_op(
2373 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2374) -> Any:
2375 return ~_escaped_like_impl(a.contains, b, escape, autoescape)
2378# 1.4 deprecated; see #5435
2379if TYPE_CHECKING:
2381 @_operator_fn
2382 def notcontains_op(
2383 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2384 ) -> Any: ...
2386else:
2387 notcontains_op = not_contains_op
2390@comparison_op
2391@_operator_fn
2392def icontains_op(
2393 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2394) -> Any:
2395 return _escaped_like_impl(a.icontains, b, escape, autoescape)
2398@comparison_op
2399@_operator_fn
2400def not_icontains_op(
2401 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2402) -> Any:
2403 return ~_escaped_like_impl(a.icontains, b, escape, autoescape)
2406@comparison_op
2407@_operator_fn
2408def match_op(a: Any, b: Any, **kw: Any) -> Any:
2409 return a.match(b, **kw)
2412@comparison_op
2413@_operator_fn
2414def regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any:
2415 return a.regexp_match(b, flags=flags)
2418@comparison_op
2419@_operator_fn
2420def not_regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any:
2421 return ~a.regexp_match(b, flags=flags)
2424@_operator_fn
2425def regexp_replace_op(
2426 a: Any, b: Any, replacement: Any, flags: Optional[str] = None
2427) -> Any:
2428 return a.regexp_replace(b, replacement=replacement, flags=flags)
2431@comparison_op
2432@_operator_fn
2433def not_match_op(a: Any, b: Any, **kw: Any) -> Any:
2434 return ~a.match(b, **kw)
2437# 1.4 deprecated; see #5429
2438if TYPE_CHECKING:
2440 @_operator_fn
2441 def notmatch_op(a: Any, b: Any, **kw: Any) -> Any: ...
2443else:
2444 notmatch_op = not_match_op
2447@_operator_fn
2448def comma_op(a: Any, b: Any) -> Any:
2449 raise NotImplementedError()
2452@_operator_fn
2453def filter_op(a: Any, b: Any) -> Any:
2454 raise NotImplementedError()
2457@_operator_fn
2458def concat_op(a: Any, b: Any) -> Any:
2459 try:
2460 concat = a.concat
2461 except AttributeError:
2462 return b._rconcat(a)
2463 else:
2464 return concat(b)
2467@_operator_fn
2468def desc_op(a: Any) -> Any:
2469 return a.desc()
2472@_operator_fn
2473def asc_op(a: Any) -> Any:
2474 return a.asc()
2477@_operator_fn
2478def nulls_first_op(a: Any) -> Any:
2479 return a.nulls_first()
2482# 1.4 deprecated; see #5435
2483if TYPE_CHECKING:
2485 @_operator_fn
2486 def nullsfirst_op(a: Any) -> Any: ...
2488else:
2489 nullsfirst_op = nulls_first_op
2492@_operator_fn
2493def nulls_last_op(a: Any) -> Any:
2494 return a.nulls_last()
2497# 1.4 deprecated; see #5435
2498if TYPE_CHECKING:
2500 @_operator_fn
2501 def nullslast_op(a: Any) -> Any: ...
2503else:
2504 nullslast_op = nulls_last_op
2507@_operator_fn
2508def json_getitem_op(a: Any, b: Any) -> Any:
2509 raise NotImplementedError()
2512@_operator_fn
2513def json_path_getitem_op(a: Any, b: Any) -> Any:
2514 raise NotImplementedError()
2517@_operator_fn
2518def bitwise_xor_op(a: Any, b: Any) -> Any:
2519 return a.bitwise_xor(b)
2522@_operator_fn
2523def bitwise_or_op(a: Any, b: Any) -> Any:
2524 return a.bitwise_or(b)
2527@_operator_fn
2528def bitwise_and_op(a: Any, b: Any) -> Any:
2529 return a.bitwise_and(b)
2532@_operator_fn
2533def bitwise_not_op(a: Any) -> Any:
2534 return a.bitwise_not()
2537@_operator_fn
2538def bitwise_lshift_op(a: Any, b: Any) -> Any:
2539 return a.bitwise_lshift(b)
2542@_operator_fn
2543def bitwise_rshift_op(a: Any, b: Any) -> Any:
2544 return a.bitwise_rshift(b)
2547def is_comparison(op: OperatorType) -> bool:
2548 return op in _comparison or isinstance(op, custom_op) and op.is_comparison
2551def is_commutative(op: OperatorType) -> bool:
2552 return op in _commutative
2555def is_ordering_modifier(op: OperatorType) -> bool:
2556 return op in (asc_op, desc_op, nulls_first_op, nulls_last_op)
2559def is_natural_self_precedent(op: OperatorType) -> bool:
2560 return (
2561 op in _natural_self_precedent
2562 or isinstance(op, custom_op)
2563 and op.natural_self_precedent
2564 )
2567_booleans = (inv, is_true, is_false, and_, or_)
2570def is_boolean(op: OperatorType) -> bool:
2571 return is_comparison(op) or op in _booleans
2574_mirror = {gt: lt, ge: le, lt: gt, le: ge}
2577def mirror(op: OperatorType) -> OperatorType:
2578 """rotate a comparison operator 180 degrees.
2580 Note this is not the same as negation.
2582 """
2583 return _mirror.get(op, op)
2586_associative = _commutative.union([concat_op, and_, or_]).difference([eq, ne])
2589def is_associative(op: OperatorType) -> bool:
2590 return op in _associative
2593def is_order_by_modifier(op: Optional[OperatorType]) -> bool:
2594 return op in _order_by_modifier
2597_order_by_modifier = {desc_op, asc_op, nulls_first_op, nulls_last_op}
2599_natural_self_precedent = _associative.union(
2600 [getitem, json_getitem_op, json_path_getitem_op]
2601)
2602"""Operators where if we have (a op b) op c, we don't want to
2603parenthesize (a op b).
2605"""
2608@_operator_fn
2609def _asbool(a: Any) -> Any:
2610 raise NotImplementedError()
2613class _OpLimit(IntEnum):
2614 _smallest = -100
2615 _largest = 100
2618_PRECEDENCE: Dict[OperatorType, int] = {
2619 from_: 15,
2620 function_as_comparison_op: 15,
2621 any_op: 15,
2622 all_op: 15,
2623 getitem: 15,
2624 json_getitem_op: 15,
2625 json_path_getitem_op: 15,
2626 pow_: 15,
2627 mul: 8,
2628 truediv: 8,
2629 floordiv: 8,
2630 mod: 8,
2631 neg: 8,
2632 bitwise_not_op: 8,
2633 add: 7,
2634 sub: 7,
2635 bitwise_xor_op: 7,
2636 bitwise_or_op: 7,
2637 bitwise_and_op: 7,
2638 bitwise_lshift_op: 7,
2639 bitwise_rshift_op: 7,
2640 filter_op: 6,
2641 concat_op: 5,
2642 match_op: 5,
2643 not_match_op: 5,
2644 regexp_match_op: 5,
2645 not_regexp_match_op: 5,
2646 regexp_replace_op: 5,
2647 ilike_op: 5,
2648 not_ilike_op: 5,
2649 like_op: 5,
2650 not_like_op: 5,
2651 in_op: 5,
2652 not_in_op: 5,
2653 is_: 5,
2654 is_not: 5,
2655 eq: 5,
2656 ne: 5,
2657 is_distinct_from: 5,
2658 is_not_distinct_from: 5,
2659 gt: 5,
2660 lt: 5,
2661 ge: 5,
2662 le: 5,
2663 between_op: 5,
2664 not_between_op: 5,
2665 distinct_op: 5,
2666 inv: 5,
2667 is_true: 5,
2668 is_false: 5,
2669 and_: 3,
2670 or_: 2,
2671 comma_op: -1,
2672 desc_op: 3,
2673 asc_op: 3,
2674 collate: 4,
2675 as_: -1,
2676 exists: 0,
2677 _asbool: -10,
2678}
2681def is_precedent(
2682 operator: OperatorType, against: Optional[OperatorType]
2683) -> bool:
2684 if operator is against and is_natural_self_precedent(operator):
2685 return False
2686 elif against is None:
2687 return True
2688 else:
2689 return bool(
2690 _PRECEDENCE.get(
2691 operator, getattr(operator, "precedence", _OpLimit._smallest)
2692 )
2693 <= _PRECEDENCE.get(
2694 against, getattr(against, "precedence", _OpLimit._largest)
2695 )
2696 )