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

1# sql/operators.py 

2# Copyright (C) 2005-2023 the SQLAlchemy authors and contributors 

3# <see AUTHORS file> 

4# 

5# This module is part of SQLAlchemy and is released under 

6# the MIT License: https://www.opensource.org/licenses/mit-license.php 

7 

8# This module is part of SQLAlchemy and is released under 

9# the MIT License: https://www.opensource.org/licenses/mit-license.php 

10 

11"""Defines operators used in SQL expressions.""" 

12 

13from operator import add 

14from operator import and_ 

15from operator import contains 

16from operator import eq 

17from operator import ge 

18from operator import getitem 

19from operator import gt 

20from operator import inv 

21from operator import le 

22from operator import lshift 

23from operator import lt 

24from operator import mod 

25from operator import mul 

26from operator import ne 

27from operator import neg 

28from operator import or_ 

29from operator import rshift 

30from operator import sub 

31from operator import truediv 

32 

33from .. import util 

34 

35 

36if util.py2k: 

37 from operator import div 

38else: 

39 div = truediv 

40 

41 

42class Operators(object): 

43 """Base of comparison and logical operators. 

44 

45 Implements base methods 

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

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

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

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

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

51 

52 Usually is used via its most common subclass 

53 :class:`.ColumnOperators`. 

54 

55 """ 

56 

57 __slots__ = () 

58 

59 def __and__(self, other): 

60 """Implement the ``&`` operator. 

61 

62 When used with SQL expressions, results in an 

63 AND operation, equivalent to 

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

65 

66 a & b 

67 

68 is equivalent to:: 

69 

70 from sqlalchemy import and_ 

71 and_(a, b) 

72 

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

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

75 The operands should be enclosed in parenthesis if they contain 

76 further sub expressions:: 

77 

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

79 

80 """ 

81 return self.operate(and_, other) 

82 

83 def __or__(self, other): 

84 """Implement the ``|`` operator. 

85 

86 When used with SQL expressions, results in an 

87 OR operation, equivalent to 

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

89 

90 a | b 

91 

92 is equivalent to:: 

93 

94 from sqlalchemy import or_ 

95 or_(a, b) 

96 

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

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

99 The operands should be enclosed in parenthesis if they contain 

100 further sub expressions:: 

101 

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

103 

104 """ 

105 return self.operate(or_, other) 

106 

107 def __invert__(self): 

108 """Implement the ``~`` operator. 

109 

110 When used with SQL expressions, results in a 

111 NOT operation, equivalent to 

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

113 

114 ~a 

115 

116 is equivalent to:: 

117 

118 from sqlalchemy import not_ 

119 not_(a) 

120 

121 """ 

122 return self.operate(inv) 

123 

124 def op( 

125 self, opstring, precedence=0, is_comparison=False, return_type=None 

126 ): 

127 """Produce a generic operator function. 

128 

129 e.g.:: 

130 

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

132 

133 produces:: 

134 

135 somecolumn * 5 

136 

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

138 example:: 

139 

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

141 

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

143 

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

145 between this element and the expression passed to the 

146 generated function. 

147 

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

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

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

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

152 expression to be parenthesized when applied against another operator 

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

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

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

156 lower than or equal to all operators. 

157 

158 .. seealso:: 

159 

160 :ref:`faq_sql_expression_op_parenthesis` - detailed description 

161 of how the SQLAlchemy SQL compiler renders parenthesis 

162 

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

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

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

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

167 comparison operator when used in a custom join condition. 

168 

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

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

171 operator sets this parameter automatically. In SQLAlchemy 2.0 it 

172 will also provide for improved typing support. 

173 

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

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

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

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

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

179 type as the left-hand operand. 

180 

181 .. seealso:: 

182 

183 :meth:`.Operators.bool_op` 

184 

185 :ref:`types_operators` 

186 

187 :ref:`relationship_custom_operator` 

188 

189 """ 

190 operator = custom_op(opstring, precedence, is_comparison, return_type) 

191 

192 def against(other): 

