1# sql/elements.py 2# Copyright (C) 2005-2021 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 """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 207 _is_textual = False 208 _is_from_clause = False 209 _is_returns_rows = False 210 _is_text_clause = False 211 _is_from_container = False 212 _is_select_container = False 213 _is_select_statement = False 214 _is_bind_parameter = False 215 _is_clause_list = False 216 _is_lambda_element = False 217 _is_singleton_constant = False 218 _is_immutable = False 219 220 _order_by_label_element = None 221 222 _cache_key_traversal = None 223 224 def _set_propagate_attrs(self, values): 225 # usually, self._propagate_attrs is empty here. one case where it's 226 # not is a subquery against ORM select, that is then pulled as a 227 # property of an aliased class. should all be good 228 229 # assert not self._propagate_attrs 230 231 self._propagate_attrs = util.immutabledict(values) 232 return self 233 234 def _clone(self, **kw): 235 """Create a shallow copy of this ClauseElement. 236 237 This method may be used by a generative API. Its also used as 238 part of the "deep" copy afforded by a traversal that combines 239 the _copy_internals() method. 240 241 """ 242 skip = self._memoized_keys 243 c = self.__class__.__new__(self.__class__) 244 c.__dict__ = {k: v for k, v in self.__dict__.items() if k not in skip} 245 246 # this is a marker that helps to "equate" clauses to each other 247 # when a Select returns its list of FROM clauses. the cloning 248 # process leaves around a lot of remnants of the previous clause 249 # typically in the form of column expressions still attached to the 250 # old table. 251 c._is_clone_of = self 252 253 return c 254 255 def _negate_in_binary(self, negated_op, original_op): 256 """a hook to allow the right side of a binary expression to respond 257 to a negation of the binary expression. 258 259 Used for the special case of expanding bind parameter with IN. 260 261 """ 262 return self 263 264 def _with_binary_element_type(self, type_): 265 """in the context of binary expression, convert the type of this 266 object to the one given. 267 268 applies only to :class:`_expression.ColumnElement` classes. 269 270 """ 271 return self 272 273 @property 274 def _constructor(self): 275 """return the 'constructor' for this ClauseElement. 276 277 This is for the purposes for creating a new object of 278 this type. Usually, its just the element's __class__. 279 However, the "Annotated" version of the object overrides 280 to return the class of its proxied element. 281 282 """ 283 return self.__class__ 284 285 @HasMemoized.memoized_attribute 286 def _cloned_set(self): 287 """Return the set consisting all cloned ancestors of this 288 ClauseElement. 289 290 Includes this ClauseElement. This accessor tends to be used for 291 FromClause objects to identify 'equivalent' FROM clauses, regardless 292 of transformative operations. 293 294 """ 295 s = util.column_set() 296 f = self 297 298 # note this creates a cycle, asserted in test_memusage. however, 299 # turning this into a plain @property adds tends of thousands of method 300 # calls to Core / ORM performance tests, so the small overhead 301 # introduced by the relatively small amount of short term cycles 302 # produced here is preferable 303 while f is not None: 304 s.add(f) 305 f = f._is_clone_of 306 return s 307 308 @property 309 def entity_namespace(self): 310 raise AttributeError( 311 "This SQL expression has no entity namespace " 312 "with which to filter from." 313 ) 314 315 def __getstate__(self): 316 d = self.__dict__.copy() 317 d.pop("_is_clone_of", None) 318 d.pop("_generate_cache_key", None) 319 return d 320 321 def _execute_on_connection( 322 self, connection, multiparams, params, execution_options, _force=False 323 ): 324 if _force or self.supports_execution: 325 return connection._execute_clauseelement( 326 self, multiparams, params, execution_options 327 ) 328 else: 329 raise exc.ObjectNotExecutableError(self) 330 331 def unique_params(self, *optionaldict, **kwargs): 332 """Return a copy with :func:`_expression.bindparam` elements 333 replaced. 334 335 Same functionality as :meth:`_expression.ClauseElement.params`, 336 except adds `unique=True` 337 to affected bind parameters so that multiple statements can be 338 used. 339 340 """ 341 return self._replace_params(True, optionaldict, kwargs) 342 343 def params(self, *optionaldict, **kwargs): 344 """Return a copy with :func:`_expression.bindparam` elements 345 replaced. 346 347 Returns a copy of this ClauseElement with 348 :func:`_expression.bindparam` 349 elements replaced with values taken from the given dictionary:: 350 351 >>> clause = column('x') + bindparam('foo') 352 >>> print(clause.compile().params) 353 {'foo':None} 354 >>> print(clause.params({'foo':7}).compile().params) 355 {'foo':7} 356 357 """ 358 return self._replace_params(False, optionaldict, kwargs) 359 360 def _replace_params(self, unique, optionaldict, kwargs): 361 if len(optionaldict) == 1: 362 kwargs.update(optionaldict[0]) 363 elif len(optionaldict) > 1: 364 raise exc.ArgumentError( 365 "params() takes zero or one positional dictionary argument" 366 ) 367 368 def visit_bindparam(bind): 369 if bind.key in kwargs: 370 bind.value = kwargs[bind.key] 371 bind.required = False 372 if unique: 373 bind._convert_to_unique() 374 375 return cloned_traverse( 376 self, {"maintain_key": True}, {"bindparam": visit_bindparam} 377 ) 378 379 def compare(self, other, **kw): 380 r"""Compare this :class:`_expression.ClauseElement` to 381 the given :class:`_expression.ClauseElement`. 382 383 Subclasses should override the default behavior, which is a 384 straight identity comparison. 385 386 \**kw are arguments consumed by subclass ``compare()`` methods and 387 may be used to modify the criteria for comparison 388 (see :class:`_expression.ColumnElement`). 389 390 """ 391 return traversals.compare(self, other, **kw) 392 393 def self_group(self, against=None): 394 """Apply a 'grouping' to this :class:`_expression.ClauseElement`. 395 396 This method is overridden by subclasses to return a "grouping" 397 construct, i.e. parenthesis. In particular it's used by "binary" 398 expressions to provide a grouping around themselves when placed into a 399 larger expression, as well as by :func:`_expression.select` 400 constructs when placed into the FROM clause of another 401 :func:`_expression.select`. (Note that subqueries should be 402 normally created using the :meth:`_expression.Select.alias` method, 403 as many 404 platforms require nested SELECT statements to be named). 405 406 As expressions are composed together, the application of 407 :meth:`self_group` is automatic - end-user code should never 408 need to use this method directly. Note that SQLAlchemy's 409 clause constructs take operator precedence into account - 410 so parenthesis might not be needed, for example, in 411 an expression like ``x OR (y AND z)`` - AND takes precedence 412 over OR. 413 414 The base :meth:`self_group` method of 415 :class:`_expression.ClauseElement` 416 just returns self. 417 """ 418 return self 419 420 def _ungroup(self): 421 """Return this :class:`_expression.ClauseElement` 422 without any groupings. 423 """ 424 425 return self 426 427 @util.preload_module("sqlalchemy.engine.default") 428 @util.preload_module("sqlalchemy.engine.url") 429 def compile(self, bind=None, dialect=None, **kw): 430 """Compile this SQL expression. 431 432 The return value is a :class:`~.Compiled` object. 433 Calling ``str()`` or ``unicode()`` on the returned value will yield a 434 string representation of the result. The 435 :class:`~.Compiled` object also can return a 436 dictionary of bind parameter names and values 437 using the ``params`` accessor. 438 439 :param bind: An ``Engine`` or ``Connection`` from which a 440 ``Compiled`` will be acquired. This argument takes precedence over 441 this :class:`_expression.ClauseElement`'s bound engine, if any. 442 443 :param column_keys: Used for INSERT and UPDATE statements, a list of 444 column names which should be present in the VALUES clause of the 445 compiled statement. If ``None``, all columns from the target table 446 object are rendered. 447 448 :param dialect: A ``Dialect`` instance from which a ``Compiled`` 449 will be acquired. This argument takes precedence over the `bind` 450 argument as well as this :class:`_expression.ClauseElement` 451 's bound engine, 452 if any. 453 454 :param compile_kwargs: optional dictionary of additional parameters 455 that will be passed through to the compiler within all "visit" 456 methods. This allows any custom flag to be passed through to 457 a custom compilation construct, for example. It is also used 458 for the case of passing the ``literal_binds`` flag through:: 459 460 from sqlalchemy.sql import table, column, select 461 462 t = table('t', column('x')) 463 464 s = select(t).where(t.c.x == 5) 465 466 print(s.compile(compile_kwargs={"literal_binds": True})) 467 468 .. versionadded:: 0.9.0 469 470 .. seealso:: 471 472 :ref:`faq_sql_expression_string` 473 474 """ 475 476 if not dialect: 477 if bind: 478 dialect = bind.dialect 479 elif self.bind: 480 dialect = self.bind.dialect 481 else: 482 if self.stringify_dialect == "default": 483 default = util.preloaded.engine_default 484 dialect = default.StrCompileDialect() 485 else: 486 url = util.preloaded.engine_url 487 dialect = url.URL.create( 488 self.stringify_dialect 489 ).get_dialect()() 490 491 return self._compiler(dialect, **kw) 492 493 def _compile_w_cache( 494 self, 495 dialect, 496 compiled_cache=None, 497 column_keys=None, 498 for_executemany=False, 499 schema_translate_map=None, 500 **kw 501 ): 502 if compiled_cache is not None and dialect._supports_statement_cache: 503 elem_cache_key = self._generate_cache_key() 504 else: 505 elem_cache_key = None 506 507 if elem_cache_key: 508 cache_key, extracted_params = elem_cache_key 509 key = ( 510 dialect, 511 cache_key, 512 tuple(column_keys), 513 bool(schema_translate_map), 514 for_executemany, 515 ) 516 compiled_sql = compiled_cache.get(key) 517 518 if compiled_sql is None: 519 cache_hit = dialect.CACHE_MISS 520 compiled_sql = self._compiler( 521 dialect, 522 cache_key=elem_cache_key, 523 column_keys=column_keys, 524 for_executemany=for_executemany, 525 schema_translate_map=schema_translate_map, 526 **kw 527 ) 528 compiled_cache[key] = compiled_sql 529 else: 530 cache_hit = dialect.CACHE_HIT 531 else: 532 extracted_params = None 533 compiled_sql = self._compiler( 534 dialect, 535 cache_key=elem_cache_key, 536 column_keys=column_keys, 537 for_executemany=for_executemany, 538 schema_translate_map=schema_translate_map, 539 **kw 540 ) 541 542 if not dialect._supports_statement_cache: 543 cache_hit = dialect.NO_DIALECT_SUPPORT 544 elif compiled_cache is None: 545 cache_hit = dialect.CACHING_DISABLED 546 else: 547 cache_hit = dialect.NO_CACHE_KEY 548 549 return compiled_sql, extracted_params, cache_hit 550 551 def _compiler(self, dialect, **kw): 552 """Return a compiler appropriate for this ClauseElement, given a 553 Dialect.""" 554 555 return dialect.statement_compiler(dialect, self, **kw) 556 557 def __str__(self): 558 if util.py3k: 559 return str(self.compile()) 560 else: 561 return unicode(self.compile()).encode( # noqa 562 "ascii", "backslashreplace" 563 ) # noqa 564 565 def __invert__(self): 566 # undocumented element currently used by the ORM for 567 # relationship.contains() 568 if hasattr(self, "negation_clause"): 569 return self.negation_clause 570 else: 571 return self._negate() 572 573 def _negate(self): 574 return UnaryExpression( 575 self.self_group(against=operators.inv), operator=operators.inv 576 ) 577 578 def __bool__(self): 579 raise TypeError("Boolean value of this clause is not defined") 580 581 __nonzero__ = __bool__ 582 583 def __repr__(self): 584 friendly = self.description 585 if friendly is None: 586 return object.__repr__(self) 587 else: 588 return "<%s.%s at 0x%x; %s>" % ( 589 self.__module__, 590 self.__class__.__name__, 591 id(self), 592 friendly, 593 ) 594 595 596class ColumnElement( 597 roles.ColumnArgumentOrKeyRole, 598 roles.StatementOptionRole, 599 roles.WhereHavingRole, 600 roles.BinaryElementRole, 601 roles.OrderByRole, 602 roles.ColumnsClauseRole, 603 roles.LimitOffsetRole, 604 roles.DMLColumnRole, 605 roles.DDLConstraintColumnRole, 606 roles.DDLExpressionRole, 607 operators.ColumnOperators, 608 ClauseElement, 609): 610 """Represent a column-oriented SQL expression suitable for usage in the 611 "columns" clause, WHERE clause etc. of a statement. 612 613 While the most familiar kind of :class:`_expression.ColumnElement` is the 614 :class:`_schema.Column` object, :class:`_expression.ColumnElement` 615 serves as the basis 616 for any unit that may be present in a SQL expression, including 617 the expressions themselves, SQL functions, bound parameters, 618 literal expressions, keywords such as ``NULL``, etc. 619 :class:`_expression.ColumnElement` 620 is the ultimate base class for all such elements. 621 622 A wide variety of SQLAlchemy Core functions work at the SQL expression 623 level, and are intended to accept instances of 624 :class:`_expression.ColumnElement` as 625 arguments. These functions will typically document that they accept a 626 "SQL expression" as an argument. What this means in terms of SQLAlchemy 627 usually refers to an input which is either already in the form of a 628 :class:`_expression.ColumnElement` object, 629 or a value which can be **coerced** into 630 one. The coercion rules followed by most, but not all, SQLAlchemy Core 631 functions with regards to SQL expressions are as follows: 632 633 * a literal Python value, such as a string, integer or floating 634 point value, boolean, datetime, ``Decimal`` object, or virtually 635 any other Python object, will be coerced into a "literal bound 636 value". This generally means that a :func:`.bindparam` will be 637 produced featuring the given value embedded into the construct; the 638 resulting :class:`.BindParameter` object is an instance of 639 :class:`_expression.ColumnElement`. 640 The Python value will ultimately be sent 641 to the DBAPI at execution time as a parameterized argument to the 642 ``execute()`` or ``executemany()`` methods, after SQLAlchemy 643 type-specific converters (e.g. those provided by any associated 644 :class:`.TypeEngine` objects) are applied to the value. 645 646 * any special object value, typically ORM-level constructs, which 647 feature an accessor called ``__clause_element__()``. The Core 648 expression system looks for this method when an object of otherwise 649 unknown type is passed to a function that is looking to coerce the 650 argument into a :class:`_expression.ColumnElement` and sometimes a 651 :class:`_expression.SelectBase` expression. 652 It is used within the ORM to 653 convert from ORM-specific objects like mapped classes and 654 mapped attributes into Core expression objects. 655 656 * The Python ``None`` value is typically interpreted as ``NULL``, 657 which in SQLAlchemy Core produces an instance of :func:`.null`. 658 659 A :class:`_expression.ColumnElement` provides the ability to generate new 660 :class:`_expression.ColumnElement` 661 objects using Python expressions. This means that Python operators 662 such as ``==``, ``!=`` and ``<`` are overloaded to mimic SQL operations, 663 and allow the instantiation of further :class:`_expression.ColumnElement` 664 instances 665 which are composed from other, more fundamental 666 :class:`_expression.ColumnElement` 667 objects. For example, two :class:`.ColumnClause` objects can be added 668 together with the addition operator ``+`` to produce 669 a :class:`.BinaryExpression`. 670 Both :class:`.ColumnClause` and :class:`.BinaryExpression` are subclasses 671 of :class:`_expression.ColumnElement`:: 672 673 >>> from sqlalchemy.sql import column 674 >>> column('a') + column('b') 675 <sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0> 676 >>> print(column('a') + column('b')) 677 a + b 678 679 .. seealso:: 680 681 :class:`_schema.Column` 682 683 :func:`_expression.column` 684 685 """ 686 687 __visit_name__ = "column_element" 688 primary_key = False 689 foreign_keys = [] 690 _proxies = () 691 692 _tq_label = None 693 """The named label that can be used to target 694 this column in a result set in a "table qualified" context. 695 696 This label is almost always the label used when 697 rendering <expr> AS <label> in a SELECT statement when using 698 the LABEL_STYLE_TABLENAME_PLUS_COL label style, which is what the legacy 699 ORM ``Query`` object uses as well. 700 701 For a regular Column bound to a Table, this is typically the label 702 <tablename>_<columnname>. For other constructs, different rules 703 may apply, such as anonymized labels and others. 704 705 .. versionchanged:: 1.4.21 renamed from ``._label`` 706 707 """ 708 709 key = None 710 """The 'key' that in some circumstances refers to this object in a 711 Python namespace. 712 713 This typically refers to the "key" of the column as present in the 714 ``.c`` collection of a selectable, e.g. ``sometable.c["somekey"]`` would 715 return a :class:`_schema.Column` with a ``.key`` of "somekey". 716 717 """ 718 719 @HasMemoized.memoized_attribute 720 def _tq_key_label(self): 721 """A label-based version of 'key' that in some circumstances refers 722 to this object in a Python namespace. 723 724 725 _tq_key_label comes into play when a select() statement is constructed 726 with apply_labels(); in this case, all Column objects in the ``.c`` 727 collection are rendered as <tablename>_<columnname> in SQL; this is 728 essentially the value of ._label. But to locate those columns in the 729 ``.c`` collection, the name is along the lines of <tablename>_<key>; 730 that's the typical value of .key_label. 731 732 .. versionchanged:: 1.4.21 renamed from ``._key_label`` 733 734 """ 735 return self._proxy_key 736 737 @property 738 def _key_label(self): 739 """legacy; renamed to _tq_key_label""" 740 return self._tq_key_label 741 742 @property 743 def _label(self): 744 """legacy; renamed to _tq_label""" 745 return self._tq_label 746 747 @property 748 def _non_anon_label(self): 749 """the 'name' that naturally applies this element when rendered in 750 SQL. 751 752 Concretely, this is the "name" of a column or a label in a 753 SELECT statement; ``<columnname>`` and ``<labelname>`` below:: 754 755 SELECT <columnmame> FROM table 756 757 SELECT column AS <labelname> FROM table 758 759 Above, the two names noted will be what's present in the DBAPI 760 ``cursor.description`` as the names. 761 762 If this attribute returns ``None``, it means that the SQL element as 763 written does not have a 100% fully predictable "name" that would appear 764 in the ``cursor.description``. Examples include SQL functions, CAST 765 functions, etc. While such things do return names in 766 ``cursor.description``, they are only predictable on a 767 database-specific basis; e.g. an expression like ``MAX(table.col)`` may 768 appear as the string ``max`` on one database (like PostgreSQL) or may 769 appear as the whole expression ``max(table.col)`` on SQLite. 770 771 The default implementation looks for a ``.name`` attribute on the 772 object, as has been the precedent established in SQLAlchemy for many 773 years. An exception is made on the ``FunctionElement`` subclass 774 so that the return value is always ``None``. 775 776 .. versionadded:: 1.4.21 777 778 779 780 """ 781 return getattr(self, "name", None) 782 783 _render_label_in_columns_clause = True 784 """A flag used by select._columns_plus_names that helps to determine 785 we are actually going to render in terms of "SELECT <col> AS <label>". 786 This flag can be returned as False for some Column objects that want 787 to be rendered as simple "SELECT <col>"; typically columns that don't have 788 any parent table and are named the same as what the label would be 789 in any case. 790 791 """ 792 793 _allow_label_resolve = True 794 """A flag that can be flipped to prevent a column from being resolvable 795 by string label name. 796 797 The joined eager loader strategy in the ORM uses this, for example. 798 799 """ 800 801 _is_implicitly_boolean = False 802 803 _alt_names = () 804 805 def self_group(self, against=None): 806 if ( 807 against in (operators.and_, operators.or_, operators._asbool) 808 and self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity 809 ): 810 return AsBoolean(self, operators.is_true, operators.is_false) 811 elif against in (operators.any_op, operators.all_op): 812 return Grouping(self) 813 else: 814 return self 815 816 def _negate(self): 817 if self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity: 818 return AsBoolean(self, operators.is_false, operators.is_true) 819 else: 820 return super(ColumnElement, self)._negate() 821 822 @util.memoized_property 823 def type(self): 824 return type_api.NULLTYPE 825 826 @HasMemoized.memoized_attribute 827 def comparator(self): 828 try: 829 comparator_factory = self.type.comparator_factory 830 except AttributeError as err: 831 util.raise_( 832 TypeError( 833 "Object %r associated with '.type' attribute " 834 "is not a TypeEngine class or object" % self.type 835 ), 836 replace_context=err, 837 ) 838 else: 839 return comparator_factory(self) 840 841 def __getattr__(self, key): 842 try: 843 return getattr(self.comparator, key) 844 except AttributeError as err: 845 util.raise_( 846 AttributeError( 847 "Neither %r object nor %r object has an attribute %r" 848 % ( 849 type(self).__name__, 850 type(self.comparator).__name__, 851 key, 852 ) 853 ), 854 replace_context=err, 855 ) 856 857 def operate(self, op, *other, **kwargs): 858 return op(self.comparator, *other, **kwargs) 859 860 def reverse_operate(self, op, other, **kwargs): 861 return op(other, self.comparator, **kwargs) 862 863 def _bind_param(self, operator, obj, type_=None, expanding=False): 864 return BindParameter( 865 None, 866 obj, 867 _compared_to_operator=operator, 868 type_=type_, 869 _compared_to_type=self.type, 870 unique=True, 871 expanding=expanding, 872 ) 873 874 @property 875 def expression(self): 876 """Return a column expression. 877 878 Part of the inspection interface; returns self. 879 880 """ 881 return self 882 883 @property 884 def _select_iterable(self): 885 return (self,) 886 887 @util.memoized_property 888 def base_columns(self): 889 return util.column_set(c for c in self.proxy_set if not c._proxies) 890 891 @util.memoized_property 892 def proxy_set(self): 893 s = util.column_set([self]) 894 for c in self._proxies: 895 s.update(c.proxy_set) 896 return s 897 898 def _uncached_proxy_set(self): 899 """An 'uncached' version of proxy set. 900 901 This is so that we can read annotations from the list of columns 902 without breaking the caching of the above proxy_set. 903 904 """ 905 s = util.column_set([self]) 906 for c in self._proxies: 907 s.update(c._uncached_proxy_set()) 908 return s 909 910 def shares_lineage(self, othercolumn): 911 """Return True if the given :class:`_expression.ColumnElement` 912 has a common ancestor to this :class:`_expression.ColumnElement`.""" 913 914 return bool(self.proxy_set.intersection(othercolumn.proxy_set)) 915 916 def _compare_name_for_result(self, other): 917 """Return True if the given column element compares to this one 918 when targeting within a result row.""" 919 920 return ( 921 hasattr(other, "name") 922 and hasattr(self, "name") 923 and other.name == self.name 924 ) 925 926 @HasMemoized.memoized_attribute 927 def _proxy_key(self): 928 if self._annotations and "proxy_key" in self._annotations: 929 return self._annotations["proxy_key"] 930 931 name = self.key 932 if not name: 933 # there's a bit of a seeming contradiction which is that the 934 # "_non_anon_label" of a column can in fact be an 935 # "_anonymous_label"; this is when it's on a column that is 936 # proxying for an anonymous expression in a subquery. 937 name = self._non_anon_label 938 939 if isinstance(name, _anonymous_label): 940 return None 941 else: 942 return name 943 944 @HasMemoized.memoized_attribute 945 def _expression_label(self): 946 """a suggested label to use in the case that the column has no name, 947 which should be used if possible as the explicit 'AS <label>' 948 where this expression would normally have an anon label. 949 950 this is essentially mostly what _proxy_key does except it returns 951 None if the column has a normal name that can be used. 952 953 """ 954 955 if getattr(self, "name", None) is not None: 956 return None 957 elif self._annotations and "proxy_key" in self._annotations: 958 return self._annotations["proxy_key"] 959 else: 960 return None 961 962 def _make_proxy( 963 self, selectable, name=None, key=None, name_is_truncatable=False, **kw 964 ): 965 """Create a new :class:`_expression.ColumnElement` representing this 966 :class:`_expression.ColumnElement` as it appears in the select list of 967 a descending selectable. 968 969 """ 970 if name is None: 971 name = self._anon_name_label 972 if key is None: 973 key = self._proxy_key 974 else: 975 key = name 976 977 co = ColumnClause( 978 coercions.expect(roles.TruncatedLabelRole, name) 979 if name_is_truncatable 980 else name, 981 type_=getattr(self, "type", None), 982 _selectable=selectable, 983 ) 984 985 co._propagate_attrs = selectable._propagate_attrs 986 co._proxies = [self] 987 if selectable._is_clone_of is not None: 988 co._is_clone_of = selectable._is_clone_of.columns.get(key) 989 return key, co 990 991 def cast(self, type_): 992 """Produce a type cast, i.e. ``CAST(<expression> AS <type>)``. 993 994 This is a shortcut to the :func:`_expression.cast` function. 995 996 .. seealso:: 997 998 :ref:`coretutorial_casts` 999 1000 :func:`_expression.cast` 1001 1002 :func:`_expression.type_coerce` 1003 1004 .. versionadded:: 1.0.7 1005 1006 """ 1007 return Cast(self, type_) 1008 1009 def label(self, name): 1010 """Produce a column label, i.e. ``<columnname> AS <name>``. 1011 1012 This is a shortcut to the :func:`_expression.label` function. 1013 1014 If 'name' is ``None``, an anonymous label name will be generated. 1015 1016 """ 1017 return Label(name, self, self.type) 1018 1019 def _anon_label(self, seed, add_hash=None): 1020 while self._is_clone_of is not None: 1021 self = self._is_clone_of 1022 1023 # as of 1.4 anonymous label for ColumnElement uses hash(), not id(), 1024 # as the identifier, because a column and its annotated version are 1025 # the same thing in a SQL statement 1026 hash_value = hash(self) 1027 1028 if add_hash: 1029 # this path is used for disambiguating anon labels that would 1030 # otherwise be the same name for the same element repeated. 1031 # an additional numeric value is factored in for each label. 1032 1033 # shift hash(self) (which is id(self), typically 8 byte integer) 1034 # 16 bits leftward. fill extra add_hash on right 1035 assert add_hash < (2 << 15) 1036 assert seed 1037 hash_value = (hash_value << 16) | add_hash 1038 1039 # extra underscore is added for labels with extra hash 1040 # values, to isolate the "deduped anon" namespace from the 1041 # regular namespace. eliminates chance of these 1042 # manufactured hash values overlapping with regular ones for some 1043 # undefined python interpreter 1044 seed = seed + "_" 1045 1046 if isinstance(seed, _anonymous_label): 1047 return _anonymous_label.safe_construct( 1048 hash_value, "", enclosing_label=seed 1049 ) 1050 1051 return _anonymous_label.safe_construct(hash_value, seed or "anon") 1052 1053 @util.memoized_property 1054 def _anon_name_label(self): 1055 """Provides a constant 'anonymous label' for this ColumnElement. 1056 1057 This is a label() expression which will be named at compile time. 1058 The same label() is returned each time ``anon_label`` is called so 1059 that expressions can reference ``anon_label`` multiple times, 1060 producing the same label name at compile time. 1061 1062 The compiler uses this function automatically at compile time 1063 for expressions that are known to be 'unnamed' like binary 1064 expressions and function calls. 1065 1066 .. versionchanged:: 1.4.9 - this attribute was not intended to be 1067 public and is renamed to _anon_name_label. anon_name exists 1068 for backwards compat 1069 1070 """ 1071 name = getattr(self, "name", None) 1072 return self._anon_label(name) 1073 1074 @util.memoized_property 1075 def _anon_key_label(self): 1076 """Provides a constant 'anonymous key label' for this ColumnElement. 1077 1078 Compare to ``anon_label``, except that the "key" of the column, 1079 if available, is used to generate the label. 1080 1081 This is used when a deduplicating key is placed into the columns 1082 collection of a selectable. 1083 1084 .. versionchanged:: 1.4.9 - this attribute was not intended to be 1085 public and is renamed to _anon_key_label. anon_key_label exists 1086 for backwards compat 1087 1088 """ 1089 return self._anon_label(self._proxy_key) 1090 1091 @property 1092 @util.deprecated( 1093 "1.4", 1094 "The :attr:`_expression.ColumnElement.anon_label` attribute is now " 1095 "private, and the public accessor is deprecated.", 1096 ) 1097 def anon_label(self): 1098 return self._anon_name_label 1099 1100 @property 1101 @util.deprecated( 1102 "1.4", 1103 "The :attr:`_expression.ColumnElement.anon_key_label` attribute is " 1104 "now private, and the public accessor is deprecated.", 1105 ) 1106 def anon_key_label(self): 1107 return self._anon_key_label 1108 1109 def _dedupe_anon_label_idx(self, idx): 1110 """label to apply to a column that is anon labeled, but repeated 1111 in the SELECT, so that we have to make an "extra anon" label that 1112 disambiguates it from the previous appearance. 1113 1114 these labels come out like "foo_bar_id__1" and have double underscores 1115 in them. 1116 1117 """ 1118 label = getattr(self, "name", None) 1119 1120 # current convention is that if the element doesn't have a 1121 # ".name" (usually because it is not NamedColumn), we try to 1122 # use a "table qualified" form for the "dedupe anon" label, 1123 # based on the notion that a label like 1124 # "CAST(casttest.v1 AS DECIMAL) AS casttest_v1__1" looks better than 1125 # "CAST(casttest.v1 AS DECIMAL) AS anon__1" 1126 1127 if label is None: 1128 return self._dedupe_anon_tq_label_idx(idx) 1129 else: 1130 return self._anon_label(label, add_hash=idx) 1131 1132 @util.memoized_property 1133 def _anon_tq_label(self): 1134 return self._anon_label(getattr(self, "_tq_label", None)) 1135 1136 @util.memoized_property 1137 def _anon_tq_key_label(self): 1138 return self._anon_label(getattr(self, "_tq_key_label", None)) 1139 1140 def _dedupe_anon_tq_label_idx(self, idx): 1141 label = getattr(self, "_tq_label", None) or "anon" 1142 1143 return self._anon_label(label, add_hash=idx) 1144 1145 1146class WrapsColumnExpression(object): 1147 """Mixin that defines a :class:`_expression.ColumnElement` 1148 as a wrapper with special 1149 labeling behavior for an expression that already has a name. 1150 1151 .. versionadded:: 1.4 1152 1153 .. seealso:: 1154 1155 :ref:`change_4449` 1156 1157 1158 """ 1159 1160 @property 1161 def wrapped_column_expression(self): 1162 raise NotImplementedError() 1163 1164 @property 1165 def _tq_label(self): 1166 wce = self.wrapped_column_expression 1167 if hasattr(wce, "_tq_label"): 1168 return wce._tq_label 1169 else: 1170 return None 1171 1172 _label = _tq_label 1173 1174 @property 1175 def _non_anon_label(self): 1176 return None 1177 1178 @property 1179 def _anon_name_label(self): 1180 wce = self.wrapped_column_expression 1181 1182 # this logic tries to get the WrappedColumnExpression to render 1183 # with "<expr> AS <name>", where "<name>" is the natural name 1184 # within the expression itself. e.g. "CAST(table.foo) AS foo". 1185 if not wce._is_text_clause: 1186 nal = wce._non_anon_label 1187 if nal: 1188 return nal 1189 elif hasattr(wce, "_anon_name_label"): 1190 return wce._anon_name_label 1191 return super(WrapsColumnExpression, self)._anon_name_label 1192 1193 def _dedupe_anon_label_idx(self, idx): 1194 wce = self.wrapped_column_expression 1195 nal = wce._non_anon_label 1196 if nal: 1197 return self._anon_label(nal + "_") 1198 else: 1199 return self._dedupe_anon_tq_label_idx(idx) 1200 1201 1202class BindParameter(roles.InElementRole, ColumnElement): 1203 r"""Represent a "bound expression". 1204 1205 :class:`.BindParameter` is invoked explicitly using the 1206 :func:`.bindparam` function, as in:: 1207 1208 from sqlalchemy import bindparam 1209 1210 stmt = select(users_table).\ 1211 where(users_table.c.name == bindparam('username')) 1212 1213 Detailed discussion of how :class:`.BindParameter` is used is 1214 at :func:`.bindparam`. 1215 1216 .. seealso:: 1217 1218 :func:`.bindparam` 1219 1220 """ 1221 1222 __visit_name__ = "bindparam" 1223 1224 _traverse_internals = [ 1225 ("key", InternalTraversal.dp_anon_name), 1226 ("type", InternalTraversal.dp_type), 1227 ("callable", InternalTraversal.dp_plain_dict), 1228 ("value", InternalTraversal.dp_plain_obj), 1229 ] 1230 1231 _is_crud = False 1232 _is_bind_parameter = True 1233 _key_is_anon = False 1234 1235 # bindparam implements its own _gen_cache_key() method however 1236 # we check subclasses for this flag, else no cache key is generated 1237 inherit_cache = True 1238 1239 def __init__( 1240 self, 1241 key, 1242 value=NO_ARG, 1243 type_=None, 1244 unique=False, 1245 required=NO_ARG, 1246 quote=None, 1247 callable_=None, 1248 expanding=False, 1249 isoutparam=False, 1250 literal_execute=False, 1251 _compared_to_operator=None, 1252 _compared_to_type=None, 1253 _is_crud=False, 1254 ): 1255 r"""Produce a "bound expression". 1256 1257 The return value is an instance of :class:`.BindParameter`; this 1258 is a :class:`_expression.ColumnElement` 1259 subclass which represents a so-called 1260 "placeholder" value in a SQL expression, the value of which is 1261 supplied at the point at which the statement in executed against a 1262 database connection. 1263 1264 In SQLAlchemy, the :func:`.bindparam` construct has 1265 the ability to carry along the actual value that will be ultimately 1266 used at expression time. In this way, it serves not just as 1267 a "placeholder" for eventual population, but also as a means of 1268 representing so-called "unsafe" values which should not be rendered 1269 directly in a SQL statement, but rather should be passed along 1270 to the :term:`DBAPI` as values which need to be correctly escaped 1271 and potentially handled for type-safety. 1272 1273 When using :func:`.bindparam` explicitly, the use case is typically 1274 one of traditional deferment of parameters; the :func:`.bindparam` 1275 construct accepts a name which can then be referred to at execution 1276 time:: 1277 1278 from sqlalchemy import bindparam 1279 1280 stmt = select(users_table).\ 1281 where(users_table.c.name == bindparam('username')) 1282 1283 The above statement, when rendered, will produce SQL similar to:: 1284 1285 SELECT id, name FROM user WHERE name = :username 1286 1287 In order to populate the value of ``:username`` above, the value 1288 would typically be applied at execution time to a method 1289 like :meth:`_engine.Connection.execute`:: 1290 1291 result = connection.execute(stmt, username='wendy') 1292 1293 Explicit use of :func:`.bindparam` is also common when producing 1294 UPDATE or DELETE statements that are to be invoked multiple times, 1295 where the WHERE criterion of the statement is to change on each 1296 invocation, such as:: 1297 1298 stmt = (users_table.update(). 1299 where(user_table.c.name == bindparam('username')). 1300 values(fullname=bindparam('fullname')) 1301 ) 1302 1303 connection.execute( 1304 stmt, [{"username": "wendy", "fullname": "Wendy Smith"}, 1305 {"username": "jack", "fullname": "Jack Jones"}, 1306 ] 1307 ) 1308 1309 SQLAlchemy's Core expression system makes wide use of 1310 :func:`.bindparam` in an implicit sense. It is typical that Python 1311 literal values passed to virtually all SQL expression functions are 1312 coerced into fixed :func:`.bindparam` constructs. For example, given 1313 a comparison operation such as:: 1314 1315 expr = users_table.c.name == 'Wendy' 1316 1317 The above expression will produce a :class:`.BinaryExpression` 1318 construct, where the left side is the :class:`_schema.Column` object 1319 representing the ``name`` column, and the right side is a 1320 :class:`.BindParameter` representing the literal value:: 1321 1322 print(repr(expr.right)) 1323 BindParameter('%(4327771088 name)s', 'Wendy', type_=String()) 1324 1325 The expression above will render SQL such as:: 1326 1327 user.name = :name_1 1328 1329 Where the ``:name_1`` parameter name is an anonymous name. The 1330 actual string ``Wendy`` is not in the rendered string, but is carried 1331 along where it is later used within statement execution. If we 1332 invoke a statement like the following:: 1333 1334 stmt = select(users_table).where(users_table.c.name == 'Wendy') 1335 result = connection.execute(stmt) 1336 1337 We would see SQL logging output as:: 1338 1339 SELECT "user".id, "user".name 1340 FROM "user" 1341 WHERE "user".name = %(name_1)s 1342 {'name_1': 'Wendy'} 1343 1344 Above, we see that ``Wendy`` is passed as a parameter to the database, 1345 while the placeholder ``:name_1`` is rendered in the appropriate form 1346 for the target database, in this case the PostgreSQL database. 1347 1348 Similarly, :func:`.bindparam` is invoked automatically when working 1349 with :term:`CRUD` statements as far as the "VALUES" portion is 1350 concerned. The :func:`_expression.insert` construct produces an 1351 ``INSERT`` expression which will, at statement execution time, generate 1352 bound placeholders based on the arguments passed, as in:: 1353 1354 stmt = users_table.insert() 1355 result = connection.execute(stmt, name='Wendy') 1356 1357 The above will produce SQL output as:: 1358 1359 INSERT INTO "user" (name) VALUES (%(name)s) 1360 {'name': 'Wendy'} 1361 1362 The :class:`_expression.Insert` construct, at 1363 compilation/execution time, rendered a single :func:`.bindparam` 1364 mirroring the column name ``name`` as a result of the single ``name`` 1365 parameter we passed to the :meth:`_engine.Connection.execute` method. 1366 1367 :param key: 1368 the key (e.g. the name) for this bind param. 1369 Will be used in the generated 1370 SQL statement for dialects that use named parameters. This 1371 value may be modified when part of a compilation operation, 1372 if other :class:`BindParameter` objects exist with the same 1373 key, or if its length is too long and truncation is 1374 required. 1375 1376 :param value: 1377 Initial value for this bind param. Will be used at statement 1378 execution time as the value for this parameter passed to the 1379 DBAPI, if no other value is indicated to the statement execution 1380 method for this particular parameter name. Defaults to ``None``. 1381 1382 :param callable\_: 1383 A callable function that takes the place of "value". The function 1384 will be called at statement execution time to determine the 1385 ultimate value. Used for scenarios where the actual bind 1386 value cannot be determined at the point at which the clause 1387 construct is created, but embedded bind values are still desirable. 1388 1389 :param type\_: 1390 A :class:`.TypeEngine` class or instance representing an optional 1391 datatype for this :func:`.bindparam`. If not passed, a type 1392 may be determined automatically for the bind, based on the given 1393 value; for example, trivial Python types such as ``str``, 1394 ``int``, ``bool`` 1395 may result in the :class:`.String`, :class:`.Integer` or 1396 :class:`.Boolean` types being automatically selected. 1397 1398 The type of a :func:`.bindparam` is significant especially in that 1399 the type will apply pre-processing to the value before it is 1400 passed to the database. For example, a :func:`.bindparam` which 1401 refers to a datetime value, and is specified as holding the 1402 :class:`.DateTime` type, may apply conversion needed to the 1403 value (such as stringification on SQLite) before passing the value 1404 to the database. 1405 1406 :param unique: 1407 if True, the key name of this :class:`.BindParameter` will be 1408 modified if another :class:`.BindParameter` of the same name 1409 already has been located within the containing 1410 expression. This flag is used generally by the internals 1411 when producing so-called "anonymous" bound expressions, it 1412 isn't generally applicable to explicitly-named :func:`.bindparam` 1413 constructs. 1414 1415 :param required: 1416 If ``True``, a value is required at execution time. If not passed, 1417 it defaults to ``True`` if neither :paramref:`.bindparam.value` 1418 or :paramref:`.bindparam.callable` were passed. If either of these 1419 parameters are present, then :paramref:`.bindparam.required` 1420 defaults to ``False``. 1421 1422 :param quote: 1423 True if this parameter name requires quoting and is not 1424 currently known as a SQLAlchemy reserved word; this currently 1425 only applies to the Oracle backend, where bound names must 1426 sometimes be quoted. 1427 1428 :param isoutparam: 1429 if True, the parameter should be treated like a stored procedure 1430 "OUT" parameter. This applies to backends such as Oracle which 1431 support OUT parameters. 1432 1433 :param expanding: 1434 if True, this parameter will be treated as an "expanding" parameter 1435 at execution time; the parameter value is expected to be a sequence, 1436 rather than a scalar value, and the string SQL statement will 1437 be transformed on a per-execution basis to accommodate the sequence 1438 with a variable number of parameter slots passed to the DBAPI. 1439 This is to allow statement caching to be used in conjunction with 1440 an IN clause. 1441 1442 .. seealso:: 1443 1444 :meth:`.ColumnOperators.in_` 1445 1446 :ref:`baked_in` - with baked queries 1447 1448 .. note:: The "expanding" feature does not support "executemany"- 1449 style parameter sets. 1450 1451 .. versionadded:: 1.2 1452 1453 .. versionchanged:: 1.3 the "expanding" bound parameter feature now 1454 supports empty lists. 1455 1456 1457 .. seealso:: 1458 1459 :ref:`coretutorial_bind_param` 1460 1461 :ref:`coretutorial_insert_expressions` 1462 1463 :func:`.outparam` 1464 1465 :param literal_execute: 1466 if True, the bound parameter will be rendered in the compile phase 1467 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will 1468 render the final value of the parameter into the SQL statement at 1469 statement execution time, omitting the value from the parameter 1470 dictionary / list passed to DBAPI ``cursor.execute()``. This 1471 produces a similar effect as that of using the ``literal_binds``, 1472 compilation flag, however takes place as the statement is sent to 1473 the DBAPI ``cursor.execute()`` method, rather than when the statement 1474 is compiled. The primary use of this 1475 capability is for rendering LIMIT / OFFSET clauses for database 1476 drivers that can't accommodate for bound parameters in these 1477 contexts, while allowing SQL constructs to be cacheable at the 1478 compilation level. 1479 1480 .. versionadded:: 1.4 Added "post compile" bound parameters 1481 1482 .. seealso:: 1483 1484 :ref:`change_4808`. 1485 1486 """ 1487 if required is NO_ARG: 1488 required = value is NO_ARG and callable_ is None 1489 if value is NO_ARG: 1490 value = None 1491 1492 if quote is not None: 1493 key = quoted_name(key, quote) 1494 1495 if unique: 1496 self.key = _anonymous_label.safe_construct( 1497 id(self), 1498 key 1499 if key is not None and not isinstance(key, _anonymous_label) 1500 else "param", 1501 sanitize_key=True, 1502 ) 1503 self._key_is_anon = True 1504 elif key: 1505 self.key = key 1506 else: 1507 self.key = _anonymous_label.safe_construct(id(self), "param") 1508 self._key_is_anon = True 1509 1510 # identifying key that won't change across 1511 # clones, used to identify the bind's logical 1512 # identity 1513 self._identifying_key = self.key 1514 1515 # key that was passed in the first place, used to 1516 # generate new keys 1517 self._orig_key = key or "param" 1518 1519 self.unique = unique 1520 self.value = value 1521 self.callable = callable_ 1522 self.isoutparam = isoutparam 1523 self.required = required 1524 1525 # indicate an "expanding" parameter; the compiler sets this 1526 # automatically in the compiler _render_in_expr_w_bindparam method 1527 # for an IN expression 1528 self.expanding = expanding 1529 1530 # this is another hint to help w/ expanding and is typically 1531 # set in the compiler _render_in_expr_w_bindparam method for an 1532 # IN expression 1533 self.expand_op = None 1534 1535 self.literal_execute = literal_execute 1536 if _is_crud: 1537 self._is_crud = True 1538 1539 if type_ is None: 1540 if expanding and value: 1541 check_value = value[0] 1542 else: 1543 check_value = value 1544 if _compared_to_type is not None: 1545 self.type = _compared_to_type.coerce_compared_value( 1546 _compared_to_operator, check_value 1547 ) 1548 else: 1549 self.type = type_api._resolve_value_to_type(check_value) 1550 elif isinstance(type_, type): 1551 self.type = type_() 1552 elif type_._is_tuple_type and value: 1553 if expanding: 1554 check_value = value[0] 1555 else: 1556 check_value = value 1557 self.type = type_._resolve_values_to_types(check_value) 1558 else: 1559 self.type = type_ 1560 1561 def _with_value(self, value, maintain_key=False, required=NO_ARG): 1562 """Return a copy of this :class:`.BindParameter` with the given value 1563 set. 1564 """ 1565 cloned = self._clone(maintain_key=maintain_key) 1566 cloned.value = value 1567 cloned.callable = None 1568 cloned.required = required if required is not NO_ARG else self.required 1569 if cloned.type is type_api.NULLTYPE: 1570 cloned.type = type_api._resolve_value_to_type(value) 1571 return cloned 1572 1573 @property 1574 def effective_value(self): 1575 """Return the value of this bound parameter, 1576 taking into account if the ``callable`` parameter 1577 was set. 1578 1579 The ``callable`` value will be evaluated 1580 and returned if present, else ``value``. 1581 1582 """ 1583 if self.callable: 1584 return self.callable() 1585 else: 1586 return self.value 1587 1588 def render_literal_execute(self): 1589 """Produce a copy of this bound parameter that will enable the 1590 :paramref:`_sql.BindParameter.literal_execute` flag. 1591 1592 The :paramref:`_sql.BindParameter.literal_execute` flag will 1593 have the effect of the parameter rendered in the compiled SQL 1594 string using ``[POSTCOMPILE]`` form, which is a special form that 1595 is converted to be a rendering of the literal value of the parameter 1596 at SQL execution time. The rationale is to support caching 1597 of SQL statement strings that can embed per-statement literal values, 1598 such as LIMIT and OFFSET parameters, in the final SQL string that 1599 is passed to the DBAPI. Dialects in particular may want to use 1600 this method within custom compilation schemes. 1601 1602 .. versionadded:: 1.4.5 1603 1604 .. seealso:: 1605 1606 :ref:`engine_thirdparty_caching` 1607 1608 """ 1609 return self.__class__( 1610 self.key, 1611 self.value, 1612 type_=self.type, 1613 literal_execute=True, 1614 ) 1615 1616 def _negate_in_binary(self, negated_op, original_op): 1617 if self.expand_op is original_op: 1618 bind = self._clone() 1619 bind.expand_op = negated_op 1620 return bind 1621 else: 1622 return self 1623 1624 def _with_binary_element_type(self, type_): 1625 c = ClauseElement._clone(self) 1626 c.type = type_ 1627 return c 1628 1629 def _clone(self, maintain_key=False, **kw): 1630 c = ClauseElement._clone(self, **kw) 1631 if not maintain_key and self.unique: 1632 c.key = _anonymous_label.safe_construct( 1633 id(c), c._orig_key or "param", sanitize_key=True 1634 ) 1635 return c 1636 1637 def _gen_cache_key(self, anon_map, bindparams): 1638 _gen_cache_ok = self.__class__.__dict__.get("inherit_cache", False) 1639 1640 if not _gen_cache_ok: 1641 if anon_map is not None: 1642 anon_map[NO_CACHE] = True 1643 return None 1644 1645 idself = id(self) 1646 if idself in anon_map: 1647 return (anon_map[idself], self.__class__) 1648 else: 1649 # inline of 1650 # id_ = anon_map[idself] 1651 anon_map[idself] = id_ = str(anon_map.index) 1652 anon_map.index += 1 1653 1654 if bindparams is not None: 1655 bindparams.append(self) 1656 1657 return ( 1658 id_, 1659 self.__class__, 1660 self.type._static_cache_key, 1661 self.key % anon_map if self._key_is_anon else self.key, 1662 ) 1663 1664 def _convert_to_unique(self): 1665 if not self.unique: 1666 self.unique = True 1667 self.key = _anonymous_label.safe_construct( 1668 id(self), self._orig_key or "param", sanitize_key=True 1669 ) 1670 1671 def __getstate__(self): 1672 """execute a deferred value for serialization purposes.""" 1673 1674 d = self.__dict__.copy() 1675 v = self.value 1676 if self.callable: 1677 v = self.callable() 1678 d["callable"] = None 1679 d["value"] = v 1680 return d 1681 1682 def __setstate__(self, state): 1683 if state.get("unique", False): 1684 state["key"] = _anonymous_label.safe_construct( 1685 id(self), state.get("_orig_key", "param"), sanitize_key=True 1686 ) 1687 self.__dict__.update(state) 1688 1689 def __repr__(self): 1690 return "%s(%r, %r, type_=%r)" % ( 1691 self.__class__.__name__, 1692 self.key, 1693 self.value, 1694 self.type, 1695 ) 1696 1697 1698class TypeClause(ClauseElement): 1699 """Handle a type keyword in a SQL statement. 1700 1701 Used by the ``Case`` statement. 1702 1703 """ 1704 1705 __visit_name__ = "typeclause" 1706 1707 _traverse_internals = [("type", InternalTraversal.dp_type)] 1708 1709 def __init__(self, type_): 1710 self.type = type_ 1711 1712 1713class TextClause( 1714 roles.DDLConstraintColumnRole, 1715 roles.DDLExpressionRole, 1716 roles.StatementOptionRole, 1717 roles.WhereHavingRole, 1718 roles.OrderByRole, 1719 roles.FromClauseRole, 1720 roles.SelectStatementRole, 1721 roles.BinaryElementRole, 1722 roles.InElementRole, 1723 Executable, 1724 ClauseElement, 1725): 1726 """Represent a literal SQL text fragment. 1727 1728 E.g.:: 1729 1730 from sqlalchemy import text 1731 1732 t = text("SELECT * FROM users") 1733 result = connection.execute(t) 1734 1735 1736 The :class:`_expression.TextClause` construct is produced using the 1737 :func:`_expression.text` 1738 function; see that function for full documentation. 1739 1740 .. seealso:: 1741 1742 :func:`_expression.text` 1743 1744 """ 1745 1746 __visit_name__ = "textclause" 1747 1748 _traverse_internals = [ 1749 ("_bindparams", InternalTraversal.dp_string_clauseelement_dict), 1750 ("text", InternalTraversal.dp_string), 1751 ] 1752 1753 _is_text_clause = True 1754 1755 _is_textual = True 1756 1757 _bind_params_regex = re.compile(r"(?<![:\w\x5c]):(\w+)(?!:)", re.UNICODE) 1758 _execution_options = Executable._execution_options.union( 1759 {"autocommit": PARSE_AUTOCOMMIT} 1760 ) 1761 _is_implicitly_boolean = False 1762 1763 _render_label_in_columns_clause = False 1764 1765 _hide_froms = () 1766 1767 def __and__(self, other): 1768 # support use in select.where(), query.filter() 1769 return and_(self, other) 1770 1771 @property 1772 def _select_iterable(self): 1773 return (self,) 1774 1775 # help in those cases where text() is 1776 # interpreted in a column expression situation 1777 key = _label = None 1778 1779 _allow_label_resolve = False 1780 1781 def __init__(self, text, bind=None): 1782 self._bind = bind 1783 self._bindparams = {} 1784 1785 def repl(m): 1786 self._bindparams[m.group(1)] = BindParameter(m.group(1)) 1787 return ":%s" % m.group(1) 1788 1789 # scan the string and search for bind parameter names, add them 1790 # to the list of bindparams 1791 self.text = self._bind_params_regex.sub(repl, text) 1792 1793 @classmethod 1794 @_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`") 1795 @util.deprecated_params( 1796 bind=( 1797 "2.0", 1798 "The :paramref:`_sql.text.bind` argument is deprecated and " 1799 "will be removed in SQLAlchemy 2.0.", 1800 ), 1801 ) 1802 def _create_text(cls, text, bind=None): 1803 r"""Construct a new :class:`_expression.TextClause` clause, 1804 representing 1805 a textual SQL string directly. 1806 1807 E.g.:: 1808 1809 from sqlalchemy import text 1810 1811 t = text("SELECT * FROM users") 1812 result = connection.execute(t) 1813 1814 The advantages :func:`_expression.text` 1815 provides over a plain string are 1816 backend-neutral support for bind parameters, per-statement 1817 execution options, as well as 1818 bind parameter and result-column typing behavior, allowing 1819 SQLAlchemy type constructs to play a role when executing 1820 a statement that is specified literally. The construct can also 1821 be provided with a ``.c`` collection of column elements, allowing 1822 it to be embedded in other SQL expression constructs as a subquery. 1823 1824 Bind parameters are specified by name, using the format ``:name``. 1825 E.g.:: 1826 1827 t = text("SELECT * FROM users WHERE id=:user_id") 1828 result = connection.execute(t, user_id=12) 1829 1830 For SQL statements where a colon is required verbatim, as within 1831 an inline string, use a backslash to escape:: 1832 1833 t = text("SELECT * FROM users WHERE name='\:username'") 1834 1835 The :class:`_expression.TextClause` 1836 construct includes methods which can 1837 provide information about the bound parameters as well as the column 1838 values which would be returned from the textual statement, assuming 1839 it's an executable SELECT type of statement. The 1840 :meth:`_expression.TextClause.bindparams` 1841 method is used to provide bound 1842 parameter detail, and :meth:`_expression.TextClause.columns` 1843 method allows 1844 specification of return columns including names and types:: 1845 1846 t = text("SELECT * FROM users WHERE id=:user_id").\ 1847 bindparams(user_id=7).\ 1848 columns(id=Integer, name=String) 1849 1850 for id, name in connection.execute(t): 1851 print(id, name) 1852 1853 The :func:`_expression.text` construct is used in cases when 1854 a literal string SQL fragment is specified as part of a larger query, 1855 such as for the WHERE clause of a SELECT statement:: 1856 1857 s = select(users.c.id, users.c.name).where(text("id=:user_id")) 1858 result = connection.execute(s, user_id=12) 1859 1860 :func:`_expression.text` is also used for the construction 1861 of a full, standalone statement using plain text. 1862 As such, SQLAlchemy refers 1863 to it as an :class:`.Executable` object, and it supports 1864 the :meth:`Executable.execution_options` method. For example, 1865 a :func:`_expression.text` 1866 construct that should be subject to "autocommit" 1867 can be set explicitly so using the 1868 :paramref:`.Connection.execution_options.autocommit` option:: 1869 1870 t = text("EXEC my_procedural_thing()").\ 1871 execution_options(autocommit=True) 1872 1873 .. deprecated:: 1.4 The "autocommit" execution option is deprecated 1874 and will be removed in SQLAlchemy 2.0. See 1875 :ref:`migration_20_autocommit` for discussion. 1876 1877 :param text: 1878 the text of the SQL statement to be created. Use ``:<param>`` 1879 to specify bind parameters; they will be compiled to their 1880 engine-specific format. 1881 1882 :param bind: 1883 an optional connection or engine to be used for this text query. 1884 1885 .. seealso:: 1886 1887 :ref:`sqlexpression_text` - in the Core tutorial 1888 1889 1890 """ 1891 return TextClause(text, bind=bind) 1892 1893 @_generative 1894 def bindparams(self, *binds, **names_to_values): 1895 """Establish the values and/or types of bound parameters within 1896 this :class:`_expression.TextClause` construct. 1897 1898 Given a text construct such as:: 1899 1900 from sqlalchemy import text 1901 stmt = text("SELECT id, name FROM user WHERE name=:name " 1902 "AND timestamp=:timestamp") 1903 1904 the :meth:`_expression.TextClause.bindparams` 1905 method can be used to establish 1906 the initial value of ``:name`` and ``:timestamp``, 1907 using simple keyword arguments:: 1908 1909 stmt = stmt.bindparams(name='jack', 1910 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5)) 1911 1912 Where above, new :class:`.BindParameter` objects 1913 will be generated with the names ``name`` and ``timestamp``, and 1914 values of ``jack`` and ``datetime.datetime(2012, 10, 8, 15, 12, 5)``, 1915 respectively. The types will be 1916 inferred from the values given, in this case :class:`.String` and 1917 :class:`.DateTime`. 1918 1919 When specific typing behavior is needed, the positional ``*binds`` 1920 argument can be used in which to specify :func:`.bindparam` constructs 1921 directly. These constructs must include at least the ``key`` 1922 argument, then an optional value and type:: 1923 1924 from sqlalchemy import bindparam 1925 stmt = stmt.bindparams( 1926 bindparam('name', value='jack', type_=String), 1927 bindparam('timestamp', type_=DateTime) 1928 ) 1929 1930 Above, we specified the type of :class:`.DateTime` for the 1931 ``timestamp`` bind, and the type of :class:`.String` for the ``name`` 1932 bind. In the case of ``name`` we also set the default value of 1933 ``"jack"``. 1934 1935 Additional bound parameters can be supplied at statement execution 1936 time, e.g.:: 1937 1938 result = connection.execute(stmt, 1939 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5)) 1940 1941 The :meth:`_expression.TextClause.bindparams` 1942 method can be called repeatedly, 1943 where it will re-use existing :class:`.BindParameter` objects to add 1944 new information. For example, we can call 1945 :meth:`_expression.TextClause.bindparams` 1946 first with typing information, and a 1947 second time with value information, and it will be combined:: 1948 1949 stmt = text("SELECT id, name FROM user WHERE name=:name " 1950 "AND timestamp=:timestamp") 1951 stmt = stmt.bindparams( 1952 bindparam('name', type_=String), 1953 bindparam('timestamp', type_=DateTime) 1954 ) 1955 stmt = stmt.bindparams( 1956 name='jack', 1957 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5) 1958 ) 1959 1960 The :meth:`_expression.TextClause.bindparams` 1961 method also supports the concept of 1962 **unique** bound parameters. These are parameters that are 1963 "uniquified" on name at statement compilation time, so that multiple 1964 :func:`_expression.text` 1965 constructs may be combined together without the names 1966 conflicting. To use this feature, specify the 1967 :paramref:`.BindParameter.unique` flag on each :func:`.bindparam` 1968 object:: 1969 1970 stmt1 = text("select id from table where name=:name").bindparams( 1971 bindparam("name", value='name1', unique=True) 1972 ) 1973 stmt2 = text("select id from table where name=:name").bindparams( 1974 bindparam("name", value='name2', unique=True) 1975 ) 1976 1977 union = union_all( 1978 stmt1.columns(column("id")), 1979 stmt2.columns(column("id")) 1980 ) 1981 1982 The above statement will render as:: 1983 1984 select id from table where name=:name_1 1985 UNION ALL select id from table where name=:name_2 1986 1987 .. versionadded:: 1.3.11 Added support for the 1988 :paramref:`.BindParameter.unique` flag to work with 1989 :func:`_expression.text` 1990 constructs. 1991 1992 """ 1993 self._bindparams = new_params = self._bindparams.copy() 1994 1995 for bind in binds: 1996 try: 1997 # the regex used for text() currently will not match 1998 # a unique/anonymous key in any case, so use the _orig_key 1999 # so that a text() construct can support unique parameters 2000 existing = new_params[bind._orig_key] 2001 except KeyError as err: 2002 util.raise_( 2003 exc.ArgumentError( 2004 "This text() construct doesn't define a " 2005 "bound parameter named %r" % bind._orig_key 2006 ), 2007 replace_context=err, 2008 ) 2009 else: 2010 new_params[existing._orig_key] = bind 2011 2012 for key, value in names_to_values.items(): 2013 try: 2014 existing = new_params[key] 2015 except KeyError as err: 2016 util.raise_( 2017 exc.ArgumentError( 2018 "This text() construct doesn't define a " 2019 "bound parameter named %r" % key 2020 ), 2021 replace_context=err, 2022 ) 2023 else: 2024 new_params[key] = existing._with_value(value, required=False) 2025 2026 @util.preload_module("sqlalchemy.sql.selectable") 2027 def columns(self, *cols, **types): 2028 r"""Turn this :class:`_expression.TextClause` object into a 2029 :class:`_expression.TextualSelect` 2030 object that serves the same role as a SELECT 2031 statement. 2032 2033 The :class:`_expression.TextualSelect` is part of the 2034 :class:`_expression.SelectBase` 2035 hierarchy and can be embedded into another statement by using the 2036 :meth:`_expression.TextualSelect.subquery` method to produce a 2037 :class:`.Subquery` 2038 object, which can then be SELECTed from. 2039 2040 This function essentially bridges the gap between an entirely 2041 textual SELECT statement and the SQL expression language concept 2042 of a "selectable":: 2043 2044 from sqlalchemy.sql import column, text 2045 2046 stmt = text("SELECT id, name FROM some_table") 2047 stmt = stmt.columns(column('id'), column('name')).subquery('st') 2048 2049 stmt = select(mytable).\ 2050 select_from( 2051 mytable.join(stmt, mytable.c.name == stmt.c.name) 2052 ).where(stmt.c.id > 5) 2053 2054 Above, we pass a series of :func:`_expression.column` elements to the 2055 :meth:`_expression.TextClause.columns` method positionally. These 2056 :func:`_expression.column` 2057 elements now become first class elements upon the 2058 :attr:`_expression.TextualSelect.selected_columns` column collection, 2059 which then 2060 become part of the :attr:`.Subquery.c` collection after 2061 :meth:`_expression.TextualSelect.subquery` is invoked. 2062 2063 The column expressions we pass to 2064 :meth:`_expression.TextClause.columns` may 2065 also be typed; when we do so, these :class:`.TypeEngine` objects become 2066 the effective return type of the column, so that SQLAlchemy's 2067 result-set-processing systems may be used on the return values. 2068 This is often needed for types such as date or boolean types, as well 2069 as for unicode processing on some dialect configurations:: 2070 2071 stmt = text("SELECT id, name, timestamp FROM some_table") 2072 stmt = stmt.columns( 2073 column('id', Integer), 2074 column('name', Unicode), 2075 column('timestamp', DateTime) 2076 ) 2077 2078 for id, name, timestamp in connection.execute(stmt): 2079 print(id, name, timestamp) 2080 2081 As a shortcut to the above syntax, keyword arguments referring to 2082 types alone may be used, if only type conversion is needed:: 2083 2084 stmt = text("SELECT id, name, timestamp FROM some_table") 2085 stmt = stmt.columns( 2086 id=Integer, 2087 name=Unicode, 2088 timestamp=DateTime 2089 ) 2090 2091 for id, name, timestamp in connection.execute(stmt): 2092 print(id, name, timestamp) 2093 2094 The positional form of :meth:`_expression.TextClause.columns` 2095 also provides the 2096 unique feature of **positional column targeting**, which is 2097 particularly useful when using the ORM with complex textual queries. If 2098 we specify the columns from our model to 2099 :meth:`_expression.TextClause.columns`, 2100 the result set will match to those columns positionally, meaning the 2101 name or origin of the column in the textual SQL doesn't matter:: 2102 2103 stmt = text("SELECT users.id, addresses.id, users.id, " 2104 "users.name, addresses.email_address AS email " 2105 "FROM users JOIN addresses ON users.id=addresses.user_id " 2106 "WHERE users.id = 1").columns( 2107 User.id, 2108 Address.id, 2109 Address.user_id, 2110 User.name, 2111 Address.email_address 2112 ) 2113 2114 query = session.query(User).from_statement(stmt).options( 2115 contains_eager(User.addresses)) 2116 2117 .. versionadded:: 1.1 the :meth:`_expression.TextClause.columns` 2118 method now 2119 offers positional column targeting in the result set when 2120 the column expressions are passed purely positionally. 2121 2122 The :meth:`_expression.TextClause.columns` method provides a direct 2123 route to calling :meth:`_expression.FromClause.subquery` as well as 2124 :meth:`_expression.SelectBase.cte` 2125 against a textual SELECT statement:: 2126 2127 stmt = stmt.columns(id=Integer, name=String).cte('st') 2128 2129 stmt = select(sometable).where(sometable.c.id == stmt.c.id) 2130 2131 :param \*cols: A series of :class:`_expression.ColumnElement` objects, 2132 typically 2133 :class:`_schema.Column` objects from a :class:`_schema.Table` 2134 or ORM level 2135 column-mapped attributes, representing a set of columns that this 2136 textual string will SELECT from. 2137 2138 :param \**types: A mapping of string names to :class:`.TypeEngine` 2139 type objects indicating the datatypes to use for names that are 2140 SELECTed from the textual string. Prefer to use the ``*cols`` 2141 argument as it also indicates positional ordering. 2142 2143 """ 2144 selectable = util.preloaded.sql_selectable 2145 positional_input_cols = [ 2146 ColumnClause(col.key, types.pop(col.key)) 2147 if col.key in types 2148 else col 2149 for col in cols 2150 ] 2151 keyed_input_cols = [ 2152 ColumnClause(key, type_) for key, type_ in types.items() 2153 ] 2154 2155 return selectable.TextualSelect( 2156 self, 2157 positional_input_cols + keyed_input_cols, 2158 positional=bool(positional_input_cols) and not keyed_input_cols, 2159 ) 2160 2161 @property 2162 def type(self): 2163 return type_api.NULLTYPE 2164 2165 @property 2166 def comparator(self): 2167 return self.type.comparator_factory(self) 2168 2169 def self_group(self, against=None): 2170 if against is operators.in_op: 2171 return Grouping(self) 2172 else: 2173 return self 2174 2175 2176class Null(SingletonConstant, roles.ConstExprRole, ColumnElement): 2177 """Represent the NULL keyword in a SQL statement. 2178 2179 :class:`.Null` is accessed as a constant via the 2180 :func:`.null` function. 2181 2182 """ 2183 2184 __visit_name__ = "null" 2185 2186 _traverse_internals = [] 2187 2188 @util.memoized_property 2189 def type(self): 2190 return type_api.NULLTYPE 2191 2192 @classmethod 2193 def _instance(cls): 2194 """Return a constant :class:`.Null` construct.""" 2195 2196 return Null() 2197 2198 2199Null._create_singleton() 2200 2201 2202class False_(SingletonConstant, roles.ConstExprRole, ColumnElement): 2203 """Represent the ``false`` keyword, or equivalent, in a SQL statement. 2204 2205 :class:`.False_` is accessed as a constant via the 2206 :func:`.false` function. 2207 2208 """ 2209 2210 __visit_name__ = "false" 2211 _traverse_internals = [] 2212 2213 @util.memoized_property 2214 def type(self): 2215 return type_api.BOOLEANTYPE 2216 2217 def _negate(self): 2218 return True_() 2219 2220 @classmethod 2221 def _instance(cls): 2222 """Return a :class:`.False_` construct. 2223 2224 E.g.:: 2225 2226 >>> from sqlalchemy import false 2227 >>> print(select(t.c.x).where(false())) 2228 SELECT x FROM t WHERE false 2229 2230 A backend which does not support true/false constants will render as 2231 an expression against 1 or 0:: 2232 2233 >>> print(select(t.c.x).where(false())) 2234 SELECT x FROM t WHERE 0 = 1 2235 2236 The :func:`.true` and :func:`.false` constants also feature 2237 "short circuit" operation within an :func:`.and_` or :func:`.or_` 2238 conjunction:: 2239 2240 >>> print(select(t.c.x).where(or_(t.c.x > 5, true()))) 2241 SELECT x FROM t WHERE true 2242 2243 >>> print(select(t.c.x).where(and_(t.c.x > 5, false()))) 2244 SELECT x FROM t WHERE false 2245 2246 .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature 2247 better integrated behavior within conjunctions and on dialects 2248 that don't support true/false constants. 2249 2250 .. seealso:: 2251 2252 :func:`.true` 2253 2254 """ 2255 2256 return False_() 2257 2258 2259False_._create_singleton() 2260 2261 2262class True_(SingletonConstant, roles.ConstExprRole, ColumnElement): 2263 """Represent the ``true`` keyword, or equivalent, in a SQL statement. 2264 2265 :class:`.True_` is accessed as a constant via the 2266 :func:`.true` function. 2267 2268 """ 2269 2270 __visit_name__ = "true" 2271 2272 _traverse_internals = [] 2273 2274 @util.memoized_property 2275 def type(self): 2276 return type_api.BOOLEANTYPE 2277 2278 def _negate(self): 2279 return False_() 2280 2281 @classmethod 2282 def _ifnone(cls, other): 2283 if other is None: 2284 return cls._instance() 2285 else: 2286 return other 2287 2288 @classmethod 2289 def _instance(cls): 2290 """Return a constant :class:`.True_` construct. 2291 2292 E.g.:: 2293 2294 >>> from sqlalchemy import true 2295 >>> print(select(t.c.x).where(true())) 2296 SELECT x FROM t WHERE true 2297 2298 A backend which does not support true/false constants will render as 2299 an expression against 1 or 0:: 2300 2301 >>> print(select(t.c.x).where(true())) 2302 SELECT x FROM t WHERE 1 = 1 2303 2304 The :func:`.true` and :func:`.false` constants also feature 2305 "short circuit" operation within an :func:`.and_` or :func:`.or_` 2306 conjunction:: 2307 2308 >>> print(select(t.c.x).where(or_(t.c.x > 5, true()))) 2309 SELECT x FROM t WHERE true 2310 2311 >>> print(select(t.c.x).where(and_(t.c.x > 5, false()))) 2312 SELECT x FROM t WHERE false 2313 2314 .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature 2315 better integrated behavior within conjunctions and on dialects 2316 that don't support true/false constants. 2317 2318 .. seealso:: 2319 2320 :func:`.false` 2321 2322 """ 2323 2324 return True_() 2325 2326 2327True_._create_singleton() 2328 2329 2330class ClauseList( 2331 roles.InElementRole, 2332 roles.OrderByRole, 2333 roles.ColumnsClauseRole, 2334 roles.DMLColumnRole, 2335 ClauseElement, 2336): 2337 """Describe a list of clauses, separated by an operator. 2338 2339 By default, is comma-separated, such as a column listing. 2340 2341 """ 2342 2343 __visit_name__ = "clauselist" 2344 2345 _is_clause_list = True 2346 2347 _traverse_internals = [ 2348 ("clauses", InternalTraversal.dp_clauseelement_list), 2349 ("operator", InternalTraversal.dp_operator), 2350 ] 2351 2352 def __init__(self, *clauses, **kwargs): 2353 self.operator = kwargs.pop("operator", operators.comma_op) 2354 self.group = kwargs.pop("group", True) 2355 self.group_contents = kwargs.pop("group_contents", True) 2356 if kwargs.pop("_flatten_sub_clauses", False): 2357 clauses = util.flatten_iterator(clauses) 2358 self._text_converter_role = text_converter_role = kwargs.pop( 2359 "_literal_as_text_role", roles.WhereHavingRole 2360 ) 2361 if self.group_contents: 2362 self.clauses = [ 2363 coercions.expect( 2364 text_converter_role, clause, apply_propagate_attrs=self 2365 ).self_group(against=self.operator) 2366 for clause in clauses 2367 ] 2368 else: 2369 self.clauses = [ 2370 coercions.expect( 2371 text_converter_role, clause, apply_propagate_attrs=self 2372 ) 2373 for clause in clauses 2374 ] 2375 self._is_implicitly_boolean = operators.is_boolean(self.operator) 2376 2377 @classmethod 2378 def _construct_raw(cls, operator, clauses=None): 2379 self = cls.__new__(cls) 2380 self.clauses = clauses if clauses else [] 2381 self.group = True 2382 self.operator = operator 2383 self.group_contents = True 2384 self._is_implicitly_boolean = False 2385 return self 2386 2387 def __iter__(self): 2388 return iter(self.clauses) 2389 2390 def __len__(self): 2391 return len(self.clauses) 2392 2393 @property 2394 def _select_iterable(self): 2395 return itertools.chain.from_iterable( 2396 [elem._select_iterable for elem in self.clauses] 2397 ) 2398 2399 def append(self, clause): 2400 if self.group_contents: 2401 self.clauses.append( 2402 coercions.expect(self._text_converter_role, clause).self_group( 2403 against=self.operator 2404 ) 2405 ) 2406 else: 2407 self.clauses.append( 2408 coercions.expect(self._text_converter_role, clause) 2409 ) 2410 2411 @property 2412 def _from_objects(self): 2413 return list(itertools.chain(*[c._from_objects for c in self.clauses])) 2414 2415 def self_group(self, against=None): 2416 if self.group and operators.is_precedent(self.operator, against): 2417 return Grouping(self) 2418 else: 2419 return self 2420 2421 2422class BooleanClauseList(ClauseList, ColumnElement): 2423 __visit_name__ = "clauselist" 2424 inherit_cache = True 2425 2426 def __init__(self, *arg, **kw): 2427 raise NotImplementedError( 2428 "BooleanClauseList has a private constructor" 2429 ) 2430 2431 @classmethod 2432 def _process_clauses_for_boolean( 2433 cls, operator, continue_on, skip_on, clauses 2434 ): 2435 has_continue_on = None 2436 2437 convert_clauses = [] 2438 2439 against = operators._asbool 2440 lcc = 0 2441 2442 for clause in clauses: 2443 if clause is continue_on: 2444 # instance of continue_on, like and_(x, y, True, z), store it 2445 # if we didn't find one already, we will use it if there 2446 # are no other expressions here. 2447 has_continue_on = clause 2448 elif clause is skip_on: 2449 # instance of skip_on, e.g. and_(x, y, False, z), cancels 2450 # the rest out 2451 convert_clauses = [clause] 2452 lcc = 1 2453 break 2454 else: 2455 if not lcc: 2456 lcc = 1 2457 else: 2458 against = operator 2459 # technically this would be len(convert_clauses) + 1 2460 # however this only needs to indicate "greater than one" 2461 lcc = 2 2462 convert_clauses.append(clause) 2463 2464 if not convert_clauses and has_continue_on is not None: 2465 convert_clauses = [has_continue_on] 2466 lcc = 1 2467 2468 return lcc, [c.self_group(against=against) for c in convert_clauses] 2469 2470 @classmethod 2471 def _construct(cls, operator, continue_on, skip_on, *clauses, **kw): 2472 lcc, convert_clauses = cls._process_clauses_for_boolean( 2473 operator, 2474 continue_on, 2475 skip_on, 2476 [ 2477 coercions.expect(roles.WhereHavingRole, clause) 2478 for clause in util.coerce_generator_arg(clauses) 2479 ], 2480 ) 2481 2482 if lcc > 1: 2483 # multiple elements. Return regular BooleanClauseList 2484 # which will link elements against the operator. 2485 return cls._construct_raw(operator, convert_clauses) 2486 elif lcc == 1: 2487 # just one element. return it as a single boolean element, 2488 # not a list and discard the operator. 2489 return convert_clauses[0] 2490 else: 2491 # no elements period. deprecated use case. return an empty 2492 # ClauseList construct that generates nothing unless it has 2493 # elements added to it. 2494 util.warn_deprecated( 2495 "Invoking %(name)s() without arguments is deprecated, and " 2496 "will be disallowed in a future release. For an empty " 2497 "%(name)s() construct, use %(name)s(%(continue_on)s, *args)." 2498 % { 2499 "name": operator.__name__, 2500 "continue_on": "True" 2501 if continue_on is True_._singleton 2502 else "False", 2503 }, 2504 version="1.4", 2505 ) 2506 return cls._construct_raw(operator) 2507 2508 @classmethod 2509 def _construct_for_whereclause(cls, clauses): 2510 operator, continue_on, skip_on = ( 2511 operators.and_, 2512 True_._singleton, 2513 False_._singleton, 2514 ) 2515 2516 lcc, convert_clauses = cls._process_clauses_for_boolean( 2517 operator, 2518 continue_on, 2519 skip_on, 2520 clauses, # these are assumed to be coerced already 2521 ) 2522 2523 if lcc > 1: 2524 # multiple elements. Return regular BooleanClauseList 2525 # which will link elements against the operator. 2526 return cls._construct_raw(operator, convert_clauses) 2527 elif lcc == 1: 2528 # just one element. return it as a single boolean element, 2529 # not a list and discard the operator. 2530 return convert_clauses[0] 2531 else: 2532 return None 2533 2534 @classmethod 2535 def _construct_raw(cls, operator, clauses=None): 2536 self = cls.__new__(cls) 2537 self.clauses = clauses if clauses else [] 2538 self.group = True 2539 self.operator = operator 2540 self.group_contents = True 2541 self.type = type_api.BOOLEANTYPE 2542 self._is_implicitly_boolean = True 2543 return self 2544 2545 @classmethod 2546 def and_(cls, *clauses): 2547 r"""Produce a conjunction of expressions joined by ``AND``. 2548 2549 E.g.:: 2550 2551 from sqlalchemy import and_ 2552 2553 stmt = select(users_table).where( 2554 and_( 2555 users_table.c.name == 'wendy', 2556 users_table.c.enrolled == True 2557 ) 2558 ) 2559 2560 The :func:`.and_` conjunction is also available using the 2561 Python ``&`` operator (though note that compound expressions 2562 need to be parenthesized in order to function with Python 2563 operator precedence behavior):: 2564 2565 stmt = select(users_table).where( 2566 (users_table.c.name == 'wendy') & 2567 (users_table.c.enrolled == True) 2568 ) 2569 2570 The :func:`.and_` operation is also implicit in some cases; 2571 the :meth:`_expression.Select.where` 2572 method for example can be invoked multiple 2573 times against a statement, which will have the effect of each 2574 clause being combined using :func:`.and_`:: 2575 2576 stmt = select(users_table).\ 2577 where(users_table.c.name == 'wendy').\ 2578 where(users_table.c.enrolled == True) 2579 2580 The :func:`.and_` construct must be given at least one positional 2581 argument in order to be valid; a :func:`.and_` construct with no 2582 arguments is ambiguous. To produce an "empty" or dynamically 2583 generated :func:`.and_` expression, from a given list of expressions, 2584 a "default" element of ``True`` should be specified:: 2585 2586 criteria = and_(True, *expressions) 2587 2588 The above expression will compile to SQL as the expression ``true`` 2589 or ``1 = 1``, depending on backend, if no other expressions are 2590 present. If expressions are present, then the ``True`` value is 2591 ignored as it does not affect the outcome of an AND expression that 2592 has other elements. 2593 2594 .. deprecated:: 1.4 The :func:`.and_` element now requires that at 2595 least one argument is passed; creating the :func:`.and_` construct 2596 with no arguments is deprecated, and will emit a deprecation warning 2597 while continuing to produce a blank SQL string. 2598 2599 .. seealso:: 2600 2601 :func:`.or_` 2602 2603 """ 2604 return cls._construct( 2605 operators.and_, True_._singleton, False_._singleton, *clauses 2606 ) 2607 2608 @classmethod 2609 def or_(cls, *clauses): 2610 """Produce a conjunction of expressions joined by ``OR``. 2611 2612 E.g.:: 2613 2614 from sqlalchemy import or_ 2615 2616 stmt = select(users_table).where( 2617 or_( 2618 users_table.c.name == 'wendy', 2619 users_table.c.name == 'jack' 2620 ) 2621 ) 2622 2623 The :func:`.or_` conjunction is also available using the 2624 Python ``|`` operator (though note that compound expressions 2625 need to be parenthesized in order to function with Python 2626 operator precedence behavior):: 2627 2628 stmt = select(users_table).where( 2629 (users_table.c.name == 'wendy') | 2630 (users_table.c.name == 'jack') 2631 ) 2632 2633 The :func:`.or_` construct must be given at least one positional 2634 argument in order to be valid; a :func:`.or_` construct with no 2635 arguments is ambiguous. To produce an "empty" or dynamically 2636 generated :func:`.or_` expression, from a given list of expressions, 2637 a "default" element of ``False`` should be specified:: 2638 2639 or_criteria = or_(False, *expressions) 2640 2641 The above expression will compile to SQL as the expression ``false`` 2642 or ``0 = 1``, depending on backend, if no other expressions are 2643 present. If expressions are present, then the ``False`` value is 2644 ignored as it does not affect the outcome of an OR expression which 2645 has other elements. 2646 2647 .. deprecated:: 1.4 The :func:`.or_` element now requires that at 2648 least one argument is passed; creating the :func:`.or_` construct 2649 with no arguments is deprecated, and will emit a deprecation warning 2650 while continuing to produce a blank SQL string. 2651 2652 .. seealso:: 2653 2654 :func:`.and_` 2655 2656 """ 2657 return cls._construct( 2658 operators.or_, False_._singleton, True_._singleton, *clauses 2659 ) 2660 2661 @property 2662 def _select_iterable(self): 2663 return (self,) 2664 2665 def self_group(self, against=None): 2666 if not self.clauses: 2667 return self 2668 else: 2669 return super(BooleanClauseList, self).self_group(against=against) 2670 2671 def _negate(self): 2672 return ClauseList._negate(self) 2673 2674 2675and_ = BooleanClauseList.and_ 2676or_ = BooleanClauseList.or_ 2677 2678 2679class Tuple(ClauseList, ColumnElement): 2680 """Represent a SQL tuple.""" 2681 2682 __visit_name__ = "tuple" 2683 2684 _traverse_internals = ClauseList._traverse_internals + [] 2685 2686 @util.preload_module("sqlalchemy.sql.sqltypes") 2687 def __init__(self, *clauses, **kw): 2688 """Return a :class:`.Tuple`. 2689 2690 Main usage is to produce a composite IN construct using 2691 :meth:`.ColumnOperators.in_` :: 2692 2693 from sqlalchemy import tuple_ 2694 2695 tuple_(table.c.col1, table.c.col2).in_( 2696 [(1, 2), (5, 12), (10, 19)] 2697 ) 2698 2699 .. versionchanged:: 1.3.6 Added support for SQLite IN tuples. 2700 2701 .. warning:: 2702 2703 The composite IN construct is not supported by all backends, and is 2704 currently known to work on PostgreSQL, MySQL, and SQLite. 2705 Unsupported backends will raise a subclass of 2706 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is 2707 invoked. 2708 2709 """ 2710 sqltypes = util.preloaded.sql_sqltypes 2711 2712 types = kw.pop("types", None) 2713 if types is None: 2714 clauses = [ 2715 coercions.expect(roles.ExpressionElementRole, c) 2716 for c in clauses 2717 ] 2718 else: 2719 if len(types) != len(clauses): 2720 raise exc.ArgumentError( 2721 "Wrong number of elements for %d-tuple: %r " 2722 % (len(types), clauses) 2723 ) 2724 clauses = [ 2725 coercions.expect( 2726 roles.ExpressionElementRole, 2727 c, 2728 type_=typ if not typ._isnull else None, 2729 ) 2730 for typ, c in zip(types, clauses) 2731 ] 2732 2733 self.type = sqltypes.TupleType(*[arg.type for arg in clauses]) 2734 super(Tuple, self).__init__(*clauses, **kw) 2735 2736 @property 2737 def _select_iterable(self): 2738 return (self,) 2739 2740 def _bind_param(self, operator, obj, type_=None, expanding=False): 2741 if expanding: 2742 return BindParameter( 2743 None, 2744 value=obj, 2745 _compared_to_operator=operator, 2746 unique=True, 2747 expanding=True, 2748 type_=self.type, 2749 ) 2750 else: 2751 return Tuple( 2752 *[ 2753 BindParameter( 2754 None, 2755 o, 2756 _compared_to_operator=operator, 2757 _compared_to_type=compared_to_type, 2758 unique=True, 2759 type_=type_, 2760 ) 2761 for o, compared_to_type in zip(obj, self.type.types) 2762 ] 2763 ) 2764 2765 def self_group(self, against=None): 2766 # Tuple is parenthesized by definition. 2767 return self 2768 2769 2770class Case(ColumnElement): 2771 """Represent a ``CASE`` expression. 2772 2773 :class:`.Case` is produced using the :func:`.case` factory function, 2774 as in:: 2775 2776 from sqlalchemy import case 2777 2778 stmt = select(users_table).\ 2779 where( 2780 case( 2781 (users_table.c.name == 'wendy', 'W'), 2782 (users_table.c.name == 'jack', 'J'), 2783 else_='E' 2784 ) 2785 ) 2786 2787 Details on :class:`.Case` usage is at :func:`.case`. 2788 2789 .. seealso:: 2790 2791 :func:`.case` 2792 2793 """ 2794 2795 __visit_name__ = "case" 2796 2797 _traverse_internals = [ 2798 ("value", InternalTraversal.dp_clauseelement), 2799 ("whens", InternalTraversal.dp_clauseelement_tuples), 2800 ("else_", InternalTraversal.dp_clauseelement), 2801 ] 2802 2803 # TODO: for Py2k removal, this will be: 2804 # def __init__(self, *whens, value=None, else_=None): 2805 2806 def __init__(self, *whens, **kw): 2807 r"""Produce a ``CASE`` expression. 2808 2809 The ``CASE`` construct in SQL is a conditional object that 2810 acts somewhat analogously to an "if/then" construct in other 2811 languages. It returns an instance of :class:`.Case`. 2812 2813 :func:`.case` in its usual form is passed a series of "when" 2814 constructs, that is, a list of conditions and results as tuples:: 2815 2816 from sqlalchemy import case 2817 2818 stmt = select(users_table).\ 2819 where( 2820 case( 2821 (users_table.c.name == 'wendy', 'W'), 2822 (users_table.c.name == 'jack', 'J'), 2823 else_='E' 2824 ) 2825 ) 2826 2827 The above statement will produce SQL resembling:: 2828 2829 SELECT id, name FROM user 2830 WHERE CASE 2831 WHEN (name = :name_1) THEN :param_1 2832 WHEN (name = :name_2) THEN :param_2 2833 ELSE :param_3 2834 END 2835 2836 When simple equality expressions of several values against a single 2837 parent column are needed, :func:`.case` also has a "shorthand" format 2838 used via the 2839 :paramref:`.case.value` parameter, which is passed a column 2840 expression to be compared. In this form, the :paramref:`.case.whens` 2841 parameter is passed as a dictionary containing expressions to be 2842 compared against keyed to result expressions. The statement below is 2843 equivalent to the preceding statement:: 2844 2845 stmt = select(users_table).\ 2846 where( 2847 case( 2848 {"wendy": "W", "jack": "J"}, 2849 value=users_table.c.name, 2850 else_='E' 2851 ) 2852 ) 2853 2854 The values which are accepted as result values in 2855 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are 2856 coerced from Python literals into :func:`.bindparam` constructs. 2857 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs, 2858 are accepted 2859 as well. To coerce a literal string expression into a constant 2860 expression rendered inline, use the :func:`_expression.literal_column` 2861 construct, 2862 as in:: 2863 2864 from sqlalchemy import case, literal_column 2865 2866 case( 2867 ( 2868 orderline.c.qty > 100, 2869 literal_column("'greaterthan100'") 2870 ), 2871 ( 2872 orderline.c.qty > 10, 2873 literal_column("'greaterthan10'") 2874 ), 2875 else_=literal_column("'lessthan10'") 2876 ) 2877 2878 The above will render the given constants without using bound 2879 parameters for the result values (but still for the comparison 2880 values), as in:: 2881 2882 CASE 2883 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100' 2884 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10' 2885 ELSE 'lessthan10' 2886 END 2887 2888 :param \*whens: The criteria to be compared against, 2889 :paramref:`.case.whens` accepts two different forms, based on 2890 whether or not :paramref:`.case.value` is used. 2891 2892 .. versionchanged:: 1.4 the :func:`_sql.case` 2893 function now accepts the series of WHEN conditions positionally; 2894 passing the expressions within a list is deprecated. 2895 2896 In the first form, it accepts a list of 2-tuples; each 2-tuple 2897 consists of ``(<sql expression>, <value>)``, where the SQL 2898 expression is a boolean expression and "value" is a resulting value, 2899 e.g.:: 2900 2901 case( 2902 (users_table.c.name == 'wendy', 'W'), 2903 (users_table.c.name == 'jack', 'J') 2904 ) 2905 2906 In the second form, it accepts a Python dictionary of comparison 2907 values mapped to a resulting value; this form requires 2908 :paramref:`.case.value` to be present, and values will be compared 2909 using the ``==`` operator, e.g.:: 2910 2911 case( 2912 {"wendy": "W", "jack": "J"}, 2913 value=users_table.c.name 2914 ) 2915 2916 :param value: An optional SQL expression which will be used as a 2917 fixed "comparison point" for candidate values within a dictionary 2918 passed to :paramref:`.case.whens`. 2919 2920 :param else\_: An optional SQL expression which will be the evaluated 2921 result of the ``CASE`` construct if all expressions within 2922 :paramref:`.case.whens` evaluate to false. When omitted, most 2923 databases will produce a result of NULL if none of the "when" 2924 expressions evaluate to true. 2925 2926 2927 """ 2928 2929 if "whens" in kw: 2930 util.warn_deprecated_20( 2931 'The "whens" argument to case() is now passed using ' 2932 "positional style only, not as a keyword argument." 2933 ) 2934 whens = (kw.pop("whens"),) 2935 2936 whens = coercions._expression_collection_was_a_list( 2937 "whens", "case", whens 2938 ) 2939 2940 try: 2941 whens = util.dictlike_iteritems(whens) 2942 except TypeError: 2943 pass 2944 2945 value = kw.pop("value", None) 2946 2947 whenlist = [ 2948 ( 2949 coercions.expect( 2950 roles.ExpressionElementRole, 2951 c, 2952 apply_propagate_attrs=self, 2953 ).self_group(), 2954 coercions.expect(roles.ExpressionElementRole, r), 2955 ) 2956 for (c, r) in whens 2957 ] 2958 2959 if whenlist: 2960 type_ = list(whenlist[-1])[-1].type 2961 else: 2962 type_ = None 2963 2964 if value is None: 2965 self.value = None 2966 else: 2967 self.value = coercions.expect(roles.ExpressionElementRole, value) 2968 2969 self.type = type_ 2970 self.whens = whenlist 2971 2972 else_ = kw.pop("else_", None) 2973 if else_ is not None: 2974 self.else_ = coercions.expect(roles.ExpressionElementRole, else_) 2975 else: 2976 self.else_ = None 2977 2978 if kw: 2979 raise TypeError("unknown arguments: %s" % (", ".join(sorted(kw)))) 2980 2981 @property 2982 def _from_objects(self): 2983 return list( 2984 itertools.chain(*[x._from_objects for x in self.get_children()]) 2985 ) 2986 2987 2988def literal_column(text, type_=None): 2989 r"""Produce a :class:`.ColumnClause` object that has the 2990 :paramref:`_expression.column.is_literal` flag set to True. 2991 2992 :func:`_expression.literal_column` is similar to 2993 :func:`_expression.column`, except that 2994 it is more often used as a "standalone" column expression that renders 2995 exactly as stated; while :func:`_expression.column` 2996 stores a string name that 2997 will be assumed to be part of a table and may be quoted as such, 2998 :func:`_expression.literal_column` can be that, 2999 or any other arbitrary column-oriented 3000 expression. 3001 3002 :param text: the text of the expression; can be any SQL expression. 3003 Quoting rules will not be applied. To specify a column-name expression 3004 which should be subject to quoting rules, use the :func:`column` 3005 function. 3006 3007 :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` 3008 object which will 3009 provide result-set translation and additional expression semantics for 3010 this column. If left as ``None`` the type will be :class:`.NullType`. 3011 3012 .. seealso:: 3013 3014 :func:`_expression.column` 3015 3016 :func:`_expression.text` 3017 3018 :ref:`sqlexpression_literal_column` 3019 3020 """ 3021 return ColumnClause(text, type_=type_, is_literal=True) 3022 3023 3024class Cast(WrapsColumnExpression, ColumnElement): 3025 """Represent a ``CAST`` expression. 3026 3027 :class:`.Cast` is produced using the :func:`.cast` factory function, 3028 as in:: 3029 3030 from sqlalchemy import cast, Numeric 3031 3032 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4))) 3033 3034 Details on :class:`.Cast` usage is at :func:`.cast`. 3035 3036 .. seealso:: 3037 3038 :ref:`coretutorial_casts` 3039 3040 :func:`.cast` 3041 3042 :func:`.type_coerce` - an alternative to CAST that coerces the type 3043 on the Python side only, which is often sufficient to generate the 3044 correct SQL and data coercion. 3045 3046 """ 3047 3048 __visit_name__ = "cast" 3049 3050 _traverse_internals = [ 3051 ("clause", InternalTraversal.dp_clauseelement), 3052 ("typeclause", InternalTraversal.dp_clauseelement), 3053 ] 3054 3055 def __init__(self, expression, type_): 3056 r"""Produce a ``CAST`` expression. 3057 3058 :func:`.cast` returns an instance of :class:`.Cast`. 3059 3060 E.g.:: 3061 3062 from sqlalchemy import cast, Numeric 3063 3064 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4))) 3065 3066 The above statement will produce SQL resembling:: 3067 3068 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product 3069 3070 The :func:`.cast` function performs two distinct functions when 3071 used. The first is that it renders the ``CAST`` expression within 3072 the resulting SQL string. The second is that it associates the given 3073 type (e.g. :class:`.TypeEngine` class or instance) with the column 3074 expression on the Python side, which means the expression will take 3075 on the expression operator behavior associated with that type, 3076 as well as the bound-value handling and result-row-handling behavior 3077 of the type. 3078 3079 .. versionchanged:: 0.9.0 :func:`.cast` now applies the given type 3080 to the expression such that it takes effect on the bound-value, 3081 e.g. the Python-to-database direction, in addition to the 3082 result handling, e.g. database-to-Python, direction. 3083 3084 An alternative to :func:`.cast` is the :func:`.type_coerce` function. 3085 This function performs the second task of associating an expression 3086 with a specific type, but does not render the ``CAST`` expression 3087 in SQL. 3088 3089 :param expression: A SQL expression, such as a 3090 :class:`_expression.ColumnElement` 3091 expression or a Python string which will be coerced into a bound 3092 literal value. 3093 3094 :param type\_: A :class:`.TypeEngine` class or instance indicating 3095 the type to which the ``CAST`` should apply. 3096 3097 .. seealso:: 3098 3099 :ref:`coretutorial_casts` 3100 3101 :func:`.type_coerce` - an alternative to CAST that coerces the type 3102 on the Python side only, which is often sufficient to generate the 3103 correct SQL and data coercion. 3104 3105 3106 """ 3107 self.type = type_api.to_instance(type_) 3108 self.clause = coercions.expect( 3109 roles.ExpressionElementRole, 3110 expression, 3111 type_=self.type, 3112 apply_propagate_attrs=self, 3113 ) 3114 self.typeclause = TypeClause(self.type) 3115 3116 @property 3117 def _from_objects(self): 3118 return self.clause._from_objects 3119 3120 @property 3121 def wrapped_column_expression(self): 3122 return self.clause 3123 3124 3125class TypeCoerce(WrapsColumnExpression, ColumnElement): 3126 """Represent a Python-side type-coercion wrapper. 3127 3128 :class:`.TypeCoerce` supplies the :func:`_expression.type_coerce` 3129 function; see that function for usage details. 3130 3131 .. versionchanged:: 1.1 The :func:`.type_coerce` function now produces 3132 a persistent :class:`.TypeCoerce` wrapper object rather than 3133 translating the given object in place. 3134 3135 .. seealso:: 3136 3137 :func:`_expression.type_coerce` 3138 3139 :func:`.cast` 3140 3141 """ 3142 3143 __visit_name__ = "type_coerce" 3144 3145 _traverse_internals = [ 3146 ("clause", InternalTraversal.dp_clauseelement), 3147 ("type", InternalTraversal.dp_type), 3148 ] 3149 3150 def __init__(self, expression, type_): 3151 r"""Associate a SQL expression with a particular type, without rendering 3152 ``CAST``. 3153 3154 E.g.:: 3155 3156 from sqlalchemy import type_coerce 3157 3158 stmt = select(type_coerce(log_table.date_string, StringDateTime())) 3159 3160 The above construct will produce a :class:`.TypeCoerce` object, which 3161 does not modify the rendering in any way on the SQL side, with the 3162 possible exception of a generated label if used in a columns clause 3163 context:: 3164 3165 SELECT date_string AS date_string FROM log 3166 3167 When result rows are fetched, the ``StringDateTime`` type processor 3168 will be applied to result rows on behalf of the ``date_string`` column. 3169 3170 .. note:: the :func:`.type_coerce` construct does not render any 3171 SQL syntax of its own, including that it does not imply 3172 parenthesization. Please use :meth:`.TypeCoerce.self_group` 3173 if explicit parenthesization is required. 3174 3175 In order to provide a named label for the expression, use 3176 :meth:`_expression.ColumnElement.label`:: 3177 3178 stmt = select( 3179 type_coerce(log_table.date_string, StringDateTime()).label('date') 3180 ) 3181 3182 3183 A type that features bound-value handling will also have that behavior 3184 take effect when literal values or :func:`.bindparam` constructs are 3185 passed to :func:`.type_coerce` as targets. 3186 For example, if a type implements the 3187 :meth:`.TypeEngine.bind_expression` 3188 method or :meth:`.TypeEngine.bind_processor` method or equivalent, 3189 these functions will take effect at statement compilation/execution 3190 time when a literal value is passed, as in:: 3191 3192 # bound-value handling of MyStringType will be applied to the 3193 # literal value "some string" 3194 stmt = select(type_coerce("some string", MyStringType)) 3195 3196 When using :func:`.type_coerce` with composed expressions, note that 3197 **parenthesis are not applied**. If :func:`.type_coerce` is being 3198 used in an operator context where the parenthesis normally present from 3199 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:: 3200 3201 >>> some_integer = column("someint", Integer) 3202 >>> some_string = column("somestr", String) 3203 >>> expr = type_coerce(some_integer + 5, String) + some_string 3204 >>> print(expr) 3205 someint + :someint_1 || somestr 3206 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string 3207 >>> print(expr) 3208 (someint + :someint_1) || somestr 3209 3210 :param expression: A SQL expression, such as a 3211 :class:`_expression.ColumnElement` 3212 expression or a Python string which will be coerced into a bound 3213 literal value. 3214 3215 :param type\_: A :class:`.TypeEngine` class or instance indicating 3216 the type to which the expression is coerced. 3217 3218 .. seealso:: 3219 3220 :ref:`coretutorial_casts` 3221 3222 :func:`.cast` 3223 3224 """ # noqa 3225 self.type = type_api.to_instance(type_) 3226 self.clause = coercions.expect( 3227 roles.ExpressionElementRole, 3228 expression, 3229 type_=self.type, 3230 apply_propagate_attrs=self, 3231 ) 3232 3233 @property 3234 def _from_objects(self): 3235 return self.clause._from_objects 3236 3237 @HasMemoized.memoized_attribute 3238 def typed_expression(self): 3239 if isinstance(self.clause, BindParameter): 3240 bp = self.clause._clone() 3241 bp.type = self.type 3242 return bp 3243 else: 3244 return self.clause 3245 3246 @property 3247 def wrapped_column_expression(self): 3248 return self.clause 3249 3250 def self_group(self, against=None): 3251 grouped = self.clause.self_group(against=against) 3252 if grouped is not self.clause: 3253 return TypeCoerce(grouped, self.type) 3254 else: 3255 return self 3256 3257 3258class Extract(ColumnElement): 3259 """Represent a SQL EXTRACT clause, ``extract(field FROM expr)``.""" 3260 3261 __visit_name__ = "extract" 3262 3263 _traverse_internals = [ 3264 ("expr", InternalTraversal.dp_clauseelement), 3265 ("field", InternalTraversal.dp_string), 3266 ] 3267 3268 def __init__(self, field, expr, **kwargs): 3269 """Return a :class:`.Extract` construct. 3270 3271 This is typically available as :func:`.extract` 3272 as well as ``func.extract`` from the 3273 :data:`.func` namespace. 3274 3275 :param field: The field to extract. 3276 3277 :param expr: A column or Python scalar expression serving as the 3278 right side of the ``EXTRACT`` expression. 3279 3280 E.g.:: 3281 3282 from sqlalchemy import extract 3283 from sqlalchemy import table, column 3284 3285 logged_table = table("user", 3286 column("id"), 3287 column("date_created"), 3288 ) 3289 3290 stmt = select(logged_table.c.id).where( 3291 extract("YEAR", logged_table.c.date_created) == 2021 3292 ) 3293 3294 In the above example, the statement is used to select ids from the 3295 database where the ``YEAR`` component matches a specific value. 3296 3297 Similarly, one can also select an extracted component:: 3298 3299 stmt = select( 3300 extract("YEAR", logged_table.c.date_created) 3301 ).where(logged_table.c.id == 1) 3302 3303 The implementation of ``EXTRACT`` may vary across database backends. 3304 Users are reminded to consult their database documentation. 3305 """ 3306 self.type = type_api.INTEGERTYPE 3307 self.field = field 3308 self.expr = coercions.expect(roles.ExpressionElementRole, expr) 3309 3310 @property 3311 def _from_objects(self): 3312 return self.expr._from_objects 3313 3314 3315class _label_reference(ColumnElement): 3316 """Wrap a column expression as it appears in a 'reference' context. 3317 3318 This expression is any that includes an _order_by_label_element, 3319 which is a Label, or a DESC / ASC construct wrapping a Label. 3320 3321 The production of _label_reference() should occur when an expression 3322 is added to this context; this includes the ORDER BY or GROUP BY of a 3323 SELECT statement, as well as a few other places, such as the ORDER BY 3324 within an OVER clause. 3325 3326 """ 3327 3328 __visit_name__ = "label_reference" 3329 3330 _traverse_internals = [("element", InternalTraversal.dp_clauseelement)] 3331 3332 def __init__(self, element): 3333 self.element = element 3334 3335 @property 3336 def _from_objects(self): 3337 return () 3338 3339 3340class _textual_label_reference(ColumnElement): 3341 __visit_name__ = "textual_label_reference" 3342 3343 _traverse_internals = [("element", InternalTraversal.dp_string)] 3344 3345 def __init__(self, element): 3346 self.element = element 3347 3348 @util.memoized_property 3349 def _text_clause(self): 3350 return TextClause._create_text(self.element) 3351 3352 3353class UnaryExpression(ColumnElement): 3354 """Define a 'unary' expression. 3355 3356 A unary expression has a single column expression 3357 and an operator. The operator can be placed on the left 3358 (where it is called the 'operator') or right (where it is called the 3359 'modifier') of the column expression. 3360 3361 :class:`.UnaryExpression` is the basis for several unary operators 3362 including those used by :func:`.desc`, :func:`.asc`, :func:`.distinct`, 3363 :func:`.nulls_first` and :func:`.nulls_last`. 3364 3365 """ 3366 3367 __visit_name__ = "unary" 3368 3369 _traverse_internals = [ 3370 ("element", InternalTraversal.dp_clauseelement), 3371 ("operator", InternalTraversal.dp_operator), 3372 ("modifier", InternalTraversal.dp_operator), 3373 ] 3374 3375 def __init__( 3376 self, 3377 element, 3378 operator=None, 3379 modifier=None, 3380 type_=None, 3381 wraps_column_expression=False, 3382 ): 3383 self.operator = operator 3384 self.modifier = modifier 3385 self._propagate_attrs = element._propagate_attrs 3386 self.element = element.self_group( 3387 against=self.operator or self.modifier 3388 ) 3389 self.type = type_api.to_instance(type_) 3390 self.wraps_column_expression = wraps_column_expression 3391 3392 @classmethod 3393 def _create_nulls_first(cls, column): 3394 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression. 3395 3396 :func:`.nulls_first` is intended to modify the expression produced 3397 by :func:`.asc` or :func:`.desc`, and indicates how NULL values 3398 should be handled when they are encountered during ordering:: 3399 3400 3401 from sqlalchemy import desc, nulls_first 3402 3403 stmt = select(users_table).order_by( 3404 nulls_first(desc(users_table.c.name))) 3405 3406 The SQL expression from the above would resemble:: 3407 3408 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST 3409 3410 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically 3411 invoked from the column expression itself using 3412 :meth:`_expression.ColumnElement.nulls_first`, 3413 rather than as its standalone 3414 function version, as in:: 3415 3416 stmt = select(users_table).order_by( 3417 users_table.c.name.desc().nulls_first()) 3418 3419 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from 3420 :func:`.nullsfirst` in previous releases. 3421 The previous name remains available for backwards compatibility. 3422 3423 .. seealso:: 3424 3425 :func:`.asc` 3426 3427 :func:`.desc` 3428 3429 :func:`.nulls_last` 3430 3431 :meth:`_expression.Select.order_by` 3432 3433 """ 3434 return UnaryExpression( 3435 coercions.expect(roles.ByOfRole, column), 3436 modifier=operators.nulls_first_op, 3437 wraps_column_expression=False, 3438 ) 3439 3440 @classmethod 3441 def _create_nulls_last(cls, column): 3442 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression. 3443 3444 :func:`.nulls_last` is intended to modify the expression produced 3445 by :func:`.asc` or :func:`.desc`, and indicates how NULL values 3446 should be handled when they are encountered during ordering:: 3447 3448 3449 from sqlalchemy import desc, nulls_last 3450 3451 stmt = select(users_table).order_by( 3452 nulls_last(desc(users_table.c.name))) 3453 3454 The SQL expression from the above would resemble:: 3455 3456 SELECT id, name FROM user ORDER BY name DESC NULLS LAST 3457 3458 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically 3459 invoked from the column expression itself using 3460 :meth:`_expression.ColumnElement.nulls_last`, 3461 rather than as its standalone 3462 function version, as in:: 3463 3464 stmt = select(users_table).order_by( 3465 users_table.c.name.desc().nulls_last()) 3466 3467 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from 3468 :func:`.nullslast` in previous releases. 3469 The previous name remains available for backwards compatibility. 3470 3471 .. seealso:: 3472 3473 :func:`.asc` 3474 3475 :func:`.desc` 3476 3477 :func:`.nulls_first` 3478 3479 :meth:`_expression.Select.order_by` 3480 3481 """ 3482 return UnaryExpression( 3483 coercions.expect(roles.ByOfRole, column), 3484 modifier=operators.nulls_last_op, 3485 wraps_column_expression=False, 3486 ) 3487 3488 @classmethod 3489 def _create_desc(cls, column): 3490 """Produce a descending ``ORDER BY`` clause element. 3491 3492 e.g.:: 3493 3494 from sqlalchemy import desc 3495 3496 stmt = select(users_table).order_by(desc(users_table.c.name)) 3497 3498 will produce SQL as:: 3499 3500 SELECT id, name FROM user ORDER BY name DESC 3501 3502 The :func:`.desc` function is a standalone version of the 3503 :meth:`_expression.ColumnElement.desc` 3504 method available on all SQL expressions, 3505 e.g.:: 3506 3507 3508 stmt = select(users_table).order_by(users_table.c.name.desc()) 3509 3510 :param column: A :class:`_expression.ColumnElement` (e.g. 3511 scalar SQL expression) 3512 with which to apply the :func:`.desc` operation. 3513 3514 .. seealso:: 3515 3516 :func:`.asc` 3517 3518 :func:`.nulls_first` 3519 3520 :func:`.nulls_last` 3521 3522 :meth:`_expression.Select.order_by` 3523 3524 """ 3525 return UnaryExpression( 3526 coercions.expect(roles.ByOfRole, column), 3527 modifier=operators.desc_op, 3528 wraps_column_expression=False, 3529 ) 3530 3531 @classmethod 3532 def _create_asc(cls, column): 3533 """Produce an ascending ``ORDER BY`` clause element. 3534 3535 e.g.:: 3536 3537 from sqlalchemy import asc 3538 stmt = select(users_table).order_by(asc(users_table.c.name)) 3539 3540 will produce SQL as:: 3541 3542 SELECT id, name FROM user ORDER BY name ASC 3543 3544 The :func:`.asc` function is a standalone version of the 3545 :meth:`_expression.ColumnElement.asc` 3546 method available on all SQL expressions, 3547 e.g.:: 3548 3549 3550 stmt = select(users_table).order_by(users_table.c.name.asc()) 3551 3552 :param column: A :class:`_expression.ColumnElement` (e.g. 3553 scalar SQL expression) 3554 with which to apply the :func:`.asc` operation. 3555 3556 .. seealso:: 3557 3558 :func:`.desc` 3559 3560 :func:`.nulls_first` 3561 3562 :func:`.nulls_last` 3563 3564 :meth:`_expression.Select.order_by` 3565 3566 """ 3567 return UnaryExpression( 3568 coercions.expect(roles.ByOfRole, column), 3569 modifier=operators.asc_op, 3570 wraps_column_expression=False, 3571 ) 3572 3573 @classmethod 3574 def _create_distinct(cls, expr): 3575 """Produce an column-expression-level unary ``DISTINCT`` clause. 3576 3577 This applies the ``DISTINCT`` keyword to an individual column 3578 expression, and is typically contained within an aggregate function, 3579 as in:: 3580 3581 from sqlalchemy import distinct, func 3582 stmt = select(func.count(distinct(users_table.c.name))) 3583 3584 The above would produce an expression resembling:: 3585 3586 SELECT COUNT(DISTINCT name) FROM user 3587 3588 The :func:`.distinct` function is also available as a column-level 3589 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in:: 3590 3591 stmt = select(func.count(users_table.c.name.distinct())) 3592 3593 The :func:`.distinct` operator is different from the 3594 :meth:`_expression.Select.distinct` method of 3595 :class:`_expression.Select`, 3596 which produces a ``SELECT`` statement 3597 with ``DISTINCT`` applied to the result set as a whole, 3598 e.g. a ``SELECT DISTINCT`` expression. See that method for further 3599 information. 3600 3601 .. seealso:: 3602 3603 :meth:`_expression.ColumnElement.distinct` 3604 3605 :meth:`_expression.Select.distinct` 3606 3607 :data:`.func` 3608 3609 """ 3610 expr = coercions.expect(roles.ExpressionElementRole, expr) 3611 return UnaryExpression( 3612 expr, 3613 operator=operators.distinct_op, 3614 type_=expr.type, 3615 wraps_column_expression=False, 3616 ) 3617 3618 @property 3619 def _order_by_label_element(self): 3620 if self.modifier in (operators.desc_op, operators.asc_op): 3621 return self.element._order_by_label_element 3622 else: 3623 return None 3624 3625 @property 3626 def _from_objects(self): 3627 return self.element._from_objects 3628 3629 def _negate(self): 3630 if self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity: 3631 return UnaryExpression( 3632 self.self_group(against=operators.inv), 3633 operator=operators.inv, 3634 type_=type_api.BOOLEANTYPE, 3635 wraps_column_expression=self.wraps_column_expression, 3636 ) 3637 else: 3638 return ClauseElement._negate(self) 3639 3640 def self_group(self, against=None): 3641 if self.operator and operators.is_precedent(self.operator, against): 3642 return Grouping(self) 3643 else: 3644 return self 3645 3646 3647class CollectionAggregate(UnaryExpression): 3648 """Forms the basis for right-hand collection operator modifiers 3649 ANY and ALL. 3650 3651 The ANY and ALL keywords are available in different ways on different 3652 backends. On PostgreSQL, they only work for an ARRAY type. On 3653 MySQL, they only work for subqueries. 3654 3655 """ 3656 3657 @classmethod 3658 def _create_any(cls, expr): 3659 """Produce an ANY expression. 3660 3661 For dialects such as that of PostgreSQL, this operator applies 3662 to usage of the :class:`_types.ARRAY` datatype, for that of 3663 MySQL, it may apply to a subquery. e.g.:: 3664 3665 # renders on PostgreSQL: 3666 # '5 = ANY (somearray)' 3667 expr = 5 == any_(mytable.c.somearray) 3668 3669 # renders on MySQL: 3670 # '5 = ANY (SELECT value FROM table)' 3671 expr = 5 == any_(select(table.c.value)) 3672 3673 Comparison to NULL may work using ``None`` or :func:`_sql.null`:: 3674 3675 None == any_(mytable.c.somearray) 3676 3677 The any_() / all_() operators also feature a special "operand flipping" 3678 behavior such that if any_() / all_() are used on the left side of a 3679 comparison using a standalone operator such as ``==``, ``!=``, etc. 3680 (not including operator methods such as 3681 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped:: 3682 3683 # would render '5 = ANY (column)` 3684 any_(mytable.c.column) == 5 3685 3686 Or with ``None``, which note will not perform 3687 the usual step of rendering "IS" as is normally the case for NULL:: 3688 3689 # would render 'NULL = ANY(somearray)' 3690 any_(mytable.c.somearray) == None 3691 3692 .. versionchanged:: 1.4.26 repaired the use of any_() / all_() 3693 comparing to NULL on the right side to be flipped to the left. 3694 3695 The column-level :meth:`_sql.ColumnElement.any_` method (not to be 3696 confused with :class:`_types.ARRAY` level 3697 :meth:`_types.ARRAY.Comparator.any`) is shorthand for 3698 ``any_(col)``:: 3699 3700 5 = mytable.c.somearray.any_() 3701 3702 .. seealso:: 3703 3704 :meth:`_sql.ColumnOperators.any_` 3705 3706 :func:`_expression.all_` 3707 3708 """ 3709 3710 expr = coercions.expect(roles.ExpressionElementRole, expr) 3711 3712 expr = expr.self_group() 3713 return CollectionAggregate( 3714 expr, 3715 operator=operators.any_op, 3716 type_=type_api.NULLTYPE, 3717 wraps_column_expression=False, 3718 ) 3719 3720 @classmethod 3721 def _create_all(cls, expr): 3722 """Produce an ALL expression. 3723 3724 For dialects such as that of PostgreSQL, this operator applies 3725 to usage of the :class:`_types.ARRAY` datatype, for that of 3726 MySQL, it may apply to a subquery. e.g.:: 3727 3728 # renders on PostgreSQL: 3729 # '5 = ALL (somearray)' 3730 expr = 5 == all_(mytable.c.somearray) 3731 3732 # renders on MySQL: 3733 # '5 = ALL (SELECT value FROM table)' 3734 expr = 5 == all_(select(table.c.value)) 3735 3736 Comparison to NULL may work using ``None``:: 3737 3738 None == all_(mytable.c.somearray) 3739 3740 The any_() / all_() operators also feature a special "operand flipping" 3741 behavior such that if any_() / all_() are used on the left side of a 3742 comparison using a standalone operator such as ``==``, ``!=``, etc. 3743 (not including operator methods such as 3744 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped:: 3745 3746 # would render '5 = ALL (column)` 3747 all_(mytable.c.column) == 5 3748 3749 Or with ``None``, which note will not perform 3750 the usual step of rendering "IS" as is normally the case for NULL:: 3751 3752 # would render 'NULL = ALL(somearray)' 3753 all_(mytable.c.somearray) == None 3754 3755 .. versionchanged:: 1.4.26 repaired the use of any_() / all_() 3756 comparing to NULL on the right side to be flipped to the left. 3757 3758 The column-level :meth:`_sql.ColumnElement.all_` method (not to be 3759 confused with :class:`_types.ARRAY` level 3760 :meth:`_types.ARRAY.Comparator.all`) is shorthand for 3761 ``all_(col)``:: 3762 3763 5 == mytable.c.somearray.all_() 3764 3765 .. seealso:: 3766 3767 :meth:`_sql.ColumnOperators.all_` 3768 3769 :func:`_expression.any_` 3770 3771 """ 3772 expr = coercions.expect(roles.ExpressionElementRole, expr) 3773 expr = expr.self_group() 3774 return CollectionAggregate( 3775 expr, 3776 operator=operators.all_op, 3777 type_=type_api.NULLTYPE, 3778 wraps_column_expression=False, 3779 ) 3780 3781 # operate and reverse_operate are hardwired to 3782 # dispatch onto the type comparator directly, so that we can 3783 # ensure "reversed" behavior. 3784 def operate(self, op, *other, **kwargs): 3785 if not operators.is_comparison(op): 3786 raise exc.ArgumentError( 3787 "Only comparison operators may be used with ANY/ALL" 3788 ) 3789 kwargs["reverse"] = kwargs["_any_all_expr"] = True 3790 return self.comparator.operate(operators.mirror(op), *other, **kwargs) 3791 3792 def reverse_operate(self, op, other, **kwargs): 3793 # comparison operators should never call reverse_operate 3794 assert not operators.is_comparison(op) 3795 raise exc.ArgumentError( 3796 "Only comparison operators may be used with ANY/ALL" 3797 ) 3798 3799 3800class AsBoolean(WrapsColumnExpression, UnaryExpression): 3801 inherit_cache = True 3802 3803 def __init__(self, element, operator, negate): 3804 self.element = element 3805 self.type = type_api.BOOLEANTYPE 3806 self.operator = operator 3807 self.negate = negate 3808 self.modifier = None 3809 self.wraps_column_expression = True 3810 self._is_implicitly_boolean = element._is_implicitly_boolean 3811 3812 @property 3813 def wrapped_column_expression(self): 3814 return self.element 3815 3816 def self_group(self, against=None): 3817 return self 3818 3819 def _negate(self): 3820 if isinstance(self.element, (True_, False_)): 3821 return self.element._negate() 3822 else: 3823 return AsBoolean(self.element, self.negate, self.operator) 3824 3825 3826class BinaryExpression(ColumnElement): 3827 """Represent an expression that is ``LEFT <operator> RIGHT``. 3828 3829 A :class:`.BinaryExpression` is generated automatically 3830 whenever two column expressions are used in a Python binary expression:: 3831 3832 >>> from sqlalchemy.sql import column 3833 >>> column('a') + column('b') 3834 <sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0> 3835 >>> print(column('a') + column('b')) 3836 a + b 3837 3838 """ 3839 3840 __visit_name__ = "binary" 3841 3842 _traverse_internals = [ 3843 ("left", InternalTraversal.dp_clauseelement), 3844 ("right", InternalTraversal.dp_clauseelement), 3845 ("operator", InternalTraversal.dp_operator), 3846 ("negate", InternalTraversal.dp_operator), 3847 ("modifiers", InternalTraversal.dp_plain_dict), 3848 ( 3849 "type", 3850 InternalTraversal.dp_type, 3851 ), # affects JSON CAST operators 3852 ] 3853 3854 _is_implicitly_boolean = True 3855 """Indicates that any database will know this is a boolean expression 3856 even if the database does not have an explicit boolean datatype. 3857 3858 """ 3859 3860 def __init__( 3861 self, left, right, operator, type_=None, negate=None, modifiers=None 3862 ): 3863 # allow compatibility with libraries that 3864 # refer to BinaryExpression directly and pass strings 3865 if isinstance(operator, util.string_types): 3866 operator = operators.custom_op(operator) 3867 self._orig = (left.__hash__(), right.__hash__()) 3868 self._propagate_attrs = left._propagate_attrs or right._propagate_attrs 3869 self.left = left.self_group(against=operator) 3870 self.right = right.self_group(against=operator) 3871 self.operator = operator 3872 self.type = type_api.to_instance(type_) 3873 self.negate = negate 3874 self._is_implicitly_boolean = operators.is_boolean(operator) 3875 3876 if modifiers is None: 3877 self.modifiers = {} 3878 else: 3879 self.modifiers = modifiers 3880 3881 def __bool__(self): 3882 if self.operator in (operator.eq, operator.ne): 3883 return self.operator(*self._orig) 3884 else: 3885 raise TypeError("Boolean value of this clause is not defined") 3886 3887 __nonzero__ = __bool__ 3888 3889 @property 3890 def is_comparison(self): 3891 return operators.is_comparison(self.operator) 3892 3893 @property 3894 def _from_objects(self): 3895 return self.left._from_objects + self.right._from_objects 3896 3897 def self_group(self, against=None): 3898 3899 if operators.is_precedent(self.operator, against): 3900 return Grouping(self) 3901 else: 3902 return self 3903 3904 def _negate(self): 3905 if self.negate is not None: 3906 return BinaryExpression( 3907 self.left, 3908 self.right._negate_in_binary(self.negate, self.operator), 3909 self.negate, 3910 negate=self.operator, 3911 type_=self.type, 3912 modifiers=self.modifiers, 3913 ) 3914 else: 3915 return super(BinaryExpression, self)._negate() 3916 3917 3918class Slice(ColumnElement): 3919 """Represent SQL for a Python array-slice object. 3920 3921 This is not a specific SQL construct at this level, but 3922 may be interpreted by specific dialects, e.g. PostgreSQL. 3923 3924 """ 3925 3926 __visit_name__ = "slice" 3927 3928 _traverse_internals = [ 3929 ("start", InternalTraversal.dp_clauseelement), 3930 ("stop", InternalTraversal.dp_clauseelement), 3931 ("step", InternalTraversal.dp_clauseelement), 3932 ] 3933 3934 def __init__(self, start, stop, step, _name=None): 3935 self.start = coercions.expect( 3936 roles.ExpressionElementRole, 3937 start, 3938 name=_name, 3939 type_=type_api.INTEGERTYPE, 3940 ) 3941 self.stop = coercions.expect( 3942 roles.ExpressionElementRole, 3943 stop, 3944 name=_name, 3945 type_=type_api.INTEGERTYPE, 3946 ) 3947 self.step = coercions.expect( 3948 roles.ExpressionElementRole, 3949 step, 3950 name=_name, 3951 type_=type_api.INTEGERTYPE, 3952 ) 3953 self.type = type_api.NULLTYPE 3954 3955 def self_group(self, against=None): 3956 assert against is operator.getitem 3957 return self 3958 3959 3960class IndexExpression(BinaryExpression): 3961 """Represent the class of expressions that are like an "index" 3962 operation.""" 3963 3964 pass 3965 3966 3967class GroupedElement(ClauseElement): 3968 """Represent any parenthesized expression""" 3969 3970 __visit_name__ = "grouping" 3971 3972 def self_group(self, against=None): 3973 return self 3974 3975 def _ungroup(self): 3976 return self.element._ungroup() 3977 3978 3979class Grouping(GroupedElement, ColumnElement): 3980 """Represent a grouping within a column expression""" 3981 3982 _traverse_internals = [ 3983 ("element", InternalTraversal.dp_clauseelement), 3984 ("type", InternalTraversal.dp_type), 3985 ] 3986 3987 def __init__(self, element): 3988 self.element = element 3989 self.type = getattr(element, "type", type_api.NULLTYPE) 3990 3991 def _with_binary_element_type(self, type_): 3992 return self.__class__(self.element._with_binary_element_type(type_)) 3993 3994 @util.memoized_property 3995 def _is_implicitly_boolean(self): 3996 return self.element._is_implicitly_boolean 3997 3998 @property 3999 def _tq_label(self): 4000 return ( 4001 getattr(self.element, "_tq_label", None) or self._anon_name_label 4002 ) 4003 4004 @property 4005 def _proxies(self): 4006 if isinstance(self.element, ColumnElement): 4007 return [self.element] 4008 else: 4009 return [] 4010 4011 @property 4012 def _from_objects(self): 4013 return self.element._from_objects 4014 4015 def __getattr__(self, attr): 4016 return getattr(self.element, attr) 4017 4018 def __getstate__(self): 4019 return {"element": self.element, "type": self.type} 4020 4021 def __setstate__(self, state): 4022 self.element = state["element"] 4023 self.type = state["type"] 4024 4025 4026RANGE_UNBOUNDED = util.symbol("RANGE_UNBOUNDED") 4027RANGE_CURRENT = util.symbol("RANGE_CURRENT") 4028 4029 4030class Over(ColumnElement): 4031 """Represent an OVER clause. 4032 4033 This is a special operator against a so-called 4034 "window" function, as well as any aggregate function, 4035 which produces results relative to the result set 4036 itself. Most modern SQL backends now support window functions. 4037 4038 """ 4039 4040 __visit_name__ = "over" 4041 4042 _traverse_internals = [ 4043 ("element", InternalTraversal.dp_clauseelement), 4044 ("order_by", InternalTraversal.dp_clauseelement), 4045 ("partition_by", InternalTraversal.dp_clauseelement), 4046 ("range_", InternalTraversal.dp_plain_obj), 4047 ("rows", InternalTraversal.dp_plain_obj), 4048 ] 4049 4050 order_by = None 4051 partition_by = None 4052 4053 element = None 4054 """The underlying expression object to which this :class:`.Over` 4055 object refers towards.""" 4056 4057 def __init__( 4058 self, element, partition_by=None, order_by=None, range_=None, rows=None 4059 ): 4060 r"""Produce an :class:`.Over` object against a function. 4061 4062 Used against aggregate or so-called "window" functions, 4063 for database backends that support window functions. 4064 4065 :func:`_expression.over` is usually called using 4066 the :meth:`.FunctionElement.over` method, e.g.:: 4067 4068 func.row_number().over(order_by=mytable.c.some_column) 4069 4070 Would produce:: 4071 4072 ROW_NUMBER() OVER(ORDER BY some_column) 4073 4074 Ranges are also possible using the :paramref:`.expression.over.range_` 4075 and :paramref:`.expression.over.rows` parameters. These 4076 mutually-exclusive parameters each accept a 2-tuple, which contains 4077 a combination of integers and None:: 4078 4079 func.row_number().over( 4080 order_by=my_table.c.some_column, range_=(None, 0)) 4081 4082 The above would produce:: 4083 4084 ROW_NUMBER() OVER(ORDER BY some_column 4085 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 4086 4087 A value of ``None`` indicates "unbounded", a 4088 value of zero indicates "current row", and negative / positive 4089 integers indicate "preceding" and "following": 4090 4091 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING:: 4092 4093 func.row_number().over(order_by='x', range_=(-5, 10)) 4094 4095 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:: 4096 4097 func.row_number().over(order_by='x', rows=(None, 0)) 4098 4099 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING:: 4100 4101 func.row_number().over(order_by='x', range_=(-2, None)) 4102 4103 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING:: 4104 4105 func.row_number().over(order_by='x', range_=(1, 3)) 4106 4107 .. versionadded:: 1.1 support for RANGE / ROWS within a window 4108 4109 4110 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`, 4111 or other compatible construct. 4112 :param partition_by: a column element or string, or a list 4113 of such, that will be used as the PARTITION BY clause 4114 of the OVER construct. 4115 :param order_by: a column element or string, or a list 4116 of such, that will be used as the ORDER BY clause 4117 of the OVER construct. 4118 :param range\_: optional range clause for the window. This is a 4119 tuple value which can contain integer values or ``None``, 4120 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause. 4121 4122 .. versionadded:: 1.1 4123 4124 :param rows: optional rows clause for the window. This is a tuple 4125 value which can contain integer values or None, and will render 4126 a ROWS BETWEEN PRECEDING / FOLLOWING clause. 4127 4128 .. versionadded:: 1.1 4129 4130 This function is also available from the :data:`~.expression.func` 4131 construct itself via the :meth:`.FunctionElement.over` method. 4132 4133 .. seealso:: 4134 4135 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial` 4136 4137 :data:`.expression.func` 4138 4139 :func:`_expression.within_group` 4140 4141 """ 4142 self.element = element 4143 if order_by is not None: 4144 self.order_by = ClauseList( 4145 *util.to_list(order_by), _literal_as_text_role=roles.ByOfRole 4146 ) 4147 if partition_by is not None: 4148 self.partition_by = ClauseList( 4149 *util.to_list(partition_by), 4150 _literal_as_text_role=roles.ByOfRole 4151 ) 4152 4153 if range_: 4154 self.range_ = self._interpret_range(range_) 4155 if rows: 4156 raise exc.ArgumentError( 4157 "'range_' and 'rows' are mutually exclusive" 4158 ) 4159 else: 4160 self.rows = None 4161 elif rows: 4162 self.rows = self._interpret_range(rows) 4163 self.range_ = None 4164 else: 4165 self.rows = self.range_ = None 4166 4167 def __reduce__(self): 4168 return self.__class__, ( 4169 self.element, 4170 self.partition_by, 4171 self.order_by, 4172 self.range_, 4173 self.rows, 4174 ) 4175 4176 def _interpret_range(self, range_): 4177 if not isinstance(range_, tuple) or len(range_) != 2: 4178 raise exc.ArgumentError("2-tuple expected for range/rows") 4179 4180 if range_[0] is None: 4181 lower = RANGE_UNBOUNDED 4182 else: 4183 try: 4184 lower = int(range_[0]) 4185 except ValueError as err: 4186 util.raise_( 4187 exc.ArgumentError( 4188 "Integer or None expected for range value" 4189 ), 4190 replace_context=err, 4191 ) 4192 else: 4193 if lower == 0: 4194 lower = RANGE_CURRENT 4195 4196 if range_[1] is None: 4197 upper = RANGE_UNBOUNDED 4198 else: 4199 try: 4200 upper = int(range_[1]) 4201 except ValueError as err: 4202 util.raise_( 4203 exc.ArgumentError( 4204 "Integer or None expected for range value" 4205 ), 4206 replace_context=err, 4207 ) 4208 else: 4209 if upper == 0: 4210 upper = RANGE_CURRENT 4211 4212 return lower, upper 4213 4214 @util.memoized_property 4215 def type(self): 4216 return self.element.type 4217 4218 @property 4219 def _from_objects(self): 4220 return list( 4221 itertools.chain( 4222 *[ 4223 c._from_objects 4224 for c in (self.element, self.partition_by, self.order_by) 4225 if c is not None 4226 ] 4227 ) 4228 ) 4229 4230 4231class WithinGroup(ColumnElement): 4232 """Represent a WITHIN GROUP (ORDER BY) clause. 4233 4234 This is a special operator against so-called 4235 "ordered set aggregate" and "hypothetical 4236 set aggregate" functions, including ``percentile_cont()``, 4237 ``rank()``, ``dense_rank()``, etc. 4238 4239 It's supported only by certain database backends, such as PostgreSQL, 4240 Oracle and MS SQL Server. 4241 4242 The :class:`.WithinGroup` construct extracts its type from the 4243 method :meth:`.FunctionElement.within_group_type`. If this returns 4244 ``None``, the function's ``.type`` is used. 4245 4246 """ 4247 4248 __visit_name__ = "withingroup" 4249 4250 _traverse_internals = [ 4251 ("element", InternalTraversal.dp_clauseelement), 4252 ("order_by", InternalTraversal.dp_clauseelement), 4253 ] 4254 4255 order_by = None 4256 4257 def __init__(self, element, *order_by): 4258 r"""Produce a :class:`.WithinGroup` object against a function. 4259 4260 Used against so-called "ordered set aggregate" and "hypothetical 4261 set aggregate" functions, including :class:`.percentile_cont`, 4262 :class:`.rank`, :class:`.dense_rank`, etc. 4263 4264 :func:`_expression.within_group` is usually called using 4265 the :meth:`.FunctionElement.within_group` method, e.g.:: 4266 4267 from sqlalchemy import within_group 4268 stmt = select( 4269 department.c.id, 4270 func.percentile_cont(0.5).within_group( 4271 department.c.salary.desc() 4272 ) 4273 ) 4274 4275 The above statement would produce SQL similar to 4276 ``SELECT department.id, percentile_cont(0.5) 4277 WITHIN GROUP (ORDER BY department.salary DESC)``. 4278 4279 :param element: a :class:`.FunctionElement` construct, typically 4280 generated by :data:`~.expression.func`. 4281 :param \*order_by: one or more column elements that will be used 4282 as the ORDER BY clause of the WITHIN GROUP construct. 4283 4284 .. versionadded:: 1.1 4285 4286 .. seealso:: 4287 4288 :ref:`tutorial_functions_within_group` - in the 4289 :ref:`unified_tutorial` 4290 4291 :data:`.expression.func` 4292 4293 :func:`_expression.over` 4294 4295 """ 4296 self.element = element 4297 if order_by is not None: 4298 self.order_by = ClauseList( 4299 *util.to_list(order_by), _literal_as_text_role=roles.ByOfRole 4300 ) 4301 4302 def __reduce__(self): 4303 return self.__class__, (self.element,) + tuple(self.order_by) 4304 4305 def over(self, partition_by=None, order_by=None, range_=None, rows=None): 4306 """Produce an OVER clause against this :class:`.WithinGroup` 4307 construct. 4308 4309 This function has the same signature as that of 4310 :meth:`.FunctionElement.over`. 4311 4312 """ 4313 return Over( 4314 self, 4315 partition_by=partition_by, 4316 order_by=order_by, 4317 range_=range_, 4318 rows=rows, 4319 ) 4320 4321 @util.memoized_property 4322 def type(self): 4323 wgt = self.element.within_group_type(self) 4324 if wgt is not None: 4325 return wgt 4326 else: 4327 return self.element.type 4328 4329 @property 4330 def _from_objects(self): 4331 return list( 4332 itertools.chain( 4333 *[ 4334 c._from_objects 4335 for c in (self.element, self.order_by) 4336 if c is not None 4337 ] 4338 ) 4339 ) 4340 4341 4342class FunctionFilter(ColumnElement): 4343 """Represent a function FILTER clause. 4344 4345 This is a special operator against aggregate and window functions, 4346 which controls which rows are passed to it. 4347 It's supported only by certain database backends. 4348 4349 Invocation of :class:`.FunctionFilter` is via 4350 :meth:`.FunctionElement.filter`:: 4351 4352 func.count(1).filter(True) 4353 4354 .. versionadded:: 1.0.0 4355 4356 .. seealso:: 4357 4358 :meth:`.FunctionElement.filter` 4359 4360 """ 4361 4362 __visit_name__ = "funcfilter" 4363 4364 _traverse_internals = [ 4365 ("func", InternalTraversal.dp_clauseelement), 4366 ("criterion", InternalTraversal.dp_clauseelement), 4367 ] 4368 4369 criterion = None 4370 4371 def __init__(self, func, *criterion): 4372 """Produce a :class:`.FunctionFilter` object against a function. 4373 4374 Used against aggregate and window functions, 4375 for database backends that support the "FILTER" clause. 4376 4377 E.g.:: 4378 4379 from sqlalchemy import funcfilter 4380 funcfilter(func.count(1), MyClass.name == 'some name') 4381 4382 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')". 4383 4384 This function is also available from the :data:`~.expression.func` 4385 construct itself via the :meth:`.FunctionElement.filter` method. 4386 4387 .. versionadded:: 1.0.0 4388 4389 .. seealso:: 4390 4391 :ref:`tutorial_functions_within_group` - in the 4392 :ref:`unified_tutorial` 4393 4394 :meth:`.FunctionElement.filter` 4395 4396 """ 4397 self.func = func 4398 self.filter(*criterion) 4399 4400 def filter(self, *criterion): 4401 """Produce an additional FILTER against the function. 4402 4403 This method adds additional criteria to the initial criteria 4404 set up by :meth:`.FunctionElement.filter`. 4405 4406 Multiple criteria are joined together at SQL render time 4407 via ``AND``. 4408 4409 4410 """ 4411 4412 for criterion in list(criterion): 4413 criterion = coercions.expect(roles.WhereHavingRole, criterion) 4414 4415 if self.criterion is not None: 4416 self.criterion = self.criterion & criterion 4417 else: 4418 self.criterion = criterion 4419 4420 return self 4421 4422 def over(self, partition_by=None, order_by=None, range_=None, rows=None): 4423 """Produce an OVER clause against this filtered function. 4424 4425 Used against aggregate or so-called "window" functions, 4426 for database backends that support window functions. 4427 4428 The expression:: 4429 4430 func.rank().filter(MyClass.y > 5).over(order_by='x') 4431 4432 is shorthand for:: 4433 4434 from sqlalchemy import over, funcfilter 4435 over(funcfilter(func.rank(), MyClass.y > 5), order_by='x') 4436 4437 See :func:`_expression.over` for a full description. 4438 4439 """ 4440 return Over( 4441 self, 4442 partition_by=partition_by, 4443 order_by=order_by, 4444 range_=range_, 4445 rows=rows, 4446 ) 4447 4448 def self_group(self, against=None): 4449 if operators.is_precedent(operators.filter_op, against): 4450 return Grouping(self) 4451 else: 4452 return self 4453 4454 @util.memoized_property 4455 def type(self): 4456 return self.func.type 4457 4458 @property 4459 def _from_objects(self): 4460 return list( 4461 itertools.chain( 4462 *[ 4463 c._from_objects 4464 for c in (self.func, self.criterion) 4465 if c is not None 4466 ] 4467 ) 4468 ) 4469 4470 4471class Label(roles.LabeledColumnExprRole, ColumnElement): 4472 """Represents a column label (AS). 4473 4474 Represent a label, as typically applied to any column-level 4475 element using the ``AS`` sql keyword. 4476 4477 """ 4478 4479 __visit_name__ = "label" 4480 4481 _traverse_internals = [ 4482 ("name", InternalTraversal.dp_anon_name), 4483 ("_type", InternalTraversal.dp_type), 4484 ("_element", InternalTraversal.dp_clauseelement), 4485 ] 4486 4487 def __init__(self, name, element, type_=None): 4488 """Return a :class:`Label` object for the 4489 given :class:`_expression.ColumnElement`. 4490 4491 A label changes the name of an element in the columns clause of a 4492 ``SELECT`` statement, typically via the ``AS`` SQL keyword. 4493 4494 This functionality is more conveniently available via the 4495 :meth:`_expression.ColumnElement.label` method on 4496 :class:`_expression.ColumnElement`. 4497 4498 :param name: label name 4499 4500 :param obj: a :class:`_expression.ColumnElement`. 4501 4502 """ 4503 4504 orig_element = element 4505 element = coercions.expect( 4506 roles.ExpressionElementRole, 4507 element, 4508 apply_propagate_attrs=self, 4509 ) 4510 while isinstance(element, Label): 4511 # TODO: this is only covered in test_text.py, but nothing 4512 # fails if it's removed. determine rationale 4513 element = element.element 4514 4515 if name: 4516 self.name = name 4517 else: 4518 self.name = _anonymous_label.safe_construct( 4519 id(self), getattr(element, "name", "anon") 4520 ) 4521 if isinstance(orig_element, Label): 4522 # TODO: no coverage for this block, again would be in 4523 # test_text.py where the resolve_label concept is important 4524 self._resolve_label = orig_element._label 4525 4526 self.key = self._tq_label = self._tq_key_label = self.name 4527 self._element = element 4528 self._type = type_ 4529 self._proxies = [element] 4530 4531 def __reduce__(self): 4532 return self.__class__, (self.name, self._element, self._type) 4533 4534 @util.memoized_property 4535 def _is_implicitly_boolean(self): 4536 return self.element._is_implicitly_boolean 4537 4538 @HasMemoized.memoized_attribute 4539 def _allow_label_resolve(self): 4540 return self.element._allow_label_resolve 4541 4542 @property 4543 def _order_by_label_element(self): 4544 return self 4545 4546 @util.memoized_property 4547 def type(self): 4548 return type_api.to_instance( 4549 self._type or getattr(self._element, "type", None) 4550 ) 4551 4552 @HasMemoized.memoized_attribute 4553 def element(self): 4554 return self._element.self_group(against=operators.as_) 4555 4556 def self_group(self, against=None): 4557 return self._apply_to_inner(self._element.self_group, against=against) 4558 4559 def _negate(self): 4560 return self._apply_to_inner(self._element._negate) 4561 4562 def _apply_to_inner(self, fn, *arg, **kw): 4563 sub_element = fn(*arg, **kw) 4564 if sub_element is not self._element: 4565 return Label(self.name, sub_element, type_=self._type) 4566 else: 4567 return self 4568 4569 @property 4570 def primary_key(self): 4571 return self.element.primary_key 4572 4573 @property 4574 def foreign_keys(self): 4575 return self.element.foreign_keys 4576 4577 def _copy_internals(self, clone=_clone, anonymize_labels=False, **kw): 4578 self._reset_memoizations() 4579 self._element = clone(self._element, **kw) 4580 if anonymize_labels: 4581 self.name = _anonymous_label.safe_construct( 4582 id(self), getattr(self.element, "name", "anon") 4583 ) 4584 self.key = self._tq_label = self._tq_key_label = self.name 4585 4586 @property 4587 def _from_objects(self): 4588 return self.element._from_objects 4589 4590 def _make_proxy(self, selectable, name=None, **kw): 4591 name = self.name if not name else name 4592 4593 key, e = self.element._make_proxy( 4594 selectable, 4595 name=name, 4596 disallow_is_literal=True, 4597 name_is_truncatable=isinstance(name, _truncated_label), 4598 ) 4599 4600 # there was a note here to remove this assertion, which was here 4601 # to determine if we later could support a use case where 4602 # the key and name of a label are separate. But I don't know what 4603 # that case was. For now, this is an unexpected case that occurs 4604 # when a label name conflicts with other columns and select() 4605 # is attempting to disambiguate an explicit label, which is not what 4606 # the user would want. See issue #6090. 4607 if key != self.name: 4608 raise exc.InvalidRequestError( 4609 "Label name %s is being renamed to an anonymous label due " 4610 "to disambiguation " 4611 "which is not supported right now. Please use unique names " 4612 "for explicit labels." % (self.name) 4613 ) 4614 4615 e._propagate_attrs = selectable._propagate_attrs 4616 e._proxies.append(self) 4617 if self._type is not None: 4618 e.type = self._type 4619 4620 return self.key, e 4621 4622 4623class NamedColumn(ColumnElement): 4624 is_literal = False 4625 table = None 4626 4627 def _compare_name_for_result(self, other): 4628 return (hasattr(other, "name") and self.name == other.name) or ( 4629 hasattr(other, "_label") and self._label == other._label 4630 ) 4631 4632 @util.memoized_property 4633 def description(self): 4634 if util.py3k: 4635 return self.name 4636 else: 4637 return self.name.encode("ascii", "backslashreplace") 4638 4639 @HasMemoized.memoized_attribute 4640 def _tq_key_label(self): 4641 """table qualified label based on column key. 4642 4643 for table-bound columns this is <tablename>_<column key/proxy key>; 4644 4645 all other expressions it resolves to key/proxy key. 4646 4647 """ 4648 proxy_key = self._proxy_key 4649 if proxy_key and proxy_key != self.name: 4650 return self._gen_tq_label(proxy_key) 4651 else: 4652 return self._tq_label 4653 4654 @HasMemoized.memoized_attribute 4655 def _tq_label(self): 4656 """table qualified label based on column name. 4657 4658 for table-bound columns this is <tablename>_<columnname>; all other 4659 expressions it resolves to .name. 4660 4661 """ 4662 return self._gen_tq_label(self.name) 4663 4664 @HasMemoized.memoized_attribute 4665 def _render_label_in_columns_clause(self): 4666 return True 4667 4668 @HasMemoized.memoized_attribute 4669 def _non_anon_label(self): 4670 return self.name 4671 4672 def _gen_tq_label(self, name, dedupe_on_key=True): 4673 return name 4674 4675 def _bind_param(self, operator, obj, type_=None, expanding=False): 4676 return BindParameter( 4677 self.key, 4678 obj, 4679 _compared_to_operator=operator, 4680 _compared_to_type=self.type, 4681 type_=type_, 4682 unique=True, 4683 expanding=expanding, 4684 ) 4685 4686 def _make_proxy( 4687 self, 4688 selectable, 4689 name=None, 4690 name_is_truncatable=False, 4691 disallow_is_literal=False, 4692 **kw 4693 ): 4694 c = ColumnClause( 4695 coercions.expect(roles.TruncatedLabelRole, name or self.name) 4696 if name_is_truncatable 4697 else (name or self.name), 4698 type_=self.type, 4699 _selectable=selectable, 4700 is_literal=False, 4701 ) 4702 c._propagate_attrs = selectable._propagate_attrs 4703 if name is None: 4704 c.key = self.key 4705 c._proxies = [self] 4706 if selectable._is_clone_of is not None: 4707 c._is_clone_of = selectable._is_clone_of.columns.get(c.key) 4708 return c.key, c 4709 4710 4711class ColumnClause( 4712 roles.DDLReferredColumnRole, 4713 roles.LabeledColumnExprRole, 4714 roles.StrAsPlainColumnRole, 4715 Immutable, 4716 NamedColumn, 4717): 4718 """Represents a column expression from any textual string. 4719 4720 The :class:`.ColumnClause`, a lightweight analogue to the 4721 :class:`_schema.Column` class, is typically invoked using the 4722 :func:`_expression.column` function, as in:: 4723 4724 from sqlalchemy import column 4725 4726 id, name = column("id"), column("name") 4727 stmt = select(id, name).select_from("user") 4728 4729 The above statement would produce SQL like:: 4730 4731 SELECT id, name FROM user 4732 4733 :class:`.ColumnClause` is the immediate superclass of the schema-specific 4734 :class:`_schema.Column` object. While the :class:`_schema.Column` 4735 class has all the 4736 same capabilities as :class:`.ColumnClause`, the :class:`.ColumnClause` 4737 class is usable by itself in those cases where behavioral requirements 4738 are limited to simple SQL expression generation. The object has none of 4739 the associations with schema-level metadata or with execution-time 4740 behavior that :class:`_schema.Column` does, 4741 so in that sense is a "lightweight" 4742 version of :class:`_schema.Column`. 4743 4744 Full details on :class:`.ColumnClause` usage is at 4745 :func:`_expression.column`. 4746 4747 .. seealso:: 4748 4749 :func:`_expression.column` 4750 4751 :class:`_schema.Column` 4752 4753 """ 4754 4755 table = None 4756 is_literal = False 4757 4758 __visit_name__ = "column" 4759 4760 _traverse_internals = [ 4761 ("name", InternalTraversal.dp_anon_name), 4762 ("type", InternalTraversal.dp_type), 4763 ("table", InternalTraversal.dp_clauseelement), 4764 ("is_literal", InternalTraversal.dp_boolean), 4765 ] 4766 4767 onupdate = default = server_default = server_onupdate = None 4768 4769 _is_multiparam_column = False 4770 4771 def __init__(self, text, type_=None, is_literal=False, _selectable=None): 4772 """Produce a :class:`.ColumnClause` object. 4773 4774 The :class:`.ColumnClause` is a lightweight analogue to the 4775 :class:`_schema.Column` class. The :func:`_expression.column` 4776 function can 4777 be invoked with just a name alone, as in:: 4778 4779 from sqlalchemy import column 4780 4781 id, name = column("id"), column("name") 4782 stmt = select(id, name).select_from("user") 4783 4784 The above statement would produce SQL like:: 4785 4786 SELECT id, name FROM user 4787 4788 Once constructed, :func:`_expression.column` 4789 may be used like any other SQL 4790 expression element such as within :func:`_expression.select` 4791 constructs:: 4792 4793 from sqlalchemy.sql import column 4794 4795 id, name = column("id"), column("name") 4796 stmt = select(id, name).select_from("user") 4797 4798 The text handled by :func:`_expression.column` 4799 is assumed to be handled 4800 like the name of a database column; if the string contains mixed case, 4801 special characters, or matches a known reserved word on the target 4802 backend, the column expression will render using the quoting 4803 behavior determined by the backend. To produce a textual SQL 4804 expression that is rendered exactly without any quoting, 4805 use :func:`_expression.literal_column` instead, 4806 or pass ``True`` as the 4807 value of :paramref:`_expression.column.is_literal`. Additionally, 4808 full SQL 4809 statements are best handled using the :func:`_expression.text` 4810 construct. 4811 4812 :func:`_expression.column` can be used in a table-like 4813 fashion by combining it with the :func:`.table` function 4814 (which is the lightweight analogue to :class:`_schema.Table` 4815 ) to produce 4816 a working table construct with minimal boilerplate:: 4817 4818 from sqlalchemy import table, column, select 4819 4820 user = table("user", 4821 column("id"), 4822 column("name"), 4823 column("description"), 4824 ) 4825 4826 stmt = select(user.c.description).where(user.c.name == 'wendy') 4827 4828 A :func:`_expression.column` / :func:`.table` 4829 construct like that illustrated 4830 above can be created in an 4831 ad-hoc fashion and is not associated with any 4832 :class:`_schema.MetaData`, DDL, or events, unlike its 4833 :class:`_schema.Table` counterpart. 4834 4835 .. versionchanged:: 1.0.0 :func:`_expression.column` can now 4836 be imported from the plain ``sqlalchemy`` namespace like any 4837 other SQL element. 4838 4839 :param text: the text of the element. 4840 4841 :param type: :class:`_types.TypeEngine` object which can associate 4842 this :class:`.ColumnClause` with a type. 4843 4844 :param is_literal: if True, the :class:`.ColumnClause` is assumed to 4845 be an exact expression that will be delivered to the output with no 4846 quoting rules applied regardless of case sensitive settings. the 4847 :func:`_expression.literal_column()` function essentially invokes 4848 :func:`_expression.column` while passing ``is_literal=True``. 4849 4850 .. seealso:: 4851 4852 :class:`_schema.Column` 4853 4854 :func:`_expression.literal_column` 4855 4856 :func:`.table` 4857 4858 :func:`_expression.text` 4859 4860 :ref:`sqlexpression_literal_column` 4861 4862 """ 4863 self.key = self.name = text 4864 self.table = _selectable 4865 self.type = type_api.to_instance(type_) 4866 self.is_literal = is_literal 4867 4868 def get_children(self, column_tables=False, **kw): 4869 # override base get_children() to not return the Table 4870 # or selectable that is parent to this column. Traversals 4871 # expect the columns of tables and subqueries to be leaf nodes. 4872 return [] 4873 4874 @property 4875 def entity_namespace(self): 4876 if self.table is not None: 4877 return self.table.entity_namespace 4878 else: 4879 return super(ColumnClause, self).entity_namespace 4880 4881 @HasMemoized.memoized_attribute 4882 def _from_objects(self): 4883 t = self.table 4884 if t is not None: 4885 return [t] 4886 else: 4887 return [] 4888 4889 @HasMemoized.memoized_attribute 4890 def _render_label_in_columns_clause(self): 4891 return self.table is not None 4892 4893 @property 4894 def _ddl_label(self): 4895 return self._gen_tq_label(self.name, dedupe_on_key=False) 4896 4897 def _compare_name_for_result(self, other): 4898 if ( 4899 self.is_literal 4900 or self.table is None 4901 or self.table._is_textual 4902 or not hasattr(other, "proxy_set") 4903 or ( 4904 isinstance(other, ColumnClause) 4905 and ( 4906 other.is_literal 4907 or other.table is None 4908 or other.table._is_textual 4909 ) 4910 ) 4911 ): 4912 return (hasattr(other, "name") and self.name == other.name) or ( 4913 hasattr(other, "_tq_label") 4914 and self._tq_label == other._tq_label 4915 ) 4916 else: 4917 return other.proxy_set.intersection(self.proxy_set) 4918 4919 def _gen_tq_label(self, name, dedupe_on_key=True): 4920 """generate table-qualified label 4921 4922 for a table-bound column this is <tablename>_<columnname>. 4923 4924 used primarily for LABEL_STYLE_TABLENAME_PLUS_COL 4925 as well as the .columns collection on a Join object. 4926 4927 """ 4928 t = self.table 4929 if self.is_literal: 4930 return None 4931 elif t is not None and t.named_with_column: 4932 if getattr(t, "schema", None): 4933 label = t.schema.replace(".", "_") + "_" + t.name + "_" + name 4934 else: 4935 label = t.name + "_" + name 4936 4937 # propagate name quoting rules for labels. 4938 if getattr(name, "quote", None) is not None: 4939 if isinstance(label, quoted_name): 4940 label.quote = name.quote 4941 else: 4942 label = quoted_name(label, name.quote) 4943 elif getattr(t.name, "quote", None) is not None: 4944 # can't get this situation to occur, so let's 4945 # assert false on it for now 4946 assert not isinstance(label, quoted_name) 4947 label = quoted_name(label, t.name.quote) 4948 4949 if dedupe_on_key: 4950 # ensure the label name doesn't conflict with that of an 4951 # existing column. note that this implies that any Column 4952 # must **not** set up its _label before its parent table has 4953 # all of its other Column objects set up. There are several 4954 # tables in the test suite which will fail otherwise; example: 4955 # table "owner" has columns "name" and "owner_name". Therefore 4956 # column owner.name cannot use the label "owner_name", it has 4957 # to be "owner_name_1". 4958 if label in t.c: 4959 _label = label 4960 counter = 1 4961 while _label in t.c: 4962 _label = label + "_" + str(counter) 4963 counter += 1 4964 label = _label 4965 4966 return coercions.expect(roles.TruncatedLabelRole, label) 4967 4968 else: 4969 return name 4970 4971 def _make_proxy( 4972 self, 4973 selectable, 4974 name=None, 4975 name_is_truncatable=False, 4976 disallow_is_literal=False, 4977 **kw 4978 ): 4979 # the "is_literal" flag normally should never be propagated; a proxied 4980 # column is always a SQL identifier and never the actual expression 4981 # being evaluated. however, there is a case where the "is_literal" flag 4982 # might be used to allow the given identifier to have a fixed quoting 4983 # pattern already, so maintain the flag for the proxy unless a 4984 # :class:`.Label` object is creating the proxy. See [ticket:4730]. 4985 is_literal = ( 4986 not disallow_is_literal 4987 and self.is_literal 4988 and ( 4989 # note this does not accommodate for quoted_name differences 4990 # right now 4991 name is None 4992 or name == self.name 4993 ) 4994 ) 4995 c = self._constructor( 4996 coercions.expect(roles.TruncatedLabelRole, name or self.name) 4997 if name_is_truncatable 4998 else (name or self.name), 4999 type_=self.type, 5000 _selectable=selectable, 5001 is_literal=is_literal, 5002 ) 5003 c._propagate_attrs = selectable._propagate_attrs 5004 if name is None: 5005 c.key = self.key 5006 c._proxies = [self] 5007 if selectable._is_clone_of is not None: 5008 c._is_clone_of = selectable._is_clone_of.columns.get(c.key) 5009 return c.key, c 5010 5011 5012class TableValuedColumn(NamedColumn): 5013 __visit_name__ = "table_valued_column" 5014 5015 _traverse_internals = [ 5016 ("name", InternalTraversal.dp_anon_name), 5017 ("type", InternalTraversal.dp_type), 5018 ("scalar_alias", InternalTraversal.dp_clauseelement), 5019 ] 5020 5021 def __init__(self, scalar_alias, type_): 5022 self.scalar_alias = scalar_alias 5023 self.key = self.name = scalar_alias.name 5024 self.type = type_ 5025 5026 def _copy_internals(self, clone=_clone, **kw): 5027 self.scalar_alias = clone(self.scalar_alias, **kw) 5028 self.key = self.name = self.scalar_alias.name 5029 5030 @property 5031 def _from_objects(self): 5032 return [self.scalar_alias] 5033 5034 5035class CollationClause(ColumnElement): 5036 __visit_name__ = "collation" 5037 5038 _traverse_internals = [("collation", InternalTraversal.dp_string)] 5039 5040 def __init__(self, collation): 5041 self.collation = collation 5042 5043 5044class _IdentifiedClause(Executable, ClauseElement): 5045 5046 __visit_name__ = "identified" 5047 _execution_options = Executable._execution_options.union( 5048 {"autocommit": False} 5049 ) 5050 5051 def __init__(self, ident): 5052 self.ident = ident 5053 5054 5055class SavepointClause(_IdentifiedClause): 5056 __visit_name__ = "savepoint" 5057 5058 5059class RollbackToSavepointClause(_IdentifiedClause): 5060 __visit_name__ = "rollback_to_savepoint" 5061 5062 5063class ReleaseSavepointClause(_IdentifiedClause): 5064 __visit_name__ = "release_savepoint" 5065 5066 5067class quoted_name(util.MemoizedSlots, util.text_type): 5068 """Represent a SQL identifier combined with quoting preferences. 5069 5070 :class:`.quoted_name` is a Python unicode/str subclass which 5071 represents a particular identifier name along with a 5072 ``quote`` flag. This ``quote`` flag, when set to 5073 ``True`` or ``False``, overrides automatic quoting behavior 5074 for this identifier in order to either unconditionally quote 5075 or to not quote the name. If left at its default of ``None``, 5076 quoting behavior is applied to the identifier on a per-backend basis 5077 based on an examination of the token itself. 5078 5079 A :class:`.quoted_name` object with ``quote=True`` is also 5080 prevented from being modified in the case of a so-called 5081 "name normalize" option. Certain database backends, such as 5082 Oracle, Firebird, and DB2 "normalize" case-insensitive names 5083 as uppercase. The SQLAlchemy dialects for these backends 5084 convert from SQLAlchemy's lower-case-means-insensitive convention 5085 to the upper-case-means-insensitive conventions of those backends. 5086 The ``quote=True`` flag here will prevent this conversion from occurring 5087 to support an identifier that's quoted as all lower case against 5088 such a backend. 5089 5090 The :class:`.quoted_name` object is normally created automatically 5091 when specifying the name for key schema constructs such as 5092 :class:`_schema.Table`, :class:`_schema.Column`, and others. 5093 The class can also be 5094 passed explicitly as the name to any function that receives a name which 5095 can be quoted. Such as to use the :meth:`_engine.Engine.has_table` 5096 method with 5097 an unconditionally quoted name:: 5098 5099 from sqlalchemy import create_engine 5100 from sqlalchemy.sql import quoted_name 5101 5102 engine = create_engine("oracle+cx_oracle://some_dsn") 5103 engine.has_table(quoted_name("some_table", True)) 5104 5105 The above logic will run the "has table" logic against the Oracle backend, 5106 passing the name exactly as ``"some_table"`` without converting to 5107 upper case. 5108 5109 .. versionadded:: 0.9.0 5110 5111 .. versionchanged:: 1.2 The :class:`.quoted_name` construct is now 5112 importable from ``sqlalchemy.sql``, in addition to the previous 5113 location of ``sqlalchemy.sql.elements``. 5114 5115 """ 5116 5117 __slots__ = "quote", "lower", "upper" 5118 5119 def __new__(cls, value, quote): 5120 if value is None: 5121 return None 5122 # experimental - don't bother with quoted_name 5123 # if quote flag is None. doesn't seem to make any dent 5124 # in performance however 5125 # elif not sprcls and quote is None: 5126 # return value 5127 elif isinstance(value, cls) and ( 5128 quote is None or value.quote == quote 5129 ): 5130 return value 5131 self = super(quoted_name, cls).__new__(cls, value) 5132 5133 self.quote = quote 5134 return self 5135 5136 def __reduce__(self): 5137 return quoted_name, (util.text_type(self), self.quote) 5138 5139 def _memoized_method_lower(self): 5140 if self.quote: 5141 return self 5142 else: 5143 return util.text_type(self).lower() 5144 5145 def _memoized_method_upper(self): 5146 if self.quote: 5147 return self 5148 else: 5149 return util.text_type(self).upper() 5150 5151 def __repr__(self): 5152 if util.py2k: 5153 backslashed = self.encode("ascii", "backslashreplace") 5154 if not util.py2k: 5155 backslashed = backslashed.decode("ascii") 5156 return "'%s'" % backslashed 5157 else: 5158 return str.__repr__(self) 5159 5160 5161def _find_columns(clause): 5162 """locate Column objects within the given expression.""" 5163 5164 cols = util.column_set() 5165 traverse(clause, {}, {"column": cols.add}) 5166 return cols 5167 5168 5169def _type_from_args(args): 5170 for a in args: 5171 if not a.type._isnull: 5172 return a.type 5173 else: 5174 return type_api.NULLTYPE 5175 5176 5177def _corresponding_column_or_error(fromclause, column, require_embedded=False): 5178 c = fromclause.corresponding_column( 5179 column, require_embedded=require_embedded 5180 ) 5181 if c is None: 5182 raise exc.InvalidRequestError( 5183 "Given column '%s', attached to table '%s', " 5184 "failed to locate a corresponding column from table '%s'" 5185 % (column, getattr(column, "table", None), fromclause.description) 5186 ) 5187 return c 5188 5189 5190class AnnotatedColumnElement(Annotated): 5191 def __init__(self, element, values): 5192 Annotated.__init__(self, element, values) 5193 for attr in ( 5194 "comparator", 5195 "_proxy_key", 5196 "_tq_key_label", 5197 "_tq_label", 5198 "_non_anon_label", 5199 ): 5200 self.__dict__.pop(attr, None) 5201 for attr in ("name", "key", "table"): 5202 if self.__dict__.get(attr, False) is None: 5203 self.__dict__.pop(attr) 5204 5205 def _with_annotations(self, values): 5206 clone = super(AnnotatedColumnElement, self)._with_annotations(values) 5207 clone.__dict__.pop("comparator", None) 5208 return clone 5209 5210 @util.memoized_property 5211 def name(self): 5212 """pull 'name' from parent, if not present""" 5213 return self._Annotated__element.name 5214 5215 @util.memoized_property 5216 def table(self): 5217 """pull 'table' from parent, if not present""" 5218 return self._Annotated__element.table 5219 5220 @util.memoized_property 5221 def key(self): 5222 """pull 'key' from parent, if not present""" 5223 return self._Annotated__element.key 5224 5225 @util.memoized_property 5226 def info(self): 5227 return self._Annotated__element.info 5228 5229 @util.memoized_property 5230 def _anon_name_label(self): 5231 return self._Annotated__element._anon_name_label 5232 5233 5234class _truncated_label(quoted_name): 5235 """A unicode subclass used to identify symbolic " 5236 "names that may require truncation.""" 5237 5238 __slots__ = () 5239 5240 def __new__(cls, value, quote=None): 5241 quote = getattr(value, "quote", quote) 5242 # return super(_truncated_label, cls).__new__(cls, value, quote, True) 5243 return super(_truncated_label, cls).__new__(cls, value, quote) 5244 5245 def __reduce__(self): 5246 return self.__class__, (util.text_type(self), self.quote) 5247 5248 def apply_map(self, map_): 5249 return self 5250 5251 5252class conv(_truncated_label): 5253 """Mark a string indicating that a name has already been converted 5254 by a naming convention. 5255 5256 This is a string subclass that indicates a name that should not be 5257 subject to any further naming conventions. 5258 5259 E.g. when we create a :class:`.Constraint` using a naming convention 5260 as follows:: 5261 5262 m = MetaData(naming_convention={ 5263 "ck": "ck_%(table_name)s_%(constraint_name)s" 5264 }) 5265 t = Table('t', m, Column('x', Integer), 5266 CheckConstraint('x > 5', name='x5')) 5267 5268 The name of the above constraint will be rendered as ``"ck_t_x5"``. 5269 That is, the existing name ``x5`` is used in the naming convention as the 5270 ``constraint_name`` token. 5271 5272 In some situations, such as in migration scripts, we may be rendering 5273 the above :class:`.CheckConstraint` with a name that's already been 5274 converted. In order to make sure the name isn't double-modified, the 5275 new name is applied using the :func:`_schema.conv` marker. We can 5276 use this explicitly as follows:: 5277 5278 5279 m = MetaData(naming_convention={ 5280 "ck": "ck_%(table_name)s_%(constraint_name)s" 5281 }) 5282 t = Table('t', m, Column('x', Integer), 5283 CheckConstraint('x > 5', name=conv('ck_t_x5'))) 5284 5285 Where above, the :func:`_schema.conv` marker indicates that the constraint 5286 name here is final, and the name will render as ``"ck_t_x5"`` and not 5287 ``"ck_t_ck_t_x5"`` 5288 5289 .. versionadded:: 0.9.4 5290 5291 .. seealso:: 5292 5293 :ref:`constraint_naming_conventions` 5294 5295 """ 5296 5297 __slots__ = () 5298 5299 5300_NONE_NAME = util.symbol("NONE_NAME") 5301"""indicate a 'deferred' name that was ultimately the value None.""" 5302 5303# for backwards compatibility in case 5304# someone is re-implementing the 5305# _truncated_identifier() sequence in a custom 5306# compiler 5307_generated_label = _truncated_label 5308 5309 5310class _anonymous_label(_truncated_label): 5311 """A unicode subclass used to identify anonymously 5312 generated names.""" 5313 5314 __slots__ = () 5315 5316 @classmethod 5317 def safe_construct( 5318 cls, seed, body, enclosing_label=None, sanitize_key=False 5319 ): 5320 5321 if sanitize_key: 5322 body = re.sub(r"[%\(\) \$]+", "_", body).strip("_") 5323 5324 label = "%%(%d %s)s" % (seed, body.replace("%", "%%")) 5325 if enclosing_label: 5326 label = "%s%s" % (enclosing_label, label) 5327 5328 return _anonymous_label(label) 5329 5330 def __add__(self, other): 5331 if "%" in other and not isinstance(other, _anonymous_label): 5332 other = util.text_type(other).replace("%", "%%") 5333 else: 5334 other = util.text_type(other) 5335 5336 return _anonymous_label( 5337 quoted_name( 5338 util.text_type.__add__(self, other), 5339 self.quote, 5340 ) 5341 ) 5342 5343 def __radd__(self, other): 5344 if "%" in other and not isinstance(other, _anonymous_label): 5345 other = util.text_type(other).replace("%", "%%") 5346 else: 5347 other = util.text_type(other) 5348 5349 return _anonymous_label( 5350 quoted_name( 5351 util.text_type.__add__(other, self), 5352 self.quote, 5353 ) 5354 ) 5355 5356 def apply_map(self, map_): 5357 if self.quote is not None: 5358 # preserve quoting only if necessary 5359 return quoted_name(self % map_, self.quote) 5360 else: 5361 # else skip the constructor call 5362 return self % map_ 5363