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

Shortcuts on this page

r m x   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

1419 statements  

1# sql/elements.py 

2# Copyright (C) 2005-2024 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"""Core SQL expression elements, including :class:`_expression.ClauseElement`, 

9:class:`_expression.ColumnElement`, and derived classes. 

10 

11""" 

12 

13from __future__ import unicode_literals 

14 

15import itertools 

16import operator 

17import re 

18 

19from . import coercions 

20from . import operators 

21from . import roles 

22from . import traversals 

23from . import type_api 

24from .annotation import Annotated 

25from .annotation import SupportsWrappingAnnotations 

26from .base import _clone 

27from .base import _generative 

28from .base import Executable 

29from .base import HasMemoized 

30from .base import Immutable 

31from .base import NO_ARG 

32from .base import PARSE_AUTOCOMMIT 

33from .base import SingletonConstant 

34from .coercions import _document_text_coercion 

35from .traversals import HasCopyInternals 

36from .traversals import MemoizedHasCacheKey 

37from .traversals import NO_CACHE 

38from .visitors import cloned_traverse 

39from .visitors import InternalTraversal 

40from .visitors import traverse 

41from .visitors import Traversible 

42from .. import exc 

43from .. import inspection 

44from .. import util 

45 

46 

47def collate(expression, collation): 

48 """Return the clause ``expression COLLATE collation``. 

49 

50 e.g.:: 

51 

52 collate(mycolumn, 'utf8_bin') 

53 

54 produces:: 

55 

56 mycolumn COLLATE utf8_bin 

57 

58 The collation expression is also quoted if it is a case sensitive 

59 identifier, e.g. contains uppercase characters. 

60 

61 .. versionchanged:: 1.2 quoting is automatically applied to COLLATE 

62 expressions if they are case sensitive. 

63 

64 """ 

65 

66 expr = coercions.expect(roles.ExpressionElementRole, expression) 

67 return BinaryExpression( 

68 expr, CollationClause(collation), operators.collate, type_=expr.type 

69 ) 

70 

71 

72def between(expr, lower_bound, upper_bound, symmetric=False): 

73 """Produce a ``BETWEEN`` predicate clause. 

74 

75 E.g.:: 

76 

77 from sqlalchemy import between 

78 stmt = select(users_table).where(between(users_table.c.id, 5, 7)) 

79 

80 Would produce SQL resembling:: 

81 

82 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2 

83 

84 The :func:`.between` function is a standalone version of the 

85 :meth:`_expression.ColumnElement.between` method available on all 

86 SQL expressions, as in:: 

87 

88 stmt = select(users_table).where(users_table.c.id.between(5, 7)) 

89 

90 All arguments passed to :func:`.between`, including the left side 

91 column expression, are coerced from Python scalar values if a 

92 the value is not a :class:`_expression.ColumnElement` subclass. 

93 For example, 

94 three fixed values can be compared as in:: 

95 

96 print(between(5, 3, 7)) 

97 

98 Which would produce:: 

99 

100 :param_1 BETWEEN :param_2 AND :param_3 

101 

102 :param expr: a column expression, typically a 

103 :class:`_expression.ColumnElement` 

104 instance or alternatively a Python scalar expression to be coerced 

105 into a column expression, serving as the left side of the ``BETWEEN`` 

106 expression. 

107 

108 :param lower_bound: a column or Python scalar expression serving as the 

109 lower bound of the right side of the ``BETWEEN`` expression. 

110 

111 :param upper_bound: a column or Python scalar expression serving as the 

112 upper bound of the right side of the ``BETWEEN`` expression. 

113 

114 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note 

115 that not all databases support this syntax. 

116 

117 .. versionadded:: 0.9.5 

118 

119 .. seealso:: 

120 

121 :meth:`_expression.ColumnElement.between` 

122 

123 """ 

124 expr = coercions.expect(roles.ExpressionElementRole, expr) 

125 return expr.between(lower_bound, upper_bound, symmetric=symmetric) 

126 

127 

128def literal(value, type_=None): 

129 r"""Return a literal clause, bound to a bind parameter. 

130 

131 Literal clauses are created automatically when non- 

132 :class:`_expression.ClauseElement` objects (such as strings, ints, dates, 

133 etc.) are 

134 used in a comparison operation with a :class:`_expression.ColumnElement` 

135 subclass, 

136 such as a :class:`~sqlalchemy.schema.Column` object. Use this function 

137 to force the generation of a literal clause, which will be created as a 

138 :class:`BindParameter` with a bound value. 

139 

140 :param value: the value to be bound. Can be any Python object supported by 

141 the underlying DB-API, or is translatable via the given type argument. 

142 

