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