Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/operators.py: 65%

1# sql/operators.py

3# <see AUTHORS file>

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 __future__ import annotations

15from enum import auto

16from enum import Flag

17from enum import IntEnum

18from operator import add as _uncast_add

19from operator import and_ as _uncast_and_

20from operator import contains as _uncast_contains

21from operator import eq as _uncast_eq

22from operator import floordiv as _uncast_floordiv

23from operator import ge as _uncast_ge

24from operator import getitem as _uncast_getitem

25from operator import gt as _uncast_gt

26from operator import inv as _uncast_inv

27from operator import le as _uncast_le

28from operator import lshift as _uncast_lshift

29from operator import lt as _uncast_lt

30from operator import matmul as _uncast_matmul

31from operator import mod as _uncast_mod

32from operator import mul as _uncast_mul

33from operator import ne as _uncast_ne

34from operator import neg as _uncast_neg

35from operator import or_ as _uncast_or_

36from operator import pow as _uncast_pow

37from operator import rshift as _uncast_rshift

38from operator import sub as _uncast_sub

39from operator import truediv as _uncast_truediv

40import typing

41from typing import Any

42from typing import Callable

43from typing import cast

44from typing import Dict

45from typing import Generic

46from typing import Hashable

47from typing import Literal

48from typing import Optional

49from typing import overload

50from typing import Protocol

51from typing import Set

52from typing import Tuple

53from typing import Type

54from typing import TYPE_CHECKING

55from typing import TypeVar

56from typing import Union

58from .. import exc

59from .. import util

61if typing.TYPE_CHECKING:

62 from ._typing import ColumnExpressionArgument

63 from .cache_key import CacheConst

64 from .elements import ColumnElement

65 from .type_api import TypeEngine

67_T = TypeVar("_T", bound=Any)

68_FN = TypeVar("_FN", bound=Callable[..., Any])

71class OperatorClass(Flag):

72 """Describes a class of SQLAlchemy built-in operators that should be

73 available on a particular type.

75 The :class:`.OperatorClass` should be present on the

76 :attr:`.TypeEngine.operator_classes` attribute of any particular type.

78 The enums here can be ORed together to provide sets of operators merged

79 together.

81 .. versionadded:: 2.1

83 """

85 UNSPECIFIED = auto()

86 BASE = auto()

87 BOOLEAN_ALGEBRA = auto()

88 COMPARISON = auto()

89 INDEXABLE = auto() # noqa: F811

90 CONTAINS = auto()

91 CONCATENABLE = auto()

92 STRING_MATCH = auto()

93 MATH = auto()

94 BITWISE = auto()

95 DATE_ARITHEMETIC = auto()

96 JSON_GETITEM = auto()

98 STRING = (

100 )

101 INTEGER = BASE | COMPARISON | MATH | BITWISE

102 NUMERIC = BASE | COMPARISON | MATH | BITWISE

103 BOOLEAN = BASE | COMPARISON | BOOLEAN_ALGEBRA | COMPARISON

104 BINARY = BASE | COMPARISON | CONTAINS | CONCATENABLE | INDEXABLE

105 DATETIME = BASE | COMPARISON | DATE_ARITHEMETIC

106 JSON = BASE | COMPARISON | INDEXABLE | JSON_GETITEM

107 ARRAY = BASE | COMPARISON | CONTAINS | CONCATENABLE | INDEXABLE

108 TUPLE = BASE | COMPARISON | CONTAINS | CONCATENABLE | INDEXABLE

109

110 ANY = (

111 STRING

112 | INTEGER

113 | NUMERIC

114 | BOOLEAN

115 | DATETIME

116 | BINARY

117 | JSON

118 | ARRAY

119 | TUPLE

120 )

121

122

123class OperatorType(Hashable, Protocol):

124 """describe an op() function."""

125

126 __slots__ = ()

127

128 __name__: str

129

130 @overload

131 def __call__(

132 self,

133 left: ColumnExpressionArgument[Any],

134 right: Optional[Any] = None,

135 *other: Any,

136 **kwargs: Any,

137 ) -> ColumnElement[Any]: ...

138

139 @overload

140 def __call__(

141 self,

142 left: Operators,

143 right: Optional[Any] = None,

144 *other: Any,

145 **kwargs: Any,

146 ) -> Operators: ...

147

148 def __call__(

149 self,

150 left: Any,

151 right: Optional[Any] = None,

152 *other: Any,

153 **kwargs: Any,

154 ) -> Operators: ...

155

156

157add = cast(OperatorType, _uncast_add)

158and_ = cast(OperatorType, _uncast_and_)

159contains = cast(OperatorType, _uncast_contains)

160eq = cast(OperatorType, _uncast_eq)

161floordiv = cast(OperatorType, _uncast_floordiv)

162ge = cast(OperatorType, _uncast_ge)

163getitem = cast(OperatorType, _uncast_getitem)

164gt = cast(OperatorType, _uncast_gt)

165inv = cast(OperatorType, _uncast_inv)

166le = cast(OperatorType, _uncast_le)

167lshift = cast(OperatorType, _uncast_lshift)

168lt = cast(OperatorType, _uncast_lt)

169matmul = cast(OperatorType, _uncast_matmul)

170mod = cast(OperatorType, _uncast_mod)

171mul = cast(OperatorType, _uncast_mul)

172ne = cast(OperatorType, _uncast_ne)

173neg = cast(OperatorType, _uncast_neg)

174or_ = cast(OperatorType, _uncast_or_)

175pow_ = cast(OperatorType, _uncast_pow)

176rshift = cast(OperatorType, _uncast_rshift)

177sub = cast(OperatorType, _uncast_sub)

178truediv = cast(OperatorType, _uncast_truediv)

179

180

181class Operators:

182 """Base of comparison and logical operators.

183

184 Implements base methods

185 :meth:`~sqlalchemy.sql.operators.Operators.operate` and

186 :meth:`~sqlalchemy.sql.operators.Operators.reverse_operate`, as well as

187 :meth:`~sqlalchemy.sql.operators.Operators.__and__`,

188 :meth:`~sqlalchemy.sql.operators.Operators.__or__`,

189 :meth:`~sqlalchemy.sql.operators.Operators.__invert__`.

190

191 Usually is used via its most common subclass

192 :class:`.ColumnOperators`.

193

194 """

195

196 __slots__ = ()

197

198 def __and__(self, other: Any) -> Operators:

199 """Implement the ``&`` operator.

200

201 When used with SQL expressions, results in an

202 AND operation, equivalent to

203 :func:`_expression.and_`, that is::

204

205 a & b

206

207 is equivalent to::

208

209 from sqlalchemy import and_

210

211 and_(a, b)

212

213 Care should be taken when using ``&`` regarding

214 operator precedence; the ``&`` operator has the highest precedence.

215 The operands should be enclosed in parenthesis if they contain

216 further sub expressions::

217

218 (a == 2) & (b == 4)

219

220 """

221 return self.operate(and_, other)

222

223 def __or__(self, other: Any) -> Operators:

224 """Implement the ``|`` operator.

225

226 When used with SQL expressions, results in an

227 OR operation, equivalent to

228 :func:`_expression.or_`, that is::

229

230 a | b

231

232 is equivalent to::

233

234 from sqlalchemy import or_

235

236 or_(a, b)

237

238 Care should be taken when using ``|`` regarding

239 operator precedence; the ``|`` operator has the highest precedence.

240 The operands should be enclosed in parenthesis if they contain

241 further sub expressions::

242

243 (a == 2) | (b == 4)

244

245 """

246 return self.operate(or_, other)

247

248 def __invert__(self) -> Operators:

249 """Implement the ``~`` operator.

250

251 When used with SQL expressions, results in a

252 NOT operation, equivalent to

253 :func:`_expression.not_`, that is::

254

255 ~a

256

257 is equivalent to::

258

259 from sqlalchemy import not_

260

261 not_(a)

262

263 """

264 return self.operate(inv)

265

266 def _null_operate(self, other: Any) -> Operators:

267 """A 'null' operation available on all types, used for testing."""

268

269 return self.operate(null_op, other)

270

271 def op(

272 self,

273 opstring: str,

274 precedence: int = 0,

275 is_comparison: bool = False,

276 return_type: Optional[

277 Union[Type[TypeEngine[Any]], TypeEngine[Any]]

278 ] = None,

279 python_impl: Optional[Callable[..., Any]] = None,

280 operator_class: OperatorClass = OperatorClass.BASE,

281 visit_name: Optional[str] = None,

282 ) -> Callable[[Any], Operators]:

283 """Produce a generic operator function.

284

285 e.g.::

286

287 somecolumn.op("*")(5)

288

289 produces::

290

291 somecolumn * 5

292

293 This function can also be used to make bitwise operators explicit. For

294 example::

295

296 somecolumn.op("&")(0xFF)

297

298 is a bitwise AND of the value in ``somecolumn``.

299

300 :param opstring: a string which will be output as the infix operator

301 between this element and the expression passed to the

302 generated function.

303

304 :param precedence: precedence which the database is expected to apply

305 to the operator in SQL expressions. This integer value acts as a hint

306 for the SQL compiler to know when explicit parenthesis should be

307 rendered around a particular operation. A lower number will cause the

308 expression to be parenthesized when applied against another operator

309 with higher precedence. The default value of ``0`` is lower than all

310 operators except for the comma (``,``) and ``AS`` operators. A value

311 of 100 will be higher or equal to all operators, and -100 will be

312 lower than or equal to all operators.

313

314 .. seealso::

315

316 :ref:`faq_sql_expression_op_parenthesis` - detailed description

317 of how the SQLAlchemy SQL compiler renders parenthesis

318

319 :param is_comparison: legacy; if True, the operator will be considered

320 as a "comparison" operator, that is which evaluates to a boolean

321 true/false value, like ``==``, ``>``, etc. This flag is provided

322 so that ORM relationships can establish that the operator is a

323 comparison operator when used in a custom join condition.

324

325 Using the ``is_comparison`` parameter is superseded by using the

326 :meth:`.Operators.bool_op` method instead; this more succinct

327 operator sets this parameter automatically, but also provides

328 correct :pep:`484` typing support as the returned object will

329 express a "boolean" datatype, i.e. ``BinaryExpression[bool]``.

330

331 :param return_type: a :class:`.TypeEngine` class or object that will

332 force the return type of an expression produced by this operator

333 to be of that type. By default, operators that specify

334 :paramref:`.Operators.op.is_comparison` will resolve to

335 :class:`.Boolean`, and those that do not will be of the same

336 type as the left-hand operand.

337

338 :param python_impl: an optional Python function that can evaluate

339 two Python values in the same way as this operator works when

340 run on the database server. Useful for in-Python SQL expression

341 evaluation functions, such as for ORM hybrid attributes, and the

342 ORM "evaluator" used to match objects in a session after a multi-row

343 update or delete.

344

345 e.g.::

346

347 >>> expr = column("x").op("+", python_impl=lambda a, b: a + b)("y")

348

349 The operator for the above expression will also work for non-SQL

350 left and right objects::

351

352 >>> expr.operator(5, 10)

353 15

354

355 .. versionadded:: 2.0

356

357 :param operator_class: optional :class:`.OperatorClass` which will be

358 applied to the :class:`.custom_op` created, which provides hints

359 as to which datatypes are appropriate for this operator. Defaults

360 to :attr:`.OperatorClass.BASE` which is appropriate for all

361 datatypes.

362

363 .. versionadded:: 2.1

364

365 .. seealso::

366

367 :meth:`.Operators.bool_op`

368

369 :ref:`types_operators`

370

371 :ref:`relationship_custom_operator`

372

373 :param visit_name: string name indicating a series of methods that

374 maybe be implemented by a :class:`.Dialect`, specifically using its

375 :class:`.SQLCompiler` implementation. The naming scheme is

376 ``visit_<visit_name>_op_[binary|unary]``; e.g. using the visit name

377 ``hstore`` means that a binary expression using the operator will

378 attempt to locate a method ``visit_hstore_op_binary()`` on the

379 target dialect's compiler class, which can then provide a compilation

380 string for the full binary expression.

381

382 .. versionadded:: 2.1

383

384 """