143 :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` which 

144 will provide bind-parameter translation for this literal. 

145 

146 """ 

147 return coercions.expect(roles.LiteralValueRole, value, type_=type_) 

148 

149 

150def outparam(key, type_=None): 

151 r"""Create an 'OUT' parameter for usage in functions (stored procedures), 

152 for databases which support them. 

153 

154 The ``outparam`` can be used like a regular function parameter. 

155 The "output" value will be available from the 

156 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters`` 

157 attribute, which returns a dictionary containing the values. 

158 

159 """ 

160 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True) 

161 

162 

163def not_(clause): 

164 """Return a negation of the given clause, i.e. ``NOT(clause)``. 

165 

166 The ``~`` operator is also overloaded on all 

167 :class:`_expression.ColumnElement` subclasses to produce the 

168 same result. 

169 

170 """ 

171 return operators.inv(coercions.expect(roles.ExpressionElementRole, clause)) 

172 

173 

174@inspection._self_inspects 

175class ClauseElement( 

176 roles.SQLRole, 

177 SupportsWrappingAnnotations, 

178 MemoizedHasCacheKey, 

179 HasCopyInternals, 

180 Traversible, 

181): 

182 """Base class for elements of a programmatically constructed SQL 

183 expression. 

184 

185 """ 

186 

187 __visit_name__ = "clause" 

188 

189 _propagate_attrs = util.immutabledict() 

190 """like annotations, however these propagate outwards liberally 

191 as SQL constructs are built, and are set up at construction time. 

192 

193 """ 

194 

195 supports_execution = False 

196 

197 stringify_dialect = "default" 

198 

199 _from_objects = [] 

200 bind = None 

201 description = None 

202 _is_clone_of = None 

203 

204 is_clause_element = True 

205 is_selectable = False 

206 _gen_static_annotations_cache_key = False 

207 _is_table = False 

208 _is_textual = False 

209 _is_from_clause = False 

210 _is_returns_rows = False 

211 _is_text_clause = False 

212 _is_from_container = False 

213 _is_select_container = False 

214 _is_select_statement = False 

215 _is_bind_parameter = False 

216 _is_clause_list = False 

217 _is_lambda_element = False 

218 _is_singleton_constant = False 

219 _is_immutable = False 

220 _is_star = False 

221 

222 _order_by_label_element = None 

223 

224 _cache_key_traversal = None 

225 

226 def _set_propagate_attrs(self, values): 

227 # usually, self._propagate_attrs is empty here. one case where it's 

228 # not is a subquery against ORM select, that is then pulled as a 

229 # property of an aliased class. should all be good 

230 

231 # assert not self._propagate_attrs 

232 

233 self._propagate_attrs = util.immutabledict(values) 

234 return self 

235 

236 def _clone(self, **kw): 

237 """Create a shallow copy of this ClauseElement. 

238 

239 This method may be used by a generative API. Its also used as 

240 part of the "deep" copy afforded by a traversal that combines 

241 the _copy_internals() method. 

242 

243 """ 

244 skip = self._memoized_keys 

245 c = self.__class__.__new__(self.__class__) 

246 

247 if skip: 

248 # ensure this iteration remains atomic 

249 c.__dict__ = { 

250 k: v for k, v in self.__dict__.copy().items() if k not in skip 

251 } 

252 else: 

253 c.__dict__ = self.__dict__.copy() 

254 

255 # this is a marker that helps to "equate" clauses to each other 

256 # when a Select returns its list of FROM clauses. the cloning 

257 # process leaves around a lot of remnants of the previous clause 

258 # typically in the form of column expressions still attached to the 

259 # old table. 

260 cc = self._is_clone_of 

261 c._is_clone_of = cc if cc is not None else self 

262 return c 

263 

264 def _negate_in_binary(self, negated_op, original_op): 

265 """a hook to allow the right side of a binary expression to respond 

266 to a negation of the binary expression. 

267 

268 Used for the special case of expanding bind parameter with IN. 

269 

270 """ 

271 return self 

272 

273 def _with_binary_element_type(self, type_): 

274 """in the context of binary expression, convert the type of this 

275 object to the one given. 

276 

277 applies only to :class:`_expression.ColumnElement` classes. 

278 

279 """ 

280 return self 

281 

282 @property 

283 def _constructor(self): 

284 """return the 'constructor' for this ClauseElement. 

285 

286 This is for the purposes for creating a new object of 

287 this type. Usually, its just the element's __class__. 

288 However, the "Annotated" version of the object overrides 

289 to return the class of its proxied element. 

290 

291 """ 

292 return self.__class__ 

293 

294 @HasMemoized.memoized_attribute 

295 def _cloned_set(self): 

296 """Return the set consisting all cloned ancestors of this 

297 ClauseElement. 

298 

299 Includes this ClauseElement. This accessor tends to be used for 

300 FromClause objects to identify 'equivalent' FROM clauses, regardless 

301 of transformative operations. 

302 

303 """ 

304 s = util.column_set() 

305 f = self 

306 

307 # note this creates a cycle, asserted in test_memusage. however, 

308 # turning this into a plain @property adds tends of thousands of method 

309 # calls to Core / ORM performance tests, so the small overhead 

310 # introduced by the relatively small amount of short term cycles 

311 # produced here is preferable 

312 while f is not None: 

313 s.add(f) 

314 f = f._is_clone_of 

315 return s 

316 

317 @property 

318 def entity_namespace(self): 

319 raise AttributeError( 

320 "This SQL expression has no entity namespace " 

321 "with which to filter from." 

322 ) 

323 

324 def __getstate__(self): 

325 d = self.__dict__.copy() 

326 d.pop("_is_clone_of", None) 

327 d.pop("_generate_cache_key", None) 

328 return d 

329 

330 def _execute_on_connection( 

331 self, connection, multiparams, params, execution_options, _force=False 

332 ): 

333 if _force or self.supports_execution: 

334 return connection._execute_clauseelement( 

335 self, multiparams, params, execution_options 

336 ) 

337 else: 

338 raise exc.ObjectNotExecutableError(self) 

339 

340 def unique_params(self, *optionaldict, **kwargs): 

341 """Return a copy with :func:`_expression.bindparam` elements 

342 replaced. 

343 

344 Same functionality as :meth:`_expression.ClauseElement.params`, 

345 except adds `unique=True` 

346 to affected bind parameters so that multiple statements can be 

347 used. 

348 

349 """ 

350 return self._replace_params(True, optionaldict, kwargs) 

351 

352 def params(self, *optionaldict, **kwargs): 

353 """Return a copy with :func:`_expression.bindparam` elements 

354 replaced. 

355 

356 Returns a copy of this ClauseElement with 

357 :func:`_expression.bindparam` 

358 elements replaced with values taken from the given dictionary:: 

359 

360 >>> clause = column('x') + bindparam('foo') 

361 >>> print(clause.compile().params) 

362 {'foo':None} 

363 >>> print(clause.params({'foo':7}).compile().params) 

364 {'foo':7} 

365 

366 """ 

367 return self._replace_params(False, optionaldict, kwargs) 

368 

369 def _replace_params(self, unique, optionaldict, kwargs): 

370 

371 if len(optionaldict) == 1: 

372 kwargs.update(optionaldict[0]) 

373 elif len(optionaldict) > 1: 

374 raise exc.ArgumentError( 

375 "params() takes zero or one positional dictionary argument" 

376 ) 

377 

378 def visit_bindparam(bind): 

379 if bind.key in kwargs: 

380 bind.value = kwargs[bind.key] 

381 bind.required = False 

382 if unique: 

383 bind._convert_to_unique() 

384 

385 return cloned_traverse( 

386 self, 

387 {"maintain_key": True, "detect_subquery_cols": True}, 

388 {"bindparam": visit_bindparam}, 

389 ) 

390 

391 def compare(self, other, **kw): 

392 r"""Compare this :class:`_expression.ClauseElement` to 

393 the given :class:`_expression.ClauseElement`. 

394 

395 Subclasses should override the default behavior, which is a 

396 straight identity comparison. 

397 

398 \**kw are arguments consumed by subclass ``compare()`` methods and 

399 may be used to modify the criteria for comparison 

400 (see :class:`_expression.ColumnElement`). 

401 

402 """ 

403 return traversals.compare(self, other, **kw) 

404 

405 def self_group(self, against=None): 

406 """Apply a 'grouping' to this :class:`_expression.ClauseElement`. 

407 

408 This method is overridden by subclasses to return a "grouping" 

409 construct, i.e. parenthesis. In particular it's used by "binary" 

410 expressions to provide a grouping around themselves when placed into a 

411 larger expression, as well as by :func:`_expression.select` 

412 constructs when placed into the FROM clause of another 

413 :func:`_expression.select`. (Note that subqueries should be 

414 normally created using the :meth:`_expression.Select.alias` method, 

415 as many 

416 platforms require nested SELECT statements to be named). 

417 

418 As expressions are composed together, the application of 

419 :meth:`self_group` is automatic - end-user code should never 

420 need to use this method directly. Note that SQLAlchemy's 

421 clause constructs take operator precedence into account - 

422 so parenthesis might not be needed, for example, in 

423 an expression like ``x OR (y AND z)`` - AND takes precedence 

424 over OR. 

425 

426 The base :meth:`self_group` method of 

427 :class:`_expression.ClauseElement` 

428 just returns self. 

429 """ 

430 return self 

431 

432 def _ungroup(self): 

433 """Return this :class:`_expression.ClauseElement` 

434 without any groupings. 

435 """ 

436 

437 return self 

438 

439 @util.preload_module("sqlalchemy.engine.default") 

440 @util.preload_module("sqlalchemy.engine.url") 

441 def compile(self, bind=None, dialect=None, **kw): 

442 """Compile this SQL expression. 

443 

444 The return value is a :class:`~.Compiled` object. 

445 Calling ``str()`` or ``unicode()`` on the returned value will yield a 

446 string representation of the result. The 

447 :class:`~.Compiled` object also can return a 

448 dictionary of bind parameter names and values 

449 using the ``params`` accessor. 

450 

451 :param bind: An ``Engine`` or ``Connection`` from which a 

452 ``Compiled`` will be acquired. This argument takes precedence over 

453 this :class:`_expression.ClauseElement`'s bound engine, if any. 

454 

455 :param column_keys: Used for INSERT and UPDATE statements, a list of 

456 column names which should be present in the VALUES clause of the 

457 compiled statement. If ``None``, all columns from the target table 

458 object are rendered. 

459 

460 :param dialect: A ``Dialect`` instance from which a ``Compiled`` 

461 will be acquired. This argument takes precedence over the `bind` 

462 argument as well as this :class:`_expression.ClauseElement` 

463 's bound engine, 

464 if any. 

465 

466 :param compile_kwargs: optional dictionary of additional parameters 

467 that will be passed through to the compiler within all "visit" 

468 methods. This allows any custom flag to be passed through to 

469 a custom compilation construct, for example. It is also used 

470 for the case of passing the ``literal_binds`` flag through:: 

471 

472 from sqlalchemy.sql import table, column, select 

473 

474 t = table('t', column('x')) 

475 

476 s = select(t).where(t.c.x == 5) 

477 

478 print(s.compile(compile_kwargs={"literal_binds": True})) 

479 

480 .. versionadded:: 0.9.0 

481 

482 .. seealso:: 

483 

484 :ref:`faq_sql_expression_string` 

485 

486 """ 

487 

488 if not dialect: 

489 if bind: 

490 dialect = bind.dialect 

491 elif self.bind: 

492 dialect = self.bind.dialect 

493 else: 

494 if self.stringify_dialect == "default": 

495 default = util.preloaded.engine_default 

496 dialect = default.StrCompileDialect() 

497 else: 

498 url = util.preloaded.engine_url 

499 dialect = url.URL.create( 

500 self.stringify_dialect 

501 ).get_dialect()() 

502 

503 return self._compiler(dialect, **kw) 

504 

505 def _compile_w_cache( 

506 self, 

507 dialect, 

508 compiled_cache=None, 

509 column_keys=None, 

510 for_executemany=False, 

511 schema_translate_map=None, 

512 **kw 

513 ): 

514 if compiled_cache is not None and dialect._supports_statement_cache: 

515 elem_cache_key = self._generate_cache_key() 

516 else: 

517 elem_cache_key = None 

518 

519 if elem_cache_key: 

520 cache_key, extracted_params = elem_cache_key 

521 key = ( 

522 dialect, 

523 cache_key, 

524 tuple(column_keys), 

525 bool(schema_translate_map), 

526 for_executemany, 

527 ) 

528 compiled_sql = compiled_cache.get(key) 

529 

530 if compiled_sql is None: 

531 cache_hit = dialect.CACHE_MISS 

532 compiled_sql = self._compiler( 

533 dialect, 

534 cache_key=elem_cache_key, 

535 column_keys=column_keys, 

536 for_executemany=for_executemany, 

537 schema_translate_map=schema_translate_map, 

538 **kw 

539 ) 

540 compiled_cache[key] = compiled_sql 

541 else: 

542 cache_hit = dialect.CACHE_HIT 

543 else: 

544 extracted_params = None 

545 compiled_sql = self._compiler( 

546 dialect, 

547 cache_key=elem_cache_key, 

548 column_keys=column_keys, 

549 for_executemany=for_executemany, 

550 schema_translate_map=schema_translate_map, 

551 **kw 

552 ) 

553 

554 if not dialect._supports_statement_cache: 

555 cache_hit = dialect.NO_DIALECT_SUPPORT 

556 elif compiled_cache is None: 

557 cache_hit = dialect.CACHING_DISABLED 

558 else: 

559 cache_hit = dialect.NO_CACHE_KEY 

560 

561 return compiled_sql, extracted_params, cache_hit 

562 

563 def _compiler(self, dialect, **kw): 

564 """Return a compiler appropriate for this ClauseElement, given a 

565 Dialect.""" 

566 

567 return dialect.statement_compiler(dialect, self, **kw) 

568 

569 def __str__(self): 

570 if util.py3k: 

571 return str(self.compile()) 

572 else: 

573 return unicode(self.compile()).encode( # noqa 

574 "ascii", "backslashreplace" 

575 ) # noqa 

576 

577 def __invert__(self): 

578 # undocumented element currently used by the ORM for 

579 # relationship.contains() 

580 if hasattr(self, "negation_clause"): 

581 return self.negation_clause 

582 else: 

583 return self._negate() 

584 

585 def _negate(self): 

586 return UnaryExpression( 

587 self.self_group(against=operators.inv), operator=operators.inv 

588 ) 

589 

590 def __bool__(self): 

591 raise TypeError("Boolean value of this clause is not defined") 

592 

593 __nonzero__ = __bool__ 

594 

595 def __repr__(self): 

596 friendly = self.description 

597 if friendly is None: 

598 return object.__repr__(self) 

599 else: 

600 return "<%s.%s at 0x%x; %s>" % ( 

601 self.__module__, 

602 self.__class__.__name__, 

603 id(self), 

604 friendly, 

605 ) 

606 

607 

608class ColumnElement( 

609 roles.ColumnArgumentOrKeyRole, 

610 roles.StatementOptionRole, 

611 roles.WhereHavingRole, 

612 roles.BinaryElementRole, 

613 roles.OrderByRole, 

614 roles.ColumnsClauseRole, 

615 roles.LimitOffsetRole, 

616 roles.DMLColumnRole, 

617 roles.DDLConstraintColumnRole, 

618 roles.DDLExpressionRole, 

619 operators.ColumnOperators, 

620 ClauseElement, 

621): 

622 """Represent a column-oriented SQL expression suitable for usage in the 

623 "columns" clause, WHERE clause etc. of a statement. 

624 

625 While the most familiar kind of :class:`_expression.ColumnElement` is the 

626 :class:`_schema.Column` object, :class:`_expression.ColumnElement` 

627 serves as the basis 

628 for any unit that may be present in a SQL expression, including 

629 the expressions themselves, SQL functions, bound parameters, 

630 literal expressions, keywords such as ``NULL``, etc. 

631 :class:`_expression.ColumnElement` 

632 is the ultimate base class for all such elements. 

633 

634 A wide variety of SQLAlchemy Core functions work at the SQL expression 

635 level, and are intended to accept instances of 

636 :class:`_expression.ColumnElement` as 

637 arguments. These functions will typically document that they accept a 

638 "SQL expression" as an argument. What this means in terms of SQLAlchemy 

639 usually refers to an input which is either already in the form of a 

640 :class:`_expression.ColumnElement` object, 

641 or a value which can be **coerced** into 

642 one. The coercion rules followed by most, but not all, SQLAlchemy Core 

643 functions with regards to SQL expressions are as follows: 

644 

645 * a literal Python value, such as a string, integer or floating 

646 point value, boolean, datetime, ``Decimal`` object, or virtually 

647 any other Python object, will be coerced into a "literal bound 

648 value". This generally means that a :func:`.bindparam` will be 

649 produced featuring the given value embedded into the construct; the 

650 resulting :class:`.BindParameter` object is an instance of 

651 :class:`_expression.ColumnElement`. 

652 The Python value will ultimately be sent 

653 to the DBAPI at execution time as a parameterized argument to the 

654 ``execute()`` or ``executemany()`` methods, after SQLAlchemy 

655 type-specific converters (e.g. those provided by any associated 

656 :class:`.TypeEngine` objects) are applied to the value. 

657 

658 * any special object value, typically ORM-level constructs, which 

659 feature an accessor called ``__clause_element__()``. The Core 

660 expression system looks for this method when an object of otherwise 

661 unknown type is passed to a function that is looking to coerce the 

662 argument into a :class:`_expression.ColumnElement` and sometimes a 

663 :class:`_expression.SelectBase` expression. 

664 It is used within the ORM to 

665 convert from ORM-specific objects like mapped classes and 

666 mapped attributes into Core expression objects. 

667 

668 * The Python ``None`` value is typically interpreted as ``NULL``, 

669 which in SQLAlchemy Core produces an instance of :func:`.null`. 

670 

671 A :class:`_expression.ColumnElement` provides the ability to generate new 

672 :class:`_expression.ColumnElement` 

673 objects using Python expressions. This means that Python operators 

674 such as ``==``, ``!=`` and ``<`` are overloaded to mimic SQL operations, 

675 and allow the instantiation of further :class:`_expression.ColumnElement` 

676 instances 

677 which are composed from other, more fundamental 

678 :class:`_expression.ColumnElement` 

679 objects. For example, two :class:`.ColumnClause` objects can be added 

680 together with the addition operator ``+`` to produce 

681 a :class:`.BinaryExpression`. 

682 Both :class:`.ColumnClause` and :class:`.BinaryExpression` are subclasses 

683 of :class:`_expression.ColumnElement`:: 

684 

685 >>> from sqlalchemy.sql import column 

686 >>> column('a') + column('b') 

687 <sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0> 

688 >>> print(column('a') + column('b')) 

689 a + b 

690 

691 .. seealso:: 

692 

693 :class:`_schema.Column` 

694 

695 :func:`_expression.column` 

696 

697 """ 

698 

699 __visit_name__ = "column_element" 

700 primary_key = False 

701 foreign_keys = [] 

702 _proxies = () 

703 

704 _tq_label = None 

705 """The named label that can be used to target 

706 this column in a result set in a "table qualified" context. 

707 

708 This label is almost always the label used when 

709 rendering <expr> AS <label> in a SELECT statement when using 

710 the LABEL_STYLE_TABLENAME_PLUS_COL label style, which is what the legacy 

711 ORM ``Query`` object uses as well. 

712 

713 For a regular Column bound to a Table, this is typically the label 

714 <tablename>_<columnname>. For other constructs, different rules 

715 may apply, such as anonymized labels and others. 

716 

717 .. versionchanged:: 1.4.21 renamed from ``._label`` 

718 

719 """ 

720 

721 key = None 

722 """The 'key' that in some circumstances refers to this object in a 

723 Python namespace. 

724 

725 This typically refers to the "key" of the column as present in the 

726 ``.c`` collection of a selectable, e.g. ``sometable.c["somekey"]`` would 

727 return a :class:`_schema.Column` with a ``.key`` of "somekey". 

728 

729 """ 

730 

731 @HasMemoized.memoized_attribute 

732 def _tq_key_label(self): 

733 """A label-based version of 'key' that in some circumstances refers 

734 to this object in a Python namespace. 

735 

736 

737 _tq_key_label comes into play when a select() statement is constructed 

738 with apply_labels(); in this case, all Column objects in the ``.c`` 

739 collection are rendered as <tablename>_<columnname> in SQL; this is 

740 essentially the value of ._label. But to locate those columns in the 

741 ``.c`` collection, the name is along the lines of <tablename>_<key>; 

742 that's the typical value of .key_label. 

743 

744 .. versionchanged:: 1.4.21 renamed from ``._key_label`` 

745 

746 """ 

747 return self._proxy_key 

748 

749 @property 

750 def _key_label(self): 

751 """legacy; renamed to _tq_key_label""" 

752 return self._tq_key_label 

753 

754 @property 

755 def _label(self): 

756 """legacy; renamed to _tq_label""" 

757 return self._tq_label 

758 

759 @property 

760 def _non_anon_label(self): 

761 """the 'name' that naturally applies this element when rendered in 

762 SQL. 

763 

764 Concretely, this is the "name" of a column or a label in a 

765 SELECT statement; ``<columnname>`` and ``<labelname>`` below:: 

766 

767 SELECT <columnmame> FROM table 

768 

769 SELECT column AS <labelname> FROM table 

770 

771 Above, the two names noted will be what's present in the DBAPI 

772 ``cursor.description`` as the names. 

773 

774 If this attribute returns ``None``, it means that the SQL element as 

775 written does not have a 100% fully predictable "name" that would appear 

776 in the ``cursor.description``. Examples include SQL functions, CAST 

777 functions, etc. While such things do return names in 

778 ``cursor.description``, they are only predictable on a 

779 database-specific basis; e.g. an expression like ``MAX(table.col)`` may 

780 appear as the string ``max`` on one database (like PostgreSQL) or may 

781 appear as the whole expression ``max(table.col)`` on SQLite. 

782 

783 The default implementation looks for a ``.name`` attribute on the 

784 object, as has been the precedent established in SQLAlchemy for many 

785 years. An exception is made on the ``FunctionElement`` subclass 

786 so that the return value is always ``None``. 

787 

788 .. versionadded:: 1.4.21 

789 

790 

791 

792 """ 

793 return getattr(self, "name", None) 

794 

795 _render_label_in_columns_clause = True 

796 """A flag used by select._columns_plus_names that helps to determine 

797 we are actually going to render in terms of "SELECT <col> AS <label>". 

798 This flag can be returned as False for some Column objects that want 

799 to be rendered as simple "SELECT <col>"; typically columns that don't have 

800 any parent table and are named the same as what the label would be 

801 in any case. 

802 

803 """ 

804 

805 _allow_label_resolve = True 

806 """A flag that can be flipped to prevent a column from being resolvable 

807 by string label name. 

808 

809 The joined eager loader strategy in the ORM uses this, for example. 

810 

811 """ 

812 

813 _is_implicitly_boolean = False 

814 

815 _alt_names = () 

816 

817 def self_group(self, against=None): 

818 if ( 

819 against in (operators.and_, operators.or_, operators._asbool) 

820 and self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity 

821 ): 

822 return AsBoolean(self, operators.is_true, operators.is_false) 

823 elif against in (operators.any_op, operators.all_op): 

824 return Grouping(self) 

825 else: 

826 return self 

827 

828 def _negate(self): 

829 if self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity: 

830 return AsBoolean(self, operators.is_false, operators.is_true) 

831 else: 

832 return super(ColumnElement, self)._negate() 

833 

834 @util.memoized_property 

835 def type(self): 

836 return type_api.NULLTYPE 

837 

838 @HasMemoized.memoized_attribute 

839 def comparator(self): 

840 try: 

841 comparator_factory = self.type.comparator_factory 

842 except AttributeError as err: 

843 util.raise_( 

844 TypeError( 

845 "Object %r associated with '.type' attribute " 

846 "is not a TypeEngine class or object" % self.type 

847 ), 

848 replace_context=err, 

849 ) 

850 else: 

851 return comparator_factory(self) 

852 

853 def __setstate__(self, state): 

854 self.__dict__.update(state) 

855 

856 def __getattr__(self, key): 

857 try: 

858 return getattr(self.comparator, key) 

859 except AttributeError as err: 

860 util.raise_( 

861 AttributeError( 

862 "Neither %r object nor %r object has an attribute %r" 

863 % ( 

864 type(self).__name__, 

865 type(self.comparator).__name__, 

866 key, 

867 ) 

868 ), 

869 replace_context=err, 

870 ) 

871 

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

873 return op(self.comparator, *other, **kwargs) 

874 

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

876 return op(other, self.comparator, **kwargs) 

877 

878 def _bind_param(self, operator, obj, type_=None, expanding=False): 

879 return BindParameter( 

880 None, 

881 obj, 

882 _compared_to_operator=operator, 

883 type_=type_, 

884 _compared_to_type=self.type, 

885 unique=True, 

886 expanding=expanding, 

887 ) 

888 

889 @property 

890 def expression(self): 

891 """Return a column expression. 

892 

893 Part of the inspection interface; returns self. 

894 

895 """ 

896 return self 

897 

898 @property 

899 def _select_iterable(self): 

900 return (self,) 

901 

902 @util.memoized_property 

903 def base_columns(self): 

904 return util.column_set(c for c in self.proxy_set if not c._proxies) 

905 

906 @util.memoized_property 

907 def proxy_set(self): 

908 s = util.column_set([self._deannotate()]) 

909 for c in self._proxies: 

910 s.update(c.proxy_set) 

911 return s 

912 

913 def _uncached_proxy_list(self): 

914 """An 'uncached' version of proxy set. 

915 

916 This is so that we can read annotations from the list of columns 

917 without breaking the caching of the above proxy_set. 

918 

919 """ 

920 s = [self] 

921 for c in self._proxies: 

922 s.extend(c._uncached_proxy_list()) 

923 return s 

924 

925 def shares_lineage(self, othercolumn): 

926 """Return True if the given :class:`_expression.ColumnElement` 

927 has a common ancestor to this :class:`_expression.ColumnElement`.""" 

928 

929 return bool(self.proxy_set.intersection(othercolumn.proxy_set)) 

930 

931 def _compare_name_for_result(self, other): 

932 """Return True if the given column element compares to this one 

933 when targeting within a result row.""" 

934 

935 return ( 

936 hasattr(other, "name") 

937 and hasattr(self, "name") 

938 and other.name == self.name 

939 ) 

940 

941 @HasMemoized.memoized_attribute 

942 def _proxy_key(self): 

943 if self._annotations and "proxy_key" in self._annotations: 

944 return self._annotations["proxy_key"] 

945 

946 name = self.key 

947 if not name: 

948 # there's a bit of a seeming contradiction which is that the 

949 # "_non_anon_label" of a column can in fact be an 

950 # "_anonymous_label"; this is when it's on a column that is 

951 # proxying for an anonymous expression in a subquery. 

952 name = self._non_anon_label 

953 

954 if isinstance(name, _anonymous_label): 

955 return None 

956 else: 

957 return name 

958 

959 @HasMemoized.memoized_attribute 

960 def _expression_label(self): 

961 """a suggested label to use in the case that the column has no name, 

962 which should be used if possible as the explicit 'AS <label>' 

963 where this expression would normally have an anon label. 

964 

965 this is essentially mostly what _proxy_key does except it returns 

966 None if the column has a normal name that can be used. 

967 

968 """ 

969 

970 if getattr(self, "name", None) is not None: 

971 return None 

972 elif self._annotations and "proxy_key" in self._annotations: 

973 return self._annotations["proxy_key"] 

974 else: 

975 return None 

976 

977 def _make_proxy( 

978 self, selectable, name=None, key=None, name_is_truncatable=False, **kw 

979 ): 

980 """Create a new :class:`_expression.ColumnElement` representing this 

981 :class:`_expression.ColumnElement` as it appears in the select list of 

982 a descending selectable. 

983 

984 """ 

985 if name is None: 

986 name = self._anon_name_label 

987 if key is None: 

988 key = self._proxy_key 

989 else: 

990 key = name 

991 

992 co = ColumnClause( 

993 coercions.expect(roles.TruncatedLabelRole, name) 

994 if name_is_truncatable 

995 else name, 

996 type_=getattr(self, "type", None), 

997 _selectable=selectable, 

998 ) 

999 

1000 co._propagate_attrs = selectable._propagate_attrs 

1001 co._proxies = [self] 

1002 if selectable._is_clone_of is not None: 

1003 co._is_clone_of = selectable._is_clone_of.columns.get(key) 

1004 return key, co 

1005 

1006 def cast(self, type_): 

1007 """Produce a type cast, i.e. ``CAST(<expression> AS <type>)``. 

1008 

1009 This is a shortcut to the :func:`_expression.cast` function. 

1010 

1011 .. seealso:: 

1012 

1013 :ref:`tutorial_casts` 

1014 

1015 :func:`_expression.cast` 

1016 

1017 :func:`_expression.type_coerce` 

1018 

1019 .. versionadded:: 1.0.7 

1020 

1021 """ 

1022 return Cast(self, type_) 

1023 

1024 def label(self, name): 

1025 """Produce a column label, i.e. ``<columnname> AS <name>``. 

1026 

1027 This is a shortcut to the :func:`_expression.label` function. 

1028 

1029 If 'name' is ``None``, an anonymous label name will be generated. 

1030 

1031 """ 

1032 return Label(name, self, self.type) 

1033 

1034 def _anon_label(self, seed, add_hash=None): 

1035 while self._is_clone_of is not None: 

1036 self = self._is_clone_of 

1037 

1038 # as of 1.4 anonymous label for ColumnElement uses hash(), not id(), 

1039 # as the identifier, because a column and its annotated version are 

1040 # the same thing in a SQL statement 

1041 hash_value = hash(self) 

1042 

1043 if add_hash: 

1044 # this path is used for disambiguating anon labels that would 

1045 # otherwise be the same name for the same element repeated. 

1046 # an additional numeric value is factored in for each label. 

1047 

1048 # shift hash(self) (which is id(self), typically 8 byte integer) 

1049 # 16 bits leftward. fill extra add_hash on right 

1050 assert add_hash < (2 << 15) 

1051 assert seed 

1052 hash_value = (hash_value << 16) | add_hash 

1053 

1054 # extra underscore is added for labels with extra hash 

1055 # values, to isolate the "deduped anon" namespace from the 

1056 # regular namespace. eliminates chance of these 

1057 # manufactured hash values overlapping with regular ones for some 

1058 # undefined python interpreter 

1059 seed = seed + "_" 

1060 

1061 if isinstance(seed, _anonymous_label): 

1062 return _anonymous_label.safe_construct( 

1063 hash_value, "", enclosing_label=seed 

1064 ) 

1065 

1066 return _anonymous_label.safe_construct(hash_value, seed or "anon") 

1067 

1068 @util.memoized_property 

1069 def _anon_name_label(self): 

1070 """Provides a constant 'anonymous label' for this ColumnElement. 

1071 

1072 This is a label() expression which will be named at compile time. 

1073 The same label() is returned each time ``anon_label`` is called so 

1074 that expressions can reference ``anon_label`` multiple times, 

1075 producing the same label name at compile time. 

1076 

1077 The compiler uses this function automatically at compile time 

1078 for expressions that are known to be 'unnamed' like binary 

1079 expressions and function calls. 

1080 

1081 .. versionchanged:: 1.4.9 - this attribute was not intended to be 

1082 public and is renamed to _anon_name_label. anon_name exists 

1083 for backwards compat 

1084 

1085 """ 

1086 name = getattr(self, "name", None) 

1087 return self._anon_label(name) 

1088 

1089 @util.memoized_property 

1090 def _anon_key_label(self): 

1091 """Provides a constant 'anonymous key label' for this ColumnElement. 

1092 

1093 Compare to ``anon_label``, except that the "key" of the column, 

1094 if available, is used to generate the label. 

1095 

1096 This is used when a deduplicating key is placed into the columns 

1097 collection of a selectable. 

1098 

1099 .. versionchanged:: 1.4.9 - this attribute was not intended to be 

1100 public and is renamed to _anon_key_label. anon_key_label exists 

1101 for backwards compat 

1102 

1103 """ 

1104 return self._anon_label(self._proxy_key) 

1105 

1106 @property 

1107 @util.deprecated( 

1108 "1.4", 

1109 "The :attr:`_expression.ColumnElement.anon_label` attribute is now " 

1110 "private, and the public accessor is deprecated.", 

1111 ) 

1112 def anon_label(self): 

1113 return self._anon_name_label 

1114 

1115 @property 

1116 @util.deprecated( 

1117 "1.4", 

1118 "The :attr:`_expression.ColumnElement.anon_key_label` attribute is " 

1119 "now private, and the public accessor is deprecated.", 

1120 ) 

1121 def anon_key_label(self): 

1122 return self._anon_key_label 

1123 

1124 def _dedupe_anon_label_idx(self, idx): 

1125 """label to apply to a column that is anon labeled, but repeated 

1126 in the SELECT, so that we have to make an "extra anon" label that 

1127 disambiguates it from the previous appearance. 

1128 

1129 these labels come out like "foo_bar_id__1" and have double underscores 

1130 in them. 

1131 

1132 """ 

1133 label = getattr(self, "name", None) 

1134 

1135 # current convention is that if the element doesn't have a 

1136 # ".name" (usually because it is not NamedColumn), we try to 

1137 # use a "table qualified" form for the "dedupe anon" label, 

1138 # based on the notion that a label like 

1139 # "CAST(casttest.v1 AS DECIMAL) AS casttest_v1__1" looks better than 

1140 # "CAST(casttest.v1 AS DECIMAL) AS anon__1" 

1141 

1142 if label is None: 

1143 return self._dedupe_anon_tq_label_idx(idx) 

1144 else: 

1145 return self._anon_label(label, add_hash=idx) 

1146 

1147 @util.memoized_property 

1148 def _anon_tq_label(self): 

1149 return self._anon_label(getattr(self, "_tq_label", None)) 

1150 

1151 @util.memoized_property 

1152 def _anon_tq_key_label(self): 

1153 return self._anon_label(getattr(self, "_tq_key_label", None)) 

1154 

1155 def _dedupe_anon_tq_label_idx(self, idx): 

1156 label = getattr(self, "_tq_label", None) or "anon" 

1157 

1158 return self._anon_label(label, add_hash=idx) 

1159 

1160 

1161class WrapsColumnExpression(object): 

1162 """Mixin that defines a :class:`_expression.ColumnElement` 

1163 as a wrapper with special 

1164 labeling behavior for an expression that already has a name. 

1165 

1166 .. versionadded:: 1.4 

1167 

1168 .. seealso:: 

1169 

1170 :ref:`change_4449` 

1171 

1172 

1173 """ 

1174 

1175 @property 

1176 def wrapped_column_expression(self): 

1177 raise NotImplementedError() 

1178 

1179 @property 

1180 def _tq_label(self): 

1181 wce = self.wrapped_column_expression 

1182 if hasattr(wce, "_tq_label"): 

1183 return wce._tq_label 

1184 else: 

1185 return None 

1186 

1187 _label = _tq_label 

1188 

1189 @property 

1190 def _non_anon_label(self): 

1191 return None 

1192 

1193 @property 

1194 def _anon_name_label(self): 

1195 wce = self.wrapped_column_expression 

1196 

1197 # this logic tries to get the WrappedColumnExpression to render 

1198 # with "<expr> AS <name>", where "<name>" is the natural name 

1199 # within the expression itself. e.g. "CAST(table.foo) AS foo". 

1200 if not wce._is_text_clause: 

1201 nal = wce._non_anon_label 

1202 if nal: 

1203 return nal 

1204 elif hasattr(wce, "_anon_name_label"): 

1205 return wce._anon_name_label 

1206 return super(WrapsColumnExpression, self)._anon_name_label 

1207 

1208 def _dedupe_anon_label_idx(self, idx): 

1209 wce = self.wrapped_column_expression 

1210 nal = wce._non_anon_label 

1211 if nal: 

1212 return self._anon_label(nal + "_") 

1213 else: 

1214 return self._dedupe_anon_tq_label_idx(idx) 

1215 

1216 @property 

1217 def _proxy_key(self): 

1218 wce = self.wrapped_column_expression 

1219 

1220 if not wce._is_text_clause: 

1221 return wce._proxy_key 

1222 return super(WrapsColumnExpression, self)._proxy_key 

1223 

1224 

1225class BindParameter(roles.InElementRole, ColumnElement): 

1226 r"""Represent a "bound expression". 

1227 

1228 :class:`.BindParameter` is invoked explicitly using the 

1229 :func:`.bindparam` function, as in:: 

1230 

1231 from sqlalchemy import bindparam 

1232 

1233 stmt = select(users_table).\ 

1234 where(users_table.c.name == bindparam('username')) 

1235 

1236 Detailed discussion of how :class:`.BindParameter` is used is 

1237 at :func:`.bindparam`. 

1238 

1239 .. seealso:: 

1240 

1241 :func:`.bindparam` 

1242 

1243 """ 

1244 

1245 __visit_name__ = "bindparam" 

1246 

1247 _traverse_internals = [ 

1248 ("key", InternalTraversal.dp_anon_name), 

1249 ("type", InternalTraversal.dp_type), 

1250 ("callable", InternalTraversal.dp_plain_dict), 

1251 ("value", InternalTraversal.dp_plain_obj), 

1252 ("literal_execute", InternalTraversal.dp_boolean), 

1253 ] 

1254 

1255 _is_crud = False 

1256 _is_bind_parameter = True 

1257 _key_is_anon = False 

1258 

1259 # bindparam implements its own _gen_cache_key() method however 

1260 # we check subclasses for this flag, else no cache key is generated 

1261 inherit_cache = True 

1262 

1263 def __init__( 

1264 self, 

1265 key, 

1266 value=NO_ARG, 

1267 type_=None, 

1268 unique=False, 

1269 required=NO_ARG, 

1270 quote=None, 

1271 callable_=None, 

1272 expanding=False, 

1273 isoutparam=False, 

1274 literal_execute=False, 

1275 _compared_to_operator=None, 

1276 _compared_to_type=None, 

1277 _is_crud=False, 

1278 ): 

1279 r"""Produce a "bound expression". 

1280 

1281 The return value is an instance of :class:`.BindParameter`; this 

1282 is a :class:`_expression.ColumnElement` 

1283 subclass which represents a so-called 

1284 "placeholder" value in a SQL expression, the value of which is 

1285 supplied at the point at which the statement in executed against a 

1286 database connection. 

1287 

1288 In SQLAlchemy, the :func:`.bindparam` construct has 

1289 the ability to carry along the actual value that will be ultimately 

1290 used at expression time. In this way, it serves not just as 

1291 a "placeholder" for eventual population, but also as a means of 

1292 representing so-called "unsafe" values which should not be rendered 

1293 directly in a SQL statement, but rather should be passed along 

1294 to the :term:`DBAPI` as values which need to be correctly escaped 

1295 and potentially handled for type-safety. 

1296 

1297 When using :func:`.bindparam` explicitly, the use case is typically 

1298 one of traditional deferment of parameters; the :func:`.bindparam` 

1299 construct accepts a name which can then be referred to at execution 

1300 time:: 

1301 

1302 from sqlalchemy import bindparam 

1303 

1304 stmt = select(users_table).\ 

1305 where(users_table.c.name == bindparam('username')) 

1306 

1307 The above statement, when rendered, will produce SQL similar to:: 

1308 

1309 SELECT id, name FROM user WHERE name = :username 

1310 

1311 In order to populate the value of ``:username`` above, the value 

1312 would typically be applied at execution time to a method 

1313 like :meth:`_engine.Connection.execute`:: 

1314 

1315 result = connection.execute(stmt, username='wendy') 

1316 

1317 Explicit use of :func:`.bindparam` is also common when producing 

1318 UPDATE or DELETE statements that are to be invoked multiple times, 

1319 where the WHERE criterion of the statement is to change on each 

1320 invocation, such as:: 

1321 

1322 stmt = (users_table.update(). 

1323 where(user_table.c.name == bindparam('username')). 

1324 values(fullname=bindparam('fullname')) 

1325 ) 

1326 

1327 connection.execute( 

1328 stmt, [{"username": "wendy", "fullname": "Wendy Smith"}, 

1329 {"username": "jack", "fullname": "Jack Jones"}, 

1330 ] 

1331 ) 

1332 

1333 SQLAlchemy's Core expression system makes wide use of 

1334 :func:`.bindparam` in an implicit sense. It is typical that Python 

1335 literal values passed to virtually all SQL expression functions are 

1336 coerced into fixed :func:`.bindparam` constructs. For example, given 

1337 a comparison operation such as:: 

1338 

1339 expr = users_table.c.name == 'Wendy' 

1340 

1341 The above expression will produce a :class:`.BinaryExpression` 

1342 construct, where the left side is the :class:`_schema.Column` object 

1343 representing the ``name`` column, and the right side is a 

1344 :class:`.BindParameter` representing the literal value:: 

1345 

1346 print(repr(expr.right)) 

1347 BindParameter('%(4327771088 name)s', 'Wendy', type_=String()) 

1348 

1349 The expression above will render SQL such as:: 

1350 

1351 user.name = :name_1 

1352 

1353 Where the ``:name_1`` parameter name is an anonymous name. The 

1354 actual string ``Wendy`` is not in the rendered string, but is carried 

1355 along where it is later used within statement execution. If we 

1356 invoke a statement like the following:: 

1357 

1358 stmt = select(users_table).where(users_table.c.name == 'Wendy') 

1359 result = connection.execute(stmt) 

1360 

1361 We would see SQL logging output as:: 

1362 

1363 SELECT "user".id, "user".name 

1364 FROM "user" 

1365 WHERE "user".name = %(name_1)s 

1366 {'name_1': 'Wendy'} 

1367 

1368 Above, we see that ``Wendy`` is passed as a parameter to the database, 

1369 while the placeholder ``:name_1`` is rendered in the appropriate form 

1370 for the target database, in this case the PostgreSQL database. 

1371 

1372 Similarly, :func:`.bindparam` is invoked automatically when working 

1373 with :term:`CRUD` statements as far as the "VALUES" portion is 

1374 concerned. The :func:`_expression.insert` construct produces an 

1375 ``INSERT`` expression which will, at statement execution time, generate 

1376 bound placeholders based on the arguments passed, as in:: 

1377 

1378 stmt = users_table.insert() 

1379 result = connection.execute(stmt, name='Wendy') 

1380 

1381 The above will produce SQL output as:: 

1382 

1383 INSERT INTO "user" (name) VALUES (%(name)s) 

1384 {'name': 'Wendy'} 

1385 

1386 The :class:`_expression.Insert` construct, at 

1387 compilation/execution time, rendered a single :func:`.bindparam` 

1388 mirroring the column name ``name`` as a result of the single ``name`` 

1389 parameter we passed to the :meth:`_engine.Connection.execute` method. 

1390 

1391 :param key: 

1392 the key (e.g. the name) for this bind param. 

1393 Will be used in the generated 

1394 SQL statement for dialects that use named parameters. This 

1395 value may be modified when part of a compilation operation, 

1396 if other :class:`BindParameter` objects exist with the same 

1397 key, or if its length is too long and truncation is 

1398 required. 

1399 

1400 :param value: 

1401 Initial value for this bind param. Will be used at statement 

1402 execution time as the value for this parameter passed to the 

1403 DBAPI, if no other value is indicated to the statement execution 

1404 method for this particular parameter name. Defaults to ``None``. 

1405 

1406 :param callable\_: 

1407 A callable function that takes the place of "value". The function 

1408 will be called at statement execution time to determine the 

1409 ultimate value. Used for scenarios where the actual bind 

1410 value cannot be determined at the point at which the clause 

1411 construct is created, but embedded bind values are still desirable. 

1412 

1413 :param type\_: 

1414 A :class:`.TypeEngine` class or instance representing an optional 

1415 datatype for this :func:`.bindparam`. If not passed, a type 

1416 may be determined automatically for the bind, based on the given 

1417 value; for example, trivial Python types such as ``str``, 

1418 ``int``, ``bool`` 

1419 may result in the :class:`.String`, :class:`.Integer` or 

1420 :class:`.Boolean` types being automatically selected. 

1421 

1422 The type of a :func:`.bindparam` is significant especially in that 

1423 the type will apply pre-processing to the value before it is 

1424 passed to the database. For example, a :func:`.bindparam` which 

1425 refers to a datetime value, and is specified as holding the 

1426 :class:`.DateTime` type, may apply conversion needed to the 

1427 value (such as stringification on SQLite) before passing the value 

1428 to the database. 

1429 

1430 :param unique: 

1431 if True, the key name of this :class:`.BindParameter` will be 

1432 modified if another :class:`.BindParameter` of the same name 

1433 already has been located within the containing 

1434 expression. This flag is used generally by the internals 

1435 when producing so-called "anonymous" bound expressions, it 

1436 isn't generally applicable to explicitly-named :func:`.bindparam` 

1437 constructs. 

1438 

1439 :param required: 

1440 If ``True``, a value is required at execution time. If not passed, 

1441 it defaults to ``True`` if neither :paramref:`.bindparam.value` 

1442 or :paramref:`.bindparam.callable` were passed. If either of these 

1443 parameters are present, then :paramref:`.bindparam.required` 

1444 defaults to ``False``. 

1445 

1446 :param quote: 

1447 True if this parameter name requires quoting and is not 

1448 currently known as a SQLAlchemy reserved word; this currently 

1449 only applies to the Oracle backend, where bound names must 

1450 sometimes be quoted. 

1451 

1452 :param isoutparam: 

1453 if True, the parameter should be treated like a stored procedure 

1454 "OUT" parameter. This applies to backends such as Oracle which 

1455 support OUT parameters. 

1456 

1457 :param expanding: 

1458 if True, this parameter will be treated as an "expanding" parameter 

1459 at execution time; the parameter value is expected to be a sequence, 

1460 rather than a scalar value, and the string SQL statement will 

1461 be transformed on a per-execution basis to accommodate the sequence 

1462 with a variable number of parameter slots passed to the DBAPI. 

1463 This is to allow statement caching to be used in conjunction with 

1464 an IN clause. 

1465 

1466 .. seealso:: 

1467 

1468 :meth:`.ColumnOperators.in_` 

1469 

1470 :ref:`baked_in` - with baked queries 

1471 

1472 .. note:: The "expanding" feature does not support "executemany"- 

1473 style parameter sets. 

1474 

1475 .. versionadded:: 1.2 

1476 

1477 .. versionchanged:: 1.3 the "expanding" bound parameter feature now 

1478 supports empty lists. 

1479 

1480 :param literal_execute: 

1481 if True, the bound parameter will be rendered in the compile phase 

1482 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will 

1483 render the final value of the parameter into the SQL statement at 

1484 statement execution time, omitting the value from the parameter 

1485 dictionary / list passed to DBAPI ``cursor.execute()``. This 

1486 produces a similar effect as that of using the ``literal_binds``, 

1487 compilation flag, however takes place as the statement is sent to 

1488 the DBAPI ``cursor.execute()`` method, rather than when the statement 

1489 is compiled. The primary use of this 

1490 capability is for rendering LIMIT / OFFSET clauses for database 

1491 drivers that can't accommodate for bound parameters in these 

1492 contexts, while allowing SQL constructs to be cacheable at the 

1493 compilation level. 

1494 

1495 .. versionadded:: 1.4 Added "post compile" bound parameters 

1496 

1497 .. seealso:: 

1498 

1499 :ref:`change_4808`. 

1500 

1501 .. seealso:: 

1502 

1503 :ref:`tutorial_sending_parameters` - in the 

1504 :ref:`unified_tutorial` 

1505 

1506 """ 

1507 if required is NO_ARG: 

1508 required = value is NO_ARG and callable_ is None 

1509 if value is NO_ARG: 

1510 value = None 

1511 

1512 if quote is not None: 

1513 key = quoted_name(key, quote) 

1514 

1515 if unique: 

1516 self.key = _anonymous_label.safe_construct( 

1517 id(self), 

1518 key 

1519 if key is not None and not isinstance(key, _anonymous_label) 

1520 else "param", 

1521 sanitize_key=True, 

1522 ) 

1523 self._key_is_anon = True 

1524 elif key: 

1525 self.key = key 

1526 else: 

1527 self.key = _anonymous_label.safe_construct(id(self), "param") 

1528 self._key_is_anon = True 

1529 

1530 # identifying key that won't change across 

1531 # clones, used to identify the bind's logical 

1532 # identity 

1533 self._identifying_key = self.key 

1534 

1535 # key that was passed in the first place, used to 

1536 # generate new keys 

1537 self._orig_key = key or "param" 

1538 

1539 self.unique = unique 

1540 self.value = value 

1541 self.callable = callable_ 

1542 self.isoutparam = isoutparam 

1543 self.required = required 

1544 

1545 # indicate an "expanding" parameter; the compiler sets this 

1546 # automatically in the compiler _render_in_expr_w_bindparam method 

1547 # for an IN expression 

1548 self.expanding = expanding 

1549 

1550 # this is another hint to help w/ expanding and is typically 

1551 # set in the compiler _render_in_expr_w_bindparam method for an 

1552 # IN expression 

1553 self.expand_op = None 

1554 

1555 self.literal_execute = literal_execute 

1556 if _is_crud: 

1557 self._is_crud = True 

1558 

1559 if type_ is None: 

1560 if expanding and value: 

1561 check_value = value[0] 

1562 else: 

1563 check_value = value 

1564 if _compared_to_type is not None: 

1565 self.type = _compared_to_type.coerce_compared_value( 

1566 _compared_to_operator, check_value 

1567 ) 

1568 else: 

1569 self.type = type_api._resolve_value_to_type(check_value) 

1570 elif isinstance(type_, type): 

1571 self.type = type_() 

1572 elif type_._is_tuple_type and value: 

1573 if expanding: 

1574 check_value = value[0] 

1575 else: 

1576 check_value = value 

1577 self.type = type_._resolve_values_to_types(check_value) 

1578 else: 

1579 self.type = type_ 

1580 

1581 def _with_value(self, value, maintain_key=False, required=NO_ARG): 

1582 """Return a copy of this :class:`.BindParameter` with the given value 

1583 set. 

1584 """ 

1585 cloned = self._clone(maintain_key=maintain_key) 

1586 cloned.value = value 

1587 cloned.callable = None 

1588 cloned.required = required if required is not NO_ARG else self.required 

1589 if cloned.type is type_api.NULLTYPE: 

1590 cloned.type = type_api._resolve_value_to_type(value) 

1591 return cloned 

1592 

1593 @property 

1594 def effective_value(self): 

1595 """Return the value of this bound parameter, 

1596 taking into account if the ``callable`` parameter 

1597 was set. 

1598 

1599 The ``callable`` value will be evaluated 

1600 and returned if present, else ``value``. 

1601 

1602 """ 

1603 if self.callable: 

1604 return self.callable() 

1605 else: 

1606 return self.value 

1607 

1608 def render_literal_execute(self): 

1609 """Produce a copy of this bound parameter that will enable the 

1610 :paramref:`_sql.BindParameter.literal_execute` flag. 

1611 

1612 The :paramref:`_sql.BindParameter.literal_execute` flag will 

1613 have the effect of the parameter rendered in the compiled SQL 

1614 string using ``[POSTCOMPILE]`` form, which is a special form that 

1615 is converted to be a rendering of the literal value of the parameter 

1616 at SQL execution time. The rationale is to support caching 

1617 of SQL statement strings that can embed per-statement literal values, 

1618 such as LIMIT and OFFSET parameters, in the final SQL string that 

1619 is passed to the DBAPI. Dialects in particular may want to use 

1620 this method within custom compilation schemes. 

1621 

1622 .. versionadded:: 1.4.5 

1623 

1624 .. seealso:: 

1625 

1626 :ref:`engine_thirdparty_caching` 

1627 

1628 """ 

1629 return self.__class__( 

1630 self.key, 

1631 self.value, 

1632 type_=self.type, 

1633 literal_execute=True, 

1634 ) 

1635 

1636 def _negate_in_binary(self, negated_op, original_op): 

1637 if self.expand_op is original_op: 

1638 bind = self._clone() 

1639 bind.expand_op = negated_op 

1640 return bind 

1641 else: 

1642 return self 

1643 

1644 def _with_binary_element_type(self, type_): 

1645 c = ClauseElement._clone(self) 

1646 c.type = type_ 

1647 return c 

1648 

1649 def _clone(self, maintain_key=False, **kw): 

1650 c = ClauseElement._clone(self, **kw) 

1651 # ensure all the BindParameter objects stay in cloned set. 

1652 # in #7823, we changed "clone" so that a clone only keeps a reference 

1653 # to the "original" element, since for column correspondence, that's 

1654 # all we need. However, for BindParam, _cloned_set is used by 

1655 # the "cache key bind match" lookup, which means if any of those 

1656 # interim BindParameter objects became part of a cache key in the 

1657 # cache, we need it. So here, make sure all clones keep carrying 

1658 # forward. 

1659 c._cloned_set.update(self._cloned_set) 

1660 if not maintain_key and self.unique: 

1661 c.key = _anonymous_label.safe_construct( 

1662 id(c), c._orig_key or "param", sanitize_key=True 

1663 ) 

1664 return c 

1665 

1666 def _gen_cache_key(self, anon_map, bindparams): 

1667 _gen_cache_ok = self.__class__.__dict__.get("inherit_cache", False) 

1668 

1669 if not _gen_cache_ok: 

1670 if anon_map is not None: 

1671 anon_map[NO_CACHE] = True 

1672 return None 

1673 

1674 idself = id(self) 

1675 if idself in anon_map: 

1676 return (anon_map[idself], self.__class__) 

1677 else: 

1678 # inline of 

1679 # id_ = anon_map[idself] 

1680 anon_map[idself] = id_ = str(anon_map.index) 

1681 anon_map.index += 1 

1682 

1683 if bindparams is not None: 

1684 bindparams.append(self) 

1685 

1686 return ( 

1687 id_, 

1688 self.__class__, 

1689 self.type._static_cache_key, 

1690 self.key % anon_map if self._key_is_anon else self.key, 

1691 self.literal_execute, 

1692 ) 

1693 

1694 def _convert_to_unique(self): 

1695 if not self.unique: 

1696 self.unique = True 

1697 self.key = _anonymous_label.safe_construct( 

1698 id(self), self._orig_key or "param", sanitize_key=True 

1699 ) 

1700 

1701 def __getstate__(self): 

1702 """execute a deferred value for serialization purposes.""" 

1703 

1704 d = self.__dict__.copy() 

1705 v = self.value 

1706 if self.callable: 

1707 v = self.callable() 

1708 d["callable"] = None 

1709 d["value"] = v 

1710 return d 

1711 

1712 def __setstate__(self, state): 

1713 if state.get("unique", False): 

1714 state["key"] = _anonymous_label.safe_construct( 

1715 id(self), state.get("_orig_key", "param"), sanitize_key=True 

1716 ) 

1717 self.__dict__.update(state) 

1718 

1719 def __repr__(self): 

1720 return "%s(%r, %r, type_=%r)" % ( 

1721 self.__class__.__name__, 

1722 self.key, 

1723 self.value, 

1724 self.type, 

1725 ) 

1726 

1727 

1728class TypeClause(ClauseElement): 

1729 """Handle a type keyword in a SQL statement. 

1730 

1731 Used by the ``Case`` statement. 

1732 

1733 """ 

1734 

1735 __visit_name__ = "typeclause" 

1736 

1737 _traverse_internals = [("type", InternalTraversal.dp_type)] 

1738 

1739 def __init__(self, type_): 

1740 self.type = type_ 

1741 

1742 

1743class TextClause( 

1744 roles.DDLConstraintColumnRole, 

1745 roles.DDLExpressionRole, 

1746 roles.StatementOptionRole, 

1747 roles.WhereHavingRole, 

1748 roles.OrderByRole, 

1749 roles.FromClauseRole, 

1750 roles.SelectStatementRole, 

1751 roles.BinaryElementRole, 

1752 roles.InElementRole, 

1753 Executable, 

1754 ClauseElement, 

1755): 

1756 """Represent a literal SQL text fragment. 

1757 

1758 E.g.:: 

1759 

1760 from sqlalchemy import text 

1761 

1762 t = text("SELECT * FROM users") 

1763 result = connection.execute(t) 

1764 

1765 

1766 The :class:`_expression.TextClause` construct is produced using the 

1767 :func:`_expression.text` 

1768 function; see that function for full documentation. 

1769 

1770 .. seealso:: 

1771 

1772 :func:`_expression.text` 

1773 

1774 """ 

1775 

1776 __visit_name__ = "textclause" 

1777 

1778 _traverse_internals = [ 

1779 ("_bindparams", InternalTraversal.dp_string_clauseelement_dict), 

1780 ("text", InternalTraversal.dp_string), 

1781 ] 

1782 

1783 _is_text_clause = True 

1784 

1785 _is_textual = True 

1786 

1787 _bind_params_regex = re.compile(r"(?<![:\w\x5c]):(\w+)(?!:)", re.UNICODE) 

1788 _execution_options = Executable._execution_options.union( 

1789 {"autocommit": PARSE_AUTOCOMMIT} 

1790 ) 

1791 _is_implicitly_boolean = False 

1792 

1793 _render_label_in_columns_clause = False 

1794 

1795 _hide_froms = () 

1796 

1797 def __and__(self, other): 

1798 # support use in select.where(), query.filter() 

1799 return and_(self, other) 

1800 

1801 @property 

1802 def _select_iterable(self): 

1803 return (self,) 

1804 

1805 # help in those cases where text() is 

1806 # interpreted in a column expression situation 

1807 key = _label = None 

1808 

1809 _allow_label_resolve = False 

1810 

1811 @property 

1812 def _is_star(self): 

1813 return self.text == "*" 

1814 

1815 def __init__(self, text, bind=None): 

1816 self._bind = bind 

1817 self._bindparams = {} 

1818 

1819 def repl(m): 

1820 self._bindparams[m.group(1)] = BindParameter(m.group(1)) 

1821 return ":%s" % m.group(1) 

1822 

1823 # scan the string and search for bind parameter names, add them 

1824 # to the list of bindparams 

1825 self.text = self._bind_params_regex.sub(repl, text) 

1826 

1827 @classmethod 

1828 @_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`") 

