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-2021 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: http://www.opensource.org/licenses/mit-license.php
8# This module is part of SQLAlchemy and is released under
9# the MIT License: http://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 to apply to the operator, when
149 parenthesizing expressions. A lower number will cause the expression
150 to be parenthesized when applied against another operator with
151 higher precedence. The default value of ``0`` is lower than all
152 operators except for the comma (``,``) and ``AS`` operators.
153 A value of 100 will be higher or equal to all operators, and -100
154 will be lower than or equal to all operators.
156 :param is_comparison: if True, the operator will be considered as a
157 "comparison" operator, that is which evaluates to a boolean
158 true/false value, like ``==``, ``>``, etc. This flag should be set
159 so that ORM relationships can establish that the operator is a
160 comparison operator when used in a custom join condition.
162 .. versionadded:: 0.9.2 - added the
163 :paramref:`.Operators.op.is_comparison` flag.
165 :param return_type: a :class:`.TypeEngine` class or object that will
166 force the return type of an expression produced by this operator
167 to be of that type. By default, operators that specify
168 :paramref:`.Operators.op.is_comparison` will resolve to
169 :class:`.Boolean`, and those that do not will be of the same
170 type as the left-hand operand.
172 .. versionadded:: 1.2.0b3 - added the
173 :paramref:`.Operators.op.return_type` argument.
175 .. seealso::
177 :ref:`types_operators`
179 :ref:`relationship_custom_operator`
181 """
182 operator = custom_op(opstring, precedence, is_comparison, return_type)
184 def against(other):
185 return operator(self, other)
187 return against
189 def bool_op(self, opstring, precedence=0):
190 """Return a custom boolean operator.
192 This method is shorthand for calling
193 :meth:`.Operators.op` and passing the
194 :paramref:`.Operators.op.is_comparison`
195 flag with True.
197 .. versionadded:: 1.2.0b3
199 .. seealso::
201 :meth:`.Operators.op`
203 """
204 return self.op(opstring, precedence=precedence, is_comparison=True)
206 def operate(self, op, *other, **kwargs):
207 r"""Operate on an argument.
209 This is the lowest level of operation, raises
210 :class:`NotImplementedError` by default.
212 Overriding this on a subclass can allow common
213 behavior to be applied to all operations.
214 For example, overriding :class:`.ColumnOperators`
215 to apply ``func.lower()`` to the left and right
216 side::
218 class MyComparator(ColumnOperators):
219 def operate(self, op, other):
220 return op(func.lower(self), func.lower(other))
222 :param op: Operator callable.
223 :param \*other: the 'other' side of the operation. Will
224 be a single scalar for most operations.
225 :param \**kwargs: modifiers. These may be passed by special
226 operators such as :meth:`ColumnOperators.contains`.
229 """
230 raise NotImplementedError(str(op))
232 def reverse_operate(self, op, other, **kwargs):
233 """Reverse operate on an argument.
235 Usage is the same as :meth:`operate`.
237 """
238 raise NotImplementedError(str(op))
241class custom_op(object):
242 """Represent a 'custom' operator.
244 :class:`.custom_op` is normally instantiated when the
245 :meth:`.Operators.op` or :meth:`.Operators.bool_op` methods
246 are used to create a custom operator callable. The class can also be
247 used directly when programmatically constructing expressions. E.g.
248 to represent the "factorial" operation::
250 from sqlalchemy.sql import UnaryExpression
251 from sqlalchemy.sql import operators
252 from sqlalchemy import Numeric
254 unary = UnaryExpression(table.c.somecolumn,
255 modifier=operators.custom_op("!"),
256 type_=Numeric)
259 .. seealso::
261 :meth:`.Operators.op`
263 :meth:`.Operators.bool_op`
265 """
267 __name__ = "custom_op"
269 def __init__(
270 self,
271 opstring,
272 precedence=0,
273 is_comparison=False,
274 return_type=None,
275 natural_self_precedent=False,
276 eager_grouping=False,
277 ):
278 self.opstring = opstring
279 self.precedence = precedence
280 self.is_comparison = is_comparison
281 self.natural_self_precedent = natural_self_precedent
282 self.eager_grouping = eager_grouping
283 self.return_type = (
284 return_type._to_instance(return_type) if return_type else None
285 )
287 def __eq__(self, other):
288 return isinstance(other, custom_op) and other.opstring == self.opstring
290 def __hash__(self):
291 return id(self)
293 def __call__(self, left, right, **kw):
294 return left.operate(self, right, **kw)
297class ColumnOperators(Operators):
298 """Defines boolean, comparison, and other operators for
299 :class:`_expression.ColumnElement` expressions.
301 By default, all methods call down to
302 :meth:`.operate` or :meth:`.reverse_operate`,
303 passing in the appropriate operator function from the
304 Python builtin ``operator`` module or
305 a SQLAlchemy-specific operator function from
306 :mod:`sqlalchemy.expression.operators`. For example
307 the ``__eq__`` function::
309 def __eq__(self, other):
310 return self.operate(operators.eq, other)
312 Where ``operators.eq`` is essentially::
314 def eq(a, b):
315 return a == b
317 The core column expression unit :class:`_expression.ColumnElement`
318 overrides :meth:`.Operators.operate` and others
319 to return further :class:`_expression.ColumnElement` constructs,
320 so that the ``==`` operation above is replaced by a clause
321 construct.
323 .. seealso::
325 :ref:`types_operators`
327 :attr:`.TypeEngine.comparator_factory`
329 :class:`.ColumnOperators`
331 :class:`.PropComparator`
333 """
335 __slots__ = ()
337 timetuple = None
338 """Hack, allows datetime objects to be compared on the LHS."""
340 def __lt__(self, other):
341 """Implement the ``<`` operator.
343 In a column context, produces the clause ``a < b``.
345 """
346 return self.operate(lt, other)
348 def __le__(self, other):
349 """Implement the ``<=`` operator.
351 In a column context, produces the clause ``a <= b``.
353 """
354 return self.operate(le, other)
356 __hash__ = Operators.__hash__
358 def __eq__(self, other):
359 """Implement the ``==`` operator.
361 In a column context, produces the clause ``a = b``.
362 If the target is ``None``, produces ``a IS NULL``.
364 """
365 return self.operate(eq, other)
367 def __ne__(self, other):
368 """Implement the ``!=`` operator.
370 In a column context, produces the clause ``a != b``.
371 If the target is ``None``, produces ``a IS NOT NULL``.
373 """
374 return self.operate(ne, other)
376 def is_distinct_from(self, other):
377 """Implement the ``IS DISTINCT FROM`` operator.
379 Renders "a IS DISTINCT FROM b" on most platforms;
380 on some such as SQLite may render "a IS NOT b".
382 .. versionadded:: 1.1
384 """
385 return self.operate(is_distinct_from, other)
387 def isnot_distinct_from(self, other):
388 """Implement the ``IS NOT DISTINCT FROM`` operator.
390 Renders "a IS NOT DISTINCT FROM b" on most platforms;
391 on some such as SQLite may render "a IS b".
393 .. versionadded:: 1.1
395 """
396 return self.operate(isnot_distinct_from, other)
398 def __gt__(self, other):
399 """Implement the ``>`` operator.
401 In a column context, produces the clause ``a > b``.
403 """
404 return self.operate(gt, other)
406 def __ge__(self, other):
407 """Implement the ``>=`` operator.
409 In a column context, produces the clause ``a >= b``.
411 """
412 return self.operate(ge, other)
414 def __neg__(self):
415 """Implement the ``-`` operator.
417 In a column context, produces the clause ``-a``.
419 """
420 return self.operate(neg)
422 def __contains__(self, other):
423 return self.operate(contains, other)
425 def __getitem__(self, index):
426 """Implement the [] operator.
428 This can be used by some database-specific types
429 such as PostgreSQL ARRAY and HSTORE.
431 """
432 return self.operate(getitem, index)
434 def __lshift__(self, other):
435 """implement the << operator.
437 Not used by SQLAlchemy core, this is provided
438 for custom operator systems which want to use
439 << as an extension point.
440 """
441 return self.operate(lshift, other)
443 def __rshift__(self, other):
444 """implement the >> operator.
446 Not used by SQLAlchemy core, this is provided
447 for custom operator systems which want to use
448 >> as an extension point.
449 """
450 return self.operate(rshift, other)
452 def concat(self, other):
453 """Implement the 'concat' operator.
455 In a column context, produces the clause ``a || b``,
456 or uses the ``concat()`` operator on MySQL.
458 """
459 return self.operate(concat_op, other)
461 def like(self, other, escape=None):
462 r"""Implement the ``like`` operator.
464 In a column context, produces the expression::
466 a LIKE other
468 E.g.::
470 stmt = select([sometable]).\
471 where(sometable.c.column.like("%foobar%"))
473 :param other: expression to be compared
474 :param escape: optional escape character, renders the ``ESCAPE``
475 keyword, e.g.::
477 somecolumn.like("foo/%bar", escape="/")
479 .. seealso::
481 :meth:`.ColumnOperators.ilike`
483 """
484 return self.operate(like_op, other, escape=escape)
486 def ilike(self, other, escape=None):
487 r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE.
489 In a column context, produces an expression either of the form::
491 lower(a) LIKE lower(other)
493 Or on backends that support the ILIKE operator::
495 a ILIKE other
497 E.g.::
499 stmt = select([sometable]).\
500 where(sometable.c.column.ilike("%foobar%"))
502 :param other: expression to be compared
503 :param escape: optional escape character, renders the ``ESCAPE``
504 keyword, e.g.::
506 somecolumn.ilike("foo/%bar", escape="/")
508 .. seealso::
510 :meth:`.ColumnOperators.like`
512 """
513 return self.operate(ilike_op, other, escape=escape)
515 def in_(self, other):
516 """Implement the ``in`` operator.
518 In a column context, produces the clause ``column IN <other>``.
520 The given parameter ``other`` may be:
522 * A list of literal values, e.g.::
524 stmt.where(column.in_([1, 2, 3]))
526 In this calling form, the list of items is converted to a set of
527 bound parameters the same length as the list given::
529 WHERE COL IN (?, ?, ?)
531 * A list of tuples may be provided if the comparison is against a
532 :func:`.tuple_` containing multiple expressions::
534 from sqlalchemy import tuple_
535 stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
537 * An empty list, e.g.::
539 stmt.where(column.in_([]))
541 In this calling form, the expression renders a "false" expression,
542 e.g.::
544 WHERE 1 != 1
546 This "false" expression has historically had different behaviors
547 in older SQLAlchemy versions, see
548 :paramref:`_sa.create_engine.empty_in_strategy`
549 for behavioral options.
551 .. versionchanged:: 1.2 simplified the behavior of "empty in"
552 expressions
554 * A bound parameter, e.g. :func:`.bindparam`, may be used if it
555 includes the :paramref:`.bindparam.expanding` flag::
557 stmt.where(column.in_(bindparam('value', expanding=True)))
559 In this calling form, the expression renders a special non-SQL
560 placeholder expression that looks like::
562 WHERE COL IN ([EXPANDING_value])
564 This placeholder expression is intercepted at statement execution
565 time to be converted into the variable number of bound parameter
566 form illustrated earlier. If the statement were executed as::
568 connection.execute(stmt, {"value": [1, 2, 3]})
570 The database would be passed a bound parameter for each value::
572 WHERE COL IN (?, ?, ?)
574 .. versionadded:: 1.2 added "expanding" bound parameters
576 If an empty list is passed, a special "empty list" expression,
577 which is specific to the database in use, is rendered. On
578 SQLite this would be::
580 WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
582 .. versionadded:: 1.3 "expanding" bound parameters now support
583 empty lists
585 * a :func:`_expression.select` construct,
586 which is usually a correlated
587 scalar select::
589 stmt.where(
590 column.in_(
591 select([othertable.c.y]).
592 where(table.c.x == othertable.c.x)
593 )
594 )
596 In this calling form, :meth:`.ColumnOperators.in_` renders as given::
598 WHERE COL IN (SELECT othertable.y
599 FROM othertable WHERE othertable.x = table.x)
601 :param other: a list of literals, a :func:`_expression.select`
602 construct,
603 or a :func:`.bindparam` construct that includes the
604 :paramref:`.bindparam.expanding` flag set to True.
606 """
607 return self.operate(in_op, other)
609 def notin_(self, other):
610 """implement the ``NOT IN`` operator.
612 This is equivalent to using negation with
613 :meth:`.ColumnOperators.in_`, i.e. ``~x.in_(y)``.
615 In the case that ``other`` is an empty sequence, the compiler
616 produces an "empty not in" expression. This defaults to the
617 expression "1 = 1" to produce true in all cases. The
618 :paramref:`_sa.create_engine.empty_in_strategy` may be used to
619 alter this behavior.
621 .. versionchanged:: 1.2 The :meth:`.ColumnOperators.in_` and
622 :meth:`.ColumnOperators.notin_` operators
623 now produce a "static" expression for an empty IN sequence
624 by default.
626 .. seealso::
628 :meth:`.ColumnOperators.in_`
630 """
631 return self.operate(notin_op, other)
633 def notlike(self, other, escape=None):
634 """implement the ``NOT LIKE`` operator.
636 This is equivalent to using negation with
637 :meth:`.ColumnOperators.like`, i.e. ``~x.like(y)``.
639 .. seealso::
641 :meth:`.ColumnOperators.like`
643 """
644 return self.operate(notlike_op, other, escape=escape)
646 def notilike(self, other, escape=None):
647 """implement the ``NOT ILIKE`` operator.
649 This is equivalent to using negation with
650 :meth:`.ColumnOperators.ilike`, i.e. ``~x.ilike(y)``.
652 .. seealso::
654 :meth:`.ColumnOperators.ilike`
656 """
657 return self.operate(notilike_op, other, escape=escape)
659 def is_(self, other):
660 """Implement the ``IS`` operator.
662 Normally, ``IS`` is generated automatically when comparing to a
663 value of ``None``, which resolves to ``NULL``. However, explicit
664 usage of ``IS`` may be desirable if comparing to boolean values
665 on certain platforms.
667 .. seealso:: :meth:`.ColumnOperators.isnot`
669 """
670 return self.operate(is_, other)
672 def isnot(self, other):
673 """Implement the ``IS NOT`` operator.
675 Normally, ``IS NOT`` is generated automatically when comparing to a
676 value of ``None``, which resolves to ``NULL``. However, explicit
677 usage of ``IS NOT`` may be desirable if comparing to boolean values
678 on certain platforms.
680 .. seealso:: :meth:`.ColumnOperators.is_`
682 """
683 return self.operate(isnot, other)
685 def startswith(self, other, **kwargs):
686 r"""Implement the ``startswith`` operator.
688 Produces a LIKE expression that tests against a match for the start
689 of a string value::
691 column LIKE <other> || '%'
693 E.g.::
695 stmt = select([sometable]).\
696 where(sometable.c.column.startswith("foobar"))
698 Since the operator uses ``LIKE``, wildcard characters
699 ``"%"`` and ``"_"`` that are present inside the <other> expression
700 will behave like wildcards as well. For literal string
701 values, the :paramref:`.ColumnOperators.startswith.autoescape` flag
702 may be set to ``True`` to apply escaping to occurrences of these
703 characters within the string value so that they match as themselves
704 and not as wildcard characters. Alternatively, the
705 :paramref:`.ColumnOperators.startswith.escape` parameter will establish
706 a given character as an escape character which can be of use when
707 the target expression is not a literal string.
709 :param other: expression to be compared. This is usually a plain
710 string value, but can also be an arbitrary SQL expression. LIKE
711 wildcard characters ``%`` and ``_`` are not escaped by default unless
712 the :paramref:`.ColumnOperators.startswith.autoescape` flag is
713 set to True.
715 :param autoescape: boolean; when True, establishes an escape character
716 within the LIKE expression, then applies it to all occurrences of
717 ``"%"``, ``"_"`` and the escape character itself within the
718 comparison value, which is assumed to be a literal string and not a
719 SQL expression.
721 An expression such as::
723 somecolumn.startswith("foo%bar", autoescape=True)
725 Will render as::
727 somecolumn LIKE :param || '%' ESCAPE '/'
729 With the value of ``:param`` as ``"foo/%bar"``.
731 .. versionadded:: 1.2
733 .. versionchanged:: 1.2.0 The
734 :paramref:`.ColumnOperators.startswith.autoescape` parameter is
735 now a simple boolean rather than a character; the escape
736 character itself is also escaped, and defaults to a forwards
737 slash, which itself can be customized using the
738 :paramref:`.ColumnOperators.startswith.escape` parameter.
740 :param escape: a character which when given will render with the
741 ``ESCAPE`` keyword to establish that character as the escape
742 character. This character can then be placed preceding occurrences
743 of ``%`` and ``_`` to allow them to act as themselves and not
744 wildcard characters.
746 An expression such as::
748 somecolumn.startswith("foo/%bar", escape="^")
750 Will render as::
752 somecolumn LIKE :param || '%' ESCAPE '^'
754 The parameter may also be combined with
755 :paramref:`.ColumnOperators.startswith.autoescape`::
757 somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
759 Where above, the given literal parameter will be converted to
760 ``"foo^%bar^^bat"`` before being passed to the database.
762 .. seealso::
764 :meth:`.ColumnOperators.endswith`
766 :meth:`.ColumnOperators.contains`
768 :meth:`.ColumnOperators.like`
770 """
771 return self.operate(startswith_op, other, **kwargs)
773 def endswith(self, other, **kwargs):
774 r"""Implement the 'endswith' operator.
776 Produces a LIKE expression that tests against a match for the end
777 of a string value::
779 column LIKE '%' || <other>
781 E.g.::
783 stmt = select([sometable]).\
784 where(sometable.c.column.endswith("foobar"))
786 Since the operator uses ``LIKE``, wildcard characters
787 ``"%"`` and ``"_"`` that are present inside the <other> expression
788 will behave like wildcards as well. For literal string
789 values, the :paramref:`.ColumnOperators.endswith.autoescape` flag
790 may be set to ``True`` to apply escaping to occurrences of these
791 characters within the string value so that they match as themselves
792 and not as wildcard characters. Alternatively, the
793 :paramref:`.ColumnOperators.endswith.escape` parameter will establish
794 a given character as an escape character which can be of use when
795 the target expression is not a literal string.
797 :param other: expression to be compared. This is usually a plain
798 string value, but can also be an arbitrary SQL expression. LIKE
799 wildcard characters ``%`` and ``_`` are not escaped by default unless
800 the :paramref:`.ColumnOperators.endswith.autoescape` flag is
801 set to True.
803 :param autoescape: boolean; when True, establishes an escape character
804 within the LIKE expression, then applies it to all occurrences of
805 ``"%"``, ``"_"`` and the escape character itself within the
806 comparison value, which is assumed to be a literal string and not a
807 SQL expression.
809 An expression such as::
811 somecolumn.endswith("foo%bar", autoescape=True)
813 Will render as::
815 somecolumn LIKE '%' || :param ESCAPE '/'
817 With the value of ``:param`` as ``"foo/%bar"``.
819 .. versionadded:: 1.2
821 .. versionchanged:: 1.2.0 The
822 :paramref:`.ColumnOperators.endswith.autoescape` parameter is
823 now a simple boolean rather than a character; the escape
824 character itself is also escaped, and defaults to a forwards
825 slash, which itself can be customized using the
826 :paramref:`.ColumnOperators.endswith.escape` parameter.
828 :param escape: a character which when given will render with the
829 ``ESCAPE`` keyword to establish that character as the escape
830 character. This character can then be placed preceding occurrences
831 of ``%`` and ``_`` to allow them to act as themselves and not
832 wildcard characters.
834 An expression such as::
836 somecolumn.endswith("foo/%bar", escape="^")
838 Will render as::
840 somecolumn LIKE '%' || :param ESCAPE '^'
842 The parameter may also be combined with
843 :paramref:`.ColumnOperators.endswith.autoescape`::
845 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
847 Where above, the given literal parameter will be converted to
848 ``"foo^%bar^^bat"`` before being passed to the database.
850 .. seealso::
852 :meth:`.ColumnOperators.startswith`
854 :meth:`.ColumnOperators.contains`
856 :meth:`.ColumnOperators.like`
858 """
859 return self.operate(endswith_op, other, **kwargs)
861 def contains(self, other, **kwargs):
862 r"""Implement the 'contains' operator.
864 Produces a LIKE expression that tests against a match for the middle
865 of a string value::
867 column LIKE '%' || <other> || '%'
869 E.g.::
871 stmt = select([sometable]).\
872 where(sometable.c.column.contains("foobar"))
874 Since the operator uses ``LIKE``, wildcard characters
875 ``"%"`` and ``"_"`` that are present inside the <other> expression
876 will behave like wildcards as well. For literal string
877 values, the :paramref:`.ColumnOperators.contains.autoescape` flag
878 may be set to ``True`` to apply escaping to occurrences of these
879 characters within the string value so that they match as themselves
880 and not as wildcard characters. Alternatively, the
881 :paramref:`.ColumnOperators.contains.escape` parameter will establish
882 a given character as an escape character which can be of use when
883 the target expression is not a literal string.
885 :param other: expression to be compared. This is usually a plain
886 string value, but can also be an arbitrary SQL expression. LIKE
887 wildcard characters ``%`` and ``_`` are not escaped by default unless
888 the :paramref:`.ColumnOperators.contains.autoescape` flag is
889 set to True.
891 :param autoescape: boolean; when True, establishes an escape character
892 within the LIKE expression, then applies it to all occurrences of
893 ``"%"``, ``"_"`` and the escape character itself within the
894 comparison value, which is assumed to be a literal string and not a
895 SQL expression.
897 An expression such as::
899 somecolumn.contains("foo%bar", autoescape=True)
901 Will render as::
903 somecolumn LIKE '%' || :param || '%' ESCAPE '/'
905 With the value of ``:param`` as ``"foo/%bar"``.
907 .. versionadded:: 1.2
909 .. versionchanged:: 1.2.0 The
910 :paramref:`.ColumnOperators.contains.autoescape` parameter is
911 now a simple boolean rather than a character; the escape
912 character itself is also escaped, and defaults to a forwards
913 slash, which itself can be customized using the
914 :paramref:`.ColumnOperators.contains.escape` parameter.
916 :param escape: a character which when given will render with the
917 ``ESCAPE`` keyword to establish that character as the escape
918 character. This character can then be placed preceding occurrences
919 of ``%`` and ``_`` to allow them to act as themselves and not
920 wildcard characters.
922 An expression such as::
924 somecolumn.contains("foo/%bar", escape="^")
926 Will render as::
928 somecolumn LIKE '%' || :param || '%' ESCAPE '^'
930 The parameter may also be combined with
931 :paramref:`.ColumnOperators.contains.autoescape`::
933 somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
935 Where above, the given literal parameter will be converted to
936 ``"foo^%bar^^bat"`` before being passed to the database.
938 .. seealso::
940 :meth:`.ColumnOperators.startswith`
942 :meth:`.ColumnOperators.endswith`
944 :meth:`.ColumnOperators.like`
947 """
948 return self.operate(contains_op, other, **kwargs)
950 def match(self, other, **kwargs):
951 """Implements a database-specific 'match' operator.
953 :meth:`~.ColumnOperators.match` attempts to resolve to
954 a MATCH-like function or operator provided by the backend.
955 Examples include:
957 * PostgreSQL - renders ``x @@ to_tsquery(y)``
958 * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)``
959 * Oracle - renders ``CONTAINS(x, y)``
960 * other backends may provide special implementations.
961 * Backends without any special implementation will emit
962 the operator as "MATCH". This is compatible with SQLite, for
963 example.
965 """
966 return self.operate(match_op, other, **kwargs)
968 def desc(self):
969 """Produce a :func:`_expression.desc` clause against the
970 parent object."""
971 return self.operate(desc_op)
973 def asc(self):
974 """Produce a :func:`_expression.asc` clause against the
975 parent object."""
976 return self.operate(asc_op)
978 def nullsfirst(self):
979 """Produce a :func:`_expression.nullsfirst` clause against the
980 parent object."""
981 return self.operate(nullsfirst_op)
983 def nullslast(self):
984 """Produce a :func:`_expression.nullslast` clause against the
985 parent object."""
986 return self.operate(nullslast_op)
988 def collate(self, collation):
989 """Produce a :func:`_expression.collate` clause against
990 the parent object, given the collation string.
992 .. seealso::
994 :func:`_expression.collate`
996 """
997 return self.operate(collate, collation)
999 def __radd__(self, other):
1000 """Implement the ``+`` operator in reverse.
1002 See :meth:`.ColumnOperators.__add__`.
1004 """
1005 return self.reverse_operate(add, other)
1007 def __rsub__(self, other):
1008 """Implement the ``-`` operator in reverse.
1010 See :meth:`.ColumnOperators.__sub__`.
1012 """
1013 return self.reverse_operate(sub, other)
1015 def __rmul__(self, other):
1016 """Implement the ``*`` operator in reverse.
1018 See :meth:`.ColumnOperators.__mul__`.
1020 """
1021 return self.reverse_operate(mul, other)
1023 def __rdiv__(self, other):
1024 """Implement the ``/`` operator in reverse.
1026 See :meth:`.ColumnOperators.__div__`.
1028 """
1029 return self.reverse_operate(div, other)
1031 def __rmod__(self, other):
1032 """Implement the ``%`` operator in reverse.
1034 See :meth:`.ColumnOperators.__mod__`.
1036 """
1037 return self.reverse_operate(mod, other)
1039 def between(self, cleft, cright, symmetric=False):
1040 """Produce a :func:`_expression.between` clause against
1041 the parent object, given the lower and upper range.
1043 """
1044 return self.operate(between_op, cleft, cright, symmetric=symmetric)
1046 def distinct(self):
1047 """Produce a :func:`_expression.distinct` clause against the
1048 parent object.
1050 """
1051 return self.operate(distinct_op)
1053 def any_(self):
1054 """Produce a :func:`_expression.any_` clause against the
1055 parent object.
1057 This operator is only appropriate against a scalar subquery
1058 object, or for some backends an column expression that is
1059 against the ARRAY type, e.g.::
1061 # postgresql '5 = ANY (somearray)'
1062 expr = 5 == mytable.c.somearray.any_()
1064 # mysql '5 = ANY (SELECT value FROM table)'
1065 expr = 5 == select([table.c.value]).as_scalar().any_()
1067 .. seealso::
1069 :func:`_expression.any_` - standalone version
1071 :func:`_expression.all_` - ALL operator
1073 .. versionadded:: 1.1
1075 """
1076 return self.operate(any_op)
1078 def all_(self):
1079 """Produce a :func:`_expression.all_` clause against the
1080 parent object.
1082 This operator is only appropriate against a scalar subquery
1083 object, or for some backends an column expression that is
1084 against the ARRAY type, e.g.::
1086 # postgresql '5 = ALL (somearray)'
1087 expr = 5 == mytable.c.somearray.all_()
1089 # mysql '5 = ALL (SELECT value FROM table)'
1090 expr = 5 == select([table.c.value]).as_scalar().all_()
1092 .. seealso::
1094 :func:`_expression.all_` - standalone version
1096 :func:`_expression.any_` - ANY operator
1098 .. versionadded:: 1.1
1100 """
1101 return self.operate(all_op)
1103 def __add__(self, other):
1104 """Implement the ``+`` operator.
1106 In a column context, produces the clause ``a + b``
1107 if the parent object has non-string affinity.
1108 If the parent object has a string affinity,
1109 produces the concatenation operator, ``a || b`` -
1110 see :meth:`.ColumnOperators.concat`.
1112 """
1113 return self.operate(add, other)
1115 def __sub__(self, other):
1116 """Implement the ``-`` operator.
1118 In a column context, produces the clause ``a - b``.
1120 """
1121 return self.operate(sub, other)
1123 def __mul__(self, other):
1124 """Implement the ``*`` operator.
1126 In a column context, produces the clause ``a * b``.
1128 """
1129 return self.operate(mul, other)
1131 def __div__(self, other):
1132 """Implement the ``/`` operator.
1134 In a column context, produces the clause ``a / b``.
1136 """
1137 return self.operate(div, other)
1139 def __mod__(self, other):
1140 """Implement the ``%`` operator.
1142 In a column context, produces the clause ``a % b``.
1144 """
1145 return self.operate(mod, other)
1147 def __truediv__(self, other):
1148 """Implement the ``//`` operator.
1150 In a column context, produces the clause ``a / b``.
1152 """
1153 return self.operate(truediv, other)
1155 def __rtruediv__(self, other):
1156 """Implement the ``//`` operator in reverse.
1158 See :meth:`.ColumnOperators.__truediv__`.
1160 """
1161 return self.reverse_operate(truediv, other)
1164_commutative = {eq, ne, add, mul}
1165_comparison = {eq, ne, lt, gt, ge, le}
1168def commutative_op(fn):
1169 _commutative.add(fn)
1170 return fn
1173def comparison_op(fn):
1174 _comparison.add(fn)
1175 return fn
1178def from_():
1179 raise NotImplementedError()
1182@comparison_op
1183def function_as_comparison_op():
1184 raise NotImplementedError()
1187def as_():
1188 raise NotImplementedError()
1191def exists():
1192 raise NotImplementedError()
1195def istrue(a):
1196 raise NotImplementedError()
1199def isfalse(a):
1200 raise NotImplementedError()
1203@comparison_op
1204def is_distinct_from(a, b):
1205 return a.is_distinct_from(b)
1208@comparison_op
1209def isnot_distinct_from(a, b):
1210 return a.isnot_distinct_from(b)
1213@comparison_op
1214def is_(a, b):
1215 return a.is_(b)
1218@comparison_op
1219def isnot(a, b):
1220 return a.isnot(b)
1223def collate(a, b):
1224 return a.collate(b)
1227def op(a, opstring, b):
1228 return a.op(opstring)(b)
1231@comparison_op
1232def like_op(a, b, escape=None):
1233 return a.like(b, escape=escape)
1236@comparison_op
1237def notlike_op(a, b, escape=None):
1238 return a.notlike(b, escape=escape)
1241@comparison_op
1242def ilike_op(a, b, escape=None):
1243 return a.ilike(b, escape=escape)
1246@comparison_op
1247def notilike_op(a, b, escape=None):
1248 return a.notilike(b, escape=escape)
1251@comparison_op
1252def between_op(a, b, c, symmetric=False):
1253 return a.between(b, c, symmetric=symmetric)
1256@comparison_op
1257def notbetween_op(a, b, c, symmetric=False):
1258 return a.notbetween(b, c, symmetric=symmetric)
1261@comparison_op
1262def in_op(a, b):
1263 return a.in_(b)
1266@comparison_op
1267def notin_op(a, b):
1268 return a.notin_(b)
1271def distinct_op(a):
1272 return a.distinct()
1275def any_op(a):
1276 return a.any_()
1279def all_op(a):
1280 return a.all_()
1283def _escaped_like_impl(fn, other, escape, autoescape):
1284 if autoescape:
1285 if autoescape is not True:
1286 util.warn(
1287 "The autoescape parameter is now a simple boolean True/False"
1288 )
1289 if escape is None:
1290 escape = "/"
1292 if not isinstance(other, util.compat.string_types):
1293 raise TypeError("String value expected when autoescape=True")
1295 if escape not in ("%", "_"):
1296 other = other.replace(escape, escape + escape)
1298 other = other.replace("%", escape + "%").replace("_", escape + "_")
1300 return fn(other, escape=escape)
1303@comparison_op
1304def startswith_op(a, b, escape=None, autoescape=False):
1305 return _escaped_like_impl(a.startswith, b, escape, autoescape)
1308@comparison_op
1309def notstartswith_op(a, b, escape=None, autoescape=False):
1310 return ~_escaped_like_impl(a.startswith, b, escape, autoescape)
1313@comparison_op
1314def endswith_op(a, b, escape=None, autoescape=False):
1315 return _escaped_like_impl(a.endswith, b, escape, autoescape)
1318@comparison_op
1319def notendswith_op(a, b, escape=None, autoescape=False):
1320 return ~_escaped_like_impl(a.endswith, b, escape, autoescape)
1323@comparison_op
1324def contains_op(a, b, escape=None, autoescape=False):
1325 return _escaped_like_impl(a.contains, b, escape, autoescape)
1328@comparison_op
1329def notcontains_op(a, b, escape=None, autoescape=False):
1330 return ~_escaped_like_impl(a.contains, b, escape, autoescape)
1333@comparison_op
1334def match_op(a, b, **kw):
1335 return a.match(b, **kw)
1338@comparison_op
1339def notmatch_op(a, b, **kw):
1340 return a.notmatch(b, **kw)
1343def comma_op(a, b):
1344 raise NotImplementedError()
1347@comparison_op
1348def empty_in_op(a, b):
1349 raise NotImplementedError()
1352@comparison_op
1353def empty_notin_op(a, b):
1354 raise NotImplementedError()
1357def filter_op(a, b):
1358 raise NotImplementedError()
1361def concat_op(a, b):
1362 return a.concat(b)
1365def desc_op(a):
1366 return a.desc()
1369def asc_op(a):
1370 return a.asc()
1373def nullsfirst_op(a):
1374 return a.nullsfirst()
1377def nullslast_op(a):
1378 return a.nullslast()
1381def json_getitem_op(a, b):
1382 raise NotImplementedError()
1385def json_path_getitem_op(a, b):
1386 raise NotImplementedError()
1389def is_comparison(op):
1390 return op in _comparison or isinstance(op, custom_op) and op.is_comparison
1393def is_commutative(op):
1394 return op in _commutative
1397def is_ordering_modifier(op):
1398 return op in (asc_op, desc_op, nullsfirst_op, nullslast_op)
1401def is_natural_self_precedent(op):
1402 return (
1403 op in _natural_self_precedent
1404 or isinstance(op, custom_op)
1405 and op.natural_self_precedent
1406 )
1409_booleans = (inv, istrue, isfalse, and_, or_)
1412def is_boolean(op):
1413 return is_comparison(op) or op in _booleans
1416_mirror = {gt: lt, ge: le, lt: gt, le: ge}
1419def mirror(op):
1420 """rotate a comparison operator 180 degrees.
1422 Note this is not the same as negation.
1424 """
1425 return _mirror.get(op, op)
1428_associative = _commutative.union([concat_op, and_, or_]).difference([eq, ne])
1430_natural_self_precedent = _associative.union(
1431 [getitem, json_getitem_op, json_path_getitem_op]
1432)
1433"""Operators where if we have (a op b) op c, we don't want to
1434parenthesize (a op b).
1436"""
1439_asbool = util.symbol("_asbool", canonical=-10)
1440_smallest = util.symbol("_smallest", canonical=-100)
1441_largest = util.symbol("_largest", canonical=100)
1443_PRECEDENCE = {
1444 from_: 15,
1445 function_as_comparison_op: 15,
1446 any_op: 15,
1447 all_op: 15,
1448 getitem: 15,
1449 json_getitem_op: 15,
1450 json_path_getitem_op: 15,
1451 mul: 8,
1452 truediv: 8,
1453 div: 8,
1454 mod: 8,
1455 neg: 8,
1456 add: 7,
1457 sub: 7,
1458 concat_op: 6,
1459 filter_op: 6,
1460 match_op: 5,
1461 notmatch_op: 5,
1462 ilike_op: 5,
1463 notilike_op: 5,
1464 like_op: 5,
1465 notlike_op: 5,
1466 in_op: 5,
1467 notin_op: 5,
1468 is_: 5,
1469 isnot: 5,
1470 eq: 5,
1471 ne: 5,
1472 is_distinct_from: 5,
1473 isnot_distinct_from: 5,
1474 empty_in_op: 5,
1475 empty_notin_op: 5,
1476 gt: 5,
1477 lt: 5,
1478 ge: 5,
1479 le: 5,
1480 between_op: 5,
1481 notbetween_op: 5,
1482 distinct_op: 5,
1483 inv: 5,
1484 istrue: 5,
1485 isfalse: 5,
1486 and_: 3,
1487 or_: 2,
1488 comma_op: -1,
1489 desc_op: 3,
1490 asc_op: 3,
1491 collate: 4,
1492 as_: -1,
1493 exists: 0,
1494 _asbool: -10,
1495 _smallest: _smallest,
1496 _largest: _largest,
1497}
1500def is_precedent(operator, against):
1501 if operator is against and is_natural_self_precedent(operator):
1502 return False
1503 else:
1504 return _PRECEDENCE.get(
1505 operator, getattr(operator, "precedence", _smallest)
1506 ) <= _PRECEDENCE.get(against, getattr(against, "precedence", _largest))