385 operator = custom_op(

386 opstring,

387 precedence=precedence,

388 is_comparison=is_comparison,

389 return_type=return_type,

390 python_impl=python_impl,

391 operator_class=operator_class,

392 visit_name=visit_name,

393 )

394

395 def against(other: Any) -> Operators:

396 return operator(self, other)

397

398 return against

399

400 def bool_op(

401 self,

402 opstring: str,

403 precedence: int = 0,

404 python_impl: Optional[Callable[..., Any]] = None,

405 ) -> Callable[[Any], Operators]:

406 """Return a custom boolean operator.

407

408 This method is shorthand for calling

409 :meth:`.Operators.op` and passing the

410 :paramref:`.Operators.op.is_comparison`

411 flag with True. A key advantage to using :meth:`.Operators.bool_op`

412 is that when using column constructs, the "boolean" nature of the

413 returned expression will be present for :pep:`484` purposes.

414

415 .. seealso::

416

417 :meth:`.Operators.op`

418

419 """

420 return self.op(

421 opstring,

422 precedence=precedence,

423 is_comparison=True,

424 python_impl=python_impl,

425 )

426

427 def operate(

428 self, op: OperatorType, *other: Any, **kwargs: Any

429 ) -> Operators:

430 r"""Operate on an argument.

431

432 This is the lowest level of operation, raises

433 :class:`NotImplementedError` by default.

434

435 Overriding this on a subclass can allow common

436 behavior to be applied to all operations.

437 For example, overriding :class:`.ColumnOperators`

438 to apply ``func.lower()`` to the left and right

439 side::

440

441 class MyComparator(ColumnOperators):

442 def operate(self, op, other, **kwargs):

443 return op(func.lower(self), func.lower(other), **kwargs)

444

445 :param op: Operator callable.

446 :param \*other: the 'other' side of the operation. Will

447 be a single scalar for most operations.

448 :param \**kwargs: modifiers. These may be passed by special

449 operators such as :meth:`ColumnOperators.contains`.

450

451

452 """

453 raise NotImplementedError(str(op))

454

455 __sa_operate__ = operate

456

457 def reverse_operate(

458 self, op: OperatorType, other: Any, **kwargs: Any

459 ) -> Operators:

460 """Reverse operate on an argument.

461

462 Usage is the same as :meth:`operate`.

463

464 """

465 raise NotImplementedError(str(op))

466

467

468class custom_op(OperatorType, Generic[_T]):

469 """Represent a 'custom' operator.

470

471 :class:`.custom_op` is normally instantiated when the

472 :meth:`.Operators.op` or :meth:`.Operators.bool_op` methods

473 are used to create a custom operator callable. The class can also be

474 used directly when programmatically constructing expressions. E.g.

475 to represent the "factorial" operation::

476

477 from sqlalchemy.sql import UnaryExpression

478 from sqlalchemy.sql import operators

479 from sqlalchemy import Numeric

480

481 unary = UnaryExpression(

482 table.c.somecolumn, modifier=operators.custom_op("!"), type_=Numeric

483 )

484

485 .. seealso::

486

487 :meth:`.Operators.op`

488

489 :meth:`.Operators.bool_op`

490

491 """ # noqa: E501

492

493 __name__ = "custom_op"

494

495 __slots__ = (

496 "opstring",

497 "precedence",

498 "is_comparison",

499 "natural_self_precedent",

500 "eager_grouping",

501 "return_type",

502 "python_impl",

503 "operator_class",

504 "visit_name",

505 )

506

507 def __init__(

508 self,

509 opstring: str,

510 *,

511 precedence: int = 0,

512 is_comparison: bool = False,

513 return_type: Optional[

514 Union[Type[TypeEngine[_T]], TypeEngine[_T]]

515 ] = None,

516 natural_self_precedent: bool = False,

517 eager_grouping: bool = False,

518 python_impl: Optional[Callable[..., Any]] = None,

519 operator_class: OperatorClass = OperatorClass.BASE,

520 visit_name: Optional[str] = None,

521 ):

522 """Create a new :class:`.custom_op`.

523

524 See :meth:`.Operators.op` for parameter information.

525

526 """

527 self.opstring = opstring

528 self.precedence = precedence

529 self.is_comparison = is_comparison

530 self.natural_self_precedent = natural_self_precedent

531 self.eager_grouping = eager_grouping

532 self.return_type = (

533 return_type._to_instance(return_type) if return_type else None

534 )

535 self.python_impl = python_impl

536 self.operator_class = operator_class

537 self.visit_name = visit_name

538

539 def __eq__(self, other: Any) -> bool:

540 return (

541 isinstance(other, custom_op)

542 and other._hash_key() == self._hash_key()

543 )

544

545 def __hash__(self) -> int:

546 return hash(self._hash_key())

547

548 def _hash_key(self) -> Union[CacheConst, Tuple[Any, ...]]:

549 return (

550 self.__class__,

551 self.opstring,

552 self.precedence,

553 self.is_comparison,

554 self.natural_self_precedent,

555 self.eager_grouping,

556 self.return_type._static_cache_key if self.return_type else None,

557 self.operator_class,

558 )

559

560 @overload

561 def __call__(

562 self,

563 left: ColumnExpressionArgument[Any],

564 right: Optional[Any] = None,

565 *other: Any,

566 **kwargs: Any,

567 ) -> ColumnElement[Any]: ...

568

569 @overload

570 def __call__(

571 self,

572 left: Operators,

573 right: Optional[Any] = None,

574 *other: Any,

575 **kwargs: Any,

576 ) -> Operators: ...

577

578 def __call__(

579 self,

580 left: Any,

581 right: Optional[Any] = None,

582 *other: Any,

583 **kwargs: Any,

584 ) -> Operators:

585 if hasattr(left, "__sa_operate__"):

586 return left.operate(self, right, *other, **kwargs) # type: ignore[no-any-return] # noqa: E501

587 elif self.python_impl:

588 return self.python_impl(left, right, *other, **kwargs) # type: ignore[no-any-return] # noqa: E501

589 else:

590 raise exc.InvalidRequestError(

591 f"Custom operator {self.opstring!r} can't be used with "

592 "plain Python objects unless it includes the "

593 "'python_impl' parameter."

594 )

595

596

597class OrderingOperators(Operators):

598 """defines ORDER BY operators, which can operate as single expressions

599 or comma-separated lists

600

601 """

602

603 __slots__ = ()

604

605 if typing.TYPE_CHECKING:

606

607 def operate(

608 self, op: OperatorType, *other: Any, **kwargs: Any

609 ) -> OrderingOperators: ...

610

611 def reverse_operate(

612 self, op: OperatorType, other: Any, **kwargs: Any

613 ) -> OrderingOperators: ...

614

615 def desc(self) -> OrderingOperators:

616 """Produce a :func:`_expression.desc` clause against the

617 parent object."""

618 return self.operate(desc_op)

619

620 def asc(self) -> OrderingOperators:

621 """Produce a :func:`_expression.asc` clause against the

622 parent object."""

623 return self.operate(asc_op)

624

625 def nulls_first(self) -> OrderingOperators:

626 """Produce a :func:`_expression.nulls_first` clause against the

627 parent object.

628

629 .. versionchanged:: 1.4 The ``nulls_first()`` operator is

630 renamed from ``nullsfirst()`` in previous releases.

631 The previous name remains available for backwards compatibility.

632 """

633 return self.operate(nulls_first_op)

634

635 # deprecated 1.4; see #5435

636 if TYPE_CHECKING:

637

638 def nullsfirst(self) -> OrderingOperators: ...

639

640 else:

641 nullsfirst = nulls_first

642

643 def nulls_last(self) -> OrderingOperators:

644 """Produce a :func:`_expression.nulls_last` clause against the

645 parent object.

646

647 .. versionchanged:: 1.4 The ``nulls_last()`` operator is

648 renamed from ``nullslast()`` in previous releases.

649 The previous name remains available for backwards compatibility.

650 """

651 return self.operate(nulls_last_op)

652

653 # deprecated 1.4; see #5429

654 if TYPE_CHECKING:

655

656 def nullslast(self) -> OrderingOperators: ...

657

658 else:

659 nullslast = nulls_last

660

661

662class ColumnOperators(OrderingOperators):

663 """Defines boolean, comparison, and other operators for

664 :class:`_expression.ColumnElement` expressions.

665

666 By default, all methods call down to

667 :meth:`.operate` or :meth:`.reverse_operate`,

668 passing in the appropriate operator function from the

669 Python builtin ``operator`` module or

670 a SQLAlchemy-specific operator function from

671 :mod:`sqlalchemy.expression.operators`. For example

672 the ``__eq__`` function::

673

674 def __eq__(self, other):

675 return self.operate(operators.eq, other)

676

677 Where ``operators.eq`` is essentially::

678

679 def eq(a, b):

680 return a == b

681

682 The core column expression unit :class:`_expression.ColumnElement`

683 overrides :meth:`.Operators.operate` and others

684 to return further :class:`_expression.ColumnElement` constructs,

685 so that the ``==`` operation above is replaced by a clause

686 construct.

687

688 .. seealso::

689

690 :ref:`types_operators`

691

692 :attr:`.TypeEngine.comparator_factory`

693

694 :class:`.ColumnOperators`

695

696 :class:`.PropComparator`

697

698 """

699

700 __slots__ = ()

701

702 timetuple: Literal[None] = None

703 """Hack, allows datetime objects to be compared on the LHS."""

704

705 if typing.TYPE_CHECKING:

706

707 def operate(

708 self, op: OperatorType, *other: Any, **kwargs: Any

709 ) -> ColumnOperators: ...

710

711 def reverse_operate(

712 self, op: OperatorType, other: Any, **kwargs: Any

713 ) -> ColumnOperators: ...

714

715 def __lt__(self, other: Any) -> ColumnOperators:

716 """Implement the ``<`` operator.

717

718 In a column context, produces the clause ``a < b``.

719

720 """

721 return self.operate(lt, other)

722

723 def __le__(self, other: Any) -> ColumnOperators:

724 """Implement the ``<=`` operator.

725

726 In a column context, produces the clause ``a <= b``.

727

728 """

729 return self.operate(le, other)

730

731 # ColumnOperators defines an __eq__ so it must explicitly declare also

732 # an hash or it's set to None by python:

733 # https://docs.python.org/3/reference/datamodel.html#object.__hash__

734 if TYPE_CHECKING:

735

736 def __hash__(self) -> int: ...

737

738 else:

739 __hash__ = Operators.__hash__

740

741 def __eq__(self, other: Any) -> ColumnOperators: # type: ignore[override]

742 """Implement the ``==`` operator.

743

744 In a column context, produces the clause ``a = b``.

745 If the target is ``None``, produces ``a IS NULL``.

746

747 """

748 return self.operate(eq, other)

749

750 def __ne__(self, other: Any) -> ColumnOperators: # type: ignore[override]

751 """Implement the ``!=`` operator.

752

753 In a column context, produces the clause ``a != b``.

754 If the target is ``None``, produces ``a IS NOT NULL``.

755

756 """

757 return self.operate(ne, other)

