class Select(HasPrefixes, HasSuffixes, HasHints, HasCompileState, DeprecatedSelectGenerations, _SelectFromElements, GenerativeSelect):
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 |
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.
.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 This parameter will also accept Note The :paramref:`_expression.select.columns`
parameter is not available
in the method form of See Also
|
whereclause | A See Also
|
from_obj | A list of See Also
|
distinct | Undocumented |
having | a See Also
|
correlate | Undocumented |
prefixes | Undocumented |
suffixes | Undocumented |
**kwargs | Undocumented |
bind=None | an _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 See Also
|
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
See Also
|
group_by | a list of See Also
|
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 See Also
|
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 See Also
|
order_by | a scalar or list of See Also
|
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 This parameter can also be specified on an existing
See Also
|
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.
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.
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.
Parameters | |
*fromclauses | a list of one or more
_expression.FromClause
constructs, or other compatible constructs (i.e. ORM-mapped
classes) to become part of the correlate collection. |
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.
Parameters | |
*fromclauses | a 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. |
_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. |
Parameters | |
*other | one or more elements with which to create a UNION.
Changed in version 1.4.28: multiple elements are now accepted.
|
**kwargs | keyword arguments are forwarded to the constructor
for the newly created _sql.CompoundSelect object. |
Parameters | |
*other | one or more elements with which to create a UNION.
Changed in version 1.4.28: multiple elements are now accepted.
|
**kwargs | keyword arguments are forwarded to the constructor
for the newly created _sql.CompoundSelect object. |
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
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).
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.
_sql.Select.get_final_froms
method replaces the previous _sql.Select.froms
accessor,
which is deprecated.See Also
_sql.Select.columns_clause_froms
_expression.select
construct with
the given expression added to
its HAVING clause, joined to the existing clause via AND, if any.Parameters | |
*other | one or more elements with which to create a UNION.
Changed in version 1.4.28: multiple elements are now accepted.
|
**kwargs | keyword arguments are forwarded to the constructor
for the newly created _sql.CompoundSelect object. |
Parameters | |
*other | one or more elements with which to create a UNION.
Changed in version 1.4.28: multiple elements are now accepted.
|
**kwargs | keyword arguments are forwarded to the constructor
for the newly created _sql.CompoundSelect object. |
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 | |
target | target table to join towards |
onclause | ON 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. |
isouter | if True, generate LEFT OUTER join. Same as
_expression.Select.outerjoin . |
full | if True, generate FULL OUTER join. |
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
See Also
:ref:`tutorial_select_join` - in the :doc:`/tutorial/index`
:ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`
_expression.Select.join
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. |
target | target table to join towards |
onclause | ON clause of the join. |
isouter | if True, generate LEFT OUTER join. Same as
_expression.Select.outerjoin . |
full | if True, generate FULL OUTER join. |
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`.
See Also
:ref:`tutorial_select_join` - in the :doc:`/tutorial/index`
:ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`
_expression.Select.join
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
.
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_synonyms | when 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. |
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)
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.
Parameters | |
*other | one or more elements with which to create a UNION.
Changed in version 1.4.28: multiple elements are now accepted.
|
**kwargs | keyword arguments are forwarded to the constructor
for the newly created _sql.CompoundSelect object. |
Parameters | |
*other | one or more elements with which to create a UNION.
Changed in version 1.4.28: multiple elements are now accepted.
|
**kwargs | keyword arguments are forwarded to the constructor
for the newly created _sql.CompoundSelect object. |
_expression.select
construct with
the given expression added to
its WHERE clause, joined to the existing clause via AND, if any.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. |
**kw | Undocumented |
maintain_column_froms | boolean parameter that will ensure the
FROM list implied from the current columns clause will be transferred
to the
New in version 1.4.23.
|
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.
_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 | |
*args | Undocumented |
**kw | Undocumented |
entities | Entities to SELECT from. For Core usage, this is typically a series
of This parameter will also accept |
Construct a new _expression.Select
using the 2.
x style API.
_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 This parameter will also accept |
Create a .Select
using raw __new__ with no coercions.
Used internally to build up .Select
constructs with
pre-established state.
A sequence of expressions that correspond to what is rendered
in the columns clause, including _sql.TextClause
constructs.
See Also
_sql.SelectBase.exported_columns
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).
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.
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.
sqlalchemy.sql.base.Executable.bind
_engine.Engine
or _engine.Connection
to which this .Executable
is bound, or None if none found.Return the set of _expression.FromClause
objects implied
by the columns clause of this SELECT statement.
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
_expression.FromClause
elements.