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