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