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

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/selectable.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"""The :class:`_expression.FromClause` class of SQL expression elements,
9representing
10SQL tables and derived rowsets.
12"""
14import collections
15import itertools
16import operator
17from operator import attrgetter
19from sqlalchemy.sql.visitors import Visitable
20from . import operators
21from . import type_api
22from .annotation import Annotated
23from .base import _from_objects
24from .base import _generative
25from .base import ColumnCollection
26from .base import ColumnSet
27from .base import Executable
28from .base import Generative
29from .base import Immutable
30from .elements import _anonymous_label
31from .elements import _clause_element_as_expr
32from .elements import _clone
33from .elements import _cloned_difference
34from .elements import _cloned_intersection
35from .elements import _document_text_coercion
36from .elements import _expand_cloned
37from .elements import _interpret_as_column_or_from
38from .elements import _literal_and_labels_as_label_reference
39from .elements import _literal_as_label_reference
40from .elements import _literal_as_text
41from .elements import _no_text_coercion
42from .elements import _select_iterables
43from .elements import and_
44from .elements import BindParameter
45from .elements import ClauseElement
46from .elements import ClauseList
47from .elements import Grouping
48from .elements import literal_column
49from .elements import True_
50from .elements import UnaryExpression
51from .. import exc
52from .. import inspection
53from .. import util
56def _interpret_as_from(element):
57 insp = inspection.inspect(element, raiseerr=False)
58 if insp is None:
59 if isinstance(element, util.string_types):
60 _no_text_coercion(element)
61 try:
62 return insp.selectable
63 except AttributeError as err:
64 util.raise_(
65 exc.ArgumentError("FROM expression expected"), replace_context=err
66 )
69def _interpret_as_select(element):
70 element = _interpret_as_from(element)
71 if isinstance(element, Alias):
72 element = element.original
73 if not isinstance(element, SelectBase):
74 element = element.select()
75 return element
78class _OffsetLimitParam(BindParameter):
79 @property
80 def _limit_offset_value(self):
81 return self.effective_value
84def _offset_or_limit_clause(element, name=None, type_=None):
85 """Convert the given value to an "offset or limit" clause.
87 This handles incoming integers and converts to an expression; if
88 an expression is already given, it is passed through.
90 """
91 if element is None:
92 return None
93 elif hasattr(element, "__clause_element__"):
94 return element.__clause_element__()
95 elif isinstance(element, Visitable):
96 return element
97 else:
98 value = util.asint(element)
99 return _OffsetLimitParam(name, value, type_=type_, unique=True)
102def _offset_or_limit_clause_asint(clause, attrname):
103 """Convert the "offset or limit" clause of a select construct to an
104 integer.
106 This is only possible if the value is stored as a simple bound parameter.
107 Otherwise, a compilation error is raised.
109 """
110 if clause is None:
111 return None
112 try:
113 value = clause._limit_offset_value
114 except AttributeError as err:
115 util.raise_(
116 exc.CompileError(
117 "This SELECT structure does not use a simple "
118 "integer value for %s" % attrname
119 ),
120 replace_context=err,
121 )
122 else:
123 return util.asint(value)
126def subquery(alias, *args, **kwargs):
127 r"""Return an :class:`_expression.Alias` object derived
128 from a :class:`_expression.Select`.
130 name
131 alias name
133 \*args, \**kwargs
135 all other arguments are delivered to the
136 :func:`select` function.
138 """
139 return Select(*args, **kwargs).alias(alias)
142class Selectable(ClauseElement):
143 """mark a class as being selectable"""
145 __visit_name__ = "selectable"
147 is_selectable = True
149 @property
150 def selectable(self):
151 return self
154class HasPrefixes(object):
155 _prefixes = ()
157 @_generative
158 @_document_text_coercion(
159 "expr",
160 ":meth:`_expression.HasPrefixes.prefix_with`",
161 ":paramref:`.HasPrefixes.prefix_with.*expr`",
162 )
163 def prefix_with(self, *expr, **kw):
164 r"""Add one or more expressions following the statement keyword, i.e.
165 SELECT, INSERT, UPDATE, or DELETE. Generative.
167 This is used to support backend-specific prefix keywords such as those
168 provided by MySQL.
170 E.g.::
172 stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")
174 # MySQL 5.7 optimizer hints
175 stmt = select([table]).prefix_with(
176 "/*+ BKA(t1) */", dialect="mysql")
178 Multiple prefixes can be specified by multiple calls
179 to :meth:`_expression.HasPrefixes.prefix_with`.
181 :param \*expr: textual or :class:`_expression.ClauseElement`
182 construct which
183 will be rendered following the INSERT, UPDATE, or DELETE
184 keyword.
185 :param \**kw: A single keyword 'dialect' is accepted. This is an
186 optional string dialect name which will
187 limit rendering of this prefix to only that dialect.
189 """
190 dialect = kw.pop("dialect", None)
191 if kw:
192 raise exc.ArgumentError(
193 "Unsupported argument(s): %s" % ",".join(kw)
194 )
195 self._setup_prefixes(expr, dialect)
197 def _setup_prefixes(self, prefixes, dialect=None):
198 self._prefixes = self._prefixes + tuple(
199 [
200 (_literal_as_text(p, allow_coercion_to_text=True), dialect)
201 for p in prefixes
202 ]
203 )
206class HasSuffixes(object):
207 _suffixes = ()
209 @_generative
210 @_document_text_coercion(
211 "expr",
212 ":meth:`_expression.HasSuffixes.suffix_with`",
213 ":paramref:`.HasSuffixes.suffix_with.*expr`",
214 )
215 def suffix_with(self, *expr, **kw):
216 r"""Add one or more expressions following the statement as a whole.
218 This is used to support backend-specific suffix keywords on
219 certain constructs.
221 E.g.::
223 stmt = select([col1, col2]).cte().suffix_with(
224 "cycle empno set y_cycle to 1 default 0", dialect="oracle")
226 Multiple suffixes can be specified by multiple calls
227 to :meth:`_expression.HasSuffixes.suffix_with`.
229 :param \*expr: textual or :class:`_expression.ClauseElement`
230 construct which
231 will be rendered following the target clause.
232 :param \**kw: A single keyword 'dialect' is accepted. This is an
233 optional string dialect name which will
234 limit rendering of this suffix to only that dialect.
236 """
237 dialect = kw.pop("dialect", None)
238 if kw:
239 raise exc.ArgumentError(
240 "Unsupported argument(s): %s" % ",".join(kw)
241 )
242 self._setup_suffixes(expr, dialect)
244 def _setup_suffixes(self, suffixes, dialect=None):
245 self._suffixes = self._suffixes + tuple(
246 [
247 (_literal_as_text(p, allow_coercion_to_text=True), dialect)
248 for p in suffixes
249 ]
250 )
253class FromClause(Selectable):
254 """Represent an element that can be used within the ``FROM``
255 clause of a ``SELECT`` statement.
257 The most common forms of :class:`_expression.FromClause` are the
258 :class:`_schema.Table` and the :func:`_expression.select` constructs. Key
259 features common to all :class:`_expression.FromClause` objects include:
261 * a :attr:`.c` collection, which provides per-name access to a collection
262 of :class:`_expression.ColumnElement` objects.
263 * a :attr:`.primary_key` attribute, which is a collection of all those
264 :class:`_expression.ColumnElement`
265 objects that indicate the ``primary_key`` flag.
266 * Methods to generate various derivations of a "from" clause, including
267 :meth:`_expression.FromClause.alias`,
268 :meth:`_expression.FromClause.join`,
269 :meth:`_expression.FromClause.select`.
272 """
274 __visit_name__ = "fromclause"
275 named_with_column = False
276 _hide_froms = []
278 _is_join = False
279 _is_select = False
280 _is_from_container = False
282 _is_lateral = False
284 _textual = False
285 """a marker that allows us to easily distinguish a :class:`.TextAsFrom`
286 or similar object from other kinds of :class:`_expression.FromClause`
287 objects."""
289 schema = None
290 """Define the 'schema' attribute for this :class:`_expression.FromClause`.
292 This is typically ``None`` for most objects except that of
293 :class:`_schema.Table`, where it is taken as the value of the
294 :paramref:`_schema.Table.schema` argument.
296 """
298 def _translate_schema(self, effective_schema, map_):
299 return effective_schema
301 _memoized_property = util.group_expirable_memoized_property(["_columns"])
303 @util.deprecated(
304 "1.1",
305 message="The :meth:`.FromClause.count` method is deprecated, "
306 "and will be removed in a future release. Please use the "
307 ":class:`_functions.count` function available from the "
308 ":attr:`.func` namespace.",
309 )
310 @util.dependencies("sqlalchemy.sql.functions")
311 def count(self, functions, whereclause=None, **params):
312 """return a SELECT COUNT generated against this
313 :class:`_expression.FromClause`.
315 .. seealso::
317 :class:`_functions.count`
319 """
321 if self.primary_key:
322 col = list(self.primary_key)[0]
323 else:
324 col = list(self.columns)[0]
325 return Select(
326 [functions.func.count(col).label("tbl_row_count")],
327 whereclause,
328 from_obj=[self],
329 **params
330 )
332 def select(self, whereclause=None, **params):
333 """return a SELECT of this :class:`_expression.FromClause`.
335 .. seealso::
337 :func:`_expression.select` - general purpose
338 method which allows for arbitrary column lists.
340 """
342 return Select([self], whereclause, **params)
344 def join(self, right, onclause=None, isouter=False, full=False):
345 """Return a :class:`_expression.Join` from this
346 :class:`_expression.FromClause`
347 to another :class:`FromClause`.
349 E.g.::
351 from sqlalchemy import join
353 j = user_table.join(address_table,
354 user_table.c.id == address_table.c.user_id)
355 stmt = select([user_table]).select_from(j)
357 would emit SQL along the lines of::
359 SELECT user.id, user.name FROM user
360 JOIN address ON user.id = address.user_id
362 :param right: the right side of the join; this is any
363 :class:`_expression.FromClause` object such as a
364 :class:`_schema.Table` object, and
365 may also be a selectable-compatible object such as an ORM-mapped
366 class.
368 :param onclause: a SQL expression representing the ON clause of the
369 join. If left at ``None``, :meth:`_expression.FromClause.join`
370 will attempt to
371 join the two tables based on a foreign key relationship.
373 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
375 :param full: if True, render a FULL OUTER JOIN, instead of LEFT OUTER
376 JOIN. Implies :paramref:`.FromClause.join.isouter`.
378 .. versionadded:: 1.1
380 .. seealso::
382 :func:`_expression.join` - standalone function
384 :class:`_expression.Join` - the type of object produced
386 """
388 return Join(self, right, onclause, isouter, full)
390 def outerjoin(self, right, onclause=None, full=False):
391 """Return a :class:`_expression.Join` from this
392 :class:`_expression.FromClause`
393 to another :class:`FromClause`, with the "isouter" flag set to
394 True.
396 E.g.::
398 from sqlalchemy import outerjoin
400 j = user_table.outerjoin(address_table,
401 user_table.c.id == address_table.c.user_id)
403 The above is equivalent to::
405 j = user_table.join(
406 address_table,
407 user_table.c.id == address_table.c.user_id,
408 isouter=True)
410 :param right: the right side of the join; this is any
411 :class:`_expression.FromClause` object such as a
412 :class:`_schema.Table` object, and
413 may also be a selectable-compatible object such as an ORM-mapped
414 class.
416 :param onclause: a SQL expression representing the ON clause of the
417 join. If left at ``None``, :meth:`_expression.FromClause.join`
418 will attempt to
419 join the two tables based on a foreign key relationship.
421 :param full: if True, render a FULL OUTER JOIN, instead of
422 LEFT OUTER JOIN.
424 .. versionadded:: 1.1
426 .. seealso::
428 :meth:`_expression.FromClause.join`
430 :class:`_expression.Join`
432 """
434 return Join(self, right, onclause, True, full)
436 def alias(self, name=None, flat=False):
437 """return an alias of this :class:`_expression.FromClause`.
439 E.g.::
441 a2 = some_table.alias('a2')
443 The above code creates an :class:`_expression.Alias`
444 object which can be used
445 as a FROM clause in any SELECT statement.
447 .. seealso::
449 :ref:`core_tutorial_aliases`
451 :func:`_expression.alias`
453 """
455 return Alias._construct(self, name)
457 def lateral(self, name=None):
458 """Return a LATERAL alias of this :class:`_expression.FromClause`.
460 The return value is the :class:`_expression.Lateral` construct also
461 provided by the top-level :func:`_expression.lateral` function.
463 .. versionadded:: 1.1
465 .. seealso::
467 :ref:`lateral_selects` - overview of usage.
469 """
470 return Lateral._construct(self, name)
472 def tablesample(self, sampling, name=None, seed=None):
473 """Return a TABLESAMPLE alias of this :class:`_expression.FromClause`.
475 The return value is the :class:`_expression.TableSample`
476 construct also
477 provided by the top-level :func:`_expression.tablesample` function.
479 .. versionadded:: 1.1
481 .. seealso::
483 :func:`_expression.tablesample` - usage guidelines and parameters
485 """
486 return TableSample._construct(self, sampling, name, seed)
488 def is_derived_from(self, fromclause):
489 """Return True if this FromClause is 'derived' from the given
490 FromClause.
492 An example would be an Alias of a Table is derived from that Table.
494 """
495 # this is essentially an "identity" check in the base class.
496 # Other constructs override this to traverse through
497 # contained elements.
498 return fromclause in self._cloned_set
500 def _is_lexical_equivalent(self, other):
501 """Return True if this FromClause and the other represent
502 the same lexical identity.
504 This tests if either one is a copy of the other, or
505 if they are the same via annotation identity.
507 """
508 return self._cloned_set.intersection(other._cloned_set)
510 @util.dependencies("sqlalchemy.sql.util")
511 def replace_selectable(self, sqlutil, old, alias):
512 """replace all occurrences of FromClause 'old' with the given Alias
513 object, returning a copy of this :class:`_expression.FromClause`.
515 """
517 return sqlutil.ClauseAdapter(alias).traverse(self)
519 def correspond_on_equivalents(self, column, equivalents):
520 """Return corresponding_column for the given column, or if None
521 search for a match in the given dictionary.
523 """
524 col = self.corresponding_column(column, require_embedded=True)
525 if col is None and col in equivalents:
526 for equiv in equivalents[col]:
527 nc = self.corresponding_column(equiv, require_embedded=True)
528 if nc:
529 return nc
530 return col
532 def corresponding_column(self, column, require_embedded=False):
533 """Given a :class:`_expression.ColumnElement`, return the exported
534 :class:`_expression.ColumnElement` object from this
535 :class:`expression.Selectable`
536 which corresponds to that original
537 :class:`~sqlalchemy.schema.Column` via a common ancestor
538 column.
540 :param column: the target :class:`_expression.ColumnElement`
541 to be matched
543 :param require_embedded: only return corresponding columns for
544 the given :class:`_expression.ColumnElement`, if the given
545 :class:`_expression.ColumnElement`
546 is actually present within a sub-element
547 of this :class:`_expression.FromClause`.
548 Normally the column will match if
549 it merely shares a common ancestor with one of the exported
550 columns of this :class:`_expression.FromClause`.
552 """
554 def embedded(expanded_proxy_set, target_set):
555 for t in target_set.difference(expanded_proxy_set):
556 if not set(_expand_cloned([t])).intersection(
557 expanded_proxy_set
558 ):
559 return False
560 return True
562 # don't dig around if the column is locally present
563 if self.c.contains_column(column):
564 return column
565 col, intersect = None, None
566 target_set = column.proxy_set
567 cols = self.c._all_columns
568 for c in cols:
569 expanded_proxy_set = set(_expand_cloned(c.proxy_set))
570 i = target_set.intersection(expanded_proxy_set)
571 if i and (
572 not require_embedded
573 or embedded(expanded_proxy_set, target_set)
574 ):
575 if col is None:
577 # no corresponding column yet, pick this one.
579 col, intersect = c, i
580 elif len(i) > len(intersect):
582 # 'c' has a larger field of correspondence than
583 # 'col'. i.e. selectable.c.a1_x->a1.c.x->table.c.x
584 # matches a1.c.x->table.c.x better than
585 # selectable.c.x->table.c.x does.
587 col, intersect = c, i
588 elif i == intersect:
590 # they have the same field of correspondence. see
591 # which proxy_set has fewer columns in it, which
592 # indicates a closer relationship with the root
593 # column. Also take into account the "weight"
594 # attribute which CompoundSelect() uses to give
595 # higher precedence to columns based on vertical
596 # position in the compound statement, and discard
597 # columns that have no reference to the target
598 # column (also occurs with CompoundSelect)
600 col_distance = util.reduce(
601 operator.add,
602 [
603 sc._annotations.get("weight", 1)
604 for sc in col._uncached_proxy_set()
605 if sc.shares_lineage(column)
606 ],
607 )
608 c_distance = util.reduce(
609 operator.add,
610 [
611 sc._annotations.get("weight", 1)
612 for sc in c._uncached_proxy_set()
613 if sc.shares_lineage(column)
614 ],
615 )
616 if c_distance < col_distance:
617 col, intersect = c, i
618 return col
620 @property
621 def description(self):
622 """a brief description of this FromClause.
624 Used primarily for error message formatting.
626 """
627 return getattr(self, "name", self.__class__.__name__ + " object")
629 def _reset_exported(self):
630 """delete memoized collections when a FromClause is cloned."""
632 self._memoized_property.expire_instance(self)
634 @_memoized_property
635 def columns(self):
636 """A named-based collection of :class:`_expression.ColumnElement`
637 objects
638 maintained by this :class:`_expression.FromClause`.
640 The :attr:`.columns`, or :attr:`.c` collection, is the gateway
641 to the construction of SQL expressions using table-bound or
642 other selectable-bound columns::
644 select([mytable]).where(mytable.c.somecolumn == 5)
646 """
648 if "_columns" not in self.__dict__:
649 self._init_collections()
650 self._populate_column_collection()
651 return self._columns.as_immutable()
653 @_memoized_property
654 def primary_key(self):
655 """Return the collection of Column objects which comprise the
656 primary key of this FromClause."""
658 self._init_collections()
659 self._populate_column_collection()
660 return self.primary_key
662 @_memoized_property
663 def foreign_keys(self):
664 """Return the collection of ForeignKey objects which this
665 FromClause references."""
667 self._init_collections()
668 self._populate_column_collection()
669 return self.foreign_keys
671 c = property(
672 attrgetter("columns"),
673 doc="An alias for the :attr:`.columns` attribute.",
674 )
675 _select_iterable = property(attrgetter("columns"))
677 def _init_collections(self):
678 assert "_columns" not in self.__dict__
679 assert "primary_key" not in self.__dict__
680 assert "foreign_keys" not in self.__dict__
682 self._columns = ColumnCollection()
683 self.primary_key = ColumnSet()
684 self.foreign_keys = set()
686 @property
687 def _cols_populated(self):
688 return "_columns" in self.__dict__
690 def _populate_column_collection(self):
691 """Called on subclasses to establish the .c collection.
693 Each implementation has a different way of establishing
694 this collection.
696 """
698 def _refresh_for_new_column(self, column):
699 """Given a column added to the .c collection of an underlying
700 selectable, produce the local version of that column, assuming this
701 selectable ultimately should proxy this column.
703 this is used to "ping" a derived selectable to add a new column
704 to its .c. collection when a Column has been added to one of the
705 Table objects it ultimtely derives from.
707 If the given selectable hasn't populated its .c. collection yet,
708 it should at least pass on the message to the contained selectables,
709 but it will return None.
711 This method is currently used by Declarative to allow Table
712 columns to be added to a partially constructed inheritance
713 mapping that may have already produced joins. The method
714 isn't public right now, as the full span of implications
715 and/or caveats aren't yet clear.
717 It's also possible that this functionality could be invoked by
718 default via an event, which would require that
719 selectables maintain a weak referencing collection of all
720 derivations.
722 """
723 if not self._cols_populated:
724 return None
725 elif column.key in self.columns and self.columns[column.key] is column:
726 return column
727 else:
728 return None
731class Join(FromClause):
732 """represent a ``JOIN`` construct between two
733 :class:`_expression.FromClause`
734 elements.
736 The public constructor function for :class:`_expression.Join`
737 is the module-level
738 :func:`_expression.join()` function, as well as the
739 :meth:`_expression.FromClause.join` method
740 of any :class:`_expression.FromClause` (e.g. such as
741 :class:`_schema.Table`).
743 .. seealso::
745 :func:`_expression.join`
747 :meth:`_expression.FromClause.join`
749 """
751 __visit_name__ = "join"
753 _is_join = True
755 def __init__(self, left, right, onclause=None, isouter=False, full=False):
756 """Construct a new :class:`_expression.Join`.
758 The usual entrypoint here is the :func:`_expression.join`
759 function or the :meth:`_expression.FromClause.join` method of any
760 :class:`_expression.FromClause` object.
762 """
763 self.left = _interpret_as_from(left)
764 self.right = _interpret_as_from(right).self_group()
766 if onclause is None:
767 self.onclause = self._match_primaries(self.left, self.right)
768 else:
769 self.onclause = onclause
771 self.isouter = isouter
772 self.full = full
774 @classmethod
775 def _create_outerjoin(cls, left, right, onclause=None, full=False):
776 """Return an ``OUTER JOIN`` clause element.
778 The returned object is an instance of :class:`_expression.Join`.
780 Similar functionality is also available via the
781 :meth:`_expression.FromClause.outerjoin()` method on any
782 :class:`_expression.FromClause`.
784 :param left: The left side of the join.
786 :param right: The right side of the join.
788 :param onclause: Optional criterion for the ``ON`` clause, is
789 derived from foreign key relationships established between
790 left and right otherwise.
792 To chain joins together, use the :meth:`_expression.FromClause.join`
793 or
794 :meth:`_expression.FromClause.outerjoin` methods on the resulting
795 :class:`_expression.Join` object.
797 """
798 return cls(left, right, onclause, isouter=True, full=full)
800 @classmethod
801 def _create_join(
802 cls, left, right, onclause=None, isouter=False, full=False
803 ):
804 """Produce a :class:`_expression.Join` object, given two
805 :class:`_expression.FromClause`
806 expressions.
808 E.g.::
810 j = join(user_table, address_table,
811 user_table.c.id == address_table.c.user_id)
812 stmt = select([user_table]).select_from(j)
814 would emit SQL along the lines of::
816 SELECT user.id, user.name FROM user
817 JOIN address ON user.id = address.user_id
819 Similar functionality is available given any
820 :class:`_expression.FromClause` object (e.g. such as a
821 :class:`_schema.Table`) using
822 the :meth:`_expression.FromClause.join` method.
824 :param left: The left side of the join.
826 :param right: the right side of the join; this is any
827 :class:`_expression.FromClause` object such as a
828 :class:`_schema.Table` object, and
829 may also be a selectable-compatible object such as an ORM-mapped
830 class.
832 :param onclause: a SQL expression representing the ON clause of the
833 join. If left at ``None``, :meth:`_expression.FromClause.join`
834 will attempt to
835 join the two tables based on a foreign key relationship.
837 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
839 :param full: if True, render a FULL OUTER JOIN, instead of JOIN.
841 .. versionadded:: 1.1
843 .. seealso::
845 :meth:`_expression.FromClause.join` - method form,
846 based on a given left side
848 :class:`_expression.Join` - the type of object produced
850 """
852 return cls(left, right, onclause, isouter, full)
854 @property
855 def description(self):
856 return "Join object on %s(%d) and %s(%d)" % (
857 self.left.description,
858 id(self.left),
859 self.right.description,
860 id(self.right),
861 )
863 def is_derived_from(self, fromclause):
864 return (
865 fromclause is self
866 or self.left.is_derived_from(fromclause)
867 or self.right.is_derived_from(fromclause)
868 )
870 def self_group(self, against=None):
871 return FromGrouping(self)
873 @util.dependencies("sqlalchemy.sql.util")
874 def _populate_column_collection(self, sqlutil):
875 columns = [c for c in self.left.columns] + [
876 c for c in self.right.columns
877 ]
879 self.primary_key.extend(
880 sqlutil.reduce_columns(
881 (c for c in columns if c.primary_key), self.onclause
882 )
883 )
884 self._columns.update((col._label, col) for col in columns)
885 self.foreign_keys.update(
886 itertools.chain(*[col.foreign_keys for col in columns])
887 )
889 def _refresh_for_new_column(self, column):
890 col = self.left._refresh_for_new_column(column)
891 if col is None:
892 col = self.right._refresh_for_new_column(column)
893 if col is not None:
894 if self._cols_populated:
895 self._columns[col._label] = col
896 self.foreign_keys.update(col.foreign_keys)
897 if col.primary_key:
898 self.primary_key.add(col)
899 return col
900 return None
902 def _copy_internals(self, clone=_clone, **kw):
903 self._reset_exported()
904 self.left = clone(self.left, **kw)
905 self.right = clone(self.right, **kw)
906 self.onclause = clone(self.onclause, **kw)
908 def get_children(self, **kwargs):
909 return self.left, self.right, self.onclause
911 def _match_primaries(self, left, right):
912 if isinstance(left, Join):
913 left_right = left.right
914 else:
915 left_right = None
916 return self._join_condition(left, right, a_subset=left_right)
918 @classmethod
919 @util.deprecated_params(
920 ignore_nonexistent_tables=(
921 "0.9",
922 "The :paramref:`.join_condition.ignore_nonexistent_tables` "
923 "parameter is deprecated and will be removed in a future "
924 "release. Tables outside of the two tables being handled "
925 "are no longer considered.",
926 )
927 )
928 def _join_condition(
929 cls,
930 a,
931 b,
932 ignore_nonexistent_tables=False,
933 a_subset=None,
934 consider_as_foreign_keys=None,
935 ):
936 """create a join condition between two tables or selectables.
938 e.g.::
940 join_condition(tablea, tableb)
942 would produce an expression along the lines of::
944 tablea.c.id==tableb.c.tablea_id
946 The join is determined based on the foreign key relationships
947 between the two selectables. If there are multiple ways
948 to join, or no way to join, an error is raised.
950 :param ignore_nonexistent_tables: unused - tables outside of the
951 two tables being handled are not considered.
953 :param a_subset: An optional expression that is a sub-component
954 of ``a``. An attempt will be made to join to just this sub-component
955 first before looking at the full ``a`` construct, and if found
956 will be successful even if there are other ways to join to ``a``.
957 This allows the "right side" of a join to be passed thereby
958 providing a "natural join".
960 """
961 constraints = cls._joincond_scan_left_right(
962 a, a_subset, b, consider_as_foreign_keys
963 )
965 if len(constraints) > 1:
966 cls._joincond_trim_constraints(
967 a, b, constraints, consider_as_foreign_keys
968 )
970 if len(constraints) == 0:
971 if isinstance(b, FromGrouping):
972 hint = (
973 " Perhaps you meant to convert the right side to a "
974 "subquery using alias()?"
975 )
976 else:
977 hint = ""
978 raise exc.NoForeignKeysError(
979 "Can't find any foreign key relationships "
980 "between '%s' and '%s'.%s"
981 % (a.description, b.description, hint)
982 )
984 crit = [(x == y) for x, y in list(constraints.values())[0]]
985 if len(crit) == 1:
986 return crit[0]
987 else:
988 return and_(*crit)
990 @classmethod
991 def _can_join(cls, left, right, consider_as_foreign_keys=None):
992 if isinstance(left, Join):
993 left_right = left.right
994 else:
995 left_right = None
997 constraints = cls._joincond_scan_left_right(
998 a=left,
999 b=right,
1000 a_subset=left_right,
1001 consider_as_foreign_keys=consider_as_foreign_keys,
1002 )
1004 return bool(constraints)
1006 @classmethod
1007 def _joincond_scan_left_right(
1008 cls, a, a_subset, b, consider_as_foreign_keys
1009 ):
1010 constraints = collections.defaultdict(list)
1012 for left in (a_subset, a):
1013 if left is None:
1014 continue
1015 for fk in sorted(
1016 b.foreign_keys, key=lambda fk: fk.parent._creation_order
1017 ):
1018 if (
1019 consider_as_foreign_keys is not None
1020 and fk.parent not in consider_as_foreign_keys
1021 ):
1022 continue
1023 try:
1024 col = fk.get_referent(left)
1025 except exc.NoReferenceError as nrte:
1026 if nrte.table_name == left.name:
1027 raise
1028 else:
1029 continue
1031 if col is not None:
1032 constraints[fk.constraint].append((col, fk.parent))
1033 if left is not b:
1034 for fk in sorted(
1035 left.foreign_keys, key=lambda fk: fk.parent._creation_order
1036 ):
1037 if (
1038 consider_as_foreign_keys is not None
1039 and fk.parent not in consider_as_foreign_keys
1040 ):
1041 continue
1042 try:
1043 col = fk.get_referent(b)
1044 except exc.NoReferenceError as nrte:
1045 if nrte.table_name == b.name:
1046 raise
1047 else:
1048 continue
1050 if col is not None:
1051 constraints[fk.constraint].append((col, fk.parent))
1052 if constraints:
1053 break
1054 return constraints
1056 @classmethod
1057 def _joincond_trim_constraints(
1058 cls, a, b, constraints, consider_as_foreign_keys
1059 ):
1060 # more than one constraint matched. narrow down the list
1061 # to include just those FKCs that match exactly to
1062 # "consider_as_foreign_keys".
1063 if consider_as_foreign_keys:
1064 for const in list(constraints):
1065 if set(f.parent for f in const.elements) != set(
1066 consider_as_foreign_keys
1067 ):
1068 del constraints[const]
1070 # if still multiple constraints, but
1071 # they all refer to the exact same end result, use it.
1072 if len(constraints) > 1:
1073 dedupe = set(tuple(crit) for crit in constraints.values())
1074 if len(dedupe) == 1:
1075 key = list(constraints)[0]
1076 constraints = {key: constraints[key]}
1078 if len(constraints) != 1:
1079 raise exc.AmbiguousForeignKeysError(
1080 "Can't determine join between '%s' and '%s'; "
1081 "tables have more than one foreign key "
1082 "constraint relationship between them. "
1083 "Please specify the 'onclause' of this "
1084 "join explicitly." % (a.description, b.description)
1085 )
1087 def select(self, whereclause=None, **kwargs):
1088 r"""Create a :class:`_expression.Select` from this
1089 :class:`_expression.Join`.
1091 The equivalent long-hand form, given a :class:`_expression.Join`
1092 object
1093 ``j``, is::
1095 from sqlalchemy import select
1096 j = select([j.left, j.right], **kw).\
1097 where(whereclause).\
1098 select_from(j)
1100 :param whereclause: the WHERE criterion that will be sent to
1101 the :func:`select()` function
1103 :param \**kwargs: all other kwargs are sent to the
1104 underlying :func:`select()` function.
1106 """
1107 collist = [self.left, self.right]
1109 return Select(collist, whereclause, from_obj=[self], **kwargs)
1111 @property
1112 def bind(self):
1113 return self.left.bind or self.right.bind
1115 @util.dependencies("sqlalchemy.sql.util")
1116 def alias(self, sqlutil, name=None, flat=False):
1117 r"""return an alias of this :class:`_expression.Join`.
1119 The default behavior here is to first produce a SELECT
1120 construct from this :class:`_expression.Join`, then to produce an
1121 :class:`_expression.Alias` from that. So given a join of the form::
1123 j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
1125 The JOIN by itself would look like::
1127 table_a JOIN table_b ON table_a.id = table_b.a_id
1129 Whereas the alias of the above, ``j.alias()``, would in a
1130 SELECT context look like::
1132 (SELECT table_a.id AS table_a_id, table_b.id AS table_b_id,
1133 table_b.a_id AS table_b_a_id
1134 FROM table_a
1135 JOIN table_b ON table_a.id = table_b.a_id) AS anon_1
1137 The equivalent long-hand form, given a :class:`_expression.Join`
1138 object
1139 ``j``, is::
1141 from sqlalchemy import select, alias
1142 j = alias(
1143 select([j.left, j.right]).\
1144 select_from(j).\
1145 with_labels(True).\
1146 correlate(False),
1147 name=name
1148 )
1150 The selectable produced by :meth:`_expression.Join.alias`
1151 features the same
1152 columns as that of the two individual selectables presented under
1153 a single name - the individual columns are "auto-labeled", meaning
1154 the ``.c.`` collection of the resulting :class:`_expression.Alias`
1155 represents
1156 the names of the individual columns using a
1157 ``<tablename>_<columname>`` scheme::
1159 j.c.table_a_id
1160 j.c.table_b_a_id
1162 :meth:`_expression.Join.alias` also features an alternate
1163 option for aliasing joins which produces no enclosing SELECT and
1164 does not normally apply labels to the column names. The
1165 ``flat=True`` option will call :meth:`_expression.FromClause.alias`
1166 against the left and right sides individually.
1167 Using this option, no new ``SELECT`` is produced;
1168 we instead, from a construct as below::
1170 j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
1171 j = j.alias(flat=True)
1173 we get a result like this::
1175 table_a AS table_a_1 JOIN table_b AS table_b_1 ON
1176 table_a_1.id = table_b_1.a_id
1178 The ``flat=True`` argument is also propagated to the contained
1179 selectables, so that a composite join such as::
1181 j = table_a.join(
1182 table_b.join(table_c,
1183 table_b.c.id == table_c.c.b_id),
1184 table_b.c.a_id == table_a.c.id
1185 ).alias(flat=True)
1187 Will produce an expression like::
1189 table_a AS table_a_1 JOIN (
1190 table_b AS table_b_1 JOIN table_c AS table_c_1
1191 ON table_b_1.id = table_c_1.b_id
1192 ) ON table_a_1.id = table_b_1.a_id
1194 The standalone :func:`_expression.alias` function as well as the
1195 base :meth:`_expression.FromClause.alias`
1196 method also support the ``flat=True``
1197 argument as a no-op, so that the argument can be passed to the
1198 ``alias()`` method of any selectable.
1200 .. versionadded:: 0.9.0 Added the ``flat=True`` option to create
1201 "aliases" of joins without enclosing inside of a SELECT
1202 subquery.
1204 :param name: name given to the alias.
1206 :param flat: if True, produce an alias of the left and right
1207 sides of this :class:`_expression.Join` and return the join of those
1208 two selectables. This produces join expression that does not
1209 include an enclosing SELECT.
1211 .. versionadded:: 0.9.0
1213 .. seealso::
1215 :ref:`core_tutorial_aliases`
1217 :func:`_expression.alias`
1219 """
1220 if flat:
1221 assert name is None, "Can't send name argument with flat"
1222 left_a, right_a = (
1223 self.left.alias(flat=True),
1224 self.right.alias(flat=True),
1225 )
1226 adapter = sqlutil.ClauseAdapter(left_a).chain(
1227 sqlutil.ClauseAdapter(right_a)
1228 )
1230 return left_a.join(
1231 right_a,
1232 adapter.traverse(self.onclause),
1233 isouter=self.isouter,
1234 full=self.full,
1235 )
1236 else:
1237 return self.select(use_labels=True, correlate=False).alias(name)
1239 @property
1240 def _hide_froms(self):
1241 return itertools.chain(
1242 *[_from_objects(x.left, x.right) for x in self._cloned_set]
1243 )
1245 @property
1246 def _from_objects(self):
1247 return (
1248 [self]
1249 + self.onclause._from_objects
1250 + self.left._from_objects
1251 + self.right._from_objects
1252 )
1255class Alias(FromClause):
1256 """Represents an table or selectable alias (AS).
1258 Represents an alias, as typically applied to any table or
1259 sub-select within a SQL statement using the ``AS`` keyword (or
1260 without the keyword on certain databases such as Oracle).
1262 This object is constructed from the :func:`_expression.alias` module
1263 level function as well as the :meth:`_expression.FromClause.alias`
1264 method available
1265 on all :class:`_expression.FromClause` subclasses.
1267 """
1269 __visit_name__ = "alias"
1270 named_with_column = True
1272 _is_from_container = True
1274 def __init__(self, *arg, **kw):
1275 raise NotImplementedError(
1276 "The %s class is not intended to be constructed "
1277 "directly. Please use the %s() standalone "
1278 "function or the %s() method available from appropriate "
1279 "selectable objects."
1280 % (
1281 self.__class__.__name__,
1282 self.__class__.__name__.lower(),
1283 self.__class__.__name__.lower(),
1284 )
1285 )
1287 @classmethod
1288 def _construct(cls, *arg, **kw):
1289 obj = cls.__new__(cls)
1290 obj._init(*arg, **kw)
1291 return obj
1293 @classmethod
1294 def _factory(cls, selectable, name=None, flat=False):
1295 """Return an :class:`_expression.Alias` object.
1297 An :class:`_expression.Alias` represents any
1298 :class:`_expression.FromClause`
1299 with an alternate name assigned within SQL, typically using the ``AS``
1300 clause when generated, e.g. ``SELECT * FROM table AS aliasname``.
1302 Similar functionality is available via the
1303 :meth:`_expression.FromClause.alias` method
1304 available on all :class:`_expression.FromClause` subclasses.
1305 In terms of a
1306 SELECT object as generated from the :func:`_expression.select`
1307 function, the
1308 :meth:`_expression.SelectBase.alias` method returns an
1309 :class:`_expression.Alias` or
1310 similar object which represents a named, parenthesized subquery.
1312 When an :class:`_expression.Alias` is created from a
1313 :class:`_schema.Table` object,
1314 this has the effect of the table being rendered
1315 as ``tablename AS aliasname`` in a SELECT statement.
1317 For :func:`_expression.select` objects,
1318 the effect is that of creating a named
1319 subquery, i.e. ``(select ...) AS aliasname``.
1321 The ``name`` parameter is optional, and provides the name
1322 to use in the rendered SQL. If blank, an "anonymous" name
1323 will be deterministically generated at compile time.
1324 Deterministic means the name is guaranteed to be unique against
1325 other constructs used in the same statement, and will also be the
1326 same name for each successive compilation of the same statement
1327 object.
1329 :param selectable: any :class:`_expression.FromClause` subclass,
1330 such as a table, select statement, etc.
1332 :param name: string name to be assigned as the alias.
1333 If ``None``, a name will be deterministically generated
1334 at compile time.
1336 :param flat: Will be passed through to if the given selectable
1337 is an instance of :class:`_expression.Join` - see
1338 :meth:`_expression.Join.alias`
1339 for details.
1341 .. versionadded:: 0.9.0
1343 """
1344 return _interpret_as_from(selectable).alias(name=name, flat=flat)
1346 def _init(self, selectable, name=None):
1347 baseselectable = selectable
1348 while isinstance(baseselectable, Alias):
1349 baseselectable = baseselectable.element
1350 self.original = baseselectable
1351 self.supports_execution = baseselectable.supports_execution
1352 if self.supports_execution:
1353 self._execution_options = baseselectable._execution_options
1354 self.element = selectable
1355 if name is None:
1356 if self.original.named_with_column:
1357 name = getattr(self.original, "name", None)
1358 name = _anonymous_label("%%(%d %s)s" % (id(self), name or "anon"))
1359 self.name = name
1361 def self_group(self, against=None):
1362 if (
1363 isinstance(against, CompoundSelect)
1364 and isinstance(self.original, Select)
1365 and self.original._needs_parens_for_grouping()
1366 ):
1367 return FromGrouping(self)
1369 return super(Alias, self).self_group(against=against)
1371 @property
1372 def description(self):
1373 if util.py3k:
1374 return self.name
1375 else:
1376 return self.name.encode("ascii", "backslashreplace")
1378 def as_scalar(self):
1379 try:
1380 return self.element.as_scalar()
1381 except AttributeError as err:
1382 util.raise_(
1383 AttributeError(
1384 "Element %s does not support "
1385 "'as_scalar()'" % self.element
1386 ),
1387 replace_context=err,
1388 )
1390 def is_derived_from(self, fromclause):
1391 if fromclause in self._cloned_set:
1392 return True
1393 return self.element.is_derived_from(fromclause)
1395 def _populate_column_collection(self):
1396 for col in self.element.columns._all_columns:
1397 col._make_proxy(self)
1399 def _refresh_for_new_column(self, column):
1400 col = self.element._refresh_for_new_column(column)
1401 if col is not None:
1402 if not self._cols_populated:
1403 return None
1404 else:
1405 return col._make_proxy(self)
1406 else:
1407 return None
1409 def _copy_internals(self, clone=_clone, **kw):
1410 # don't apply anything to an aliased Table
1411 # for now. May want to drive this from
1412 # the given **kw.
1413 if isinstance(self.element, TableClause):
1414 return
1415 self._reset_exported()
1416 self.element = clone(self.element, **kw)
1417 baseselectable = self.element
1418 while isinstance(baseselectable, Alias):
1419 baseselectable = baseselectable.element
1420 self.original = baseselectable
1422 def get_children(self, column_collections=True, **kw):
1423 if column_collections:
1424 for c in self.c:
1425 yield c
1426 yield self.element
1428 @property
1429 def _from_objects(self):
1430 return [self]
1432 @property
1433 def bind(self):
1434 return self.element.bind
1437class Lateral(Alias):
1438 """Represent a LATERAL subquery.
1440 This object is constructed from the :func:`_expression.lateral` module
1441 level function as well as the :meth:`_expression.FromClause.lateral`
1442 method available
1443 on all :class:`_expression.FromClause` subclasses.
1445 While LATERAL is part of the SQL standard, currently only more recent
1446 PostgreSQL versions provide support for this keyword.
1448 .. versionadded:: 1.1
1450 .. seealso::
1452 :ref:`lateral_selects` - overview of usage.
1454 """
1456 __visit_name__ = "lateral"
1457 _is_lateral = True
1459 @classmethod
1460 def _factory(cls, selectable, name=None):
1461 """Return a :class:`_expression.Lateral` object.
1463 :class:`_expression.Lateral` is an :class:`_expression.Alias`
1464 subclass that represents
1465 a subquery with the LATERAL keyword applied to it.
1467 The special behavior of a LATERAL subquery is that it appears in the
1468 FROM clause of an enclosing SELECT, but may correlate to other
1469 FROM clauses of that SELECT. It is a special case of subquery
1470 only supported by a small number of backends, currently more recent
1471 PostgreSQL versions.
1473 .. versionadded:: 1.1
1475 .. seealso::
1477 :ref:`lateral_selects` - overview of usage.
1479 """
1480 return _interpret_as_from(selectable).lateral(name=name)
1483class TableSample(Alias):
1484 """Represent a TABLESAMPLE clause.
1486 This object is constructed from the :func:`_expression.tablesample` module
1487 level function as well as the :meth:`_expression.FromClause.tablesample`
1488 method
1489 available on all :class:`_expression.FromClause` subclasses.
1491 .. versionadded:: 1.1
1493 .. seealso::
1495 :func:`_expression.tablesample`
1497 """
1499 __visit_name__ = "tablesample"
1501 @classmethod
1502 def _factory(cls, selectable, sampling, name=None, seed=None):
1503 """Return a :class:`_expression.TableSample` object.
1505 :class:`_expression.TableSample` is an :class:`_expression.Alias`
1506 subclass that represents
1507 a table with the TABLESAMPLE clause applied to it.
1508 :func:`_expression.tablesample`
1509 is also available from the :class:`_expression.FromClause`
1510 class via the
1511 :meth:`_expression.FromClause.tablesample` method.
1513 The TABLESAMPLE clause allows selecting a randomly selected approximate
1514 percentage of rows from a table. It supports multiple sampling methods,
1515 most commonly BERNOULLI and SYSTEM.
1517 e.g.::
1519 from sqlalchemy import func
1521 selectable = people.tablesample(
1522 func.bernoulli(1),
1523 name='alias',
1524 seed=func.random())
1525 stmt = select([selectable.c.people_id])
1527 Assuming ``people`` with a column ``people_id``, the above
1528 statement would render as::
1530 SELECT alias.people_id FROM
1531 people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
1532 REPEATABLE (random())
1534 .. versionadded:: 1.1
1536 :param sampling: a ``float`` percentage between 0 and 100 or
1537 :class:`_functions.Function`.
1539 :param name: optional alias name
1541 :param seed: any real-valued SQL expression. When specified, the
1542 REPEATABLE sub-clause is also rendered.
1544 """
1545 return _interpret_as_from(selectable).tablesample(
1546 sampling, name=name, seed=seed
1547 )
1549 def _init(self, selectable, sampling, name=None, seed=None):
1550 self.sampling = sampling
1551 self.seed = seed
1552 super(TableSample, self)._init(selectable, name=name)
1554 @util.dependencies("sqlalchemy.sql.functions")
1555 def _get_method(self, functions):
1556 if isinstance(self.sampling, functions.Function):
1557 return self.sampling
1558 else:
1559 return functions.func.system(self.sampling)
1562class CTE(Generative, HasPrefixes, HasSuffixes, Alias):
1563 """Represent a Common Table Expression.
1565 The :class:`_expression.CTE` object is obtained using the
1566 :meth:`_expression.SelectBase.cte` method from any selectable.
1567 See that method for complete examples.
1569 """
1571 __visit_name__ = "cte"
1573 @classmethod
1574 def _factory(cls, selectable, name=None, recursive=False):
1575 r"""Return a new :class:`_expression.CTE`,
1576 or Common Table Expression instance.
1578 Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage.
1580 """
1581 return _interpret_as_from(selectable).cte(
1582 name=name, recursive=recursive
1583 )
1585 def _init(
1586 self,
1587 selectable,
1588 name=None,
1589 recursive=False,
1590 _cte_alias=None,
1591 _restates=frozenset(),
1592 _prefixes=None,
1593 _suffixes=None,
1594 ):
1595 self.recursive = recursive
1596 self._cte_alias = _cte_alias
1597 self._restates = _restates
1598 if _prefixes:
1599 self._prefixes = _prefixes
1600 if _suffixes:
1601 self._suffixes = _suffixes
1602 super(CTE, self)._init(selectable, name=name)
1604 def _copy_internals(self, clone=_clone, **kw):
1605 super(CTE, self)._copy_internals(clone, **kw)
1606 if self._cte_alias is not None:
1607 self._cte_alias = clone(self._cte_alias, **kw)
1608 self._restates = frozenset(
1609 [clone(elem, **kw) for elem in self._restates]
1610 )
1612 @util.dependencies("sqlalchemy.sql.dml")
1613 def _populate_column_collection(self, dml):
1614 if isinstance(self.element, dml.UpdateBase):
1615 for col in self.element._returning:
1616 col._make_proxy(self)
1617 else:
1618 for col in self.element.columns._all_columns:
1619 col._make_proxy(self)
1621 def alias(self, name=None, flat=False):
1622 """Return an :class:`_expression.Alias` of this
1623 :class:`_expression.CTE`.
1625 This method is a CTE-specific specialization of the
1626 :class:`_expression.FromClause.alias` method.
1628 .. seealso::
1630 :ref:`core_tutorial_aliases`
1632 :func:`_expression.alias`
1634 """
1635 return CTE._construct(
1636 self.original,
1637 name=name,
1638 recursive=self.recursive,
1639 _cte_alias=self,
1640 _prefixes=self._prefixes,
1641 _suffixes=self._suffixes,
1642 )
1644 def union(self, other):
1645 return CTE._construct(
1646 self.original.union(other),
1647 name=self.name,
1648 recursive=self.recursive,
1649 _restates=self._restates.union([self]),
1650 _prefixes=self._prefixes,
1651 _suffixes=self._suffixes,
1652 )
1654 def union_all(self, other):
1655 return CTE._construct(
1656 self.original.union_all(other),
1657 name=self.name,
1658 recursive=self.recursive,
1659 _restates=self._restates.union([self]),
1660 _prefixes=self._prefixes,
1661 _suffixes=self._suffixes,
1662 )
1665class HasCTE(object):
1666 """Mixin that declares a class to include CTE support.
1668 .. versionadded:: 1.1
1670 """
1672 def cte(self, name=None, recursive=False):
1673 r"""Return a new :class:`_expression.CTE`,
1674 or Common Table Expression instance.
1676 Common table expressions are a SQL standard whereby SELECT
1677 statements can draw upon secondary statements specified along
1678 with the primary statement, using a clause called "WITH".
1679 Special semantics regarding UNION can also be employed to
1680 allow "recursive" queries, where a SELECT statement can draw
1681 upon the set of rows that have previously been selected.
1683 CTEs can also be applied to DML constructs UPDATE, INSERT
1684 and DELETE on some databases, both as a source of CTE rows
1685 when combined with RETURNING, as well as a consumer of
1686 CTE rows.
1688 .. versionchanged:: 1.1 Added support for UPDATE/INSERT/DELETE as
1689 CTE, CTEs added to UPDATE/INSERT/DELETE.
1691 SQLAlchemy detects :class:`_expression.CTE` objects, which are treated
1692 similarly to :class:`_expression.Alias` objects, as special elements
1693 to be delivered to the FROM clause of the statement as well
1694 as to a WITH clause at the top of the statement.
1696 For special prefixes such as PostgreSQL "MATERIALIZED" and
1697 "NOT MATERIALIZED", the :meth:`_expression.CTE.prefix_with`
1698 method may be
1699 used to establish these.
1701 .. versionchanged:: 1.3.13 Added support for prefixes.
1702 In particular - MATERIALIZED and NOT MATERIALIZED.
1704 :param name: name given to the common table expression. Like
1705 :meth:`._FromClause.alias`, the name can be left as ``None``
1706 in which case an anonymous symbol will be used at query
1707 compile time.
1708 :param recursive: if ``True``, will render ``WITH RECURSIVE``.
1709 A recursive common table expression is intended to be used in
1710 conjunction with UNION ALL in order to derive rows
1711 from those already selected.
1713 The following examples include two from PostgreSQL's documentation at
1714 http://www.postgresql.org/docs/current/static/queries-with.html,
1715 as well as additional examples.
1717 Example 1, non recursive::
1719 from sqlalchemy import (Table, Column, String, Integer,
1720 MetaData, select, func)
1722 metadata = MetaData()
1724 orders = Table('orders', metadata,
1725 Column('region', String),
1726 Column('amount', Integer),
1727 Column('product', String),
1728 Column('quantity', Integer)
1729 )
1731 regional_sales = select([
1732 orders.c.region,
1733 func.sum(orders.c.amount).label('total_sales')
1734 ]).group_by(orders.c.region).cte("regional_sales")
1737 top_regions = select([regional_sales.c.region]).\
1738 where(
1739 regional_sales.c.total_sales >
1740 select([
1741 func.sum(regional_sales.c.total_sales)/10
1742 ])
1743 ).cte("top_regions")
1745 statement = select([
1746 orders.c.region,
1747 orders.c.product,
1748 func.sum(orders.c.quantity).label("product_units"),
1749 func.sum(orders.c.amount).label("product_sales")
1750 ]).where(orders.c.region.in_(
1751 select([top_regions.c.region])
1752 )).group_by(orders.c.region, orders.c.product)
1754 result = conn.execute(statement).fetchall()
1756 Example 2, WITH RECURSIVE::
1758 from sqlalchemy import (Table, Column, String, Integer,
1759 MetaData, select, func)
1761 metadata = MetaData()
1763 parts = Table('parts', metadata,
1764 Column('part', String),
1765 Column('sub_part', String),
1766 Column('quantity', Integer),
1767 )
1769 included_parts = select([
1770 parts.c.sub_part,
1771 parts.c.part,
1772 parts.c.quantity]).\
1773 where(parts.c.part=='our part').\
1774 cte(recursive=True)
1777 incl_alias = included_parts.alias()
1778 parts_alias = parts.alias()
1779 included_parts = included_parts.union_all(
1780 select([
1781 parts_alias.c.sub_part,
1782 parts_alias.c.part,
1783 parts_alias.c.quantity
1784 ]).
1785 where(parts_alias.c.part==incl_alias.c.sub_part)
1786 )
1788 statement = select([
1789 included_parts.c.sub_part,
1790 func.sum(included_parts.c.quantity).
1791 label('total_quantity')
1792 ]).\
1793 group_by(included_parts.c.sub_part)
1795 result = conn.execute(statement).fetchall()
1797 Example 3, an upsert using UPDATE and INSERT with CTEs::
1799 from datetime import date
1800 from sqlalchemy import (MetaData, Table, Column, Integer,
1801 Date, select, literal, and_, exists)
1803 metadata = MetaData()
1805 visitors = Table('visitors', metadata,
1806 Column('product_id', Integer, primary_key=True),
1807 Column('date', Date, primary_key=True),
1808 Column('count', Integer),
1809 )
1811 # add 5 visitors for the product_id == 1
1812 product_id = 1
1813 day = date.today()
1814 count = 5
1816 update_cte = (
1817 visitors.update()
1818 .where(and_(visitors.c.product_id == product_id,
1819 visitors.c.date == day))
1820 .values(count=visitors.c.count + count)
1821 .returning(literal(1))
1822 .cte('update_cte')
1823 )
1825 upsert = visitors.insert().from_select(
1826 [visitors.c.product_id, visitors.c.date, visitors.c.count],
1827 select([literal(product_id), literal(day), literal(count)])
1828 .where(~exists(update_cte.select()))
1829 )
1831 connection.execute(upsert)
1833 .. seealso::
1835 :meth:`.orm.query.Query.cte` - ORM version of
1836 :meth:`_expression.HasCTE.cte`.
1838 """
1839 return CTE._construct(self, name=name, recursive=recursive)
1842class FromGrouping(FromClause):
1843 """Represent a grouping of a FROM clause"""
1845 __visit_name__ = "grouping"
1847 def __init__(self, element):
1848 self.element = element
1850 def _init_collections(self):
1851 pass
1853 @property
1854 def columns(self):
1855 return self.element.columns
1857 @property
1858 def primary_key(self):
1859 return self.element.primary_key
1861 @property
1862 def foreign_keys(self):
1863 return self.element.foreign_keys
1865 def is_derived_from(self, element):
1866 return self.element.is_derived_from(element)
1868 def alias(self, **kw):
1869 return FromGrouping(self.element.alias(**kw))
1871 @property
1872 def _hide_froms(self):
1873 return self.element._hide_froms
1875 def get_children(self, **kwargs):
1876 return (self.element,)
1878 def _copy_internals(self, clone=_clone, **kw):
1879 self.element = clone(self.element, **kw)
1881 @property
1882 def _from_objects(self):
1883 return self.element._from_objects
1885 def __getattr__(self, attr):
1886 return getattr(self.element, attr)
1888 def __getstate__(self):
1889 return {"element": self.element}
1891 def __setstate__(self, state):
1892 self.element = state["element"]
1895class TableClause(Immutable, FromClause):
1896 """Represents a minimal "table" construct.
1898 This is a lightweight table object that has only a name, a
1899 collection of columns, which are typically produced
1900 by the :func:`_expression.column` function, and a schema::
1902 from sqlalchemy import table, column
1904 user = table("user",
1905 column("id"),
1906 column("name"),
1907 column("description"),
1908 )
1910 The :class:`_expression.TableClause` construct serves as the base for
1911 the more commonly used :class:`_schema.Table` object, providing
1912 the usual set of :class:`_expression.FromClause` services including
1913 the ``.c.`` collection and statement generation methods.
1915 It does **not** provide all the additional schema-level services
1916 of :class:`_schema.Table`, including constraints, references to other
1917 tables, or support for :class:`_schema.MetaData`-level services.
1918 It's useful
1919 on its own as an ad-hoc construct used to generate quick SQL
1920 statements when a more fully fledged :class:`_schema.Table`
1921 is not on hand.
1923 """
1925 __visit_name__ = "table"
1927 named_with_column = True
1929 implicit_returning = False
1930 """:class:`_expression.TableClause`
1931 doesn't support having a primary key or column
1932 -level defaults, so implicit returning doesn't apply."""
1934 _autoincrement_column = None
1935 """No PK or default support so no autoincrement column."""
1937 def __init__(self, name, *columns, **kw):
1938 """Produce a new :class:`_expression.TableClause`.
1940 The object returned is an instance of :class:`_expression.TableClause`
1941 , which
1942 represents the "syntactical" portion of the schema-level
1943 :class:`_schema.Table` object.
1944 It may be used to construct lightweight table constructs.
1946 .. versionchanged:: 1.0.0 :func:`_expression.table` can now
1947 be imported from the plain ``sqlalchemy`` namespace like any
1948 other SQL element.
1951 :param name: Name of the table.
1953 :param columns: A collection of :func:`_expression.column` constructs.
1955 :param schema: The schema name for this table.
1957 .. versionadded:: 1.3.18 :func:`_expression.table` can now
1958 accept a ``schema`` argument.
1959 """
1961 super(TableClause, self).__init__()
1962 self.name = self.fullname = name
1963 self._columns = ColumnCollection()
1964 self.primary_key = ColumnSet()
1965 self.foreign_keys = set()
1966 for c in columns:
1967 self.append_column(c)
1969 schema = kw.pop("schema", None)
1970 if schema is not None:
1971 self.schema = schema
1972 if kw:
1973 raise exc.ArgumentError("Unsupported argument(s): %s" % list(kw))
1975 def _init_collections(self):
1976 pass
1978 @util.memoized_property
1979 def description(self):
1980 if util.py3k:
1981 return self.name
1982 else:
1983 return self.name.encode("ascii", "backslashreplace")
1985 def append_column(self, c):
1986 self._columns[c.key] = c
1987 c.table = self
1989 def get_children(self, column_collections=True, **kwargs):
1990 if column_collections:
1991 return [c for c in self.c]
1992 else:
1993 return []
1995 @util.dependencies("sqlalchemy.sql.dml")
1996 def insert(self, dml, values=None, inline=False, **kwargs):
1997 """Generate an :func:`_expression.insert` construct against this
1998 :class:`_expression.TableClause`.
2000 E.g.::
2002 table.insert().values(name='foo')
2004 See :func:`_expression.insert` for argument and usage information.
2006 """
2008 return dml.Insert(self, values=values, inline=inline, **kwargs)
2010 @util.dependencies("sqlalchemy.sql.dml")
2011 def update(
2012 self, dml, whereclause=None, values=None, inline=False, **kwargs
2013 ):
2014 """Generate an :func:`_expression.update` construct against this
2015 :class:`_expression.TableClause`.
2017 E.g.::
2019 table.update().where(table.c.id==7).values(name='foo')
2021 See :func:`_expression.update` for argument and usage information.
2023 """
2025 return dml.Update(
2026 self,
2027 whereclause=whereclause,
2028 values=values,
2029 inline=inline,
2030 **kwargs
2031 )
2033 @util.dependencies("sqlalchemy.sql.dml")
2034 def delete(self, dml, whereclause=None, **kwargs):
2035 """Generate a :func:`_expression.delete` construct against this
2036 :class:`_expression.TableClause`.
2038 E.g.::
2040 table.delete().where(table.c.id==7)
2042 See :func:`_expression.delete` for argument and usage information.
2044 """
2046 return dml.Delete(self, whereclause, **kwargs)
2048 @property
2049 def _from_objects(self):
2050 return [self]
2053class ForUpdateArg(ClauseElement):
2054 @classmethod
2055 def parse_legacy_select(self, arg):
2056 """Parse the for_update argument of :func:`_expression.select`.
2058 :param mode: Defines the lockmode to use.
2060 ``None`` - translates to no lockmode
2062 ``'update'`` - translates to ``FOR UPDATE``
2063 (standard SQL, supported by most dialects)
2065 ``'nowait'`` - translates to ``FOR UPDATE NOWAIT``
2066 (supported by Oracle, PostgreSQL 8.1 upwards)
2068 ``'read'`` - translates to ``LOCK IN SHARE MODE`` (for MySQL),
2069 and ``FOR SHARE`` (for PostgreSQL)
2071 ``'read_nowait'`` - translates to ``FOR SHARE NOWAIT``
2072 (supported by PostgreSQL). ``FOR SHARE`` and
2073 ``FOR SHARE NOWAIT`` (PostgreSQL).
2075 """
2076 if arg in (None, False):
2077 return None
2079 nowait = read = False
2080 if arg == "nowait":
2081 nowait = True
2082 elif arg == "read":
2083 read = True
2084 elif arg == "read_nowait":
2085 read = nowait = True
2086 elif arg is not True:
2087 raise exc.ArgumentError("Unknown for_update argument: %r" % arg)
2089 return ForUpdateArg(read=read, nowait=nowait)
2091 @property
2092 def legacy_for_update_value(self):
2093 if self.read and not self.nowait:
2094 return "read"
2095 elif self.read and self.nowait:
2096 return "read_nowait"
2097 elif self.nowait:
2098 return "nowait"
2099 else:
2100 return True
2102 def __eq__(self, other):
2103 return (
2104 isinstance(other, ForUpdateArg)
2105 and other.nowait == self.nowait
2106 and other.read == self.read
2107 and other.skip_locked == self.skip_locked
2108 and other.key_share == self.key_share
2109 and other.of is self.of
2110 )
2112 def __hash__(self):
2113 return id(self)
2115 def _copy_internals(self, clone=_clone, **kw):
2116 if self.of is not None:
2117 self.of = [clone(col, **kw) for col in self.of]
2119 def __init__(
2120 self,
2121 nowait=False,
2122 read=False,
2123 of=None,
2124 skip_locked=False,
2125 key_share=False,
2126 ):
2127 """Represents arguments specified to
2128 :meth:`_expression.Select.for_update`.
2130 .. versionadded:: 0.9.0
2132 """
2134 self.nowait = nowait
2135 self.read = read
2136 self.skip_locked = skip_locked
2137 self.key_share = key_share
2138 if of is not None:
2139 self.of = [
2140 _interpret_as_column_or_from(elem) for elem in util.to_list(of)
2141 ]
2142 else:
2143 self.of = None
2146class SelectBase(HasCTE, Executable, FromClause):
2147 """Base class for SELECT statements.
2150 This includes :class:`_expression.Select`,
2151 :class:`_selectable.CompoundSelect` and
2152 :class:`.TextAsFrom`.
2155 """
2157 def as_scalar(self):
2158 """return a 'scalar' representation of this selectable, which can be
2159 used as a column expression.
2161 Typically, a select statement which has only one column in its columns
2162 clause is eligible to be used as a scalar expression.
2164 The returned object is an instance of
2165 :class:`ScalarSelect`.
2167 """
2168 return ScalarSelect(self)
2170 def label(self, name):
2171 """return a 'scalar' representation of this selectable, embedded as a
2172 subquery with a label.
2174 .. seealso::
2176 :meth:`_expression.SelectBase.as_scalar`.
2178 """
2179 return self.as_scalar().label(name)
2181 @_generative
2182 @util.deprecated(
2183 "0.6",
2184 message="The :meth:`.SelectBase.autocommit` method is deprecated, "
2185 "and will be removed in a future release. Please use the "
2186 "the :paramref:`.Connection.execution_options.autocommit` "
2187 "parameter in conjunction with the "
2188 ":meth:`.Executable.execution_options` method.",
2189 )
2190 def autocommit(self):
2191 """return a new selectable with the 'autocommit' flag set to
2192 True.
2193 """
2195 self._execution_options = self._execution_options.union(
2196 {"autocommit": True}
2197 )
2199 def _generate(self):
2200 """Override the default _generate() method to also clear out
2201 exported collections."""
2203 s = self.__class__.__new__(self.__class__)
2204 s.__dict__ = self.__dict__.copy()
2205 s._reset_exported()
2206 return s
2208 @property
2209 def _from_objects(self):
2210 return [self]
2213class GenerativeSelect(SelectBase):
2214 """Base class for SELECT statements where additional elements can be
2215 added.
2217 This serves as the base for :class:`_expression.Select` and
2218 :class:`_selectable.CompoundSelect`
2219 where elements such as ORDER BY, GROUP BY can be added and column
2220 rendering can be controlled. Compare to :class:`.TextAsFrom`, which,
2221 while it subclasses :class:`_expression.SelectBase`
2222 and is also a SELECT construct,
2223 represents a fixed textual string which cannot be altered at this level,
2224 only wrapped as a subquery.
2226 .. versionadded:: 0.9.0 :class:`_expression.GenerativeSelect` was added to
2227 provide functionality specific to :class:`_expression.Select` and
2228 :class:`_selectable.CompoundSelect` while allowing
2229 :class:`_expression.SelectBase` to be
2230 used for other SELECT-like objects, e.g. :class:`.TextAsFrom`.
2232 """
2234 _order_by_clause = ClauseList()
2235 _group_by_clause = ClauseList()
2236 _limit_clause = None
2237 _offset_clause = None
2238 _for_update_arg = None
2240 def __init__(
2241 self,
2242 use_labels=False,
2243 for_update=False,
2244 limit=None,
2245 offset=None,
2246 order_by=None,
2247 group_by=None,
2248 bind=None,
2249 autocommit=None,
2250 ):
2251 self.use_labels = use_labels
2253 if for_update is not False:
2254 self._for_update_arg = ForUpdateArg.parse_legacy_select(for_update)
2256 if autocommit is not None:
2257 util.warn_deprecated(
2258 "The select.autocommit parameter is deprecated and will be "
2259 "removed in a future release. Please refer to the "
2260 "Select.execution_options.autocommit` parameter."
2261 )
2262 self._execution_options = self._execution_options.union(
2263 {"autocommit": autocommit}
2264 )
2265 if limit is not None:
2266 self._limit_clause = _offset_or_limit_clause(limit)
2267 if offset is not None:
2268 self._offset_clause = _offset_or_limit_clause(offset)
2269 self._bind = bind
2271 if order_by is not None:
2272 self._order_by_clause = ClauseList(
2273 *util.to_list(order_by),
2274 _literal_as_text=_literal_and_labels_as_label_reference
2275 )
2276 if group_by is not None:
2277 self._group_by_clause = ClauseList(
2278 *util.to_list(group_by),
2279 _literal_as_text=_literal_as_label_reference
2280 )
2282 @property
2283 def for_update(self):
2284 """Provide legacy dialect support for the ``for_update`` attribute.
2285 """
2286 if self._for_update_arg is not None:
2287 return self._for_update_arg.legacy_for_update_value
2288 else:
2289 return None
2291 @for_update.setter
2292 def for_update(self, value):
2293 self._for_update_arg = ForUpdateArg.parse_legacy_select(value)
2295 @_generative
2296 def with_for_update(
2297 self,
2298 nowait=False,
2299 read=False,
2300 of=None,
2301 skip_locked=False,
2302 key_share=False,
2303 ):
2304 """Specify a ``FOR UPDATE`` clause for this
2305 :class:`_expression.GenerativeSelect`.
2307 E.g.::
2309 stmt = select([table]).with_for_update(nowait=True)
2311 On a database like PostgreSQL or Oracle, the above would render a
2312 statement like::
2314 SELECT table.a, table.b FROM table FOR UPDATE NOWAIT
2316 on other backends, the ``nowait`` option is ignored and instead
2317 would produce::
2319 SELECT table.a, table.b FROM table FOR UPDATE
2321 When called with no arguments, the statement will render with
2322 the suffix ``FOR UPDATE``. Additional arguments can then be
2323 provided which allow for common database-specific
2324 variants.
2326 :param nowait: boolean; will render ``FOR UPDATE NOWAIT`` on Oracle
2327 and PostgreSQL dialects.
2329 :param read: boolean; will render ``LOCK IN SHARE MODE`` on MySQL,
2330 ``FOR SHARE`` on PostgreSQL. On PostgreSQL, when combined with
2331 ``nowait``, will render ``FOR SHARE NOWAIT``.
2333 :param of: SQL expression or list of SQL expression elements
2334 (typically :class:`_schema.Column`
2335 objects or a compatible expression) which
2336 will render into a ``FOR UPDATE OF`` clause; supported by PostgreSQL
2337 and Oracle. May render as a table or as a column depending on
2338 backend.
2340 :param skip_locked: boolean, will render ``FOR UPDATE SKIP LOCKED``
2341 on Oracle and PostgreSQL dialects or ``FOR SHARE SKIP LOCKED`` if
2342 ``read=True`` is also specified.
2344 .. versionadded:: 1.1.0
2346 :param key_share: boolean, will render ``FOR NO KEY UPDATE``,
2347 or if combined with ``read=True`` will render ``FOR KEY SHARE``,
2348 on the PostgreSQL dialect.
2350 .. versionadded:: 1.1.0
2352 """
2353 self._for_update_arg = ForUpdateArg(
2354 nowait=nowait,
2355 read=read,
2356 of=of,
2357 skip_locked=skip_locked,
2358 key_share=key_share,
2359 )
2361 @_generative
2362 def apply_labels(self):
2363 """return a new selectable with the 'use_labels' flag set to True.
2365 This will result in column expressions being generated using labels
2366 against their table name, such as "SELECT somecolumn AS
2367 tablename_somecolumn". This allows selectables which contain multiple
2368 FROM clauses to produce a unique set of column names regardless of
2369 name conflicts among the individual FROM clauses.
2371 """
2372 self.use_labels = True
2374 @property
2375 def _limit(self):
2376 """Get an integer value for the limit. This should only be used
2377 by code that cannot support a limit as a BindParameter or
2378 other custom clause as it will throw an exception if the limit
2379 isn't currently set to an integer.
2381 """
2382 return _offset_or_limit_clause_asint(self._limit_clause, "limit")
2384 @property
2385 def _simple_int_limit(self):
2386 """True if the LIMIT clause is a simple integer, False
2387 if it is not present or is a SQL expression.
2388 """
2389 return isinstance(self._limit_clause, _OffsetLimitParam)
2391 @property
2392 def _simple_int_offset(self):
2393 """True if the OFFSET clause is a simple integer, False
2394 if it is not present or is a SQL expression.
2395 """
2396 return isinstance(self._offset_clause, _OffsetLimitParam)
2398 @property
2399 def _offset(self):
2400 """Get an integer value for the offset. This should only be used
2401 by code that cannot support an offset as a BindParameter or
2402 other custom clause as it will throw an exception if the
2403 offset isn't currently set to an integer.
2405 """
2406 return _offset_or_limit_clause_asint(self._offset_clause, "offset")
2408 @_generative
2409 def limit(self, limit):
2410 """return a new selectable with the given LIMIT criterion
2411 applied.
2413 This is a numerical value which usually renders as a ``LIMIT``
2414 expression in the resulting select. Backends that don't
2415 support ``LIMIT`` will attempt to provide similar
2416 functionality.
2418 .. versionchanged:: 1.0.0 - :meth:`_expression.Select.limit` can now
2419 accept arbitrary SQL expressions as well as integer values.
2421 :param limit: an integer LIMIT parameter, or a SQL expression
2422 that provides an integer result.
2424 """
2426 self._limit_clause = _offset_or_limit_clause(limit)
2428 @_generative
2429 def offset(self, offset):
2430 """return a new selectable with the given OFFSET criterion
2431 applied.
2434 This is a numeric value which usually renders as an ``OFFSET``
2435 expression in the resulting select. Backends that don't
2436 support ``OFFSET`` will attempt to provide similar
2437 functionality.
2440 .. versionchanged:: 1.0.0 - :meth:`_expression.Select.offset` can now
2441 accept arbitrary SQL expressions as well as integer values.
2443 :param offset: an integer OFFSET parameter, or a SQL expression
2444 that provides an integer result.
2446 """
2448 self._offset_clause = _offset_or_limit_clause(offset)
2450 @_generative
2451 def order_by(self, *clauses):
2452 r"""return a new selectable with the given list of ORDER BY
2453 criterion applied.
2455 e.g.::
2457 stmt = select([table]).order_by(table.c.id, table.c.name)
2459 :param \*order_by: a series of :class:`_expression.ColumnElement`
2460 constructs
2461 which will be used to generate an ORDER BY clause.
2463 .. seealso::
2465 :ref:`core_tutorial_ordering`
2467 """
2469 self.append_order_by(*clauses)
2471 @_generative
2472 def group_by(self, *clauses):
2473 r"""return a new selectable with the given list of GROUP BY
2474 criterion applied.
2476 e.g.::
2478 stmt = select([table.c.name, func.max(table.c.stat)]).\
2479 group_by(table.c.name)
2481 :param \*group_by: a series of :class:`_expression.ColumnElement`
2482 constructs
2483 which will be used to generate an GROUP BY clause.
2485 .. seealso::
2487 :ref:`core_tutorial_ordering`
2489 """
2491 self.append_group_by(*clauses)
2493 def append_order_by(self, *clauses):
2494 """Append the given ORDER BY criterion applied to this selectable.
2496 The criterion will be appended to any pre-existing ORDER BY criterion.
2498 This is an **in-place** mutation method; the
2499 :meth:`_expression.GenerativeSelect.order_by` method is preferred,
2500 as it
2501 provides standard :term:`method chaining`.
2503 .. seealso::
2505 :meth:`_expression.GenerativeSelect.order_by`
2507 """
2508 if len(clauses) == 1 and clauses[0] is None:
2509 self._order_by_clause = ClauseList()
2510 else:
2511 if getattr(self, "_order_by_clause", None) is not None:
2512 clauses = list(self._order_by_clause) + list(clauses)
2513 self._order_by_clause = ClauseList(
2514 *clauses,
2515 _literal_as_text=_literal_and_labels_as_label_reference
2516 )
2518 def append_group_by(self, *clauses):
2519 """Append the given GROUP BY criterion applied to this selectable.
2521 The criterion will be appended to any pre-existing GROUP BY criterion.
2523 This is an **in-place** mutation method; the
2524 :meth:`_expression.GenerativeSelect.group_by` method is preferred,
2525 as it
2526 provides standard :term:`method chaining`.
2528 .. seealso::
2530 :meth:`_expression.GenerativeSelect.group_by`
2532 """
2533 if len(clauses) == 1 and clauses[0] is None:
2534 self._group_by_clause = ClauseList()
2535 else:
2536 if getattr(self, "_group_by_clause", None) is not None:
2537 clauses = list(self._group_by_clause) + list(clauses)
2538 self._group_by_clause = ClauseList(
2539 *clauses, _literal_as_text=_literal_as_label_reference
2540 )
2542 @property
2543 def _label_resolve_dict(self):
2544 raise NotImplementedError()
2546 def _copy_internals(self, clone=_clone, **kw):
2547 if self._limit_clause is not None:
2548 self._limit_clause = clone(self._limit_clause, **kw)
2549 if self._offset_clause is not None:
2550 self._offset_clause = clone(self._offset_clause, **kw)
2553class CompoundSelect(GenerativeSelect):
2554 """Forms the basis of ``UNION``, ``UNION ALL``, and other
2555 SELECT-based set operations.
2558 .. seealso::
2560 :func:`_expression.union`
2562 :func:`_expression.union_all`
2564 :func:`_expression.intersect`
2566 :func:`_expression.intersect_all`
2568 :func:`_expression.except`
2570 :func:`_expression.except_all`
2572 """
2574 __visit_name__ = "compound_select"
2576 UNION = util.symbol("UNION")
2577 UNION_ALL = util.symbol("UNION ALL")
2578 EXCEPT = util.symbol("EXCEPT")
2579 EXCEPT_ALL = util.symbol("EXCEPT ALL")
2580 INTERSECT = util.symbol("INTERSECT")
2581 INTERSECT_ALL = util.symbol("INTERSECT ALL")
2583 _is_from_container = True
2585 def __init__(self, keyword, *selects, **kwargs):
2586 self._auto_correlate = kwargs.pop("correlate", False)
2587 self.keyword = keyword
2588 self.selects = []
2590 numcols = None
2592 # some DBs do not like ORDER BY in the inner queries of a UNION, etc.
2593 for n, s in enumerate(selects):
2594 s = _clause_element_as_expr(s)
2596 if not numcols:
2597 numcols = len(s.c._all_columns)
2598 elif len(s.c._all_columns) != numcols:
2599 raise exc.ArgumentError(
2600 "All selectables passed to "
2601 "CompoundSelect must have identical numbers of "
2602 "columns; select #%d has %d columns, select "
2603 "#%d has %d"
2604 % (
2605 1,
2606 len(self.selects[0].c._all_columns),
2607 n + 1,
2608 len(s.c._all_columns),
2609 )
2610 )
2612 self.selects.append(s.self_group(against=self))
2614 GenerativeSelect.__init__(self, **kwargs)
2616 @property
2617 def _label_resolve_dict(self):
2618 d = dict((c.key, c) for c in self.c)
2619 return d, d, d
2621 @classmethod
2622 def _create_union(cls, *selects, **kwargs):
2623 r"""Return a ``UNION`` of multiple selectables.
2625 The returned object is an instance of
2626 :class:`_selectable.CompoundSelect`.
2628 A similar :func:`union()` method is available on all
2629 :class:`_expression.FromClause` subclasses.
2631 \*selects
2632 a list of :class:`_expression.Select` instances.
2634 \**kwargs
2635 available keyword arguments are the same as those of
2636 :func:`select`.
2638 """
2639 return CompoundSelect(CompoundSelect.UNION, *selects, **kwargs)
2641 @classmethod
2642 def _create_union_all(cls, *selects, **kwargs):
2643 r"""Return a ``UNION ALL`` of multiple selectables.
2645 The returned object is an instance of
2646 :class:`_selectable.CompoundSelect`.
2648 A similar :func:`union_all()` method is available on all
2649 :class:`_expression.FromClause` subclasses.
2651 \*selects
2652 a list of :class:`_expression.Select` instances.
2654 \**kwargs
2655 available keyword arguments are the same as those of
2656 :func:`select`.
2658 """
2659 return CompoundSelect(CompoundSelect.UNION_ALL, *selects, **kwargs)
2661 @classmethod
2662 def _create_except(cls, *selects, **kwargs):
2663 r"""Return an ``EXCEPT`` of multiple selectables.
2665 The returned object is an instance of
2666 :class:`_selectable.CompoundSelect`.
2668 \*selects
2669 a list of :class:`_expression.Select` instances.
2671 \**kwargs
2672 available keyword arguments are the same as those of
2673 :func:`select`.
2675 """
2676 return CompoundSelect(CompoundSelect.EXCEPT, *selects, **kwargs)
2678 @classmethod
2679 def _create_except_all(cls, *selects, **kwargs):
2680 r"""Return an ``EXCEPT ALL`` of multiple selectables.
2682 The returned object is an instance of
2683 :class:`_selectable.CompoundSelect`.
2685 \*selects
2686 a list of :class:`_expression.Select` instances.
2688 \**kwargs
2689 available keyword arguments are the same as those of
2690 :func:`select`.
2692 """
2693 return CompoundSelect(CompoundSelect.EXCEPT_ALL, *selects, **kwargs)
2695 @classmethod
2696 def _create_intersect(cls, *selects, **kwargs):
2697 r"""Return an ``INTERSECT`` of multiple selectables.
2699 The returned object is an instance of
2700 :class:`_selectable.CompoundSelect`.
2702 \*selects
2703 a list of :class:`_expression.Select` instances.
2705 \**kwargs
2706 available keyword arguments are the same as those of
2707 :func:`select`.
2709 """
2710 return CompoundSelect(CompoundSelect.INTERSECT, *selects, **kwargs)
2712 @classmethod
2713 def _create_intersect_all(cls, *selects, **kwargs):
2714 r"""Return an ``INTERSECT ALL`` of multiple selectables.
2716 The returned object is an instance of
2717 :class:`_selectable.CompoundSelect`.
2719 \*selects
2720 a list of :class:`_expression.Select` instances.
2722 \**kwargs
2723 available keyword arguments are the same as those of
2724 :func:`select`.
2726 """
2727 return CompoundSelect(CompoundSelect.INTERSECT_ALL, *selects, **kwargs)
2729 def _scalar_type(self):
2730 return self.selects[0]._scalar_type()
2732 def self_group(self, against=None):
2733 return FromGrouping(self)
2735 def is_derived_from(self, fromclause):
2736 for s in self.selects:
2737 if s.is_derived_from(fromclause):
2738 return True
2739 return False
2741 def _populate_column_collection(self):
2742 for cols in zip(*[s.c._all_columns for s in self.selects]):
2744 # this is a slightly hacky thing - the union exports a
2745 # column that resembles just that of the *first* selectable.
2746 # to get at a "composite" column, particularly foreign keys,
2747 # you have to dig through the proxies collection which we
2748 # generate below. We may want to improve upon this, such as
2749 # perhaps _make_proxy can accept a list of other columns
2750 # that are "shared" - schema.column can then copy all the
2751 # ForeignKeys in. this would allow the union() to have all
2752 # those fks too.
2754 proxy = cols[0]._make_proxy(
2755 self,
2756 name=cols[0]._label if self.use_labels else None,
2757 key=cols[0]._key_label if self.use_labels else None,
2758 )
2760 # hand-construct the "_proxies" collection to include all
2761 # derived columns place a 'weight' annotation corresponding
2762 # to how low in the list of select()s the column occurs, so
2763 # that the corresponding_column() operation can resolve
2764 # conflicts
2765 proxy._proxies = [
2766 c._annotate({"weight": i + 1}) for (i, c) in enumerate(cols)
2767 ]
2769 def _refresh_for_new_column(self, column):
2770 for s in self.selects:
2771 s._refresh_for_new_column(column)
2773 if not self._cols_populated:
2774 return None
2776 raise NotImplementedError(
2777 "CompoundSelect constructs don't support "
2778 "addition of columns to underlying "
2779 "selectables"
2780 )
2782 def _copy_internals(self, clone=_clone, **kw):
2783 super(CompoundSelect, self)._copy_internals(clone, **kw)
2784 self._reset_exported()
2785 self.selects = [clone(s, **kw) for s in self.selects]
2786 if hasattr(self, "_col_map"):
2787 del self._col_map
2788 for attr in (
2789 "_order_by_clause",
2790 "_group_by_clause",
2791 "_for_update_arg",
2792 ):
2793 if getattr(self, attr) is not None:
2794 setattr(self, attr, clone(getattr(self, attr), **kw))
2796 def get_children(self, column_collections=True, **kwargs):
2797 return (
2798 (column_collections and list(self.c) or [])
2799 + [self._order_by_clause, self._group_by_clause]
2800 + list(self.selects)
2801 )
2803 def bind(self):
2804 if self._bind:
2805 return self._bind
2806 for s in self.selects:
2807 e = s.bind
2808 if e:
2809 return e
2810 else:
2811 return None
2813 def _set_bind(self, bind):
2814 self._bind = bind
2816 bind = property(bind, _set_bind)
2819class Select(HasPrefixes, HasSuffixes, GenerativeSelect):
2820 """Represents a ``SELECT`` statement.
2822 """
2824 __visit_name__ = "select"
2826 _prefixes = ()
2827 _suffixes = ()
2828 _hints = util.immutabledict()
2829 _statement_hints = ()
2830 _distinct = False
2831 _from_cloned = None
2832 _correlate = ()
2833 _correlate_except = None
2834 _memoized_property = SelectBase._memoized_property
2835 _is_select = True
2837 @util.deprecated_params(
2838 autocommit=(
2839 "0.6",
2840 "The :paramref:`_expression.select.autocommit` "
2841 "parameter is deprecated "
2842 "and will be removed in a future release. Please refer to "
2843 "the :paramref:`.Connection.execution_options.autocommit` "
2844 "parameter in conjunction with the the "
2845 ":meth:`.Executable.execution_options` method in order to "
2846 "affect the autocommit behavior for a statement.",
2847 ),
2848 for_update=(
2849 "0.9",
2850 "The :paramref:`_expression.select.for_update` "
2851 "parameter is deprecated and "
2852 "will be removed in a future release. Please refer to the "
2853 ":meth:`_expression.Select.with_for_update` to specify the "
2854 "structure of the ``FOR UPDATE`` clause.",
2855 ),
2856 )
2857 def __init__(
2858 self,
2859 columns=None,
2860 whereclause=None,
2861 from_obj=None,
2862 distinct=False,
2863 having=None,
2864 correlate=True,
2865 prefixes=None,
2866 suffixes=None,
2867 **kwargs
2868 ):
2869 """Construct a new :class:`_expression.Select`.
2871 Similar functionality is also available via the
2872 :meth:`_expression.FromClause.select` method on any
2873 :class:`_expression.FromClause`.
2875 All arguments which accept :class:`_expression.ClauseElement`
2876 arguments also
2877 accept string arguments, which will be converted as appropriate into
2878 either :func:`_expression.text()` or
2879 :func:`_expression.literal_column()` constructs.
2881 .. seealso::
2883 :ref:`coretutorial_selecting` - Core Tutorial description of
2884 :func:`_expression.select`.
2886 :param columns:
2887 A list of :class:`_expression.ColumnElement` or
2888 :class:`_expression.FromClause`
2889 objects which will form the columns clause of the resulting
2890 statement. For those objects that are instances of
2891 :class:`_expression.FromClause` (typically :class:`_schema.Table`
2892 or :class:`_expression.Alias`
2893 objects), the :attr:`_expression.FromClause.c`
2894 collection is extracted
2895 to form a collection of :class:`_expression.ColumnElement` objects.
2897 This parameter will also accept :class:`_expression.TextClause`
2898 constructs as
2899 given, as well as ORM-mapped classes.
2901 .. note::
2903 The :paramref:`_expression.select.columns`
2904 parameter is not available
2905 in the method form of :func:`_expression.select`, e.g.
2906 :meth:`_expression.FromClause.select`.
2908 .. seealso::
2910 :meth:`_expression.Select.column`
2912 :meth:`_expression.Select.with_only_columns`
2914 :param whereclause:
2915 A :class:`_expression.ClauseElement`
2916 expression which will be used to form the
2917 ``WHERE`` clause. It is typically preferable to add WHERE
2918 criterion to an existing :class:`_expression.Select`
2919 using method chaining
2920 with :meth:`_expression.Select.where`.
2922 .. seealso::
2924 :meth:`_expression.Select.where`
2926 :param from_obj:
2927 A list of :class:`_expression.ClauseElement`
2928 objects which will be added to the
2929 ``FROM`` clause of the resulting statement. This is equivalent
2930 to calling :meth:`_expression.Select.select_from`
2931 using method chaining on
2932 an existing :class:`_expression.Select` object.
2934 .. seealso::
2936 :meth:`_expression.Select.select_from`
2937 - full description of explicit
2938 FROM clause specification.
2940 :param autocommit: legacy autocommit parameter.
2942 :param bind=None:
2943 an :class:`_engine.Engine` or :class:`_engine.Connection` instance
2944 to which the
2945 resulting :class:`_expression.Select` object will be bound. The
2946 :class:`_expression.Select`
2947 object will otherwise automatically bind to
2948 whatever :class:`~.base.Connectable` instances can be located within
2949 its contained :class:`_expression.ClauseElement` members.
2951 :param correlate=True:
2952 indicates that this :class:`_expression.Select`
2953 object should have its
2954 contained :class:`_expression.FromClause`
2955 elements "correlated" to an enclosing
2956 :class:`_expression.Select` object.
2957 It is typically preferable to specify
2958 correlations on an existing :class:`_expression.Select`
2959 construct using
2960 :meth:`_expression.Select.correlate`.
2962 .. seealso::
2964 :meth:`_expression.Select.correlate`
2965 - full description of correlation.
2967 :param distinct=False:
2968 when ``True``, applies a ``DISTINCT`` qualifier to the columns
2969 clause of the resulting statement.
2971 The boolean argument may also be a column expression or list
2972 of column expressions - this is a special calling form which
2973 is understood by the PostgreSQL dialect to render the
2974 ``DISTINCT ON (<columns>)`` syntax.
2976 ``distinct`` is also available on an existing
2977 :class:`_expression.Select`
2978 object via the :meth:`_expression.Select.distinct` method.
2980 .. seealso::
2982 :meth:`_expression.Select.distinct`
2984 :param for_update=False:
2985 when ``True``, applies ``FOR UPDATE`` to the end of the
2986 resulting statement.
2988 ``for_update`` accepts various string values interpreted by
2989 specific backends, including:
2991 * ``"read"`` - on MySQL, translates to ``LOCK IN SHARE MODE``;
2992 on PostgreSQL, translates to ``FOR SHARE``.
2993 * ``"nowait"`` - on PostgreSQL and Oracle, translates to
2994 ``FOR UPDATE NOWAIT``.
2995 * ``"read_nowait"`` - on PostgreSQL, translates to
2996 ``FOR SHARE NOWAIT``.
2998 .. seealso::
3000 :meth:`_expression.Select.with_for_update` - improved API for
3001 specifying the ``FOR UPDATE`` clause.
3003 :param group_by:
3004 a list of :class:`_expression.ClauseElement`
3005 objects which will comprise the
3006 ``GROUP BY`` clause of the resulting select. This parameter
3007 is typically specified more naturally using the
3008 :meth:`_expression.Select.group_by` method on an existing
3009 :class:`_expression.Select`.
3011 .. seealso::
3013 :meth:`_expression.Select.group_by`
3015 :param having:
3016 a :class:`_expression.ClauseElement`
3017 that will comprise the ``HAVING`` clause
3018 of the resulting select when ``GROUP BY`` is used. This parameter
3019 is typically specified more naturally using the
3020 :meth:`_expression.Select.having` method on an existing
3021 :class:`_expression.Select`.
3023 .. seealso::
3025 :meth:`_expression.Select.having`
3027 :param limit=None:
3028 a numerical value which usually renders as a ``LIMIT``
3029 expression in the resulting select. Backends that don't
3030 support ``LIMIT`` will attempt to provide similar
3031 functionality. This parameter is typically specified more
3032 naturally using the :meth:`_expression.Select.limit`
3033 method on an existing
3034 :class:`_expression.Select`.
3036 .. seealso::
3038 :meth:`_expression.Select.limit`
3040 :param offset=None:
3041 a numeric value which usually renders as an ``OFFSET``
3042 expression in the resulting select. Backends that don't
3043 support ``OFFSET`` will attempt to provide similar
3044 functionality. This parameter is typically specified more naturally
3045 using the :meth:`_expression.Select.offset` method on an existing
3046 :class:`_expression.Select`.
3048 .. seealso::
3050 :meth:`_expression.Select.offset`
3052 :param order_by:
3053 a scalar or list of :class:`_expression.ClauseElement`
3054 objects which will
3055 comprise the ``ORDER BY`` clause of the resulting select.
3056 This parameter is typically specified more naturally using the
3057 :meth:`_expression.Select.order_by` method on an existing
3058 :class:`_expression.Select`.
3060 .. seealso::
3062 :meth:`_expression.Select.order_by`
3064 :param use_labels=False:
3065 when ``True``, the statement will be generated using labels
3066 for each column in the columns clause, which qualify each
3067 column with its parent table's (or aliases) name so that name
3068 conflicts between columns in different tables don't occur.
3069 The format of the label is <tablename>_<column>. The "c"
3070 collection of the resulting :class:`_expression.Select`
3071 object will use these
3072 names as well for targeting column members.
3074 This parameter can also be specified on an existing
3075 :class:`_expression.Select` object using the
3076 :meth:`_expression.Select.apply_labels`
3077 method.
3079 .. seealso::
3081 :meth:`_expression.Select.apply_labels`
3083 """
3084 self._auto_correlate = correlate
3085 if distinct is not False:
3086 if distinct is True:
3087 self._distinct = True
3088 else:
3089 self._distinct = [
3090 _literal_as_label_reference(e)
3091 for e in util.to_list(distinct)
3092 ]
3094 if from_obj is not None:
3095 self._from_obj = util.OrderedSet(
3096 _interpret_as_from(f) for f in util.to_list(from_obj)
3097 )
3098 else:
3099 self._from_obj = util.OrderedSet()
3101 try:
3102 cols_present = bool(columns)
3103 except TypeError as err:
3104 util.raise_(
3105 exc.ArgumentError(
3106 "columns argument to select() must "
3107 "be a Python list or other iterable"
3108 ),
3109 replace_context=err,
3110 )
3112 if cols_present:
3113 self._raw_columns = []
3114 for c in columns:
3115 c = _interpret_as_column_or_from(c)
3116 if isinstance(c, ScalarSelect):
3117 c = c.self_group(against=operators.comma_op)
3118 self._raw_columns.append(c)
3119 else:
3120 self._raw_columns = []
3122 if whereclause is not None:
3123 self._whereclause = _literal_as_text(whereclause).self_group(
3124 against=operators._asbool
3125 )
3126 else:
3127 self._whereclause = None
3129 if having is not None:
3130 self._having = _literal_as_text(having).self_group(
3131 against=operators._asbool
3132 )
3133 else:
3134 self._having = None
3136 if prefixes:
3137 self._setup_prefixes(prefixes)
3139 if suffixes:
3140 self._setup_suffixes(suffixes)
3142 GenerativeSelect.__init__(self, **kwargs)
3144 @property
3145 def _froms(self):
3146 # would love to cache this,
3147 # but there's just enough edge cases, particularly now that
3148 # declarative encourages construction of SQL expressions
3149 # without tables present, to just regen this each time.
3150 froms = []
3151 seen = set()
3152 translate = self._from_cloned
3154 for item in itertools.chain(
3155 _from_objects(*self._raw_columns),
3156 _from_objects(self._whereclause)
3157 if self._whereclause is not None
3158 else (),
3159 self._from_obj,
3160 ):
3161 if item is self:
3162 raise exc.InvalidRequestError(
3163 "select() construct refers to itself as a FROM"
3164 )
3165 if translate and item in translate:
3166 item = translate[item]
3167 if not seen.intersection(item._cloned_set):
3168 froms.append(item)
3169 seen.update(item._cloned_set)
3171 return froms
3173 def _get_display_froms(
3174 self, explicit_correlate_froms=None, implicit_correlate_froms=None
3175 ):
3176 """Return the full list of 'from' clauses to be displayed.
3178 Takes into account a set of existing froms which may be
3179 rendered in the FROM clause of enclosing selects; this Select
3180 may want to leave those absent if it is automatically
3181 correlating.
3183 """
3184 froms = self._froms
3186 toremove = set(
3187 itertools.chain(*[_expand_cloned(f._hide_froms) for f in froms])
3188 )
3189 if toremove:
3190 # if we're maintaining clones of froms,
3191 # add the copies out to the toremove list. only include
3192 # clones that are lexical equivalents.
3193 if self._from_cloned:
3194 toremove.update(
3195 self._from_cloned[f]
3196 for f in toremove.intersection(self._from_cloned)
3197 if self._from_cloned[f]._is_lexical_equivalent(f)
3198 )
3199 # filter out to FROM clauses not in the list,
3200 # using a list to maintain ordering
3201 froms = [f for f in froms if f not in toremove]
3203 if self._correlate:
3204 to_correlate = self._correlate
3205 if to_correlate:
3206 froms = [
3207 f
3208 for f in froms
3209 if f
3210 not in _cloned_intersection(
3211 _cloned_intersection(
3212 froms, explicit_correlate_froms or ()
3213 ),
3214 to_correlate,
3215 )
3216 ]
3218 if self._correlate_except is not None:
3220 froms = [
3221 f
3222 for f in froms
3223 if f
3224 not in _cloned_difference(
3225 _cloned_intersection(
3226 froms, explicit_correlate_froms or ()
3227 ),
3228 self._correlate_except,
3229 )
3230 ]
3232 if (
3233 self._auto_correlate
3234 and implicit_correlate_froms
3235 and len(froms) > 1
3236 ):
3238 froms = [
3239 f
3240 for f in froms
3241 if f
3242 not in _cloned_intersection(froms, implicit_correlate_froms)
3243 ]
3245 if not len(froms):
3246 raise exc.InvalidRequestError(
3247 "Select statement '%s"
3248 "' returned no FROM clauses "
3249 "due to auto-correlation; "
3250 "specify correlate(<tables>) "
3251 "to control correlation "
3252 "manually." % self
3253 )
3255 return froms
3257 def _scalar_type(self):
3258 elem = self._raw_columns[0]
3259 cols = list(elem._select_iterable)
3260 return cols[0].type
3262 @property
3263 def froms(self):
3264 """Return the displayed list of FromClause elements."""
3266 return self._get_display_froms()
3268 def with_statement_hint(self, text, dialect_name="*"):
3269 """add a statement hint to this :class:`_expression.Select`.
3271 This method is similar to :meth:`_expression.Select.with_hint`
3272 except that
3273 it does not require an individual table, and instead applies to the
3274 statement as a whole.
3276 Hints here are specific to the backend database and may include
3277 directives such as isolation levels, file directives, fetch directives,
3278 etc.
3280 .. versionadded:: 1.0.0
3282 .. seealso::
3284 :meth:`_expression.Select.with_hint`
3286 :meth:`.Select.prefix_with` - generic SELECT prefixing which also
3287 can suit some database-specific HINT syntaxes such as MySQL
3288 optimizer hints
3290 """
3291 return self.with_hint(None, text, dialect_name)
3293 @_generative
3294 def with_hint(self, selectable, text, dialect_name="*"):
3295 r"""Add an indexing or other executional context hint for the given
3296 selectable to this :class:`_expression.Select`.
3298 The text of the hint is rendered in the appropriate
3299 location for the database backend in use, relative
3300 to the given :class:`_schema.Table` or :class:`_expression.Alias`
3301 passed as the
3302 ``selectable`` argument. The dialect implementation
3303 typically uses Python string substitution syntax
3304 with the token ``%(name)s`` to render the name of
3305 the table or alias. E.g. when using Oracle, the
3306 following::
3308 select([mytable]).\
3309 with_hint(mytable, "index(%(name)s ix_mytable)")
3311 Would render SQL as::
3313 select /*+ index(mytable ix_mytable) */ ... from mytable
3315 The ``dialect_name`` option will limit the rendering of a particular
3316 hint to a particular backend. Such as, to add hints for both Oracle
3317 and Sybase simultaneously::
3319 select([mytable]).\
3320 with_hint(mytable, "index(%(name)s ix_mytable)", 'oracle').\
3321 with_hint(mytable, "WITH INDEX ix_mytable", 'sybase')
3323 .. seealso::
3325 :meth:`_expression.Select.with_statement_hint`
3327 """
3328 if selectable is None:
3329 self._statement_hints += ((dialect_name, text),)
3330 else:
3331 self._hints = self._hints.union({(selectable, dialect_name): text})
3333 @property
3334 def type(self):
3335 raise exc.InvalidRequestError(
3336 "Select objects don't have a type. "
3337 "Call as_scalar() on this Select "
3338 "object to return a 'scalar' version "
3339 "of this Select."
3340 )
3342 @_memoized_property.method
3343 def locate_all_froms(self):
3344 """return a Set of all FromClause elements referenced by this Select.
3346 This set is a superset of that returned by the ``froms`` property,
3347 which is specifically for those FromClause elements that would
3348 actually be rendered.
3350 """
3351 froms = self._froms
3352 return froms + list(_from_objects(*froms))
3354 @property
3355 def inner_columns(self):
3356 """an iterator of all ColumnElement expressions which would
3357 be rendered into the columns clause of the resulting SELECT statement.
3359 """
3360 return _select_iterables(self._raw_columns)
3362 @_memoized_property
3363 def _label_resolve_dict(self):
3364 with_cols = dict(
3365 (c._resolve_label or c._label or c.key, c)
3366 for c in _select_iterables(self._raw_columns)
3367 if c._allow_label_resolve
3368 )
3369 only_froms = dict(
3370 (c.key, c)
3371 for c in _select_iterables(self.froms)
3372 if c._allow_label_resolve
3373 )
3374 only_cols = with_cols.copy()
3375 for key, value in only_froms.items():
3376 with_cols.setdefault(key, value)
3378 return with_cols, only_froms, only_cols
3380 def is_derived_from(self, fromclause):
3381 if self in fromclause._cloned_set:
3382 return True
3384 for f in self.locate_all_froms():
3385 if f.is_derived_from(fromclause):
3386 return True
3387 return False
3389 def _copy_internals(self, clone=_clone, **kw):
3390 super(Select, self)._copy_internals(clone, **kw)
3392 # Select() object has been cloned and probably adapted by the
3393 # given clone function. Apply the cloning function to internal
3394 # objects
3396 # 1. keep a dictionary of the froms we've cloned, and what
3397 # they've become. This is consulted later when we derive
3398 # additional froms from "whereclause" and the columns clause,
3399 # which may still reference the uncloned parent table.
3400 # as of 0.7.4 we also put the current version of _froms, which
3401 # gets cleared on each generation. previously we were "baking"
3402 # _froms into self._from_obj.
3403 self._from_cloned = from_cloned = dict(
3404 (f, clone(f, **kw)) for f in self._from_obj.union(self._froms)
3405 )
3407 # 3. update persistent _from_obj with the cloned versions.
3408 self._from_obj = util.OrderedSet(
3409 from_cloned[f] for f in self._from_obj
3410 )
3412 # the _correlate collection is done separately, what can happen
3413 # here is the same item is _correlate as in _from_obj but the
3414 # _correlate version has an annotation on it - (specifically
3415 # RelationshipProperty.Comparator._criterion_exists() does
3416 # this). Also keep _correlate liberally open with its previous
3417 # contents, as this set is used for matching, not rendering.
3418 self._correlate = set(clone(f, **kw) for f in self._correlate).union(
3419 self._correlate
3420 )
3422 # do something similar for _correlate_except - this is a more
3423 # unusual case but same idea applies
3424 if self._correlate_except:
3425 self._correlate_except = set(
3426 clone(f, **kw) for f in self._correlate_except
3427 ).union(self._correlate_except)
3429 # 4. clone other things. The difficulty here is that Column
3430 # objects are not actually cloned, and refer to their original
3431 # .table, resulting in the wrong "from" parent after a clone
3432 # operation. Hence _from_cloned and _from_obj supersede what is
3433 # present here.
3434 self._raw_columns = [clone(c, **kw) for c in self._raw_columns]
3435 for attr in (
3436 "_whereclause",
3437 "_having",
3438 "_order_by_clause",
3439 "_group_by_clause",
3440 "_for_update_arg",
3441 ):
3442 if getattr(self, attr) is not None:
3443 setattr(self, attr, clone(getattr(self, attr), **kw))
3445 # erase exported column list, _froms collection,
3446 # etc.
3447 self._reset_exported()
3449 def get_children(self, column_collections=True, **kwargs):
3450 """return child elements as per the ClauseElement specification."""
3452 return (
3453 (column_collections and list(self.columns) or [])
3454 + self._raw_columns
3455 + list(self._froms)
3456 + [
3457 x
3458 for x in (
3459 self._whereclause,
3460 self._having,
3461 self._order_by_clause,
3462 self._group_by_clause,
3463 )
3464 if x is not None
3465 ]
3466 )
3468 @_generative
3469 def column(self, column):
3470 """return a new select() construct with the given column expression
3471 added to its columns clause.
3473 E.g.::
3475 my_select = my_select.column(table.c.new_column)
3477 See the documentation for
3478 :meth:`_expression.Select.with_only_columns`
3479 for guidelines on adding /replacing the columns of a
3480 :class:`_expression.Select` object.
3482 """
3483 self.append_column(column)
3485 @util.dependencies("sqlalchemy.sql.util")
3486 def reduce_columns(self, sqlutil, only_synonyms=True):
3487 """Return a new :func`.select` construct with redundantly
3488 named, equivalently-valued columns removed from the columns clause.
3490 "Redundant" here means two columns where one refers to the
3491 other either based on foreign key, or via a simple equality
3492 comparison in the WHERE clause of the statement. The primary purpose
3493 of this method is to automatically construct a select statement
3494 with all uniquely-named columns, without the need to use
3495 table-qualified labels as :meth:`_expression.Select.apply_labels` does
3496 .
3498 When columns are omitted based on foreign key, the referred-to
3499 column is the one that's kept. When columns are omitted based on
3500 WHERE equivalence, the first column in the columns clause is the
3501 one that's kept.
3503 :param only_synonyms: when True, limit the removal of columns
3504 to those which have the same name as the equivalent. Otherwise,
3505 all columns that are equivalent to another are removed.
3507 """
3508 return self.with_only_columns(
3509 sqlutil.reduce_columns(
3510 self.inner_columns,
3511 only_synonyms=only_synonyms,
3512 *(self._whereclause,) + tuple(self._from_obj)
3513 )
3514 )
3516 @_generative
3517 def with_only_columns(self, columns):
3518 r"""Return a new :func:`_expression.select` construct with its columns
3519 clause replaced with the given columns.
3521 This method is exactly equivalent to as if the original
3522 :func:`_expression.select` had been called with the given columns
3523 clause. I.e. a statement::
3525 s = select([table1.c.a, table1.c.b])
3526 s = s.with_only_columns([table1.c.b])
3528 should be exactly equivalent to::
3530 s = select([table1.c.b])
3532 This means that FROM clauses which are only derived
3533 from the column list will be discarded if the new column
3534 list no longer contains that FROM::
3536 >>> table1 = table('t1', column('a'), column('b'))
3537 >>> table2 = table('t2', column('a'), column('b'))
3538 >>> s1 = select([table1.c.a, table2.c.b])
3539 >>> print(s1)
3540 SELECT t1.a, t2.b FROM t1, t2
3541 >>> s2 = s1.with_only_columns([table2.c.b])
3542 >>> print(s2)
3543 SELECT t2.b FROM t1
3545 The preferred way to maintain a specific FROM clause
3546 in the construct, assuming it won't be represented anywhere
3547 else (i.e. not in the WHERE clause, etc.) is to set it using
3548 :meth:`_expression.Select.select_from`::
3550 >>> s1 = select([table1.c.a, table2.c.b]).\
3551 ... select_from(table1.join(table2,
3552 ... table1.c.a==table2.c.a))
3553 >>> s2 = s1.with_only_columns([table2.c.b])
3554 >>> print(s2)
3555 SELECT t2.b FROM t1 JOIN t2 ON t1.a=t2.a
3557 Care should also be taken to use the correct
3558 set of column objects passed to
3559 :meth:`_expression.Select.with_only_columns`.
3560 Since the method is essentially equivalent to calling the
3561 :func:`_expression.select` construct in the first place with the given
3562 columns, the columns passed to
3563 :meth:`_expression.Select.with_only_columns`
3564 should usually be a subset of those which were passed
3565 to the :func:`_expression.select` construct,
3566 not those which are available
3567 from the ``.c`` collection of that :func:`_expression.select`. That
3568 is::
3570 s = select([table1.c.a, table1.c.b]).select_from(table1)
3571 s = s.with_only_columns([table1.c.b])
3573 and **not**::
3575 # usually incorrect
3576 s = s.with_only_columns([s.c.b])
3578 The latter would produce the SQL::
3580 SELECT b
3581 FROM (SELECT t1.a AS a, t1.b AS b
3582 FROM t1), t1
3584 Since the :func:`_expression.select` construct is essentially being
3585 asked to select both from ``table1`` as well as itself.
3587 """
3588 self._reset_exported()
3589 rc = []
3590 for c in columns:
3591 c = _interpret_as_column_or_from(c)
3592 if isinstance(c, ScalarSelect):
3593 c = c.self_group(against=operators.comma_op)
3594 rc.append(c)
3595 self._raw_columns = rc
3597 @_generative
3598 def where(self, whereclause):
3599 """return a new select() construct with the given expression added to
3600 its WHERE clause, joined to the existing clause via AND, if any.
3602 """
3604 self.append_whereclause(whereclause)
3606 @_generative
3607 def having(self, having):
3608 """return a new select() construct with the given expression added to
3609 its HAVING clause, joined to the existing clause via AND, if any.
3611 """
3612 self.append_having(having)
3614 @_generative
3615 def distinct(self, *expr):
3616 r"""Return a new select() construct which will apply DISTINCT to its
3617 columns clause.
3619 :param \*expr: optional column expressions. When present,
3620 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>>)``
3621 construct.
3623 """
3624 if expr:
3625 expr = [_literal_as_label_reference(e) for e in expr]
3626 if isinstance(self._distinct, list):
3627 self._distinct = self._distinct + expr
3628 else:
3629 self._distinct = expr
3630 else:
3631 self._distinct = True
3633 @_generative
3634 def select_from(self, fromclause):
3635 r"""return a new :func:`_expression.select` construct with the
3636 given FROM expression
3637 merged into its list of FROM objects.
3639 E.g.::
3641 table1 = table('t1', column('a'))
3642 table2 = table('t2', column('b'))
3643 s = select([table1.c.a]).\
3644 select_from(
3645 table1.join(table2, table1.c.a==table2.c.b)
3646 )
3648 The "from" list is a unique set on the identity of each element,
3649 so adding an already present :class:`_schema.Table`
3650 or other selectable
3651 will have no effect. Passing a :class:`_expression.Join` that refers
3652 to an already present :class:`_schema.Table`
3653 or other selectable will have
3654 the effect of concealing the presence of that selectable as
3655 an individual element in the rendered FROM list, instead
3656 rendering it into a JOIN clause.
3658 While the typical purpose of :meth:`_expression.Select.select_from`
3659 is to
3660 replace the default, derived FROM clause with a join, it can
3661 also be called with individual table elements, multiple times
3662 if desired, in the case that the FROM clause cannot be fully
3663 derived from the columns clause::
3665 select([func.count('*')]).select_from(table1)
3667 """
3668 self.append_from(fromclause)
3670 @_generative
3671 def correlate(self, *fromclauses):
3672 r"""return a new :class:`_expression.Select`
3673 which will correlate the given FROM
3674 clauses to that of an enclosing :class:`_expression.Select`.
3676 Calling this method turns off the :class:`_expression.Select` object's
3677 default behavior of "auto-correlation". Normally, FROM elements
3678 which appear in a :class:`_expression.Select`
3679 that encloses this one via
3680 its :term:`WHERE clause`, ORDER BY, HAVING or
3681 :term:`columns clause` will be omitted from this
3682 :class:`_expression.Select`
3683 object's :term:`FROM clause`.
3684 Setting an explicit correlation collection using the
3685 :meth:`_expression.Select.correlate`
3686 method provides a fixed list of FROM objects
3687 that can potentially take place in this process.
3689 When :meth:`_expression.Select.correlate`
3690 is used to apply specific FROM clauses
3691 for correlation, the FROM elements become candidates for
3692 correlation regardless of how deeply nested this
3693 :class:`_expression.Select`
3694 object is, relative to an enclosing :class:`_expression.Select`
3695 which refers to
3696 the same FROM object. This is in contrast to the behavior of
3697 "auto-correlation" which only correlates to an immediate enclosing
3698 :class:`_expression.Select`.
3699 Multi-level correlation ensures that the link
3700 between enclosed and enclosing :class:`_expression.Select`
3701 is always via
3702 at least one WHERE/ORDER BY/HAVING/columns clause in order for
3703 correlation to take place.
3705 If ``None`` is passed, the :class:`_expression.Select`
3706 object will correlate
3707 none of its FROM entries, and all will render unconditionally
3708 in the local FROM clause.
3710 :param \*fromclauses: a list of one or more
3711 :class:`_expression.FromClause`
3712 constructs, or other compatible constructs (i.e. ORM-mapped
3713 classes) to become part of the correlate collection.
3715 .. seealso::
3717 :meth:`_expression.Select.correlate_except`
3719 :ref:`correlated_subqueries`
3721 """
3722 self._auto_correlate = False
3723 if fromclauses and fromclauses[0] is None:
3724 self._correlate = ()
3725 else:
3726 self._correlate = set(self._correlate).union(
3727 _interpret_as_from(f) for f in fromclauses
3728 )
3730 @_generative
3731 def correlate_except(self, *fromclauses):
3732 r"""return a new :class:`_expression.Select`
3733 which will omit the given FROM
3734 clauses from the auto-correlation process.
3736 Calling :meth:`_expression.Select.correlate_except` turns off the
3737 :class:`_expression.Select` object's default behavior of
3738 "auto-correlation" for the given FROM elements. An element
3739 specified here will unconditionally appear in the FROM list, while
3740 all other FROM elements remain subject to normal auto-correlation
3741 behaviors.
3743 If ``None`` is passed, the :class:`_expression.Select`
3744 object will correlate
3745 all of its FROM entries.
3747 :param \*fromclauses: a list of one or more
3748 :class:`_expression.FromClause`
3749 constructs, or other compatible constructs (i.e. ORM-mapped
3750 classes) to become part of the correlate-exception collection.
3752 .. seealso::
3754 :meth:`_expression.Select.correlate`
3756 :ref:`correlated_subqueries`
3758 """
3760 self._auto_correlate = False
3761 if fromclauses and fromclauses[0] is None:
3762 self._correlate_except = ()
3763 else:
3764 self._correlate_except = set(self._correlate_except or ()).union(
3765 _interpret_as_from(f) for f in fromclauses
3766 )
3768 def append_correlation(self, fromclause):
3769 """append the given correlation expression to this select()
3770 construct.
3772 This is an **in-place** mutation method; the
3773 :meth:`_expression.Select.correlate` method is preferred,
3774 as it provides
3775 standard :term:`method chaining`.
3777 """
3779 self._auto_correlate = False
3780 self._correlate = set(self._correlate).union(
3781 _interpret_as_from(f) for f in fromclause
3782 )
3784 def append_column(self, column):
3785 """append the given column expression to the columns clause of this
3786 select() construct.
3788 E.g.::
3790 my_select.append_column(some_table.c.new_column)
3792 This is an **in-place** mutation method; the
3793 :meth:`_expression.Select.column` method is preferred,
3794 as it provides standard
3795 :term:`method chaining`.
3797 See the documentation for :meth:`_expression.Select.with_only_columns`
3798 for guidelines on adding /replacing the columns of a
3799 :class:`_expression.Select` object.
3801 """
3802 self._reset_exported()
3803 column = _interpret_as_column_or_from(column)
3805 if isinstance(column, ScalarSelect):
3806 column = column.self_group(against=operators.comma_op)
3808 self._raw_columns = self._raw_columns + [column]
3810 def append_prefix(self, clause):
3811 """append the given columns clause prefix expression to this select()
3812 construct.
3814 This is an **in-place** mutation method; the
3815 :meth:`_expression.Select.prefix_with` method is preferred,
3816 as it provides
3817 standard :term:`method chaining`.
3819 """
3820 clause = _literal_as_text(clause)
3821 self._prefixes = self._prefixes + (clause,)
3823 def append_whereclause(self, whereclause):
3824 """append the given expression to this select() construct's WHERE
3825 criterion.
3827 The expression will be joined to existing WHERE criterion via AND.
3829 This is an **in-place** mutation method; the
3830 :meth:`_expression.Select.where` method is preferred,
3831 as it provides standard
3832 :term:`method chaining`.
3834 """
3836 self._reset_exported()
3837 self._whereclause = and_(True_._ifnone(self._whereclause), whereclause)
3839 def append_having(self, having):
3840 """append the given expression to this select() construct's HAVING
3841 criterion.
3843 The expression will be joined to existing HAVING criterion via AND.
3845 This is an **in-place** mutation method; the
3846 :meth:`_expression.Select.having` method is preferred,
3847 as it provides standard
3848 :term:`method chaining`.
3850 """
3851 self._reset_exported()
3852 self._having = and_(True_._ifnone(self._having), having)
3854 def append_from(self, fromclause):
3855 """append the given FromClause expression to this select() construct's
3856 FROM clause.
3858 This is an **in-place** mutation method; the
3859 :meth:`_expression.Select.select_from` method is preferred,
3860 as it provides
3861 standard :term:`method chaining`.
3863 """
3864 self._reset_exported()
3865 fromclause = _interpret_as_from(fromclause)
3866 self._from_obj = self._from_obj.union([fromclause])
3868 @_memoized_property
3869 def _columns_plus_names(self):
3870 if self.use_labels:
3871 names = set()
3873 def name_for_col(c):
3874 if c._label is None or not c._render_label_in_columns_clause:
3875 return (None, c)
3877 name = c._label
3878 if name in names:
3879 name = c.anon_label
3880 else:
3881 names.add(name)
3882 return name, c
3884 return [
3885 name_for_col(c)
3886 for c in util.unique_list(_select_iterables(self._raw_columns))
3887 ]
3888 else:
3889 return [
3890 (None, c)
3891 for c in util.unique_list(_select_iterables(self._raw_columns))
3892 ]
3894 def _populate_column_collection(self):
3895 for name, c in self._columns_plus_names:
3896 if not hasattr(c, "_make_proxy"):
3897 continue
3898 if name is None:
3899 key = None
3900 elif self.use_labels:
3901 key = c._key_label
3902 if key is not None and key in self.c:
3903 key = c.anon_label
3904 else:
3905 key = None
3906 c._make_proxy(self, key=key, name=name, name_is_truncatable=True)
3908 def _refresh_for_new_column(self, column):
3909 for fromclause in self._froms:
3910 col = fromclause._refresh_for_new_column(column)
3911 if col is not None:
3912 if col in self.inner_columns and self._cols_populated:
3913 our_label = col._key_label if self.use_labels else col.key
3914 if our_label not in self.c:
3915 return col._make_proxy(
3916 self,
3917 name=col._label if self.use_labels else None,
3918 key=col._key_label if self.use_labels else None,
3919 name_is_truncatable=True,
3920 )
3921 return None
3922 return None
3924 def _needs_parens_for_grouping(self):
3925 return (
3926 self._limit_clause is not None
3927 or self._offset_clause is not None
3928 or bool(self._order_by_clause.clauses)
3929 )
3931 def self_group(self, against=None):
3932 """return a 'grouping' construct as per the ClauseElement
3933 specification.
3935 This produces an element that can be embedded in an expression. Note
3936 that this method is called automatically as needed when constructing
3937 expressions and should not require explicit use.
3939 """
3940 if (
3941 isinstance(against, CompoundSelect)
3942 and not self._needs_parens_for_grouping()
3943 ):
3944 return self
3945 return FromGrouping(self)
3947 def union(self, other, **kwargs):
3948 """return a SQL UNION of this select() construct against the given
3949 selectable."""
3951 return CompoundSelect._create_union(self, other, **kwargs)
3953 def union_all(self, other, **kwargs):
3954 """return a SQL UNION ALL of this select() construct against the given
3955 selectable.
3957 """
3958 return CompoundSelect._create_union_all(self, other, **kwargs)
3960 def except_(self, other, **kwargs):
3961 """return a SQL EXCEPT of this select() construct against the given
3962 selectable."""
3964 return CompoundSelect._create_except(self, other, **kwargs)
3966 def except_all(self, other, **kwargs):
3967 """return a SQL EXCEPT ALL of this select() construct against the
3968 given selectable.
3970 """
3971 return CompoundSelect._create_except_all(self, other, **kwargs)
3973 def intersect(self, other, **kwargs):
3974 """return a SQL INTERSECT of this select() construct against the given
3975 selectable.
3977 """
3978 return CompoundSelect._create_intersect(self, other, **kwargs)
3980 def intersect_all(self, other, **kwargs):
3981 """return a SQL INTERSECT ALL of this select() construct against the
3982 given selectable.
3984 """
3985 return CompoundSelect._create_intersect_all(self, other, **kwargs)
3987 def bind(self):
3988 if self._bind:
3989 return self._bind
3990 froms = self._froms
3991 if not froms:
3992 for c in self._raw_columns:
3993 e = c.bind
3994 if e:
3995 self._bind = e
3996 return e
3997 else:
3998 e = list(froms)[0].bind
3999 if e:
4000 self._bind = e
4001 return e
4003 return None
4005 def _set_bind(self, bind):
4006 self._bind = bind
4008 bind = property(bind, _set_bind)
4011class ScalarSelect(Generative, Grouping):
4012 _from_objects = []
4013 _is_from_container = True
4014 _is_implicitly_boolean = False
4016 def __init__(self, element):
4017 self.element = element
4018 self.type = element._scalar_type()
4020 @property
4021 def columns(self):
4022 raise exc.InvalidRequestError(
4023 "Scalar Select expression has no "
4024 "columns; use this object directly "
4025 "within a column-level expression."
4026 )
4028 c = columns
4030 @_generative
4031 def where(self, crit):
4032 """Apply a WHERE clause to the SELECT statement referred to
4033 by this :class:`_expression.ScalarSelect`.
4035 """
4036 self.element = self.element.where(crit)
4038 def self_group(self, **kwargs):
4039 return self
4042class Exists(UnaryExpression):
4043 """Represent an ``EXISTS`` clause.
4045 """
4047 __visit_name__ = UnaryExpression.__visit_name__
4048 _from_objects = []
4050 def __init__(self, *args, **kwargs):
4051 """Construct a new :class:`_expression.Exists` against an existing
4052 :class:`_expression.Select` object.
4054 Calling styles are of the following forms::
4056 # use on an existing select()
4057 s = select([table.c.col1]).where(table.c.col2==5)
4058 s = exists(s)
4060 # construct a select() at once
4061 exists(['*'], **select_arguments).where(criterion)
4063 # columns argument is optional, generates "EXISTS (SELECT *)"
4064 # by default.
4065 exists().where(table.c.col2==5)
4067 """
4068 if args and isinstance(args[0], (SelectBase, ScalarSelect)):
4069 s = args[0]
4070 else:
4071 if not args:
4072 args = ([literal_column("*")],)
4073 s = Select(*args, **kwargs).as_scalar().self_group()
4075 UnaryExpression.__init__(
4076 self,
4077 s,
4078 operator=operators.exists,
4079 type_=type_api.BOOLEANTYPE,
4080 wraps_column_expression=True,
4081 )
4083 def select(self, whereclause=None, **params):
4084 return Select([self], whereclause, **params)
4086 def correlate(self, *fromclause):
4087 e = self._clone()
4088 e.element = self.element.correlate(*fromclause).self_group()
4089 return e
4091 def correlate_except(self, *fromclause):
4092 e = self._clone()
4093 e.element = self.element.correlate_except(*fromclause).self_group()
4094 return e
4096 def select_from(self, clause):
4097 """return a new :class:`_expression.Exists` construct,
4098 applying the given
4099 expression to the :meth:`_expression.Select.select_from`
4100 method of the select
4101 statement contained.
4103 """
4104 e = self._clone()
4105 e.element = self.element.select_from(clause).self_group()
4106 return e
4108 def where(self, clause):
4109 """return a new exists() construct with the given expression added to
4110 its WHERE clause, joined to the existing clause via AND, if any.
4112 """
4113 e = self._clone()
4114 e.element = self.element.where(clause).self_group()
4115 return e
4118class TextAsFrom(SelectBase):
4119 """Wrap a :class:`_expression.TextClause` construct within a
4120 :class:`_expression.SelectBase`
4121 interface.
4123 This allows the :class:`_expression.TextClause` object to gain a ``.
4124 c`` collection
4125 and other FROM-like capabilities such as
4126 :meth:`_expression.FromClause.alias`,
4127 :meth:`_expression.SelectBase.cte`, etc.
4129 The :class:`.TextAsFrom` construct is produced via the
4130 :meth:`_expression.TextClause.columns`
4131 method - see that method for details.
4133 .. versionadded:: 0.9.0
4135 .. seealso::
4137 :func:`_expression.text`
4139 :meth:`_expression.TextClause.columns`
4141 """
4143 __visit_name__ = "text_as_from"
4145 _textual = True
4147 def __init__(self, text, columns, positional=False):
4148 self.element = text
4149 self.column_args = columns
4150 self.positional = positional
4152 @property
4153 def _bind(self):
4154 return self.element._bind
4156 @_generative
4157 def bindparams(self, *binds, **bind_as_values):
4158 self.element = self.element.bindparams(*binds, **bind_as_values)
4160 def _populate_column_collection(self):
4161 for c in self.column_args:
4162 c._make_proxy(self)
4164 def _copy_internals(self, clone=_clone, **kw):
4165 self._reset_exported()
4166 self.element = clone(self.element, **kw)
4168 def _scalar_type(self):
4169 return self.column_args[0].type
4172class AnnotatedFromClause(Annotated):
4173 def __init__(self, element, values):
4174 # force FromClause to generate their internal
4175 # collections into __dict__
4176 element.c
4177 Annotated.__init__(self, element, values)