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 Optional
45from typing import overload
46from typing import Protocol
47from typing import Set
48from typing import Tuple
49from typing import Type
50from typing import TYPE_CHECKING
51from typing import TypeVar
52from typing import Union
54from .. import exc
55from .. import util
56from ..util.typing import Literal
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 ColumnOperators(Operators):
503 """Defines boolean, comparison, and other operators for
504 :class:`_expression.ColumnElement` expressions.
506 By default, all methods call down to
507 :meth:`.operate` or :meth:`.reverse_operate`,
508 passing in the appropriate operator function from the
509 Python builtin ``operator`` module or
510 a SQLAlchemy-specific operator function from
511 :mod:`sqlalchemy.expression.operators`. For example
512 the ``__eq__`` function::
514 def __eq__(self, other):
515 return self.operate(operators.eq, other)
517 Where ``operators.eq`` is essentially::
519 def eq(a, b):
520 return a == b
522 The core column expression unit :class:`_expression.ColumnElement`
523 overrides :meth:`.Operators.operate` and others
524 to return further :class:`_expression.ColumnElement` constructs,
525 so that the ``==`` operation above is replaced by a clause
526 construct.
528 .. seealso::
530 :ref:`types_operators`
532 :attr:`.TypeEngine.comparator_factory`
534 :class:`.ColumnOperators`
536 :class:`.PropComparator`
538 """
540 __slots__ = ()
542 timetuple: Literal[None] = None
543 """Hack, allows datetime objects to be compared on the LHS."""
545 if typing.TYPE_CHECKING:
547 def operate(
548 self, op: OperatorType, *other: Any, **kwargs: Any
549 ) -> ColumnOperators: ...
551 def reverse_operate(
552 self, op: OperatorType, other: Any, **kwargs: Any
553 ) -> ColumnOperators: ...
555 def __lt__(self, other: Any) -> ColumnOperators:
556 """Implement the ``<`` operator.
558 In a column context, produces the clause ``a < b``.
560 """
561 return self.operate(lt, other)
563 def __le__(self, other: Any) -> ColumnOperators:
564 """Implement the ``<=`` operator.
566 In a column context, produces the clause ``a <= b``.
568 """
569 return self.operate(le, other)
571 # ColumnOperators defines an __eq__ so it must explicitly declare also
572 # an hash or it's set to None by python:
573 # https://docs.python.org/3/reference/datamodel.html#object.__hash__
574 if TYPE_CHECKING:
576 def __hash__(self) -> int: ...
578 else:
579 __hash__ = Operators.__hash__
581 def __eq__(self, other: Any) -> ColumnOperators: # type: ignore[override]
582 """Implement the ``==`` operator.
584 In a column context, produces the clause ``a = b``.
585 If the target is ``None``, produces ``a IS NULL``.
587 """
588 return self.operate(eq, other)
590 def __ne__(self, other: Any) -> ColumnOperators: # type: ignore[override]
591 """Implement the ``!=`` operator.
593 In a column context, produces the clause ``a != b``.
594 If the target is ``None``, produces ``a IS NOT NULL``.
596 """
597 return self.operate(ne, other)
599 def is_distinct_from(self, other: Any) -> ColumnOperators:
600 """Implement the ``IS DISTINCT FROM`` operator.
602 Renders "a IS DISTINCT FROM b" on most platforms;
603 on some such as SQLite may render "a IS NOT b".
605 """
606 return self.operate(is_distinct_from, other)
608 def is_not_distinct_from(self, other: Any) -> ColumnOperators:
609 """Implement the ``IS NOT DISTINCT FROM`` operator.
611 Renders "a IS NOT DISTINCT FROM b" on most platforms;
612 on some such as SQLite may render "a IS b".
614 .. versionchanged:: 1.4 The ``is_not_distinct_from()`` operator is
615 renamed from ``isnot_distinct_from()`` in previous releases.
616 The previous name remains available for backwards compatibility.
618 """
619 return self.operate(is_not_distinct_from, other)
621 # deprecated 1.4; see #5435
622 if TYPE_CHECKING:
624 def isnot_distinct_from(self, other: Any) -> ColumnOperators: ...
626 else:
627 isnot_distinct_from = is_not_distinct_from
629 def __gt__(self, other: Any) -> ColumnOperators:
630 """Implement the ``>`` operator.
632 In a column context, produces the clause ``a > b``.
634 """
635 return self.operate(gt, other)
637 def __ge__(self, other: Any) -> ColumnOperators:
638 """Implement the ``>=`` operator.
640 In a column context, produces the clause ``a >= b``.
642 """
643 return self.operate(ge, other)
645 def __neg__(self) -> ColumnOperators:
646 """Implement the ``-`` operator.
648 In a column context, produces the clause ``-a``.
650 """
651 return self.operate(neg)
653 def __contains__(self, other: Any) -> ColumnOperators:
654 return self.operate(contains, other)
656 def __getitem__(self, index: Any) -> ColumnOperators:
657 """Implement the [] operator.
659 This can be used by some database-specific types
660 such as PostgreSQL ARRAY and HSTORE.
662 """
663 return self.operate(getitem, index)
665 def __lshift__(self, other: Any) -> ColumnOperators:
666 """Implement the ``<<`` operator.
668 Not used by SQLAlchemy core, this is provided
669 for custom operator systems which want to use
670 << as an extension point.
671 """
672 return self.operate(lshift, other)
674 def __rlshift__(self, other: Any) -> ColumnOperators:
675 """Implement the ``<<`` operator in reverse.
677 Not used by SQLAlchemy core, this is provided
678 for custom operator systems which want to use
679 << as an extension point.
680 """
681 return self.reverse_operate(lshift, other)
683 def __rshift__(self, other: Any) -> ColumnOperators:
684 """Implement the ``>>`` operator.
686 Not used by SQLAlchemy core, this is provided
687 for custom operator systems which want to use
688 >> as an extension point.
689 """
690 return self.operate(rshift, other)
692 def __rrshift__(self, other: Any) -> ColumnOperators:
693 """Implement the ``>>`` operator in reverse.
695 Not used by SQLAlchemy core, this is provided
696 for custom operator systems which want to use
697 >> as an extension point.
698 """
699 return self.reverse_operate(rshift, other)
701 def __matmul__(self, other: Any) -> ColumnOperators:
702 """Implement the ``@`` operator.
704 Not used by SQLAlchemy core, this is provided
705 for custom operator systems which want to use
706 @ as an extension point.
707 """
708 return self.operate(matmul, other)
710 def __rmatmul__(self, other: Any) -> ColumnOperators:
711 """Implement the ``@`` operator in reverse.
713 Not used by SQLAlchemy core, this is provided
714 for custom operator systems which want to use
715 @ as an extension point.
716 """
717 return self.reverse_operate(matmul, other)
719 def concat(self, other: Any) -> ColumnOperators:
720 """Implement the 'concat' operator.
722 In a column context, produces the clause ``a || b``,
723 or uses the ``concat()`` operator on MySQL.
725 """
726 return self.operate(concat_op, other)
728 def _rconcat(self, other: Any) -> ColumnOperators:
729 """Implement an 'rconcat' operator.
731 this is for internal use at the moment
733 .. versionadded:: 1.4.40
735 """
736 return self.reverse_operate(concat_op, other)
738 def like(
739 self, other: Any, escape: Optional[str] = None
740 ) -> ColumnOperators:
741 r"""Implement the ``like`` operator.
743 In a column context, produces the expression:
745 .. sourcecode:: sql
747 a LIKE other
749 E.g.::
751 stmt = select(sometable).where(sometable.c.column.like("%foobar%"))
753 :param other: expression to be compared
754 :param escape: optional escape character, renders the ``ESCAPE``
755 keyword, e.g.::
757 somecolumn.like("foo/%bar", escape="/")
759 .. seealso::
761 :meth:`.ColumnOperators.ilike`
763 """
764 return self.operate(like_op, other, escape=escape)
766 def ilike(
767 self, other: Any, escape: Optional[str] = None
768 ) -> ColumnOperators:
769 r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE.
771 In a column context, produces an expression either of the form:
773 .. sourcecode:: sql
775 lower(a) LIKE lower(other)
777 Or on backends that support the ILIKE operator:
779 .. sourcecode:: sql
781 a ILIKE other
783 E.g.::
785 stmt = select(sometable).where(sometable.c.column.ilike("%foobar%"))
787 :param other: expression to be compared
788 :param escape: optional escape character, renders the ``ESCAPE``
789 keyword, e.g.::
791 somecolumn.ilike("foo/%bar", escape="/")
793 .. seealso::
795 :meth:`.ColumnOperators.like`
797 """ # noqa: E501
798 return self.operate(ilike_op, other, escape=escape)
800 def bitwise_xor(self, other: Any) -> ColumnOperators:
801 """Produce a bitwise XOR operation, typically via the ``^``
802 operator, or ``#`` for PostgreSQL.
804 .. versionadded:: 2.0.2
806 .. seealso::
808 :ref:`operators_bitwise`
810 """
812 return self.operate(bitwise_xor_op, other)
814 def bitwise_or(self, other: Any) -> ColumnOperators:
815 """Produce a bitwise OR operation, typically via the ``|``
816 operator.
818 .. versionadded:: 2.0.2
820 .. seealso::
822 :ref:`operators_bitwise`
824 """
826 return self.operate(bitwise_or_op, other)
828 def bitwise_and(self, other: Any) -> ColumnOperators:
829 """Produce a bitwise AND operation, typically via the ``&``
830 operator.
832 .. versionadded:: 2.0.2
834 .. seealso::
836 :ref:`operators_bitwise`
838 """
840 return self.operate(bitwise_and_op, other)
842 def bitwise_not(self) -> ColumnOperators:
843 """Produce a bitwise NOT operation, typically via the ``~``
844 operator.
846 .. versionadded:: 2.0.2
848 .. seealso::
850 :ref:`operators_bitwise`
852 """
854 return self.operate(bitwise_not_op)
856 def bitwise_lshift(self, other: Any) -> ColumnOperators:
857 """Produce a bitwise LSHIFT operation, typically via the ``<<``
858 operator.
860 .. versionadded:: 2.0.2
862 .. seealso::
864 :ref:`operators_bitwise`
866 """
868 return self.operate(bitwise_lshift_op, other)
870 def bitwise_rshift(self, other: Any) -> ColumnOperators:
871 """Produce a bitwise RSHIFT operation, typically via the ``>>``
872 operator.
874 .. versionadded:: 2.0.2
876 .. seealso::
878 :ref:`operators_bitwise`
880 """
882 return self.operate(bitwise_rshift_op, other)
884 def in_(self, other: Any) -> ColumnOperators:
885 """Implement the ``in`` operator.
887 In a column context, produces the clause ``column IN <other>``.
889 The given parameter ``other`` may be:
891 * A list of literal values,
892 e.g.::
894 stmt.where(column.in_([1, 2, 3]))
896 In this calling form, the list of items is converted to a set of
897 bound parameters the same length as the list given:
899 .. sourcecode:: sql
901 WHERE COL IN (?, ?, ?)
903 * A list of tuples may be provided if the comparison is against a
904 :func:`.tuple_` containing multiple expressions::
906 from sqlalchemy import tuple_
908 stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
910 * An empty list,
911 e.g.::
913 stmt.where(column.in_([]))
915 In this calling form, the expression renders an "empty set"
916 expression. These expressions are tailored to individual backends
917 and are generally trying to get an empty SELECT statement as a
918 subquery. Such as on SQLite, the expression is:
920 .. sourcecode:: sql
922 WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
924 .. versionchanged:: 1.4 empty IN expressions now use an
925 execution-time generated SELECT subquery in all cases.
927 * A bound parameter, e.g. :func:`.bindparam`, may be used if it
928 includes the :paramref:`.bindparam.expanding` flag::
930 stmt.where(column.in_(bindparam("value", expanding=True)))
932 In this calling form, the expression renders a special non-SQL
933 placeholder expression that looks like:
935 .. sourcecode:: sql
937 WHERE COL IN ([EXPANDING_value])
939 This placeholder expression is intercepted at statement execution
940 time to be converted into the variable number of bound parameter
941 form illustrated earlier. If the statement were executed as::
943 connection.execute(stmt, {"value": [1, 2, 3]})
945 The database would be passed a bound parameter for each value:
947 .. sourcecode:: sql
949 WHERE COL IN (?, ?, ?)
951 If an empty list is passed, a special "empty list" expression,
952 which is specific to the database in use, is rendered. On
953 SQLite this would be:
955 .. sourcecode:: sql
957 WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
959 * a :func:`_expression.select` construct, which is usually a
960 correlated scalar select::
962 stmt.where(
963 column.in_(select(othertable.c.y).where(table.c.x == othertable.c.x))
964 )
966 In this calling form, :meth:`.ColumnOperators.in_` renders as given:
968 .. sourcecode:: sql
970 WHERE COL IN (SELECT othertable.y
971 FROM othertable WHERE othertable.x = table.x)
973 :param other: a list of literals, a :func:`_expression.select`
974 construct, or a :func:`.bindparam` construct that includes the
975 :paramref:`.bindparam.expanding` flag set to True.
977 """ # noqa: E501
978 return self.operate(in_op, other)
980 def not_in(self, other: Any) -> ColumnOperators:
981 """implement the ``NOT IN`` operator.
983 This is equivalent to using negation with
984 :meth:`.ColumnOperators.in_`, i.e. ``~x.in_(y)``.
986 In the case that ``other`` is an empty sequence, the compiler
987 produces an "empty not in" expression. This defaults to the
988 expression "1 = 1" to produce true in all cases. The
989 :paramref:`_sa.create_engine.empty_in_strategy` may be used to
990 alter this behavior.
992 .. versionchanged:: 1.4 The ``not_in()`` operator is renamed from
993 ``notin_()`` in previous releases. The previous name remains
994 available for backwards compatibility.
996 .. seealso::
998 :meth:`.ColumnOperators.in_`
1000 """
1001 return self.operate(not_in_op, other)
1003 # deprecated 1.4; see #5429
1004 if TYPE_CHECKING:
1006 def notin_(self, other: Any) -> ColumnOperators: ...
1008 else:
1009 notin_ = not_in
1011 def not_like(
1012 self, other: Any, escape: Optional[str] = None
1013 ) -> ColumnOperators:
1014 """implement the ``NOT LIKE`` operator.
1016 This is equivalent to using negation with
1017 :meth:`.ColumnOperators.like`, i.e. ``~x.like(y)``.
1019 .. versionchanged:: 1.4 The ``not_like()`` operator is renamed from
1020 ``notlike()`` in previous releases. The previous name remains
1021 available for backwards compatibility.
1023 .. seealso::
1025 :meth:`.ColumnOperators.like`
1027 """
1028 return self.operate(not_like_op, other, escape=escape)
1030 # deprecated 1.4; see #5435
1031 if TYPE_CHECKING:
1033 def notlike(
1034 self, other: Any, escape: Optional[str] = None
1035 ) -> ColumnOperators: ...
1037 else:
1038 notlike = not_like
1040 def not_ilike(
1041 self, other: Any, escape: Optional[str] = None
1042 ) -> ColumnOperators:
1043 """implement the ``NOT ILIKE`` operator.
1045 This is equivalent to using negation with
1046 :meth:`.ColumnOperators.ilike`, i.e. ``~x.ilike(y)``.
1048 .. versionchanged:: 1.4 The ``not_ilike()`` operator is renamed from
1049 ``notilike()`` in previous releases. The previous name remains
1050 available for backwards compatibility.
1052 .. seealso::
1054 :meth:`.ColumnOperators.ilike`
1056 """
1057 return self.operate(not_ilike_op, other, escape=escape)
1059 # deprecated 1.4; see #5435
1060 if TYPE_CHECKING:
1062 def notilike(
1063 self, other: Any, escape: Optional[str] = None
1064 ) -> ColumnOperators: ...
1066 else:
1067 notilike = not_ilike
1069 def is_(self, other: Any) -> ColumnOperators:
1070 """Implement the ``IS`` operator.
1072 Normally, ``IS`` is generated automatically when comparing to a
1073 value of ``None``, which resolves to ``NULL``. However, explicit
1074 usage of ``IS`` may be desirable if comparing to boolean values
1075 on certain platforms.
1077 .. seealso:: :meth:`.ColumnOperators.is_not`
1079 """
1080 return self.operate(is_, other)
1082 def is_not(self, other: Any) -> ColumnOperators:
1083 """Implement the ``IS NOT`` operator.
1085 Normally, ``IS NOT`` is generated automatically when comparing to a
1086 value of ``None``, which resolves to ``NULL``. However, explicit
1087 usage of ``IS NOT`` may be desirable if comparing to boolean values
1088 on certain platforms.
1090 .. versionchanged:: 1.4 The ``is_not()`` operator is renamed from
1091 ``isnot()`` in previous releases. The previous name remains
1092 available for backwards compatibility.
1094 .. seealso:: :meth:`.ColumnOperators.is_`
1096 """
1097 return self.operate(is_not, other)
1099 # deprecated 1.4; see #5429
1100 if TYPE_CHECKING:
1102 def isnot(self, other: Any) -> ColumnOperators: ...
1104 else:
1105 isnot = is_not
1107 def startswith(
1108 self,
1109 other: Any,
1110 escape: Optional[str] = None,
1111 autoescape: bool = False,
1112 ) -> ColumnOperators:
1113 r"""Implement the ``startswith`` operator.
1115 Produces a LIKE expression that tests against a match for the start
1116 of a string value:
1118 .. sourcecode:: sql
1120 column LIKE <other> || '%'
1122 E.g.::
1124 stmt = select(sometable).where(sometable.c.column.startswith("foobar"))
1126 Since the operator uses ``LIKE``, wildcard characters
1127 ``"%"`` and ``"_"`` that are present inside the <other> expression
1128 will behave like wildcards as well. For literal string
1129 values, the :paramref:`.ColumnOperators.startswith.autoescape` flag
1130 may be set to ``True`` to apply escaping to occurrences of these
1131 characters within the string value so that they match as themselves
1132 and not as wildcard characters. Alternatively, the
1133 :paramref:`.ColumnOperators.startswith.escape` parameter will establish
1134 a given character as an escape character which can be of use when
1135 the target expression is not a literal string.
1137 :param other: expression to be compared. This is usually a plain
1138 string value, but can also be an arbitrary SQL expression. LIKE
1139 wildcard characters ``%`` and ``_`` are not escaped by default unless
1140 the :paramref:`.ColumnOperators.startswith.autoescape` flag is
1141 set to True.
1143 :param autoescape: boolean; when True, establishes an escape character
1144 within the LIKE expression, then applies it to all occurrences of
1145 ``"%"``, ``"_"`` and the escape character itself within the
1146 comparison value, which is assumed to be a literal string and not a
1147 SQL expression.
1149 An expression such as::
1151 somecolumn.startswith("foo%bar", autoescape=True)
1153 Will render as:
1155 .. sourcecode:: sql
1157 somecolumn LIKE :param || '%' ESCAPE '/'
1159 With the value of ``:param`` as ``"foo/%bar"``.
1161 :param escape: a character which when given will render with the
1162 ``ESCAPE`` keyword to establish that character as the escape
1163 character. This character can then be placed preceding occurrences
1164 of ``%`` and ``_`` to allow them to act as themselves and not
1165 wildcard characters.
1167 An expression such as::
1169 somecolumn.startswith("foo/%bar", escape="^")
1171 Will render as:
1173 .. sourcecode:: sql
1175 somecolumn LIKE :param || '%' ESCAPE '^'
1177 The parameter may also be combined with
1178 :paramref:`.ColumnOperators.startswith.autoescape`::
1180 somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
1182 Where above, the given literal parameter will be converted to
1183 ``"foo^%bar^^bat"`` before being passed to the database.
1185 .. seealso::
1187 :meth:`.ColumnOperators.endswith`
1189 :meth:`.ColumnOperators.contains`
1191 :meth:`.ColumnOperators.like`
1193 """ # noqa: E501
1194 return self.operate(
1195 startswith_op, other, escape=escape, autoescape=autoescape
1196 )
1198 def istartswith(
1199 self,
1200 other: Any,
1201 escape: Optional[str] = None,
1202 autoescape: bool = False,
1203 ) -> ColumnOperators:
1204 r"""Implement the ``istartswith`` operator, e.g. case insensitive
1205 version of :meth:`.ColumnOperators.startswith`.
1207 Produces a LIKE expression that tests against an insensitive
1208 match for the start of a string value:
1210 .. sourcecode:: sql
1212 lower(column) LIKE lower(<other>) || '%'
1214 E.g.::
1216 stmt = select(sometable).where(sometable.c.column.istartswith("foobar"))
1218 Since the operator uses ``LIKE``, wildcard characters
1219 ``"%"`` and ``"_"`` that are present inside the <other> expression
1220 will behave like wildcards as well. For literal string
1221 values, the :paramref:`.ColumnOperators.istartswith.autoescape` flag
1222 may be set to ``True`` to apply escaping to occurrences of these
1223 characters within the string value so that they match as themselves
1224 and not as wildcard characters. Alternatively, the
1225 :paramref:`.ColumnOperators.istartswith.escape` parameter will
1226 establish a given character as an escape character which can be of
1227 use when the target expression is not a literal string.
1229 :param other: expression to be compared. This is usually a plain
1230 string value, but can also be an arbitrary SQL expression. LIKE
1231 wildcard characters ``%`` and ``_`` are not escaped by default unless
1232 the :paramref:`.ColumnOperators.istartswith.autoescape` flag is
1233 set to True.
1235 :param autoescape: boolean; when True, establishes an escape character
1236 within the LIKE expression, then applies it to all occurrences of
1237 ``"%"``, ``"_"`` and the escape character itself within the
1238 comparison value, which is assumed to be a literal string and not a
1239 SQL expression.
1241 An expression such as::
1243 somecolumn.istartswith("foo%bar", autoescape=True)
1245 Will render as:
1247 .. sourcecode:: sql
1249 lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/'
1251 With the value of ``:param`` as ``"foo/%bar"``.
1253 :param escape: a character which when given will render with the
1254 ``ESCAPE`` keyword to establish that character as the escape
1255 character. This character can then be placed preceding occurrences
1256 of ``%`` and ``_`` to allow them to act as themselves and not
1257 wildcard characters.
1259 An expression such as::
1261 somecolumn.istartswith("foo/%bar", escape="^")
1263 Will render as:
1265 .. sourcecode:: sql
1267 lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^'
1269 The parameter may also be combined with
1270 :paramref:`.ColumnOperators.istartswith.autoescape`::
1272 somecolumn.istartswith("foo%bar^bat", escape="^", autoescape=True)
1274 Where above, the given literal parameter will be converted to
1275 ``"foo^%bar^^bat"`` before being passed to the database.
1277 .. seealso::
1279 :meth:`.ColumnOperators.startswith`
1280 """ # noqa: E501
1281 return self.operate(
1282 istartswith_op, other, escape=escape, autoescape=autoescape
1283 )
1285 def endswith(
1286 self,
1287 other: Any,
1288 escape: Optional[str] = None,
1289 autoescape: bool = False,
1290 ) -> ColumnOperators:
1291 r"""Implement the 'endswith' operator.
1293 Produces a LIKE expression that tests against a match for the end
1294 of a string value:
1296 .. sourcecode:: sql
1298 column LIKE '%' || <other>
1300 E.g.::
1302 stmt = select(sometable).where(sometable.c.column.endswith("foobar"))
1304 Since the operator uses ``LIKE``, wildcard characters
1305 ``"%"`` and ``"_"`` that are present inside the <other> expression
1306 will behave like wildcards as well. For literal string
1307 values, the :paramref:`.ColumnOperators.endswith.autoescape` flag
1308 may be set to ``True`` to apply escaping to occurrences of these
1309 characters within the string value so that they match as themselves
1310 and not as wildcard characters. Alternatively, the
1311 :paramref:`.ColumnOperators.endswith.escape` parameter will establish
1312 a given character as an escape character which can be of use when
1313 the target expression is not a literal string.
1315 :param other: expression to be compared. This is usually a plain
1316 string value, but can also be an arbitrary SQL expression. LIKE
1317 wildcard characters ``%`` and ``_`` are not escaped by default unless
1318 the :paramref:`.ColumnOperators.endswith.autoescape` flag is
1319 set to True.
1321 :param autoescape: boolean; when True, establishes an escape character
1322 within the LIKE expression, then applies it to all occurrences of
1323 ``"%"``, ``"_"`` and the escape character itself within the
1324 comparison value, which is assumed to be a literal string and not a
1325 SQL expression.
1327 An expression such as::
1329 somecolumn.endswith("foo%bar", autoescape=True)
1331 Will render as:
1333 .. sourcecode:: sql
1335 somecolumn LIKE '%' || :param ESCAPE '/'
1337 With the value of ``:param`` as ``"foo/%bar"``.
1339 :param escape: a character which when given will render with the
1340 ``ESCAPE`` keyword to establish that character as the escape
1341 character. This character can then be placed preceding occurrences
1342 of ``%`` and ``_`` to allow them to act as themselves and not
1343 wildcard characters.
1345 An expression such as::
1347 somecolumn.endswith("foo/%bar", escape="^")
1349 Will render as:
1351 .. sourcecode:: sql
1353 somecolumn LIKE '%' || :param ESCAPE '^'
1355 The parameter may also be combined with
1356 :paramref:`.ColumnOperators.endswith.autoescape`::
1358 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
1360 Where above, the given literal parameter will be converted to
1361 ``"foo^%bar^^bat"`` before being passed to the database.
1363 .. seealso::
1365 :meth:`.ColumnOperators.startswith`
1367 :meth:`.ColumnOperators.contains`
1369 :meth:`.ColumnOperators.like`
1371 """ # noqa: E501
1372 return self.operate(
1373 endswith_op, other, escape=escape, autoescape=autoescape
1374 )
1376 def iendswith(
1377 self,
1378 other: Any,
1379 escape: Optional[str] = None,
1380 autoescape: bool = False,
1381 ) -> ColumnOperators:
1382 r"""Implement the ``iendswith`` operator, e.g. case insensitive
1383 version of :meth:`.ColumnOperators.endswith`.
1385 Produces a LIKE expression that tests against an insensitive match
1386 for the end of a string value:
1388 .. sourcecode:: sql
1390 lower(column) LIKE '%' || lower(<other>)
1392 E.g.::
1394 stmt = select(sometable).where(sometable.c.column.iendswith("foobar"))
1396 Since the operator uses ``LIKE``, wildcard characters
1397 ``"%"`` and ``"_"`` that are present inside the <other> expression
1398 will behave like wildcards as well. For literal string
1399 values, the :paramref:`.ColumnOperators.iendswith.autoescape` flag
1400 may be set to ``True`` to apply escaping to occurrences of these
1401 characters within the string value so that they match as themselves
1402 and not as wildcard characters. Alternatively, the
1403 :paramref:`.ColumnOperators.iendswith.escape` parameter will establish
1404 a given character as an escape character which can be of use when
1405 the target expression is not a literal string.
1407 :param other: expression to be compared. This is usually a plain
1408 string value, but can also be an arbitrary SQL expression. LIKE
1409 wildcard characters ``%`` and ``_`` are not escaped by default unless
1410 the :paramref:`.ColumnOperators.iendswith.autoescape` flag is
1411 set to True.
1413 :param autoescape: boolean; when True, establishes an escape character
1414 within the LIKE expression, then applies it to all occurrences of
1415 ``"%"``, ``"_"`` and the escape character itself within the
1416 comparison value, which is assumed to be a literal string and not a
1417 SQL expression.
1419 An expression such as::
1421 somecolumn.iendswith("foo%bar", autoescape=True)
1423 Will render as:
1425 .. sourcecode:: sql
1427 lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '/'
1429 With the value of ``:param`` as ``"foo/%bar"``.
1431 :param escape: a character which when given will render with the
1432 ``ESCAPE`` keyword to establish that character as the escape
1433 character. This character can then be placed preceding occurrences
1434 of ``%`` and ``_`` to allow them to act as themselves and not
1435 wildcard characters.
1437 An expression such as::
1439 somecolumn.iendswith("foo/%bar", escape="^")
1441 Will render as:
1443 .. sourcecode:: sql
1445 lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^'
1447 The parameter may also be combined with
1448 :paramref:`.ColumnOperators.iendswith.autoescape`::
1450 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
1452 Where above, the given literal parameter will be converted to
1453 ``"foo^%bar^^bat"`` before being passed to the database.
1455 .. seealso::
1457 :meth:`.ColumnOperators.endswith`
1458 """ # noqa: E501
1459 return self.operate(
1460 iendswith_op, other, escape=escape, autoescape=autoescape
1461 )
1463 def contains(self, other: Any, **kw: Any) -> ColumnOperators:
1464 r"""Implement the 'contains' operator.
1466 Produces a LIKE expression that tests against a match for the middle
1467 of a string value:
1469 .. sourcecode:: sql
1471 column LIKE '%' || <other> || '%'
1473 E.g.::
1475 stmt = select(sometable).where(sometable.c.column.contains("foobar"))
1477 Since the operator uses ``LIKE``, wildcard characters
1478 ``"%"`` and ``"_"`` that are present inside the <other> expression
1479 will behave like wildcards as well. For literal string
1480 values, the :paramref:`.ColumnOperators.contains.autoescape` flag
1481 may be set to ``True`` to apply escaping to occurrences of these
1482 characters within the string value so that they match as themselves
1483 and not as wildcard characters. Alternatively, the
1484 :paramref:`.ColumnOperators.contains.escape` parameter will establish
1485 a given character as an escape character which can be of use when
1486 the target expression is not a literal string.
1488 :param other: expression to be compared. This is usually a plain
1489 string value, but can also be an arbitrary SQL expression. LIKE
1490 wildcard characters ``%`` and ``_`` are not escaped by default unless
1491 the :paramref:`.ColumnOperators.contains.autoescape` flag is
1492 set to True.
1494 :param autoescape: boolean; when True, establishes an escape character
1495 within the LIKE expression, then applies it to all occurrences of
1496 ``"%"``, ``"_"`` and the escape character itself within the
1497 comparison value, which is assumed to be a literal string and not a
1498 SQL expression.
1500 An expression such as::
1502 somecolumn.contains("foo%bar", autoescape=True)
1504 Will render as:
1506 .. sourcecode:: sql
1508 somecolumn LIKE '%' || :param || '%' ESCAPE '/'
1510 With the value of ``:param`` as ``"foo/%bar"``.
1512 :param escape: a character which when given will render with the
1513 ``ESCAPE`` keyword to establish that character as the escape
1514 character. This character can then be placed preceding occurrences
1515 of ``%`` and ``_`` to allow them to act as themselves and not
1516 wildcard characters.
1518 An expression such as::
1520 somecolumn.contains("foo/%bar", escape="^")
1522 Will render as:
1524 .. sourcecode:: sql
1526 somecolumn LIKE '%' || :param || '%' ESCAPE '^'
1528 The parameter may also be combined with
1529 :paramref:`.ColumnOperators.contains.autoescape`::
1531 somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
1533 Where above, the given literal parameter will be converted to
1534 ``"foo^%bar^^bat"`` before being passed to the database.
1536 .. seealso::
1538 :meth:`.ColumnOperators.startswith`
1540 :meth:`.ColumnOperators.endswith`
1542 :meth:`.ColumnOperators.like`
1545 """ # noqa: E501
1546 return self.operate(contains_op, other, **kw)
1548 def icontains(self, other: Any, **kw: Any) -> ColumnOperators:
1549 r"""Implement the ``icontains`` operator, e.g. case insensitive
1550 version of :meth:`.ColumnOperators.contains`.
1552 Produces a LIKE expression that tests against an insensitive match
1553 for the middle of a string value:
1555 .. sourcecode:: sql
1557 lower(column) LIKE '%' || lower(<other>) || '%'
1559 E.g.::
1561 stmt = select(sometable).where(sometable.c.column.icontains("foobar"))
1563 Since the operator uses ``LIKE``, wildcard characters
1564 ``"%"`` and ``"_"`` that are present inside the <other> expression
1565 will behave like wildcards as well. For literal string
1566 values, the :paramref:`.ColumnOperators.icontains.autoescape` flag
1567 may be set to ``True`` to apply escaping to occurrences of these
1568 characters within the string value so that they match as themselves
1569 and not as wildcard characters. Alternatively, the
1570 :paramref:`.ColumnOperators.icontains.escape` parameter will establish
1571 a given character as an escape character which can be of use when
1572 the target expression is not a literal string.
1574 :param other: expression to be compared. This is usually a plain
1575 string value, but can also be an arbitrary SQL expression. LIKE
1576 wildcard characters ``%`` and ``_`` are not escaped by default unless
1577 the :paramref:`.ColumnOperators.icontains.autoescape` flag is
1578 set to True.
1580 :param autoescape: boolean; when True, establishes an escape character
1581 within the LIKE expression, then applies it to all occurrences of
1582 ``"%"``, ``"_"`` and the escape character itself within the
1583 comparison value, which is assumed to be a literal string and not a
1584 SQL expression.
1586 An expression such as::
1588 somecolumn.icontains("foo%bar", autoescape=True)
1590 Will render as:
1592 .. sourcecode:: sql
1594 lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '/'
1596 With the value of ``:param`` as ``"foo/%bar"``.
1598 :param escape: a character which when given will render with the
1599 ``ESCAPE`` keyword to establish that character as the escape
1600 character. This character can then be placed preceding occurrences
1601 of ``%`` and ``_`` to allow them to act as themselves and not
1602 wildcard characters.
1604 An expression such as::
1606 somecolumn.icontains("foo/%bar", escape="^")
1608 Will render as:
1610 .. sourcecode:: sql
1612 lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^'
1614 The parameter may also be combined with
1615 :paramref:`.ColumnOperators.contains.autoescape`::
1617 somecolumn.icontains("foo%bar^bat", escape="^", autoescape=True)
1619 Where above, the given literal parameter will be converted to
1620 ``"foo^%bar^^bat"`` before being passed to the database.
1622 .. seealso::
1624 :meth:`.ColumnOperators.contains`
1626 """ # noqa: E501
1627 return self.operate(icontains_op, other, **kw)
1629 def match(self, other: Any, **kwargs: Any) -> ColumnOperators:
1630 """Implements a database-specific 'match' operator.
1632 :meth:`_sql.ColumnOperators.match` attempts to resolve to
1633 a MATCH-like function or operator provided by the backend.
1634 Examples include:
1636 * PostgreSQL - renders ``x @@ plainto_tsquery(y)``
1638 .. versionchanged:: 2.0 ``plainto_tsquery()`` is used instead
1639 of ``to_tsquery()`` for PostgreSQL now; for compatibility with
1640 other forms, see :ref:`postgresql_match`.
1643 * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)``
1645 .. seealso::
1647 :class:`_mysql.match` - MySQL specific construct with
1648 additional features.
1650 * Oracle Database - renders ``CONTAINS(x, y)``
1651 * other backends may provide special implementations.
1652 * Backends without any special implementation will emit
1653 the operator as "MATCH". This is compatible with SQLite, for
1654 example.
1656 """
1657 return self.operate(match_op, other, **kwargs)
1659 def regexp_match(
1660 self, pattern: Any, flags: Optional[str] = None
1661 ) -> ColumnOperators:
1662 """Implements a database-specific 'regexp match' operator.
1664 E.g.::
1666 stmt = select(table.c.some_column).where(
1667 table.c.some_column.regexp_match("^(b|c)")
1668 )
1670 :meth:`_sql.ColumnOperators.regexp_match` attempts to resolve to
1671 a REGEXP-like function or operator provided by the backend, however
1672 the specific regular expression syntax and flags available are
1673 **not backend agnostic**.
1675 Examples include:
1677 * PostgreSQL - renders ``x ~ y`` or ``x !~ y`` when negated.
1678 * Oracle Database - renders ``REGEXP_LIKE(x, y)``
1679 * SQLite - uses SQLite's ``REGEXP`` placeholder operator and calls into
1680 the Python ``re.match()`` builtin.
1681 * other backends may provide special implementations.
1682 * Backends without any special implementation will emit
1683 the operator as "REGEXP" or "NOT REGEXP". This is compatible with
1684 SQLite and MySQL, for example.
1686 Regular expression support is currently implemented for Oracle
1687 Database, PostgreSQL, MySQL and MariaDB. Partial support is available
1688 for SQLite. Support among third-party dialects may vary.
1690 :param pattern: The regular expression pattern string or column
1691 clause.
1692 :param flags: Any regular expression string flags to apply, passed as
1693 plain Python string only. These flags are backend specific.
1694 Some backends, like PostgreSQL and MariaDB, may alternatively
1695 specify the flags as part of the pattern.
1696 When using the ignore case flag 'i' in PostgreSQL, the ignore case
1697 regexp match operator ``~*`` or ``!~*`` will be used.
1699 .. versionadded:: 1.4
1701 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation
1702 error, the "flags" parameter previously accepted SQL expression
1703 objects such as column expressions in addition to plain Python
1704 strings. This implementation did not work correctly with caching
1705 and was removed; strings only should be passed for the "flags"
1706 parameter, as these flags are rendered as literal inline values
1707 within SQL expressions.
1709 .. seealso::
1711 :meth:`_sql.ColumnOperators.regexp_replace`
1714 """
1715 return self.operate(regexp_match_op, pattern, flags=flags)
1717 def regexp_replace(
1718 self, pattern: Any, replacement: Any, flags: Optional[str] = None
1719 ) -> ColumnOperators:
1720 """Implements a database-specific 'regexp replace' operator.
1722 E.g.::
1724 stmt = select(
1725 table.c.some_column.regexp_replace("b(..)", "X\1Y", flags="g")
1726 )
1728 :meth:`_sql.ColumnOperators.regexp_replace` attempts to resolve to
1729 a REGEXP_REPLACE-like function provided by the backend, that
1730 usually emit the function ``REGEXP_REPLACE()``. However,
1731 the specific regular expression syntax and flags available are
1732 **not backend agnostic**.
1734 Regular expression replacement support is currently implemented for
1735 Oracle Database, PostgreSQL, MySQL 8 or greater and MariaDB. Support
1736 among third-party dialects may vary.
1738 :param pattern: The regular expression pattern string or column
1739 clause.
1740 :param pattern: The replacement string or column clause.
1741 :param flags: Any regular expression string flags to apply, passed as
1742 plain Python string only. These flags are backend specific.
1743 Some backends, like PostgreSQL and MariaDB, may alternatively
1744 specify the flags as part of the pattern.
1746 .. versionadded:: 1.4
1748 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation
1749 error, the "flags" parameter previously accepted SQL expression
1750 objects such as column expressions in addition to plain Python
1751 strings. This implementation did not work correctly with caching
1752 and was removed; strings only should be passed for the "flags"
1753 parameter, as these flags are rendered as literal inline values
1754 within SQL expressions.
1757 .. seealso::
1759 :meth:`_sql.ColumnOperators.regexp_match`
1761 """
1762 return self.operate(
1763 regexp_replace_op,
1764 pattern,
1765 replacement=replacement,
1766 flags=flags,
1767 )
1769 def desc(self) -> ColumnOperators:
1770 """Produce a :func:`_expression.desc` clause against the
1771 parent object."""
1772 return self.operate(desc_op)
1774 def asc(self) -> ColumnOperators:
1775 """Produce a :func:`_expression.asc` clause against the
1776 parent object."""
1777 return self.operate(asc_op)
1779 def nulls_first(self) -> ColumnOperators:
1780 """Produce a :func:`_expression.nulls_first` clause against the
1781 parent object.
1783 .. versionchanged:: 1.4 The ``nulls_first()`` operator is
1784 renamed from ``nullsfirst()`` in previous releases.
1785 The previous name remains available for backwards compatibility.
1786 """
1787 return self.operate(nulls_first_op)
1789 # deprecated 1.4; see #5435
1790 if TYPE_CHECKING:
1792 def nullsfirst(self) -> ColumnOperators: ...
1794 else:
1795 nullsfirst = nulls_first
1797 def nulls_last(self) -> ColumnOperators:
1798 """Produce a :func:`_expression.nulls_last` clause against the
1799 parent object.
1801 .. versionchanged:: 1.4 The ``nulls_last()`` operator is
1802 renamed from ``nullslast()`` in previous releases.
1803 The previous name remains available for backwards compatibility.
1804 """
1805 return self.operate(nulls_last_op)
1807 # deprecated 1.4; see #5429
1808 if TYPE_CHECKING:
1810 def nullslast(self) -> ColumnOperators: ...
1812 else:
1813 nullslast = nulls_last
1815 def collate(self, collation: str) -> ColumnOperators:
1816 """Produce a :func:`_expression.collate` clause against
1817 the parent object, given the collation string.
1819 .. seealso::
1821 :func:`_expression.collate`
1823 """
1824 return self.operate(collate, collation)
1826 def __radd__(self, other: Any) -> ColumnOperators:
1827 """Implement the ``+`` operator in reverse.
1829 See :meth:`.ColumnOperators.__add__`.
1831 """
1832 return self.reverse_operate(add, other)
1834 def __rsub__(self, other: Any) -> ColumnOperators:
1835 """Implement the ``-`` operator in reverse.
1837 See :meth:`.ColumnOperators.__sub__`.
1839 """
1840 return self.reverse_operate(sub, other)
1842 def __rmul__(self, other: Any) -> ColumnOperators:
1843 """Implement the ``*`` operator in reverse.
1845 See :meth:`.ColumnOperators.__mul__`.
1847 """
1848 return self.reverse_operate(mul, other)
1850 def __rmod__(self, other: Any) -> ColumnOperators:
1851 """Implement the ``%`` operator in reverse.
1853 See :meth:`.ColumnOperators.__mod__`.
1855 """
1856 return self.reverse_operate(mod, other)
1858 def between(
1859 self, cleft: Any, cright: Any, symmetric: bool = False
1860 ) -> ColumnOperators:
1861 """Produce a :func:`_expression.between` clause against
1862 the parent object, given the lower and upper range.
1864 """
1865 return self.operate(between_op, cleft, cright, symmetric=symmetric)
1867 def distinct(self) -> ColumnOperators:
1868 """Produce a :func:`_expression.distinct` clause against the
1869 parent object.
1871 """
1872 return self.operate(distinct_op)
1874 def any_(self) -> ColumnOperators:
1875 """Produce an :func:`_expression.any_` clause against the
1876 parent object.
1878 See the documentation for :func:`_sql.any_` for examples.
1880 .. note:: be sure to not confuse the newer
1881 :meth:`_sql.ColumnOperators.any_` method with the **legacy**
1882 version of this method, the :meth:`_types.ARRAY.Comparator.any`
1883 method that's specific to :class:`_types.ARRAY`, which uses a
1884 different calling style.
1886 """
1887 return self.operate(any_op)
1889 def all_(self) -> ColumnOperators:
1890 """Produce an :func:`_expression.all_` clause against the
1891 parent object.
1893 See the documentation for :func:`_sql.all_` for examples.
1895 .. note:: be sure to not confuse the newer
1896 :meth:`_sql.ColumnOperators.all_` method with the **legacy**
1897 version of this method, the :meth:`_types.ARRAY.Comparator.all`
1898 method that's specific to :class:`_types.ARRAY`, which uses a
1899 different calling style.
1901 """
1902 return self.operate(all_op)
1904 def __add__(self, other: Any) -> ColumnOperators:
1905 """Implement the ``+`` operator.
1907 In a column context, produces the clause ``a + b``
1908 if the parent object has non-string affinity.
1909 If the parent object has a string affinity,
1910 produces the concatenation operator, ``a || b`` -
1911 see :meth:`.ColumnOperators.concat`.
1913 """
1914 return self.operate(add, other)
1916 def __sub__(self, other: Any) -> ColumnOperators:
1917 """Implement the ``-`` operator.
1919 In a column context, produces the clause ``a - b``.
1921 """
1922 return self.operate(sub, other)
1924 def __mul__(self, other: Any) -> ColumnOperators:
1925 """Implement the ``*`` operator.
1927 In a column context, produces the clause ``a * b``.
1929 """
1930 return self.operate(mul, other)
1932 def __mod__(self, other: Any) -> ColumnOperators:
1933 """Implement the ``%`` operator.
1935 In a column context, produces the clause ``a % b``.
1937 """
1938 return self.operate(mod, other)
1940 def __truediv__(self, other: Any) -> ColumnOperators:
1941 """Implement the ``/`` operator.
1943 In a column context, produces the clause ``a / b``, and
1944 considers the result type to be numeric.
1946 .. versionchanged:: 2.0 The truediv operator against two integers
1947 is now considered to return a numeric value. Behavior on specific
1948 backends may vary.
1950 """
1951 return self.operate(truediv, other)
1953 def __rtruediv__(self, other: Any) -> ColumnOperators:
1954 """Implement the ``/`` operator in reverse.
1956 See :meth:`.ColumnOperators.__truediv__`.
1958 """
1959 return self.reverse_operate(truediv, other)
1961 def __floordiv__(self, other: Any) -> ColumnOperators:
1962 """Implement the ``//`` operator.
1964 In a column context, produces the clause ``a / b``,
1965 which is the same as "truediv", but considers the result
1966 type to be integer.
1968 .. versionadded:: 2.0
1970 """
1971 return self.operate(floordiv, other)
1973 def __rfloordiv__(self, other: Any) -> ColumnOperators:
1974 """Implement the ``//`` operator in reverse.
1976 See :meth:`.ColumnOperators.__floordiv__`.
1978 """
1979 return self.reverse_operate(floordiv, other)
1981 def __pow__(self, other: Any) -> ColumnOperators:
1982 """Implement the ``**`` operator.
1984 In a column context, produces the clause ``pow(a, b)``, or a similar
1985 dialect-specific expression.
1987 .. versionadded:: 2.1
1989 """
1990 return self.operate(pow_, other)
1992 def __rpow__(self, other: Any) -> ColumnOperators:
1993 """Implement the ``**`` operator in reverse.
1995 .. seealso::
1997 :meth:`.ColumnOperators.__pow__`.
1999 .. versionadded:: 2.1
2001 """
2002 return self.reverse_operate(pow_, other)
2005_commutative: Set[Any] = {eq, ne, add, mul}
2006_comparison: Set[Any] = {eq, ne, lt, gt, ge, le}
2009def _operator_fn(fn: Callable[..., Any]) -> OperatorType:
2010 return cast(OperatorType, fn)
2013def commutative_op(fn: _FN) -> _FN:
2014 _commutative.add(fn)
2015 return fn
2018def comparison_op(fn: _FN) -> _FN:
2019 _comparison.add(fn)
2020 return fn
2023@_operator_fn
2024def from_() -> Any:
2025 raise NotImplementedError()
2028@_operator_fn
2029@comparison_op
2030def function_as_comparison_op() -> Any:
2031 raise NotImplementedError()
2034@_operator_fn
2035def as_() -> Any:
2036 raise NotImplementedError()
2039@_operator_fn
2040def exists() -> Any:
2041 raise NotImplementedError()
2044@_operator_fn
2045def is_true(a: Any) -> Any:
2046 raise NotImplementedError()
2049# 1.4 deprecated; see #5435
2050if TYPE_CHECKING:
2052 @_operator_fn
2053 def istrue(a: Any) -> Any: ...
2055else:
2056 istrue = is_true
2059@_operator_fn
2060def is_false(a: Any) -> Any:
2061 raise NotImplementedError()
2064# 1.4 deprecated; see #5435
2065if TYPE_CHECKING:
2067 @_operator_fn
2068 def isfalse(a: Any) -> Any: ...
2070else:
2071 isfalse = is_false
2074@comparison_op
2075@_operator_fn
2076def is_distinct_from(a: Any, b: Any) -> Any:
2077 return a.is_distinct_from(b)
2080@comparison_op
2081@_operator_fn
2082def is_not_distinct_from(a: Any, b: Any) -> Any:
2083 return a.is_not_distinct_from(b)
2086# deprecated 1.4; see #5435
2087if TYPE_CHECKING:
2089 @_operator_fn
2090 def isnot_distinct_from(a: Any, b: Any) -> Any: ...
2092else:
2093 isnot_distinct_from = is_not_distinct_from
2096@comparison_op
2097@_operator_fn
2098def is_(a: Any, b: Any) -> Any:
2099 return a.is_(b)
2102@comparison_op
2103@_operator_fn
2104def is_not(a: Any, b: Any) -> Any:
2105 return a.is_not(b)
2108# 1.4 deprecated; see #5429
2109if TYPE_CHECKING:
2111 @_operator_fn
2112 def isnot(a: Any, b: Any) -> Any: ...
2114else:
2115 isnot = is_not
2118@_operator_fn
2119def collate(a: Any, b: Any) -> Any:
2120 return a.collate(b)
2123@_operator_fn
2124def op(a: Any, opstring: str, b: Any) -> Any:
2125 return a.op(opstring)(b)
2128@comparison_op
2129@_operator_fn
2130def like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2131 return a.like(b, escape=escape)
2134@comparison_op
2135@_operator_fn
2136def not_like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2137 return a.notlike(b, escape=escape)
2140# 1.4 deprecated; see #5435
2141if TYPE_CHECKING:
2143 @_operator_fn
2144 def notlike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ...
2146else:
2147 notlike_op = not_like_op
2150@comparison_op
2151@_operator_fn
2152def ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2153 return a.ilike(b, escape=escape)
2156@comparison_op
2157@_operator_fn
2158def not_ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2159 return a.not_ilike(b, escape=escape)
2162# 1.4 deprecated; see #5435
2163if TYPE_CHECKING:
2165 @_operator_fn
2166 def notilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ...
2168else:
2169 notilike_op = not_ilike_op
2172@comparison_op
2173@_operator_fn
2174def between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any:
2175 return a.between(b, c, symmetric=symmetric)
2178@comparison_op
2179@_operator_fn
2180def not_between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any:
2181 return ~a.between(b, c, symmetric=symmetric)
2184# 1.4 deprecated; see #5435
2185if TYPE_CHECKING:
2187 @_operator_fn
2188 def notbetween_op(
2189 a: Any, b: Any, c: Any, symmetric: bool = False
2190 ) -> Any: ...
2192else:
2193 notbetween_op = not_between_op
2196@comparison_op
2197@_operator_fn
2198def in_op(a: Any, b: Any) -> Any:
2199 return a.in_(b)
2202@comparison_op
2203@_operator_fn
2204def not_in_op(a: Any, b: Any) -> Any:
2205 return a.not_in(b)
2208# 1.4 deprecated; see #5429
2209if TYPE_CHECKING:
2211 @_operator_fn
2212 def notin_op(a: Any, b: Any) -> Any: ...
2214else:
2215 notin_op = not_in_op
2218@_operator_fn
2219def distinct_op(a: Any) -> Any:
2220 return a.distinct()
2223@_operator_fn
2224def any_op(a: Any) -> Any:
2225 return a.any_()
2228@_operator_fn
2229def all_op(a: Any) -> Any:
2230 return a.all_()
2233def _escaped_like_impl(
2234 fn: Callable[..., Any], other: Any, escape: Optional[str], autoescape: bool
2235) -> Any:
2236 if autoescape:
2237 if autoescape is not True:
2238 util.warn(
2239 "The autoescape parameter is now a simple boolean True/False"
2240 )
2241 if escape is None:
2242 escape = "/"
2244 if not isinstance(other, str):
2245 raise TypeError("String value expected when autoescape=True")
2247 if escape not in ("%", "_"):
2248 other = other.replace(escape, escape + escape)
2250 other = other.replace("%", escape + "%").replace("_", escape + "_")
2252 return fn(other, escape=escape)
2255@comparison_op
2256@_operator_fn
2257def startswith_op(
2258 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2259) -> Any:
2260 return _escaped_like_impl(a.startswith, b, escape, autoescape)
2263@comparison_op
2264@_operator_fn
2265def not_startswith_op(
2266 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2267) -> Any:
2268 return ~_escaped_like_impl(a.startswith, b, escape, autoescape)
2271# 1.4 deprecated; see #5435
2272if TYPE_CHECKING:
2274 @_operator_fn
2275 def notstartswith_op(
2276 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2277 ) -> Any: ...
2279else:
2280 notstartswith_op = not_startswith_op
2283@comparison_op
2284@_operator_fn
2285def istartswith_op(
2286 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2287) -> Any:
2288 return _escaped_like_impl(a.istartswith, b, escape, autoescape)
2291@comparison_op
2292@_operator_fn
2293def not_istartswith_op(
2294 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2295) -> Any:
2296 return ~_escaped_like_impl(a.istartswith, b, escape, autoescape)
2299@comparison_op
2300@_operator_fn
2301def endswith_op(
2302 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2303) -> Any:
2304 return _escaped_like_impl(a.endswith, b, escape, autoescape)
2307@comparison_op
2308@_operator_fn
2309def not_endswith_op(
2310 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2311) -> Any:
2312 return ~_escaped_like_impl(a.endswith, b, escape, autoescape)
2315# 1.4 deprecated; see #5435
2316if TYPE_CHECKING:
2318 @_operator_fn
2319 def notendswith_op(
2320 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2321 ) -> Any: ...
2323else:
2324 notendswith_op = not_endswith_op
2327@comparison_op
2328@_operator_fn
2329def iendswith_op(
2330 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2331) -> Any:
2332 return _escaped_like_impl(a.iendswith, b, escape, autoescape)
2335@comparison_op
2336@_operator_fn
2337def not_iendswith_op(
2338 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2339) -> Any:
2340 return ~_escaped_like_impl(a.iendswith, b, escape, autoescape)
2343@comparison_op
2344@_operator_fn
2345def contains_op(
2346 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2347) -> Any:
2348 return _escaped_like_impl(a.contains, b, escape, autoescape)
2351@comparison_op
2352@_operator_fn
2353def not_contains_op(
2354 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2355) -> Any:
2356 return ~_escaped_like_impl(a.contains, b, escape, autoescape)
2359# 1.4 deprecated; see #5435
2360if TYPE_CHECKING:
2362 @_operator_fn
2363 def notcontains_op(
2364 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2365 ) -> Any: ...
2367else:
2368 notcontains_op = not_contains_op
2371@comparison_op
2372@_operator_fn
2373def icontains_op(
2374 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2375) -> Any:
2376 return _escaped_like_impl(a.icontains, b, escape, autoescape)
2379@comparison_op
2380@_operator_fn
2381def not_icontains_op(
2382 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2383) -> Any:
2384 return ~_escaped_like_impl(a.icontains, b, escape, autoescape)
2387@comparison_op
2388@_operator_fn
2389def match_op(a: Any, b: Any, **kw: Any) -> Any:
2390 return a.match(b, **kw)
2393@comparison_op
2394@_operator_fn
2395def regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any:
2396 return a.regexp_match(b, flags=flags)
2399@comparison_op
2400@_operator_fn
2401def not_regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any:
2402 return ~a.regexp_match(b, flags=flags)
2405@_operator_fn
2406def regexp_replace_op(
2407 a: Any, b: Any, replacement: Any, flags: Optional[str] = None
2408) -> Any:
2409 return a.regexp_replace(b, replacement=replacement, flags=flags)
2412@comparison_op
2413@_operator_fn
2414def not_match_op(a: Any, b: Any, **kw: Any) -> Any:
2415 return ~a.match(b, **kw)
2418# 1.4 deprecated; see #5429
2419if TYPE_CHECKING:
2421 @_operator_fn
2422 def notmatch_op(a: Any, b: Any, **kw: Any) -> Any: ...
2424else:
2425 notmatch_op = not_match_op
2428@_operator_fn
2429def comma_op(a: Any, b: Any) -> Any:
2430 raise NotImplementedError()
2433@_operator_fn
2434def filter_op(a: Any, b: Any) -> Any:
2435 raise NotImplementedError()
2438@_operator_fn
2439def concat_op(a: Any, b: Any) -> Any:
2440 try:
2441 concat = a.concat
2442 except AttributeError:
2443 return b._rconcat(a)
2444 else:
2445 return concat(b)
2448@_operator_fn
2449def desc_op(a: Any) -> Any:
2450 return a.desc()
2453@_operator_fn
2454def asc_op(a: Any) -> Any:
2455 return a.asc()
2458@_operator_fn
2459def nulls_first_op(a: Any) -> Any:
2460 return a.nulls_first()
2463# 1.4 deprecated; see #5435
2464if TYPE_CHECKING:
2466 @_operator_fn
2467 def nullsfirst_op(a: Any) -> Any: ...
2469else:
2470 nullsfirst_op = nulls_first_op
2473@_operator_fn
2474def nulls_last_op(a: Any) -> Any:
2475 return a.nulls_last()
2478# 1.4 deprecated; see #5435
2479if TYPE_CHECKING:
2481 @_operator_fn
2482 def nullslast_op(a: Any) -> Any: ...
2484else:
2485 nullslast_op = nulls_last_op
2488@_operator_fn
2489def json_getitem_op(a: Any, b: Any) -> Any:
2490 raise NotImplementedError()
2493@_operator_fn
2494def json_path_getitem_op(a: Any, b: Any) -> Any:
2495 raise NotImplementedError()
2498@_operator_fn
2499def bitwise_xor_op(a: Any, b: Any) -> Any:
2500 return a.bitwise_xor(b)
2503@_operator_fn
2504def bitwise_or_op(a: Any, b: Any) -> Any:
2505 return a.bitwise_or(b)
2508@_operator_fn
2509def bitwise_and_op(a: Any, b: Any) -> Any:
2510 return a.bitwise_and(b)
2513@_operator_fn
2514def bitwise_not_op(a: Any) -> Any:
2515 return a.bitwise_not()
2518@_operator_fn
2519def bitwise_lshift_op(a: Any, b: Any) -> Any:
2520 return a.bitwise_lshift(b)
2523@_operator_fn
2524def bitwise_rshift_op(a: Any, b: Any) -> Any:
2525 return a.bitwise_rshift(b)
2528def is_comparison(op: OperatorType) -> bool:
2529 return op in _comparison or isinstance(op, custom_op) and op.is_comparison
2532def is_commutative(op: OperatorType) -> bool:
2533 return op in _commutative
2536def is_ordering_modifier(op: OperatorType) -> bool:
2537 return op in (asc_op, desc_op, nulls_first_op, nulls_last_op)
2540def is_natural_self_precedent(op: OperatorType) -> bool:
2541 return (
2542 op in _natural_self_precedent
2543 or isinstance(op, custom_op)
2544 and op.natural_self_precedent
2545 )
2548_booleans = (inv, is_true, is_false, and_, or_)
2551def is_boolean(op: OperatorType) -> bool:
2552 return is_comparison(op) or op in _booleans
2555_mirror = {gt: lt, ge: le, lt: gt, le: ge}
2558def mirror(op: OperatorType) -> OperatorType:
2559 """rotate a comparison operator 180 degrees.
2561 Note this is not the same as negation.
2563 """
2564 return _mirror.get(op, op)
2567_associative = _commutative.union([concat_op, and_, or_]).difference([eq, ne])
2570def is_associative(op: OperatorType) -> bool:
2571 return op in _associative
2574def is_order_by_modifier(op: Optional[OperatorType]) -> bool:
2575 return op in _order_by_modifier
2578_order_by_modifier = {desc_op, asc_op, nulls_first_op, nulls_last_op}
2580_natural_self_precedent = _associative.union(
2581 [getitem, json_getitem_op, json_path_getitem_op]
2582)
2583"""Operators where if we have (a op b) op c, we don't want to
2584parenthesize (a op b).
2586"""
2589@_operator_fn
2590def _asbool(a: Any) -> Any:
2591 raise NotImplementedError()
2594class _OpLimit(IntEnum):
2595 _smallest = -100
2596 _largest = 100
2599_PRECEDENCE: Dict[OperatorType, int] = {
2600 from_: 15,
2601 function_as_comparison_op: 15,
2602 any_op: 15,
2603 all_op: 15,
2604 getitem: 15,
2605 json_getitem_op: 15,
2606 json_path_getitem_op: 15,
2607 pow_: 15,
2608 mul: 8,
2609 truediv: 8,
2610 floordiv: 8,
2611 mod: 8,
2612 neg: 8,
2613 bitwise_not_op: 8,
2614 add: 7,
2615 sub: 7,
2616 bitwise_xor_op: 7,
2617 bitwise_or_op: 7,
2618 bitwise_and_op: 7,
2619 bitwise_lshift_op: 7,
2620 bitwise_rshift_op: 7,
2621 filter_op: 6,
2622 concat_op: 5,
2623 match_op: 5,
2624 not_match_op: 5,
2625 regexp_match_op: 5,
2626 not_regexp_match_op: 5,
2627 regexp_replace_op: 5,
2628 ilike_op: 5,
2629 not_ilike_op: 5,
2630 like_op: 5,
2631 not_like_op: 5,
2632 in_op: 5,
2633 not_in_op: 5,
2634 is_: 5,
2635 is_not: 5,
2636 eq: 5,
2637 ne: 5,
2638 is_distinct_from: 5,
2639 is_not_distinct_from: 5,
2640 gt: 5,
2641 lt: 5,
2642 ge: 5,
2643 le: 5,
2644 between_op: 5,
2645 not_between_op: 5,
2646 distinct_op: 5,
2647 inv: 5,
2648 is_true: 5,
2649 is_false: 5,
2650 and_: 3,
2651 or_: 2,
2652 comma_op: -1,
2653 desc_op: 3,
2654 asc_op: 3,
2655 collate: 4,
2656 as_: -1,
2657 exists: 0,
2658 _asbool: -10,
2659}
2662def is_precedent(
2663 operator: OperatorType, against: Optional[OperatorType]
2664) -> bool:
2665 if operator is against and is_natural_self_precedent(operator):
2666 return False
2667 elif against is None:
2668 return True
2669 else:
2670 return bool(
2671 _PRECEDENCE.get(
2672 operator, getattr(operator, "precedence", _OpLimit._smallest)
2673 )
2674 <= _PRECEDENCE.get(
2675 against, getattr(against, "precedence", _OpLimit._largest)
2676 )
2677 )