class documentation

class Select(HasPrefixes, HasSuffixes, HasHints, HasCompileState, DeprecatedSelectGenerations, _SelectFromElements, GenerativeSelect):

View In Hierarchy

Represents a SELECT statement.

The _sql.Select object is normally constructed using the _sql.select function. See that function for details.

See Also

_sql.select

:ref:`coretutorial_selecting` - in the 1.x tutorial

:ref:`tutorial_selecting_data` - in the 2.0 tutorial

Class Method create​_legacy​_select Construct a new _expression.Select using the 1.x style API.
Method add​_columns Return a new _expression.select construct with the given column expressions added to its columns clause.
Method column Return a new _expression.select construct with the given column expression added to its columns clause.
Method correlate Return a new _expression.Select which will correlate the given FROM clauses to that of an enclosing _expression.Select.
Method correlate​_except Return a new _expression.Select which will omit the given FROM clauses from the auto-correlation process.
Method distinct Return a new _expression.select construct which will apply DISTINCT to its columns clause.
Method except​_ Return a SQL EXCEPT of this select() construct against the given selectable provided as positional arguments.
Method except​_all Return a SQL EXCEPT ALL of this select() construct against the given selectables provided as positional arguments.
Method filter A synonym for the _future.Select.where method.
Method filter​_by apply the given filtering criterion as a WHERE clause to this select.
Method from​_statement Apply the columns which this .Select would select onto another statement.
Method get​_children Return immediate child .visitors.Traversible elements of this .visitors.Traversible.
Method get​_final​_froms Compute the final displayed list of _expression.FromClause elements.
Method having Return a new _expression.select construct with the given expression added to its HAVING clause, joined to the existing clause via AND, if any.
Method intersect Return a SQL INTERSECT of this select() construct against the given selectables provided as positional arguments.
Method intersect​_all Return a SQL INTERSECT ALL of this select() construct against the given selectables provided as positional arguments.
Method join No summary
Method join​_from No summary
Method outerjoin Create a left outer join.
Method outerjoin​_from No summary
Method reduce​_columns Return a new _expression.select construct with redundantly named, equivalently-valued columns removed from the columns clause.
Method select​_from Return a new _expression.select construct with the given FROM expression(s) merged into its list of FROM objects.
Method self​_group Return a 'grouping' construct as per the _expression.ClauseElement specification.
Method union Return a SQL UNION of this select() construct against the given selectables provided as positional arguments.
Method union​_all Return a SQL UNION ALL of this select() construct against the given selectables provided as positional arguments.
Method where Return a new _expression.select construct with the given expression added to its WHERE clause, joined to the existing clause via AND, if any.
Method with​_only​_columns Return a new _expression.select construct with its columns clause replaced with the given columns.
Class Method ​_create Create a .Select using either the 1.x or 2.0 constructor style.
Class Method ​_create​_future​_select Construct a new _expression.Select using the 2. x style API.
Class Method ​_create​_raw​_select Create a .Select using raw __new__ with no coercions.
Class Method ​_create​_select​_from​_fromclause Undocumented
Method __init__ Undocumented
Method ​_all​_selected​_columns A sequence of expressions that correspond to what is rendered in the columns clause, including _sql.TextClause constructs.
Method ​_copy​_internals Reassign internal elements to be clones of themselves.
Method ​_ensure​_disambiguated​_names Ensure that the names generated by this selectbase will be disambiguated in some way, if possible.
Method ​_filter​_by​_zero Undocumented
Method ​_generate​_columns​_plus​_names Generate column names as rendered in a SELECT statement by the compiler.
Method ​_generate​_fromclause​_column​_proxies Generate column proxies to place in the exported .c collection of a subquery.
Method ​_needs​_parens​_for​_grouping Undocumented
Method ​_scalar​_type Undocumented
Method ​_set​_entities Undocumented
Method bind.setter Undocumented
Method is​_derived​_from Undocumented
Method selected​_columns No summary
Class Variable __visit​_name__ Undocumented
Class Variable ​_cache​_key​_traversal Undocumented
Class Variable ​_having​_criteria Undocumented
Class Variable ​_legacy​_setup​_joins Undocumented
Class Variable ​_memoized​_select​_entities Undocumented
Class Variable ​_setup​_joins Undocumented
Class Variable ​_traverse​_internals Undocumented
Class Variable ​_where​_criteria Undocumented
Instance Variable ​_auto​_correlate Undocumented
Instance Variable ​_bind Undocumented
Instance Variable ​_correlate Undocumented
Instance Variable ​_correlate​_except Undocumented
Instance Variable ​_distinct Undocumented
Instance Variable ​_distinct​_on Undocumented
Instance Variable ​_from​_obj Undocumented
Instance Variable ​_raw​_columns Undocumented
Property bind Returns the _engine.Engine or _engine.Connection to which this .Executable is bound, or None if none found.
Property column​_descriptions Return a 'column descriptions' structure which may be :term:`plugin-specific`.
Property columns​_clause​_froms Return the set of _expression.FromClause objects implied by the columns clause of this SELECT statement.
Property froms Return the displayed list of _expression.FromClause elements.
Property inner​_columns An iterator of all _expression.ColumnElement expressions which would be rendered into the columns clause of the resulting SELECT statement.
Property whereclause Return the completed WHERE clause for this _expression.Select statement.

Inherited from HasPrefixes:

Method ​_setup​_prefixes Undocumented
Method prefix​_with Add one or more expressions following the statement keyword, i.e. SELECT, INSERT, UPDATE, or DELETE. Generative.
Class Variable ​_has​_prefixes​_traverse​_internals Undocumented
Instance Variable ​_prefixes Undocumented