193 return operator(self, other) 

194 

195 return against 

196 

197 def bool_op(self, opstring, precedence=0): 

198 """Return a custom boolean operator. 

199 

200 This method is shorthand for calling 

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

202 :paramref:`.Operators.op.is_comparison` 

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

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

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

206 

207 .. seealso:: 

208 

209 :meth:`.Operators.op` 

210 

211 """ 

212 return self.op(opstring, precedence=precedence, is_comparison=True) 

213 

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

215 r"""Operate on an argument. 

216 

217 This is the lowest level of operation, raises 

218 :class:`NotImplementedError` by default. 

219 

220 Overriding this on a subclass can allow common 

221 behavior to be applied to all operations. 

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

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

224 side:: 

225 

226 class MyComparator(ColumnOperators): 

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

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

229 

230 :param op: Operator callable. 

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

232 be a single scalar for most operations. 

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

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

235 

236 

237 """ 

238 raise NotImplementedError(str(op)) 

239 

240 def reverse_operate(self, op, other, **kwargs): 

241 """Reverse operate on an argument. 

242 

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

244 

245 """ 

246 raise NotImplementedError(str(op)) 

247 

248 

249class custom_op(object): 

250 """Represent a 'custom' operator. 

251 

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

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

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

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

256 to represent the "factorial" operation:: 

257 

258 from sqlalchemy.sql import UnaryExpression 

259 from sqlalchemy.sql import operators 

260 from sqlalchemy import Numeric 

261 

262 unary = UnaryExpression(table.c.somecolumn, 

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

264 type_=Numeric) 

265 

266 

267 .. seealso:: 

268 

269 :meth:`.Operators.op` 

270 

271 :meth:`.Operators.bool_op` 

272 

273 """ 

274 

275 __name__ = "custom_op" 

276 

277 def __init__( 

278 self, 

279 opstring, 

280 precedence=0, 

281 is_comparison=False, 

282 return_type=None, 

283 natural_self_precedent=False, 

284 eager_grouping=False, 

285 ): 

286 self.opstring = opstring 

287 self.precedence = precedence 

288 self.is_comparison = is_comparison 

289 self.natural_self_precedent = natural_self_precedent 

290 self.eager_grouping = eager_grouping 

291 self.return_type = ( 

292 return_type._to_instance(return_type) if return_type else None 

293 ) 

294 

295 def __eq__(self, other): 

296 return ( 

297 isinstance(other, custom_op) 

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

299 ) 

300 

301 def __hash__(self): 

302 return hash(self._hash_key()) 

303 

304 def _hash_key(self): 

305 return ( 

306 self.__class__, 

307 self.opstring, 

308 self.precedence, 

309 self.is_comparison, 

310 self.natural_self_precedent, 

311 self.eager_grouping, 

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

313 ) 

314 

315 def __call__(self, left, right, **kw): 

316 return left.operate(self, right, **kw) 

317 

318 

319class ColumnOperators(Operators): 

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

321 :class:`_expression.ColumnElement` expressions. 

322 

323 By default, all methods call down to 

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

325 passing in the appropriate operator function from the 

326 Python builtin ``operator`` module or 

327 a SQLAlchemy-specific operator function from 

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

329 the ``__eq__`` function:: 

330 

331 def __eq__(self, other): 

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

333 

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

335 

336 def eq(a, b): 

337 return a == b 

338 

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

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

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

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

343 construct. 

344 

345 .. seealso:: 

346 

347 :ref:`types_operators` 

348 

349 :attr:`.TypeEngine.comparator_factory` 

350 

351 :class:`.ColumnOperators` 

352 

353 :class:`.PropComparator` 

354 

355 """ 

356 

357 __slots__ = () 

358 

359 timetuple = None 

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

361 

362 def __lt__(self, other): 

363 """Implement the ``<`` operator. 

364 

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

366 

367 """ 

368 return self.operate(lt, other) 

369 

370 def __le__(self, other): 

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

372 

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

374 

375 """ 

376 return self.operate(le, other) 

377 

