class documentation

class CompoundSelect(HasCompileState, GenerativeSelect):

View In Hierarchy

Forms the basis of UNION, UNION ALL, and other SELECT-based set operations.

See Also

_expression.union

_expression.union_all

_expression.intersect

_expression.intersect_all

_expression.except

_expression.except_all

Method self​_group Apply a 'grouping' to this _expression.ClauseElement.
Constant EXCEPT Undocumented
Constant EXCEPT​_ALL Undocumented
Constant INTERSECT Undocumented
Constant INTERSECT​_ALL Undocumented
Constant UNION Undocumented
Constant UNION​_ALL Undocumented
Class Method ​_create​_except Return an EXCEPT of multiple selectables.
Class Method ​_create​_except​_all Return an EXCEPT ALL of multiple selectables.
Class Method ​_create​_intersect Return an INTERSECT of multiple selectables.
Class Method ​_create​_intersect​_all Return an INTERSECT ALL of multiple selectables.
Class Method ​_create​_union Return a UNION of multiple selectables.
Class Method ​_create​_union​_all Return a UNION ALL of multiple selectables.
Method __init__ Undocumented
Method ​_ensure​_disambiguated​_names Ensure that the names generated by this selectbase will be disambiguated in some way, if possible.
Method ​_generate​_fromclause​_column​_proxies Undocumented
Method ​_refresh​_for​_new​_column Undocumented
Method ​_scalar​_type Undocumented
Method ​_set​_label​_style Undocumented
Method bind.setter Undocumented
Method is​_derived​_from Undocumented
Class Variable __visit​_name__ Undocumented
Class Variable ​_is​_from​_container Undocumented
Class Variable ​_traverse​_internals Undocumented
Instance Variable ​_auto​_correlate Undocumented
Instance Variable ​_bind Undocumented
Instance Variable keyword Undocumented
Instance Variable selects Undocumented
Property ​_all​_selected​_columns A sequence of expressions that correspond to what is rendered in the columns clause, including _sql.TextClause constructs.
Property bind Returns the _engine.Engine or _engine.Connection to which this .Executable is bound, or None if none found.
Property selected​_columns No summary

Inherited from HasCompileState:

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

Inherited from Generative (via HasCompileState):

Method ​_generate 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 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 ​_cache​_key​_traversal Undocumented
Class Variable ​_is​_bind​_parameter Undocumented
Class Variable ​_is​_clause​_list Undocumented
Class Variable ​_is​_clone​_of 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 HasCopyInternals (via GenerativeSelect, SelectBase, Selectable, ReturnsRows, ClauseElement):

Method ​_copy​_internals Reassign internal elements to be clones of themselves.

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

Method get​_children Return immediate child .visitors.Traversible elements of this .visitors.Traversible.
Method __class​_getitem__ Undocumented
def self_group(self, against=None):

Apply a 'grouping' to this _expression.ClauseElement.

This method is overridden by subclasses to return a "grouping" construct, i.e. parenthesis. In particular it's used by "binary" expressions to provide a grouping around themselves when placed into a larger expression, as well as by _expression.select constructs when placed into the FROM clause of another _expression.select. (Note that subqueries should be normally created using the _expression.Select.alias method, as many platforms require nested SELECT statements to be named).

As expressions are composed together, the application of self_group is automatic - end-user code should never need to use this method directly. Note that SQLAlchemy's clause constructs take operator precedence into account - so parenthesis might not be needed, for example, in an expression like x OR (y AND z) - AND takes precedence over OR.

The base self_group method of _expression.ClauseElement just returns self.

EXCEPT =

Undocumented

Value
util.symbol('EXCEPT')
EXCEPT_ALL =

Undocumented

Value
util.symbol('EXCEPT ALL')
INTERSECT =

Undocumented

Value
util.symbol('INTERSECT')
INTERSECT_ALL =

Undocumented

Value
util.symbol('INTERSECT ALL')
UNION =

Undocumented

Value
util.symbol('UNION')
UNION_ALL =

Undocumented

Value
util.symbol('UNION ALL')
@classmethod
def _create_except(cls, *selects, **kwargs):

Return an EXCEPT of multiple selectables.

The returned object is an instance of _expression.CompoundSelect.

Parameters
*selectsa list of _expression.Select instances.
**kwargsavailable keyword arguments are the same as those of select.
@classmethod
def _create_except_all(cls, *selects, **kwargs):

Return an EXCEPT ALL of multiple selectables.

The returned object is an instance of _expression.CompoundSelect.

Parameters
*selectsa list of _expression.Select instances.
**kwargsavailable keyword arguments are the same as those of select.
@classmethod
def _create_intersect(cls, *selects, **kwargs):

Return an INTERSECT of multiple selectables.

The returned object is an instance of _expression.CompoundSelect.

Parameters
*selectsa list of _expression.Select instances.
**kwargsavailable keyword arguments are the same as those of select.
@classmethod
def _create_intersect_all(cls, *selects, **kwargs):

Return an INTERSECT ALL of multiple selectables.

The returned object is an instance of _expression.CompoundSelect.

Parameters
*selectsa list of _expression.Select instances.
**kwargsavailable keyword arguments are the same as those of select.
@classmethod
def _create_union(cls, *selects, **kwargs):

Return a UNION of multiple selectables.

The returned object is an instance of _expression.CompoundSelect.

A similar union() method is available on all _expression.FromClause subclasses.

Parameters
*selectsa list of _expression.Select instances.
**kwargsavailable keyword arguments are the same as those of select.
@classmethod
def _create_union_all(cls, *selects, **kwargs):

Return a UNION ALL of multiple selectables.

The returned object is an instance of _expression.CompoundSelect.

A similar union_all() method is available on all _expression.FromClause subclasses.

Parameters
*selectsa list of _expression.Select instances.
**kwargsavailable keyword arguments are the same as those of select.
def __init__(self, keyword, *selects, **kwargs):
def _ensure_disambiguated_names(self):
Ensure that the names generated by this selectbase will be disambiguated in some way, if possible.
def _generate_fromclause_column_proxies(self, subquery):
def _refresh_for_new_column(self, column):
def _scalar_type(self):

Undocumented

def _set_label_style(self, style):

Undocumented

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

Undocumented

def is_derived_from(self, fromclause):

Undocumented

__visit_name__: str =
_is_from_container: bool =
_traverse_internals =

Undocumented

_auto_correlate =

Undocumented

keyword =

Undocumented

selects =

Undocumented

@property
_all_selected_columns =

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

@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
selected_columns =

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

For a _expression.CompoundSelect, the _expression.CompoundSelect.selected_columns attribute returns the selected columns of the first SELECT statement contained within the series of statements within the set operation.

See Also

_sql.Select.selected_columns

New in version 1.4.