758

759 def is_distinct_from(self, other: Any) -> ColumnOperators:

760 """Implement the ``IS DISTINCT FROM`` operator.

761

762 Renders "a IS DISTINCT FROM b" on most platforms;

763 on some such as SQLite may render "a IS NOT b".

764

765 """

766 return self.operate(is_distinct_from, other)

767

768 def is_not_distinct_from(self, other: Any) -> ColumnOperators:

769 """Implement the ``IS NOT DISTINCT FROM`` operator.

770

771 Renders "a IS NOT DISTINCT FROM b" on most platforms;

772 on some such as SQLite may render "a IS b".

773

774 .. versionchanged:: 1.4 The ``is_not_distinct_from()`` operator is

775 renamed from ``isnot_distinct_from()`` in previous releases.

776 The previous name remains available for backwards compatibility.

777

778 """

779 return self.operate(is_not_distinct_from, other)

780

781 # deprecated 1.4; see #5435

782 if TYPE_CHECKING:

783

784 def isnot_distinct_from(self, other: Any) -> ColumnOperators: ...

785

786 else:

787 isnot_distinct_from = is_not_distinct_from

788

789 def __gt__(self, other: Any) -> ColumnOperators:

790 """Implement the ``>`` operator.

791

792 In a column context, produces the clause ``a > b``.

793

794 """

795 return self.operate(gt, other)

796

797 def __ge__(self, other: Any) -> ColumnOperators:

798 """Implement the ``>=`` operator.

799

800 In a column context, produces the clause ``a >= b``.

801

802 """

803 return self.operate(ge, other)

804

805 def __neg__(self) -> ColumnOperators:

806 """Implement the ``-`` operator.

807

808 In a column context, produces the clause ``-a``.

809

810 """

811 return self.operate(neg)

812

813 def __contains__(self, other: Any) -> ColumnOperators:

814 return self.operate(contains, other)

815

816 def __getitem__(self, index: Any) -> ColumnOperators:

817 """Implement the [] operator.

818

819 This can be used by some database-specific types

820 such as PostgreSQL ARRAY and HSTORE.

821

822 """

823 return self.operate(getitem, index)

824

825 def __lshift__(self, other: Any) -> ColumnOperators:

826 """Implement the ``<<`` operator.

827

828 Not used by SQLAlchemy core, this is provided

829 for custom operator systems which want to use

830 << as an extension point.

831 """

832 return self.operate(lshift, other)

833

834 def __rlshift__(self, other: Any) -> ColumnOperators:

835 """Implement the ``<<`` operator in reverse.

836

837 Not used by SQLAlchemy core, this is provided

838 for custom operator systems which want to use

839 << as an extension point.

840 """

841 return self.reverse_operate(lshift, other)

842

843 def __rshift__(self, other: Any) -> ColumnOperators:

844 """Implement the ``>>`` operator.

845

846 Not used by SQLAlchemy core, this is provided

847 for custom operator systems which want to use

848 >> as an extension point.

849 """

850 return self.operate(rshift, other)

851

852 def __rrshift__(self, other: Any) -> ColumnOperators:

853 """Implement the ``>>`` operator in reverse.

854

855 Not used by SQLAlchemy core, this is provided

856 for custom operator systems which want to use

857 >> as an extension point.

858 """

859 return self.reverse_operate(rshift, other)

860

861 def __matmul__(self, other: Any) -> ColumnOperators:

862 """Implement the ``@`` operator.

863

864 Not used by SQLAlchemy core, this is provided

865 for custom operator systems which want to use

866 @ as an extension point.

867 """

868 return self.operate(matmul, other)

869

870 def __rmatmul__(self, other: Any) -> ColumnOperators:

871 """Implement the ``@`` operator in reverse.

872

873 Not used by SQLAlchemy core, this is provided

874 for custom operator systems which want to use

875 @ as an extension point.

876 """

877 return self.reverse_operate(matmul, other)

878

879 def concat(self, other: Any) -> ColumnOperators:

880 """Implement the 'concat' operator.

881

882 In a column context, produces the clause ``a || b``,

883 or uses the ``concat()`` operator on MySQL.

884

885 """

886 return self.operate(concat_op, other)

887

888 def _rconcat(self, other: Any) -> ColumnOperators:

889 """Implement an 'rconcat' operator.

890

891 this is for internal use at the moment

892

893 .. versionadded:: 1.4.40

894

895 """

896 return self.reverse_operate(concat_op, other)

897

898 def like(

899 self, other: Any, escape: Optional[str] = None

900 ) -> ColumnOperators:

901 r"""Implement the ``like`` operator.

902

903 In a column context, produces the expression:

904

905 .. sourcecode:: sql

906

907 a LIKE other

908

909 E.g.::

910

911 stmt = select(sometable).where(sometable.c.column.like("%foobar%"))

912

913 :param other: expression to be compared

914 :param escape: optional escape character, renders the ``ESCAPE``

915 keyword, e.g.::

916

917 somecolumn.like("foo/%bar", escape="/")

918

919 .. seealso::

920

921 :meth:`.ColumnOperators.ilike`

922

923 """

924 return self.operate(like_op, other, escape=escape)

925

926 def ilike(

927 self, other: Any, escape: Optional[str] = None

928 ) -> ColumnOperators:

929 r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE.

930

931 In a column context, produces an expression either of the form:

932

933 .. sourcecode:: sql

934

935 lower(a) LIKE lower(other)

936

937 Or on backends that support the ILIKE operator:

938

939 .. sourcecode:: sql

940

941 a ILIKE other

942

943 E.g.::

944

945 stmt = select(sometable).where(sometable.c.column.ilike("%foobar%"))

946

947 :param other: expression to be compared

948 :param escape: optional escape character, renders the ``ESCAPE``

949 keyword, e.g.::

950

951 somecolumn.ilike("foo/%bar", escape="/")

952

953 .. seealso::

954

955 :meth:`.ColumnOperators.like`

956

957 """ # noqa: E501

958 return self.operate(ilike_op, other, escape=escape)

959

960 def bitwise_xor(self, other: Any) -> ColumnOperators:

961 """Produce a bitwise XOR operation, typically via the ``^``

962 operator, or ``#`` for PostgreSQL.

963

964 .. versionadded:: 2.0.2

965

966 .. seealso::

967

968 :ref:`operators_bitwise`

969

970 """

971

972 return self.operate(bitwise_xor_op, other)

973

974 def bitwise_or(self, other: Any) -> ColumnOperators:

975 """Produce a bitwise OR operation, typically via the ``|``

976 operator.

977

978 .. versionadded:: 2.0.2

979

980 .. seealso::

981

982 :ref:`operators_bitwise`

983

984 """

985

986 return self.operate(bitwise_or_op, other)

987

988 def bitwise_and(self, other: Any) -> ColumnOperators:

989 """Produce a bitwise AND operation, typically via the ``&``

990 operator.

991

992 .. versionadded:: 2.0.2

993

994 .. seealso::

995

996 :ref:`operators_bitwise`

997

998 """

999

1000 return self.operate(bitwise_and_op, other)

1001

1002 def bitwise_not(self) -> ColumnOperators:

1003 """Produce a bitwise NOT operation, typically via the ``~``

1004 operator.

1005

1006 .. versionadded:: 2.0.2

1007

1008 .. seealso::

1009

1010 :ref:`operators_bitwise`

1011

1012 """

1013

1014 return self.operate(bitwise_not_op)

1015

1016 def bitwise_lshift(self, other: Any) -> ColumnOperators:

1017 """Produce a bitwise LSHIFT operation, typically via the ``<<``

1018 operator.

1019

1020 .. versionadded:: 2.0.2

1021

1022 .. seealso::

1023

1024 :ref:`operators_bitwise`

1025

1026 """

1027

1028 return self.operate(bitwise_lshift_op, other)

1029

1030 def bitwise_rshift(self, other: Any) -> ColumnOperators:

1031 """Produce a bitwise RSHIFT operation, typically via the ``>>``

1032 operator.

1033

1034 .. versionadded:: 2.0.2

1035

1036 .. seealso::

1037

1038 :ref:`operators_bitwise`

1039

1040 """

1041

1042 return self.operate(bitwise_rshift_op, other)

1043

1044 def in_(self, other: Any) -> ColumnOperators:

1045 """Implement the ``in`` operator.

1046

1047 In a column context, produces the clause ``column IN <other>``.

1048

1049 The given parameter ``other`` may be:

1050

1051 * A list of literal values,

1052 e.g.::

1053

1054 stmt.where(column.in_([1, 2, 3]))

1055

1056 In this calling form, the list of items is converted to a set of

1057 bound parameters the same length as the list given:

1058

1059 .. sourcecode:: sql

1060

1061 WHERE COL IN (?, ?, ?)

1062

1063 * A list of tuples may be provided if the comparison is against a

1064 :func:`.tuple_` containing multiple expressions::

1065

1066 from sqlalchemy import tuple_

1067

1068 stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))

1069

1070 * An empty list,

1071 e.g.::

1072

1073 stmt.where(column.in_([]))

1074

1075 In this calling form, the expression renders an "empty set"

1076 expression. These expressions are tailored to individual backends

1077 and are generally trying to get an empty SELECT statement as a

1078 subquery. Such as on SQLite, the expression is:

1079

1080 .. sourcecode:: sql

1081

1082 WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

1083

1084 .. versionchanged:: 1.4 empty IN expressions now use an

1085 execution-time generated SELECT subquery in all cases.

1086

1087 * A bound parameter, e.g. :func:`.bindparam`, may be used if it

1088 includes the :paramref:`.bindparam.expanding` flag::

1089

1090 stmt.where(column.in_(bindparam("value", expanding=True)))

1091

1092 In this calling form, the expression renders a special non-SQL

1093 placeholder expression that looks like:

1094

1095 .. sourcecode:: sql

1096

1097 WHERE COL IN ([EXPANDING_value])

1098

1099 This placeholder expression is intercepted at statement execution

1100 time to be converted into the variable number of bound parameter

1101 form illustrated earlier. If the statement were executed as::

1102

1103 connection.execute(stmt, {"value": [1, 2, 3]})

1104

1105 The database would be passed a bound parameter for each value:

1106

1107 .. sourcecode:: sql

1108

1109 WHERE COL IN (?, ?, ?)

1110

1111 If an empty list is passed, a special "empty list" expression,

1112 which is specific to the database in use, is rendered. On

1113 SQLite this would be:

1114

1115 .. sourcecode:: sql

1116

1117 WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

1118

1119 * a :func:`_expression.select` construct, which is usually a

1120 correlated scalar select::

1121

1122 stmt.where(

1123 column.in_(select(othertable.c.y).where(table.c.x == othertable.c.x))

1124 )

1125

1126 In this calling form, :meth:`.ColumnOperators.in_` renders as given:

1127

1128 .. sourcecode:: sql

1129

1130 WHERE COL IN (SELECT othertable.y

1131 FROM othertable WHERE othertable.x = table.x)

1132

1133 :param other: a list of literals, a :func:`_expression.select`

1134 construct, or a :func:`.bindparam` construct that includes the

1135 :paramref:`.bindparam.expanding` flag set to True.

1136

1137 """ # noqa: E501

1138 return self.operate(in_op, other)

1139

1140 def not_in(self, other: Any) -> ColumnOperators:

1141 """implement the ``NOT IN`` operator.

1142

1143 This is equivalent to using negation with

1144 :meth:`.ColumnOperators.in_`, i.e. ``~x.in_(y)``.

1145

1146 In the case that ``other`` is an empty sequence, the compiler

1147 produces an "empty not in" expression. This defaults to the

