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 ) -> Callable[[Any], Operators]:

282 """Produce a generic operator function.

283

284 e.g.::

285

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

287

288 produces::

289

290 somecolumn * 5

291

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

293 example::

294

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

296

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

298

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

300 between this element and the expression passed to the

301 generated function.

302

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

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

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

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

307 expression to be parenthesized when applied against another operator

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

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

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

311 lower than or equal to all operators.

312

313 .. seealso::

314

315 :ref:`faq_sql_expression_op_parenthesis` - detailed description

316 of how the SQLAlchemy SQL compiler renders parenthesis

317

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

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

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

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

322 comparison operator when used in a custom join condition.

323

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

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

326 operator sets this parameter automatically, but also provides

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

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

329

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

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

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

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

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

335 type as the left-hand operand.

336

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

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

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

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

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

342 update or delete.

343

344 e.g.::

345

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

347

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

349 left and right objects::

350

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

352 15

353

354 .. versionadded:: 2.0

355

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

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

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

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

360 datatypes.

361

362 .. versionadded:: 2.1

363

364 .. seealso::

365

366 :meth:`.Operators.bool_op`

367

368 :ref:`types_operators`

369

370 :ref:`relationship_custom_operator`

371

372 """

373 operator = custom_op(

374 opstring,

375 precedence=precedence,

376 is_comparison=is_comparison,

377 return_type=return_type,

378 python_impl=python_impl,

379 operator_class=operator_class,

380 )

381

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

383 return operator(self, other)

384

385 return against

386