378 __hash__ = Operators.__hash__ 

379 

380 def __eq__(self, other): 

381 """Implement the ``==`` operator. 

382 

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

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

385 

386 """ 

387 return self.operate(eq, other) 

388 

389 def __ne__(self, other): 

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

391 

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

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

394 

395 """ 

396 return self.operate(ne, other) 

397 

398 def is_distinct_from(self, other): 

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

400 

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

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

403 

404 .. versionadded:: 1.1 

405 

406 """ 

407 return self.operate(is_distinct_from, other) 

408 

409 def is_not_distinct_from(self, other): 

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

411 

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

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

414 

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

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

417 The previous name remains available for backwards compatibility. 

418 

419 .. versionadded:: 1.1 

420 

421 """ 

422 return self.operate(is_not_distinct_from, other) 

423 

424 # deprecated 1.4; see #5435 

425 isnot_distinct_from = is_not_distinct_from 

426 

427 def __gt__(self, other): 

428 """Implement the ``>`` operator. 

429 

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

431 

432 """ 

433 return self.operate(gt, other) 

434 

435 def __ge__(self, other): 

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

437 

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

439 

440 """ 

441 return self.operate(ge, other) 

442 

443 def __neg__(self): 

444 """Implement the ``-`` operator. 

445 

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

447 

448 """ 

449 return self.operate(neg) 

450 

451 def __contains__(self, other): 

452 return self.operate(contains, other) 

453 

454 def __getitem__(self, index): 

455 """Implement the [] operator. 

456 

457 This can be used by some database-specific types 

458 such as PostgreSQL ARRAY and HSTORE. 

459 

460 """ 

461 return self.operate(getitem, index) 

462 

463 def __lshift__(self, other): 

464 """implement the << operator. 

465 

466 Not used by SQLAlchemy core, this is provided 

467 for custom operator systems which want to use 

468 << as an extension point. 

469 """ 

470 return self.operate(lshift, other) 

471 

472 def __rshift__(self, other): 

473 """implement the >> operator. 

474 

475 Not used by SQLAlchemy core, this is provided 

476 for custom operator systems which want to use 

477 >> as an extension point. 

478 """ 

479 return self.operate(rshift, other) 

480 

481 def concat(self, other): 

482 """Implement the 'concat' operator. 

483 

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

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

486 

487 """ 

488 return self.operate(concat_op, other) 

489 

490 def _rconcat(self, other): 

491 """Implement an 'rconcat' operator. 

492 

493 this is for internal use at the moment 

494 

495 .. versionadded:: 1.4.40 

496 

497 """ 

498 return self.reverse_operate(concat_op, other) 

499 

500 def like(self, other, escape=None): 

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

502 

503 In a column context, produces the expression:: 

504 

505 a LIKE other 

506 

507 E.g.:: 

508 

509 stmt = select(sometable).\ 

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

511 

512 :param other: expression to be compared 

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

514 keyword, e.g.:: 

515 

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

517 

518 .. seealso:: 

519 

520 :meth:`.ColumnOperators.ilike` 

521 

522 """ 

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

524 

525 def ilike(self, other, escape=None): 

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

527 

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

529 

530 lower(a) LIKE lower(other) 

531 

532 Or on backends that support the ILIKE operator:: 

533 

534 a ILIKE other 

535 

536 E.g.:: 

537 

538 stmt = select(sometable).\ 

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

540 

541 :param other: expression to be compared 

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

543 keyword, e.g.:: 

544 

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

546 

547 .. seealso:: 

548 

549 :meth:`.ColumnOperators.like` 

550 

551 """ 

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

553 

554 def in_(self, other): 