1148 expression "1 = 1" to produce true in all cases. The

1149 :paramref:`_sa.create_engine.empty_in_strategy` may be used to

1150 alter this behavior.

1151

1152 .. versionchanged:: 1.4 The ``not_in()`` operator is renamed from

1153 ``notin_()`` in previous releases. The previous name remains

1154 available for backwards compatibility.

1155

1156 .. seealso::

1157

1158 :meth:`.ColumnOperators.in_`

1159

1160 """

1161 return self.operate(not_in_op, other)

1162

1163 # deprecated 1.4; see #5429

1164 if TYPE_CHECKING:

1165

1166 def notin_(self, other: Any) -> ColumnOperators: ...

1167

1168 else:

1169 notin_ = not_in

1170

1171 def not_like(

1172 self, other: Any, escape: Optional[str] = None

1173 ) -> ColumnOperators:

1174 """implement the ``NOT LIKE`` operator.

1175

1176 This is equivalent to using negation with

1177 :meth:`.ColumnOperators.like`, i.e. ``~x.like(y)``.

1178

1179 .. versionchanged:: 1.4 The ``not_like()`` operator is renamed from

1180 ``notlike()`` in previous releases. The previous name remains

1181 available for backwards compatibility.

1182

1183 .. seealso::

1184

1185 :meth:`.ColumnOperators.like`

1186

1187 """

1188 return self.operate(not_like_op, other, escape=escape)

1189

1190 # deprecated 1.4; see #5435

1191 if TYPE_CHECKING:

1192

1193 def notlike(

1194 self, other: Any, escape: Optional[str] = None

1195 ) -> ColumnOperators: ...

1196

1197 else:

1198 notlike = not_like

1199

1200 def not_ilike(

1201 self, other: Any, escape: Optional[str] = None

1202 ) -> ColumnOperators:

1203 """implement the ``NOT ILIKE`` operator.

1204

1205 This is equivalent to using negation with

1206 :meth:`.ColumnOperators.ilike`, i.e. ``~x.ilike(y)``.

1207

1208 .. versionchanged:: 1.4 The ``not_ilike()`` operator is renamed from

1209 ``notilike()`` in previous releases. The previous name remains

1210 available for backwards compatibility.

1211

1212 .. seealso::

1213

1214 :meth:`.ColumnOperators.ilike`

1215

1216 """

1217 return self.operate(not_ilike_op, other, escape=escape)

1218

1219 # deprecated 1.4; see #5435

1220 if TYPE_CHECKING:

1221

1222 def notilike(

1223 self, other: Any, escape: Optional[str] = None

1224 ) -> ColumnOperators: ...

1225

1226 else:

1227 notilike = not_ilike

1228

1229 def is_(self, other: Any) -> ColumnOperators:

1230 """Implement the ``IS`` operator.

1231

1232 Normally, ``IS`` is generated automatically when comparing to a

1233 value of ``None``, which resolves to ``NULL``. However, explicit

1234 usage of ``IS`` may be desirable if comparing to boolean values

1235 on certain platforms.

1236

1237 .. seealso:: :meth:`.ColumnOperators.is_not`

1238

1239 """

1240 return self.operate(is_, other)

1241

1242 def is_not(self, other: Any) -> ColumnOperators:

1243 """Implement the ``IS NOT`` operator.

1244

1245 Normally, ``IS NOT`` is generated automatically when comparing to a

1246 value of ``None``, which resolves to ``NULL``. However, explicit

1247 usage of ``IS NOT`` may be desirable if comparing to boolean values

1248 on certain platforms.

1249

1250 .. versionchanged:: 1.4 The ``is_not()`` operator is renamed from

1251 ``isnot()`` in previous releases. The previous name remains

1252 available for backwards compatibility.

1253

1254 .. seealso:: :meth:`.ColumnOperators.is_`

1255

1256 """

1257 return self.operate(is_not, other)

1258

1259 # deprecated 1.4; see #5429

1260 if TYPE_CHECKING:

1261

1262 def isnot(self, other: Any) -> ColumnOperators: ...

1263

1264 else:

1265 isnot = is_not

1266

1267 def startswith(

1268 self,

1269 other: Any,

1270 escape: Optional[str] = None,

1271 autoescape: bool = False,

1272 ) -> ColumnOperators:

1273 r"""Implement the ``startswith`` operator.

1274

1275 Produces a LIKE expression that tests against a match for the start

1276 of a string value:

1277

1278 .. sourcecode:: sql

1279

1280 column LIKE <other> || '%'

1281

1282 E.g.::

1283

1284 stmt = select(sometable).where(sometable.c.column.startswith("foobar"))

1285

1286 Since the operator uses ``LIKE``, wildcard characters

1287 ``"%"`` and ``"_"`` that are present inside the <other> expression

1288 will behave like wildcards as well. For literal string

1289 values, the :paramref:`.ColumnOperators.startswith.autoescape` flag

1290 may be set to ``True`` to apply escaping to occurrences of these

1291 characters within the string value so that they match as themselves

1292 and not as wildcard characters. Alternatively, the

1293 :paramref:`.ColumnOperators.startswith.escape` parameter will establish

1294 a given character as an escape character which can be of use when

1295 the target expression is not a literal string.

1296

1297 :param other: expression to be compared. This is usually a plain

1298 string value, but can also be an arbitrary SQL expression. LIKE

1299 wildcard characters ``%`` and ``_`` are not escaped by default unless

1300 the :paramref:`.ColumnOperators.startswith.autoescape` flag is

1301 set to True.

1302

1303 :param autoescape: boolean; when True, establishes an escape character

1304 within the LIKE expression, then applies it to all occurrences of

1305 ``"%"``, ``"_"`` and the escape character itself within the

1306 comparison value, which is assumed to be a literal string and not a

1307 SQL expression.

1308

1309 An expression such as::

1310

1311 somecolumn.startswith("foo%bar", autoescape=True)

1312

1313 Will render as:

1314

1315 .. sourcecode:: sql

1316

1317 somecolumn LIKE :param || '%' ESCAPE '/'

1318

1319 With the value of ``:param`` as ``"foo/%bar"``.

1320

1321 :param escape: a character which when given will render with the

1322 ``ESCAPE`` keyword to establish that character as the escape

1323 character. This character can then be placed preceding occurrences

1324 of ``%`` and ``_`` to allow them to act as themselves and not

1325 wildcard characters.

1326

1327 An expression such as::

1328

1329 somecolumn.startswith("foo/%bar", escape="^")

1330

1331 Will render as:

1332

1333 .. sourcecode:: sql

1334

1335 somecolumn LIKE :param || '%' ESCAPE '^'

1336

1337 The parameter may also be combined with

1338 :paramref:`.ColumnOperators.startswith.autoescape`::

1339

1340 somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)

1341

1342 Where above, the given literal parameter will be converted to

1343 ``"foo^%bar^^bat"`` before being passed to the database.

1344

1345 .. seealso::

1346

1347 :meth:`.ColumnOperators.endswith`

1348

1349 :meth:`.ColumnOperators.contains`

1350

1351 :meth:`.ColumnOperators.like`

1352

1353 """ # noqa: E501

1354 return self.operate(

1355 startswith_op, other, escape=escape, autoescape=autoescape

1356 )

1357

1358 def istartswith(

1359 self,

1360 other: Any,

1361 escape: Optional[str] = None,

1362 autoescape: bool = False,

1363 ) -> ColumnOperators:

1364 r"""Implement the ``istartswith`` operator, e.g. case insensitive

1365 version of :meth:`.ColumnOperators.startswith`.

1366

1367 Produces a LIKE expression that tests against an insensitive

1368 match for the start of a string value:

1369

1370 .. sourcecode:: sql

1371

1372 lower(column) LIKE lower(<other>) || '%'

1373

1374 E.g.::

1375

1376 stmt = select(sometable).where(sometable.c.column.istartswith("foobar"))

1377

1378 Since the operator uses ``LIKE``, wildcard characters

1379 ``"%"`` and ``"_"`` that are present inside the <other> expression

1380 will behave like wildcards as well. For literal string

1381 values, the :paramref:`.ColumnOperators.istartswith.autoescape` flag

1382 may be set to ``True`` to apply escaping to occurrences of these

1383 characters within the string value so that they match as themselves

1384 and not as wildcard characters. Alternatively, the

1385 :paramref:`.ColumnOperators.istartswith.escape` parameter will

1386 establish a given character as an escape character which can be of

1387 use when the target expression is not a literal string.

1388

1389 :param other: expression to be compared. This is usually a plain

1390 string value, but can also be an arbitrary SQL expression. LIKE

1391 wildcard characters ``%`` and ``_`` are not escaped by default unless

1392 the :paramref:`.ColumnOperators.istartswith.autoescape` flag is

1393 set to True.

1394

1395 :param autoescape: boolean; when True, establishes an escape character

1396 within the LIKE expression, then applies it to all occurrences of

1397 ``"%"``, ``"_"`` and the escape character itself within the

1398 comparison value, which is assumed to be a literal string and not a

1399 SQL expression.

1400

1401 An expression such as::

1402

1403 somecolumn.istartswith("foo%bar", autoescape=True)

1404

1405 Will render as:

1406

1407 .. sourcecode:: sql

1408

1409 lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/'

1410

1411 With the value of ``:param`` as ``"foo/%bar"``.

1412

1413 :param escape: a character which when given will render with the

1414 ``ESCAPE`` keyword to establish that character as the escape

1415 character. This character can then be placed preceding occurrences

1416 of ``%`` and ``_`` to allow them to act as themselves and not

1417 wildcard characters.

1418

1419 An expression such as::

1420

1421 somecolumn.istartswith("foo/%bar", escape="^")

1422

1423 Will render as:

1424

1425 .. sourcecode:: sql

1426

1427 lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^'

1428

1429 The parameter may also be combined with

1430 :paramref:`.ColumnOperators.istartswith.autoescape`::

1431

1432 somecolumn.istartswith("foo%bar^bat", escape="^", autoescape=True)

1433

1434 Where above, the given literal parameter will be converted to

1435 ``"foo^%bar^^bat"`` before being passed to the database.

1436

1437 .. seealso::

1438

1439 :meth:`.ColumnOperators.startswith`

1440 """ # noqa: E501

1441 return self.operate(

1442 istartswith_op, other, escape=escape, autoescape=autoescape

1443 )

1444

1445 def endswith(

1446 self,

1447 other: Any,

1448 escape: Optional[str] = None,

1449 autoescape: bool = False,

1450 ) -> ColumnOperators:

1451 r"""Implement the 'endswith' operator.

1452

1453 Produces a LIKE expression that tests against a match for the end

1454 of a string value:

1455

1456 .. sourcecode:: sql

1457

1458 column LIKE '%' || <other>

1459

1460 E.g.::

1461

1462 stmt = select(sometable).where(sometable.c.column.endswith("foobar"))

1463

1464 Since the operator uses ``LIKE``, wildcard characters

1465 ``"%"`` and ``"_"`` that are present inside the <other> expression

1466 will behave like wildcards as well. For literal string

1467 values, the :paramref:`.ColumnOperators.endswith.autoescape` flag

1468 may be set to ``True`` to apply escaping to occurrences of these

1469 characters within the string value so that they match as themselves

1470 and not as wildcard characters. Alternatively, the

1471 :paramref:`.ColumnOperators.endswith.escape` parameter will establish

1472 a given character as an escape character which can be of use when

1473 the target expression is not a literal string.

1474

1475 :param other: expression to be compared. This is usually a plain

1476 string value, but can also be an arbitrary SQL expression. LIKE

1477 wildcard characters ``%`` and ``_`` are not escaped by default unless

