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

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 IntEnum

16from operator import add as _uncast_add

17from operator import and_ as _uncast_and_

18from operator import contains as _uncast_contains

19from operator import eq as _uncast_eq

20from operator import floordiv as _uncast_floordiv

21from operator import ge as _uncast_ge

22from operator import getitem as _uncast_getitem

23from operator import gt as _uncast_gt

24from operator import inv as _uncast_inv

25from operator import le as _uncast_le

26from operator import lshift as _uncast_lshift

27from operator import lt as _uncast_lt

28from operator import mod as _uncast_mod

29from operator import mul as _uncast_mul

30from operator import ne as _uncast_ne

31from operator import neg as _uncast_neg

32from operator import or_ as _uncast_or_

33from operator import rshift as _uncast_rshift

34from operator import sub as _uncast_sub

35from operator import truediv as _uncast_truediv

36import typing

37from typing import Any

38from typing import Callable

39from typing import cast

40from typing import Dict

41from typing import Generic

42from typing import Optional

43from typing import overload

44from typing import Protocol

45from typing import Set

46from typing import Tuple

47from typing import Type

48from typing import TYPE_CHECKING

49from typing import TypeVar

50from typing import Union

52from .. import exc

53from .. import util

54from ..util.typing import Literal

56if typing.TYPE_CHECKING:

57 from ._typing import ColumnExpressionArgument

58 from .cache_key import CacheConst

59 from .elements import ColumnElement

60 from .type_api import TypeEngine

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

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

66class OperatorType(Protocol):

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

69 __slots__ = ()

71 __name__: str

73 @overload

