Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/sqlalchemy/sql/elements.py : 57%

Hot-keys on this page
r m x p 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-2020 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:`bindparam()` elements replaced.
305 Same functionality as ``params()``, except adds `unique=True`
306 to affected bind parameters so that multiple statements can be
307 used.
309 """
310 return self._params(True, optionaldict, kwargs)
312 def params(self, *optionaldict, **kwargs):
313 """Return a copy with :func:`bindparam()` elements replaced.
315 Returns a copy of this ClauseElement with :func:`bindparam()`
316 elements replaced with values taken from the given dictionary::
318 >>> clause = column('x') + bindparam('foo')
319 >>> print(clause.compile().params)
320 {'foo':None}
321 >>> print(clause.params({'foo':7}).compile().params)
322 {'foo':7}
324 """
325 return self._params(False, optionaldict, kwargs)
327 def _params(self, unique, optionaldict, kwargs):
328 if len(optionaldict) == 1:
329 kwargs.update(optionaldict[0])
330 elif len(optionaldict) > 1:
331 raise exc.ArgumentError(
332 "params() takes zero or one positional dictionary argument"
333 )
335 def visit_bindparam(bind):
336 if bind.key in kwargs:
337 bind.value = kwargs[bind.key]
338 bind.required = False
339 if unique:
340 bind._convert_to_unique()
342 return cloned_traverse(self, {}, {"bindparam": visit_bindparam})
344 def compare(self, other, **kw):
345 r"""Compare this ClauseElement to the given ClauseElement.
347 Subclasses should override the default behavior, which is a
348 straight identity comparison.
350 \**kw are arguments consumed by subclass compare() methods and
351 may be used to modify the criteria for comparison.
352 (see :class:`_expression.ColumnElement`)
354 """
355 return self is other
357 def _copy_internals(self, clone=_clone, **kw):
358 """Reassign internal elements to be clones of themselves.
360 Called during a copy-and-traverse operation on newly
361 shallow-copied elements to create a deep copy.
363 The given clone function should be used, which may be applying
364 additional transformations to the element (i.e. replacement
365 traversal, cloned traversal, annotations).
367 """
368 pass
370 def get_children(self, **kwargs):
371 r"""Return immediate child elements of this
372 :class:`_expression.ClauseElement`.
374 This is used for visit traversal.
376 \**kwargs may contain flags that change the collection that is
377 returned, for example to return a subset of items in order to
378 cut down on larger traversals, or to return child items from a
379 different context (such as schema-level collections instead of
380 clause-level).
382 """
383 return []
385 def self_group(self, against=None):
386 """Apply a 'grouping' to this :class:`_expression.ClauseElement`.
388 This method is overridden by subclasses to return a
389 "grouping" construct, i.e. parenthesis. In particular
390 it's used by "binary" expressions to provide a grouping
391 around themselves when placed into a larger expression,
392 as well as by :func:`_expression.select` constructs when placed into
393 the FROM clause of another :func:`_expression.select`. (Note that
394 subqueries should be normally created using the
395 :meth:`_expression.Select.alias` method, as many platforms require
396 nested SELECT statements to be named).
398 As expressions are composed together, the application of
399 :meth:`self_group` is automatic - end-user code should never
400 need to use this method directly. Note that SQLAlchemy's
401 clause constructs take operator precedence into account -
402 so parenthesis might not be needed, for example, in
403 an expression like ``x OR (y AND z)`` - AND takes precedence
404 over OR.
406 The base :meth:`self_group` method of
407 :class:`_expression.ClauseElement`
408 just returns self.
409 """
410 return self
412 @util.dependencies("sqlalchemy.engine.default")
413 def compile(self, default, bind=None, dialect=None, **kw):
414 """Compile this SQL expression.
416 The return value is a :class:`~.Compiled` object.
417 Calling ``str()`` or ``unicode()`` on the returned value will yield a
418 string representation of the result. The
419 :class:`~.Compiled` object also can return a
420 dictionary of bind parameter names and values
421 using the ``params`` accessor.
423 :param bind: An ``Engine`` or ``Connection`` from which a
424 ``Compiled`` will be acquired. This argument takes precedence over
425 this :class:`_expression.ClauseElement`'s bound engine, if any.
427 :param column_keys: Used for INSERT and UPDATE statements, a list of
428 column names which should be present in the VALUES clause of the
429 compiled statement. If ``None``, all columns from the target table
430 object are rendered.
432 :param dialect: A ``Dialect`` instance from which a ``Compiled``
433 will be acquired. This argument takes precedence over the `bind`
434 argument as well as this :class:`_expression.ClauseElement`
435 's bound engine,
436 if any.
438 :param inline: Used for INSERT statements, for a dialect which does
439 not support inline retrieval of newly generated primary key
440 columns, will force the expression used to create the new primary
441 key value to be rendered inline within the INSERT statement's
442 VALUES clause. This typically refers to Sequence execution but may
443 also refer to any server-side default generation function
444 associated with a primary key `Column`.
446 :param compile_kwargs: optional dictionary of additional parameters
447 that will be passed through to the compiler within all "visit"
448 methods. This allows any custom flag to be passed through to
449 a custom compilation construct, for example. It is also used
450 for the case of passing the ``literal_binds`` flag through::
452 from sqlalchemy.sql import table, column, select
454 t = table('t', column('x'))
456 s = select([t]).where(t.c.x == 5)
458 print(s.compile(compile_kwargs={"literal_binds": True}))
460 .. versionadded:: 0.9.0
462 .. seealso::
464 :ref:`faq_sql_expression_string`
466 """
468 if not dialect:
469 if bind:
470 dialect = bind.dialect
471 elif self.bind:
472 dialect = self.bind.dialect
473 bind = self.bind
474 else:
475 dialect = default.StrCompileDialect()
476 return self._compiler(dialect, bind=bind, **kw)
478 def _compiler(self, dialect, **kw):
479 """Return a compiler appropriate for this ClauseElement, given a
480 Dialect."""
482 return dialect.statement_compiler(dialect, self, **kw)
484 def __str__(self):
485 if util.py3k:
486 return str(self.compile())
487 else:
488 return unicode(self.compile()).encode( # noqa
489 "ascii", "backslashreplace"
490 ) # noqa
492 @util.deprecated(
493 "0.9",
494 "The :meth:`_expression.ClauseElement.__and__` "
495 "method is deprecated and will "
496 "be removed in a future release. Conjunctions should only be "
497 "used from a :class:`_expression.ColumnElement` subclass, e.g. "
498 ":meth:`_expression.ColumnElement.__and__`.",
499 )
500 def __and__(self, other):
501 """'and' at the ClauseElement level.
502 """
503 return and_(self, other)
505 @util.deprecated(
506 "0.9",
507 "The :meth:`_expression.ClauseElement.__or__` "
508 "method is deprecated and will "
509 "be removed in a future release. Conjunctions should only be "
510 "used from a :class:`_expression.ColumnElement` subclass, e.g. "
511 ":meth:`_expression.ColumnElement.__or__`.",
512 )
513 def __or__(self, other):
514 """'or' at the ClauseElement level.
515 """
516 return or_(self, other)
518 def __invert__(self):
519 if hasattr(self, "negation_clause"):
520 return self.negation_clause
521 else:
522 return self._negate()
524 def _negate(self):
525 return UnaryExpression(
526 self.self_group(against=operators.inv),
527 operator=operators.inv,
528 negate=None,
529 )
531 def __bool__(self):
532 raise TypeError("Boolean value of this clause is not defined")
534 __nonzero__ = __bool__
536 def __repr__(self):
537 friendly = self.description
538 if friendly is None:
539 return object.__repr__(self)
540 else:
541 return "<%s.%s at 0x%x; %s>" % (
542 self.__module__,
543 self.__class__.__name__,
544 id(self),
545 friendly,
546 )
549class ColumnElement(operators.ColumnOperators, ClauseElement):
550 """Represent a column-oriented SQL expression suitable for usage in the
551 "columns" clause, WHERE clause etc. of a statement.
553 While the most familiar kind of :class:`_expression.ColumnElement` is the
554 :class:`_schema.Column` object, :class:`_expression.ColumnElement`
555 serves as the basis
556 for any unit that may be present in a SQL expression, including
557 the expressions themselves, SQL functions, bound parameters,
558 literal expressions, keywords such as ``NULL``, etc.
559 :class:`_expression.ColumnElement`
560 is the ultimate base class for all such elements.
562 A wide variety of SQLAlchemy Core functions work at the SQL expression
563 level, and are intended to accept instances of
564 :class:`_expression.ColumnElement` as
565 arguments. These functions will typically document that they accept a
566 "SQL expression" as an argument. What this means in terms of SQLAlchemy
567 usually refers to an input which is either already in the form of a
568 :class:`_expression.ColumnElement` object,
569 or a value which can be **coerced** into
570 one. The coercion rules followed by most, but not all, SQLAlchemy Core
571 functions with regards to SQL expressions are as follows:
573 * a literal Python value, such as a string, integer or floating
574 point value, boolean, datetime, ``Decimal`` object, or virtually
575 any other Python object, will be coerced into a "literal bound
576 value". This generally means that a :func:`.bindparam` will be
577 produced featuring the given value embedded into the construct; the
578 resulting :class:`.BindParameter` object is an instance of
579 :class:`_expression.ColumnElement`.
580 The Python value will ultimately be sent
581 to the DBAPI at execution time as a parameterized argument to the
582 ``execute()`` or ``executemany()`` methods, after SQLAlchemy
583 type-specific converters (e.g. those provided by any associated
584 :class:`.TypeEngine` objects) are applied to the value.
586 * any special object value, typically ORM-level constructs, which
587 feature a method called ``__clause_element__()``. The Core
588 expression system looks for this method when an object of otherwise
589 unknown type is passed to a function that is looking to coerce the
590 argument into a :class:`_expression.ColumnElement` expression. The
591 ``__clause_element__()`` method, if present, should return a
592 :class:`_expression.ColumnElement` instance. The primary use of
593 ``__clause_element__()`` within SQLAlchemy is that of class-bound
594 attributes on ORM-mapped classes; a ``User`` class which contains a
595 mapped attribute named ``.name`` will have a method
596 ``User.name.__clause_element__()`` which when invoked returns the
597 :class:`_schema.Column`
598 called ``name`` associated with the mapped table.
600 * The Python ``None`` value is typically interpreted as ``NULL``,
601 which in SQLAlchemy Core produces an instance of :func:`.null`.
603 A :class:`_expression.ColumnElement` provides the ability to generate new
604 :class:`_expression.ColumnElement`
605 objects using Python expressions. This means that Python operators
606 such as ``==``, ``!=`` and ``<`` are overloaded to mimic SQL operations,
607 and allow the instantiation of further :class:`_expression.ColumnElement`
608 instances
609 which are composed from other, more fundamental
610 :class:`_expression.ColumnElement`
611 objects. For example, two :class:`.ColumnClause` objects can be added
612 together with the addition operator ``+`` to produce
613 a :class:`.BinaryExpression`.
614 Both :class:`.ColumnClause` and :class:`.BinaryExpression` are subclasses
615 of :class:`_expression.ColumnElement`::
617 >>> from sqlalchemy.sql import column
618 >>> column('a') + column('b')
619 <sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
620 >>> print(column('a') + column('b'))
621 a + b
623 .. seealso::
625 :class:`_schema.Column`
627 :func:`_expression.column`
629 """
631 __visit_name__ = "column_element"
632 primary_key = False
633 foreign_keys = []
634 _proxies = ()
636 _label = None
637 """The named label that can be used to target
638 this column in a result set.
640 This label is almost always the label used when
641 rendering <expr> AS <label> in a SELECT statement. It also
642 refers to a name that this column expression can be located from
643 in a result set.
645 For a regular Column bound to a Table, this is typically the label
646 <tablename>_<columnname>. For other constructs, different rules
647 may apply, such as anonymized labels and others.
649 """
651 key = None
652 """the 'key' that in some circumstances refers to this object in a
653 Python namespace.
655 This typically refers to the "key" of the column as present in the
656 ``.c`` collection of a selectable, e.g. sometable.c["somekey"] would
657 return a Column with a .key of "somekey".
659 """
661 _key_label = None
662 """A label-based version of 'key' that in some circumstances refers
663 to this object in a Python namespace.
666 _key_label comes into play when a select() statement is constructed with
667 apply_labels(); in this case, all Column objects in the ``.c`` collection
668 are rendered as <tablename>_<columnname> in SQL; this is essentially the
669 value of ._label. But to locate those columns in the ``.c`` collection,
670 the name is along the lines of <tablename>_<key>; that's the typical
671 value of .key_label.
673 """
675 _render_label_in_columns_clause = True
676 """A flag used by select._columns_plus_names that helps to determine
677 we are actually going to render in terms of "SELECT <col> AS <label>".
678 This flag can be returned as False for some Column objects that want
679 to be rendered as simple "SELECT <col>"; typically columns that don't have
680 any parent table and are named the same as what the label would be
681 in any case.
683 """
685 _resolve_label = None
686 """The name that should be used to identify this ColumnElement in a
687 select() object when "label resolution" logic is used; this refers
688 to using a string name in an expression like order_by() or group_by()
689 that wishes to target a labeled expression in the columns clause.
691 The name is distinct from that of .name or ._label to account for the case
692 where anonymizing logic may be used to change the name that's actually
693 rendered at compile time; this attribute should hold onto the original
694 name that was user-assigned when producing a .label() construct.
696 """
698 _allow_label_resolve = True
699 """A flag that can be flipped to prevent a column from being resolvable
700 by string label name."""
702 _is_implicitly_boolean = False
704 _alt_names = ()
706 def self_group(self, against=None):
707 if (
708 against in (operators.and_, operators.or_, operators._asbool)
709 and self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity
710 ):
711 return AsBoolean(self, operators.istrue, operators.isfalse)
712 elif against in (operators.any_op, operators.all_op):
713 return Grouping(self)
714 else:
715 return self
717 def _negate(self):
718 if self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity:
719 return AsBoolean(self, operators.isfalse, operators.istrue)
720 else:
721 return super(ColumnElement, self)._negate()
723 @util.memoized_property
724 def type(self):
725 return type_api.NULLTYPE
727 @util.memoized_property
728 def comparator(self):
729 try:
730 comparator_factory = self.type.comparator_factory
731 except AttributeError as err:
732 util.raise_(
733 TypeError(
734 "Object %r associated with '.type' attribute "
735 "is not a TypeEngine class or object" % self.type
736 ),
737 replace_context=err,
738 )
739 else:
740 return comparator_factory(self)
742 def __getattr__(self, key):
743 try:
744 return getattr(self.comparator, key)
745 except AttributeError as err:
746 util.raise_(
747 AttributeError(
748 "Neither %r object nor %r object has an attribute %r"
749 % (
750 type(self).__name__,
751 type(self.comparator).__name__,
752 key,
753 )
754 ),
755 replace_context=err,
756 )
758 def operate(self, op, *other, **kwargs):
759 return op(self.comparator, *other, **kwargs)
761 def reverse_operate(self, op, other, **kwargs):
762 return op(other, self.comparator, **kwargs)
764 def _bind_param(self, operator, obj, type_=None):
765 return BindParameter(
766 None,
767 obj,
768 _compared_to_operator=operator,
769 type_=type_,
770 _compared_to_type=self.type,
771 unique=True,
772 )
774 @property
775 def expression(self):
776 """Return a column expression.
778 Part of the inspection interface; returns self.
780 """
781 return self
783 @property
784 def _select_iterable(self):
785 return (self,)
787 @util.memoized_property
788 def base_columns(self):
789 return util.column_set(c for c in self.proxy_set if not c._proxies)
791 @util.memoized_property
792 def proxy_set(self):
793 s = util.column_set([self])
794 for c in self._proxies:
795 s.update(c.proxy_set)
796 return s
798 def _uncached_proxy_set(self):
799 """An 'uncached' version of proxy set.
801 This is so that we can read annotations from the list of columns
802 without breaking the caching of the above proxy_set.
804 """
805 s = util.column_set([self])
806 for c in self._proxies:
807 s.update(c._uncached_proxy_set())
808 return s
810 def shares_lineage(self, othercolumn):
811 """Return True if the given :class:`_expression.ColumnElement`
812 has a common ancestor to this :class:`_expression.ColumnElement`."""
814 return bool(self.proxy_set.intersection(othercolumn.proxy_set))
816 def _compare_name_for_result(self, other):
817 """Return True if the given column element compares to this one
818 when targeting within a result row."""
820 return (
821 hasattr(other, "name")
822 and hasattr(self, "name")
823 and other.name == self.name
824 )
826 def _make_proxy(
827 self, selectable, name=None, name_is_truncatable=False, **kw
828 ):
829 """Create a new :class:`_expression.ColumnElement` representing this
830 :class:`_expression.ColumnElement`
831 as it appears in the select list of a
832 descending selectable.
834 """
835 if name is None:
836 name = self.anon_label
837 if self.key:
838 key = self.key
839 else:
840 try:
841 key = str(self)
842 except exc.UnsupportedCompilationError:
843 key = self.anon_label
845 else:
846 key = name
847 co = ColumnClause(
848 _as_truncated(name) if name_is_truncatable else name,
849 type_=getattr(self, "type", None),
850 _selectable=selectable,
851 )
852 co._proxies = [self]
853 if selectable._is_clone_of is not None:
854 co._is_clone_of = selectable._is_clone_of.columns.get(key)
855 selectable._columns[key] = co
856 return co
858 def compare(self, other, use_proxies=False, equivalents=None, **kw):
859 """Compare this ColumnElement to another.
861 Special arguments understood:
863 :param use_proxies: when True, consider two columns that
864 share a common base column as equivalent (i.e. shares_lineage())
866 :param equivalents: a dictionary of columns as keys mapped to sets
867 of columns. If the given "other" column is present in this
868 dictionary, if any of the columns in the corresponding set() pass
869 the comparison test, the result is True. This is used to expand the
870 comparison to other columns that may be known to be equivalent to
871 this one via foreign key or other criterion.
873 """
874 to_compare = (other,)
875 if equivalents and other in equivalents:
876 to_compare = equivalents[other].union(to_compare)
878 for oth in to_compare:
879 if use_proxies and self.shares_lineage(oth):
880 return True
881 elif hash(oth) == hash(self):
882 return True
883 else:
884 return False
886 def cast(self, type_):
887 """Produce a type cast, i.e. ``CAST(<expression> AS <type>)``.
889 This is a shortcut to the :func:`_expression.cast` function.
891 .. seealso::
893 :ref:`coretutorial_casts`
895 :func:`_expression.cast`
897 :func:`_expression.type_coerce`
899 .. versionadded:: 1.0.7
901 """
902 return Cast(self, type_)
904 def label(self, name):
905 """Produce a column label, i.e. ``<columnname> AS <name>``.
907 This is a shortcut to the :func:`_expression.label` function.
909 if 'name' is None, an anonymous label name will be generated.
911 """
912 return Label(name, self, self.type)
914 @util.memoized_property
915 def anon_label(self):
916 """provides a constant 'anonymous label' for this ColumnElement.
918 This is a label() expression which will be named at compile time.
919 The same label() is returned each time anon_label is called so
920 that expressions can reference anon_label multiple times, producing
921 the same label name at compile time.
923 the compiler uses this function automatically at compile time
924 for expressions that are known to be 'unnamed' like binary
925 expressions and function calls.
927 """
928 while self._is_clone_of is not None:
929 self = self._is_clone_of
931 return _anonymous_label(
932 "%%(%d %s)s" % (id(self), getattr(self, "name", "anon"))
933 )
936class BindParameter(ColumnElement):
937 r"""Represent a "bound expression".
939 :class:`.BindParameter` is invoked explicitly using the
940 :func:`.bindparam` function, as in::
942 from sqlalchemy import bindparam
944 stmt = select([users_table]).\
945 where(users_table.c.name == bindparam('username'))
947 Detailed discussion of how :class:`.BindParameter` is used is
948 at :func:`.bindparam`.
950 .. seealso::
952 :func:`.bindparam`
954 """
956 __visit_name__ = "bindparam"
958 _is_crud = False
959 _expanding_in_types = ()
961 def __init__(
962 self,
963 key,
964 value=NO_ARG,
965 type_=None,
966 unique=False,
967 required=NO_ARG,
968 quote=None,
969 callable_=None,
970 expanding=False,
971 isoutparam=False,
972 _compared_to_operator=None,
973 _compared_to_type=None,
974 ):
975 r"""Produce a "bound expression".
977 The return value is an instance of :class:`.BindParameter`; this
978 is a :class:`_expression.ColumnElement`
979 subclass which represents a so-called
980 "placeholder" value in a SQL expression, the value of which is
981 supplied at the point at which the statement in executed against a
982 database connection.
984 In SQLAlchemy, the :func:`.bindparam` construct has
985 the ability to carry along the actual value that will be ultimately
986 used at expression time. In this way, it serves not just as
987 a "placeholder" for eventual population, but also as a means of
988 representing so-called "unsafe" values which should not be rendered
989 directly in a SQL statement, but rather should be passed along
990 to the :term:`DBAPI` as values which need to be correctly escaped
991 and potentially handled for type-safety.
993 When using :func:`.bindparam` explicitly, the use case is typically
994 one of traditional deferment of parameters; the :func:`.bindparam`
995 construct accepts a name which can then be referred to at execution
996 time::
998 from sqlalchemy import bindparam
1000 stmt = select([users_table]).\
1001 where(users_table.c.name == bindparam('username'))
1003 The above statement, when rendered, will produce SQL similar to::
1005 SELECT id, name FROM user WHERE name = :username
1007 In order to populate the value of ``:username`` above, the value
1008 would typically be applied at execution time to a method
1009 like :meth:`_engine.Connection.execute`::
1011 result = connection.execute(stmt, username='wendy')
1013 Explicit use of :func:`.bindparam` is also common when producing
1014 UPDATE or DELETE statements that are to be invoked multiple times,
1015 where the WHERE criterion of the statement is to change on each
1016 invocation, such as::
1018 stmt = (users_table.update().
1019 where(user_table.c.name == bindparam('username')).
1020 values(fullname=bindparam('fullname'))
1021 )
1023 connection.execute(
1024 stmt, [{"username": "wendy", "fullname": "Wendy Smith"},
1025 {"username": "jack", "fullname": "Jack Jones"},
1026 ]
1027 )
1029 SQLAlchemy's Core expression system makes wide use of
1030 :func:`.bindparam` in an implicit sense. It is typical that Python
1031 literal values passed to virtually all SQL expression functions are
1032 coerced into fixed :func:`.bindparam` constructs. For example, given
1033 a comparison operation such as::
1035 expr = users_table.c.name == 'Wendy'
1037 The above expression will produce a :class:`.BinaryExpression`
1038 construct, where the left side is the :class:`_schema.Column` object
1039 representing the ``name`` column, and the right side is a
1040 :class:`.BindParameter` representing the literal value::
1042 print(repr(expr.right))
1043 BindParameter('%(4327771088 name)s', 'Wendy', type_=String())
1045 The expression above will render SQL such as::
1047 user.name = :name_1
1049 Where the ``:name_1`` parameter name is an anonymous name. The
1050 actual string ``Wendy`` is not in the rendered string, but is carried
1051 along where it is later used within statement execution. If we
1052 invoke a statement like the following::
1054 stmt = select([users_table]).where(users_table.c.name == 'Wendy')
1055 result = connection.execute(stmt)
1057 We would see SQL logging output as::
1059 SELECT "user".id, "user".name
1060 FROM "user"
1061 WHERE "user".name = %(name_1)s
1062 {'name_1': 'Wendy'}
1064 Above, we see that ``Wendy`` is passed as a parameter to the database,
1065 while the placeholder ``:name_1`` is rendered in the appropriate form
1066 for the target database, in this case the PostgreSQL database.
1068 Similarly, :func:`.bindparam` is invoked automatically
1069 when working with :term:`CRUD` statements as far as the "VALUES"
1070 portion is concerned. The :func:`_expression.insert`
1071 construct produces an
1072 ``INSERT`` expression which will, at statement execution time,
1073 generate bound placeholders based on the arguments passed, as in::
1075 stmt = users_table.insert()
1076 result = connection.execute(stmt, name='Wendy')
1078 The above will produce SQL output as::
1080 INSERT INTO "user" (name) VALUES (%(name)s)
1081 {'name': 'Wendy'}
1083 The :class:`_expression.Insert` construct,
1084 at compilation/execution time,
1085 rendered a single :func:`.bindparam` mirroring the column
1086 name ``name`` as a result of the single ``name`` parameter
1087 we passed to the :meth:`_engine.Connection.execute` method.
1089 :param key:
1090 the key (e.g. the name) for this bind param.
1091 Will be used in the generated
1092 SQL statement for dialects that use named parameters. This
1093 value may be modified when part of a compilation operation,
1094 if other :class:`BindParameter` objects exist with the same
1095 key, or if its length is too long and truncation is
1096 required.
1098 :param value:
1099 Initial value for this bind param. Will be used at statement
1100 execution time as the value for this parameter passed to the
1101 DBAPI, if no other value is indicated to the statement execution
1102 method for this particular parameter name. Defaults to ``None``.
1104 :param callable\_:
1105 A callable function that takes the place of "value". The function
1106 will be called at statement execution time to determine the
1107 ultimate value. Used for scenarios where the actual bind
1108 value cannot be determined at the point at which the clause
1109 construct is created, but embedded bind values are still desirable.
1111 :param type\_:
1112 A :class:`.TypeEngine` class or instance representing an optional
1113 datatype for this :func:`.bindparam`. If not passed, a type
1114 may be determined automatically for the bind, based on the given
1115 value; for example, trivial Python types such as ``str``,
1116 ``int``, ``bool``
1117 may result in the :class:`.String`, :class:`.Integer` or
1118 :class:`.Boolean` types being automatically selected.
1120 The type of a :func:`.bindparam` is significant especially in that
1121 the type will apply pre-processing to the value before it is
1122 passed to the database. For example, a :func:`.bindparam` which
1123 refers to a datetime value, and is specified as holding the
1124 :class:`.DateTime` type, may apply conversion needed to the
1125 value (such as stringification on SQLite) before passing the value
1126 to the database.
1128 :param unique:
1129 if True, the key name of this :class:`.BindParameter` will be
1130 modified if another :class:`.BindParameter` of the same name
1131 already has been located within the containing
1132 expression. This flag is used generally by the internals
1133 when producing so-called "anonymous" bound expressions, it
1134 isn't generally applicable to explicitly-named :func:`.bindparam`
1135 constructs.
1137 :param required:
1138 If ``True``, a value is required at execution time. If not passed,
1139 it defaults to ``True`` if neither :paramref:`.bindparam.value`
1140 or :paramref:`.bindparam.callable` were passed. If either of these
1141 parameters are present, then :paramref:`.bindparam.required`
1142 defaults to ``False``.
1144 :param quote:
1145 True if this parameter name requires quoting and is not
1146 currently known as a SQLAlchemy reserved word; this currently
1147 only applies to the Oracle backend, where bound names must
1148 sometimes be quoted.
1150 :param isoutparam:
1151 if True, the parameter should be treated like a stored procedure
1152 "OUT" parameter. This applies to backends such as Oracle which
1153 support OUT parameters.
1155 :param expanding:
1156 if True, this parameter will be treated as an "expanding" parameter
1157 at execution time; the parameter value is expected to be a sequence,
1158 rather than a scalar value, and the string SQL statement will
1159 be transformed on a per-execution basis to accommodate the sequence
1160 with a variable number of parameter slots passed to the DBAPI.
1161 This is to allow statement caching to be used in conjunction with
1162 an IN clause.
1164 .. seealso::
1166 :meth:`.ColumnOperators.in_`
1168 :ref:`baked_in` - with baked queries
1170 .. note:: The "expanding" feature does not support "executemany"-
1171 style parameter sets.
1173 .. versionadded:: 1.2
1175 .. versionchanged:: 1.3 the "expanding" bound parameter feature now
1176 supports empty lists.
1179 .. seealso::
1181 :ref:`coretutorial_bind_param`
1183 :ref:`coretutorial_insert_expressions`
1185 :func:`.outparam`
1187 """
1188 if isinstance(key, ColumnClause):
1189 type_ = key.type
1190 key = key.key
1191 if required is NO_ARG:
1192 required = value is NO_ARG and callable_ is None
1193 if value is NO_ARG:
1194 value = None
1196 if quote is not None:
1197 key = quoted_name(key, quote)
1199 if unique:
1200 self.key = _anonymous_label(
1201 "%%(%d %s)s"
1202 % (
1203 id(self),
1204 re.sub(r"[%\(\) \$]+", "_", key).strip("_")
1205 if key is not None
1206 else "param",
1207 )
1208 )
1209 else:
1210 self.key = key or _anonymous_label("%%(%d param)s" % id(self))
1212 # identifying key that won't change across
1213 # clones, used to identify the bind's logical
1214 # identity
1215 self._identifying_key = self.key
1217 # key that was passed in the first place, used to
1218 # generate new keys
1219 self._orig_key = key or "param"
1221 self.unique = unique
1222 self.value = value
1223 self.callable = callable_
1224 self.isoutparam = isoutparam
1225 self.required = required
1226 self.expanding = expanding
1228 if type_ is None:
1229 if _compared_to_type is not None:
1230 self.type = _compared_to_type.coerce_compared_value(
1231 _compared_to_operator, value
1232 )
1233 else:
1234 self.type = type_api._resolve_value_to_type(value)
1235 elif isinstance(type_, type):
1236 self.type = type_()
1237 else:
1238 self.type = type_
1240 def _with_expanding_in_types(self, types):
1241 """Return a copy of this :class:`.BindParameter` in
1242 the context of an expanding IN against a tuple.
1244 """
1245 cloned = self._clone()
1246 cloned._expanding_in_types = types
1247 return cloned
1249 def _with_value(self, value):
1250 """Return a copy of this :class:`.BindParameter` with the given value
1251 set.
1252 """
1253 cloned = self._clone()
1254 cloned.value = value
1255 cloned.callable = None
1256 cloned.required = False
1257 if cloned.type is type_api.NULLTYPE:
1258 cloned.type = type_api._resolve_value_to_type(value)
1259 return cloned
1261 @property
1262 def effective_value(self):
1263 """Return the value of this bound parameter,
1264 taking into account if the ``callable`` parameter
1265 was set.
1267 The ``callable`` value will be evaluated
1268 and returned if present, else ``value``.
1270 """
1271 if self.callable:
1272 return self.callable()
1273 else:
1274 return self.value
1276 def _clone(self):
1277 c = ClauseElement._clone(self)
1278 if self.unique:
1279 c.key = _anonymous_label(
1280 "%%(%d %s)s" % (id(c), c._orig_key or "param")
1281 )
1282 return c
1284 def _convert_to_unique(self):
1285 if not self.unique:
1286 self.unique = True
1287 self.key = _anonymous_label(
1288 "%%(%d %s)s" % (id(self), self._orig_key or "param")
1289 )
1291 def compare(self, other, **kw):
1292 """Compare this :class:`BindParameter` to the given
1293 clause."""
1295 return (
1296 isinstance(other, BindParameter)
1297 and self.type._compare_type_affinity(other.type)
1298 and self.value == other.value
1299 and self.callable == other.callable
1300 )
1302 def __getstate__(self):
1303 """execute a deferred value for serialization purposes."""
1305 d = self.__dict__.copy()
1306 v = self.value
1307 if self.callable:
1308 v = self.callable()
1309 d["callable"] = None
1310 d["value"] = v
1311 return d
1313 def __setstate__(self, state):
1314 if state.get("unique", False):
1315 state["key"] = _anonymous_label(
1316 "%%(%d %s)s" % (id(self), state.get("_orig_key", "param"))
1317 )
1318 self.__dict__.update(state)
1320 def __repr__(self):
1321 return "BindParameter(%r, %r, type_=%r)" % (
1322 self.key,
1323 self.value,
1324 self.type,
1325 )
1328class TypeClause(ClauseElement):
1329 """Handle a type keyword in a SQL statement.
1331 Used by the ``Case`` statement.
1333 """
1335 __visit_name__ = "typeclause"
1337 def __init__(self, type_):
1338 self.type = type_
1341class TextClause(Executable, ClauseElement):
1342 """Represent a literal SQL text fragment.
1344 E.g.::
1346 from sqlalchemy import text
1348 t = text("SELECT * FROM users")
1349 result = connection.execute(t)
1352 The :class:`_expression.TextClause` construct is produced using the
1353 :func:`_expression.text`
1354 function; see that function for full documentation.
1356 .. seealso::
1358 :func:`_expression.text`
1360 """
1362 __visit_name__ = "textclause"
1364 _bind_params_regex = re.compile(r"(?<![:\w\x5c]):(\w+)(?!:)", re.UNICODE)
1365 _execution_options = Executable._execution_options.union(
1366 {"autocommit": PARSE_AUTOCOMMIT}
1367 )
1368 _is_implicitly_boolean = False
1370 def __and__(self, other):
1371 # support use in select.where(), query.filter()
1372 return and_(self, other)
1374 @property
1375 def _select_iterable(self):
1376 return (self,)
1378 @property
1379 def selectable(self):
1380 # allows text() to be considered by
1381 # _interpret_as_from
1382 return self
1384 _hide_froms = []
1386 # help in those cases where text() is
1387 # interpreted in a column expression situation
1388 key = _label = _resolve_label = None
1390 _allow_label_resolve = False
1392 def __init__(self, text, bind=None):
1393 self._bind = bind
1394 self._bindparams = {}
1396 def repl(m):
1397 self._bindparams[m.group(1)] = BindParameter(m.group(1))
1398 return ":%s" % m.group(1)
1400 # scan the string and search for bind parameter names, add them
1401 # to the list of bindparams
1402 self.text = self._bind_params_regex.sub(repl, text)
1404 @classmethod
1405 @util.deprecated_params(
1406 autocommit=(
1407 "0.6",
1408 "The :paramref:`_expression.text.autocommit` "
1409 "parameter is deprecated and "
1410 "will be removed in a future release. Please use the "
1411 ":paramref:`.Connection.execution_options.autocommit` parameter "
1412 "in conjunction with the :meth:`.Executable.execution_options` "
1413 "method.",
1414 ),
1415 bindparams=(
1416 "0.9",
1417 "The :paramref:`_expression.text.bindparams` parameter "
1418 "is deprecated and will be removed in a future release. Please "
1419 "refer to the :meth:`_expression.TextClause.bindparams` method.",
1420 ),
1421 typemap=(
1422 "0.9",
1423 "The :paramref:`_expression.text.typemap` parameter is "
1424 "deprecated and will be removed in a future release. Please "
1425 "refer to the :meth:`_expression.TextClause.columns` method.",
1426 ),
1427 )
1428 @_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1429 def _create_text(
1430 self, text, bind=None, bindparams=None, typemap=None, autocommit=None
1431 ):
1432 r"""Construct a new :class:`_expression.TextClause` clause,
1433 representing
1434 a textual SQL string directly.
1436 E.g.::
1438 from sqlalchemy import text
1440 t = text("SELECT * FROM users")
1441 result = connection.execute(t)
1443 The advantages :func:`_expression.text`
1444 provides over a plain string are
1445 backend-neutral support for bind parameters, per-statement
1446 execution options, as well as
1447 bind parameter and result-column typing behavior, allowing
1448 SQLAlchemy type constructs to play a role when executing
1449 a statement that is specified literally. The construct can also
1450 be provided with a ``.c`` collection of column elements, allowing
1451 it to be embedded in other SQL expression constructs as a subquery.
1453 Bind parameters are specified by name, using the format ``:name``.
1454 E.g.::
1456 t = text("SELECT * FROM users WHERE id=:user_id")
1457 result = connection.execute(t, user_id=12)
1459 For SQL statements where a colon is required verbatim, as within
1460 an inline string, use a backslash to escape::
1462 t = text("SELECT * FROM users WHERE name='\:username'")
1464 The :class:`_expression.TextClause`
1465 construct includes methods which can
1466 provide information about the bound parameters as well as the column
1467 values which would be returned from the textual statement, assuming
1468 it's an executable SELECT type of statement. The
1469 :meth:`_expression.TextClause.bindparams`
1470 method is used to provide bound
1471 parameter detail, and :meth:`_expression.TextClause.columns`
1472 method allows
1473 specification of return columns including names and types::
1475 t = text("SELECT * FROM users WHERE id=:user_id").\
1476 bindparams(user_id=7).\
1477 columns(id=Integer, name=String)
1479 for id, name in connection.execute(t):
1480 print(id, name)
1482 The :func:`_expression.text` construct is used in cases when
1483 a literal string SQL fragment is specified as part of a larger query,
1484 such as for the WHERE clause of a SELECT statement::
1486 s = select([users.c.id, users.c.name]).where(text("id=:user_id"))
1487 result = connection.execute(s, user_id=12)
1489 :func:`_expression.text` is also used for the construction
1490 of a full, standalone statement using plain text.
1491 As such, SQLAlchemy refers
1492 to it as an :class:`.Executable` object, and it supports
1493 the :meth:`Executable.execution_options` method. For example,
1494 a :func:`_expression.text`
1495 construct that should be subject to "autocommit"
1496 can be set explicitly so using the
1497 :paramref:`.Connection.execution_options.autocommit` option::
1499 t = text("EXEC my_procedural_thing()").\
1500 execution_options(autocommit=True)
1502 Note that SQLAlchemy's usual "autocommit" behavior applies to
1503 :func:`_expression.text` constructs implicitly - that is,
1504 statements which begin
1505 with a phrase such as ``INSERT``, ``UPDATE``, ``DELETE``,
1506 or a variety of other phrases specific to certain backends, will
1507 be eligible for autocommit if no transaction is in progress.
1509 :param text:
1510 the text of the SQL statement to be created. use ``:<param>``
1511 to specify bind parameters; they will be compiled to their
1512 engine-specific format.
1514 :param autocommit: whether or not to set the "autocommit" execution
1515 option for this :class:`_expression.TextClause` object.
1517 :param bind:
1518 an optional connection or engine to be used for this text query.
1520 :param bindparams:
1521 A list of :func:`.bindparam` instances used to
1522 provide information about parameters embedded in the statement.
1524 E.g.::
1526 stmt = text("SELECT * FROM table WHERE id=:id",
1527 bindparams=[bindparam('id', value=5, type_=Integer)])
1529 :param typemap:
1530 A dictionary mapping the names of columns represented in the columns
1531 clause of a ``SELECT`` statement to type objects.
1533 E.g.::
1535 stmt = text("SELECT * FROM table",
1536 typemap={'id': Integer, 'name': String},
1537 )
1539 .. seealso::
1541 :ref:`sqlexpression_text` - in the Core tutorial
1543 :ref:`orm_tutorial_literal_sql` - in the ORM tutorial
1545 """
1546 stmt = TextClause(text, bind=bind)
1547 if bindparams:
1548 stmt = stmt.bindparams(*bindparams)
1549 if typemap:
1550 stmt = stmt.columns(**typemap)
1551 if autocommit is not None:
1552 stmt = stmt.execution_options(autocommit=autocommit)
1554 return stmt
1556 @_generative
1557 def bindparams(self, *binds, **names_to_values):
1558 """Establish the values and/or types of bound parameters within
1559 this :class:`_expression.TextClause` construct.
1561 Given a text construct such as::
1563 from sqlalchemy import text
1564 stmt = text("SELECT id, name FROM user WHERE name=:name "
1565 "AND timestamp=:timestamp")
1567 the :meth:`_expression.TextClause.bindparams`
1568 method can be used to establish
1569 the initial value of ``:name`` and ``:timestamp``,
1570 using simple keyword arguments::
1572 stmt = stmt.bindparams(name='jack',
1573 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5))
1575 Where above, new :class:`.BindParameter` objects
1576 will be generated with the names ``name`` and ``timestamp``, and
1577 values of ``jack`` and ``datetime.datetime(2012, 10, 8, 15, 12, 5)``,
1578 respectively. The types will be
1579 inferred from the values given, in this case :class:`.String` and
1580 :class:`.DateTime`.
1582 When specific typing behavior is needed, the positional ``*binds``
1583 argument can be used in which to specify :func:`.bindparam` constructs
1584 directly. These constructs must include at least the ``key``
1585 argument, then an optional value and type::
1587 from sqlalchemy import bindparam
1588 stmt = stmt.bindparams(
1589 bindparam('name', value='jack', type_=String),
1590 bindparam('timestamp', type_=DateTime)
1591 )
1593 Above, we specified the type of :class:`.DateTime` for the
1594 ``timestamp`` bind, and the type of :class:`.String` for the ``name``
1595 bind. In the case of ``name`` we also set the default value of
1596 ``"jack"``.
1598 Additional bound parameters can be supplied at statement execution
1599 time, e.g.::
1601 result = connection.execute(stmt,
1602 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5))
1604 The :meth:`_expression.TextClause.bindparams`
1605 method can be called repeatedly,
1606 where it will re-use existing :class:`.BindParameter` objects to add
1607 new information. For example, we can call
1608 :meth:`_expression.TextClause.bindparams`
1609 first with typing information, and a
1610 second time with value information, and it will be combined::
1612 stmt = text("SELECT id, name FROM user WHERE name=:name "
1613 "AND timestamp=:timestamp")
1614 stmt = stmt.bindparams(
1615 bindparam('name', type_=String),
1616 bindparam('timestamp', type_=DateTime)
1617 )
1618 stmt = stmt.bindparams(
1619 name='jack',
1620 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5)
1621 )
1623 The :meth:`_expression.TextClause.bindparams`
1624 method also supports the concept of
1625 **unique** bound parameters. These are parameters that are
1626 "uniquified" on name at statement compilation time, so that multiple
1627 :func:`_expression.text`
1628 constructs may be combined together without the names
1629 conflicting. To use this feature, specify the
1630 :paramref:`.BindParameter.unique` flag on each :func:`.bindparam`
1631 object::
1633 stmt1 = text("select id from table where name=:name").bindparams(
1634 bindparam("name", value='name1', unique=True)
1635 )
1636 stmt2 = text("select id from table where name=:name").bindparams(
1637 bindparam("name", value='name2', unique=True)
1638 )
1640 union = union_all(
1641 stmt1.columns(column("id")),
1642 stmt2.columns(column("id"))
1643 )
1645 The above statement will render as::
1647 select id from table where name=:name_1
1648 UNION ALL select id from table where name=:name_2
1650 .. versionadded:: 1.3.11 Added support for the
1651 :paramref:`.BindParameter.unique` flag to work with
1652 :func:`_expression.text`
1653 constructs.
1655 """
1656 self._bindparams = new_params = self._bindparams.copy()
1658 for bind in binds:
1659 try:
1660 # the regex used for text() currently will not match
1661 # a unique/anonymous key in any case, so use the _orig_key
1662 # so that a text() construct can support unique parameters
1663 existing = new_params[bind._orig_key]
1664 except KeyError as err:
1665 util.raise_(
1666 exc.ArgumentError(
1667 "This text() construct doesn't define a "
1668 "bound parameter named %r" % bind._orig_key
1669 ),
1670 replace_context=err,
1671 )
1672 else:
1673 new_params[existing._orig_key] = bind
1675 for key, value in names_to_values.items():
1676 try:
1677 existing = new_params[key]
1678 except KeyError as err:
1679 util.raise_(
1680 exc.ArgumentError(
1681 "This text() construct doesn't define a "
1682 "bound parameter named %r" % key
1683 ),
1684 replace_context=err,
1685 )
1686 else:
1687 new_params[key] = existing._with_value(value)
1689 @util.dependencies("sqlalchemy.sql.selectable")
1690 def columns(self, selectable, *cols, **types):
1691 """Turn this :class:`_expression.TextClause` object into a
1692 :class:`.TextAsFrom`
1693 object that can be embedded into another statement.
1695 This function essentially bridges the gap between an entirely
1696 textual SELECT statement and the SQL expression language concept
1697 of a "selectable"::
1699 from sqlalchemy.sql import column, text
1701 stmt = text("SELECT id, name FROM some_table")
1702 stmt = stmt.columns(column('id'), column('name')).alias('st')
1704 stmt = select([mytable]).\
1705 select_from(
1706 mytable.join(stmt, mytable.c.name == stmt.c.name)
1707 ).where(stmt.c.id > 5)
1709 Above, we pass a series of :func:`_expression.column` elements to the
1710 :meth:`_expression.TextClause.columns` method positionally. These
1711 :func:`_expression.column`
1712 elements now become first class elements upon the :attr:`.TextAsFrom.c`
1713 column collection, just like any other selectable.
1715 The column expressions we pass to
1716 :meth:`_expression.TextClause.columns` may
1717 also be typed; when we do so, these :class:`.TypeEngine` objects become
1718 the effective return type of the column, so that SQLAlchemy's
1719 result-set-processing systems may be used on the return values.
1720 This is often needed for types such as date or boolean types, as well
1721 as for unicode processing on some dialect configurations::
1723 stmt = text("SELECT id, name, timestamp FROM some_table")
1724 stmt = stmt.columns(
1725 column('id', Integer),
1726 column('name', Unicode),
1727 column('timestamp', DateTime)
1728 )
1730 for id, name, timestamp in connection.execute(stmt):
1731 print(id, name, timestamp)
1733 As a shortcut to the above syntax, keyword arguments referring to
1734 types alone may be used, if only type conversion is needed::
1736 stmt = text("SELECT id, name, timestamp FROM some_table")
1737 stmt = stmt.columns(
1738 id=Integer,
1739 name=Unicode,
1740 timestamp=DateTime
1741 )
1743 for id, name, timestamp in connection.execute(stmt):
1744 print(id, name, timestamp)
1746 The positional form of :meth:`_expression.TextClause.columns`
1747 also provides the
1748 unique feature of **positional column targeting**, which is
1749 particularly useful when using the ORM with complex textual queries. If
1750 we specify the columns from our model to
1751 :meth:`_expression.TextClause.columns`,
1752 the result set will match to those columns positionally, meaning the
1753 name or origin of the column in the textual SQL doesn't matter::
1755 stmt = text("SELECT users.id, addresses.id, users.id, "
1756 "users.name, addresses.email_address AS email "
1757 "FROM users JOIN addresses ON users.id=addresses.user_id "
1758 "WHERE users.id = 1").columns(
1759 User.id,
1760 Address.id,
1761 Address.user_id,
1762 User.name,
1763 Address.email_address
1764 )
1766 query = session.query(User).from_statement(stmt).options(
1767 contains_eager(User.addresses))
1769 .. versionadded:: 1.1 the :meth:`_expression.TextClause.columns`
1770 method now
1771 offers positional column targeting in the result set when
1772 the column expressions are passed purely positionally.
1774 The :meth:`_expression.TextClause.columns` method provides a direct
1775 route to calling :meth:`_expression.FromClause.alias` as well as
1776 :meth:`_expression.SelectBase.cte`
1777 against a textual SELECT statement::
1779 stmt = stmt.columns(id=Integer, name=String).cte('st')
1781 stmt = select([sometable]).where(sometable.c.id == stmt.c.id)
1783 .. versionadded:: 0.9.0 :func:`_expression.text`
1784 can now be converted into a
1785 fully featured "selectable" construct using the
1786 :meth:`_expression.TextClause.columns` method.
1789 """
1791 positional_input_cols = [
1792 ColumnClause(col.key, types.pop(col.key))
1793 if col.key in types
1794 else col
1795 for col in cols
1796 ]
1797 keyed_input_cols = [
1798 ColumnClause(key, type_) for key, type_ in types.items()
1799 ]
1801 return selectable.TextAsFrom(
1802 self,
1803 positional_input_cols + keyed_input_cols,
1804 positional=bool(positional_input_cols) and not keyed_input_cols,
1805 )
1807 @property
1808 def type(self):
1809 return type_api.NULLTYPE
1811 @property
1812 def comparator(self):
1813 return self.type.comparator_factory(self)
1815 def self_group(self, against=None):
1816 if against is operators.in_op:
1817 return Grouping(self)
1818 else:
1819 return self
1821 def _copy_internals(self, clone=_clone, **kw):
1822 self._bindparams = dict(
1823 (b.key, clone(b, **kw)) for b in self._bindparams.values()
1824 )
1826 def get_children(self, **kwargs):
1827 return list(self._bindparams.values())
1829 def compare(self, other):
1830 return isinstance(other, TextClause) and other.text == self.text
1833class Null(ColumnElement):
1834 """Represent the NULL keyword in a SQL statement.
1836 :class:`.Null` is accessed as a constant via the
1837 :func:`.null` function.
1839 """
1841 __visit_name__ = "null"
1843 @util.memoized_property
1844 def type(self):
1845 return type_api.NULLTYPE
1847 @classmethod
1848 def _instance(cls):
1849 """Return a constant :class:`.Null` construct."""
1851 return Null()
1853 def compare(self, other):
1854 return isinstance(other, Null)
1857class False_(ColumnElement):
1858 """Represent the ``false`` keyword, or equivalent, in a SQL statement.
1860 :class:`.False_` is accessed as a constant via the
1861 :func:`.false` function.
1863 """
1865 __visit_name__ = "false"
1867 @util.memoized_property
1868 def type(self):
1869 return type_api.BOOLEANTYPE
1871 def _negate(self):
1872 return True_()
1874 @classmethod
1875 def _instance(cls):
1876 """Return a :class:`.False_` construct.
1878 E.g.::
1880 >>> from sqlalchemy import false
1881 >>> print(select([t.c.x]).where(false()))
1882 SELECT x FROM t WHERE false
1884 A backend which does not support true/false constants will render as
1885 an expression against 1 or 0::
1887 >>> print(select([t.c.x]).where(false()))
1888 SELECT x FROM t WHERE 0 = 1
1890 The :func:`.true` and :func:`.false` constants also feature
1891 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1892 conjunction::
1894 >>> print(select([t.c.x]).where(or_(t.c.x > 5, true())))
1895 SELECT x FROM t WHERE true
1897 >>> print(select([t.c.x]).where(and_(t.c.x > 5, false())))
1898 SELECT x FROM t WHERE false
1900 .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature
1901 better integrated behavior within conjunctions and on dialects
1902 that don't support true/false constants.
1904 .. seealso::
1906 :func:`.true`
1908 """
1910 return False_()
1912 def compare(self, other):
1913 return isinstance(other, False_)
1916class True_(ColumnElement):
1917 """Represent the ``true`` keyword, or equivalent, in a SQL statement.
1919 :class:`.True_` is accessed as a constant via the
1920 :func:`.true` function.
1922 """
1924 __visit_name__ = "true"
1926 @util.memoized_property
1927 def type(self):
1928 return type_api.BOOLEANTYPE
1930 def _negate(self):
1931 return False_()
1933 @classmethod
1934 def _ifnone(cls, other):
1935 if other is None:
1936 return cls._instance()
1937 else:
1938 return other
1940 @classmethod
1941 def _instance(cls):
1942 """Return a constant :class:`.True_` construct.
1944 E.g.::
1946 >>> from sqlalchemy import true
1947 >>> print(select([t.c.x]).where(true()))
1948 SELECT x FROM t WHERE true
1950 A backend which does not support true/false constants will render as
1951 an expression against 1 or 0::
1953 >>> print(select([t.c.x]).where(true()))
1954 SELECT x FROM t WHERE 1 = 1
1956 The :func:`.true` and :func:`.false` constants also feature
1957 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1958 conjunction::
1960 >>> print(select([t.c.x]).where(or_(t.c.x > 5, true())))
1961 SELECT x FROM t WHERE true
1963 >>> print(select([t.c.x]).where(and_(t.c.x > 5, false())))
1964 SELECT x FROM t WHERE false
1966 .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature
1967 better integrated behavior within conjunctions and on dialects
1968 that don't support true/false constants.
1970 .. seealso::
1972 :func:`.false`
1974 """
1976 return True_()
1978 def compare(self, other):
1979 return isinstance(other, True_)
1982class ClauseList(ClauseElement):
1983 """Describe a list of clauses, separated by an operator.
1985 By default, is comma-separated, such as a column listing.
1987 """
1989 __visit_name__ = "clauselist"
1991 def __init__(self, *clauses, **kwargs):
1992 self.operator = kwargs.pop("operator", operators.comma_op)
1993 self.group = kwargs.pop("group", True)
1994 self.group_contents = kwargs.pop("group_contents", True)
1995 self._tuple_values = kwargs.pop("_tuple_values", False)
1996 text_converter = kwargs.pop(
1997 "_literal_as_text", _expression_literal_as_text
1998 )
1999 if self.group_contents:
2000 self.clauses = [
2001 text_converter(clause).self_group(against=self.operator)
2002 for clause in clauses
2003 ]
2004 else:
2005 self.clauses = [text_converter(clause) for clause in clauses]
2006 self._is_implicitly_boolean = operators.is_boolean(self.operator)
2008 def __iter__(self):
2009 return iter(self.clauses)
2011 def __len__(self):
2012 return len(self.clauses)
2014 @property
2015 def _select_iterable(self):
2016 return iter(self)
2018 def append(self, clause):
2019 if self.group_contents:
2020 self.clauses.append(
2021 _literal_as_text(clause).self_group(against=self.operator)
2022 )
2023 else:
2024 self.clauses.append(_literal_as_text(clause))
2026 def _copy_internals(self, clone=_clone, **kw):
2027 self.clauses = [clone(clause, **kw) for clause in self.clauses]
2029 def get_children(self, **kwargs):
2030 return self.clauses
2032 @property
2033 def _from_objects(self):
2034 return list(itertools.chain(*[c._from_objects for c in self.clauses]))
2036 def self_group(self, against=None):
2037 if self.group and operators.is_precedent(self.operator, against):
2038 return Grouping(self)
2039 else:
2040 return self
2042 def compare(self, other, **kw):
2043 """Compare this :class:`.ClauseList` to the given :class:`.ClauseList`,
2044 including a comparison of all the clause items.
2046 """
2047 if not isinstance(other, ClauseList) and len(self.clauses) == 1:
2048 return self.clauses[0].compare(other, **kw)
2049 elif (
2050 isinstance(other, ClauseList)
2051 and len(self.clauses) == len(other.clauses)
2052 and self.operator is other.operator
2053 ):
2055 if self.operator in (operators.and_, operators.or_):
2056 completed = set()
2057 for clause in self.clauses:
2058 for other_clause in set(other.clauses).difference(
2059 completed
2060 ):
2061 if clause.compare(other_clause, **kw):
2062 completed.add(other_clause)
2063 break
2064 return len(completed) == len(other.clauses)
2065 else:
2066 for i in range(0, len(self.clauses)):
2067 if not self.clauses[i].compare(other.clauses[i], **kw):
2068 return False
2069 else:
2070 return True
2071 else:
2072 return False
2075class BooleanClauseList(ClauseList, ColumnElement):
2076 __visit_name__ = "clauselist"
2078 _tuple_values = False
2080 def __init__(self, *arg, **kw):
2081 raise NotImplementedError(
2082 "BooleanClauseList has a private constructor"
2083 )
2085 @classmethod
2086 def _construct(cls, operator, continue_on, skip_on, *clauses, **kw):
2087 convert_clauses = []
2089 clauses = [
2090 _expression_literal_as_text(clause)
2091 for clause in util.coerce_generator_arg(clauses)
2092 ]
2093 for clause in clauses:
2095 if isinstance(clause, continue_on):
2096 continue
2097 elif isinstance(clause, skip_on):
2098 return clause.self_group(against=operators._asbool)
2100 convert_clauses.append(clause)
2102 if len(convert_clauses) == 1:
2103 return convert_clauses[0].self_group(against=operators._asbool)
2104 elif not convert_clauses and clauses:
2105 return clauses[0].self_group(against=operators._asbool)
2107 convert_clauses = [
2108 c.self_group(against=operator) for c in convert_clauses
2109 ]
2111 self = cls.__new__(cls)
2112 self.clauses = convert_clauses
2113 self.group = True
2114 self.operator = operator
2115 self.group_contents = True
2116 self.type = type_api.BOOLEANTYPE
2117 self._is_implicitly_boolean = True
2118 return self
2120 @classmethod
2121 def and_(cls, *clauses):
2122 """Produce a conjunction of expressions joined by ``AND``.
2124 E.g.::
2126 from sqlalchemy import and_
2128 stmt = select([users_table]).where(
2129 and_(
2130 users_table.c.name == 'wendy',
2131 users_table.c.enrolled == True
2132 )
2133 )
2135 The :func:`.and_` conjunction is also available using the
2136 Python ``&`` operator (though note that compound expressions
2137 need to be parenthesized in order to function with Python
2138 operator precedence behavior)::
2140 stmt = select([users_table]).where(
2141 (users_table.c.name == 'wendy') &
2142 (users_table.c.enrolled == True)
2143 )
2145 The :func:`.and_` operation is also implicit in some cases;
2146 the :meth:`_expression.Select.where`
2147 method for example can be invoked multiple
2148 times against a statement, which will have the effect of each
2149 clause being combined using :func:`.and_`::
2151 stmt = select([users_table]).\
2152 where(users_table.c.name == 'wendy').\
2153 where(users_table.c.enrolled == True)
2155 .. seealso::
2157 :func:`.or_`
2159 """
2160 return cls._construct(operators.and_, True_, False_, *clauses)
2162 @classmethod
2163 def or_(cls, *clauses):
2164 """Produce a conjunction of expressions joined by ``OR``.
2166 E.g.::
2168 from sqlalchemy import or_
2170 stmt = select([users_table]).where(
2171 or_(
2172 users_table.c.name == 'wendy',
2173 users_table.c.name == 'jack'
2174 )
2175 )
2177 The :func:`.or_` conjunction is also available using the
2178 Python ``|`` operator (though note that compound expressions
2179 need to be parenthesized in order to function with Python
2180 operator precedence behavior)::
2182 stmt = select([users_table]).where(
2183 (users_table.c.name == 'wendy') |
2184 (users_table.c.name == 'jack')
2185 )
2187 .. seealso::
2189 :func:`.and_`
2191 """
2192 return cls._construct(operators.or_, False_, True_, *clauses)
2194 @property
2195 def _select_iterable(self):
2196 return (self,)
2198 def self_group(self, against=None):
2199 if not self.clauses:
2200 return self
2201 else:
2202 return super(BooleanClauseList, self).self_group(against=against)
2204 def _negate(self):
2205 return ClauseList._negate(self)
2208and_ = BooleanClauseList.and_
2209or_ = BooleanClauseList.or_
2212class Tuple(ClauseList, ColumnElement):
2213 """Represent a SQL tuple."""
2215 def __init__(self, *clauses, **kw):
2216 """Return a :class:`.Tuple`.
2218 Main usage is to produce a composite IN construct using
2219 :meth:`.ColumnOperators.in_` ::
2221 from sqlalchemy import tuple_
2223 tuple_(table.c.col1, table.c.col2).in_(
2224 [(1, 2), (5, 12), (10, 19)]
2225 )
2227 .. versionchanged:: 1.3.6 Added support for SQLite IN tuples.
2229 .. warning::
2231 The composite IN construct is not supported by all backends, and is
2232 currently known to work on PostgreSQL, MySQL, and SQLite.
2233 Unsupported backends will raise a subclass of
2234 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
2235 invoked.
2237 """
2239 clauses = [_literal_as_binds(c) for c in clauses]
2240 self._type_tuple = [arg.type for arg in clauses]
2241 self.type = kw.pop(
2242 "type_",
2243 self._type_tuple[0] if self._type_tuple else type_api.NULLTYPE,
2244 )
2246 super(Tuple, self).__init__(*clauses, **kw)
2248 @property
2249 def _select_iterable(self):
2250 return (self,)
2252 def _bind_param(self, operator, obj, type_=None):
2253 return Tuple(
2254 *[
2255 BindParameter(
2256 None,
2257 o,
2258 _compared_to_operator=operator,
2259 _compared_to_type=compared_to_type,
2260 unique=True,
2261 type_=type_,
2262 )
2263 for o, compared_to_type in zip(obj, self._type_tuple)
2264 ]
2265 ).self_group()
2268class Case(ColumnElement):
2269 """Represent a ``CASE`` expression.
2271 :class:`.Case` is produced using the :func:`.case` factory function,
2272 as in::
2274 from sqlalchemy import case
2276 stmt = select([users_table]).\
2277 where(
2278 case(
2279 [
2280 (users_table.c.name == 'wendy', 'W'),
2281 (users_table.c.name == 'jack', 'J')
2282 ],
2283 else_='E'
2284 )
2285 )
2287 Details on :class:`.Case` usage is at :func:`.case`.
2289 .. seealso::
2291 :func:`.case`
2293 """
2295 __visit_name__ = "case"
2297 def __init__(self, whens, value=None, else_=None):
2298 r"""Produce a ``CASE`` expression.
2300 The ``CASE`` construct in SQL is a conditional object that
2301 acts somewhat analogously to an "if/then" construct in other
2302 languages. It returns an instance of :class:`.Case`.
2304 :func:`.case` in its usual form is passed a list of "when"
2305 constructs, that is, a list of conditions and results as tuples::
2307 from sqlalchemy import case
2309 stmt = select([users_table]).\
2310 where(
2311 case(
2312 [
2313 (users_table.c.name == 'wendy', 'W'),
2314 (users_table.c.name == 'jack', 'J')
2315 ],
2316 else_='E'
2317 )
2318 )
2320 The above statement will produce SQL resembling::
2322 SELECT id, name FROM user
2323 WHERE CASE
2324 WHEN (name = :name_1) THEN :param_1
2325 WHEN (name = :name_2) THEN :param_2
2326 ELSE :param_3
2327 END
2329 When simple equality expressions of several values against a single
2330 parent column are needed, :func:`.case` also has a "shorthand" format
2331 used via the
2332 :paramref:`.case.value` parameter, which is passed a column
2333 expression to be compared. In this form, the :paramref:`.case.whens`
2334 parameter is passed as a dictionary containing expressions to be
2335 compared against keyed to result expressions. The statement below is
2336 equivalent to the preceding statement::
2338 stmt = select([users_table]).\
2339 where(
2340 case(
2341 {"wendy": "W", "jack": "J"},
2342 value=users_table.c.name,
2343 else_='E'
2344 )
2345 )
2347 The values which are accepted as result values in
2348 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
2349 coerced from Python literals into :func:`.bindparam` constructs.
2350 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
2351 are accepted
2352 as well. To coerce a literal string expression into a constant
2353 expression rendered inline, use the :func:`_expression.literal_column`
2354 construct,
2355 as in::
2357 from sqlalchemy import case, literal_column
2359 case(
2360 [
2361 (
2362 orderline.c.qty > 100,
2363 literal_column("'greaterthan100'")
2364 ),
2365 (
2366 orderline.c.qty > 10,
2367 literal_column("'greaterthan10'")
2368 )
2369 ],
2370 else_=literal_column("'lessthan10'")
2371 )
2373 The above will render the given constants without using bound
2374 parameters for the result values (but still for the comparison
2375 values), as in::
2377 CASE
2378 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
2379 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
2380 ELSE 'lessthan10'
2381 END
2383 :param whens: The criteria to be compared against,
2384 :paramref:`.case.whens` accepts two different forms, based on
2385 whether or not :paramref:`.case.value` is used.
2387 In the first form, it accepts a list of 2-tuples; each 2-tuple
2388 consists of ``(<sql expression>, <value>)``, where the SQL
2389 expression is a boolean expression and "value" is a resulting value,
2390 e.g.::
2392 case([
2393 (users_table.c.name == 'wendy', 'W'),
2394 (users_table.c.name == 'jack', 'J')
2395 ])
2397 In the second form, it accepts a Python dictionary of comparison
2398 values mapped to a resulting value; this form requires
2399 :paramref:`.case.value` to be present, and values will be compared
2400 using the ``==`` operator, e.g.::
2402 case(
2403 {"wendy": "W", "jack": "J"},
2404 value=users_table.c.name
2405 )
2407 :param value: An optional SQL expression which will be used as a
2408 fixed "comparison point" for candidate values within a dictionary
2409 passed to :paramref:`.case.whens`.
2411 :param else\_: An optional SQL expression which will be the evaluated
2412 result of the ``CASE`` construct if all expressions within
2413 :paramref:`.case.whens` evaluate to false. When omitted, most
2414 databases will produce a result of NULL if none of the "when"
2415 expressions evaluate to true.
2418 """
2420 try:
2421 whens = util.dictlike_iteritems(whens)
2422 except TypeError:
2423 pass
2425 if value is not None:
2426 whenlist = [
2427 (_literal_as_binds(c).self_group(), _literal_as_binds(r))
2428 for (c, r) in whens
2429 ]
2430 else:
2431 whenlist = [
2432 (_no_literals(c).self_group(), _literal_as_binds(r))
2433 for (c, r) in whens
2434 ]
2436 if whenlist:
2437 type_ = list(whenlist[-1])[-1].type
2438 else:
2439 type_ = None
2441 if value is None:
2442 self.value = None
2443 else:
2444 self.value = _literal_as_binds(value)
2446 self.type = type_
2447 self.whens = whenlist
2448 if else_ is not None:
2449 self.else_ = _literal_as_binds(else_)
2450 else:
2451 self.else_ = None
2453 def _copy_internals(self, clone=_clone, **kw):
2454 if self.value is not None:
2455 self.value = clone(self.value, **kw)
2456 self.whens = [(clone(x, **kw), clone(y, **kw)) for x, y in self.whens]
2457 if self.else_ is not None:
2458 self.else_ = clone(self.else_, **kw)
2460 def get_children(self, **kwargs):
2461 if self.value is not None:
2462 yield self.value
2463 for x, y in self.whens:
2464 yield x
2465 yield y
2466 if self.else_ is not None:
2467 yield self.else_
2469 @property
2470 def _from_objects(self):
2471 return list(
2472 itertools.chain(*[x._from_objects for x in self.get_children()])
2473 )
2476def literal_column(text, type_=None):
2477 r"""Produce a :class:`.ColumnClause` object that has the
2478 :paramref:`_expression.column.is_literal` flag set to True.
2480 :func:`_expression.literal_column` is similar to
2481 :func:`_expression.column`, except that
2482 it is more often used as a "standalone" column expression that renders
2483 exactly as stated; while :func:`_expression.column`
2484 stores a string name that
2485 will be assumed to be part of a table and may be quoted as such,
2486 :func:`_expression.literal_column` can be that,
2487 or any other arbitrary column-oriented
2488 expression.
2490 :param text: the text of the expression; can be any SQL expression.
2491 Quoting rules will not be applied. To specify a column-name expression
2492 which should be subject to quoting rules, use the :func:`column`
2493 function.
2495 :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine`
2496 object which will
2497 provide result-set translation and additional expression semantics for
2498 this column. If left as None the type will be NullType.
2500 .. seealso::
2502 :func:`_expression.column`
2504 :func:`_expression.text`
2506 :ref:`sqlexpression_literal_column`
2508 """
2509 return ColumnClause(text, type_=type_, is_literal=True)
2512class Cast(ColumnElement):
2513 """Represent a ``CAST`` expression.
2515 :class:`.Cast` is produced using the :func:`.cast` factory function,
2516 as in::
2518 from sqlalchemy import cast, Numeric
2520 stmt = select([
2521 cast(product_table.c.unit_price, Numeric(10, 4))
2522 ])
2524 Details on :class:`.Cast` usage is at :func:`.cast`.
2526 .. seealso::
2528 :ref:`coretutorial_casts`
2530 :func:`.cast`
2532 :func:`.type_coerce` - an alternative to CAST that coerces the type
2533 on the Python side only, which is often sufficient to generate the
2534 correct SQL and data coercion.
2536 """
2538 __visit_name__ = "cast"
2540 def __init__(self, expression, type_):
2541 r"""Produce a ``CAST`` expression.
2543 :func:`.cast` returns an instance of :class:`.Cast`.
2545 E.g.::
2547 from sqlalchemy import cast, Numeric
2549 stmt = select([
2550 cast(product_table.c.unit_price, Numeric(10, 4))
2551 ])
2553 The above statement will produce SQL resembling::
2555 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
2557 The :func:`.cast` function performs two distinct functions when
2558 used. The first is that it renders the ``CAST`` expression within
2559 the resulting SQL string. The second is that it associates the given
2560 type (e.g. :class:`.TypeEngine` class or instance) with the column
2561 expression on the Python side, which means the expression will take
2562 on the expression operator behavior associated with that type,
2563 as well as the bound-value handling and result-row-handling behavior
2564 of the type.
2566 .. versionchanged:: 0.9.0 :func:`.cast` now applies the given type
2567 to the expression such that it takes effect on the bound-value,
2568 e.g. the Python-to-database direction, in addition to the
2569 result handling, e.g. database-to-Python, direction.
2571 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
2572 This function performs the second task of associating an expression
2573 with a specific type, but does not render the ``CAST`` expression
2574 in SQL.
2576 :param expression: A SQL expression, such as a
2577 :class:`_expression.ColumnElement`
2578 expression or a Python string which will be coerced into a bound
2579 literal value.
2581 :param type\_: A :class:`.TypeEngine` class or instance indicating
2582 the type to which the ``CAST`` should apply.
2584 .. seealso::
2586 :ref:`coretutorial_casts`
2588 :func:`.type_coerce` - an alternative to CAST that coerces the type
2589 on the Python side only, which is often sufficient to generate the
2590 correct SQL and data coercion.
2593 """
2594 self.type = type_api.to_instance(type_)
2595 self.clause = _literal_as_binds(expression, type_=self.type)
2596 self.typeclause = TypeClause(self.type)
2598 def _copy_internals(self, clone=_clone, **kw):
2599 self.clause = clone(self.clause, **kw)
2600 self.typeclause = clone(self.typeclause, **kw)
2602 def get_children(self, **kwargs):
2603 return self.clause, self.typeclause
2605 @property
2606 def _from_objects(self):
2607 return self.clause._from_objects
2610class TypeCoerce(ColumnElement):
2611 """Represent a Python-side type-coercion wrapper.
2613 :class:`.TypeCoerce` supplies the :func:`_expression.type_coerce`
2614 function; see that function for usage details.
2616 .. versionchanged:: 1.1 The :func:`.type_coerce` function now produces
2617 a persistent :class:`.TypeCoerce` wrapper object rather than
2618 translating the given object in place.
2620 .. seealso::
2622 :func:`_expression.type_coerce`
2624 :func:`.cast`
2626 """
2628 __visit_name__ = "type_coerce"
2630 def __init__(self, expression, type_):
2631 r"""Associate a SQL expression with a particular type, without rendering
2632 ``CAST``.
2634 E.g.::
2636 from sqlalchemy import type_coerce
2638 stmt = select([type_coerce(log_table.date_string, StringDateTime())])
2640 The above construct will produce a :class:`.TypeCoerce` object, which
2641 does not modify the rendering in any way on the SQL side, with the
2642 possible exception of a generated label if used in a columns clause
2643 context::
2645 SELECT date_string AS anon_1 FROM log
2647 When result rows are fetched, the ``StringDateTime`` type processor
2648 will be applied to result rows on behalf of the ``date_string`` column.
2650 .. note:: the :func:`.type_coerce` construct does not render any
2651 SQL syntax of its own, including that it does not imply
2652 parenthesization. Please use :meth:`.TypeCoerce.self_group`
2653 if explicit parenthesization is required.
2655 In order to provide a named label for the expression, use
2656 :meth:`_expression.ColumnElement.label`::
2658 stmt = select([
2659 type_coerce(log_table.date_string, StringDateTime()).label('date')
2660 ])
2663 A type that features bound-value handling will also have that behavior
2664 take effect when literal values or :func:`.bindparam` constructs are
2665 passed to :func:`.type_coerce` as targets.
2666 For example, if a type implements the
2667 :meth:`.TypeEngine.bind_expression`
2668 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
2669 these functions will take effect at statement compilation/execution
2670 time when a literal value is passed, as in::
2672 # bound-value handling of MyStringType will be applied to the
2673 # literal value "some string"
2674 stmt = select([type_coerce("some string", MyStringType)])
2676 When using :func:`.type_coerce` with composed expressions, note that
2677 **parenthesis are not applied**. If :func:`.type_coerce` is being
2678 used in an operator context where the parenthesis normally present from
2679 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method::
2681 >>> some_integer = column("someint", Integer)
2682 >>> some_string = column("somestr", String)
2683 >>> expr = type_coerce(some_integer + 5, String) + some_string
2684 >>> print(expr)
2685 someint + :someint_1 || somestr
2686 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
2687 >>> print(expr)
2688 (someint + :someint_1) || somestr
2690 :param expression: A SQL expression, such as a
2691 :class:`_expression.ColumnElement`
2692 expression or a Python string which will be coerced into a bound
2693 literal value.
2695 :param type\_: A :class:`.TypeEngine` class or instance indicating
2696 the type to which the expression is coerced.
2698 .. seealso::
2700 :ref:`coretutorial_casts`
2702 :func:`.cast`
2704 """ # noqa
2705 self.type = type_api.to_instance(type_)
2706 self.clause = _literal_as_binds(expression, type_=self.type)
2708 def _copy_internals(self, clone=_clone, **kw):
2709 self.clause = clone(self.clause, **kw)
2710 self.__dict__.pop("typed_expression", None)
2712 def get_children(self, **kwargs):
2713 return (self.clause,)
2715 @property
2716 def _from_objects(self):
2717 return self.clause._from_objects
2719 @util.memoized_property
2720 def typed_expression(self):
2721 if isinstance(self.clause, BindParameter):
2722 bp = self.clause._clone()
2723 bp.type = self.type
2724 return bp
2725 else:
2726 return self.clause
2728 def self_group(self, against=None):
2729 grouped = self.clause.self_group(against=against)
2730 if grouped is not self.clause:
2731 return TypeCoerce(grouped, self.type)
2732 else:
2733 return self
2736class Extract(ColumnElement):
2737 """Represent a SQL EXTRACT clause, ``extract(field FROM expr)``."""
2739 __visit_name__ = "extract"
2741 def __init__(self, field, expr, **kwargs):
2742 """Return a :class:`.Extract` construct.
2744 This is typically available as :func:`.extract`
2745 as well as ``func.extract`` from the
2746 :data:`.func` namespace.
2748 """
2749 self.type = type_api.INTEGERTYPE
2750 self.field = field
2751 self.expr = _literal_as_binds(expr, None)
2753 def _copy_internals(self, clone=_clone, **kw):
2754 self.expr = clone(self.expr, **kw)
2756 def get_children(self, **kwargs):
2757 return (self.expr,)
2759 @property
2760 def _from_objects(self):
2761 return self.expr._from_objects
2764class _label_reference(ColumnElement):
2765 """Wrap a column expression as it appears in a 'reference' context.
2767 This expression is any that includes an _order_by_label_element,
2768 which is a Label, or a DESC / ASC construct wrapping a Label.
2770 The production of _label_reference() should occur when an expression
2771 is added to this context; this includes the ORDER BY or GROUP BY of a
2772 SELECT statement, as well as a few other places, such as the ORDER BY
2773 within an OVER clause.
2775 """
2777 __visit_name__ = "label_reference"
2779 def __init__(self, element):
2780 self.element = element
2782 def _copy_internals(self, clone=_clone, **kw):
2783 self.element = clone(self.element, **kw)
2785 @property
2786 def _from_objects(self):
2787 return ()
2790class _textual_label_reference(ColumnElement):
2791 __visit_name__ = "textual_label_reference"
2793 def __init__(self, element):
2794 self.element = element
2796 @util.memoized_property
2797 def _text_clause(self):
2798 return TextClause._create_text(self.element)
2801class UnaryExpression(ColumnElement):
2802 """Define a 'unary' expression.
2804 A unary expression has a single column expression
2805 and an operator. The operator can be placed on the left
2806 (where it is called the 'operator') or right (where it is called the
2807 'modifier') of the column expression.
2809 :class:`.UnaryExpression` is the basis for several unary operators
2810 including those used by :func:`.desc`, :func:`.asc`, :func:`.distinct`,
2811 :func:`.nullsfirst` and :func:`.nullslast`.
2813 """
2815 __visit_name__ = "unary"
2817 def __init__(
2818 self,
2819 element,
2820 operator=None,
2821 modifier=None,
2822 type_=None,
2823 negate=None,
2824 wraps_column_expression=False,
2825 ):
2826 self.operator = operator
2827 self.modifier = modifier
2828 self.element = element.self_group(
2829 against=self.operator or self.modifier
2830 )
2831 self.type = type_api.to_instance(type_)
2832 self.negate = negate
2833 self.wraps_column_expression = wraps_column_expression
2835 @classmethod
2836 def _create_nullsfirst(cls, column):
2837 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
2839 :func:`.nullsfirst` is intended to modify the expression produced
2840 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
2841 should be handled when they are encountered during ordering::
2844 from sqlalchemy import desc, nullsfirst
2846 stmt = select([users_table]).\
2847 order_by(nullsfirst(desc(users_table.c.name)))
2849 The SQL expression from the above would resemble::
2851 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
2853 Like :func:`.asc` and :func:`.desc`, :func:`.nullsfirst` is typically
2854 invoked from the column expression itself using
2855 :meth:`_expression.ColumnElement.nullsfirst`,
2856 rather than as its standalone
2857 function version, as in::
2859 stmt = (select([users_table]).
2860 order_by(users_table.c.name.desc().nullsfirst())
2861 )
2863 .. seealso::
2865 :func:`.asc`
2867 :func:`.desc`
2869 :func:`.nullslast`
2871 :meth:`_expression.Select.order_by`
2873 """
2874 return UnaryExpression(
2875 _literal_as_label_reference(column),
2876 modifier=operators.nullsfirst_op,
2877 wraps_column_expression=False,
2878 )
2880 @classmethod
2881 def _create_nullslast(cls, column):
2882 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
2884 :func:`.nullslast` is intended to modify the expression produced
2885 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
2886 should be handled when they are encountered during ordering::
2889 from sqlalchemy import desc, nullslast
2891 stmt = select([users_table]).\
2892 order_by(nullslast(desc(users_table.c.name)))
2894 The SQL expression from the above would resemble::
2896 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
2898 Like :func:`.asc` and :func:`.desc`, :func:`.nullslast` is typically
2899 invoked from the column expression itself using
2900 :meth:`_expression.ColumnElement.nullslast`,
2901 rather than as its standalone
2902 function version, as in::
2904 stmt = select([users_table]).\
2905 order_by(users_table.c.name.desc().nullslast())
2907 .. seealso::
2909 :func:`.asc`
2911 :func:`.desc`
2913 :func:`.nullsfirst`
2915 :meth:`_expression.Select.order_by`
2917 """
2918 return UnaryExpression(
2919 _literal_as_label_reference(column),
2920 modifier=operators.nullslast_op,
2921 wraps_column_expression=False,
2922 )
2924 @classmethod
2925 def _create_desc(cls, column):
2926 """Produce a descending ``ORDER BY`` clause element.
2928 e.g.::
2930 from sqlalchemy import desc
2932 stmt = select([users_table]).order_by(desc(users_table.c.name))
2934 will produce SQL as::
2936 SELECT id, name FROM user ORDER BY name DESC
2938 The :func:`.desc` function is a standalone version of the
2939 :meth:`_expression.ColumnElement.desc`
2940 method available on all SQL expressions,
2941 e.g.::
2944 stmt = select([users_table]).order_by(users_table.c.name.desc())
2946 :param column: A :class:`_expression.ColumnElement` (e.g.
2947 scalar SQL expression)
2948 with which to apply the :func:`.desc` operation.
2950 .. seealso::
2952 :func:`.asc`
2954 :func:`.nullsfirst`
2956 :func:`.nullslast`
2958 :meth:`_expression.Select.order_by`
2960 """
2961 return UnaryExpression(
2962 _literal_as_label_reference(column),
2963 modifier=operators.desc_op,
2964 wraps_column_expression=False,
2965 )
2967 @classmethod
2968 def _create_asc(cls, column):
2969 """Produce an ascending ``ORDER BY`` clause element.
2971 e.g.::
2973 from sqlalchemy import asc
2974 stmt = select([users_table]).order_by(asc(users_table.c.name))
2976 will produce SQL as::
2978 SELECT id, name FROM user ORDER BY name ASC
2980 The :func:`.asc` function is a standalone version of the
2981 :meth:`_expression.ColumnElement.asc`
2982 method available on all SQL expressions,
2983 e.g.::
2986 stmt = select([users_table]).order_by(users_table.c.name.asc())
2988 :param column: A :class:`_expression.ColumnElement` (e.g.
2989 scalar SQL expression)
2990 with which to apply the :func:`.asc` operation.
2992 .. seealso::
2994 :func:`.desc`
2996 :func:`.nullsfirst`
2998 :func:`.nullslast`
3000 :meth:`_expression.Select.order_by`
3002 """
3003 return UnaryExpression(
3004 _literal_as_label_reference(column),
3005 modifier=operators.asc_op,
3006 wraps_column_expression=False,
3007 )
3009 @classmethod
3010 def _create_distinct(cls, expr):
3011 """Produce an column-expression-level unary ``DISTINCT`` clause.
3013 This applies the ``DISTINCT`` keyword to an individual column
3014 expression, and is typically contained within an aggregate function,
3015 as in::
3017 from sqlalchemy import distinct, func
3018 stmt = select([func.count(distinct(users_table.c.name))])
3020 The above would produce an expression resembling::
3022 SELECT COUNT(DISTINCT name) FROM user
3024 The :func:`.distinct` function is also available as a column-level
3025 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
3027 stmt = select([func.count(users_table.c.name.distinct())])
3029 The :func:`.distinct` operator is different from the
3030 :meth:`_expression.Select.distinct` method of
3031 :class:`_expression.Select`,
3032 which produces a ``SELECT`` statement
3033 with ``DISTINCT`` applied to the result set as a whole,
3034 e.g. a ``SELECT DISTINCT`` expression. See that method for further
3035 information.
3037 .. seealso::
3039 :meth:`_expression.ColumnElement.distinct`
3041 :meth:`_expression.Select.distinct`
3043 :data:`.func`
3045 """
3046 expr = _literal_as_binds(expr)
3047 return UnaryExpression(
3048 expr,
3049 operator=operators.distinct_op,
3050 type_=expr.type,
3051 wraps_column_expression=False,
3052 )
3054 @property
3055 def _order_by_label_element(self):
3056 if self.modifier in (operators.desc_op, operators.asc_op):
3057 return self.element._order_by_label_element
3058 else:
3059 return None
3061 @property
3062 def _from_objects(self):
3063 return self.element._from_objects
3065 def _copy_internals(self, clone=_clone, **kw):
3066 self.element = clone(self.element, **kw)
3068 def get_children(self, **kwargs):
3069 return (self.element,)
3071 def compare(self, other, **kw):
3072 """Compare this :class:`UnaryExpression` against the given
3073 :class:`_expression.ClauseElement`."""
3075 return (
3076 isinstance(other, UnaryExpression)
3077 and self.operator == other.operator
3078 and self.modifier == other.modifier
3079 and self.element.compare(other.element, **kw)
3080 )
3082 def _negate(self):
3083 if self.negate is not None:
3084 return UnaryExpression(
3085 self.element,
3086 operator=self.negate,
3087 negate=self.operator,
3088 modifier=self.modifier,
3089 type_=self.type,
3090 wraps_column_expression=self.wraps_column_expression,
3091 )
3092 elif self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity:
3093 return UnaryExpression(
3094 self.self_group(against=operators.inv),
3095 operator=operators.inv,
3096 type_=type_api.BOOLEANTYPE,
3097 wraps_column_expression=self.wraps_column_expression,
3098 negate=None,
3099 )
3100 else:
3101 return ClauseElement._negate(self)
3103 def self_group(self, against=None):
3104 if self.operator and operators.is_precedent(self.operator, against):
3105 return Grouping(self)
3106 else:
3107 return self
3110class CollectionAggregate(UnaryExpression):
3111 """Forms the basis for right-hand collection operator modifiers
3112 ANY and ALL.
3114 The ANY and ALL keywords are available in different ways on different
3115 backends. On PostgreSQL, they only work for an ARRAY type. On
3116 MySQL, they only work for subqueries.
3118 """
3120 @classmethod
3121 def _create_any(cls, expr):
3122 """Produce an ANY expression.
3124 This may apply to an array type for some dialects (e.g. postgresql),
3125 or to a subquery for others (e.g. mysql). e.g.::
3127 # postgresql '5 = ANY (somearray)'
3128 expr = 5 == any_(mytable.c.somearray)
3130 # mysql '5 = ANY (SELECT value FROM table)'
3131 expr = 5 == any_(select([table.c.value]))
3133 .. versionadded:: 1.1
3135 .. seealso::
3137 :func:`_expression.all_`
3139 """
3141 expr = _literal_as_binds(expr)
3143 if expr.is_selectable and hasattr(expr, "as_scalar"):
3144 expr = expr.as_scalar()
3145 expr = expr.self_group()
3146 return CollectionAggregate(
3147 expr,
3148 operator=operators.any_op,
3149 type_=type_api.NULLTYPE,
3150 wraps_column_expression=False,
3151 )
3153 @classmethod
3154 def _create_all(cls, expr):
3155 """Produce an ALL expression.
3157 This may apply to an array type for some dialects (e.g. postgresql),
3158 or to a subquery for others (e.g. mysql). e.g.::
3160 # postgresql '5 = ALL (somearray)'
3161 expr = 5 == all_(mytable.c.somearray)
3163 # mysql '5 = ALL (SELECT value FROM table)'
3164 expr = 5 == all_(select([table.c.value]))
3166 .. versionadded:: 1.1
3168 .. seealso::
3170 :func:`_expression.any_`
3172 """
3174 expr = _literal_as_binds(expr)
3175 if expr.is_selectable and hasattr(expr, "as_scalar"):
3176 expr = expr.as_scalar()
3177 expr = expr.self_group()
3178 return CollectionAggregate(
3179 expr,
3180 operator=operators.all_op,
3181 type_=type_api.NULLTYPE,
3182 wraps_column_expression=False,
3183 )
3185 # operate and reverse_operate are hardwired to
3186 # dispatch onto the type comparator directly, so that we can
3187 # ensure "reversed" behavior.
3188 def operate(self, op, *other, **kwargs):
3189 if not operators.is_comparison(op):
3190 raise exc.ArgumentError(
3191 "Only comparison operators may be used with ANY/ALL"
3192 )
3193 kwargs["reverse"] = True
3194 return self.comparator.operate(operators.mirror(op), *other, **kwargs)
3196 def reverse_operate(self, op, other, **kwargs):
3197 # comparison operators should never call reverse_operate
3198 assert not operators.is_comparison(op)
3199 raise exc.ArgumentError(
3200 "Only comparison operators may be used with ANY/ALL"
3201 )
3204class AsBoolean(UnaryExpression):
3205 def __init__(self, element, operator, negate):
3206 self.element = element
3207 self.type = type_api.BOOLEANTYPE
3208 self.operator = operator
3209 self.negate = negate
3210 self.modifier = None
3211 self.wraps_column_expression = True
3212 self._is_implicitly_boolean = element._is_implicitly_boolean
3214 def self_group(self, against=None):
3215 return self
3217 def _negate(self):
3218 if isinstance(self.element, (True_, False_)):
3219 return self.element._negate()
3220 else:
3221 return AsBoolean(self.element, self.negate, self.operator)
3224class BinaryExpression(ColumnElement):
3225 """Represent an expression that is ``LEFT <operator> RIGHT``.
3227 A :class:`.BinaryExpression` is generated automatically
3228 whenever two column expressions are used in a Python binary expression::
3230 >>> from sqlalchemy.sql import column
3231 >>> column('a') + column('b')
3232 <sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
3233 >>> print(column('a') + column('b'))
3234 a + b
3236 """
3238 __visit_name__ = "binary"
3240 _is_implicitly_boolean = True
3241 """Indicates that any database will know this is a boolean expression
3242 even if the database does not have an explicit boolean datatype.
3244 """
3246 def __init__(
3247 self, left, right, operator, type_=None, negate=None, modifiers=None
3248 ):
3249 # allow compatibility with libraries that
3250 # refer to BinaryExpression directly and pass strings
3251 if isinstance(operator, util.string_types):
3252 operator = operators.custom_op(operator)
3253 self._orig = (left, right)
3254 self.left = left.self_group(against=operator)
3255 self.right = right.self_group(against=operator)
3256 self.operator = operator
3257 self.type = type_api.to_instance(type_)
3258 self.negate = negate
3259 self._is_implicitly_boolean = operators.is_boolean(operator)
3261 if modifiers is None:
3262 self.modifiers = {}
3263 else:
3264 self.modifiers = modifiers
3266 def __bool__(self):
3267 if self.operator in (operator.eq, operator.ne):
3268 return self.operator(hash(self._orig[0]), hash(self._orig[1]))
3269 else:
3270 raise TypeError("Boolean value of this clause is not defined")
3272 __nonzero__ = __bool__
3274 @property
3275 def is_comparison(self):
3276 return operators.is_comparison(self.operator)
3278 @property
3279 def _from_objects(self):
3280 return self.left._from_objects + self.right._from_objects
3282 def _copy_internals(self, clone=_clone, **kw):
3283 self.left = clone(self.left, **kw)
3284 self.right = clone(self.right, **kw)
3286 def get_children(self, **kwargs):
3287 return self.left, self.right
3289 def compare(self, other, **kw):
3290 """Compare this :class:`BinaryExpression` against the
3291 given :class:`BinaryExpression`."""
3293 return (
3294 isinstance(other, BinaryExpression)
3295 and self.operator == other.operator
3296 and (
3297 self.left.compare(other.left, **kw)
3298 and self.right.compare(other.right, **kw)
3299 or (
3300 operators.is_commutative(self.operator)
3301 and self.left.compare(other.right, **kw)
3302 and self.right.compare(other.left, **kw)
3303 )
3304 )
3305 )
3307 def self_group(self, against=None):
3308 if operators.is_precedent(self.operator, against):
3309 return Grouping(self)
3310 else:
3311 return self
3313 def _negate(self):
3314 if self.negate is not None:
3315 return BinaryExpression(
3316 self.left,
3317 self.right,
3318 self.negate,
3319 negate=self.operator,
3320 type_=self.type,
3321 modifiers=self.modifiers,
3322 )
3323 else:
3324 return super(BinaryExpression, self)._negate()
3327class Slice(ColumnElement):
3328 """Represent SQL for a Python array-slice object.
3330 This is not a specific SQL construct at this level, but
3331 may be interpreted by specific dialects, e.g. PostgreSQL.
3333 """
3335 __visit_name__ = "slice"
3337 def __init__(self, start, stop, step):
3338 self.start = start
3339 self.stop = stop
3340 self.step = step
3341 self.type = type_api.NULLTYPE
3343 def self_group(self, against=None):
3344 assert against is operator.getitem
3345 return self
3348class IndexExpression(BinaryExpression):
3349 """Represent the class of expressions that are like an "index" operation.
3350 """
3352 pass
3355class Grouping(ColumnElement):
3356 """Represent a grouping within a column expression"""
3358 __visit_name__ = "grouping"
3360 def __init__(self, element):
3361 self.element = element
3362 self.type = getattr(element, "type", type_api.NULLTYPE)
3364 def self_group(self, against=None):
3365 return self
3367 @util.memoized_property
3368 def _is_implicitly_boolean(self):
3369 return self.element._is_implicitly_boolean
3371 @property
3372 def _key_label(self):
3373 return self._label
3375 @property
3376 def _label(self):
3377 return getattr(self.element, "_label", None) or self.anon_label
3379 def _copy_internals(self, clone=_clone, **kw):
3380 self.element = clone(self.element, **kw)
3382 def get_children(self, **kwargs):
3383 return (self.element,)
3385 @property
3386 def _from_objects(self):
3387 return self.element._from_objects
3389 def __getattr__(self, attr):
3390 return getattr(self.element, attr)
3392 def __getstate__(self):
3393 return {"element": self.element, "type": self.type}
3395 def __setstate__(self, state):
3396 self.element = state["element"]
3397 self.type = state["type"]
3399 def compare(self, other, **kw):
3400 return isinstance(other, Grouping) and self.element.compare(
3401 other.element
3402 )
3405RANGE_UNBOUNDED = util.symbol("RANGE_UNBOUNDED")
3406RANGE_CURRENT = util.symbol("RANGE_CURRENT")
3409class Over(ColumnElement):
3410 """Represent an OVER clause.
3412 This is a special operator against a so-called
3413 "window" function, as well as any aggregate function,
3414 which produces results relative to the result set
3415 itself. It's supported only by certain database
3416 backends.
3418 """
3420 __visit_name__ = "over"
3422 order_by = None
3423 partition_by = None
3425 element = None
3426 """The underlying expression object to which this :class:`.Over`
3427 object refers towards."""
3429 def __init__(
3430 self, element, partition_by=None, order_by=None, range_=None, rows=None
3431 ):
3432 r"""Produce an :class:`.Over` object against a function.
3434 Used against aggregate or so-called "window" functions,
3435 for database backends that support window functions.
3437 :func:`_expression.over` is usually called using
3438 the :meth:`.FunctionElement.over` method, e.g.::
3440 func.row_number().over(order_by=mytable.c.some_column)
3442 Would produce::
3444 ROW_NUMBER() OVER(ORDER BY some_column)
3446 Ranges are also possible using the :paramref:`.expression.over.range_`
3447 and :paramref:`.expression.over.rows` parameters. These
3448 mutually-exclusive parameters each accept a 2-tuple, which contains
3449 a combination of integers and None::
3451 func.row_number().over(
3452 order_by=my_table.c.some_column, range_=(None, 0))
3454 The above would produce::
3456 ROW_NUMBER() OVER(ORDER BY some_column
3457 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
3459 A value of None indicates "unbounded", a
3460 value of zero indicates "current row", and negative / positive
3461 integers indicate "preceding" and "following":
3463 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
3465 func.row_number().over(order_by='x', range_=(-5, 10))
3467 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
3469 func.row_number().over(order_by='x', rows=(None, 0))
3471 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
3473 func.row_number().over(order_by='x', range_=(-2, None))
3475 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
3477 func.row_number().over(order_by='x', range_=(1, 3))
3479 .. versionadded:: 1.1 support for RANGE / ROWS within a window
3482 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
3483 or other compatible construct.
3484 :param partition_by: a column element or string, or a list
3485 of such, that will be used as the PARTITION BY clause
3486 of the OVER construct.
3487 :param order_by: a column element or string, or a list
3488 of such, that will be used as the ORDER BY clause
3489 of the OVER construct.
3490 :param range\_: optional range clause for the window. This is a
3491 tuple value which can contain integer values or None, and will
3492 render a RANGE BETWEEN PRECEDING / FOLLOWING clause
3494 .. versionadded:: 1.1
3496 :param rows: optional rows clause for the window. This is a tuple
3497 value which can contain integer values or None, and will render
3498 a ROWS BETWEEN PRECEDING / FOLLOWING clause.
3500 .. versionadded:: 1.1
3502 This function is also available from the :data:`~.expression.func`
3503 construct itself via the :meth:`.FunctionElement.over` method.
3505 .. seealso::
3507 :data:`.expression.func`
3509 :func:`_expression.within_group`
3511 """
3512 self.element = element
3513 if order_by is not None:
3514 self.order_by = ClauseList(
3515 *util.to_list(order_by),
3516 _literal_as_text=_literal_as_label_reference
3517 )
3518 if partition_by is not None:
3519 self.partition_by = ClauseList(
3520 *util.to_list(partition_by),
3521 _literal_as_text=_literal_as_label_reference
3522 )
3524 if range_:
3525 self.range_ = self._interpret_range(range_)
3526 if rows:
3527 raise exc.ArgumentError(
3528 "'range_' and 'rows' are mutually exclusive"
3529 )
3530 else:
3531 self.rows = None
3532 elif rows:
3533 self.rows = self._interpret_range(rows)
3534 self.range_ = None
3535 else:
3536 self.rows = self.range_ = None
3538 def _interpret_range(self, range_):
3539 if not isinstance(range_, tuple) or len(range_) != 2:
3540 raise exc.ArgumentError("2-tuple expected for range/rows")
3542 if range_[0] is None:
3543 lower = RANGE_UNBOUNDED
3544 else:
3545 try:
3546 lower = int(range_[0])
3547 except ValueError as err:
3548 util.raise_(
3549 exc.ArgumentError(
3550 "Integer or None expected for range value"
3551 ),
3552 replace_context=err,
3553 )
3554 else:
3555 if lower == 0:
3556 lower = RANGE_CURRENT
3558 if range_[1] is None:
3559 upper = RANGE_UNBOUNDED
3560 else:
3561 try:
3562 upper = int(range_[1])
3563 except ValueError as err:
3564 util.raise_(
3565 exc.ArgumentError(
3566 "Integer or None expected for range value"
3567 ),
3568 replace_context=err,
3569 )
3570 else:
3571 if upper == 0:
3572 upper = RANGE_CURRENT
3574 return lower, upper
3576 @property
3577 @util.deprecated(
3578 "1.1",
3579 "the :attr:`.Over.func` member of the :class:`.Over` "
3580 "class is deprecated and will be removed in a future release. "
3581 "Please refer to the :attr:`.Over.element` attribute.",
3582 )
3583 def func(self):
3584 """the element referred to by this :class:`.Over`
3585 clause.
3588 """
3589 return self.element
3591 @util.memoized_property
3592 def type(self):
3593 return self.element.type
3595 def get_children(self, **kwargs):
3596 return [
3597 c
3598 for c in (self.element, self.partition_by, self.order_by)
3599 if c is not None
3600 ]
3602 def _copy_internals(self, clone=_clone, **kw):
3603 self.element = clone(self.element, **kw)
3604 if self.partition_by is not None:
3605 self.partition_by = clone(self.partition_by, **kw)
3606 if self.order_by is not None:
3607 self.order_by = clone(self.order_by, **kw)
3609 @property
3610 def _from_objects(self):
3611 return list(
3612 itertools.chain(
3613 *[
3614 c._from_objects
3615 for c in (self.element, self.partition_by, self.order_by)
3616 if c is not None
3617 ]
3618 )
3619 )
3622class WithinGroup(ColumnElement):
3623 """Represent a WITHIN GROUP (ORDER BY) clause.
3625 This is a special operator against so-called
3626 "ordered set aggregate" and "hypothetical
3627 set aggregate" functions, including ``percentile_cont()``,
3628 ``rank()``, ``dense_rank()``, etc.
3630 It's supported only by certain database backends, such as PostgreSQL,
3631 Oracle and MS SQL Server.
3633 The :class:`.WithinGroup` construct extracts its type from the
3634 method :meth:`.FunctionElement.within_group_type`. If this returns
3635 ``None``, the function's ``.type`` is used.
3637 """
3639 __visit_name__ = "withingroup"
3641 order_by = None
3643 def __init__(self, element, *order_by):
3644 r"""Produce a :class:`.WithinGroup` object against a function.
3646 Used against so-called "ordered set aggregate" and "hypothetical
3647 set aggregate" functions, including :class:`.percentile_cont`,
3648 :class:`.rank`, :class:`.dense_rank`, etc.
3650 :func:`_expression.within_group` is usually called using
3651 the :meth:`.FunctionElement.within_group` method, e.g.::
3653 from sqlalchemy import within_group
3654 stmt = select([
3655 department.c.id,
3656 func.percentile_cont(0.5).within_group(
3657 department.c.salary.desc()
3658 )
3659 ])
3661 The above statement would produce SQL similar to
3662 ``SELECT department.id, percentile_cont(0.5)
3663 WITHIN GROUP (ORDER BY department.salary DESC)``.
3665 :param element: a :class:`.FunctionElement` construct, typically
3666 generated by :data:`~.expression.func`.
3667 :param \*order_by: one or more column elements that will be used
3668 as the ORDER BY clause of the WITHIN GROUP construct.
3670 .. versionadded:: 1.1
3672 .. seealso::
3674 :data:`.expression.func`
3676 :func:`_expression.over`
3678 """
3679 self.element = element
3680 if order_by is not None:
3681 self.order_by = ClauseList(
3682 *util.to_list(order_by),
3683 _literal_as_text=_literal_as_label_reference
3684 )
3686 def over(self, partition_by=None, order_by=None, range_=None, rows=None):
3687 """Produce an OVER clause against this :class:`.WithinGroup`
3688 construct.
3690 This function has the same signature as that of
3691 :meth:`.FunctionElement.over`.
3693 """
3694 return Over(
3695 self,
3696 partition_by=partition_by,
3697 order_by=order_by,
3698 range_=range_,
3699 rows=rows,
3700 )
3702 @util.memoized_property
3703 def type(self):
3704 wgt = self.element.within_group_type(self)
3705 if wgt is not None:
3706 return wgt
3707 else:
3708 return self.element.type
3710 def get_children(self, **kwargs):
3711 return [c for c in (self.element, self.order_by) if c is not None]
3713 def _copy_internals(self, clone=_clone, **kw):
3714 self.element = clone(self.element, **kw)
3715 if self.order_by is not None:
3716 self.order_by = clone(self.order_by, **kw)
3718 @property
3719 def _from_objects(self):
3720 return list(
3721 itertools.chain(
3722 *[
3723 c._from_objects
3724 for c in (self.element, self.order_by)
3725 if c is not None
3726 ]
3727 )
3728 )
3731class FunctionFilter(ColumnElement):
3732 """Represent a function FILTER clause.
3734 This is a special operator against aggregate and window functions,
3735 which controls which rows are passed to it.
3736 It's supported only by certain database backends.
3738 Invocation of :class:`.FunctionFilter` is via
3739 :meth:`.FunctionElement.filter`::
3741 func.count(1).filter(True)
3743 .. versionadded:: 1.0.0
3745 .. seealso::
3747 :meth:`.FunctionElement.filter`
3749 """
3751 __visit_name__ = "funcfilter"
3753 criterion = None
3755 def __init__(self, func, *criterion):
3756 """Produce a :class:`.FunctionFilter` object against a function.
3758 Used against aggregate and window functions,
3759 for database backends that support the "FILTER" clause.
3761 E.g.::
3763 from sqlalchemy import funcfilter
3764 funcfilter(func.count(1), MyClass.name == 'some name')
3766 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
3768 This function is also available from the :data:`~.expression.func`
3769 construct itself via the :meth:`.FunctionElement.filter` method.
3771 .. versionadded:: 1.0.0
3773 .. seealso::
3775 :meth:`.FunctionElement.filter`
3778 """
3779 self.func = func
3780 self.filter(*criterion)
3782 def filter(self, *criterion):
3783 """Produce an additional FILTER against the function.
3785 This method adds additional criteria to the initial criteria
3786 set up by :meth:`.FunctionElement.filter`.
3788 Multiple criteria are joined together at SQL render time
3789 via ``AND``.
3792 """
3794 for criterion in list(criterion):
3795 criterion = _expression_literal_as_text(criterion)
3797 if self.criterion is not None:
3798 self.criterion = self.criterion & criterion
3799 else:
3800 self.criterion = criterion
3802 return self
3804 def over(self, partition_by=None, order_by=None, range_=None, rows=None):
3805 """Produce an OVER clause against this filtered function.
3807 Used against aggregate or so-called "window" functions,
3808 for database backends that support window functions.
3810 The expression::
3812 func.rank().filter(MyClass.y > 5).over(order_by='x')
3814 is shorthand for::
3816 from sqlalchemy import over, funcfilter
3817 over(funcfilter(func.rank(), MyClass.y > 5), order_by='x')
3819 See :func:`_expression.over` for a full description.
3821 """
3822 return Over(
3823 self,
3824 partition_by=partition_by,
3825 order_by=order_by,
3826 range_=range_,
3827 rows=rows,
3828 )
3830 def self_group(self, against=None):
3831 if operators.is_precedent(operators.filter_op, against):
3832 return Grouping(self)
3833 else:
3834 return self
3836 @util.memoized_property
3837 def type(self):
3838 return self.func.type
3840 def get_children(self, **kwargs):
3841 return [c for c in (self.func, self.criterion) if c is not None]
3843 def _copy_internals(self, clone=_clone, **kw):
3844 self.func = clone(self.func, **kw)
3845 if self.criterion is not None:
3846 self.criterion = clone(self.criterion, **kw)
3848 @property
3849 def _from_objects(self):
3850 return list(
3851 itertools.chain(
3852 *[
3853 c._from_objects
3854 for c in (self.func, self.criterion)
3855 if c is not None
3856 ]
3857 )
3858 )
3861class Label(ColumnElement):
3862 """Represents a column label (AS).
3864 Represent a label, as typically applied to any column-level
3865 element using the ``AS`` sql keyword.
3867 """
3869 __visit_name__ = "label"
3871 def __init__(self, name, element, type_=None):
3872 """Return a :class:`Label` object for the
3873 given :class:`_expression.ColumnElement`.
3875 A label changes the name of an element in the columns clause of a
3876 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
3878 This functionality is more conveniently available via the
3879 :meth:`_expression.ColumnElement.label` method on
3880 :class:`_expression.ColumnElement`.
3882 :param name: label name
3884 :param obj: a :class:`_expression.ColumnElement`.
3886 """
3888 if isinstance(element, Label):
3889 self._resolve_label = element._label
3891 while isinstance(element, Label):
3892 element = element.element
3894 if name:
3895 self.name = name
3896 self._resolve_label = self.name
3897 else:
3898 self.name = _anonymous_label(
3899 "%%(%d %s)s" % (id(self), getattr(element, "name", "anon"))
3900 )
3902 self.key = self._label = self._key_label = self.name
3903 self._element = element
3904 self._type = type_
3905 self._proxies = [element]
3907 def __reduce__(self):
3908 return self.__class__, (self.name, self._element, self._type)
3910 @util.memoized_property
3911 def _is_implicitly_boolean(self):
3912 return self.element._is_implicitly_boolean
3914 @util.memoized_property
3915 def _allow_label_resolve(self):
3916 return self.element._allow_label_resolve
3918 @property
3919 def _order_by_label_element(self):
3920 return self
3922 @util.memoized_property
3923 def type(self):
3924 return type_api.to_instance(
3925 self._type or getattr(self._element, "type", None)
3926 )
3928 @util.memoized_property
3929 def element(self):
3930 return self._element.self_group(against=operators.as_)
3932 def self_group(self, against=None):
3933 return self._apply_to_inner(self._element.self_group, against=against)
3935 def _negate(self):
3936 return self._apply_to_inner(self._element._negate)
3938 def _apply_to_inner(self, fn, *arg, **kw):
3939 sub_element = fn(*arg, **kw)
3940 if sub_element is not self._element:
3941 return Label(self.name, sub_element, type_=self._type)
3942 else:
3943 return self
3945 @property
3946 def primary_key(self):
3947 return self.element.primary_key
3949 @property
3950 def foreign_keys(self):
3951 return self.element.foreign_keys
3953 def get_children(self, **kwargs):
3954 return (self.element,)
3956 def _copy_internals(self, clone=_clone, anonymize_labels=False, **kw):
3957 self._element = clone(self._element, **kw)
3958 self.__dict__.pop("element", None)
3959 self.__dict__.pop("_allow_label_resolve", None)
3960 if anonymize_labels:
3961 self.name = self._resolve_label = _anonymous_label(
3962 "%%(%d %s)s"
3963 % (id(self), getattr(self.element, "name", "anon"))
3964 )
3965 self.key = self._label = self._key_label = self.name
3967 @property
3968 def _from_objects(self):
3969 return self.element._from_objects
3971 def _make_proxy(self, selectable, name=None, **kw):
3972 e = self.element._make_proxy(
3973 selectable,
3974 name=name if name else self.name,
3975 disallow_is_literal=True,
3976 )
3977 e._proxies.append(self)
3978 if self._type is not None:
3979 e.type = self._type
3980 return e
3983class ColumnClause(Immutable, ColumnElement):
3984 """Represents a column expression from any textual string.
3986 The :class:`.ColumnClause`, a lightweight analogue to the
3987 :class:`_schema.Column` class, is typically invoked using the
3988 :func:`_expression.column` function, as in::
3990 from sqlalchemy import column
3992 id, name = column("id"), column("name")
3993 stmt = select([id, name]).select_from("user")
3995 The above statement would produce SQL like::
3997 SELECT id, name FROM user
3999 :class:`.ColumnClause` is the immediate superclass of the schema-specific
4000 :class:`_schema.Column` object. While the :class:`_schema.Column`
4001 class has all the
4002 same capabilities as :class:`.ColumnClause`, the :class:`.ColumnClause`
4003 class is usable by itself in those cases where behavioral requirements
4004 are limited to simple SQL expression generation. The object has none of
4005 the associations with schema-level metadata or with execution-time
4006 behavior that :class:`_schema.Column` does,
4007 so in that sense is a "lightweight"
4008 version of :class:`_schema.Column`.
4010 Full details on :class:`.ColumnClause` usage is at
4011 :func:`_expression.column`.
4013 .. seealso::
4015 :func:`_expression.column`
4017 :class:`_schema.Column`
4019 """
4021 __visit_name__ = "column"
4023 onupdate = default = server_default = server_onupdate = None
4025 _is_multiparam_column = False
4027 _memoized_property = util.group_expirable_memoized_property()
4029 def __init__(self, text, type_=None, is_literal=False, _selectable=None):
4030 """Produce a :class:`.ColumnClause` object.
4032 The :class:`.ColumnClause` is a lightweight analogue to the
4033 :class:`_schema.Column` class. The :func:`_expression.column`
4034 function can
4035 be invoked with just a name alone, as in::
4037 from sqlalchemy import column
4039 id, name = column("id"), column("name")
4040 stmt = select([id, name]).select_from("user")
4042 The above statement would produce SQL like::
4044 SELECT id, name FROM user
4046 Once constructed, :func:`_expression.column`
4047 may be used like any other SQL
4048 expression element such as within :func:`_expression.select`
4049 constructs::
4051 from sqlalchemy.sql import column
4053 id, name = column("id"), column("name")
4054 stmt = select([id, name]).select_from("user")
4056 The text handled by :func:`_expression.column`
4057 is assumed to be handled
4058 like the name of a database column; if the string contains mixed case,
4059 special characters, or matches a known reserved word on the target
4060 backend, the column expression will render using the quoting
4061 behavior determined by the backend. To produce a textual SQL
4062 expression that is rendered exactly without any quoting,
4063 use :func:`_expression.literal_column` instead,
4064 or pass ``True`` as the
4065 value of :paramref:`_expression.column.is_literal`. Additionally,
4066 full SQL
4067 statements are best handled using the :func:`_expression.text`
4068 construct.
4070 :func:`_expression.column` can be used in a table-like
4071 fashion by combining it with the :func:`.table` function
4072 (which is the lightweight analogue to :class:`_schema.Table`
4073 ) to produce
4074 a working table construct with minimal boilerplate::
4076 from sqlalchemy import table, column, select
4078 user = table("user",
4079 column("id"),
4080 column("name"),
4081 column("description"),
4082 )
4084 stmt = select([user.c.description]).where(user.c.name == 'wendy')
4086 A :func:`_expression.column` / :func:`.table`
4087 construct like that illustrated
4088 above can be created in an
4089 ad-hoc fashion and is not associated with any
4090 :class:`_schema.MetaData`, DDL, or events, unlike its
4091 :class:`_schema.Table` counterpart.
4093 .. versionchanged:: 1.0.0 :func:`_expression.column` can now
4094 be imported from the plain ``sqlalchemy`` namespace like any
4095 other SQL element.
4097 :param text: the text of the element.
4099 :param type: :class:`_types.TypeEngine` object which can associate
4100 this :class:`.ColumnClause` with a type.
4102 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
4103 be an exact expression that will be delivered to the output with no
4104 quoting rules applied regardless of case sensitive settings. the
4105 :func:`_expression.literal_column()` function essentially invokes
4106 :func:`_expression.column` while passing ``is_literal=True``.
4108 .. seealso::
4110 :class:`_schema.Column`
4112 :func:`_expression.literal_column`
4114 :func:`.table`
4116 :func:`_expression.text`
4118 :ref:`sqlexpression_literal_column`
4120 """
4121 self.key = self.name = text
4122 self.table = _selectable
4123 self.type = type_api.to_instance(type_)
4124 self.is_literal = is_literal
4126 def _compare_name_for_result(self, other):
4127 if (
4128 self.is_literal
4129 or self.table is None
4130 or self.table._textual
4131 or not hasattr(other, "proxy_set")
4132 or (
4133 isinstance(other, ColumnClause)
4134 and (
4135 other.is_literal
4136 or other.table is None
4137 or other.table._textual
4138 )
4139 )
4140 ):
4141 return (hasattr(other, "name") and self.name == other.name) or (
4142 hasattr(other, "_label") and self._label == other._label
4143 )
4144 else:
4145 return other.proxy_set.intersection(self.proxy_set)
4147 def _get_table(self):
4148 return self.__dict__["table"]
4150 def _set_table(self, table):
4151 self._memoized_property.expire_instance(self)
4152 self.__dict__["table"] = table
4154 table = property(_get_table, _set_table)
4156 @_memoized_property
4157 def _from_objects(self):
4158 t = self.table
4159 if t is not None:
4160 return [t]
4161 else:
4162 return []
4164 @util.memoized_property
4165 def description(self):
4166 if util.py3k:
4167 return self.name
4168 else:
4169 return self.name.encode("ascii", "backslashreplace")
4171 @_memoized_property
4172 def _key_label(self):
4173 if self.key != self.name:
4174 return self._gen_label(self.key)
4175 else:
4176 return self._label
4178 @_memoized_property
4179 def _label(self):
4180 return self._gen_label(self.name)
4182 @_memoized_property
4183 def _render_label_in_columns_clause(self):
4184 return self.table is not None
4186 @property
4187 def _ddl_label(self):
4188 return self._gen_label(self.name, dedupe_on_key=False)
4190 def _gen_label(self, name, dedupe_on_key=True):
4191 t = self.table
4193 if self.is_literal:
4194 return None
4196 elif t is not None and t.named_with_column:
4197 if getattr(t, "schema", None):
4198 label = t.schema.replace(".", "_") + "_" + t.name + "_" + name
4199 else:
4200 label = t.name + "_" + name
4202 # propagate name quoting rules for labels.
4203 if getattr(name, "quote", None) is not None:
4204 if isinstance(label, quoted_name):
4205 label.quote = name.quote
4206 else:
4207 label = quoted_name(label, name.quote)
4208 elif getattr(t.name, "quote", None) is not None:
4209 # can't get this situation to occur, so let's
4210 # assert false on it for now
4211 assert not isinstance(label, quoted_name)
4212 label = quoted_name(label, t.name.quote)
4214 if dedupe_on_key:
4215 # ensure the label name doesn't conflict with that of an
4216 # existing column. note that this implies that any Column
4217 # must **not** set up its _label before its parent table has
4218 # all of its other Column objects set up. There are several
4219 # tables in the test suite which will fail otherwise; example:
4220 # table "owner" has columns "name" and "owner_name". Therefore
4221 # column owner.name cannot use the label "owner_name", it has
4222 # to be "owner_name_1".
4223 if label in t.c:
4224 _label = label
4225 counter = 1
4226 while _label in t.c:
4227 _label = label + "_" + str(counter)
4228 counter += 1
4229 label = _label
4231 return _as_truncated(label)
4233 else:
4234 return name
4236 def _bind_param(self, operator, obj, type_=None):
4237 return BindParameter(
4238 self.key,
4239 obj,
4240 _compared_to_operator=operator,
4241 _compared_to_type=self.type,
4242 type_=type_,
4243 unique=True,
4244 )
4246 def _make_proxy(
4247 self,
4248 selectable,
4249 name=None,
4250 attach=True,
4251 name_is_truncatable=False,
4252 disallow_is_literal=False,
4253 **kw
4254 ):
4255 # the "is_literal" flag normally should never be propagated; a proxied
4256 # column is always a SQL identifier and never the actual expression
4257 # being evaluated. however, there is a case where the "is_literal" flag
4258 # might be used to allow the given identifier to have a fixed quoting
4259 # pattern already, so maintain the flag for the proxy unless a
4260 # :class:`.Label` object is creating the proxy. See [ticket:4730].
4261 is_literal = (
4262 not disallow_is_literal
4263 and self.is_literal
4264 and (
4265 # note this does not accommodate for quoted_name differences
4266 # right now
4267 name is None
4268 or name == self.name
4269 )
4270 )
4271 c = self._constructor(
4272 _as_truncated(name or self.name)
4273 if name_is_truncatable
4274 else (name or self.name),
4275 type_=self.type,
4276 _selectable=selectable,
4277 is_literal=is_literal,
4278 )
4279 if name is None:
4280 c.key = self.key
4281 c._proxies = [self]
4282 if selectable._is_clone_of is not None:
4283 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
4285 if attach:
4286 selectable._columns[c.key] = c
4287 return c
4290class CollationClause(ColumnElement):
4291 __visit_name__ = "collation"
4293 def __init__(self, collation):
4294 self.collation = collation
4297class _IdentifiedClause(Executable, ClauseElement):
4299 __visit_name__ = "identified"
4300 _execution_options = Executable._execution_options.union(
4301 {"autocommit": False}
4302 )
4304 def __init__(self, ident):
4305 self.ident = ident
4308class SavepointClause(_IdentifiedClause):
4309 __visit_name__ = "savepoint"
4312class RollbackToSavepointClause(_IdentifiedClause):
4313 __visit_name__ = "rollback_to_savepoint"
4316class ReleaseSavepointClause(_IdentifiedClause):
4317 __visit_name__ = "release_savepoint"
4320class quoted_name(util.MemoizedSlots, util.text_type):
4321 """Represent a SQL identifier combined with quoting preferences.
4323 :class:`.quoted_name` is a Python unicode/str subclass which
4324 represents a particular identifier name along with a
4325 ``quote`` flag. This ``quote`` flag, when set to
4326 ``True`` or ``False``, overrides automatic quoting behavior
4327 for this identifier in order to either unconditionally quote
4328 or to not quote the name. If left at its default of ``None``,
4329 quoting behavior is applied to the identifier on a per-backend basis
4330 based on an examination of the token itself.
4332 A :class:`.quoted_name` object with ``quote=True`` is also
4333 prevented from being modified in the case of a so-called
4334 "name normalize" option. Certain database backends, such as
4335 Oracle, Firebird, and DB2 "normalize" case-insensitive names
4336 as uppercase. The SQLAlchemy dialects for these backends
4337 convert from SQLAlchemy's lower-case-means-insensitive convention
4338 to the upper-case-means-insensitive conventions of those backends.
4339 The ``quote=True`` flag here will prevent this conversion from occurring
4340 to support an identifier that's quoted as all lower case against
4341 such a backend.
4343 The :class:`.quoted_name` object is normally created automatically
4344 when specifying the name for key schema constructs such as
4345 :class:`_schema.Table`, :class:`_schema.Column`, and others.
4346 The class can also be
4347 passed explicitly as the name to any function that receives a name which
4348 can be quoted. Such as to use the :meth:`_engine.Engine.has_table`
4349 method with
4350 an unconditionally quoted name::
4352 from sqlalchemy import create_engine
4353 from sqlalchemy.sql import quoted_name
4355 engine = create_engine("oracle+cx_oracle://some_dsn")
4356 engine.has_table(quoted_name("some_table", True))
4358 The above logic will run the "has table" logic against the Oracle backend,
4359 passing the name exactly as ``"some_table"`` without converting to
4360 upper case.
4362 .. versionadded:: 0.9.0
4364 .. versionchanged:: 1.2 The :class:`.quoted_name` construct is now
4365 importable from ``sqlalchemy.sql``, in addition to the previous
4366 location of ``sqlalchemy.sql.elements``.
4368 """
4370 __slots__ = "quote", "lower", "upper"
4372 def __new__(cls, value, quote):
4373 if value is None:
4374 return None
4375 # experimental - don't bother with quoted_name
4376 # if quote flag is None. doesn't seem to make any dent
4377 # in performance however
4378 # elif not sprcls and quote is None:
4379 # return value
4380 elif isinstance(value, cls) and (
4381 quote is None or value.quote == quote
4382 ):
4383 return value
4384 self = super(quoted_name, cls).__new__(cls, value)
4386 self.quote = quote
4387 return self
4389 def __reduce__(self):
4390 return quoted_name, (util.text_type(self), self.quote)
4392 def _memoized_method_lower(self):
4393 if self.quote:
4394 return self
4395 else:
4396 return util.text_type(self).lower()
4398 def _memoized_method_upper(self):
4399 if self.quote:
4400 return self
4401 else:
4402 return util.text_type(self).upper()
4404 def __repr__(self):
4405 if util.py2k:
4406 backslashed = self.encode("ascii", "backslashreplace")
4407 if not util.py2k:
4408 backslashed = backslashed.decode("ascii")
4409 return "'%s'" % backslashed
4410 else:
4411 return str.__repr__(self)
4414class _truncated_label(quoted_name):
4415 """A unicode subclass used to identify symbolic "
4416 "names that may require truncation."""
4418 __slots__ = ()
4420 def __new__(cls, value, quote=None):
4421 quote = getattr(value, "quote", quote)
4422 # return super(_truncated_label, cls).__new__(cls, value, quote, True)
4423 return super(_truncated_label, cls).__new__(cls, value, quote)
4425 def __reduce__(self):
4426 return self.__class__, (util.text_type(self), self.quote)
4428 def apply_map(self, map_):
4429 return self
4432class conv(_truncated_label):
4433 """Mark a string indicating that a name has already been converted
4434 by a naming convention.
4436 This is a string subclass that indicates a name that should not be
4437 subject to any further naming conventions.
4439 E.g. when we create a :class:`.Constraint` using a naming convention
4440 as follows::
4442 m = MetaData(naming_convention={
4443 "ck": "ck_%(table_name)s_%(constraint_name)s"
4444 })
4445 t = Table('t', m, Column('x', Integer),
4446 CheckConstraint('x > 5', name='x5'))
4448 The name of the above constraint will be rendered as ``"ck_t_x5"``.
4449 That is, the existing name ``x5`` is used in the naming convention as the
4450 ``constraint_name`` token.
4452 In some situations, such as in migration scripts, we may be rendering
4453 the above :class:`.CheckConstraint` with a name that's already been
4454 converted. In order to make sure the name isn't double-modified, the
4455 new name is applied using the :func:`_schema.conv` marker. We can
4456 use this explicitly as follows::
4459 m = MetaData(naming_convention={
4460 "ck": "ck_%(table_name)s_%(constraint_name)s"
4461 })
4462 t = Table('t', m, Column('x', Integer),
4463 CheckConstraint('x > 5', name=conv('ck_t_x5')))
4465 Where above, the :func:`_schema.conv` marker indicates that the constraint
4466 name here is final, and the name will render as ``"ck_t_x5"`` and not
4467 ``"ck_t_ck_t_x5"``
4469 .. versionadded:: 0.9.4
4471 .. seealso::
4473 :ref:`constraint_naming_conventions`
4475 """
4477 __slots__ = ()
4480class _defer_name(_truncated_label):
4481 """mark a name as 'deferred' for the purposes of automated name
4482 generation.
4484 """
4486 __slots__ = ()
4488 def __new__(cls, value):
4489 if value is None:
4490 return _NONE_NAME
4491 elif isinstance(value, conv):
4492 return value
4493 else:
4494 return super(_defer_name, cls).__new__(cls, value)
4496 def __reduce__(self):
4497 return self.__class__, (util.text_type(self),)
4500class _defer_none_name(_defer_name):
4501 """indicate a 'deferred' name that was ultimately the value None."""
4503 __slots__ = ()
4506_NONE_NAME = _defer_none_name("_unnamed_")
4508# for backwards compatibility in case
4509# someone is re-implementing the
4510# _truncated_identifier() sequence in a custom
4511# compiler
4512_generated_label = _truncated_label
4515class _anonymous_label(_truncated_label):
4516 """A unicode subclass used to identify anonymously
4517 generated names."""
4519 __slots__ = ()
4521 def __add__(self, other):
4522 return _anonymous_label(
4523 quoted_name(
4524 util.text_type.__add__(self, util.text_type(other)), self.quote
4525 )
4526 )
4528 def __radd__(self, other):
4529 return _anonymous_label(
4530 quoted_name(
4531 util.text_type.__add__(util.text_type(other), self), self.quote
4532 )
4533 )
4535 def apply_map(self, map_):
4536 if self.quote is not None:
4537 # preserve quoting only if necessary
4538 return quoted_name(self % map_, self.quote)
4539 else:
4540 # else skip the constructor call
4541 return self % map_
4544def _as_truncated(value):
4545 """coerce the given value to :class:`._truncated_label`.
4547 Existing :class:`._truncated_label` and
4548 :class:`._anonymous_label` objects are passed
4549 unchanged.
4550 """
4552 if isinstance(value, _truncated_label):
4553 return value
4554 else:
4555 return _truncated_label(value)
4558def _string_or_unprintable(element):
4559 if isinstance(element, util.string_types):
4560 return element
4561 else:
4562 try:
4563 return str(element)
4564 except Exception:
4565 return "unprintable element %r" % element
4568def _expand_cloned(elements):
4569 """expand the given set of ClauseElements to be the set of all 'cloned'
4570 predecessors.
4572 """
4573 return itertools.chain(*[x._cloned_set for x in elements])
4576def _select_iterables(elements):
4577 """expand tables into individual columns in the
4578 given list of column expressions.
4580 """
4581 return itertools.chain(*[c._select_iterable for c in elements])
4584def _cloned_intersection(a, b):
4585 """return the intersection of sets a and b, counting
4586 any overlap between 'cloned' predecessors.
4588 The returned set is in terms of the entities present within 'a'.
4590 """
4591 all_overlap = set(_expand_cloned(a)).intersection(_expand_cloned(b))
4592 return set(
4593 elem for elem in a if all_overlap.intersection(elem._cloned_set)
4594 )
4597def _cloned_difference(a, b):
4598 all_overlap = set(_expand_cloned(a)).intersection(_expand_cloned(b))
4599 return set(
4600 elem for elem in a if not all_overlap.intersection(elem._cloned_set)
4601 )
4604@util.dependencies("sqlalchemy.sql.functions")
4605def _labeled(functions, element):
4606 if not hasattr(element, "name") or isinstance(
4607 element, functions.FunctionElement
4608 ):
4609 return element.label(None)
4610 else:
4611 return element
4614def _is_column(col):
4615 """True if ``col`` is an instance of
4616 :class:`_expression.ColumnElement`. """
4618 return isinstance(col, ColumnElement)
4621def _find_columns(clause):
4622 """locate Column objects within the given expression."""
4624 cols = util.column_set()
4625 traverse(clause, {}, {"column": cols.add})
4626 return cols
4629# there is some inconsistency here between the usage of
4630# inspect() vs. checking for Visitable and __clause_element__.
4631# Ideally all functions here would derive from inspect(),
4632# however the inspect() versions add significant callcount
4633# overhead for critical functions like _interpret_as_column_or_from().
4634# Generally, the column-based functions are more performance critical
4635# and are fine just checking for __clause_element__(). It is only
4636# _interpret_as_from() where we'd like to be able to receive ORM entities
4637# that have no defined namespace, hence inspect() is needed there.
4640def _column_as_key(element):
4641 if isinstance(element, util.string_types):
4642 return element
4643 if hasattr(element, "__clause_element__"):
4644 element = element.__clause_element__()
4645 try:
4646 return element.key
4647 except AttributeError:
4648 return None
4651def _clause_element_as_expr(element):
4652 if hasattr(element, "__clause_element__"):
4653 return element.__clause_element__()
4654 else:
4655 return element
4658def _literal_as_label_reference(element):
4659 if isinstance(element, util.string_types):
4660 return _textual_label_reference(element)
4662 elif hasattr(element, "__clause_element__"):
4663 element = element.__clause_element__()
4665 return _literal_as_text(element)
4668def _literal_and_labels_as_label_reference(element):
4669 if isinstance(element, util.string_types):
4670 return _textual_label_reference(element)
4672 elif hasattr(element, "__clause_element__"):
4673 element = element.__clause_element__()
4675 if (
4676 isinstance(element, ColumnElement)
4677 and element._order_by_label_element is not None
4678 ):
4679 return _label_reference(element)
4680 else:
4681 return _literal_as_text(element)
4684def _expression_literal_as_text(element):
4685 return _literal_as_text(element)
4688def _literal_as(element, text_fallback):
4689 if isinstance(element, Visitable):
4690 return element
4691 elif hasattr(element, "__clause_element__"):
4692 return element.__clause_element__()
4693 elif isinstance(element, util.string_types):
4694 return text_fallback(element)
4695 elif isinstance(element, (util.NoneType, bool)):
4696 return _const_expr(element)
4697 else:
4698 raise exc.ArgumentError(
4699 "SQL expression object expected, got object of type %r "
4700 "instead" % type(element)
4701 )
4704def _literal_as_text(element, allow_coercion_to_text=False):
4705 if allow_coercion_to_text:
4706 return _literal_as(element, TextClause)
4707 else:
4708 return _literal_as(element, _no_text_coercion)
4711def _literal_as_column(element):
4712 return _literal_as(element, ColumnClause)
4715def _no_column_coercion(element):
4716 element = str(element)
4717 guess_is_literal = not _guess_straight_column.match(element)
4718 raise exc.ArgumentError(
4719 "Textual column expression %(column)r should be "
4720 "explicitly declared with text(%(column)r), "
4721 "or use %(literal_column)s(%(column)r) "
4722 "for more specificity"
4723 % {
4724 "column": util.ellipses_string(element),
4725 "literal_column": "literal_column"
4726 if guess_is_literal
4727 else "column",
4728 }
4729 )
4732def _no_text_coercion(
4733 element, exc_cls=exc.ArgumentError, extra=None, err=None
4734):
4735 util.raise_(
4736 exc_cls(
4737 "%(extra)sTextual SQL expression %(expr)r should be "
4738 "explicitly declared as text(%(expr)r)"
4739 % {
4740 "expr": util.ellipses_string(element),
4741 "extra": "%s " % extra if extra else "",
4742 }
4743 ),
4744 replace_context=err,
4745 )
4748def _no_literals(element):
4749 if hasattr(element, "__clause_element__"):
4750 return element.__clause_element__()
4751 elif not isinstance(element, Visitable):
4752 raise exc.ArgumentError(
4753 "Ambiguous literal: %r. Use the 'text()' "
4754 "function to indicate a SQL expression "
4755 "literal, or 'literal()' to indicate a "
4756 "bound value." % (element,)
4757 )
4758 else:
4759 return element
4762def _is_literal(element):
4763 return not isinstance(element, Visitable) and not hasattr(
4764 element, "__clause_element__"
4765 )
4768def _only_column_elements_or_none(element, name):
4769 if element is None:
4770 return None
4771 else:
4772 return _only_column_elements(element, name)
4775def _only_column_elements(element, name):
4776 if hasattr(element, "__clause_element__"):
4777 element = element.__clause_element__()
4778 if not isinstance(element, ColumnElement):
4779 raise exc.ArgumentError(
4780 "Column-based expression object expected for argument "
4781 "'%s'; got: '%s', type %s" % (name, element, type(element))
4782 )
4783 return element
4786def _literal_as_binds(element, name=None, type_=None):
4787 if hasattr(element, "__clause_element__"):
4788 return element.__clause_element__()
4789 elif not isinstance(element, Visitable):
4790 if element is None:
4791 return Null()
4792 else:
4793 return BindParameter(name, element, type_=type_, unique=True)
4794 else:
4795 return element
4798_guess_straight_column = re.compile(r"^\w\S*$", re.I)
4801def _interpret_as_column_or_from(element):
4802 if isinstance(element, Visitable):
4803 return element
4804 elif hasattr(element, "__clause_element__"):
4805 return element.__clause_element__()
4807 insp = inspection.inspect(element, raiseerr=False)
4808 if insp is None:
4809 if isinstance(element, (util.NoneType, bool)):
4810 return _const_expr(element)
4811 elif hasattr(insp, "selectable"):
4812 return insp.selectable
4814 # be forgiving as this is an extremely common
4815 # and known expression
4816 if element == "*":
4817 guess_is_literal = True
4818 elif isinstance(element, (numbers.Number)):
4819 return ColumnClause(str(element), is_literal=True)
4820 else:
4821 _no_column_coercion(element)
4822 return ColumnClause(element, is_literal=guess_is_literal)
4825def _const_expr(element):
4826 if isinstance(element, (Null, False_, True_)):
4827 return element
4828 elif element is None:
4829 return Null()
4830 elif element is False:
4831 return False_()
4832 elif element is True:
4833 return True_()
4834 else:
4835 raise exc.ArgumentError("Expected None, False, or True")
4838def _type_from_args(args):
4839 for a in args:
4840 if not a.type._isnull:
4841 return a.type
4842 else:
4843 return type_api.NULLTYPE
4846def _corresponding_column_or_error(fromclause, column, require_embedded=False):
4847 c = fromclause.corresponding_column(
4848 column, require_embedded=require_embedded
4849 )
4850 if c is None:
4851 raise exc.InvalidRequestError(
4852 "Given column '%s', attached to table '%s', "
4853 "failed to locate a corresponding column from table '%s'"
4854 % (column, getattr(column, "table", None), fromclause.description)
4855 )
4856 return c
4859class AnnotatedColumnElement(Annotated):
4860 def __init__(self, element, values):
4861 Annotated.__init__(self, element, values)
4862 ColumnElement.comparator._reset(self)
4863 for attr in ("name", "key", "table"):
4864 if self.__dict__.get(attr, False) is None:
4865 self.__dict__.pop(attr)
4867 def _with_annotations(self, values):
4868 clone = super(AnnotatedColumnElement, self)._with_annotations(values)
4869 ColumnElement.comparator._reset(clone)
4870 return clone
4872 @util.memoized_property
4873 def name(self):
4874 """pull 'name' from parent, if not present"""
4875 return self._Annotated__element.name
4877 @util.memoized_property
4878 def table(self):
4879 """pull 'table' from parent, if not present"""
4880 return self._Annotated__element.table
4882 @util.memoized_property
4883 def key(self):
4884 """pull 'key' from parent, if not present"""
4885 return self._Annotated__element.key
4887 @util.memoized_property
4888 def info(self):
4889 return self._Annotated__element.info
4891 @util.memoized_property
4892 def anon_label(self):
4893 return self._Annotated__element.anon_label