555 """Implement the ``in`` operator. 

556 

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

558 

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

560 

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

562 

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

564 

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

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

567 

568 WHERE COL IN (?, ?, ?) 

569 

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

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

572 

573 from sqlalchemy import tuple_ 

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

575 

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

577 

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

579 

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

581 expression. These expressions are tailored to individual backends 

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

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

584 

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

586 

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

588 execution-time generated SELECT subquery in all cases. 

589 

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

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

592 

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

594 

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

596 placeholder expression that looks like:: 

597 

598 WHERE COL IN ([EXPANDING_value]) 

599 

600 This placeholder expression is intercepted at statement execution 

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

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

603 

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

605 

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

607 

608 WHERE COL IN (?, ?, ?) 

609 

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

611 

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

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

614 SQLite this would be:: 

615 

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

617 

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

619 empty lists 

620 

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

622 correlated scalar select:: 

623 

624 stmt.where( 

625 column.in_( 

626 select(othertable.c.y). 

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

628 ) 

629 ) 

630 

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

632 

633 WHERE COL IN (SELECT othertable.y 

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

635 

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

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

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

639 

640 """ 

641 return self.operate(in_op, other) 

642 

643 def not_in(self, other): 

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

645 

646 This is equivalent to using negation with 

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

648 

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

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

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

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

653 alter this behavior. 

654 

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

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

657 available for backwards compatibility. 

658 

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

660 :meth:`.ColumnOperators.not_in` operators 

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

662 by default. 

663 

664 .. seealso:: 

665 

666 :meth:`.ColumnOperators.in_` 

667 

668 """ 

669 return self.operate(not_in_op, other) 

670 

671 # deprecated 1.4; see #5429 

672 notin_ = not_in 

673 

674 def not_like(self, other, escape=None): 

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

676 

677 This is equivalent to using negation with 

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

679 

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

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

682 available for backwards compatibility. 

683 

684 .. seealso:: 

685 

686 :meth:`.ColumnOperators.like` 

687 

688 """ 

689 return self.operate(notlike_op, other, escape=escape) 

690 

691 # deprecated 1.4; see #5435 

692 notlike = not_like 

693 

694 def not_ilike(self, other, escape=None): 

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

696 

697 This is equivalent to using negation with 

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

699 

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

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

702 available for backwards compatibility. 

703 

704 .. seealso:: 

705 

706 :meth:`.ColumnOperators.ilike` 

707 

708 """ 

709 return self.operate(notilike_op, other, escape=escape) 

710 

711 # deprecated 1.4; see #5435 

712 notilike = not_ilike 

713 

714 def is_(self, other): 

715 """Implement the ``IS`` operator. 

716 

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

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

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

720 on certain platforms. 

721 

722 .. seealso:: :meth:`.ColumnOperators.is_not` 

723 

724 """ 

725 return self.operate(is_, other) 

726 

727 def is_not(self, other): 

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

729 

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

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

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

733 on certain platforms. 

734 

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

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

737 available for backwards compatibility. 

738 

739 .. seealso:: :meth:`.ColumnOperators.is_` 

740 

741 """ 

742 return self.operate(is_not, other) 

743 

744 # deprecated 1.4; see #5429 

745 isnot = is_not 

746 

747 def startswith(self, other, **kwargs): 

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

749 

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

751 of a string value:: 

752 

753 column LIKE <other> || '%' 

754 

755 E.g.:: 

756 

757 stmt = select(sometable).\ 

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

759 

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

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

762 will behave like wildcards as well. For literal string 

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

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

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

766 and not as wildcard characters. Alternatively, the 

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

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

769 the target expression is not a literal string. 

770 

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

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

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

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

775 set to True. 

776 

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

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

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

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

781 SQL expression. 

782 

783 An expression such as:: 

784 

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

786 

787 Will render as:: 

788 

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

790 

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

792 

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

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

795 character. This character can then be placed preceding occurrences 

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

797 wildcard characters. 

798 

799 An expression such as:: 

800 

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

802 

803 Will render as:: 

804 

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

806 

807 The parameter may also be combined with 

808 :paramref:`.ColumnOperators.startswith.autoescape`:: 

809 

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

811 

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

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

814 

815 .. seealso:: 

816 

817 :meth:`.ColumnOperators.endswith` 

818 

819 :meth:`.ColumnOperators.contains` 

820 

821 :meth:`.ColumnOperators.like` 

822 