1829 @util.deprecated_params( 

1830 bind=( 

1831 "2.0", 

1832 "The :paramref:`_sql.text.bind` argument is deprecated and " 

1833 "will be removed in SQLAlchemy 2.0.", 

1834 ), 

1835 ) 

1836 def _create_text(cls, text, bind=None): 

1837 r"""Construct a new :class:`_expression.TextClause` clause, 

1838 representing 

1839 a textual SQL string directly. 

1840 

1841 E.g.:: 

1842 

1843 from sqlalchemy import text 

1844 

1845 t = text("SELECT * FROM users") 

1846 result = connection.execute(t) 

1847 

1848 The advantages :func:`_expression.text` 

1849 provides over a plain string are 

1850 backend-neutral support for bind parameters, per-statement 

1851 execution options, as well as 

1852 bind parameter and result-column typing behavior, allowing 

1853 SQLAlchemy type constructs to play a role when executing 

1854 a statement that is specified literally. The construct can also 

1855 be provided with a ``.c`` collection of column elements, allowing 

1856 it to be embedded in other SQL expression constructs as a subquery. 

1857 

1858 Bind parameters are specified by name, using the format ``:name``. 

1859 E.g.:: 

1860 

1861 t = text("SELECT * FROM users WHERE id=:user_id") 

1862 result = connection.execute(t, user_id=12) 

1863 

1864 For SQL statements where a colon is required verbatim, as within 

1865 an inline string, use a backslash to escape:: 

1866 

1867 t = text("SELECT * FROM users WHERE name='\:username'") 

1868 

1869 The :class:`_expression.TextClause` 

1870 construct includes methods which can 

1871 provide information about the bound parameters as well as the column 

1872 values which would be returned from the textual statement, assuming 

1873 it's an executable SELECT type of statement. The 

1874 :meth:`_expression.TextClause.bindparams` 

1875 method is used to provide bound 

1876 parameter detail, and :meth:`_expression.TextClause.columns` 

1877 method allows 

1878 specification of return columns including names and types:: 

1879 

1880 t = text("SELECT * FROM users WHERE id=:user_id").\ 

1881 bindparams(user_id=7).\ 

1882 columns(id=Integer, name=String) 

1883 

1884 for id, name in connection.execute(t): 

1885 print(id, name) 

1886 

1887 The :func:`_expression.text` construct is used in cases when 

1888 a literal string SQL fragment is specified as part of a larger query, 

1889 such as for the WHERE clause of a SELECT statement:: 

1890 

1891 s = select(users.c.id, users.c.name).where(text("id=:user_id")) 

1892 result = connection.execute(s, user_id=12) 

1893 

1894 :func:`_expression.text` is also used for the construction 

1895 of a full, standalone statement using plain text. 

1896 As such, SQLAlchemy refers 

1897 to it as an :class:`.Executable` object, and it supports 

1898 the :meth:`Executable.execution_options` method. For example, 

1899 a :func:`_expression.text` 

1900 construct that should be subject to "autocommit" 

1901 can be set explicitly so using the 

1902 :paramref:`.Connection.execution_options.autocommit` option:: 

1903 

1904 t = text("EXEC my_procedural_thing()").\ 

1905 execution_options(autocommit=True) 

1906 

1907 .. deprecated:: 1.4 The "autocommit" execution option is deprecated 

1908 and will be removed in SQLAlchemy 2.0. See 

1909 :ref:`migration_20_autocommit` for discussion. 

1910 

1911 :param text: 

1912 the text of the SQL statement to be created. Use ``:<param>`` 

1913 to specify bind parameters; they will be compiled to their 

1914 engine-specific format. 

1915 

1916 :param bind: 

1917 an optional connection or engine to be used for this text query. 

1918 

1919 .. seealso:: 

1920 

1921 :ref:`tutorial_select_arbitrary_text` 

1922 

1923 

1924 """ 

1925 return TextClause(text, bind=bind) 

1926 

1927 @_generative 

1928 def bindparams(self, *binds, **names_to_values): 

1929 """Establish the values and/or types of bound parameters within 

1930 this :class:`_expression.TextClause` construct. 

1931 

1932 Given a text construct such as:: 

1933 

1934 from sqlalchemy import text 

1935 stmt = text("SELECT id, name FROM user WHERE name=:name " 

1936 "AND timestamp=:timestamp") 

1937 

1938 the :meth:`_expression.TextClause.bindparams` 

1939 method can be used to establish 

1940 the initial value of ``:name`` and ``:timestamp``, 

1941 using simple keyword arguments:: 

1942 

1943 stmt = stmt.bindparams(name='jack', 

1944 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5)) 

1945 

1946 Where above, new :class:`.BindParameter` objects 

1947 will be generated with the names ``name`` and ``timestamp``, and 

1948 values of ``jack`` and ``datetime.datetime(2012, 10, 8, 15, 12, 5)``, 

1949 respectively. The types will be 

1950 inferred from the values given, in this case :class:`.String` and 

1951 :class:`.DateTime`. 

1952 

1953 When specific typing behavior is needed, the positional ``*binds`` 

1954 argument can be used in which to specify :func:`.bindparam` constructs 

1955 directly. These constructs must include at least the ``key`` 

1956 argument, then an optional value and type:: 

1957 

1958 from sqlalchemy import bindparam 

1959 stmt = stmt.bindparams( 

1960 bindparam('name', value='jack', type_=String), 

1961 bindparam('timestamp', type_=DateTime) 

1962 ) 

1963 

1964 Above, we specified the type of :class:`.DateTime` for the 

1965 ``timestamp`` bind, and the type of :class:`.String` for the ``name`` 

1966 bind. In the case of ``name`` we also set the default value of 

1967 ``"jack"``. 

1968 

1969 Additional bound parameters can be supplied at statement execution 

1970 time, e.g.:: 

1971 

1972 result = connection.execute(stmt, 

1973 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5)) 

1974 

1975 The :meth:`_expression.TextClause.bindparams` 

1976 method can be called repeatedly, 

1977 where it will re-use existing :class:`.BindParameter` objects to add 

1978 new information. For example, we can call 

1979 :meth:`_expression.TextClause.bindparams` 

1980 first with typing information, and a 

1981 second time with value information, and it will be combined:: 

1982 

1983 stmt = text("SELECT id, name FROM user WHERE name=:name " 

1984 "AND timestamp=:timestamp") 

1985 stmt = stmt.bindparams( 

1986 bindparam('name', type_=String), 

1987 bindparam('timestamp', type_=DateTime) 

1988 ) 

1989 stmt = stmt.bindparams( 

1990 name='jack', 

1991 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5) 

1992 ) 

1993 

1994 The :meth:`_expression.TextClause.bindparams` 

1995 method also supports the concept of 

1996 **unique** bound parameters. These are parameters that are 

1997 "uniquified" on name at statement compilation time, so that multiple 

1998 :func:`_expression.text` 

1999 constructs may be combined together without the names 

2000 conflicting. To use this feature, specify the 

2001 :paramref:`.BindParameter.unique` flag on each :func:`.bindparam` 

2002 object:: 

2003 

2004 stmt1 = text("select id from table where name=:name").bindparams( 

2005 bindparam("name", value='name1', unique=True) 

2006 ) 

2007 stmt2 = text("select id from table where name=:name").bindparams( 

2008 bindparam("name", value='name2', unique=True) 

2009 ) 

2010 

2011 union = union_all( 

2012 stmt1.columns(column("id")), 

2013 stmt2.columns(column("id")) 

2014 ) 

2015 

2016 The above statement will render as:: 

2017 

2018 select id from table where name=:name_1 

2019 UNION ALL select id from table where name=:name_2 

2020 

2021 .. versionadded:: 1.3.11 Added support for the 

2022 :paramref:`.BindParameter.unique` flag to work with 

2023 :func:`_expression.text` 

2024 constructs. 

2025 

2026 """ 

