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