Inherited from HasSuffixes:

Method ​_setup​_suffixes Undocumented
Method suffix​_with Add one or more expressions following the statement as a whole.
Class Variable ​_has​_suffixes​_traverse​_internals Undocumented
Instance Variable ​_suffixes Undocumented

Inherited from HasHints:

Method with​_hint Add an indexing or other executional context hint for the given selectable to this _expression.Select or other selectable object.
Method with​_statement​_hint Add a statement hint to this _expression.Select or other selectable object.
Class Variable ​_has​_hints​_traverse​_internals Undocumented
Class Variable ​_hints Undocumented
Class Variable ​_statement​_hints Undocumented

Inherited from HasCompileState:

Class Variable ​_attributes Undocumented
Class Variable ​_compile​_state​_plugin Undocumented

Inherited from Generative (via HasCompileState):

Method ​_generate Undocumented

Inherited from DeprecatedSelectGenerations:

Method append​_column Append the given column expression to the columns clause of this select() construct.
Method append​_correlation Append the given correlation expression to this select() construct.
Method append​_from Append the given _expression.FromClause expression to this select() construct's FROM clause.
Method append​_having Append the given expression to this select() construct's HAVING criterion.
Method append​_prefix Append the given columns clause prefix expression to this select() construct.
Method append​_whereclause Append the given expression to this select() construct's WHERE criterion.

Inherited from _SelectFromElements:

Method ​_iterate​_from​_elements Undocumented

Inherited from GenerativeSelect:

Method ​_offset​_or​_limit​_clause Convert the given value to an "offset or limit" clause.
Method ​_offset​_or​_limit​_clause​_asint Convert the "offset or limit" clause of a select construct to an integer.
Method ​_simple​_int​_clause True if the clause is a simple integer, False if it is not present or is a SQL expression.
Method apply​_labels Undocumented
Method fetch Return a new selectable with the given FETCH FIRST criterion applied.
Method get​_label​_style Retrieve the current label style.
Method group​_by Return a new selectable with the given list of GROUP BY criterion applied.
Method limit Return a new selectable with the given LIMIT criterion applied.
Method offset Return a new selectable with the given OFFSET criterion applied.
Method order​_by Return a new selectable with the given list of ORDER BY criteria applied.
Method set​_label​_style Return a new selectable with the specified label style.
Method slice Apply LIMIT / OFFSET to this statement based on a slice.
Method with​_for​_update Specify a FOR UPDATE clause for this _expression.GenerativeSelect.
Instance Variable ​_fetch​_clause Undocumented
Instance Variable ​_fetch​_clause​_options Undocumented
Instance Variable ​_for​_update​_arg Undocumented
Instance Variable ​_group​_by​_clauses Undocumented
Instance Variable ​_label​_style Undocumented
Instance Variable ​_limit​_clause Undocumented
Instance Variable ​_offset​_clause Undocumented
Instance Variable ​_order​_by​_clauses Undocumented
Property ​_group​_by​_clause ClauseList access to group_by_clauses for legacy dialects
Property ​_has​_row​_limiting​_clause Undocumented
Property ​_limit No summary
Property ​_offset No summary
Property ​_order​_by​_clause ClauseList access to order_by_clauses for legacy dialects

Inherited from DeprecatedSelectBaseGenerations (via GenerativeSelect):

Method append​_group​_by Append the given GROUP BY criterion applied to this selectable.
Method append​_order​_by Append the given ORDER BY criterion applied to this selectable.

Inherited from SelectBase (via GenerativeSelect):

Method ​_implicit​_subquery Undocumented
Method ​_refresh​_for​_new​_column Undocumented
Method alias Return a named subquery against this _expression.SelectBase.
Method as​_scalar Undocumented
Method exists Return an _sql.Exists representation of this selectable, which can be used as a column expression.
Method label Return a 'scalar' representation of this selectable, embedded as a subquery with a label.
Method lateral Return a LATERAL alias of this _expression.Selectable.
Method scalar​_subquery Return a 'scalar' representation of this selectable, which can be used as a column expression.
Method select Undocumented
Method subquery Return a subquery of this _expression.SelectBase.
Class Variable ​_is​_select​_statement Undocumented
Class Variable is​_select Undocumented
Property ​_from​_objects Undocumented
Property c Undocumented
Property columns Undocumented
Property exported​_columns No summary

Inherited from SelectStatementRole (via GenerativeSelect, SelectBase):

Class Variable ​_role​_name Undocumented

Inherited from StatementRole (via GenerativeSelect, SelectBase, SelectStatementRole):

Class Variable ​_propagate​_attrs Undocumented