2027 self._bindparams = new_params = self._bindparams.copy() 

2028 

2029 for bind in binds: 

2030 try: 

2031 # the regex used for text() currently will not match 

2032 # a unique/anonymous key in any case, so use the _orig_key 

2033 # so that a text() construct can support unique parameters 

2034 existing = new_params[bind._orig_key] 

2035 except KeyError as err: 

2036 util.raise_( 

2037 exc.ArgumentError( 

2038 "This text() construct doesn't define a " 

2039 "bound parameter named %r" % bind._orig_key 

2040 ), 

2041 replace_context=err, 

2042 ) 

2043 else: 

2044 new_params[existing._orig_key] = bind 

2045 

2046 for key, value in names_to_values.items(): 

2047 try: 

2048 existing = new_params[key] 

2049 except KeyError as err: 

2050 util.raise_( 

2051 exc.ArgumentError( 

2052 "This text() construct doesn't define a " 

2053 "bound parameter named %r" % key 

2054 ), 

2055 replace_context=err, 

2056 ) 

2057 else: 

2058 new_params[key] = existing._with_value(value, required=False) 

2059 

2060 @util.preload_module("sqlalchemy.sql.selectable") 

2061 def columns(self, *cols, **types): 

2062 r"""Turn this :class:`_expression.TextClause` object into a 

2063 :class:`_expression.TextualSelect` 

2064 object that serves the same role as a SELECT 

2065 statement. 

2066 

2067 The :class:`_expression.TextualSelect` is part of the 

2068 :class:`_expression.SelectBase` 

2069 hierarchy and can be embedded into another statement by using the 

2070 :meth:`_expression.TextualSelect.subquery` method to produce a 

2071 :class:`.Subquery` 

2072 object, which can then be SELECTed from. 

2073 

2074 This function essentially bridges the gap between an entirely 

2075 textual SELECT statement and the SQL expression language concept 

2076 of a "selectable":: 

2077 

2078 from sqlalchemy.sql import column, text 

2079 

2080 stmt = text("SELECT id, name FROM some_table") 

2081 stmt = stmt.columns(column('id'), column('name')).subquery('st') 

2082 

2083 stmt = select(mytable).\ 

2084 select_from( 

2085 mytable.join(stmt, mytable.c.name == stmt.c.name) 

2086 ).where(stmt.c.id > 5) 

2087 

2088 Above, we pass a series of :func:`_expression.column` elements to the 

2089 :meth:`_expression.TextClause.columns` method positionally. These 

2090 :func:`_expression.column` 

2091 elements now become first class elements upon the 

2092 :attr:`_expression.TextualSelect.selected_columns` column collection, 

2093 which then 

2094 become part of the :attr:`.Subquery.c` collection after 

2095 :meth:`_expression.TextualSelect.subquery` is invoked. 

2096 

2097 The column expressions we pass to 

2098 :meth:`_expression.TextClause.columns` may 

2099 also be typed; when we do so, these :class:`.TypeEngine` objects become 

2100 the effective return type of the column, so that SQLAlchemy's 

2101 result-set-processing systems may be used on the return values. 

2102 This is often needed for types such as date or boolean types, as well 

2103 as for unicode processing on some dialect configurations:: 

2104 

2105 stmt = text("SELECT id, name, timestamp FROM some_table") 

2106 stmt = stmt.columns( 

2107 column('id', Integer), 

2108 column('name', Unicode), 

2109 column('timestamp', DateTime) 

2110 ) 

2111 

2112 for id, name, timestamp in connection.execute(stmt): 

2113 print(id, name, timestamp) 

2114 

2115 As a shortcut to the above syntax, keyword arguments referring to 

2116 types alone may be used, if only type conversion is needed:: 

2117 

2118 stmt = text("SELECT id, name, timestamp FROM some_table") 

2119 stmt = stmt.columns( 

2120 id=Integer, 

2121 name=Unicode, 

2122 timestamp=DateTime 

2123 ) 

2124 

2125 for id, name, timestamp in connection.execute(stmt): 

2126 print(id, name, timestamp) 

2127 

2128 The positional form of :meth:`_expression.TextClause.columns` 

2129 also provides the 

2130 unique feature of **positional column targeting**, which is 

2131 particularly useful when using the ORM with complex textual queries. If 

2132 we specify the columns from our model to 

2133 :meth:`_expression.TextClause.columns`, 

2134 the result set will match to those columns positionally, meaning the 

2135 name or origin of the column in the textual SQL doesn't matter:: 

2136 

2137 stmt = text("SELECT users.id, addresses.id, users.id, " 

2138 "users.name, addresses.email_address AS email " 

2139 "FROM users JOIN addresses ON users.id=addresses.user_id " 

2140 "WHERE users.id = 1").columns( 

2141 User.id, 

2142 Address.id, 

2143 Address.user_id, 

2144 User.name, 

2145 Address.email_address 

2146 ) 

2147 

2148 query = session.query(User).from_statement(stmt).options( 

2149 contains_eager(User.addresses)) 

2150 

2151 .. versionadded:: 1.1 the :meth:`_expression.TextClause.columns` 

2152 method now 

2153 offers positional column targeting in the result set when 

2154 the column expressions are passed purely positionally. 

2155 

2156 The :meth:`_expression.TextClause.columns` method provides a direct 

2157 route to calling :meth:`_expression.FromClause.subquery` as well as 

2158 :meth:`_expression.SelectBase.cte` 

2159 against a textual SELECT statement:: 

2160 

2161 stmt = stmt.columns(id=Integer, name=String).cte('st') 

2162 

2163 stmt = select(sometable).where(sometable.c.id == stmt.c.id) 

2164 

2165 :param \*cols: A series of :class:`_expression.ColumnElement` objects, 

2166 typically 

2167 :class:`_schema.Column` objects from a :class:`_schema.Table` 

2168 or ORM level 

2169 column-mapped attributes, representing a set of columns that this 

2170 textual string will SELECT from. 

2171 

2172 :param \**types: A mapping of string names to :class:`.TypeEngine` 

2173 type objects indicating the datatypes to use for names that are 

2174 SELECTed from the textual string. Prefer to use the ``*cols`` 

2175 argument as it also indicates positional ordering. 

2176 

2177 """ 

2178 selectable = util.preloaded.sql_selectable 

2179 positional_input_cols = [ 

2180 ColumnClause(col.key, types.pop(col.key)) 

2181 if col.key in types 

2182 else col 

2183 for col in cols 

2184 ] 

2185 keyed_input_cols = [ 

2186 ColumnClause(key, type_) for key, type_ in types.items() 

2187 ] 

2188 

2189 return selectable.TextualSelect( 

2190 self, 

2191 positional_input_cols + keyed_input_cols, 

2192 positional=bool(positional_input_cols) and not keyed_input_cols, 

2193 ) 

2194 

2195 @property 

2196 def type(self): 

2197 return type_api.NULLTYPE 

2198 

2199 @property 

2200 def comparator(self): 

2201 return self.type.comparator_factory(self) 

2202 

2203 def self_group(self, against=None): 

2204 if against is operators.in_op: 

2205 return Grouping(self) 

2206 else: 

2207 return self 

2208 

2209 

2210class Null(SingletonConstant, roles.ConstExprRole, ColumnElement): 

2211 """Represent the NULL keyword in a SQL statement. 

2212 

2213 :class:`.Null` is accessed as a constant via the 

2214 :func:`.null` function. 

2215 

2216 """ 

2217 

2218 __visit_name__ = "null" 

2219 

2220 _traverse_internals = [] 

2221 

2222 @util.memoized_property 

2223 def type(self): 

2224 return type_api.NULLTYPE 

2225 

2226 @classmethod 

2227 def _instance(cls): 

2228 """Return a constant :class:`.Null` construct.""" 

2229 

2230 return Null() 

2231 

2232 

2233Null._create_singleton() 

2234 

2235 

2236class False_(SingletonConstant, roles.ConstExprRole, ColumnElement): 

2237 """Represent the ``false`` keyword, or equivalent, in a SQL statement. 

2238 

2239 :class:`.False_` is accessed as a constant via the 

2240 :func:`.false` function. 

2241 

2242 """ 

2243 

2244 __visit_name__ = "false" 

2245 _traverse_internals = [] 

2246 

2247 @util.memoized_property 

2248 def type(self): 

2249 return type_api.BOOLEANTYPE 

2250 

2251 def _negate(self): 

2252 return True_() 

2253 

2254 @classmethod 

2255 def _instance(cls): 

2256 """Return a :class:`.False_` construct. 

2257 

2258 E.g.:: 

2259 

2260 >>> from sqlalchemy import false 

2261 >>> print(select(t.c.x).where(false())) 

2262 SELECT x FROM t WHERE false 

2263 

2264 A backend which does not support true/false constants will render as 

2265 an expression against 1 or 0:: 

2266 

2267 >>> print(select(t.c.x).where(false())) 

2268 SELECT x FROM t WHERE 0 = 1 

2269 

2270 The :func:`.true` and :func:`.false` constants also feature 

2271 "short circuit" operation within an :func:`.and_` or :func:`.or_` 

2272 conjunction:: 

2273 

2274 >>> print(select(t.c.x).where(or_(t.c.x > 5, true()))) 

2275 SELECT x FROM t WHERE true 

2276 

2277 >>> print(select(t.c.x).where(and_(t.c.x > 5, false()))) 

2278 SELECT x FROM t WHERE false 

2279 

2280 .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature 

2281 better integrated behavior within conjunctions and on dialects 

2282 that don't support true/false constants. 

2283 

2284 .. seealso:: 

2285 

2286 :func:`.true` 

2287 

2288 """ 

2289 

2290 return False_() 

2291 

2292 

2293False_._create_singleton() 

2294 

2295 

2296class True_(SingletonConstant, roles.ConstExprRole, ColumnElement): 

2297 """Represent the ``true`` keyword, or equivalent, in a SQL statement. 

2298 

2299 :class:`.True_` is accessed as a constant via the 

2300 :func:`.true` function. 

2301 

2302 """ 

2303 

2304 __visit_name__ = "true" 

2305 

2306 _traverse_internals = [] 

2307 

2308 @util.memoized_property 

2309 def type(self): 

2310 return type_api.BOOLEANTYPE 

2311 

2312 def _negate(self): 

2313 return False_() 

2314 

2315 @classmethod 

2316 def _ifnone(cls, other): 

2317 if other is None: 

2318 return cls._instance() 

2319 else: 

2320 return other 

2321 

2322 @classmethod 

2323 def _instance(cls): 

2324 """Return a constant :class:`.True_` construct. 

2325 

2326 E.g.:: 

2327 

2328 >>> from sqlalchemy import true 

2329 >>> print(select(t.c.x).where(true())) 

2330 SELECT x FROM t WHERE true 

2331 

2332 A backend which does not support true/false constants will render as 

2333 an expression against 1 or 0:: 

2334 

2335 >>> print(select(t.c.x).where(true())) 

2336 SELECT x FROM t WHERE 1 = 1 

2337 

2338 The :func:`.true` and :func:`.false` constants also feature 

2339 "short circuit" operation within an :func:`.and_` or :func:`.or_` 

2340 conjunction:: 

2341 

2342 >>> print(select(t.c.x).where(or_(t.c.x > 5, true()))) 

2343 SELECT x FROM t WHERE true 

2344 

2345 >>> print(select(t.c.x).where(and_(t.c.x > 5, false()))) 

2346 SELECT x FROM t WHERE false 

2347 

2348 .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature 

2349 better integrated behavior within conjunctions and on dialects 

2350 that don't support true/false constants. 

2351 

2352 .. seealso:: 

2353 

2354 :func:`.false` 

2355 

2356 """ 

2357 

2358 return True_() 

2359 

2360 

2361True_._create_singleton() 

2362 

2363 

2364class ClauseList( 

2365 roles.InElementRole, 

2366 roles.OrderByRole, 

2367 roles.ColumnsClauseRole, 

2368 roles.DMLColumnRole, 

2369 ClauseElement, 

2370): 

2371 """Describe a list of clauses, separated by an operator. 

2372 

2373 By default, is comma-separated, such as a column listing. 

2374 

2375 """ 

2376 

2377 __visit_name__ = "clauselist" 

2378 

2379 _is_clause_list = True 

2380 

2381 _traverse_internals = [ 

2382 ("clauses", InternalTraversal.dp_clauseelement_list), 

2383 ("operator", InternalTraversal.dp_operator), 

2384 ] 

2385 

2386 def __init__(self, *clauses, **kwargs): 

2387 self.operator = kwargs.pop("operator", operators.comma_op) 

2388 self.group = kwargs.pop("group", True) 

2389 self.group_contents = kwargs.pop("group_contents", True) 

2390 if kwargs.pop("_flatten_sub_clauses", False): 

2391 clauses = util.flatten_iterator(clauses) 

2392 self._text_converter_role = text_converter_role = kwargs.pop( 

2393 "_literal_as_text_role", roles.WhereHavingRole 

2394 ) 

2395 if self.group_contents: 

2396 self.clauses = [ 

2397 coercions.expect( 

2398 text_converter_role, clause, apply_propagate_attrs=self 

2399 ).self_group(against=self.operator) 

2400 for clause in clauses 

2401 ] 

2402 else: 

2403 self.clauses = [ 

2404 coercions.expect( 

2405 text_converter_role, clause, apply_propagate_attrs=self 

2406 ) 

2407 for clause in clauses 

2408 ] 

2409 self._is_implicitly_boolean = operators.is_boolean(self.operator) 

2410 

2411 @classmethod 

2412 def _construct_raw(cls, operator, clauses=None): 

2413 self = cls.__new__(cls) 

2414 self.clauses = clauses if clauses else [] 

2415 self.group = True 

2416 self.operator = operator 

2417 self.group_contents = True 

2418 self._is_implicitly_boolean = False 

2419 return self 

2420 

2421 def __iter__(self): 

2422 return iter(self.clauses) 

2423 

2424 def __len__(self): 

2425 return len(self.clauses) 

2426 

2427 @property 

2428 def _select_iterable(self): 

2429 return itertools.chain.from_iterable( 

2430 [elem._select_iterable for elem in self.clauses] 

2431 ) 

2432 

2433 def append(self, clause): 

2434 if self.group_contents: 

2435 self.clauses.append( 

2436 coercions.expect(self._text_converter_role, clause).self_group( 

2437 against=self.operator 

2438 ) 

2439 ) 

2440 else: 

2441 self.clauses.append( 

2442 coercions.expect(self._text_converter_role, clause) 

2443 ) 

2444 

2445 @property 

2446 def _from_objects(self): 

2447 return list(itertools.chain(*[c._from_objects for c in self.clauses])) 

2448 

2449 def self_group(self, against=None): 

2450 if self.group and operators.is_precedent(self.operator, against): 

2451 return Grouping(self) 

2452 else: 

2453 return self 

2454 

2455 

2456class BooleanClauseList(ClauseList, ColumnElement): 

2457 __visit_name__ = "clauselist" 

2458 inherit_cache = True 

2459 

2460 def __init__(self, *arg, **kw): 

2461 raise NotImplementedError( 

2462 "BooleanClauseList has a private constructor" 

2463 ) 

2464 

2465 @classmethod 

2466 def _process_clauses_for_boolean( 

2467 cls, operator, continue_on, skip_on, clauses 

2468 ): 

2469 has_continue_on = None 

2470 

2471 convert_clauses = [] 

2472 

2473 against = operators._asbool 

2474 lcc = 0 

2475 

2476 for clause in clauses: 

2477 if clause is continue_on: 

2478 # instance of continue_on, like and_(x, y, True, z), store it 

2479 # if we didn't find one already, we will use it if there 

2480 # are no other expressions here. 

2481 has_continue_on = clause 

2482 elif clause is skip_on: 

2483 # instance of skip_on, e.g. and_(x, y, False, z), cancels 

2484 # the rest out 

2485 convert_clauses = [clause] 

2486 lcc = 1 

2487 break 

2488 else: 

2489 if not lcc: 

2490 lcc = 1 

2491 else: 

2492 against = operator 

2493 # technically this would be len(convert_clauses) + 1 

2494 # however this only needs to indicate "greater than one" 

2495 lcc = 2 

2496 convert_clauses.append(clause) 

2497 

2498 if not convert_clauses and has_continue_on is not None: 

2499 convert_clauses = [has_continue_on] 

2500 lcc = 1 

2501 

2502 return lcc, [c.self_group(against=against) for c in convert_clauses] 

2503 

2504 @classmethod 

2505 def _construct(cls, operator, continue_on, skip_on, *clauses, **kw): 

2506 lcc, convert_clauses = cls._process_clauses_for_boolean( 

2507 operator, 

2508 continue_on, 

2509 skip_on, 

2510 [ 

2511 coercions.expect(roles.WhereHavingRole, clause) 

2512 for clause in util.coerce_generator_arg(clauses) 

2513 ], 

2514 ) 

2515 

2516 if lcc > 1: 

2517 # multiple elements. Return regular BooleanClauseList 

2518 # which will link elements against the operator. 

2519 return cls._construct_raw(operator, convert_clauses) 

2520 elif lcc == 1: 

2521 # just one element. return it as a single boolean element, 

2522 # not a list and discard the operator. 

2523 return convert_clauses[0] 

2524 else: 

2525 # no elements period. deprecated use case. return an empty 

2526 # ClauseList construct that generates nothing unless it has 

2527 # elements added to it. 

2528 util.warn_deprecated( 

2529 "Invoking %(name)s() without arguments is deprecated, and " 

2530 "will be disallowed in a future release. For an empty " 

2531 "%(name)s() construct, use %(name)s(%(continue_on)s, *args)." 

2532 % { 

2533 "name": operator.__name__, 

2534 "continue_on": "True" 

2535 if continue_on is True_._singleton 

2536 else "False", 

2537 }, 

2538 version="1.4", 

2539 ) 

2540 return cls._construct_raw(operator) 

2541 

2542 @classmethod 

2543 def _construct_for_whereclause(cls, clauses): 

2544 operator, continue_on, skip_on = ( 

2545 operators.and_, 

2546 True_._singleton, 

2547 False_._singleton, 

2548 ) 

2549 

2550 lcc, convert_clauses = cls._process_clauses_for_boolean( 

2551 operator, 

2552 continue_on, 

2553 skip_on, 

2554 clauses, # these are assumed to be coerced already 

2555 ) 

2556 

2557 if lcc > 1: 

2558 # multiple elements. Return regular BooleanClauseList 

2559 # which will link elements against the operator. 

2560 return cls._construct_raw(operator, convert_clauses) 

2561 elif lcc == 1: 

2562 # just one element. return it as a single boolean element, 

2563 # not a list and discard the operator. 

2564 return convert_clauses[0] 

2565 else: 

2566 return None 

2567 

2568 @classmethod 

2569 def _construct_raw(cls, operator, clauses=None): 

2570 self = cls.__new__(cls) 

2571 self.clauses = clauses if clauses else [] 

2572 self.group = True 

2573 self.operator = operator 

2574 self.group_contents = True 

2575 self.type = type_api.BOOLEANTYPE 

2576 self._is_implicitly_boolean = True 

2577 return self 

2578 

2579 @classmethod 

2580 def and_(cls, *clauses): 

2581 r"""Produce a conjunction of expressions joined by ``AND``. 

2582 

2583 E.g.:: 

2584 

2585 from sqlalchemy import and_ 

2586 

2587 stmt = select(users_table).where( 

2588 and_( 

2589 users_table.c.name == 'wendy', 

2590 users_table.c.enrolled == True 

2591 ) 

2592 ) 

2593 

2594 The :func:`.and_` conjunction is also available using the 

2595 Python ``&`` operator (though note that compound expressions 

2596 need to be parenthesized in order to function with Python 

2597 operator precedence behavior):: 

2598 

2599 stmt = select(users_table).where( 

2600 (users_table.c.name == 'wendy') & 

2601 (users_table.c.enrolled == True) 

2602 ) 

2603 

2604 The :func:`.and_` operation is also implicit in some cases; 

2605 the :meth:`_expression.Select.where` 

2606 method for example can be invoked multiple 

2607 times against a statement, which will have the effect of each 

2608 clause being combined using :func:`.and_`:: 

2609 

2610 stmt = select(users_table).\ 

2611 where(users_table.c.name == 'wendy').\ 

2612 where(users_table.c.enrolled == True) 

2613 

2614 The :func:`.and_` construct must be given at least one positional 

2615 argument in order to be valid; a :func:`.and_` construct with no 

2616 arguments is ambiguous. To produce an "empty" or dynamically 

2617 generated :func:`.and_` expression, from a given list of expressions, 

2618 a "default" element of ``True`` should be specified:: 

2619 

2620 criteria = and_(True, *expressions) 

2621 

2622 The above expression will compile to SQL as the expression ``true`` 

2623 or ``1 = 1``, depending on backend, if no other expressions are 

2624 present. If expressions are present, then the ``True`` value is 

2625 ignored as it does not affect the outcome of an AND expression that 

2626 has other elements. 

2627 

2628 .. deprecated:: 1.4 The :func:`.and_` element now requires that at 

2629 least one argument is passed; creating the :func:`.and_` construct 

2630 with no arguments is deprecated, and will emit a deprecation warning 

2631 while continuing to produce a blank SQL string. 

2632 

2633 .. seealso:: 

2634 

2635 :func:`.or_` 

2636 

2637 """ 

2638 return cls._construct( 

2639 operators.and_, True_._singleton, False_._singleton, *clauses 

2640 ) 

2641 

2642 @classmethod 

2643 def or_(cls, *clauses): 

2644 """Produce a conjunction of expressions joined by ``OR``. 

2645 

2646 E.g.:: 

2647 

2648 from sqlalchemy import or_ 

2649 

2650 stmt = select(users_table).where( 

2651 or_( 

2652 users_table.c.name == 'wendy', 

2653 users_table.c.name == 'jack' 

2654 ) 

2655 ) 

2656 

2657 The :func:`.or_` conjunction is also available using the 

2658 Python ``|`` operator (though note that compound expressions 

2659 need to be parenthesized in order to function with Python 

2660 operator precedence behavior):: 

2661 

2662 stmt = select(users_table).where( 

2663 (users_table.c.name == 'wendy') | 

2664 (users_table.c.name == 'jack') 

2665 ) 

2666 

2667 The :func:`.or_` construct must be given at least one positional 

2668 argument in order to be valid; a :func:`.or_` construct with no 

2669 arguments is ambiguous. To produce an "empty" or dynamically 

2670 generated :func:`.or_` expression, from a given list of expressions, 

2671 a "default" element of ``False`` should be specified:: 

2672 

2673 or_criteria = or_(False, *expressions) 

2674 

2675 The above expression will compile to SQL as the expression ``false`` 

2676 or ``0 = 1``, depending on backend, if no other expressions are 

2677 present. If expressions are present, then the ``False`` value is 

2678 ignored as it does not affect the outcome of an OR expression which 

2679 has other elements. 

2680 

2681 .. deprecated:: 1.4 The :func:`.or_` element now requires that at 

2682 least one argument is passed; creating the :func:`.or_` construct 

2683 with no arguments is deprecated, and will emit a deprecation warning 

2684 while continuing to produce a blank SQL string. 

2685 

2686 .. seealso:: 

2687 

2688 :func:`.and_` 

2689 

2690 """ 