1478 the :paramref:`.ColumnOperators.endswith.autoescape` flag is

1479 set to True.

1480

1481 :param autoescape: boolean; when True, establishes an escape character

1482 within the LIKE expression, then applies it to all occurrences of

1483 ``"%"``, ``"_"`` and the escape character itself within the

1484 comparison value, which is assumed to be a literal string and not a

1485 SQL expression.

1486

1487 An expression such as::

1488

1489 somecolumn.endswith("foo%bar", autoescape=True)

1490

1491 Will render as:

1492

1493 .. sourcecode:: sql

1494

1495 somecolumn LIKE '%' || :param ESCAPE '/'

1496

1497 With the value of ``:param`` as ``"foo/%bar"``.

1498

1499 :param escape: a character which when given will render with the

1500 ``ESCAPE`` keyword to establish that character as the escape

1501 character. This character can then be placed preceding occurrences

1502 of ``%`` and ``_`` to allow them to act as themselves and not

1503 wildcard characters.

1504

1505 An expression such as::

1506

1507 somecolumn.endswith("foo/%bar", escape="^")

1508

1509 Will render as:

1510

1511 .. sourcecode:: sql

1512

1513 somecolumn LIKE '%' || :param ESCAPE '^'

1514

1515 The parameter may also be combined with

1516 :paramref:`.ColumnOperators.endswith.autoescape`::

1517

1518 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)

1519

1520 Where above, the given literal parameter will be converted to

1521 ``"foo^%bar^^bat"`` before being passed to the database.

1522

1523 .. seealso::

1524

1525 :meth:`.ColumnOperators.startswith`

1526

1527 :meth:`.ColumnOperators.contains`

1528

1529 :meth:`.ColumnOperators.like`

1530

1531 """ # noqa: E501

1532 return self.operate(

1533 endswith_op, other, escape=escape, autoescape=autoescape

1534 )

1535

1536 def iendswith(

1537 self,

1538 other: Any,

1539 escape: Optional[str] = None,

1540 autoescape: bool = False,

1541 ) -> ColumnOperators:

1542 r"""Implement the ``iendswith`` operator, e.g. case insensitive

1543 version of :meth:`.ColumnOperators.endswith`.

1544

1545 Produces a LIKE expression that tests against an insensitive match

1546 for the end of a string value:

1547

1548 .. sourcecode:: sql

1549

1550 lower(column) LIKE '%' || lower(<other>)

1551

1552 E.g.::

1553

1554 stmt = select(sometable).where(sometable.c.column.iendswith("foobar"))

1555

1556 Since the operator uses ``LIKE``, wildcard characters

1557 ``"%"`` and ``"_"`` that are present inside the <other> expression

1558 will behave like wildcards as well. For literal string

1559 values, the :paramref:`.ColumnOperators.iendswith.autoescape` flag

1560 may be set to ``True`` to apply escaping to occurrences of these

1561 characters within the string value so that they match as themselves

1562 and not as wildcard characters. Alternatively, the

1563 :paramref:`.ColumnOperators.iendswith.escape` parameter will establish

1564 a given character as an escape character which can be of use when

1565 the target expression is not a literal string.

1566

1567 :param other: expression to be compared. This is usually a plain

1568 string value, but can also be an arbitrary SQL expression. LIKE

1569 wildcard characters ``%`` and ``_`` are not escaped by default unless

1570 the :paramref:`.ColumnOperators.iendswith.autoescape` flag is

1571 set to True.

1572

1573 :param autoescape: boolean; when True, establishes an escape character

1574 within the LIKE expression, then applies it to all occurrences of

1575 ``"%"``, ``"_"`` and the escape character itself within the

1576 comparison value, which is assumed to be a literal string and not a

1577 SQL expression.

1578

1579 An expression such as::

1580

1581 somecolumn.iendswith("foo%bar", autoescape=True)

1582

1583 Will render as:

1584

1585 .. sourcecode:: sql

1586

1587 lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '/'

1588

1589 With the value of ``:param`` as ``"foo/%bar"``.

1590

1591 :param escape: a character which when given will render with the

1592 ``ESCAPE`` keyword to establish that character as the escape

1593 character. This character can then be placed preceding occurrences

1594 of ``%`` and ``_`` to allow them to act as themselves and not

1595 wildcard characters.

1596

1597 An expression such as::

1598

1599 somecolumn.iendswith("foo/%bar", escape="^")

1600

1601 Will render as:

1602

1603 .. sourcecode:: sql

1604

1605 lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^'

1606

1607 The parameter may also be combined with

1608 :paramref:`.ColumnOperators.iendswith.autoescape`::

1609

1610 somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)

1611

1612 Where above, the given literal parameter will be converted to

1613 ``"foo^%bar^^bat"`` before being passed to the database.

1614

1615 .. seealso::

1616

1617 :meth:`.ColumnOperators.endswith`

1618 """ # noqa: E501

1619 return self.operate(

1620 iendswith_op, other, escape=escape, autoescape=autoescape

1621 )

1622

1623 def contains(self, other: Any, **kw: Any) -> ColumnOperators:

1624 r"""Implement the 'contains' operator.

1625

1626 Produces a LIKE expression that tests against a match for the middle

1627 of a string value:

1628

1629 .. sourcecode:: sql

1630

1631 column LIKE '%' || <other> || '%'

1632

1633 E.g.::

1634

1635 stmt = select(sometable).where(sometable.c.column.contains("foobar"))

1636

1637 Since the operator uses ``LIKE``, wildcard characters

1638 ``"%"`` and ``"_"`` that are present inside the <other> expression

1639 will behave like wildcards as well. For literal string

1640 values, the :paramref:`.ColumnOperators.contains.autoescape` flag

1641 may be set to ``True`` to apply escaping to occurrences of these

1642 characters within the string value so that they match as themselves

1643 and not as wildcard characters. Alternatively, the

1644 :paramref:`.ColumnOperators.contains.escape` parameter will establish

1645 a given character as an escape character which can be of use when

1646 the target expression is not a literal string.

1647

1648 :param other: expression to be compared. This is usually a plain

1649 string value, but can also be an arbitrary SQL expression. LIKE

1650 wildcard characters ``%`` and ``_`` are not escaped by default unless

1651 the :paramref:`.ColumnOperators.contains.autoescape` flag is

1652 set to True.

1653

1654 :param autoescape: boolean; when True, establishes an escape character

1655 within the LIKE expression, then applies it to all occurrences of

1656 ``"%"``, ``"_"`` and the escape character itself within the

1657 comparison value, which is assumed to be a literal string and not a

1658 SQL expression.

1659

1660 An expression such as::

1661

1662 somecolumn.contains("foo%bar", autoescape=True)

1663

1664 Will render as:

1665

1666 .. sourcecode:: sql

1667

1668 somecolumn LIKE '%' || :param || '%' ESCAPE '/'

1669

1670 With the value of ``:param`` as ``"foo/%bar"``.

1671

1672 :param escape: a character which when given will render with the

1673 ``ESCAPE`` keyword to establish that character as the escape

1674 character. This character can then be placed preceding occurrences

1675 of ``%`` and ``_`` to allow them to act as themselves and not

1676 wildcard characters.

1677

1678 An expression such as::

1679

1680 somecolumn.contains("foo/%bar", escape="^")

1681

1682 Will render as:

1683

1684 .. sourcecode:: sql

1685

1686 somecolumn LIKE '%' || :param || '%' ESCAPE '^'

1687

1688 The parameter may also be combined with

1689 :paramref:`.ColumnOperators.contains.autoescape`::

1690

1691 somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)

1692

1693 Where above, the given literal parameter will be converted to

1694 ``"foo^%bar^^bat"`` before being passed to the database.

1695

1696 .. seealso::

1697

1698 :meth:`.ColumnOperators.startswith`

1699

1700 :meth:`.ColumnOperators.endswith`

1701

1702 :meth:`.ColumnOperators.like`

1703

1704

1705 """ # noqa: E501

1706 return self.operate(contains_op, other, **kw)

1707

1708 def icontains(self, other: Any, **kw: Any) -> ColumnOperators:

1709 r"""Implement the ``icontains`` operator, e.g. case insensitive

1710 version of :meth:`.ColumnOperators.contains`.

1711

1712 Produces a LIKE expression that tests against an insensitive match

1713 for the middle of a string value:

1714

1715 .. sourcecode:: sql

1716

1717 lower(column) LIKE '%' || lower(<other>) || '%'

1718

1719 E.g.::

1720

1721 stmt = select(sometable).where(sometable.c.column.icontains("foobar"))

1722

1723 Since the operator uses ``LIKE``, wildcard characters

1724 ``"%"`` and ``"_"`` that are present inside the <other> expression

1725 will behave like wildcards as well. For literal string

1726 values, the :paramref:`.ColumnOperators.icontains.autoescape` flag

1727 may be set to ``True`` to apply escaping to occurrences of these

1728 characters within the string value so that they match as themselves

1729 and not as wildcard characters. Alternatively, the

1730 :paramref:`.ColumnOperators.icontains.escape` parameter will establish

1731 a given character as an escape character which can be of use when

1732 the target expression is not a literal string.

1733

1734 :param other: expression to be compared. This is usually a plain

1735 string value, but can also be an arbitrary SQL expression. LIKE

1736 wildcard characters ``%`` and ``_`` are not escaped by default unless

1737 the :paramref:`.ColumnOperators.icontains.autoescape` flag is

1738 set to True.

1739

1740 :param autoescape: boolean; when True, establishes an escape character

1741 within the LIKE expression, then applies it to all occurrences of

1742 ``"%"``, ``"_"`` and the escape character itself within the

1743 comparison value, which is assumed to be a literal string and not a

1744 SQL expression.

1745

1746 An expression such as::

1747

1748 somecolumn.icontains("foo%bar", autoescape=True)

1749

1750 Will render as:

1751

1752 .. sourcecode:: sql

1753

1754 lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '/'

1755

1756 With the value of ``:param`` as ``"foo/%bar"``.

1757

1758 :param escape: a character which when given will render with the

1759 ``ESCAPE`` keyword to establish that character as the escape

1760 character. This character can then be placed preceding occurrences

1761 of ``%`` and ``_`` to allow them to act as themselves and not

1762 wildcard characters.

1763

1764 An expression such as::

1765

1766 somecolumn.icontains("foo/%bar", escape="^")

1767

1768 Will render as:

1769

1770 .. sourcecode:: sql

1771

1772 lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^'

1773

1774 The parameter may also be combined with

1775 :paramref:`.ColumnOperators.contains.autoescape`::

1776

1777 somecolumn.icontains("foo%bar^bat", escape="^", autoescape=True)

1778

1779 Where above, the given literal parameter will be converted to

1780 ``"foo^%bar^^bat"`` before being passed to the database.

1781

1782 .. seealso::

1783

1784 :meth:`.ColumnOperators.contains`

1785

1786 """ # noqa: E501

1787 return self.operate(icontains_op, other, **kw)

1788

1789 def match(self, other: Any, **kwargs: Any) -> ColumnOperators:

1790 """Implements a database-specific 'match' operator.

1791

1792 :meth:`_sql.ColumnOperators.match` attempts to resolve to

1793 a MATCH-like function or operator provided by the backend.

1794 Examples include:

1795

1796 * PostgreSQL - renders ``x @@ plainto_tsquery(y)``

1797

1798 .. versionchanged:: 2.0 ``plainto_tsquery()`` is used instead

1799 of ``to_tsquery()`` for PostgreSQL now; for compatibility with

1800 other forms, see :ref:`postgresql_match`.

1801

1802

1803 * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)``

