Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py: 46%
1553 statements
« prev ^ index » next coverage.py v7.0.1, created at 2022-12-25 06:11 +0000
1# sql/selectable.py
2# Copyright (C) 2005-2022 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: https://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
16from operator import attrgetter
18from . import coercions
19from . import operators
20from . import roles
21from . import traversals
22from . import type_api
23from . import visitors
24from .annotation import Annotated
25from .annotation import SupportsCloneAnnotations
26from .base import _clone
27from .base import _cloned_difference
28from .base import _cloned_intersection
29from .base import _entity_namespace_key
30from .base import _expand_cloned
31from .base import _from_objects
32from .base import _generative
33from .base import _select_iterables
34from .base import CacheableOptions
35from .base import ColumnCollection
36from .base import ColumnSet
37from .base import CompileState
38from .base import DedupeColumnCollection
39from .base import Executable
40from .base import Generative
41from .base import HasCompileState
42from .base import HasMemoized
43from .base import Immutable
44from .base import prefix_anon_map
45from .coercions import _document_text_coercion
46from .elements import _anonymous_label
47from .elements import and_
48from .elements import BindParameter
49from .elements import BooleanClauseList
50from .elements import ClauseElement
51from .elements import ClauseList
52from .elements import ColumnClause
53from .elements import GroupedElement
54from .elements import Grouping
55from .elements import literal_column
56from .elements import TableValuedColumn
57from .elements import UnaryExpression
58from .visitors import InternalTraversal
59from .. import exc
60from .. import util
61from ..inspection import inspect
64class _OffsetLimitParam(BindParameter):
65 inherit_cache = True
67 @property
68 def _limit_offset_value(self):
69 return self.effective_value
72@util.deprecated(
73 "1.4",
74 "The standalone :func:`.subquery` function is deprecated "
75 "and will be removed in a future release. Use select().subquery().",
76)
77def subquery(alias, *args, **kwargs):
78 r"""Return an :class:`.Subquery` object derived
79 from a :class:`_expression.Select`.
81 :param alias: the alias name for the subquery
83 :param \*args, \**kwargs: all other arguments are passed through to the
84 :func:`_expression.select` function.
86 """
87 return Select.create_legacy_select(*args, **kwargs).subquery(alias)
90class ReturnsRows(roles.ReturnsRowsRole, ClauseElement):
91 """The base-most class for Core constructs that have some concept of
92 columns that can represent rows.
94 While the SELECT statement and TABLE are the primary things we think
95 of in this category, DML like INSERT, UPDATE and DELETE can also specify
96 RETURNING which means they can be used in CTEs and other forms, and
97 PostgreSQL has functions that return rows also.
99 .. versionadded:: 1.4
101 """
103 _is_returns_rows = True
105 # sub-elements of returns_rows
106 _is_from_clause = False
107 _is_select_statement = False
108 _is_lateral = False
110 @property
111 def selectable(self):
112 return self
114 @property
115 def _all_selected_columns(self):
116 """A sequence of column expression objects that represents the
117 "selected" columns of this :class:`_expression.ReturnsRows`.
119 This is typically equivalent to .exported_columns except it is
120 delivered in the form of a straight sequence and not keyed
121 :class:`_expression.ColumnCollection`.
123 """
124 raise NotImplementedError()
126 @property
127 def exported_columns(self):
128 """A :class:`_expression.ColumnCollection`
129 that represents the "exported"
130 columns of this :class:`_expression.ReturnsRows`.
132 The "exported" columns represent the collection of
133 :class:`_expression.ColumnElement`
134 expressions that are rendered by this SQL
135 construct. There are primary varieties which are the
136 "FROM clause columns" of a FROM clause, such as a table, join,
137 or subquery, the "SELECTed columns", which are the columns in
138 the "columns clause" of a SELECT statement, and the RETURNING
139 columns in a DML statement..
141 .. versionadded:: 1.4
143 .. seealso::
145 :attr:`_expression.FromClause.exported_columns`
147 :attr:`_expression.SelectBase.exported_columns`
148 """
150 raise NotImplementedError()
153class Selectable(ReturnsRows):
154 """Mark a class as being selectable."""
156 __visit_name__ = "selectable"
158 is_selectable = True
160 def _refresh_for_new_column(self, column):
161 raise NotImplementedError()
163 def lateral(self, name=None):
164 """Return a LATERAL alias of this :class:`_expression.Selectable`.
166 The return value is the :class:`_expression.Lateral` construct also
167 provided by the top-level :func:`_expression.lateral` function.
169 .. versionadded:: 1.1
171 .. seealso::
173 :ref:`tutorial_lateral_correlation` - overview of usage.
175 """
176 return Lateral._construct(self, name)
178 @util.deprecated(
179 "1.4",
180 message="The :meth:`.Selectable.replace_selectable` method is "
181 "deprecated, and will be removed in a future release. Similar "
182 "functionality is available via the sqlalchemy.sql.visitors module.",
183 )
184 @util.preload_module("sqlalchemy.sql.util")
185 def replace_selectable(self, old, alias):
186 """Replace all occurrences of :class:`_expression.FromClause`
187 'old' with the given :class:`_expression.Alias`
188 object, returning a copy of this :class:`_expression.FromClause`.
190 """
191 return util.preloaded.sql_util.ClauseAdapter(alias).traverse(self)
193 def corresponding_column(self, column, require_embedded=False):
194 """Given a :class:`_expression.ColumnElement`, return the exported
195 :class:`_expression.ColumnElement` object from the
196 :attr:`_expression.Selectable.exported_columns`
197 collection of this :class:`_expression.Selectable`
198 which corresponds to that
199 original :class:`_expression.ColumnElement` via a common ancestor
200 column.
202 :param column: the target :class:`_expression.ColumnElement`
203 to be matched.
205 :param require_embedded: only return corresponding columns for
206 the given :class:`_expression.ColumnElement`, if the given
207 :class:`_expression.ColumnElement`
208 is actually present within a sub-element
209 of this :class:`_expression.Selectable`.
210 Normally the column will match if
211 it merely shares a common ancestor with one of the exported
212 columns of this :class:`_expression.Selectable`.
214 .. seealso::
216 :attr:`_expression.Selectable.exported_columns` - the
217 :class:`_expression.ColumnCollection`
218 that is used for the operation.
220 :meth:`_expression.ColumnCollection.corresponding_column`
221 - implementation
222 method.
224 """
226 return self.exported_columns.corresponding_column(
227 column, require_embedded
228 )
231class HasPrefixes(object):
232 _prefixes = ()
234 _has_prefixes_traverse_internals = [
235 ("_prefixes", InternalTraversal.dp_prefix_sequence)
236 ]
238 @_generative
239 @_document_text_coercion(
240 "expr",
241 ":meth:`_expression.HasPrefixes.prefix_with`",
242 ":paramref:`.HasPrefixes.prefix_with.*expr`",
243 )
244 def prefix_with(self, *expr, **kw):
245 r"""Add one or more expressions following the statement keyword, i.e.
246 SELECT, INSERT, UPDATE, or DELETE. Generative.
248 This is used to support backend-specific prefix keywords such as those
249 provided by MySQL.
251 E.g.::
253 stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")
255 # MySQL 5.7 optimizer hints
256 stmt = select(table).prefix_with(
257 "/*+ BKA(t1) */", dialect="mysql")
259 Multiple prefixes can be specified by multiple calls
260 to :meth:`_expression.HasPrefixes.prefix_with`.
262 :param \*expr: textual or :class:`_expression.ClauseElement`
263 construct which
264 will be rendered following the INSERT, UPDATE, or DELETE
265 keyword.
266 :param \**kw: A single keyword 'dialect' is accepted. This is an
267 optional string dialect name which will
268 limit rendering of this prefix to only that dialect.
270 """
271 dialect = kw.pop("dialect", None)
272 if kw:
273 raise exc.ArgumentError(
274 "Unsupported argument(s): %s" % ",".join(kw)
275 )
276 self._setup_prefixes(expr, dialect)
278 def _setup_prefixes(self, prefixes, dialect=None):
279 self._prefixes = self._prefixes + tuple(
280 [
281 (coercions.expect(roles.StatementOptionRole, p), dialect)
282 for p in prefixes
283 ]
284 )
287class HasSuffixes(object):
288 _suffixes = ()
290 _has_suffixes_traverse_internals = [
291 ("_suffixes", InternalTraversal.dp_prefix_sequence)
292 ]
294 @_generative
295 @_document_text_coercion(
296 "expr",
297 ":meth:`_expression.HasSuffixes.suffix_with`",
298 ":paramref:`.HasSuffixes.suffix_with.*expr`",
299 )
300 def suffix_with(self, *expr, **kw):
301 r"""Add one or more expressions following the statement as a whole.
303 This is used to support backend-specific suffix keywords on
304 certain constructs.
306 E.g.::
308 stmt = select(col1, col2).cte().suffix_with(
309 "cycle empno set y_cycle to 1 default 0", dialect="oracle")
311 Multiple suffixes can be specified by multiple calls
312 to :meth:`_expression.HasSuffixes.suffix_with`.
314 :param \*expr: textual or :class:`_expression.ClauseElement`
315 construct which
316 will be rendered following the target clause.
317 :param \**kw: A single keyword 'dialect' is accepted. This is an
318 optional string dialect name which will
319 limit rendering of this suffix to only that dialect.
321 """
322 dialect = kw.pop("dialect", None)
323 if kw:
324 raise exc.ArgumentError(
325 "Unsupported argument(s): %s" % ",".join(kw)
326 )
327 self._setup_suffixes(expr, dialect)
329 def _setup_suffixes(self, suffixes, dialect=None):
330 self._suffixes = self._suffixes + tuple(
331 [
332 (coercions.expect(roles.StatementOptionRole, p), dialect)
333 for p in suffixes
334 ]
335 )
338class HasHints(object):
339 _hints = util.immutabledict()
340 _statement_hints = ()
342 _has_hints_traverse_internals = [
343 ("_statement_hints", InternalTraversal.dp_statement_hint_list),
344 ("_hints", InternalTraversal.dp_table_hint_list),
345 ]
347 def with_statement_hint(self, text, dialect_name="*"):
348 """Add a statement hint to this :class:`_expression.Select` or
349 other selectable object.
351 This method is similar to :meth:`_expression.Select.with_hint`
352 except that
353 it does not require an individual table, and instead applies to the
354 statement as a whole.
356 Hints here are specific to the backend database and may include
357 directives such as isolation levels, file directives, fetch directives,
358 etc.
360 .. versionadded:: 1.0.0
362 .. seealso::
364 :meth:`_expression.Select.with_hint`
366 :meth:`_expression.Select.prefix_with` - generic SELECT prefixing
367 which also can suit some database-specific HINT syntaxes such as
368 MySQL optimizer hints
370 """
371 return self.with_hint(None, text, dialect_name)
373 @_generative
374 def with_hint(self, selectable, text, dialect_name="*"):
375 r"""Add an indexing or other executional context hint for the given
376 selectable to this :class:`_expression.Select` or other selectable
377 object.
379 The text of the hint is rendered in the appropriate
380 location for the database backend in use, relative
381 to the given :class:`_schema.Table` or :class:`_expression.Alias`
382 passed as the
383 ``selectable`` argument. The dialect implementation
384 typically uses Python string substitution syntax
385 with the token ``%(name)s`` to render the name of
386 the table or alias. E.g. when using Oracle, the
387 following::
389 select(mytable).\
390 with_hint(mytable, "index(%(name)s ix_mytable)")
392 Would render SQL as::
394 select /*+ index(mytable ix_mytable) */ ... from mytable
396 The ``dialect_name`` option will limit the rendering of a particular
397 hint to a particular backend. Such as, to add hints for both Oracle
398 and Sybase simultaneously::
400 select(mytable).\
401 with_hint(mytable, "index(%(name)s ix_mytable)", 'oracle').\
402 with_hint(mytable, "WITH INDEX ix_mytable", 'sybase')
404 .. seealso::
406 :meth:`_expression.Select.with_statement_hint`
408 """
409 if selectable is None:
410 self._statement_hints += ((dialect_name, text),)
411 else:
412 self._hints = self._hints.union(
413 {
414 (
415 coercions.expect(roles.FromClauseRole, selectable),
416 dialect_name,
417 ): text
418 }
419 )
422class FromClause(roles.AnonymizedFromClauseRole, Selectable):
423 """Represent an element that can be used within the ``FROM``
424 clause of a ``SELECT`` statement.
426 The most common forms of :class:`_expression.FromClause` are the
427 :class:`_schema.Table` and the :func:`_expression.select` constructs. Key
428 features common to all :class:`_expression.FromClause` objects include:
430 * a :attr:`.c` collection, which provides per-name access to a collection
431 of :class:`_expression.ColumnElement` objects.
432 * a :attr:`.primary_key` attribute, which is a collection of all those
433 :class:`_expression.ColumnElement`
434 objects that indicate the ``primary_key`` flag.
435 * Methods to generate various derivations of a "from" clause, including
436 :meth:`_expression.FromClause.alias`,
437 :meth:`_expression.FromClause.join`,
438 :meth:`_expression.FromClause.select`.
441 """
443 __visit_name__ = "fromclause"
444 named_with_column = False
445 _hide_froms = []
447 schema = None
448 """Define the 'schema' attribute for this :class:`_expression.FromClause`.
450 This is typically ``None`` for most objects except that of
451 :class:`_schema.Table`, where it is taken as the value of the
452 :paramref:`_schema.Table.schema` argument.
454 """
456 is_selectable = True
457 _is_from_clause = True
458 _is_join = False
460 _use_schema_map = False
462 @util.deprecated_params(
463 whereclause=(
464 "2.0",
465 "The :paramref:`_sql.FromClause.select().whereclause` parameter "
466 "is deprecated and will be removed in version 2.0. "
467 "Please make use of "
468 "the :meth:`.Select.where` "
469 "method to add WHERE criteria to the SELECT statement.",
470 ),
471 kwargs=(
472 "2.0",
473 "The :meth:`_sql.FromClause.select` method will no longer accept "
474 "keyword arguments in version 2.0. Please use generative methods "
475 "from the "
476 ":class:`_sql.Select` construct in order to apply additional "
477 "modifications.",
478 ),
479 )
480 def select(self, whereclause=None, **kwargs):
481 r"""Return a SELECT of this :class:`_expression.FromClause`.
484 e.g.::
486 stmt = some_table.select().where(some_table.c.id == 5)
488 :param whereclause: a WHERE clause, equivalent to calling the
489 :meth:`_sql.Select.where` method.
491 :param \**kwargs: additional keyword arguments are passed to the
492 legacy constructor for :class:`_sql.Select` described at
493 :meth:`_sql.Select.create_legacy_select`.
495 .. seealso::
497 :func:`_expression.select` - general purpose
498 method which allows for arbitrary column lists.
500 """
501 if whereclause is not None:
502 kwargs["whereclause"] = whereclause
503 return Select._create_select_from_fromclause(self, [self], **kwargs)
505 def join(self, right, onclause=None, isouter=False, full=False):
506 """Return a :class:`_expression.Join` from this
507 :class:`_expression.FromClause`
508 to another :class:`FromClause`.
510 E.g.::
512 from sqlalchemy import join
514 j = user_table.join(address_table,
515 user_table.c.id == address_table.c.user_id)
516 stmt = select(user_table).select_from(j)
518 would emit SQL along the lines of::
520 SELECT user.id, user.name FROM user
521 JOIN address ON user.id = address.user_id
523 :param right: the right side of the join; this is any
524 :class:`_expression.FromClause` object such as a
525 :class:`_schema.Table` object, and
526 may also be a selectable-compatible object such as an ORM-mapped
527 class.
529 :param onclause: a SQL expression representing the ON clause of the
530 join. If left at ``None``, :meth:`_expression.FromClause.join`
531 will attempt to
532 join the two tables based on a foreign key relationship.
534 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
536 :param full: if True, render a FULL OUTER JOIN, instead of LEFT OUTER
537 JOIN. Implies :paramref:`.FromClause.join.isouter`.
539 .. versionadded:: 1.1
541 .. seealso::
543 :func:`_expression.join` - standalone function
545 :class:`_expression.Join` - the type of object produced
547 """
549 return Join(self, right, onclause, isouter, full)
551 def outerjoin(self, right, onclause=None, full=False):
552 """Return a :class:`_expression.Join` from this
553 :class:`_expression.FromClause`
554 to another :class:`FromClause`, with the "isouter" flag set to
555 True.
557 E.g.::
559 from sqlalchemy import outerjoin
561 j = user_table.outerjoin(address_table,
562 user_table.c.id == address_table.c.user_id)
564 The above is equivalent to::
566 j = user_table.join(
567 address_table,
568 user_table.c.id == address_table.c.user_id,
569 isouter=True)
571 :param right: the right side of the join; this is any
572 :class:`_expression.FromClause` object such as a
573 :class:`_schema.Table` object, and
574 may also be a selectable-compatible object such as an ORM-mapped
575 class.
577 :param onclause: a SQL expression representing the ON clause of the
578 join. If left at ``None``, :meth:`_expression.FromClause.join`
579 will attempt to
580 join the two tables based on a foreign key relationship.
582 :param full: if True, render a FULL OUTER JOIN, instead of
583 LEFT OUTER JOIN.
585 .. versionadded:: 1.1
587 .. seealso::
589 :meth:`_expression.FromClause.join`
591 :class:`_expression.Join`
593 """
595 return Join(self, right, onclause, True, full)
597 def alias(self, name=None, flat=False):
598 """Return an alias of this :class:`_expression.FromClause`.
600 E.g.::
602 a2 = some_table.alias('a2')
604 The above code creates an :class:`_expression.Alias`
605 object which can be used
606 as a FROM clause in any SELECT statement.
608 .. seealso::
610 :ref:`tutorial_using_aliases`
612 :func:`_expression.alias`
614 """
616 return Alias._construct(self, name)
618 @util.preload_module("sqlalchemy.sql.sqltypes")
619 def table_valued(self):
620 """Return a :class:`_sql.TableValuedColumn` object for this
621 :class:`_expression.FromClause`.
623 A :class:`_sql.TableValuedColumn` is a :class:`_sql.ColumnElement` that
624 represents a complete row in a table. Support for this construct is
625 backend dependent, and is supported in various forms by backends
626 such as PostgreSQL, Oracle and SQL Server.
628 E.g.::
630 >>> from sqlalchemy import select, column, func, table
631 >>> a = table("a", column("id"), column("x"), column("y"))
632 >>> stmt = select(func.row_to_json(a.table_valued()))
633 >>> print(stmt)
634 SELECT row_to_json(a) AS row_to_json_1
635 FROM a
637 .. versionadded:: 1.4.0b2
639 .. seealso::
641 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
643 """
644 return TableValuedColumn(self, type_api.TABLEVALUE)
646 def tablesample(self, sampling, name=None, seed=None):
647 """Return a TABLESAMPLE alias of this :class:`_expression.FromClause`.
649 The return value is the :class:`_expression.TableSample`
650 construct also
651 provided by the top-level :func:`_expression.tablesample` function.
653 .. versionadded:: 1.1
655 .. seealso::
657 :func:`_expression.tablesample` - usage guidelines and parameters
659 """
660 return TableSample._construct(self, sampling, name, seed)
662 def is_derived_from(self, fromclause):
663 """Return ``True`` if this :class:`_expression.FromClause` is
664 'derived' from the given ``FromClause``.
666 An example would be an Alias of a Table is derived from that Table.
668 """
669 # this is essentially an "identity" check in the base class.
670 # Other constructs override this to traverse through
671 # contained elements.
672 return fromclause in self._cloned_set
674 def _is_lexical_equivalent(self, other):
675 """Return ``True`` if this :class:`_expression.FromClause` and
676 the other represent the same lexical identity.
678 This tests if either one is a copy of the other, or
679 if they are the same via annotation identity.
681 """
682 return self._cloned_set.intersection(other._cloned_set)
684 @property
685 def description(self):
686 """A brief description of this :class:`_expression.FromClause`.
688 Used primarily for error message formatting.
690 """
691 return getattr(self, "name", self.__class__.__name__ + " object")
693 def _generate_fromclause_column_proxies(self, fromclause):
694 fromclause._columns._populate_separate_keys(
695 col._make_proxy(fromclause) for col in self.c
696 )
698 @property
699 def exported_columns(self):
700 """A :class:`_expression.ColumnCollection`
701 that represents the "exported"
702 columns of this :class:`_expression.Selectable`.
704 The "exported" columns for a :class:`_expression.FromClause`
705 object are synonymous
706 with the :attr:`_expression.FromClause.columns` collection.
708 .. versionadded:: 1.4
710 .. seealso::
712 :attr:`_expression.Selectable.exported_columns`
714 :attr:`_expression.SelectBase.exported_columns`
717 """
718 return self.columns
720 @util.memoized_property
721 def columns(self):
722 """A named-based collection of :class:`_expression.ColumnElement`
723 objects maintained by this :class:`_expression.FromClause`.
725 The :attr:`.columns`, or :attr:`.c` collection, is the gateway
726 to the construction of SQL expressions using table-bound or
727 other selectable-bound columns::
729 select(mytable).where(mytable.c.somecolumn == 5)
731 :return: a :class:`.ColumnCollection` object.
733 """
735 if "_columns" not in self.__dict__:
736 self._init_collections()
737 self._populate_column_collection()
738 return self._columns.as_immutable()
740 @property
741 def entity_namespace(self):
742 """Return a namespace used for name-based access in SQL expressions.
744 This is the namespace that is used to resolve "filter_by()" type
745 expressions, such as::
747 stmt.filter_by(address='some address')
749 It defaults to the ``.c`` collection, however internally it can
750 be overridden using the "entity_namespace" annotation to deliver
751 alternative results.
753 """
754 return self.columns
756 @util.memoized_property
757 def primary_key(self):
758 """Return the iterable collection of :class:`_schema.Column` objects
759 which comprise the primary key of this :class:`_selectable.FromClause`.
761 For a :class:`_schema.Table` object, this collection is represented
762 by the :class:`_schema.PrimaryKeyConstraint` which itself is an
763 iterable collection of :class:`_schema.Column` objects.
765 """
766 self._init_collections()
767 self._populate_column_collection()
768 return self.primary_key
770 @util.memoized_property
771 def foreign_keys(self):
772 """Return the collection of :class:`_schema.ForeignKey` marker objects
773 which this FromClause references.
775 Each :class:`_schema.ForeignKey` is a member of a
776 :class:`_schema.Table`-wide
777 :class:`_schema.ForeignKeyConstraint`.
779 .. seealso::
781 :attr:`_schema.Table.foreign_key_constraints`
783 """
784 self._init_collections()
785 self._populate_column_collection()
786 return self.foreign_keys
788 def _reset_column_collection(self):
789 """Reset the attributes linked to the ``FromClause.c`` attribute.
791 This collection is separate from all the other memoized things
792 as it has shown to be sensitive to being cleared out in situations
793 where enclosing code, typically in a replacement traversal scenario,
794 has already established strong relationships
795 with the exported columns.
797 The collection is cleared for the case where a table is having a
798 column added to it as well as within a Join during copy internals.
800 """
802 for key in ["_columns", "columns", "primary_key", "foreign_keys"]:
803 self.__dict__.pop(key, None)
805 c = property(
806 attrgetter("columns"),
807 doc="""
808 A named-based collection of :class:`_expression.ColumnElement`
809 objects maintained by this :class:`_expression.FromClause`.
811 The :attr:`_sql.FromClause.c` attribute is an alias for the
812 :attr:`_sql.FromClause.columns` attribute.
814 :return: a :class:`.ColumnCollection`
816 """,
817 )
818 _select_iterable = property(attrgetter("columns"))
820 def _init_collections(self):
821 assert "_columns" not in self.__dict__
822 assert "primary_key" not in self.__dict__
823 assert "foreign_keys" not in self.__dict__
825 self._columns = ColumnCollection()
826 self.primary_key = ColumnSet()
827 self.foreign_keys = set()
829 @property
830 def _cols_populated(self):
831 return "_columns" in self.__dict__
833 def _populate_column_collection(self):
834 """Called on subclasses to establish the .c collection.
836 Each implementation has a different way of establishing
837 this collection.
839 """
841 def _refresh_for_new_column(self, column):
842 """Given a column added to the .c collection of an underlying
843 selectable, produce the local version of that column, assuming this
844 selectable ultimately should proxy this column.
846 this is used to "ping" a derived selectable to add a new column
847 to its .c. collection when a Column has been added to one of the
848 Table objects it ultimately derives from.
850 If the given selectable hasn't populated its .c. collection yet,
851 it should at least pass on the message to the contained selectables,
852 but it will return None.
854 This method is currently used by Declarative to allow Table
855 columns to be added to a partially constructed inheritance
856 mapping that may have already produced joins. The method
857 isn't public right now, as the full span of implications
858 and/or caveats aren't yet clear.
860 It's also possible that this functionality could be invoked by
861 default via an event, which would require that
862 selectables maintain a weak referencing collection of all
863 derivations.
865 """
866 self._reset_column_collection()
868 def _anonymous_fromclause(self, name=None, flat=False):
869 return self.alias(name=name)
872LABEL_STYLE_NONE = util.symbol(
873 "LABEL_STYLE_NONE",
874 """Label style indicating no automatic labeling should be applied to the
875 columns clause of a SELECT statement.
877 Below, the columns named ``columna`` are both rendered as is, meaning that
878 the name ``columna`` can only refer to the first occurrence of this name
879 within a result set, as well as if the statement were used as a subquery::
881 >>> from sqlalchemy import table, column, select, true, LABEL_STYLE_NONE
882 >>> table1 = table("table1", column("columna"), column("columnb"))
883 >>> table2 = table("table2", column("columna"), column("columnc"))
884 >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_NONE))
885 SELECT table1.columna, table1.columnb, table2.columna, table2.columnc
886 FROM table1 JOIN table2 ON true
888 Used with the :meth:`_sql.Select.set_label_style` method.
890 .. versionadded:: 1.4
892""", # noqa: E501
893)
895LABEL_STYLE_TABLENAME_PLUS_COL = util.symbol(
896 "LABEL_STYLE_TABLENAME_PLUS_COL",
897 """Label style indicating all columns should be labeled as
898 ``<tablename>_<columnname>`` when generating the columns clause of a SELECT
899 statement, to disambiguate same-named columns referenced from different
900 tables, aliases, or subqueries.
902 Below, all column names are given a label so that the two same-named
903 columns ``columna`` are disambiguated as ``table1_columna`` and
904 ``table2_columna``::
906 >>> from sqlalchemy import table, column, select, true, LABEL_STYLE_TABLENAME_PLUS_COL
907 >>> table1 = table("table1", column("columna"), column("columnb"))
908 >>> table2 = table("table2", column("columna"), column("columnc"))
909 >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL))
910 SELECT table1.columna AS table1_columna, table1.columnb AS table1_columnb, table2.columna AS table2_columna, table2.columnc AS table2_columnc
911 FROM table1 JOIN table2 ON true
913 Used with the :meth:`_sql.GenerativeSelect.set_label_style` method.
914 Equivalent to the legacy method ``Select.apply_labels()``;
915 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` is SQLAlchemy's legacy
916 auto-labeling style. :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` provides a
917 less intrusive approach to disambiguation of same-named column expressions.
920 .. versionadded:: 1.4
922""", # noqa: E501
923)
926LABEL_STYLE_DISAMBIGUATE_ONLY = util.symbol(
927 "LABEL_STYLE_DISAMBIGUATE_ONLY",
928 """Label style indicating that columns with a name that conflicts with
929 an existing name should be labeled with a semi-anonymizing label
930 when generating the columns clause of a SELECT statement.
932 Below, most column names are left unaffected, except for the second
933 occurrence of the name ``columna``, which is labeled using the
934 label ``columna_1`` to disambiguate it from that of ``tablea.columna``::
936 >>> from sqlalchemy import table, column, select, true, LABEL_STYLE_DISAMBIGUATE_ONLY
937 >>> table1 = table("table1", column("columna"), column("columnb"))
938 >>> table2 = table("table2", column("columna"), column("columnc"))
939 >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY))
940 SELECT table1.columna, table1.columnb, table2.columna AS columna_1, table2.columnc
941 FROM table1 JOIN table2 ON true
943 Used with the :meth:`_sql.GenerativeSelect.set_label_style` method,
944 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` is the default labeling style
945 for all SELECT statements outside of :term:`1.x style` ORM queries.
947 .. versionadded:: 1.4
949""", # noqa: E501,
950)
953LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY
954"""The default label style, refers to
955:data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.
957.. versionadded:: 1.4
959"""
962class Join(roles.DMLTableRole, FromClause):
963 """Represent a ``JOIN`` construct between two
964 :class:`_expression.FromClause`
965 elements.
967 The public constructor function for :class:`_expression.Join`
968 is the module-level
969 :func:`_expression.join()` function, as well as the
970 :meth:`_expression.FromClause.join` method
971 of any :class:`_expression.FromClause` (e.g. such as
972 :class:`_schema.Table`).
974 .. seealso::
976 :func:`_expression.join`
978 :meth:`_expression.FromClause.join`
980 """
982 __visit_name__ = "join"
984 _traverse_internals = [
985 ("left", InternalTraversal.dp_clauseelement),
986 ("right", InternalTraversal.dp_clauseelement),
987 ("onclause", InternalTraversal.dp_clauseelement),
988 ("isouter", InternalTraversal.dp_boolean),
989 ("full", InternalTraversal.dp_boolean),
990 ]
992 _is_join = True
994 def __init__(self, left, right, onclause=None, isouter=False, full=False):
995 """Construct a new :class:`_expression.Join`.
997 The usual entrypoint here is the :func:`_expression.join`
998 function or the :meth:`_expression.FromClause.join` method of any
999 :class:`_expression.FromClause` object.
1001 """
1002 self.left = coercions.expect(
1003 roles.FromClauseRole, left, deannotate=True
1004 )
1005 self.right = coercions.expect(
1006 roles.FromClauseRole, right, deannotate=True
1007 ).self_group()
1009 if onclause is None:
1010 self.onclause = self._match_primaries(self.left, self.right)
1011 else:
1012 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba
1013 # not merged yet
1014 self.onclause = coercions.expect(
1015 roles.OnClauseRole, onclause
1016 ).self_group(against=operators._asbool)
1018 self.isouter = isouter
1019 self.full = full
1021 @classmethod
1022 def _create_outerjoin(cls, left, right, onclause=None, full=False):
1023 """Return an ``OUTER JOIN`` clause element.
1025 The returned object is an instance of :class:`_expression.Join`.
1027 Similar functionality is also available via the
1028 :meth:`_expression.FromClause.outerjoin` method on any
1029 :class:`_expression.FromClause`.
1031 :param left: The left side of the join.
1033 :param right: The right side of the join.
1035 :param onclause: Optional criterion for the ``ON`` clause, is
1036 derived from foreign key relationships established between
1037 left and right otherwise.
1039 To chain joins together, use the :meth:`_expression.FromClause.join`
1040 or
1041 :meth:`_expression.FromClause.outerjoin` methods on the resulting
1042 :class:`_expression.Join` object.
1044 """
1045 return cls(left, right, onclause, isouter=True, full=full)
1047 @classmethod
1048 def _create_join(
1049 cls, left, right, onclause=None, isouter=False, full=False
1050 ):
1051 """Produce a :class:`_expression.Join` object, given two
1052 :class:`_expression.FromClause`
1053 expressions.
1055 E.g.::
1057 j = join(user_table, address_table,
1058 user_table.c.id == address_table.c.user_id)
1059 stmt = select(user_table).select_from(j)
1061 would emit SQL along the lines of::
1063 SELECT user.id, user.name FROM user
1064 JOIN address ON user.id = address.user_id
1066 Similar functionality is available given any
1067 :class:`_expression.FromClause` object (e.g. such as a
1068 :class:`_schema.Table`) using
1069 the :meth:`_expression.FromClause.join` method.
1071 :param left: The left side of the join.
1073 :param right: the right side of the join; this is any
1074 :class:`_expression.FromClause` object such as a
1075 :class:`_schema.Table` object, and
1076 may also be a selectable-compatible object such as an ORM-mapped
1077 class.
1079 :param onclause: a SQL expression representing the ON clause of the
1080 join. If left at ``None``, :meth:`_expression.FromClause.join`
1081 will attempt to
1082 join the two tables based on a foreign key relationship.
1084 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
1086 :param full: if True, render a FULL OUTER JOIN, instead of JOIN.
1088 .. versionadded:: 1.1
1090 .. seealso::
1092 :meth:`_expression.FromClause.join` - method form,
1093 based on a given left side.
1095 :class:`_expression.Join` - the type of object produced.
1097 """
1099 return cls(left, right, onclause, isouter, full)
1101 @property
1102 def description(self):
1103 return "Join object on %s(%d) and %s(%d)" % (
1104 self.left.description,
1105 id(self.left),
1106 self.right.description,
1107 id(self.right),
1108 )
1110 def is_derived_from(self, fromclause):
1111 return (
1112 # use hash() to ensure direct comparison to annotated works
1113 # as well
1114 hash(fromclause) == hash(self)
1115 or self.left.is_derived_from(fromclause)
1116 or self.right.is_derived_from(fromclause)
1117 )
1119 def self_group(self, against=None):
1120 return FromGrouping(self)
1122 @util.preload_module("sqlalchemy.sql.util")
1123 def _populate_column_collection(self):
1124 sqlutil = util.preloaded.sql_util
1125 columns = [c for c in self.left.columns] + [
1126 c for c in self.right.columns
1127 ]
1129 self.primary_key.extend(
1130 sqlutil.reduce_columns(
1131 (c for c in columns if c.primary_key), self.onclause
1132 )
1133 )
1134 self._columns._populate_separate_keys(
1135 (col._tq_key_label, col) for col in columns
1136 )
1137 self.foreign_keys.update(
1138 itertools.chain(*[col.foreign_keys for col in columns])
1139 )
1141 def _copy_internals(self, clone=_clone, **kw):
1142 # see Select._copy_internals() for similar concept
1144 # here we pre-clone "left" and "right" so that we can
1145 # determine the new FROM clauses
1146 all_the_froms = set(
1147 itertools.chain(
1148 _from_objects(self.left),
1149 _from_objects(self.right),
1150 )
1151 )
1153 # run the clone on those. these will be placed in the
1154 # cache used by the clone function
1155 new_froms = {f: clone(f, **kw) for f in all_the_froms}
1157 # set up a special replace function that will replace for
1158 # ColumnClause with parent table referring to those
1159 # replaced FromClause objects
1160 def replace(obj, **kw):
1161 if isinstance(obj, ColumnClause) and obj.table in new_froms:
1162 newelem = new_froms[obj.table].corresponding_column(obj)
1163 return newelem
1165 kw["replace"] = replace
1167 # run normal _copy_internals. the clones for
1168 # left and right will come from the clone function's
1169 # cache
1170 super(Join, self)._copy_internals(clone=clone, **kw)
1172 self._reset_memoizations()
1174 def _refresh_for_new_column(self, column):
1175 super(Join, self)._refresh_for_new_column(column)
1176 self.left._refresh_for_new_column(column)
1177 self.right._refresh_for_new_column(column)
1179 def _match_primaries(self, left, right):
1180 if isinstance(left, Join):
1181 left_right = left.right
1182 else:
1183 left_right = None
1184 return self._join_condition(left, right, a_subset=left_right)
1186 @classmethod
1187 def _join_condition(
1188 cls, a, b, a_subset=None, consider_as_foreign_keys=None
1189 ):
1190 """Create a join condition between two tables or selectables.
1192 e.g.::
1194 join_condition(tablea, tableb)
1196 would produce an expression along the lines of::
1198 tablea.c.id==tableb.c.tablea_id
1200 The join is determined based on the foreign key relationships
1201 between the two selectables. If there are multiple ways
1202 to join, or no way to join, an error is raised.
1204 :param a_subset: An optional expression that is a sub-component
1205 of ``a``. An attempt will be made to join to just this sub-component
1206 first before looking at the full ``a`` construct, and if found
1207 will be successful even if there are other ways to join to ``a``.
1208 This allows the "right side" of a join to be passed thereby
1209 providing a "natural join".
1211 """
1212 constraints = cls._joincond_scan_left_right(
1213 a, a_subset, b, consider_as_foreign_keys
1214 )
1216 if len(constraints) > 1:
1217 cls._joincond_trim_constraints(
1218 a, b, constraints, consider_as_foreign_keys
1219 )
1221 if len(constraints) == 0:
1222 if isinstance(b, FromGrouping):
1223 hint = (
1224 " Perhaps you meant to convert the right side to a "
1225 "subquery using alias()?"
1226 )
1227 else:
1228 hint = ""
1229 raise exc.NoForeignKeysError(
1230 "Can't find any foreign key relationships "
1231 "between '%s' and '%s'.%s"
1232 % (a.description, b.description, hint)
1233 )
1235 crit = [(x == y) for x, y in list(constraints.values())[0]]
1236 if len(crit) == 1:
1237 return crit[0]
1238 else:
1239 return and_(*crit)
1241 @classmethod
1242 def _can_join(cls, left, right, consider_as_foreign_keys=None):
1243 if isinstance(left, Join):
1244 left_right = left.right
1245 else:
1246 left_right = None
1248 constraints = cls._joincond_scan_left_right(
1249 a=left,
1250 b=right,
1251 a_subset=left_right,
1252 consider_as_foreign_keys=consider_as_foreign_keys,
1253 )
1255 return bool(constraints)
1257 @classmethod
1258 @util.preload_module("sqlalchemy.sql.util")
1259 def _joincond_scan_left_right(
1260 cls, a, a_subset, b, consider_as_foreign_keys
1261 ):
1262 sql_util = util.preloaded.sql_util
1264 a = coercions.expect(roles.FromClauseRole, a)
1265 b = coercions.expect(roles.FromClauseRole, b)
1267 constraints = collections.defaultdict(list)
1269 for left in (a_subset, a):
1270 if left is None:
1271 continue
1272 for fk in sorted(
1273 b.foreign_keys, key=lambda fk: fk.parent._creation_order
1274 ):
1275 if (
1276 consider_as_foreign_keys is not None
1277 and fk.parent not in consider_as_foreign_keys
1278 ):
1279 continue
1280 try:
1281 col = fk.get_referent(left)
1282 except exc.NoReferenceError as nrte:
1283 table_names = {t.name for t in sql_util.find_tables(left)}
1284 if nrte.table_name in table_names:
1285 raise
1286 else:
1287 continue
1289 if col is not None:
1290 constraints[fk.constraint].append((col, fk.parent))
1291 if left is not b:
1292 for fk in sorted(
1293 left.foreign_keys, key=lambda fk: fk.parent._creation_order
1294 ):
1295 if (
1296 consider_as_foreign_keys is not None
1297 and fk.parent not in consider_as_foreign_keys
1298 ):
1299 continue
1300 try:
1301 col = fk.get_referent(b)
1302 except exc.NoReferenceError as nrte:
1303 table_names = {t.name for t in sql_util.find_tables(b)}
1304 if nrte.table_name in table_names:
1305 raise
1306 else:
1307 continue
1309 if col is not None:
1310 constraints[fk.constraint].append((col, fk.parent))
1311 if constraints:
1312 break
1313 return constraints
1315 @classmethod
1316 def _joincond_trim_constraints(
1317 cls, a, b, constraints, consider_as_foreign_keys
1318 ):
1319 # more than one constraint matched. narrow down the list
1320 # to include just those FKCs that match exactly to
1321 # "consider_as_foreign_keys".
1322 if consider_as_foreign_keys:
1323 for const in list(constraints):
1324 if set(f.parent for f in const.elements) != set(
1325 consider_as_foreign_keys
1326 ):
1327 del constraints[const]
1329 # if still multiple constraints, but
1330 # they all refer to the exact same end result, use it.
1331 if len(constraints) > 1:
1332 dedupe = set(tuple(crit) for crit in constraints.values())
1333 if len(dedupe) == 1:
1334 key = list(constraints)[0]
1335 constraints = {key: constraints[key]}
1337 if len(constraints) != 1:
1338 raise exc.AmbiguousForeignKeysError(
1339 "Can't determine join between '%s' and '%s'; "
1340 "tables have more than one foreign key "
1341 "constraint relationship between them. "
1342 "Please specify the 'onclause' of this "
1343 "join explicitly." % (a.description, b.description)
1344 )
1346 @util.deprecated_params(
1347 whereclause=(
1348 "2.0",
1349 "The :paramref:`_sql.Join.select().whereclause` parameter "
1350 "is deprecated and will be removed in version 2.0. "
1351 "Please make use of "
1352 "the :meth:`.Select.where` "
1353 "method to add WHERE criteria to the SELECT statement.",
1354 ),
1355 kwargs=(
1356 "2.0",
1357 "The :meth:`_sql.Join.select` method will no longer accept "
1358 "keyword arguments in version 2.0. Please use generative "
1359 "methods from the "
1360 ":class:`_sql.Select` construct in order to apply additional "
1361 "modifications.",
1362 ),
1363 )
1364 def select(self, whereclause=None, **kwargs):
1365 r"""Create a :class:`_expression.Select` from this
1366 :class:`_expression.Join`.
1368 E.g.::
1370 stmt = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
1372 stmt = stmt.select()
1374 The above will produce a SQL string resembling::
1376 SELECT table_a.id, table_a.col, table_b.id, table_b.a_id
1377 FROM table_a JOIN table_b ON table_a.id = table_b.a_id
1379 :param whereclause: WHERE criteria, same as calling
1380 :meth:`_sql.Select.where` on the resulting statement
1382 :param \**kwargs: additional keyword arguments are passed to the
1383 legacy constructor for :class:`_sql.Select` described at
1384 :meth:`_sql.Select.create_legacy_select`.
1386 """
1387 collist = [self.left, self.right]
1389 if whereclause is not None:
1390 kwargs["whereclause"] = whereclause
1391 return Select._create_select_from_fromclause(
1392 self, collist, **kwargs
1393 ).select_from(self)
1395 @property
1396 @util.deprecated_20(
1397 ":attr:`.Executable.bind`",
1398 alternative="Bound metadata is being removed as of SQLAlchemy 2.0.",
1399 enable_warnings=False,
1400 )
1401 def bind(self):
1402 """Return the bound engine associated with either the left or right
1403 side of this :class:`_sql.Join`.
1405 """
1407 return self.left.bind or self.right.bind
1409 @util.preload_module("sqlalchemy.sql.util")
1410 def _anonymous_fromclause(self, name=None, flat=False):
1411 sqlutil = util.preloaded.sql_util
1412 if flat:
1413 if name is not None:
1414 raise exc.ArgumentError("Can't send name argument with flat")
1415 left_a, right_a = (
1416 self.left._anonymous_fromclause(flat=True),
1417 self.right._anonymous_fromclause(flat=True),
1418 )
1419 adapter = sqlutil.ClauseAdapter(left_a).chain(
1420 sqlutil.ClauseAdapter(right_a)
1421 )
1423 return left_a.join(
1424 right_a,
1425 adapter.traverse(self.onclause),
1426 isouter=self.isouter,
1427 full=self.full,
1428 )
1429 else:
1430 return (
1431 self.select()
1432 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
1433 .correlate(None)
1434 .alias(name)
1435 )
1437 @util.deprecated_20(
1438 ":meth:`_sql.Join.alias`",
1439 alternative="Create a select + subquery, or alias the "
1440 "individual tables inside the join, instead.",
1441 )
1442 def alias(self, name=None, flat=False):
1443 r"""Return an alias of this :class:`_expression.Join`.
1445 The default behavior here is to first produce a SELECT
1446 construct from this :class:`_expression.Join`, then to produce an
1447 :class:`_expression.Alias` from that. So given a join of the form::
1449 j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
1451 The JOIN by itself would look like::
1453 table_a JOIN table_b ON table_a.id = table_b.a_id
1455 Whereas the alias of the above, ``j.alias()``, would in a
1456 SELECT context look like::
1458 (SELECT table_a.id AS table_a_id, table_b.id AS table_b_id,
1459 table_b.a_id AS table_b_a_id
1460 FROM table_a
1461 JOIN table_b ON table_a.id = table_b.a_id) AS anon_1
1463 The equivalent long-hand form, given a :class:`_expression.Join`
1464 object ``j``, is::
1466 from sqlalchemy import select, alias
1467 j = alias(
1468 select(j.left, j.right).\
1469 select_from(j).\
1470 set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).\
1471 correlate(False),
1472 name=name
1473 )
1475 The selectable produced by :meth:`_expression.Join.alias`
1476 features the same
1477 columns as that of the two individual selectables presented under
1478 a single name - the individual columns are "auto-labeled", meaning
1479 the ``.c.`` collection of the resulting :class:`_expression.Alias`
1480 represents
1481 the names of the individual columns using a
1482 ``<tablename>_<columname>`` scheme::
1484 j.c.table_a_id
1485 j.c.table_b_a_id
1487 :meth:`_expression.Join.alias` also features an alternate
1488 option for aliasing joins which produces no enclosing SELECT and
1489 does not normally apply labels to the column names. The
1490 ``flat=True`` option will call :meth:`_expression.FromClause.alias`
1491 against the left and right sides individually.
1492 Using this option, no new ``SELECT`` is produced;
1493 we instead, from a construct as below::
1495 j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
1496 j = j.alias(flat=True)
1498 we get a result like this::
1500 table_a AS table_a_1 JOIN table_b AS table_b_1 ON
1501 table_a_1.id = table_b_1.a_id
1503 The ``flat=True`` argument is also propagated to the contained
1504 selectables, so that a composite join such as::
1506 j = table_a.join(
1507 table_b.join(table_c,
1508 table_b.c.id == table_c.c.b_id),
1509 table_b.c.a_id == table_a.c.id
1510 ).alias(flat=True)
1512 Will produce an expression like::
1514 table_a AS table_a_1 JOIN (
1515 table_b AS table_b_1 JOIN table_c AS table_c_1
1516 ON table_b_1.id = table_c_1.b_id
1517 ) ON table_a_1.id = table_b_1.a_id
1519 The standalone :func:`_expression.alias` function as well as the
1520 base :meth:`_expression.FromClause.alias`
1521 method also support the ``flat=True``
1522 argument as a no-op, so that the argument can be passed to the
1523 ``alias()`` method of any selectable.
1525 :param name: name given to the alias.
1527 :param flat: if True, produce an alias of the left and right
1528 sides of this :class:`_expression.Join` and return the join of those
1529 two selectables. This produces join expression that does not
1530 include an enclosing SELECT.
1532 .. seealso::
1534 :ref:`core_tutorial_aliases`
1536 :func:`_expression.alias`
1538 """
1539 return self._anonymous_fromclause(flat=flat, name=name)
1541 @property
1542 def _hide_froms(self):
1543 return itertools.chain(
1544 *[_from_objects(x.left, x.right) for x in self._cloned_set]
1545 )
1547 @property
1548 def _from_objects(self):
1549 return [self] + self.left._from_objects + self.right._from_objects
1552class NoInit(object):
1553 def __init__(self, *arg, **kw):
1554 raise NotImplementedError(
1555 "The %s class is not intended to be constructed "
1556 "directly. Please use the %s() standalone "
1557 "function or the %s() method available from appropriate "
1558 "selectable objects."
1559 % (
1560 self.__class__.__name__,
1561 self.__class__.__name__.lower(),
1562 self.__class__.__name__.lower(),
1563 )
1564 )
1567# FromClause ->
1568# AliasedReturnsRows
1569# -> Alias only for FromClause
1570# -> Subquery only for SelectBase
1571# -> CTE only for HasCTE -> SelectBase, DML
1572# -> Lateral -> FromClause, but we accept SelectBase
1573# w/ non-deprecated coercion
1574# -> TableSample -> only for FromClause
1575class AliasedReturnsRows(NoInit, FromClause):
1576 """Base class of aliases against tables, subqueries, and other
1577 selectables."""
1579 _is_from_container = True
1580 named_with_column = True
1582 _supports_derived_columns = False
1584 _traverse_internals = [
1585 ("element", InternalTraversal.dp_clauseelement),
1586 ("name", InternalTraversal.dp_anon_name),
1587 ]
1589 @classmethod
1590 def _construct(cls, *arg, **kw):
1591 obj = cls.__new__(cls)
1592 obj._init(*arg, **kw)
1593 return obj
1595 @classmethod
1596 def _factory(cls, returnsrows, name=None):
1597 """Base factory method. Subclasses need to provide this."""
1598 raise NotImplementedError()
1600 def _init(self, selectable, name=None):
1601 self.element = coercions.expect(
1602 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self
1603 )
1604 self.element = selectable
1605 self._orig_name = name
1606 if name is None:
1607 if (
1608 isinstance(selectable, FromClause)
1609 and selectable.named_with_column
1610 ):
1611 name = getattr(selectable, "name", None)
1612 if isinstance(name, _anonymous_label):
1613 name = None
1614 name = _anonymous_label.safe_construct(id(self), name or "anon")
1615 self.name = name
1617 def _refresh_for_new_column(self, column):
1618 super(AliasedReturnsRows, self)._refresh_for_new_column(column)
1619 self.element._refresh_for_new_column(column)
1621 @property
1622 def description(self):
1623 name = self.name
1624 if isinstance(name, _anonymous_label):
1625 name = "anon_1"
1627 if util.py3k:
1628 return name
1629 else:
1630 return name.encode("ascii", "backslashreplace")
1632 @property
1633 def original(self):
1634 """Legacy for dialects that are referring to Alias.original."""
1635 return self.element
1637 def is_derived_from(self, fromclause):
1638 if fromclause in self._cloned_set:
1639 return True
1640 return self.element.is_derived_from(fromclause)
1642 def _populate_column_collection(self):
1643 self.element._generate_fromclause_column_proxies(self)
1645 def _copy_internals(self, clone=_clone, **kw):
1646 existing_element = self.element
1648 super(AliasedReturnsRows, self)._copy_internals(clone=clone, **kw)
1650 # the element clone is usually against a Table that returns the
1651 # same object. don't reset exported .c. collections and other
1652 # memoized details if it was not changed. this saves a lot on
1653 # performance.
1654 if existing_element is not self.element:
1655 self._reset_column_collection()
1657 @property
1658 def _from_objects(self):
1659 return [self]
1661 @property
1662 def bind(self):
1663 return self.element.bind
1666class Alias(roles.DMLTableRole, AliasedReturnsRows):
1667 """Represents an table or selectable alias (AS).
1669 Represents an alias, as typically applied to any table or
1670 sub-select within a SQL statement using the ``AS`` keyword (or
1671 without the keyword on certain databases such as Oracle).
1673 This object is constructed from the :func:`_expression.alias` module
1674 level function as well as the :meth:`_expression.FromClause.alias`
1675 method available
1676 on all :class:`_expression.FromClause` subclasses.
1678 .. seealso::
1680 :meth:`_expression.FromClause.alias`
1682 """
1684 __visit_name__ = "alias"
1686 inherit_cache = True
1688 @classmethod
1689 def _factory(cls, selectable, name=None, flat=False):
1690 """Return an :class:`_expression.Alias` object.
1692 An :class:`_expression.Alias` represents any
1693 :class:`_expression.FromClause`
1694 with an alternate name assigned within SQL, typically using the ``AS``
1695 clause when generated, e.g. ``SELECT * FROM table AS aliasname``.
1697 Similar functionality is available via the
1698 :meth:`_expression.FromClause.alias`
1699 method available on all :class:`_expression.FromClause` subclasses.
1700 In terms of
1701 a SELECT object as generated from the :func:`_expression.select`
1702 function, the :meth:`_expression.SelectBase.alias` method returns an
1703 :class:`_expression.Alias` or similar object which represents a named,
1704 parenthesized subquery.
1706 When an :class:`_expression.Alias` is created from a
1707 :class:`_schema.Table` object,
1708 this has the effect of the table being rendered
1709 as ``tablename AS aliasname`` in a SELECT statement.
1711 For :func:`_expression.select` objects, the effect is that of
1712 creating a named subquery, i.e. ``(select ...) AS aliasname``.
1714 The ``name`` parameter is optional, and provides the name
1715 to use in the rendered SQL. If blank, an "anonymous" name
1716 will be deterministically generated at compile time.
1717 Deterministic means the name is guaranteed to be unique against
1718 other constructs used in the same statement, and will also be the
1719 same name for each successive compilation of the same statement
1720 object.
1722 :param selectable: any :class:`_expression.FromClause` subclass,
1723 such as a table, select statement, etc.
1725 :param name: string name to be assigned as the alias.
1726 If ``None``, a name will be deterministically generated
1727 at compile time.
1729 :param flat: Will be passed through to if the given selectable
1730 is an instance of :class:`_expression.Join` - see
1731 :meth:`_expression.Join.alias`
1732 for details.
1734 """
1735 return coercions.expect(
1736 roles.FromClauseRole, selectable, allow_select=True
1737 ).alias(name=name, flat=flat)
1740class TableValuedAlias(Alias):
1741 """An alias against a "table valued" SQL function.
1743 This construct provides for a SQL function that returns columns
1744 to be used in the FROM clause of a SELECT statement. The
1745 object is generated using the :meth:`_functions.FunctionElement.table_valued`
1746 method, e.g.::
1748 >>> from sqlalchemy import select, func
1749 >>> fn = func.json_array_elements_text('["one", "two", "three"]').table_valued("value")
1750 >>> print(select(fn.c.value))
1751 SELECT anon_1.value
1752 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1
1754 .. versionadded:: 1.4.0b2
1756 .. seealso::
1758 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
1760 """ # noqa: E501
1762 __visit_name__ = "table_valued_alias"
1764 _supports_derived_columns = True
1765 _render_derived = False
1766 _render_derived_w_types = False
1767 joins_implicitly = False
1769 _traverse_internals = [
1770 ("element", InternalTraversal.dp_clauseelement),
1771 ("name", InternalTraversal.dp_anon_name),
1772 ("_tableval_type", InternalTraversal.dp_type),
1773 ("_render_derived", InternalTraversal.dp_boolean),
1774 ("_render_derived_w_types", InternalTraversal.dp_boolean),
1775 ]
1777 def _init(
1778 self,
1779 selectable,
1780 name=None,
1781 table_value_type=None,
1782 joins_implicitly=False,
1783 ):
1784 super(TableValuedAlias, self)._init(selectable, name=name)
1786 self.joins_implicitly = joins_implicitly
1787 self._tableval_type = (
1788 type_api.TABLEVALUE
1789 if table_value_type is None
1790 else table_value_type
1791 )
1793 @HasMemoized.memoized_attribute
1794 def column(self):
1795 """Return a column expression representing this
1796 :class:`_sql.TableValuedAlias`.
1798 This accessor is used to implement the
1799 :meth:`_functions.FunctionElement.column_valued` method. See that
1800 method for further details.
1802 E.g.::
1804 >>> print(select(func.some_func().table_valued("value").column))
1805 SELECT anon_1 FROM some_func() AS anon_1
1807 .. seealso::
1809 :meth:`_functions.FunctionElement.column_valued`
1811 """
1813 return TableValuedColumn(self, self._tableval_type)
1815 def alias(self, name=None):
1816 """Return a new alias of this :class:`_sql.TableValuedAlias`.
1818 This creates a distinct FROM object that will be distinguished
1819 from the original one when used in a SQL statement.
1821 """
1823 tva = TableValuedAlias._construct(
1824 self,
1825 name=name,
1826 table_value_type=self._tableval_type,
1827 joins_implicitly=self.joins_implicitly,
1828 )
1830 if self._render_derived:
1831 tva._render_derived = True
1832 tva._render_derived_w_types = self._render_derived_w_types
1834 return tva
1836 def lateral(self, name=None):
1837 """Return a new :class:`_sql.TableValuedAlias` with the lateral flag
1838 set, so that it renders as LATERAL.
1840 .. seealso::
1842 :func:`_expression.lateral`
1844 """
1845 tva = self.alias(name=name)
1846 tva._is_lateral = True
1847 return tva
1849 def render_derived(self, name=None, with_types=False):
1850 """Apply "render derived" to this :class:`_sql.TableValuedAlias`.
1852 This has the effect of the individual column names listed out
1853 after the alias name in the "AS" sequence, e.g.::
1855 >>> print(
1856 ... select(
1857 ... func.unnest(array(["one", "two", "three"])).
1858 table_valued("x", with_ordinality="o").render_derived()
1859 ... )
1860 ... )
1861 SELECT anon_1.x, anon_1.o
1862 FROM unnest(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) WITH ORDINALITY AS anon_1(x, o)
1864 The ``with_types`` keyword will render column types inline within
1865 the alias expression (this syntax currently applies to the
1866 PostgreSQL database)::
1868 >>> print(
1869 ... select(
1870 ... func.json_to_recordset(
1871 ... '[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]'
1872 ... )
1873 ... .table_valued(column("a", Integer), column("b", String))
1874 ... .render_derived(with_types=True)
1875 ... )
1876 ... )
1877 SELECT anon_1.a, anon_1.b FROM json_to_recordset(:json_to_recordset_1)
1878 AS anon_1(a INTEGER, b VARCHAR)
1880 :param name: optional string name that will be applied to the alias
1881 generated. If left as None, a unique anonymizing name will be used.
1883 :param with_types: if True, the derived columns will include the
1884 datatype specification with each column. This is a special syntax
1885 currently known to be required by PostgreSQL for some SQL functions.
1887 """ # noqa: E501
1889 # note: don't use the @_generative system here, keep a reference
1890 # to the original object. otherwise you can have re-use of the
1891 # python id() of the original which can cause name conflicts if
1892 # a new anon-name grabs the same identifier as the local anon-name
1893 # (just saw it happen on CI)
1895 # construct against original to prevent memory growth
1896 # for repeated generations
1897 new_alias = TableValuedAlias._construct(
1898 self.element,
1899 name=name,
1900 table_value_type=self._tableval_type,
1901 joins_implicitly=self.joins_implicitly,
1902 )
1903 new_alias._render_derived = True
1904 new_alias._render_derived_w_types = with_types
1905 return new_alias
1908class Lateral(AliasedReturnsRows):
1909 """Represent a LATERAL subquery.
1911 This object is constructed from the :func:`_expression.lateral` module
1912 level function as well as the :meth:`_expression.FromClause.lateral`
1913 method available
1914 on all :class:`_expression.FromClause` subclasses.
1916 While LATERAL is part of the SQL standard, currently only more recent
1917 PostgreSQL versions provide support for this keyword.
1919 .. versionadded:: 1.1
1921 .. seealso::
1923 :ref:`tutorial_lateral_correlation` - overview of usage.
1925 """
1927 __visit_name__ = "lateral"
1928 _is_lateral = True
1930 inherit_cache = True
1932 @classmethod
1933 def _factory(cls, selectable, name=None):
1934 """Return a :class:`_expression.Lateral` object.
1936 :class:`_expression.Lateral` is an :class:`_expression.Alias`
1937 subclass that represents
1938 a subquery with the LATERAL keyword applied to it.
1940 The special behavior of a LATERAL subquery is that it appears in the
1941 FROM clause of an enclosing SELECT, but may correlate to other
1942 FROM clauses of that SELECT. It is a special case of subquery
1943 only supported by a small number of backends, currently more recent
1944 PostgreSQL versions.
1946 .. versionadded:: 1.1
1948 .. seealso::
1950 :ref:`tutorial_lateral_correlation` - overview of usage.
1953 """
1954 return coercions.expect(
1955 roles.FromClauseRole, selectable, explicit_subquery=True
1956 ).lateral(name=name)
1959class TableSample(AliasedReturnsRows):
1960 """Represent a TABLESAMPLE clause.
1962 This object is constructed from the :func:`_expression.tablesample` module
1963 level function as well as the :meth:`_expression.FromClause.tablesample`
1964 method
1965 available on all :class:`_expression.FromClause` subclasses.
1967 .. versionadded:: 1.1
1969 .. seealso::
1971 :func:`_expression.tablesample`
1973 """
1975 __visit_name__ = "tablesample"
1977 _traverse_internals = AliasedReturnsRows._traverse_internals + [
1978 ("sampling", InternalTraversal.dp_clauseelement),
1979 ("seed", InternalTraversal.dp_clauseelement),
1980 ]
1982 @classmethod
1983 def _factory(cls, selectable, sampling, name=None, seed=None):
1984 """Return a :class:`_expression.TableSample` object.
1986 :class:`_expression.TableSample` is an :class:`_expression.Alias`
1987 subclass that represents
1988 a table with the TABLESAMPLE clause applied to it.
1989 :func:`_expression.tablesample`
1990 is also available from the :class:`_expression.FromClause`
1991 class via the
1992 :meth:`_expression.FromClause.tablesample` method.
1994 The TABLESAMPLE clause allows selecting a randomly selected approximate
1995 percentage of rows from a table. It supports multiple sampling methods,
1996 most commonly BERNOULLI and SYSTEM.
1998 e.g.::
2000 from sqlalchemy import func
2002 selectable = people.tablesample(
2003 func.bernoulli(1),
2004 name='alias',
2005 seed=func.random())
2006 stmt = select(selectable.c.people_id)
2008 Assuming ``people`` with a column ``people_id``, the above
2009 statement would render as::
2011 SELECT alias.people_id FROM
2012 people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
2013 REPEATABLE (random())
2015 .. versionadded:: 1.1
2017 :param sampling: a ``float`` percentage between 0 and 100 or
2018 :class:`_functions.Function`.
2020 :param name: optional alias name
2022 :param seed: any real-valued SQL expression. When specified, the
2023 REPEATABLE sub-clause is also rendered.
2025 """
2026 return coercions.expect(roles.FromClauseRole, selectable).tablesample(
2027 sampling, name=name, seed=seed
2028 )
2030 @util.preload_module("sqlalchemy.sql.functions")
2031 def _init(self, selectable, sampling, name=None, seed=None):
2032 functions = util.preloaded.sql_functions
2033 if not isinstance(sampling, functions.Function):
2034 sampling = functions.func.system(sampling)
2036 self.sampling = sampling
2037 self.seed = seed
2038 super(TableSample, self)._init(selectable, name=name)
2040 def _get_method(self):
2041 return self.sampling
2044class CTE(
2045 roles.DMLTableRole,
2046 roles.IsCTERole,
2047 Generative,
2048 HasPrefixes,
2049 HasSuffixes,
2050 AliasedReturnsRows,
2051):
2052 """Represent a Common Table Expression.
2054 The :class:`_expression.CTE` object is obtained using the
2055 :meth:`_sql.SelectBase.cte` method from any SELECT statement. A less often
2056 available syntax also allows use of the :meth:`_sql.HasCTE.cte` method
2057 present on :term:`DML` constructs such as :class:`_sql.Insert`,
2058 :class:`_sql.Update` and
2059 :class:`_sql.Delete`. See the :meth:`_sql.HasCTE.cte` method for
2060 usage details on CTEs.
2062 .. seealso::
2064 :ref:`tutorial_subqueries_ctes` - in the 2.0 tutorial
2066 :meth:`_sql.HasCTE.cte` - examples of calling styles
2068 """
2070 __visit_name__ = "cte"
2072 _traverse_internals = (
2073 AliasedReturnsRows._traverse_internals
2074 + [
2075 ("_cte_alias", InternalTraversal.dp_clauseelement),
2076 ("_restates", InternalTraversal.dp_clauseelement),
2077 ("recursive", InternalTraversal.dp_boolean),
2078 ("nesting", InternalTraversal.dp_boolean),
2079 ]
2080 + HasPrefixes._has_prefixes_traverse_internals
2081 + HasSuffixes._has_suffixes_traverse_internals
2082 )
2084 @classmethod
2085 def _factory(cls, selectable, name=None, recursive=False):
2086 r"""Return a new :class:`_expression.CTE`,
2087 or Common Table Expression instance.
2089 Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage.
2091 """
2092 return coercions.expect(roles.HasCTERole, selectable).cte(
2093 name=name, recursive=recursive
2094 )
2096 def _init(
2097 self,
2098 selectable,
2099 name=None,
2100 recursive=False,
2101 nesting=False,
2102 _cte_alias=None,
2103 _restates=None,
2104 _prefixes=None,
2105 _suffixes=None,
2106 ):
2107 self.recursive = recursive
2108 self.nesting = nesting
2109 self._cte_alias = _cte_alias
2110 # Keep recursivity reference with union/union_all
2111 self._restates = _restates
2112 if _prefixes:
2113 self._prefixes = _prefixes
2114 if _suffixes:
2115 self._suffixes = _suffixes
2116 super(CTE, self)._init(selectable, name=name)
2118 def _populate_column_collection(self):
2119 if self._cte_alias is not None:
2120 self._cte_alias._generate_fromclause_column_proxies(self)
2121 else:
2122 self.element._generate_fromclause_column_proxies(self)
2124 def alias(self, name=None, flat=False):
2125 """Return an :class:`_expression.Alias` of this
2126 :class:`_expression.CTE`.
2128 This method is a CTE-specific specialization of the
2129 :meth:`_expression.FromClause.alias` method.
2131 .. seealso::
2133 :ref:`tutorial_using_aliases`
2135 :func:`_expression.alias`
2137 """
2138 return CTE._construct(
2139 self.element,
2140 name=name,
2141 recursive=self.recursive,
2142 nesting=self.nesting,
2143 _cte_alias=self,
2144 _prefixes=self._prefixes,
2145 _suffixes=self._suffixes,
2146 )
2148 def union(self, *other):
2149 r"""Return a new :class:`_expression.CTE` with a SQL ``UNION``
2150 of the original CTE against the given selectables provided
2151 as positional arguments.
2153 :param \*other: one or more elements with which to create a
2154 UNION.
2156 .. versionchanged:: 1.4.28 multiple elements are now accepted.
2158 .. seealso::
2160 :meth:`_sql.HasCTE.cte` - examples of calling styles
2162 """
2163 return CTE._construct(
2164 self.element.union(*other),
2165 name=self.name,
2166 recursive=self.recursive,
2167 nesting=self.nesting,
2168 _restates=self,
2169 _prefixes=self._prefixes,
2170 _suffixes=self._suffixes,
2171 )
2173 def union_all(self, *other):
2174 r"""Return a new :class:`_expression.CTE` with a SQL ``UNION ALL``
2175 of the original CTE against the given selectables provided
2176 as positional arguments.
2178 :param \*other: one or more elements with which to create a
2179 UNION.
2181 .. versionchanged:: 1.4.28 multiple elements are now accepted.
2183 .. seealso::
2185 :meth:`_sql.HasCTE.cte` - examples of calling styles
2187 """
2188 return CTE._construct(
2189 self.element.union_all(*other),
2190 name=self.name,
2191 recursive=self.recursive,
2192 nesting=self.nesting,
2193 _restates=self,
2194 _prefixes=self._prefixes,
2195 _suffixes=self._suffixes,
2196 )
2198 def _get_reference_cte(self):
2199 """
2200 A recursive CTE is updated to attach the recursive part.
2201 Updated CTEs should still refer to the original CTE.
2202 This function returns this reference identifier.
2203 """
2204 return self._restates if self._restates is not None else self
2207class HasCTE(roles.HasCTERole):
2208 """Mixin that declares a class to include CTE support.
2210 .. versionadded:: 1.1
2212 """
2214 _has_ctes_traverse_internals = [
2215 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),
2216 ]
2218 _independent_ctes = ()
2220 @_generative
2221 def add_cte(self, cte):
2222 """Add a :class:`_sql.CTE` to this statement object that will be
2223 independently rendered even if not referenced in the statement
2224 otherwise.
2226 This feature is useful for the use case of embedding a DML statement
2227 such as an INSERT or UPDATE as a CTE inline with a primary statement
2228 that may draw from its results indirectly; while PostgreSQL is known
2229 to support this usage, it may not be supported by other backends.
2231 E.g.::
2233 from sqlalchemy import table, column, select
2234 t = table('t', column('c1'), column('c2'))
2236 ins = t.insert().values({"c1": "x", "c2": "y"}).cte()
2238 stmt = select(t).add_cte(ins)
2240 Would render::
2242 WITH anon_1 AS
2243 (INSERT INTO t (c1, c2) VALUES (:param_1, :param_2))
2244 SELECT t.c1, t.c2
2245 FROM t
2247 Above, the "anon_1" CTE is not referred towards in the SELECT
2248 statement, however still accomplishes the task of running an INSERT
2249 statement.
2251 Similarly in a DML-related context, using the PostgreSQL
2252 :class:`_postgresql.Insert` construct to generate an "upsert"::
2254 from sqlalchemy import table, column
2255 from sqlalchemy.dialects.postgresql import insert
2257 t = table("t", column("c1"), column("c2"))
2259 delete_statement_cte = (
2260 t.delete().where(t.c.c1 < 1).cte("deletions")
2261 )
2263 insert_stmt = insert(t).values({"c1": 1, "c2": 2})
2264 update_statement = insert_stmt.on_conflict_do_update(
2265 index_elements=[t.c.c1],
2266 set_={
2267 "c1": insert_stmt.excluded.c1,
2268 "c2": insert_stmt.excluded.c2,
2269 },
2270 ).add_cte(delete_statement_cte)
2272 print(update_statement)
2274 The above statement renders as::
2276 WITH deletions AS
2277 (DELETE FROM t WHERE t.c1 < %(c1_1)s)
2278 INSERT INTO t (c1, c2) VALUES (%(c1)s, %(c2)s)
2279 ON CONFLICT (c1) DO UPDATE SET c1 = excluded.c1, c2 = excluded.c2
2281 .. versionadded:: 1.4.21
2283 """
2284 cte = coercions.expect(roles.IsCTERole, cte)
2285 self._independent_ctes += (cte,)
2287 def cte(self, name=None, recursive=False, nesting=False):
2288 r"""Return a new :class:`_expression.CTE`,
2289 or Common Table Expression instance.
2291 Common table expressions are a SQL standard whereby SELECT
2292 statements can draw upon secondary statements specified along
2293 with the primary statement, using a clause called "WITH".
2294 Special semantics regarding UNION can also be employed to
2295 allow "recursive" queries, where a SELECT statement can draw
2296 upon the set of rows that have previously been selected.
2298 CTEs can also be applied to DML constructs UPDATE, INSERT
2299 and DELETE on some databases, both as a source of CTE rows
2300 when combined with RETURNING, as well as a consumer of
2301 CTE rows.
2303 .. versionchanged:: 1.1 Added support for UPDATE/INSERT/DELETE as
2304 CTE, CTEs added to UPDATE/INSERT/DELETE.
2306 SQLAlchemy detects :class:`_expression.CTE` objects, which are treated
2307 similarly to :class:`_expression.Alias` objects, as special elements
2308 to be delivered to the FROM clause of the statement as well
2309 as to a WITH clause at the top of the statement.
2311 For special prefixes such as PostgreSQL "MATERIALIZED" and
2312 "NOT MATERIALIZED", the :meth:`_expression.CTE.prefix_with`
2313 method may be
2314 used to establish these.
2316 .. versionchanged:: 1.3.13 Added support for prefixes.
2317 In particular - MATERIALIZED and NOT MATERIALIZED.
2319 :param name: name given to the common table expression. Like
2320 :meth:`_expression.FromClause.alias`, the name can be left as
2321 ``None`` in which case an anonymous symbol will be used at query
2322 compile time.
2323 :param recursive: if ``True``, will render ``WITH RECURSIVE``.
2324 A recursive common table expression is intended to be used in
2325 conjunction with UNION ALL in order to derive rows
2326 from those already selected.
2327 :param nesting: if ``True``, will render the CTE locally to the
2328 actual statement.
2330 .. versionadded:: 1.4.24
2332 The following examples include two from PostgreSQL's documentation at
2333 https://www.postgresql.org/docs/current/static/queries-with.html,
2334 as well as additional examples.
2336 Example 1, non recursive::
2338 from sqlalchemy import (Table, Column, String, Integer,
2339 MetaData, select, func)
2341 metadata = MetaData()
2343 orders = Table('orders', metadata,
2344 Column('region', String),
2345 Column('amount', Integer),
2346 Column('product', String),
2347 Column('quantity', Integer)
2348 )
2350 regional_sales = select(
2351 orders.c.region,
2352 func.sum(orders.c.amount).label('total_sales')
2353 ).group_by(orders.c.region).cte("regional_sales")
2356 top_regions = select(regional_sales.c.region).\
2357 where(
2358 regional_sales.c.total_sales >
2359 select(
2360 func.sum(regional_sales.c.total_sales) / 10
2361 )
2362 ).cte("top_regions")
2364 statement = select(
2365 orders.c.region,
2366 orders.c.product,
2367 func.sum(orders.c.quantity).label("product_units"),
2368 func.sum(orders.c.amount).label("product_sales")
2369 ).where(orders.c.region.in_(
2370 select(top_regions.c.region)
2371 )).group_by(orders.c.region, orders.c.product)
2373 result = conn.execute(statement).fetchall()
2375 Example 2, WITH RECURSIVE::
2377 from sqlalchemy import (Table, Column, String, Integer,
2378 MetaData, select, func)
2380 metadata = MetaData()
2382 parts = Table('parts', metadata,
2383 Column('part', String),
2384 Column('sub_part', String),
2385 Column('quantity', Integer),
2386 )
2388 included_parts = select(\
2389 parts.c.sub_part, parts.c.part, parts.c.quantity\
2390 ).\
2391 where(parts.c.part=='our part').\
2392 cte(recursive=True)
2395 incl_alias = included_parts.alias()
2396 parts_alias = parts.alias()
2397 included_parts = included_parts.union_all(
2398 select(
2399 parts_alias.c.sub_part,
2400 parts_alias.c.part,
2401 parts_alias.c.quantity
2402 ).\
2403 where(parts_alias.c.part==incl_alias.c.sub_part)
2404 )
2406 statement = select(
2407 included_parts.c.sub_part,
2408 func.sum(included_parts.c.quantity).
2409 label('total_quantity')
2410 ).\
2411 group_by(included_parts.c.sub_part)
2413 result = conn.execute(statement).fetchall()
2415 Example 3, an upsert using UPDATE and INSERT with CTEs::
2417 from datetime import date
2418 from sqlalchemy import (MetaData, Table, Column, Integer,
2419 Date, select, literal, and_, exists)
2421 metadata = MetaData()
2423 visitors = Table('visitors', metadata,
2424 Column('product_id', Integer, primary_key=True),
2425 Column('date', Date, primary_key=True),
2426 Column('count', Integer),
2427 )
2429 # add 5 visitors for the product_id == 1
2430 product_id = 1
2431 day = date.today()
2432 count = 5
2434 update_cte = (
2435 visitors.update()
2436 .where(and_(visitors.c.product_id == product_id,
2437 visitors.c.date == day))
2438 .values(count=visitors.c.count + count)
2439 .returning(literal(1))
2440 .cte('update_cte')
2441 )
2443 upsert = visitors.insert().from_select(
2444 [visitors.c.product_id, visitors.c.date, visitors.c.count],
2445 select(literal(product_id), literal(day), literal(count))
2446 .where(~exists(update_cte.select()))
2447 )
2449 connection.execute(upsert)
2451 Example 4, Nesting CTE (SQLAlchemy 1.4.24 and above)::
2453 value_a = select(
2454 literal("root").label("n")
2455 ).cte("value_a")
2457 # A nested CTE with the same name as the root one
2458 value_a_nested = select(
2459 literal("nesting").label("n")
2460 ).cte("value_a", nesting=True)
2462 # Nesting CTEs takes ascendency locally
2463 # over the CTEs at a higher level
2464 value_b = select(value_a_nested.c.n).cte("value_b")
2466 value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))
2468 The above query will render the second CTE nested inside the first,
2469 shown with inline parameters below as::
2471 WITH
2472 value_a AS
2473 (SELECT 'root' AS n),
2474 value_b AS
2475 (WITH value_a AS
2476 (SELECT 'nesting' AS n)
2477 SELECT value_a.n AS n FROM value_a)
2478 SELECT value_a.n AS a, value_b.n AS b
2479 FROM value_a, value_b
2481 Example 5, Non-Linear CTE (SQLAlchemy 1.4.28 and above)::
2483 edge = Table(
2484 "edge",
2485 metadata,
2486 Column("id", Integer, primary_key=True),
2487 Column("left", Integer),
2488 Column("right", Integer),
2489 )
2491 root_node = select(literal(1).label("node")).cte(
2492 "nodes", recursive=True
2493 )
2495 left_edge = select(edge.c.left).join(
2496 root_node, edge.c.right == root_node.c.node
2497 )
2498 right_edge = select(edge.c.right).join(
2499 root_node, edge.c.left == root_node.c.node
2500 )
2502 subgraph_cte = root_node.union(left_edge, right_edge)
2504 subgraph = select(subgraph_cte)
2506 The above query will render 2 UNIONs inside the recursive CTE::
2508 WITH RECURSIVE nodes(node) AS (
2509 SELECT 1 AS node
2510 UNION
2511 SELECT edge."left" AS "left"
2512 FROM edge JOIN nodes ON edge."right" = nodes.node
2513 UNION
2514 SELECT edge."right" AS "right"
2515 FROM edge JOIN nodes ON edge."left" = nodes.node
2516 )
2517 SELECT nodes.node FROM nodes
2519 .. seealso::
2521 :meth:`_orm.Query.cte` - ORM version of
2522 :meth:`_expression.HasCTE.cte`.
2524 """
2525 return CTE._construct(
2526 self, name=name, recursive=recursive, nesting=nesting
2527 )
2530class Subquery(AliasedReturnsRows):
2531 """Represent a subquery of a SELECT.
2533 A :class:`.Subquery` is created by invoking the
2534 :meth:`_expression.SelectBase.subquery` method, or for convenience the
2535 :meth:`_expression.SelectBase.alias` method, on any
2536 :class:`_expression.SelectBase` subclass
2537 which includes :class:`_expression.Select`,
2538 :class:`_expression.CompoundSelect`, and
2539 :class:`_expression.TextualSelect`. As rendered in a FROM clause,
2540 it represents the
2541 body of the SELECT statement inside of parenthesis, followed by the usual
2542 "AS <somename>" that defines all "alias" objects.
2544 The :class:`.Subquery` object is very similar to the
2545 :class:`_expression.Alias`
2546 object and can be used in an equivalent way. The difference between
2547 :class:`_expression.Alias` and :class:`.Subquery` is that
2548 :class:`_expression.Alias` always
2549 contains a :class:`_expression.FromClause` object whereas
2550 :class:`.Subquery`
2551 always contains a :class:`_expression.SelectBase` object.
2553 .. versionadded:: 1.4 The :class:`.Subquery` class was added which now
2554 serves the purpose of providing an aliased version of a SELECT
2555 statement.
2557 """
2559 __visit_name__ = "subquery"
2561 _is_subquery = True
2563 inherit_cache = True
2565 @classmethod
2566 def _factory(cls, selectable, name=None):
2567 """Return a :class:`.Subquery` object."""
2568 return coercions.expect(
2569 roles.SelectStatementRole, selectable
2570 ).subquery(name=name)
2572 @util.deprecated(
2573 "1.4",
2574 "The :meth:`.Subquery.as_scalar` method, which was previously "
2575 "``Alias.as_scalar()`` prior to version 1.4, is deprecated and "
2576 "will be removed in a future release; Please use the "
2577 ":meth:`_expression.Select.scalar_subquery` method of the "
2578 ":func:`_expression.select` "
2579 "construct before constructing a subquery object, or with the ORM "
2580 "use the :meth:`_query.Query.scalar_subquery` method.",
2581 )
2582 def as_scalar(self):
2583 return self.element.set_label_style(LABEL_STYLE_NONE).scalar_subquery()
2585 def _execute_on_connection(
2586 self,
2587 connection,
2588 multiparams,
2589 params,
2590 execution_options,
2591 ):
2592 util.warn_deprecated(
2593 "Executing a subquery object is deprecated and will raise "
2594 "ObjectNotExecutableError in an upcoming release. Please "
2595 "execute the underlying select() statement directly.",
2596 "1.4",
2597 )
2598 return self.element._execute_on_connection(
2599 connection, multiparams, params, execution_options, _force=True
2600 )
2603class FromGrouping(GroupedElement, FromClause):
2604 """Represent a grouping of a FROM clause"""
2606 _traverse_internals = [("element", InternalTraversal.dp_clauseelement)]
2608 def __init__(self, element):
2609 self.element = coercions.expect(roles.FromClauseRole, element)
2611 def _init_collections(self):
2612 pass
2614 @property
2615 def columns(self):
2616 return self.element.columns
2618 @property
2619 def primary_key(self):
2620 return self.element.primary_key
2622 @property
2623 def foreign_keys(self):
2624 return self.element.foreign_keys
2626 def is_derived_from(self, element):
2627 return self.element.is_derived_from(element)
2629 def alias(self, **kw):
2630 return FromGrouping(self.element.alias(**kw))
2632 def _anonymous_fromclause(self, **kw):
2633 return FromGrouping(self.element._anonymous_fromclause(**kw))
2635 @property
2636 def _hide_froms(self):
2637 return self.element._hide_froms
2639 @property
2640 def _from_objects(self):
2641 return self.element._from_objects
2643 def __getstate__(self):
2644 return {"element": self.element}
2646 def __setstate__(self, state):
2647 self.element = state["element"]
2650class TableClause(roles.DMLTableRole, Immutable, FromClause):
2651 """Represents a minimal "table" construct.
2653 This is a lightweight table object that has only a name, a
2654 collection of columns, which are typically produced
2655 by the :func:`_expression.column` function, and a schema::
2657 from sqlalchemy import table, column
2659 user = table("user",
2660 column("id"),
2661 column("name"),
2662 column("description"),
2663 )
2665 The :class:`_expression.TableClause` construct serves as the base for
2666 the more commonly used :class:`_schema.Table` object, providing
2667 the usual set of :class:`_expression.FromClause` services including
2668 the ``.c.`` collection and statement generation methods.
2670 It does **not** provide all the additional schema-level services
2671 of :class:`_schema.Table`, including constraints, references to other
2672 tables, or support for :class:`_schema.MetaData`-level services.
2673 It's useful
2674 on its own as an ad-hoc construct used to generate quick SQL
2675 statements when a more fully fledged :class:`_schema.Table`
2676 is not on hand.
2678 """
2680 __visit_name__ = "table"
2682 _traverse_internals = [
2683 (
2684 "columns",
2685 InternalTraversal.dp_fromclause_canonical_column_collection,
2686 ),
2687 ("name", InternalTraversal.dp_string),
2688 ("schema", InternalTraversal.dp_string),
2689 ]
2691 named_with_column = True
2693 implicit_returning = False
2694 """:class:`_expression.TableClause`
2695 doesn't support having a primary key or column
2696 -level defaults, so implicit returning doesn't apply."""
2698 _autoincrement_column = None
2699 """No PK or default support so no autoincrement column."""
2701 def __init__(self, name, *columns, **kw):
2702 """Produce a new :class:`_expression.TableClause`.
2704 The object returned is an instance of
2705 :class:`_expression.TableClause`, which
2706 represents the "syntactical" portion of the schema-level
2707 :class:`_schema.Table` object.
2708 It may be used to construct lightweight table constructs.
2710 .. versionchanged:: 1.0.0 :func:`_expression.table` can now
2711 be imported from the plain ``sqlalchemy`` namespace like any
2712 other SQL element.
2715 :param name: Name of the table.
2717 :param columns: A collection of :func:`_expression.column` constructs.
2719 :param schema: The schema name for this table.
2721 .. versionadded:: 1.3.18 :func:`_expression.table` can now
2722 accept a ``schema`` argument.
2723 """
2725 super(TableClause, self).__init__()
2726 self.name = name
2727 self._columns = DedupeColumnCollection()
2728 self.primary_key = ColumnSet()
2729 self.foreign_keys = set()
2730 for c in columns:
2731 self.append_column(c)
2733 schema = kw.pop("schema", None)
2734 if schema is not None:
2735 self.schema = schema
2736 if self.schema is not None:
2737 self.fullname = "%s.%s" % (self.schema, self.name)
2738 else:
2739 self.fullname = self.name
2740 if kw:
2741 raise exc.ArgumentError("Unsupported argument(s): %s" % list(kw))
2743 def __str__(self):
2744 if self.schema is not None:
2745 return self.schema + "." + self.name
2746 else:
2747 return self.name
2749 def _refresh_for_new_column(self, column):
2750 pass
2752 def _init_collections(self):
2753 pass
2755 @util.memoized_property
2756 def description(self):
2757 if util.py3k:
2758 return self.name
2759 else:
2760 return self.name.encode("ascii", "backslashreplace")
2762 def append_column(self, c, **kw):
2763 existing = c.table
2764 if existing is not None and existing is not self:
2765 raise exc.ArgumentError(
2766 "column object '%s' already assigned to table '%s'"
2767 % (c.key, existing)
2768 )
2770 self._columns.add(c)
2771 c.table = self
2773 @util.preload_module("sqlalchemy.sql.dml")
2774 def insert(self, values=None, inline=False, **kwargs):
2775 """Generate an :func:`_expression.insert` construct against this
2776 :class:`_expression.TableClause`.
2778 E.g.::
2780 table.insert().values(name='foo')
2782 See :func:`_expression.insert` for argument and usage information.
2784 """
2785 return util.preloaded.sql_dml.Insert(
2786 self, values=values, inline=inline, **kwargs
2787 )
2789 @util.preload_module("sqlalchemy.sql.dml")
2790 def update(self, whereclause=None, values=None, inline=False, **kwargs):
2791 """Generate an :func:`_expression.update` construct against this
2792 :class:`_expression.TableClause`.
2794 E.g.::
2796 table.update().where(table.c.id==7).values(name='foo')
2798 See :func:`_expression.update` for argument and usage information.
2800 """
2801 return util.preloaded.sql_dml.Update(
2802 self,
2803 whereclause=whereclause,
2804 values=values,
2805 inline=inline,
2806 **kwargs
2807 )
2809 @util.preload_module("sqlalchemy.sql.dml")
2810 def delete(self, whereclause=None, **kwargs):
2811 """Generate a :func:`_expression.delete` construct against this
2812 :class:`_expression.TableClause`.
2814 E.g.::
2816 table.delete().where(table.c.id==7)
2818 See :func:`_expression.delete` for argument and usage information.
2820 """
2821 return util.preloaded.sql_dml.Delete(self, whereclause, **kwargs)
2823 @property
2824 def _from_objects(self):
2825 return [self]
2828class ForUpdateArg(ClauseElement):
2829 _traverse_internals = [
2830 ("of", InternalTraversal.dp_clauseelement_list),
2831 ("nowait", InternalTraversal.dp_boolean),
2832 ("read", InternalTraversal.dp_boolean),
2833 ("skip_locked", InternalTraversal.dp_boolean),
2834 ]
2836 @classmethod
2837 def _from_argument(cls, with_for_update):
2838 if isinstance(with_for_update, ForUpdateArg):
2839 return with_for_update
2840 elif with_for_update in (None, False):
2841 return None
2842 elif with_for_update is True:
2843 return ForUpdateArg()
2844 else:
2845 return ForUpdateArg(**with_for_update)
2847 def __eq__(self, other):
2848 return (
2849 isinstance(other, ForUpdateArg)
2850 and other.nowait == self.nowait
2851 and other.read == self.read
2852 and other.skip_locked == self.skip_locked
2853 and other.key_share == self.key_share
2854 and other.of is self.of
2855 )
2857 def __ne__(self, other):
2858 return not self.__eq__(other)
2860 def __hash__(self):
2861 return id(self)
2863 def __init__(
2864 self,
2865 nowait=False,
2866 read=False,
2867 of=None,
2868 skip_locked=False,
2869 key_share=False,
2870 ):
2871 """Represents arguments specified to
2872 :meth:`_expression.Select.for_update`.
2874 """
2876 self.nowait = nowait
2877 self.read = read
2878 self.skip_locked = skip_locked
2879 self.key_share = key_share
2880 if of is not None:
2881 self.of = [
2882 coercions.expect(roles.ColumnsClauseRole, elem)
2883 for elem in util.to_list(of)
2884 ]
2885 else:
2886 self.of = None
2889class Values(Generative, FromClause):
2890 """Represent a ``VALUES`` construct that can be used as a FROM element
2891 in a statement.
2893 The :class:`_expression.Values` object is created from the
2894 :func:`_expression.values` function.
2896 .. versionadded:: 1.4
2898 """
2900 named_with_column = True
2901 __visit_name__ = "values"
2903 _data = ()
2905 _traverse_internals = [
2906 ("_column_args", InternalTraversal.dp_clauseelement_list),
2907 ("_data", InternalTraversal.dp_dml_multi_values),
2908 ("name", InternalTraversal.dp_string),
2909 ("literal_binds", InternalTraversal.dp_boolean),
2910 ]
2912 def __init__(self, *columns, **kw):
2913 r"""Construct a :class:`_expression.Values` construct.
2915 The column expressions and the actual data for
2916 :class:`_expression.Values` are given in two separate steps. The
2917 constructor receives the column expressions typically as
2918 :func:`_expression.column` constructs,
2919 and the data is then passed via the
2920 :meth:`_expression.Values.data` method as a list,
2921 which can be called multiple
2922 times to add more data, e.g.::
2924 from sqlalchemy import column
2925 from sqlalchemy import values
2927 value_expr = values(
2928 column('id', Integer),
2929 column('name', String),
2930 name="my_values"
2931 ).data(
2932 [(1, 'name1'), (2, 'name2'), (3, 'name3')]
2933 )
2935 :param \*columns: column expressions, typically composed using
2936 :func:`_expression.column` objects.
2938 :param name: the name for this VALUES construct. If omitted, the
2939 VALUES construct will be unnamed in a SQL expression. Different
2940 backends may have different requirements here.
2942 :param literal_binds: Defaults to False. Whether or not to render
2943 the data values inline in the SQL output, rather than using bound
2944 parameters.
2946 """
2948 super(Values, self).__init__()
2949 self._column_args = columns
2950 self.name = kw.pop("name", None)
2951 self.literal_binds = kw.pop("literal_binds", False)
2952 self.named_with_column = self.name is not None
2954 @property
2955 def _column_types(self):
2956 return [col.type for col in self._column_args]
2958 @_generative
2959 def alias(self, name, **kw):
2960 """Return a new :class:`_expression.Values`
2961 construct that is a copy of this
2962 one with the given name.
2964 This method is a VALUES-specific specialization of the
2965 :meth:`_expression.FromClause.alias` method.
2967 .. seealso::
2969 :ref:`tutorial_using_aliases`
2971 :func:`_expression.alias`
2973 """
2974 self.name = name
2975 self.named_with_column = self.name is not None
2977 @_generative
2978 def lateral(self, name=None):
2979 """Return a new :class:`_expression.Values` with the lateral flag set,
2980 so that
2981 it renders as LATERAL.
2983 .. seealso::
2985 :func:`_expression.lateral`
2987 """
2988 self._is_lateral = True
2989 if name is not None:
2990 self.name = name
2992 @_generative
2993 def data(self, values):
2994 """Return a new :class:`_expression.Values` construct,
2995 adding the given data
2996 to the data list.
2998 E.g.::
3000 my_values = my_values.data([(1, 'value 1'), (2, 'value2')])
3002 :param values: a sequence (i.e. list) of tuples that map to the
3003 column expressions given in the :class:`_expression.Values`
3004 constructor.
3006 """
3008 self._data += (values,)
3010 def _populate_column_collection(self):
3011 for c in self._column_args:
3012 self._columns.add(c)
3013 c.table = self
3015 @property
3016 def _from_objects(self):
3017 return [self]
3020class SelectBase(
3021 roles.SelectStatementRole,
3022 roles.DMLSelectRole,
3023 roles.CompoundElementRole,
3024 roles.InElementRole,
3025 HasCTE,
3026 Executable,
3027 SupportsCloneAnnotations,
3028 Selectable,
3029):
3030 """Base class for SELECT statements.
3033 This includes :class:`_expression.Select`,
3034 :class:`_expression.CompoundSelect` and
3035 :class:`_expression.TextualSelect`.
3038 """
3040 _is_select_statement = True
3041 is_select = True
3043 def _generate_fromclause_column_proxies(self, fromclause):
3044 raise NotImplementedError()
3046 def _refresh_for_new_column(self, column):
3047 self._reset_memoizations()
3049 @property
3050 def selected_columns(self):
3051 """A :class:`_expression.ColumnCollection`
3052 representing the columns that
3053 this SELECT statement or similar construct returns in its result set.
3055 This collection differs from the :attr:`_expression.FromClause.columns`
3056 collection of a :class:`_expression.FromClause` in that the columns
3057 within this collection cannot be directly nested inside another SELECT
3058 statement; a subquery must be applied first which provides for the
3059 necessary parenthesization required by SQL.
3061 .. note::
3063 The :attr:`_sql.SelectBase.selected_columns` collection does not
3064 include expressions established in the columns clause using the
3065 :func:`_sql.text` construct; these are silently omitted from the
3066 collection. To use plain textual column expressions inside of a
3067 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`
3068 construct.
3070 .. seealso::
3072 :attr:`_sql.Select.selected_columns`
3074 .. versionadded:: 1.4
3076 """
3077 raise NotImplementedError()
3079 @property
3080 def _all_selected_columns(self):
3081 """A sequence of expressions that correspond to what is rendered
3082 in the columns clause, including :class:`_sql.TextClause`
3083 constructs.
3085 .. versionadded:: 1.4.12
3087 .. seealso::
3089 :attr:`_sql.SelectBase.exported_columns`
3091 """
3092 raise NotImplementedError()
3094 @property
3095 def exported_columns(self):
3096 """A :class:`_expression.ColumnCollection`
3097 that represents the "exported"
3098 columns of this :class:`_expression.Selectable`, not including
3099 :class:`_sql.TextClause` constructs.
3101 The "exported" columns for a :class:`_expression.SelectBase`
3102 object are synonymous
3103 with the :attr:`_expression.SelectBase.selected_columns` collection.
3105 .. versionadded:: 1.4
3107 .. seealso::
3109 :attr:`_expression.Select.exported_columns`
3111 :attr:`_expression.Selectable.exported_columns`
3113 :attr:`_expression.FromClause.exported_columns`
3116 """
3117 return self.selected_columns
3119 @property
3120 @util.deprecated(
3121 "1.4",
3122 "The :attr:`_expression.SelectBase.c` and "
3123 ":attr:`_expression.SelectBase.columns` attributes "
3124 "are deprecated and will be removed in a future release; these "
3125 "attributes implicitly create a subquery that should be explicit. "
3126 "Please call :meth:`_expression.SelectBase.subquery` "
3127 "first in order to create "
3128 "a subquery, which then contains this attribute. To access the "
3129 "columns that this SELECT object SELECTs "
3130 "from, use the :attr:`_expression.SelectBase.selected_columns` "
3131 "attribute.",
3132 )
3133 def c(self):
3134 return self._implicit_subquery.columns
3136 @property
3137 def columns(self):
3138 return self.c
3140 @util.deprecated(
3141 "1.4",
3142 "The :meth:`_expression.SelectBase.select` method is deprecated "
3143 "and will be removed in a future release; this method implicitly "
3144 "creates a subquery that should be explicit. "
3145 "Please call :meth:`_expression.SelectBase.subquery` "
3146 "first in order to create "
3147 "a subquery, which then can be selected.",
3148 )
3149 def select(self, *arg, **kw):
3150 return self._implicit_subquery.select(*arg, **kw)
3152 @HasMemoized.memoized_attribute
3153 def _implicit_subquery(self):
3154 return self.subquery()
3156 @util.deprecated(
3157 "1.4",
3158 "The :meth:`_expression.SelectBase.as_scalar` "
3159 "method is deprecated and will be "
3160 "removed in a future release. Please refer to "
3161 ":meth:`_expression.SelectBase.scalar_subquery`.",
3162 )
3163 def as_scalar(self):
3164 return self.scalar_subquery()
3166 def exists(self):
3167 """Return an :class:`_sql.Exists` representation of this selectable,
3168 which can be used as a column expression.
3170 The returned object is an instance of :class:`_sql.Exists`.
3172 .. seealso::
3174 :func:`_sql.exists`
3176 :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.
3178 .. versionadded:: 1.4
3180 """
3181 return Exists(self)
3183 def scalar_subquery(self):
3184 """Return a 'scalar' representation of this selectable, which can be
3185 used as a column expression.
3187 The returned object is an instance of :class:`_sql.ScalarSelect`.
3189 Typically, a select statement which has only one column in its columns
3190 clause is eligible to be used as a scalar expression. The scalar
3191 subquery can then be used in the WHERE clause or columns clause of
3192 an enclosing SELECT.
3194 Note that the scalar subquery differentiates from the FROM-level
3195 subquery that can be produced using the
3196 :meth:`_expression.SelectBase.subquery`
3197 method.
3199 .. versionchanged: 1.4 - the ``.as_scalar()`` method was renamed to
3200 :meth:`_expression.SelectBase.scalar_subquery`.
3202 .. seealso::
3204 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
3206 """
3207 if self._label_style is not LABEL_STYLE_NONE:
3208 self = self.set_label_style(LABEL_STYLE_NONE)
3210 return ScalarSelect(self)
3212 def label(self, name):
3213 """Return a 'scalar' representation of this selectable, embedded as a
3214 subquery with a label.
3216 .. seealso::
3218 :meth:`_expression.SelectBase.as_scalar`.
3220 """
3221 return self.scalar_subquery().label(name)
3223 def lateral(self, name=None):
3224 """Return a LATERAL alias of this :class:`_expression.Selectable`.
3226 The return value is the :class:`_expression.Lateral` construct also
3227 provided by the top-level :func:`_expression.lateral` function.
3229 .. versionadded:: 1.1
3231 .. seealso::
3233 :ref:`tutorial_lateral_correlation` - overview of usage.
3235 """
3236 return Lateral._factory(self, name)
3238 @property
3239 def _from_objects(self):
3240 return [self]
3242 def subquery(self, name=None):
3243 """Return a subquery of this :class:`_expression.SelectBase`.
3245 A subquery is from a SQL perspective a parenthesized, named
3246 construct that can be placed in the FROM clause of another
3247 SELECT statement.
3249 Given a SELECT statement such as::
3251 stmt = select(table.c.id, table.c.name)
3253 The above statement might look like::
3255 SELECT table.id, table.name FROM table
3257 The subquery form by itself renders the same way, however when
3258 embedded into the FROM clause of another SELECT statement, it becomes
3259 a named sub-element::
3261 subq = stmt.subquery()
3262 new_stmt = select(subq)
3264 The above renders as::
3266 SELECT anon_1.id, anon_1.name
3267 FROM (SELECT table.id, table.name FROM table) AS anon_1
3269 Historically, :meth:`_expression.SelectBase.subquery`
3270 is equivalent to calling
3271 the :meth:`_expression.FromClause.alias`
3272 method on a FROM object; however,
3273 as a :class:`_expression.SelectBase`
3274 object is not directly FROM object,
3275 the :meth:`_expression.SelectBase.subquery`
3276 method provides clearer semantics.
3278 .. versionadded:: 1.4
3280 """
3282 return Subquery._construct(self._ensure_disambiguated_names(), name)
3284 def _ensure_disambiguated_names(self):
3285 """Ensure that the names generated by this selectbase will be
3286 disambiguated in some way, if possible.
3288 """
3290 raise NotImplementedError()
3292 def alias(self, name=None, flat=False):
3293 """Return a named subquery against this
3294 :class:`_expression.SelectBase`.
3296 For a :class:`_expression.SelectBase` (as opposed to a
3297 :class:`_expression.FromClause`),
3298 this returns a :class:`.Subquery` object which behaves mostly the
3299 same as the :class:`_expression.Alias` object that is used with a
3300 :class:`_expression.FromClause`.
3302 .. versionchanged:: 1.4 The :meth:`_expression.SelectBase.alias`
3303 method is now
3304 a synonym for the :meth:`_expression.SelectBase.subquery` method.
3306 """
3307 return self.subquery(name=name)
3310class SelectStatementGrouping(GroupedElement, SelectBase):
3311 """Represent a grouping of a :class:`_expression.SelectBase`.
3313 This differs from :class:`.Subquery` in that we are still
3314 an "inner" SELECT statement, this is strictly for grouping inside of
3315 compound selects.
3317 """
3319 __visit_name__ = "select_statement_grouping"
3320 _traverse_internals = [("element", InternalTraversal.dp_clauseelement)]
3322 _is_select_container = True
3324 def __init__(self, element):
3325 self.element = coercions.expect(roles.SelectStatementRole, element)
3327 def _ensure_disambiguated_names(self):
3328 new_element = self.element._ensure_disambiguated_names()
3329 if new_element is not self.element:
3330 return SelectStatementGrouping(new_element)
3331 else:
3332 return self
3334 def get_label_style(self):
3335 return self._label_style
3337 def set_label_style(self, label_style):
3338 return SelectStatementGrouping(
3339 self.element.set_label_style(label_style)
3340 )
3342 @property
3343 def _label_style(self):
3344 return self.element._label_style
3346 @property
3347 def select_statement(self):
3348 return self.element
3350 def self_group(self, against=None):
3351 return self
3353 def _generate_columns_plus_names(self, anon_for_dupe_key):
3354 return self.element._generate_columns_plus_names(anon_for_dupe_key)
3356 def _generate_fromclause_column_proxies(self, subquery):
3357 self.element._generate_fromclause_column_proxies(subquery)
3359 def _generate_proxy_for_new_column(self, column, subquery):
3360 return self.element._generate_proxy_for_new_column(subquery)
3362 @property
3363 def _all_selected_columns(self):
3364 return self.element._all_selected_columns
3366 @property
3367 def selected_columns(self):
3368 """A :class:`_expression.ColumnCollection`
3369 representing the columns that
3370 the embedded SELECT statement returns in its result set, not including
3371 :class:`_sql.TextClause` constructs.
3373 .. versionadded:: 1.4
3375 .. seealso::
3377 :attr:`_sql.Select.selected_columns`
3379 """
3380 return self.element.selected_columns
3382 @property
3383 def _from_objects(self):
3384 return self.element._from_objects
3387class DeprecatedSelectBaseGenerations(object):
3388 """A collection of methods available on :class:`_sql.Select` and
3389 :class:`_sql.CompoundSelect`, these are all **deprecated** methods as they
3390 modify the object in-place.
3392 """
3394 @util.deprecated(
3395 "1.4",
3396 "The :meth:`_expression.GenerativeSelect.append_order_by` "
3397 "method is deprecated "
3398 "and will be removed in a future release. Use the generative method "
3399 ":meth:`_expression.GenerativeSelect.order_by`.",
3400 )
3401 def append_order_by(self, *clauses):
3402 """Append the given ORDER BY criterion applied to this selectable.
3404 The criterion will be appended to any pre-existing ORDER BY criterion.
3406 This is an **in-place** mutation method; the
3407 :meth:`_expression.GenerativeSelect.order_by` method is preferred,
3408 as it
3409 provides standard :term:`method chaining`.
3411 .. seealso::
3413 :meth:`_expression.GenerativeSelect.order_by`
3415 """
3416 self.order_by.non_generative(self, *clauses)
3418 @util.deprecated(
3419 "1.4",
3420 "The :meth:`_expression.GenerativeSelect.append_group_by` "
3421 "method is deprecated "
3422 "and will be removed in a future release. Use the generative method "
3423 ":meth:`_expression.GenerativeSelect.group_by`.",
3424 )
3425 def append_group_by(self, *clauses):
3426 """Append the given GROUP BY criterion applied to this selectable.
3428 The criterion will be appended to any pre-existing GROUP BY criterion.
3430 This is an **in-place** mutation method; the
3431 :meth:`_expression.GenerativeSelect.group_by` method is preferred,
3432 as it
3433 provides standard :term:`method chaining`.
3436 """
3437 self.group_by.non_generative(self, *clauses)
3440class GenerativeSelect(DeprecatedSelectBaseGenerations, SelectBase):
3441 """Base class for SELECT statements where additional elements can be
3442 added.
3444 This serves as the base for :class:`_expression.Select` and
3445 :class:`_expression.CompoundSelect`
3446 where elements such as ORDER BY, GROUP BY can be added and column
3447 rendering can be controlled. Compare to
3448 :class:`_expression.TextualSelect`, which,
3449 while it subclasses :class:`_expression.SelectBase`
3450 and is also a SELECT construct,
3451 represents a fixed textual string which cannot be altered at this level,
3452 only wrapped as a subquery.
3454 """
3456 _order_by_clauses = ()
3457 _group_by_clauses = ()
3458 _limit_clause = None
3459 _offset_clause = None
3460 _fetch_clause = None
3461 _fetch_clause_options = None
3462 _for_update_arg = None
3464 @util.deprecated_params(
3465 bind=(
3466 "2.0",
3467 "The :paramref:`_sql.select.bind` argument is deprecated and "
3468 "will be removed in SQLAlchemy 2.0.",
3469 ),
3470 )
3471 def __init__(
3472 self,
3473 _label_style=LABEL_STYLE_DEFAULT,
3474 use_labels=False,
3475 limit=None,
3476 offset=None,
3477 order_by=None,
3478 group_by=None,
3479 bind=None,
3480 ):
3481 if use_labels:
3482 if util.SQLALCHEMY_WARN_20:
3483 util.warn_deprecated_20(
3484 "The use_labels=True keyword argument to GenerativeSelect "
3485 "is deprecated and will be removed in version 2.0. Please "
3486 "use "
3487 "select.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) "
3488 "if you need to replicate this legacy behavior.",
3489 stacklevel=4,
3490 )
3491 _label_style = LABEL_STYLE_TABLENAME_PLUS_COL
3493 self._label_style = _label_style
3495 if limit is not None:
3496 self.limit.non_generative(self, limit)
3497 if offset is not None:
3498 self.offset.non_generative(self, offset)
3500 if order_by is not None:
3501 self.order_by.non_generative(self, *util.to_list(order_by))
3502 if group_by is not None:
3503 self.group_by.non_generative(self, *util.to_list(group_by))
3505 self._bind = bind
3507 @_generative
3508 def with_for_update(
3509 self,
3510 nowait=False,
3511 read=False,
3512 of=None,
3513 skip_locked=False,
3514 key_share=False,
3515 ):
3516 """Specify a ``FOR UPDATE`` clause for this
3517 :class:`_expression.GenerativeSelect`.
3519 E.g.::
3521 stmt = select(table).with_for_update(nowait=True)
3523 On a database like PostgreSQL or Oracle, the above would render a
3524 statement like::
3526 SELECT table.a, table.b FROM table FOR UPDATE NOWAIT
3528 on other backends, the ``nowait`` option is ignored and instead
3529 would produce::
3531 SELECT table.a, table.b FROM table FOR UPDATE
3533 When called with no arguments, the statement will render with
3534 the suffix ``FOR UPDATE``. Additional arguments can then be
3535 provided which allow for common database-specific
3536 variants.
3538 :param nowait: boolean; will render ``FOR UPDATE NOWAIT`` on Oracle
3539 and PostgreSQL dialects.
3541 :param read: boolean; will render ``LOCK IN SHARE MODE`` on MySQL,
3542 ``FOR SHARE`` on PostgreSQL. On PostgreSQL, when combined with
3543 ``nowait``, will render ``FOR SHARE NOWAIT``.
3545 :param of: SQL expression or list of SQL expression elements
3546 (typically :class:`_schema.Column`
3547 objects or a compatible expression) which
3548 will render into a ``FOR UPDATE OF`` clause; supported by PostgreSQL
3549 and Oracle. May render as a table or as a column depending on
3550 backend.
3552 :param skip_locked: boolean, will render ``FOR UPDATE SKIP LOCKED``
3553 on Oracle and PostgreSQL dialects or ``FOR SHARE SKIP LOCKED`` if
3554 ``read=True`` is also specified.
3556 :param key_share: boolean, will render ``FOR NO KEY UPDATE``,
3557 or if combined with ``read=True`` will render ``FOR KEY SHARE``,
3558 on the PostgreSQL dialect.
3560 """
3561 self._for_update_arg = ForUpdateArg(
3562 nowait=nowait,
3563 read=read,
3564 of=of,
3565 skip_locked=skip_locked,
3566 key_share=key_share,
3567 )
3569 def get_label_style(self):
3570 """
3571 Retrieve the current label style.
3573 .. versionadded:: 1.4
3575 """
3576 return self._label_style
3578 def set_label_style(self, style):
3579 """Return a new selectable with the specified label style.
3581 There are three "label styles" available,
3582 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`,
3583 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`, and
3584 :data:`_sql.LABEL_STYLE_NONE`. The default style is
3585 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`.
3587 In modern SQLAlchemy, there is not generally a need to change the
3588 labeling style, as per-expression labels are more effectively used by
3589 making use of the :meth:`_sql.ColumnElement.label` method. In past
3590 versions, :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` was used to
3591 disambiguate same-named columns from different tables, aliases, or
3592 subqueries; the newer :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` now
3593 applies labels only to names that conflict with an existing name so
3594 that the impact of this labeling is minimal.
3596 The rationale for disambiguation is mostly so that all column
3597 expressions are available from a given :attr:`_sql.FromClause.c`
3598 collection when a subquery is created.
3600 .. versionadded:: 1.4 - the
3601 :meth:`_sql.GenerativeSelect.set_label_style` method replaces the
3602 previous combination of ``.apply_labels()``, ``.with_labels()`` and
3603 ``use_labels=True`` methods and/or parameters.
3605 .. seealso::
3607 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`
3609 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`
3611 :data:`_sql.LABEL_STYLE_NONE`
3613 :data:`_sql.LABEL_STYLE_DEFAULT`
3615 """
3616 if self._label_style is not style:
3617 self = self._generate()
3618 self._label_style = style
3619 return self
3621 @util.deprecated_20(
3622 ":meth:`_sql.GenerativeSelect.apply_labels`",
3623 alternative="Use set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) "
3624 "instead.",
3625 )
3626 def apply_labels(self):
3627 return self.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
3629 @property
3630 def _group_by_clause(self):
3631 """ClauseList access to group_by_clauses for legacy dialects"""
3632 return ClauseList._construct_raw(
3633 operators.comma_op, self._group_by_clauses
3634 )
3636 @property
3637 def _order_by_clause(self):
3638 """ClauseList access to order_by_clauses for legacy dialects"""
3639 return ClauseList._construct_raw(
3640 operators.comma_op, self._order_by_clauses
3641 )
3643 def _offset_or_limit_clause(self, element, name=None, type_=None):
3644 """Convert the given value to an "offset or limit" clause.
3646 This handles incoming integers and converts to an expression; if
3647 an expression is already given, it is passed through.
3649 """
3650 return coercions.expect(
3651 roles.LimitOffsetRole, element, name=name, type_=type_
3652 )
3654 def _offset_or_limit_clause_asint(self, clause, attrname):
3655 """Convert the "offset or limit" clause of a select construct to an
3656 integer.
3658 This is only possible if the value is stored as a simple bound
3659 parameter. Otherwise, a compilation error is raised.
3661 """
3662 if clause is None:
3663 return None
3664 try:
3665 value = clause._limit_offset_value
3666 except AttributeError as err:
3667 util.raise_(
3668 exc.CompileError(
3669 "This SELECT structure does not use a simple "
3670 "integer value for %s" % attrname
3671 ),
3672 replace_context=err,
3673 )
3674 else:
3675 return util.asint(value)
3677 @property
3678 def _limit(self):
3679 """Get an integer value for the limit. This should only be used
3680 by code that cannot support a limit as a BindParameter or
3681 other custom clause as it will throw an exception if the limit
3682 isn't currently set to an integer.
3684 """
3685 return self._offset_or_limit_clause_asint(self._limit_clause, "limit")
3687 def _simple_int_clause(self, clause):
3688 """True if the clause is a simple integer, False
3689 if it is not present or is a SQL expression.
3690 """
3691 return isinstance(clause, _OffsetLimitParam)
3693 @property
3694 def _offset(self):
3695 """Get an integer value for the offset. This should only be used
3696 by code that cannot support an offset as a BindParameter or
3697 other custom clause as it will throw an exception if the
3698 offset isn't currently set to an integer.
3700 """
3701 return self._offset_or_limit_clause_asint(
3702 self._offset_clause, "offset"
3703 )
3705 @property
3706 def _has_row_limiting_clause(self):
3707 return (
3708 self._limit_clause is not None
3709 or self._offset_clause is not None
3710 or self._fetch_clause is not None
3711 )
3713 @_generative
3714 def limit(self, limit):
3715 """Return a new selectable with the given LIMIT criterion
3716 applied.
3718 This is a numerical value which usually renders as a ``LIMIT``
3719 expression in the resulting select. Backends that don't
3720 support ``LIMIT`` will attempt to provide similar
3721 functionality.
3723 .. note::
3725 The :meth:`_sql.GenerativeSelect.limit` method will replace
3726 any clause applied with :meth:`_sql.GenerativeSelect.fetch`.
3728 .. versionchanged:: 1.0.0 - :meth:`_expression.Select.limit` can now
3729 accept arbitrary SQL expressions as well as integer values.
3731 :param limit: an integer LIMIT parameter, or a SQL expression
3732 that provides an integer result. Pass ``None`` to reset it.
3734 .. seealso::
3736 :meth:`_sql.GenerativeSelect.fetch`
3738 :meth:`_sql.GenerativeSelect.offset`
3740 """
3742 self._fetch_clause = self._fetch_clause_options = None
3743 self._limit_clause = self._offset_or_limit_clause(limit)
3745 @_generative
3746 def fetch(self, count, with_ties=False, percent=False):
3747 """Return a new selectable with the given FETCH FIRST criterion
3748 applied.
3750 This is a numeric value which usually renders as
3751 ``FETCH {FIRST | NEXT} [ count ] {ROW | ROWS} {ONLY | WITH TIES}``
3752 expression in the resulting select. This functionality is
3753 is currently implemented for Oracle, PostgreSQL, MSSQL.
3755 Use :meth:`_sql.GenerativeSelect.offset` to specify the offset.
3757 .. note::
3759 The :meth:`_sql.GenerativeSelect.fetch` method will replace
3760 any clause applied with :meth:`_sql.GenerativeSelect.limit`.
3762 .. versionadded:: 1.4
3764 :param count: an integer COUNT parameter, or a SQL expression
3765 that provides an integer result. When ``percent=True`` this will
3766 represent the percentage of rows to return, not the absolute value.
3767 Pass ``None`` to reset it.
3769 :param with_ties: When ``True``, the WITH TIES option is used
3770 to return any additional rows that tie for the last place in the
3771 result set according to the ``ORDER BY`` clause. The
3772 ``ORDER BY`` may be mandatory in this case. Defaults to ``False``
3774 :param percent: When ``True``, ``count`` represents the percentage
3775 of the total number of selected rows to return. Defaults to ``False``
3777 .. seealso::
3779 :meth:`_sql.GenerativeSelect.limit`
3781 :meth:`_sql.GenerativeSelect.offset`
3783 """
3785 self._limit_clause = None
3786 if count is None:
3787 self._fetch_clause = self._fetch_clause_options = None
3788 else:
3789 self._fetch_clause = self._offset_or_limit_clause(count)
3790 self._fetch_clause_options = {
3791 "with_ties": with_ties,
3792 "percent": percent,
3793 }
3795 @_generative
3796 def offset(self, offset):
3797 """Return a new selectable with the given OFFSET criterion
3798 applied.
3801 This is a numeric value which usually renders as an ``OFFSET``
3802 expression in the resulting select. Backends that don't
3803 support ``OFFSET`` will attempt to provide similar
3804 functionality.
3807 .. versionchanged:: 1.0.0 - :meth:`_expression.Select.offset` can now
3808 accept arbitrary SQL expressions as well as integer values.
3810 :param offset: an integer OFFSET parameter, or a SQL expression
3811 that provides an integer result. Pass ``None`` to reset it.
3813 .. seealso::
3815 :meth:`_sql.GenerativeSelect.limit`
3817 :meth:`_sql.GenerativeSelect.fetch`
3819 """
3821 self._offset_clause = self._offset_or_limit_clause(offset)
3823 @_generative
3824 @util.preload_module("sqlalchemy.sql.util")
3825 def slice(self, start, stop):
3826 """Apply LIMIT / OFFSET to this statement based on a slice.
3828 The start and stop indices behave like the argument to Python's
3829 built-in :func:`range` function. This method provides an
3830 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
3831 query.
3833 For example, ::
3835 stmt = select(User).order_by(User).id.slice(1, 3)
3837 renders as
3839 .. sourcecode:: sql
3841 SELECT users.id AS users_id,
3842 users.name AS users_name
3843 FROM users ORDER BY users.id
3844 LIMIT ? OFFSET ?
3845 (2, 1)
3847 .. note::
3849 The :meth:`_sql.GenerativeSelect.slice` method will replace
3850 any clause applied with :meth:`_sql.GenerativeSelect.fetch`.
3852 .. versionadded:: 1.4 Added the :meth:`_sql.GenerativeSelect.slice`
3853 method generalized from the ORM.
3855 .. seealso::
3857 :meth:`_sql.GenerativeSelect.limit`
3859 :meth:`_sql.GenerativeSelect.offset`
3861 :meth:`_sql.GenerativeSelect.fetch`
3863 """
3864 sql_util = util.preloaded.sql_util
3865 self._fetch_clause = self._fetch_clause_options = None
3866 self._limit_clause, self._offset_clause = sql_util._make_slice(
3867 self._limit_clause, self._offset_clause, start, stop
3868 )
3870 @_generative
3871 def order_by(self, *clauses):
3872 r"""Return a new selectable with the given list of ORDER BY
3873 criteria applied.
3875 e.g.::
3877 stmt = select(table).order_by(table.c.id, table.c.name)
3879 Calling this method multiple times is equivalent to calling it once
3880 with all the clauses concatenated. All existing ORDER BY criteria may
3881 be cancelled by passing ``None`` by itself. New ORDER BY criteria may
3882 then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::
3884 # will erase all ORDER BY and ORDER BY new_col alone
3885 stmt = stmt.order_by(None).order_by(new_col)
3887 :param \*clauses: a series of :class:`_expression.ColumnElement`
3888 constructs
3889 which will be used to generate an ORDER BY clause.
3891 .. seealso::
3893 :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`
3895 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
3897 """
3899 if len(clauses) == 1 and clauses[0] is None:
3900 self._order_by_clauses = ()
3901 else:
3902 self._order_by_clauses += tuple(
3903 coercions.expect(roles.OrderByRole, clause)
3904 for clause in clauses
3905 )
3907 @_generative
3908 def group_by(self, *clauses):
3909 r"""Return a new selectable with the given list of GROUP BY
3910 criterion applied.
3912 All existing GROUP BY settings can be suppressed by passing ``None``.
3914 e.g.::
3916 stmt = select(table.c.name, func.max(table.c.stat)).\
3917 group_by(table.c.name)
3919 :param \*clauses: a series of :class:`_expression.ColumnElement`
3920 constructs
3921 which will be used to generate an GROUP BY clause.
3923 .. seealso::
3925 :ref:`tutorial_group_by_w_aggregates` - in the
3926 :ref:`unified_tutorial`
3928 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
3930 """
3932 if len(clauses) == 1 and clauses[0] is None:
3933 self._group_by_clauses = ()
3934 else:
3935 self._group_by_clauses += tuple(
3936 coercions.expect(roles.GroupByRole, clause)
3937 for clause in clauses
3938 )
3941@CompileState.plugin_for("default", "compound_select")
3942class CompoundSelectState(CompileState):
3943 @util.memoized_property
3944 def _label_resolve_dict(self):
3945 # TODO: this is hacky and slow
3946 hacky_subquery = self.statement.subquery()
3947 hacky_subquery.named_with_column = False
3948 d = dict((c.key, c) for c in hacky_subquery.c)
3949 return d, d, d
3952class CompoundSelect(HasCompileState, GenerativeSelect):
3953 """Forms the basis of ``UNION``, ``UNION ALL``, and other
3954 SELECT-based set operations.
3957 .. seealso::
3959 :func:`_expression.union`
3961 :func:`_expression.union_all`
3963 :func:`_expression.intersect`
3965 :func:`_expression.intersect_all`
3967 :func:`_expression.except`
3969 :func:`_expression.except_all`
3971 """
3973 __visit_name__ = "compound_select"
3975 _traverse_internals = [
3976 ("selects", InternalTraversal.dp_clauseelement_list),
3977 ("_limit_clause", InternalTraversal.dp_clauseelement),
3978 ("_offset_clause", InternalTraversal.dp_clauseelement),
3979 ("_fetch_clause", InternalTraversal.dp_clauseelement),
3980 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),
3981 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),
3982 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),
3983 ("_for_update_arg", InternalTraversal.dp_clauseelement),
3984 ("keyword", InternalTraversal.dp_string),
3985 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals
3987 UNION = util.symbol("UNION")
3988 UNION_ALL = util.symbol("UNION ALL")
3989 EXCEPT = util.symbol("EXCEPT")
3990 EXCEPT_ALL = util.symbol("EXCEPT ALL")
3991 INTERSECT = util.symbol("INTERSECT")
3992 INTERSECT_ALL = util.symbol("INTERSECT ALL")
3994 _is_from_container = True
3996 def __init__(self, keyword, *selects, **kwargs):
3997 self._auto_correlate = kwargs.pop("correlate", False)
3998 self.keyword = keyword
3999 self.selects = [
4000 coercions.expect(roles.CompoundElementRole, s).self_group(
4001 against=self
4002 )
4003 for s in selects
4004 ]
4006 if kwargs and util.SQLALCHEMY_WARN_20:
4007 util.warn_deprecated_20(
4008 "Set functions such as union(), union_all(), extract(), etc. "
4009 "in SQLAlchemy 2.0 will accept a "
4010 "series of SELECT statements only. "
4011 "Please use generative methods such as order_by() for "
4012 "additional modifications to this CompoundSelect.",
4013 stacklevel=4,
4014 )
4016 GenerativeSelect.__init__(self, **kwargs)
4018 @classmethod
4019 def _create_union(cls, *selects, **kwargs):
4020 r"""Return a ``UNION`` of multiple selectables.
4022 The returned object is an instance of
4023 :class:`_expression.CompoundSelect`.
4025 A similar :func:`union()` method is available on all
4026 :class:`_expression.FromClause` subclasses.
4028 :param \*selects:
4029 a list of :class:`_expression.Select` instances.
4031 :param \**kwargs:
4032 available keyword arguments are the same as those of
4033 :func:`select`.
4035 """
4036 return CompoundSelect(CompoundSelect.UNION, *selects, **kwargs)
4038 @classmethod
4039 def _create_union_all(cls, *selects, **kwargs):
4040 r"""Return a ``UNION ALL`` of multiple selectables.
4042 The returned object is an instance of
4043 :class:`_expression.CompoundSelect`.
4045 A similar :func:`union_all()` method is available on all
4046 :class:`_expression.FromClause` subclasses.
4048 :param \*selects:
4049 a list of :class:`_expression.Select` instances.
4051 :param \**kwargs:
4052 available keyword arguments are the same as those of
4053 :func:`select`.
4055 """
4056 return CompoundSelect(CompoundSelect.UNION_ALL, *selects, **kwargs)
4058 @classmethod
4059 def _create_except(cls, *selects, **kwargs):
4060 r"""Return an ``EXCEPT`` of multiple selectables.
4062 The returned object is an instance of
4063 :class:`_expression.CompoundSelect`.
4065 :param \*selects:
4066 a list of :class:`_expression.Select` instances.
4068 :param \**kwargs:
4069 available keyword arguments are the same as those of
4070 :func:`select`.
4072 """
4073 return CompoundSelect(CompoundSelect.EXCEPT, *selects, **kwargs)
4075 @classmethod
4076 def _create_except_all(cls, *selects, **kwargs):
4077 r"""Return an ``EXCEPT ALL`` of multiple selectables.
4079 The returned object is an instance of
4080 :class:`_expression.CompoundSelect`.
4082 :param \*selects:
4083 a list of :class:`_expression.Select` instances.
4085 :param \**kwargs:
4086 available keyword arguments are the same as those of
4087 :func:`select`.
4089 """
4090 return CompoundSelect(CompoundSelect.EXCEPT_ALL, *selects, **kwargs)
4092 @classmethod
4093 def _create_intersect(cls, *selects, **kwargs):
4094 r"""Return an ``INTERSECT`` of multiple selectables.
4096 The returned object is an instance of
4097 :class:`_expression.CompoundSelect`.
4099 :param \*selects:
4100 a list of :class:`_expression.Select` instances.
4102 :param \**kwargs:
4103 available keyword arguments are the same as those of
4104 :func:`select`.
4106 """
4107 return CompoundSelect(CompoundSelect.INTERSECT, *selects, **kwargs)
4109 @classmethod
4110 def _create_intersect_all(cls, *selects, **kwargs):
4111 r"""Return an ``INTERSECT ALL`` of multiple selectables.
4113 The returned object is an instance of
4114 :class:`_expression.CompoundSelect`.
4116 :param \*selects:
4117 a list of :class:`_expression.Select` instances.
4119 :param \**kwargs:
4120 available keyword arguments are the same as those of
4121 :func:`select`.
4123 """
4124 return CompoundSelect(CompoundSelect.INTERSECT_ALL, *selects, **kwargs)
4126 def _scalar_type(self):
4127 return self.selects[0]._scalar_type()
4129 def self_group(self, against=None):
4130 return SelectStatementGrouping(self)
4132 def is_derived_from(self, fromclause):
4133 for s in self.selects:
4134 if s.is_derived_from(fromclause):
4135 return True
4136 return False
4138 def _set_label_style(self, style):
4139 if self._label_style is not style:
4140 self = self._generate()
4141 select_0 = self.selects[0]._set_label_style(style)
4142 self.selects = [select_0] + self.selects[1:]
4144 return self
4146 def _ensure_disambiguated_names(self):
4147 new_select = self.selects[0]._ensure_disambiguated_names()
4148 if new_select is not self.selects[0]:
4149 self = self._generate()
4150 self.selects = [new_select] + self.selects[1:]
4152 return self
4154 def _generate_fromclause_column_proxies(self, subquery):
4156 # this is a slightly hacky thing - the union exports a
4157 # column that resembles just that of the *first* selectable.
4158 # to get at a "composite" column, particularly foreign keys,
4159 # you have to dig through the proxies collection which we
4160 # generate below. We may want to improve upon this, such as
4161 # perhaps _make_proxy can accept a list of other columns
4162 # that are "shared" - schema.column can then copy all the
4163 # ForeignKeys in. this would allow the union() to have all
4164 # those fks too.
4165 select_0 = self.selects[0]
4167 if self._label_style is not LABEL_STYLE_DEFAULT:
4168 select_0 = select_0.set_label_style(self._label_style)
4169 select_0._generate_fromclause_column_proxies(subquery)
4171 # hand-construct the "_proxies" collection to include all
4172 # derived columns place a 'weight' annotation corresponding
4173 # to how low in the list of select()s the column occurs, so
4174 # that the corresponding_column() operation can resolve
4175 # conflicts
4177 for subq_col, select_cols in zip(
4178 subquery.c._all_columns,
4179 zip(*[s.selected_columns for s in self.selects]),
4180 ):
4181 subq_col._proxies = [
4182 c._annotate({"weight": i + 1})
4183 for (i, c) in enumerate(select_cols)
4184 ]
4186 def _refresh_for_new_column(self, column):
4187 super(CompoundSelect, self)._refresh_for_new_column(column)
4188 for select in self.selects:
4189 select._refresh_for_new_column(column)
4191 @property
4192 def _all_selected_columns(self):
4193 return self.selects[0]._all_selected_columns
4195 @property
4196 def selected_columns(self):
4197 """A :class:`_expression.ColumnCollection`
4198 representing the columns that
4199 this SELECT statement or similar construct returns in its result set,
4200 not including :class:`_sql.TextClause` constructs.
4202 For a :class:`_expression.CompoundSelect`, the
4203 :attr:`_expression.CompoundSelect.selected_columns`
4204 attribute returns the selected
4205 columns of the first SELECT statement contained within the series of
4206 statements within the set operation.
4208 .. seealso::
4210 :attr:`_sql.Select.selected_columns`
4212 .. versionadded:: 1.4
4214 """
4215 return self.selects[0].selected_columns
4217 @property
4218 @util.deprecated_20(
4219 ":attr:`.Executable.bind`",
4220 alternative="Bound metadata is being removed as of SQLAlchemy 2.0.",
4221 enable_warnings=False,
4222 )
4223 def bind(self):
4224 """Returns the :class:`_engine.Engine` or :class:`_engine.Connection`
4225 to which this :class:`.Executable` is bound, or None if none found.
4227 """
4228 if self._bind:
4229 return self._bind
4230 for s in self.selects:
4231 e = s.bind
4232 if e:
4233 return e
4234 else:
4235 return None
4237 @bind.setter
4238 def bind(self, bind):
4239 self._bind = bind
4242class DeprecatedSelectGenerations(object):
4243 """A collection of methods available on :class:`_sql.Select`, these
4244 are all **deprecated** methods as they modify the :class:`_sql.Select`
4245 object in -place.
4247 """
4249 @util.deprecated(
4250 "1.4",
4251 "The :meth:`_expression.Select.append_correlation` "
4252 "method is deprecated "
4253 "and will be removed in a future release. Use the generative "
4254 "method :meth:`_expression.Select.correlate`.",
4255 )
4256 def append_correlation(self, fromclause):
4257 """Append the given correlation expression to this select()
4258 construct.
4260 This is an **in-place** mutation method; the
4261 :meth:`_expression.Select.correlate` method is preferred,
4262 as it provides
4263 standard :term:`method chaining`.
4265 """
4267 self.correlate.non_generative(self, fromclause)
4269 @util.deprecated(
4270 "1.4",
4271 "The :meth:`_expression.Select.append_column` method is deprecated "
4272 "and will be removed in a future release. Use the generative "
4273 "method :meth:`_expression.Select.add_columns`.",
4274 )
4275 def append_column(self, column):
4276 """Append the given column expression to the columns clause of this
4277 select() construct.
4279 E.g.::
4281 my_select.append_column(some_table.c.new_column)
4283 This is an **in-place** mutation method; the
4284 :meth:`_expression.Select.add_columns` method is preferred,
4285 as it provides standard
4286 :term:`method chaining`.
4288 """
4289 self.add_columns.non_generative(self, column)
4291 @util.deprecated(
4292 "1.4",
4293 "The :meth:`_expression.Select.append_prefix` method is deprecated "
4294 "and will be removed in a future release. Use the generative "
4295 "method :meth:`_expression.Select.prefix_with`.",
4296 )
4297 def append_prefix(self, clause):
4298 """Append the given columns clause prefix expression to this select()
4299 construct.
4301 This is an **in-place** mutation method; the
4302 :meth:`_expression.Select.prefix_with` method is preferred,
4303 as it provides
4304 standard :term:`method chaining`.
4306 """
4307 self.prefix_with.non_generative(self, clause)
4309 @util.deprecated(
4310 "1.4",
4311 "The :meth:`_expression.Select.append_whereclause` "
4312 "method is deprecated "
4313 "and will be removed in a future release. Use the generative "
4314 "method :meth:`_expression.Select.where`.",
4315 )
4316 def append_whereclause(self, whereclause):
4317 """Append the given expression to this select() construct's WHERE
4318 criterion.
4320 The expression will be joined to existing WHERE criterion via AND.
4322 This is an **in-place** mutation method; the
4323 :meth:`_expression.Select.where` method is preferred,
4324 as it provides standard
4325 :term:`method chaining`.
4327 """
4328 self.where.non_generative(self, whereclause)
4330 @util.deprecated(
4331 "1.4",
4332 "The :meth:`_expression.Select.append_having` method is deprecated "
4333 "and will be removed in a future release. Use the generative "
4334 "method :meth:`_expression.Select.having`.",
4335 )
4336 def append_having(self, having):
4337 """Append the given expression to this select() construct's HAVING
4338 criterion.
4340 The expression will be joined to existing HAVING criterion via AND.
4342 This is an **in-place** mutation method; the
4343 :meth:`_expression.Select.having` method is preferred,
4344 as it provides standard
4345 :term:`method chaining`.
4347 """
4349 self.having.non_generative(self, having)
4351 @util.deprecated(
4352 "1.4",
4353 "The :meth:`_expression.Select.append_from` method is deprecated "
4354 "and will be removed in a future release. Use the generative "
4355 "method :meth:`_expression.Select.select_from`.",
4356 )
4357 def append_from(self, fromclause):
4358 """Append the given :class:`_expression.FromClause` expression
4359 to this select() construct's FROM clause.
4361 This is an **in-place** mutation method; the
4362 :meth:`_expression.Select.select_from` method is preferred,
4363 as it provides
4364 standard :term:`method chaining`.
4366 """
4367 self.select_from.non_generative(self, fromclause)
4370@CompileState.plugin_for("default", "select")
4371class SelectState(util.MemoizedSlots, CompileState):
4372 __slots__ = (
4373 "from_clauses",
4374 "froms",
4375 "columns_plus_names",
4376 "_label_resolve_dict",
4377 )
4379 class default_select_compile_options(CacheableOptions):
4380 _cache_key_traversal = []
4382 def __init__(self, statement, compiler, **kw):
4383 self.statement = statement
4384 self.from_clauses = statement._from_obj
4386 for memoized_entities in statement._memoized_select_entities:
4387 self._setup_joins(
4388 memoized_entities._setup_joins, memoized_entities._raw_columns
4389 )
4391 if statement._setup_joins:
4392 self._setup_joins(statement._setup_joins, statement._raw_columns)
4394 self.froms = self._get_froms(statement)
4396 self.columns_plus_names = statement._generate_columns_plus_names(True)
4398 @classmethod
4399 def _plugin_not_implemented(cls):
4400 raise NotImplementedError(
4401 "The default SELECT construct without plugins does not "
4402 "implement this method."
4403 )
4405 @classmethod
4406 def get_column_descriptions(cls, statement):
4407 return [
4408 {
4409 "name": name,
4410 "type": element.type,
4411 "expr": element,
4412 }
4413 for _, name, _, element, _ in (
4414 statement._generate_columns_plus_names(False)
4415 )
4416 ]
4418 @classmethod
4419 def from_statement(cls, statement, from_statement):
4420 cls._plugin_not_implemented()
4422 @classmethod
4423 def get_columns_clause_froms(cls, statement):
4424 return cls._normalize_froms(
4425 itertools.chain.from_iterable(
4426 element._from_objects for element in statement._raw_columns
4427 )
4428 )
4430 @classmethod
4431 def _column_naming_convention(cls, label_style):
4433 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL
4434 dedupe = label_style is not LABEL_STYLE_NONE
4436 pa = prefix_anon_map()
4437 names = set()
4439 def go(c, col_name=None):
4440 if c._is_text_clause:
4441 return None
4443 elif not dedupe:
4444 name = c._proxy_key
4445 if name is None:
4446 name = "_no_label"
4447 return name
4449 name = c._tq_key_label if table_qualified else c._proxy_key
4451 if name is None:
4452 name = "_no_label"
4453 if name in names:
4454 return c._anon_label(name) % pa
4455 else:
4456 names.add(name)
4457 return name
4459 elif name in names:
4460 return (
4461 c._anon_tq_key_label % pa
4462 if table_qualified
4463 else c._anon_key_label % pa
4464 )
4465 else:
4466 names.add(name)
4467 return name
4469 return go
4471 def _get_froms(self, statement):
4472 return self._normalize_froms(
4473 itertools.chain(
4474 itertools.chain.from_iterable(
4475 [
4476 element._from_objects
4477 for element in statement._raw_columns
4478 ]
4479 ),
4480 itertools.chain.from_iterable(
4481 [
4482 element._from_objects
4483 for element in statement._where_criteria
4484 ]
4485 ),
4486 self.from_clauses,
4487 ),
4488 check_statement=statement,
4489 )
4491 @classmethod
4492 def _normalize_froms(cls, iterable_of_froms, check_statement=None):
4493 """given an iterable of things to select FROM, reduce them to what
4494 would actually render in the FROM clause of a SELECT.
4496 This does the job of checking for JOINs, tables, etc. that are in fact
4497 overlapping due to cloning, adaption, present in overlapping joins,
4498 etc.
4500 """
4501 seen = set()
4502 froms = []
4504 for item in iterable_of_froms:
4505 if item._is_subquery and item.element is check_statement:
4506 raise exc.InvalidRequestError(
4507 "select() construct refers to itself as a FROM"
4508 )
4510 if not seen.intersection(item._cloned_set):
4511 froms.append(item)
4512 seen.update(item._cloned_set)
4514 if froms:
4515 toremove = set(
4516 itertools.chain.from_iterable(
4517 [_expand_cloned(f._hide_froms) for f in froms]
4518 )
4519 )
4520 if toremove:
4521 # filter out to FROM clauses not in the list,
4522 # using a list to maintain ordering
4523 froms = [f for f in froms if f not in toremove]
4525 return froms
4527 def _get_display_froms(
4528 self, explicit_correlate_froms=None, implicit_correlate_froms=None
4529 ):
4530 """Return the full list of 'from' clauses to be displayed.
4532 Takes into account a set of existing froms which may be
4533 rendered in the FROM clause of enclosing selects; this Select
4534 may want to leave those absent if it is automatically
4535 correlating.
4537 """
4539 froms = self.froms
4541 if self.statement._correlate:
4542 to_correlate = self.statement._correlate
4543 if to_correlate:
4544 froms = [
4545 f
4546 for f in froms
4547 if f
4548 not in _cloned_intersection(
4549 _cloned_intersection(
4550 froms, explicit_correlate_froms or ()
4551 ),
4552 to_correlate,
4553 )
4554 ]
4556 if self.statement._correlate_except is not None:
4558 froms = [
4559 f
4560 for f in froms
4561 if f
4562 not in _cloned_difference(
4563 _cloned_intersection(
4564 froms, explicit_correlate_froms or ()
4565 ),
4566 self.statement._correlate_except,
4567 )
4568 ]
4570 if (
4571 self.statement._auto_correlate
4572 and implicit_correlate_froms
4573 and len(froms) > 1
4574 ):
4576 froms = [
4577 f
4578 for f in froms
4579 if f
4580 not in _cloned_intersection(froms, implicit_correlate_froms)
4581 ]
4583 if not len(froms):
4584 raise exc.InvalidRequestError(
4585 "Select statement '%r"
4586 "' returned no FROM clauses "
4587 "due to auto-correlation; "
4588 "specify correlate(<tables>) "
4589 "to control correlation "
4590 "manually." % self.statement
4591 )
4593 return froms
4595 def _memoized_attr__label_resolve_dict(self):
4596 with_cols = dict(
4597 (c._tq_label or c.key, c)
4598 for c in self.statement._all_selected_columns
4599 if c._allow_label_resolve
4600 )
4601 only_froms = dict(
4602 (c.key, c)
4603 for c in _select_iterables(self.froms)
4604 if c._allow_label_resolve
4605 )
4606 only_cols = with_cols.copy()
4607 for key, value in only_froms.items():
4608 with_cols.setdefault(key, value)
4610 return with_cols, only_froms, only_cols
4612 @classmethod
4613 def determine_last_joined_entity(cls, stmt):
4614 if stmt._setup_joins:
4615 return stmt._setup_joins[-1][0]
4616 else:
4617 return None
4619 @classmethod
4620 def all_selected_columns(cls, statement):
4621 return [c for c in _select_iterables(statement._raw_columns)]
4623 def _setup_joins(self, args, raw_columns):
4624 for (right, onclause, left, flags) in args:
4625 isouter = flags["isouter"]
4626 full = flags["full"]
4628 if left is None:
4629 (
4630 left,
4631 replace_from_obj_index,
4632 ) = self._join_determine_implicit_left_side(
4633 raw_columns, left, right, onclause
4634 )
4635 else:
4636 (replace_from_obj_index) = self._join_place_explicit_left_side(
4637 left
4638 )
4640 if replace_from_obj_index is not None:
4641 # splice into an existing element in the
4642 # self._from_obj list
4643 left_clause = self.from_clauses[replace_from_obj_index]
4645 self.from_clauses = (
4646 self.from_clauses[:replace_from_obj_index]
4647 + (
4648 Join(
4649 left_clause,
4650 right,
4651 onclause,
4652 isouter=isouter,
4653 full=full,
4654 ),
4655 )
4656 + self.from_clauses[replace_from_obj_index + 1 :]
4657 )
4658 else:
4660 self.from_clauses = self.from_clauses + (
4661 Join(left, right, onclause, isouter=isouter, full=full),
4662 )
4664 @util.preload_module("sqlalchemy.sql.util")
4665 def _join_determine_implicit_left_side(
4666 self, raw_columns, left, right, onclause
4667 ):
4668 """When join conditions don't express the left side explicitly,
4669 determine if an existing FROM or entity in this query
4670 can serve as the left hand side.
4672 """
4674 sql_util = util.preloaded.sql_util
4676 replace_from_obj_index = None
4678 from_clauses = self.from_clauses
4680 if from_clauses:
4682 indexes = sql_util.find_left_clause_to_join_from(
4683 from_clauses, right, onclause
4684 )
4686 if len(indexes) == 1:
4687 replace_from_obj_index = indexes[0]
4688 left = from_clauses[replace_from_obj_index]
4689 else:
4690 potential = {}
4691 statement = self.statement
4693 for from_clause in itertools.chain(
4694 itertools.chain.from_iterable(
4695 [element._from_objects for element in raw_columns]
4696 ),
4697 itertools.chain.from_iterable(
4698 [
4699 element._from_objects
4700 for element in statement._where_criteria
4701 ]
4702 ),
4703 ):
4705 potential[from_clause] = ()
4707 all_clauses = list(potential.keys())
4708 indexes = sql_util.find_left_clause_to_join_from(
4709 all_clauses, right, onclause
4710 )
4712 if len(indexes) == 1:
4713 left = all_clauses[indexes[0]]
4715 if len(indexes) > 1:
4716 raise exc.InvalidRequestError(
4717 "Can't determine which FROM clause to join "
4718 "from, there are multiple FROMS which can "
4719 "join to this entity. Please use the .select_from() "
4720 "method to establish an explicit left side, as well as "
4721 "providing an explicit ON clause if not present already to "
4722 "help resolve the ambiguity."
4723 )
4724 elif not indexes:
4725 raise exc.InvalidRequestError(
4726 "Don't know how to join to %r. "
4727 "Please use the .select_from() "
4728 "method to establish an explicit left side, as well as "
4729 "providing an explicit ON clause if not present already to "
4730 "help resolve the ambiguity." % (right,)
4731 )
4732 return left, replace_from_obj_index
4734 @util.preload_module("sqlalchemy.sql.util")
4735 def _join_place_explicit_left_side(self, left):
4736 replace_from_obj_index = None
4738 sql_util = util.preloaded.sql_util
4740 from_clauses = list(self.statement._iterate_from_elements())
4742 if from_clauses:
4743 indexes = sql_util.find_left_clause_that_matches_given(
4744 self.from_clauses, left
4745 )
4746 else:
4747 indexes = []
4749 if len(indexes) > 1:
4750 raise exc.InvalidRequestError(
4751 "Can't identify which entity in which to assign the "
4752 "left side of this join. Please use a more specific "
4753 "ON clause."
4754 )
4756 # have an index, means the left side is already present in
4757 # an existing FROM in the self._from_obj tuple
4758 if indexes:
4759 replace_from_obj_index = indexes[0]
4761 # no index, means we need to add a new element to the
4762 # self._from_obj tuple
4764 return replace_from_obj_index
4767class _SelectFromElements(object):
4768 def _iterate_from_elements(self):
4769 # note this does not include elements
4770 # in _setup_joins or _legacy_setup_joins
4772 seen = set()
4773 for element in self._raw_columns:
4774 for fr in element._from_objects:
4775 if fr in seen:
4776 continue
4777 seen.add(fr)
4778 yield fr
4779 for element in self._where_criteria:
4780 for fr in element._from_objects:
4781 if fr in seen:
4782 continue
4783 seen.add(fr)
4784 yield fr
4785 for element in self._from_obj:
4786 if element in seen:
4787 continue
4788 seen.add(element)
4789 yield element
4792class _MemoizedSelectEntities(
4793 traversals.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible
4794):
4795 __visit_name__ = "memoized_select_entities"
4797 _traverse_internals = [
4798 ("_raw_columns", InternalTraversal.dp_clauseelement_list),
4799 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),
4800 ("_legacy_setup_joins", InternalTraversal.dp_setup_join_tuple),
4801 ("_with_options", InternalTraversal.dp_executable_options),
4802 ]
4804 _annotations = util.EMPTY_DICT
4806 def _clone(self, **kw):
4807 c = self.__class__.__new__(self.__class__)
4808 c.__dict__ = {k: v for k, v in self.__dict__.items()}
4810 c._is_clone_of = self.__dict__.get("_is_clone_of", self)
4811 return c
4813 @classmethod
4814 def _generate_for_statement(cls, select_stmt):
4815 if (
4816 select_stmt._setup_joins
4817 or select_stmt._legacy_setup_joins
4818 or select_stmt._with_options
4819 ):
4820 self = _MemoizedSelectEntities()
4821 self._raw_columns = select_stmt._raw_columns
4822 self._setup_joins = select_stmt._setup_joins
4823 self._legacy_setup_joins = select_stmt._legacy_setup_joins
4824 self._with_options = select_stmt._with_options
4826 select_stmt._memoized_select_entities += (self,)
4827 select_stmt._raw_columns = (
4828 select_stmt._setup_joins
4829 ) = (
4830 select_stmt._legacy_setup_joins
4831 ) = select_stmt._with_options = ()
4834class Select(
4835 HasPrefixes,
4836 HasSuffixes,
4837 HasHints,
4838 HasCompileState,
4839 DeprecatedSelectGenerations,
4840 _SelectFromElements,
4841 GenerativeSelect,
4842):
4843 """Represents a ``SELECT`` statement.
4845 The :class:`_sql.Select` object is normally constructed using the
4846 :func:`_sql.select` function. See that function for details.
4848 .. seealso::
4850 :func:`_sql.select`
4852 :ref:`tutorial_selecting_data` - in the 2.0 tutorial
4854 """
4856 __visit_name__ = "select"
4858 _setup_joins = ()
4859 _legacy_setup_joins = ()
4860 _memoized_select_entities = ()
4862 _distinct = False
4863 _distinct_on = ()
4864 _correlate = ()
4865 _correlate_except = None
4866 _where_criteria = ()
4867 _having_criteria = ()
4868 _from_obj = ()
4869 _auto_correlate = True
4871 _compile_options = SelectState.default_select_compile_options
4873 _traverse_internals = (
4874 [
4875 ("_raw_columns", InternalTraversal.dp_clauseelement_list),
4876 (
4877 "_memoized_select_entities",
4878 InternalTraversal.dp_memoized_select_entities,
4879 ),
4880 ("_from_obj", InternalTraversal.dp_clauseelement_list),
4881 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),
4882 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),
4883 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),
4884 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),
4885 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),
4886 ("_legacy_setup_joins", InternalTraversal.dp_setup_join_tuple),
4887 ("_correlate", InternalTraversal.dp_clauseelement_tuple),
4888 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),
4889 ("_limit_clause", InternalTraversal.dp_clauseelement),
4890 ("_offset_clause", InternalTraversal.dp_clauseelement),
4891 ("_fetch_clause", InternalTraversal.dp_clauseelement),
4892 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),
4893 ("_for_update_arg", InternalTraversal.dp_clauseelement),
4894 ("_distinct", InternalTraversal.dp_boolean),
4895 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),
4896 ("_label_style", InternalTraversal.dp_plain_obj),
4897 ]
4898 + HasCTE._has_ctes_traverse_internals
4899 + HasPrefixes._has_prefixes_traverse_internals
4900 + HasSuffixes._has_suffixes_traverse_internals
4901 + HasHints._has_hints_traverse_internals
4902 + SupportsCloneAnnotations._clone_annotations_traverse_internals
4903 + Executable._executable_traverse_internals
4904 )
4906 _cache_key_traversal = _traverse_internals + [
4907 ("_compile_options", InternalTraversal.dp_has_cache_key)
4908 ]
4910 @classmethod
4911 def _create_select_from_fromclause(cls, target, entities, *arg, **kw):
4912 if arg or kw:
4913 return Select.create_legacy_select(entities, *arg, **kw)
4914 else:
4915 return Select._create_select(*entities)
4917 @classmethod
4918 @util.deprecated(
4919 "2.0",
4920 "The legacy calling style of :func:`_sql.select` is deprecated and "
4921 "will be removed in SQLAlchemy 2.0. Please use the new calling "
4922 "style described at :func:`_sql.select`.",
4923 )
4924 def create_legacy_select(
4925 cls,
4926 columns=None,
4927 whereclause=None,
4928 from_obj=None,
4929 distinct=False,
4930 having=None,
4931 correlate=True,
4932 prefixes=None,
4933 suffixes=None,
4934 **kwargs
4935 ):
4936 """Construct a new :class:`_expression.Select` using the 1.x style API.
4938 This method is called implicitly when the :func:`_expression.select`
4939 construct is used and the first argument is a Python list or other
4940 plain sequence object, which is taken to refer to the columns
4941 collection.
4943 .. versionchanged:: 1.4 Added the :meth:`.Select.create_legacy_select`
4944 constructor which documents the calling style in use when the
4945 :func:`.select` construct is invoked using 1.x-style arguments.
4947 Similar functionality is also available via the
4948 :meth:`_expression.FromClause.select` method on any
4949 :class:`_expression.FromClause`.
4951 All arguments which accept :class:`_expression.ClauseElement` arguments
4952 also accept string arguments, which will be converted as appropriate
4953 into either :func:`_expression.text()` or
4954 :func:`_expression.literal_column()` constructs.
4956 .. seealso::
4958 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`
4960 :param columns:
4961 A list of :class:`_expression.ColumnElement` or
4962 :class:`_expression.FromClause`
4963 objects which will form the columns clause of the resulting
4964 statement. For those objects that are instances of
4965 :class:`_expression.FromClause` (typically :class:`_schema.Table`
4966 or :class:`_expression.Alias`
4967 objects), the :attr:`_expression.FromClause.c`
4968 collection is extracted
4969 to form a collection of :class:`_expression.ColumnElement` objects.
4971 This parameter will also accept :class:`_expression.TextClause`
4972 constructs as
4973 given, as well as ORM-mapped classes.
4975 .. note::
4977 The :paramref:`_expression.select.columns`
4978 parameter is not available
4979 in the method form of :func:`_expression.select`, e.g.
4980 :meth:`_expression.FromClause.select`.
4982 .. seealso::
4984 :meth:`_expression.Select.column`
4986 :meth:`_expression.Select.with_only_columns`
4988 :param whereclause:
4989 A :class:`_expression.ClauseElement`
4990 expression which will be used to form the
4991 ``WHERE`` clause. It is typically preferable to add WHERE
4992 criterion to an existing :class:`_expression.Select`
4993 using method chaining
4994 with :meth:`_expression.Select.where`.
4996 .. seealso::
4998 :meth:`_expression.Select.where`
5000 :param from_obj:
5001 A list of :class:`_expression.ClauseElement`
5002 objects which will be added to the
5003 ``FROM`` clause of the resulting statement. This is equivalent
5004 to calling :meth:`_expression.Select.select_from`
5005 using method chaining on
5006 an existing :class:`_expression.Select` object.
5008 .. seealso::
5010 :meth:`_expression.Select.select_from`
5011 - full description of explicit
5012 FROM clause specification.
5014 :param bind=None:
5015 an :class:`_engine.Engine` or :class:`_engine.Connection` instance
5016 to which the
5017 resulting :class:`_expression.Select` object will be bound. The
5018 :class:`_expression.Select`
5019 object will otherwise automatically bind to
5020 whatever :class:`~.base.Connectable` instances can be located within
5021 its contained :class:`_expression.ClauseElement` members.
5023 :param correlate=True:
5024 indicates that this :class:`_expression.Select`
5025 object should have its
5026 contained :class:`_expression.FromClause`
5027 elements "correlated" to an enclosing
5028 :class:`_expression.Select` object.
5029 It is typically preferable to specify
5030 correlations on an existing :class:`_expression.Select`
5031 construct using
5032 :meth:`_expression.Select.correlate`.
5034 .. seealso::
5036 :meth:`_expression.Select.correlate`
5037 - full description of correlation.
5039 :param distinct=False:
5040 when ``True``, applies a ``DISTINCT`` qualifier to the columns
5041 clause of the resulting statement.
5043 The boolean argument may also be a column expression or list
5044 of column expressions - this is a special calling form which
5045 is understood by the PostgreSQL dialect to render the
5046 ``DISTINCT ON (<columns>)`` syntax.
5048 ``distinct`` is also available on an existing
5049 :class:`_expression.Select`
5050 object via the :meth:`_expression.Select.distinct` method.
5052 .. seealso::
5054 :meth:`_expression.Select.distinct`
5056 :param group_by:
5057 a list of :class:`_expression.ClauseElement`
5058 objects which will comprise the
5059 ``GROUP BY`` clause of the resulting select. This parameter
5060 is typically specified more naturally using the
5061 :meth:`_expression.Select.group_by` method on an existing
5062 :class:`_expression.Select`.
5064 .. seealso::
5066 :meth:`_expression.Select.group_by`
5068 :param having:
5069 a :class:`_expression.ClauseElement`
5070 that will comprise the ``HAVING`` clause
5071 of the resulting select when ``GROUP BY`` is used. This parameter
5072 is typically specified more naturally using the
5073 :meth:`_expression.Select.having` method on an existing
5074 :class:`_expression.Select`.
5076 .. seealso::
5078 :meth:`_expression.Select.having`
5080 :param limit=None:
5081 a numerical value which usually renders as a ``LIMIT``
5082 expression in the resulting select. Backends that don't
5083 support ``LIMIT`` will attempt to provide similar
5084 functionality. This parameter is typically specified more
5085 naturally using the :meth:`_expression.Select.limit`
5086 method on an existing
5087 :class:`_expression.Select`.
5089 .. seealso::
5091 :meth:`_expression.Select.limit`
5093 :param offset=None:
5094 a numeric value which usually renders as an ``OFFSET``
5095 expression in the resulting select. Backends that don't
5096 support ``OFFSET`` will attempt to provide similar
5097 functionality. This parameter is typically specified more naturally
5098 using the :meth:`_expression.Select.offset` method on an existing
5099 :class:`_expression.Select`.
5101 .. seealso::
5103 :meth:`_expression.Select.offset`
5105 :param order_by:
5106 a scalar or list of :class:`_expression.ClauseElement`
5107 objects which will
5108 comprise the ``ORDER BY`` clause of the resulting select.
5109 This parameter is typically specified more naturally using the
5110 :meth:`_expression.Select.order_by` method on an existing
5111 :class:`_expression.Select`.
5113 .. seealso::
5115 :meth:`_expression.Select.order_by`
5117 :param use_labels=False:
5118 when ``True``, the statement will be generated using labels
5119 for each column in the columns clause, which qualify each
5120 column with its parent table's (or aliases) name so that name
5121 conflicts between columns in different tables don't occur.
5122 The format of the label is ``<tablename>_<column>``. The "c"
5123 collection of a :class:`_expression.Subquery` created
5124 against this :class:`_expression.Select`
5125 object, as well as the :attr:`_expression.Select.selected_columns`
5126 collection of the :class:`_expression.Select` itself, will use these
5127 names for targeting column members.
5129 This parameter can also be specified on an existing
5130 :class:`_expression.Select` object using the
5131 :meth:`_expression.Select.set_label_style`
5132 method.
5134 .. seealso::
5136 :meth:`_expression.Select.set_label_style`
5138 """
5139 self = cls.__new__(cls)
5141 self._auto_correlate = correlate
5143 if distinct is not False:
5144 if distinct is True:
5145 self.distinct.non_generative(self)
5146 else:
5147 self.distinct.non_generative(self, *util.to_list(distinct))
5149 if from_obj is not None:
5150 self.select_from.non_generative(self, *util.to_list(from_obj))
5152 try:
5153 cols_present = bool(columns)
5154 except TypeError as err:
5155 util.raise_(
5156 exc.ArgumentError(
5157 "select() construct created in legacy mode, i.e. with "
5158 "keyword arguments, must provide the columns argument as "
5159 "a Python list or other iterable.",
5160 code="c9ae",
5161 ),
5162 from_=err,
5163 )
5165 if cols_present:
5166 self._raw_columns = [
5167 coercions.expect(
5168 roles.ColumnsClauseRole, c, apply_propagate_attrs=self
5169 )
5170 for c in columns
5171 ]
5172 else:
5173 self._raw_columns = []
5175 if whereclause is not None:
5176 self.where.non_generative(self, whereclause)
5178 if having is not None:
5179 self.having.non_generative(self, having)
5181 if prefixes:
5182 self._setup_prefixes(prefixes)
5184 if suffixes:
5185 self._setup_suffixes(suffixes)
5187 GenerativeSelect.__init__(self, **kwargs)
5188 return self
5190 @classmethod
5191 def _create_future_select(cls, *entities):
5192 r"""Construct a new :class:`_expression.Select` using the 2.
5193 x style API.
5195 .. versionadded:: 1.4 - The :func:`_sql.select` function now accepts
5196 column arguments positionally. The top-level :func:`_sql.select`
5197 function will automatically use the 1.x or 2.x style API based on
5198 the incoming arguments; using :func:`_future.select` from the
5199 ``sqlalchemy.future`` module will enforce that only the 2.x style
5200 constructor is used.
5202 Similar functionality is also available via the
5203 :meth:`_expression.FromClause.select` method on any
5204 :class:`_expression.FromClause`.
5206 .. seealso::
5208 :ref:`coretutorial_selecting` - Core Tutorial description of
5209 :func:`_expression.select`.
5211 :param \*entities:
5212 Entities to SELECT from. For Core usage, this is typically a series
5213 of :class:`_expression.ColumnElement` and / or
5214 :class:`_expression.FromClause`
5215 objects which will form the columns clause of the resulting
5216 statement. For those objects that are instances of
5217 :class:`_expression.FromClause` (typically :class:`_schema.Table`
5218 or :class:`_expression.Alias`
5219 objects), the :attr:`_expression.FromClause.c`
5220 collection is extracted
5221 to form a collection of :class:`_expression.ColumnElement` objects.
5223 This parameter will also accept :class:`_expression.TextClause`
5224 constructs as
5225 given, as well as ORM-mapped classes.
5227 """
5229 self = cls.__new__(cls)
5230 self._raw_columns = [
5231 coercions.expect(
5232 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self
5233 )
5234 for ent in entities
5235 ]
5237 GenerativeSelect.__init__(self)
5239 return self
5241 _create_select = _create_future_select
5243 @classmethod
5244 def _create_raw_select(cls, **kw):
5245 """Create a :class:`.Select` using raw ``__new__`` with no coercions.
5247 Used internally to build up :class:`.Select` constructs with
5248 pre-established state.
5250 """
5252 stmt = Select.__new__(Select)
5253 stmt.__dict__.update(kw)
5254 return stmt
5256 @classmethod
5257 def _create(cls, *args, **kw):
5258 r"""Create a :class:`.Select` using either the 1.x or 2.0 constructor
5259 style.
5261 For the legacy calling style, see :meth:`.Select.create_legacy_select`.
5262 If the first argument passed is a Python sequence or if keyword
5263 arguments are present, this style is used.
5265 .. versionadded:: 2.0 - the :func:`_future.select` construct is
5266 the same construct as the one returned by
5267 :func:`_expression.select`, except that the function only
5268 accepts the "columns clause" entities up front; the rest of the
5269 state of the SELECT should be built up using generative methods.
5271 Similar functionality is also available via the
5272 :meth:`_expression.FromClause.select` method on any
5273 :class:`_expression.FromClause`.
5275 .. seealso::
5277 :ref:`coretutorial_selecting` - Core Tutorial description of
5278 :func:`_expression.select`.
5280 :param \*entities:
5281 Entities to SELECT from. For Core usage, this is typically a series
5282 of :class:`_expression.ColumnElement` and / or
5283 :class:`_expression.FromClause`
5284 objects which will form the columns clause of the resulting
5285 statement. For those objects that are instances of
5286 :class:`_expression.FromClause` (typically :class:`_schema.Table`
5287 or :class:`_expression.Alias`
5288 objects), the :attr:`_expression.FromClause.c`
5289 collection is extracted
5290 to form a collection of :class:`_expression.ColumnElement` objects.
5292 This parameter will also accept :class:`_expression.TextClause`
5293 constructs as given, as well as ORM-mapped classes.
5295 """
5296 if (
5297 args
5298 and (
5299 isinstance(args[0], list)
5300 or (
5301 hasattr(args[0], "__iter__")
5302 and not isinstance(
5303 args[0], util.string_types + (ClauseElement,)
5304 )
5305 and inspect(args[0], raiseerr=False) is None
5306 and not hasattr(args[0], "__clause_element__")
5307 )
5308 )
5309 ) or kw:
5310 return cls.create_legacy_select(*args, **kw)
5311 else:
5312 return cls._create_future_select(*args)
5314 def __init__(self):
5315 raise NotImplementedError()
5317 def _scalar_type(self):
5318 elem = self._raw_columns[0]
5319 cols = list(elem._select_iterable)
5320 return cols[0].type
5322 def filter(self, *criteria):
5323 """A synonym for the :meth:`_future.Select.where` method."""
5325 return self.where(*criteria)
5327 def _filter_by_zero(self):
5328 if self._setup_joins:
5329 meth = SelectState.get_plugin_class(
5330 self
5331 ).determine_last_joined_entity
5332 _last_joined_entity = meth(self)
5333 if _last_joined_entity is not None:
5334 return _last_joined_entity
5336 if self._from_obj:
5337 return self._from_obj[0]
5339 return self._raw_columns[0]
5341 def filter_by(self, **kwargs):
5342 r"""apply the given filtering criterion as a WHERE clause
5343 to this select.
5345 """
5346 from_entity = self._filter_by_zero()
5348 clauses = [
5349 _entity_namespace_key(from_entity, key) == value
5350 for key, value in kwargs.items()
5351 ]
5352 return self.filter(*clauses)
5354 @property
5355 def column_descriptions(self):
5356 """Return a :term:`plugin-enabled` 'column descriptions' structure
5357 referring to the columns which are SELECTed by this statement.
5359 This attribute is generally useful when using the ORM, as an
5360 extended structure which includes information about mapped
5361 entities is returned. The section :ref:`queryguide_inspection`
5362 contains more background.
5364 For a Core-only statement, the structure returned by this accessor
5365 is derived from the same objects that are returned by the
5366 :attr:`.Select.selected_columns` accessor, formatted as a list of
5367 dictionaries which contain the keys ``name``, ``type`` and ``expr``,
5368 which indicate the column expressions to be selected::
5370 >>> stmt = select(user_table)
5371 >>> stmt.column_descriptions
5372 [
5373 {
5374 'name': 'id',
5375 'type': Integer(),
5376 'expr': Column('id', Integer(), ...)},
5377 {
5378 'name': 'name',
5379 'type': String(length=30),
5380 'expr': Column('name', String(length=30), ...)}
5381 ]
5383 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`
5384 attribute returns a structure for a Core-only set of entities,
5385 not just ORM-only entities.
5387 .. seealso::
5389 :attr:`.UpdateBase.entity_description` - entity information for
5390 an :func:`.insert`, :func:`.update`, or :func:`.delete`
5392 :ref:`queryguide_inspection` - ORM background
5394 """
5395 meth = SelectState.get_plugin_class(self).get_column_descriptions
5396 return meth(self)
5398 def from_statement(self, statement):
5399 """Apply the columns which this :class:`.Select` would select
5400 onto another statement.
5402 This operation is :term:`plugin-specific` and will raise a not
5403 supported exception if this :class:`_sql.Select` does not select from
5404 plugin-enabled entities.
5407 The statement is typically either a :func:`_expression.text` or
5408 :func:`_expression.select` construct, and should return the set of
5409 columns appropriate to the entities represented by this
5410 :class:`.Select`.
5412 .. seealso::
5414 :ref:`orm_queryguide_selecting_text` - usage examples in the
5415 ORM Querying Guide
5417 """
5418 meth = SelectState.get_plugin_class(self).from_statement
5419 return meth(self, statement)
5421 @_generative
5422 def join(self, target, onclause=None, isouter=False, full=False):
5423 r"""Create a SQL JOIN against this :class:`_expression.Select`
5424 object's criterion
5425 and apply generatively, returning the newly resulting
5426 :class:`_expression.Select`.
5428 E.g.::
5430 stmt = select(user_table).join(address_table, user_table.c.id == address_table.c.user_id)
5432 The above statement generates SQL similar to::
5434 SELECT user.id, user.name FROM user JOIN address ON user.id = address.user_id
5436 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates
5437 a :class:`_sql.Join` object between a :class:`_sql.FromClause`
5438 source that is within the FROM clause of the existing SELECT,
5439 and a given target :class:`_sql.FromClause`, and then adds
5440 this :class:`_sql.Join` to the FROM clause of the newly generated
5441 SELECT statement. This is completely reworked from the behavior
5442 in 1.3, which would instead create a subquery of the entire
5443 :class:`_expression.Select` and then join that subquery to the
5444 target.
5446 This is a **backwards incompatible change** as the previous behavior
5447 was mostly useless, producing an unnamed subquery rejected by
5448 most databases in any case. The new behavior is modeled after
5449 that of the very successful :meth:`_orm.Query.join` method in the
5450 ORM, in order to support the functionality of :class:`_orm.Query`
5451 being available by using a :class:`_sql.Select` object with an
5452 :class:`_orm.Session`.
5454 See the notes for this change at :ref:`change_select_join`.
5457 :param target: target table to join towards
5459 :param onclause: ON clause of the join. If omitted, an ON clause
5460 is generated automatically based on the :class:`_schema.ForeignKey`
5461 linkages between the two tables, if one can be unambiguously
5462 determined, otherwise an error is raised.
5464 :param isouter: if True, generate LEFT OUTER join. Same as
5465 :meth:`_expression.Select.outerjoin`.
5467 :param full: if True, generate FULL OUTER join.
5469 .. seealso::
5471 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`
5473 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`
5475 :meth:`_expression.Select.join_from`
5477 :meth:`_expression.Select.outerjoin`
5479 """ # noqa: E501
5480 target = coercions.expect(
5481 roles.JoinTargetRole, target, apply_propagate_attrs=self
5482 )
5483 if onclause is not None:
5484 onclause = coercions.expect(roles.OnClauseRole, onclause)
5485 self._setup_joins += (
5486 (target, onclause, None, {"isouter": isouter, "full": full}),
5487 )
5489 def outerjoin_from(self, from_, target, onclause=None, full=False):
5490 r"""Create a SQL LEFT OUTER JOIN against this
5491 :class:`_expression.Select` object's criterion and apply generatively,
5492 returning the newly resulting :class:`_expression.Select`.
5494 Usage is the same as that of :meth:`_selectable.Select.join_from`.
5496 """
5497 return self.join_from(
5498 from_, target, onclause=onclause, isouter=True, full=full
5499 )
5501 @_generative
5502 def join_from(
5503 self, from_, target, onclause=None, isouter=False, full=False
5504 ):
5505 r"""Create a SQL JOIN against this :class:`_expression.Select`
5506 object's criterion
5507 and apply generatively, returning the newly resulting
5508 :class:`_expression.Select`.
5510 E.g.::
5512 stmt = select(user_table, address_table).join_from(
5513 user_table, address_table, user_table.c.id == address_table.c.user_id
5514 )
5516 The above statement generates SQL similar to::
5518 SELECT user.id, user.name, address.id, address.email, address.user_id
5519 FROM user JOIN address ON user.id = address.user_id
5521 .. versionadded:: 1.4
5523 :param from\_: the left side of the join, will be rendered in the
5524 FROM clause and is roughly equivalent to using the
5525 :meth:`.Select.select_from` method.
5527 :param target: target table to join towards
5529 :param onclause: ON clause of the join.
5531 :param isouter: if True, generate LEFT OUTER join. Same as
5532 :meth:`_expression.Select.outerjoin`.
5534 :param full: if True, generate FULL OUTER join.
5536 .. seealso::
5538 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`
5540 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`
5542 :meth:`_expression.Select.join`
5544 """ # noqa: E501
5546 # note the order of parsing from vs. target is important here, as we
5547 # are also deriving the source of the plugin (i.e. the subject mapper
5548 # in an ORM query) which should favor the "from_" over the "target"
5550 from_ = coercions.expect(
5551 roles.FromClauseRole, from_, apply_propagate_attrs=self
5552 )
5553 target = coercions.expect(
5554 roles.JoinTargetRole, target, apply_propagate_attrs=self
5555 )
5556 if onclause is not None:
5557 onclause = coercions.expect(roles.OnClauseRole, onclause)
5559 self._setup_joins += (
5560 (target, onclause, from_, {"isouter": isouter, "full": full}),
5561 )
5563 def outerjoin(self, target, onclause=None, full=False):
5564 """Create a left outer join.
5566 Parameters are the same as that of :meth:`_expression.Select.join`.
5568 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now
5569 creates a :class:`_sql.Join` object between a
5570 :class:`_sql.FromClause` source that is within the FROM clause of
5571 the existing SELECT, and a given target :class:`_sql.FromClause`,
5572 and then adds this :class:`_sql.Join` to the FROM clause of the
5573 newly generated SELECT statement. This is completely reworked
5574 from the behavior in 1.3, which would instead create a subquery of
5575 the entire
5576 :class:`_expression.Select` and then join that subquery to the
5577 target.
5579 This is a **backwards incompatible change** as the previous behavior
5580 was mostly useless, producing an unnamed subquery rejected by
5581 most databases in any case. The new behavior is modeled after
5582 that of the very successful :meth:`_orm.Query.join` method in the
5583 ORM, in order to support the functionality of :class:`_orm.Query`
5584 being available by using a :class:`_sql.Select` object with an
5585 :class:`_orm.Session`.
5587 See the notes for this change at :ref:`change_select_join`.
5589 .. seealso::
5591 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`
5593 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`
5595 :meth:`_expression.Select.join`
5597 """
5598 return self.join(target, onclause=onclause, isouter=True, full=full)
5600 def get_final_froms(self):
5601 """Compute the final displayed list of :class:`_expression.FromClause`
5602 elements.
5604 This method will run through the full computation required to
5605 determine what FROM elements will be displayed in the resulting
5606 SELECT statement, including shadowing individual tables with
5607 JOIN objects, as well as full computation for ORM use cases including
5608 eager loading clauses.
5610 For ORM use, this accessor returns the **post compilation**
5611 list of FROM objects; this collection will include elements such as
5612 eagerly loaded tables and joins. The objects will **not** be
5613 ORM enabled and not work as a replacement for the
5614 :meth:`_sql.Select.select_froms` collection; additionally, the
5615 method is not well performing for an ORM enabled statement as it
5616 will incur the full ORM construction process.
5618 To retrieve the FROM list that's implied by the "columns" collection
5619 passed to the :class:`_sql.Select` originally, use the
5620 :attr:`_sql.Select.columns_clause_froms` accessor.
5622 To select from an alternative set of columns while maintaining the
5623 FROM list, use the :meth:`_sql.Select.with_only_columns` method and
5624 pass the
5625 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`
5626 parameter.
5628 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`
5629 method replaces the previous :attr:`_sql.Select.froms` accessor,
5630 which is deprecated.
5632 .. seealso::
5634 :attr:`_sql.Select.columns_clause_froms`
5636 """
5637 return self._compile_state_factory(self, None)._get_display_froms()
5639 @property
5640 @util.deprecated(
5641 "1.4.23",
5642 "The :attr:`_expression.Select.froms` attribute is moved to "
5643 "the :meth:`_expression.Select.get_final_froms` method.",
5644 )
5645 def froms(self):
5646 """Return the displayed list of :class:`_expression.FromClause`
5647 elements.
5650 """
5651 return self.get_final_froms()
5653 @property
5654 def columns_clause_froms(self):
5655 """Return the set of :class:`_expression.FromClause` objects implied
5656 by the columns clause of this SELECT statement.
5658 .. versionadded:: 1.4.23
5660 .. seealso::
5662 :attr:`_sql.Select.froms` - "final" FROM list taking the full
5663 statement into account
5665 :meth:`_sql.Select.with_only_columns` - makes use of this
5666 collection to set up a new FROM list
5668 """
5670 return SelectState.get_plugin_class(self).get_columns_clause_froms(
5671 self
5672 )
5674 @property
5675 def inner_columns(self):
5676 """An iterator of all :class:`_expression.ColumnElement`
5677 expressions which would
5678 be rendered into the columns clause of the resulting SELECT statement.
5680 This method is legacy as of 1.4 and is superseded by the
5681 :attr:`_expression.Select.exported_columns` collection.
5683 """
5685 return iter(self._all_selected_columns)
5687 def is_derived_from(self, fromclause):
5688 if self in fromclause._cloned_set:
5689 return True
5691 for f in self._iterate_from_elements():
5692 if f.is_derived_from(fromclause):
5693 return True
5694 return False
5696 def _copy_internals(self, clone=_clone, **kw):
5697 # Select() object has been cloned and probably adapted by the
5698 # given clone function. Apply the cloning function to internal
5699 # objects
5701 # 1. keep a dictionary of the froms we've cloned, and what
5702 # they've become. This allows us to ensure the same cloned from
5703 # is used when other items such as columns are "cloned"
5705 all_the_froms = set(
5706 itertools.chain(
5707 _from_objects(*self._raw_columns),
5708 _from_objects(*self._where_criteria),
5709 _from_objects(*[elem[0] for elem in self._setup_joins]),
5710 )
5711 )
5713 # do a clone for the froms we've gathered. what is important here
5714 # is if any of the things we are selecting from, like tables,
5715 # were converted into Join objects. if so, these need to be
5716 # added to _from_obj explicitly, because otherwise they won't be
5717 # part of the new state, as they don't associate themselves with
5718 # their columns.
5719 new_froms = {f: clone(f, **kw) for f in all_the_froms}
5721 # 2. copy FROM collections, adding in joins that we've created.
5722 existing_from_obj = [clone(f, **kw) for f in self._from_obj]
5723 add_froms = (
5724 set(f for f in new_froms.values() if isinstance(f, Join))
5725 .difference(all_the_froms)
5726 .difference(existing_from_obj)
5727 )
5729 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)
5731 # 3. clone everything else, making sure we use columns
5732 # corresponding to the froms we just made.
5733 def replace(obj, **kw):
5734 if isinstance(obj, ColumnClause) and obj.table in new_froms:
5735 newelem = new_froms[obj.table].corresponding_column(obj)
5736 return newelem
5738 kw["replace"] = replace
5740 # copy everything else. for table-ish things like correlate,
5741 # correlate_except, setup_joins, these clone normally. For
5742 # column-expression oriented things like raw_columns, where_criteria,
5743 # order by, we get this from the new froms.
5744 super(Select, self)._copy_internals(
5745 clone=clone, omit_attrs=("_from_obj",), **kw
5746 )
5748 self._reset_memoizations()
5750 def get_children(self, **kwargs):
5751 return itertools.chain(
5752 super(Select, self).get_children(
5753 omit_attrs=["_from_obj", "_correlate", "_correlate_except"]
5754 ),
5755 self._iterate_from_elements(),
5756 )
5758 @_generative
5759 def add_columns(self, *columns):
5760 """Return a new :func:`_expression.select` construct with
5761 the given column expressions added to its columns clause.
5763 E.g.::
5765 my_select = my_select.add_columns(table.c.new_column)
5767 See the documentation for
5768 :meth:`_expression.Select.with_only_columns`
5769 for guidelines on adding /replacing the columns of a
5770 :class:`_expression.Select` object.
5772 """
5773 self._reset_memoizations()
5775 self._raw_columns = self._raw_columns + [
5776 coercions.expect(
5777 roles.ColumnsClauseRole, column, apply_propagate_attrs=self
5778 )
5779 for column in columns
5780 ]
5782 def _set_entities(self, entities):
5783 self._raw_columns = [
5784 coercions.expect(
5785 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self
5786 )
5787 for ent in util.to_list(entities)
5788 ]
5790 @util.deprecated(
5791 "1.4",
5792 "The :meth:`_expression.Select.column` method is deprecated and will "
5793 "be removed in a future release. Please use "
5794 ":meth:`_expression.Select.add_columns`",
5795 )
5796 def column(self, column):
5797 """Return a new :func:`_expression.select` construct with
5798 the given column expression added to its columns clause.
5800 E.g.::
5802 my_select = my_select.column(table.c.new_column)
5804 See the documentation for
5805 :meth:`_expression.Select.with_only_columns`
5806 for guidelines on adding /replacing the columns of a
5807 :class:`_expression.Select` object.
5809 """
5810 return self.add_columns(column)
5812 @util.preload_module("sqlalchemy.sql.util")
5813 def reduce_columns(self, only_synonyms=True):
5814 """Return a new :func:`_expression.select` construct with redundantly
5815 named, equivalently-valued columns removed from the columns clause.
5817 "Redundant" here means two columns where one refers to the
5818 other either based on foreign key, or via a simple equality
5819 comparison in the WHERE clause of the statement. The primary purpose
5820 of this method is to automatically construct a select statement
5821 with all uniquely-named columns, without the need to use
5822 table-qualified labels as
5823 :meth:`_expression.Select.set_label_style`
5824 does.
5826 When columns are omitted based on foreign key, the referred-to
5827 column is the one that's kept. When columns are omitted based on
5828 WHERE equivalence, the first column in the columns clause is the
5829 one that's kept.
5831 :param only_synonyms: when True, limit the removal of columns
5832 to those which have the same name as the equivalent. Otherwise,
5833 all columns that are equivalent to another are removed.
5835 """
5836 return self.with_only_columns(
5837 *util.preloaded.sql_util.reduce_columns(
5838 self._all_selected_columns,
5839 only_synonyms=only_synonyms,
5840 *(self._where_criteria + self._from_obj)
5841 )
5842 )
5844 @_generative
5845 def with_only_columns(self, *columns, **kw):
5846 r"""Return a new :func:`_expression.select` construct with its columns
5847 clause replaced with the given columns.
5849 By default, this method is exactly equivalent to as if the original
5850 :func:`_expression.select` had been called with the given columns
5851 clause. E.g. a statement::
5853 s = select(table1.c.a, table1.c.b)
5854 s = s.with_only_columns(table1.c.b)
5856 should be exactly equivalent to::
5858 s = select(table1.c.b)
5860 In this mode of operation, :meth:`_sql.Select.with_only_columns`
5861 will also dynamically alter the FROM clause of the
5862 statement if it is not explicitly stated.
5863 To maintain the existing set of FROMs including those implied by the
5864 current columns clause, add the
5865 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`
5866 parameter::
5868 s = select(table1.c.a, table2.c.b)
5869 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)
5871 The above parameter performs a transfer of the effective FROMs
5872 in the columns collection to the :meth:`_sql.Select.select_from`
5873 method, as though the following were invoked::
5875 s = select(table1.c.a, table2.c.b)
5876 s = s.select_from(table1, table2).with_only_columns(table1.c.a)
5878 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`
5879 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`
5880 collection and performs an operation equivalent to the following::
5882 s = select(table1.c.a, table2.c.b)
5883 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)
5885 :param \*columns: column expressions to be used.
5887 .. versionchanged:: 1.4 the :meth:`_sql.Select.with_only_columns`
5888 method accepts the list of column expressions positionally;
5889 passing the expressions as a list is deprecated.
5891 :param maintain_column_froms: boolean parameter that will ensure the
5892 FROM list implied from the current columns clause will be transferred
5893 to the :meth:`_sql.Select.select_from` method first.
5895 .. versionadded:: 1.4.23
5897 """ # noqa: E501
5899 # memoizations should be cleared here as of
5900 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this
5901 # is the case for now.
5902 self._assert_no_memoizations()
5904 maintain_column_froms = kw.pop("maintain_column_froms", False)
5905 if kw:
5906 raise TypeError("unknown parameters: %s" % (", ".join(kw),))
5908 if maintain_column_froms:
5909 self.select_from.non_generative(self, *self.columns_clause_froms)
5911 # then memoize the FROMs etc.
5912 _MemoizedSelectEntities._generate_for_statement(self)
5914 self._raw_columns = [
5915 coercions.expect(roles.ColumnsClauseRole, c)
5916 for c in coercions._expression_collection_was_a_list(
5917 "columns", "Select.with_only_columns", columns
5918 )
5919 ]
5921 @property
5922 def whereclause(self):
5923 """Return the completed WHERE clause for this
5924 :class:`_expression.Select` statement.
5926 This assembles the current collection of WHERE criteria
5927 into a single :class:`_expression.BooleanClauseList` construct.
5930 .. versionadded:: 1.4
5932 """
5934 return BooleanClauseList._construct_for_whereclause(
5935 self._where_criteria
5936 )
5938 _whereclause = whereclause
5940 @_generative
5941 def where(self, *whereclause):
5942 """Return a new :func:`_expression.select` construct with
5943 the given expression added to
5944 its WHERE clause, joined to the existing clause via AND, if any.
5946 """
5948 assert isinstance(self._where_criteria, tuple)
5950 for criterion in whereclause:
5951 where_criteria = coercions.expect(roles.WhereHavingRole, criterion)
5952 self._where_criteria += (where_criteria,)
5954 @_generative
5955 def having(self, having):
5956 """Return a new :func:`_expression.select` construct with
5957 the given expression added to
5958 its HAVING clause, joined to the existing clause via AND, if any.
5960 """
5961 self._having_criteria += (
5962 coercions.expect(roles.WhereHavingRole, having),
5963 )
5965 @_generative
5966 def distinct(self, *expr):
5967 r"""Return a new :func:`_expression.select` construct which
5968 will apply DISTINCT to its columns clause.
5970 :param \*expr: optional column expressions. When present,
5971 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>>)``
5972 construct.
5974 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated
5975 and will raise :class:`_exc.CompileError` in a future version.
5977 """
5978 if expr:
5979 self._distinct = True
5980 self._distinct_on = self._distinct_on + tuple(
5981 coercions.expect(roles.ByOfRole, e) for e in expr
5982 )
5983 else:
5984 self._distinct = True
5986 @_generative
5987 def select_from(self, *froms):
5988 r"""Return a new :func:`_expression.select` construct with the
5989 given FROM expression(s)
5990 merged into its list of FROM objects.
5992 E.g.::
5994 table1 = table('t1', column('a'))
5995 table2 = table('t2', column('b'))
5996 s = select(table1.c.a).\
5997 select_from(
5998 table1.join(table2, table1.c.a==table2.c.b)
5999 )
6001 The "from" list is a unique set on the identity of each element,
6002 so adding an already present :class:`_schema.Table`
6003 or other selectable
6004 will have no effect. Passing a :class:`_expression.Join` that refers
6005 to an already present :class:`_schema.Table`
6006 or other selectable will have
6007 the effect of concealing the presence of that selectable as
6008 an individual element in the rendered FROM list, instead
6009 rendering it into a JOIN clause.
6011 While the typical purpose of :meth:`_expression.Select.select_from`
6012 is to
6013 replace the default, derived FROM clause with a join, it can
6014 also be called with individual table elements, multiple times
6015 if desired, in the case that the FROM clause cannot be fully
6016 derived from the columns clause::
6018 select(func.count('*')).select_from(table1)
6020 """
6022 self._from_obj += tuple(
6023 coercions.expect(
6024 roles.FromClauseRole, fromclause, apply_propagate_attrs=self
6025 )
6026 for fromclause in froms
6027 )
6029 @_generative
6030 def correlate(self, *fromclauses):
6031 r"""Return a new :class:`_expression.Select`
6032 which will correlate the given FROM
6033 clauses to that of an enclosing :class:`_expression.Select`.
6035 Calling this method turns off the :class:`_expression.Select` object's
6036 default behavior of "auto-correlation". Normally, FROM elements
6037 which appear in a :class:`_expression.Select`
6038 that encloses this one via
6039 its :term:`WHERE clause`, ORDER BY, HAVING or
6040 :term:`columns clause` will be omitted from this
6041 :class:`_expression.Select`
6042 object's :term:`FROM clause`.
6043 Setting an explicit correlation collection using the
6044 :meth:`_expression.Select.correlate`
6045 method provides a fixed list of FROM objects
6046 that can potentially take place in this process.
6048 When :meth:`_expression.Select.correlate`
6049 is used to apply specific FROM clauses
6050 for correlation, the FROM elements become candidates for
6051 correlation regardless of how deeply nested this
6052 :class:`_expression.Select`
6053 object is, relative to an enclosing :class:`_expression.Select`
6054 which refers to
6055 the same FROM object. This is in contrast to the behavior of
6056 "auto-correlation" which only correlates to an immediate enclosing
6057 :class:`_expression.Select`.
6058 Multi-level correlation ensures that the link
6059 between enclosed and enclosing :class:`_expression.Select`
6060 is always via
6061 at least one WHERE/ORDER BY/HAVING/columns clause in order for
6062 correlation to take place.
6064 If ``None`` is passed, the :class:`_expression.Select`
6065 object will correlate
6066 none of its FROM entries, and all will render unconditionally
6067 in the local FROM clause.
6069 :param \*fromclauses: a list of one or more
6070 :class:`_expression.FromClause`
6071 constructs, or other compatible constructs (i.e. ORM-mapped
6072 classes) to become part of the correlate collection.
6074 .. seealso::
6076 :meth:`_expression.Select.correlate_except`
6078 :ref:`tutorial_scalar_subquery`
6080 """
6082 self._auto_correlate = False
6083 if fromclauses and fromclauses[0] in {None, False}:
6084 self._correlate = ()
6085 else:
6086 self._correlate = self._correlate + tuple(
6087 coercions.expect(roles.FromClauseRole, f) for f in fromclauses
6088 )
6090 @_generative
6091 def correlate_except(self, *fromclauses):
6092 r"""Return a new :class:`_expression.Select`
6093 which will omit the given FROM
6094 clauses from the auto-correlation process.
6096 Calling :meth:`_expression.Select.correlate_except` turns off the
6097 :class:`_expression.Select` object's default behavior of
6098 "auto-correlation" for the given FROM elements. An element
6099 specified here will unconditionally appear in the FROM list, while
6100 all other FROM elements remain subject to normal auto-correlation
6101 behaviors.
6103 If ``None`` is passed, the :class:`_expression.Select`
6104 object will correlate
6105 all of its FROM entries.
6107 :param \*fromclauses: a list of one or more
6108 :class:`_expression.FromClause`
6109 constructs, or other compatible constructs (i.e. ORM-mapped
6110 classes) to become part of the correlate-exception collection.
6112 .. seealso::
6114 :meth:`_expression.Select.correlate`
6116 :ref:`tutorial_scalar_subquery`
6118 """
6120 self._auto_correlate = False
6121 if fromclauses and fromclauses[0] in {None, False}:
6122 self._correlate_except = ()
6123 else:
6124 self._correlate_except = (self._correlate_except or ()) + tuple(
6125 coercions.expect(roles.FromClauseRole, f) for f in fromclauses
6126 )
6128 @HasMemoized.memoized_attribute
6129 def selected_columns(self):
6130 """A :class:`_expression.ColumnCollection`
6131 representing the columns that
6132 this SELECT statement or similar construct returns in its result set,
6133 not including :class:`_sql.TextClause` constructs.
6135 This collection differs from the :attr:`_expression.FromClause.columns`
6136 collection of a :class:`_expression.FromClause` in that the columns
6137 within this collection cannot be directly nested inside another SELECT
6138 statement; a subquery must be applied first which provides for the
6139 necessary parenthesization required by SQL.
6141 For a :func:`_expression.select` construct, the collection here is
6142 exactly what would be rendered inside the "SELECT" statement, and the
6143 :class:`_expression.ColumnElement` objects are directly present as they
6144 were given, e.g.::
6146 col1 = column('q', Integer)
6147 col2 = column('p', Integer)
6148 stmt = select(col1, col2)
6150 Above, ``stmt.selected_columns`` would be a collection that contains
6151 the ``col1`` and ``col2`` objects directly. For a statement that is
6152 against a :class:`_schema.Table` or other
6153 :class:`_expression.FromClause`, the collection will use the
6154 :class:`_expression.ColumnElement` objects that are in the
6155 :attr:`_expression.FromClause.c` collection of the from element.
6157 .. note::
6159 The :attr:`_sql.Select.selected_columns` collection does not
6160 include expressions established in the columns clause using the
6161 :func:`_sql.text` construct; these are silently omitted from the
6162 collection. To use plain textual column expressions inside of a
6163 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`
6164 construct.
6167 .. versionadded:: 1.4
6169 """
6171 # compare to SelectState._generate_columns_plus_names, which
6172 # generates the actual names used in the SELECT string. that
6173 # method is more complex because it also renders columns that are
6174 # fully ambiguous, e.g. same column more than once.
6175 conv = SelectState._column_naming_convention(self._label_style)
6177 return ColumnCollection(
6178 [
6179 (conv(c), c)
6180 for c in self._all_selected_columns
6181 if not c._is_text_clause
6182 ]
6183 ).as_immutable()
6185 @HasMemoized.memoized_attribute
6186 def _all_selected_columns(self):
6187 meth = SelectState.get_plugin_class(self).all_selected_columns
6188 return list(meth(self))
6190 def _ensure_disambiguated_names(self):
6191 if self._label_style is LABEL_STYLE_NONE:
6192 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)
6193 return self
6195 def _generate_columns_plus_names(self, anon_for_dupe_key, cols=None):
6196 """Generate column names as rendered in a SELECT statement by
6197 the compiler.
6199 This is distinct from the _column_naming_convention generator that's
6200 intended for population of .c collections and similar, which has
6201 different rules. the collection returned here calls upon the
6202 _column_naming_convention as well.
6204 """
6206 if cols is None:
6207 cols = self._all_selected_columns
6209 key_naming_convention = SelectState._column_naming_convention(
6210 self._label_style
6211 )
6213 names = {}
6215 result = []
6216 result_append = result.append
6218 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL
6219 label_style_none = self._label_style is LABEL_STYLE_NONE
6221 # a counter used for "dedupe" labels, which have double underscores
6222 # in them and are never referred by name; they only act
6223 # as positional placeholders. they need only be unique within
6224 # the single columns clause they're rendered within (required by
6225 # some dbs such as mysql). So their anon identity is tracked against
6226 # a fixed counter rather than hash() identity.
6227 dedupe_hash = 1
6229 for c in cols:
6230 repeated = False
6232 if not c._render_label_in_columns_clause:
6233 effective_name = (
6234 required_label_name
6235 ) = fallback_label_name = None
6236 elif label_style_none:
6237 effective_name = required_label_name = None
6238 fallback_label_name = c._non_anon_label or c._anon_name_label
6239 else:
6240 if table_qualified:
6241 required_label_name = (
6242 effective_name
6243 ) = fallback_label_name = c._tq_label
6244 else:
6245 effective_name = fallback_label_name = c._non_anon_label
6246 required_label_name = None
6248 if effective_name is None:
6249 # it seems like this could be _proxy_key and we would
6250 # not need _expression_label but it isn't
6251 # giving us a clue when to use anon_label instead
6252 expr_label = c._expression_label
6253 if expr_label is None:
6254 repeated = c._anon_name_label in names
6255 names[c._anon_name_label] = c
6256 effective_name = required_label_name = None
6258 if repeated:
6259 # here, "required_label_name" is sent as
6260 # "None" and "fallback_label_name" is sent.
6261 if table_qualified:
6262 fallback_label_name = (
6263 c._dedupe_anon_tq_label_idx(dedupe_hash)
6264 )
6265 dedupe_hash += 1
6266 else:
6267 fallback_label_name = c._dedupe_anon_label_idx(
6268 dedupe_hash
6269 )
6270 dedupe_hash += 1
6271 else:
6272 fallback_label_name = c._anon_name_label
6273 else:
6274 required_label_name = (
6275 effective_name
6276 ) = fallback_label_name = expr_label
6278 if effective_name is not None:
6279 if effective_name in names:
6280 # when looking to see if names[name] is the same column as
6281 # c, use hash(), so that an annotated version of the column
6282 # is seen as the same as the non-annotated
6283 if hash(names[effective_name]) != hash(c):
6285 # different column under the same name. apply
6286 # disambiguating label
6287 if table_qualified:
6288 required_label_name = (
6289 fallback_label_name
6290 ) = c._anon_tq_label
6291 else:
6292 required_label_name = (
6293 fallback_label_name
6294 ) = c._anon_name_label
6296 if anon_for_dupe_key and required_label_name in names:
6297 # here, c._anon_tq_label is definitely unique to
6298 # that column identity (or annotated version), so
6299 # this should always be true.
6300 # this is also an infrequent codepath because
6301 # you need two levels of duplication to be here
6302 assert hash(names[required_label_name]) == hash(c)
6304 # the column under the disambiguating label is
6305 # already present. apply the "dedupe" label to
6306 # subsequent occurrences of the column so that the
6307 # original stays non-ambiguous
6308 if table_qualified:
6309 required_label_name = (
6310 fallback_label_name
6311 ) = c._dedupe_anon_tq_label_idx(dedupe_hash)
6312 dedupe_hash += 1
6313 else:
6314 required_label_name = (
6315 fallback_label_name
6316 ) = c._dedupe_anon_label_idx(dedupe_hash)
6317 dedupe_hash += 1
6318 repeated = True
6319 else:
6320 names[required_label_name] = c
6321 elif anon_for_dupe_key:
6322 # same column under the same name. apply the "dedupe"
6323 # label so that the original stays non-ambiguous
6324 if table_qualified:
6325 required_label_name = (
6326 fallback_label_name
6327 ) = c._dedupe_anon_tq_label_idx(dedupe_hash)
6328 dedupe_hash += 1
6329 else:
6330 required_label_name = (
6331 fallback_label_name
6332 ) = c._dedupe_anon_label_idx(dedupe_hash)
6333 dedupe_hash += 1
6334 repeated = True
6335 else:
6336 names[effective_name] = c
6338 result_append(
6339 (
6340 # string label name, if non-None, must be rendered as a
6341 # label, i.e. "AS <name>"
6342 required_label_name,
6343 # proxy_key that is to be part of the result map for this
6344 # col. this is also the key in a fromclause.c or
6345 # select.selected_columns collection
6346 key_naming_convention(c),
6347 # name that can be used to render an "AS <name>" when
6348 # we have to render a label even though
6349 # required_label_name was not given
6350 fallback_label_name,
6351 # the ColumnElement itself
6352 c,
6353 # True if this is a duplicate of a previous column
6354 # in the list of columns
6355 repeated,
6356 )
6357 )
6359 return result
6361 def _generate_fromclause_column_proxies(self, subquery):
6362 """Generate column proxies to place in the exported ``.c``
6363 collection of a subquery."""
6365 prox = [
6366 c._make_proxy(
6367 subquery,
6368 key=proxy_key,
6369 name=required_label_name,
6370 name_is_truncatable=True,
6371 )
6372 for (
6373 required_label_name,
6374 proxy_key,
6375 fallback_label_name,
6376 c,
6377 repeated,
6378 ) in (self._generate_columns_plus_names(False))
6379 if not c._is_text_clause
6380 ]
6382 subquery._columns._populate_separate_keys(prox)
6384 def _needs_parens_for_grouping(self):
6385 return self._has_row_limiting_clause or bool(
6386 self._order_by_clause.clauses
6387 )
6389 def self_group(self, against=None):
6390 """Return a 'grouping' construct as per the
6391 :class:`_expression.ClauseElement` specification.
6393 This produces an element that can be embedded in an expression. Note
6394 that this method is called automatically as needed when constructing
6395 expressions and should not require explicit use.
6397 """
6398 if (
6399 isinstance(against, CompoundSelect)
6400 and not self._needs_parens_for_grouping()
6401 ):
6402 return self
6403 else:
6404 return SelectStatementGrouping(self)
6406 def union(self, *other, **kwargs):
6407 r"""Return a SQL ``UNION`` of this select() construct against
6408 the given selectables provided as positional arguments.
6410 :param \*other: one or more elements with which to create a
6411 UNION.
6413 .. versionchanged:: 1.4.28
6415 multiple elements are now accepted.
6417 :param \**kwargs: keyword arguments are forwarded to the constructor
6418 for the newly created :class:`_sql.CompoundSelect` object.
6420 """
6421 return CompoundSelect._create_union(self, *other, **kwargs)
6423 def union_all(self, *other, **kwargs):
6424 r"""Return a SQL ``UNION ALL`` of this select() construct against
6425 the given selectables provided as positional arguments.
6427 :param \*other: one or more elements with which to create a
6428 UNION.
6430 .. versionchanged:: 1.4.28
6432 multiple elements are now accepted.
6434 :param \**kwargs: keyword arguments are forwarded to the constructor
6435 for the newly created :class:`_sql.CompoundSelect` object.
6437 """
6438 return CompoundSelect._create_union_all(self, *other, **kwargs)
6440 def except_(self, *other, **kwargs):
6441 r"""Return a SQL ``EXCEPT`` of this select() construct against
6442 the given selectable provided as positional arguments.
6444 :param \*other: one or more elements with which to create a
6445 UNION.
6447 .. versionchanged:: 1.4.28
6449 multiple elements are now accepted.
6451 :param \**kwargs: keyword arguments are forwarded to the constructor
6452 for the newly created :class:`_sql.CompoundSelect` object.
6454 """
6455 return CompoundSelect._create_except(self, *other, **kwargs)
6457 def except_all(self, *other, **kwargs):
6458 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against
6459 the given selectables provided as positional arguments.
6461 :param \*other: one or more elements with which to create a
6462 UNION.
6464 .. versionchanged:: 1.4.28
6466 multiple elements are now accepted.
6468 :param \**kwargs: keyword arguments are forwarded to the constructor
6469 for the newly created :class:`_sql.CompoundSelect` object.
6471 """
6472 return CompoundSelect._create_except_all(self, *other, **kwargs)
6474 def intersect(self, *other, **kwargs):
6475 r"""Return a SQL ``INTERSECT`` of this select() construct against
6476 the given selectables provided as positional arguments.
6478 :param \*other: one or more elements with which to create a
6479 UNION.
6481 .. versionchanged:: 1.4.28
6483 multiple elements are now accepted.
6485 :param \**kwargs: keyword arguments are forwarded to the constructor
6486 for the newly created :class:`_sql.CompoundSelect` object.
6488 """
6489 return CompoundSelect._create_intersect(self, *other, **kwargs)
6491 def intersect_all(self, *other, **kwargs):
6492 r"""Return a SQL ``INTERSECT ALL`` of this select() construct
6493 against the given selectables provided as positional arguments.
6495 :param \*other: one or more elements with which to create a
6496 UNION.
6498 .. versionchanged:: 1.4.28
6500 multiple elements are now accepted.
6502 :param \**kwargs: keyword arguments are forwarded to the constructor
6503 for the newly created :class:`_sql.CompoundSelect` object.
6505 """
6506 return CompoundSelect._create_intersect_all(self, *other, **kwargs)
6508 @property
6509 @util.deprecated_20(
6510 ":attr:`.Executable.bind`",
6511 alternative="Bound metadata is being removed as of SQLAlchemy 2.0.",
6512 enable_warnings=False,
6513 )
6514 def bind(self):
6515 """Returns the :class:`_engine.Engine` or :class:`_engine.Connection`
6516 to which this :class:`.Executable` is bound, or None if none found.
6518 """
6519 if self._bind:
6520 return self._bind
6522 for item in self._iterate_from_elements():
6523 if item._is_subquery and item.element is self:
6524 raise exc.InvalidRequestError(
6525 "select() construct refers to itself as a FROM"
6526 )
6528 e = item.bind
6529 if e:
6530 self._bind = e
6531 return e
6532 else:
6533 break
6535 for c in self._raw_columns:
6536 e = c.bind
6537 if e:
6538 self._bind = e
6539 return e
6541 @bind.setter
6542 def bind(self, bind):
6543 self._bind = bind
6546class ScalarSelect(roles.InElementRole, Generative, Grouping):
6547 """Represent a scalar subquery.
6550 A :class:`_sql.ScalarSelect` is created by invoking the
6551 :meth:`_sql.SelectBase.scalar_subquery` method. The object
6552 then participates in other SQL expressions as a SQL column expression
6553 within the :class:`_sql.ColumnElement` hierarchy.
6555 .. seealso::
6557 :meth:`_sql.SelectBase.scalar_subquery`
6559 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
6561 """
6563 _from_objects = []
6564 _is_from_container = True
6565 _is_implicitly_boolean = False
6566 inherit_cache = True
6568 def __init__(self, element):
6569 self.element = element
6570 self.type = element._scalar_type()
6572 @property
6573 def columns(self):
6574 raise exc.InvalidRequestError(
6575 "Scalar Select expression has no "
6576 "columns; use this object directly "
6577 "within a column-level expression."
6578 )
6580 c = columns
6582 @_generative
6583 def where(self, crit):
6584 """Apply a WHERE clause to the SELECT statement referred to
6585 by this :class:`_expression.ScalarSelect`.
6587 """
6588 self.element = self.element.where(crit)
6590 def self_group(self, **kwargs):
6591 return self
6593 @_generative
6594 def correlate(self, *fromclauses):
6595 r"""Return a new :class:`_expression.ScalarSelect`
6596 which will correlate the given FROM
6597 clauses to that of an enclosing :class:`_expression.Select`.
6599 This method is mirrored from the :meth:`_sql.Select.correlate` method
6600 of the underlying :class:`_sql.Select`. The method applies the
6601 :meth:_sql.Select.correlate` method, then returns a new
6602 :class:`_sql.ScalarSelect` against that statement.
6604 .. versionadded:: 1.4 Previously, the
6605 :meth:`_sql.ScalarSelect.correlate`
6606 method was only available from :class:`_sql.Select`.
6608 :param \*fromclauses: a list of one or more
6609 :class:`_expression.FromClause`
6610 constructs, or other compatible constructs (i.e. ORM-mapped
6611 classes) to become part of the correlate collection.
6613 .. seealso::
6615 :meth:`_expression.ScalarSelect.correlate_except`
6617 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
6620 """
6621 self.element = self.element.correlate(*fromclauses)
6623 @_generative
6624 def correlate_except(self, *fromclauses):
6625 r"""Return a new :class:`_expression.ScalarSelect`
6626 which will omit the given FROM
6627 clauses from the auto-correlation process.
6629 This method is mirrored from the
6630 :meth:`_sql.Select.correlate_except` method of the underlying
6631 :class:`_sql.Select`. The method applies the
6632 :meth:_sql.Select.correlate_except` method, then returns a new
6633 :class:`_sql.ScalarSelect` against that statement.
6635 .. versionadded:: 1.4 Previously, the
6636 :meth:`_sql.ScalarSelect.correlate_except`
6637 method was only available from :class:`_sql.Select`.
6639 :param \*fromclauses: a list of one or more
6640 :class:`_expression.FromClause`
6641 constructs, or other compatible constructs (i.e. ORM-mapped
6642 classes) to become part of the correlate-exception collection.
6644 .. seealso::
6646 :meth:`_expression.ScalarSelect.correlate`
6648 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
6651 """
6653 self.element = self.element.correlate_except(*fromclauses)
6656class Exists(UnaryExpression):
6657 """Represent an ``EXISTS`` clause.
6659 See :func:`_sql.exists` for a description of usage.
6661 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`
6662 instance by calling :meth:`_sql.SelectBase.exists`.
6664 """
6666 _from_objects = []
6667 inherit_cache = True
6669 def __init__(self, *args, **kwargs):
6670 """Construct a new :class:`_expression.Exists` construct.
6672 The :func:`_sql.exists` can be invoked by itself to produce an
6673 :class:`_sql.Exists` construct, which will accept simple WHERE
6674 criteria::
6676 exists_criteria = exists().where(table1.c.col1 == table2.c.col2)
6678 However, for greater flexibility in constructing the SELECT, an
6679 existing :class:`_sql.Select` construct may be converted to an
6680 :class:`_sql.Exists`, most conveniently by making use of the
6681 :meth:`_sql.SelectBase.exists` method::
6683 exists_criteria = (
6684 select(table2.c.col2).
6685 where(table1.c.col1 == table2.c.col2).
6686 exists()
6687 )
6689 The EXISTS criteria is then used inside of an enclosing SELECT::
6691 stmt = select(table1.c.col1).where(exists_criteria)
6693 The above statement will then be of the form::
6695 SELECT col1 FROM table1 WHERE EXISTS
6696 (SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1)
6698 .. seealso::
6700 :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.
6702 :meth:`_sql.SelectBase.exists` - method to transform a ``SELECT`` to an
6703 ``EXISTS`` clause.
6705 """ # noqa: E501
6706 if args and isinstance(args[0], (SelectBase, ScalarSelect)):
6707 s = args[0]
6708 else:
6709 if not args:
6710 args = (literal_column("*"),)
6711 s = Select._create(*args, **kwargs).scalar_subquery()
6713 UnaryExpression.__init__(
6714 self,
6715 s,
6716 operator=operators.exists,
6717 type_=type_api.BOOLEANTYPE,
6718 wraps_column_expression=True,
6719 )
6721 def _regroup(self, fn):
6722 element = self.element._ungroup()
6723 element = fn(element)
6724 return element.self_group(against=operators.exists)
6726 @util.deprecated_params(
6727 whereclause=(
6728 "2.0",
6729 "The :paramref:`_sql.Exists.select().whereclause` parameter "
6730 "is deprecated and will be removed in version 2.0. "
6731 "Please make use "
6732 "of the :meth:`.Select.where` "
6733 "method to add WHERE criteria to the SELECT statement.",
6734 ),
6735 kwargs=(
6736 "2.0",
6737 "The :meth:`_sql.Exists.select` method will no longer accept "
6738 "keyword arguments in version 2.0. "
6739 "Please use generative methods from the "
6740 ":class:`_sql.Select` construct in order to apply additional "
6741 "modifications.",
6742 ),
6743 )
6744 def select(self, whereclause=None, **kwargs):
6745 r"""Return a SELECT of this :class:`_expression.Exists`.
6747 e.g.::
6749 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()
6751 This will produce a statement resembling::
6753 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1
6755 :param whereclause: a WHERE clause, equivalent to calling the
6756 :meth:`_sql.Select.where` method.
6758 :param **kwargs: additional keyword arguments are passed to the
6759 legacy constructor for :class:`_sql.Select` described at
6760 :meth:`_sql.Select.create_legacy_select`.
6762 .. seealso::
6764 :func:`_expression.select` - general purpose
6765 method which allows for arbitrary column lists.
6767 """ # noqa
6769 if whereclause is not None:
6770 kwargs["whereclause"] = whereclause
6771 return Select._create_select_from_fromclause(self, [self], **kwargs)
6773 def correlate(self, *fromclause):
6774 """Apply correlation to the subquery noted by this
6775 :class:`_sql.Exists`.
6777 .. seealso::
6779 :meth:`_sql.ScalarSelect.correlate`
6781 """
6782 e = self._clone()
6783 e.element = self._regroup(
6784 lambda element: element.correlate(*fromclause)
6785 )
6786 return e
6788 def correlate_except(self, *fromclause):
6789 """Apply correlation to the subquery noted by this
6790 :class:`_sql.Exists`.
6792 .. seealso::
6794 :meth:`_sql.ScalarSelect.correlate_except`
6796 """
6798 e = self._clone()
6799 e.element = self._regroup(
6800 lambda element: element.correlate_except(*fromclause)
6801 )
6802 return e
6804 def select_from(self, *froms):
6805 """Return a new :class:`_expression.Exists` construct,
6806 applying the given
6807 expression to the :meth:`_expression.Select.select_from`
6808 method of the select
6809 statement contained.
6811 .. note:: it is typically preferable to build a :class:`_sql.Select`
6812 statement first, including the desired WHERE clause, then use the
6813 :meth:`_sql.SelectBase.exists` method to produce an
6814 :class:`_sql.Exists` object at once.
6816 """
6817 e = self._clone()
6818 e.element = self._regroup(lambda element: element.select_from(*froms))
6819 return e
6821 def where(self, *clause):
6822 """Return a new :func:`_expression.exists` construct with the
6823 given expression added to
6824 its WHERE clause, joined to the existing clause via AND, if any.
6827 .. note:: it is typically preferable to build a :class:`_sql.Select`
6828 statement first, including the desired WHERE clause, then use the
6829 :meth:`_sql.SelectBase.exists` method to produce an
6830 :class:`_sql.Exists` object at once.
6832 """
6833 e = self._clone()
6834 e.element = self._regroup(lambda element: element.where(*clause))
6835 return e
6838class TextualSelect(SelectBase):
6839 """Wrap a :class:`_expression.TextClause` construct within a
6840 :class:`_expression.SelectBase`
6841 interface.
6843 This allows the :class:`_expression.TextClause` object to gain a
6844 ``.c`` collection
6845 and other FROM-like capabilities such as
6846 :meth:`_expression.FromClause.alias`,
6847 :meth:`_expression.SelectBase.cte`, etc.
6849 The :class:`_expression.TextualSelect` construct is produced via the
6850 :meth:`_expression.TextClause.columns`
6851 method - see that method for details.
6853 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`
6854 class was renamed
6855 from ``TextAsFrom``, to more correctly suit its role as a
6856 SELECT-oriented object and not a FROM clause.
6858 .. seealso::
6860 :func:`_expression.text`
6862 :meth:`_expression.TextClause.columns` - primary creation interface.
6864 """
6866 __visit_name__ = "textual_select"
6868 _label_style = LABEL_STYLE_NONE
6870 _traverse_internals = [
6871 ("element", InternalTraversal.dp_clauseelement),
6872 ("column_args", InternalTraversal.dp_clauseelement_list),
6873 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals
6875 _is_textual = True
6877 is_text = True
6878 is_select = True
6880 def __init__(self, text, columns, positional=False):
6881 self.element = text
6882 # convert for ORM attributes->columns, etc
6883 self.column_args = [
6884 coercions.expect(roles.ColumnsClauseRole, c) for c in columns
6885 ]
6886 self.positional = positional
6888 @HasMemoized.memoized_attribute
6889 def selected_columns(self):
6890 """A :class:`_expression.ColumnCollection`
6891 representing the columns that
6892 this SELECT statement or similar construct returns in its result set,
6893 not including :class:`_sql.TextClause` constructs.
6895 This collection differs from the :attr:`_expression.FromClause.columns`
6896 collection of a :class:`_expression.FromClause` in that the columns
6897 within this collection cannot be directly nested inside another SELECT
6898 statement; a subquery must be applied first which provides for the
6899 necessary parenthesization required by SQL.
6901 For a :class:`_expression.TextualSelect` construct, the collection
6902 contains the :class:`_expression.ColumnElement` objects that were
6903 passed to the constructor, typically via the
6904 :meth:`_expression.TextClause.columns` method.
6907 .. versionadded:: 1.4
6909 """
6910 return ColumnCollection(
6911 (c.key, c) for c in self.column_args
6912 ).as_immutable()
6914 @property
6915 def _all_selected_columns(self):
6916 return self.column_args
6918 def _set_label_style(self, style):
6919 return self
6921 def _ensure_disambiguated_names(self):
6922 return self
6924 @property
6925 def _bind(self):
6926 return self.element._bind
6928 @_generative
6929 def bindparams(self, *binds, **bind_as_values):
6930 self.element = self.element.bindparams(*binds, **bind_as_values)
6932 def _generate_fromclause_column_proxies(self, fromclause):
6933 fromclause._columns._populate_separate_keys(
6934 c._make_proxy(fromclause) for c in self.column_args
6935 )
6937 def _scalar_type(self):
6938 return self.column_args[0].type
6941TextAsFrom = TextualSelect
6942"""Backwards compatibility with the previous name"""
6945class AnnotatedFromClause(Annotated):
6946 def __init__(self, element, values):
6947 # force FromClause to generate their internal
6948 # collections into __dict__
6949 element.c
6950 Annotated.__init__(self, element, values)