387 def bool_op(

388 self,

389 opstring: str,

390 precedence: int = 0,

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

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

393 """Return a custom boolean operator.

394

395 This method is shorthand for calling

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

397 :paramref:`.Operators.op.is_comparison`

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

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

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

401

402 .. seealso::

403

404 :meth:`.Operators.op`

405

406 """

407 return self.op(

408 opstring,

409 precedence=precedence,

410 is_comparison=True,

411 python_impl=python_impl,

412 )

413

414 def operate(

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

416 ) -> Operators:

417 r"""Operate on an argument.

418

419 This is the lowest level of operation, raises

420 :class:`NotImplementedError` by default.

421

422 Overriding this on a subclass can allow common

423 behavior to be applied to all operations.

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

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

426 side::

427

428 class MyComparator(ColumnOperators):

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

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

431

432 :param op: Operator callable.

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

434 be a single scalar for most operations.

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

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

437

438

439 """

440 raise NotImplementedError(str(op))

441

442 __sa_operate__ = operate

443

444 def reverse_operate(

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

446 ) -> Operators:

447 """Reverse operate on an argument.

448

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

450

451 """

452 raise NotImplementedError(str(op))

453

454

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

456 """Represent a 'custom' operator.

457

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

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

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

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

462 to represent the "factorial" operation::

463

464 from sqlalchemy.sql import UnaryExpression

465 from sqlalchemy.sql import operators

466 from sqlalchemy import Numeric

467

468 unary = UnaryExpression(

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

470 )

471

472 .. seealso::

473

474 :meth:`.Operators.op`

475

476 :meth:`.Operators.bool_op`

477

478 """ # noqa: E501

479

480 __name__ = "custom_op"

481

482 __slots__ = (

483 "opstring",

484 "precedence",

485 "is_comparison",

486 "natural_self_precedent",

487 "eager_grouping",

488 "return_type",

489 "python_impl",

490 "operator_class",

491 )

492

493 def __init__(

494 self,

495 opstring: str,

496 *,

497 precedence: int = 0,

498 is_comparison: bool = False,

499 return_type: Optional[

500 Union[Type[TypeEngine[_T]], TypeEngine[_T]]

501 ] = None,

502 natural_self_precedent: bool = False,

503 eager_grouping: bool = False,

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

505 operator_class: OperatorClass = OperatorClass.BASE,

506 ):

507 self.opstring = opstring

508 self.precedence = precedence

509 self.is_comparison = is_comparison

510 self.natural_self_precedent = natural_self_precedent

511 self.eager_grouping = eager_grouping

512 self.return_type = (

513 return_type._to_instance(return_type) if return_type else None

514 )

515 self.python_impl = python_impl

516 self.operator_class = operator_class

517

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

519 return (

520 isinstance(other, custom_op)

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

522 )

523

524 def __hash__(self) -> int:

525 return hash(self._hash_key())

526

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

528 return (

529 self.__class__,

530 self.opstring,

531 self.precedence,

532 self.is_comparison,

533 self.natural_self_precedent,

534 self.eager_grouping,

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

536 self.operator_class,

537 )

538

539 @overload

540 def __call__(

541 self,

542 left: ColumnExpressionArgument[Any],

543 right: Optional[Any] = None,

544 *other: Any,

545 **kwargs: Any,

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

547

548 @overload

549 def __call__(

550 self,

551 left: Operators,

552 right: Optional[Any] = None,

553 *other: Any,

554 **kwargs: Any,

555 ) -> Operators: ...

556

557 def __call__(

558 self,

559 left: Any,

560 right: Optional[Any] = None,

561 *other: Any,

562 **kwargs: Any,

563 ) -> Operators:

564 if hasattr(left, "__sa_operate__"):

565 return left.operate(self, right, *other, **kwargs) # type: ignore

566 elif self.python_impl:

567 return self.python_impl(left, right, *other, **kwargs) # type: ignore # noqa: E501

568 else:

569 raise exc.InvalidRequestError(

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

571 "plain Python objects unless it includes the "

572 "'python_impl' parameter."

573 )

574

575

576class OrderingOperators(Operators):

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

578 or comma-separated lists

579

580 """

581

582 __slots__ = ()

583

584 if typing.TYPE_CHECKING:

585

586 def operate(

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

588 ) -> OrderingOperators: ...

589

590 def reverse_operate(

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

592 ) -> OrderingOperators: ...

593

594 def desc(self) -> OrderingOperators:

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

596 parent object."""

597 return self.operate(desc_op)

598

599 def asc(self) -> OrderingOperators:

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

601 parent object."""

602 return self.operate(asc_op)

603

604 def nulls_first(self) -> OrderingOperators:

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

606 parent object.

607

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

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

610 The previous name remains available for backwards compatibility.

611 """

612 return self.operate(nulls_first_op)

613

614 # deprecated 1.4; see #5435

615 if TYPE_CHECKING:

616

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

618

619 else:

620 nullsfirst = nulls_first

621

622 def nulls_last(self) -> OrderingOperators:

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

624 parent object.

625

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

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

628 The previous name remains available for backwards compatibility.

629 """

630 return self.operate(nulls_last_op)

631

632 # deprecated 1.4; see #5429

633 if TYPE_CHECKING:

634

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

636

637 else:

638 nullslast = nulls_last

639

640

641class ColumnOperators(OrderingOperators):

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

643 :class:`_expression.ColumnElement` expressions.

644

645 By default, all methods call down to

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

647 passing in the appropriate operator function from the

648 Python builtin ``operator`` module or

649 a SQLAlchemy-specific operator function from

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

651 the ``__eq__`` function::

652

653 def __eq__(self, other):

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

655

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

657

658 def eq(a, b):

659 return a == b

660

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

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

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

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

665 construct.

666

667 .. seealso::

668

669 :ref:`types_operators`

670

671 :attr:`.TypeEngine.comparator_factory`

672

673 :class:`.ColumnOperators`

674

675 :class:`.PropComparator`

676

677 """

678

679 __slots__ = ()

680

681 timetuple: Literal[None] = None

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

683

684 if typing.TYPE_CHECKING:

685

686 def operate(

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

688 ) -> ColumnOperators: ...

689

690 def reverse_operate(

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

692 ) -> ColumnOperators: ...

693

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

695 """Implement the ``<`` operator.

696

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

698

699 """

700 return self.operate(lt, other)

701

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

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

704

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

706

707 """

708 return self.operate(le, other)

709

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

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

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

713 if TYPE_CHECKING:

714

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

716

717 else:

718 __hash__ = Operators.__hash__

719

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

721 """Implement the ``==`` operator.

722

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

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

725

726 """

727 return self.operate(eq, other)

728

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

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

731

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

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

734

735 """

736 return self.operate(ne, other)

737

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

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

740

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

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

743

744 """

745 return self.operate(is_distinct_from, other)

746

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

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

749

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

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

752

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

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

755 The previous name remains available for backwards compatibility.

756

757 """

758 return self.operate(is_not_distinct_from, other)

759

760 # deprecated 1.4; see #5435

761 if TYPE_CHECKING:

762

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

764

765 else:

766 isnot_distinct_from = is_not_distinct_from

767

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

769 """Implement the ``>`` operator.

770

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

772

773 """

774 return self.operate(gt, other)

775

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

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

778

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

780

781 """

782 return self.operate(ge, other)

783

784 def __neg__(self) -> ColumnOperators:

785 """Implement the ``-`` operator.

786

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

788

789 """

790 return self.operate(neg)

791

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

793 return self.operate(contains, other)

794

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

796 """Implement the [] operator.

797

798 This can be used by some database-specific types

799 such as PostgreSQL ARRAY and HSTORE.

800

801 """

802 return self.operate(getitem, index)

803

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

805 """Implement the ``<<`` operator.

806

807 Not used by SQLAlchemy core, this is provided

808 for custom operator systems which want to use

809 << as an extension point.

810 """

811 return self.operate(lshift, other)

812

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

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

815

816 Not used by SQLAlchemy core, this is provided

817 for custom operator systems which want to use

818 << as an extension point.

819 """

820 return self.reverse_operate(lshift, other)

821

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

823 """Implement the ``>>`` operator.

824

825 Not used by SQLAlchemy core, this is provided

826 for custom operator systems which want to use

827 >> as an extension point.

828 """

829 return self.operate(rshift, other)

830

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

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

833

834 Not used by SQLAlchemy core, this is provided

835 for custom operator systems which want to use

836 >> as an extension point.

837 """

838 return self.reverse_operate(rshift, other)

839

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

841 """Implement the ``@`` operator.

842

843 Not used by SQLAlchemy core, this is provided

844 for custom operator systems which want to use

845 @ as an extension point.

846 """

847 return self.operate(matmul, other)

848

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

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

851

852 Not used by SQLAlchemy core, this is provided

853 for custom operator systems which want to use

854 @ as an extension point.

855 """

856 return self.reverse_operate(matmul, other)

857

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

859 """Implement the 'concat' operator.

860

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

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

863

864 """

865 return self.operate(concat_op, other)

866

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

868 """Implement an 'rconcat' operator.

869

870 this is for internal use at the moment

871

872 .. versionadded:: 1.4.40

873

874 """

875 return self.reverse_operate(concat_op, other)

876

877 def like(

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

879 ) -> ColumnOperators:

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

881

882 In a column context, produces the expression:

883

884 .. sourcecode:: sql

885

886 a LIKE other

887

888 E.g.::

889

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

891

892 :param other: expression to be compared

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

894 keyword, e.g.::

895

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

897

898 .. seealso::

899

900 :meth:`.ColumnOperators.ilike`

901

902 """

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

904

905 def ilike(

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

907 ) -> ColumnOperators:

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

909

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

911

912 .. sourcecode:: sql

913

914 lower(a) LIKE lower(other)

915

916 Or on backends that support the ILIKE operator:

917

918 .. sourcecode:: sql

919

920 a ILIKE other

921

922 E.g.::

923

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

925

926 :param other: expression to be compared

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

928 keyword, e.g.::

929

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

931

932 .. seealso::

933

934 :meth:`.ColumnOperators.like`

935

936 """ # noqa: E501

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

938

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

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

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

942

943 .. versionadded:: 2.0.2

944

945 .. seealso::

946

947 :ref:`operators_bitwise`

948

949 """

950

951 return self.operate(bitwise_xor_op, other)

952

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

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

955 operator.

956

957 .. versionadded:: 2.0.2

958

959 .. seealso::

960

961 :ref:`operators_bitwise`

962

963 """

964

965 return self.operate(bitwise_or_op, other)

966

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

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

969 operator.

970

971 .. versionadded:: 2.0.2

972

973 .. seealso::

974

975 :ref:`operators_bitwise`

976

977 """

978

979 return self.operate(bitwise_and_op, other)

980

981 def bitwise_not(self) -> ColumnOperators:

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

983 operator.

984

985 .. versionadded:: 2.0.2

986

987 .. seealso::

988

989 :ref:`operators_bitwise`

990

991 """

992

993 return self.operate(bitwise_not_op)

994

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

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

997 operator.

998

999 .. versionadded:: 2.0.2

1000

1001 .. seealso::

1002

1003 :ref:`operators_bitwise`

1004

1005 """

1006

1007 return self.operate(bitwise_lshift_op, other)

1008

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

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

1011 operator.

1012

1013 .. versionadded:: 2.0.2

1014

1015 .. seealso::

1016

1017 :ref:`operators_bitwise`

1018

1019 """

1020

1021 return self.operate(bitwise_rshift_op, other)

1022

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

1024 """Implement the ``in`` operator.

1025

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

1027

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

1029

1030 * A list of literal values,

1031 e.g.::

1032

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

1034

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

1036 bound parameters the same length as the list given:

1037

1038 .. sourcecode:: sql

1039

1040 WHERE COL IN (?, ?, ?)

1041

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

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

1044

1045 from sqlalchemy import tuple_

1046

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

1048

1049 * An empty list,

1050 e.g.::

1051

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

1053

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

1055 expression. These expressions are tailored to individual backends

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

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

1058

1059 .. sourcecode:: sql

1060

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

1062

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

1064 execution-time generated SELECT subquery in all cases.

1065

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

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

1068

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

1070

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

1072 placeholder expression that looks like:

1073

1074 .. sourcecode:: sql

1075

1076 WHERE COL IN ([EXPANDING_value])

1077

1078 This placeholder expression is intercepted at statement execution

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

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

1081

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

1083

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

1085

1086 .. sourcecode:: sql

1087

1088 WHERE COL IN (?, ?, ?)

1089

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

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

1092 SQLite this would be:

1093

1094 .. sourcecode:: sql

1095

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

1097

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

1099 correlated scalar select::

1100

1101 stmt.where(

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

1103 )

1104

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

1106

1107 .. sourcecode:: sql

1108

1109 WHERE COL IN (SELECT othertable.y

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

1111

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

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

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

1115

1116 """ # noqa: E501

1117 return self.operate(in_op, other)

1118

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

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

1121

1122 This is equivalent to using negation with

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

1124

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

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

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

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

1129 alter this behavior.

1130

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

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

1133 available for backwards compatibility.

1134

1135 .. seealso::

1136

1137 :meth:`.ColumnOperators.in_`

1138

1139 """

1140 return self.operate(not_in_op, other)

1141

1142 # deprecated 1.4; see #5429

1143 if TYPE_CHECKING:

1144

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

1146

1147 else:

1148 notin_ = not_in

1149

1150 def not_like(

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

1152 ) -> ColumnOperators:

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

1154

1155 This is equivalent to using negation with

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

1157

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

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

1160 available for backwards compatibility.

1161

1162 .. seealso::

1163

1164 :meth:`.ColumnOperators.like`

1165

1166 """

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

1168

1169 # deprecated 1.4; see #5435

1170 if TYPE_CHECKING:

1171

1172 def notlike(

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

1174 ) -> ColumnOperators: ...

1175

1176 else:

1177 notlike = not_like

1178

1179 def not_ilike(

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

1181 ) -> ColumnOperators:

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

1183

1184 This is equivalent to using negation with

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

1186

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

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

1189 available for backwards compatibility.

1190

1191 .. seealso::

1192

1193 :meth:`.ColumnOperators.ilike`

1194

1195 """

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

1197

1198 # deprecated 1.4; see #5435

1199 if TYPE_CHECKING:

1200

1201 def notilike(

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

1203 ) -> ColumnOperators: ...

1204

1205 else:

1206 notilike = not_ilike

1207

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

1209 """Implement the ``IS`` operator.

1210

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

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

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

1214 on certain platforms.

1215

1216 .. seealso:: :meth:`.ColumnOperators.is_not`

1217

1218 """

1219 return self.operate(is_, other)

1220

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

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

1223

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

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

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

1227 on certain platforms.

1228

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

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

1231 available for backwards compatibility.

1232

1233 .. seealso:: :meth:`.ColumnOperators.is_`

1234

1235 """

1236 return self.operate(is_not, other)

1237

1238 # deprecated 1.4; see #5429

1239 if TYPE_CHECKING:

1240

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

1242

1243 else:

1244 isnot = is_not

1245

1246 def startswith(

1247 self,

1248 other: Any,

1249 escape: Optional[str] = None,

1250 autoescape: bool = False,

1251 ) -> ColumnOperators:

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

1253

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

1255 of a string value:

1256

1257 .. sourcecode:: sql

1258

1259 column LIKE <other> || '%'

1260

1261 E.g.::

1262

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

1264

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

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

1267 will behave like wildcards as well. For literal string

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

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

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

1271 and not as wildcard characters. Alternatively, the

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

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

1274 the target expression is not a literal string.

1275

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

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

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

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

1280 set to True.

1281

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

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

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

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

1286 SQL expression.

1287

1288 An expression such as::

1289

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

1291

1292 Will render as:

1293

1294 .. sourcecode:: sql

1295

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

1297

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

1299

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

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

1302 character. This character can then be placed preceding occurrences

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

1304 wildcard characters.

1305

1306 An expression such as::

1307

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

1309

1310 Will render as:

1311

1312 .. sourcecode:: sql

1313

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

1315

1316 The parameter may also be combined with

1317 :paramref:`.ColumnOperators.startswith.autoescape`::

1318

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

1320

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

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

1323

1324 .. seealso::

1325

1326 :meth:`.ColumnOperators.endswith`

1327

1328 :meth:`.ColumnOperators.contains`

1329

1330 :meth:`.ColumnOperators.like`

1331

1332 """ # noqa: E501

1333 return self.operate(

1334 startswith_op, other, escape=escape, autoescape=autoescape

1335 )

1336

1337 def istartswith(

1338 self,

1339 other: Any,

1340 escape: Optional[str] = None,

1341 autoescape: bool = False,

1342 ) -> ColumnOperators:

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

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

1345

1346 Produces a LIKE expression that tests against an insensitive

1347 match for the start of a string value:

1348

1349 .. sourcecode:: sql

1350

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

1352

1353 E.g.::

1354

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

1356

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

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

1359 will behave like wildcards as well. For literal string

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

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

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

1363 and not as wildcard characters. Alternatively, the

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

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

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

1367

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

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

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

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

1372 set to True.

1373

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

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

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

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

1378 SQL expression.

1379

1380 An expression such as::

1381

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

1383

1384 Will render as:

1385

1386 .. sourcecode:: sql

1387

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

1389

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

1391

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

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

1394 character. This character can then be placed preceding occurrences

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

1396 wildcard characters.

1397

1398 An expression such as::

1399

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

1401

1402 Will render as:

1403

1404 .. sourcecode:: sql

1405

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

1407

1408 The parameter may also be combined with

1409 :paramref:`.ColumnOperators.istartswith.autoescape`::

1410

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

1412

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

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

1415

1416 .. seealso::

1417

1418 :meth:`.ColumnOperators.startswith`

1419 """ # noqa: E501

1420 return self.operate(

1421 istartswith_op, other, escape=escape, autoescape=autoescape

1422 )

1423

1424 def endswith(

1425 self,

1426 other: Any,

1427 escape: Optional[str] = None,

1428 autoescape: bool = False,

1429 ) -> ColumnOperators:

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

1431

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

1433 of a string value:

1434

1435 .. sourcecode:: sql

1436

1437 column LIKE '%' || <other>

1438

1439 E.g.::

1440

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

1442

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

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

1445 will behave like wildcards as well. For literal string

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

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

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

1449 and not as wildcard characters. Alternatively, the

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

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

1452 the target expression is not a literal string.

1453

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

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

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

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

1458 set to True.

1459

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

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

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

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

1464 SQL expression.

1465

1466 An expression such as::

1467

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

1469

1470 Will render as:

1471

1472 .. sourcecode:: sql

1473

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

1475

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

1477

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

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

1480 character. This character can then be placed preceding occurrences

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

1482 wildcard characters.

1483

1484 An expression such as::

1485

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

1487

1488 Will render as:

1489

1490 .. sourcecode:: sql

1491

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

1493

1494 The parameter may also be combined with

1495 :paramref:`.ColumnOperators.endswith.autoescape`::

1496

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

1498

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

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

1501

1502 .. seealso::

1503

1504 :meth:`.ColumnOperators.startswith`

1505

1506 :meth:`.ColumnOperators.contains`

1507

1508 :meth:`.ColumnOperators.like`

1509

1510 """ # noqa: E501

1511 return self.operate(

1512 endswith_op, other, escape=escape, autoescape=autoescape

1513 )

1514

1515 def iendswith(

1516 self,

1517 other: Any,

1518 escape: Optional[str] = None,

1519 autoescape: bool = False,

1520 ) -> ColumnOperators:

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

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

1523

1524 Produces a LIKE expression that tests against an insensitive match

1525 for the end of a string value:

1526

1527 .. sourcecode:: sql

1528

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

1530

1531 E.g.::

1532

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

1534

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

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

1537 will behave like wildcards as well. For literal string

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

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

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

1541 and not as wildcard characters. Alternatively, the

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

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

1544 the target expression is not a literal string.

1545

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

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

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

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

1550 set to True.

1551

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

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

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

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

1556 SQL expression.

1557

1558 An expression such as::

1559

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

1561

1562 Will render as:

1563

1564 .. sourcecode:: sql

1565

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

1567

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

1569

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

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

1572 character. This character can then be placed preceding occurrences

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

1574 wildcard characters.

1575

1576 An expression such as::

1577

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

1579

1580 Will render as:

1581

1582 .. sourcecode:: sql

1583

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

1585

1586 The parameter may also be combined with

1587 :paramref:`.ColumnOperators.iendswith.autoescape`::

1588

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

1590

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

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

1593

1594 .. seealso::

1595

1596 :meth:`.ColumnOperators.endswith`

1597 """ # noqa: E501

1598 return self.operate(

1599 iendswith_op, other, escape=escape, autoescape=autoescape

1600 )

1601

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

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

1604

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

1606 of a string value:

1607

1608 .. sourcecode:: sql

1609

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

1611

1612 E.g.::

1613

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

1615

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

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

1618 will behave like wildcards as well. For literal string

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

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

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

1622 and not as wildcard characters. Alternatively, the

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

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

1625 the target expression is not a literal string.

1626

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

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

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

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

1631 set to True.

1632

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

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

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

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

1637 SQL expression.

1638

1639 An expression such as::

1640

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

1642

1643 Will render as:

1644

1645 .. sourcecode:: sql

1646

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

1648

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

1650

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

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

1653 character. This character can then be placed preceding occurrences

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

1655 wildcard characters.

1656

1657 An expression such as::

1658

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

1660

1661 Will render as:

1662

1663 .. sourcecode:: sql

1664

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

1666

1667 The parameter may also be combined with

1668 :paramref:`.ColumnOperators.contains.autoescape`::

1669

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

1671

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

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

1674

1675 .. seealso::

1676

1677 :meth:`.ColumnOperators.startswith`

1678

1679 :meth:`.ColumnOperators.endswith`

1680

1681 :meth:`.ColumnOperators.like`

1682

1683

1684 """ # noqa: E501

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

1686

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

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

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

1690

1691 Produces a LIKE expression that tests against an insensitive match

1692 for the middle of a string value:

1693

1694 .. sourcecode:: sql

1695

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

1697

1698 E.g.::

1699

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

1701

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

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

1704 will behave like wildcards as well. For literal string

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

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

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

1708 and not as wildcard characters. Alternatively, the

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

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

1711 the target expression is not a literal string.

1712

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

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

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

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

1717 set to True.

1718

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

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

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

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

1723 SQL expression.

1724

1725 An expression such as::

1726

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

1728

1729 Will render as:

1730

1731 .. sourcecode:: sql

1732

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

1734

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

1736

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

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

1739 character. This character can then be placed preceding occurrences

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

1741 wildcard characters.

1742

1743 An expression such as::

1744

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

1746

1747 Will render as:

1748

1749 .. sourcecode:: sql

1750

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

1752

1753 The parameter may also be combined with

1754 :paramref:`.ColumnOperators.contains.autoescape`::

1755

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

1757

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

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

1760

1761 .. seealso::

1762

1763 :meth:`.ColumnOperators.contains`

1764

1765 """ # noqa: E501

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

1767

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

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

1770

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

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

1773 Examples include:

1774

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

1776

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

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

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

1780

1781

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

1783

1784 .. seealso::

1785

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

1787 additional features.

1788

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

1790 * other backends may provide special implementations.

1791 * Backends without any special implementation will emit

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

1793 example.

1794

1795 """

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

1797

1798 def regexp_match(

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

1800 ) -> ColumnOperators:

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

1802

1803 E.g.::

1804

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

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

1807 )

1808

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

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

1811 the specific regular expression syntax and flags available are

1812 **not backend agnostic**.

1813

1814 Examples include:

1815

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

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

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

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

1820 * other backends may provide special implementations.

1821 * Backends without any special implementation will emit

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

1823 SQLite and MySQL, for example.

1824

1825 Regular expression support is currently implemented for Oracle

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

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

1828

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

1830 clause.

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

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

1833 Some backends, like PostgreSQL and MariaDB, may alternatively

1834 specify the flags as part of the pattern.

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

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

1837

1838 .. versionadded:: 1.4

1839

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

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

1842 objects such as column expressions in addition to plain Python

1843 strings. This implementation did not work correctly with caching

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

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

1846 within SQL expressions.

1847

1848 .. seealso::

1849

1850 :meth:`_sql.ColumnOperators.regexp_replace`

1851

1852

1853 """

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

1855

1856 def regexp_replace(

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

1858 ) -> ColumnOperators:

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

1860

1861 E.g.::

1862

1863 stmt = select(

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

1865 )

1866

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

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

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

1870 the specific regular expression syntax and flags available are

1871 **not backend agnostic**.

1872

1873 Regular expression replacement support is currently implemented for

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

1875 among third-party dialects may vary.

1876

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

1878 clause.

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

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

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

1882 Some backends, like PostgreSQL and MariaDB, may alternatively

1883 specify the flags as part of the pattern.

1884

1885 .. versionadded:: 1.4

1886

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

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

1889 objects such as column expressions in addition to plain Python

1890 strings. This implementation did not work correctly with caching

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

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

1893 within SQL expressions.

1894

1895

1896 .. seealso::

1897

1898 :meth:`_sql.ColumnOperators.regexp_match`

1899

1900 """

1901 return self.operate(

1902 regexp_replace_op,

1903 pattern,

1904 replacement=replacement,

1905 flags=flags,

1906 )

1907

1908 def collate(self, collation: str) -> ColumnOperators:

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

1910 the parent object, given the collation string.

1911

1912 .. seealso::

1913

1914 :func:`_expression.collate`

1915

1916 """

1917 return self.operate(collate, collation)

1918

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

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

1921

1922 See :meth:`.ColumnOperators.__add__`.

1923

1924 """

1925 return self.reverse_operate(add, other)

1926

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

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

1929

1930 See :meth:`.ColumnOperators.__sub__`.

1931

1932 """

1933 return self.reverse_operate(sub, other)

1934

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

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

1937

1938 See :meth:`.ColumnOperators.__mul__`.

1939

1940 """

1941 return self.reverse_operate(mul, other)

1942

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

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

1945

1946 See :meth:`.ColumnOperators.__mod__`.

1947

1948 """

1949 return self.reverse_operate(mod, other)

1950

1951 def between(

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

1953 ) -> ColumnOperators:

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

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

1956

1957 """

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

1959

1960 def distinct(self) -> ColumnOperators:

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

1962 parent object.

1963

1964 """

1965 return self.operate(distinct_op)

1966

1967 def any_(self) -> ColumnOperators:

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

1969 parent object.

1970

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

1972

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

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

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

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

1977 different calling style.

1978

1979 """

1980 return self.operate(any_op)

1981

1982 def all_(self) -> ColumnOperators:

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

1984 parent object.

1985

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

1987

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

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

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

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

1992 different calling style.

1993

1994 """

1995 return self.operate(all_op)

1996

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

1998 """Implement the ``+`` operator.

1999

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

2001 if the parent object has non-string affinity.

2002 If the parent object has a string affinity,

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

2004 see :meth:`.ColumnOperators.concat`.

2005

2006 """

2007 return self.operate(add, other)

2008

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

2010 """Implement the ``-`` operator.

2011

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

2013

2014 """

2015 return self.operate(sub, other)

2016

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

2018 """Implement the ``*`` operator.

2019

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

2021

2022 """

2023 return self.operate(mul, other)

2024

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

2026 """Implement the ``%`` operator.

2027

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

2029

2030 """

2031 return self.operate(mod, other)

2032

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

2034 """Implement the ``/`` operator.

2035

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

2037 considers the result type to be numeric.

2038

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

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

2041 backends may vary.

2042

2043 """

2044 return self.operate(truediv, other)

2045

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

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

2048

2049 See :meth:`.ColumnOperators.__truediv__`.

2050

2051 """

2052 return self.reverse_operate(truediv, other)

2053

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

2055 """Implement the ``//`` operator.

2056

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

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

2059 type to be integer.

2060

2061 .. versionadded:: 2.0

2062

2063 """

2064 return self.operate(floordiv, other)

2065

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

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

2068

2069 See :meth:`.ColumnOperators.__floordiv__`.

2070

2071 """

2072 return self.reverse_operate(floordiv, other)

2073

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

2075 """Implement the ``**`` operator.

2076

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

2078 dialect-specific expression.

2079

2080 .. versionadded:: 2.1

2081

2082 """

2083 return self.operate(pow_, other)

2084

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

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

2087

2088 .. seealso::

2089

2090 :meth:`.ColumnOperators.__pow__`.

2091

2092 .. versionadded:: 2.1

2093

2094 """

2095 return self.reverse_operate(pow_, other)

2096

2097

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

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

2100

2101

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

2103 return cast(OperatorType, fn)

2104

2105

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

2107 _commutative.add(fn)

2108 return fn

2109

2110

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

2112 _comparison.add(fn)

2113 return fn

2114

2115

2116@_operator_fn

2117def from_() -> Any:

2118 raise NotImplementedError()

2119

2120

2121@_operator_fn

2122@comparison_op

2123def function_as_comparison_op() -> Any:

2124 raise NotImplementedError()

2125

2126

2127@_operator_fn

2128def as_() -> Any:

2129 raise NotImplementedError()

2130

2131

2132@_operator_fn

2133def exists() -> Any:

2134 raise NotImplementedError()

2135

2136

2137@_operator_fn

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

2139 raise NotImplementedError()

2140

2141

2142# 1.4 deprecated; see #5435

2143if TYPE_CHECKING:

2144

2145 @_operator_fn

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

2147

2148else:

2149 istrue = is_true

2150

2151

2152@_operator_fn

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

2154 raise NotImplementedError()

2155

2156

2157# 1.4 deprecated; see #5435

2158if TYPE_CHECKING:

2159

2160 @_operator_fn

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

2162

2163else:

2164 isfalse = is_false

2165

2166

2167@comparison_op

2168@_operator_fn

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

2170 return a.is_distinct_from(b)

2171

2172

2173@comparison_op

2174@_operator_fn

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

2176 return a.is_not_distinct_from(b)

2177

2178

2179# deprecated 1.4; see #5435

2180if TYPE_CHECKING:

2181

2182 @_operator_fn

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

2184

2185else:

2186 isnot_distinct_from = is_not_distinct_from

2187

2188

2189@comparison_op

2190@_operator_fn

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

2192 return a.is_(b)

2193

2194

2195@comparison_op

2196@_operator_fn

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

2198 return a.is_not(b)

2199

2200

2201# 1.4 deprecated; see #5429

2202if TYPE_CHECKING:

2203

2204 @_operator_fn

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

2206

2207else:

2208 isnot = is_not

2209

2210

2211@_operator_fn

2212def collate(a: Any, b: Any) -> Any:

2213 return a.collate(b)

2214

2215

2216@_operator_fn

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

2218 return a.op(opstring)(b)

2219

2220

2221@comparison_op

2222@_operator_fn

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

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

2225

2226

2227@comparison_op

2228@_operator_fn

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

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

2231

2232

2233# 1.4 deprecated; see #5435

2234if TYPE_CHECKING:

2235

2236 @_operator_fn

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

2238

2239else:

2240 notlike_op = not_like_op

2241

2242

2243@comparison_op

2244@_operator_fn

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

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

2247

2248

2249@comparison_op

2250@_operator_fn

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

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

2253

2254

2255# 1.4 deprecated; see #5435

2256if TYPE_CHECKING:

2257

2258 @_operator_fn

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

2260

2261else:

2262 notilike_op = not_ilike_op

2263

2264

2265@comparison_op

2266@_operator_fn

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

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

2269

2270

2271@comparison_op

2272@_operator_fn

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

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

2275

2276

2277# 1.4 deprecated; see #5435

2278if TYPE_CHECKING:

2279

2280 @_operator_fn

2281 def notbetween_op(

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

2283 ) -> Any: ...

2284

2285else:

2286 notbetween_op = not_between_op

2287

2288

2289@comparison_op

2290@_operator_fn

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

2292 return a.in_(b)

2293

2294

2295@comparison_op

2296@_operator_fn

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

2298 return a.not_in(b)

2299

2300

2301# 1.4 deprecated; see #5429

2302if TYPE_CHECKING:

2303

2304 @_operator_fn

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

2306

2307else:

2308 notin_op = not_in_op

2309

2310

2311@_operator_fn

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

2313 return a.distinct()

2314

2315

2316@_operator_fn

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

2318 return a.any_()

2319

2320

2321@_operator_fn

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

2323 return a.all_()

2324

2325

2326def _escaped_like_impl(

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

2328) -> Any:

2329 if autoescape:

2330 if autoescape is not True:

2331 util.warn(

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

2333 )

2334 if escape is None:

2335 escape = "/"

2336

2337 if not isinstance(other, str):

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

2339

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

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

2342

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

2344

2345 return fn(other, escape=escape)

2346

2347

2348@comparison_op

2349@_operator_fn

2350def startswith_op(

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

2352) -> Any:

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

2354

2355

2356@comparison_op

2357@_operator_fn

2358def not_startswith_op(

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

2360) -> Any:

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

2362

2363

2364# 1.4 deprecated; see #5435

2365if TYPE_CHECKING:

2366

2367 @_operator_fn

2368 def notstartswith_op(

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

2370 ) -> Any: ...

2371

2372else:

2373 notstartswith_op = not_startswith_op

2374

2375

2376@comparison_op

2377@_operator_fn

2378def istartswith_op(

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

2380) -> Any:

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

2382

2383

2384@comparison_op

2385@_operator_fn

2386def not_istartswith_op(

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

2388) -> Any:

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

2390

2391

2392@comparison_op

2393@_operator_fn

2394def endswith_op(

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

2396) -> Any:

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

2398

2399

2400@comparison_op

2401@_operator_fn

2402def not_endswith_op(

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

2404) -> Any:

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

2406

2407

2408# 1.4 deprecated; see #5435

2409if TYPE_CHECKING:

2410

2411 @_operator_fn

2412 def notendswith_op(

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

2414 ) -> Any: ...

2415

2416else:

2417 notendswith_op = not_endswith_op

2418

2419

2420@comparison_op

2421@_operator_fn

2422def iendswith_op(

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

2424) -> Any:

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

2426

2427

2428@comparison_op

2429@_operator_fn

2430def not_iendswith_op(

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

2432) -> Any:

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

2434

2435

2436@comparison_op

2437@_operator_fn

2438def contains_op(

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

2440) -> Any:

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

2442

2443

2444@comparison_op

2445@_operator_fn

2446def not_contains_op(

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

2448) -> Any:

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

2450

2451

2452# 1.4 deprecated; see #5435

2453if TYPE_CHECKING:

2454

2455 @_operator_fn

2456 def notcontains_op(

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

2458 ) -> Any: ...

2459

2460else:

2461 notcontains_op = not_contains_op

2462

2463

2464@comparison_op

2465@_operator_fn

2466def icontains_op(

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

2468) -> Any:

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

2470

2471

2472@comparison_op

2473@_operator_fn

2474def not_icontains_op(

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

2476) -> Any:

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

2478

2479

2480@comparison_op

2481@_operator_fn

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

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

2484

2485

2486@comparison_op

2487@_operator_fn

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

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

2490

2491

2492@comparison_op

2493@_operator_fn

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

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

2496

2497

2498@_operator_fn

2499def regexp_replace_op(

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

2501) -> Any:

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

2503

2504

2505@comparison_op

2506@_operator_fn

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

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

2509

2510

2511# 1.4 deprecated; see #5429

2512if TYPE_CHECKING:

2513

2514 @_operator_fn

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

2516

2517else:

2518 notmatch_op = not_match_op

2519

2520

2521@_operator_fn

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

2523 raise NotImplementedError()

2524

2525

2526@_operator_fn

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

2528 raise NotImplementedError()

2529

2530

2531@_operator_fn

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

2533 try:

2534 concat = a.concat

2535 except AttributeError:

2536 return b._rconcat(a)

2537 else:

2538 return concat(b)

2539

2540

2541@_operator_fn

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

2543 return a.desc()

2544

2545

2546@_operator_fn

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

2548 return a.asc()

2549

2550

2551@_operator_fn

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

2553 return a.nulls_first()

2554

2555

2556# 1.4 deprecated; see #5435

2557if TYPE_CHECKING:

2558

2559 @_operator_fn

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

2561

2562else:

2563 nullsfirst_op = nulls_first_op

2564

2565

2566@_operator_fn

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

2568 return a.nulls_last()

2569

2570

2571# 1.4 deprecated; see #5435

2572if TYPE_CHECKING:

2573

2574 @_operator_fn

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

2576

2577else:

2578 nullslast_op = nulls_last_op

2579

2580

2581@_operator_fn

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

2583 raise NotImplementedError()

2584

2585

2586@_operator_fn

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

2588 raise NotImplementedError()

2589

2590

2591@_operator_fn

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

2593 return a.bitwise_xor(b)

2594

2595

2596@_operator_fn

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

2598 return a.bitwise_or(b)

2599

2600

2601@_operator_fn

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

2603 return a.bitwise_and(b)

2604

2605

2606@_operator_fn

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

2608 return a.bitwise_not()

2609

2610

2611@_operator_fn

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

2613 return a.bitwise_lshift(b)

2614

2615

2616@_operator_fn

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

2618 return a.bitwise_rshift(b)

2619

2620

2621@_operator_fn

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

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

2624

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

2626

2627 .. versionadded:: 2.1

2628

2629 """

2630 return a._null_operate(b)

2631

2632

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

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

2635

2636

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

2638 return op in _commutative

2639

2640

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

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

2643

2644

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

2646 return (

2647 op in _natural_self_precedent

2648 or isinstance(op, custom_op)

2649 and op.natural_self_precedent

2650 )

2651

2652

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

2654

2655

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

2657 return is_comparison(op) or op in _booleans

2658

2659

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

2661

2662

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

2664 """rotate a comparison operator 180 degrees.

2665

2666 Note this is not the same as negation.

2667

2668 """

2669 return _mirror.get(op, op)

2670

2671

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

2673

2674

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

2676 return op in _associative

2677

2678

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

2680 return op in _order_by_modifier

2681

2682

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

2684

2685_natural_self_precedent = _associative.union(

2686 [getitem, json_getitem_op, json_path_getitem_op]

2687)

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

2689parenthesize (a op b).

2690

2691"""

2692

2693

2694@_operator_fn

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

2696 raise NotImplementedError()

2697

2698

2699class _OpLimit(IntEnum):

2700 _smallest = -100

2701 _largest = 100

2702

2703

2704_PRECEDENCE: Dict[OperatorType, int] = {

2705 from_: 15,

2706 function_as_comparison_op: 15,

2707 any_op: 15,

2708 all_op: 15,

2709 getitem: 15,

2710 json_getitem_op: 15,

2711 json_path_getitem_op: 15,

2712 pow_: 15,

2713 mul: 8,

2714 truediv: 8,

2715 floordiv: 8,

2716 mod: 8,

2717 neg: 8,

2718 bitwise_not_op: 8,

2719 add: 7,

2720 sub: 7,

2721 bitwise_xor_op: 7,

2722 bitwise_or_op: 7,

2723 bitwise_and_op: 7,

2724 bitwise_lshift_op: 7,

2725 bitwise_rshift_op: 7,

2726 null_op: 7,

2727 filter_op: 6,

2728 concat_op: 5,

2729 match_op: 5,

2730 not_match_op: 5,

2731 regexp_match_op: 5,

2732 not_regexp_match_op: 5,

2733 regexp_replace_op: 5,

2734 ilike_op: 5,

2735 not_ilike_op: 5,

2736 like_op: 5,

2737 not_like_op: 5,

2738 in_op: 5,

2739 not_in_op: 5,

2740 is_: 5,

2741 is_not: 5,

2742 eq: 5,

2743 ne: 5,

2744 is_distinct_from: 5,

2745 is_not_distinct_from: 5,

2746 gt: 5,

2747 lt: 5,

2748 ge: 5,

2749 le: 5,

2750 between_op: 5,

2751 not_between_op: 5,

2752 distinct_op: 5,

2753 inv: 5,

2754 is_true: 5,

2755 is_false: 5,

2756 and_: 3,

2757 or_: 2,

2758 comma_op: -1,

2759 desc_op: 3,

2760 asc_op: 3,

2761 collate: 4,

2762 as_: -1,

2763 exists: 0,

2764 _asbool: -10,

2765}

2766

2767

2768# Mapping of OperatorType objects to their corresponding OperatorClass

2769# Derived from unified_operator_lookup in default_comparator.py

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

2771 util.immutabledict(

2772 {

2773 # BASE operators

2774 null_op: OperatorClass.BASE,

2775 # COMPARISON operators

2776 lt: OperatorClass.COMPARISON,

2777 le: OperatorClass.COMPARISON,

2778 ne: OperatorClass.COMPARISON,

2779 gt: OperatorClass.COMPARISON,

2780 ge: OperatorClass.COMPARISON,

2781 eq: OperatorClass.COMPARISON,

2782 is_distinct_from: OperatorClass.COMPARISON,

2783 is_not_distinct_from: OperatorClass.COMPARISON,

2784 in_op: OperatorClass.COMPARISON,

2785 not_in_op: OperatorClass.COMPARISON,

2786 is_: OperatorClass.COMPARISON,

2787 is_not: OperatorClass.COMPARISON,

2788 between_op: OperatorClass.COMPARISON,

2789 not_between_op: OperatorClass.COMPARISON,

2790 desc_op: OperatorClass.COMPARISON,

2791 asc_op: OperatorClass.COMPARISON,

2792 nulls_first_op: OperatorClass.COMPARISON,

2793 nulls_last_op: OperatorClass.COMPARISON,

2794 distinct_op: OperatorClass.COMPARISON,

2795 any_op: OperatorClass.COMPARISON,

2796 all_op: OperatorClass.COMPARISON,

2797 # BOOLEAN_ALGEBRA operators

2798 and_: OperatorClass.BOOLEAN_ALGEBRA,

2799 or_: OperatorClass.BOOLEAN_ALGEBRA,

2800 inv: OperatorClass.BOOLEAN_ALGEBRA | OperatorClass.BITWISE,

2801 # CONCATENABLE | MATH | DATE_ARITHMETIC | BITWISE operators

2802 add: OperatorClass.CONCATENABLE

2803 | OperatorClass.MATH

2804 | OperatorClass.DATE_ARITHEMETIC

2805 | OperatorClass.BITWISE,

2806 # CONCATENABLE | BITWISE operators

2807 concat_op: OperatorClass.CONCATENABLE | OperatorClass.BITWISE,

2808 # INDEXABLE operators

2809 getitem: OperatorClass.INDEXABLE,

2810 # CONTAINS operators

2811 contains_op: OperatorClass.CONTAINS,

2812 icontains_op: OperatorClass.CONTAINS,

2813 contains: OperatorClass.CONTAINS,

2814 not_contains_op: OperatorClass.CONTAINS,

2815 not_icontains_op: OperatorClass.CONTAINS,

2816 # STRING_MATCH operators

2817 like_op: OperatorClass.STRING_MATCH,

2818 ilike_op: OperatorClass.STRING_MATCH,

2819 not_like_op: OperatorClass.STRING_MATCH,

2820 not_ilike_op: OperatorClass.STRING_MATCH,

2821 startswith_op: OperatorClass.STRING_MATCH,

2822 istartswith_op: OperatorClass.STRING_MATCH,

2823 endswith_op: OperatorClass.STRING_MATCH,

2824 iendswith_op: OperatorClass.STRING_MATCH,

2825 not_startswith_op: OperatorClass.STRING_MATCH,

2826 not_istartswith_op: OperatorClass.STRING_MATCH,

2827 not_endswith_op: OperatorClass.STRING_MATCH,

2828 not_iendswith_op: OperatorClass.STRING_MATCH,

2829 collate: OperatorClass.STRING_MATCH,

2830 match_op: OperatorClass.STRING_MATCH,

2831 not_match_op: OperatorClass.STRING_MATCH,

2832 regexp_match_op: OperatorClass.STRING_MATCH,

2833 not_regexp_match_op: OperatorClass.STRING_MATCH,

2834 regexp_replace_op: OperatorClass.STRING_MATCH,

2835 # BITWISE operators

2836 lshift: OperatorClass.BITWISE,

2837 rshift: OperatorClass.BITWISE,

2838 bitwise_xor_op: OperatorClass.BITWISE,

2839 bitwise_or_op: OperatorClass.BITWISE,

2840 bitwise_and_op: OperatorClass.BITWISE,

2841 bitwise_not_op: OperatorClass.BITWISE,

2842 bitwise_lshift_op: OperatorClass.BITWISE,

2843 bitwise_rshift_op: OperatorClass.BITWISE,

2844 # MATH operators

2845 matmul: OperatorClass.MATH,

2846 pow_: OperatorClass.MATH,

2847 neg: OperatorClass.MATH,

2848 mul: OperatorClass.MATH,

2849 sub: OperatorClass.MATH | OperatorClass.DATE_ARITHEMETIC,

2850 truediv: OperatorClass.MATH,

2851 floordiv: OperatorClass.MATH,

2852 mod: OperatorClass.MATH,

2853 # JSON_GETITEM operators

2854 json_path_getitem_op: OperatorClass.JSON_GETITEM,

2855 json_getitem_op: OperatorClass.JSON_GETITEM,

2856 }

2857 )

2858)

2859

2860

2861def is_precedent(

2862 operator: OperatorType, against: Optional[OperatorType]

2863) -> bool:

2864 if operator is against and is_natural_self_precedent(operator):

2865 return False

2866 elif against is None:

2867 return True

2868 else:

2869 return bool(

2870 _PRECEDENCE.get(

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

2872 )

2873 <= _PRECEDENCE.get(

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

2875 )

2876 )