2691 return cls._construct( 

2692 operators.or_, False_._singleton, True_._singleton, *clauses 

2693 ) 

2694 

2695 @property 

2696 def _select_iterable(self): 

2697 return (self,) 

2698 

2699 def self_group(self, against=None): 

2700 if not self.clauses: 

2701 return self 

2702 else: 

2703 return super(BooleanClauseList, self).self_group(against=against) 

2704 

2705 def _negate(self): 

2706 return ClauseList._negate(self) 

2707 

2708 

2709and_ = BooleanClauseList.and_ 

2710or_ = BooleanClauseList.or_ 

2711 

2712 

2713class Tuple(ClauseList, ColumnElement): 

2714 """Represent a SQL tuple.""" 

2715 

2716 __visit_name__ = "tuple" 

2717 

2718 _traverse_internals = ClauseList._traverse_internals + [] 

2719 

2720 @util.preload_module("sqlalchemy.sql.sqltypes") 

2721 def __init__(self, *clauses, **kw): 

2722 """Return a :class:`.Tuple`. 

2723 

2724 Main usage is to produce a composite IN construct using 

2725 :meth:`.ColumnOperators.in_` :: 

2726 

2727 from sqlalchemy import tuple_ 

2728 

2729 tuple_(table.c.col1, table.c.col2).in_( 

2730 [(1, 2), (5, 12), (10, 19)] 

2731 ) 

2732 

2733 .. versionchanged:: 1.3.6 Added support for SQLite IN tuples. 

2734 

2735 .. warning:: 

2736 

2737 The composite IN construct is not supported by all backends, and is 

2738 currently known to work on PostgreSQL, MySQL, and SQLite. 

2739 Unsupported backends will raise a subclass of 

2740 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is 

2741 invoked. 

2742 

2743 """ 

2744 sqltypes = util.preloaded.sql_sqltypes 

2745 

2746 types = kw.pop("types", None) 

2747 if types is None: 

2748 clauses = [ 

2749 coercions.expect(roles.ExpressionElementRole, c) 

2750 for c in clauses 

2751 ] 

2752 else: 

2753 if len(types) != len(clauses): 

2754 raise exc.ArgumentError( 

2755 "Wrong number of elements for %d-tuple: %r " 

2756 % (len(types), clauses) 

2757 ) 

2758 clauses = [ 

2759 coercions.expect( 

2760 roles.ExpressionElementRole, 

2761 c, 

2762 type_=typ if not typ._isnull else None, 

2763 ) 

2764 for typ, c in zip(types, clauses) 

2765 ] 

2766 

2767 self.type = sqltypes.TupleType(*[arg.type for arg in clauses]) 

2768 super(Tuple, self).__init__(*clauses, **kw) 

2769 

2770 @property 

2771 def _select_iterable(self): 

2772 return (self,) 

2773 

2774 def _bind_param(self, operator, obj, type_=None, expanding=False): 

2775 if expanding: 

2776 return BindParameter( 

2777 None, 

2778 value=obj, 

2779 _compared_to_operator=operator, 

2780 unique=True, 

2781 expanding=True, 

2782 type_=self.type, 

2783 ) 

2784 else: 

2785 return Tuple( 

2786 *[ 

2787 BindParameter( 

2788 None, 

2789 o, 

2790 _compared_to_operator=operator, 

2791 _compared_to_type=compared_to_type, 

2792 unique=True, 

2793 type_=type_, 

2794 ) 

2795 for o, compared_to_type in zip(obj, self.type.types) 

2796 ] 

2797 ) 

2798 

2799 def self_group(self, against=None): 

2800 # Tuple is parenthesized by definition. 

2801 return self 

2802 

2803 

2804class Case(ColumnElement): 

2805 """Represent a ``CASE`` expression. 

2806 

2807 :class:`.Case` is produced using the :func:`.case` factory function, 

2808 as in:: 

2809 

2810 from sqlalchemy import case 

2811 

2812 stmt = select(users_table).\ 

2813 where( 

2814 case( 

2815 (users_table.c.name == 'wendy', 'W'), 

2816 (users_table.c.name == 'jack', 'J'), 

2817 else_='E' 

2818 ) 

2819 ) 

2820 

2821 Details on :class:`.Case` usage is at :func:`.case`. 

2822 

2823 .. seealso:: 

2824 

2825 :func:`.case` 

2826 

2827 """ 

2828 

2829 __visit_name__ = "case" 

2830 

2831 _traverse_internals = [ 

2832 ("value", InternalTraversal.dp_clauseelement), 

2833 ("whens", InternalTraversal.dp_clauseelement_tuples), 

2834 ("else_", InternalTraversal.dp_clauseelement), 

2835 ] 

2836 

2837 # TODO: for Py2k removal, this will be: 

2838 # def __init__(self, *whens, value=None, else_=None): 

2839 

2840 def __init__(self, *whens, **kw): 

2841 r"""Produce a ``CASE`` expression. 

2842 

2843 The ``CASE`` construct in SQL is a conditional object that 

2844 acts somewhat analogously to an "if/then" construct in other 

2845 languages. It returns an instance of :class:`.Case`. 

2846 

2847 :func:`.case` in its usual form is passed a series of "when" 

2848 constructs, that is, a list of conditions and results as tuples:: 

2849 

2850 from sqlalchemy import case 

2851 

2852 stmt = select(users_table).\ 

2853 where( 

2854 case( 

2855 (users_table.c.name == 'wendy', 'W'), 

2856 (users_table.c.name == 'jack', 'J'), 

2857 else_='E' 

2858 ) 

2859 ) 

2860 

2861 The above statement will produce SQL resembling:: 

2862 

2863 SELECT id, name FROM user 

2864 WHERE CASE 

2865 WHEN (name = :name_1) THEN :param_1 

2866 WHEN (name = :name_2) THEN :param_2 

2867 ELSE :param_3 

2868 END 

2869 

2870 When simple equality expressions of several values against a single 

2871 parent column are needed, :func:`.case` also has a "shorthand" format 

2872 used via the 

2873 :paramref:`.case.value` parameter, which is passed a column 

2874 expression to be compared. In this form, the :paramref:`.case.whens` 

2875 parameter is passed as a dictionary containing expressions to be 

2876 compared against keyed to result expressions. The statement below is 

2877 equivalent to the preceding statement:: 

2878 

2879 stmt = select(users_table).\ 

2880 where( 

2881 case( 

2882 {"wendy": "W", "jack": "J"}, 

2883 value=users_table.c.name, 

2884 else_='E' 

2885 ) 

2886 ) 

2887 

2888 The values which are accepted as result values in 

2889 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are 

2890 coerced from Python literals into :func:`.bindparam` constructs. 

2891 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs, 

2892 are accepted 

2893 as well. To coerce a literal string expression into a constant 

2894 expression rendered inline, use the :func:`_expression.literal_column` 

2895 construct, 

2896 as in:: 

2897 

2898 from sqlalchemy import case, literal_column 

2899 

2900 case( 

2901 ( 

2902 orderline.c.qty > 100, 

2903 literal_column("'greaterthan100'") 

2904 ), 

2905 ( 

2906 orderline.c.qty > 10, 

2907 literal_column("'greaterthan10'") 

2908 ), 

2909 else_=literal_column("'lessthan10'") 

2910 ) 

2911 

2912 The above will render the given constants without using bound 

2913 parameters for the result values (but still for the comparison 

2914 values), as in:: 

2915 

2916 CASE 

2917 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100' 

2918 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10' 

2919 ELSE 'lessthan10' 

2920 END 

2921 

2922 :param \*whens: The criteria to be compared against, 

2923 :paramref:`.case.whens` accepts two different forms, based on 

2924 whether or not :paramref:`.case.value` is used. 

2925 

2926 .. versionchanged:: 1.4 the :func:`_sql.case` 

2927 function now accepts the series of WHEN conditions positionally; 

2928 passing the expressions within a list is deprecated. 

2929 

2930 In the first form, it accepts a list of 2-tuples; each 2-tuple 

2931 consists of ``(<sql expression>, <value>)``, where the SQL 

2932 expression is a boolean expression and "value" is a resulting value, 

2933 e.g.:: 

2934 

2935 case( 

2936 (users_table.c.name == 'wendy', 'W'), 

2937 (users_table.c.name == 'jack', 'J') 

2938 ) 

2939 

2940 In the second form, it accepts a Python dictionary of comparison 

2941 values mapped to a resulting value; this form requires 

2942 :paramref:`.case.value` to be present, and values will be compared 

2943 using the ``==`` operator, e.g.:: 

2944 

2945 case( 

2946 {"wendy": "W", "jack": "J"}, 

2947 value=users_table.c.name 

2948 ) 

2949 

2950 :param value: An optional SQL expression which will be used as a 

2951 fixed "comparison point" for candidate values within a dictionary 

2952 passed to :paramref:`.case.whens`. 

2953 

2954 :param else\_: An optional SQL expression which will be the evaluated 

2955 result of the ``CASE`` construct if all expressions within 

2956 :paramref:`.case.whens` evaluate to false. When omitted, most 

2957 databases will produce a result of NULL if none of the "when" 

2958 expressions evaluate to true. 

2959 

2960 

2961 """ 

2962 

2963 if "whens" in kw: 

2964 util.warn_deprecated_20( 

2965 'The "whens" argument to case() is now passed using ' 

2966 "positional style only, not as a keyword argument." 

2967 ) 

2968 whens = (kw.pop("whens"),) 

2969 

2970 whens = coercions._expression_collection_was_a_list( 

2971 "whens", "case", whens 

2972 ) 

2973 

2974 try: 

2975 whens = util.dictlike_iteritems(whens) 

2976 except TypeError: 

2977 pass 

2978 

2979 value = kw.pop("value", None) 

2980 

2981 whenlist = [ 

2982 ( 

2983 coercions.expect( 

2984 roles.ExpressionElementRole, 

2985 c, 

2986 apply_propagate_attrs=self, 

2987 ).self_group(), 

2988 coercions.expect(roles.ExpressionElementRole, r), 

2989 ) 

2990 for (c, r) in whens 

2991 ] 

2992 

2993 if whenlist: 

2994 type_ = list(whenlist[-1])[-1].type 

2995 else: 

2996 type_ = None 

2997 

2998 if value is None: 

2999 self.value = None 

3000 else: 

3001 self.value = coercions.expect(roles.ExpressionElementRole, value) 

3002 

3003 self.type = type_ 

3004 self.whens = whenlist 

3005 

3006 else_ = kw.pop("else_", None) 

3007 if else_ is not None: 

3008 self.else_ = coercions.expect(roles.ExpressionElementRole, else_) 

3009 else: 

3010 self.else_ = None 

3011 

3012 if kw: 

3013 raise TypeError("unknown arguments: %s" % (", ".join(sorted(kw)))) 

3014 

3015 @property 

3016 def _from_objects(self): 

3017 return list( 

3018 itertools.chain(*[x._from_objects for x in self.get_children()]) 

3019 ) 

3020 

3021 

3022def literal_column(text, type_=None): 

3023 r"""Produce a :class:`.ColumnClause` object that has the 

3024 :paramref:`_expression.column.is_literal` flag set to True. 

3025 

3026 :func:`_expression.literal_column` is similar to 

3027 :func:`_expression.column`, except that 

3028 it is more often used as a "standalone" column expression that renders 

3029 exactly as stated; while :func:`_expression.column` 

3030 stores a string name that 

3031 will be assumed to be part of a table and may be quoted as such, 

3032 :func:`_expression.literal_column` can be that, 

3033 or any other arbitrary column-oriented 

3034 expression. 

3035 

3036 :param text: the text of the expression; can be any SQL expression. 

3037 Quoting rules will not be applied. To specify a column-name expression 

3038 which should be subject to quoting rules, use the :func:`column` 

3039 function. 

3040 

3041 :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` 

3042 object which will 

3043 provide result-set translation and additional expression semantics for 

3044 this column. If left as ``None`` the type will be :class:`.NullType`. 

3045 

3046 .. seealso:: 

3047 

3048 :func:`_expression.column` 

3049 

3050 :func:`_expression.text` 

3051 

3052 :ref:`sqlexpression_literal_column` 

3053 

3054 """ 

3055 return ColumnClause(text, type_=type_, is_literal=True) 

3056 

3057 

3058class Cast(WrapsColumnExpression, ColumnElement): 

3059 """Represent a ``CAST`` expression. 

3060 

3061 :class:`.Cast` is produced using the :func:`.cast` factory function, 

3062 as in:: 

3063 

3064 from sqlalchemy import cast, Numeric 

3065 

3066 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4))) 

3067 

3068 Details on :class:`.Cast` usage is at :func:`.cast`. 

3069 

3070 .. seealso:: 

3071 

3072 :ref:`tutorial_casts` 

3073 

3074 :func:`.cast` 

3075 

3076 :func:`.type_coerce` - an alternative to CAST that coerces the type 

3077 on the Python side only, which is often sufficient to generate the 

3078 correct SQL and data coercion. 

3079 

3080 """ 

3081 

3082 __visit_name__ = "cast" 

3083 

3084 _traverse_internals = [ 

3085 ("clause", InternalTraversal.dp_clauseelement), 

3086 ("type", InternalTraversal.dp_type), 

3087 ] 

3088 

3089 def __init__(self, expression, type_): 

3090 r"""Produce a ``CAST`` expression. 

3091 

3092 :func:`.cast` returns an instance of :class:`.Cast`. 

3093 

3094 E.g.:: 

3095 

3096 from sqlalchemy import cast, Numeric 

3097 

3098 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4))) 

3099 

3100 The above statement will produce SQL resembling:: 

3101 

3102 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product 

3103 

3104 The :func:`.cast` function performs two distinct functions when 

3105 used. The first is that it renders the ``CAST`` expression within 

3106 the resulting SQL string. The second is that it associates the given 

3107 type (e.g. :class:`.TypeEngine` class or instance) with the column 

3108 expression on the Python side, which means the expression will take 

3109 on the expression operator behavior associated with that type, 

3110 as well as the bound-value handling and result-row-handling behavior 

3111 of the type. 

3112 

3113 .. versionchanged:: 0.9.0 :func:`.cast` now applies the given type 

3114 to the expression such that it takes effect on the bound-value, 

3115 e.g. the Python-to-database direction, in addition to the 

3116 result handling, e.g. database-to-Python, direction. 

3117 

3118 An alternative to :func:`.cast` is the :func:`.type_coerce` function. 

3119 This function performs the second task of associating an expression 

3120 with a specific type, but does not render the ``CAST`` expression 

3121 in SQL. 

3122 

3123 :param expression: A SQL expression, such as a 

3124 :class:`_expression.ColumnElement` 

3125 expression or a Python string which will be coerced into a bound 

3126 literal value. 

3127 

3128 :param type\_: A :class:`.TypeEngine` class or instance indicating 

3129 the type to which the ``CAST`` should apply. 

3130 

3131 .. seealso:: 

3132 

3133 :ref:`tutorial_casts` 

3134 

3135 :func:`.type_coerce` - an alternative to CAST that coerces the type 

3136 on the Python side only, which is often sufficient to generate the 

3137 correct SQL and data coercion. 

3138 

3139 

3140 """ 

3141 self.type = type_api.to_instance(type_) 

3142 self.clause = coercions.expect( 

3143 roles.ExpressionElementRole, 

3144 expression, 

3145 type_=self.type, 

3146 apply_propagate_attrs=self, 

3147 ) 

3148 self.typeclause = TypeClause(self.type) 

3149 

3150 @property 

3151 def _from_objects(self): 

3152 return self.clause._from_objects 

3153 

3154 @property 

3155 def wrapped_column_expression(self): 

3156 return self.clause 

3157 

3158 

3159class TypeCoerce(WrapsColumnExpression, ColumnElement): 

3160 """Represent a Python-side type-coercion wrapper. 

3161 

3162 :class:`.TypeCoerce` supplies the :func:`_expression.type_coerce` 

3163 function; see that function for usage details. 

3164 

3165 .. versionchanged:: 1.1 The :func:`.type_coerce` function now produces 

3166 a persistent :class:`.TypeCoerce` wrapper object rather than 

3167 translating the given object in place. 

3168 

3169 .. seealso:: 

3170 

3171 :func:`_expression.type_coerce` 

3172 

3173 :func:`.cast` 

3174 

3175 """ 

3176 

3177 __visit_name__ = "type_coerce" 

3178 

3179 _traverse_internals = [ 

3180 ("clause", InternalTraversal.dp_clauseelement), 

3181 ("type", InternalTraversal.dp_type), 

3182 ] 

3183 

3184 def __init__(self, expression, type_): 

3185 r"""Associate a SQL expression with a particular type, without rendering 

3186 ``CAST``. 

3187 

3188 E.g.:: 

3189 

3190 from sqlalchemy import type_coerce 

3191 

3192 stmt = select(type_coerce(log_table.date_string, StringDateTime())) 

3193 

3194 The above construct will produce a :class:`.TypeCoerce` object, which 

3195 does not modify the rendering in any way on the SQL side, with the 

3196 possible exception of a generated label if used in a columns clause 

3197 context:: 

3198 

3199 SELECT date_string AS date_string FROM log 

3200 

3201 When result rows are fetched, the ``StringDateTime`` type processor 

3202 will be applied to result rows on behalf of the ``date_string`` column. 

3203 

3204 .. note:: the :func:`.type_coerce` construct does not render any 

3205 SQL syntax of its own, including that it does not imply 

3206 parenthesization. Please use :meth:`.TypeCoerce.self_group` 

3207 if explicit parenthesization is required. 

3208 

3209 In order to provide a named label for the expression, use 

3210 :meth:`_expression.ColumnElement.label`:: 

3211 

3212 stmt = select( 

3213 type_coerce(log_table.date_string, StringDateTime()).label('date') 

3214 ) 

3215 

3216 

3217 A type that features bound-value handling will also have that behavior 

3218 take effect when literal values or :func:`.bindparam` constructs are 

3219 passed to :func:`.type_coerce` as targets. 

3220 For example, if a type implements the 

3221 :meth:`.TypeEngine.bind_expression` 

3222 method or :meth:`.TypeEngine.bind_processor` method or equivalent, 

3223 these functions will take effect at statement compilation/execution 

3224 time when a literal value is passed, as in:: 

3225 

3226 # bound-value handling of MyStringType will be applied to the 

3227 # literal value "some string" 

3228 stmt = select(type_coerce("some string", MyStringType)) 

3229 

3230 When using :func:`.type_coerce` with composed expressions, note that 

3231 **parenthesis are not applied**. If :func:`.type_coerce` is being 

3232 used in an operator context where the parenthesis normally present from 

3233 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:: 

3234 

3235 >>> some_integer = column("someint", Integer) 

3236 >>> some_string = column("somestr", String) 

3237 >>> expr = type_coerce(some_integer + 5, String) + some_string 

3238 >>> print(expr) 

3239 someint + :someint_1 || somestr 

3240 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string 

3241 >>> print(expr) 

3242 (someint + :someint_1) || somestr 

3243 

3244 :param expression: A SQL expression, such as a 

3245 :class:`_expression.ColumnElement` 

3246 expression or a Python string which will be coerced into a bound 

3247 literal value. 

3248 

3249 :param type\_: A :class:`.TypeEngine` class or instance indicating 

3250 the type to which the expression is coerced. 

3251 

3252 .. seealso:: 

3253 

3254 :ref:`tutorial_casts` 

3255 

3256 :func:`.cast` 

3257 

3258 """ # noqa 

3259 self.type = type_api.to_instance(type_) 

3260 self.clause = coercions.expect( 

3261 roles.ExpressionElementRole, 

3262 expression, 

3263 type_=self.type, 

3264 apply_propagate_attrs=self, 

3265 ) 

3266 

3267 @property 

3268 def _from_objects(self): 

3269 return self.clause._from_objects 

3270 

3271 @HasMemoized.memoized_attribute 

3272 def typed_expression(self): 

3273 if isinstance(self.clause, BindParameter): 

3274 bp = self.clause._clone() 

3275 bp.type = self.type 

3276 return bp 

3277 else: 

3278 return self.clause 

3279 

3280 @property 

3281 def wrapped_column_expression(self): 

3282 return self.clause 

3283 

3284 def self_group(self, against=None): 

3285 grouped = self.clause.self_group(against=against) 

3286 if grouped is not self.clause: 

3287 return TypeCoerce(grouped, self.type) 

3288 else: 

3289 return self 

3290 

3291 

3292class Extract(ColumnElement): 

3293 """Represent a SQL EXTRACT clause, ``extract(field FROM expr)``.""" 

3294 

3295 __visit_name__ = "extract" 

3296 

3297 _traverse_internals = [ 

3298 ("expr", InternalTraversal.dp_clauseelement), 

3299 ("field", InternalTraversal.dp_string), 

3300 ] 

3301 

3302 def __init__(self, field, expr, **kwargs): 

3303 """Return a :class:`.Extract` construct. 

3304 

3305 This is typically available as :func:`.extract` 

3306 as well as ``func.extract`` from the 

3307 :data:`.func` namespace. 

3308 

3309 :param field: The field to extract. 

3310 

3311 :param expr: A column or Python scalar expression serving as the 

3312 right side of the ``EXTRACT`` expression. 

3313 

3314 E.g.:: 

3315 

3316 from sqlalchemy import extract 

3317 from sqlalchemy import table, column 

3318 

3319 logged_table = table("user", 

3320 column("id"), 

3321 column("date_created"), 

3322 ) 

3323 

3324 stmt = select(logged_table.c.id).where( 

3325 extract("YEAR", logged_table.c.date_created) == 2021 

3326 ) 

3327 

3328 In the above example, the statement is used to select ids from the 

3329 database where the ``YEAR`` component matches a specific value. 

3330 

3331 Similarly, one can also select an extracted component:: 

3332 

3333 stmt = select( 

3334 extract("YEAR", logged_table.c.date_created) 

3335 ).where(logged_table.c.id == 1) 

3336 

3337 The implementation of ``EXTRACT`` may vary across database backends. 

3338 Users are reminded to consult their database documentation. 

3339 """ 

3340 self.type = type_api.INTEGERTYPE 

3341 self.field = field 

3342 self.expr = coercions.expect(roles.ExpressionElementRole, expr) 

3343 

3344 @property 

3345 def _from_objects(self): 

3346 return self.expr._from_objects 

3347 

3348 

3349class _label_reference(ColumnElement): 

3350 """Wrap a column expression as it appears in a 'reference' context. 

3351 

3352 This expression is any that includes an _order_by_label_element, 

3353 which is a Label, or a DESC / ASC construct wrapping a Label. 

3354 

3355 The production of _label_reference() should occur when an expression 

3356 is added to this context; this includes the ORDER BY or GROUP BY of a 

3357 SELECT statement, as well as a few other places, such as the ORDER BY 

3358 within an OVER clause. 

3359 

3360 """ 

3361 

3362 __visit_name__ = "label_reference" 

3363 

3364 _traverse_internals = [("element", InternalTraversal.dp_clauseelement)] 

3365 

3366 def __init__(self, element): 

3367 self.element = element 

3368 

3369 @property 

3370 def _from_objects(self): 

3371 return () 

3372 

3373 

3374class _textual_label_reference(ColumnElement): 

3375 __visit_name__ = "textual_label_reference" 

3376 

3377 _traverse_internals = [("element", InternalTraversal.dp_string)] 