1804

1805 .. seealso::

1806

1807 :class:`_mysql.match` - MySQL specific construct with

1808 additional features.

1809

1810 * Oracle Database - renders ``CONTAINS(x, y)``

1811 * other backends may provide special implementations.

1812 * Backends without any special implementation will emit

1813 the operator as "MATCH". This is compatible with SQLite, for

1814 example.

1815

1816 """

1817 return self.operate(match_op, other, **kwargs)

1818

1819 def regexp_match(

1820 self, pattern: Any, flags: Optional[str] = None

1821 ) -> ColumnOperators:

1822 """Implements a database-specific 'regexp match' operator.

1823

1824 E.g.::

1825

1826 stmt = select(table.c.some_column).where(

1827 table.c.some_column.regexp_match("^(b|c)")

1828 )

1829

1830 :meth:`_sql.ColumnOperators.regexp_match` attempts to resolve to

1831 a REGEXP-like function or operator provided by the backend, however

1832 the specific regular expression syntax and flags available are

1833 **not backend agnostic**.

1834

1835 Examples include:

1836

1837 * PostgreSQL - renders ``x ~ y`` or ``x !~ y`` when negated.

1838 * Oracle Database - renders ``REGEXP_LIKE(x, y)``

1839 * SQLite - uses SQLite's ``REGEXP`` placeholder operator and calls into

1840 the Python ``re.match()`` builtin.

1841 * other backends may provide special implementations.

1842 * Backends without any special implementation will emit

1843 the operator as "REGEXP" or "NOT REGEXP". This is compatible with

1844 SQLite and MySQL, for example.

1845

1846 Regular expression support is currently implemented for Oracle

1847 Database, PostgreSQL, MySQL and MariaDB. Partial support is available

1848 for SQLite. Support among third-party dialects may vary.

1849

1850 :param pattern: The regular expression pattern string or column

1851 clause.

1852 :param flags: Any regular expression string flags to apply, passed as

1853 plain Python string only. These flags are backend specific.

1854 Some backends, like PostgreSQL and MariaDB, may alternatively

1855 specify the flags as part of the pattern.

1856 When using the ignore case flag 'i' in PostgreSQL, the ignore case

1857 regexp match operator ``~*`` or ``!~*`` will be used.

1858

1859 .. versionadded:: 1.4

1860

1861 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation

1862 error, the "flags" parameter previously accepted SQL expression

1863 objects such as column expressions in addition to plain Python

1864 strings. This implementation did not work correctly with caching

1865 and was removed; strings only should be passed for the "flags"

1866 parameter, as these flags are rendered as literal inline values

1867 within SQL expressions.

1868

1869 .. seealso::

1870

1871 :meth:`_sql.ColumnOperators.regexp_replace`

1872

1873

1874 """

1875 return self.operate(regexp_match_op, pattern, flags=flags)

1876

1877 def regexp_replace(

1878 self, pattern: Any, replacement: Any, flags: Optional[str] = None

1879 ) -> ColumnOperators:

1880 """Implements a database-specific 'regexp replace' operator.

1881

1882 E.g.::

1883

1884 stmt = select(

1885 table.c.some_column.regexp_replace("b(..)", "X\1Y", flags="g")

1886 )

1887

1888 :meth:`_sql.ColumnOperators.regexp_replace` attempts to resolve to

1889 a REGEXP_REPLACE-like function provided by the backend, that

1890 usually emit the function ``REGEXP_REPLACE()``. However,

1891 the specific regular expression syntax and flags available are

1892 **not backend agnostic**.

1893

1894 Regular expression replacement support is currently implemented for

1895 Oracle Database, PostgreSQL, MySQL 8 or greater and MariaDB. Support

1896 among third-party dialects may vary.

1897

1898 :param pattern: The regular expression pattern string or column

1899 clause.

1900 :param pattern: The replacement string or column clause.

1901 :param flags: Any regular expression string flags to apply, passed as

1902 plain Python string only. These flags are backend specific.

1903 Some backends, like PostgreSQL and MariaDB, may alternatively

1904 specify the flags as part of the pattern.

1905

1906 .. versionadded:: 1.4

1907

1908 .. versionchanged:: 1.4.48, 2.0.18 Note that due to an implementation

1909 error, the "flags" parameter previously accepted SQL expression

1910 objects such as column expressions in addition to plain Python

1911 strings. This implementation did not work correctly with caching

1912 and was removed; strings only should be passed for the "flags"

1913 parameter, as these flags are rendered as literal inline values

1914 within SQL expressions.

1915

1916

1917 .. seealso::

1918

1919 :meth:`_sql.ColumnOperators.regexp_match`

1920

1921 """

1922 return self.operate(

1923 regexp_replace_op,

1924 pattern,

1925 replacement=replacement,

1926 flags=flags,

1927 )

1928

1929 def collate(

1930 self, collation: str, collation_schema: Optional[str] = None

1931 ) -> ColumnOperators:

1932 """Produce a :func:`_expression.collate` clause against

1933 the parent object, given the collation string.

1934

1935 :param collation: the name of the collation.

1936

1937 :param collation_schema: optional, the name of the schema in which

1938 the collation is defined, for use with database backends that

1939 support schema-qualified collations, currently PostgreSQL.

1940

1941 .. versionadded:: 2.1

1942

1943 .. seealso::

1944

1945 :func:`_expression.collate`

1946

1947 """

1948 return self.operate(

1949 collate, collation, collation_schema=collation_schema

1950 )

1951

1952 def __radd__(self, other: Any) -> ColumnOperators:

1953 """Implement the ``+`` operator in reverse.

1954

1955 See :meth:`.ColumnOperators.__add__`.

1956

1957 """

1958 return self.reverse_operate(add, other)

1959

1960 def __rsub__(self, other: Any) -> ColumnOperators:

1961 """Implement the ``-`` operator in reverse.

1962

1963 See :meth:`.ColumnOperators.__sub__`.

1964

1965 """

1966 return self.reverse_operate(sub, other)

1967

1968 def __rmul__(self, other: Any) -> ColumnOperators:

1969 """Implement the ``*`` operator in reverse.

1970

1971 See :meth:`.ColumnOperators.__mul__`.

1972

1973 """

1974 return self.reverse_operate(mul, other)

1975

1976 def __rmod__(self, other: Any) -> ColumnOperators:

1977 """Implement the ``%`` operator in reverse.

1978

1979 See :meth:`.ColumnOperators.__mod__`.

1980

1981 """

1982 return self.reverse_operate(mod, other)

1983

1984 def between(

1985 self, cleft: Any, cright: Any, symmetric: bool = False

1986 ) -> ColumnOperators:

1987 """Produce a :func:`_expression.between` clause against

1988 the parent object, given the lower and upper range.

1989

1990 """

1991 return self.operate(between_op, cleft, cright, symmetric=symmetric)

1992

1993 def distinct(self) -> ColumnOperators:

1994 """Produce a :func:`_expression.distinct` clause against the

1995 parent object.

1996

1997 """

1998 return self.operate(distinct_op)

1999

2000 def any_(self) -> ColumnOperators:

2001 """Produce an :func:`_expression.any_` clause against the

2002 parent object.

2003

2004 See the documentation for :func:`_sql.any_` for examples.

2005

2006 .. note:: be sure to not confuse the newer

2007 :meth:`_sql.ColumnOperators.any_` method with the **legacy**

2008 version of this method, the :meth:`_types.ARRAY.Comparator.any`

2009 method that's specific to :class:`_types.ARRAY`, which uses a

2010 different calling style.

2011

2012 """

2013 return self.operate(any_op)

2014

2015 def all_(self) -> ColumnOperators:

2016 """Produce an :func:`_expression.all_` clause against the

2017 parent object.

2018

2019 See the documentation for :func:`_sql.all_` for examples.

2020

2021 .. note:: be sure to not confuse the newer

2022 :meth:`_sql.ColumnOperators.all_` method with the **legacy**

2023 version of this method, the :meth:`_types.ARRAY.Comparator.all`

2024 method that's specific to :class:`_types.ARRAY`, which uses a

2025 different calling style.

2026

2027 """

2028 return self.operate(all_op)

2029

2030 def __add__(self, other: Any) -> ColumnOperators:

2031 """Implement the ``+`` operator.

2032

2033 In a column context, produces the clause ``a + b``

2034 if the parent object has non-string affinity.

2035 If the parent object has a string affinity,

2036 produces the concatenation operator, ``a || b`` -

2037 see :meth:`.ColumnOperators.concat`.

2038

2039 """

2040 return self.operate(add, other)

2041

2042 def __sub__(self, other: Any) -> ColumnOperators:

2043 """Implement the ``-`` operator.

2044

2045 In a column context, produces the clause ``a - b``.

2046

2047 """

2048 return self.operate(sub, other)

2049

2050 def __mul__(self, other: Any) -> ColumnOperators:

2051 """Implement the ``*`` operator.

2052

2053 In a column context, produces the clause ``a * b``.

2054

2055 """

2056 return self.operate(mul, other)

2057

2058 def __mod__(self, other: Any) -> ColumnOperators:

2059 """Implement the ``%`` operator.

2060

2061 In a column context, produces the clause ``a % b``.

2062

2063 """

2064 return self.operate(mod, other)

2065

2066 def __truediv__(self, other: Any) -> ColumnOperators:

2067 """Implement the ``/`` operator.

2068

2069 In a column context, produces the clause ``a / b``, and

2070 considers the result type to be numeric.

2071

2072 .. versionchanged:: 2.0 The truediv operator against two integers

2073 is now considered to return a numeric value. Behavior on specific

2074 backends may vary.

2075

2076 """

2077 return self.operate(truediv, other)

2078

2079 def __rtruediv__(self, other: Any) -> ColumnOperators:

2080 """Implement the ``/`` operator in reverse.

2081

2082 See :meth:`.ColumnOperators.__truediv__`.

2083

2084 """

2085 return self.reverse_operate(truediv, other)

2086

2087 def __floordiv__(self, other: Any) -> ColumnOperators:

2088 """Implement the ``//`` operator.

2089

2090 In a column context, produces the clause ``a / b``,

2091 which is the same as "truediv", but considers the result

2092 type to be integer.

2093

2094 .. versionadded:: 2.0

2095

2096 """

2097 return self.operate(floordiv, other)

2098

2099 def __rfloordiv__(self, other: Any) -> ColumnOperators:

2100 """Implement the ``//`` operator in reverse.

2101

2102 See :meth:`.ColumnOperators.__floordiv__`.

2103

2104 """

2105 return self.reverse_operate(floordiv, other)

2106

2107 def __pow__(self, other: Any) -> ColumnOperators:

2108 """Implement the ``**`` operator.

2109

2110 In a column context, produces the clause ``pow(a, b)``, or a similar

2111 dialect-specific expression.

2112

2113 .. versionadded:: 2.1

2114

2115 """

2116 return self.operate(pow_, other)

2117

2118 def __rpow__(self, other: Any) -> ColumnOperators:

2119 """Implement the ``**`` operator in reverse.

2120

2121 .. seealso::

2122

2123 :meth:`.ColumnOperators.__pow__`.

2124

2125 .. versionadded:: 2.1

2126

