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 auto
16from enum import Flag
17from enum import IntEnum
18from operator import add as _uncast_add
19from operator import and_ as _uncast_and_
20from operator import contains as _uncast_contains
21from operator import eq as _uncast_eq
22from operator import floordiv as _uncast_floordiv
23from operator import ge as _uncast_ge
24from operator import getitem as _uncast_getitem
25from operator import gt as _uncast_gt
26from operator import inv as _uncast_inv
27from operator import le as _uncast_le
28from operator import lshift as _uncast_lshift
29from operator import lt as _uncast_lt
30from operator import matmul as _uncast_matmul
31from operator import mod as _uncast_mod
32from operator import mul as _uncast_mul
33from operator import ne as _uncast_ne
34from operator import neg as _uncast_neg
35from operator import or_ as _uncast_or_
36from operator import pow as _uncast_pow
37from operator import rshift as _uncast_rshift
38from operator import sub as _uncast_sub
39from operator import truediv as _uncast_truediv
40import typing
41from typing import Any
42from typing import Callable
43from typing import cast
44from typing import Dict
45from typing import Generic
46from typing import Hashable
47from typing import Literal
48from typing import Optional
49from typing import overload
50from typing import Protocol
51from typing import Set
52from typing import Tuple
53from typing import Type
54from typing import TYPE_CHECKING
55from typing import TypeVar
56from typing import Union
58from .. import exc
59from .. import util
61if typing.TYPE_CHECKING:
62 from ._typing import ColumnExpressionArgument
63 from .cache_key import CacheConst
64 from .elements import ColumnElement
65 from .type_api import TypeEngine
67_T = TypeVar("_T", bound=Any)
68_FN = TypeVar("_FN", bound=Callable[..., Any])
71class OperatorClass(Flag):
72 """Describes a class of SQLAlchemy built-in operators that should be
73 available on a particular type.
75 The :class:`.OperatorClass` should be present on the
76 :attr:`.TypeEngine.operator_classes` attribute of any particular type.
78 The enums here can be ORed together to provide sets of operators merged
79 together.
81 .. versionadded:: 2.1
83 """
85 UNSPECIFIED = auto()
86 BASE = auto()
87 BOOLEAN_ALGEBRA = auto()
88 COMPARISON = auto()
89 INDEXABLE = auto() # noqa: F811
90 CONTAINS = auto()
91 CONCATENABLE = auto()
92 STRING_MATCH = auto()
93 MATH = auto()
94 BITWISE = auto()
95 DATE_ARITHEMETIC = auto()
96 JSON_GETITEM = auto()
98 STRING = (
99 BASE | COMPARISON | STRING_MATCH | CONTAINS | CONCATENABLE | INDEXABLE
100 )
101 INTEGER = BASE | COMPARISON | MATH | BITWISE
102 NUMERIC = BASE | COMPARISON | MATH | BITWISE
103 BOOLEAN = BASE | COMPARISON | BOOLEAN_ALGEBRA | COMPARISON
104 BINARY = BASE | COMPARISON | CONTAINS | CONCATENABLE | INDEXABLE
105 DATETIME = BASE | COMPARISON | DATE_ARITHEMETIC
106 JSON = BASE | COMPARISON | INDEXABLE | JSON_GETITEM
107 ARRAY = BASE | COMPARISON | CONTAINS | CONCATENABLE | INDEXABLE
108 TUPLE = BASE | COMPARISON | CONTAINS | CONCATENABLE | INDEXABLE
110 ANY = (
111 STRING
112 | INTEGER
113 | NUMERIC
114 | BOOLEAN
115 | DATETIME
116 | BINARY
117 | JSON
118 | ARRAY
119 | TUPLE
120 )
123class OperatorType(Hashable, Protocol):
124 """describe an op() function."""
126 __slots__ = ()
128 __name__: str
130 @overload
131 def __call__(
132 self,
133 left: ColumnExpressionArgument[Any],
134 right: Optional[Any] = None,
135 *other: Any,
136 **kwargs: Any,
137 ) -> ColumnElement[Any]: ...
139 @overload
140 def __call__(
141 self,
142 left: Operators,
143 right: Optional[Any] = None,
144 *other: Any,
145 **kwargs: Any,
146 ) -> Operators: ...
148 def __call__(
149 self,
150 left: Any,
151 right: Optional[Any] = None,
152 *other: Any,
153 **kwargs: Any,
154 ) -> Operators: ...
157add = cast(OperatorType, _uncast_add)
158and_ = cast(OperatorType, _uncast_and_)
159contains = cast(OperatorType, _uncast_contains)
160eq = cast(OperatorType, _uncast_eq)
161floordiv = cast(OperatorType, _uncast_floordiv)
162ge = cast(OperatorType, _uncast_ge)
163getitem = cast(OperatorType, _uncast_getitem)
164gt = cast(OperatorType, _uncast_gt)
165inv = cast(OperatorType, _uncast_inv)
166le = cast(OperatorType, _uncast_le)
167lshift = cast(OperatorType, _uncast_lshift)
168lt = cast(OperatorType, _uncast_lt)
169matmul = cast(OperatorType, _uncast_matmul)
170mod = cast(OperatorType, _uncast_mod)
171mul = cast(OperatorType, _uncast_mul)
172ne = cast(OperatorType, _uncast_ne)
173neg = cast(OperatorType, _uncast_neg)
174or_ = cast(OperatorType, _uncast_or_)
175pow_ = cast(OperatorType, _uncast_pow)
176rshift = cast(OperatorType, _uncast_rshift)
177sub = cast(OperatorType, _uncast_sub)
178truediv = cast(OperatorType, _uncast_truediv)
181class Operators:
182 """Base of comparison and logical operators.
184 Implements base methods
185 :meth:`~sqlalchemy.sql.operators.Operators.operate` and
186 :meth:`~sqlalchemy.sql.operators.Operators.reverse_operate`, as well as
187 :meth:`~sqlalchemy.sql.operators.Operators.__and__`,
188 :meth:`~sqlalchemy.sql.operators.Operators.__or__`,
189 :meth:`~sqlalchemy.sql.operators.Operators.__invert__`.
191 Usually is used via its most common subclass
192 :class:`.ColumnOperators`.
194 """
196 __slots__ = ()
198 def __and__(self, other: Any) -> Operators:
199 """Implement the ``&`` operator.
201 When used with SQL expressions, results in an
202 AND operation, equivalent to
203 :func:`_expression.and_`, that is::
205 a & b
207 is equivalent to::
209 from sqlalchemy import and_
211 and_(a, b)
213 Care should be taken when using ``&`` regarding
214 operator precedence; the ``&`` operator has the highest precedence.
215 The operands should be enclosed in parenthesis if they contain
216 further sub expressions::
218 (a == 2) & (b == 4)
220 """
221 return self.operate(and_, other)
223 def __or__(self, other: Any) -> Operators:
224 """Implement the ``|`` operator.
226 When used with SQL expressions, results in an
227 OR operation, equivalent to
228 :func:`_expression.or_`, that is::
230 a | b
232 is equivalent to::
234 from sqlalchemy import or_
236 or_(a, b)
238 Care should be taken when using ``|`` regarding
239 operator precedence; the ``|`` operator has the highest precedence.
240 The operands should be enclosed in parenthesis if they contain
241 further sub expressions::
243 (a == 2) | (b == 4)
245 """
246 return self.operate(or_, other)
248 def __invert__(self) -> Operators:
249 """Implement the ``~`` operator.
251 When used with SQL expressions, results in a
252 NOT operation, equivalent to
253 :func:`_expression.not_`, that is::
255 ~a
257 is equivalent to::
259 from sqlalchemy import not_
261 not_(a)
263 """
264 return self.operate(inv)
266 def _null_operate(self, other: Any) -> Operators:
267 """A 'null' operation available on all types, used for testing."""
269 return self.operate(null_op, other)
271 def op(
272 self,
273 opstring: str,
274 precedence: int = 0,
275 is_comparison: bool = False,
276 return_type: Optional[
277 Union[Type[TypeEngine[Any]], TypeEngine[Any]]
278 ] = None,
279 python_impl: Optional[Callable[..., Any]] = None,
280 operator_class: OperatorClass = OperatorClass.BASE,
281 visit_name: Optional[str] = None,
282 ) -> Callable[[Any], Operators]:
283 """Produce a generic operator function.
285 e.g.::
287 somecolumn.op("*")(5)
289 produces::
291 somecolumn * 5
293 This function can also be used to make bitwise operators explicit. For
294 example::
296 somecolumn.op("&")(0xFF)
298 is a bitwise AND of the value in ``somecolumn``.
300 :param opstring: a string which will be output as the infix operator
301 between this element and the expression passed to the
302 generated function.
304 :param precedence: precedence which the database is expected to apply
305 to the operator in SQL expressions. This integer value acts as a hint
306 for the SQL compiler to know when explicit parenthesis should be
307 rendered around a particular operation. A lower number will cause the
308 expression to be parenthesized when applied against another operator
309 with higher precedence. The default value of ``0`` is lower than all
310 operators except for the comma (``,``) and ``AS`` operators. A value
311 of 100 will be higher or equal to all operators, and -100 will be
312 lower than or equal to all operators.
314 .. seealso::
316 :ref:`faq_sql_expression_op_parenthesis` - detailed description
317 of how the SQLAlchemy SQL compiler renders parenthesis
319 :param is_comparison: legacy; if True, the operator will be considered
320 as a "comparison" operator, that is which evaluates to a boolean
321 true/false value, like ``==``, ``>``, etc. This flag is provided
322 so that ORM relationships can establish that the operator is a
323 comparison operator when used in a custom join condition.
325 Using the ``is_comparison`` parameter is superseded by using the
326 :meth:`.Operators.bool_op` method instead; this more succinct
327 operator sets this parameter automatically, but also provides
328 correct :pep:`484` typing support as the returned object will
329 express a "boolean" datatype, i.e. ``BinaryExpression[bool]``.
331 :param return_type: a :class:`.TypeEngine` class or object that will
332 force the return type of an expression produced by this operator
333 to be of that type. By default, operators that specify
334 :paramref:`.Operators.op.is_comparison` will resolve to
335 :class:`.Boolean`, and those that do not will be of the same
336 type as the left-hand operand.
338 :param python_impl: an optional Python function that can evaluate
339 two Python values in the same way as this operator works when
340 run on the database server. Useful for in-Python SQL expression
341 evaluation functions, such as for ORM hybrid attributes, and the
342 ORM "evaluator" used to match objects in a session after a multi-row
343 update or delete.
345 e.g.::
347 >>> expr = column("x").op("+", python_impl=lambda a, b: a + b)("y")
349 The operator for the above expression will also work for non-SQL
350 left and right objects::
352 >>> expr.operator(5, 10)
353 15
355 .. versionadded:: 2.0
357 :param operator_class: optional :class:`.OperatorClass` which will be
358 applied to the :class:`.custom_op` created, which provides hints
359 as to which datatypes are appropriate for this operator. Defaults
360 to :attr:`.OperatorClass.BASE` which is appropriate for all
361 datatypes.
363 .. versionadded:: 2.1
365 .. seealso::
367 :meth:`.Operators.bool_op`
369 :ref:`types_operators`
371 :ref:`relationship_custom_operator`
373 :param visit_name: string name indicating a series of methods that
374 maybe be implemented by a :class:`.Dialect`, specifically using its
375 :class:`.SQLCompiler` implementation. The naming scheme is
376 ``visit_<visit_name>_op_[binary|unary]``; e.g. using the visit name
377 ``hstore`` means that a binary expression using the operator will
378 attempt to locate a method ``visit_hstore_op_binary()`` on the
379 target dialect's compiler class, which can then provide a compilation
380 string for the full binary expression.
382 .. versionadded:: 2.1
384 """
385 operator = custom_op(
386 opstring,
387 precedence=precedence,
388 is_comparison=is_comparison,
389 return_type=return_type,
390 python_impl=python_impl,
391 operator_class=operator_class,
392 visit_name=visit_name,
393 )
395 def against(other: Any) -> Operators:
396 return operator(self, other)
398 return against
400 def bool_op(
401 self,
402 opstring: str,
403 precedence: int = 0,
404 python_impl: Optional[Callable[..., Any]] = None,
405 ) -> Callable[[Any], Operators]:
406 """Return a custom boolean operator.
408 This method is shorthand for calling
409 :meth:`.Operators.op` and passing the
410 :paramref:`.Operators.op.is_comparison`
411 flag with True. A key advantage to using :meth:`.Operators.bool_op`
412 is that when using column constructs, the "boolean" nature of the
413 returned expression will be present for :pep:`484` purposes.
415 .. seealso::
417 :meth:`.Operators.op`
419 """
420 return self.op(
421 opstring,
422 precedence=precedence,
423 is_comparison=True,
424 python_impl=python_impl,
425 )
427 def operate(
428 self, op: OperatorType, *other: Any, **kwargs: Any
429 ) -> Operators:
430 r"""Operate on an argument.
432 This is the lowest level of operation, raises
433 :class:`NotImplementedError` by default.
435 Overriding this on a subclass can allow common
436 behavior to be applied to all operations.
437 For example, overriding :class:`.ColumnOperators`
438 to apply ``func.lower()`` to the left and right
439 side::
441 class MyComparator(ColumnOperators):
442 def operate(self, op, other, **kwargs):
443 return op(func.lower(self), func.lower(other), **kwargs)
445 :param op: Operator callable.
446 :param \*other: the 'other' side of the operation. Will
447 be a single scalar for most operations.
448 :param \**kwargs: modifiers. These may be passed by special
449 operators such as :meth:`ColumnOperators.contains`.
452 """
453 raise NotImplementedError(str(op))
455 __sa_operate__ = operate
457 def reverse_operate(
458 self, op: OperatorType, other: Any, **kwargs: Any
459 ) -> Operators:
460 """Reverse operate on an argument.
462 Usage is the same as :meth:`operate`.
464 """
465 raise NotImplementedError(str(op))
468class custom_op(OperatorType, Generic[_T]):
469 """Represent a 'custom' operator.
471 :class:`.custom_op` is normally instantiated when the
472 :meth:`.Operators.op` or :meth:`.Operators.bool_op` methods
473 are used to create a custom operator callable. The class can also be
474 used directly when programmatically constructing expressions. E.g.
475 to represent the "factorial" operation::
477 from sqlalchemy.sql import UnaryExpression
478 from sqlalchemy.sql import operators
479 from sqlalchemy import Numeric
481 unary = UnaryExpression(
482 table.c.somecolumn, modifier=operators.custom_op("!"), type_=Numeric
483 )
485 .. seealso::
487 :meth:`.Operators.op`
489 :meth:`.Operators.bool_op`
491 """ # noqa: E501
493 __name__ = "custom_op"
495 __slots__ = (
496 "opstring",
497 "precedence",
498 "is_comparison",
499 "natural_self_precedent",
500 "eager_grouping",
501 "return_type",
502 "python_impl",
503 "operator_class",
504 "visit_name",
505 )
507 def __init__(
508 self,
509 opstring: str,
510 *,
511 precedence: int = 0,
512 is_comparison: bool = False,
513 return_type: Optional[
514 Union[Type[TypeEngine[_T]], TypeEngine[_T]]
515 ] = None,
516 natural_self_precedent: bool = False,
517 eager_grouping: bool = False,
518 python_impl: Optional[Callable[..., Any]] = None,
519 operator_class: OperatorClass = OperatorClass.BASE,
520 visit_name: Optional[str] = None,
521 ):
522 """Create a new :class:`.custom_op`.
524 See :meth:`.Operators.op` for parameter information.
526 """
527 self.opstring = opstring
528 self.precedence = precedence
529 self.is_comparison = is_comparison
530 self.natural_self_precedent = natural_self_precedent
531 self.eager_grouping = eager_grouping
532 self.return_type = (
533 return_type._to_instance(return_type) if return_type else None
534 )
535 self.python_impl = python_impl
536 self.operator_class = operator_class
537 self.visit_name = visit_name
539 def __eq__(self, other: Any) -> bool:
540 return (
541 isinstance(other, custom_op)
542 and other._hash_key() == self._hash_key()
543 )
545 def __hash__(self) -> int:
546 return hash(self._hash_key())
548 def _hash_key(self) -> Union[CacheConst, Tuple[Any, ...]]:
549 return (
550 self.__class__,
551 self.opstring,
552 self.precedence,
553 self.is_comparison,
554 self.natural_self_precedent,
555 self.eager_grouping,
556 self.return_type._static_cache_key if self.return_type else None,
557 self.operator_class,
558 )
560 @overload
561 def __call__(
562 self,
563 left: ColumnExpressionArgument[Any],
564 right: Optional[Any] = None,
565 *other: Any,
566 **kwargs: Any,
567 ) -> ColumnElement[Any]: ...
569 @overload
570 def __call__(
571 self,
572 left: Operators,
573 right: Optional[Any] = None,
574 *other: Any,
575 **kwargs: Any,
576 ) -> Operators: ...
578 def __call__(
579 self,
580 left: Any,
581 right: Optional[Any] = None,
582 *other: Any,
583 **kwargs: Any,
584 ) -> Operators:
585 if hasattr(left, "__sa_operate__"):
586 return left.operate(self, right, *other, **kwargs) # type: ignore
587 elif self.python_impl:
588 return self.python_impl(left, right, *other, **kwargs) # type: ignore # noqa: E501
589 else:
590 raise exc.InvalidRequestError(
591 f"Custom operator {self.opstring!r} can't be used with "
592 "plain Python objects unless it includes the "
593 "'python_impl' parameter."
594 )
597class OrderingOperators(Operators):
598 """defines ORDER BY operators, which can operate as single expressions
599 or comma-separated lists
601 """
603 __slots__ = ()
605 if typing.TYPE_CHECKING:
607 def operate(
608 self, op: OperatorType, *other: Any, **kwargs: Any
609 ) -> OrderingOperators: ...
611 def reverse_operate(
612 self, op: OperatorType, other: Any, **kwargs: Any
613 ) -> OrderingOperators: ...
615 def desc(self) -> OrderingOperators:
616 """Produce a :func:`_expression.desc` clause against the
617 parent object."""
618 return self.operate(desc_op)
620 def asc(self) -> OrderingOperators:
621 """Produce a :func:`_expression.asc` clause against the
622 parent object."""
623 return self.operate(asc_op)
625 def nulls_first(self) -> OrderingOperators:
626 """Produce a :func:`_expression.nulls_first` clause against the
627 parent object.
629 .. versionchanged:: 1.4 The ``nulls_first()`` operator is
630 renamed from ``nullsfirst()`` in previous releases.
631 The previous name remains available for backwards compatibility.
632 """
633 return self.operate(nulls_first_op)
635 # deprecated 1.4; see #5435
636 if TYPE_CHECKING:
638 def nullsfirst(self) -> OrderingOperators: ...
640 else:
641 nullsfirst = nulls_first
643 def nulls_last(self) -> OrderingOperators:
644 """Produce a :func:`_expression.nulls_last` clause against the
645 parent object.
647 .. versionchanged:: 1.4 The ``nulls_last()`` operator is
648 renamed from ``nullslast()`` in previous releases.
649 The previous name remains available for backwards compatibility.
650 """
651 return self.operate(nulls_last_op)
653 # deprecated 1.4; see #5429
654 if TYPE_CHECKING:
656 def nullslast(self) -> OrderingOperators: ...
658 else:
659 nullslast = nulls_last
662class ColumnOperators(OrderingOperators):
663 """Defines boolean, comparison, and other operators for
664 :class:`_expression.ColumnElement` expressions.
666 By default, all methods call down to
667 :meth:`.operate` or :meth:`.reverse_operate`,
668 passing in the appropriate operator function from the
669 Python builtin ``operator`` module or
670 a SQLAlchemy-specific operator function from
671 :mod:`sqlalchemy.expression.operators`. For example
672 the ``__eq__`` function::
674 def __eq__(self, other):
675 return self.operate(operators.eq, other)
677 Where ``operators.eq`` is essentially::
679 def eq(a, b):
680 return a == b
682 The core column expression unit :class:`_expression.ColumnElement`
683 overrides :meth:`.Operators.operate` and others
684 to return further :class:`_expression.ColumnElement` constructs,
685 so that the ``==`` operation above is replaced by a clause
686 construct.
688 .. seealso::
690 :ref:`types_operators`
692 :attr:`.TypeEngine.comparator_factory`
694 :class:`.ColumnOperators`
696 :class:`.PropComparator`
698 """
700 __slots__ = ()
702 timetuple: Literal[None] = None
703 """Hack, allows datetime objects to be compared on the LHS."""
705 if typing.TYPE_CHECKING:
707 def operate(
708 self, op: OperatorType, *other: Any, **kwargs: Any
709 ) -> ColumnOperators: ...
711 def reverse_operate(
712 self, op: OperatorType, other: Any, **kwargs: Any
713 ) -> ColumnOperators: ...
715 def __lt__(self, other: Any) -> ColumnOperators:
716 """Implement the ``<`` operator.
718 In a column context, produces the clause ``a < b``.
720 """
721 return self.operate(lt, other)
723 def __le__(self, other: Any) -> ColumnOperators:
724 """Implement the ``<=`` operator.
726 In a column context, produces the clause ``a <= b``.
728 """
729 return self.operate(le, other)
731 # ColumnOperators defines an __eq__ so it must explicitly declare also
732 # an hash or it's set to None by python:
733 # https://docs.python.org/3/reference/datamodel.html#object.__hash__
734 if TYPE_CHECKING:
736 def __hash__(self) -> int: ...
738 else:
739 __hash__ = Operators.__hash__
741 def __eq__(self, other: Any) -> ColumnOperators: # type: ignore[override]
742 """Implement the ``==`` operator.
744 In a column context, produces the clause ``a = b``.
745 If the target is ``None``, produces ``a IS NULL``.
747 """
748 return self.operate(eq, other)
750 def __ne__(self, other: Any) -> ColumnOperators: # type: ignore[override]
751 """Implement the ``!=`` operator.
753 In a column context, produces the clause ``a != b``.
754 If the target is ``None``, produces ``a IS NOT NULL``.
756 """
757 return self.operate(ne, other)
759 def is_distinct_from(self, other: Any) -> ColumnOperators:
760 """Implement the ``IS DISTINCT FROM`` operator.
762 Renders "a IS DISTINCT FROM b" on most platforms;
763 on some such as SQLite may render "a IS NOT b".
765 """
766 return self.operate(is_distinct_from, other)
768 def is_not_distinct_from(self, other: Any) -> ColumnOperators:
769 """Implement the ``IS NOT DISTINCT FROM`` operator.
771 Renders "a IS NOT DISTINCT FROM b" on most platforms;
772 on some such as SQLite may render "a IS b".
774 .. versionchanged:: 1.4 The ``is_not_distinct_from()`` operator is
775 renamed from ``isnot_distinct_from()`` in previous releases.
776 The previous name remains available for backwards compatibility.
778 """
779 return self.operate(is_not_distinct_from, other)
781 # deprecated 1.4; see #5435
782 if TYPE_CHECKING:
784 def isnot_distinct_from(self, other: Any) -> ColumnOperators: ...
786 else:
787 isnot_distinct_from = is_not_distinct_from
789 def __gt__(self, other: Any) -> ColumnOperators:
790 """Implement the ``>`` operator.
792 In a column context, produces the clause ``a > b``.
794 """
795 return self.operate(gt, other)
797 def __ge__(self, other: Any) -> ColumnOperators:
798 """Implement the ``>=`` operator.
800 In a column context, produces the clause ``a >= b``.
802 """
803 return self.operate(ge, other)
805 def __neg__(self) -> ColumnOperators:
806 """Implement the ``-`` operator.
808 In a column context, produces the clause ``-a``.
810 """
811 return self.operate(neg)
813 def __contains__(self, other: Any) -> ColumnOperators:
814 return self.operate(contains, other)
816 def __getitem__(self, index: Any) -> ColumnOperators:
817 """Implement the [] operator.
819 This can be used by some database-specific types
820 such as PostgreSQL ARRAY and HSTORE.
822 """
823 return self.operate(getitem, index)
825 def __lshift__(self, other: Any) -> ColumnOperators:
826 """Implement the ``<<`` operator.
828 Not used by SQLAlchemy core, this is provided
829 for custom operator systems which want to use
830 << as an extension point.
831 """
832 return self.operate(lshift, other)
834 def __rlshift__(self, other: Any) -> ColumnOperators:
835 """Implement the ``<<`` operator in reverse.
837 Not used by SQLAlchemy core, this is provided
838 for custom operator systems which want to use
839 << as an extension point.
840 """
841 return self.reverse_operate(lshift, other)
843 def __rshift__(self, other: Any) -> ColumnOperators:
844 """Implement the ``>>`` operator.
846 Not used by SQLAlchemy core, this is provided
847 for custom operator systems which want to use
848 >> as an extension point.
849 """
850 return self.operate(rshift, other)
852 def __rrshift__(self, other: Any) -> ColumnOperators:
853 """Implement the ``>>`` operator in reverse.
855 Not used by SQLAlchemy core, this is provided
856 for custom operator systems which want to use
857 >> as an extension point.
858 """
859 return self.reverse_operate(rshift, other)
861 def __matmul__(self, other: Any) -> ColumnOperators:
862 """Implement the ``@`` operator.
864 Not used by SQLAlchemy core, this is provided
865 for custom operator systems which want to use
866 @ as an extension point.
867 """
868 return self.operate(matmul, other)
870 def __rmatmul__(self, other: Any) -> ColumnOperators:
871 """Implement the ``@`` operator in reverse.
873 Not used by SQLAlchemy core, this is provided
874 for custom operator systems which want to use
875 @ as an extension point.
876 """
877 return self.reverse_operate(matmul, other)
879 def concat(self, other: Any) -> ColumnOperators:
880 """Implement the 'concat' operator.
882 In a column context, produces the clause ``a || b``,
883 or uses the ``concat()`` operator on MySQL.
885 """
886 return self.operate(concat_op, other)
888 def _rconcat(self, other: Any) -> ColumnOperators:
889 """Implement an 'rconcat' operator.
891 this is for internal use at the moment
893 .. versionadded:: 1.4.40
895 """
896 return self.reverse_operate(concat_op, other)
898 def like(
899 self, other: Any, escape: Optional[str] = None
900 ) -> ColumnOperators:
901 r"""Implement the ``like`` operator.
903 In a column context, produces the expression:
905 .. sourcecode:: sql
907 a LIKE other
909 E.g.::
911 stmt = select(sometable).where(sometable.c.column.like("%foobar%"))
913 :param other: expression to be compared
914 :param escape: optional escape character, renders the ``ESCAPE``
915 keyword, e.g.::
917 somecolumn.like("foo/%bar", escape="/")
919 .. seealso::
921 :meth:`.ColumnOperators.ilike`
923 """
924 return self.operate(like_op, other, escape=escape)
926 def ilike(
927 self, other: Any, escape: Optional[str] = None
928 ) -> ColumnOperators:
929 r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE.
931 In a column context, produces an expression either of the form:
933 .. sourcecode:: sql
935 lower(a) LIKE lower(other)
937 Or on backends that support the ILIKE operator:
939 .. sourcecode:: sql
941 a ILIKE other
943 E.g.::
945 stmt = select(sometable).where(sometable.c.column.ilike("%foobar%"))
947 :param other: expression to be compared
948 :param escape: optional escape character, renders the ``ESCAPE``
949 keyword, e.g.::
951 somecolumn.ilike("foo/%bar", escape="/")
953 .. seealso::
955 :meth:`.ColumnOperators.like`
957 """ # noqa: E501
958 return self.operate(ilike_op, other, escape=escape)
960 def bitwise_xor(self, other: Any) -> ColumnOperators:
961 """Produce a bitwise XOR operation, typically via the ``^``
962 operator, or ``#`` for PostgreSQL.
964 .. versionadded:: 2.0.2
966 .. seealso::
968 :ref:`operators_bitwise`
970 """
972 return self.operate(bitwise_xor_op, other)
974 def bitwise_or(self, other: Any) -> ColumnOperators:
975 """Produce a bitwise OR operation, typically via the ``|``
976 operator.
978 .. versionadded:: 2.0.2
980 .. seealso::
982 :ref:`operators_bitwise`
984 """
986 return self.operate(bitwise_or_op, other)
988 def bitwise_and(self, other: Any) -> ColumnOperators:
989 """Produce a bitwise AND operation, typically via the ``&``
990 operator.
992 .. versionadded:: 2.0.2
994 .. seealso::
996 :ref:`operators_bitwise`
998 """
1000 return self.operate(bitwise_and_op, other)
1002 def bitwise_not(self) -> ColumnOperators:
1003 """Produce a bitwise NOT operation, typically via the ``~``
1004 operator.
1006 .. versionadded:: 2.0.2
1008 .. seealso::
1010 :ref:`operators_bitwise`
1012 """
1014 return self.operate(bitwise_not_op)
1016 def bitwise_lshift(self, other: Any) -> ColumnOperators:
1017 """Produce a bitwise LSHIFT operation, typically via the ``<<``
1018 operator.
1020 .. versionadded:: 2.0.2
1022 .. seealso::
1024 :ref:`operators_bitwise`
1026 """
1028 return self.operate(bitwise_lshift_op, other)
1030 def bitwise_rshift(self, other: Any) -> ColumnOperators:
1031 """Produce a bitwise RSHIFT operation, typically via the ``>>``
1032 operator.
1034 .. versionadded:: 2.0.2
1036 .. seealso::
1038 :ref:`operators_bitwise`
1040 """
1042 return self.operate(bitwise_rshift_op, other)
1044 def in_(self, other: Any) -> ColumnOperators:
1045 """Implement the ``in`` operator.
1047 In a column context, produces the clause ``column IN <other>``.
1049 The given parameter ``other`` may be:
1051 * A list of literal values,
1052 e.g.::
1054 stmt.where(column.in_([1, 2, 3]))
1056 In this calling form, the list of items is converted to a set of
1057 bound parameters the same length as the list given:
1059 .. sourcecode:: sql
1061 WHERE COL IN (?, ?, ?)
1063 * A list of tuples may be provided if the comparison is against a
1064 :func:`.tuple_` containing multiple expressions::
1066 from sqlalchemy import tuple_
1068 stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
1070 * An empty list,
1071 e.g.::
1073 stmt.where(column.in_([]))
1075 In this calling form, the expression renders an "empty set"
1076 expression. These expressions are tailored to individual backends
1077 and are generally trying to get an empty SELECT statement as a
1078 subquery. Such as on SQLite, the expression is:
1080 .. sourcecode:: sql
1082 WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
1084 .. versionchanged:: 1.4 empty IN expressions now use an
1085 execution-time generated SELECT subquery in all cases.
1087 * A bound parameter, e.g. :func:`.bindparam`, may be used if it
1088 includes the :paramref:`.bindparam.expanding` flag::
1090 stmt.where(column.in_(bindparam("value", expanding=True)))
1092 In this calling form, the expression renders a special non-SQL
1093 placeholder expression that looks like:
1095 .. sourcecode:: sql
1097 WHERE COL IN ([EXPANDING_value])
1099 This placeholder expression is intercepted at statement execution
1100 time to be converted into the variable number of bound parameter
1101 form illustrated earlier. If the statement were executed as::
1103 connection.execute(stmt, {"value": [1, 2, 3]})
1105 The database would be passed a bound parameter for each value:
1107 .. sourcecode:: sql
1109 WHERE COL IN (?, ?, ?)
1111 If an empty list is passed, a special "empty list" expression,
1112 which is specific to the database in use, is rendered. On
1113 SQLite this would be:
1115 .. sourcecode:: sql
1117 WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
1119 * a :func:`_expression.select` construct, which is usually a
1120 correlated scalar select::
1122 stmt.where(
1123 column.in_(select(othertable.c.y).where(table.c.x == othertable.c.x))
1124 )
1126 In this calling form, :meth:`.ColumnOperators.in_` renders as given:
1128 .. sourcecode:: sql
1130 WHERE COL IN (SELECT othertable.y
1131 FROM othertable WHERE othertable.x = table.x)
1133 :param other: a list of literals, a :func:`_expression.select`
1134 construct, or a :func:`.bindparam` construct that includes the
1135 :paramref:`.bindparam.expanding` flag set to True.
1137 """ # noqa: E501
1138 return self.operate(in_op, other)
1140 def not_in(self, other: Any) -> ColumnOperators:
1141 """implement the ``NOT IN`` operator.
1143 This is equivalent to using negation with
1144 :meth:`.ColumnOperators.in_`, i.e. ``~x.in_(y)``.
1146 In the case that ``other`` is an empty sequence, the compiler
1147 produces an "empty not in" expression. This defaults to the
1148 expression "1 = 1" to produce true in all cases. The
1149 :paramref:`_sa.create_engine.empty_in_strategy` may be used to
1150 alter this behavior.
1152 .. versionchanged:: 1.4 The ``not_in()`` operator is renamed from
1153 ``notin_()`` in previous releases. The previous name remains
1154 available for backwards compatibility.
1156 .. seealso::
1158 :meth:`.ColumnOperators.in_`
1160 """
1161 return self.operate(not_in_op, other)
1163 # deprecated 1.4; see #5429
1164 if TYPE_CHECKING:
1166 def notin_(self, other: Any) -> ColumnOperators: ...
1168 else:
1169 notin_ = not_in
1171 def not_like(
1172 self, other: Any, escape: Optional[str] = None
1173 ) -> ColumnOperators:
1174 """implement the ``NOT LIKE`` operator.
1176 This is equivalent to using negation with
1177 :meth:`.ColumnOperators.like`, i.e. ``~x.like(y)``.
1179 .. versionchanged:: 1.4 The ``not_like()`` operator is renamed from
1180 ``notlike()`` in previous releases. The previous name remains
1181 available for backwards compatibility.
1183 .. seealso::
1185 :meth:`.ColumnOperators.like`
1187 """
1188 return self.operate(not_like_op, other, escape=escape)
1190 # deprecated 1.4; see #5435
1191 if TYPE_CHECKING:
1193 def notlike(
1194 self, other: Any, escape: Optional[str] = None
1195 ) -> ColumnOperators: ...
1197 else:
1198 notlike = not_like
1200 def not_ilike(
1201 self, other: Any, escape: Optional[str] = None
1202 ) -> ColumnOperators:
1203 """implement the ``NOT ILIKE`` operator.
1205 This is equivalent to using negation with
1206 :meth:`.ColumnOperators.ilike`, i.e. ``~x.ilike(y)``.
1208 .. versionchanged:: 1.4 The ``not_ilike()`` operator is renamed from
1209 ``notilike()`` in previous releases. The previous name remains
1210 available for backwards compatibility.
1212 .. seealso::
1214 :meth:`.ColumnOperators.ilike`
1216 """
1217 return self.operate(not_ilike_op, other, escape=escape)
1219 # deprecated 1.4; see #5435
1220 if TYPE_CHECKING:
1222 def notilike(
1223 self, other: Any, escape: Optional[str] = None
1224 ) -> ColumnOperators: ...
1226 else:
1227 notilike = not_ilike
1229 def is_(self, other: Any) -> ColumnOperators:
1230 """Implement the ``IS`` operator.
1232 Normally, ``IS`` is generated automatically when comparing to a
1233 value of ``None``, which resolves to ``NULL``. However, explicit
1234 usage of ``IS`` may be desirable if comparing to boolean values
1235 on certain platforms.
1237 .. seealso:: :meth:`.ColumnOperators.is_not`
1239 """
1240 return self.operate(is_, other)
1242 def is_not(self, other: Any) -> ColumnOperators:
1243 """Implement the ``IS NOT`` operator.
1245 Normally, ``IS NOT`` is generated automatically when comparing to a
1246 value of ``None``, which resolves to ``NULL``. However, explicit
1247 usage of ``IS NOT`` may be desirable if comparing to boolean values
1248 on certain platforms.
1250 .. versionchanged:: 1.4 The ``is_not()`` operator is renamed from
1251 ``isnot()`` in previous releases. The previous name remains
1252 available for backwards compatibility.
1254 .. seealso:: :meth:`.ColumnOperators.is_`
1256 """
1257 return self.operate(is_not, other)
1259 # deprecated 1.4; see #5429
1260 if TYPE_CHECKING:
1262 def isnot(self, other: Any) -> ColumnOperators: ...
1264 else:
1265 isnot = is_not
1267 def startswith(
1268 self,
1269 other: Any,
1270 escape: Optional[str] = None,
1271 autoescape: bool = False,
1272 ) -> ColumnOperators:
1273 r"""Implement the ``startswith`` operator.
1275 Produces a LIKE expression that tests against a match for the start
1276 of a string value:
1278 .. sourcecode:: sql
1280 column LIKE <other> || '%'
1282 E.g.::
1284 stmt = select(sometable).where(sometable.c.column.startswith("foobar"))
1286 Since the operator uses ``LIKE``, wildcard characters
1287 ``"%"`` and ``"_"`` that are present inside the <other> expression
1288 will behave like wildcards as well. For literal string
1289 values, the :paramref:`.ColumnOperators.startswith.autoescape` flag
1290 may be set to ``True`` to apply escaping to occurrences of these
1291 characters within the string value so that they match as themselves
1292 and not as wildcard characters. Alternatively, the
1293 :paramref:`.ColumnOperators.startswith.escape` parameter will establish
1294 a given character as an escape character which can be of use when
1295 the target expression is not a literal string.
1297 :param other: expression to be compared. This is usually a plain
1298 string value, but can also be an arbitrary SQL expression. LIKE
1299 wildcard characters ``%`` and ``_`` are not escaped by default unless
1300 the :paramref:`.ColumnOperators.startswith.autoescape` flag is
1301 set to True.
1303 :param autoescape: boolean; when True, establishes an escape character
1304 within the LIKE expression, then applies it to all occurrences of
1305 ``"%"``, ``"_"`` and the escape character itself within the
1306 comparison value, which is assumed to be a literal string and not a
1307 SQL expression.
1309 An expression such as::
1311 somecolumn.startswith("foo%bar", autoescape=True)
1313 Will render as:
1315 .. sourcecode:: sql
1317 somecolumn LIKE :param || '%' ESCAPE '/'
1319 With the value of ``:param`` as ``"foo/%bar"``.
1321 :param escape: a character which when given will render with the
1322 ``ESCAPE`` keyword to establish that character as the escape
1323 character. This character can then be placed preceding occurrences
1324 of ``%`` and ``_`` to allow them to act as themselves and not
1325 wildcard characters.
1327 An expression such as::
1329 somecolumn.startswith("foo/%bar", escape="^")
1331 Will render as:
1333 .. sourcecode:: sql
1335 somecolumn LIKE :param || '%' ESCAPE '^'
1337 The parameter may also be combined with
1338 :paramref:`.ColumnOperators.startswith.autoescape`::
1340 somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
1342 Where above, the given literal parameter will be converted to
1343 ``"foo^%bar^^bat"`` before being passed to the database.
1345 .. seealso::
1347 :meth:`.ColumnOperators.endswith`
1349 :meth:`.ColumnOperators.contains`
1351 :meth:`.ColumnOperators.like`
1353 """ # noqa: E501
1354 return self.operate(
1355 startswith_op, other, escape=escape, autoescape=autoescape
1356 )
1358 def istartswith(
1359 self,
1360 other: Any,
1361 escape: Optional[str] = None,
1362 autoescape: bool = False,
1363 ) -> ColumnOperators:
1364 r"""Implement the ``istartswith`` operator, e.g. case insensitive
1365 version of :meth:`.ColumnOperators.startswith`.
1367 Produces a LIKE expression that tests against an insensitive
1368 match for the start of a string value:
1370 .. sourcecode:: sql
1372 lower(column) LIKE lower(<other>) || '%'
1374 E.g.::
1376 stmt = select(sometable).where(sometable.c.column.istartswith("foobar"))
1378 Since the operator uses ``LIKE``, wildcard characters
1379 ``"%"`` and ``"_"`` that are present inside the <other> expression
1380 will behave like wildcards as well. For literal string
1381 values, the :paramref:`.ColumnOperators.istartswith.autoescape` flag
1382 may be set to ``True`` to apply escaping to occurrences of these
1383 characters within the string value so that they match as themselves
1384 and not as wildcard characters. Alternatively, the
1385 :paramref:`.ColumnOperators.istartswith.escape` parameter will
1386 establish a given character as an escape character which can be of
1387 use when the target expression is not a literal string.
1389 :param other: expression to be compared. This is usually a plain
1390 string value, but can also be an arbitrary SQL expression. LIKE
1391 wildcard characters ``%`` and ``_`` are not escaped by default unless
1392 the :paramref:`.ColumnOperators.istartswith.autoescape` flag is
1393 set to True.
1395 :param autoescape: boolean; when True, establishes an escape character
1396 within the LIKE expression, then applies it to all occurrences of
1397 ``"%"``, ``"_"`` and the escape character itself within the
1398 comparison value, which is assumed to be a literal string and not a
1399 SQL expression.
1401 An expression such as::
1403 somecolumn.istartswith("foo%bar", autoescape=True)
1405 Will render as:
1407 .. sourcecode:: sql
1409 lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/'
1411 With the value of ``:param`` as ``"foo/%bar"``.
1413 :param escape: a character which when given will render with the
1414 ``ESCAPE`` keyword to establish that character as the escape
1415 character. This character can then be placed preceding occurrences
1416 of ``%`` and ``_`` to allow them to act as themselves and not
1417 wildcard characters.
1419 An expression such as::
1421 somecolumn.istartswith("foo/%bar", escape="^")
1423 Will render as:
1425 .. sourcecode:: sql
1427 lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^'
1429 The parameter may also be combined with
1430 :paramref:`.ColumnOperators.istartswith.autoescape`::
1432 somecolumn.istartswith("foo%bar^bat", escape="^", autoescape=True)
1434 Where above, the given literal parameter will be converted to
1435 ``"foo^%bar^^bat"`` before being passed to the database.
1437 .. seealso::
1439 :meth:`.ColumnOperators.startswith`
1440 """ # noqa: E501
1441 return self.operate(
1442 istartswith_op, other, escape=escape, autoescape=autoescape
1443 )
1445 def endswith(
1446 self,
1447 other: Any,
1448 escape: Optional[str] = None,
1449 autoescape: bool = False,
1450 ) -> ColumnOperators:
1451 r"""Implement the 'endswith' operator.
1453 Produces a LIKE expression that tests against a match for the end
1454 of a string value:
1456 .. sourcecode:: sql
1458 column LIKE '%' || <other>
1460 E.g.::
1462 stmt = select(sometable).where(sometable.c.column.endswith("foobar"))
1464 Since the operator uses ``LIKE``, wildcard characters
1465 ``"%"`` and ``"_"`` that are present inside the <other> expression
1466 will behave like wildcards as well. For literal string
1467 values, the :paramref:`.ColumnOperators.endswith.autoescape` flag
1468 may be set to ``True`` to apply escaping to occurrences of these
1469 characters within the string value so that they match as themselves
1470 and not as wildcard characters. Alternatively, the
1471 :paramref:`.ColumnOperators.endswith.escape` parameter will establish
1472 a given character as an escape character which can be of use when
1473 the target expression is not a literal string.
1475 :param other: expression to be compared. This is usually a plain
1476 string value, but can also be an arbitrary SQL expression. LIKE
1477 wildcard characters ``%`` and ``_`` are not escaped by default unless
1478 the :paramref:`.ColumnOperators.endswith.autoescape` flag is
1479 set to True.
1481 :param autoescape: boolean; when True, establishes an escape character
1482 within the LIKE expression, then applies it to all occurrences of
1483 ``"%"``, ``"_"`` and the escape character itself within the
1484 comparison value, which is assumed to be a literal string and not a
1485 SQL expression.
1487 An expression such as::
1489 somecolumn.endswith("foo%bar", autoescape=True)
1491 Will render as:
1493 .. sourcecode:: sql
1495 somecolumn LIKE '%' || :param ESCAPE '/'
1497 With the value of ``:param`` as ``"foo/%bar"``.
1499 :param escape: a character which when given will render with the
1500 ``ESCAPE`` keyword to establish that character as the escape
1501 character. This character can then be placed preceding occurrences
1502 of ``%`` and ``_`` to allow them to act as themselves and not
1503 wildcard characters.
1505 An expression such as::
1507 somecolumn.endswith("foo/%bar", escape="^")
1509 Will render as:
1511 .. sourcecode:: sql
1513 somecolumn LIKE '%' || :param ESCAPE '^'
1515 The parameter may also be combined with
1516 :paramref:`.ColumnOperators.endswith.autoescape`::
1518 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
1520 Where above, the given literal parameter will be converted to
1521 ``"foo^%bar^^bat"`` before being passed to the database.
1523 .. seealso::
1525 :meth:`.ColumnOperators.startswith`
1527 :meth:`.ColumnOperators.contains`
1529 :meth:`.ColumnOperators.like`
1531 """ # noqa: E501
1532 return self.operate(
1533 endswith_op, other, escape=escape, autoescape=autoescape
1534 )
1536 def iendswith(
1537 self,
1538 other: Any,
1539 escape: Optional[str] = None,
1540 autoescape: bool = False,
1541 ) -> ColumnOperators:
1542 r"""Implement the ``iendswith`` operator, e.g. case insensitive
1543 version of :meth:`.ColumnOperators.endswith`.
1545 Produces a LIKE expression that tests against an insensitive match
1546 for the end of a string value:
1548 .. sourcecode:: sql
1550 lower(column) LIKE '%' || lower(<other>)
1552 E.g.::
1554 stmt = select(sometable).where(sometable.c.column.iendswith("foobar"))
1556 Since the operator uses ``LIKE``, wildcard characters
1557 ``"%"`` and ``"_"`` that are present inside the <other> expression
1558 will behave like wildcards as well. For literal string
1559 values, the :paramref:`.ColumnOperators.iendswith.autoescape` flag
1560 may be set to ``True`` to apply escaping to occurrences of these
1561 characters within the string value so that they match as themselves
1562 and not as wildcard characters. Alternatively, the
1563 :paramref:`.ColumnOperators.iendswith.escape` parameter will establish
1564 a given character as an escape character which can be of use when
1565 the target expression is not a literal string.
1567 :param other: expression to be compared. This is usually a plain
1568 string value, but can also be an arbitrary SQL expression. LIKE
1569 wildcard characters ``%`` and ``_`` are not escaped by default unless
1570 the :paramref:`.ColumnOperators.iendswith.autoescape` flag is
1571 set to True.
1573 :param autoescape: boolean; when True, establishes an escape character
1574 within the LIKE expression, then applies it to all occurrences of
1575 ``"%"``, ``"_"`` and the escape character itself within the
1576 comparison value, which is assumed to be a literal string and not a
1577 SQL expression.
1579 An expression such as::
1581 somecolumn.iendswith("foo%bar", autoescape=True)
1583 Will render as:
1585 .. sourcecode:: sql
1587 lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '/'
1589 With the value of ``:param`` as ``"foo/%bar"``.
1591 :param escape: a character which when given will render with the
1592 ``ESCAPE`` keyword to establish that character as the escape
1593 character. This character can then be placed preceding occurrences
1594 of ``%`` and ``_`` to allow them to act as themselves and not
1595 wildcard characters.
1597 An expression such as::
1599 somecolumn.iendswith("foo/%bar", escape="^")
1601 Will render as:
1603 .. sourcecode:: sql
1605 lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^'
1607 The parameter may also be combined with
1608 :paramref:`.ColumnOperators.iendswith.autoescape`::
1610 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
1612 Where above, the given literal parameter will be converted to
1613 ``"foo^%bar^^bat"`` before being passed to the database.
1615 .. seealso::
1617 :meth:`.ColumnOperators.endswith`
1618 """ # noqa: E501
1619 return self.operate(
1620 iendswith_op, other, escape=escape, autoescape=autoescape
1621 )
1623 def contains(self, other: Any, **kw: Any) -> ColumnOperators:
1624 r"""Implement the 'contains' operator.
1626 Produces a LIKE expression that tests against a match for the middle
1627 of a string value:
1629 .. sourcecode:: sql
1631 column LIKE '%' || <other> || '%'
1633 E.g.::
1635 stmt = select(sometable).where(sometable.c.column.contains("foobar"))
1637 Since the operator uses ``LIKE``, wildcard characters
1638 ``"%"`` and ``"_"`` that are present inside the <other> expression
1639 will behave like wildcards as well. For literal string
1640 values, the :paramref:`.ColumnOperators.contains.autoescape` flag
1641 may be set to ``True`` to apply escaping to occurrences of these
1642 characters within the string value so that they match as themselves
1643 and not as wildcard characters. Alternatively, the
1644 :paramref:`.ColumnOperators.contains.escape` parameter will establish
1645 a given character as an escape character which can be of use when
1646 the target expression is not a literal string.
1648 :param other: expression to be compared. This is usually a plain
1649 string value, but can also be an arbitrary SQL expression. LIKE
1650 wildcard characters ``%`` and ``_`` are not escaped by default unless
1651 the :paramref:`.ColumnOperators.contains.autoescape` flag is
1652 set to True.
1654 :param autoescape: boolean; when True, establishes an escape character
1655 within the LIKE expression, then applies it to all occurrences of
1656 ``"%"``, ``"_"`` and the escape character itself within the
1657 comparison value, which is assumed to be a literal string and not a
1658 SQL expression.
1660 An expression such as::
1662 somecolumn.contains("foo%bar", autoescape=True)
1664 Will render as:
1666 .. sourcecode:: sql
1668 somecolumn LIKE '%' || :param || '%' ESCAPE '/'
1670 With the value of ``:param`` as ``"foo/%bar"``.
1672 :param escape: a character which when given will render with the
1673 ``ESCAPE`` keyword to establish that character as the escape
1674 character. This character can then be placed preceding occurrences
1675 of ``%`` and ``_`` to allow them to act as themselves and not
1676 wildcard characters.
1678 An expression such as::
1680 somecolumn.contains("foo/%bar", escape="^")
1682 Will render as:
1684 .. sourcecode:: sql
1686 somecolumn LIKE '%' || :param || '%' ESCAPE '^'
1688 The parameter may also be combined with
1689 :paramref:`.ColumnOperators.contains.autoescape`::
1691 somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
1693 Where above, the given literal parameter will be converted to
1694 ``"foo^%bar^^bat"`` before being passed to the database.
1696 .. seealso::
1698 :meth:`.ColumnOperators.startswith`
1700 :meth:`.ColumnOperators.endswith`
1702 :meth:`.ColumnOperators.like`
1705 """ # noqa: E501
1706 return self.operate(contains_op, other, **kw)
1708 def icontains(self, other: Any, **kw: Any) -> ColumnOperators:
1709 r"""Implement the ``icontains`` operator, e.g. case insensitive
1710 version of :meth:`.ColumnOperators.contains`.
1712 Produces a LIKE expression that tests against an insensitive match
1713 for the middle of a string value:
1715 .. sourcecode:: sql
1717 lower(column) LIKE '%' || lower(<other>) || '%'
1719 E.g.::
1721 stmt = select(sometable).where(sometable.c.column.icontains("foobar"))
1723 Since the operator uses ``LIKE``, wildcard characters
1724 ``"%"`` and ``"_"`` that are present inside the <other> expression
1725 will behave like wildcards as well. For literal string
1726 values, the :paramref:`.ColumnOperators.icontains.autoescape` flag
1727 may be set to ``True`` to apply escaping to occurrences of these
1728 characters within the string value so that they match as themselves
1729 and not as wildcard characters. Alternatively, the
1730 :paramref:`.ColumnOperators.icontains.escape` parameter will establish
1731 a given character as an escape character which can be of use when
1732 the target expression is not a literal string.
1734 :param other: expression to be compared. This is usually a plain
1735 string value, but can also be an arbitrary SQL expression. LIKE
1736 wildcard characters ``%`` and ``_`` are not escaped by default unless
1737 the :paramref:`.ColumnOperators.icontains.autoescape` flag is
1738 set to True.
1740 :param autoescape: boolean; when True, establishes an escape character
1741 within the LIKE expression, then applies it to all occurrences of
1742 ``"%"``, ``"_"`` and the escape character itself within the
1743 comparison value, which is assumed to be a literal string and not a
1744 SQL expression.
1746 An expression such as::
1748 somecolumn.icontains("foo%bar", autoescape=True)
1750 Will render as:
1752 .. sourcecode:: sql
1754 lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '/'
1756 With the value of ``:param`` as ``"foo/%bar"``.
1758 :param escape: a character which when given will render with the
1759 ``ESCAPE`` keyword to establish that character as the escape
1760 character. This character can then be placed preceding occurrences
1761 of ``%`` and ``_`` to allow them to act as themselves and not
1762 wildcard characters.
1764 An expression such as::
1766 somecolumn.icontains("foo/%bar", escape="^")
1768 Will render as:
1770 .. sourcecode:: sql
1772 lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^'
1774 The parameter may also be combined with
1775 :paramref:`.ColumnOperators.contains.autoescape`::
1777 somecolumn.icontains("foo%bar^bat", escape="^", autoescape=True)
1779 Where above, the given literal parameter will be converted to
1780 ``"foo^%bar^^bat"`` before being passed to the database.
1782 .. seealso::
1784 :meth:`.ColumnOperators.contains`
1786 """ # noqa: E501
1787 return self.operate(icontains_op, other, **kw)
1789 def match(self, other: Any, **kwargs: Any) -> ColumnOperators:
1790 """Implements a database-specific 'match' operator.
1792 :meth:`_sql.ColumnOperators.match` attempts to resolve to
1793 a MATCH-like function or operator provided by the backend.
1794 Examples include:
1796 * PostgreSQL - renders ``x @@ plainto_tsquery(y)``
1798 .. versionchanged:: 2.0 ``plainto_tsquery()`` is used instead
1799 of ``to_tsquery()`` for PostgreSQL now; for compatibility with
1800 other forms, see :ref:`postgresql_match`.
1803 * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)``
1805 .. seealso::
1807 :class:`_mysql.match` - MySQL specific construct with
1808 additional features.
1810 * Oracle Database - renders ``CONTAINS(x, y)``
1811 * other backends may provide special implementations.
1812 * Backends without any special implementation will emit
1813 the operator as "MATCH". This is compatible with SQLite, for
1814 example.
1816 """
1817 return self.operate(match_op, other, **kwargs)
1819 def regexp_match(
1820 self, pattern: Any, flags: Optional[str] = None
1821 ) -> ColumnOperators:
1822 """Implements a database-specific 'regexp match' operator.
1824 E.g.::
1826 stmt = select(table.c.some_column).where(
1827 table.c.some_column.regexp_match("^(b|c)")
1828 )
1830 :meth:`_sql.ColumnOperators.regexp_match` attempts to resolve to
1831 a REGEXP-like function or operator provided by the backend, however
1832 the specific regular expression syntax and flags available are
1833 **not backend agnostic**.
1835 Examples include:
1837 * PostgreSQL - renders ``x ~ y`` or ``x !~ y`` when negated.
1838 * Oracle Database - renders ``REGEXP_LIKE(x, y)``
1839 * SQLite - uses SQLite's ``REGEXP`` placeholder operator and calls into
1840 the Python ``re.match()`` builtin.
1841 * other backends may provide special implementations.
1842 * Backends without any special implementation will emit
1843 the operator as "REGEXP" or "NOT REGEXP". This is compatible with
1844 SQLite and MySQL, for example.
1846 Regular expression support is currently implemented for Oracle
1847 Database, PostgreSQL, MySQL and MariaDB. Partial support is available
1848 for SQLite. Support among third-party dialects may vary.
1850 :param pattern: The regular expression pattern string or column
1851 clause.
1852 :param flags: Any regular expression string flags to apply, passed as
1853 plain Python string only. These flags are backend specific.
1854 Some backends, like PostgreSQL and MariaDB, may alternatively
1855 specify the flags as part of the pattern.
1856 When using the ignore case flag 'i' in PostgreSQL, the ignore case
1857 regexp match operator ``~*`` or ``!~*`` will be used.
1859 .. versionadded:: 1.4
1861 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation
1862 error, the "flags" parameter previously accepted SQL expression
1863 objects such as column expressions in addition to plain Python
1864 strings. This implementation did not work correctly with caching
1865 and was removed; strings only should be passed for the "flags"
1866 parameter, as these flags are rendered as literal inline values
1867 within SQL expressions.
1869 .. seealso::
1871 :meth:`_sql.ColumnOperators.regexp_replace`
1874 """
1875 return self.operate(regexp_match_op, pattern, flags=flags)
1877 def regexp_replace(
1878 self, pattern: Any, replacement: Any, flags: Optional[str] = None
1879 ) -> ColumnOperators:
1880 """Implements a database-specific 'regexp replace' operator.
1882 E.g.::
1884 stmt = select(
1885 table.c.some_column.regexp_replace("b(..)", "X\1Y", flags="g")
1886 )
1888 :meth:`_sql.ColumnOperators.regexp_replace` attempts to resolve to
1889 a REGEXP_REPLACE-like function provided by the backend, that
1890 usually emit the function ``REGEXP_REPLACE()``. However,
1891 the specific regular expression syntax and flags available are
1892 **not backend agnostic**.
1894 Regular expression replacement support is currently implemented for
1895 Oracle Database, PostgreSQL, MySQL 8 or greater and MariaDB. Support
1896 among third-party dialects may vary.
1898 :param pattern: The regular expression pattern string or column
1899 clause.
1900 :param pattern: The replacement string or column clause.
1901 :param flags: Any regular expression string flags to apply, passed as
1902 plain Python string only. These flags are backend specific.
1903 Some backends, like PostgreSQL and MariaDB, may alternatively
1904 specify the flags as part of the pattern.
1906 .. versionadded:: 1.4
1908 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation
1909 error, the "flags" parameter previously accepted SQL expression
1910 objects such as column expressions in addition to plain Python
1911 strings. This implementation did not work correctly with caching
1912 and was removed; strings only should be passed for the "flags"
1913 parameter, as these flags are rendered as literal inline values
1914 within SQL expressions.
1917 .. seealso::
1919 :meth:`_sql.ColumnOperators.regexp_match`
1921 """
1922 return self.operate(
1923 regexp_replace_op,
1924 pattern,
1925 replacement=replacement,
1926 flags=flags,
1927 )
1929 def collate(self, collation: str) -> ColumnOperators:
1930 """Produce a :func:`_expression.collate` clause against
1931 the parent object, given the collation string.
1933 .. seealso::
1935 :func:`_expression.collate`
1937 """
1938 return self.operate(collate, collation)
1940 def __radd__(self, other: Any) -> ColumnOperators:
1941 """Implement the ``+`` operator in reverse.
1943 See :meth:`.ColumnOperators.__add__`.
1945 """
1946 return self.reverse_operate(add, other)
1948 def __rsub__(self, other: Any) -> ColumnOperators:
1949 """Implement the ``-`` operator in reverse.
1951 See :meth:`.ColumnOperators.__sub__`.
1953 """
1954 return self.reverse_operate(sub, other)
1956 def __rmul__(self, other: Any) -> ColumnOperators:
1957 """Implement the ``*`` operator in reverse.
1959 See :meth:`.ColumnOperators.__mul__`.
1961 """
1962 return self.reverse_operate(mul, other)
1964 def __rmod__(self, other: Any) -> ColumnOperators:
1965 """Implement the ``%`` operator in reverse.
1967 See :meth:`.ColumnOperators.__mod__`.
1969 """
1970 return self.reverse_operate(mod, other)
1972 def between(
1973 self, cleft: Any, cright: Any, symmetric: bool = False
1974 ) -> ColumnOperators:
1975 """Produce a :func:`_expression.between` clause against
1976 the parent object, given the lower and upper range.
1978 """
1979 return self.operate(between_op, cleft, cright, symmetric=symmetric)
1981 def distinct(self) -> ColumnOperators:
1982 """Produce a :func:`_expression.distinct` clause against the
1983 parent object.
1985 """
1986 return self.operate(distinct_op)
1988 def any_(self) -> ColumnOperators:
1989 """Produce an :func:`_expression.any_` clause against the
1990 parent object.
1992 See the documentation for :func:`_sql.any_` for examples.
1994 .. note:: be sure to not confuse the newer
1995 :meth:`_sql.ColumnOperators.any_` method with the **legacy**
1996 version of this method, the :meth:`_types.ARRAY.Comparator.any`
1997 method that's specific to :class:`_types.ARRAY`, which uses a
1998 different calling style.
2000 """
2001 return self.operate(any_op)
2003 def all_(self) -> ColumnOperators:
2004 """Produce an :func:`_expression.all_` clause against the
2005 parent object.
2007 See the documentation for :func:`_sql.all_` for examples.
2009 .. note:: be sure to not confuse the newer
2010 :meth:`_sql.ColumnOperators.all_` method with the **legacy**
2011 version of this method, the :meth:`_types.ARRAY.Comparator.all`
2012 method that's specific to :class:`_types.ARRAY`, which uses a
2013 different calling style.
2015 """
2016 return self.operate(all_op)
2018 def __add__(self, other: Any) -> ColumnOperators:
2019 """Implement the ``+`` operator.
2021 In a column context, produces the clause ``a + b``
2022 if the parent object has non-string affinity.
2023 If the parent object has a string affinity,
2024 produces the concatenation operator, ``a || b`` -
2025 see :meth:`.ColumnOperators.concat`.
2027 """
2028 return self.operate(add, other)
2030 def __sub__(self, other: Any) -> ColumnOperators:
2031 """Implement the ``-`` operator.
2033 In a column context, produces the clause ``a - b``.
2035 """
2036 return self.operate(sub, other)
2038 def __mul__(self, other: Any) -> ColumnOperators:
2039 """Implement the ``*`` operator.
2041 In a column context, produces the clause ``a * b``.
2043 """
2044 return self.operate(mul, other)
2046 def __mod__(self, other: Any) -> ColumnOperators:
2047 """Implement the ``%`` operator.
2049 In a column context, produces the clause ``a % b``.
2051 """
2052 return self.operate(mod, other)
2054 def __truediv__(self, other: Any) -> ColumnOperators:
2055 """Implement the ``/`` operator.
2057 In a column context, produces the clause ``a / b``, and
2058 considers the result type to be numeric.
2060 .. versionchanged:: 2.0 The truediv operator against two integers
2061 is now considered to return a numeric value. Behavior on specific
2062 backends may vary.
2064 """
2065 return self.operate(truediv, other)
2067 def __rtruediv__(self, other: Any) -> ColumnOperators:
2068 """Implement the ``/`` operator in reverse.
2070 See :meth:`.ColumnOperators.__truediv__`.
2072 """
2073 return self.reverse_operate(truediv, other)
2075 def __floordiv__(self, other: Any) -> ColumnOperators:
2076 """Implement the ``//`` operator.
2078 In a column context, produces the clause ``a / b``,
2079 which is the same as "truediv", but considers the result
2080 type to be integer.
2082 .. versionadded:: 2.0
2084 """
2085 return self.operate(floordiv, other)
2087 def __rfloordiv__(self, other: Any) -> ColumnOperators:
2088 """Implement the ``//`` operator in reverse.
2090 See :meth:`.ColumnOperators.__floordiv__`.
2092 """
2093 return self.reverse_operate(floordiv, other)
2095 def __pow__(self, other: Any) -> ColumnOperators:
2096 """Implement the ``**`` operator.
2098 In a column context, produces the clause ``pow(a, b)``, or a similar
2099 dialect-specific expression.
2101 .. versionadded:: 2.1
2103 """
2104 return self.operate(pow_, other)
2106 def __rpow__(self, other: Any) -> ColumnOperators:
2107 """Implement the ``**`` operator in reverse.
2109 .. seealso::
2111 :meth:`.ColumnOperators.__pow__`.
2113 .. versionadded:: 2.1
2115 """
2116 return self.reverse_operate(pow_, other)
2119_commutative: Set[Any] = {eq, ne, add, mul}
2120_comparison: Set[Any] = {eq, ne, lt, gt, ge, le}
2123def _operator_fn(fn: Callable[..., Any]) -> OperatorType:
2124 return cast(OperatorType, fn)
2127def commutative_op(fn: _FN) -> _FN:
2128 _commutative.add(fn)
2129 return fn
2132def comparison_op(fn: _FN) -> _FN:
2133 _comparison.add(fn)
2134 return fn
2137@_operator_fn
2138def from_() -> Any:
2139 raise NotImplementedError()
2142@_operator_fn
2143@comparison_op
2144def function_as_comparison_op() -> Any:
2145 raise NotImplementedError()
2148@_operator_fn
2149def as_() -> Any:
2150 raise NotImplementedError()
2153@_operator_fn
2154def exists() -> Any:
2155 raise NotImplementedError()
2158@_operator_fn
2159def is_true(a: Any) -> Any:
2160 raise NotImplementedError()
2163# 1.4 deprecated; see #5435
2164if TYPE_CHECKING:
2166 @_operator_fn
2167 def istrue(a: Any) -> Any: ...
2169else:
2170 istrue = is_true
2173@_operator_fn
2174def is_false(a: Any) -> Any:
2175 raise NotImplementedError()
2178# 1.4 deprecated; see #5435
2179if TYPE_CHECKING:
2181 @_operator_fn
2182 def isfalse(a: Any) -> Any: ...
2184else:
2185 isfalse = is_false
2188@comparison_op
2189@_operator_fn
2190def is_distinct_from(a: Any, b: Any) -> Any:
2191 return a.is_distinct_from(b)
2194@comparison_op
2195@_operator_fn
2196def is_not_distinct_from(a: Any, b: Any) -> Any:
2197 return a.is_not_distinct_from(b)
2200# deprecated 1.4; see #5435
2201if TYPE_CHECKING:
2203 @_operator_fn
2204 def isnot_distinct_from(a: Any, b: Any) -> Any: ...
2206else:
2207 isnot_distinct_from = is_not_distinct_from
2210@comparison_op
2211@_operator_fn
2212def is_(a: Any, b: Any) -> Any:
2213 return a.is_(b)
2216@comparison_op
2217@_operator_fn
2218def is_not(a: Any, b: Any) -> Any:
2219 return a.is_not(b)
2222# 1.4 deprecated; see #5429
2223if TYPE_CHECKING:
2225 @_operator_fn
2226 def isnot(a: Any, b: Any) -> Any: ...
2228else:
2229 isnot = is_not
2232@_operator_fn
2233def collate(a: Any, b: Any) -> Any:
2234 return a.collate(b)
2237@_operator_fn
2238def op(a: Any, opstring: str, b: Any) -> Any:
2239 return a.op(opstring)(b)
2242@comparison_op
2243@_operator_fn
2244def like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2245 return a.like(b, escape=escape)
2248@comparison_op
2249@_operator_fn
2250def not_like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2251 return a.notlike(b, escape=escape)
2254# 1.4 deprecated; see #5435
2255if TYPE_CHECKING:
2257 @_operator_fn
2258 def notlike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ...
2260else:
2261 notlike_op = not_like_op
2264@comparison_op
2265@_operator_fn
2266def ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2267 return a.ilike(b, escape=escape)
2270@comparison_op
2271@_operator_fn
2272def not_ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:
2273 return a.not_ilike(b, escape=escape)
2276# 1.4 deprecated; see #5435
2277if TYPE_CHECKING:
2279 @_operator_fn
2280 def notilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ...
2282else:
2283 notilike_op = not_ilike_op
2286@comparison_op
2287@_operator_fn
2288def between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any:
2289 return a.between(b, c, symmetric=symmetric)
2292@comparison_op
2293@_operator_fn
2294def not_between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any:
2295 return ~a.between(b, c, symmetric=symmetric)
2298# 1.4 deprecated; see #5435
2299if TYPE_CHECKING:
2301 @_operator_fn
2302 def notbetween_op(
2303 a: Any, b: Any, c: Any, symmetric: bool = False
2304 ) -> Any: ...
2306else:
2307 notbetween_op = not_between_op
2310@comparison_op
2311@_operator_fn
2312def in_op(a: Any, b: Any) -> Any:
2313 return a.in_(b)
2316@comparison_op
2317@_operator_fn
2318def not_in_op(a: Any, b: Any) -> Any:
2319 return a.not_in(b)
2322# 1.4 deprecated; see #5429
2323if TYPE_CHECKING:
2325 @_operator_fn
2326 def notin_op(a: Any, b: Any) -> Any: ...
2328else:
2329 notin_op = not_in_op
2332@_operator_fn
2333def distinct_op(a: Any) -> Any:
2334 return a.distinct()
2337@_operator_fn
2338def any_op(a: Any) -> Any:
2339 return a.any_()
2342@_operator_fn
2343def all_op(a: Any) -> Any:
2344 return a.all_()
2347def _escaped_like_impl(
2348 fn: Callable[..., Any], other: Any, escape: Optional[str], autoescape: bool
2349) -> Any:
2350 if autoescape:
2351 if autoescape is not True:
2352 util.warn(
2353 "The autoescape parameter is now a simple boolean True/False"
2354 )
2355 if escape is None:
2356 escape = "/"
2358 if not isinstance(other, str):
2359 raise TypeError("String value expected when autoescape=True")
2361 if escape not in ("%", "_"):
2362 other = other.replace(escape, escape + escape)
2364 other = other.replace("%", escape + "%").replace("_", escape + "_")
2366 return fn(other, escape=escape)
2369@comparison_op
2370@_operator_fn
2371def startswith_op(
2372 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2373) -> Any:
2374 return _escaped_like_impl(a.startswith, b, escape, autoescape)
2377@comparison_op
2378@_operator_fn
2379def not_startswith_op(
2380 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2381) -> Any:
2382 return ~_escaped_like_impl(a.startswith, b, escape, autoescape)
2385# 1.4 deprecated; see #5435
2386if TYPE_CHECKING:
2388 @_operator_fn
2389 def notstartswith_op(
2390 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2391 ) -> Any: ...
2393else:
2394 notstartswith_op = not_startswith_op
2397@comparison_op
2398@_operator_fn
2399def istartswith_op(
2400 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2401) -> Any:
2402 return _escaped_like_impl(a.istartswith, b, escape, autoescape)
2405@comparison_op
2406@_operator_fn
2407def not_istartswith_op(
2408 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2409) -> Any:
2410 return ~_escaped_like_impl(a.istartswith, b, escape, autoescape)
2413@comparison_op
2414@_operator_fn
2415def endswith_op(
2416 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2417) -> Any:
2418 return _escaped_like_impl(a.endswith, b, escape, autoescape)
2421@comparison_op
2422@_operator_fn
2423def not_endswith_op(
2424 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2425) -> Any:
2426 return ~_escaped_like_impl(a.endswith, b, escape, autoescape)
2429# 1.4 deprecated; see #5435
2430if TYPE_CHECKING:
2432 @_operator_fn
2433 def notendswith_op(
2434 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2435 ) -> Any: ...
2437else:
2438 notendswith_op = not_endswith_op
2441@comparison_op
2442@_operator_fn
2443def iendswith_op(
2444 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2445) -> Any:
2446 return _escaped_like_impl(a.iendswith, b, escape, autoescape)
2449@comparison_op
2450@_operator_fn
2451def not_iendswith_op(
2452 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2453) -> Any:
2454 return ~_escaped_like_impl(a.iendswith, b, escape, autoescape)
2457@comparison_op
2458@_operator_fn
2459def contains_op(
2460 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2461) -> Any:
2462 return _escaped_like_impl(a.contains, b, escape, autoescape)
2465@comparison_op
2466@_operator_fn
2467def not_contains_op(
2468 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2469) -> Any:
2470 return ~_escaped_like_impl(a.contains, b, escape, autoescape)
2473# 1.4 deprecated; see #5435
2474if TYPE_CHECKING:
2476 @_operator_fn
2477 def notcontains_op(
2478 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2479 ) -> Any: ...
2481else:
2482 notcontains_op = not_contains_op
2485@comparison_op
2486@_operator_fn
2487def icontains_op(
2488 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2489) -> Any:
2490 return _escaped_like_impl(a.icontains, b, escape, autoescape)
2493@comparison_op
2494@_operator_fn
2495def not_icontains_op(
2496 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False
2497) -> Any:
2498 return ~_escaped_like_impl(a.icontains, b, escape, autoescape)
2501@comparison_op
2502@_operator_fn
2503def match_op(a: Any, b: Any, **kw: Any) -> Any:
2504 return a.match(b, **kw)
2507@comparison_op
2508@_operator_fn
2509def regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any:
2510 return a.regexp_match(b, flags=flags)
2513@comparison_op
2514@_operator_fn
2515def not_regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any:
2516 return ~a.regexp_match(b, flags=flags)
2519@_operator_fn
2520def regexp_replace_op(
2521 a: Any, b: Any, replacement: Any, flags: Optional[str] = None
2522) -> Any:
2523 return a.regexp_replace(b, replacement=replacement, flags=flags)
2526@comparison_op
2527@_operator_fn
2528def not_match_op(a: Any, b: Any, **kw: Any) -> Any:
2529 return ~a.match(b, **kw)
2532# 1.4 deprecated; see #5429
2533if TYPE_CHECKING:
2535 @_operator_fn
2536 def notmatch_op(a: Any, b: Any, **kw: Any) -> Any: ...
2538else:
2539 notmatch_op = not_match_op
2542@_operator_fn
2543def comma_op(a: Any, b: Any) -> Any:
2544 raise NotImplementedError()
2547@_operator_fn
2548def filter_op(a: Any, b: Any) -> Any:
2549 raise NotImplementedError()
2552@_operator_fn
2553def concat_op(a: Any, b: Any) -> Any:
2554 try:
2555 concat = a.concat
2556 except AttributeError:
2557 return b._rconcat(a)
2558 else:
2559 return concat(b)
2562@_operator_fn
2563def desc_op(a: Any) -> Any:
2564 return a.desc()
2567@_operator_fn
2568def asc_op(a: Any) -> Any:
2569 return a.asc()
2572@_operator_fn
2573def nulls_first_op(a: Any) -> Any:
2574 return a.nulls_first()
2577# 1.4 deprecated; see #5435
2578if TYPE_CHECKING:
2580 @_operator_fn
2581 def nullsfirst_op(a: Any) -> Any: ...
2583else:
2584 nullsfirst_op = nulls_first_op
2587@_operator_fn
2588def nulls_last_op(a: Any) -> Any:
2589 return a.nulls_last()
2592# 1.4 deprecated; see #5435
2593if TYPE_CHECKING:
2595 @_operator_fn
2596 def nullslast_op(a: Any) -> Any: ...
2598else:
2599 nullslast_op = nulls_last_op
2602@_operator_fn
2603def json_getitem_op(a: Any, b: Any) -> Any:
2604 raise NotImplementedError()
2607@_operator_fn
2608def json_path_getitem_op(a: Any, b: Any) -> Any:
2609 raise NotImplementedError()
2612@_operator_fn
2613def bitwise_xor_op(a: Any, b: Any) -> Any:
2614 return a.bitwise_xor(b)
2617@_operator_fn
2618def bitwise_or_op(a: Any, b: Any) -> Any:
2619 return a.bitwise_or(b)
2622@_operator_fn
2623def bitwise_and_op(a: Any, b: Any) -> Any:
2624 return a.bitwise_and(b)
2627@_operator_fn
2628def bitwise_not_op(a: Any) -> Any:
2629 return a.bitwise_not()
2632@_operator_fn
2633def bitwise_lshift_op(a: Any, b: Any) -> Any:
2634 return a.bitwise_lshift(b)
2637@_operator_fn
2638def bitwise_rshift_op(a: Any, b: Any) -> Any:
2639 return a.bitwise_rshift(b)
2642@_operator_fn
2643def null_op(a: Any, b: Any) -> Any:
2644 """a 'null' operator that provides a boolean operation.
2646 Does not compile in a SQL context, used for testing operators only.
2648 .. versionadded:: 2.1
2650 """
2651 return a._null_operate(b)
2654def is_comparison(op: OperatorType) -> bool:
2655 return op in _comparison or isinstance(op, custom_op) and op.is_comparison
2658def is_commutative(op: OperatorType) -> bool:
2659 return op in _commutative
2662def is_ordering_modifier(op: OperatorType) -> bool:
2663 return op in (asc_op, desc_op, nulls_first_op, nulls_last_op)
2666def is_natural_self_precedent(op: OperatorType) -> bool:
2667 return (
2668 op in _natural_self_precedent
2669 or isinstance(op, custom_op)
2670 and op.natural_self_precedent
2671 )
2674_booleans = (inv, is_true, is_false, and_, or_)
2677def is_boolean(op: OperatorType) -> bool:
2678 return is_comparison(op) or op in _booleans
2681_mirror = {gt: lt, ge: le, lt: gt, le: ge}
2684def mirror(op: OperatorType) -> OperatorType:
2685 """rotate a comparison operator 180 degrees.
2687 Note this is not the same as negation.
2689 """
2690 return _mirror.get(op, op)
2693_associative = _commutative.union([concat_op, and_, or_]).difference([eq, ne])
2696def is_associative(op: OperatorType) -> bool:
2697 return op in _associative
2700def is_order_by_modifier(op: Optional[OperatorType]) -> bool:
2701 return op in _order_by_modifier
2704_order_by_modifier = {desc_op, asc_op, nulls_first_op, nulls_last_op}
2706_natural_self_precedent = _associative.union(
2707 [getitem, json_getitem_op, json_path_getitem_op]
2708)
2709"""Operators where if we have (a op b) op c, we don't want to
2710parenthesize (a op b).
2712"""
2715@_operator_fn
2716def _asbool(a: Any) -> Any:
2717 raise NotImplementedError()
2720class _OpLimit(IntEnum):
2721 _smallest = -100
2722 _largest = 100
2725_PRECEDENCE: Dict[OperatorType, int] = {
2726 from_: 15,
2727 function_as_comparison_op: 15,
2728 any_op: 15,
2729 all_op: 15,
2730 getitem: 15,
2731 json_getitem_op: 15,
2732 json_path_getitem_op: 15,
2733 pow_: 15,
2734 mul: 8,
2735 truediv: 8,
2736 floordiv: 8,
2737 mod: 8,
2738 neg: 8,
2739 bitwise_not_op: 8,
2740 add: 7,
2741 sub: 7,
2742 bitwise_xor_op: 7,
2743 bitwise_or_op: 7,
2744 bitwise_and_op: 7,
2745 bitwise_lshift_op: 7,
2746 bitwise_rshift_op: 7,
2747 null_op: 7,
2748 filter_op: 6,
2749 concat_op: 5,
2750 match_op: 5,
2751 not_match_op: 5,
2752 regexp_match_op: 5,
2753 not_regexp_match_op: 5,
2754 regexp_replace_op: 5,
2755 ilike_op: 5,
2756 not_ilike_op: 5,
2757 like_op: 5,
2758 not_like_op: 5,
2759 in_op: 5,
2760 not_in_op: 5,
2761 is_: 5,
2762 is_not: 5,
2763 eq: 5,
2764 ne: 5,
2765 is_distinct_from: 5,
2766 is_not_distinct_from: 5,
2767 gt: 5,
2768 lt: 5,
2769 ge: 5,
2770 le: 5,
2771 between_op: 5,
2772 not_between_op: 5,
2773 distinct_op: 5,
2774 inv: 5,
2775 is_true: 5,
2776 is_false: 5,
2777 and_: 3,
2778 or_: 2,
2779 comma_op: -1,
2780 desc_op: 3,
2781 asc_op: 3,
2782 collate: 4,
2783 as_: -1,
2784 exists: 0,
2785 _asbool: -10,
2786}
2789# Mapping of OperatorType objects to their corresponding OperatorClass
2790# Derived from unified_operator_lookup in default_comparator.py
2791_OPERATOR_CLASSES: util.immutabledict[OperatorType, OperatorClass] = (
2792 util.immutabledict(
2793 {
2794 # BASE operators
2795 null_op: OperatorClass.BASE,
2796 # COMPARISON operators
2797 lt: OperatorClass.COMPARISON,
2798 le: OperatorClass.COMPARISON,
2799 ne: OperatorClass.COMPARISON,
2800 gt: OperatorClass.COMPARISON,
2801 ge: OperatorClass.COMPARISON,
2802 eq: OperatorClass.COMPARISON,
2803 is_distinct_from: OperatorClass.COMPARISON,
2804 is_not_distinct_from: OperatorClass.COMPARISON,
2805 in_op: OperatorClass.COMPARISON,
2806 not_in_op: OperatorClass.COMPARISON,
2807 is_: OperatorClass.COMPARISON,
2808 is_not: OperatorClass.COMPARISON,
2809 between_op: OperatorClass.COMPARISON,
2810 not_between_op: OperatorClass.COMPARISON,
2811 desc_op: OperatorClass.COMPARISON,
2812 asc_op: OperatorClass.COMPARISON,
2813 nulls_first_op: OperatorClass.COMPARISON,
2814 nulls_last_op: OperatorClass.COMPARISON,
2815 distinct_op: OperatorClass.COMPARISON,
2816 any_op: OperatorClass.COMPARISON,
2817 all_op: OperatorClass.COMPARISON,
2818 # BOOLEAN_ALGEBRA operators
2819 and_: OperatorClass.BOOLEAN_ALGEBRA,
2820 or_: OperatorClass.BOOLEAN_ALGEBRA,
2821 inv: OperatorClass.BOOLEAN_ALGEBRA | OperatorClass.BITWISE,
2822 # CONCATENABLE | MATH | DATE_ARITHMETIC | BITWISE operators
2823 add: OperatorClass.CONCATENABLE
2824 | OperatorClass.MATH
2825 | OperatorClass.DATE_ARITHEMETIC
2826 | OperatorClass.BITWISE,
2827 # CONCATENABLE | BITWISE operators
2828 concat_op: OperatorClass.CONCATENABLE | OperatorClass.BITWISE,
2829 # INDEXABLE operators
2830 getitem: OperatorClass.INDEXABLE,
2831 # CONTAINS operators
2832 contains_op: OperatorClass.CONTAINS,
2833 icontains_op: OperatorClass.CONTAINS,
2834 contains: OperatorClass.CONTAINS,
2835 not_contains_op: OperatorClass.CONTAINS,
2836 not_icontains_op: OperatorClass.CONTAINS,
2837 # STRING_MATCH operators
2838 like_op: OperatorClass.STRING_MATCH,
2839 ilike_op: OperatorClass.STRING_MATCH,
2840 not_like_op: OperatorClass.STRING_MATCH,
2841 not_ilike_op: OperatorClass.STRING_MATCH,
2842 startswith_op: OperatorClass.STRING_MATCH,
2843 istartswith_op: OperatorClass.STRING_MATCH,
2844 endswith_op: OperatorClass.STRING_MATCH,
2845 iendswith_op: OperatorClass.STRING_MATCH,
2846 not_startswith_op: OperatorClass.STRING_MATCH,
2847 not_istartswith_op: OperatorClass.STRING_MATCH,
2848 not_endswith_op: OperatorClass.STRING_MATCH,
2849 not_iendswith_op: OperatorClass.STRING_MATCH,
2850 collate: OperatorClass.STRING_MATCH,
2851 match_op: OperatorClass.STRING_MATCH,
2852 not_match_op: OperatorClass.STRING_MATCH,
2853 regexp_match_op: OperatorClass.STRING_MATCH,
2854 not_regexp_match_op: OperatorClass.STRING_MATCH,
2855 regexp_replace_op: OperatorClass.STRING_MATCH,
2856 # BITWISE operators
2857 lshift: OperatorClass.BITWISE,
2858 rshift: OperatorClass.BITWISE,
2859 bitwise_xor_op: OperatorClass.BITWISE,
2860 bitwise_or_op: OperatorClass.BITWISE,
2861 bitwise_and_op: OperatorClass.BITWISE,
2862 bitwise_not_op: OperatorClass.BITWISE,
2863 bitwise_lshift_op: OperatorClass.BITWISE,
2864 bitwise_rshift_op: OperatorClass.BITWISE,
2865 # MATH operators
2866 matmul: OperatorClass.MATH,
2867 pow_: OperatorClass.MATH,
2868 neg: OperatorClass.MATH,
2869 mul: OperatorClass.MATH,
2870 sub: OperatorClass.MATH | OperatorClass.DATE_ARITHEMETIC,
2871 truediv: OperatorClass.MATH,
2872 floordiv: OperatorClass.MATH,
2873 mod: OperatorClass.MATH,
2874 # JSON_GETITEM operators
2875 json_path_getitem_op: OperatorClass.JSON_GETITEM,
2876 json_getitem_op: OperatorClass.JSON_GETITEM,
2877 }
2878 )
2879)
2882def is_precedent(
2883 operator: OperatorType, against: Optional[OperatorType]
2884) -> bool:
2885 if operator is against and is_natural_self_precedent(operator):
2886 return False
2887 elif against is None:
2888 return True
2889 else:
2890 return bool(
2891 _PRECEDENCE.get(
2892 operator, getattr(operator, "precedence", _OpLimit._smallest)
2893 )
2894 <= _PRECEDENCE.get(
2895 against, getattr(against, "precedence", _OpLimit._largest)
2896 )
2897 )