3378 

3379 def __init__(self, element): 

3380 self.element = element 

3381 

3382 @util.memoized_property 

3383 def _text_clause(self): 

3384 return TextClause._create_text(self.element) 

3385 

3386 

3387class UnaryExpression(ColumnElement): 

3388 """Define a 'unary' expression. 

3389 

3390 A unary expression has a single column expression 

3391 and an operator. The operator can be placed on the left 

3392 (where it is called the 'operator') or right (where it is called the 

3393 'modifier') of the column expression. 

3394 

3395 :class:`.UnaryExpression` is the basis for several unary operators 

3396 including those used by :func:`.desc`, :func:`.asc`, :func:`.distinct`, 

3397 :func:`.nulls_first` and :func:`.nulls_last`. 

3398 

3399 """ 

3400 

3401 __visit_name__ = "unary" 

3402 

3403 _traverse_internals = [ 

3404 ("element", InternalTraversal.dp_clauseelement), 

3405 ("operator", InternalTraversal.dp_operator), 

3406 ("modifier", InternalTraversal.dp_operator), 

3407 ] 

3408 

3409 def __init__( 

3410 self, 

3411 element, 

3412 operator=None, 

3413 modifier=None, 

3414 type_=None, 

3415 wraps_column_expression=False, 

3416 ): 

3417 self.operator = operator 

3418 self.modifier = modifier 

3419 self._propagate_attrs = element._propagate_attrs 

3420 self.element = element.self_group( 

3421 against=self.operator or self.modifier 

3422 ) 

3423 self.type = type_api.to_instance(type_) 

3424 self.wraps_column_expression = wraps_column_expression 

3425 

3426 @classmethod 

3427 def _create_nulls_first(cls, column): 

3428 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression. 

3429 

3430 :func:`.nulls_first` is intended to modify the expression produced 

3431 by :func:`.asc` or :func:`.desc`, and indicates how NULL values 

3432 should be handled when they are encountered during ordering:: 

3433 

3434 

3435 from sqlalchemy import desc, nulls_first 

3436 

3437 stmt = select(users_table).order_by( 

3438 nulls_first(desc(users_table.c.name))) 

3439 

3440 The SQL expression from the above would resemble:: 

3441 

3442 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST 

3443 

3444 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically 

3445 invoked from the column expression itself using 

3446 :meth:`_expression.ColumnElement.nulls_first`, 

3447 rather than as its standalone 

3448 function version, as in:: 

3449 

3450 stmt = select(users_table).order_by( 

3451 users_table.c.name.desc().nulls_first()) 

3452 

3453 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from 

3454 :func:`.nullsfirst` in previous releases. 

3455 The previous name remains available for backwards compatibility. 

3456 

3457 .. seealso:: 

3458 

3459 :func:`.asc` 

3460 

3461 :func:`.desc` 

3462 

3463 :func:`.nulls_last` 

3464 

3465 :meth:`_expression.Select.order_by` 

3466 

3467 """ 

3468 return UnaryExpression( 

3469 coercions.expect(roles.ByOfRole, column), 

3470 modifier=operators.nulls_first_op, 

3471 wraps_column_expression=False, 

3472 ) 

3473 

3474 @classmethod 

3475 def _create_nulls_last(cls, column): 

3476 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression. 

3477 

3478 :func:`.nulls_last` is intended to modify the expression produced 

3479 by :func:`.asc` or :func:`.desc`, and indicates how NULL values 

3480 should be handled when they are encountered during ordering:: 

3481 

3482 

3483 from sqlalchemy import desc, nulls_last 

3484 

3485 stmt = select(users_table).order_by( 

3486 nulls_last(desc(users_table.c.name))) 

3487 

3488 The SQL expression from the above would resemble:: 

3489 

3490 SELECT id, name FROM user ORDER BY name DESC NULLS LAST 

3491 

3492 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically 

3493 invoked from the column expression itself using 

3494 :meth:`_expression.ColumnElement.nulls_last`, 

3495 rather than as its standalone 

3496 function version, as in:: 

3497 

3498 stmt = select(users_table).order_by( 

3499 users_table.c.name.desc().nulls_last()) 

3500 

3501 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from 

3502 :func:`.nullslast` in previous releases. 

3503 The previous name remains available for backwards compatibility. 

3504 

3505 .. seealso:: 

3506 

3507 :func:`.asc` 

3508 

3509 :func:`.desc` 

3510 

3511 :func:`.nulls_first` 

3512 

3513 :meth:`_expression.Select.order_by` 

3514 

3515 """ 

3516 return UnaryExpression( 

3517 coercions.expect(roles.ByOfRole, column), 

3518 modifier=operators.nulls_last_op, 

3519 wraps_column_expression=False, 

3520 ) 

3521 

3522 @classmethod 

3523 def _create_desc(cls, column): 

3524 """Produce a descending ``ORDER BY`` clause element. 

3525 

3526 e.g.:: 

3527 

3528 from sqlalchemy import desc 

3529 

3530 stmt = select(users_table).order_by(desc(users_table.c.name)) 

3531 

3532 will produce SQL as:: 

3533 

3534 SELECT id, name FROM user ORDER BY name DESC 

3535 

3536 The :func:`.desc` function is a standalone version of the 

3537 :meth:`_expression.ColumnElement.desc` 

3538 method available on all SQL expressions, 

3539 e.g.:: 

3540 

3541 

3542 stmt = select(users_table).order_by(users_table.c.name.desc()) 

3543 

3544 :param column: A :class:`_expression.ColumnElement` (e.g. 

3545 scalar SQL expression) 

3546 with which to apply the :func:`.desc` operation. 

3547 

3548 .. seealso:: 

3549 

3550 :func:`.asc` 

3551 

3552 :func:`.nulls_first` 

3553 

3554 :func:`.nulls_last` 

3555 

3556 :meth:`_expression.Select.order_by` 

3557 

3558 """ 

3559 return UnaryExpression( 

3560 coercions.expect(roles.ByOfRole, column), 

3561 modifier=operators.desc_op, 

3562 wraps_column_expression=False, 

3563 ) 

3564 

3565 @classmethod 

3566 def _create_asc(cls, column): 

3567 """Produce an ascending ``ORDER BY`` clause element. 

3568 

3569 e.g.:: 

3570 

3571 from sqlalchemy import asc 

3572 stmt = select(users_table).order_by(asc(users_table.c.name)) 

3573 

3574 will produce SQL as:: 

3575 

3576 SELECT id, name FROM user ORDER BY name ASC 

3577 

3578 The :func:`.asc` function is a standalone version of the 

3579 :meth:`_expression.ColumnElement.asc` 

3580 method available on all SQL expressions, 

3581 e.g.:: 

3582 

3583 

3584 stmt = select(users_table).order_by(users_table.c.name.asc()) 

3585 

3586 :param column: A :class:`_expression.ColumnElement` (e.g. 

3587 scalar SQL expression) 

3588 with which to apply the :func:`.asc` operation. 

3589 

3590 .. seealso:: 

3591 

3592 :func:`.desc` 

3593 

3594 :func:`.nulls_first` 

3595 

3596 :func:`.nulls_last` 

3597 

3598 :meth:`_expression.Select.order_by` 

3599 

3600 """ 

3601 return UnaryExpression( 

3602 coercions.expect(roles.ByOfRole, column), 

3603 modifier=operators.asc_op, 

3604 wraps_column_expression=False, 

3605 ) 

3606 

3607 @classmethod 

3608 def _create_distinct(cls, expr): 

3609 """Produce an column-expression-level unary ``DISTINCT`` clause. 

3610 

3611 This applies the ``DISTINCT`` keyword to an individual column 

3612 expression, and is typically contained within an aggregate function, 

3613 as in:: 

3614 

3615 from sqlalchemy import distinct, func 

3616 stmt = select(func.count(distinct(users_table.c.name))) 

3617 

3618 The above would produce an expression resembling:: 

3619 

3620 SELECT COUNT(DISTINCT name) FROM user 

3621 

3622 The :func:`.distinct` function is also available as a column-level 

3623 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in:: 

3624 

3625 stmt = select(func.count(users_table.c.name.distinct())) 

3626 

3627 The :func:`.distinct` operator is different from the 

3628 :meth:`_expression.Select.distinct` method of 

3629 :class:`_expression.Select`, 

3630 which produces a ``SELECT`` statement 

3631 with ``DISTINCT`` applied to the result set as a whole, 

3632 e.g. a ``SELECT DISTINCT`` expression. See that method for further 

3633 information. 

3634 

3635 .. seealso:: 

3636 

3637 :meth:`_expression.ColumnElement.distinct` 

3638 

3639 :meth:`_expression.Select.distinct` 

3640 

3641 :data:`.func` 

3642 

3643 """ 

3644 expr = coercions.expect(roles.ExpressionElementRole, expr) 

3645 return UnaryExpression( 

3646 expr, 

3647 operator=operators.distinct_op, 

3648 type_=expr.type, 

3649 wraps_column_expression=False, 

3650 ) 

3651 

3652 @property 

3653 def _order_by_label_element(self): 

3654 if self.modifier in (operators.desc_op, operators.asc_op): 

3655 return self.element._order_by_label_element 

3656 else: 

3657 return None 

3658 

3659 @property 

3660 def _from_objects(self): 

3661 return self.element._from_objects 

3662 

3663 def _negate(self): 

3664 if self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity: 

3665 return UnaryExpression( 

3666 self.self_group(against=operators.inv), 

3667 operator=operators.inv, 

3668 type_=type_api.BOOLEANTYPE, 

3669 wraps_column_expression=self.wraps_column_expression, 

3670 ) 

3671 else: 

3672 return ClauseElement._negate(self) 

3673 

3674 def self_group(self, against=None): 

3675 if self.operator and operators.is_precedent(self.operator, against): 

3676 return Grouping(self) 

3677 else: 

3678 return self 

3679 

3680 

3681class CollectionAggregate(UnaryExpression): 

3682 """Forms the basis for right-hand collection operator modifiers 

3683 ANY and ALL. 

3684 

3685 The ANY and ALL keywords are available in different ways on different 

3686 backends. On PostgreSQL, they only work for an ARRAY type. On 

3687 MySQL, they only work for subqueries. 

3688 

3689 """ 

3690 

3691 inherit_cache = True 

3692 

3693 @classmethod 

3694 def _create_any(cls, expr): 

3695 """Produce an ANY expression. 

3696 

3697 For dialects such as that of PostgreSQL, this operator applies 

3698 to usage of the :class:`_types.ARRAY` datatype, for that of 

3699 MySQL, it may apply to a subquery. e.g.:: 

3700 

3701 # renders on PostgreSQL: 

3702 # '5 = ANY (somearray)' 

3703 expr = 5 == any_(mytable.c.somearray) 

3704 

3705 # renders on MySQL: 

3706 # '5 = ANY (SELECT value FROM table)' 

3707 expr = 5 == any_(select(table.c.value)) 

3708 

3709 Comparison to NULL may work using ``None`` or :func:`_sql.null`:: 

3710 

3711 None == any_(mytable.c.somearray) 

3712 

3713 The any_() / all_() operators also feature a special "operand flipping" 

3714 behavior such that if any_() / all_() are used on the left side of a 

3715 comparison using a standalone operator such as ``==``, ``!=``, etc. 

3716 (not including operator methods such as 

3717 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped:: 

3718 

3719 # would render '5 = ANY (column)` 

3720 any_(mytable.c.column) == 5 

3721 

3722 Or with ``None``, which note will not perform 

3723 the usual step of rendering "IS" as is normally the case for NULL:: 

3724 

3725 # would render 'NULL = ANY(somearray)' 

3726 any_(mytable.c.somearray) == None 

3727 

3728 .. versionchanged:: 1.4.26 repaired the use of any_() / all_() 

3729 comparing to NULL on the right side to be flipped to the left. 

3730 

3731 The column-level :meth:`_sql.ColumnElement.any_` method (not to be 

3732 confused with :class:`_types.ARRAY` level 

3733 :meth:`_types.ARRAY.Comparator.any`) is shorthand for 

3734 ``any_(col)``:: 

3735 

3736 5 = mytable.c.somearray.any_() 

3737 

3738 .. seealso:: 

3739 

3740 :meth:`_sql.ColumnOperators.any_` 

3741 

3742 :func:`_expression.all_` 

3743 

3744 """ 

3745 

3746 expr = coercions.expect(roles.ExpressionElementRole, expr) 

3747 

3748 expr = expr.self_group() 

3749 return CollectionAggregate( 

3750 expr, 

3751 operator=operators.any_op, 

3752 type_=type_api.NULLTYPE, 

3753 wraps_column_expression=False, 

3754 ) 

3755 

3756 @classmethod 

3757 def _create_all(cls, expr): 

3758 """Produce an ALL expression. 

3759 

3760 For dialects such as that of PostgreSQL, this operator applies 

3761 to usage of the :class:`_types.ARRAY` datatype, for that of 

3762 MySQL, it may apply to a subquery. e.g.:: 

3763 

3764 # renders on PostgreSQL: 

3765 # '5 = ALL (somearray)' 

3766 expr = 5 == all_(mytable.c.somearray) 

3767 

3768 # renders on MySQL: 

3769 # '5 = ALL (SELECT value FROM table)' 

3770 expr = 5 == all_(select(table.c.value)) 

3771 

3772 Comparison to NULL may work using ``None``:: 

3773 

3774 None == all_(mytable.c.somearray) 

3775 

3776 The any_() / all_() operators also feature a special "operand flipping" 

3777 behavior such that if any_() / all_() are used on the left side of a 

3778 comparison using a standalone operator such as ``==``, ``!=``, etc. 

3779 (not including operator methods such as 

3780 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped:: 

3781 

3782 # would render '5 = ALL (column)` 

3783 all_(mytable.c.column) == 5 

3784 

3785 Or with ``None``, which note will not perform 

3786 the usual step of rendering "IS" as is normally the case for NULL:: 

3787 

3788 # would render 'NULL = ALL(somearray)' 

3789 all_(mytable.c.somearray) == None 

3790 

3791 .. versionchanged:: 1.4.26 repaired the use of any_() / all_() 

3792 comparing to NULL on the right side to be flipped to the left. 

3793 

3794 The column-level :meth:`_sql.ColumnElement.all_` method (not to be 

3795 confused with :class:`_types.ARRAY` level 

3796 :meth:`_types.ARRAY.Comparator.all`) is shorthand for 

3797 ``all_(col)``:: 

3798 

3799 5 == mytable.c.somearray.all_() 

3800 

3801 .. seealso:: 

3802 

3803 :meth:`_sql.ColumnOperators.all_` 

3804 

3805 :func:`_expression.any_` 

3806 

3807 """ 

3808 expr = coercions.expect(roles.ExpressionElementRole, expr) 

3809 expr = expr.self_group() 

3810 return CollectionAggregate( 

3811 expr, 

3812 operator=operators.all_op, 

3813 type_=type_api.NULLTYPE, 

3814 wraps_column_expression=False, 

3815 ) 

3816 

3817 # operate and reverse_operate are hardwired to 

3818 # dispatch onto the type comparator directly, so that we can 

3819 # ensure "reversed" behavior. 

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

3821 if not operators.is_comparison(op): 

3822 raise exc.ArgumentError( 

3823 "Only comparison operators may be used with ANY/ALL" 

3824 ) 

3825 kwargs["reverse"] = kwargs["_any_all_expr"] = True 

3826 return self.comparator.operate(operators.mirror(op), *other, **kwargs) 

3827 

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

3829 # comparison operators should never call reverse_operate 

3830 assert not operators.is_comparison(op) 

3831 raise exc.ArgumentError( 

3832 "Only comparison operators may be used with ANY/ALL" 

3833 ) 

3834 

3835 

3836class AsBoolean(WrapsColumnExpression, UnaryExpression): 

3837 inherit_cache = True 

3838 

3839 def __init__(self, element, operator, negate): 

3840 self.element = element 

3841 self.type = type_api.BOOLEANTYPE 

3842 self.operator = operator 

3843 self.negate = negate 

3844 self.modifier = None 

3845 self.wraps_column_expression = True 

3846 self._is_implicitly_boolean = element._is_implicitly_boolean 

3847 

3848 @property 

3849 def wrapped_column_expression(self): 

3850 return self.element 

3851 

3852 def self_group(self, against=None): 

3853 return self 

3854 

3855 def _negate(self): 

3856 if isinstance(self.element, (True_, False_)): 

3857 return self.element._negate() 

3858 else: 

3859 return AsBoolean(self.element, self.negate, self.operator) 

3860 

3861 

3862class BinaryExpression(ColumnElement): 

3863 """Represent an expression that is ``LEFT <operator> RIGHT``. 

3864 

3865 A :class:`.BinaryExpression` is generated automatically 

3866 whenever two column expressions are used in a Python binary expression:: 

3867 

3868 >>> from sqlalchemy.sql import column 

3869 >>> column('a') + column('b') 

3870 <sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0> 

3871 >>> print(column('a') + column('b')) 

3872 a + b 

3873 

3874 """ 

3875 

3876 __visit_name__ = "binary" 

3877 

3878 _traverse_internals = [ 

3879 ("left", InternalTraversal.dp_clauseelement), 

3880 ("right", InternalTraversal.dp_clauseelement), 

3881 ("operator", InternalTraversal.dp_operator), 

3882 ("negate", InternalTraversal.dp_operator), 

3883 ("modifiers", InternalTraversal.dp_plain_dict), 

3884 ( 

3885 "type", 

3886 InternalTraversal.dp_type, 

3887 ), 

3888 ] 

3889 

3890 _cache_key_traversal = [ 

3891 ("left", InternalTraversal.dp_clauseelement), 

3892 ("right", InternalTraversal.dp_clauseelement), 

3893 ("operator", InternalTraversal.dp_operator), 

3894 ("modifiers", InternalTraversal.dp_plain_dict), 

3895 # "type" affects JSON CAST operators, so while redundant in most cases, 

3896 # is needed for that one 

3897 ( 

3898 "type", 

3899 InternalTraversal.dp_type, 

3900 ), 

3901 ] 

3902 

3903 _is_implicitly_boolean = True 

3904 """Indicates that any database will know this is a boolean expression 

3905 even if the database does not have an explicit boolean datatype. 

3906 

3907 """ 

3908 

3909 def __init__( 

3910 self, left, right, operator, type_=None, negate=None, modifiers=None 

3911 ): 

3912 # allow compatibility with libraries that 

3913 # refer to BinaryExpression directly and pass strings 

3914 if isinstance(operator, util.string_types): 

3915 operator = operators.custom_op(operator) 

3916 self._orig = (left.__hash__(), right.__hash__()) 

3917 self._propagate_attrs = left._propagate_attrs or right._propagate_attrs 

3918 self.left = left.self_group(against=operator) 

3919 self.right = right.self_group(against=operator) 

3920 self.operator = operator 

3921 self.type = type_api.to_instance(type_) 

3922 self.negate = negate 

3923 self._is_implicitly_boolean = operators.is_boolean(operator) 

3924 

3925 if modifiers is None: 

3926 self.modifiers = {} 

3927 else: 

3928 self.modifiers = modifiers 

3929 

3930 def __bool__(self): 

3931 if self.operator in (operator.eq, operator.ne): 

3932 return self.operator(*self._orig) 

3933 else: 

3934 raise TypeError("Boolean value of this clause is not defined") 

3935 

3936 __nonzero__ = __bool__ 

3937 

3938 @property 

3939 def is_comparison(self): 

3940 return operators.is_comparison(self.operator) 

3941 

3942 @property 

3943 def _from_objects(self): 

3944 return self.left._from_objects + self.right._from_objects 

3945 

3946 def self_group(self, against=None): 

3947 

3948 if operators.is_precedent(self.operator, against): 

3949 return Grouping(self) 

3950 else: 

3951 return self 

3952 

3953 def _negate(self): 

3954 if self.negate is not None: 

3955 return BinaryExpression( 

3956 self.left, 

3957 self.right._negate_in_binary(self.negate, self.operator), 

3958 self.negate, 

3959 negate=self.operator, 

3960 type_=self.type, 

3961 modifiers=self.modifiers, 

3962 ) 

3963 else: 

3964 return super(BinaryExpression, self)._negate() 

3965 

3966 

3967class Slice(ColumnElement): 

3968 """Represent SQL for a Python array-slice object. 

3969 

3970 This is not a specific SQL construct at this level, but 

3971 may be interpreted by specific dialects, e.g. PostgreSQL. 

3972 

3973 """ 

3974 

3975 __visit_name__ = "slice" 

3976 

3977 _traverse_internals = [ 

3978 ("start", InternalTraversal.dp_clauseelement), 

3979 ("stop", InternalTraversal.dp_clauseelement), 

3980 ("step", InternalTraversal.dp_clauseelement), 

3981 ] 

3982 

3983 def __init__(self, start, stop, step, _name=None): 

3984 self.start = coercions.expect( 

3985 roles.ExpressionElementRole, 

3986 start, 

3987 name=_name, 

3988 type_=type_api.INTEGERTYPE, 

3989 ) 

3990 self.stop = coercions.expect( 

3991 roles.ExpressionElementRole, 

3992 stop, 

3993 name=_name, 

3994 type_=type_api.INTEGERTYPE, 

3995 ) 

3996 self.step = coercions.expect( 

3997 roles.ExpressionElementRole, 

3998 step, 

3999 name=_name, 

4000 type_=type_api.INTEGERTYPE, 

4001 ) 

4002 self.type = type_api.NULLTYPE 

4003 

4004 def self_group(self, against=None): 

4005 assert against is operator.getitem 

4006 return self 

4007 

4008 

4009class IndexExpression(BinaryExpression): 

4010 """Represent the class of expressions that are like an "index" 

4011 operation.""" 

4012 

4013 inherit_cache = True 

4014 

4015 

4016class GroupedElement(ClauseElement): 

4017 """Represent any parenthesized expression""" 

4018 

4019 __visit_name__ = "grouping" 

4020 

4021 def self_group(self, against=None): 

4022 return self 

4023 

4024 def _ungroup(self): 

4025 return self.element._ungroup() 

4026 

4027 

4028class Grouping(GroupedElement, ColumnElement): 

4029 """Represent a grouping within a column expression""" 

4030 

4031 _traverse_internals = [ 

4032 ("element", InternalTraversal.dp_clauseelement), 

4033 ("type", InternalTraversal.dp_type), 

4034 ] 

4035 

4036 _cache_key_traversal = [ 

4037 ("element", InternalTraversal.dp_clauseelement), 

4038 ] 

4039 

4040 def __init__(self, element): 

4041 self.element = element 

4042 self.type = getattr(element, "type", type_api.NULLTYPE) 

4043 

4044 def _with_binary_element_type(self, type_): 

4045 return self.__class__(self.element._with_binary_element_type(type_)) 

4046 

4047 @util.memoized_property 

4048 def _is_implicitly_boolean(self): 

4049 return self.element._is_implicitly_boolean 

4050 

4051 @property 

4052 def _tq_label(self): 

4053 return ( 

4054 getattr(self.element, "_tq_label", None) or self._anon_name_label 

4055 ) 

4056 

4057 @property 

4058 def _proxies(self): 

4059 if isinstance(self.element, ColumnElement): 

4060 return [self.element] 

4061 else: 

4062 return [] 

4063 

4064 @property 

4065 def _from_objects(self): 

4066 return self.element._from_objects 

4067 

4068 def __getattr__(self, attr): 

4069 return getattr(self.element, attr) 

4070 