2127 """

2128 return self.reverse_operate(pow_, other)

2129

2130

2131_commutative: Set[Any] = {eq, ne, add, mul}

2132_comparison: Set[Any] = {eq, ne, lt, gt, ge, le}

2133

2134

2135def _operator_fn(fn: Callable[..., Any]) -> OperatorType:

2136 return cast(OperatorType, fn)

2137

2138

2139def commutative_op(fn: _FN) -> _FN:

2140 _commutative.add(fn)

2141 return fn

2142

2143

2144def comparison_op(fn: _FN) -> _FN:

2145 _comparison.add(fn)

2146 return fn

2147

2148

2149@_operator_fn

2150def from_() -> Any:

2151 raise NotImplementedError()

2152

2153

2154@_operator_fn

2155@comparison_op

2156def function_as_comparison_op() -> Any:

2157 raise NotImplementedError()

2158

2159

2160@_operator_fn

2161def as_() -> Any:

2162 raise NotImplementedError()

2163

2164

2165@_operator_fn

2166def exists() -> Any:

2167 raise NotImplementedError()

2168

2169

2170@_operator_fn

2171def is_true(a: Any) -> Any:

2172 raise NotImplementedError()

2173

2174

2175# 1.4 deprecated; see #5435

2176if TYPE_CHECKING:

2177

2178 @_operator_fn

2179 def istrue(a: Any) -> Any: ...

2180

2181else:

2182 istrue = is_true

2183

2184

2185@_operator_fn

2186def is_false(a: Any) -> Any:

2187 raise NotImplementedError()

2188

2189

2190# 1.4 deprecated; see #5435

2191if TYPE_CHECKING:

2192

2193 @_operator_fn

2194 def isfalse(a: Any) -> Any: ...

2195

2196else:

2197 isfalse = is_false

2198

2199

2200@comparison_op

2201@_operator_fn

2202def is_distinct_from(a: Any, b: Any) -> Any:

2203 return a.is_distinct_from(b)

2204

2205

2206@comparison_op

2207@_operator_fn

2208def is_not_distinct_from(a: Any, b: Any) -> Any:

2209 return a.is_not_distinct_from(b)

2210

2211

2212# deprecated 1.4; see #5435

2213if TYPE_CHECKING:

2214

2215 @_operator_fn

2216 def isnot_distinct_from(a: Any, b: Any) -> Any: ...

2217

2218else:

2219 isnot_distinct_from = is_not_distinct_from

2220

2221

2222@comparison_op

2223@_operator_fn

2224def is_(a: Any, b: Any) -> Any:

2225 return a.is_(b)

2226

2227

2228@comparison_op

2229@_operator_fn

2230def is_not(a: Any, b: Any) -> Any:

2231 return a.is_not(b)

2232

2233

2234# 1.4 deprecated; see #5429

2235if TYPE_CHECKING:

2236

2237 @_operator_fn

2238 def isnot(a: Any, b: Any) -> Any: ...

2239

2240else:

2241 isnot = is_not

2242

2243

2244@_operator_fn

2245def collate(a: Any, b: Any, collation_schema: Optional[str] = None) -> Any:

2246 return a.collate(b, collation_schema=collation_schema)

2247

2248

2249@_operator_fn

2250def op(a: Any, opstring: str, b: Any) -> Any:

2251 return a.op(opstring)(b)

2252

2253

2254@comparison_op

2255@_operator_fn

2256def like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:

2257 return a.like(b, escape=escape)

2258

2259

2260@comparison_op

2261@_operator_fn

2262def not_like_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:

2263 return a.notlike(b, escape=escape)

2264

2265

2266# 1.4 deprecated; see #5435

2267if TYPE_CHECKING:

2268

2269 @_operator_fn

2270 def notlike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ...

2271

2272else:

2273 notlike_op = not_like_op

2274

2275

2276@comparison_op

2277@_operator_fn

2278def ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:

2279 return a.ilike(b, escape=escape)

2280

2281

2282@comparison_op

2283@_operator_fn

2284def not_ilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any:

2285 return a.not_ilike(b, escape=escape)

2286

2287

2288# 1.4 deprecated; see #5435

2289if TYPE_CHECKING:

2290

2291 @_operator_fn

2292 def notilike_op(a: Any, b: Any, escape: Optional[str] = None) -> Any: ...

2293

2294else:

2295 notilike_op = not_ilike_op

2296

2297

2298@comparison_op

2299@_operator_fn

2300def between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any:

2301 return a.between(b, c, symmetric=symmetric)

2302

2303

2304@comparison_op

2305@_operator_fn

2306def not_between_op(a: Any, b: Any, c: Any, symmetric: bool = False) -> Any:

2307 return ~a.between(b, c, symmetric=symmetric)

2308

2309

2310# 1.4 deprecated; see #5435

2311if TYPE_CHECKING:

2312

2313 @_operator_fn

2314 def notbetween_op(

2315 a: Any, b: Any, c: Any, symmetric: bool = False

2316 ) -> Any: ...

2317

2318else:

2319 notbetween_op = not_between_op

2320

2321

2322@comparison_op

2323@_operator_fn

2324def in_op(a: Any, b: Any) -> Any:

2325 return a.in_(b)

2326

2327

2328@comparison_op

2329@_operator_fn

2330def not_in_op(a: Any, b: Any) -> Any:

2331 return a.not_in(b)

2332

2333

2334# 1.4 deprecated; see #5429

2335if TYPE_CHECKING:

2336

2337 @_operator_fn

2338 def notin_op(a: Any, b: Any) -> Any: ...

2339

2340else:

2341 notin_op = not_in_op

2342

2343

2344@_operator_fn

2345def distinct_op(a: Any) -> Any:

2346 return a.distinct()

2347

2348

2349@_operator_fn

2350def any_op(a: Any) -> Any:

2351 return a.any_()

2352

2353

2354@_operator_fn

2355def all_op(a: Any) -> Any:

2356 return a.all_()

2357

2358

2359def _escaped_like_impl(

2360 fn: Callable[..., Any], other: Any, escape: Optional[str], autoescape: bool

2361) -> Any:

2362 if autoescape:

2363 if autoescape is not True:

2364 util.warn(

2365 "The autoescape parameter is now a simple boolean True/False"

2366 )

2367 if escape is None:

2368 escape = "/"

2369

2370 if not isinstance(other, str):

2371 raise TypeError("String value expected when autoescape=True")

2372

2373 if escape not in ("%", "_"):

2374 other = other.replace(escape, escape + escape)

2375

2376 other = other.replace("%", escape + "%").replace("_", escape + "_")

2377

2378 return fn(other, escape=escape)

2379

2380

2381@comparison_op

2382@_operator_fn

2383def startswith_op(

2384 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2385) -> Any:

2386 return _escaped_like_impl(a.startswith, b, escape, autoescape)

2387

2388

2389@comparison_op

2390@_operator_fn

2391def not_startswith_op(

2392 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2393) -> Any:

2394 return ~_escaped_like_impl(a.startswith, b, escape, autoescape)

2395

2396

2397# 1.4 deprecated; see #5435

2398if TYPE_CHECKING:

2399

2400 @_operator_fn

2401 def notstartswith_op(

2402 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2403 ) -> Any: ...

2404

2405else:

2406 notstartswith_op = not_startswith_op

2407

2408

2409@comparison_op

2410@_operator_fn

2411def istartswith_op(

2412 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2413) -> Any:

2414 return _escaped_like_impl(a.istartswith, b, escape, autoescape)

2415

2416

2417@comparison_op

2418@_operator_fn

2419def not_istartswith_op(

2420 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2421) -> Any:

2422 return ~_escaped_like_impl(a.istartswith, b, escape, autoescape)

2423

2424

2425@comparison_op

2426@_operator_fn

2427def endswith_op(

2428 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2429) -> Any:

2430 return _escaped_like_impl(a.endswith, b, escape, autoescape)

2431

2432

2433@comparison_op

2434@_operator_fn

2435def not_endswith_op(

2436 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2437) -> Any:

2438 return ~_escaped_like_impl(a.endswith, b, escape, autoescape)

2439

2440

2441# 1.4 deprecated; see #5435

2442if TYPE_CHECKING:

2443

2444 @_operator_fn

2445 def notendswith_op(

2446 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2447 ) -> Any: ...

2448

2449else:

2450 notendswith_op = not_endswith_op

2451

2452

2453@comparison_op

2454@_operator_fn

2455def iendswith_op(

2456 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2457) -> Any:

2458 return _escaped_like_impl(a.iendswith, b, escape, autoescape)

2459

2460

2461@comparison_op

2462@_operator_fn

2463def not_iendswith_op(

2464 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2465) -> Any:

2466 return ~_escaped_like_impl(a.iendswith, b, escape, autoescape)

2467

2468

2469@comparison_op

2470@_operator_fn

2471def contains_op(

2472 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2473) -> Any:

2474 return _escaped_like_impl(a.contains, b, escape, autoescape)

2475

2476

2477@comparison_op

2478@_operator_fn

2479def not_contains_op(

2480 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2481) -> Any:

2482 return ~_escaped_like_impl(a.contains, b, escape, autoescape)

2483

2484

2485# 1.4 deprecated; see #5435

2486if TYPE_CHECKING:

2487

2488 @_operator_fn

2489 def notcontains_op(

2490 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2491 ) -> Any: ...

2492

2493else:

2494 notcontains_op = not_contains_op

2495

2496

2497@comparison_op

2498@_operator_fn

2499def icontains_op(

2500 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2501) -> Any:

2502 return _escaped_like_impl(a.icontains, b, escape, autoescape)

2503

2504

2505@comparison_op

2506@_operator_fn

2507def not_icontains_op(

2508 a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False

2509) -> Any:

2510 return ~_escaped_like_impl(a.icontains, b, escape, autoescape)

2511

2512

2513@comparison_op

2514@_operator_fn

2515def match_op(a: Any, b: Any, **kw: Any) -> Any:

2516 return a.match(b, **kw)

2517

2518

2519@comparison_op

2520@_operator_fn

2521def regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any:

2522 return a.regexp_match(b, flags=flags)

2523

2524

2525@comparison_op

2526@_operator_fn

2527def not_regexp_match_op(a: Any, b: Any, flags: Optional[str] = None) -> Any:

2528 return ~a.regexp_match(b, flags=flags)

2529

2530

2531@_operator_fn

2532def regexp_replace_op(

2533 a: Any, b: Any, replacement: Any, flags: Optional[str] = None

2534) -> Any:

2535 return a.regexp_replace(b, replacement=replacement, flags=flags)

2536

2537

2538@comparison_op

2539@_operator_fn

2540def not_match_op(a: Any, b: Any, **kw: Any) -> Any:

2541 return ~a.match(b, **kw)

2542

2543

2544# 1.4 deprecated; see #5429

2545if TYPE_CHECKING:

2546

2547 @_operator_fn

2548 def notmatch_op(a: Any, b: Any, **kw: Any) -> Any: ...

2549

2550else:

2551 notmatch_op = not_match_op

2552

2553

2554@_operator_fn

2555def comma_op(a: Any, b: Any) -> Any:

2556 raise NotImplementedError()

2557

2558

2559@_operator_fn

2560def filter_op(a: Any, b: Any) -> Any:

2561 raise NotImplementedError()

2562

2563

2564@_operator_fn

2565def concat_op(a: Any, b: Any) -> Any:

2566 try:

2567 concat = a.concat

2568 except AttributeError:

2569 return b._rconcat(a)

2570 else:

2571 return concat(b)

2572

2573

2574@_operator_fn

2575def desc_op(a: Any) -> Any:

2576 return a.desc()

2577

2578

2579@_operator_fn

2580def asc_op(a: Any) -> Any:

2581 return a.asc()

2582

2583

2584@_operator_fn

2585def nulls_first_op(a: Any) -> Any:

2586 return a.nulls_first()

2587

2588

2589# 1.4 deprecated; see #5435

2590if TYPE_CHECKING:

2591

2592 @_operator_fn

2593 def nullsfirst_op(a: Any) -> Any: ...

2594

2595else:

2596 nullsfirst_op = nulls_first_op

2597

2598

2599@_operator_fn

2600def nulls_last_op(a: Any) -> Any:

2601 return a.nulls_last()

2602

2603

2604# 1.4 deprecated; see #5435

2605if TYPE_CHECKING:

2606

2607 @_operator_fn

2608 def nullslast_op(a: Any) -> Any: ...

2609

2610else:

2611 nullslast_op = nulls_last_op

2612

2613

2614@_operator_fn

2615def json_getitem_op(a: Any, b: Any) -> Any:

2616 raise NotImplementedError()

2617

2618

2619@_operator_fn

2620def json_path_getitem_op(a: Any, b: Any) -> Any:

2621 raise NotImplementedError()

2622

2623

2624@_operator_fn

2625def bitwise_xor_op(a: Any, b: Any) -> Any:

2626 return a.bitwise_xor(b)

2627

2628

2629@_operator_fn

2630def bitwise_or_op(a: Any, b: Any) -> Any:

2631 return a.bitwise_or(b)

2632

2633

2634@_operator_fn

2635def bitwise_and_op(a: Any, b: Any) -> Any:

2636 return a.bitwise_and(b)

2637

2638

2639@_operator_fn

2640def bitwise_not_op(a: Any) -> Any:

2641 return a.bitwise_not()

2642

2643

2644@_operator_fn

2645def bitwise_lshift_op(a: Any, b: Any) -> Any:

2646 return a.bitwise_lshift(b)

2647

2648

2649@_operator_fn

2650def bitwise_rshift_op(a: Any, b: Any) -> Any:

2651 return a.bitwise_rshift(b)

2652

2653

2654@_operator_fn

2655def null_op(a: Any, b: Any) -> Any:

2656 """a 'null' operator that provides a boolean operation.

