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