4071 def __getstate__(self): 

4072 return {"element": self.element, "type": self.type} 

4073 

4074 def __setstate__(self, state): 

4075 self.element = state["element"] 

4076 self.type = state["type"] 

4077 

4078 

4079RANGE_UNBOUNDED = util.symbol("RANGE_UNBOUNDED") 

4080RANGE_CURRENT = util.symbol("RANGE_CURRENT") 

4081 

4082 

4083class Over(ColumnElement): 

4084 """Represent an OVER clause. 

4085 

4086 This is a special operator against a so-called 

4087 "window" function, as well as any aggregate function, 

4088 which produces results relative to the result set 

4089 itself. Most modern SQL backends now support window functions. 

4090 

4091 """ 

4092 

4093 __visit_name__ = "over" 

4094 

4095 _traverse_internals = [ 

4096 ("element", InternalTraversal.dp_clauseelement), 

4097 ("order_by", InternalTraversal.dp_clauseelement), 

4098 ("partition_by", InternalTraversal.dp_clauseelement), 

4099 ("range_", InternalTraversal.dp_plain_obj), 

4100 ("rows", InternalTraversal.dp_plain_obj), 

4101 ] 

4102 

4103 order_by = None 

4104 partition_by = None 

4105 

4106 element = None 

4107 """The underlying expression object to which this :class:`.Over` 

4108 object refers towards.""" 

4109 

4110 def __init__( 

4111 self, element, partition_by=None, order_by=None, range_=None, rows=None 

4112 ): 

4113 r"""Produce an :class:`.Over` object against a function. 

4114 

4115 Used against aggregate or so-called "window" functions, 

4116 for database backends that support window functions. 

4117 

4118 :func:`_expression.over` is usually called using 

4119 the :meth:`.FunctionElement.over` method, e.g.:: 

4120 

4121 func.row_number().over(order_by=mytable.c.some_column) 

4122 

4123 Would produce:: 

4124 

4125 ROW_NUMBER() OVER(ORDER BY some_column) 

4126 

4127 Ranges are also possible using the :paramref:`.expression.over.range_` 

4128 and :paramref:`.expression.over.rows` parameters. These 

4129 mutually-exclusive parameters each accept a 2-tuple, which contains 

4130 a combination of integers and None:: 

4131 

4132 func.row_number().over( 

4133 order_by=my_table.c.some_column, range_=(None, 0)) 

4134 

4135 The above would produce:: 

4136 

4137 ROW_NUMBER() OVER(ORDER BY some_column 

4138 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 

4139 

4140 A value of ``None`` indicates "unbounded", a 

4141 value of zero indicates "current row", and negative / positive 

4142 integers indicate "preceding" and "following": 

4143 

4144 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING:: 

4145 

4146 func.row_number().over(order_by='x', range_=(-5, 10)) 

4147 

4148 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:: 

4149 

4150 func.row_number().over(order_by='x', rows=(None, 0)) 

4151 

4152 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING:: 

4153 

4154 func.row_number().over(order_by='x', range_=(-2, None)) 

4155 

4156 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING:: 

4157 

4158 func.row_number().over(order_by='x', range_=(1, 3)) 

4159 

4160 .. versionadded:: 1.1 support for RANGE / ROWS within a window 

4161 

4162 

4163 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`, 

4164 or other compatible construct. 

4165 :param partition_by: a column element or string, or a list 

4166 of such, that will be used as the PARTITION BY clause 

4167 of the OVER construct. 

4168 :param order_by: a column element or string, or a list 

4169 of such, that will be used as the ORDER BY clause 

4170 of the OVER construct. 

4171 :param range\_: optional range clause for the window. This is a 

4172 tuple value which can contain integer values or ``None``, 

4173 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause. 

4174 

4175 .. versionadded:: 1.1 

4176 

4177 :param rows: optional rows clause for the window. This is a tuple 

4178 value which can contain integer values or None, and will render 

4179 a ROWS BETWEEN PRECEDING / FOLLOWING clause. 

4180 

4181 .. versionadded:: 1.1 

4182 

4183 This function is also available from the :data:`~.expression.func` 

4184 construct itself via the :meth:`.FunctionElement.over` method. 

4185 

4186 .. seealso:: 

4187 

4188 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial` 

4189 

4190 :data:`.expression.func` 

4191 

4192 :func:`_expression.within_group` 

4193 

4194 """ 

4195 self.element = element 

4196 if order_by is not None: 

4197 self.order_by = ClauseList( 

4198 *util.to_list(order_by), _literal_as_text_role=roles.ByOfRole 

4199 ) 

4200 if partition_by is not None: 

4201 self.partition_by = ClauseList( 

4202 *util.to_list(partition_by), 

4203 _literal_as_text_role=roles.ByOfRole 

4204 ) 

4205 

4206 if range_: 

4207 self.range_ = self._interpret_range(range_) 

4208 if rows: 

4209 raise exc.ArgumentError( 

4210 "'range_' and 'rows' are mutually exclusive" 

4211 ) 

4212 else: 

4213 self.rows = None 

4214 elif rows: 

4215 self.rows = self._interpret_range(rows) 

4216 self.range_ = None 

4217 else: 

4218 self.rows = self.range_ = None 

4219 

4220 def __reduce__(self): 

4221 return self.__class__, ( 

4222 self.element, 

4223 self.partition_by, 

4224 self.order_by, 

4225 self.range_, 

4226 self.rows, 

4227 ) 

4228 

4229 def _interpret_range(self, range_): 

4230 if not isinstance(range_, tuple) or len(range_) != 2: 

4231 raise exc.ArgumentError("2-tuple expected for range/rows") 

4232 

4233 if range_[0] is None: 

4234 lower = RANGE_UNBOUNDED 

4235 else: 

4236 try: 

4237 lower = int(range_[0]) 

4238 except ValueError as err: 

4239 util.raise_( 

4240 exc.ArgumentError( 

4241 "Integer or None expected for range value" 

4242 ), 

4243 replace_context=err, 

4244 ) 

4245 else: 

4246 if lower == 0: 

4247 lower = RANGE_CURRENT 

4248 

4249 if range_[1] is None: 

4250 upper = RANGE_UNBOUNDED 

4251 else: 

4252 try: 

4253 upper = int(range_[1]) 

4254 except ValueError as err: 

4255 util.raise_( 

4256 exc.ArgumentError( 

4257 "Integer or None expected for range value" 

4258 ), 

4259 replace_context=err, 

4260 ) 

4261 else: 

4262 if upper == 0: 

4263 upper = RANGE_CURRENT 

4264 

4265 return lower, upper 

4266 

4267 @util.memoized_property 

4268 def type(self): 

4269 return self.element.type 

4270 

4271 @property 

4272 def _from_objects(self): 

4273 return list( 

4274 itertools.chain( 

4275 *[ 

4276 c._from_objects 

4277 for c in (self.element, self.partition_by, self.order_by) 

4278 if c is not None 

4279 ] 

4280 ) 

4281 ) 

4282 

4283 

4284class WithinGroup(ColumnElement): 

4285 """Represent a WITHIN GROUP (ORDER BY) clause. 

4286 

4287 This is a special operator against so-called 

4288 "ordered set aggregate" and "hypothetical 

4289 set aggregate" functions, including ``percentile_cont()``, 

4290 ``rank()``, ``dense_rank()``, etc. 

4291 

4292 It's supported only by certain database backends, such as PostgreSQL, 

4293 Oracle and MS SQL Server. 

4294 

4295 The :class:`.WithinGroup` construct extracts its type from the 

4296 method :meth:`.FunctionElement.within_group_type`. If this returns 

4297 ``None``, the function's ``.type`` is used. 

4298 

4299 """ 

4300 

4301 __visit_name__ = "withingroup" 

4302 

4303 _traverse_internals = [ 

4304 ("element", InternalTraversal.dp_clauseelement), 

4305 ("order_by", InternalTraversal.dp_clauseelement), 

4306 ] 

4307 

4308 order_by = None 

4309 

4310 def __init__(self, element, *order_by): 

4311 r"""Produce a :class:`.WithinGroup` object against a function. 

4312 

4313 Used against so-called "ordered set aggregate" and "hypothetical 

4314 set aggregate" functions, including :class:`.percentile_cont`, 

4315 :class:`.rank`, :class:`.dense_rank`, etc. 

4316 

4317 :func:`_expression.within_group` is usually called using 

4318 the :meth:`.FunctionElement.within_group` method, e.g.:: 

4319 

4320 from sqlalchemy import within_group 

4321 stmt = select( 

4322 department.c.id, 

4323 func.percentile_cont(0.5).within_group( 

4324 department.c.salary.desc() 

4325 ) 

4326 ) 

4327 

4328 The above statement would produce SQL similar to 

4329 ``SELECT department.id, percentile_cont(0.5) 

4330 WITHIN GROUP (ORDER BY department.salary DESC)``. 

4331 

4332 :param element: a :class:`.FunctionElement` construct, typically 

4333 generated by :data:`~.expression.func`. 

4334 :param \*order_by: one or more column elements that will be used 

4335 as the ORDER BY clause of the WITHIN GROUP construct. 

4336 

4337 .. versionadded:: 1.1 

4338 

4339 .. seealso:: 

4340 

4341 :ref:`tutorial_functions_within_group` - in the 

4342 :ref:`unified_tutorial` 

4343 

4344 :data:`.expression.func` 

4345 

4346 :func:`_expression.over` 

4347 

4348 """ 

4349 self.element = element 

4350 if order_by is not None: 

4351 self.order_by = ClauseList( 

4352 *util.to_list(order_by), _literal_as_text_role=roles.ByOfRole 

4353 ) 

4354 

4355 def __reduce__(self): 

4356 return self.__class__, (self.element,) + tuple(self.order_by) 

4357 

4358 def over(self, partition_by=None, order_by=None, range_=None, rows=None): 

4359 """Produce an OVER clause against this :class:`.WithinGroup` 

4360 construct. 

4361 

4362 This function has the same signature as that of 

4363 :meth:`.FunctionElement.over`. 

4364 

4365 """ 

4366 return Over( 

4367 self, 

4368 partition_by=partition_by, 

4369 order_by=order_by, 

4370 range_=range_, 

4371 rows=rows, 

4372 ) 

4373 

4374 @util.memoized_property 

4375 def type(self): 

4376 wgt = self.element.within_group_type(self) 

4377 if wgt is not None: 

4378 return wgt 

4379 else: 

4380 return self.element.type 

4381 

4382 @property 

4383 def _from_objects(self): 

4384 return list( 

4385 itertools.chain( 

4386 *[ 

4387 c._from_objects 

4388 for c in (self.element, self.order_by) 

4389 if c is not None 

4390 ] 

4391 ) 

4392 ) 

4393 

4394 

4395class FunctionFilter(ColumnElement): 

4396 """Represent a function FILTER clause. 

4397 

4398 This is a special operator against aggregate and window functions, 

4399 which controls which rows are passed to it. 

4400 It's supported only by certain database backends. 

4401 

4402 Invocation of :class:`.FunctionFilter` is via 

4403 :meth:`.FunctionElement.filter`:: 

4404 

4405 func.count(1).filter(True) 

4406 

4407 .. versionadded:: 1.0.0 

4408 

4409 .. seealso:: 

4410 

4411 :meth:`.FunctionElement.filter` 

4412 

4413 """ 

4414 

4415 __visit_name__ = "funcfilter" 

4416 

4417 _traverse_internals = [ 

4418 ("func", InternalTraversal.dp_clauseelement), 

4419 ("criterion", InternalTraversal.dp_clauseelement), 

4420 ] 

4421 

4422 criterion = None 

4423 

4424 def __init__(self, func, *criterion): 

4425 """Produce a :class:`.FunctionFilter` object against a function. 

4426 

4427 Used against aggregate and window functions, 

4428 for database backends that support the "FILTER" clause. 

4429 

4430 E.g.:: 

4431 

4432 from sqlalchemy import funcfilter 

4433 funcfilter(func.count(1), MyClass.name == 'some name') 

4434 

4435 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')". 

4436 

4437 This function is also available from the :data:`~.expression.func` 

4438 construct itself via the :meth:`.FunctionElement.filter` method. 

4439 

4440 .. versionadded:: 1.0.0 

4441 

4442 .. seealso:: 

4443 

4444 :ref:`tutorial_functions_within_group` - in the 

4445 :ref:`unified_tutorial` 

4446 

4447 :meth:`.FunctionElement.filter` 

4448 

4449 """ 

4450 self.func = func 

4451 self.filter(*criterion) 

4452 

4453 def filter(self, *criterion): 

4454 """Produce an additional FILTER against the function. 

4455 

4456 This method adds additional criteria to the initial criteria 

4457 set up by :meth:`.FunctionElement.filter`. 

4458 

4459 Multiple criteria are joined together at SQL render time 

4460 via ``AND``. 

4461 

4462 

4463 """ 

4464 

4465 for criterion in list(criterion): 

4466 criterion = coercions.expect(roles.WhereHavingRole, criterion) 

4467 

4468 if self.criterion is not None: 

4469 self.criterion = self.criterion & criterion 

4470 else: 

4471 self.criterion = criterion 

4472 

4473 return self 

4474 

4475 def over(self, partition_by=None, order_by=None, range_=None, rows=None): 

4476 """Produce an OVER clause against this filtered function. 

4477 

4478 Used against aggregate or so-called "window" functions, 

4479 for database backends that support window functions. 

4480 

4481 The expression:: 

4482 

4483 func.rank().filter(MyClass.y > 5).over(order_by='x') 

4484 

4485 is shorthand for:: 

4486 

4487 from sqlalchemy import over, funcfilter 

4488 over(funcfilter(func.rank(), MyClass.y > 5), order_by='x') 

4489 

4490 See :func:`_expression.over` for a full description. 

4491 

4492 """ 

4493 return Over( 

4494 self, 

4495 partition_by=partition_by, 

4496 order_by=order_by, 

4497 range_=range_, 

4498 rows=rows, 

4499 ) 

4500 

4501 def self_group(self, against=None): 

4502 if operators.is_precedent(operators.filter_op, against): 

4503 return Grouping(self) 

4504 else: 

4505 return self 

4506 

4507 @util.memoized_property 

4508 def type(self): 

4509 return self.func.type 

4510 

4511 @property 

4512 def _from_objects(self): 

4513 return list( 

4514 itertools.chain( 

4515 *[ 

4516 c._from_objects 

4517 for c in (self.func, self.criterion) 

4518 if c is not None 

4519 ] 

4520 ) 

4521 ) 

4522 

4523 

4524class Label(roles.LabeledColumnExprRole, ColumnElement): 

4525 """Represents a column label (AS). 

4526 

4527 Represent a label, as typically applied to any column-level 

4528 element using the ``AS`` sql keyword. 

4529 

4530 """ 

4531 

4532 __visit_name__ = "label" 

4533 

4534 _traverse_internals = [ 

4535 ("name", InternalTraversal.dp_anon_name), 

4536 ("_type", InternalTraversal.dp_type), 

4537 ("_element", InternalTraversal.dp_clauseelement), 

4538 ] 

4539 

4540 _cache_key_traversal = [ 

4541 ("name", InternalTraversal.dp_anon_name), 

4542 ("_element", InternalTraversal.dp_clauseelement), 

4543 ] 

4544 

4545 def __init__(self, name, element, type_=None): 

4546 """Return a :class:`Label` object for the 

4547 given :class:`_expression.ColumnElement`. 

4548 

4549 A label changes the name of an element in the columns clause of a 

4550 ``SELECT`` statement, typically via the ``AS`` SQL keyword. 

4551 

4552 This functionality is more conveniently available via the 

4553 :meth:`_expression.ColumnElement.label` method on 

4554 :class:`_expression.ColumnElement`. 

4555 

4556 :param name: label name 

4557 

4558 :param obj: a :class:`_expression.ColumnElement`. 

4559 

4560 """ 

4561 

4562 orig_element = element 

4563 element = coercions.expect( 

4564 roles.ExpressionElementRole, 

4565 element, 

4566 apply_propagate_attrs=self, 

4567 ) 

4568 while isinstance(element, Label): 

4569 # TODO: this is only covered in test_text.py, but nothing 

4570 # fails if it's removed. determine rationale 

4571 element = element.element 

4572 

4573 if name: 

4574 self.name = name 

4575 else: 

4576 self.name = _anonymous_label.safe_construct( 

4577 id(self), getattr(element, "name", "anon") 

4578 ) 

4579 if isinstance(orig_element, Label): 

4580 # TODO: no coverage for this block, again would be in 

4581 # test_text.py where the resolve_label concept is important 

4582 self._resolve_label = orig_element._label 

4583 

4584 self.key = self._tq_label = self._tq_key_label = self.name 

4585 self._element = element 

4586 self._type = type_ 

4587 self._proxies = [element] 

4588 

4589 def __reduce__(self): 

4590 return self.__class__, (self.name, self._element, self._type) 

4591 

4592 @util.memoized_property 

4593 def _is_implicitly_boolean(self): 

4594 return self.element._is_implicitly_boolean 

4595 

4596 @HasMemoized.memoized_attribute 

4597 def _allow_label_resolve(self): 

4598 return self.element._allow_label_resolve 

4599 

4600 @property 

4601 def _order_by_label_element(self): 

4602 return self 

4603 

4604 @util.memoized_property 

4605 def type(self): 

4606 return type_api.to_instance( 

4607 self._type or getattr(self._element, "type", None) 

4608 ) 

4609 

4610 @HasMemoized.memoized_attribute 

4611 def element(self): 

4612 return self._element.self_group(against=operators.as_) 

4613 

4614 def self_group(self, against=None): 

4615 return self._apply_to_inner(self._element.self_group, against=against) 

4616 

4617 def _negate(self): 

4618 return self._apply_to_inner(self._element._negate) 

4619 

4620 def _apply_to_inner(self, fn, *arg, **kw): 

4621 sub_element = fn(*arg, **kw) 

4622 if sub_element is not self._element: 

4623 return Label(self.name, sub_element, type_=self._type) 

4624 else: 

4625 return self 

4626 

4627 @property 

4628 def primary_key(self): 

4629 return self.element.primary_key 

4630 

4631 @property 

4632 def foreign_keys(self): 

4633 return self.element.foreign_keys 

4634 

4635 def _copy_internals(self, clone=_clone, anonymize_labels=False, **kw): 

4636 self._reset_memoizations() 

4637 self._element = clone(self._element, **kw) 

4638 if anonymize_labels: 

4639 self.name = _anonymous_label.safe_construct( 

4640 id(self), getattr(self.element, "name", "anon") 

4641 ) 

4642 self.key = self._tq_label = self._tq_key_label = self.name 

4643 

4644 @property 

4645 def _from_objects(self): 

4646 return self.element._from_objects 

4647 

4648 def _make_proxy(self, selectable, name=None, **kw): 

4649 name = self.name if not name else name 

4650 

4651 key, e = self.element._make_proxy( 

4652 selectable, 

4653 name=name, 

4654 disallow_is_literal=True, 

4655 name_is_truncatable=isinstance(name, _truncated_label), 

4656 ) 

4657 

4658 # there was a note here to remove this assertion, which was here 

4659 # to determine if we later could support a use case where 

4660 # the key and name of a label are separate. But I don't know what 

4661 # that case was. For now, this is an unexpected case that occurs 

4662 # when a label name conflicts with other columns and select() 

4663 # is attempting to disambiguate an explicit label, which is not what 

4664 # the user would want. See issue #6090. 

4665 if key != self.name and not isinstance(self.name, _anonymous_label): 

4666 raise exc.InvalidRequestError( 

4667 "Label name %s is being renamed to an anonymous label due " 

4668 "to disambiguation " 

4669 "which is not supported right now. Please use unique names " 

4670 "for explicit labels." % (self.name) 

4671 ) 

4672 

4673 e._propagate_attrs = selectable._propagate_attrs 

4674 e._proxies.append(self) 

4675 if self._type is not None: 

4676 e.type = self._type 

4677 

4678 return self.key, e 

4679 

4680 

4681class NamedColumn(ColumnElement): 

4682 is_literal = False 

4683 table = None 

4684 

4685 def _compare_name_for_result(self, other): 

4686 return (hasattr(other, "name") and self.name == other.name) or ( 

4687 hasattr(other, "_label") and self._label == other._label 

4688 ) 

4689 

4690 @util.memoized_property 

4691 def description(self): 

4692 if util.py3k: 

4693 return self.name 

4694 else: 

4695 return self.name.encode("ascii", "backslashreplace") 

4696 

4697 @HasMemoized.memoized_attribute 

4698 def _tq_key_label(self): 

4699 """table qualified label based on column key. 

4700 

4701 for table-bound columns this is <tablename>_<column key/proxy key>; 

4702 

4703 all other expressions it resolves to key/proxy key. 

4704 

4705 """ 

4706 proxy_key = self._proxy_key 

4707 if proxy_key and proxy_key != self.name: 

4708 return self._gen_tq_label(proxy_key) 

4709 else: 

4710 return self._tq_label 

4711 

4712 @HasMemoized.memoized_attribute 

4713 def _tq_label(self): 

4714 """table qualified label based on column name. 

4715 

4716 for table-bound columns this is <tablename>_<columnname>; all other 

4717 expressions it resolves to .name. 

4718 

4719 """ 

4720 return self._gen_tq_label(self.name) 

4721 

4722 @HasMemoized.memoized_attribute 

4723 def _render_label_in_columns_clause(self): 

4724 return True 

4725 

4726 @HasMemoized.memoized_attribute 

4727 def _non_anon_label(self): 

4728 return self.name 

4729 

4730 def _gen_tq_label(self, name, dedupe_on_key=True): 

4731 return name 

4732 

4733 def _bind_param(self, operator, obj, type_=None, expanding=False): 

4734 return BindParameter( 

4735 self.key, 

4736 obj, 

4737 _compared_to_operator=operator, 

4738 _compared_to_type=self.type, 

4739 type_=type_, 

4740 unique=True, 

4741 expanding=expanding, 

4742 ) 

4743 

4744 def _make_proxy( 

4745 self, 

4746 selectable, 

4747 name=None, 

4748 name_is_truncatable=False, 

4749 disallow_is_literal=False, 

4750 **kw 

4751 ): 

4752 c = ColumnClause( 

4753 coercions.expect(roles.TruncatedLabelRole, name or self.name) 

4754 if name_is_truncatable 

4755 else (name or self.name), 

4756 type_=self.type, 

4757 _selectable=selectable, 

4758 is_literal=False, 

4759 ) 

4760 c._propagate_attrs = selectable._propagate_attrs 

4761 if name is None: 

4762 c.key = self.key 

4763 c._proxies = [self] 

4764 if selectable._is_clone_of is not None: 

4765 c._is_clone_of = selectable._is_clone_of.columns.get(c.key) 

4766 return c.key, c 

4767 

4768 

4769class ColumnClause( 

4770 roles.DDLReferredColumnRole, 

4771 roles.LabeledColumnExprRole, 

4772 roles.StrAsPlainColumnRole, 

4773 Immutable, 

4774 NamedColumn, 

4775): 

