Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/operators.py: 63%
342 statements
« prev ^ index » next coverage.py v7.0.1, created at 2022-12-25 06:11 +0000
1# sql/operators.py
2# Copyright (C) 2005-2022 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 isinstance(other, custom_op) and other.opstring == self.opstring
298 def __hash__(self):
299 return id(self)
301 def __call__(self, left, right, **kw):
302 return left.operate(self, right, **kw)
305class ColumnOperators(Operators):
306 """Defines boolean, comparison, and other operators for
307 :class:`_expression.ColumnElement` expressions.
309 By default, all methods call down to
310 :meth:`.operate` or :meth:`.reverse_operate`,
311 passing in the appropriate operator function from the
312 Python builtin ``operator`` module or
313 a SQLAlchemy-specific operator function from
314 :mod:`sqlalchemy.expression.operators`. For example
315 the ``__eq__`` function::
317 def __eq__(self, other):
318 return self.operate(operators.eq, other)
320 Where ``operators.eq`` is essentially::
322 def eq(a, b):
323 return a == b
325 The core column expression unit :class:`_expression.ColumnElement`
326 overrides :meth:`.Operators.operate` and others
327 to return further :class:`_expression.ColumnElement` constructs,
328 so that the ``==`` operation above is replaced by a clause
329 construct.
331 .. seealso::
333 :ref:`types_operators`
335 :attr:`.TypeEngine.comparator_factory`
337 :class:`.ColumnOperators`
339 :class:`.PropComparator`
341 """
343 __slots__ = ()
345 timetuple = None
346 """Hack, allows datetime objects to be compared on the LHS."""
348 def __lt__(self, other):
349 """Implement the ``<`` operator.
351 In a column context, produces the clause ``a < b``.
353 """
354 return self.operate(lt, other)
356 def __le__(self, other):
357 """Implement the ``<=`` operator.
359 In a column context, produces the clause ``a <= b``.
361 """
362 return self.operate(le, other)
364 __hash__ = Operators.__hash__
366 def __eq__(self, other):
367 """Implement the ``==`` operator.
369 In a column context, produces the clause ``a = b``.
370 If the target is ``None``, produces ``a IS NULL``.
372 """
373 return self.operate(eq, other)
375 def __ne__(self, other):
376 """Implement the ``!=`` operator.
378 In a column context, produces the clause ``a != b``.
379 If the target is ``None``, produces ``a IS NOT NULL``.
381 """
382 return self.operate(ne, other)
384 def is_distinct_from(self, other):
385 """Implement the ``IS DISTINCT FROM`` operator.
387 Renders "a IS DISTINCT FROM b" on most platforms;
388 on some such as SQLite may render "a IS NOT b".
390 .. versionadded:: 1.1
392 """
393 return self.operate(is_distinct_from, other)
395 def is_not_distinct_from(self, other):
396 """Implement the ``IS NOT DISTINCT FROM`` operator.
398 Renders "a IS NOT DISTINCT FROM b" on most platforms;
399 on some such as SQLite may render "a IS b".
401 .. versionchanged:: 1.4 The ``is_not_distinct_from()`` operator is
402 renamed from ``isnot_distinct_from()`` in previous releases.
403 The previous name remains available for backwards compatibility.
405 .. versionadded:: 1.1
407 """
408 return self.operate(is_not_distinct_from, other)
410 # deprecated 1.4; see #5435
411 isnot_distinct_from = is_not_distinct_from
413 def __gt__(self, other):
414 """Implement the ``>`` operator.
416 In a column context, produces the clause ``a > b``.
418 """
419 return self.operate(gt, other)
421 def __ge__(self, other):
422 """Implement the ``>=`` operator.
424 In a column context, produces the clause ``a >= b``.
426 """
427 return self.operate(ge, other)
429 def __neg__(self):
430 """Implement the ``-`` operator.
432 In a column context, produces the clause ``-a``.
434 """
435 return self.operate(neg)
437 def __contains__(self, other):
438 return self.operate(contains, other)
440 def __getitem__(self, index):
441 """Implement the [] operator.
443 This can be used by some database-specific types
444 such as PostgreSQL ARRAY and HSTORE.
446 """
447 return self.operate(getitem, index)
449 def __lshift__(self, other):
450 """implement the << operator.
452 Not used by SQLAlchemy core, this is provided
453 for custom operator systems which want to use
454 << as an extension point.
455 """
456 return self.operate(lshift, other)
458 def __rshift__(self, other):
459 """implement the >> operator.
461 Not used by SQLAlchemy core, this is provided
462 for custom operator systems which want to use
463 >> as an extension point.
464 """
465 return self.operate(rshift, other)
467 def concat(self, other):
468 """Implement the 'concat' operator.
470 In a column context, produces the clause ``a || b``,
471 or uses the ``concat()`` operator on MySQL.
473 """
474 return self.operate(concat_op, other)
476 def _rconcat(self, other):
477 """Implement an 'rconcat' operator.
479 this is for internal use at the moment
481 .. versionadded:: 1.4.40
483 """
484 return self.reverse_operate(concat_op, other)
486 def like(self, other, escape=None):
487 r"""Implement the ``like`` operator.
489 In a column context, produces the expression::
491 a LIKE other
493 E.g.::
495 stmt = select(sometable).\
496 where(sometable.c.column.like("%foobar%"))
498 :param other: expression to be compared
499 :param escape: optional escape character, renders the ``ESCAPE``
500 keyword, e.g.::
502 somecolumn.like("foo/%bar", escape="/")
504 .. seealso::
506 :meth:`.ColumnOperators.ilike`
508 """
509 return self.operate(like_op, other, escape=escape)
511 def ilike(self, other, escape=None):
512 r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE.
514 In a column context, produces an expression either of the form::
516 lower(a) LIKE lower(other)
518 Or on backends that support the ILIKE operator::
520 a ILIKE other
522 E.g.::
524 stmt = select(sometable).\
525 where(sometable.c.column.ilike("%foobar%"))
527 :param other: expression to be compared
528 :param escape: optional escape character, renders the ``ESCAPE``
529 keyword, e.g.::
531 somecolumn.ilike("foo/%bar", escape="/")
533 .. seealso::
535 :meth:`.ColumnOperators.like`
537 """
538 return self.operate(ilike_op, other, escape=escape)
540 def in_(self, other):
541 """Implement the ``in`` operator.
543 In a column context, produces the clause ``column IN <other>``.
545 The given parameter ``other`` may be:
547 * A list of literal values, e.g.::
549 stmt.where(column.in_([1, 2, 3]))
551 In this calling form, the list of items is converted to a set of
552 bound parameters the same length as the list given::
554 WHERE COL IN (?, ?, ?)
556 * A list of tuples may be provided if the comparison is against a
557 :func:`.tuple_` containing multiple expressions::
559 from sqlalchemy import tuple_
560 stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
562 * An empty list, e.g.::
564 stmt.where(column.in_([]))
566 In this calling form, the expression renders an "empty set"
567 expression. These expressions are tailored to individual backends
568 and are generally trying to get an empty SELECT statement as a
569 subquery. Such as on SQLite, the expression is::
571 WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
573 .. versionchanged:: 1.4 empty IN expressions now use an
574 execution-time generated SELECT subquery in all cases.
576 * A bound parameter, e.g. :func:`.bindparam`, may be used if it
577 includes the :paramref:`.bindparam.expanding` flag::
579 stmt.where(column.in_(bindparam('value', expanding=True)))
581 In this calling form, the expression renders a special non-SQL
582 placeholder expression that looks like::
584 WHERE COL IN ([EXPANDING_value])
586 This placeholder expression is intercepted at statement execution
587 time to be converted into the variable number of bound parameter
588 form illustrated earlier. If the statement were executed as::
590 connection.execute(stmt, {"value": [1, 2, 3]})
592 The database would be passed a bound parameter for each value::
594 WHERE COL IN (?, ?, ?)
596 .. versionadded:: 1.2 added "expanding" bound parameters
598 If an empty list is passed, a special "empty list" expression,
599 which is specific to the database in use, is rendered. On
600 SQLite this would be::
602 WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
604 .. versionadded:: 1.3 "expanding" bound parameters now support
605 empty lists
607 * a :func:`_expression.select` construct, which is usually a
608 correlated scalar select::
610 stmt.where(
611 column.in_(
612 select(othertable.c.y).
613 where(table.c.x == othertable.c.x)
614 )
615 )
617 In this calling form, :meth:`.ColumnOperators.in_` renders as given::
619 WHERE COL IN (SELECT othertable.y
620 FROM othertable WHERE othertable.x = table.x)
622 :param other: a list of literals, a :func:`_expression.select`
623 construct, or a :func:`.bindparam` construct that includes the
624 :paramref:`.bindparam.expanding` flag set to True.
626 """
627 return self.operate(in_op, other)
629 def not_in(self, other):
630 """implement the ``NOT IN`` operator.
632 This is equivalent to using negation with
633 :meth:`.ColumnOperators.in_`, i.e. ``~x.in_(y)``.
635 In the case that ``other`` is an empty sequence, the compiler
636 produces an "empty not in" expression. This defaults to the
637 expression "1 = 1" to produce true in all cases. The
638 :paramref:`_sa.create_engine.empty_in_strategy` may be used to
639 alter this behavior.
641 .. versionchanged:: 1.4 The ``not_in()`` operator is renamed from
642 ``notin_()`` in previous releases. The previous name remains
643 available for backwards compatibility.
645 .. versionchanged:: 1.2 The :meth:`.ColumnOperators.in_` and
646 :meth:`.ColumnOperators.not_in` operators
647 now produce a "static" expression for an empty IN sequence
648 by default.
650 .. seealso::
652 :meth:`.ColumnOperators.in_`
654 """
655 return self.operate(not_in_op, other)
657 # deprecated 1.4; see #5429
658 notin_ = not_in
660 def not_like(self, other, escape=None):
661 """implement the ``NOT LIKE`` operator.
663 This is equivalent to using negation with
664 :meth:`.ColumnOperators.like`, i.e. ``~x.like(y)``.
666 .. versionchanged:: 1.4 The ``not_like()`` operator is renamed from
667 ``notlike()`` in previous releases. The previous name remains
668 available for backwards compatibility.
670 .. seealso::
672 :meth:`.ColumnOperators.like`
674 """
675 return self.operate(notlike_op, other, escape=escape)
677 # deprecated 1.4; see #5435
678 notlike = not_like
680 def not_ilike(self, other, escape=None):
681 """implement the ``NOT ILIKE`` operator.
683 This is equivalent to using negation with
684 :meth:`.ColumnOperators.ilike`, i.e. ``~x.ilike(y)``.
686 .. versionchanged:: 1.4 The ``not_ilike()`` operator is renamed from
687 ``notilike()`` in previous releases. The previous name remains
688 available for backwards compatibility.
690 .. seealso::
692 :meth:`.ColumnOperators.ilike`
694 """
695 return self.operate(notilike_op, other, escape=escape)
697 # deprecated 1.4; see #5435
698 notilike = not_ilike
700 def is_(self, other):
701 """Implement the ``IS`` operator.
703 Normally, ``IS`` is generated automatically when comparing to a
704 value of ``None``, which resolves to ``NULL``. However, explicit
705 usage of ``IS`` may be desirable if comparing to boolean values
706 on certain platforms.
708 .. seealso:: :meth:`.ColumnOperators.is_not`
710 """
711 return self.operate(is_, other)
713 def is_not(self, other):
714 """Implement the ``IS NOT`` operator.
716 Normally, ``IS NOT`` is generated automatically when comparing to a
717 value of ``None``, which resolves to ``NULL``. However, explicit
718 usage of ``IS NOT`` may be desirable if comparing to boolean values
719 on certain platforms.
721 .. versionchanged:: 1.4 The ``is_not()`` operator is renamed from
722 ``isnot()`` in previous releases. The previous name remains
723 available for backwards compatibility.
725 .. seealso:: :meth:`.ColumnOperators.is_`
727 """
728 return self.operate(is_not, other)
730 # deprecated 1.4; see #5429
731 isnot = is_not
733 def startswith(self, other, **kwargs):
734 r"""Implement the ``startswith`` operator.
736 Produces a LIKE expression that tests against a match for the start
737 of a string value::
739 column LIKE <other> || '%'
741 E.g.::
743 stmt = select(sometable).\
744 where(sometable.c.column.startswith("foobar"))
746 Since the operator uses ``LIKE``, wildcard characters
747 ``"%"`` and ``"_"`` that are present inside the <other> expression
748 will behave like wildcards as well. For literal string
749 values, the :paramref:`.ColumnOperators.startswith.autoescape` flag
750 may be set to ``True`` to apply escaping to occurrences of these
751 characters within the string value so that they match as themselves
752 and not as wildcard characters. Alternatively, the
753 :paramref:`.ColumnOperators.startswith.escape` parameter will establish
754 a given character as an escape character which can be of use when
755 the target expression is not a literal string.
757 :param other: expression to be compared. This is usually a plain
758 string value, but can also be an arbitrary SQL expression. LIKE
759 wildcard characters ``%`` and ``_`` are not escaped by default unless
760 the :paramref:`.ColumnOperators.startswith.autoescape` flag is
761 set to True.
763 :param autoescape: boolean; when True, establishes an escape character
764 within the LIKE expression, then applies it to all occurrences of
765 ``"%"``, ``"_"`` and the escape character itself within the
766 comparison value, which is assumed to be a literal string and not a
767 SQL expression.
769 An expression such as::
771 somecolumn.startswith("foo%bar", autoescape=True)
773 Will render as::
775 somecolumn LIKE :param || '%' ESCAPE '/'
777 With the value of ``:param`` as ``"foo/%bar"``.
779 :param escape: a character which when given will render with the
780 ``ESCAPE`` keyword to establish that character as the escape
781 character. This character can then be placed preceding occurrences
782 of ``%`` and ``_`` to allow them to act as themselves and not
783 wildcard characters.
785 An expression such as::
787 somecolumn.startswith("foo/%bar", escape="^")
789 Will render as::
791 somecolumn LIKE :param || '%' ESCAPE '^'
793 The parameter may also be combined with
794 :paramref:`.ColumnOperators.startswith.autoescape`::
796 somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
798 Where above, the given literal parameter will be converted to
799 ``"foo^%bar^^bat"`` before being passed to the database.
801 .. seealso::
803 :meth:`.ColumnOperators.endswith`
805 :meth:`.ColumnOperators.contains`
807 :meth:`.ColumnOperators.like`
809 """
810 return self.operate(startswith_op, other, **kwargs)
812 def endswith(self, other, **kwargs):
813 r"""Implement the 'endswith' operator.
815 Produces a LIKE expression that tests against a match for the end
816 of a string value::
818 column LIKE '%' || <other>
820 E.g.::
822 stmt = select(sometable).\
823 where(sometable.c.column.endswith("foobar"))
825 Since the operator uses ``LIKE``, wildcard characters
826 ``"%"`` and ``"_"`` that are present inside the <other> expression
827 will behave like wildcards as well. For literal string
828 values, the :paramref:`.ColumnOperators.endswith.autoescape` flag
829 may be set to ``True`` to apply escaping to occurrences of these
830 characters within the string value so that they match as themselves
831 and not as wildcard characters. Alternatively, the
832 :paramref:`.ColumnOperators.endswith.escape` parameter will establish
833 a given character as an escape character which can be of use when
834 the target expression is not a literal string.
836 :param other: expression to be compared. This is usually a plain
837 string value, but can also be an arbitrary SQL expression. LIKE
838 wildcard characters ``%`` and ``_`` are not escaped by default unless
839 the :paramref:`.ColumnOperators.endswith.autoescape` flag is
840 set to True.
842 :param autoescape: boolean; when True, establishes an escape character
843 within the LIKE expression, then applies it to all occurrences of
844 ``"%"``, ``"_"`` and the escape character itself within the
845 comparison value, which is assumed to be a literal string and not a
846 SQL expression.
848 An expression such as::
850 somecolumn.endswith("foo%bar", autoescape=True)
852 Will render as::
854 somecolumn LIKE '%' || :param ESCAPE '/'
856 With the value of ``:param`` as ``"foo/%bar"``.
858 :param escape: a character which when given will render with the
859 ``ESCAPE`` keyword to establish that character as the escape
860 character. This character can then be placed preceding occurrences
861 of ``%`` and ``_`` to allow them to act as themselves and not
862 wildcard characters.
864 An expression such as::
866 somecolumn.endswith("foo/%bar", escape="^")
868 Will render as::
870 somecolumn LIKE '%' || :param ESCAPE '^'
872 The parameter may also be combined with
873 :paramref:`.ColumnOperators.endswith.autoescape`::
875 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
877 Where above, the given literal parameter will be converted to
878 ``"foo^%bar^^bat"`` before being passed to the database.
880 .. seealso::
882 :meth:`.ColumnOperators.startswith`
884 :meth:`.ColumnOperators.contains`
886 :meth:`.ColumnOperators.like`
888 """
889 return self.operate(endswith_op, other, **kwargs)
891 def contains(self, other, **kwargs):
892 r"""Implement the 'contains' operator.
894 Produces a LIKE expression that tests against a match for the middle
895 of a string value::
897 column LIKE '%' || <other> || '%'
899 E.g.::
901 stmt = select(sometable).\
902 where(sometable.c.column.contains("foobar"))
904 Since the operator uses ``LIKE``, wildcard characters
905 ``"%"`` and ``"_"`` that are present inside the <other> expression
906 will behave like wildcards as well. For literal string
907 values, the :paramref:`.ColumnOperators.contains.autoescape` flag
908 may be set to ``True`` to apply escaping to occurrences of these
909 characters within the string value so that they match as themselves
910 and not as wildcard characters. Alternatively, the
911 :paramref:`.ColumnOperators.contains.escape` parameter will establish
912 a given character as an escape character which can be of use when
913 the target expression is not a literal string.
915 :param other: expression to be compared. This is usually a plain
916 string value, but can also be an arbitrary SQL expression. LIKE
917 wildcard characters ``%`` and ``_`` are not escaped by default unless
918 the :paramref:`.ColumnOperators.contains.autoescape` flag is
919 set to True.
921 :param autoescape: boolean; when True, establishes an escape character
922 within the LIKE expression, then applies it to all occurrences of
923 ``"%"``, ``"_"`` and the escape character itself within the
924 comparison value, which is assumed to be a literal string and not a
925 SQL expression.
927 An expression such as::
929 somecolumn.contains("foo%bar", autoescape=True)
931 Will render as::
933 somecolumn LIKE '%' || :param || '%' ESCAPE '/'
935 With the value of ``:param`` as ``"foo/%bar"``.
937 :param escape: a character which when given will render with the
938 ``ESCAPE`` keyword to establish that character as the escape
939 character. This character can then be placed preceding occurrences
940 of ``%`` and ``_`` to allow them to act as themselves and not
941 wildcard characters.
943 An expression such as::
945 somecolumn.contains("foo/%bar", escape="^")
947 Will render as::
949 somecolumn LIKE '%' || :param || '%' ESCAPE '^'
951 The parameter may also be combined with
952 :paramref:`.ColumnOperators.contains.autoescape`::
954 somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
956 Where above, the given literal parameter will be converted to
957 ``"foo^%bar^^bat"`` before being passed to the database.
959 .. seealso::
961 :meth:`.ColumnOperators.startswith`
963 :meth:`.ColumnOperators.endswith`
965 :meth:`.ColumnOperators.like`
968 """
969 return self.operate(contains_op, other, **kwargs)
971 def match(self, other, **kwargs):
972 """Implements a database-specific 'match' operator.
974 :meth:`_sql.ColumnOperators.match` attempts to resolve to
975 a MATCH-like function or operator provided by the backend.
976 Examples include:
978 * PostgreSQL - renders ``x @@ to_tsquery(y)``
979 * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)``
981 .. seealso::
983 :class:`_mysql.match` - MySQL specific construct with
984 additional features.
986 * Oracle - renders ``CONTAINS(x, y)``
987 * other backends may provide special implementations.
988 * Backends without any special implementation will emit
989 the operator as "MATCH". This is compatible with SQLite, for
990 example.
992 """
993 return self.operate(match_op, other, **kwargs)
995 def regexp_match(self, pattern, flags=None):
996 """Implements a database-specific 'regexp match' operator.
998 E.g.::
1000 stmt = select(table.c.some_column).where(
1001 table.c.some_column.regexp_match('^(b|c)')
1002 )
1004 :meth:`_sql.ColumnOperators.regexp_match` attempts to resolve to
1005 a REGEXP-like function or operator provided by the backend, however
1006 the specific regular expression syntax and flags available are
1007 **not backend agnostic**.
1009 Examples include:
1011 * PostgreSQL - renders ``x ~ y`` or ``x !~ y`` when negated.
1012 * Oracle - renders ``REGEXP_LIKE(x, y)``
1013 * SQLite - uses SQLite's ``REGEXP`` placeholder operator and calls into
1014 the Python ``re.match()`` builtin.
1015 * other backends may provide special implementations.
1016 * Backends without any special implementation will emit
1017 the operator as "REGEXP" or "NOT REGEXP". This is compatible with
1018 SQLite and MySQL, for example.
1020 Regular expression support is currently implemented for Oracle,
1021 PostgreSQL, MySQL and MariaDB. Partial support is available for
1022 SQLite. Support among third-party dialects may vary.
1024 :param pattern: The regular expression pattern string or column
1025 clause.
1026 :param flags: Any regular expression string flags to apply. Flags
1027 tend to be backend specific. It can be a string or a column clause.
1028 Some backends, like PostgreSQL and MariaDB, may alternatively
1029 specify the flags as part of the pattern.
1030 When using the ignore case flag 'i' in PostgreSQL, the ignore case
1031 regexp match operator ``~*`` or ``!~*`` will be used.
1033 .. versionadded:: 1.4
1035 .. seealso::
1037 :meth:`_sql.ColumnOperators.regexp_replace`
1040 """
1041 return self.operate(regexp_match_op, pattern, flags=flags)
1043 def regexp_replace(self, pattern, replacement, flags=None):
1044 """Implements a database-specific 'regexp replace' operator.
1046 E.g.::
1048 stmt = select(
1049 table.c.some_column.regexp_replace(
1050 'b(..)',
1051 'X\1Y',
1052 flags='g'
1053 )
1054 )
1056 :meth:`_sql.ColumnOperators.regexp_replace` attempts to resolve to
1057 a REGEXP_REPLACE-like function provided by the backend, that
1058 usually emit the function ``REGEXP_REPLACE()``. However,
1059 the specific regular expression syntax and flags available are
1060 **not backend agnostic**.
1062 Regular expression replacement support is currently implemented for
1063 Oracle, PostgreSQL, MySQL 8 or greater and MariaDB. Support among
1064 third-party dialects may vary.
1066 :param pattern: The regular expression pattern string or column
1067 clause.
1068 :param pattern: The replacement string or column clause.
1069 :param flags: Any regular expression string flags to apply. Flags
1070 tend to be backend specific. It can be a string or a column clause.
1071 Some backends, like PostgreSQL and MariaDB, may alternatively
1072 specify the flags as part of the pattern.
1074 .. versionadded:: 1.4
1076 .. seealso::
1078 :meth:`_sql.ColumnOperators.regexp_match`
1080 """
1081 return self.operate(
1082 regexp_replace_op, pattern, replacement=replacement, flags=flags
1083 )
1085 def desc(self):
1086 """Produce a :func:`_expression.desc` clause against the
1087 parent object."""
1088 return self.operate(desc_op)
1090 def asc(self):
1091 """Produce a :func:`_expression.asc` clause against the
1092 parent object."""
1093 return self.operate(asc_op)
1095 def nulls_first(self):
1096 """Produce a :func:`_expression.nulls_first` clause against the
1097 parent object.
1099 .. versionchanged:: 1.4 The ``nulls_first()`` operator is
1100 renamed from ``nullsfirst()`` in previous releases.
1101 The previous name remains available for backwards compatibility.
1102 """
1103 return self.operate(nulls_first_op)
1105 # deprecated 1.4; see #5435
1106 nullsfirst = nulls_first
1108 def nulls_last(self):
1109 """Produce a :func:`_expression.nulls_last` clause against the
1110 parent object.
1112 .. versionchanged:: 1.4 The ``nulls_last()`` operator is
1113 renamed from ``nullslast()`` in previous releases.
1114 The previous name remains available for backwards compatibility.
1115 """
1116 return self.operate(nulls_last_op)
1118 # deprecated 1.4; see #5429
1119 nullslast = nulls_last
1121 def collate(self, collation):
1122 """Produce a :func:`_expression.collate` clause against
1123 the parent object, given the collation string.
1125 .. seealso::
1127 :func:`_expression.collate`
1129 """
1130 return self.operate(collate, collation)
1132 def __radd__(self, other):
1133 """Implement the ``+`` operator in reverse.
1135 See :meth:`.ColumnOperators.__add__`.
1137 """
1138 return self.reverse_operate(add, other)
1140 def __rsub__(self, other):
1141 """Implement the ``-`` operator in reverse.
1143 See :meth:`.ColumnOperators.__sub__`.
1145 """
1146 return self.reverse_operate(sub, other)
1148 def __rmul__(self, other):
1149 """Implement the ``*`` operator in reverse.
1151 See :meth:`.ColumnOperators.__mul__`.
1153 """
1154 return self.reverse_operate(mul, other)
1156 def __rdiv__(self, other):
1157 """Implement the ``/`` operator in reverse.
1159 See :meth:`.ColumnOperators.__div__`.
1161 """
1162 return self.reverse_operate(div, other)
1164 def __rmod__(self, other):
1165 """Implement the ``%`` operator in reverse.
1167 See :meth:`.ColumnOperators.__mod__`.
1169 """
1170 return self.reverse_operate(mod, other)
1172 def between(self, cleft, cright, symmetric=False):
1173 """Produce a :func:`_expression.between` clause against
1174 the parent object, given the lower and upper range.
1176 """
1177 return self.operate(between_op, cleft, cright, symmetric=symmetric)
1179 def distinct(self):
1180 """Produce a :func:`_expression.distinct` clause against the
1181 parent object.
1183 """
1184 return self.operate(distinct_op)
1186 def any_(self):
1187 """Produce an :func:`_expression.any_` clause against the
1188 parent object.
1190 See the documentation for :func:`_sql.any_` for examples.
1192 .. note:: be sure to not confuse the newer
1193 :meth:`_sql.ColumnOperators.any_` method with its older
1194 :class:`_types.ARRAY`-specific counterpart, the
1195 :meth:`_types.ARRAY.Comparator.any` method, which a different
1196 calling syntax and usage pattern.
1198 .. versionadded:: 1.1
1200 """
1201 return self.operate(any_op)
1203 def all_(self):
1204 """Produce an :func:`_expression.all_` clause against the
1205 parent object.
1207 See the documentation for :func:`_sql.all_` for examples.
1209 .. note:: be sure to not confuse the newer
1210 :meth:`_sql.ColumnOperators.all_` method with its older
1211 :class:`_types.ARRAY`-specific counterpart, the
1212 :meth:`_types.ARRAY.Comparator.all` method, which a different
1213 calling syntax and usage pattern.
1216 .. versionadded:: 1.1
1218 """
1219 return self.operate(all_op)
1221 def __add__(self, other):
1222 """Implement the ``+`` operator.
1224 In a column context, produces the clause ``a + b``
1225 if the parent object has non-string affinity.
1226 If the parent object has a string affinity,
1227 produces the concatenation operator, ``a || b`` -
1228 see :meth:`.ColumnOperators.concat`.
1230 """
1231 return self.operate(add, other)
1233 def __sub__(self, other):
1234 """Implement the ``-`` operator.
1236 In a column context, produces the clause ``a - b``.
1238 """
1239 return self.operate(sub, other)
1241 def __mul__(self, other):
1242 """Implement the ``*`` operator.
1244 In a column context, produces the clause ``a * b``.
1246 """
1247 return self.operate(mul, other)
1249 def __div__(self, other):
1250 """Implement the ``/`` operator.
1252 In a column context, produces the clause ``a / b``.
1254 """
1255 return self.operate(div, other)
1257 def __mod__(self, other):
1258 """Implement the ``%`` operator.
1260 In a column context, produces the clause ``a % b``.
1262 """
1263 return self.operate(mod, other)
1265 def __truediv__(self, other):
1266 """Implement the ``//`` operator.
1268 In a column context, produces the clause ``a / b``.
1270 """
1271 return self.operate(truediv, other)
1273 def __rtruediv__(self, other):
1274 """Implement the ``//`` operator in reverse.
1276 See :meth:`.ColumnOperators.__truediv__`.
1278 """
1279 return self.reverse_operate(truediv, other)
1282_commutative = {eq, ne, add, mul}
1283_comparison = {eq, ne, lt, gt, ge, le}
1286def commutative_op(fn):
1287 _commutative.add(fn)
1288 return fn
1291def comparison_op(fn):
1292 _comparison.add(fn)
1293 return fn
1296def from_():
1297 raise NotImplementedError()
1300@comparison_op
1301def function_as_comparison_op():
1302 raise NotImplementedError()
1305def as_():
1306 raise NotImplementedError()
1309def exists():
1310 raise NotImplementedError()
1313def is_true(a):
1314 raise NotImplementedError()
1317# 1.4 deprecated; see #5435
1318istrue = is_true
1321def is_false(a):
1322 raise NotImplementedError()
1325# 1.4 deprecated; see #5435
1326isfalse = is_false
1329@comparison_op
1330def is_distinct_from(a, b):
1331 return a.is_distinct_from(b)
1334@comparison_op
1335def is_not_distinct_from(a, b):
1336 return a.is_not_distinct_from(b)
1339# deprecated 1.4; see #5435
1340isnot_distinct_from = is_not_distinct_from
1343@comparison_op
1344def is_(a, b):
1345 return a.is_(b)
1348@comparison_op
1349def is_not(a, b):
1350 return a.is_not(b)
1353# 1.4 deprecated; see #5429
1354isnot = is_not
1357def collate(a, b):
1358 return a.collate(b)
1361def op(a, opstring, b):
1362 return a.op(opstring)(b)
1365@comparison_op
1366def like_op(a, b, escape=None):
1367 return a.like(b, escape=escape)
1370@comparison_op
1371def not_like_op(a, b, escape=None):
1372 return a.notlike(b, escape=escape)
1375# 1.4 deprecated; see #5435
1376notlike_op = not_like_op
1379@comparison_op
1380def ilike_op(a, b, escape=None):
1381 return a.ilike(b, escape=escape)
1384@comparison_op
1385def not_ilike_op(a, b, escape=None):
1386 return a.not_ilike(b, escape=escape)
1389# 1.4 deprecated; see #5435
1390notilike_op = not_ilike_op
1393@comparison_op
1394def between_op(a, b, c, symmetric=False):
1395 return a.between(b, c, symmetric=symmetric)
1398@comparison_op
1399def not_between_op(a, b, c, symmetric=False):
1400 return ~a.between(b, c, symmetric=symmetric)
1403# 1.4 deprecated; see #5435
1404notbetween_op = not_between_op
1407@comparison_op
1408def in_op(a, b):
1409 return a.in_(b)
1412@comparison_op
1413def not_in_op(a, b):
1414 return a.not_in(b)
1417# 1.4 deprecated; see #5429
1418notin_op = not_in_op
1421def distinct_op(a):
1422 return a.distinct()
1425def any_op(a):
1426 return a.any_()
1429def all_op(a):
1430 return a.all_()
1433def _escaped_like_impl(fn, other, escape, autoescape):
1434 if autoescape:
1435 if autoescape is not True:
1436 util.warn(
1437 "The autoescape parameter is now a simple boolean True/False"
1438 )
1439 if escape is None:
1440 escape = "/"
1442 if not isinstance(other, util.compat.string_types):
1443 raise TypeError("String value expected when autoescape=True")
1445 if escape not in ("%", "_"):
1446 other = other.replace(escape, escape + escape)
1448 other = other.replace("%", escape + "%").replace("_", escape + "_")
1450 return fn(other, escape=escape)
1453@comparison_op
1454def startswith_op(a, b, escape=None, autoescape=False):
1455 return _escaped_like_impl(a.startswith, b, escape, autoescape)
1458@comparison_op
1459def not_startswith_op(a, b, escape=None, autoescape=False):
1460 return ~_escaped_like_impl(a.startswith, b, escape, autoescape)
1463# 1.4 deprecated; see #5435
1464notstartswith_op = not_startswith_op
1467@comparison_op
1468def endswith_op(a, b, escape=None, autoescape=False):
1469 return _escaped_like_impl(a.endswith, b, escape, autoescape)
1472@comparison_op
1473def not_endswith_op(a, b, escape=None, autoescape=False):
1474 return ~_escaped_like_impl(a.endswith, b, escape, autoescape)
1477# 1.4 deprecated; see #5435
1478notendswith_op = not_endswith_op
1481@comparison_op
1482def contains_op(a, b, escape=None, autoescape=False):
1483 return _escaped_like_impl(a.contains, b, escape, autoescape)
1486@comparison_op
1487def not_contains_op(a, b, escape=None, autoescape=False):
1488 return ~_escaped_like_impl(a.contains, b, escape, autoescape)
1491# 1.4 deprecated; see #5435
1492notcontains_op = not_contains_op
1495@comparison_op
1496def match_op(a, b, **kw):
1497 return a.match(b, **kw)
1500@comparison_op
1501def regexp_match_op(a, b, flags=None):
1502 return a.regexp_match(b, flags=flags)
1505@comparison_op
1506def not_regexp_match_op(a, b, flags=None):
1507 return ~a.regexp_match(b, flags=flags)
1510def regexp_replace_op(a, b, replacement, flags=None):
1511 return a.regexp_replace(b, replacement=replacement, flags=flags)
1514@comparison_op
1515def not_match_op(a, b, **kw):
1516 return ~a.match(b, **kw)
1519# 1.4 deprecated; see #5429
1520notmatch_op = not_match_op
1523def comma_op(a, b):
1524 raise NotImplementedError()
1527def filter_op(a, b):
1528 raise NotImplementedError()
1531def concat_op(a, b):
1532 try:
1533 concat = a.concat
1534 except AttributeError:
1535 return b._rconcat(a)
1536 else:
1537 return concat(b)
1540def desc_op(a):
1541 return a.desc()
1544def asc_op(a):
1545 return a.asc()
1548def nulls_first_op(a):
1549 return a.nulls_first()
1552# 1.4 deprecated; see #5435
1553nullsfirst_op = nulls_first_op
1556def nulls_last_op(a):
1557 return a.nulls_last()
1560# 1.4 deprecated; see #5435
1561nullslast_op = nulls_last_op
1564def json_getitem_op(a, b):
1565 raise NotImplementedError()
1568def json_path_getitem_op(a, b):
1569 raise NotImplementedError()
1572def is_comparison(op):
1573 return op in _comparison or isinstance(op, custom_op) and op.is_comparison
1576def is_commutative(op):
1577 return op in _commutative
1580def is_ordering_modifier(op):
1581 return op in (asc_op, desc_op, nulls_first_op, nulls_last_op)
1584def is_natural_self_precedent(op):
1585 return (
1586 op in _natural_self_precedent
1587 or isinstance(op, custom_op)
1588 and op.natural_self_precedent
1589 )
1592_booleans = (inv, is_true, is_false, and_, or_)
1595def is_boolean(op):
1596 return is_comparison(op) or op in _booleans
1599_mirror = {gt: lt, ge: le, lt: gt, le: ge}
1602def mirror(op):
1603 """rotate a comparison operator 180 degrees.
1605 Note this is not the same as negation.
1607 """
1608 return _mirror.get(op, op)
1611_associative = _commutative.union([concat_op, and_, or_]).difference([eq, ne])
1614def is_associative(op):
1615 return op in _associative
1618_natural_self_precedent = _associative.union(
1619 [getitem, json_getitem_op, json_path_getitem_op]
1620)
1621"""Operators where if we have (a op b) op c, we don't want to
1622parenthesize (a op b).
1624"""
1627_asbool = util.symbol("_asbool", canonical=-10)
1628_smallest = util.symbol("_smallest", canonical=-100)
1629_largest = util.symbol("_largest", canonical=100)
1631_PRECEDENCE = {
1632 from_: 15,
1633 function_as_comparison_op: 15,
1634 any_op: 15,
1635 all_op: 15,
1636 getitem: 15,
1637 json_getitem_op: 15,
1638 json_path_getitem_op: 15,
1639 mul: 8,
1640 truediv: 8,
1641 div: 8,
1642 mod: 8,
1643 neg: 8,
1644 add: 7,
1645 sub: 7,
1646 concat_op: 6,
1647 filter_op: 6,
1648 match_op: 5,
1649 not_match_op: 5,
1650 regexp_match_op: 5,
1651 not_regexp_match_op: 5,
1652 regexp_replace_op: 5,
1653 ilike_op: 5,
1654 not_ilike_op: 5,
1655 like_op: 5,
1656 not_like_op: 5,
1657 in_op: 5,
1658 not_in_op: 5,
1659 is_: 5,
1660 is_not: 5,
1661 eq: 5,
1662 ne: 5,
1663 is_distinct_from: 5,
1664 is_not_distinct_from: 5,
1665 gt: 5,
1666 lt: 5,
1667 ge: 5,
1668 le: 5,
1669 between_op: 5,
1670 not_between_op: 5,
1671 distinct_op: 5,
1672 inv: 5,
1673 is_true: 5,
1674 is_false: 5,
1675 and_: 3,
1676 or_: 2,
1677 comma_op: -1,
1678 desc_op: 3,
1679 asc_op: 3,
1680 collate: 4,
1681 as_: -1,
1682 exists: 0,
1683 _asbool: -10,
1684 _smallest: _smallest,
1685 _largest: _largest,
1686}
1689def is_precedent(operator, against):
1690 if operator is against and is_natural_self_precedent(operator):
1691 return False
1692 else:
1693 return _PRECEDENCE.get(
1694 operator, getattr(operator, "precedence", _smallest)
1695 ) <= _PRECEDENCE.get(against, getattr(against, "precedence", _largest))