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-2024 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 operator import add
14from operator import and_
15from operator import contains
16from operator import eq
17from operator import ge
18from operator import getitem
19from operator import gt
20from operator import inv
21from operator import le
22from operator import lshift
23from operator import lt
24from operator import mod
25from operator import mul
26from operator import ne
27from operator import neg
28from operator import or_
29from operator import rshift
30from operator import sub
31from operator import truediv
33from .. import util
36if util.py2k:
37 from operator import div
38else:
39 div = truediv
42class Operators(object):
43 """Base of comparison and logical operators.
45 Implements base methods
46 :meth:`~sqlalchemy.sql.operators.Operators.operate` and
47 :meth:`~sqlalchemy.sql.operators.Operators.reverse_operate`, as well as
48 :meth:`~sqlalchemy.sql.operators.Operators.__and__`,
49 :meth:`~sqlalchemy.sql.operators.Operators.__or__`,
50 :meth:`~sqlalchemy.sql.operators.Operators.__invert__`.
52 Usually is used via its most common subclass
53 :class:`.ColumnOperators`.
55 """
57 __slots__ = ()
59 def __and__(self, other):
60 """Implement the ``&`` operator.
62 When used with SQL expressions, results in an
63 AND operation, equivalent to
64 :func:`_expression.and_`, that is::
66 a & b
68 is equivalent to::
70 from sqlalchemy import and_
71 and_(a, b)
73 Care should be taken when using ``&`` regarding
74 operator precedence; the ``&`` operator has the highest precedence.
75 The operands should be enclosed in parenthesis if they contain
76 further sub expressions::
78 (a == 2) & (b == 4)
80 """
81 return self.operate(and_, other)
83 def __or__(self, other):
84 """Implement the ``|`` operator.
86 When used with SQL expressions, results in an
87 OR operation, equivalent to
88 :func:`_expression.or_`, that is::
90 a | b
92 is equivalent to::
94 from sqlalchemy import or_
95 or_(a, b)
97 Care should be taken when using ``|`` regarding
98 operator precedence; the ``|`` operator has the highest precedence.
99 The operands should be enclosed in parenthesis if they contain
100 further sub expressions::
102 (a == 2) | (b == 4)
104 """
105 return self.operate(or_, other)
107 def __invert__(self):
108 """Implement the ``~`` operator.
110 When used with SQL expressions, results in a
111 NOT operation, equivalent to
112 :func:`_expression.not_`, that is::
114 ~a
116 is equivalent to::
118 from sqlalchemy import not_
119 not_(a)
121 """
122 return self.operate(inv)
124 def op(
125 self, opstring, precedence=0, is_comparison=False, return_type=None
126 ):
127 """Produce a generic operator function.
129 e.g.::
131 somecolumn.op("*")(5)
133 produces::
135 somecolumn * 5
137 This function can also be used to make bitwise operators explicit. For
138 example::
140 somecolumn.op('&')(0xff)
142 is a bitwise AND of the value in ``somecolumn``.
144 :param operator: a string which will be output as the infix operator
145 between this element and the expression passed to the
146 generated function.
148 :param precedence: precedence which the database is expected to apply
149 to the operator in SQL expressions. This integer value acts as a hint
150 for the SQL compiler to know when explicit parenthesis should be
151 rendered around a particular operation. A lower number will cause the
152 expression to be parenthesized when applied against another operator
153 with higher precedence. The default value of ``0`` is lower than all
154 operators except for the comma (``,``) and ``AS`` operators. A value
155 of 100 will be higher or equal to all operators, and -100 will be
156 lower than or equal to all operators.
158 .. seealso::
160 :ref:`faq_sql_expression_op_parenthesis` - detailed description
161 of how the SQLAlchemy SQL compiler renders parenthesis
163 :param is_comparison: legacy; if True, the operator will be considered
164 as a "comparison" operator, that is which evaluates to a boolean
165 true/false value, like ``==``, ``>``, etc. This flag is provided
166 so that ORM relationships can establish that the operator is a
167 comparison operator when used in a custom join condition.
169 Using the ``is_comparison`` parameter is superseded by using the
170 :meth:`.Operators.bool_op` method instead; this more succinct
171 operator sets this parameter automatically. In SQLAlchemy 2.0 it
172 will also provide for improved typing support.
174 :param return_type: a :class:`.TypeEngine` class or object that will
175 force the return type of an expression produced by this operator
176 to be of that type. By default, operators that specify
177 :paramref:`.Operators.op.is_comparison` will resolve to
178 :class:`.Boolean`, and those that do not will be of the same
179 type as the left-hand operand.
181 .. seealso::
183 :meth:`.Operators.bool_op`
185 :ref:`types_operators`
187 :ref:`relationship_custom_operator`
189 """
190 operator = custom_op(opstring, precedence, is_comparison, return_type)
192 def against(other):
193 return operator(self, other)
195 return against
197 def bool_op(self, opstring, precedence=0):
198 """Return a custom boolean operator.
200 This method is shorthand for calling
201 :meth:`.Operators.op` and passing the
202 :paramref:`.Operators.op.is_comparison`
203 flag with True. A key advantage to using :meth:`.Operators.bool_op`
204 is that when using column constructs, the "boolean" nature of the
205 returned expression will be present for :pep:`484` purposes.
207 .. seealso::
209 :meth:`.Operators.op`
211 """
212 return self.op(opstring, precedence=precedence, is_comparison=True)
214 def operate(self, op, *other, **kwargs):
215 r"""Operate on an argument.
217 This is the lowest level of operation, raises
218 :class:`NotImplementedError` by default.
220 Overriding this on a subclass can allow common
221 behavior to be applied to all operations.
222 For example, overriding :class:`.ColumnOperators`
223 to apply ``func.lower()`` to the left and right
224 side::
226 class MyComparator(ColumnOperators):
227 def operate(self, op, other, **kwargs):
228 return op(func.lower(self), func.lower(other), **kwargs)
230 :param op: Operator callable.
231 :param \*other: the 'other' side of the operation. Will
232 be a single scalar for most operations.
233 :param \**kwargs: modifiers. These may be passed by special
234 operators such as :meth:`ColumnOperators.contains`.
237 """
238 raise NotImplementedError(str(op))
240 def reverse_operate(self, op, other, **kwargs):
241 """Reverse operate on an argument.
243 Usage is the same as :meth:`operate`.
245 """
246 raise NotImplementedError(str(op))
249class custom_op(object):
250 """Represent a 'custom' operator.
252 :class:`.custom_op` is normally instantiated when the
253 :meth:`.Operators.op` or :meth:`.Operators.bool_op` methods
254 are used to create a custom operator callable. The class can also be
255 used directly when programmatically constructing expressions. E.g.
256 to represent the "factorial" operation::
258 from sqlalchemy.sql import UnaryExpression
259 from sqlalchemy.sql import operators
260 from sqlalchemy import Numeric
262 unary = UnaryExpression(table.c.somecolumn,
263 modifier=operators.custom_op("!"),
264 type_=Numeric)
267 .. seealso::
269 :meth:`.Operators.op`
271 :meth:`.Operators.bool_op`
273 """
275 __name__ = "custom_op"
277 def __init__(
278 self,
279 opstring,
280 precedence=0,
281 is_comparison=False,
282 return_type=None,
283 natural_self_precedent=False,
284 eager_grouping=False,
285 ):
286 self.opstring = opstring
287 self.precedence = precedence
288 self.is_comparison = is_comparison
289 self.natural_self_precedent = natural_self_precedent
290 self.eager_grouping = eager_grouping
291 self.return_type = (
292 return_type._to_instance(return_type) if return_type else None
293 )
295 def __eq__(self, other):
296 return (
297 isinstance(other, custom_op)
298 and other._hash_key() == self._hash_key()
299 )
301 def __hash__(self):
302 return hash(self._hash_key())
304 def _hash_key(self):
305 return (
306 self.__class__,
307 self.opstring,
308 self.precedence,
309 self.is_comparison,
310 self.natural_self_precedent,
311 self.eager_grouping,
312 self.return_type._static_cache_key if self.return_type else None,
313 )
315 def __call__(self, left, right, **kw):
316 return left.operate(self, right, **kw)
319class ColumnOperators(Operators):
320 """Defines boolean, comparison, and other operators for
321 :class:`_expression.ColumnElement` expressions.
323 By default, all methods call down to
324 :meth:`.operate` or :meth:`.reverse_operate`,
325 passing in the appropriate operator function from the
326 Python builtin ``operator`` module or
327 a SQLAlchemy-specific operator function from
328 :mod:`sqlalchemy.expression.operators`. For example
329 the ``__eq__`` function::
331 def __eq__(self, other):
332 return self.operate(operators.eq, other)
334 Where ``operators.eq`` is essentially::
336 def eq(a, b):
337 return a == b
339 The core column expression unit :class:`_expression.ColumnElement`
340 overrides :meth:`.Operators.operate` and others
341 to return further :class:`_expression.ColumnElement` constructs,
342 so that the ``==`` operation above is replaced by a clause
343 construct.
345 .. seealso::
347 :ref:`types_operators`
349 :attr:`.TypeEngine.comparator_factory`
351 :class:`.ColumnOperators`
353 :class:`.PropComparator`
355 """
357 __slots__ = ()
359 timetuple = None
360 """Hack, allows datetime objects to be compared on the LHS."""
362 def __lt__(self, other):
363 """Implement the ``<`` operator.
365 In a column context, produces the clause ``a < b``.
367 """
368 return self.operate(lt, other)
370 def __le__(self, other):
371 """Implement the ``<=`` operator.
373 In a column context, produces the clause ``a <= b``.
375 """
376 return self.operate(le, other)
378 __hash__ = Operators.__hash__
380 def __eq__(self, other):
381 """Implement the ``==`` operator.
383 In a column context, produces the clause ``a = b``.
384 If the target is ``None``, produces ``a IS NULL``.
386 """
387 return self.operate(eq, other)
389 def __ne__(self, other):
390 """Implement the ``!=`` operator.
392 In a column context, produces the clause ``a != b``.
393 If the target is ``None``, produces ``a IS NOT NULL``.
395 """
396 return self.operate(ne, other)
398 def is_distinct_from(self, other):
399 """Implement the ``IS DISTINCT FROM`` operator.
401 Renders "a IS DISTINCT FROM b" on most platforms;
402 on some such as SQLite may render "a IS NOT b".
404 .. versionadded:: 1.1
406 """
407 return self.operate(is_distinct_from, other)
409 def is_not_distinct_from(self, other):
410 """Implement the ``IS NOT DISTINCT FROM`` operator.
412 Renders "a IS NOT DISTINCT FROM b" on most platforms;
413 on some such as SQLite may render "a IS b".
415 .. versionchanged:: 1.4 The ``is_not_distinct_from()`` operator is
416 renamed from ``isnot_distinct_from()`` in previous releases.
417 The previous name remains available for backwards compatibility.
419 .. versionadded:: 1.1
421 """
422 return self.operate(is_not_distinct_from, other)
424 # deprecated 1.4; see #5435
425 isnot_distinct_from = is_not_distinct_from
427 def __gt__(self, other):
428 """Implement the ``>`` operator.
430 In a column context, produces the clause ``a > b``.
432 """
433 return self.operate(gt, other)
435 def __ge__(self, other):
436 """Implement the ``>=`` operator.
438 In a column context, produces the clause ``a >= b``.
440 """
441 return self.operate(ge, other)
443 def __neg__(self):
444 """Implement the ``-`` operator.
446 In a column context, produces the clause ``-a``.
448 """
449 return self.operate(neg)
451 def __contains__(self, other):
452 return self.operate(contains, other)
454 def __getitem__(self, index):
455 """Implement the [] operator.
457 This can be used by some database-specific types
458 such as PostgreSQL ARRAY and HSTORE.
460 """
461 return self.operate(getitem, index)
463 def __lshift__(self, other):
464 """implement the << operator.
466 Not used by SQLAlchemy core, this is provided
467 for custom operator systems which want to use
468 << as an extension point.
469 """
470 return self.operate(lshift, other)
472 def __rshift__(self, other):
473 """implement the >> operator.
475 Not used by SQLAlchemy core, this is provided
476 for custom operator systems which want to use
477 >> as an extension point.
478 """
479 return self.operate(rshift, other)
481 def concat(self, other):
482 """Implement the 'concat' operator.
484 In a column context, produces the clause ``a || b``,
485 or uses the ``concat()`` operator on MySQL.
487 """
488 return self.operate(concat_op, other)
490 def _rconcat(self, other):
491 """Implement an 'rconcat' operator.
493 this is for internal use at the moment
495 .. versionadded:: 1.4.40
497 """
498 return self.reverse_operate(concat_op, other)
500 def like(self, other, escape=None):
501 r"""Implement the ``like`` operator.
503 In a column context, produces the expression::
505 a LIKE other
507 E.g.::
509 stmt = select(sometable).\
510 where(sometable.c.column.like("%foobar%"))
512 :param other: expression to be compared
513 :param escape: optional escape character, renders the ``ESCAPE``
514 keyword, e.g.::
516 somecolumn.like("foo/%bar", escape="/")
518 .. seealso::
520 :meth:`.ColumnOperators.ilike`
522 """
523 return self.operate(like_op, other, escape=escape)
525 def ilike(self, other, escape=None):
526 r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE.
528 In a column context, produces an expression either of the form::
530 lower(a) LIKE lower(other)
532 Or on backends that support the ILIKE operator::
534 a ILIKE other
536 E.g.::
538 stmt = select(sometable).\
539 where(sometable.c.column.ilike("%foobar%"))
541 :param other: expression to be compared
542 :param escape: optional escape character, renders the ``ESCAPE``
543 keyword, e.g.::
545 somecolumn.ilike("foo/%bar", escape="/")
547 .. seealso::
549 :meth:`.ColumnOperators.like`
551 """
552 return self.operate(ilike_op, other, escape=escape)
554 def in_(self, other):
555 """Implement the ``in`` operator.
557 In a column context, produces the clause ``column IN <other>``.
559 The given parameter ``other`` may be:
561 * A list of literal values, e.g.::
563 stmt.where(column.in_([1, 2, 3]))
565 In this calling form, the list of items is converted to a set of
566 bound parameters the same length as the list given::
568 WHERE COL IN (?, ?, ?)
570 * A list of tuples may be provided if the comparison is against a
571 :func:`.tuple_` containing multiple expressions::
573 from sqlalchemy import tuple_
574 stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
576 * An empty list, e.g.::
578 stmt.where(column.in_([]))
580 In this calling form, the expression renders an "empty set"
581 expression. These expressions are tailored to individual backends
582 and are generally trying to get an empty SELECT statement as a
583 subquery. Such as on SQLite, the expression is::
585 WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
587 .. versionchanged:: 1.4 empty IN expressions now use an
588 execution-time generated SELECT subquery in all cases.
590 * A bound parameter, e.g. :func:`.bindparam`, may be used if it
591 includes the :paramref:`.bindparam.expanding` flag::
593 stmt.where(column.in_(bindparam('value', expanding=True)))
595 In this calling form, the expression renders a special non-SQL
596 placeholder expression that looks like::
598 WHERE COL IN ([EXPANDING_value])
600 This placeholder expression is intercepted at statement execution
601 time to be converted into the variable number of bound parameter
602 form illustrated earlier. If the statement were executed as::
604 connection.execute(stmt, {"value": [1, 2, 3]})
606 The database would be passed a bound parameter for each value::
608 WHERE COL IN (?, ?, ?)
610 .. versionadded:: 1.2 added "expanding" bound parameters
612 If an empty list is passed, a special "empty list" expression,
613 which is specific to the database in use, is rendered. On
614 SQLite this would be::
616 WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
618 .. versionadded:: 1.3 "expanding" bound parameters now support
619 empty lists
621 * a :func:`_expression.select` construct, which is usually a
622 correlated scalar select::
624 stmt.where(
625 column.in_(
626 select(othertable.c.y).
627 where(table.c.x == othertable.c.x)
628 )
629 )
631 In this calling form, :meth:`.ColumnOperators.in_` renders as given::
633 WHERE COL IN (SELECT othertable.y
634 FROM othertable WHERE othertable.x = table.x)
636 :param other: a list of literals, a :func:`_expression.select`
637 construct, or a :func:`.bindparam` construct that includes the
638 :paramref:`.bindparam.expanding` flag set to True.
640 """
641 return self.operate(in_op, other)
643 def not_in(self, other):
644 """implement the ``NOT IN`` operator.
646 This is equivalent to using negation with
647 :meth:`.ColumnOperators.in_`, i.e. ``~x.in_(y)``.
649 In the case that ``other`` is an empty sequence, the compiler
650 produces an "empty not in" expression. This defaults to the
651 expression "1 = 1" to produce true in all cases. The
652 :paramref:`_sa.create_engine.empty_in_strategy` may be used to
653 alter this behavior.
655 .. versionchanged:: 1.4 The ``not_in()`` operator is renamed from
656 ``notin_()`` in previous releases. The previous name remains
657 available for backwards compatibility.
659 .. versionchanged:: 1.2 The :meth:`.ColumnOperators.in_` and
660 :meth:`.ColumnOperators.not_in` operators
661 now produce a "static" expression for an empty IN sequence
662 by default.
664 .. seealso::
666 :meth:`.ColumnOperators.in_`
668 """
669 return self.operate(not_in_op, other)
671 # deprecated 1.4; see #5429
672 notin_ = not_in
674 def not_like(self, other, escape=None):
675 """implement the ``NOT LIKE`` operator.
677 This is equivalent to using negation with
678 :meth:`.ColumnOperators.like`, i.e. ``~x.like(y)``.
680 .. versionchanged:: 1.4 The ``not_like()`` operator is renamed from
681 ``notlike()`` in previous releases. The previous name remains
682 available for backwards compatibility.
684 .. seealso::
686 :meth:`.ColumnOperators.like`
688 """
689 return self.operate(notlike_op, other, escape=escape)
691 # deprecated 1.4; see #5435
692 notlike = not_like
694 def not_ilike(self, other, escape=None):
695 """implement the ``NOT ILIKE`` operator.
697 This is equivalent to using negation with
698 :meth:`.ColumnOperators.ilike`, i.e. ``~x.ilike(y)``.
700 .. versionchanged:: 1.4 The ``not_ilike()`` operator is renamed from
701 ``notilike()`` in previous releases. The previous name remains
702 available for backwards compatibility.
704 .. seealso::
706 :meth:`.ColumnOperators.ilike`
708 """
709 return self.operate(notilike_op, other, escape=escape)
711 # deprecated 1.4; see #5435
712 notilike = not_ilike
714 def is_(self, other):
715 """Implement the ``IS`` operator.
717 Normally, ``IS`` is generated automatically when comparing to a
718 value of ``None``, which resolves to ``NULL``. However, explicit
719 usage of ``IS`` may be desirable if comparing to boolean values
720 on certain platforms.
722 .. seealso:: :meth:`.ColumnOperators.is_not`
724 """
725 return self.operate(is_, other)
727 def is_not(self, other):
728 """Implement the ``IS NOT`` operator.
730 Normally, ``IS NOT`` is generated automatically when comparing to a
731 value of ``None``, which resolves to ``NULL``. However, explicit
732 usage of ``IS NOT`` may be desirable if comparing to boolean values
733 on certain platforms.
735 .. versionchanged:: 1.4 The ``is_not()`` operator is renamed from
736 ``isnot()`` in previous releases. The previous name remains
737 available for backwards compatibility.
739 .. seealso:: :meth:`.ColumnOperators.is_`
741 """
742 return self.operate(is_not, other)
744 # deprecated 1.4; see #5429
745 isnot = is_not
747 def startswith(self, other, **kwargs):
748 r"""Implement the ``startswith`` operator.
750 Produces a LIKE expression that tests against a match for the start
751 of a string value::
753 column LIKE <other> || '%'
755 E.g.::
757 stmt = select(sometable).\
758 where(sometable.c.column.startswith("foobar"))
760 Since the operator uses ``LIKE``, wildcard characters
761 ``"%"`` and ``"_"`` that are present inside the <other> expression
762 will behave like wildcards as well. For literal string
763 values, the :paramref:`.ColumnOperators.startswith.autoescape` flag
764 may be set to ``True`` to apply escaping to occurrences of these
765 characters within the string value so that they match as themselves
766 and not as wildcard characters. Alternatively, the
767 :paramref:`.ColumnOperators.startswith.escape` parameter will establish
768 a given character as an escape character which can be of use when
769 the target expression is not a literal string.
771 :param other: expression to be compared. This is usually a plain
772 string value, but can also be an arbitrary SQL expression. LIKE
773 wildcard characters ``%`` and ``_`` are not escaped by default unless
774 the :paramref:`.ColumnOperators.startswith.autoescape` flag is
775 set to True.
777 :param autoescape: boolean; when True, establishes an escape character
778 within the LIKE expression, then applies it to all occurrences of
779 ``"%"``, ``"_"`` and the escape character itself within the
780 comparison value, which is assumed to be a literal string and not a
781 SQL expression.
783 An expression such as::
785 somecolumn.startswith("foo%bar", autoescape=True)
787 Will render as::
789 somecolumn LIKE :param || '%' ESCAPE '/'
791 With the value of ``:param`` as ``"foo/%bar"``.
793 :param escape: a character which when given will render with the
794 ``ESCAPE`` keyword to establish that character as the escape
795 character. This character can then be placed preceding occurrences
796 of ``%`` and ``_`` to allow them to act as themselves and not
797 wildcard characters.
799 An expression such as::
801 somecolumn.startswith("foo/%bar", escape="^")
803 Will render as::
805 somecolumn LIKE :param || '%' ESCAPE '^'
807 The parameter may also be combined with
808 :paramref:`.ColumnOperators.startswith.autoescape`::
810 somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
812 Where above, the given literal parameter will be converted to
813 ``"foo^%bar^^bat"`` before being passed to the database.
815 .. seealso::
817 :meth:`.ColumnOperators.endswith`
819 :meth:`.ColumnOperators.contains`
821 :meth:`.ColumnOperators.like`
823 """
824 return self.operate(startswith_op, other, **kwargs)
826 def endswith(self, other, **kwargs):
827 r"""Implement the 'endswith' operator.
829 Produces a LIKE expression that tests against a match for the end
830 of a string value::
832 column LIKE '%' || <other>
834 E.g.::
836 stmt = select(sometable).\
837 where(sometable.c.column.endswith("foobar"))
839 Since the operator uses ``LIKE``, wildcard characters
840 ``"%"`` and ``"_"`` that are present inside the <other> expression
841 will behave like wildcards as well. For literal string
842 values, the :paramref:`.ColumnOperators.endswith.autoescape` flag
843 may be set to ``True`` to apply escaping to occurrences of these
844 characters within the string value so that they match as themselves
845 and not as wildcard characters. Alternatively, the
846 :paramref:`.ColumnOperators.endswith.escape` parameter will establish
847 a given character as an escape character which can be of use when
848 the target expression is not a literal string.
850 :param other: expression to be compared. This is usually a plain
851 string value, but can also be an arbitrary SQL expression. LIKE
852 wildcard characters ``%`` and ``_`` are not escaped by default unless
853 the :paramref:`.ColumnOperators.endswith.autoescape` flag is
854 set to True.
856 :param autoescape: boolean; when True, establishes an escape character
857 within the LIKE expression, then applies it to all occurrences of
858 ``"%"``, ``"_"`` and the escape character itself within the
859 comparison value, which is assumed to be a literal string and not a
860 SQL expression.
862 An expression such as::
864 somecolumn.endswith("foo%bar", autoescape=True)
866 Will render as::
868 somecolumn LIKE '%' || :param ESCAPE '/'
870 With the value of ``:param`` as ``"foo/%bar"``.
872 :param escape: a character which when given will render with the
873 ``ESCAPE`` keyword to establish that character as the escape
874 character. This character can then be placed preceding occurrences
875 of ``%`` and ``_`` to allow them to act as themselves and not
876 wildcard characters.
878 An expression such as::
880 somecolumn.endswith("foo/%bar", escape="^")
882 Will render as::
884 somecolumn LIKE '%' || :param ESCAPE '^'
886 The parameter may also be combined with
887 :paramref:`.ColumnOperators.endswith.autoescape`::
889 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
891 Where above, the given literal parameter will be converted to
892 ``"foo^%bar^^bat"`` before being passed to the database.
894 .. seealso::
896 :meth:`.ColumnOperators.startswith`
898 :meth:`.ColumnOperators.contains`
900 :meth:`.ColumnOperators.like`
902 """
903 return self.operate(endswith_op, other, **kwargs)
905 def contains(self, other, **kwargs):
906 r"""Implement the 'contains' operator.
908 Produces a LIKE expression that tests against a match for the middle
909 of a string value::
911 column LIKE '%' || <other> || '%'
913 E.g.::
915 stmt = select(sometable).\
916 where(sometable.c.column.contains("foobar"))
918 Since the operator uses ``LIKE``, wildcard characters
919 ``"%"`` and ``"_"`` that are present inside the <other> expression
920 will behave like wildcards as well. For literal string
921 values, the :paramref:`.ColumnOperators.contains.autoescape` flag
922 may be set to ``True`` to apply escaping to occurrences of these
923 characters within the string value so that they match as themselves
924 and not as wildcard characters. Alternatively, the
925 :paramref:`.ColumnOperators.contains.escape` parameter will establish
926 a given character as an escape character which can be of use when
927 the target expression is not a literal string.
929 :param other: expression to be compared. This is usually a plain
930 string value, but can also be an arbitrary SQL expression. LIKE
931 wildcard characters ``%`` and ``_`` are not escaped by default unless
932 the :paramref:`.ColumnOperators.contains.autoescape` flag is
933 set to True.
935 :param autoescape: boolean; when True, establishes an escape character
936 within the LIKE expression, then applies it to all occurrences of
937 ``"%"``, ``"_"`` and the escape character itself within the
938 comparison value, which is assumed to be a literal string and not a
939 SQL expression.
941 An expression such as::
943 somecolumn.contains("foo%bar", autoescape=True)
945 Will render as::
947 somecolumn LIKE '%' || :param || '%' ESCAPE '/'
949 With the value of ``:param`` as ``"foo/%bar"``.
951 :param escape: a character which when given will render with the
952 ``ESCAPE`` keyword to establish that character as the escape
953 character. This character can then be placed preceding occurrences
954 of ``%`` and ``_`` to allow them to act as themselves and not
955 wildcard characters.
957 An expression such as::
959 somecolumn.contains("foo/%bar", escape="^")
961 Will render as::
963 somecolumn LIKE '%' || :param || '%' ESCAPE '^'
965 The parameter may also be combined with
966 :paramref:`.ColumnOperators.contains.autoescape`::
968 somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
970 Where above, the given literal parameter will be converted to
971 ``"foo^%bar^^bat"`` before being passed to the database.
973 .. seealso::
975 :meth:`.ColumnOperators.startswith`
977 :meth:`.ColumnOperators.endswith`
979 :meth:`.ColumnOperators.like`
982 """
983 return self.operate(contains_op, other, **kwargs)
985 def match(self, other, **kwargs):
986 """Implements a database-specific 'match' operator.
988 :meth:`_sql.ColumnOperators.match` attempts to resolve to
989 a MATCH-like function or operator provided by the backend.
990 Examples include:
992 * PostgreSQL - renders ``x @@ to_tsquery(y)``
993 * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)``
995 .. seealso::
997 :class:`_mysql.match` - MySQL specific construct with
998 additional features.
1000 * Oracle - renders ``CONTAINS(x, y)``
1001 * other backends may provide special implementations.
1002 * Backends without any special implementation will emit
1003 the operator as "MATCH". This is compatible with SQLite, for
1004 example.
1006 """
1007 return self.operate(match_op, other, **kwargs)
1009 def regexp_match(self, pattern, flags=None):
1010 """Implements a database-specific 'regexp match' operator.
1012 E.g.::
1014 stmt = select(table.c.some_column).where(
1015 table.c.some_column.regexp_match('^(b|c)')
1016 )
1018 :meth:`_sql.ColumnOperators.regexp_match` attempts to resolve to
1019 a REGEXP-like function or operator provided by the backend, however
1020 the specific regular expression syntax and flags available are
1021 **not backend agnostic**.
1023 Examples include:
1025 * PostgreSQL - renders ``x ~ y`` or ``x !~ y`` when negated.
1026 * Oracle - renders ``REGEXP_LIKE(x, y)``
1027 * SQLite - uses SQLite's ``REGEXP`` placeholder operator and calls into
1028 the Python ``re.match()`` builtin.
1029 * other backends may provide special implementations.
1030 * Backends without any special implementation will emit
1031 the operator as "REGEXP" or "NOT REGEXP". This is compatible with
1032 SQLite and MySQL, for example.
1034 Regular expression support is currently implemented for Oracle,
1035 PostgreSQL, MySQL and MariaDB. Partial support is available for
1036 SQLite. Support among third-party dialects may vary.
1038 :param pattern: The regular expression pattern string or column
1039 clause.
1040 :param flags: Any regular expression string flags to apply, passed as
1041 plain Python string only. These flags are backend specific.
1042 Some backends, like PostgreSQL and MariaDB, may alternatively
1043 specify the flags as part of the pattern.
1044 When using the ignore case flag 'i' in PostgreSQL, the ignore case
1045 regexp match operator ``~*`` or ``!~*`` will be used.
1047 .. versionadded:: 1.4
1049 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation
1050 error, the "flags" parameter previously accepted SQL expression
1051 objects such as column expressions in addition to plain Python
1052 strings. This implementation did not work correctly with caching
1053 and was removed; strings only should be passed for the "flags"
1054 parameter, as these flags are rendered as literal inline values
1055 within SQL expressions.
1057 .. seealso::
1059 :meth:`_sql.ColumnOperators.regexp_replace`
1062 """
1063 return self.operate(regexp_match_op, pattern, flags=flags)
1065 def regexp_replace(self, pattern, replacement, flags=None):
1066 """Implements a database-specific 'regexp replace' operator.
1068 E.g.::
1070 stmt = select(
1071 table.c.some_column.regexp_replace(
1072 'b(..)',
1073 'X\1Y',
1074 flags='g'
1075 )
1076 )
1078 :meth:`_sql.ColumnOperators.regexp_replace` attempts to resolve to
1079 a REGEXP_REPLACE-like function provided by the backend, that
1080 usually emit the function ``REGEXP_REPLACE()``. However,
1081 the specific regular expression syntax and flags available are
1082 **not backend agnostic**.
1084 Regular expression replacement support is currently implemented for
1085 Oracle, PostgreSQL, MySQL 8 or greater and MariaDB. Support among
1086 third-party dialects may vary.
1088 :param pattern: The regular expression pattern string or column
1089 clause.
1090 :param pattern: The replacement string or column clause.
1091 :param flags: Any regular expression string flags to apply, passed as
1092 plain Python string only. These flags are backend specific.
1093 Some backends, like PostgreSQL and MariaDB, may alternatively
1094 specify the flags as part of the pattern.
1096 .. versionadded:: 1.4
1098 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation
1099 error, the "flags" parameter previously accepted SQL expression
1100 objects such as column expressions in addition to plain Python
1101 strings. This implementation did not work correctly with caching
1102 and was removed; strings only should be passed for the "flags"
1103 parameter, as these flags are rendered as literal inline values
1104 within SQL expressions.
1107 .. seealso::
1109 :meth:`_sql.ColumnOperators.regexp_match`
1111 """
1112 return self.operate(
1113 regexp_replace_op, pattern, replacement=replacement, flags=flags
1114 )
1116 def desc(self):
1117 """Produce a :func:`_expression.desc` clause against the
1118 parent object."""
1119 return self.operate(desc_op)
1121 def asc(self):
1122 """Produce a :func:`_expression.asc` clause against the
1123 parent object."""
1124 return self.operate(asc_op)
1126 def nulls_first(self):
1127 """Produce a :func:`_expression.nulls_first` clause against the
1128 parent object.
1130 .. versionchanged:: 1.4 The ``nulls_first()`` operator is
1131 renamed from ``nullsfirst()`` in previous releases.
1132 The previous name remains available for backwards compatibility.
1133 """
1134 return self.operate(nulls_first_op)
1136 # deprecated 1.4; see #5435
1137 nullsfirst = nulls_first
1139 def nulls_last(self):
1140 """Produce a :func:`_expression.nulls_last` clause against the
1141 parent object.
1143 .. versionchanged:: 1.4 The ``nulls_last()`` operator is
1144 renamed from ``nullslast()`` in previous releases.
1145 The previous name remains available for backwards compatibility.
1146 """
1147 return self.operate(nulls_last_op)
1149 # deprecated 1.4; see #5429
1150 nullslast = nulls_last
1152 def collate(self, collation):
1153 """Produce a :func:`_expression.collate` clause against
1154 the parent object, given the collation string.
1156 .. seealso::
1158 :func:`_expression.collate`
1160 """
1161 return self.operate(collate, collation)
1163 def __radd__(self, other):
1164 """Implement the ``+`` operator in reverse.
1166 See :meth:`.ColumnOperators.__add__`.
1168 """
1169 return self.reverse_operate(add, other)
1171 def __rsub__(self, other):
1172 """Implement the ``-`` operator in reverse.
1174 See :meth:`.ColumnOperators.__sub__`.
1176 """
1177 return self.reverse_operate(sub, other)
1179 def __rmul__(self, other):
1180 """Implement the ``*`` operator in reverse.
1182 See :meth:`.ColumnOperators.__mul__`.
1184 """
1185 return self.reverse_operate(mul, other)
1187 def __rdiv__(self, other):
1188 """Implement the ``/`` operator in reverse.
1190 See :meth:`.ColumnOperators.__div__`.
1192 """
1193 return self.reverse_operate(div, other)
1195 def __rmod__(self, other):
1196 """Implement the ``%`` operator in reverse.
1198 See :meth:`.ColumnOperators.__mod__`.
1200 """
1201 return self.reverse_operate(mod, other)
1203 def between(self, cleft, cright, symmetric=False):
1204 """Produce a :func:`_expression.between` clause against
1205 the parent object, given the lower and upper range.
1207 """
1208 return self.operate(between_op, cleft, cright, symmetric=symmetric)
1210 def distinct(self):
1211 """Produce a :func:`_expression.distinct` clause against the
1212 parent object.
1214 """
1215 return self.operate(distinct_op)
1217 def any_(self):
1218 """Produce an :func:`_expression.any_` clause against the
1219 parent object.
1221 See the documentation for :func:`_sql.any_` for examples.
1223 .. note:: be sure to not confuse the newer
1224 :meth:`_sql.ColumnOperators.any_` method with its older
1225 :class:`_types.ARRAY`-specific counterpart, the
1226 :meth:`_types.ARRAY.Comparator.any` method, which a different
1227 calling syntax and usage pattern.
1229 .. versionadded:: 1.1
1231 """
1232 return self.operate(any_op)
1234 def all_(self):
1235 """Produce an :func:`_expression.all_` clause against the
1236 parent object.
1238 See the documentation for :func:`_sql.all_` for examples.
1240 .. note:: be sure to not confuse the newer
1241 :meth:`_sql.ColumnOperators.all_` method with its older
1242 :class:`_types.ARRAY`-specific counterpart, the
1243 :meth:`_types.ARRAY.Comparator.all` method, which a different
1244 calling syntax and usage pattern.
1247 .. versionadded:: 1.1
1249 """
1250 return self.operate(all_op)
1252 def __add__(self, other):
1253 """Implement the ``+`` operator.
1255 In a column context, produces the clause ``a + b``
1256 if the parent object has non-string affinity.
1257 If the parent object has a string affinity,
1258 produces the concatenation operator, ``a || b`` -
1259 see :meth:`.ColumnOperators.concat`.
1261 """
1262 return self.operate(add, other)
1264 def __sub__(self, other):
1265 """Implement the ``-`` operator.
1267 In a column context, produces the clause ``a - b``.
1269 """
1270 return self.operate(sub, other)
1272 def __mul__(self, other):
1273 """Implement the ``*`` operator.
1275 In a column context, produces the clause ``a * b``.
1277 """
1278 return self.operate(mul, other)
1280 def __div__(self, other):
1281 """Implement the ``/`` operator.
1283 In a column context, produces the clause ``a / b``.
1285 """
1286 return self.operate(div, other)
1288 def __mod__(self, other):
1289 """Implement the ``%`` operator.
1291 In a column context, produces the clause ``a % b``.
1293 """
1294 return self.operate(mod, other)
1296 def __truediv__(self, other):
1297 """Implement the ``//`` operator.
1299 In a column context, produces the clause ``a / b``.
1301 """
1302 return self.operate(truediv, other)
1304 def __rtruediv__(self, other):
1305 """Implement the ``//`` operator in reverse.
1307 See :meth:`.ColumnOperators.__truediv__`.
1309 """
1310 return self.reverse_operate(truediv, other)
1313_commutative = {eq, ne, add, mul}
1314_comparison = {eq, ne, lt, gt, ge, le}
1317def commutative_op(fn):
1318 _commutative.add(fn)
1319 return fn
1322def comparison_op(fn):
1323 _comparison.add(fn)
1324 return fn
1327def from_():
1328 raise NotImplementedError()
1331@comparison_op
1332def function_as_comparison_op():
1333 raise NotImplementedError()
1336def as_():
1337 raise NotImplementedError()
1340def exists():
1341 raise NotImplementedError()
1344def is_true(a):
1345 raise NotImplementedError()
1348# 1.4 deprecated; see #5435
1349istrue = is_true
1352def is_false(a):
1353 raise NotImplementedError()
1356# 1.4 deprecated; see #5435
1357isfalse = is_false
1360@comparison_op
1361def is_distinct_from(a, b):
1362 return a.is_distinct_from(b)
1365@comparison_op
1366def is_not_distinct_from(a, b):
1367 return a.is_not_distinct_from(b)
1370# deprecated 1.4; see #5435
1371isnot_distinct_from = is_not_distinct_from
1374@comparison_op
1375def is_(a, b):
1376 return a.is_(b)
1379@comparison_op
1380def is_not(a, b):
1381 return a.is_not(b)
1384# 1.4 deprecated; see #5429
1385isnot = is_not
1388def collate(a, b):
1389 return a.collate(b)
1392def op(a, opstring, b):
1393 return a.op(opstring)(b)
1396@comparison_op
1397def like_op(a, b, escape=None):
1398 return a.like(b, escape=escape)
1401@comparison_op
1402def not_like_op(a, b, escape=None):
1403 return a.notlike(b, escape=escape)
1406# 1.4 deprecated; see #5435
1407notlike_op = not_like_op
1410@comparison_op
1411def ilike_op(a, b, escape=None):
1412 return a.ilike(b, escape=escape)
1415@comparison_op
1416def not_ilike_op(a, b, escape=None):
1417 return a.not_ilike(b, escape=escape)
1420# 1.4 deprecated; see #5435
1421notilike_op = not_ilike_op
1424@comparison_op
1425def between_op(a, b, c, symmetric=False):
1426 return a.between(b, c, symmetric=symmetric)
1429@comparison_op
1430def not_between_op(a, b, c, symmetric=False):
1431 return ~a.between(b, c, symmetric=symmetric)
1434# 1.4 deprecated; see #5435
1435notbetween_op = not_between_op
1438@comparison_op
1439def in_op(a, b):
1440 return a.in_(b)
1443@comparison_op
1444def not_in_op(a, b):
1445 return a.not_in(b)
1448# 1.4 deprecated; see #5429
1449notin_op = not_in_op
1452def distinct_op(a):
1453 return a.distinct()
1456def any_op(a):
1457 return a.any_()
1460def all_op(a):
1461 return a.all_()
1464def _escaped_like_impl(fn, other, escape, autoescape):
1465 if autoescape:
1466 if autoescape is not True:
1467 util.warn(
1468 "The autoescape parameter is now a simple boolean True/False"
1469 )
1470 if escape is None:
1471 escape = "/"
1473 if not isinstance(other, util.compat.string_types):
1474 raise TypeError("String value expected when autoescape=True")
1476 if escape not in ("%", "_"):
1477 other = other.replace(escape, escape + escape)
1479 other = other.replace("%", escape + "%").replace("_", escape + "_")
1481 return fn(other, escape=escape)
1484@comparison_op
1485def startswith_op(a, b, escape=None, autoescape=False):
1486 return _escaped_like_impl(a.startswith, b, escape, autoescape)
1489@comparison_op
1490def not_startswith_op(a, b, escape=None, autoescape=False):
1491 return ~_escaped_like_impl(a.startswith, b, escape, autoescape)
1494# 1.4 deprecated; see #5435
1495notstartswith_op = not_startswith_op
1498@comparison_op
1499def endswith_op(a, b, escape=None, autoescape=False):
1500 return _escaped_like_impl(a.endswith, b, escape, autoescape)
1503@comparison_op
1504def not_endswith_op(a, b, escape=None, autoescape=False):
1505 return ~_escaped_like_impl(a.endswith, b, escape, autoescape)
1508# 1.4 deprecated; see #5435
1509notendswith_op = not_endswith_op
1512@comparison_op
1513def contains_op(a, b, escape=None, autoescape=False):
1514 return _escaped_like_impl(a.contains, b, escape, autoescape)
1517@comparison_op
1518def not_contains_op(a, b, escape=None, autoescape=False):
1519 return ~_escaped_like_impl(a.contains, b, escape, autoescape)
1522# 1.4 deprecated; see #5435
1523notcontains_op = not_contains_op
1526@comparison_op
1527def match_op(a, b, **kw):
1528 return a.match(b, **kw)
1531@comparison_op
1532def regexp_match_op(a, b, flags=None):
1533 return a.regexp_match(b, flags=flags)
1536@comparison_op
1537def not_regexp_match_op(a, b, flags=None):
1538 return ~a.regexp_match(b, flags=flags)
1541def regexp_replace_op(a, b, replacement, flags=None):
1542 return a.regexp_replace(b, replacement=replacement, flags=flags)
1545@comparison_op
1546def not_match_op(a, b, **kw):
1547 return ~a.match(b, **kw)
1550# 1.4 deprecated; see #5429
1551notmatch_op = not_match_op
1554def comma_op(a, b):
1555 raise NotImplementedError()
1558def filter_op(a, b):
1559 raise NotImplementedError()
1562def concat_op(a, b):
1563 try:
1564 concat = a.concat
1565 except AttributeError:
1566 return b._rconcat(a)
1567 else:
1568 return concat(b)
1571def desc_op(a):
1572 return a.desc()
1575def asc_op(a):
1576 return a.asc()
1579def nulls_first_op(a):
1580 return a.nulls_first()
1583# 1.4 deprecated; see #5435
1584nullsfirst_op = nulls_first_op
1587def nulls_last_op(a):
1588 return a.nulls_last()
1591# 1.4 deprecated; see #5435
1592nullslast_op = nulls_last_op
1595def json_getitem_op(a, b):
1596 raise NotImplementedError()
1599def json_path_getitem_op(a, b):
1600 raise NotImplementedError()
1603def is_comparison(op):
1604 return op in _comparison or isinstance(op, custom_op) and op.is_comparison
1607def is_commutative(op):
1608 return op in _commutative
1611def is_ordering_modifier(op):
1612 return op in (asc_op, desc_op, nulls_first_op, nulls_last_op)
1615def is_natural_self_precedent(op):
1616 return (
1617 op in _natural_self_precedent
1618 or isinstance(op, custom_op)
1619 and op.natural_self_precedent
1620 )
1623_booleans = (inv, is_true, is_false, and_, or_)
1626def is_boolean(op):
1627 return is_comparison(op) or op in _booleans
1630_mirror = {gt: lt, ge: le, lt: gt, le: ge}
1633def mirror(op):
1634 """rotate a comparison operator 180 degrees.
1636 Note this is not the same as negation.
1638 """
1639 return _mirror.get(op, op)
1642_associative = _commutative.union([concat_op, and_, or_]).difference([eq, ne])
1645def is_associative(op):
1646 return op in _associative
1649_natural_self_precedent = _associative.union(
1650 [getitem, json_getitem_op, json_path_getitem_op]
1651)
1652"""Operators where if we have (a op b) op c, we don't want to
1653parenthesize (a op b).
1655"""
1658_asbool = util.symbol("_asbool", canonical=-10)
1659_smallest = util.symbol("_smallest", canonical=-100)
1660_largest = util.symbol("_largest", canonical=100)
1662_PRECEDENCE = {
1663 from_: 15,
1664 function_as_comparison_op: 15,
1665 any_op: 15,
1666 all_op: 15,
1667 getitem: 15,
1668 json_getitem_op: 15,
1669 json_path_getitem_op: 15,
1670 mul: 8,
1671 truediv: 8,
1672 div: 8,
1673 mod: 8,
1674 neg: 8,
1675 add: 7,
1676 sub: 7,
1677 concat_op: 6,
1678 filter_op: 6,
1679 match_op: 5,
1680 not_match_op: 5,
1681 regexp_match_op: 5,
1682 not_regexp_match_op: 5,
1683 regexp_replace_op: 5,
1684 ilike_op: 5,
1685 not_ilike_op: 5,
1686 like_op: 5,
1687 not_like_op: 5,
1688 in_op: 5,
1689 not_in_op: 5,
1690 is_: 5,
1691 is_not: 5,
1692 eq: 5,
1693 ne: 5,
1694 is_distinct_from: 5,
1695 is_not_distinct_from: 5,
1696 gt: 5,
1697 lt: 5,
1698 ge: 5,
1699 le: 5,
1700 between_op: 5,
1701 not_between_op: 5,
1702 distinct_op: 5,
1703 inv: 5,
1704 is_true: 5,
1705 is_false: 5,
1706 and_: 3,
1707 or_: 2,
1708 comma_op: -1,
1709 desc_op: 3,
1710 asc_op: 3,
1711 collate: 4,
1712 as_: -1,
1713 exists: 0,
1714 _asbool: -10,
1715 _smallest: _smallest,
1716 _largest: _largest,
1717}
1720def is_precedent(operator, against):
1721 if operator is against and is_natural_self_precedent(operator):
1722 return False
1723 else:
1724 return _PRECEDENCE.get(
1725 operator, getattr(operator, "precedence", _smallest)
1726 ) <= _PRECEDENCE.get(against, getattr(against, "precedence", _largest))