codekingpro/portable-devtools
115k
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
7
8# This module is part of SQLAlchemy and is released under
9# the MIT License: https://www.opensource.org/licenses/mit-license.php
10
11"""Defines operators used in SQL expressions."""
12
13from __future__ import annotations
14
15from enum import IntEnum
16from operator import add as _uncast_add
17from operator import and_ as _uncast_and_
18from operator import contains as _uncast_contains
19from operator import eq as _uncast_eq
20from operator import floordiv as _uncast_floordiv
21from operator import ge as _uncast_ge
22from operator import getitem as _uncast_getitem
23from operator import gt as _uncast_gt
24from operator import inv as _uncast_inv
25from operator import le as _uncast_le
26from operator import lshift as _uncast_lshift
27from operator import lt as _uncast_lt
28from operator import mod as _uncast_mod
29from operator import mul as _uncast_mul
30from operator import ne as _uncast_ne
31from operator import neg as _uncast_neg
32from operator import or_ as _uncast_or_
33from operator import rshift as _uncast_rshift
34from operator import sub as _uncast_sub
35from operator import truediv as _uncast_truediv
36import typing
37from typing import Any
38from typing import Callable
39from typing import cast
40from typing import Dict
41from typing import Generic
42from typing import Optional
43from typing import overload
44from typing import Set
45from typing import Tuple
46from typing import Type
47from typing import TYPE_CHECKING
48from typing import TypeVar
49from typing import Union
50
51from .. import exc
52from .. import util
53from ..util.typing import Literal
54from ..util.typing import Protocol
55
56if typing.TYPE_CHECKING:
57 from ._typing import ColumnExpressionArgument
58 from .cache_key import CacheConst
59 from .elements import ColumnElement
60 from .type_api import TypeEngine
61
62_T = TypeVar("_T", bound=Any)
63_FN = TypeVar("_FN", bound=Callable[..., Any])
64
65
66class OperatorType(Protocol):
67 """describe an op() function."""
68
69 __slots__ = ()
70
71 __name__: str
72
73 @overload
74 def __call__(
75 self,
76 left: ColumnExpressionArgument[Any],
77 right: Optional[Any] = None,
78 *other: Any,
79 **kwargs: Any,
80 ) -> ColumnElement[Any]: ...
81
82 @overload
83 def __call__(
84 self,
85 left: Operators,
86 right: Optional[Any] = None,
87 *other: Any,
88 **kwargs: Any,
89 ) -> Operators: ...
90
91 def __call__(
92 self,
93 left: Any,
94 right: Optional[Any] = None,
95 *other: Any,
96 **kwargs: Any,
97 ) -> Operators: ...
98
99
100add = cast(OperatorType, _uncast_add)
101and_ = cast(OperatorType, _uncast_and_)
102contains = cast(OperatorType, _uncast_contains)
103eq = cast(OperatorType, _uncast_eq)
104floordiv = cast(OperatorType, _uncast_floordiv)
105ge = cast(OperatorType, _uncast_ge)
106getitem = cast(OperatorType, _uncast_getitem)
107gt = cast(OperatorType, _uncast_gt)
108inv = cast(OperatorType, _uncast_inv)
109le = cast(OperatorType, _uncast_le)
110lshift = cast(OperatorType, _uncast_lshift)
111lt = cast(OperatorType, _uncast_lt)
112mod = cast(OperatorType, _uncast_mod)
113mul = cast(OperatorType, _uncast_mul)
114ne = cast(OperatorType, _uncast_ne)
115neg = cast(OperatorType, _uncast_neg)
116or_ = cast(OperatorType, _uncast_or_)
117rshift = cast(OperatorType, _uncast_rshift)
118sub = cast(OperatorType, _uncast_sub)
119truediv = cast(OperatorType, _uncast_truediv)
120
121
122class Operators:
123 """Base of comparison and logical operators.
124
125 Implements base methods
126 :meth:`~sqlalchemy.sql.operators.Operators.operate` and
127 :meth:`~sqlalchemy.sql.operators.Operators.reverse_operate`, as well as
128 :meth:`~sqlalchemy.sql.operators.Operators.__and__`,
129 :meth:`~sqlalchemy.sql.operators.Operators.__or__`,
130 :meth:`~sqlalchemy.sql.operators.Operators.__invert__`.
131
132 Usually is used via its most common subclass
133 :class:`.ColumnOperators`.
134
135 """
136
137 __slots__ = ()
138
139 def __and__(self, other: Any) -> Operators:
140 """Implement the ``&`` operator.
141
142 When used with SQL expressions, results in an
143 AND operation, equivalent to
144 :func:`_expression.and_`, that is::
145
146 a & b
147
148 is equivalent to::
149
150 from sqlalchemy import and_
151 and_(a, b)
152
153 Care should be taken when using ``&`` regarding
154 operator precedence; the ``&`` operator has the highest precedence.
155 The operands should be enclosed in parenthesis if they contain
156 further sub expressions::
157
158 (a == 2) & (b == 4)
159
160 """
161 return self.operate(and_, other)
162
163 def __or__(self, other: Any) -> Operators:
164 """Implement the ``|`` operator.
165
166 When used with SQL expressions, results in an
167 OR operation, equivalent to
168 :func:`_expression.or_`, that is::
169
170 a | b
171
172 is equivalent to::
173
174 from sqlalchemy import or_
175 or_(a, b)
176
177 Care should be taken when using ``|`` regarding
178 operator precedence; the ``|`` operator has the highest precedence.
179 The operands should be enclosed in parenthesis if they contain
180 further sub expressions::
181
182 (a == 2) | (b == 4)
183
184 """
185 return self.operate(or_, other)
186
187 def __invert__(self) -> Operators:
188 """Implement the ``~`` operator.
189
190 When used with SQL expressions, results in a
191 NOT operation, equivalent to
192 :func:`_expression.not_`, that is::
193
194 ~a
195
196 is equivalent to::
197
198 from sqlalchemy import not_
199 not_(a)
200
201 """
202 return self.operate(inv)
203
204 def op(
205 self,
206 opstring: str,
207 precedence: int = 0,
208 is_comparison: bool = False,
209 return_type: Optional[
210 Union[Type[TypeEngine[Any]], TypeEngine[Any]]
211 ] = None,
212 python_impl: Optional[Callable[..., Any]] = None,
213 ) -> Callable[[Any], Operators]:
214 """Produce a generic operator function.
215
216 e.g.::
217
218 somecolumn.op("*")(5)
219
220 produces::
221
222 somecolumn * 5
223
224 This function can also be used to make bitwise operators explicit. For
225 example::
226
227 somecolumn.op('&')(0xff)
228
229 is a bitwise AND of the value in ``somecolumn``.
230
231 :param opstring: a string which will be output as the infix operator
232 between this element and the expression passed to the
233 generated function.
234
235 :param precedence: precedence which the database is expected to apply
236 to the operator in SQL expressions. This integer value acts as a hint
237 for the SQL compiler to know when explicit parenthesis should be
238 rendered around a particular operation. A lower number will cause the
239 expression to be parenthesized when applied against another operator
240 with higher precedence. The default value of ``0`` is lower than all
241 operators except for the comma (``,``) and ``AS`` operators. A value
242 of 100 will be higher or equal to all operators, and -100 will be
243 lower than or equal to all operators.
244
245 .. seealso::
246
247 :ref:`faq_sql_expression_op_parenthesis` - detailed description
248 of how the SQLAlchemy SQL compiler renders parenthesis
249
250 :param is_comparison: legacy; if True, the operator will be considered
251 as a "comparison" operator, that is which evaluates to a boolean
252 true/false value, like ``==``, ``>``, etc. This flag is provided
253 so that ORM relationships can establish that the operator is a
254 comparison operator when used in a custom join condition.
255
256 Using the ``is_comparison`` parameter is superseded by using the
257 :meth:`.Operators.bool_op` method instead; this more succinct
258 operator sets this parameter automatically, but also provides
259 correct :pep:`484` typing support as the returned object will
260 express a "boolean" datatype, i.e. ``BinaryExpression[bool]``.
261
262 :param return_type: a :class:`.TypeEngine` class or object that will
263 force the return type of an expression produced by this operator
264 to be of that type. By default, operators that specify
265 :paramref:`.Operators.op.is_comparison` will resolve to
266 :class:`.Boolean`, and those that do not will be of the same
267 type as the left-hand operand.
268
269 :param python_impl: an optional Python function that can evaluate
270 two Python values in the same way as this operator works when
271 run on the database server. Useful for in-Python SQL expression
272 evaluation functions, such as for ORM hybrid attributes, and the
273 ORM "evaluator" used to match objects in a session after a multi-row
274 update or delete.
275
276 e.g.::
277
278 >>> expr = column('x').op('+', python_impl=lambda a, b: a + b)('y')
279
280 The operator for the above expression will also work for non-SQL
281 left and right objects::
282
283 >>> expr.operator(5, 10)
284 15
285
286 .. versionadded:: 2.0
287
288
289 .. seealso::
290
291 :meth:`.Operators.bool_op`
292
293 :ref:`types_operators`
294
295 :ref:`relationship_custom_operator`
296
297 """
298 operator = custom_op(
299 opstring,
300 precedence,
301 is_comparison,
302 return_type,
303 python_impl=python_impl,
304 )
305
306 def against(other: Any) -> Operators:
307 return operator(self, other)
308
309 return against
310
311 def bool_op(
312 self,
313 opstring: str,
314 precedence: int = 0,
315 python_impl: Optional[Callable[..., Any]] = None,
316 ) -> Callable[[Any], Operators]:
317 """Return a custom boolean operator.
318
319 This method is shorthand for calling
320 :meth:`.Operators.op` and passing the
321 :paramref:`.Operators.op.is_comparison`
322 flag with True. A key advantage to using :meth:`.Operators.bool_op`
323 is that when using column constructs, the "boolean" nature of the
324 returned expression will be present for :pep:`484` purposes.
325
326 .. seealso::
327
328 :meth:`.Operators.op`
329
330 """
331 return self.op(
332 opstring,
333 precedence=precedence,
334 is_comparison=True,
335 python_impl=python_impl,
336 )
337
338 def operate(
339 self, op: OperatorType, *other: Any, **kwargs: Any
340 ) -> Operators:
341 r"""Operate on an argument.
342
343 This is the lowest level of operation, raises
344 :class:`NotImplementedError` by default.
345
346 Overriding this on a subclass can allow common
347 behavior to be applied to all operations.
348 For example, overriding :class:`.ColumnOperators`
349 to apply ``func.lower()`` to the left and right
350 side::
351
352 class MyComparator(ColumnOperators):
353 def operate(self, op, other, **kwargs):
354 return op(func.lower(self), func.lower(other), **kwargs)
355
356 :param op: Operator callable.
357 :param \*other: the 'other' side of the operation. Will
358 be a single scalar for most operations.
359 :param \**kwargs: modifiers. These may be passed by special
360 operators such as :meth:`ColumnOperators.contains`.
361
362
363 """
364 raise NotImplementedError(str(op))
365
366 __sa_operate__ = operate
367
368 def reverse_operate(
369 self, op: OperatorType, other: Any, **kwargs: Any
370 ) -> Operators:
371 """Reverse operate on an argument.
372
373 Usage is the same as :meth:`operate`.
374
375 """
376 raise NotImplementedError(str(op))
377
378
379class custom_op(OperatorType, Generic[_T]):
380 """Represent a 'custom' operator.
381
382 :class:`.custom_op` is normally instantiated when the
383 :meth:`.Operators.op` or :meth:`.Operators.bool_op` methods
384 are used to create a custom operator callable. The class can also be
385 used directly when programmatically constructing expressions. E.g.
386 to represent the "factorial" operation::
387
388 from sqlalchemy.sql import UnaryExpression
389 from sqlalchemy.sql import operators
390 from sqlalchemy import Numeric
391
392 unary = UnaryExpression(table.c.somecolumn,
393 modifier=operators.custom_op("!"),
394 type_=Numeric)
395
396
397 .. seealso::
398
399 :meth:`.Operators.op`
400
401 :meth:`.Operators.bool_op`
402
403 """
404
405 __name__ = "custom_op"
406
407 __slots__ = (
408 "opstring",
409 "precedence",
410 "is_comparison",
411 "natural_self_precedent",
412 "eager_grouping",
413 "return_type",
414 "python_impl",
415 )
416
417 def __init__(
418 self,
419 opstring: str,
420 precedence: int = 0,
421 is_comparison: bool = False,
422 return_type: Optional[
423 Union[Type[TypeEngine[_T]], TypeEngine[_T]]
424 ] = None,
425 natural_self_precedent: bool = False,
426 eager_grouping: bool = False,
427 python_impl: Optional[Callable[..., Any]] = None,
428 ):
429 self.opstring = opstring
430 self.precedence = precedence
431 self.is_comparison = is_comparison
432 self.natural_self_precedent = natural_self_precedent
433 self.eager_grouping = eager_grouping
434 self.return_type = (
435 return_type._to_instance(return_type) if return_type else None
436 )
437 self.python_impl = python_impl
438
439 def __eq__(self, other: Any) -> bool:
440 return (
441 isinstance(other, custom_op)
442 and other._hash_key() == self._hash_key()
443 )
444
445 def __hash__(self) -> int:
446 return hash(self._hash_key())
447
448 def _hash_key(self) -> Union[CacheConst, Tuple[Any, ...]]:
449 return (
450 self.__class__,
451 self.opstring,
452 self.precedence,
453 self.is_comparison,
454 self.natural_self_precedent,
455 self.eager_grouping,
456 self.return_type._static_cache_key if self.return_type else None,
457 )
458
459 @overload
460 def __call__(
461 self,
462 left: ColumnExpressionArgument[Any],
463 right: Optional[Any] = None,
464 *other: Any,
465 **kwargs: Any,
466 ) -> ColumnElement[Any]: ...
467
468 @overload
469 def __call__(
470 self,
471 left: Operators,
472 right: Optional[Any] = None,
473 *other: Any,
474 **kwargs: Any,
475 ) -> Operators: ...
476
477 def __call__(
478 self,
479 left: Any,
480 right: Optional[Any] = None,
481 *other: Any,
482 **kwargs: Any,
483 ) -> Operators:
484 if hasattr(left, "__sa_operate__"):
485 return left.operate(self, right, *other, **kwargs) # type: ignore
486 elif self.python_impl:
487 return self.python_impl(left, right, *other, **kwargs) # type: ignore # noqa: E501
488 else:
489 raise exc.InvalidRequestError(
490 f"Custom operator {self.opstring!r} can't be used with "
491 "plain Python objects unless it includes the "
492 "'python_impl' parameter."
493 )
494
495
496class ColumnOperators(Operators):
497 """Defines boolean, comparison, and other operators for
498 :class:`_expression.ColumnElement` expressions.
499
500 By default, all methods call down to
501 :meth:`.operate` or :meth:`.reverse_operate`,
502 passing in the appropriate operator function from the
503 Python builtin ``operator`` module or
504 a SQLAlchemy-specific operator function from
505 :mod:`sqlalchemy.expression.operators`. For example
506 the ``__eq__`` function::
507
508 def __eq__(self, other):
509 return self.operate(operators.eq, other)
510
511 Where ``operators.eq`` is essentially::
512
513 def eq(a, b):
514 return a == b
515
516 The core column expression unit :class:`_expression.ColumnElement`
517 overrides :meth:`.Operators.operate` and others
518 to return further :class:`_expression.ColumnElement` constructs,
519 so that the ``==`` operation above is replaced by a clause
520 construct.
521
522 .. seealso::
523
524 :ref:`types_operators`
525
526 :attr:`.TypeEngine.comparator_factory`
527
528 :class:`.ColumnOperators`
529
530 :class:`.PropComparator`
531
532 """
533
534 __slots__ = ()
535
536 timetuple: Literal[None] = None
537 """Hack, allows datetime objects to be compared on the LHS."""
538
539 if typing.TYPE_CHECKING:
540
541 def operate(
542 self, op: OperatorType, *other: Any, **kwargs: Any
543 ) -> ColumnOperators: ...
544
545 def reverse_operate(
546 self, op: OperatorType, other: Any, **kwargs: Any
547 ) -> ColumnOperators: ...
548
549 def __lt__(self, other: Any) -> ColumnOperators:
550 """Implement the ``<`` operator.
551
552 In a column context, produces the clause ``a < b``.
553
554 """
555 return self.operate(lt, other)
556
557 def __le__(self, other: Any) -> ColumnOperators:
558 """Implement the ``<=`` operator.
559
560 In a column context, produces the clause ``a <= b``.
561
562 """
563 return self.operate(le, other)
564
565 # ColumnOperators defines an __eq__ so it must explicitly declare also
566 # an hash or it's set to None by python:
567 # https://docs.python.org/3/reference/datamodel.html#object.__hash__
568 if TYPE_CHECKING:
569
570 def __hash__(self) -> int: ...
571
572 else:
573 __hash__ = Operators.__hash__
574
575 def __eq__(self, other: Any) -> ColumnOperators: # type: ignore[override]
576 """Implement the ``==`` operator.
577
578 In a column context, produces the clause ``a = b``.
579 If the target is ``None``, produces ``a IS NULL``.
580
581 """
582 return self.operate(eq, other)
583
584 def __ne__(self, other: Any) -> ColumnOperators: # type: ignore[override]
585 """Implement the ``!=`` operator.
586
587 In a column context, produces the clause ``a != b``.
588 If the target is ``None``, produces ``a IS NOT NULL``.
589
590 """
591 return self.operate(ne, other)
592
593 def is_distinct_from(self, other: Any) -> ColumnOperators:
594 """Implement the ``IS DISTINCT FROM`` operator.
595
596 Renders "a IS DISTINCT FROM b" on most platforms;
597 on some such as SQLite may render "a IS NOT b".
598
599 """
600 return self.operate(is_distinct_from, other)
601
602 def is_not_distinct_from(self, other: Any) -> ColumnOperators:
603 """Implement the ``IS NOT DISTINCT FROM`` operator.
604
605 Renders "a IS NOT DISTINCT FROM b" on most platforms;
606 on some such as SQLite may render "a IS b".
607
608 .. versionchanged:: 1.4 The ``is_not_distinct_from()`` operator is
609 renamed from ``isnot_distinct_from()`` in previous releases.
610 The previous name remains available for backwards compatibility.
611
612 """
613 return self.operate(is_not_distinct_from, other)
614
615 # deprecated 1.4; see #5435
616 if TYPE_CHECKING:
617
618 def isnot_distinct_from(self, other: Any) -> ColumnOperators: ...
619
620 else:
621 isnot_distinct_from = is_not_distinct_from
622
623 def __gt__(self, other: Any) -> ColumnOperators:
624 """Implement the ``>`` operator.
625
626 In a column context, produces the clause ``a > b``.
627
628 """
629 return self.operate(gt, other)
630
631 def __ge__(self, other: Any) -> ColumnOperators:
632 """Implement the ``>=`` operator.
633
634 In a column context, produces the clause ``a >= b``.
635
636 """
637 return self.operate(ge, other)
638
639 def __neg__(self) -> ColumnOperators:
640 """Implement the ``-`` operator.
641
642 In a column context, produces the clause ``-a``.
643
644 """
645 return self.operate(neg)
646
647 def __contains__(self, other: Any) -> ColumnOperators:
648 return self.operate(contains, other)
649
650 def __getitem__(self, index: Any) -> ColumnOperators:
651 """Implement the [] operator.
652
653 This can be used by some database-specific types
654 such as PostgreSQL ARRAY and HSTORE.
655
656 """
657 return self.operate(getitem, index)
658
659 def __lshift__(self, other: Any) -> ColumnOperators:
660 """implement the << operator.
661
662 Not used by SQLAlchemy core, this is provided
663 for custom operator systems which want to use
664 << as an extension point.
665 """
666 return self.operate(lshift, other)
667
668 def __rshift__(self, other: Any) -> ColumnOperators:
669 """implement the >> operator.
670
671 Not used by SQLAlchemy core, this is provided
672 for custom operator systems which want to use
673 >> as an extension point.
674 """
675 return self.operate(rshift, other)
676
677 def concat(self, other: Any) -> ColumnOperators:
678 """Implement the 'concat' operator.
679
680 In a column context, produces the clause ``a || b``,
681 or uses the ``concat()`` operator on MySQL.
682
683 """
684 return self.operate(concat_op, other)
685
686 def _rconcat(self, other: Any) -> ColumnOperators:
687 """Implement an 'rconcat' operator.
688
689 this is for internal use at the moment
690
691 .. versionadded:: 1.4.40
692
693 """
694 return self.reverse_operate(concat_op, other)
695
696 def like(
697 self, other: Any, escape: Optional[str] = None
698 ) -> ColumnOperators:
699 r"""Implement the ``like`` operator.
700
701 In a column context, produces the expression::
702
703 a LIKE other
704
705 E.g.::
706
707 stmt = select(sometable).\
708 where(sometable.c.column.like("%foobar%"))
709
710 :param other: expression to be compared
711 :param escape: optional escape character, renders the ``ESCAPE``
712 keyword, e.g.::
713
714 somecolumn.like("foo/%bar", escape="/")
715
716 .. seealso::
717
718 :meth:`.ColumnOperators.ilike`
719
720 """
721 return self.operate(like_op, other, escape=escape)
722
723 def ilike(
724 self, other: Any, escape: Optional[str] = None
725 ) -> ColumnOperators:
726 r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE.
727
728 In a column context, produces an expression either of the form::
729
730 lower(a) LIKE lower(other)
731
732 Or on backends that support the ILIKE operator::
733
734 a ILIKE other
735
736 E.g.::
737
738 stmt = select(sometable).\
739 where(sometable.c.column.ilike("%foobar%"))
740
741 :param other: expression to be compared
742 :param escape: optional escape character, renders the ``ESCAPE``
743 keyword, e.g.::
744
745 somecolumn.ilike("foo/%bar", escape="/")
746
747 .. seealso::
748
749 :meth:`.ColumnOperators.like`
750
751 """
752 return self.operate(ilike_op, other, escape=escape)
753
754 def bitwise_xor(self, other: Any) -> ColumnOperators:
755 """Produce a bitwise XOR operation, typically via the ``^``
756 operator, or ``#`` for PostgreSQL.
757
758 .. versionadded:: 2.0.2
759
760 .. seealso::
761
762 :ref:`operators_bitwise`
763
764 """
765
766 return self.operate(bitwise_xor_op, other)
767
768 def bitwise_or(self, other: Any) -> ColumnOperators:
769 """Produce a bitwise OR operation, typically via the ``|``
770 operator.
771
772 .. versionadded:: 2.0.2
773
774 .. seealso::
775
776 :ref:`operators_bitwise`
777
778 """
779
780 return self.operate(bitwise_or_op, other)
781
782 def bitwise_and(self, other: Any) -> ColumnOperators:
783 """Produce a bitwise AND operation, typically via the ``&``
784 operator.
785
786 .. versionadded:: 2.0.2
787
788 .. seealso::
789
790 :ref:`operators_bitwise`
791
792 """
793
794 return self.operate(bitwise_and_op, other)
795
796 def bitwise_not(self) -> ColumnOperators:
797 """Produce a bitwise NOT operation, typically via the ``~``
798 operator.
799
800 .. versionadded:: 2.0.2
801
802 .. seealso::
803
804 :ref:`operators_bitwise`
805
806 """
807
808 return self.operate(bitwise_not_op)
809
810 def bitwise_lshift(self, other: Any) -> ColumnOperators:
811 """Produce a bitwise LSHIFT operation, typically via the ``<<``
812 operator.
813
814 .. versionadded:: 2.0.2
815
816 .. seealso::
817
818 :ref:`operators_bitwise`
819
820 """
821
822 return self.operate(bitwise_lshift_op, other)
823
824 def bitwise_rshift(self, other: Any) -> ColumnOperators:
825 """Produce a bitwise RSHIFT operation, typically via the ``>>``
826 operator.
827
828 .. versionadded:: 2.0.2
829
830 .. seealso::
831
832 :ref:`operators_bitwise`
833
834 """
835
836 return self.operate(bitwise_rshift_op, other)
837
838 def in_(self, other: Any) -> ColumnOperators:
839 """Implement the ``in`` operator.
840
841 In a column context, produces the clause ``column IN <other>``.
842
843 The given parameter ``other`` may be:
844
845 * A list of literal values, e.g.::
846
847 stmt.where(column.in_([1, 2, 3]))
848
849 In this calling form, the list of items is converted to a set of
850 bound parameters the same length as the list given::
851
852 WHERE COL IN (?, ?, ?)
853
854 * A list of tuples may be provided if the comparison is against a
855 :func:`.tuple_` containing multiple expressions::
856
857 from sqlalchemy import tuple_
858 stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
859
860 * An empty list, e.g.::
861
862 stmt.where(column.in_([]))
863
864 In this calling form, the expression renders an "empty set"
865 expression. These expressions are tailored to individual backends
866 and are generally trying to get an empty SELECT statement as a
867 subquery. Such as on SQLite, the expression is::
868
869 WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
870
871 .. versionchanged:: 1.4 empty IN expressions now use an
872 execution-time generated SELECT subquery in all cases.
873
874 * A bound parameter, e.g. :func:`.bindparam`, may be used if it
875 includes the :paramref:`.bindparam.expanding` flag::
876
877 stmt.where(column.in_(bindparam('value', expanding=True)))
878
879 In this calling form, the expression renders a special non-SQL
880 placeholder expression that looks like::
881
882 WHERE COL IN ([EXPANDING_value])
883
884 This placeholder expression is intercepted at statement execution
885 time to be converted into the variable number of bound parameter
886 form illustrated earlier. If the statement were executed as::
887
888 connection.execute(stmt, {"value": [1, 2, 3]})
889
890 The database would be passed a bound parameter for each value::
891
892 WHERE COL IN (?, ?, ?)
893
894 .. versionadded:: 1.2 added "expanding" bound parameters
895
896 If an empty list is passed, a special "empty list" expression,
897 which is specific to the database in use, is rendered. On
898 SQLite this would be::
899
900 WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
901
902 .. versionadded:: 1.3 "expanding" bound parameters now support
903 empty lists
904
905 * a :func:`_expression.select` construct, which is usually a
906 correlated scalar select::
907
908 stmt.where(
909 column.in_(
910 select(othertable.c.y).
911 where(table.c.x == othertable.c.x)
912 )
913 )
914
915 In this calling form, :meth:`.ColumnOperators.in_` renders as given::
916
917 WHERE COL IN (SELECT othertable.y
918 FROM othertable WHERE othertable.x = table.x)
919
920 :param other: a list of literals, a :func:`_expression.select`
921 construct, or a :func:`.bindparam` construct that includes the
922 :paramref:`.bindparam.expanding` flag set to True.
923
924 """
925 return self.operate(in_op, other)
926
927 def not_in(self, other: Any) -> ColumnOperators:
928 """implement the ``NOT IN`` operator.
929
930 This is equivalent to using negation with
931 :meth:`.ColumnOperators.in_`, i.e. ``~x.in_(y)``.
932
933 In the case that ``other`` is an empty sequence, the compiler
934 produces an "empty not in" expression. This defaults to the
935 expression "1 = 1" to produce true in all cases. The
936 :paramref:`_sa.create_engine.empty_in_strategy` may be used to
937 alter this behavior.
938
939 .. versionchanged:: 1.4 The ``not_in()`` operator is renamed from
940 ``notin_()`` in previous releases. The previous name remains
941 available for backwards compatibility.
942
943 .. versionchanged:: 1.2 The :meth:`.ColumnOperators.in_` and
944 :meth:`.ColumnOperators.not_in` operators
945 now produce a "static" expression for an empty IN sequence
946 by default.
947
948 .. seealso::
949
950 :meth:`.ColumnOperators.in_`
951
952 """
953 return self.operate(not_in_op, other)
954
955 # deprecated 1.4; see #5429
956 if TYPE_CHECKING:
957
958 def notin_(self, other: Any) -> ColumnOperators: ...
959
960 else:
961 notin_ = not_in
962
963 def not_like(
964 self, other: Any, escape: Optional[str] = None
965 ) -> ColumnOperators:
966 """implement the ``NOT LIKE`` operator.
967
968 This is equivalent to using negation with
969 :meth:`.ColumnOperators.like`, i.e. ``~x.like(y)``.
970
971 .. versionchanged:: 1.4 The ``not_like()`` operator is renamed from
972 ``notlike()`` in previous releases. The previous name remains
973 available for backwards compatibility.
974
975 .. seealso::
976
977 :meth:`.ColumnOperators.like`
978
979 """
980 return self.operate(not_like_op, other, escape=escape)
981
982 # deprecated 1.4; see #5435
983 if TYPE_CHECKING:
984
985 def notlike(
986 self, other: Any, escape: Optional[str] = None
987 ) -> ColumnOperators: ...
988
989 else:
990 notlike = not_like
991
992 def not_ilike(
993 self, other: Any, escape: Optional[str] = None
994 ) -> ColumnOperators:
995 """implement the ``NOT ILIKE`` operator.
996
997 This is equivalent to using negation with
998 :meth:`.ColumnOperators.ilike`, i.e. ``~x.ilike(y)``.
999
1000 .. versionchanged:: 1.4 The ``not_ilike()`` operator is renamed from
1001 ``notilike()`` in previous releases. The previous name remains
1002 available for backwards compatibility.
1003
1004 .. seealso::
1005
1006 :meth:`.ColumnOperators.ilike`
1007
1008 """
1009 return self.operate(not_ilike_op, other, escape=escape)
1010
1011 # deprecated 1.4; see #5435
1012 if TYPE_CHECKING:
1013
1014 def notilike(
1015 self, other: Any, escape: Optional[str] = None
1016 ) -> ColumnOperators: ...
1017
1018 else:
1019 notilike = not_ilike
1020
1021 def is_(self, other: Any) -> ColumnOperators:
1022 """Implement the ``IS`` operator.
1023
1024 Normally, ``IS`` is generated automatically when comparing to a
1025 value of ``None``, which resolves to ``NULL``. However, explicit
1026 usage of ``IS`` may be desirable if comparing to boolean values
1027 on certain platforms.
1028
1029 .. seealso:: :meth:`.ColumnOperators.is_not`
1030
1031 """
1032 return self.operate(is_, other)
1033
1034 def is_not(self, other: Any) -> ColumnOperators:
1035 """Implement the ``IS NOT`` operator.
1036
1037 Normally, ``IS NOT`` is generated automatically when comparing to a
1038 value of ``None``, which resolves to ``NULL``. However, explicit
1039 usage of ``IS NOT`` may be desirable if comparing to boolean values
1040 on certain platforms.
1041
1042 .. versionchanged:: 1.4 The ``is_not()`` operator is renamed from
1043 ``isnot()`` in previous releases. The previous name remains
1044 available for backwards compatibility.
1045
1046 .. seealso:: :meth:`.ColumnOperators.is_`
1047
1048 """
1049 return self.operate(is_not, other)
1050
1051 # deprecated 1.4; see #5429
1052 if TYPE_CHECKING:
1053
1054 def isnot(self, other: Any) -> ColumnOperators: ...
1055
1056 else:
1057 isnot = is_not
1058
1059 def startswith(
1060 self,
1061 other: Any,
1062 escape: Optional[str] = None,
1063 autoescape: bool = False,
1064 ) -> ColumnOperators:
1065 r"""Implement the ``startswith`` operator.
1066
1067 Produces a LIKE expression that tests against a match for the start
1068 of a string value::
1069
1070 column LIKE <other> || '%'
1071
1072 E.g.::
1073
1074 stmt = select(sometable).\
1075 where(sometable.c.column.startswith("foobar"))
1076
1077 Since the operator uses ``LIKE``, wildcard characters
1078 ``"%"`` and ``"_"`` that are present inside the <other> expression
1079 will behave like wildcards as well. For literal string
1080 values, the :paramref:`.ColumnOperators.startswith.autoescape` flag
1081 may be set to ``True`` to apply escaping to occurrences of these
1082 characters within the string value so that they match as themselves
1083 and not as wildcard characters. Alternatively, the
1084 :paramref:`.ColumnOperators.startswith.escape` parameter will establish
1085 a given character as an escape character which can be of use when
1086 the target expression is not a literal string.
1087
1088 :param other: expression to be compared. This is usually a plain
1089 string value, but can also be an arbitrary SQL expression. LIKE
1090 wildcard characters ``%`` and ``_`` are not escaped by default unless
1091 the :paramref:`.ColumnOperators.startswith.autoescape` flag is
1092 set to True.
1093
1094 :param autoescape: boolean; when True, establishes an escape character
1095 within the LIKE expression, then applies it to all occurrences of
1096 ``"%"``, ``"_"`` and the escape character itself within the
1097 comparison value, which is assumed to be a literal string and not a
1098 SQL expression.
1099
1100 An expression such as::
1101
1102 somecolumn.startswith("foo%bar", autoescape=True)
1103
1104 Will render as::
1105
1106 somecolumn LIKE :param || '%' ESCAPE '/'
1107
1108 With the value of ``:param`` as ``"foo/%bar"``.
1109
1110 :param escape: a character which when given will render with the
1111 ``ESCAPE`` keyword to establish that character as the escape
1112 character. This character can then be placed preceding occurrences
1113 of ``%`` and ``_`` to allow them to act as themselves and not
1114 wildcard characters.
1115
1116 An expression such as::
1117
1118 somecolumn.startswith("foo/%bar", escape="^")
1119
1120 Will render as::
1121
1122 somecolumn LIKE :param || '%' ESCAPE '^'
1123
1124 The parameter may also be combined with
1125 :paramref:`.ColumnOperators.startswith.autoescape`::
1126
1127 somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
1128
1129 Where above, the given literal parameter will be converted to
1130 ``"foo^%bar^^bat"`` before being passed to the database.
1131
1132 .. seealso::
1133
1134 :meth:`.ColumnOperators.endswith`
1135
1136 :meth:`.ColumnOperators.contains`
1137
1138 :meth:`.ColumnOperators.like`
1139
1140 """
1141 return self.operate(
1142 startswith_op, other, escape=escape, autoescape=autoescape
1143 )
1144
1145 def istartswith(
1146 self,
1147 other: Any,
1148 escape: Optional[str] = None,
1149 autoescape: bool = False,
1150 ) -> ColumnOperators:
1151 r"""Implement the ``istartswith`` operator, e.g. case insensitive
1152 version of :meth:`.ColumnOperators.startswith`.
1153
1154 Produces a LIKE expression that tests against an insensitive
1155 match for the start of a string value::
1156
1157 lower(column) LIKE lower(<other>) || '%'
1158
1159 E.g.::
1160
1161 stmt = select(sometable).\
1162 where(sometable.c.column.istartswith("foobar"))
1163
1164 Since the operator uses ``LIKE``, wildcard characters
1165 ``"%"`` and ``"_"`` that are present inside the <other> expression
1166 will behave like wildcards as well. For literal string
1167 values, the :paramref:`.ColumnOperators.istartswith.autoescape` flag
1168 may be set to ``True`` to apply escaping to occurrences of these
1169 characters within the string value so that they match as themselves
1170 and not as wildcard characters. Alternatively, the
1171 :paramref:`.ColumnOperators.istartswith.escape` parameter will
1172 establish a given character as an escape character which can be of
1173 use when the target expression is not a literal string.
1174
1175 :param other: expression to be compared. This is usually a plain
1176 string value, but can also be an arbitrary SQL expression. LIKE
1177 wildcard characters ``%`` and ``_`` are not escaped by default unless
1178 the :paramref:`.ColumnOperators.istartswith.autoescape` flag is
1179 set to True.
1180
1181 :param autoescape: boolean; when True, establishes an escape character
1182 within the LIKE expression, then applies it to all occurrences of
1183 ``"%"``, ``"_"`` and the escape character itself within the
1184 comparison value, which is assumed to be a literal string and not a
1185 SQL expression.
1186
1187 An expression such as::
1188
1189 somecolumn.istartswith("foo%bar", autoescape=True)
1190
1191 Will render as::
1192
1193 lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/'
1194
1195 With the value of ``:param`` as ``"foo/%bar"``.
1196
1197 :param escape: a character which when given will render with the
1198 ``ESCAPE`` keyword to establish that character as the escape
1199 character. This character can then be placed preceding occurrences
1200 of ``%`` and ``_`` to allow them to act as themselves and not