74 def __call__(

75 self,

76 left: ColumnExpressionArgument[Any],

77 right: Optional[Any] = None,

78 *other: Any,

79 **kwargs: Any,

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

82 @overload

83 def __call__(

84 self,

85 left: Operators,

86 right: Optional[Any] = None,

87 *other: Any,

88 **kwargs: Any,

89 ) -> Operators: ...

91 def __call__(

92 self,

93 left: Any,

94 right: Optional[Any] = None,

95 *other: Any,

96 **kwargs: Any,

97 ) -> Operators: ...

100add = cast(OperatorType, _uncast_add)

101and_ = cast(OperatorType, _uncast_and_)

102contains = cast(OperatorType, _uncast_contains)

103eq = cast(OperatorType, _uncast_eq)

104floordiv = cast(OperatorType, _uncast_floordiv)

105ge = cast(OperatorType, _uncast_ge)

106getitem = cast(OperatorType, _uncast_getitem)

107gt = cast(OperatorType, _uncast_gt)

108inv = cast(OperatorType, _uncast_inv)

109le = cast(OperatorType, _uncast_le)

110lshift = cast(OperatorType, _uncast_lshift)

111lt = cast(OperatorType, _uncast_lt)

112mod = cast(OperatorType, _uncast_mod)

113mul = cast(OperatorType, _uncast_mul)

114ne = cast(OperatorType, _uncast_ne)

115neg = cast(OperatorType, _uncast_neg)

116or_ = cast(OperatorType, _uncast_or_)

117rshift = cast(OperatorType, _uncast_rshift)

118sub = cast(OperatorType, _uncast_sub)

119truediv = cast(OperatorType, _uncast_truediv)

120

121

122class Operators:

123 """Base of comparison and logical operators.

124

125 Implements base methods

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

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

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

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

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

131

132 Usually is used via its most common subclass

133 :class:`.ColumnOperators`.

134

135 """

136

137 __slots__ = ()

138

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

140 """Implement the ``&`` operator.

141

142 When used with SQL expressions, results in an

143 AND operation, equivalent to

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

145

146 a & b

147

148 is equivalent to::

149

150 from sqlalchemy import and_

151 and_(a, b)

152

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

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

155 The operands should be enclosed in parenthesis if they contain

156 further sub expressions::

157

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

159

160 """

161 return self.operate(and_, other)

162

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

164 """Implement the ``|`` operator.

165

166 When used with SQL expressions, results in an

167 OR operation, equivalent to

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

169

170 a | b

171

172 is equivalent to::

173

174 from sqlalchemy import or_

175 or_(a, b)

176

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

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

179 The operands should be enclosed in parenthesis if they contain

180 further sub expressions::

181

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

183

184 """

185 return self.operate(or_, other)

186

187 def __invert__(self) -> Operators:

188 """Implement the ``~`` operator.

189

190 When used with SQL expressions, results in a

191 NOT operation, equivalent to

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

193

194 ~a

195

196 is equivalent to::

197

198 from sqlalchemy import not_

199 not_(a)

200

201 """

202 return self.operate(inv)

203

204 def op(

205 self,

206 opstring: str,

207 precedence: int = 0,

208 is_comparison: bool = False,

209 return_type: Optional[

210 Union[Type[TypeEngine[Any]], TypeEngine[Any]]

211 ] = None,

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

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

214 """Produce a generic operator function.

215

216 e.g.::

217

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

219

220 produces::

221

222 somecolumn * 5

223

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

225 example::

226

227 somecolumn.op('&')(0xff)

228

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

230

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

232 between this element and the expression passed to the

233 generated function.

234

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

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

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

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

239 expression to be parenthesized when applied against another operator

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

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

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

243 lower than or equal to all operators.

244

245 .. seealso::

246

247 :ref:`faq_sql_expression_op_parenthesis` - detailed description

248 of how the SQLAlchemy SQL compiler renders parenthesis

249

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

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

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

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

254 comparison operator when used in a custom join condition.

255

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

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

258 operator sets this parameter automatically, but also provides

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

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

261

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

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

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

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

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

267 type as the left-hand operand.

268

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

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

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

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

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

274 update or delete.

275

276 e.g.::

277

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

279

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

281 left and right objects::

282

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

284 15

285

286 .. versionadded:: 2.0

287

288

289 .. seealso::

290

291 :meth:`.Operators.bool_op`

292

293 :ref:`types_operators`

294

295 :ref:`relationship_custom_operator`

296

297 """

298 operator = custom_op(

299 opstring,

300 precedence,

301 is_comparison,

302 return_type,

303 python_impl=python_impl,

304 )

305

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

307 return operator(self, other)

308

309 return against

310

311 def bool_op(

312 self,

313 opstring: str,

314 precedence: int = 0,

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

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

317 """Return a custom boolean operator.

318

319 This method is shorthand for calling

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

321 :paramref:`.Operators.op.is_comparison`

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

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

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

325

326 .. seealso::

327

328 :meth:`.Operators.op`

329

330 """

331 return self.op(

332 opstring,

333 precedence=precedence,

334 is_comparison=True,

335 python_impl=python_impl,

336 )

337

338 def operate(

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

340 ) -> Operators:

341 r"""Operate on an argument.

342

343 This is the lowest level of operation, raises

344 :class:`NotImplementedError` by default.

345

346 Overriding this on a subclass can allow common

347 behavior to be applied to all operations.

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

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

350 side::

351

352 class MyComparator(ColumnOperators):

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

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

355

356 :param op: Operator callable.

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

358 be a single scalar for most operations.

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

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

361

362

363 """

364 raise NotImplementedError(str(op))

365

366 __sa_operate__ = operate

367

368 def reverse_operate(

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

370 ) -> Operators:

371 """Reverse operate on an argument.

372

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

374

375 """

376 raise NotImplementedError(str(op))

377

378

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

380 """Represent a 'custom' operator.

381

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

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

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

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

386 to represent the "factorial" operation::

387

388 from sqlalchemy.sql import UnaryExpression

389 from sqlalchemy.sql import operators

390 from sqlalchemy import Numeric

391

392 unary = UnaryExpression(table.c.somecolumn,

393 modifier=operators.custom_op("!"),

394 type_=Numeric)

395

396

397 .. seealso::

398

399 :meth:`.Operators.op`

400

401 :meth:`.Operators.bool_op`

402

403 """

404

405 __name__ = "custom_op"

406

407 __slots__ = (

408 "opstring",

409 "precedence",

410 "is_comparison",

411 "natural_self_precedent",

412 "eager_grouping",

413 "return_type",

414 "python_impl",

415 )

416

417 def __init__(

418 self,

419 opstring: str,

420 precedence: int = 0,

421 is_comparison: bool = False,

422 return_type: Optional[

423 Union[Type[TypeEngine[_T]], TypeEngine[_T]]

424 ] = None,

425 natural_self_precedent: bool = False,

426 eager_grouping: bool = False,

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

428 ):

429 self.opstring = opstring

430 self.precedence = precedence

431 self.is_comparison = is_comparison

432 self.natural_self_precedent = natural_self_precedent

433 self.eager_grouping = eager_grouping

434 self.return_type = (

435 return_type._to_instance(return_type) if return_type else None

436 )

437 self.python_impl = python_impl

438

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

440 return (

441 isinstance(other, custom_op)

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

443 )

444

445 def __hash__(self) -> int:

446 return hash(self._hash_key())

447

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

449 return (

450 self.__class__,

451 self.opstring,

452 self.precedence,

453 self.is_comparison,

454 self.natural_self_precedent,

455 self.eager_grouping,

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

457 )

458

459 @overload

460 def __call__(

461 self,

462 left: ColumnExpressionArgument[Any],

463 right: Optional[Any] = None,

464 *other: Any,

465 **kwargs: Any,

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

467

468 @overload

469 def __call__(

470 self,

471 left: Operators,

472 right: Optional[Any] = None,

473 *other: Any,

474 **kwargs: Any,

475 ) -> Operators: ...

476

477 def __call__(

478 self,

479 left: Any,

480 right: Optional[Any] = None,

481 *other: Any,

482 **kwargs: Any,

483 ) -> Operators:

484 if hasattr(left, "__sa_operate__"):

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

486 elif self.python_impl:

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

488 else:

489 raise exc.InvalidRequestError(

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

491 "plain Python objects unless it includes the "

492 "'python_impl' parameter."

493 )

494

495

496class ColumnOperators(Operators):

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

498 :class:`_expression.ColumnElement` expressions.

499

500 By default, all methods call down to

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

502 passing in the appropriate operator function from the

503 Python builtin ``operator`` module or

504 a SQLAlchemy-specific operator function from

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

506 the ``__eq__`` function::

507

508 def __eq__(self, other):

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

510

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

512

513 def eq(a, b):

514 return a == b

515

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

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

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

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

520 construct.

521

522 .. seealso::

523

524 :ref:`types_operators`

525

526 :attr:`.TypeEngine.comparator_factory`

527

528 :class:`.ColumnOperators`

529

530 :class:`.PropComparator`

531

532 """

533

534 __slots__ = ()

535

536 timetuple: Literal[None] = None

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

538

539 if typing.TYPE_CHECKING:

540

541 def operate(

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

543 ) -> ColumnOperators: ...

544

545 def reverse_operate(

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

547 ) -> ColumnOperators: ...

548

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

550 """Implement the ``<`` operator.

551

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

553

554 """

555 return self.operate(lt, other)

556

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

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

559

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

561

562 """

563 return self.operate(le, other)

564

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

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

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

568 if TYPE_CHECKING:

569

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

571

572 else:

573 __hash__ = Operators.__hash__

574

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

576 """Implement the ``==`` operator.

577

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

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

580

581 """

582 return self.operate(eq, other)

583

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

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

586

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

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

589

590 """

591 return self.operate(ne, other)

592

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

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

595

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

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

598

599 """

600 return self.operate(is_distinct_from, other)

601

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

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

604

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

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

607

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

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

610 The previous name remains available for backwards compatibility.

611

612 """

613 return self.operate(is_not_distinct_from, other)

614

615 # deprecated 1.4; see #5435

616 if TYPE_CHECKING:

617

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

619

620 else:

621 isnot_distinct_from = is_not_distinct_from

622

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

624 """Implement the ``>`` operator.

625

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

627

628 """

629 return self.operate(gt, other)

630

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

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

633

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

635

636 """

637 return self.operate(ge, other)

638

639 def __neg__(self) -> ColumnOperators:

640 """Implement the ``-`` operator.

641

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

643

644 """

645 return self.operate(neg)

646

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

648 return self.operate(contains, other)

649

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

651 """Implement the [] operator.

652

653 This can be used by some database-specific types

654 such as PostgreSQL ARRAY and HSTORE.

655

656 """

657 return self.operate(getitem, index)

658

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

660 """implement the << operator.

661

662 Not used by SQLAlchemy core, this is provided

663 for custom operator systems which want to use

664 << as an extension point.

665 """

666 return self.operate(lshift, other)

667

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

669 """implement the >> operator.

670

671 Not used by SQLAlchemy core, this is provided

672 for custom operator systems which want to use

673 >> as an extension point.

674 """

675 return self.operate(rshift, other)

676

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

678 """Implement the 'concat' operator.

679

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

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

682

683 """

684 return self.operate(concat_op, other)

685

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

687 """Implement an 'rconcat' operator.

688

689 this is for internal use at the moment

690

691 .. versionadded:: 1.4.40

692

693 """

694 return self.reverse_operate(concat_op, other)

695

696 def like(

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

698 ) -> ColumnOperators:

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

700

701 In a column context, produces the expression::

702

703 a LIKE other

704

705 E.g.::

706

707 stmt = select(sometable).\

708 where(sometable.c.column.like("%foobar%"))

709

710 :param other: expression to be compared

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

712 keyword, e.g.::

713

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

715

716 .. seealso::

717

718 :meth:`.ColumnOperators.ilike`

719

720 """

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

722

723 def ilike(

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

725 ) -> ColumnOperators:

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

727

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

729

730 lower(a) LIKE lower(other)

731

732 Or on backends that support the ILIKE operator::

733

734 a ILIKE other

735

736 E.g.::

737

738 stmt = select(sometable).\

739 where(sometable.c.column.ilike("%foobar%"))

740

741 :param other: expression to be compared

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

743 keyword, e.g.::

744

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

746

747 .. seealso::

748

749 :meth:`.ColumnOperators.like`

750

751 """

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

753

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

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

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

757

758 .. versionadded:: 2.0.2

759

760 .. seealso::

761

762 :ref:`operators_bitwise`

763

764 """

765

766 return self.operate(bitwise_xor_op, other)

767

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

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

770 operator.

771

772 .. versionadded:: 2.0.2

773

774 .. seealso::

775

776 :ref:`operators_bitwise`

777

778 """

779

780 return self.operate(bitwise_or_op, other)

781

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

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

784 operator.

785

786 .. versionadded:: 2.0.2

787

788 .. seealso::

789

790 :ref:`operators_bitwise`

791

792 """

793

794 return self.operate(bitwise_and_op, other)

795

796 def bitwise_not(self) -> ColumnOperators:

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

798 operator.

799

800 .. versionadded:: 2.0.2

801

802 .. seealso::

803

804 :ref:`operators_bitwise`

805

806 """

807

808 return self.operate(bitwise_not_op)

809

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

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

812 operator.

813

814 .. versionadded:: 2.0.2

815

816 .. seealso::

817

818 :ref:`operators_bitwise`

819

820 """

821

822 return self.operate(bitwise_lshift_op, other)

823

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

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

826 operator.

827

828 .. versionadded:: 2.0.2

829

830 .. seealso::

831

832 :ref:`operators_bitwise`

833

834 """

835

836 return self.operate(bitwise_rshift_op, other)

837

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

839 """Implement the ``in`` operator.

840

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

842

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

844

845 * A list of literal values, e.g.::

846

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

848

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

850 bound parameters the same length as the list given::

851

852 WHERE COL IN (?, ?, ?)

853

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

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

856

857 from sqlalchemy import tuple_

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

859

860 * An empty list, e.g.::

861

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

863

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

865 expression. These expressions are tailored to individual backends

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

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

868

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

870

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

872 execution-time generated SELECT subquery in all cases.

873

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

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

876

877 stmt.where(column.in_(bindparam('value', expanding=True)))

878

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

880 placeholder expression that looks like::

881

882 WHERE COL IN ([EXPANDING_value])

883

884 This placeholder expression is intercepted at statement execution

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

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

887

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

889

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

891

892 WHERE COL IN (?, ?, ?)

893

894 .. versionadded:: 1.2 added "expanding" bound parameters

895

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

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

898 SQLite this would be::

899

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

901

902 .. versionadded:: 1.3 "expanding" bound parameters now support

903 empty lists

904

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

906 correlated scalar select::

907

908 stmt.where(

909 column.in_(

910 select(othertable.c.y).

911 where(table.c.x == othertable.c.x)

912 )

913 )

914

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

916

917 WHERE COL IN (SELECT othertable.y

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

919

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

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

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

923

924 """

925 return self.operate(in_op, other)

926

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

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

929

930 This is equivalent to using negation with

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

932

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

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

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

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

937 alter this behavior.

938

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

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

941 available for backwards compatibility.

942

943 .. versionchanged:: 1.2 The :meth:`.ColumnOperators.in_` and

944 :meth:`.ColumnOperators.not_in` operators

945 now produce a "static" expression for an empty IN sequence

946 by default.

947

948 .. seealso::

949

950 :meth:`.ColumnOperators.in_`

951

952 """

953 return self.operate(not_in_op, other)

954

955 # deprecated 1.4; see #5429

956 if TYPE_CHECKING:

957

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

959

960 else:

961 notin_ = not_in

962

963 def not_like(

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

965 ) -> ColumnOperators:

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

967

968 This is equivalent to using negation with

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

970

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

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

973 available for backwards compatibility.

974

975 .. seealso::

976

977 :meth:`.ColumnOperators.like`

978

979 """

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

981

982 # deprecated 1.4; see #5435

983 if TYPE_CHECKING:

984

985 def notlike(

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

987 ) -> ColumnOperators: ...

988

989 else:

990 notlike = not_like

991

992 def not_ilike(

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

994 ) -> ColumnOperators:

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

996

997 This is equivalent to using negation with

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

999

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

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

1002 available for backwards compatibility.

1003

1004 .. seealso::

1005

1006 :meth:`.ColumnOperators.ilike`

1007

1008 """

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

1010

1011 # deprecated 1.4; see #5435

1012 if TYPE_CHECKING:

1013

1014 def notilike(

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

1016 ) -> ColumnOperators: ...

1017

1018 else:

1019 notilike = not_ilike

1020

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

1022 """Implement the ``IS`` operator.

1023

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

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

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

1027 on certain platforms.

1028

1029 .. seealso:: :meth:`.ColumnOperators.is_not`

1030

1031 """

1032 return self.operate(is_, other)

1033

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

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

1036

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

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

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

1040 on certain platforms.

1041

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

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

1044 available for backwards compatibility.

1045

1046 .. seealso:: :meth:`.ColumnOperators.is_`

1047

1048 """

1049 return self.operate(is_not, other)

1050

1051 # deprecated 1.4; see #5429

1052 if TYPE_CHECKING:

1053

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

1055

1056 else:

1057 isnot = is_not

1058

1059 def startswith(

1060 self,

1061 other: Any,

1062 escape: Optional[str] = None,

1063 autoescape: bool = False,

1064 ) -> ColumnOperators:

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

1066

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

1068 of a string value::

1069

1070 column LIKE <other> || '%'

1071

1072 E.g.::

1073

1074 stmt = select(sometable).\

1075 where(sometable.c.column.startswith("foobar"))

1076

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

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

1079 will behave like wildcards as well. For literal string

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

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

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

1083 and not as wildcard characters. Alternatively, the

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

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

1086 the target expression is not a literal string.

1087

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

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

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

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

1092 set to True.

1093

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

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

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

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

1098 SQL expression.

1099

1100 An expression such as::

1101

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

1103

1104 Will render as::

1105

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

1107

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

1109

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

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

1112 character. This character can then be placed preceding occurrences

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

1114 wildcard characters.

1115

1116 An expression such as::

1117

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

1119

1120 Will render as::

1121

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

1123

1124 The parameter may also be combined with

1125 :paramref:`.ColumnOperators.startswith.autoescape`::

1126

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

1128

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

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

1131

1132 .. seealso::

1133

1134 :meth:`.ColumnOperators.endswith`

1135

1136 :meth:`.ColumnOperators.contains`

1137

1138 :meth:`.ColumnOperators.like`

1139

1140 """

1141 return self.operate(

1142 startswith_op, other, escape=escape, autoescape=autoescape

1143 )

1144

1145 def istartswith(

1146 self,

1147 other: Any,

1148 escape: Optional[str] = None,

1149 autoescape: bool = False,

1150 ) -> ColumnOperators:

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

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

1153

1154 Produces a LIKE expression that tests against an insensitive

1155 match for the start of a string value::

1156

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

1158

1159 E.g.::

1160

1161 stmt = select(sometable).\

1162 where(sometable.c.column.istartswith("foobar"))

1163

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

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

1166 will behave like wildcards as well. For literal string

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

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

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

1170 and not as wildcard characters. Alternatively, the

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

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

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

1174

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

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

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

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

1179 set to True.

1180

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

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

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

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

1185 SQL expression.

1186

1187 An expression such as::

1188

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

1190

1191 Will render as::

1192

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

1194

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

1196

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

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

1199 character. This character can then be placed preceding occurrences

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

1201 wildcard characters.

1202

1203 An expression such as::

1204

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

1206

1207 Will render as::

1208

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

1210

1211 The parameter may also be combined with

1212 :paramref:`.ColumnOperators.istartswith.autoescape`::

1213

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

1215

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

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

1218

1219 .. seealso::

1220

1221 :meth:`.ColumnOperators.startswith`

1222 """

1223 return self.operate(

1224 istartswith_op, other, escape=escape, autoescape=autoescape

1225 )

1226

1227 def endswith(

1228 self,

1229 other: Any,

1230 escape: Optional[str] = None,

1231 autoescape: bool = False,

1232 ) -> ColumnOperators:

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

1234

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

1236 of a string value::

1237

1238 column LIKE '%' || <other>

1239

1240 E.g.::

1241

1242 stmt = select(sometable).\

1243 where(sometable.c.column.endswith("foobar"))

1244

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

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

1247 will behave like wildcards as well. For literal string

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

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

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

1251 and not as wildcard characters. Alternatively, the

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

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

1254 the target expression is not a literal string.

1255

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

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

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

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

1260 set to True.

1261

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

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

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

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

1266 SQL expression.

1267

1268 An expression such as::

1269

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

1271

1272 Will render as::

1273

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

1275

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

1277

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

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

1280 character. This character can then be placed preceding occurrences

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

1282 wildcard characters.

1283

1284 An expression such as::

1285

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

1287

1288 Will render as::

1289

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

1291

1292 The parameter may also be combined with

1293 :paramref:`.ColumnOperators.endswith.autoescape`::

1294

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

1296

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

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

1299

1300 .. seealso::

1301

1302 :meth:`.ColumnOperators.startswith`

1303

1304 :meth:`.ColumnOperators.contains`

1305

1306 :meth:`.ColumnOperators.like`

1307

1308 """

1309 return self.operate(

1310 endswith_op, other, escape=escape, autoescape=autoescape

1311 )

1312

1313 def iendswith(

1314 self,

1315 other: Any,

1316 escape: Optional[str] = None,

1317 autoescape: bool = False,

1318 ) -> ColumnOperators:

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

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

1321

1322 Produces a LIKE expression that tests against an insensitive match

1323 for the end of a string value::

1324

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

1326

1327 E.g.::

1328

1329 stmt = select(sometable).\

1330 where(sometable.c.column.iendswith("foobar"))

1331

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

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

1334 will behave like wildcards as well. For literal string

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

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

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

1338 and not as wildcard characters. Alternatively, the

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

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

1341 the target expression is not a literal string.

1342

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

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

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

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

1347 set to True.

1348

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

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

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

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

1353 SQL expression.

1354

1355 An expression such as::

1356

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

1358

1359 Will render as::

1360

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

1362

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

1364

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

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

1367 character. This character can then be placed preceding occurrences

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

1369 wildcard characters.

1370

1371 An expression such as::

1372

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

1374

1375 Will render as::

1376

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

1378

1379 The parameter may also be combined with

1380 :paramref:`.ColumnOperators.iendswith.autoescape`::

1381

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

1383

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

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

1386

1387 .. seealso::

1388

1389 :meth:`.ColumnOperators.endswith`

1390 """

1391 return self.operate(

1392 iendswith_op, other, escape=escape, autoescape=autoescape

1393 )

1394

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

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

1397

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

1399 of a string value::

1400

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

1402

1403 E.g.::

1404

1405 stmt = select(sometable).\

1406 where(sometable.c.column.contains("foobar"))

1407

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

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

1410 will behave like wildcards as well. For literal string

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

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

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

1414 and not as wildcard characters. Alternatively, the

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

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

1417 the target expression is not a literal string.

1418

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

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

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

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

1423 set to True.

1424

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

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

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

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

1429 SQL expression.

1430

1431 An expression such as::

1432

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

1434

1435 Will render as::

1436

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

1438

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

1440

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

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

1443 character. This character can then be placed preceding occurrences

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

1445 wildcard characters.

1446

1447 An expression such as::

1448

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

1450

1451 Will render as::

1452

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

1454

1455 The parameter may also be combined with

1456 :paramref:`.ColumnOperators.contains.autoescape`::

1457

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

1459

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

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

1462

1463 .. seealso::

1464

1465 :meth:`.ColumnOperators.startswith`

1466

1467 :meth:`.ColumnOperators.endswith`

1468

1469 :meth:`.ColumnOperators.like`

1470

1471

1472 """

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

1474

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

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

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

1478

1479 Produces a LIKE expression that tests against an insensitive match

1480 for the middle of a string value::

1481

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

1483

1484 E.g.::

1485

1486 stmt = select(sometable).\

1487 where(sometable.c.column.icontains("foobar"))

1488

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

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

1491 will behave like wildcards as well. For literal string

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

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

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

1495 and not as wildcard characters. Alternatively, the

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

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

1498 the target expression is not a literal string.

1499

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

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

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

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

1504 set to True.

1505

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

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

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

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

1510 SQL expression.

1511

1512 An expression such as::

1513

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

1515

1516 Will render as::

1517

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

1519

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

1521

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

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

1524 character. This character can then be placed preceding occurrences

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

1526 wildcard characters.

1527

1528 An expression such as::

1529

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

1531

1532 Will render as::

1533

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

1535

1536 The parameter may also be combined with

1537 :paramref:`.ColumnOperators.contains.autoescape`::

1538

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

1540

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

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

1543

1544 .. seealso::

1545

1546 :meth:`.ColumnOperators.contains`

1547

1548 """

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

1550

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

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

1553

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

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

1556 Examples include:

1557

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

1559

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

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

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

1563

1564

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

1566

1567 .. seealso::

1568

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

1570 additional features.

1571

1572 * Oracle - renders ``CONTAINS(x, y)``

1573 * other backends may provide special implementations.

1574 * Backends without any special implementation will emit

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

1576 example.

1577

1578 """

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

1580

1581 def regexp_match(

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

1583 ) -> ColumnOperators:

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

1585

1586 E.g.::

1587

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

1589 table.c.some_column.regexp_match('^(b|c)')

1590 )

1591

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

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

1594 the specific regular expression syntax and flags available are

1595 **not backend agnostic**.

1596

1597 Examples include:

1598

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

1600 * Oracle - renders ``REGEXP_LIKE(x, y)``

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

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

1603 * other backends may provide special implementations.

1604 * Backends without any special implementation will emit

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

1606 SQLite and MySQL, for example.

1607

1608 Regular expression support is currently implemented for Oracle,

1609 PostgreSQL, MySQL and MariaDB. Partial support is available for

1610 SQLite. Support among third-party dialects may vary.

1611

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

1613 clause.

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

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

1616 Some backends, like PostgreSQL and MariaDB, may alternatively

1617 specify the flags as part of the pattern.

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

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

1620

1621 .. versionadded:: 1.4

1622

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

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

1625 objects such as column expressions in addition to plain Python

1626 strings. This implementation did not work correctly with caching

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

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

1629 within SQL expressions.

1630

1631 .. seealso::

1632

1633 :meth:`_sql.ColumnOperators.regexp_replace`

1634

1635

1636 """

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

1638

1639 def regexp_replace(

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

1641 ) -> ColumnOperators:

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

1643

1644 E.g.::

1645

1646 stmt = select(

1647 table.c.some_column.regexp_replace(

1648 'b(..)',

1649 'X\1Y',

1650 flags='g'

1651 )

1652 )

1653

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

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

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

1657 the specific regular expression syntax and flags available are

1658 **not backend agnostic**.

1659

1660 Regular expression replacement support is currently implemented for

1661 Oracle, PostgreSQL, MySQL 8 or greater and MariaDB. Support among

1662 third-party dialects may vary.

1663

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

1665 clause.

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

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

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

1669 Some backends, like PostgreSQL and MariaDB, may alternatively

1670 specify the flags as part of the pattern.

1671

1672 .. versionadded:: 1.4

1673

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

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

1676 objects such as column expressions in addition to plain Python

1677 strings. This implementation did not work correctly with caching

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

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

1680 within SQL expressions.

1681

1682

1683 .. seealso::

1684

1685 :meth:`_sql.ColumnOperators.regexp_match`

1686

1687 """

1688 return self.operate(

1689 regexp_replace_op,

1690 pattern,

1691 replacement=replacement,

1692 flags=flags,

1693 )

1694

1695 def desc(self) -> ColumnOperators:

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

1697 parent object."""

1698 return self.operate(desc_op)

1699

1700 def asc(self) -> ColumnOperators:

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

1702 parent object."""

1703 return self.operate(asc_op)

1704

1705 def nulls_first(self) -> ColumnOperators:

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

1707 parent object.

1708

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

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

1711 The previous name remains available for backwards compatibility.

1712 """

1713 return self.operate(nulls_first_op)

1714

1715 # deprecated 1.4; see #5435

1716 if TYPE_CHECKING:

1717

1718 def nullsfirst(self) -> ColumnOperators: ...

1719

1720 else:

1721 nullsfirst = nulls_first

1722

1723 def nulls_last(self) -> ColumnOperators:

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

1725 parent object.

1726

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

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

1729 The previous name remains available for backwards compatibility.

1730 """

1731 return self.operate(nulls_last_op)

1732

1733 # deprecated 1.4; see #5429

1734 if TYPE_CHECKING:

1735

1736 def nullslast(self) -> ColumnOperators: ...

1737

1738 else:

1739 nullslast = nulls_last

1740

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

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

1743 the parent object, given the collation string.

1744

1745 .. seealso::

1746

1747 :func:`_expression.collate`

1748

1749 """

1750 return self.operate(collate, collation)

1751

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

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

1754

1755 See :meth:`.ColumnOperators.__add__`.

1756

1757 """

1758 return self.reverse_operate(add, other)

1759

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

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

1762

1763 See :meth:`.ColumnOperators.__sub__`.

1764

1765 """

1766 return self.reverse_operate(sub, other)

1767

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

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

1770

1771 See :meth:`.ColumnOperators.__mul__`.

1772

1773 """

1774 return self.reverse_operate(mul, other)

1775

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

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

1778

1779 See :meth:`.ColumnOperators.__mod__`.

1780

1781 """

1782 return self.reverse_operate(mod, other)

1783

1784 def between(

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

1786 ) -> ColumnOperators:

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

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

1789

1790 """

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

1792

1793 def distinct(self) -> ColumnOperators:

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

1795 parent object.

1796

1797 """

1798 return self.operate(distinct_op)

1799

1800 def any_(self) -> ColumnOperators:

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

1802 parent object.

1803

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

1805

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

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

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

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

1810 different calling style.

1811

1812 """

1813 return self.operate(any_op)

1814

1815 def all_(self) -> ColumnOperators:

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

1817 parent object.

1818

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

1820

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

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

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

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

1825 different calling style.

1826

1827 """

1828 return self.operate(all_op)

1829

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

1831 """Implement the ``+`` operator.

1832

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

1834 if the parent object has non-string affinity.

1835 If the parent object has a string affinity,

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

1837 see :meth:`.ColumnOperators.concat`.

1838

1839 """

1840 return self.operate(add, other)

1841

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

1843 """Implement the ``-`` operator.

1844

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

1846

1847 """

1848 return self.operate(sub, other)

1849

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

1851 """Implement the ``*`` operator.

1852

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

1854

1855 """

1856 return self.operate(mul, other)

1857

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

1859 """Implement the ``%`` operator.

1860

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

1862

1863 """

1864 return self.operate(mod, other)

1865

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

1867 """Implement the ``/`` operator.

1868

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

1870 considers the result type to be numeric.

1871

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

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

1874 backends may vary.

1875

1876 """

1877 return self.operate(truediv, other)

1878

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

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

1881

1882 See :meth:`.ColumnOperators.__truediv__`.

1883

1884 """

1885 return self.reverse_operate(truediv, other)

1886

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

1888 """Implement the ``//`` operator.

1889

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

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

1892 type to be integer.

1893

1894 .. versionadded:: 2.0

1895

1896 """

1897 return self.operate(floordiv, other)

1898

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

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

1901

1902 See :meth:`.ColumnOperators.__floordiv__`.

1903

1904 """

1905 return self.reverse_operate(floordiv, other)

1906

1907

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

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

1910

1911

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

1913 return cast(OperatorType, fn)

1914

1915

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

1917 _commutative.add(fn)

1918 return fn

1919

1920

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

1922 _comparison.add(fn)

1923 return fn

1924

1925

1926@_operator_fn

1927def from_() -> Any:

1928 raise NotImplementedError()

1929

1930

1931@_operator_fn

1932@comparison_op

1933def function_as_comparison_op() -> Any:

1934 raise NotImplementedError()

1935

1936

1937@_operator_fn

1938def as_() -> Any:

1939 raise NotImplementedError()

1940

1941

1942@_operator_fn

1943def exists() -> Any:

1944 raise NotImplementedError()

1945

1946

1947@_operator_fn

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

1949 raise NotImplementedError()

1950

1951

1952# 1.4 deprecated; see #5435

1953if TYPE_CHECKING:

1954

1955 @_operator_fn

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

1957

1958else:

1959 istrue = is_true

1960

1961

1962@_operator_fn

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

1964 raise NotImplementedError()

1965

1966

1967# 1.4 deprecated; see #5435

1968if TYPE_CHECKING:

1969

1970 @_operator_fn

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

1972

1973else:

1974 isfalse = is_false

1975

1976

1977@comparison_op

1978@_operator_fn

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

1980 return a.is_distinct_from(b)

1981

1982

1983@comparison_op

1984@_operator_fn

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

1986 return a.is_not_distinct_from(b)

1987

1988

1989# deprecated 1.4; see #5435

1990if TYPE_CHECKING:

1991

1992 @_operator_fn

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

1994

1995else:

1996 isnot_distinct_from = is_not_distinct_from

1997

1998

1999@comparison_op

2000@_operator_fn

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

2002 return a.is_(b)

2003

2004

2005@comparison_op

2006@_operator_fn

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

2008 return a.is_not(b)

2009

2010

2011# 1.4 deprecated; see #5429

2012if TYPE_CHECKING:

2013

2014 @_operator_fn

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

2016

2017else:

2018 isnot = is_not

2019

2020

2021@_operator_fn

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

2023 return a.collate(b)

2024

2025

2026@_operator_fn

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

2028 return a.op(opstring)(b)

2029

2030

2031@comparison_op

2032@_operator_fn

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

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

2035

2036

2037@comparison_op

2038@_operator_fn

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

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

2041

2042

2043# 1.4 deprecated; see #5435

2044if TYPE_CHECKING:

2045

2046 @_operator_fn

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

2048

2049else:

2050 notlike_op = not_like_op

2051

2052

2053@comparison_op

2054@_operator_fn

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

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

2057

2058

2059@comparison_op

2060@_operator_fn

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

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

2063

2064

2065# 1.4 deprecated; see #5435

2066if TYPE_CHECKING:

2067

2068 @_operator_fn

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

2070

2071else:

2072 notilike_op = not_ilike_op

2073

2074

2075@comparison_op

2076@_operator_fn

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

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

2079

2080

2081@comparison_op

2082@_operator_fn

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

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

2085

2086

2087# 1.4 deprecated; see #5435

2088if TYPE_CHECKING:

2089

2090 @_operator_fn

2091 def notbetween_op(

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

2093 ) -> Any: ...

2094

2095else:

2096 notbetween_op = not_between_op

2097

2098

2099@comparison_op

2100@_operator_fn

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

2102 return a.in_(b)

2103

2104

2105@comparison_op

2106@_operator_fn

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

2108 return a.not_in(b)

2109

2110

2111# 1.4 deprecated; see #5429

2112if TYPE_CHECKING:

2113

2114 @_operator_fn

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

2116

2117else:

2118 notin_op = not_in_op

2119

2120

2121@_operator_fn

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

2123 return a.distinct()

2124

2125

2126@_operator_fn

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

2128 return a.any_()

2129

2130

2131@_operator_fn

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

2133 return a.all_()

2134

2135

2136def _escaped_like_impl(

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

2138) -> Any:

2139 if autoescape:

2140 if autoescape is not True:

2141 util.warn(

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

2143 )

2144 if escape is None:

2145 escape = "/"

2146

2147 if not isinstance(other, str):

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

2149

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

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

2152

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

2154

2155 return fn(other, escape=escape)

2156

2157

2158@comparison_op

2159@_operator_fn

2160def startswith_op(

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

2162) -> Any:

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

2164

2165

2166@comparison_op

2167@_operator_fn

2168def not_startswith_op(

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

2170) -> Any:

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

2172

2173

2174# 1.4 deprecated; see #5435

2175if TYPE_CHECKING:

2176

2177 @_operator_fn

2178 def notstartswith_op(

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

2180 ) -> Any: ...

2181

2182else:

2183 notstartswith_op = not_startswith_op

2184

2185

2186@comparison_op

2187@_operator_fn

2188def istartswith_op(

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

2190) -> Any:

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

2192

2193

2194@comparison_op

2195@_operator_fn

2196def not_istartswith_op(

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

2198) -> Any:

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

2200

2201

2202@comparison_op

2203@_operator_fn

2204def endswith_op(

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

2206) -> Any:

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

2208

2209

2210@comparison_op

2211@_operator_fn

2212def not_endswith_op(

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

2214) -> Any:

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

2216

2217

2218# 1.4 deprecated; see #5435

2219if TYPE_CHECKING:

2220

2221 @_operator_fn

2222 def notendswith_op(

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

2224 ) -> Any: ...

2225

2226else:

2227 notendswith_op = not_endswith_op

2228

2229

2230@comparison_op

2231@_operator_fn

2232def iendswith_op(

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

2234) -> Any:

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

2236

2237

2238@comparison_op

2239@_operator_fn

2240def not_iendswith_op(

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

2242) -> Any:

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

2244

2245

2246@comparison_op

2247@_operator_fn

2248def contains_op(

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

2250) -> Any:

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

2252

2253

2254@comparison_op

2255@_operator_fn

2256def not_contains_op(

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

2258) -> Any:

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

2260

2261

2262# 1.4 deprecated; see #5435

2263if TYPE_CHECKING:

2264

2265 @_operator_fn

2266 def notcontains_op(

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

2268 ) -> Any: ...

2269

2270else:

2271 notcontains_op = not_contains_op

2272

2273

2274@comparison_op

2275@_operator_fn

2276def icontains_op(

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

2278) -> Any:

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

2280

2281

2282@comparison_op

2283@_operator_fn

2284def not_icontains_op(

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

2286) -> Any:

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

2288

2289

2290@comparison_op

2291@_operator_fn

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

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

2294

2295

2296@comparison_op

2297@_operator_fn

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

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

2300

2301

2302@comparison_op

2303@_operator_fn

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

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

2306

2307

2308@_operator_fn

2309def regexp_replace_op(

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

2311) -> Any:

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

2313

2314

2315@comparison_op

2316@_operator_fn

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

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

2319

2320

2321# 1.4 deprecated; see #5429

2322if TYPE_CHECKING:

2323

2324 @_operator_fn

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

2326

2327else:

2328 notmatch_op = not_match_op

2329

2330

2331@_operator_fn

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

2333 raise NotImplementedError()

2334

2335

2336@_operator_fn

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

2338 raise NotImplementedError()

2339

2340

2341@_operator_fn

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

2343 try:

2344 concat = a.concat

2345 except AttributeError:

2346 return b._rconcat(a)

2347 else:

2348 return concat(b)

2349

2350

2351@_operator_fn

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

2353 return a.desc()

2354

2355

2356@_operator_fn

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

2358 return a.asc()

2359

2360

2361@_operator_fn

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

2363 return a.nulls_first()

2364

2365

2366# 1.4 deprecated; see #5435

2367if TYPE_CHECKING:

2368

2369 @_operator_fn

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

2371

2372else:

2373 nullsfirst_op = nulls_first_op

2374

2375

2376@_operator_fn

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

2378 return a.nulls_last()

2379

2380

2381# 1.4 deprecated; see #5435

2382if TYPE_CHECKING:

2383

2384 @_operator_fn

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

2386

2387else:

2388 nullslast_op = nulls_last_op

2389

2390

2391@_operator_fn

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

2393 raise NotImplementedError()

2394

2395

2396@_operator_fn

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

2398 raise NotImplementedError()

2399

2400

2401@_operator_fn

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

2403 return a.bitwise_xor(b)

2404

2405

2406@_operator_fn

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

2408 return a.bitwise_or(b)

2409

2410

2411@_operator_fn

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

2413 return a.bitwise_and(b)

2414

2415

2416@_operator_fn

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

2418 return a.bitwise_not()

2419

2420

2421@_operator_fn

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

2423 return a.bitwise_lshift(b)

2424

2425

2426@_operator_fn

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

2428 return a.bitwise_rshift(b)

2429

2430

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

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

2433

2434

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

2436 return op in _commutative

2437

2438

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

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

2441

2442

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

2444 return (

2445 op in _natural_self_precedent

2446 or isinstance(op, custom_op)

2447 and op.natural_self_precedent

2448 )

2449

2450

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

2452

2453

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

2455 return is_comparison(op) or op in _booleans

2456

2457

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

2459

2460

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

2462 """rotate a comparison operator 180 degrees.

2463

2464 Note this is not the same as negation.

2465

2466 """

2467 return _mirror.get(op, op)

2468

2469

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

2471

2472

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

2474 return op in _associative

2475

2476

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

2478 return op in _order_by_modifier

2479

2480

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

2482

2483_natural_self_precedent = _associative.union(

2484 [getitem, json_getitem_op, json_path_getitem_op]

2485)

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

2487parenthesize (a op b).

2488

2489"""

2490

2491

2492@_operator_fn

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

2494 raise NotImplementedError()

2495

2496

2497class _OpLimit(IntEnum):

2498 _smallest = -100

2499 _largest = 100

2500

2501

2502_PRECEDENCE: Dict[OperatorType, int] = {

2503 from_: 15,

2504 function_as_comparison_op: 15,

2505 any_op: 15,

2506 all_op: 15,

2507 getitem: 15,

2508 json_getitem_op: 15,

2509 json_path_getitem_op: 15,

2510 mul: 8,

2511 truediv: 8,

2512 floordiv: 8,

2513 mod: 8,

2514 neg: 8,

2515 bitwise_not_op: 8,

2516 add: 7,

2517 sub: 7,

2518 bitwise_xor_op: 7,

2519 bitwise_or_op: 7,

2520 bitwise_and_op: 7,

2521 bitwise_lshift_op: 7,

2522 bitwise_rshift_op: 7,

2523 filter_op: 6,

2524 concat_op: 5,

2525 match_op: 5,

2526 not_match_op: 5,

2527 regexp_match_op: 5,

2528 not_regexp_match_op: 5,

2529 regexp_replace_op: 5,

2530 ilike_op: 5,

2531 not_ilike_op: 5,

2532 like_op: 5,

2533 not_like_op: 5,

2534 in_op: 5,

2535 not_in_op: 5,

2536 is_: 5,

2537 is_not: 5,

2538 eq: 5,

2539 ne: 5,

2540 is_distinct_from: 5,

2541 is_not_distinct_from: 5,

2542 gt: 5,

2543 lt: 5,

2544 ge: 5,

2545 le: 5,

2546 between_op: 5,

2547 not_between_op: 5,

2548 distinct_op: 5,

2549 inv: 5,

2550 is_true: 5,

2551 is_false: 5,

2552 and_: 3,

2553 or_: 2,

2554 comma_op: -1,

2555 desc_op: 3,

2556 asc_op: 3,

2557 collate: 4,

2558 as_: -1,

2559 exists: 0,

2560 _asbool: -10,

2561}

2562

2563

2564def is_precedent(

2565 operator: OperatorType, against: Optional[OperatorType]

2566) -> bool:

2567 if operator is against and is_natural_self_precedent(operator):

2568 return False

2569 elif against is None:

2570 return True

2571 else:

2572 return bool(

2573 _PRECEDENCE.get(

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

2575 )

2576 <= _PRECEDENCE.get(

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

2578 )

2579 )