Inherited from SQLRole (via GenerativeSelect, SelectBase, SelectStatementRole, StatementRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from SQLRole (via GenerativeSelect, SelectBase, SelectStatementRole, ReturnsRowsRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from DMLSelectRole (via GenerativeSelect, SelectBase):

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via GenerativeSelect, SelectBase, DMLSelectRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from CompoundElementRole (via GenerativeSelect, SelectBase):

Class Variable ​_role​_name Undocumented

Inherited from AllowsLambdaRole (via GenerativeSelect, SelectBase, CompoundElementRole):

Class Variable allows​_lambda Undocumented

Inherited from SQLRole (via GenerativeSelect, SelectBase, CompoundElementRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from InElementRole (via GenerativeSelect, SelectBase):

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via GenerativeSelect, SelectBase, InElementRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from HasCTE (via GenerativeSelect, SelectBase):

Method add​_cte Add a _sql.CTE to this statement object that will be independently rendered even if not referenced in the statement otherwise.
Method cte Return a new _expression.CTE, or Common Table Expression instance.
Class Variable ​_has​_ctes​_traverse​_internals Undocumented
Class Variable ​_independent​_ctes Undocumented

Inherited from ReturnsRowsRole (via GenerativeSelect, SelectBase, HasCTE, HasCTERole):

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via GenerativeSelect, SelectBase, HasCTE, HasCTERole, ReturnsRowsRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from Executable (via GenerativeSelect, SelectBase):

Method ​_add​_context​_option Add a context option to this statement.
Method ​_set​_compile​_options Assign the compile options to a new value.
Method ​_update​_compile​_options update the _compile_options with new keys.
Method execute Compile and execute this .Executable.
Method execution​_options Set non-SQL options for the statement which take effect during execution.
Method get​_execution​_options Get the non-SQL options which will take effect during execution.
Method options Apply options to this statement.
Method scalar Compile and execute this .Executable, returning the result's scalar representation.
Class Variable ​_executable​_traverse​_internals Undocumented
Class Variable ​_with​_context​_options Undocumented
Class Variable ​_with​_options Undocumented
Class Variable is​_delete Undocumented
Class Variable is​_dml Undocumented
Class Variable is​_insert Undocumented
Class Variable is​_text Undocumented
Class Variable is​_update Undocumented
Class Variable supports​_execution Undocumented
Instance Variable ​_compile​_options Undocumented
Instance Variable ​_execution​_options Undocumented
Property ​_effective​_plugin​_target Undocumented

Inherited from StatementRole (via GenerativeSelect, SelectBase, Executable):

Class Variable ​_propagate​_attrs Undocumented
Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via GenerativeSelect, SelectBase, Executable, StatementRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from Generative (via GenerativeSelect, SelectBase, Executable):

Method ​_generate Undocumented

Inherited from SupportsCloneAnnotations (via GenerativeSelect, SelectBase):

Method ​_annotate return a copy of this ClauseElement with annotations updated by the given dictionary.
Method ​_deannotate return a copy of this _expression.ClauseElement with annotations removed.
Method ​_with​_annotations return a copy of this ClauseElement with annotations replaced by the given dictionary.
Class Variable ​_clone​_annotations​_traverse​_internals Undocumented

Inherited from SupportsAnnotations (via GenerativeSelect, SelectBase, SupportsCloneAnnotations):

Property ​_annotations​_cache​_key Undocumented

Inherited from Selectable (via GenerativeSelect, SelectBase):

Method corresponding​_column No summary
Method replace​_selectable Replace all occurrences of _expression.FromClause 'old' with the given _expression.Alias object, returning a copy of this _expression.FromClause.
Class Variable is​_selectable Undocumented

Inherited from ReturnsRows (via GenerativeSelect, SelectBase, Selectable):

Class Variable ​_is​_from​_clause Undocumented
Class Variable ​_is​_lateral Undocumented
Class Variable ​_is​_returns​_rows Undocumented
Property selectable Undocumented

Inherited from ReturnsRowsRole (via GenerativeSelect, SelectBase, Selectable, ReturnsRows):

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via GenerativeSelect, SelectBase, Selectable, ReturnsRows, ReturnsRowsRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from ClauseElement (via GenerativeSelect, SelectBase, Selectable, ReturnsRows):

Method compare Compare this _expression.ClauseElement to the given _expression.ClauseElement.
Method compile Compile this SQL expression.
Method params Return a copy with _expression.bindparam elements replaced.
Method unique​_params Return a copy with _expression.bindparam elements replaced.
Method __bool__ Undocumented
Method __getstate__ Undocumented
Method __invert__ Undocumented
Method __repr__ Undocumented
Method __str__ Undocumented
Method ​_clone Create a shallow copy of this ClauseElement.
Method ​_cloned​_set Return the set consisting all cloned ancestors of this ClauseElement.
Method ​_compile​_w​_cache Undocumented
Method ​_compiler Return a compiler appropriate for this ClauseElement, given a Dialect.
Method ​_execute​_on​_connection Undocumented
Method ​_negate Undocumented
Method ​_negate​_in​_binary a hook to allow the right side of a binary expression to respond to a negation of the binary expression.
Method ​_replace​_params Undocumented
Method ​_set​_propagate​_attrs Undocumented
Method ​_ungroup Return this _expression.ClauseElement without any groupings.
Method ​_with​_binary​_element​_type in the context of binary expression, convert the type of this object to the one given.
Class Variable ​_is​_bind​_parameter Undocumented
Class Variable ​_is​_clause​_list Undocumented
Class Variable ​_is​_clone​_of Undocumented
Class Variable ​_is​_from​_container Undocumented
Class Variable ​_is​_immutable Undocumented
Class Variable ​_is​_lambda​_element Undocumented
Class Variable ​_is​_select​_container Undocumented
Class Variable ​_is​_singleton​_constant Undocumented
Class Variable ​_is​_text​_clause Undocumented
Class Variable ​_is​_textual Undocumented
Class Variable ​_order​_by​_label​_element Undocumented
Class Variable description Undocumented
Class Variable is​_clause​_element Undocumented
Class Variable stringify​_dialect Undocumented
Class Variable supports​_execution Undocumented
Instance Variable ​_propagate​_attrs like annotations, however these propagate outwards liberally as SQL constructs are built, and are set up at construction time.
Property ​_constructor return the 'constructor' for this ClauseElement.
Property entity​_namespace Undocumented

Inherited from SQLRole (via GenerativeSelect, SelectBase, Selectable, ReturnsRows, ClauseElement):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from SupportsWrappingAnnotations (via GenerativeSelect, SelectBase, Selectable, ReturnsRows, ClauseElement):

Method ​_annotate return a copy of this ClauseElement with annotations updated by the given dictionary.
Method ​_deannotate return a copy of this _expression.ClauseElement with annotations removed.
Method ​_with​_annotations return a copy of this ClauseElement with annotations replaced by the given dictionary.

Inherited from SupportsAnnotations (via GenerativeSelect, SelectBase, Selectable, ReturnsRows, ClauseElement, SupportsWrappingAnnotations):

Property ​_annotations​_cache​_key Undocumented

Inherited from MemoizedHasCacheKey (via GenerativeSelect, SelectBase, Selectable, ReturnsRows, ClauseElement):

Method ​_generate​_cache​_key return a cache key.

Inherited from HasCacheKey (via GenerativeSelect, SelectBase, Selectable, ReturnsRows, ClauseElement, MemoizedHasCacheKey):

Class Variable inherit​_cache Indicate if this .HasCacheKey instance should make use of the cache key generation scheme used by its immediate superclass.
Class Method ​_generate​_cache​_attrs generate cache key dispatcher for a new class.
Class Method ​_generate​_cache​_key​_for​_object Undocumented
Method ​_gen​_cache​_key return an optional cache key.
Class Variable __slots__ Undocumented
Class Variable ​_hierarchy​_supports​_caching private attribute which may be set to False to prevent the inherit_cache warning from being emitted for a hierarchy of subclasses.
Class Variable ​_is​_has​_cache​_key Undocumented

Inherited from Traversible (via GenerativeSelect, SelectBase, Selectable, ReturnsRows, ClauseElement):

Method __class​_getitem__ Undocumented
@classmethod
@util.deprecated('2.0', 'The legacy calling style of :func:`_sql.select` is deprecated and will be removed in SQLAlchemy 2.0. Please use the new calling style described at :func:`_sql.select`.')
def create_legacy_select(cls, columns=None, whereclause=None, from_obj=None, distinct=False, having=None, correlate=True, prefixes=None, suffixes=None, **kwargs):

Construct a new _expression.Select using the 1.x style API.

This method is called implicitly when the _expression.select construct is used and the first argument is a Python list or other plain sequence object, which is taken to refer to the columns collection.

Changed in version 1.4: Added the .Select.create_legacy_select constructor which documents the calling style in use when the .select construct is invoked using 1.x-style arguments.

Similar functionality is also available via the _expression.FromClause.select method on any _expression.FromClause.

All arguments which accept _expression.ClauseElement arguments also accept string arguments, which will be converted as appropriate into either _expression.text() or _expression.literal_column() constructs.

See Also

:ref:`coretutorial_selecting` - Core Tutorial description of _expression.select.

Parameters
columns

A list of _expression.ColumnElement or _expression.FromClause objects which will form the columns clause of the resulting statement. For those objects that are instances of _expression.FromClause (typically _schema.Table or _expression.Alias objects), the _expression.FromClause.c collection is extracted to form a collection of _expression.ColumnElement objects.

This parameter will also accept _expression.TextClause constructs as given, as well as ORM-mapped classes.

Note

The :paramref:`_expression.select.columns` parameter is not available in the method form of _expression.select, e.g. _expression.FromClause.select.

See Also

_expression.Select.column

_expression.Select.with_only_columns

whereclause

A _expression.ClauseElement expression which will be used to form the WHERE clause. It is typically preferable to add WHERE criterion to an existing _expression.Select using method chaining with _expression.Select.where.

See Also

_expression.Select.where

from​_obj

A list of _expression.ClauseElement objects which will be added to the FROM clause of the resulting statement. This is equivalent to calling _expression.Select.select_from using method chaining on an existing _expression.Select object.

See Also

_expression.Select.select_from - full description of explicit FROM clause specification.

distinctUndocumented
having

a _expression.ClauseElement that will comprise the HAVING clause of the resulting select when GROUP BY is used. This parameter is typically specified more naturally using the _expression.Select.having method on an existing _expression.Select.

See Also

_expression.Select.having

correlateUndocumented
prefixesUndocumented
suffixesUndocumented
**kwargsUndocumented
bind=​Nonean _engine.Engine or _engine.Connection instance to which the resulting _expression.Select object will be bound. The _expression.Select object will otherwise automatically bind to whatever ~.base.Connectable instances can be located within its contained _expression.ClauseElement members.
correlate=​True

indicates that this _expression.Select object should have its contained _expression.FromClause elements "correlated" to an enclosing _expression.Select object. It is typically preferable to specify correlations on an existing _expression.Select construct using _expression.Select.correlate.

See Also

_expression.Select.correlate - full description of correlation.

distinct=​False

when True, applies a DISTINCT qualifier to the columns clause of the resulting statement.

The boolean argument may also be a column expression or list of column expressions - this is a special calling form which is understood by the PostgreSQL dialect to render the DISTINCT ON (<columns>) syntax.

distinct is also available on an existing _expression.Select object via the _expression.Select.distinct method.

See Also

_expression.Select.distinct

group​_by

a list of _expression.ClauseElement objects which will comprise the GROUP BY clause of the resulting select. This parameter is typically specified more naturally using the _expression.Select.group_by method on an existing _expression.Select.

See Also

_expression.Select.group_by

limit=​None

a numerical value which usually renders as a LIMIT expression in the resulting select. Backends that don't support LIMIT will attempt to provide similar functionality. This parameter is typically specified more naturally using the _expression.Select.limit method on an existing _expression.Select.

See Also

_expression.Select.limit

offset=​None

a numeric value which usually renders as an OFFSET expression in the resulting select. Backends that don't support OFFSET will attempt to provide similar functionality. This parameter is typically specified more naturally using the _expression.Select.offset method on an existing _expression.Select.

See Also

_expression.Select.offset

order​_by

a scalar or list of _expression.ClauseElement objects which will comprise the ORDER BY clause of the resulting select. This parameter is typically specified more naturally using the _expression.Select.order_by method on an existing _expression.Select.

See Also

_expression.Select.order_by

use_labels=​False

when True, the statement will be generated using labels for each column in the columns clause, which qualify each column with its parent table's (or aliases) name so that name conflicts between columns in different tables don't occur. The format of the label is <tablename>_<column>. The "c" collection of a _expression.Subquery created against this _expression.Select object, as well as the _expression.Select.selected_columns collection of the _expression.Select itself, will use these names for targeting column members.

This parameter can also be specified on an existing _expression.Select object using the _expression.Select.set_label_style method.

See Also

_expression.Select.set_label_style

@_generative
def add_columns(self, *columns):

Return a new _expression.select construct with the given column expressions added to its columns clause.

E.g.:

my_select = my_select.add_columns(table.c.new_column)

See the documentation for _expression.Select.with_only_columns for guidelines on adding /replacing the columns of a _expression.Select object.

@util.deprecated('1.4', 'The :meth:`_expression.Select.column` method is deprecated and will be removed in a future release. Please use :meth:`_expression.Select.add_columns`')
def column(self, column):

Return a new _expression.select construct with the given column expression added to its columns clause.

E.g.:

my_select = my_select.column(table.c.new_column)

See the documentation for _expression.Select.with_only_columns for guidelines on adding /replacing the columns of a _expression.Select object.

@_generative
def correlate(self, *fromclauses):

Return a new _expression.Select which will correlate the given FROM clauses to that of an enclosing _expression.Select.

Calling this method turns off the _expression.Select object's default behavior of "auto-correlation". Normally, FROM elements which appear in a _expression.Select that encloses this one via its :term:`WHERE clause`, ORDER BY, HAVING or :term:`columns clause` will be omitted from this _expression.Select object's :term:`FROM clause`. Setting an explicit correlation collection using the _expression.Select.correlate method provides a fixed list of FROM objects that can potentially take place in this process.

When _expression.Select.correlate is used to apply specific FROM clauses for correlation, the FROM elements become candidates for correlation regardless of how deeply nested this _expression.Select object is, relative to an enclosing _expression.Select which refers to the same FROM object. This is in contrast to the behavior of "auto-correlation" which only correlates to an immediate enclosing _expression.Select. Multi-level correlation ensures that the link between enclosed and enclosing _expression.Select is always via at least one WHERE/ORDER BY/HAVING/columns clause in order for correlation to take place.

If None is passed, the _expression.Select object will correlate none of its FROM entries, and all will render unconditionally in the local FROM clause.

See Also

_expression.Select.correlate_except

:ref:`correlated_subqueries`

Parameters
*fromclausesa list of one or more _expression.FromClause constructs, or other compatible constructs (i.e. ORM-mapped classes) to become part of the correlate collection.
@_generative
def correlate_except(self, *fromclauses):

Return a new _expression.Select which will omit the given FROM clauses from the auto-correlation process.

Calling _expression.Select.correlate_except turns off the _expression.Select object's default behavior of "auto-correlation" for the given FROM elements. An element specified here will unconditionally appear in the FROM list, while all other FROM elements remain subject to normal auto-correlation behaviors.

If None is passed, the _expression.Select object will correlate all of its FROM entries.

See Also

_expression.Select.correlate

:ref:`correlated_subqueries`

Parameters
*fromclausesa list of one or more _expression.FromClause constructs, or other compatible constructs (i.e. ORM-mapped classes) to become part of the correlate-exception collection.
@_generative
def distinct(self, *expr):
Return a new _expression.select construct which will apply DISTINCT to its columns clause.
Parameters
*expr

optional column expressions. When present, the PostgreSQL dialect will render a DISTINCT ON (<expressions>>) construct.

Deprecated since version 1.4: Using *expr in other dialects is deprecated and will raise _exc.CompileError in a future version.
def except_(self, *other, **kwargs):
Return a SQL EXCEPT of this select() construct against the given selectable provided as positional arguments.
Parameters
*other

one or more elements with which to create a UNION.

Changed in version 1.4.28: multiple elements are now accepted.
**kwargskeyword arguments are forwarded to the constructor for the newly created _sql.CompoundSelect object.
def except_all(self, *other, **kwargs):
Return a SQL EXCEPT ALL of this select() construct against the given selectables provided as positional arguments.
Parameters
*other

one or more elements with which to create a UNION.

Changed in version 1.4.28: multiple elements are now accepted.
**kwargskeyword arguments are forwarded to the constructor for the newly created _sql.CompoundSelect object.
def filter(self, *criteria):
A synonym for the _future.Select.where method.
def filter_by(self, **kwargs):
apply the given filtering criterion as a WHERE clause to this select.
def from_statement(self, statement):

Apply the columns which this .Select would select onto another statement.

This operation is :term:`plugin-specific` and will raise a not supported exception if this _sql.Select does not select from plugin-enabled entities.

The statement is typically either a _expression.text or _expression.select construct, and should return the set of columns appropriate to the entities represented by this .Select.

See Also

:ref:`orm_queryguide_selecting_text` - usage examples in the ORM Querying Guide

def get_children(self, **kwargs):

Return immediate child .visitors.Traversible elements of this .visitors.Traversible.

This is used for visit traversal.

**kw may contain flags that change the collection that is returned, for example to return a subset of items in order to cut down on larger traversals, or to return child items from a different context (such as schema-level collections instead of clause-level).

def get_final_froms(self):

Compute the final displayed list of _expression.FromClause elements.

This method will run through the full computation required to determine what FROM elements will be displayed in the resulting SELECT statement, including shadowing individual tables with JOIN objects, as well as full computation for ORM use cases including eager loading clauses.

For ORM use, this accessor returns the post compilation list of FROM objects; this collection will include elements such as eagerly loaded tables and joins. The objects will not be ORM enabled and not work as a replacement for the _sql.Select.select_froms collection; additionally, the method is not well performing for an ORM enabled statement as it will incur the full ORM construction process.

To retrieve the FROM list that's implied by the "columns" collection passed to the _sql.Select originally, use the _sql.Select.columns_clause_froms accessor.

To select from an alternative set of columns while maintaining the FROM list, use the _sql.Select.with_only_columns method and pass the :paramref:`_sql.Select.with_only_columns.maintain_column_froms` parameter.

New in version 1.4.23: - the _sql.Select.get_final_froms method replaces the previous _sql.Select.froms accessor, which is deprecated.

See Also

_sql.Select.columns_clause_froms

@_generative
def having(self, having):
Return a new _expression.select construct with the given expression added to its HAVING clause, joined to the existing clause via AND, if any.
def intersect(self, *other, **kwargs):
Return a SQL INTERSECT of this select() construct against the given selectables provided as positional arguments.
Parameters
*other

one or more elements with which to create a UNION.

Changed in version 1.4.28: multiple elements are now accepted.
**kwargskeyword arguments are forwarded to the constructor for the newly created _sql.CompoundSelect object.
def intersect_all(self, *other, **kwargs):
Return a SQL INTERSECT ALL of this select() construct against the given selectables provided as positional arguments.
Parameters
*other

one or more elements with which to create a UNION.

Changed in version 1.4.28: multiple elements are now accepted.
**kwargskeyword arguments are forwarded to the constructor for the newly created _sql.CompoundSelect object.
@_generative
def join(self, target, onclause=None, isouter=False, full=False):

Create a SQL JOIN against this _expression.Select object's criterion and apply generatively, returning the newly resulting _expression.Select.

E.g.:

stmt = select(user_table).join(address_table, user_table.c.id == address_table.c.user_id)

The above statement generates SQL similar to:

SELECT user.id, user.name FROM user JOIN address ON user.id = address.user_id

Changed in version 1.4: _expression.Select.join now creates a _sql.Join object between a _sql.FromClause source that is within the FROM clause of the existing SELECT, and a given target _sql.FromClause, and then adds this _sql.Join to the FROM clause of the newly generated SELECT statement. This is completely reworked from the behavior in 1.3, which would instead create a subquery of the entire _expression.Select and then join that subquery to the target.

This is a backwards incompatible change as the previous behavior was mostly useless, producing an unnamed subquery rejected by most databases in any case. The new behavior is modeled after that of the very successful _orm.Query.join method in the ORM, in order to support the functionality of _orm.Query being available by using a _sql.Select object with an _orm.Session.

See the notes for this change at :ref:`change_select_join`.

See Also

:ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

:ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

_expression.Select.join_from

_expression.Select.outerjoin

Parameters
targettarget table to join towards
onclauseON clause of the join. If omitted, an ON clause is generated automatically based on the _schema.ForeignKey linkages between the two tables, if one can be unambiguously determined, otherwise an error is raised.
isouterif True, generate LEFT OUTER join. Same as _expression.Select.outerjoin.
fullif True, generate FULL OUTER join.
@_generative
def join_from(self, from_, target, onclause=None, isouter=False, full=False):

Create a SQL JOIN against this _expression.Select object's criterion and apply generatively, returning the newly resulting _expression.Select.

E.g.:

stmt = select(user_table, address_table).join_from(
    user_table, address_table, user_table.c.id == address_table.c.user_id
)

The above statement generates SQL similar to:

SELECT user.id, user.name, address.id, address.email, address.user_id
FROM user JOIN address ON user.id = address.user_id
New in version 1.4.
Parameters
from​_the left side of the join, will be rendered in the FROM clause and is roughly equivalent to using the .Select.select_from method.
targettarget table to join towards
onclauseON clause of the join.
isouterif True, generate LEFT OUTER join. Same as _expression.Select.outerjoin.
fullif True, generate FULL OUTER join.
def outerjoin(self, target, onclause=None, full=False):

Create a left outer join.

Parameters are the same as that of _expression.Select.join.

Changed in version 1.4: _expression.Select.outerjoin now creates a _sql.Join object between a _sql.FromClause source that is within the FROM clause of the existing SELECT, and a given target _sql.FromClause, and then adds this _sql.Join to the FROM clause of the newly generated SELECT statement. This is completely reworked from the behavior in 1.3, which would instead create a subquery of the entire _expression.Select and then join that subquery to the target.

This is a backwards incompatible change as the previous behavior was mostly useless, producing an unnamed subquery rejected by most databases in any case. The new behavior is modeled after that of the very successful _orm.Query.join method in the ORM, in order to support the functionality of _orm.Query being available by using a _sql.Select object with an _orm.Session.

See the notes for this change at :ref:`change_select_join`.

def outerjoin_from(self, from_, target, onclause=None, full=False):

Create a SQL LEFT OUTER JOIN against this _expression.Select object's criterion and apply generatively, returning the newly resulting _expression.Select.

Usage is the same as that of _selectable.Select.join_from.

@util.preload_module('sqlalchemy.sql.util')
def reduce_columns(self, only_synonyms=True):

Return a new _expression.select construct with redundantly named, equivalently-valued columns removed from the columns clause.

"Redundant" here means two columns where one refers to the other either based on foreign key, or via a simple equality comparison in the WHERE clause of the statement. The primary purpose of this method is to automatically construct a select statement with all uniquely-named columns, without the need to use table-qualified labels as _expression.Select.set_label_style does.

When columns are omitted based on foreign key, the referred-to column is the one that's kept. When columns are omitted based on WHERE equivalence, the first column in the columns clause is the one that's kept.

Parameters
only​_synonymswhen True, limit the removal of columns to those which have the same name as the equivalent. Otherwise, all columns that are equivalent to another are removed.
@_generative
def select_from(self, *froms):

Return a new _expression.select construct with the given FROM expression(s) merged into its list of FROM objects.

E.g.:

table1 = table('t1', column('a'))
table2 = table('t2', column('b'))
s = select(table1.c.a).\
    select_from(
        table1.join(table2, table1.c.a==table2.c.b)
    )

The "from" list is a unique set on the identity of each element, so adding an already present _schema.Table or other selectable will have no effect. Passing a _expression.Join that refers to an already present _schema.Table or other selectable will have the effect of concealing the presence of that selectable as an individual element in the rendered FROM list, instead rendering it into a JOIN clause.

While the typical purpose of _expression.Select.select_from is to replace the default, derived FROM clause with a join, it can also be called with individual table elements, multiple times if desired, in the case that the FROM clause cannot be fully derived from the columns clause:

select(func.count('*')).select_from(table1)
def self_group(self, against=None):

Return a 'grouping' construct as per the _expression.ClauseElement specification.

This produces an element that can be embedded in an expression. Note that this method is called automatically as needed when constructing expressions and should not require explicit use.

def union(self, *other, **kwargs):
Return a SQL UNION of this select() construct against the given selectables provided as positional arguments.
Parameters
*other

one or more elements with which to create a UNION.

Changed in version 1.4.28: multiple elements are now accepted.
**kwargskeyword arguments are forwarded to the constructor for the newly created _sql.CompoundSelect object.
def union_all(self, *other, **kwargs):
Return a SQL UNION ALL of this select() construct against the given selectables provided as positional arguments.
Parameters
*other

one or more elements with which to create a UNION.

Changed in version 1.4.28: multiple elements are now accepted.
**kwargskeyword arguments are forwarded to the constructor for the newly created _sql.CompoundSelect object.
@_generative
def where(self, *whereclause):
Return a new _expression.select construct with the given expression added to its WHERE clause, joined to the existing clause via AND, if any.
@_generative
def with_only_columns(self, *columns, **kw):

Return a new _expression.select construct with its columns clause replaced with the given columns.

By default, this method is exactly equivalent to as if the original _expression.select had been called with the given columns clause. E.g. a statement:

s = select(table1.c.a, table1.c.b)
s = s.with_only_columns(table1.c.b)

should be exactly equivalent to:

s = select(table1.c.b)

In this mode of operation, _sql.Select.with_only_columns will also dynamically alter the FROM clause of the statement if it is not explicitly stated. To maintain the existing set of FROMs including those implied by the current columns clause, add the :paramref:`_sql.Select.with_only_columns.maintain_column_froms` parameter:

s = select(table1.c.a, table2.c.b)
s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

The above parameter performs a transfer of the effective FROMs in the columns collection to the _sql.Select.select_from method, as though the following were invoked:

s = select(table1.c.a, table2.c.b)
s = s.select_from(table1, table2).with_only_columns(table1.c.a)

The :paramref:`_sql.Select.with_only_columns.maintain_column_froms` parameter makes use of the _sql.Select.columns_clause_froms collection and performs an operation equivalent to the following:

s = select(table1.c.a, table2.c.b)
s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)
Parameters
*columns

column expressions to be used.

Changed in version 1.4: the _sql.Select.with_only_columns method accepts the list of column expressions positionally; passing the expressions as a list is deprecated.
**kwUndocumented
maintain​_column​_froms

boolean parameter that will ensure the FROM list implied from the current columns clause will be transferred to the _sql.Select.select_from method first.

New in version 1.4.23.
@classmethod
def _create(cls, *args, **kw):

Create a .Select using either the 1.x or 2.0 constructor style.

For the legacy calling style, see .Select.create_legacy_select. If the first argument passed is a Python sequence or if keyword arguments are present, this style is used.

New in version 2.0: - the _future.select construct is the same construct as the one returned by _expression.select, except that the function only accepts the "columns clause" entities up front; the rest of the state of the SELECT should be built up using generative methods.

Similar functionality is also available via the _expression.FromClause.select method on any _expression.FromClause.

See Also

:ref:`coretutorial_selecting` - Core Tutorial description of _expression.select.

Parameters
*argsUndocumented
**kwUndocumented
entities

Entities to SELECT from. For Core usage, this is typically a series of _expression.ColumnElement and / or _expression.FromClause objects which will form the columns clause of the resulting statement. For those objects that are instances of _expression.FromClause (typically _schema.Table or _expression.Alias objects), the _expression.FromClause.c collection is extracted to form a collection of _expression.ColumnElement objects.

This parameter will also accept _expression.TextClause constructs as given, as well as ORM-mapped classes.

@classmethod
def _create_future_select(cls, *entities):

Construct a new _expression.Select using the 2. x style API.

New in version 1.4: - The _sql.select function now accepts column arguments positionally. The top-level _sql.select function will automatically use the 1.x or 2.x style API based on the incoming arguments; using _future.select from the sqlalchemy.future module will enforce that only the 2.x style constructor is used.

Similar functionality is also available via the _expression.FromClause.select method on any _expression.FromClause.

See Also

:ref:`coretutorial_selecting` - Core Tutorial description of _expression.select.

Parameters
*entities

Entities to SELECT from. For Core usage, this is typically a series of _expression.ColumnElement and / or _expression.FromClause objects which will form the columns clause of the resulting statement. For those objects that are instances of _expression.FromClause (typically _schema.Table or _expression.Alias objects), the _expression.FromClause.c collection is extracted to form a collection of _expression.ColumnElement objects.

This parameter will also accept _expression.TextClause constructs as given, as well as ORM-mapped classes.

@classmethod
def _create_raw_select(cls, **kw):

Create a .Select using raw __new__ with no coercions.

Used internally to build up .Select constructs with pre-established state.

@classmethod
def _create_select_from_fromclause(cls, target, entities, *arg, **kw):

Undocumented

def __init__(self):
@HasMemoized.memoized_attribute
def _all_selected_columns(self):

A sequence of expressions that correspond to what is rendered in the columns clause, including _sql.TextClause constructs.

New in version 1.4.12.

See Also

_sql.SelectBase.exported_columns

def _copy_internals(self, clone=_clone, **kw):

Reassign internal elements to be clones of themselves.

Called during a copy-and-traverse operation on newly shallow-copied elements to create a deep copy.

The given clone function should be used, which may be applying additional transformations to the element (i.e. replacement traversal, cloned traversal, annotations).

def _ensure_disambiguated_names(self):
Ensure that the names generated by this selectbase will be disambiguated in some way, if possible.
def _filter_by_zero(self):

Undocumented

def _generate_columns_plus_names(self, anon_for_dupe_key):

Generate column names as rendered in a SELECT statement by the compiler.

This is distinct from the _column_naming_convention generator that's intended for population of .c collections and similar, which has different rules. the collection returned here calls upon the _column_naming_convention as well.

def _generate_fromclause_column_proxies(self, subquery):
Generate column proxies to place in the exported .c collection of a subquery.
def _needs_parens_for_grouping(self):

Undocumented

def _scalar_type(self):

Undocumented

def _set_entities(self, entities):

Undocumented

@bind.setter
def bind(self, bind):

Undocumented

def is_derived_from(self, fromclause):

Undocumented

@HasMemoized.memoized_attribute
def selected_columns(self):

A _expression.ColumnCollection representing the columns that this SELECT statement or similar construct returns in its result set, not including _sql.TextClause constructs.

This collection differs from the _expression.FromClause.columns collection of a _expression.FromClause in that the columns within this collection cannot be directly nested inside another SELECT statement; a subquery must be applied first which provides for the necessary parenthesization required by SQL.

For a _expression.select construct, the collection here is exactly what would be rendered inside the "SELECT" statement, and the _expression.ColumnElement objects are directly present as they were given, e.g.:

col1 = column('q', Integer)
col2 = column('p', Integer)
stmt = select(col1, col2)

Above, stmt.selected_columns would be a collection that contains the col1 and col2 objects directly. For a statement that is against a _schema.Table or other _expression.FromClause, the collection will use the _expression.ColumnElement objects that are in the _expression.FromClause.c collection of the from element.

Note

The _sql.Select.selected_columns collection does not include expressions established in the columns clause using the _sql.text construct; these are silently omitted from the collection. To use plain textual column expressions inside of a _sql.Select construct, use the _sql.literal_column construct.

New in version 1.4.
__visit_name__: str =
_cache_key_traversal =
_having_criteria: tuple =

Undocumented

_legacy_setup_joins: tuple =

Undocumented

_memoized_select_entities: tuple =

Undocumented

_setup_joins: tuple =

Undocumented

_traverse_internals =

Undocumented

_where_criteria: tuple =

Undocumented

_auto_correlate: bool =

Undocumented

_correlate: tuple =

Undocumented

_correlate_except: tuple =

Undocumented

_distinct: bool =

Undocumented

_distinct_on =

Undocumented

_from_obj =

Undocumented

_raw_columns =

Undocumented

@property
@util.deprecated_20(':attr:`.Executable.bind`', alternative='Bound metadata is being removed as of SQLAlchemy 2.0.', enable_warnings=False)
bind =
Returns the _engine.Engine or _engine.Connection to which this .Executable is bound, or None if none found.
@property
column_descriptions =
Return a 'column descriptions' structure which may be :term:`plugin-specific`.
@property
columns_clause_froms =

Return the set of _expression.FromClause objects implied by the columns clause of this SELECT statement.

New in version 1.4.23.

See Also

_sql.Select.froms - "final" FROM list taking the full statement into account

_sql.Select.with_only_columns - makes use of this collection to set up a new FROM list

@property
@util.deprecated('1.4.23', 'The :attr:`_expression.Select.froms` attribute is moved to the :meth:`_expression.Select.get_final_froms` method.')
froms =
Return the displayed list of _expression.FromClause elements.
@property
inner_columns =

An iterator of all _expression.ColumnElement expressions which would be rendered into the columns clause of the resulting SELECT statement.

This method is legacy as of 1.4 and is superseded by the _expression.Select.exported_columns collection.

@property
whereclause =

Return the completed WHERE clause for this _expression.Select statement.

This assembles the current collection of WHERE criteria into a single _expression.BooleanClauseList construct.

New in version 1.4.