4776 """Represents a column expression from any textual string. 

4777 

4778 The :class:`.ColumnClause`, a lightweight analogue to the 

4779 :class:`_schema.Column` class, is typically invoked using the 

4780 :func:`_expression.column` function, as in:: 

4781 

4782 from sqlalchemy import column 

4783 

4784 id, name = column("id"), column("name") 

4785 stmt = select(id, name).select_from("user") 

4786 

4787 The above statement would produce SQL like:: 

4788 

4789 SELECT id, name FROM user 

4790 

4791 :class:`.ColumnClause` is the immediate superclass of the schema-specific 

4792 :class:`_schema.Column` object. While the :class:`_schema.Column` 

4793 class has all the 

4794 same capabilities as :class:`.ColumnClause`, the :class:`.ColumnClause` 

4795 class is usable by itself in those cases where behavioral requirements 

4796 are limited to simple SQL expression generation. The object has none of 

4797 the associations with schema-level metadata or with execution-time 

4798 behavior that :class:`_schema.Column` does, 

4799 so in that sense is a "lightweight" 

4800 version of :class:`_schema.Column`. 

4801 

4802 Full details on :class:`.ColumnClause` usage is at 

4803 :func:`_expression.column`. 

4804 

4805 .. seealso:: 

4806 

4807 :func:`_expression.column` 

4808 

4809 :class:`_schema.Column` 

4810 

4811 """ 

4812 

4813 table = None 

4814 is_literal = False 

4815 

4816 __visit_name__ = "column" 

4817 

4818 _traverse_internals = [ 

4819 ("name", InternalTraversal.dp_anon_name), 

4820 ("type", InternalTraversal.dp_type), 

4821 ("table", InternalTraversal.dp_clauseelement), 

4822 ("is_literal", InternalTraversal.dp_boolean), 

4823 ] 

4824 

4825 onupdate = default = server_default = server_onupdate = None 

4826 

4827 _is_multiparam_column = False 

4828 

4829 @property 

4830 def _is_star(self): 

4831 return self.is_literal and self.name == "*" 

4832 

4833 def __init__(self, text, type_=None, is_literal=False, _selectable=None): 

4834 """Produce a :class:`.ColumnClause` object. 

4835 

4836 The :class:`.ColumnClause` is a lightweight analogue to the 

4837 :class:`_schema.Column` class. The :func:`_expression.column` 

4838 function can 

4839 be invoked with just a name alone, as in:: 

4840 

4841 from sqlalchemy import column 

4842 

4843 id, name = column("id"), column("name") 

4844 stmt = select(id, name).select_from("user") 

4845 

4846 The above statement would produce SQL like:: 

4847 

4848 SELECT id, name FROM user 

4849 

4850 Once constructed, :func:`_expression.column` 

4851 may be used like any other SQL 

4852 expression element such as within :func:`_expression.select` 

4853 constructs:: 

4854 

4855 from sqlalchemy.sql import column 

4856 

4857 id, name = column("id"), column("name") 

4858 stmt = select(id, name).select_from("user") 

4859 

4860 The text handled by :func:`_expression.column` 

4861 is assumed to be handled 

4862 like the name of a database column; if the string contains mixed case, 

4863 special characters, or matches a known reserved word on the target 

4864 backend, the column expression will render using the quoting 

4865 behavior determined by the backend. To produce a textual SQL 

4866 expression that is rendered exactly without any quoting, 

4867 use :func:`_expression.literal_column` instead, 

4868 or pass ``True`` as the 

4869 value of :paramref:`_expression.column.is_literal`. Additionally, 

4870 full SQL 

4871 statements are best handled using the :func:`_expression.text` 

4872 construct. 

4873 

4874 :func:`_expression.column` can be used in a table-like 

4875 fashion by combining it with the :func:`.table` function 

4876 (which is the lightweight analogue to :class:`_schema.Table` 

4877 ) to produce 

4878 a working table construct with minimal boilerplate:: 

4879 

4880 from sqlalchemy import table, column, select 

4881 

4882 user = table("user", 

4883 column("id"), 

4884 column("name"), 

4885 column("description"), 

4886 ) 

4887 

4888 stmt = select(user.c.description).where(user.c.name == 'wendy') 

4889 

4890 A :func:`_expression.column` / :func:`.table` 

4891 construct like that illustrated 

4892 above can be created in an 

4893 ad-hoc fashion and is not associated with any 

4894 :class:`_schema.MetaData`, DDL, or events, unlike its 

4895 :class:`_schema.Table` counterpart. 

4896 

4897 .. versionchanged:: 1.0.0 :func:`_expression.column` can now 

4898 be imported from the plain ``sqlalchemy`` namespace like any 

4899 other SQL element. 

4900 

4901 :param text: the text of the element. 

4902 

4903 :param type: :class:`_types.TypeEngine` object which can associate 

4904 this :class:`.ColumnClause` with a type. 

4905 

4906 :param is_literal: if True, the :class:`.ColumnClause` is assumed to 

4907 be an exact expression that will be delivered to the output with no 

4908 quoting rules applied regardless of case sensitive settings. the 

4909 :func:`_expression.literal_column()` function essentially invokes 

4910 :func:`_expression.column` while passing ``is_literal=True``. 

4911 

4912 .. seealso:: 

4913 

4914 :class:`_schema.Column` 

4915 

4916 :func:`_expression.literal_column` 

4917 

4918 :func:`.table` 

4919 

4920 :func:`_expression.text` 

4921 

4922 :ref:`tutorial_select_arbitrary_text` 

4923 

4924 """ 

4925 self.key = self.name = text 

4926 self.table = _selectable 

4927 self.type = type_api.to_instance(type_) 

4928 self.is_literal = is_literal 

4929 

4930 def get_children(self, column_tables=False, **kw): 

4931 # override base get_children() to not return the Table 

4932 # or selectable that is parent to this column. Traversals 

4933 # expect the columns of tables and subqueries to be leaf nodes. 

4934 return [] 

4935 

4936 @property 

4937 def entity_namespace(self): 

4938 if self.table is not None: 

4939 return self.table.entity_namespace 

4940 else: 

4941 return super(ColumnClause, self).entity_namespace 

4942 

4943 def _clone(self, detect_subquery_cols=False, **kw): 

4944 if ( 

4945 detect_subquery_cols 

4946 and self.table is not None 

4947 and self.table._is_subquery 

4948 ): 

4949 clone = kw.pop("clone") 

4950 table = clone(self.table, **kw) 

4951 new = table.c.corresponding_column(self) 

4952 return new 

4953 

4954 return super(ColumnClause, self)._clone(**kw) 

4955 

4956 @HasMemoized.memoized_attribute 

4957 def _from_objects(self): 

4958 t = self.table 

4959 if t is not None: 

4960 return [t] 

4961 else: 

4962 return [] 

4963 

4964 @HasMemoized.memoized_attribute 

4965 def _render_label_in_columns_clause(self): 

4966 return self.table is not None 

4967 

4968 @property 

4969 def _ddl_label(self): 

4970 return self._gen_tq_label(self.name, dedupe_on_key=False) 

4971 

4972 def _compare_name_for_result(self, other): 

4973 if ( 

4974 self.is_literal 

4975 or self.table is None 

4976 or self.table._is_textual 

4977 or not hasattr(other, "proxy_set") 

4978 or ( 

4979 isinstance(other, ColumnClause) 

4980 and ( 

4981 other.is_literal 

4982 or other.table is None 

4983 or other.table._is_textual 

4984 ) 

4985 ) 

4986 ): 

4987 return (hasattr(other, "name") and self.name == other.name) or ( 

4988 hasattr(other, "_tq_label") 

4989 and self._tq_label == other._tq_label 

4990 ) 

4991 else: 

4992 return other.proxy_set.intersection(self.proxy_set) 

4993 

4994 def _gen_tq_label(self, name, dedupe_on_key=True): 

4995 """generate table-qualified label 

4996 

4997 for a table-bound column this is <tablename>_<columnname>. 

4998 

4999 used primarily for LABEL_STYLE_TABLENAME_PLUS_COL 

5000 as well as the .columns collection on a Join object. 

5001 

5002 """ 

5003 t = self.table 

5004 if self.is_literal: 

5005 return None 

5006 elif t is not None and t.named_with_column: 

5007 if getattr(t, "schema", None): 

5008 label = t.schema.replace(".", "_") + "_" + t.name + "_" + name 

5009 else: 

5010 label = t.name + "_" + name 

5011 

5012 # propagate name quoting rules for labels. 

5013 if getattr(name, "quote", None) is not None: 

5014 if isinstance(label, quoted_name): 

5015 label.quote = name.quote 

5016 else: 

5017 label = quoted_name(label, name.quote) 

5018 elif getattr(t.name, "quote", None) is not None: 

5019 # can't get this situation to occur, so let's 

5020 # assert false on it for now 

5021 assert not isinstance(label, quoted_name) 

5022 label = quoted_name(label, t.name.quote) 

5023 

5024 if dedupe_on_key: 

5025 # ensure the label name doesn't conflict with that of an 

5026 # existing column. note that this implies that any Column 

5027 # must **not** set up its _label before its parent table has 

5028 # all of its other Column objects set up. There are several 

5029 # tables in the test suite which will fail otherwise; example: 

5030 # table "owner" has columns "name" and "owner_name". Therefore 

5031 # column owner.name cannot use the label "owner_name", it has 

5032 # to be "owner_name_1". 

5033 if label in t.c: 

5034 _label = label 

5035 counter = 1 

5036 while _label in t.c: 

5037 _label = label + "_" + str(counter) 

5038 counter += 1 

5039 label = _label 

5040 

5041 return coercions.expect(roles.TruncatedLabelRole, label) 

5042 

5043 else: 

5044 return name 

5045 

5046 def _make_proxy( 

5047 self, 

5048 selectable, 

5049 name=None, 

5050 name_is_truncatable=False, 

5051 disallow_is_literal=False, 

5052 **kw 

5053 ): 

5054 # the "is_literal" flag normally should never be propagated; a proxied 

5055 # column is always a SQL identifier and never the actual expression 

5056 # being evaluated. however, there is a case where the "is_literal" flag 

5057 # might be used to allow the given identifier to have a fixed quoting 

5058 # pattern already, so maintain the flag for the proxy unless a 

5059 # :class:`.Label` object is creating the proxy. See [ticket:4730]. 

5060 is_literal = ( 

5061 not disallow_is_literal 

5062 and self.is_literal 

5063 and ( 

5064 # note this does not accommodate for quoted_name differences 

5065 # right now 

5066 name is None 

5067 or name == self.name 

5068 ) 

5069 ) 

5070 c = self._constructor( 

5071 coercions.expect(roles.TruncatedLabelRole, name or self.name) 

5072 if name_is_truncatable 

5073 else (name or self.name), 

5074 type_=self.type, 

5075 _selectable=selectable, 

5076 is_literal=is_literal, 

5077 ) 

5078 c._propagate_attrs = selectable._propagate_attrs 

5079 if name is None: 

5080 c.key = self.key 

5081 c._proxies = [self] 

5082 if selectable._is_clone_of is not None: 

5083 c._is_clone_of = selectable._is_clone_of.columns.get(c.key) 

5084 return c.key, c 

5085 

5086 

5087class TableValuedColumn(NamedColumn): 

5088 __visit_name__ = "table_valued_column" 

5089 

5090 _traverse_internals = [ 

5091 ("name", InternalTraversal.dp_anon_name), 

5092 ("type", InternalTraversal.dp_type), 

5093 ("scalar_alias", InternalTraversal.dp_clauseelement), 

5094 ] 

5095 

5096 def __init__(self, scalar_alias, type_): 

5097 self.scalar_alias = scalar_alias 

5098 self.key = self.name = scalar_alias.name 

5099 self.type = type_ 

5100 

5101 def _copy_internals(self, clone=_clone, **kw): 

5102 self.scalar_alias = clone(self.scalar_alias, **kw) 

5103 self.key = self.name = self.scalar_alias.name 

5104 

5105 @property 

5106 def _from_objects(self): 

5107 return [self.scalar_alias] 

5108 

5109 

5110class CollationClause(ColumnElement): 

5111 __visit_name__ = "collation" 

5112 

5113 _traverse_internals = [("collation", InternalTraversal.dp_string)] 

5114 

5115 def __init__(self, collation): 

5116 self.collation = collation 

5117 

5118 

5119class _IdentifiedClause(Executable, ClauseElement): 

5120 

5121 __visit_name__ = "identified" 

5122 _execution_options = Executable._execution_options.union( 

5123 {"autocommit": False} 

5124 ) 

5125 

5126 def __init__(self, ident): 

5127 self.ident = ident 

5128 

5129 

5130class SavepointClause(_IdentifiedClause): 

5131 __visit_name__ = "savepoint" 

5132 inherit_cache = False 

5133 

5134 

5135class RollbackToSavepointClause(_IdentifiedClause): 

5136 __visit_name__ = "rollback_to_savepoint" 

5137 inherit_cache = False 

5138 

5139 

5140class ReleaseSavepointClause(_IdentifiedClause): 

5141 __visit_name__ = "release_savepoint" 

5142 inherit_cache = False 

5143 

5144 

5145class quoted_name(util.MemoizedSlots, util.text_type): 

5146 """Represent a SQL identifier combined with quoting preferences. 

5147 

5148 :class:`.quoted_name` is a Python unicode/str subclass which 

5149 represents a particular identifier name along with a 

5150 ``quote`` flag. This ``quote`` flag, when set to 

5151 ``True`` or ``False``, overrides automatic quoting behavior 

5152 for this identifier in order to either unconditionally quote 

5153 or to not quote the name. If left at its default of ``None``, 

5154 quoting behavior is applied to the identifier on a per-backend basis 

5155 based on an examination of the token itself. 

5156 

5157 A :class:`.quoted_name` object with ``quote=True`` is also 

5158 prevented from being modified in the case of a so-called 

5159 "name normalize" option. Certain database backends, such as 

5160 Oracle, Firebird, and DB2 "normalize" case-insensitive names 

5161 as uppercase. The SQLAlchemy dialects for these backends 

5162 convert from SQLAlchemy's lower-case-means-insensitive convention 

5163 to the upper-case-means-insensitive conventions of those backends. 

5164 The ``quote=True`` flag here will prevent this conversion from occurring 

5165 to support an identifier that's quoted as all lower case against 

5166 such a backend. 

5167 

5168 The :class:`.quoted_name` object is normally created automatically 

5169 when specifying the name for key schema constructs such as 

5170 :class:`_schema.Table`, :class:`_schema.Column`, and others. 

5171 The class can also be 

5172 passed explicitly as the name to any function that receives a name which 

5173 can be quoted. Such as to use the :meth:`_engine.Engine.has_table` 

5174 method with 

5175 an unconditionally quoted name:: 

5176 

5177 from sqlalchemy import create_engine 

5178 from sqlalchemy import inspect 

5179 from sqlalchemy.sql import quoted_name 

5180 

5181 engine = create_engine("oracle+cx_oracle://some_dsn") 

5182 print(inspect(engine).has_table(quoted_name("some_table", True))) 

5183 

5184 The above logic will run the "has table" logic against the Oracle backend, 

5185 passing the name exactly as ``"some_table"`` without converting to 

5186 upper case. 

5187 

5188 .. versionadded:: 0.9.0 

5189 

5190 .. versionchanged:: 1.2 The :class:`.quoted_name` construct is now 

5191 importable from ``sqlalchemy.sql``, in addition to the previous 

5192 location of ``sqlalchemy.sql.elements``. 

5193 

5194 """ 

5195 

5196 __slots__ = "quote", "lower", "upper" 

5197 

5198 def __new__(cls, value, quote): 

5199 if value is None: 

5200 return None 

5201 # experimental - don't bother with quoted_name 

5202 # if quote flag is None. doesn't seem to make any dent 

5203 # in performance however 

5204 # elif not sprcls and quote is None: 

5205 # return value 

5206 elif isinstance(value, cls) and ( 

5207 quote is None or value.quote == quote 

5208 ): 

5209 return value 

5210 self = super(quoted_name, cls).__new__(cls, value) 

5211 

5212 self.quote = quote 

5213 return self 

5214 

5215 def __reduce__(self): 

5216 return quoted_name, (util.text_type(self), self.quote) 

5217 

5218 def _memoized_method_lower(self): 

5219 if self.quote: 

5220 return self 

5221 else: 

5222 return util.text_type(self).lower() 

5223 

5224 def _memoized_method_upper(self): 

5225 if self.quote: 

5226 return self 

5227 else: 

5228 return util.text_type(self).upper() 

5229 

5230 def __repr__(self): 

5231 if util.py2k: 

5232 backslashed = self.encode("ascii", "backslashreplace") 

5233 if not util.py2k: 

5234 backslashed = backslashed.decode("ascii") 

5235 return "'%s'" % backslashed 

5236 else: 

5237 return str.__repr__(self) 

5238 

5239 

5240def _find_columns(clause): 

5241 """locate Column objects within the given expression.""" 

5242 

5243 cols = util.column_set() 

5244 traverse(clause, {}, {"column": cols.add}) 

5245 return cols 

5246 

5247 

5248def _type_from_args(args): 

5249 for a in args: 

5250 if not a.type._isnull: 

5251 return a.type 

5252 else: 

5253 return type_api.NULLTYPE 

5254 

5255 

5256def _corresponding_column_or_error(fromclause, column, require_embedded=False): 

5257 c = fromclause.corresponding_column( 

5258 column, require_embedded=require_embedded 

5259 ) 

5260 if c is None: 

5261 raise exc.InvalidRequestError( 

5262 "Given column '%s', attached to table '%s', " 

5263 "failed to locate a corresponding column from table '%s'" 

5264 % (column, getattr(column, "table", None), fromclause.description) 

5265 ) 

5266 return c 

5267 

5268 

5269class AnnotatedColumnElement(Annotated): 

5270 def __init__(self, element, values): 

5271 Annotated.__init__(self, element, values) 

5272 for attr in ( 

5273 "comparator", 

5274 "_proxy_key", 

5275 "_tq_key_label", 

5276 "_tq_label", 

5277 "_non_anon_label", 

5278 ): 

5279 self.__dict__.pop(attr, None) 

5280 for attr in ("name", "key", "table"): 

5281 if self.__dict__.get(attr, False) is None: 

5282 self.__dict__.pop(attr) 

5283 

5284 def _with_annotations(self, values): 

5285 clone = super(AnnotatedColumnElement, self)._with_annotations(values) 

5286 clone.__dict__.pop("comparator", None) 

5287 return clone 

5288 

5289 @util.memoized_property 

5290 def name(self): 

5291 """pull 'name' from parent, if not present""" 

5292 return self._Annotated__element.name 

5293 

5294 @util.memoized_property 

5295 def table(self): 

5296 """pull 'table' from parent, if not present""" 

5297 return self._Annotated__element.table 

5298 

5299 @util.memoized_property 

5300 def key(self): 

5301 """pull 'key' from parent, if not present""" 

5302 return self._Annotated__element.key 

5303 

5304 @util.memoized_property 

5305 def info(self): 

5306 return self._Annotated__element.info 

5307 

5308 @util.memoized_property 

5309 def _anon_name_label(self): 

5310 return self._Annotated__element._anon_name_label 

5311 

5312 

5313class _truncated_label(quoted_name): 

5314 """A unicode subclass used to identify symbolic " 

5315 "names that may require truncation.""" 

5316 

5317 __slots__ = () 

5318 

5319 def __new__(cls, value, quote=None): 

5320 quote = getattr(value, "quote", quote) 

5321 # return super(_truncated_label, cls).__new__(cls, value, quote, True) 

5322 return super(_truncated_label, cls).__new__(cls, value, quote) 

5323 

5324 def __reduce__(self): 

5325 return self.__class__, (util.text_type(self), self.quote) 

5326 

5327 def apply_map(self, map_): 

5328 return self 

5329 

5330 

5331class conv(_truncated_label): 

5332 """Mark a string indicating that a name has already been converted 

5333 by a naming convention. 

5334 

5335 This is a string subclass that indicates a name that should not be 

5336 subject to any further naming conventions. 

5337 

5338 E.g. when we create a :class:`.Constraint` using a naming convention 

5339 as follows:: 

5340 

5341 m = MetaData(naming_convention={ 

5342 "ck": "ck_%(table_name)s_%(constraint_name)s" 

5343 }) 

5344 t = Table('t', m, Column('x', Integer), 

5345 CheckConstraint('x > 5', name='x5')) 

5346 

5347 The name of the above constraint will be rendered as ``"ck_t_x5"``. 

5348 That is, the existing name ``x5`` is used in the naming convention as the 

5349 ``constraint_name`` token. 

5350 

5351 In some situations, such as in migration scripts, we may be rendering 

5352 the above :class:`.CheckConstraint` with a name that's already been 

5353 converted. In order to make sure the name isn't double-modified, the 

5354 new name is applied using the :func:`_schema.conv` marker. We can 

5355 use this explicitly as follows:: 

5356 

5357 

5358 m = MetaData(naming_convention={ 

5359 "ck": "ck_%(table_name)s_%(constraint_name)s" 

5360 }) 

5361 t = Table('t', m, Column('x', Integer), 

5362 CheckConstraint('x > 5', name=conv('ck_t_x5'))) 

5363 

5364 Where above, the :func:`_schema.conv` marker indicates that the constraint 

5365 name here is final, and the name will render as ``"ck_t_x5"`` and not 

5366 ``"ck_t_ck_t_x5"`` 

5367 

5368 .. versionadded:: 0.9.4 

5369 

5370 .. seealso:: 

5371 

5372 :ref:`constraint_naming_conventions` 

5373 

5374 """ 

5375 

5376 __slots__ = () 

5377 

5378 

5379_NONE_NAME = util.symbol("NONE_NAME") 

5380"""indicate a 'deferred' name that was ultimately the value None.""" 

5381 

5382# for backwards compatibility in case 

5383# someone is re-implementing the 

5384# _truncated_identifier() sequence in a custom 

5385# compiler 

5386_generated_label = _truncated_label 

5387 

5388 

5389class _anonymous_label(_truncated_label): 

5390 """A unicode subclass used to identify anonymously 

5391 generated names.""" 

5392 

5393 __slots__ = () 

5394 

5395 @classmethod 

5396 def safe_construct( 

5397 cls, seed, body, enclosing_label=None, sanitize_key=False 

5398 ): 

5399 

5400 # need to escape chars that interfere with format 

5401 # strings in any case, issue #8724 

5402 body = re.sub(r"[%\(\) \$]+", "_", body) 

5403 

5404 if sanitize_key: 

5405 # sanitize_key is then an extra step used by BindParameter 

5406 body = body.strip("_") 

5407 

5408 label = "%%(%d %s)s" % (seed, body.replace("%", "%%")) 

5409 if enclosing_label: 

5410 label = "%s%s" % (enclosing_label, label) 

5411 

5412 return _anonymous_label(label) 

5413 

5414 def __add__(self, other): 

5415 if "%" in other and not isinstance(other, _anonymous_label): 

5416 other = util.text_type(other).replace("%", "%%") 

5417 else: 

5418 other = util.text_type(other) 

5419 

5420 return _anonymous_label( 

5421 quoted_name( 

5422 util.text_type.__add__(self, other), 

5423 self.quote, 

5424 ) 

5425 ) 

5426 

5427 def __radd__(self, other): 

5428 if "%" in other and not isinstance(other, _anonymous_label): 

5429 other = util.text_type(other).replace("%", "%%") 

5430 else: 

5431 other = util.text_type(other) 

5432 

5433 return _anonymous_label( 

5434 quoted_name( 

5435 util.text_type.__add__(other, self), 

5436 self.quote, 

5437 ) 

5438 ) 

5439 

5440 def apply_map(self, map_): 

5441 if self.quote is not None: 

5442 # preserve quoting only if necessary 

5443 return quoted_name(self % map_, self.quote) 

5444 else: 

5445 # else skip the constructor call 

5446 return self % map_