2657

2658 Does not compile in a SQL context, used for testing operators only.

2659

2660 .. versionadded:: 2.1

2661

2662 """

2663 return a._null_operate(b)

2664

2665

2666def is_comparison(op: OperatorType) -> bool:

2667 return op in _comparison or isinstance(op, custom_op) and op.is_comparison

2668

2669

2670def is_commutative(op: OperatorType) -> bool:

2671 return op in _commutative

2672

2673

2674def is_ordering_modifier(op: OperatorType) -> bool:

2675 return op in (asc_op, desc_op, nulls_first_op, nulls_last_op)

2676

2677

2678def is_natural_self_precedent(op: OperatorType) -> bool:

2679 return (

2680 op in _natural_self_precedent

2681 or isinstance(op, custom_op)

2682 and op.natural_self_precedent

2683 )

2684

2685

2686_booleans = (inv, is_true, is_false, and_, or_)

2687

2688

2689def is_boolean(op: OperatorType) -> bool:

2690 return is_comparison(op) or op in _booleans

2691

2692

2693_mirror = {gt: lt, ge: le, lt: gt, le: ge}

2694

2695

2696def mirror(op: OperatorType) -> OperatorType:

2697 """rotate a comparison operator 180 degrees.

2698

2699 Note this is not the same as negation.

2700

2701 """

2702 return _mirror.get(op, op)

2703

2704

2705_associative = _commutative.union([concat_op, and_, or_]).difference([eq, ne])

2706

2707

2708def is_associative(op: OperatorType) -> bool:

2709 return op in _associative

2710

2711

2712def is_order_by_modifier(op: Optional[OperatorType]) -> bool:

2713 return op in _order_by_modifier

2714

2715

2716_order_by_modifier = {desc_op, asc_op, nulls_first_op, nulls_last_op}

2717

2718_natural_self_precedent = _associative.union(

2719 [getitem, json_getitem_op, json_path_getitem_op]

2720)

2721"""Operators where if we have (a op b) op c, we don't want to

2722parenthesize (a op b).

2723

2724"""

2725

2726

2727@_operator_fn

2728def _asbool(a: Any) -> Any:

2729 raise NotImplementedError()

2730

2731

2732class _OpLimit(IntEnum):

2733 _smallest = -100

2734 _largest = 100

2735

2736

2737_PRECEDENCE: Dict[OperatorType, int] = {

2738 from_: 15,

2739 function_as_comparison_op: 15,

2740 any_op: 15,

2741 all_op: 15,

2742 getitem: 15,

2743 json_getitem_op: 15,

2744 json_path_getitem_op: 15,

2745 pow_: 15,

2746 mul: 8,

2747 truediv: 8,

2748 floordiv: 8,

2749 mod: 8,

2750 neg: 8,

2751 bitwise_not_op: 8,

2752 add: 7,

2753 sub: 7,

2754 bitwise_xor_op: 7,

2755 bitwise_or_op: 7,

2756 bitwise_and_op: 7,

2757 bitwise_lshift_op: 7,

2758 bitwise_rshift_op: 7,

2759 null_op: 7,

2760 filter_op: 6,

2761 concat_op: 5,

2762 match_op: 5,

2763 not_match_op: 5,

2764 regexp_match_op: 5,

2765 not_regexp_match_op: 5,

2766 regexp_replace_op: 5,

2767 ilike_op: 5,

2768 not_ilike_op: 5,

2769 like_op: 5,

2770 not_like_op: 5,

2771 in_op: 5,

2772 not_in_op: 5,

2773 is_: 5,

2774 is_not: 5,

2775 eq: 5,

2776 ne: 5,

2777 is_distinct_from: 5,

2778 is_not_distinct_from: 5,

2779 gt: 5,

2780 lt: 5,

2781 ge: 5,

2782 le: 5,

2783 between_op: 5,

2784 not_between_op: 5,

2785 distinct_op: 5,

2786 inv: 5,

2787 is_true: 5,

2788 is_false: 5,

2789 and_: 3,

2790 or_: 2,

2791 comma_op: -1,

2792 desc_op: 3,

2793 asc_op: 3,

2794 collate: 4,

2795 as_: -1,

2796 exists: 0,

2797 _asbool: -10,

2798}

2799

2800

2801# Mapping of OperatorType objects to their corresponding OperatorClass

2802# Derived from unified_operator_lookup in default_comparator.py

2803_OPERATOR_CLASSES: util.immutabledict[OperatorType, OperatorClass] = (

2804 util.immutabledict(

2805 {

2806 # BASE operators

2807 null_op: OperatorClass.BASE,

2808 # COMPARISON operators

2809 lt: OperatorClass.COMPARISON,

2810 le: OperatorClass.COMPARISON,

2811 ne: OperatorClass.COMPARISON,

2812 gt: OperatorClass.COMPARISON,

2813 ge: OperatorClass.COMPARISON,

2814 eq: OperatorClass.COMPARISON,

2815 is_distinct_from: OperatorClass.COMPARISON,

2816 is_not_distinct_from: OperatorClass.COMPARISON,

2817 in_op: OperatorClass.COMPARISON,

2818 not_in_op: OperatorClass.COMPARISON,

2819 is_: OperatorClass.COMPARISON,

2820 is_not: OperatorClass.COMPARISON,

2821 between_op: OperatorClass.COMPARISON,

2822 not_between_op: OperatorClass.COMPARISON,

2823 desc_op: OperatorClass.COMPARISON,

2824 asc_op: OperatorClass.COMPARISON,

2825 nulls_first_op: OperatorClass.COMPARISON,

2826 nulls_last_op: OperatorClass.COMPARISON,

2827 distinct_op: OperatorClass.COMPARISON,

2828 any_op: OperatorClass.COMPARISON,

2829 all_op: OperatorClass.COMPARISON,

2830 # BOOLEAN_ALGEBRA operators

2831 and_: OperatorClass.BOOLEAN_ALGEBRA,

2832 or_: OperatorClass.BOOLEAN_ALGEBRA,

2833 inv: OperatorClass.BOOLEAN_ALGEBRA | OperatorClass.BITWISE,

2834 # CONCATENABLE | MATH | DATE_ARITHMETIC | BITWISE operators

2835 add: OperatorClass.CONCATENABLE

2836 | OperatorClass.MATH

2837 | OperatorClass.DATE_ARITHEMETIC

2838 | OperatorClass.BITWISE,

2839 # CONCATENABLE | BITWISE operators

2840 concat_op: OperatorClass.CONCATENABLE | OperatorClass.BITWISE,

2841 # INDEXABLE operators

2842 getitem: OperatorClass.INDEXABLE,

2843 # CONTAINS operators

2844 contains_op: OperatorClass.CONTAINS,

2845 icontains_op: OperatorClass.CONTAINS,

2846 contains: OperatorClass.CONTAINS,

2847 not_contains_op: OperatorClass.CONTAINS,

2848 not_icontains_op: OperatorClass.CONTAINS,

2849 # STRING_MATCH operators

2850 like_op: OperatorClass.STRING_MATCH,

2851 ilike_op: OperatorClass.STRING_MATCH,

2852 not_like_op: OperatorClass.STRING_MATCH,

2853 not_ilike_op: OperatorClass.STRING_MATCH,

2854 startswith_op: OperatorClass.STRING_MATCH,

2855 istartswith_op: OperatorClass.STRING_MATCH,

2856 endswith_op: OperatorClass.STRING_MATCH,

2857 iendswith_op: OperatorClass.STRING_MATCH,

2858 not_startswith_op: OperatorClass.STRING_MATCH,

2859 not_istartswith_op: OperatorClass.STRING_MATCH,

2860 not_endswith_op: OperatorClass.STRING_MATCH,

2861 not_iendswith_op: OperatorClass.STRING_MATCH,

2862 collate: OperatorClass.STRING_MATCH,

2863 match_op: OperatorClass.STRING_MATCH,

2864 not_match_op: OperatorClass.STRING_MATCH,

2865 regexp_match_op: OperatorClass.STRING_MATCH,

2866 not_regexp_match_op: OperatorClass.STRING_MATCH,

2867 regexp_replace_op: OperatorClass.STRING_MATCH,

2868 # BITWISE operators

2869 lshift: OperatorClass.BITWISE,

2870 rshift: OperatorClass.BITWISE,

2871 bitwise_xor_op: OperatorClass.BITWISE,

2872 bitwise_or_op: OperatorClass.BITWISE,

2873 bitwise_and_op: OperatorClass.BITWISE,

2874 bitwise_not_op: OperatorClass.BITWISE,

2875 bitwise_lshift_op: OperatorClass.BITWISE,

2876 bitwise_rshift_op: OperatorClass.BITWISE,

2877 # MATH operators

2878 matmul: OperatorClass.MATH,

2879 pow_: OperatorClass.MATH,

2880 neg: OperatorClass.MATH,

2881 mul: OperatorClass.MATH,

2882 sub: OperatorClass.MATH | OperatorClass.DATE_ARITHEMETIC,

2883 truediv: OperatorClass.MATH,

2884 floordiv: OperatorClass.MATH,

2885 mod: OperatorClass.MATH,

2886 # JSON_GETITEM operators

2887 json_path_getitem_op: OperatorClass.JSON_GETITEM,

2888 json_getitem_op: OperatorClass.JSON_GETITEM,

2889 }

2890 )

2891)

2892

2893

2894def is_precedent(

2895 operator: OperatorType, against: Optional[OperatorType]

2896) -> bool:

2897 if operator is against and is_natural_self_precedent(operator):

2898 return False

2899 elif against is None:

2900 return True

2901 else:

2902 return bool(

2903 _PRECEDENCE.get(

2904 operator, getattr(operator, "precedence", _OpLimit._smallest)

2905 )

2906 <= _PRECEDENCE.get(

2907 against, getattr(against, "precedence", _OpLimit._largest)

2908 )

2909 )