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