823 """ 

824 return self.operate(startswith_op, other, **kwargs) 

825 

826 def endswith(self, other, **kwargs): 

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

828 

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

830 of a string value:: 

831 

832 column LIKE '%' || <other> 

833 

834 E.g.:: 

835 

836 stmt = select(sometable).\ 

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

838 

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

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

841 will behave like wildcards as well. For literal string 

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

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

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

845 and not as wildcard characters. Alternatively, the 

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

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

848 the target expression is not a literal string. 

849 

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

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

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

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

854 set to True. 

855 

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

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

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

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

860 SQL expression. 

861 

862 An expression such as:: 

863 

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

865 

866 Will render as:: 

867 

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

869 

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

871 

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

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

874 character. This character can then be placed preceding occurrences 

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

876 wildcard characters. 

877 

878 An expression such as:: 

879 

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

881 

882 Will render as:: 

883 

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

885 

886 The parameter may also be combined with 

887 :paramref:`.ColumnOperators.endswith.autoescape`:: 

888 

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

890 

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

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

893 

894 .. seealso:: 

895 

896 :meth:`.ColumnOperators.startswith` 

897 

898 :meth:`.ColumnOperators.contains` 

899 

900 :meth:`.ColumnOperators.like` 

901 

902 """ 

903 return self.operate(endswith_op, other, **kwargs) 

904 

905 def contains(self, other, **kwargs): 

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

907 

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

909 of a string value:: 

910 

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

912 

913 E.g.:: 

914 

915 stmt = select(sometable).\ 

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

917 

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

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

920 will behave like wildcards as well. For literal string 

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

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

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

924 and not as wildcard characters. Alternatively, the 

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

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

927 the target expression is not a literal string. 

928 

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

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

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

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

933 set to True. 

934 

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

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

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

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

939 SQL expression. 

940 

941 An expression such as:: 

942 

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

944 

945 Will render as:: 

946 

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

948 

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

950 

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

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

953 character. This character can then be placed preceding occurrences 

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

955 wildcard characters. 

956 

957 An expression such as:: 

958 

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

960 

961 Will render as:: 

962 

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

964 

965 The parameter may also be combined with 

966 :paramref:`.ColumnOperators.contains.autoescape`:: 

967 

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

969 

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

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

972 

973 .. seealso:: 

974 

975 :meth:`.ColumnOperators.startswith` 

976 

977 :meth:`.ColumnOperators.endswith` 

978 

979 :meth:`.ColumnOperators.like` 

980 

981 

982 """ 

983 return self.operate(contains_op, other, **kwargs) 

984 

985 def match(self, other, **kwargs): 

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

987 

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

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

990 Examples include: 

991 

992 * PostgreSQL - renders ``x @@ to_tsquery(y)`` 

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

994 

995 .. seealso:: 

996 

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

998 additional features. 

999 

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

1001 * other backends may provide special implementations. 

1002 * Backends without any special implementation will emit 

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

1004 example. 

1005 

1006 """ 

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

1008 

1009 def regexp_match(self, pattern, flags=None): 

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

1011 

1012 E.g.:: 

1013 

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

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

1016 ) 

1017 

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

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

1020 the specific regular expression syntax and flags available are 

1021 **not backend agnostic**. 

1022 

1023 Examples include: 

1024 

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

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

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

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

1029 * other backends may provide special implementations. 

1030 * Backends without any special implementation will emit 

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

1032 SQLite and MySQL, for example. 

1033 

1034 Regular expression support is currently implemented for Oracle, 

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

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

1037 

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

1039 clause. 

1040 :param flags: Any regular expression string flags to apply. Flags 

1041 tend to be backend specific. It can be a string or a column clause. 

1042 Some backends, like PostgreSQL and MariaDB, may alternatively 

1043 specify the flags as part of the pattern. 

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

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

1046 

1047 .. versionadded:: 1.4 

1048 

1049 .. seealso:: 

1050 

1051 :meth:`_sql.ColumnOperators.regexp_replace` 

1052 

1053 

