Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/selectable.py
2# Copyright (C) 2005-2021 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: http://www.opensource.org/licenses/mit-license.php
8"""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 :param alias: the alias name
132 :param \*args, \**kwargs: all other arguments are delivered to the
133 :func:`_expression.select` function.
135 """
136 return Select(*args, **kwargs).alias(alias)
139class Selectable(ClauseElement):
140 """Mark a class as being selectable."""
142 __visit_name__ = "selectable"
144 is_selectable = True
146 @property
147 def selectable(self):
148 return self
151class HasPrefixes(object):
152 _prefixes = ()
154 @_generative
155 @_document_text_coercion(
156 "expr",
157 ":meth:`_expression.HasPrefixes.prefix_with`",
158 ":paramref:`.HasPrefixes.prefix_with.*expr`",
159 )
160 def prefix_with(self, *expr, **kw):
161 r"""Add one or more expressions following the statement keyword, i.e.
162 SELECT, INSERT, UPDATE, or DELETE. Generative.
164 This is used to support backend-specific prefix keywords such as those
165 provided by MySQL.
167 E.g.::
169 stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")
171 # MySQL 5.7 optimizer hints
172 stmt = select([table]).prefix_with(
173 "/*+ BKA(t1) */", dialect="mysql")
175 Multiple prefixes can be specified by multiple calls
176 to :meth:`_expression.HasPrefixes.prefix_with`.
178 :param \*expr: textual or :class:`_expression.ClauseElement`
179 construct which
180 will be rendered following the INSERT, UPDATE, or DELETE
181 keyword.
182 :param \**kw: A single keyword 'dialect' is accepted. This is an
183 optional string dialect name which will
184 limit rendering of this prefix to only that dialect.
186 """
187 dialect = kw.pop("dialect", None)
188 if kw:
189 raise exc.ArgumentError(
190 "Unsupported argument(s): %s" % ",".join(kw)
191 )
192 self._setup_prefixes(expr, dialect)
194 def _setup_prefixes(self, prefixes, dialect=None):
195 self._prefixes = self._prefixes + tuple(
196 [
197 (_literal_as_text(p, allow_coercion_to_text=True), dialect)
198 for p in prefixes
199 ]
200 )
203class HasSuffixes(object):
204 _suffixes = ()
206 @_generative
207 @_document_text_coercion(
208 "expr",
209 ":meth:`_expression.HasSuffixes.suffix_with`",
210 ":paramref:`.HasSuffixes.suffix_with.*expr`",
211 )
212 def suffix_with(self, *expr, **kw):
213 r"""Add one or more expressions following the statement as a whole.
215 This is used to support backend-specific suffix keywords on
216 certain constructs.
218 E.g.::
220 stmt = select([col1, col2]).cte().suffix_with(
221 "cycle empno set y_cycle to 1 default 0", dialect="oracle")
223 Multiple suffixes can be specified by multiple calls
224 to :meth:`_expression.HasSuffixes.suffix_with`.
226 :param \*expr: textual or :class:`_expression.ClauseElement`
227 construct which
228 will be rendered following the target clause.
229 :param \**kw: A single keyword 'dialect' is accepted. This is an
230 optional string dialect name which will
231 limit rendering of this suffix to only that dialect.
233 """
234 dialect = kw.pop("dialect", None)
235 if kw:
236 raise exc.ArgumentError(
237 "Unsupported argument(s): %s" % ",".join(kw)
238 )
239 self._setup_suffixes(expr, dialect)
241 def _setup_suffixes(self, suffixes, dialect=None):
242 self._suffixes = self._suffixes + tuple(
243 [
244 (_literal_as_text(p, allow_coercion_to_text=True), dialect)
245 for p in suffixes
246 ]
247 )
250class FromClause(Selectable):
251 """Represent an element that can be used within the ``FROM``
252 clause of a ``SELECT`` statement.
254 The most common forms of :class:`_expression.FromClause` are the
255 :class:`_schema.Table` and the :func:`_expression.select` constructs. Key
256 features common to all :class:`_expression.FromClause` objects include:
258 * a :attr:`.c` collection, which provides per-name access to a collection
259 of :class:`_expression.ColumnElement` objects.
260 * a :attr:`.primary_key` attribute, which is a collection of all those
261 :class:`_expression.ColumnElement`
262 objects that indicate the ``primary_key`` flag.
263 * Methods to generate various derivations of a "from" clause, including
264 :meth:`_expression.FromClause.alias`,
265 :meth:`_expression.FromClause.join`,
266 :meth:`_expression.FromClause.select`.
269 """
271 __visit_name__ = "fromclause"
272 named_with_column = False
273 _hide_froms = []
275 _is_join = False
276 _is_select = False
277 _is_from_container = False
279 _is_lateral = False
281 _textual = False
282 """A marker that allows us to easily distinguish a :class:`.TextAsFrom`
283 or similar object from other kinds of :class:`_expression.FromClause`
284 objects."""
286 schema = None
287 """Define the 'schema' attribute for this :class:`_expression.FromClause`.
289 This is typically ``None`` for most objects except that of
290 :class:`_schema.Table`, where it is taken as the value of the
291 :paramref:`_schema.Table.schema` argument.
293 """
295 def _translate_schema(self, effective_schema, map_):
296 return effective_schema
298 _memoized_property = util.group_expirable_memoized_property(["_columns"])
300 @util.deprecated(
301 "1.1",
302 message="The :meth:`.FromClause.count` method is deprecated, "
303 "and will be removed in a future release. Please use the "
304 ":class:`_functions.count` function available from the "
305 ":attr:`.func` namespace.",
306 )
307 @util.dependencies("sqlalchemy.sql.functions")
308 def count(self, functions, whereclause=None, **params):
309 """Return a SELECT COUNT generated against this
310 :class:`_expression.FromClause`.
312 .. seealso::
314 :class:`_functions.count`
316 """
318 if self.primary_key:
319 col = list(self.primary_key)[0]
320 else:
321 col = list(self.columns)[0]
322 return Select(
323 [functions.func.count(col).label("tbl_row_count")],
324 whereclause,
325 from_obj=[self],
326 **params
327 )
329 def select(self, whereclause=None, **params):
330 """Return a SELECT of this :class:`_expression.FromClause`.
332 .. seealso::
334 :func:`_expression.select` - general purpose
335 method which allows for arbitrary column lists.
337 """
339 return Select([self], whereclause, **params)
341 def join(self, right, onclause=None, isouter=False, full=False):
342 """Return a :class:`_expression.Join` from this
343 :class:`_expression.FromClause` to another
344 :class:`_expression.FromClause`.
346 E.g.::
348 from sqlalchemy import join
350 j = user_table.join(address_table,
351 user_table.c.id == address_table.c.user_id)
352 stmt = select([user_table]).select_from(j)
354 would emit SQL along the lines of::
356 SELECT user.id, user.name FROM user
357 JOIN address ON user.id = address.user_id
359 :param right: the right side of the join; this is any
360 :class:`_expression.FromClause` object such as a
361 :class:`_schema.Table` object, and
362 may also be a selectable-compatible object such as an ORM-mapped
363 class.
365 :param onclause: a SQL expression representing the ON clause of the
366 join. If left at ``None``, :meth:`_expression.FromClause.join`
367 will attempt to
368 join the two tables based on a foreign key relationship.
370 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
372 :param full: if True, render a FULL OUTER JOIN, instead of LEFT OUTER
373 JOIN. Implies :paramref:`.FromClause.join.isouter`.
375 .. versionadded:: 1.1
377 .. seealso::
379 :func:`_expression.join` - standalone function
381 :class:`_expression.Join` - the type of object produced
383 """
385 return Join(self, right, onclause, isouter, full)
387 def outerjoin(self, right, onclause=None, full=False):
388 """Return a :class:`_expression.Join` from this
389 :class:`_expression.FromClause`
390 to another :class:`FromClause`, with the "isouter" flag set to
391 True.
393 E.g.::
395 from sqlalchemy import outerjoin
397 j = user_table.outerjoin(address_table,
398 user_table.c.id == address_table.c.user_id)
400 The above is equivalent to::
402 j = user_table.join(
403 address_table,
404 user_table.c.id == address_table.c.user_id,
405 isouter=True)
407 :param right: the right side of the join; this is any
408 :class:`_expression.FromClause` object such as a
409 :class:`_schema.Table` object, and
410 may also be a selectable-compatible object such as an ORM-mapped
411 class.
413 :param onclause: a SQL expression representing the ON clause of the
414 join. If left at ``None``, :meth:`_expression.FromClause.join`
415 will attempt to
416 join the two tables based on a foreign key relationship.
418 :param full: if True, render a FULL OUTER JOIN, instead of
419 LEFT OUTER JOIN.
421 .. versionadded:: 1.1
423 .. seealso::
425 :meth:`_expression.FromClause.join`
427 :class:`_expression.Join`
429 """
431 return Join(self, right, onclause, True, full)
433 def alias(self, name=None, flat=False):
434 """Return an alias of this :class:`_expression.FromClause`.
436 E.g.::
438 a2 = some_table.alias('a2')
440 The above code creates an :class:`_expression.Alias`
441 object which can be used
442 as a FROM clause in any SELECT statement.
444 .. seealso::
446 :ref:`core_tutorial_aliases`
448 :func:`_expression.alias`
450 """
452 return Alias._construct(self, name)
454 def lateral(self, name=None):
455 """Return a LATERAL alias of this :class:`_expression.FromClause`.
457 The return value is the :class:`_expression.Lateral` construct also
458 provided by the top-level :func:`_expression.lateral` function.
460 .. versionadded:: 1.1
462 .. seealso::
464 :ref:`lateral_selects` - overview of usage.
466 """
467 return Lateral._construct(self, name)
469 def tablesample(self, sampling, name=None, seed=None):
470 """Return a TABLESAMPLE alias of this :class:`_expression.FromClause`.
472 The return value is the :class:`_expression.TableSample`
473 construct also
474 provided by the top-level :func:`_expression.tablesample` function.
476 .. versionadded:: 1.1
478 .. seealso::
480 :func:`_expression.tablesample` - usage guidelines and parameters
482 """
483 return TableSample._construct(self, sampling, name, seed)
485 def is_derived_from(self, fromclause):
486 """Return ``True`` if this :class:`_expression.FromClause` is
487 'derived' from the given ``FromClause``.
489 An example would be an Alias of a Table is derived from that Table.
491 """
492 # this is essentially an "identity" check in the base class.
493 # Other constructs override this to traverse through
494 # contained elements.
495 return fromclause in self._cloned_set
497 def _is_lexical_equivalent(self, other):
498 """Return ``True`` if this :class:`_expression.FromClause` and
499 the other represent the same lexical identity.
501 This tests if either one is a copy of the other, or
502 if they are the same via annotation identity.
504 """
505 return self._cloned_set.intersection(other._cloned_set)
507 @util.dependencies("sqlalchemy.sql.util")
508 def replace_selectable(self, sqlutil, old, alias):
509 """Replace all occurrences of FromClause 'old' with the given Alias
510 object, returning a copy of this :class:`_expression.FromClause`.
512 """
514 return sqlutil.ClauseAdapter(alias).traverse(self)
516 def correspond_on_equivalents(self, column, equivalents):
517 """Return corresponding_column for the given column, or if None
518 search for a match in the given dictionary.
520 """
521 col = self.corresponding_column(column, require_embedded=True)
522 if col is None and col in equivalents:
523 for equiv in equivalents[col]:
524 nc = self.corresponding_column(equiv, require_embedded=True)
525 if nc:
526 return nc
527 return col
529 def corresponding_column(self, column, require_embedded=False):
530 """Given a :class:`_expression.ColumnElement`, return the exported
531 :class:`_expression.ColumnElement` object from this
532 :class:`expression.Selectable`
533 which corresponds to that original
534 :class:`~sqlalchemy.schema.Column` via a common ancestor
535 column.
537 :param column: the target :class:`_expression.ColumnElement`
538 to be matched
540 :param require_embedded: only return corresponding columns for
541 the given :class:`_expression.ColumnElement`, if the given
542 :class:`_expression.ColumnElement`
543 is actually present within a sub-element
544 of this :class:`_expression.FromClause`.
545 Normally the column will match if
546 it merely shares a common ancestor with one of the exported
547 columns of this :class:`_expression.FromClause`.
549 """
551 def embedded(expanded_proxy_set, target_set):
552 for t in target_set.difference(expanded_proxy_set):
553 if not set(_expand_cloned([t])).intersection(
554 expanded_proxy_set
555 ):
556 return False
557 return True
559 # don't dig around if the column is locally present
560 if self.c.contains_column(column):
561 return column
562 col, intersect = None, None
563 target_set = column.proxy_set
564 cols = self.c._all_columns
565 for c in cols:
566 expanded_proxy_set = set(_expand_cloned(c.proxy_set))
567 i = target_set.intersection(expanded_proxy_set)
568 if i and (
569 not require_embedded
570 or embedded(expanded_proxy_set, target_set)
571 ):
572 if col is None:
574 # no corresponding column yet, pick this one.
576 col, intersect = c, i
577 elif len(i) > len(intersect):
579 # 'c' has a larger field of correspondence than
580 # 'col'. i.e. selectable.c.a1_x->a1.c.x->table.c.x
581 # matches a1.c.x->table.c.x better than
582 # selectable.c.x->table.c.x does.
584 col, intersect = c, i
585 elif i == intersect:
587 # they have the same field of correspondence. see
588 # which proxy_set has fewer columns in it, which
589 # indicates a closer relationship with the root
590 # column. Also take into account the "weight"
591 # attribute which CompoundSelect() uses to give
592 # higher precedence to columns based on vertical
593 # position in the compound statement, and discard
594 # columns that have no reference to the target
595 # column (also occurs with CompoundSelect)
597 col_distance = util.reduce(
598 operator.add,
599 [
600 sc._annotations.get("weight", 1)
601 for sc in col._uncached_proxy_set()
602 if sc.shares_lineage(column)
603 ],
604 )
605 c_distance = util.reduce(
606 operator.add,
607 [
608 sc._annotations.get("weight", 1)
609 for sc in c._uncached_proxy_set()
610 if sc.shares_lineage(column)
611 ],
612 )
613 if c_distance < col_distance:
614 col, intersect = c, i
615 return col
617 @property
618 def description(self):
619 """A brief description of this :class:`_expression.FromClause`.
621 Used primarily for error message formatting.
623 """
624 return getattr(self, "name", self.__class__.__name__ + " object")
626 def _reset_exported(self):
627 """Delete memoized collections when a FromClause is cloned."""
629 self._memoized_property.expire_instance(self)
631 @_memoized_property
632 def columns(self):
633 """A named-based collection of :class:`_expression.ColumnElement`
634 objects
635 maintained by this :class:`_expression.FromClause`.
637 The :attr:`.columns`, or :attr:`.c` collection, is the gateway
638 to the construction of SQL expressions using table-bound or
639 other selectable-bound columns::
641 select([mytable]).where(mytable.c.somecolumn == 5)
643 """
645 if "_columns" not in self.__dict__:
646 self._init_collections()
647 self._populate_column_collection()
648 return self._columns.as_immutable()
650 @_memoized_property
651 def primary_key(self):
652 """Return the collection of :class:`_schema.Column` objects
653 which comprise the primary key of this FromClause.
655 """
656 self._init_collections()
657 self._populate_column_collection()
658 return self.primary_key
660 @_memoized_property
661 def foreign_keys(self):
662 """Return the collection of :class:`_schema.ForeignKey` objects
663 which this FromClause references.
665 """
666 self._init_collections()
667 self._populate_column_collection()
668 return self.foreign_keys
670 c = property(
671 attrgetter("columns"),
672 doc="An alias for the :attr:`.columns` attribute.",
673 )
674 _select_iterable = property(attrgetter("columns"))
676 def _init_collections(self):
677 assert "_columns" not in self.__dict__
678 assert "primary_key" not in self.__dict__
679 assert "foreign_keys" not in self.__dict__
681 self._columns = ColumnCollection()
682 self.primary_key = ColumnSet()
683 self.foreign_keys = set()
685 @property
686 def _cols_populated(self):
687 return "_columns" in self.__dict__
689 def _populate_column_collection(self):
690 """Called on subclasses to establish the .c collection.
692 Each implementation has a different way of establishing
693 this collection.
695 """
697 def _refresh_for_new_column(self, column):
698 """Given a column added to the .c collection of an underlying
699 selectable, produce the local version of that column, assuming this
700 selectable ultimately should proxy this column.
702 this is used to "ping" a derived selectable to add a new column
703 to its .c. collection when a Column has been added to one of the
704 Table objects it ultimtely derives from.
706 If the given selectable hasn't populated its .c. collection yet,
707 it should at least pass on the message to the contained selectables,
708 but it will return None.
710 This method is currently used by Declarative to allow Table
711 columns to be added to a partially constructed inheritance
712 mapping that may have already produced joins. The method
713 isn't public right now, as the full span of implications
714 and/or caveats aren't yet clear.
716 It's also possible that this functionality could be invoked by
717 default via an event, which would require that
718 selectables maintain a weak referencing collection of all
719 derivations.
721 """
722 if not self._cols_populated:
723 return None
724 elif column.key in self.columns and self.columns[column.key] is column:
725 return column
726 else:
727 return None
730class Join(FromClause):
731 """Represent a ``JOIN`` construct between two
732 :class:`_expression.FromClause` elements.
734 The public constructor function for :class:`_expression.Join`
735 is the module-level
736 :func:`_expression.join()` function, as well as the
737 :meth:`_expression.FromClause.join` method
738 of any :class:`_expression.FromClause` (e.g. such as
739 :class:`_schema.Table`).
741 .. seealso::
743 :func:`_expression.join`
745 :meth:`_expression.FromClause.join`
747 """
749 __visit_name__ = "join"
751 _is_join = True
753 def __init__(self, left, right, onclause=None, isouter=False, full=False):
754 """Construct a new :class:`_expression.Join`.
756 The usual entrypoint here is the :func:`_expression.join`
757 function or the :meth:`_expression.FromClause.join` method of any
758 :class:`_expression.FromClause` object.
760 """
761 self.left = _interpret_as_from(left)
762 self.right = _interpret_as_from(right).self_group()
764 if onclause is None:
765 self.onclause = self._match_primaries(self.left, self.right)
766 else:
767 self.onclause = onclause
769 self.isouter = isouter
770 self.full = full
772 @classmethod
773 def _create_outerjoin(cls, left, right, onclause=None, full=False):
774 """Return an ``OUTER JOIN`` clause element.
776 The returned object is an instance of :class:`_expression.Join`.
778 Similar functionality is also available via the
779 :meth:`_expression.FromClause.outerjoin` method on any
780 :class:`_expression.FromClause`.
782 :param left: The left side of the join.
784 :param right: The right side of the join.
786 :param onclause: Optional criterion for the ``ON`` clause, is
787 derived from foreign key relationships established between
788 left and right otherwise.
790 To chain joins together, use the :meth:`_expression.FromClause.join`
791 or
792 :meth:`_expression.FromClause.outerjoin` methods on the resulting
793 :class:`_expression.Join` object.
795 """
796 return cls(left, right, onclause, isouter=True, full=full)
798 @classmethod
799 def _create_join(
800 cls, left, right, onclause=None, isouter=False, full=False
801 ):
802 """Produce a :class:`_expression.Join` object, given two
803 :class:`_expression.FromClause`
804 expressions.
806 E.g.::
808 j = join(user_table, address_table,
809 user_table.c.id == address_table.c.user_id)
810 stmt = select([user_table]).select_from(j)
812 would emit SQL along the lines of::
814 SELECT user.id, user.name FROM user
815 JOIN address ON user.id = address.user_id
817 Similar functionality is available given any
818 :class:`_expression.FromClause` object (e.g. such as a
819 :class:`_schema.Table`) using
820 the :meth:`_expression.FromClause.join` method.
822 :param left: The left side of the join.
824 :param right: the right side of the join; this is any
825 :class:`_expression.FromClause` object such as a
826 :class:`_schema.Table` object, and
827 may also be a selectable-compatible object such as an ORM-mapped
828 class.
830 :param onclause: a SQL expression representing the ON clause of the
831 join. If left at ``None``, :meth:`_expression.FromClause.join`
832 will attempt to
833 join the two tables based on a foreign key relationship.
835 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
837 :param full: if True, render a FULL OUTER JOIN, instead of JOIN.
839 .. versionadded:: 1.1
841 .. seealso::
843 :meth:`_expression.FromClause.join` - method form,
844 based on a given left side.
846 :class:`_expression.Join` - the type of object produced.
848 """
850 return cls(left, right, onclause, isouter, full)
852 @property
853 def description(self):
854 return "Join object on %s(%d) and %s(%d)" % (
855 self.left.description,
856 id(self.left),
857 self.right.description,
858 id(self.right),
859 )
861 def is_derived_from(self, fromclause):
862 return (
863 fromclause is self
864 or self.left.is_derived_from(fromclause)
865 or self.right.is_derived_from(fromclause)
866 )
868 def self_group(self, against=None):
869 return FromGrouping(self)
871 @util.dependencies("sqlalchemy.sql.util")
872 def _populate_column_collection(self, sqlutil):
873 columns = [c for c in self.left.columns] + [
874 c for c in self.right.columns
875 ]
877 self.primary_key.extend(
878 sqlutil.reduce_columns(
879 (c for c in columns if c.primary_key), self.onclause
880 )
881 )
882 self._columns.update((col._label, col) for col in columns)
883 self.foreign_keys.update(
884 itertools.chain(*[col.foreign_keys for col in columns])
885 )
887 def _refresh_for_new_column(self, column):
888 col = self.left._refresh_for_new_column(column)
889 if col is None:
890 col = self.right._refresh_for_new_column(column)
891 if col is not None:
892 if self._cols_populated:
893 self._columns[col._label] = col
894 self.foreign_keys.update(col.foreign_keys)
895 if col.primary_key:
896 self.primary_key.add(col)
897 return col
898 return None
900 def _copy_internals(self, clone=_clone, **kw):
901 self._reset_exported()
902 self.left = clone(self.left, **kw)
903 self.right = clone(self.right, **kw)
904 self.onclause = clone(self.onclause, **kw)
906 def get_children(self, **kwargs):
907 return self.left, self.right, self.onclause
909 def _match_primaries(self, left, right):
910 if isinstance(left, Join):
911 left_right = left.right
912 else:
913 left_right = None
914 return self._join_condition(left, right, a_subset=left_right)
916 @classmethod
917 @util.deprecated_params(
918 ignore_nonexistent_tables=(
919 "0.9",
920 "The :paramref:`.join_condition.ignore_nonexistent_tables` "
921 "parameter is deprecated and will be removed in a future "
922 "release. Tables outside of the two tables being handled "
923 "are no longer considered.",
924 )
925 )
926 def _join_condition(
927 cls,
928 a,
929 b,
930 ignore_nonexistent_tables=False,
931 a_subset=None,
932 consider_as_foreign_keys=None,
933 ):
934 """Create a join condition between two tables or selectables.
936 e.g.::
938 join_condition(tablea, tableb)
940 would produce an expression along the lines of::
942 tablea.c.id==tableb.c.tablea_id
944 The join is determined based on the foreign key relationships
945 between the two selectables. If there are multiple ways
946 to join, or no way to join, an error is raised.
948 :param ignore_nonexistent_tables: unused - tables outside of the
949 two tables being handled are not considered.
951 :param a_subset: An optional expression that is a sub-component
952 of ``a``. An attempt will be made to join to just this sub-component
953 first before looking at the full ``a`` construct, and if found
954 will be successful even if there are other ways to join to ``a``.
955 This allows the "right side" of a join to be passed thereby
956 providing a "natural join".
958 """
959 constraints = cls._joincond_scan_left_right(
960 a, a_subset, b, consider_as_foreign_keys
961 )
963 if len(constraints) > 1:
964 cls._joincond_trim_constraints(
965 a, b, constraints, consider_as_foreign_keys
966 )
968 if len(constraints) == 0:
969 if isinstance(b, FromGrouping):
970 hint = (
971 " Perhaps you meant to convert the right side to a "
972 "subquery using alias()?"
973 )
974 else:
975 hint = ""
976 raise exc.NoForeignKeysError(
977 "Can't find any foreign key relationships "
978 "between '%s' and '%s'.%s"
979 % (a.description, b.description, hint)
980 )
982 crit = [(x == y) for x, y in list(constraints.values())[0]]
983 if len(crit) == 1:
984 return crit[0]
985 else:
986 return and_(*crit)
988 @classmethod
989 def _can_join(cls, left, right, consider_as_foreign_keys=None):
990 if isinstance(left, Join):
991 left_right = left.right
992 else:
993 left_right = None
995 constraints = cls._joincond_scan_left_right(
996 a=left,
997 b=right,
998 a_subset=left_right,
999 consider_as_foreign_keys=consider_as_foreign_keys,
1000 )
1002 return bool(constraints)
1004 @classmethod
1005 @util.dependencies("sqlalchemy.sql.util")
1006 def _joincond_scan_left_right(
1007 cls, sql_util, a, a_subset, b, consider_as_foreign_keys
1008 ):
1009 constraints = collections.defaultdict(list)
1011 for left in (a_subset, a):
1012 if left is None:
1013 continue
1014 for fk in sorted(
1015 b.foreign_keys, key=lambda fk: fk.parent._creation_order
1016 ):
1017 if (
1018 consider_as_foreign_keys is not None
1019 and fk.parent not in consider_as_foreign_keys
1020 ):
1021 continue
1022 try:
1023 col = fk.get_referent(left)
1024 except exc.NoReferenceError as nrte:
1025 table_names = {t.name for t in sql_util.find_tables(left)}
1026 if nrte.table_name in table_names:
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 table_names = {t.name for t in sql_util.find_tables(b)}
1046 if nrte.table_name in table_names:
1047 raise
1048 else:
1049 continue
1051 if col is not None:
1052 constraints[fk.constraint].append((col, fk.parent))
1053 if constraints:
1054 break
1055 return constraints
1057 @classmethod
1058 def _joincond_trim_constraints(
1059 cls, a, b, constraints, consider_as_foreign_keys
1060 ):
1061 # more than one constraint matched. narrow down the list
1062 # to include just those FKCs that match exactly to
1063 # "consider_as_foreign_keys".
1064 if consider_as_foreign_keys:
1065 for const in list(constraints):
1066 if set(f.parent for f in const.elements) != set(
1067 consider_as_foreign_keys
1068 ):
1069 del constraints[const]
1071 # if still multiple constraints, but
1072 # they all refer to the exact same end result, use it.
1073 if len(constraints) > 1:
1074 dedupe = set(tuple(crit) for crit in constraints.values())
1075 if len(dedupe) == 1:
1076 key = list(constraints)[0]
1077 constraints = {key: constraints[key]}
1079 if len(constraints) != 1:
1080 raise exc.AmbiguousForeignKeysError(
1081 "Can't determine join between '%s' and '%s'; "
1082 "tables have more than one foreign key "
1083 "constraint relationship between them. "
1084 "Please specify the 'onclause' of this "
1085 "join explicitly." % (a.description, b.description)
1086 )
1088 def select(self, whereclause=None, **kwargs):
1089 r"""Create a :class:`_expression.Select` from this
1090 :class:`_expression.Join`.
1092 The equivalent long-hand form, given a :class:`_expression.Join`
1093 object
1094 ``j``, is::
1096 from sqlalchemy import select
1097 j = select([j.left, j.right], **kw).\
1098 where(whereclause).\
1099 select_from(j)
1101 :param whereclause: the WHERE criterion that will be sent to
1102 the :func:`select()` function
1104 :param \**kwargs: all other kwargs are sent to the
1105 underlying :func:`select()` function.
1107 """
1108 collist = [self.left, self.right]
1110 return Select(collist, whereclause, from_obj=[self], **kwargs)
1112 @property
1113 def bind(self):
1114 return self.left.bind or self.right.bind
1116 @util.dependencies("sqlalchemy.sql.util")
1117 def alias(self, sqlutil, name=None, flat=False):
1118 r"""Return an alias of this :class:`_expression.Join`.
1120 The default behavior here is to first produce a SELECT
1121 construct from this :class:`_expression.Join`, then to produce an
1122 :class:`_expression.Alias` from that. So given a join of the form::
1124 j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
1126 The JOIN by itself would look like::
1128 table_a JOIN table_b ON table_a.id = table_b.a_id
1130 Whereas the alias of the above, ``j.alias()``, would in a
1131 SELECT context look like::
1133 (SELECT table_a.id AS table_a_id, table_b.id AS table_b_id,
1134 table_b.a_id AS table_b_a_id
1135 FROM table_a
1136 JOIN table_b ON table_a.id = table_b.a_id) AS anon_1
1138 The equivalent long-hand form, given a :class:`_expression.Join`
1139 object
1140 ``j``, is::
1142 from sqlalchemy import select, alias
1143 j = alias(
1144 select([j.left, j.right]).\
1145 select_from(j).\
1146 with_labels(True).\
1147 correlate(False),
1148 name=name
1149 )
1151 The selectable produced by :meth:`_expression.Join.alias`
1152 features the same
1153 columns as that of the two individual selectables presented under
1154 a single name - the individual columns are "auto-labeled", meaning
1155 the ``.c.`` collection of the resulting :class:`_expression.Alias`
1156 represents
1157 the names of the individual columns using a
1158 ``<tablename>_<columname>`` scheme::
1160 j.c.table_a_id
1161 j.c.table_b_a_id
1163 :meth:`_expression.Join.alias` also features an alternate
1164 option for aliasing joins which produces no enclosing SELECT and
1165 does not normally apply labels to the column names. The
1166 ``flat=True`` option will call :meth:`_expression.FromClause.alias`
1167 against the left and right sides individually.
1168 Using this option, no new ``SELECT`` is produced;
1169 we instead, from a construct as below::
1171 j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
1172 j = j.alias(flat=True)
1174 we get a result like this::
1176 table_a AS table_a_1 JOIN table_b AS table_b_1 ON
1177 table_a_1.id = table_b_1.a_id
1179 The ``flat=True`` argument is also propagated to the contained
1180 selectables, so that a composite join such as::
1182 j = table_a.join(
1183 table_b.join(table_c,
1184 table_b.c.id == table_c.c.b_id),
1185 table_b.c.a_id == table_a.c.id
1186 ).alias(flat=True)
1188 Will produce an expression like::
1190 table_a AS table_a_1 JOIN (
1191 table_b AS table_b_1 JOIN table_c AS table_c_1
1192 ON table_b_1.id = table_c_1.b_id
1193 ) ON table_a_1.id = table_b_1.a_id
1195 The standalone :func:`_expression.alias` function as well as the
1196 base :meth:`_expression.FromClause.alias`
1197 method also support the ``flat=True``
1198 argument as a no-op, so that the argument can be passed to the
1199 ``alias()`` method of any selectable.
1201 .. versionadded:: 0.9.0 Added the ``flat=True`` option to create
1202 "aliases" of joins without enclosing inside of a SELECT
1203 subquery.
1205 :param name: name given to the alias.
1207 :param flat: if True, produce an alias of the left and right
1208 sides of this :class:`_expression.Join` and return the join of those
1209 two selectables. This produces join expression that does not
1210 include an enclosing SELECT.
1212 .. versionadded:: 0.9.0
1214 .. seealso::
1216 :ref:`core_tutorial_aliases`
1218 :func:`_expression.alias`
1220 """
1221 if flat:
1222 assert name is None, "Can't send name argument with flat"
1223 left_a, right_a = (
1224 self.left.alias(flat=True),
1225 self.right.alias(flat=True),
1226 )
1227 adapter = sqlutil.ClauseAdapter(left_a).chain(
1228 sqlutil.ClauseAdapter(right_a)
1229 )
1231 return left_a.join(
1232 right_a,
1233 adapter.traverse(self.onclause),
1234 isouter=self.isouter,
1235 full=self.full,
1236 )
1237 else:
1238 return self.select(use_labels=True, correlate=False).alias(name)
1240 @property
1241 def _hide_froms(self):
1242 return itertools.chain(
1243 *[_from_objects(x.left, x.right) for x in self._cloned_set]
1244 )
1246 @property
1247 def _from_objects(self):
1248 return (
1249 [self]
1250 + self.onclause._from_objects
1251 + self.left._from_objects
1252 + self.right._from_objects
1253 )
1256class Alias(FromClause):
1257 """Represents an table or selectable alias (AS).
1259 Represents an alias, as typically applied to any table or
1260 sub-select within a SQL statement using the ``AS`` keyword (or
1261 without the keyword on certain databases such as Oracle).
1263 This object is constructed from the :func:`_expression.alias` module
1264 level function as well as the :meth:`_expression.FromClause.alias`
1265 method available
1266 on all :class:`_expression.FromClause` subclasses.
1268 """
1270 __visit_name__ = "alias"
1271 named_with_column = True
1273 _is_from_container = True
1275 def __init__(self, *arg, **kw):
1276 raise NotImplementedError(
1277 "The %s class is not intended to be constructed "
1278 "directly. Please use the %s() standalone "
1279 "function or the %s() method available from appropriate "
1280 "selectable objects."
1281 % (
1282 self.__class__.__name__,
1283 self.__class__.__name__.lower(),
1284 self.__class__.__name__.lower(),
1285 )
1286 )
1288 @classmethod
1289 def _construct(cls, *arg, **kw):
1290 obj = cls.__new__(cls)
1291 obj._init(*arg, **kw)
1292 return obj
1294 @classmethod
1295 def _factory(cls, selectable, name=None, flat=False):
1296 """Return an :class:`_expression.Alias` object.
1298 An :class:`_expression.Alias` represents any
1299 :class:`_expression.FromClause`
1300 with an alternate name assigned within SQL, typically using the ``AS``
1301 clause when generated, e.g. ``SELECT * FROM table AS aliasname``.
1303 Similar functionality is available via the
1304 :meth:`_expression.FromClause.alias`
1305 method available on all :class:`_expression.FromClause` subclasses.
1306 In terms of
1307 a SELECT object as generated from the :func:`_expression.select`
1308 function, the :meth:`_expression.SelectBase.alias` method returns an
1309 :class:`_expression.Alias` or similar object which represents a named,
1310 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, the effect is that of
1318 creating a named subquery, i.e. ``(select ...) AS aliasname``.
1320 The ``name`` parameter is optional, and provides the name
1321 to use in the rendered SQL. If blank, an "anonymous" name
1322 will be deterministically generated at compile time.
1323 Deterministic means the name is guaranteed to be unique against
1324 other constructs used in the same statement, and will also be the
1325 same name for each successive compilation of the same statement
1326 object.
1328 :param selectable: any :class:`_expression.FromClause` subclass,
1329 such as a table, select statement, etc.
1331 :param name: string name to be assigned as the alias.
1332 If ``None``, a name will be deterministically generated
1333 at compile time.
1335 :param flat: Will be passed through to if the given selectable
1336 is an instance of :class:`_expression.Join` - see
1337 :meth:`_expression.Join.alias`
1338 for details.
1340 .. versionadded:: 0.9.0
1342 """
1343 return _interpret_as_from(selectable).alias(name=name, flat=flat)
1345 def _init(self, selectable, name=None):
1346 baseselectable = selectable
1347 while isinstance(baseselectable, Alias):
1348 baseselectable = baseselectable.element
1349 self.original = baseselectable
1350 self.supports_execution = baseselectable.supports_execution
1351 if self.supports_execution:
1352 self._execution_options = baseselectable._execution_options
1353 self.element = selectable
1354 if name is None:
1355 if self.original.named_with_column:
1356 name = getattr(self.original, "name", None)
1357 name = _anonymous_label("%%(%d %s)s" % (id(self), name or "anon"))
1358 self.name = name
1360 def self_group(self, against=None):
1361 if (
1362 isinstance(against, CompoundSelect)
1363 and isinstance(self.original, Select)
1364 and self.original._needs_parens_for_grouping()
1365 ):
1366 return FromGrouping(self)
1368 return super(Alias, self).self_group(against=against)
1370 @property
1371 def description(self):
1372 if util.py3k:
1373 return self.name
1374 else:
1375 return self.name.encode("ascii", "backslashreplace")
1377 def as_scalar(self):
1378 try:
1379 return self.element.as_scalar()
1380 except AttributeError as err:
1381 util.raise_(
1382 AttributeError(
1383 "Element %s does not support "
1384 "'as_scalar()'" % self.element
1385 ),
1386 replace_context=err,
1387 )
1389 def is_derived_from(self, fromclause):
1390 if fromclause in self._cloned_set:
1391 return True
1392 return self.element.is_derived_from(fromclause)
1394 def _populate_column_collection(self):
1395 for col in self.element.columns._all_columns:
1396 col._make_proxy(self)
1398 def _refresh_for_new_column(self, column):
1399 col = self.element._refresh_for_new_column(column)
1400 if col is not None:
1401 if not self._cols_populated:
1402 return None
1403 else:
1404 return col._make_proxy(self)
1405 else:
1406 return None
1408 def _copy_internals(self, clone=_clone, **kw):
1409 # don't apply anything to an aliased Table
1410 # for now. May want to drive this from
1411 # the given **kw.
1412 if isinstance(self.element, TableClause):
1413 return
1414 self._reset_exported()
1415 self.element = clone(self.element, **kw)
1416 baseselectable = self.element
1417 while isinstance(baseselectable, Alias):
1418 baseselectable = baseselectable.element
1419 self.original = baseselectable
1421 def get_children(self, column_collections=True, **kw):
1422 if column_collections:
1423 for c in self.c:
1424 yield c
1425 yield self.element
1427 @property
1428 def _from_objects(self):
1429 return [self]
1431 @property
1432 def bind(self):
1433 return self.element.bind
1436class Lateral(Alias):
1437 """Represent a LATERAL subquery.
1439 This object is constructed from the :func:`_expression.lateral` module
1440 level function as well as the :meth:`_expression.FromClause.lateral`
1441 method available
1442 on all :class:`_expression.FromClause` subclasses.
1444 While LATERAL is part of the SQL standard, currently only more recent
1445 PostgreSQL versions provide support for this keyword.
1447 .. versionadded:: 1.1
1449 .. seealso::
1451 :ref:`lateral_selects` - overview of usage.
1453 """
1455 __visit_name__ = "lateral"
1456 _is_lateral = True
1458 @classmethod
1459 def _factory(cls, selectable, name=None):
1460 """Return a :class:`_expression.Lateral` object.
1462 :class:`_expression.Lateral` is an :class:`_expression.Alias`
1463 subclass that represents
1464 a subquery with the LATERAL keyword applied to it.
1466 The special behavior of a LATERAL subquery is that it appears in the
1467 FROM clause of an enclosing SELECT, but may correlate to other
1468 FROM clauses of that SELECT. It is a special case of subquery
1469 only supported by a small number of backends, currently more recent
1470 PostgreSQL versions.
1472 .. versionadded:: 1.1
1474 .. seealso::
1476 :ref:`lateral_selects` - overview of usage.
1478 """
1479 return _interpret_as_from(selectable).lateral(name=name)
1482class TableSample(Alias):
1483 """Represent a TABLESAMPLE clause.
1485 This object is constructed from the :func:`_expression.tablesample` module
1486 level function as well as the :meth:`_expression.FromClause.tablesample`
1487 method
1488 available on all :class:`_expression.FromClause` subclasses.
1490 .. versionadded:: 1.1
1492 .. seealso::
1494 :func:`_expression.tablesample`
1496 """
1498 __visit_name__ = "tablesample"
1500 @classmethod
1501 def _factory(cls, selectable, sampling, name=None, seed=None):
1502 """Return a :class:`_expression.TableSample` object.
1504 :class:`_expression.TableSample` is an :class:`_expression.Alias`
1505 subclass that represents
1506 a table with the TABLESAMPLE clause applied to it.
1507 :func:`_expression.tablesample`
1508 is also available from the :class:`_expression.FromClause`
1509 class via the
1510 :meth:`_expression.FromClause.tablesample` method.
1512 The TABLESAMPLE clause allows selecting a randomly selected approximate
1513 percentage of rows from a table. It supports multiple sampling methods,
1514 most commonly BERNOULLI and SYSTEM.
1516 e.g.::
1518 from sqlalchemy import func
1520 selectable = people.tablesample(
1521 func.bernoulli(1),
1522 name='alias',
1523 seed=func.random())
1524 stmt = select([selectable.c.people_id])
1526 Assuming ``people`` with a column ``people_id``, the above
1527 statement would render as::
1529 SELECT alias.people_id FROM
1530 people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
1531 REPEATABLE (random())
1533 .. versionadded:: 1.1
1535 :param sampling: a ``float`` percentage between 0 and 100 or
1536 :class:`_functions.Function`.
1538 :param name: optional alias name
1540 :param seed: any real-valued SQL expression. When specified, the
1541 REPEATABLE sub-clause is also rendered.
1543 """
1544 return _interpret_as_from(selectable).tablesample(
1545 sampling, name=name, seed=seed
1546 )
1548 def _init(self, selectable, sampling, name=None, seed=None):
1549 self.sampling = sampling
1550 self.seed = seed
1551 super(TableSample, self)._init(selectable, name=name)
1553 @util.dependencies("sqlalchemy.sql.functions")
1554 def _get_method(self, functions):
1555 if isinstance(self.sampling, functions.Function):
1556 return self.sampling
1557 else:
1558 return functions.func.system(self.sampling)
1561class CTE(Generative, HasPrefixes, HasSuffixes, Alias):
1562 """Represent a Common Table Expression.
1564 The :class:`_expression.CTE` object is obtained using the
1565 :meth:`_expression.SelectBase.cte` method from any selectable.
1566 See that method for complete examples.
1568 """
1570 __visit_name__ = "cte"
1572 @classmethod
1573 def _factory(cls, selectable, name=None, recursive=False):
1574 r"""Return a new :class:`_expression.CTE`,
1575 or Common Table Expression instance.
1577 Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage.
1579 """
1580 return _interpret_as_from(selectable).cte(
1581 name=name, recursive=recursive
1582 )
1584 def _init(
1585 self,
1586 selectable,
1587 name=None,
1588 recursive=False,
1589 _cte_alias=None,
1590 _restates=frozenset(),
1591 _prefixes=None,
1592 _suffixes=None,
1593 ):
1594 self.recursive = recursive
1595 self._cte_alias = _cte_alias
1596 self._restates = _restates
1597 if _prefixes:
1598 self._prefixes = _prefixes
1599 if _suffixes:
1600 self._suffixes = _suffixes
1601 super(CTE, self)._init(selectable, name=name)
1603 def _copy_internals(self, clone=_clone, **kw):
1604 super(CTE, self)._copy_internals(clone, **kw)
1605 if self._cte_alias is not None:
1606 self._cte_alias = clone(self._cte_alias, **kw)
1607 self._restates = frozenset(
1608 [clone(elem, **kw) for elem in self._restates]
1609 )
1611 @util.dependencies("sqlalchemy.sql.dml")
1612 def _populate_column_collection(self, dml):
1613 if isinstance(self.element, dml.UpdateBase):
1614 for col in self.element._returning:
1615 col._make_proxy(self)
1616 else:
1617 for col in self.element.columns._all_columns:
1618 col._make_proxy(self)
1620 def alias(self, name=None, flat=False):
1621 """Return an :class:`_expression.Alias` of this
1622 :class:`_expression.CTE`.
1624 This method is a CTE-specific specialization of the
1625 :meth:`_expression.FromClause.alias` method.
1627 .. seealso::
1629 :ref:`core_tutorial_aliases`
1631 :func:`_expression.alias`
1633 """
1634 return CTE._construct(
1635 self.original,
1636 name=name,
1637 recursive=self.recursive,
1638 _cte_alias=self,
1639 _prefixes=self._prefixes,
1640 _suffixes=self._suffixes,
1641 )
1643 def union(self, other):
1644 return CTE._construct(
1645 self.original.union(other),
1646 name=self.name,
1647 recursive=self.recursive,
1648 _restates=self._restates.union([self]),
1649 _prefixes=self._prefixes,
1650 _suffixes=self._suffixes,
1651 )
1653 def union_all(self, other):
1654 return CTE._construct(
1655 self.original.union_all(other),
1656 name=self.name,
1657 recursive=self.recursive,
1658 _restates=self._restates.union([self]),
1659 _prefixes=self._prefixes,
1660 _suffixes=self._suffixes,
1661 )
1664class HasCTE(object):
1665 """Mixin that declares a class to include CTE support.
1667 .. versionadded:: 1.1
1669 """
1671 def cte(self, name=None, recursive=False):
1672 r"""Return a new :class:`_expression.CTE`,
1673 or Common Table Expression instance.
1675 Common table expressions are a SQL standard whereby SELECT
1676 statements can draw upon secondary statements specified along
1677 with the primary statement, using a clause called "WITH".
1678 Special semantics regarding UNION can also be employed to
1679 allow "recursive" queries, where a SELECT statement can draw
1680 upon the set of rows that have previously been selected.
1682 CTEs can also be applied to DML constructs UPDATE, INSERT
1683 and DELETE on some databases, both as a source of CTE rows
1684 when combined with RETURNING, as well as a consumer of
1685 CTE rows.
1687 .. versionchanged:: 1.1 Added support for UPDATE/INSERT/DELETE as
1688 CTE, CTEs added to UPDATE/INSERT/DELETE.
1690 SQLAlchemy detects :class:`_expression.CTE` objects, which are treated
1691 similarly to :class:`_expression.Alias` objects, as special elements
1692 to be delivered to the FROM clause of the statement as well
1693 as to a WITH clause at the top of the statement.
1695 For special prefixes such as PostgreSQL "MATERIALIZED" and
1696 "NOT MATERIALIZED", the :meth:`_expression.CTE.prefix_with`
1697 method may be
1698 used to establish these.
1700 .. versionchanged:: 1.3.13 Added support for prefixes.
1701 In particular - MATERIALIZED and NOT MATERIALIZED.
1703 :param name: name given to the common table expression. Like
1704 :meth:`_expression.FromClause.alias`, the name can be left as
1705 ``None`` in which case an anonymous symbol will be used at query
1706 compile time.
1707 :param recursive: if ``True``, will render ``WITH RECURSIVE``.
1708 A recursive common table expression is intended to be used in
1709 conjunction with UNION ALL in order to derive rows
1710 from those already selected.
1712 The following examples include two from PostgreSQL's documentation at
1713 http://www.postgresql.org/docs/current/static/queries-with.html,
1714 as well as additional examples.
1716 Example 1, non recursive::
1718 from sqlalchemy import (Table, Column, String, Integer,
1719 MetaData, select, func)
1721 metadata = MetaData()
1723 orders = Table('orders', metadata,
1724 Column('region', String),
1725 Column('amount', Integer),
1726 Column('product', String),
1727 Column('quantity', Integer)
1728 )
1730 regional_sales = select([
1731 orders.c.region,
1732 func.sum(orders.c.amount).label('total_sales')
1733 ]).group_by(orders.c.region).cte("regional_sales")
1736 top_regions = select([regional_sales.c.region]).\
1737 where(
1738 regional_sales.c.total_sales >
1739 select([
1740 func.sum(regional_sales.c.total_sales)/10
1741 ])
1742 ).cte("top_regions")
1744 statement = select([
1745 orders.c.region,
1746 orders.c.product,
1747 func.sum(orders.c.quantity).label("product_units"),
1748 func.sum(orders.c.amount).label("product_sales")
1749 ]).where(orders.c.region.in_(
1750 select([top_regions.c.region])
1751 )).group_by(orders.c.region, orders.c.product)
1753 result = conn.execute(statement).fetchall()
1755 Example 2, WITH RECURSIVE::
1757 from sqlalchemy import (Table, Column, String, Integer,
1758 MetaData, select, func)
1760 metadata = MetaData()
1762 parts = Table('parts', metadata,
1763 Column('part', String),
1764 Column('sub_part', String),
1765 Column('quantity', Integer),
1766 )
1768 included_parts = select([
1769 parts.c.sub_part,
1770 parts.c.part,
1771 parts.c.quantity]).\
1772 where(parts.c.part=='our part').\
1773 cte(recursive=True)
1776 incl_alias = included_parts.alias()
1777 parts_alias = parts.alias()
1778 included_parts = included_parts.union_all(
1779 select([
1780 parts_alias.c.sub_part,
1781 parts_alias.c.part,
1782 parts_alias.c.quantity
1783 ]).
1784 where(parts_alias.c.part==incl_alias.c.sub_part)
1785 )
1787 statement = select([
1788 included_parts.c.sub_part,
1789 func.sum(included_parts.c.quantity).
1790 label('total_quantity')
1791 ]).\
1792 group_by(included_parts.c.sub_part)
1794 result = conn.execute(statement).fetchall()
1796 Example 3, an upsert using UPDATE and INSERT with CTEs::
1798 from datetime import date
1799 from sqlalchemy import (MetaData, Table, Column, Integer,
1800 Date, select, literal, and_, exists)
1802 metadata = MetaData()
1804 visitors = Table('visitors', metadata,
1805 Column('product_id', Integer, primary_key=True),
1806 Column('date', Date, primary_key=True),
1807 Column('count', Integer),
1808 )
1810 # add 5 visitors for the product_id == 1
1811 product_id = 1
1812 day = date.today()
1813 count = 5
1815 update_cte = (
1816 visitors.update()
1817 .where(and_(visitors.c.product_id == product_id,
1818 visitors.c.date == day))
1819 .values(count=visitors.c.count + count)
1820 .returning(literal(1))
1821 .cte('update_cte')
1822 )
1824 upsert = visitors.insert().from_select(
1825 [visitors.c.product_id, visitors.c.date, visitors.c.count],
1826 select([literal(product_id), literal(day), literal(count)])
1827 .where(~exists(update_cte.select()))
1828 )
1830 connection.execute(upsert)
1832 .. seealso::
1834 :meth:`.orm.query.Query.cte` - ORM version of
1835 :meth:`_expression.HasCTE.cte`.
1837 """
1838 return CTE._construct(self, name=name, recursive=recursive)
1841class FromGrouping(FromClause):
1842 """Represent a grouping of a FROM clause"""
1844 __visit_name__ = "grouping"
1846 def __init__(self, element):
1847 self.element = element
1849 def _init_collections(self):
1850 pass
1852 @property
1853 def columns(self):
1854 return self.element.columns
1856 @property
1857 def primary_key(self):
1858 return self.element.primary_key
1860 @property
1861 def foreign_keys(self):
1862 return self.element.foreign_keys
1864 def is_derived_from(self, element):
1865 return self.element.is_derived_from(element)
1867 def alias(self, **kw):
1868 return FromGrouping(self.element.alias(**kw))
1870 @property
1871 def _hide_froms(self):
1872 return self.element._hide_froms
1874 def get_children(self, **kwargs):
1875 return (self.element,)
1877 def _copy_internals(self, clone=_clone, **kw):
1878 self.element = clone(self.element, **kw)
1880 @property
1881 def _from_objects(self):
1882 return self.element._from_objects
1884 def __getattr__(self, attr):
1885 return getattr(self.element, attr)
1887 def __getstate__(self):
1888 return {"element": self.element}
1890 def __setstate__(self, state):
1891 self.element = state["element"]
1894class TableClause(Immutable, FromClause):
1895 """Represents a minimal "table" construct.
1897 This is a lightweight table object that has only a name, a
1898 collection of columns, which are typically produced
1899 by the :func:`_expression.column` function, and a schema::
1901 from sqlalchemy import table, column
1903 user = table("user",
1904 column("id"),
1905 column("name"),
1906 column("description"),
1907 )
1909 The :class:`_expression.TableClause` construct serves as the base for
1910 the more commonly used :class:`_schema.Table` object, providing
1911 the usual set of :class:`_expression.FromClause` services including
1912 the ``.c.`` collection and statement generation methods.
1914 It does **not** provide all the additional schema-level services
1915 of :class:`_schema.Table`, including constraints, references to other
1916 tables, or support for :class:`_schema.MetaData`-level services.
1917 It's useful
1918 on its own as an ad-hoc construct used to generate quick SQL
1919 statements when a more fully fledged :class:`_schema.Table`
1920 is not on hand.
1922 """
1924 __visit_name__ = "table"
1926 named_with_column = True
1928 implicit_returning = False
1929 """:class:`_expression.TableClause`
1930 doesn't support having a primary key or column
1931 -level defaults, so implicit returning doesn't apply."""
1933 _autoincrement_column = None
1934 """No PK or default support so no autoincrement column."""
1936 def __init__(self, name, *columns, **kw):
1937 """Produce a new :class:`_expression.TableClause`.
1939 The object returned is an instance of
1940 :class:`_expression.TableClause`, which
1941 represents the "syntactical" portion of the schema-level
1942 :class:`_schema.Table` object.
1943 It may be used to construct lightweight table constructs.
1945 .. versionchanged:: 1.0.0 :func:`_expression.table` can now
1946 be imported from the plain ``sqlalchemy`` namespace like any
1947 other SQL element.
1950 :param name: Name of the table.
1952 :param columns: A collection of :func:`_expression.column` constructs.
1954 :param schema: The schema name for this table.
1956 .. versionadded:: 1.3.18 :func:`_expression.table` can now
1957 accept a ``schema`` argument.
1958 """
1960 super(TableClause, self).__init__()
1961 self.name = self.fullname = name
1962 self._columns = ColumnCollection()
1963 self.primary_key = ColumnSet()
1964 self.foreign_keys = set()
1965 for c in columns:
1966 self.append_column(c)
1968 schema = kw.pop("schema", None)
1969 if schema is not None:
1970 self.schema = schema
1971 if kw:
1972 raise exc.ArgumentError("Unsupported argument(s): %s" % list(kw))
1974 def _init_collections(self):
1975 pass
1977 @util.memoized_property
1978 def description(self):
1979 if util.py3k:
1980 return self.name
1981 else:
1982 return self.name.encode("ascii", "backslashreplace")
1984 def append_column(self, c):
1985 existing = c.table
1986 if existing is not None and existing is not self:
1987 raise exc.ArgumentError(
1988 "column object '%s' already assigned to table %r"
1989 % (c.key, getattr(existing, "description", existing))
1990 )
1992 self._columns[c.key] = c
1993 c.table = self
1995 def get_children(self, column_collections=True, **kwargs):
1996 if column_collections:
1997 return [c for c in self.c]
1998 else:
1999 return []
2001 @util.dependencies("sqlalchemy.sql.dml")
2002 def insert(self, dml, values=None, inline=False, **kwargs):
2003 """Generate an :func:`_expression.insert` construct against this
2004 :class:`_expression.TableClause`.
2006 E.g.::
2008 table.insert().values(name='foo')
2010 See :func:`_expression.insert` for argument and usage information.
2012 """
2014 return dml.Insert(self, values=values, inline=inline, **kwargs)
2016 @util.dependencies("sqlalchemy.sql.dml")
2017 def update(
2018 self, dml, whereclause=None, values=None, inline=False, **kwargs
2019 ):
2020 """Generate an :func:`_expression.update` construct against this
2021 :class:`_expression.TableClause`.
2023 E.g.::
2025 table.update().where(table.c.id==7).values(name='foo')
2027 See :func:`_expression.update` for argument and usage information.
2029 """
2031 return dml.Update(
2032 self,
2033 whereclause=whereclause,
2034 values=values,
2035 inline=inline,
2036 **kwargs
2037 )
2039 @util.dependencies("sqlalchemy.sql.dml")
2040 def delete(self, dml, whereclause=None, **kwargs):
2041 """Generate a :func:`_expression.delete` construct against this
2042 :class:`_expression.TableClause`.
2044 E.g.::
2046 table.delete().where(table.c.id==7)
2048 See :func:`_expression.delete` for argument and usage information.
2050 """
2052 return dml.Delete(self, whereclause, **kwargs)
2054 @property
2055 def _from_objects(self):
2056 return [self]
2059class ForUpdateArg(ClauseElement):
2060 @classmethod
2061 def parse_legacy_select(self, arg):
2062 """Parse the for_update argument of :func:`_expression.select`.
2064 :param mode: Defines the lockmode to use.
2066 ``None`` - translates to no lockmode
2068 ``'update'`` - translates to ``FOR UPDATE``
2069 (standard SQL, supported by most dialects)
2071 ``'nowait'`` - translates to ``FOR UPDATE NOWAIT``
2072 (supported by Oracle, PostgreSQL 8.1 upwards)
2074 ``'read'`` - translates to ``LOCK IN SHARE MODE`` (for MySQL),
2075 and ``FOR SHARE`` (for PostgreSQL)
2077 ``'read_nowait'`` - translates to ``FOR SHARE NOWAIT``
2078 (supported by PostgreSQL). ``FOR SHARE`` and
2079 ``FOR SHARE NOWAIT`` (PostgreSQL).
2081 """
2082 if arg in (None, False):
2083 return None
2085 nowait = read = False
2086 if arg == "nowait":
2087 nowait = True
2088 elif arg == "read":
2089 read = True
2090 elif arg == "read_nowait":
2091 read = nowait = True
2092 elif arg is not True:
2093 raise exc.ArgumentError("Unknown for_update argument: %r" % arg)
2095 return ForUpdateArg(read=read, nowait=nowait)
2097 @property
2098 def legacy_for_update_value(self):
2099 if self.read and not self.nowait:
2100 return "read"
2101 elif self.read and self.nowait:
2102 return "read_nowait"
2103 elif self.nowait:
2104 return "nowait"
2105 else:
2106 return True
2108 def __eq__(self, other):
2109 return (
2110 isinstance(other, ForUpdateArg)
2111 and other.nowait == self.nowait
2112 and other.read == self.read
2113 and other.skip_locked == self.skip_locked
2114 and other.key_share == self.key_share
2115 and other.of is self.of
2116 )
2118 def __hash__(self):
2119 return id(self)
2121 def _copy_internals(self, clone=_clone, **kw):
2122 if self.of is not None:
2123 self.of = [clone(col, **kw) for col in self.of]
2125 def __init__(
2126 self,
2127 nowait=False,
2128 read=False,
2129 of=None,
2130 skip_locked=False,
2131 key_share=False,
2132 ):
2133 """Represents arguments specified to
2134 :meth:`_expression.Select.for_update`.
2136 .. versionadded:: 0.9.0
2138 """
2140 self.nowait = nowait
2141 self.read = read
2142 self.skip_locked = skip_locked
2143 self.key_share = key_share
2144 if of is not None:
2145 self.of = [
2146 _interpret_as_column_or_from(elem) for elem in util.to_list(of)
2147 ]
2148 else:
2149 self.of = None
2152class SelectBase(HasCTE, Executable, FromClause):
2153 """Base class for SELECT statements.
2156 This includes :class:`_expression.Select`,
2157 :class:`_selectable.CompoundSelect` and
2158 :class:`.TextAsFrom`.
2161 """
2163 def as_scalar(self):
2164 """Return a 'scalar' representation of this selectable, which can be
2165 used as a column expression.
2167 Typically, a select statement which has only one column in its columns
2168 clause is eligible to be used as a scalar expression.
2170 The returned object is an instance of
2171 :class:`ScalarSelect`.
2173 """
2174 return ScalarSelect(self)
2176 def label(self, name):
2177 """Return a 'scalar' representation of this selectable, embedded as a
2178 subquery with a label.
2180 .. seealso::
2182 :meth:`_expression.SelectBase.as_scalar`.
2184 """
2185 return self.as_scalar().label(name)
2187 @_generative
2188 @util.deprecated(
2189 "0.6",
2190 message="The :meth:`.SelectBase.autocommit` method is deprecated, "
2191 "and will be removed in a future release. Please use the "
2192 "the :paramref:`.Connection.execution_options.autocommit` "
2193 "parameter in conjunction with the "
2194 ":meth:`.Executable.execution_options` method.",
2195 )
2196 def autocommit(self):
2197 """Return a new selectable with the 'autocommit' flag set to
2198 True.
2200 """
2202 self._execution_options = self._execution_options.union(
2203 {"autocommit": True}
2204 )
2206 def _generate(self):
2207 """Override the default _generate() method to also clear out
2208 exported collections."""
2210 s = self.__class__.__new__(self.__class__)
2211 s.__dict__ = self.__dict__.copy()
2212 s._reset_exported()
2213 return s
2215 @property
2216 def _from_objects(self):
2217 return [self]
2220class GenerativeSelect(SelectBase):
2221 """Base class for SELECT statements where additional elements can be
2222 added.
2224 This serves as the base for :class:`_expression.Select` and
2225 :class:`_selectable.CompoundSelect`
2226 where elements such as ORDER BY, GROUP BY can be added and column
2227 rendering can be controlled. Compare to :class:`.TextAsFrom`, which,
2228 while it subclasses :class:`_expression.SelectBase`
2229 and is also a SELECT construct,
2230 represents a fixed textual string which cannot be altered at this level,
2231 only wrapped as a subquery.
2233 .. versionadded:: 0.9.0 :class:`_expression.GenerativeSelect` was added to
2234 provide functionality specific to :class:`_expression.Select` and
2235 :class:`_selectable.CompoundSelect` while allowing
2236 :class:`_expression.SelectBase` to be
2237 used for other SELECT-like objects, e.g. :class:`.TextAsFrom`.
2239 """
2241 _order_by_clause = ClauseList()
2242 _group_by_clause = ClauseList()
2243 _limit_clause = None
2244 _offset_clause = None
2245 _for_update_arg = None
2247 def __init__(
2248 self,
2249 use_labels=False,
2250 for_update=False,
2251 limit=None,
2252 offset=None,
2253 order_by=None,
2254 group_by=None,
2255 bind=None,
2256 autocommit=None,
2257 ):
2258 self.use_labels = use_labels
2260 if for_update is not False:
2261 self._for_update_arg = ForUpdateArg.parse_legacy_select(for_update)
2263 if autocommit is not None:
2264 util.warn_deprecated(
2265 "The select.autocommit parameter is deprecated and will be "
2266 "removed in a future release. Please refer to the "
2267 "Select.execution_options.autocommit` parameter."
2268 )
2269 self._execution_options = self._execution_options.union(
2270 {"autocommit": autocommit}
2271 )
2272 if limit is not None:
2273 self._limit_clause = _offset_or_limit_clause(limit)
2274 if offset is not None:
2275 self._offset_clause = _offset_or_limit_clause(offset)
2276 self._bind = bind
2278 if order_by is not None:
2279 self._order_by_clause = ClauseList(
2280 *util.to_list(order_by),
2281 _literal_as_text=_literal_and_labels_as_label_reference
2282 )
2283 if group_by is not None:
2284 self._group_by_clause = ClauseList(
2285 *util.to_list(group_by),
2286 _literal_as_text=_literal_as_label_reference
2287 )
2289 @property
2290 def for_update(self):
2291 """Provide legacy dialect support for the ``for_update`` attribute."""
2292 if self._for_update_arg is not None:
2293 return self._for_update_arg.legacy_for_update_value
2294 else:
2295 return None
2297 @for_update.setter
2298 def for_update(self, value):
2299 self._for_update_arg = ForUpdateArg.parse_legacy_select(value)
2301 @_generative
2302 def with_for_update(
2303 self,
2304 nowait=False,
2305 read=False,
2306 of=None,
2307 skip_locked=False,
2308 key_share=False,
2309 ):
2310 """Specify a ``FOR UPDATE`` clause for this
2311 :class:`_expression.GenerativeSelect`.
2313 E.g.::
2315 stmt = select([table]).with_for_update(nowait=True)
2317 On a database like PostgreSQL or Oracle, the above would render a
2318 statement like::
2320 SELECT table.a, table.b FROM table FOR UPDATE NOWAIT
2322 on other backends, the ``nowait`` option is ignored and instead
2323 would produce::
2325 SELECT table.a, table.b FROM table FOR UPDATE
2327 When called with no arguments, the statement will render with
2328 the suffix ``FOR UPDATE``. Additional arguments can then be
2329 provided which allow for common database-specific
2330 variants.
2332 :param nowait: boolean; will render ``FOR UPDATE NOWAIT`` on Oracle
2333 and PostgreSQL dialects.
2335 :param read: boolean; will render ``LOCK IN SHARE MODE`` on MySQL,
2336 ``FOR SHARE`` on PostgreSQL. On PostgreSQL, when combined with
2337 ``nowait``, will render ``FOR SHARE NOWAIT``.
2339 :param of: SQL expression or list of SQL expression elements
2340 (typically :class:`_schema.Column`
2341 objects or a compatible expression) which
2342 will render into a ``FOR UPDATE OF`` clause; supported by PostgreSQL
2343 and Oracle. May render as a table or as a column depending on
2344 backend.
2346 :param skip_locked: boolean, will render ``FOR UPDATE SKIP LOCKED``
2347 on Oracle and PostgreSQL dialects or ``FOR SHARE SKIP LOCKED`` if
2348 ``read=True`` is also specified.
2350 .. versionadded:: 1.1.0
2352 :param key_share: boolean, will render ``FOR NO KEY UPDATE``,
2353 or if combined with ``read=True`` will render ``FOR KEY SHARE``,
2354 on the PostgreSQL dialect.
2356 .. versionadded:: 1.1.0
2358 """
2359 self._for_update_arg = ForUpdateArg(
2360 nowait=nowait,
2361 read=read,
2362 of=of,
2363 skip_locked=skip_locked,
2364 key_share=key_share,
2365 )
2367 @_generative
2368 def apply_labels(self):
2369 """Return a new selectable with the 'use_labels' flag set to True.
2371 This will result in column expressions being generated using labels
2372 against their table name, such as "SELECT somecolumn AS
2373 tablename_somecolumn". This allows selectables which contain multiple
2374 FROM clauses to produce a unique set of column names regardless of
2375 name conflicts among the individual FROM clauses.
2377 """
2378 self.use_labels = True
2380 @property
2381 def _limit(self):
2382 """Get an integer value for the limit. This should only be used
2383 by code that cannot support a limit as a BindParameter or
2384 other custom clause as it will throw an exception if the limit
2385 isn't currently set to an integer.
2387 """
2388 return _offset_or_limit_clause_asint(self._limit_clause, "limit")
2390 @property
2391 def _simple_int_limit(self):
2392 """True if the LIMIT clause is a simple integer, False
2393 if it is not present or is a SQL expression.
2394 """
2395 return isinstance(self._limit_clause, _OffsetLimitParam)
2397 @property
2398 def _simple_int_offset(self):
2399 """True if the OFFSET clause is a simple integer, False
2400 if it is not present or is a SQL expression.
2401 """
2402 return isinstance(self._offset_clause, _OffsetLimitParam)
2404 @property
2405 def _offset(self):
2406 """Get an integer value for the offset. This should only be used
2407 by code that cannot support an offset as a BindParameter or
2408 other custom clause as it will throw an exception if the
2409 offset isn't currently set to an integer.
2411 """
2412 return _offset_or_limit_clause_asint(self._offset_clause, "offset")
2414 @_generative
2415 def limit(self, limit):
2416 """Return a new selectable with the given LIMIT criterion
2417 applied.
2419 This is a numerical value which usually renders as a ``LIMIT``
2420 expression in the resulting select. Backends that don't
2421 support ``LIMIT`` will attempt to provide similar
2422 functionality.
2424 .. versionchanged:: 1.0.0 - :meth:`_expression.Select.limit` can now
2425 accept arbitrary SQL expressions as well as integer values.
2427 :param limit: an integer LIMIT parameter, or a SQL expression
2428 that provides an integer result.
2430 """
2432 self._limit_clause = _offset_or_limit_clause(limit)
2434 @_generative
2435 def offset(self, offset):
2436 """Return a new selectable with the given OFFSET criterion
2437 applied.
2440 This is a numeric value which usually renders as an ``OFFSET``
2441 expression in the resulting select. Backends that don't
2442 support ``OFFSET`` will attempt to provide similar
2443 functionality.
2446 .. versionchanged:: 1.0.0 - :meth:`_expression.Select.offset` can now
2447 accept arbitrary SQL expressions as well as integer values.
2449 :param offset: an integer OFFSET parameter, or a SQL expression
2450 that provides an integer result.
2452 """
2454 self._offset_clause = _offset_or_limit_clause(offset)
2456 @_generative
2457 def order_by(self, *clauses):
2458 r"""Return a new selectable with the given list of ORDER BY
2459 criterion applied.
2461 e.g.::
2463 stmt = select([table]).order_by(table.c.id, table.c.name)
2465 :param \*clauses: a series of :class:`_expression.ColumnElement`
2466 constructs
2467 which will be used to generate an ORDER BY clause.
2469 .. seealso::
2471 :ref:`core_tutorial_ordering`
2473 """
2475 self.append_order_by(*clauses)
2477 @_generative
2478 def group_by(self, *clauses):
2479 r"""Return a new selectable with the given list of GROUP BY
2480 criterion applied.
2482 e.g.::
2484 stmt = select([table.c.name, func.max(table.c.stat)]).\
2485 group_by(table.c.name)
2487 :param \*clauses: a series of :class:`_expression.ColumnElement`
2488 constructs
2489 which will be used to generate an GROUP BY clause.
2491 .. seealso::
2493 :ref:`core_tutorial_ordering`
2495 """
2497 self.append_group_by(*clauses)
2499 def append_order_by(self, *clauses):
2500 """Append the given ORDER BY criterion applied to this selectable.
2502 The criterion will be appended to any pre-existing ORDER BY criterion.
2504 This is an **in-place** mutation method; the
2505 :meth:`_expression.GenerativeSelect.order_by` method is preferred,
2506 as it
2507 provides standard :term:`method chaining`.
2509 .. seealso::
2511 :meth:`_expression.GenerativeSelect.order_by`
2513 """
2514 if len(clauses) == 1 and clauses[0] is None:
2515 self._order_by_clause = ClauseList()
2516 else:
2517 if getattr(self, "_order_by_clause", None) is not None:
2518 clauses = list(self._order_by_clause) + list(clauses)
2519 self._order_by_clause = ClauseList(
2520 *clauses,
2521 _literal_as_text=_literal_and_labels_as_label_reference
2522 )
2524 def append_group_by(self, *clauses):
2525 """Append the given GROUP BY criterion applied to this selectable.
2527 The criterion will be appended to any pre-existing GROUP BY criterion.
2529 This is an **in-place** mutation method; the
2530 :meth:`_expression.GenerativeSelect.group_by` method is preferred,
2531 as it provides standard :term:`method chaining`.
2533 .. seealso::
2535 :meth:`_expression.GenerativeSelect.group_by`
2537 """
2538 if len(clauses) == 1 and clauses[0] is None:
2539 self._group_by_clause = ClauseList()
2540 else:
2541 if getattr(self, "_group_by_clause", None) is not None:
2542 clauses = list(self._group_by_clause) + list(clauses)
2543 self._group_by_clause = ClauseList(
2544 *clauses, _literal_as_text=_literal_as_label_reference
2545 )
2547 @property
2548 def _label_resolve_dict(self):
2549 raise NotImplementedError()
2551 def _copy_internals(self, clone=_clone, **kw):
2552 if self._limit_clause is not None:
2553 self._limit_clause = clone(self._limit_clause, **kw)
2554 if self._offset_clause is not None:
2555 self._offset_clause = clone(self._offset_clause, **kw)
2558class CompoundSelect(GenerativeSelect):
2559 """Forms the basis of ``UNION``, ``UNION ALL``, and other
2560 SELECT-based set operations.
2563 .. seealso::
2565 :func:`_expression.union`
2567 :func:`_expression.union_all`
2569 :func:`_expression.intersect`
2571 :func:`_expression.intersect_all`
2573 :func:`_expression.except`
2575 :func:`_expression.except_all`
2577 """
2579 __visit_name__ = "compound_select"
2581 UNION = util.symbol("UNION")
2582 UNION_ALL = util.symbol("UNION ALL")
2583 EXCEPT = util.symbol("EXCEPT")
2584 EXCEPT_ALL = util.symbol("EXCEPT ALL")
2585 INTERSECT = util.symbol("INTERSECT")
2586 INTERSECT_ALL = util.symbol("INTERSECT ALL")
2588 _is_from_container = True
2590 def __init__(self, keyword, *selects, **kwargs):
2591 self._auto_correlate = kwargs.pop("correlate", False)
2592 self.keyword = keyword
2593 self.selects = []
2595 numcols = None
2597 # some DBs do not like ORDER BY in the inner queries of a UNION, etc.
2598 for n, s in enumerate(selects):
2599 s = _clause_element_as_expr(s)
2601 if not numcols:
2602 numcols = len(s.c._all_columns)
2603 elif len(s.c._all_columns) != numcols:
2604 raise exc.ArgumentError(
2605 "All selectables passed to "
2606 "CompoundSelect must have identical numbers of "
2607 "columns; select #%d has %d columns, select "
2608 "#%d has %d"
2609 % (
2610 1,
2611 len(self.selects[0].c._all_columns),
2612 n + 1,
2613 len(s.c._all_columns),
2614 )
2615 )
2617 self.selects.append(s.self_group(against=self))
2619 GenerativeSelect.__init__(self, **kwargs)
2621 @property
2622 def _label_resolve_dict(self):
2623 d = dict((c.key, c) for c in self.c)
2624 return d, d, d
2626 @classmethod
2627 def _create_union(cls, *selects, **kwargs):
2628 r"""Return a ``UNION`` of multiple selectables.
2630 The returned object is an instance of
2631 :class:`_selectable.CompoundSelect`.
2633 A similar :func:`union()` method is available on all
2634 :class:`_expression.FromClause` subclasses.
2636 :param \*selects:
2637 a list of :class:`_expression.Select` instances.
2639 :param \**kwargs:
2640 available keyword arguments are the same as those of
2641 :func:`select`.
2643 """
2644 return CompoundSelect(CompoundSelect.UNION, *selects, **kwargs)
2646 @classmethod
2647 def _create_union_all(cls, *selects, **kwargs):
2648 r"""Return a ``UNION ALL`` of multiple selectables.
2650 The returned object is an instance of
2651 :class:`_selectable.CompoundSelect`.
2653 A similar :func:`union_all()` method is available on all
2654 :class:`_expression.FromClause` subclasses.
2656 :param \*selects:
2657 a list of :class:`_expression.Select` instances.
2659 :param \**kwargs:
2660 available keyword arguments are the same as those of
2661 :func:`select`.
2663 """
2664 return CompoundSelect(CompoundSelect.UNION_ALL, *selects, **kwargs)
2666 @classmethod
2667 def _create_except(cls, *selects, **kwargs):
2668 r"""Return an ``EXCEPT`` of multiple selectables.
2670 The returned object is an instance of
2671 :class:`_selectable.CompoundSelect`.
2673 :param \*selects:
2674 a list of :class:`_expression.Select` instances.
2676 :param \**kwargs:
2677 available keyword arguments are the same as those of
2678 :func:`select`.
2680 """
2681 return CompoundSelect(CompoundSelect.EXCEPT, *selects, **kwargs)
2683 @classmethod
2684 def _create_except_all(cls, *selects, **kwargs):
2685 r"""Return an ``EXCEPT ALL`` of multiple selectables.
2687 The returned object is an instance of
2688 :class:`_selectable.CompoundSelect`.
2690 :param \*selects:
2691 a list of :class:`_expression.Select` instances.
2693 :param \**kwargs:
2694 available keyword arguments are the same as those of
2695 :func:`select`.
2697 """
2698 return CompoundSelect(CompoundSelect.EXCEPT_ALL, *selects, **kwargs)
2700 @classmethod
2701 def _create_intersect(cls, *selects, **kwargs):
2702 r"""Return an ``INTERSECT`` of multiple selectables.
2704 The returned object is an instance of
2705 :class:`_selectable.CompoundSelect`.
2707 :param \*selects:
2708 a list of :class:`_expression.Select` instances.
2710 :param \**kwargs:
2711 available keyword arguments are the same as those of
2712 :func:`select`.
2714 """
2715 return CompoundSelect(CompoundSelect.INTERSECT, *selects, **kwargs)
2717 @classmethod
2718 def _create_intersect_all(cls, *selects, **kwargs):
2719 r"""Return an ``INTERSECT ALL`` of multiple selectables.
2721 The returned object is an instance of
2722 :class:`_selectable.CompoundSelect`.
2724 :param \*selects:
2725 a list of :class:`_expression.Select` instances.
2727 :param \**kwargs:
2728 available keyword arguments are the same as those of
2729 :func:`select`.
2731 """
2732 return CompoundSelect(CompoundSelect.INTERSECT_ALL, *selects, **kwargs)
2734 def _scalar_type(self):
2735 return self.selects[0]._scalar_type()
2737 def self_group(self, against=None):
2738 return FromGrouping(self)
2740 def is_derived_from(self, fromclause):
2741 for s in self.selects:
2742 if s.is_derived_from(fromclause):
2743 return True
2744 return False
2746 def _populate_column_collection(self):
2747 for cols in zip(*[s.c._all_columns for s in self.selects]):
2749 # this is a slightly hacky thing - the union exports a
2750 # column that resembles just that of the *first* selectable.
2751 # to get at a "composite" column, particularly foreign keys,
2752 # you have to dig through the proxies collection which we
2753 # generate below. We may want to improve upon this, such as
2754 # perhaps _make_proxy can accept a list of other columns
2755 # that are "shared" - schema.column can then copy all the
2756 # ForeignKeys in. this would allow the union() to have all
2757 # those fks too.
2759 proxy = cols[0]._make_proxy(
2760 self,
2761 name=cols[0]._label if self.use_labels else None,
2762 key=cols[0]._key_label if self.use_labels else None,
2763 )
2765 # hand-construct the "_proxies" collection to include all
2766 # derived columns place a 'weight' annotation corresponding
2767 # to how low in the list of select()s the column occurs, so
2768 # that the corresponding_column() operation can resolve
2769 # conflicts
2770 proxy._proxies = [
2771 c._annotate({"weight": i + 1}) for (i, c) in enumerate(cols)
2772 ]
2774 def _refresh_for_new_column(self, column):
2775 for s in self.selects:
2776 s._refresh_for_new_column(column)
2778 if not self._cols_populated:
2779 return None
2781 raise NotImplementedError(
2782 "CompoundSelect constructs don't support "
2783 "addition of columns to underlying "
2784 "selectables"
2785 )
2787 def _copy_internals(self, clone=_clone, **kw):
2788 super(CompoundSelect, self)._copy_internals(clone, **kw)
2789 self._reset_exported()
2790 self.selects = [clone(s, **kw) for s in self.selects]
2791 if hasattr(self, "_col_map"):
2792 del self._col_map
2793 for attr in (
2794 "_order_by_clause",
2795 "_group_by_clause",
2796 "_for_update_arg",
2797 ):
2798 if getattr(self, attr) is not None:
2799 setattr(self, attr, clone(getattr(self, attr), **kw))
2801 def get_children(self, column_collections=True, **kwargs):
2802 return (
2803 (column_collections and list(self.c) or [])
2804 + [self._order_by_clause, self._group_by_clause]
2805 + list(self.selects)
2806 )
2808 def bind(self):
2809 if self._bind:
2810 return self._bind
2811 for s in self.selects:
2812 e = s.bind
2813 if e:
2814 return e
2815 else:
2816 return None
2818 def _set_bind(self, bind):
2819 self._bind = bind
2821 bind = property(bind, _set_bind)
2824class Select(HasPrefixes, HasSuffixes, GenerativeSelect):
2825 """Represents a ``SELECT`` statement."""
2827 __visit_name__ = "select"
2829 _prefixes = ()
2830 _suffixes = ()
2831 _hints = util.immutabledict()
2832 _statement_hints = ()
2833 _distinct = False
2834 _from_cloned = None
2835 _correlate = ()
2836 _correlate_except = None
2837 _memoized_property = SelectBase._memoized_property
2838 _is_select = True
2840 @util.deprecated_params(
2841 autocommit=(
2842 "0.6",
2843 "The :paramref:`_expression.select.autocommit` "
2844 "parameter is deprecated "
2845 "and will be removed in a future release. Please refer to "
2846 "the :paramref:`.Connection.execution_options.autocommit` "
2847 "parameter in conjunction with the the "
2848 ":meth:`.Executable.execution_options` method in order to "
2849 "affect the autocommit behavior for a statement.",
2850 ),
2851 for_update=(
2852 "0.9",
2853 "The :paramref:`_expression.select.for_update` "
2854 "parameter is deprecated and "
2855 "will be removed in a future release. Please refer to the "
2856 ":meth:`_expression.Select.with_for_update` to specify the "
2857 "structure of the ``FOR UPDATE`` clause.",
2858 ),
2859 )
2860 def __init__(
2861 self,
2862 columns=None,
2863 whereclause=None,
2864 from_obj=None,
2865 distinct=False,
2866 having=None,
2867 correlate=True,
2868 prefixes=None,
2869 suffixes=None,
2870 **kwargs
2871 ):
2872 """Construct a new :class:`_expression.Select`.
2874 Similar functionality is also available via the
2875 :meth:`_expression.FromClause.select` method on any
2876 :class:`_expression.FromClause`.
2878 All arguments which accept :class:`_expression.ClauseElement`
2879 arguments also
2880 accept string arguments, which will be converted as appropriate into
2881 either :func:`_expression.text` or
2882 :func:`_expression.literal_column` constructs.
2884 .. seealso::
2886 :ref:`coretutorial_selecting` - Core Tutorial description of
2887 :func:`_expression.select`.
2889 :param columns:
2890 A list of :class:`_expression.ColumnElement` or
2891 :class:`_expression.FromClause`
2892 objects which will form the columns clause of the resulting
2893 statement. For those objects that are instances of
2894 :class:`_expression.FromClause` (typically :class:`_schema.Table`
2895 or :class:`_expression.Alias`
2896 objects), the :attr:`_expression.FromClause.c`
2897 collection is extracted
2898 to form a collection of :class:`_expression.ColumnElement` objects.
2900 This parameter will also accept :class:`_expression.TextClause`
2901 constructs as
2902 given, as well as ORM-mapped classes.
2904 .. note::
2906 The :paramref:`_expression.select.columns`
2907 parameter is not available
2908 in the method form of :func:`_expression.select`, e.g.
2909 :meth:`_expression.FromClause.select`.
2911 .. seealso::
2913 :meth:`_expression.Select.column`
2915 :meth:`_expression.Select.with_only_columns`
2917 :param whereclause:
2918 A :class:`_expression.ClauseElement`
2919 expression which will be used to form the
2920 ``WHERE`` clause. It is typically preferable to add WHERE
2921 criterion to an existing :class:`_expression.Select`
2922 using method chaining
2923 with :meth:`_expression.Select.where`.
2925 .. seealso::
2927 :meth:`_expression.Select.where`
2929 :param from_obj:
2930 A list of :class:`_expression.ClauseElement`
2931 objects which will be added to the
2932 ``FROM`` clause of the resulting statement. This is equivalent
2933 to calling :meth:`_expression.Select.select_from`
2934 using method chaining on
2935 an existing :class:`_expression.Select` object.
2937 .. seealso::
2939 :meth:`_expression.Select.select_from`
2940 - full description of explicit
2941 FROM clause specification.
2943 :param autocommit: legacy autocommit parameter.
2945 :param bind=None:
2946 an :class:`_engine.Engine` or :class:`_engine.Connection` instance
2947 to which the
2948 resulting :class:`_expression.Select` object will be bound. The
2949 :class:`_expression.Select`
2950 object will otherwise automatically bind to
2951 whatever :class:`~.base.Connectable` instances can be located within
2952 its contained :class:`_expression.ClauseElement` members.
2954 :param correlate=True:
2955 indicates that this :class:`_expression.Select`
2956 object should have its
2957 contained :class:`_expression.FromClause`
2958 elements "correlated" to an enclosing
2959 :class:`_expression.Select` object.
2960 It is typically preferable to specify
2961 correlations on an existing :class:`_expression.Select`
2962 construct using
2963 :meth:`_expression.Select.correlate`.
2965 .. seealso::
2967 :meth:`_expression.Select.correlate`
2968 - full description of correlation.
2970 :param distinct=False:
2971 when ``True``, applies a ``DISTINCT`` qualifier to the columns
2972 clause of the resulting statement.
2974 The boolean argument may also be a column expression or list
2975 of column expressions - this is a special calling form which
2976 is understood by the PostgreSQL dialect to render the
2977 ``DISTINCT ON (<columns>)`` syntax.
2979 ``distinct`` is also available on an existing
2980 :class:`_expression.Select`
2981 object via the :meth:`_expression.Select.distinct` method.
2983 .. seealso::
2985 :meth:`_expression.Select.distinct`
2987 :param for_update=False:
2988 when ``True``, applies ``FOR UPDATE`` to the end of the
2989 resulting statement.
2991 ``for_update`` accepts various string values interpreted by
2992 specific backends, including:
2994 * ``"read"`` - on MySQL, translates to ``LOCK IN SHARE MODE``;
2995 on PostgreSQL, translates to ``FOR SHARE``.
2996 * ``"nowait"`` - on PostgreSQL and Oracle, translates to
2997 ``FOR UPDATE NOWAIT``.
2998 * ``"read_nowait"`` - on PostgreSQL, translates to
2999 ``FOR SHARE NOWAIT``.
3001 .. seealso::
3003 :meth:`_expression.Select.with_for_update` - improved API for
3004 specifying the ``FOR UPDATE`` clause.
3006 :param group_by:
3007 a list of :class:`_expression.ClauseElement`
3008 objects which will comprise the
3009 ``GROUP BY`` clause of the resulting select. This parameter
3010 is typically specified more naturally using the
3011 :meth:`_expression.Select.group_by` method on an existing
3012 :class:`_expression.Select`.
3014 .. seealso::
3016 :meth:`_expression.Select.group_by`
3018 :param having:
3019 a :class:`_expression.ClauseElement`
3020 that will comprise the ``HAVING`` clause
3021 of the resulting select when ``GROUP BY`` is used. This parameter
3022 is typically specified more naturally using the
3023 :meth:`_expression.Select.having` method on an existing
3024 :class:`_expression.Select`.
3026 .. seealso::
3028 :meth:`_expression.Select.having`
3030 :param limit=None:
3031 a numerical value which usually renders as a ``LIMIT``
3032 expression in the resulting select. Backends that don't
3033 support ``LIMIT`` will attempt to provide similar
3034 functionality. This parameter is typically specified more
3035 naturally using the :meth:`_expression.Select.limit`
3036 method on an existing
3037 :class:`_expression.Select`.
3039 .. seealso::
3041 :meth:`_expression.Select.limit`
3043 :param offset=None:
3044 a numeric value which usually renders as an ``OFFSET``
3045 expression in the resulting select. Backends that don't
3046 support ``OFFSET`` will attempt to provide similar
3047 functionality. This parameter is typically specified more naturally
3048 using the :meth:`_expression.Select.offset` method on an existing
3049 :class:`_expression.Select`.
3051 .. seealso::
3053 :meth:`_expression.Select.offset`
3055 :param order_by:
3056 a scalar or list of :class:`_expression.ClauseElement`
3057 objects which will
3058 comprise the ``ORDER BY`` clause of the resulting select.
3059 This parameter is typically specified more naturally using the
3060 :meth:`_expression.Select.order_by` method on an existing
3061 :class:`_expression.Select`.
3063 .. seealso::
3065 :meth:`_expression.Select.order_by`
3067 :param use_labels=False:
3068 when ``True``, the statement will be generated using labels
3069 for each column in the columns clause, which qualify each
3070 column with its parent table's (or aliases) name so that name
3071 conflicts between columns in different tables don't occur.
3072 The format of the label is ``<tablename>_<column>``. The "c"
3073 collection of the resulting :class:`_expression.Select`
3074 object will use these
3075 names as well for targeting column members.
3077 This parameter can also be specified on an existing
3078 :class:`_expression.Select` object using the
3079 :meth:`_expression.Select.apply_labels`
3080 method.
3082 .. seealso::
3084 :meth:`_expression.Select.apply_labels`
3086 """
3087 self._auto_correlate = correlate
3088 if distinct is not False:
3089 if distinct is True:
3090 self._distinct = True
3091 else:
3092 self._distinct = [
3093 _literal_as_label_reference(e)
3094 for e in util.to_list(distinct)
3095 ]
3097 if from_obj is not None:
3098 self._from_obj = util.OrderedSet(
3099 _interpret_as_from(f) for f in util.to_list(from_obj)
3100 )
3101 else:
3102 self._from_obj = util.OrderedSet()
3104 try:
3105 cols_present = bool(columns)
3106 except TypeError as err:
3107 util.raise_(
3108 exc.ArgumentError(
3109 "columns argument to select() must "
3110 "be a Python list or other iterable"
3111 ),
3112 replace_context=err,
3113 )
3115 if cols_present:
3116 self._raw_columns = []
3117 for c in columns:
3118 c = _interpret_as_column_or_from(c)
3119 if isinstance(c, ScalarSelect):
3120 c = c.self_group(against=operators.comma_op)
3121 self._raw_columns.append(c)
3122 else:
3123 self._raw_columns = []
3125 if whereclause is not None:
3126 self._whereclause = _literal_as_text(whereclause).self_group(
3127 against=operators._asbool
3128 )
3129 else:
3130 self._whereclause = None
3132 if having is not None:
3133 self._having = _literal_as_text(having).self_group(
3134 against=operators._asbool
3135 )
3136 else:
3137 self._having = None
3139 if prefixes:
3140 self._setup_prefixes(prefixes)
3142 if suffixes:
3143 self._setup_suffixes(suffixes)
3145 GenerativeSelect.__init__(self, **kwargs)
3147 @property
3148 def _froms(self):
3149 # would love to cache this,
3150 # but there's just enough edge cases, particularly now that
3151 # declarative encourages construction of SQL expressions
3152 # without tables present, to just regen this each time.
3153 froms = []
3154 seen = set()
3155 translate = self._from_cloned
3157 for item in itertools.chain(
3158 _from_objects(*self._raw_columns),
3159 _from_objects(self._whereclause)
3160 if self._whereclause is not None
3161 else (),
3162 self._from_obj,
3163 ):
3164 if item is self:
3165 raise exc.InvalidRequestError(
3166 "select() construct refers to itself as a FROM"
3167 )
3168 if translate and item in translate:
3169 item = translate[item]
3170 if not seen.intersection(item._cloned_set):
3171 froms.append(item)
3172 seen.update(item._cloned_set)
3174 return froms
3176 def _get_display_froms(
3177 self, explicit_correlate_froms=None, implicit_correlate_froms=None
3178 ):
3179 """Return the full list of 'from' clauses to be displayed.
3181 Takes into account a set of existing froms which may be
3182 rendered in the FROM clause of enclosing selects; this Select
3183 may want to leave those absent if it is automatically
3184 correlating.
3186 """
3187 froms = self._froms
3189 toremove = set(
3190 itertools.chain(*[_expand_cloned(f._hide_froms) for f in froms])
3191 )
3192 if toremove:
3193 # if we're maintaining clones of froms,
3194 # add the copies out to the toremove list. only include
3195 # clones that are lexical equivalents.
3196 if self._from_cloned:
3197 toremove.update(
3198 self._from_cloned[f]
3199 for f in toremove.intersection(self._from_cloned)
3200 if self._from_cloned[f]._is_lexical_equivalent(f)
3201 )
3202 # filter out to FROM clauses not in the list,
3203 # using a list to maintain ordering
3204 froms = [f for f in froms if f not in toremove]
3206 if self._correlate:
3207 to_correlate = self._correlate
3208 if to_correlate:
3209 froms = [
3210 f
3211 for f in froms
3212 if f
3213 not in _cloned_intersection(
3214 _cloned_intersection(
3215 froms, explicit_correlate_froms or ()
3216 ),
3217 to_correlate,
3218 )
3219 ]
3221 if self._correlate_except is not None:
3223 froms = [
3224 f
3225 for f in froms
3226 if f
3227 not in _cloned_difference(
3228 _cloned_intersection(
3229 froms, explicit_correlate_froms or ()
3230 ),
3231 self._correlate_except,
3232 )
3233 ]
3235 if (
3236 self._auto_correlate
3237 and implicit_correlate_froms
3238 and len(froms) > 1
3239 ):
3241 froms = [
3242 f
3243 for f in froms
3244 if f
3245 not in _cloned_intersection(froms, implicit_correlate_froms)
3246 ]
3248 if not len(froms):
3249 raise exc.InvalidRequestError(
3250 "Select statement '%s"
3251 "' returned no FROM clauses "
3252 "due to auto-correlation; "
3253 "specify correlate(<tables>) "
3254 "to control correlation "
3255 "manually." % self
3256 )
3258 return froms
3260 def _scalar_type(self):
3261 elem = self._raw_columns[0]
3262 cols = list(elem._select_iterable)
3263 return cols[0].type
3265 @property
3266 def froms(self):
3267 """Return the displayed list of FromClause elements."""
3269 return self._get_display_froms()
3271 def with_statement_hint(self, text, dialect_name="*"):
3272 """Add a statement hint to this :class:`_expression.Select`.
3274 This method is similar to :meth:`_expression.Select.with_hint`
3275 except that
3276 it does not require an individual table, and instead applies to the
3277 statement as a whole.
3279 Hints here are specific to the backend database and may include
3280 directives such as isolation levels, file directives, fetch directives,
3281 etc.
3283 .. versionadded:: 1.0.0
3285 .. seealso::
3287 :meth:`_expression.Select.with_hint`
3289 :meth:`.Select.prefix_with` - generic SELECT prefixing which also
3290 can suit some database-specific HINT syntaxes such as MySQL
3291 optimizer hints
3293 """
3294 return self.with_hint(None, text, dialect_name)
3296 @_generative
3297 def with_hint(self, selectable, text, dialect_name="*"):
3298 r"""Add an indexing or other executional context hint for the given
3299 selectable to this :class:`_expression.Select`.
3301 The text of the hint is rendered in the appropriate
3302 location for the database backend in use, relative
3303 to the given :class:`_schema.Table` or :class:`_expression.Alias`
3304 passed as the
3305 ``selectable`` argument. The dialect implementation
3306 typically uses Python string substitution syntax
3307 with the token ``%(name)s`` to render the name of
3308 the table or alias. E.g. when using Oracle, the
3309 following::
3311 select([mytable]).\
3312 with_hint(mytable, "index(%(name)s ix_mytable)")
3314 Would render SQL as::
3316 select /*+ index(mytable ix_mytable) */ ... from mytable
3318 The ``dialect_name`` option will limit the rendering of a particular
3319 hint to a particular backend. Such as, to add hints for both Oracle
3320 and Sybase simultaneously::
3322 select([mytable]).\
3323 with_hint(mytable, "index(%(name)s ix_mytable)", 'oracle').\
3324 with_hint(mytable, "WITH INDEX ix_mytable", 'sybase')
3326 .. seealso::
3328 :meth:`_expression.Select.with_statement_hint`
3330 """
3331 if selectable is None:
3332 self._statement_hints += ((dialect_name, text),)
3333 else:
3334 self._hints = self._hints.union({(selectable, dialect_name): text})
3336 @property
3337 def type(self):
3338 raise exc.InvalidRequestError(
3339 "Select objects don't have a type. "
3340 "Call as_scalar() on this Select "
3341 "object to return a 'scalar' version "
3342 "of this Select."
3343 )
3345 @_memoized_property.method
3346 def locate_all_froms(self):
3347 """Return a Set of all :class:`_expression.FromClause` elements
3348 referenced by this Select.
3350 This set is a superset of that returned by the ``froms`` property,
3351 which is specifically for those FromClause elements that would
3352 actually be rendered.
3354 """
3355 froms = self._froms
3356 return froms + list(_from_objects(*froms))
3358 @property
3359 def inner_columns(self):
3360 """An iterator of all :class:`_expression.ColumnElement`
3361 expressions which would
3362 be rendered into the columns clause of the resulting SELECT statement.
3364 """
3365 return _select_iterables(self._raw_columns)
3367 @_memoized_property
3368 def _label_resolve_dict(self):
3369 with_cols = dict(
3370 (c._resolve_label or c._label or c.key, c)
3371 for c in _select_iterables(self._raw_columns)
3372 if c._allow_label_resolve
3373 )
3374 only_froms = dict(
3375 (c.key, c)
3376 for c in _select_iterables(self.froms)
3377 if c._allow_label_resolve
3378 )
3379 only_cols = with_cols.copy()
3380 for key, value in only_froms.items():
3381 with_cols.setdefault(key, value)
3383 return with_cols, only_froms, only_cols
3385 def is_derived_from(self, fromclause):
3386 if self in fromclause._cloned_set:
3387 return True
3389 for f in self.locate_all_froms():
3390 if f.is_derived_from(fromclause):
3391 return True
3392 return False
3394 def _copy_internals(self, clone=_clone, **kw):
3395 super(Select, self)._copy_internals(clone, **kw)
3397 # Select() object has been cloned and probably adapted by the
3398 # given clone function. Apply the cloning function to internal
3399 # objects
3401 # 1. keep a dictionary of the froms we've cloned, and what
3402 # they've become. This is consulted later when we derive
3403 # additional froms from "whereclause" and the columns clause,
3404 # which may still reference the uncloned parent table.
3405 # as of 0.7.4 we also put the current version of _froms, which
3406 # gets cleared on each generation. previously we were "baking"
3407 # _froms into self._from_obj.
3408 self._from_cloned = from_cloned = dict(
3409 (f, clone(f, **kw)) for f in self._from_obj.union(self._froms)
3410 )
3412 # 3. update persistent _from_obj with the cloned versions.
3413 self._from_obj = util.OrderedSet(
3414 from_cloned[f] for f in self._from_obj
3415 )
3417 # the _correlate collection is done separately, what can happen
3418 # here is the same item is _correlate as in _from_obj but the
3419 # _correlate version has an annotation on it - (specifically
3420 # RelationshipProperty.Comparator._criterion_exists() does
3421 # this). Also keep _correlate liberally open with its previous
3422 # contents, as this set is used for matching, not rendering.
3423 self._correlate = set(clone(f, **kw) for f in self._correlate).union(
3424 self._correlate
3425 )
3427 # do something similar for _correlate_except - this is a more
3428 # unusual case but same idea applies
3429 if self._correlate_except:
3430 self._correlate_except = set(
3431 clone(f, **kw) for f in self._correlate_except
3432 ).union(self._correlate_except)
3434 # 4. clone other things. The difficulty here is that Column
3435 # objects are not actually cloned, and refer to their original
3436 # .table, resulting in the wrong "from" parent after a clone
3437 # operation. Hence _from_cloned and _from_obj supersede what is
3438 # present here.
3439 self._raw_columns = [clone(c, **kw) for c in self._raw_columns]
3440 for attr in (
3441 "_whereclause",
3442 "_having",
3443 "_order_by_clause",
3444 "_group_by_clause",
3445 "_for_update_arg",
3446 ):
3447 if getattr(self, attr) is not None:
3448 setattr(self, attr, clone(getattr(self, attr), **kw))
3450 # erase exported column list, _froms collection,
3451 # etc.
3452 self._reset_exported()
3454 def get_children(self, column_collections=True, **kwargs):
3455 """Return child elements as per the ClauseElement specification."""
3457 return (
3458 (column_collections and list(self.columns) or [])
3459 + self._raw_columns
3460 + list(self._froms)
3461 + [
3462 x
3463 for x in (
3464 self._whereclause,
3465 self._having,
3466 self._order_by_clause,
3467 self._group_by_clause,
3468 )
3469 if x is not None
3470 ]
3471 )
3473 @_generative
3474 def column(self, column):
3475 """Return a new :func:`_expression.select` construct with
3476 the given column expression added to its columns clause.
3478 E.g.::
3480 my_select = my_select.column(table.c.new_column)
3482 See the documentation for
3483 :meth:`_expression.Select.with_only_columns`
3484 for guidelines on adding /replacing the columns of a
3485 :class:`_expression.Select` object.
3487 """
3488 self.append_column(column)
3490 @util.dependencies("sqlalchemy.sql.util")
3491 def reduce_columns(self, sqlutil, only_synonyms=True):
3492 """Return a new :func:`_expression.select` construct with redundantly
3493 named, equivalently-valued columns removed from the columns clause.
3495 "Redundant" here means two columns where one refers to the
3496 other either based on foreign key, or via a simple equality
3497 comparison in the WHERE clause of the statement. The primary purpose
3498 of this method is to automatically construct a select statement
3499 with all uniquely-named columns, without the need to use
3500 table-qualified labels as :meth:`_expression.Select.apply_labels`
3501 does.
3503 When columns are omitted based on foreign key, the referred-to
3504 column is the one that's kept. When columns are omitted based on
3505 WHERE equivalence, the first column in the columns clause is the
3506 one that's kept.
3508 :param only_synonyms: when True, limit the removal of columns
3509 to those which have the same name as the equivalent. Otherwise,
3510 all columns that are equivalent to another are removed.
3512 """
3513 return self.with_only_columns(
3514 sqlutil.reduce_columns(
3515 self.inner_columns,
3516 only_synonyms=only_synonyms,
3517 *(self._whereclause,) + tuple(self._from_obj)
3518 )
3519 )
3521 @_generative
3522 def with_only_columns(self, columns):
3523 r"""Return a new :func:`_expression.select` construct with its columns
3524 clause replaced with the given columns.
3526 This method is exactly equivalent to as if the original
3527 :func:`_expression.select` had been called with the given columns
3528 clause. I.e. a statement::
3530 s = select([table1.c.a, table1.c.b])
3531 s = s.with_only_columns([table1.c.b])
3533 should be exactly equivalent to::
3535 s = select([table1.c.b])
3537 This means that FROM clauses which are only derived
3538 from the column list will be discarded if the new column
3539 list no longer contains that FROM::
3541 >>> table1 = table('t1', column('a'), column('b'))
3542 >>> table2 = table('t2', column('a'), column('b'))
3543 >>> s1 = select([table1.c.a, table2.c.b])
3544 >>> print(s1)
3545 SELECT t1.a, t2.b FROM t1, t2
3546 >>> s2 = s1.with_only_columns([table2.c.b])
3547 >>> print(s2)
3548 SELECT t2.b FROM t1
3550 The preferred way to maintain a specific FROM clause
3551 in the construct, assuming it won't be represented anywhere
3552 else (i.e. not in the WHERE clause, etc.) is to set it using
3553 :meth:`_expression.Select.select_from`::
3555 >>> s1 = select([table1.c.a, table2.c.b]).\
3556 ... select_from(table1.join(table2,
3557 ... table1.c.a==table2.c.a))
3558 >>> s2 = s1.with_only_columns([table2.c.b])
3559 >>> print(s2)
3560 SELECT t2.b FROM t1 JOIN t2 ON t1.a=t2.a
3562 Care should also be taken to use the correct set of column objects
3563 passed to :meth:`_expression.Select.with_only_columns`.
3564 Since the method is
3565 essentially equivalent to calling the :func:`_expression.select`
3566 construct in the first place with the given columns, the columns passed
3567 to :meth:`_expression.Select.with_only_columns`
3568 should usually be a subset of
3569 those which were passed to the :func:`_expression.select`
3570 construct, not those which are available from the ``.c`` collection of
3571 that :func:`_expression.select`. That is::
3573 s = select([table1.c.a, table1.c.b]).select_from(table1)
3574 s = s.with_only_columns([table1.c.b])
3576 and **not**::
3578 # usually incorrect
3579 s = s.with_only_columns([s.c.b])
3581 The latter would produce the SQL::
3583 SELECT b
3584 FROM (SELECT t1.a AS a, t1.b AS b
3585 FROM t1), t1
3587 Since the :func:`_expression.select` construct is essentially
3588 being asked to select both from ``table1`` as well as itself.
3590 """
3591 self._reset_exported()
3592 rc = []
3593 for c in columns:
3594 c = _interpret_as_column_or_from(c)
3595 if isinstance(c, ScalarSelect):
3596 c = c.self_group(against=operators.comma_op)
3597 rc.append(c)
3598 self._raw_columns = rc
3600 @_generative
3601 def where(self, whereclause):
3602 """Return a new :func:`_expression.select` construct with
3603 the given expression added to
3604 its WHERE clause, joined to the existing clause via AND, if any.
3606 """
3608 self.append_whereclause(whereclause)
3610 @_generative
3611 def having(self, having):
3612 """Return a new :func:`_expression.select` construct with
3613 the given expression added to
3614 its HAVING clause, joined to the existing clause via AND, if any.
3616 """
3617 self.append_having(having)
3619 @_generative
3620 def distinct(self, *expr):
3621 r"""Return a new :func:`_expression.select` construct which
3622 will apply DISTINCT to its columns clause.
3624 :param \*expr: optional column expressions. When present,
3625 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>>)``
3626 construct.
3628 """
3629 if expr:
3630 expr = [_literal_as_label_reference(e) for e in expr]
3631 if isinstance(self._distinct, list):
3632 self._distinct = self._distinct + expr
3633 else:
3634 self._distinct = expr
3635 else:
3636 self._distinct = True
3638 @_generative
3639 def select_from(self, fromclause):
3640 r"""Return a new :func:`_expression.select` construct with the
3641 given FROM expression(s)
3642 merged into its list of FROM objects.
3644 E.g.::
3646 table1 = table('t1', column('a'))
3647 table2 = table('t2', column('b'))
3648 s = select([table1.c.a]).\
3649 select_from(
3650 table1.join(table2, table1.c.a==table2.c.b)
3651 )
3653 The "from" list is a unique set on the identity of each element,
3654 so adding an already present :class:`_schema.Table`
3655 or other selectable
3656 will have no effect. Passing a :class:`_expression.Join` that refers
3657 to an already present :class:`_schema.Table`
3658 or other selectable will have
3659 the effect of concealing the presence of that selectable as
3660 an individual element in the rendered FROM list, instead
3661 rendering it into a JOIN clause.
3663 While the typical purpose of :meth:`_expression.Select.select_from`
3664 is to
3665 replace the default, derived FROM clause with a join, it can
3666 also be called with individual table elements, multiple times
3667 if desired, in the case that the FROM clause cannot be fully
3668 derived from the columns clause::
3670 select([func.count('*')]).select_from(table1)
3672 """
3673 self.append_from(fromclause)
3675 @_generative
3676 def correlate(self, *fromclauses):
3677 r"""Return a new :class:`_expression.Select`
3678 which will correlate the given FROM
3679 clauses to that of an enclosing :class:`_expression.Select`.
3681 Calling this method turns off the :class:`_expression.Select` object's
3682 default behavior of "auto-correlation". Normally, FROM elements
3683 which appear in a :class:`_expression.Select`
3684 that encloses this one via
3685 its :term:`WHERE clause`, ORDER BY, HAVING or
3686 :term:`columns clause` will be omitted from this
3687 :class:`_expression.Select`
3688 object's :term:`FROM clause`.
3689 Setting an explicit correlation collection using the
3690 :meth:`_expression.Select.correlate`
3691 method provides a fixed list of FROM objects
3692 that can potentially take place in this process.
3694 When :meth:`_expression.Select.correlate`
3695 is used to apply specific FROM clauses
3696 for correlation, the FROM elements become candidates for
3697 correlation regardless of how deeply nested this
3698 :class:`_expression.Select`
3699 object is, relative to an enclosing :class:`_expression.Select`
3700 which refers to
3701 the same FROM object. This is in contrast to the behavior of
3702 "auto-correlation" which only correlates to an immediate enclosing
3703 :class:`_expression.Select`.
3704 Multi-level correlation ensures that the link
3705 between enclosed and enclosing :class:`_expression.Select`
3706 is always via
3707 at least one WHERE/ORDER BY/HAVING/columns clause in order for
3708 correlation to take place.
3710 If ``None`` is passed, the :class:`_expression.Select`
3711 object will correlate
3712 none of its FROM entries, and all will render unconditionally
3713 in the local FROM clause.
3715 :param \*fromclauses: a list of one or more
3716 :class:`_expression.FromClause`
3717 constructs, or other compatible constructs (i.e. ORM-mapped
3718 classes) to become part of the correlate collection.
3720 .. seealso::
3722 :meth:`_expression.Select.correlate_except`
3724 :ref:`correlated_subqueries`
3726 """
3728 self._auto_correlate = False
3729 if fromclauses and fromclauses[0] is None:
3730 self._correlate = ()
3731 else:
3732 self._correlate = set(self._correlate).union(
3733 _interpret_as_from(f) for f in fromclauses
3734 )
3736 @_generative
3737 def correlate_except(self, *fromclauses):
3738 r"""Return a new :class:`_expression.Select`
3739 which will omit the given FROM
3740 clauses from the auto-correlation process.
3742 Calling :meth:`_expression.Select.correlate_except` turns off the
3743 :class:`_expression.Select` object's default behavior of
3744 "auto-correlation" for the given FROM elements. An element
3745 specified here will unconditionally appear in the FROM list, while
3746 all other FROM elements remain subject to normal auto-correlation
3747 behaviors.
3749 If ``None`` is passed, the :class:`_expression.Select`
3750 object will correlate
3751 all of its FROM entries.
3753 :param \*fromclauses: a list of one or more
3754 :class:`_expression.FromClause`
3755 constructs, or other compatible constructs (i.e. ORM-mapped
3756 classes) to become part of the correlate-exception collection.
3758 .. seealso::
3760 :meth:`_expression.Select.correlate`
3762 :ref:`correlated_subqueries`
3764 """
3766 self._auto_correlate = False
3767 if fromclauses and fromclauses[0] is None:
3768 self._correlate_except = ()
3769 else:
3770 self._correlate_except = set(self._correlate_except or ()).union(
3771 _interpret_as_from(f) for f in fromclauses
3772 )
3774 def append_correlation(self, fromclause):
3775 """Append the given correlation expression to this
3776 :func:`_expression.select` construct.
3778 This is an **in-place** mutation method; the
3779 :meth:`_expression.Select.correlate` method is preferred,
3780 as it provides standard :term:`method chaining`.
3782 """
3784 self._auto_correlate = False
3785 self._correlate = set(self._correlate).union(
3786 _interpret_as_from(f) for f in fromclause
3787 )
3789 def append_column(self, column):
3790 """Append the given column expression to the columns clause of this
3791 :func:`_expression.select` construct.
3793 E.g.::
3795 my_select.append_column(some_table.c.new_column)
3797 This is an **in-place** mutation method; the
3798 :meth:`_expression.Select.column` method is preferred,
3799 as it provides standard :term:`method chaining`.
3801 See the documentation for :meth:`_expression.Select.with_only_columns`
3802 for guidelines on adding /replacing the columns of a
3803 :class:`_expression.Select` object.
3805 """
3806 self._reset_exported()
3807 column = _interpret_as_column_or_from(column)
3809 if isinstance(column, ScalarSelect):
3810 column = column.self_group(against=operators.comma_op)
3812 self._raw_columns = self._raw_columns + [column]
3814 def append_prefix(self, clause):
3815 """Append the given columns clause prefix expression to this
3816 :func:`_expression.select` construct.
3818 This is an **in-place** mutation method; the
3819 :meth:`_expression.Select.prefix_with` method is preferred,
3820 as it provides standard :term:`method chaining`.
3822 """
3823 clause = _literal_as_text(clause)
3824 self._prefixes = self._prefixes + (clause,)
3826 def append_whereclause(self, whereclause):
3827 """Append the given expression to this :func:`_expression.select`
3828 construct's WHERE criterion.
3830 The expression will be joined to existing WHERE criterion via AND.
3832 This is an **in-place** mutation method; the
3833 :meth:`_expression.Select.where` method is preferred,
3834 as it provides standard :term:`method chaining`.
3836 """
3838 self._reset_exported()
3839 self._whereclause = and_(True_._ifnone(self._whereclause), whereclause)
3841 def append_having(self, having):
3842 """Append the given expression to this :func:`_expression.select`
3843 construct's HAVING criterion.
3845 The expression will be joined to existing HAVING criterion via AND.
3847 This is an **in-place** mutation method; the
3848 :meth:`_expression.Select.having` method is preferred,
3849 as it provides standard :term:`method chaining`.
3851 """
3852 self._reset_exported()
3853 self._having = and_(True_._ifnone(self._having), having)
3855 def append_from(self, fromclause):
3856 """Append the given FromClause expression to this
3857 :func:`_expression.select` construct's FROM clause.
3859 This is an **in-place** mutation method; the
3860 :meth:`_expression.Select.select_from` method is preferred,
3861 as it provides 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
3933 :class:`_expression.ClauseElement` 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
3949 the given selectable.
3951 """
3952 return CompoundSelect._create_union(self, other, **kwargs)
3954 def union_all(self, other, **kwargs):
3955 """Return a SQL ``UNION ALL`` of this select() construct against
3956 the given selectable.
3958 """
3959 return CompoundSelect._create_union_all(self, other, **kwargs)
3961 def except_(self, other, **kwargs):
3962 """Return a SQL ``EXCEPT`` of this select() construct against
3963 the given selectable.
3965 """
3966 return CompoundSelect._create_except(self, other, **kwargs)
3968 def except_all(self, other, **kwargs):
3969 """Return a SQL ``EXCEPT ALL`` of this select() construct against
3970 the given selectable.
3972 """
3973 return CompoundSelect._create_except_all(self, other, **kwargs)
3975 def intersect(self, other, **kwargs):
3976 """Return a SQL ``INTERSECT`` of this select() construct against
3977 the given selectable.
3979 """
3980 return CompoundSelect._create_intersect(self, other, **kwargs)
3982 def intersect_all(self, other, **kwargs):
3983 """Return a SQL ``INTERSECT ALL`` of this select() construct
3984 against the given selectable.
3986 """
3987 return CompoundSelect._create_intersect_all(self, other, **kwargs)
3989 def bind(self):
3990 if self._bind:
3991 return self._bind
3992 froms = self._froms
3993 if not froms:
3994 for c in self._raw_columns:
3995 e = c.bind
3996 if e:
3997 self._bind = e
3998 return e
3999 else:
4000 e = list(froms)[0].bind
4001 if e:
4002 self._bind = e
4003 return e
4005 return None
4007 def _set_bind(self, bind):
4008 self._bind = bind
4010 bind = property(bind, _set_bind)
4013class ScalarSelect(Generative, Grouping):
4014 _from_objects = []
4015 _is_from_container = True
4016 _is_implicitly_boolean = False
4018 def __init__(self, element):
4019 self.element = element
4020 self.type = element._scalar_type()
4022 @property
4023 def columns(self):
4024 raise exc.InvalidRequestError(
4025 "Scalar Select expression has no "
4026 "columns; use this object directly "
4027 "within a column-level expression."
4028 )
4030 c = columns
4032 @_generative
4033 def where(self, crit):
4034 """Apply a WHERE clause to the SELECT statement referred to
4035 by this :class:`_expression.ScalarSelect`.
4037 """
4038 self.element = self.element.where(crit)
4040 def self_group(self, **kwargs):
4041 return self
4044class Exists(UnaryExpression):
4045 """Represent an ``EXISTS`` clause."""
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_e = exists(s)
4060 # an exists is usually used in a where of another select
4061 # to produce a WHERE EXISTS (SELECT ... )
4062 select([table.c.col1]).where(s_e)
4064 # but can also be used in a select to produce a
4065 # SELECT EXISTS (SELECT ... ) query
4066 select([s_e])
4068 # construct a select() at once
4069 exists(['*'], **select_arguments).where(criterion)
4071 # columns argument is optional, generates "EXISTS (SELECT *)"
4072 # by default.
4073 exists().where(table.c.col2==5)
4075 """
4076 if args and isinstance(args[0], (SelectBase, ScalarSelect)):
4077 s = args[0]
4078 else:
4079 if not args:
4080 args = ([literal_column("*")],)
4081 s = Select(*args, **kwargs).as_scalar().self_group()
4083 UnaryExpression.__init__(
4084 self,
4085 s,
4086 operator=operators.exists,
4087 type_=type_api.BOOLEANTYPE,
4088 wraps_column_expression=True,
4089 )
4091 def select(self, whereclause=None, **params):
4092 return Select([self], whereclause, **params)
4094 def correlate(self, *fromclause):
4095 e = self._clone()
4096 e.element = self.element.correlate(*fromclause).self_group()
4097 return e
4099 def correlate_except(self, *fromclause):
4100 e = self._clone()
4101 e.element = self.element.correlate_except(*fromclause).self_group()
4102 return e
4104 def select_from(self, clause):
4105 """Return a new :class:`_expression.Exists` construct,
4106 applying the given
4107 expression to the :meth:`_expression.Select.select_from`
4108 method of the select
4109 statement contained.
4111 """
4112 e = self._clone()
4113 e.element = self.element.select_from(clause).self_group()
4114 return e
4116 def where(self, clause):
4117 """Return a new :func:`_expression.exists` construct with the
4118 given expression added to
4119 its WHERE clause, joined to the existing clause via AND, if any.
4121 """
4122 e = self._clone()
4123 e.element = self.element.where(clause).self_group()
4124 return e
4127class TextAsFrom(SelectBase):
4128 """Wrap a :class:`_expression.TextClause` construct within a
4129 :class:`_expression.SelectBase`
4130 interface.
4132 This allows the :class:`_expression.TextClause` object to gain a
4133 ``.c`` collection
4134 and other FROM-like capabilities such as
4135 :meth:`_expression.FromClause.alias`,
4136 :meth:`_expression.SelectBase.cte`, etc.
4138 The :class:`.TextAsFrom` construct is produced via the
4139 :meth:`_expression.TextClause.columns`
4140 method - see that method for details.
4142 .. versionadded:: 0.9.0
4144 .. seealso::
4146 :func:`_expression.text`
4148 :meth:`_expression.TextClause.columns`
4150 """
4152 __visit_name__ = "text_as_from"
4154 _textual = True
4156 def __init__(self, text, columns, positional=False):
4157 self.element = text
4158 self.column_args = columns
4159 self.positional = positional
4161 @property
4162 def _bind(self):
4163 return self.element._bind
4165 @_generative
4166 def bindparams(self, *binds, **bind_as_values):
4167 self.element = self.element.bindparams(*binds, **bind_as_values)
4169 def _populate_column_collection(self):
4170 for c in self.column_args:
4171 c._make_proxy(self)
4173 def _copy_internals(self, clone=_clone, **kw):
4174 self._reset_exported()
4175 self.element = clone(self.element, **kw)
4177 def _scalar_type(self):
4178 return self.column_args[0].type
4181class AnnotatedFromClause(Annotated):
4182 def __init__(self, element, values):
4183 # force FromClause to generate their internal
4184 # collections into __dict__
4185 element.c
4186 Annotated.__init__(self, element, values)