1054 """ 

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

1056 

1057 def regexp_replace(self, pattern, replacement, flags=None): 

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

1059 

1060 E.g.:: 

1061 

1062 stmt = select( 

1063 table.c.some_column.regexp_replace( 

1064 'b(..)', 

1065 'X\1Y', 

1066 flags='g' 

1067 ) 

1068 ) 

1069 

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

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

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

1073 the specific regular expression syntax and flags available are 

1074 **not backend agnostic**. 

1075 

1076 Regular expression replacement support is currently implemented for 

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

1078 third-party dialects may vary. 

1079 

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

1081 clause. 

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

1083 :param flags: Any regular expression string flags to apply. Flags 

1084 tend to be backend specific. It can be a string or a column clause. 

1085 Some backends, like PostgreSQL and MariaDB, may alternatively 

1086 specify the flags as part of the pattern. 

1087 

1088 .. versionadded:: 1.4 

1089 

1090 .. seealso:: 

1091 

1092 :meth:`_sql.ColumnOperators.regexp_match` 

1093 

1094 """ 

1095 return self.operate( 

1096 regexp_replace_op, pattern, replacement=replacement, flags=flags 

1097 ) 

1098 

1099 def desc(self): 

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

1101 parent object.""" 

1102 return self.operate(desc_op) 

1103 

1104 def asc(self): 

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

1106 parent object.""" 

1107 return self.operate(asc_op) 

1108 

1109 def nulls_first(self): 

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

1111 parent object. 

1112 

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

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

1115 The previous name remains available for backwards compatibility. 

1116 """ 

1117 return self.operate(nulls_first_op) 

1118 

1119 # deprecated 1.4; see #5435 

1120 nullsfirst = nulls_first 

1121 

1122 def nulls_last(self): 

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

1124 parent object. 

1125 

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

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

1128 The previous name remains available for backwards compatibility. 

1129 """ 

1130 return self.operate(nulls_last_op) 

1131 

1132 # deprecated 1.4; see #5429 

1133 nullslast = nulls_last 

1134 

1135 def collate(self, collation): 

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

1137 the parent object, given the collation string. 

1138 

1139 .. seealso:: 

1140 

1141 :func:`_expression.collate` 

1142 

1143 """ 

1144 return self.operate(collate, collation) 

1145 

1146 def __radd__(self, other): 

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

1148 

1149 See :meth:`.ColumnOperators.__add__`. 

1150 

1151 """ 

1152 return self.reverse_operate(add, other) 

1153 

1154 def __rsub__(self, other): 

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

1156 

1157 See :meth:`.ColumnOperators.__sub__`. 

1158 

1159 """ 

1160 return self.reverse_operate(sub, other) 

1161 

1162 def __rmul__(self, other): 

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

1164 

1165 See :meth:`.ColumnOperators.__mul__`. 

1166 

1167 """ 

1168 return self.reverse_operate(mul, other) 

1169 

1170 def __rdiv__(self, other): 

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

1172 

1173 See :meth:`.ColumnOperators.__div__`. 

1174 

1175 """ 

1176 return self.reverse_operate(div, other) 

1177 

1178 def __rmod__(self, other): 

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

1180 

1181 See :meth:`.ColumnOperators.__mod__`. 

1182 

1183 """ 

1184 return self.reverse_operate(mod, other) 

1185 

1186 def between(self, cleft, cright, symmetric=False): 

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

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

1189 

1190 """ 

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

1192 

1193 def distinct(self): 

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

1195 parent object. 

1196 

1197 """ 

1198 return self.operate(distinct_op) 

1199 

1200 def any_(self): 

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

1202 parent object. 

1203 

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

1205 

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

1207 :meth:`_sql.ColumnOperators.any_` method with its older 

1208 :class:`_types.ARRAY`-specific counterpart, the 

1209 :meth:`_types.ARRAY.Comparator.any` method, which a different 

1210 calling syntax and usage pattern. 

1211 

1212 .. versionadded:: 1.1 

1213 

1214 """ 

1215 return self.operate(any_op)