U
|
¸ý°dq¥ ã @ sj
|
d Z ddlmZ ddlZddlmZ ddlZddlmZ ddlm Z
|
ddlm Z ddlm Z ddlm Z dd lm Z dd
|
lmZ dd lmZ dd lmZ dd lmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlm Z ddl m Z ddl m Z dd l m Z dd l m!Z! dd l m"Z" dd l m#Z# dd l m$Z$ dd!l%m&Z& dd"l%m'Z' dd#l%m(Z( dd$l%m)Z) dd%l%m*Z* dd&l%m+Z+ dd'l%m,Z, dd(l%m-Z- dd)l.m/Z/ dd*l.m0Z0 dd+l1m2Z2 dd,l1m3Z3 dd-l1m4Z4 dd.l1m5Z5 dd/l1m6Z6 dd0l1m7Z7 dd1l1m8Z8 dd2l1m9Z9 dd3l1m:Z: dd4l1m;Z; dd5l1m<Z< dd6l1m=Z= dd7l1m>Z> dd8l1m?Z? dd9l1m@Z@ dd:l1mAZA dd;l1mBZB dd<l1mCZC dd=l1mDZD dd>l1mEZE dd?l1mFZF dd@l mGZG ddAlHmIZI ddBlHmJZJ ddClHmKZK ddDlHmLZL ddElHmMZM ddFlHmNZN ddGlHmOZO ddHlHmPZP ddIlHmQZQ ddJlHmRZR ddKlHmSZS ddLlHmTZT ddMl mUZU ddNlVmWZW ddOl$mXZX ddPl$mYZY ddQl$mZZZ dRdSl m[Z[ dRdTl m\Z\ dRdUl\m]Z] dRdVl^m_Z_ dRdWl^m`Z` dRdXl^maZa eKjbZbedYe dZZcerðdd[l%mdZd dd\l%meZe dd]l%mfZf dd^l%mgZg dd_l%mhZh dd`l%miZi ddal%mjZj ddbl%mkZk ddcl%mlZl dddl%mmZm ddel%mnZn ddfl%moZo ddgl%mpZp ddhl%mqZq ddil%mrZr ddjl%msZs ddkl%mtZt ddll%muZu ddml%mvZw ddnl%mxZx ddol1myZy ddpl1mzZz ddql1m{Z{ ddrl m|Z| ddsl}m~Z~ ddtlmZ ddulmZ ddvlHmZ ddwlHmZ ddxlHmZ ddylHm
Z
ddzlHmZ dd{lmZ dd|lmZ dd}lmZ dd~lVmZ ddl#mZ ddl$mZ e deOe df Ze e d gee f ZG dd
d
e`Ze def Ze def Ze e d ed f Zeeee ed e ee f f Zee d ZG dd deJe ZG dd de!jePZG dd deBeZG dd deee( ZG dd deZG dd dZG dd dZ G dd dZ¡G dd de!j¢eZ£G dd de£Z¤G dd deZ¥e¦e¥\Z§Z¨Z©Zªe©Z«G dd de!j¬e£ZG d d¡ d¡Z®G d¢d£ d£e¤Z¯G d¤d¥ d¥e®e¤Z°G d¦d§ d§e°Z±G d¨d© d©e!j¬e±Z²G dªd« d«e¯e²Z³G d¬d de±e¯Z´G d®d¯ d¯e±ZµG d°d± d±e!j¬e!j¶eCee e°Z·G d²d³ d³eZ¸G d´dµ dµeZ¹G d¶d· d·eZºG d¸d¹ d¹e!j»eºZ¼G dºd» d»e°Z½G d¼d½ d½eQe£Z¾G d¾d¿ d¿e¾e¤Z¿G dÀdÁ dÁe!j¬eFe¤ZÀe dÂdeÁe ee f f ZÂG dÃd dÂeLZÃG dÄdÅ dÅe!jÄeCe¯ZÅG dÆdÇ dÇe!jÄeQeOe ZÆG dÈdÉ dÉe!jÇe!jÈe!jÉe!jÄe¼e0e ZÊedÊeÊdZZËG dËdÌ dÌeQeÊeeË ZÌG dÍdÎ dÎeÊeCZÍe@ ÎdÏdСG dÑdÒ dÒe@ZÏG dÓdÔ dÔeZÐG dÕdÖ dÖeDeÍeZÑeÐD ]ZÒeÓeÑeÒjÔeÒ qe@ ÎdÏdסG dØdÙ dÙe\jÕe@ZÖG dÚdÛ dÛZ×G dÜdÝ dÝe jØe"jÙe$jÚZÛG dÞdß dßee e¡eDe×eÍee( ZÜG dàdá dáe!jÄeCeQeOec ZÝG dâdã dãeTeÁ ZÞG dädå dåeÊeeCZßeßZàG dædç dçe/ZádS )èztThe :class:`_expression.FromClause` class of SQL expression elements,
|
representing
|
SQL tables and derived rowsets.
|
|
é )Ú annotationsN)ÚEnum)Ú AbstractSet)ÚAny)ÚCallable)Úcast)ÚDict)ÚGeneric)ÚIterable)ÚIterator)ÚList)Ú
|
NamedTuple)ÚNoReturn)ÚOptional)Úoverload)ÚSequence)ÚSet)ÚTuple)ÚType)Ú TYPE_CHECKING)ÚTypeVar)ÚUnioné )Ú cache_key)Ú coercions)Ú operators)Úroles)Ú
|
traversals)Útype_api)Úvisitors)Ú_ColumnsClauseArgument)Ú_no_kw)Ú_TP©Úis_column_element)Úis_select_statement)Ú is_subquery)Úis_table)Úis_text_clause)Ú Annotated)ÚSupportsCloneAnnotations)Ú_clone)Ú_cloned_difference©Ú_cloned_intersection©Ú_entity_namespace_key)Ú_EntityNamespace)Ú_expand_cloned©Ú _from_objects)Ú _generative©Ú_never_select_column)Ú_NoArg)Ú_select_iterables)ÚCacheableOptions)ÚColumnCollection)Ú ColumnSet)Ú CompileState)ÚDedupeColumnCollection)Ú
|
Executable)Ú
|
Generative)ÚHasCompileState)Ú HasMemoized)Ú Immutable)Ú_document_text_coercion)Ú_anonymous_label)Ú BindParameter)ÚBooleanClauseList)Ú ClauseElement)Ú
|
ClauseList)Ú ColumnClause)Ú ColumnElement)ÚDQLDMLClauseElement)ÚGroupedElement)Úliteral_column)ÚTableValuedColumn)ÚUnaryExpression)Ú OperatorType)ÚNULLTYPE)Ú_TraverseInternalsType)ÚInternalTraversal)Úprefix_anon_mapé )Úexc)Úutil)Ú!HasMemoized_ro_memoized_attribute)ÚLiteral)ÚProtocol)ÚSelfÚ_T)Úbound)Ú_ColumnExpressionArgument)Ú#_ColumnExpressionOrStrLabelArgument)Ú_FromClauseArgument)Ú_JoinTargetArgument)Ú_LimitOffsetType)Ú _MAYBE_ENTITY)Ú _NOT_ENTITY)Ú_OnClauseArgument)Ú#_SelectStatementForCompoundArgument)Ú_T0)Ú_T1)Ú_T2)Ú_T3)Ú_T4)Ú_T5)Ú_T6)Ú_T7)Ú _TextCoercedExpressionArgument)Ú_TypedColumnClauseArgument)Ú_TypeEngineArgument)Ú_AmbiguousTableNameMap)ÚExecutableOption)ÚReadOnlyColumnCollection)Ú_CacheKeyTraversalType)Ú SQLCompiler)ÚDelete)ÚUpdate)ÚBinaryExpression)ÚKeyedColumnElement)ÚLabel)Ú NamedColumn)Ú
|
TextClause)ÚFunction)Ú
|
ForeignKey)ÚForeignKeyConstraint)ÚTableValueType)Ú
|
TypeEngine)Ú_CloneCallableTypeÚ
|
FromClauser~ )úColumnElement[Any]r~ c @ s4 e Zd ZejddddZejddddZdS ) Ú_JoinTargetProtocolúList[FromClause]©Úreturnc C s d S ©N© ©Úselfr r úPd:\z\workplace\vscode\pyvenv\venv\Lib\site-packages\sqlalchemy/sql/selectable.pyr4 s z!_JoinTargetProtocol._from_objectsr1 c C s d S r r r r r r Úentity_namespace s z$_JoinTargetProtocol.entity_namespaceN)Ú__name__Ú
|
__module__Ú __qualname__rX Úro_non_memoized_propertyr4 r r r r r r s r úColumnElement[bool])ú _ColumnExpressionArgument[Any]ra r c @ s" e Zd ZdZeddddZdS )Ú_OffsetLimitParamTú Optional[int]r c C s | j S r )Zeffective_valuer r r r Ú_limit_offset_valueÁ s z%_OffsetLimitParam._limit_offset_valueN)r r r Ú inherit_cacheÚpropertyr r r r r r ¾ s r c @ s e Zd ZdZdZdZdZdZdZe d dddZ
|
e j dddd Z d
|
d d d dZddd ddZdddddZe ddddZdS )Ú ReturnsRowsa The base-most class for Core constructs that have some concept of
|
columns that can represent rows.
|
|
While the SELECT statement and TABLE are the primary things we think
|
of in this category, DML like INSERT, UPDATE and DELETE can also specify
|
RETURNING which means they can be used in CTEs and other forms, and
|
PostgreSQL has functions that return rows also.
|
|
.. versionadded:: 1.4
|
|
TFr c C s | S r r r r r r Ú
|
selectableÛ s zReturnsRows.selectableÚ_SelectIterablec C s
|
t dS )aE A sequence of column expression objects that represents the
|
"selected" columns of this :class:`_expression.ReturnsRows`.
|
|
This is typically equivalent to .exported_columns except it is
|
delivered in the form of a straight sequence and not keyed
|
:class:`_expression.ColumnCollection`.
|
|
N©ÚNotImplementedErrorr r r r Ú_all_selected_columnsß s
|
z!ReturnsRows._all_selected_columnsúOptional[FromClause]Úbool©Ú
|
fromclauser c C s
|
t dS )z¾Return ``True`` if this :class:`.ReturnsRows` is
|
'derived' from the given :class:`.FromClause`.
|
|
An example would be an Alias of a Table is derived from that Table.
|
|
Nr ©r r¥ r r r Úis_derived_fromë s zReturnsRows.is_derived_fromr
ÚNonec C s
|
t dS )z=Populate columns into an :class:`.AliasedReturnsRows` object.Nr r¦ r r r Ú#_generate_fromclause_column_proxiesô s z/ReturnsRows._generate_fromclause_column_proxiesr ©Úcolumnr c C s
|
t dS )z>reset internal collections for an incoming column being added.Nr ©r r« r r r Ú_refresh_for_new_columnû s z#ReturnsRows._refresh_for_new_columnz"ReadOnlyColumnCollection[Any, Any]c C s
|
t dS )a A :class:`_expression.ColumnCollection`
|
that represents the "exported"
|
columns of this :class:`_expression.ReturnsRows`.
|
|
The "exported" columns represent the collection of
|
:class:`_expression.ColumnElement`
|
expressions that are rendered by this SQL
|
construct. There are primary varieties which are the
|
"FROM clause columns" of a FROM clause, such as a table, join,
|
or subquery, the "SELECTed columns", which are the columns in
|
the "columns clause" of a SELECT statement, and the RETURNING
|
columns in a DML statement..
|
|
.. versionadded:: 1.4
|
|
.. seealso::
|
|
:attr:`_expression.FromClause.exported_columns`
|
|
:attr:`_expression.SelectBase.exported_columns`
|
Nr r r r r Úexported_columnsÿ s z ReturnsRows.exported_columnsN)r r r Ú__doc__Z_is_returns_rowsÚ_is_from_clauseÚ_is_select_baseÚ_is_select_statementÚ _is_lateralr r rX r r¡ r§ r© r r® r r r r r Æ s r c @ s e Zd ZdZdS )ÚExecutableReturnsRowsú0base for executable statements that return rows.N©r r r r¯ r r r r r´ s r´ c @ s e Zd ZdZdS )ÚTypedReturnsRowsrµ Nr¶ r r r r r· s r· c @ sx e Zd ZdZdZdZdddddZd d
|
d d d dZej ddde
|
d¡ddddddZ d!ddd d d d Z d S )"Ú
|
Selectablez!Mark a class as being selectable.r Tr r¨ rª c C s
|
t d S r r r¬ r r r r ) s z"Selectable._refresh_for_new_columnNú Optional[str]ÚLateralFromClause©Únamer c C s t j| |dS )á; Return a LATERAL alias of this :class:`_expression.Selectable`.
|
|
The return value is the :class:`_expression.Lateral` construct also
|
provided by the top-level :func:`_expression.lateral` function.
|
|
.. seealso::
|
|
:ref:`tutorial_lateral_correlation` - overview of usage.
|
|
©r¼ )ÚLateralÚ
|
_construct©r r¼ r r r Úlateral, s zSelectable.lateralú1.4zµThe :meth:`.Selectable.replace_selectable` method is deprecated, and will be removed in a future release. Similar functionality is available via the sqlalchemy.sql.visitors module.)Úmessageúsqlalchemy.sql.utilr
ÚAliasr\ )ÚoldÚaliasr c C s t jj |¡ | ¡S )zÆReplace all occurrences of :class:`_expression.FromClause`
|
'old' with the given :class:`_expression.Alias`
|
object, returning a copy of this :class:`_expression.FromClause`.
|
|
)rX Ú preloadedÚsql_utilÚ ClauseAdapterÚtraverse)r rÇ rÈ r r r Úreplace_selectable9 s ÿz Selectable.replace_selectableFzKeyedColumnElement[Any]r£ z!Optional[KeyedColumnElement[Any]])r« Úrequire_embeddedr c C s | j ||¡S )aÓ Given a :class:`_expression.ColumnElement`, return the exported
|
:class:`_expression.ColumnElement` object from the
|
:attr:`_expression.Selectable.exported_columns`
|
collection of this :class:`_expression.Selectable`
|
which corresponds to that
|
original :class:`_expression.ColumnElement` via a common ancestor
|
column.
|
|
:param column: the target :class:`_expression.ColumnElement`
|
to be matched.
|
|
:param require_embedded: only return corresponding columns for
|
the given :class:`_expression.ColumnElement`, if the given
|
:class:`_expression.ColumnElement`
|
is actually present within a sub-element
|
of this :class:`_expression.Selectable`.
|
Normally the column will match if
|
it merely shares a common ancestor with one of the exported
|
columns of this :class:`_expression.Selectable`.
|
|
.. seealso::
|
|
:attr:`_expression.Selectable.exported_columns` - the
|
:class:`_expression.ColumnCollection`
|
that is used for the operation.
|
|
:meth:`_expression.ColumnCollection.corresponding_column`
|
- implementation
|
method.
|
|
)r® Úcorresponding_column)r r« rÎ r r r rÏ J s # ÿz Selectable.corresponding_column)N)F) r r r r¯ Ú__visit_name__Ú is_selectabler r rX Ú
|
deprecatedÚpreload_modulerÍ rÏ r r r r r¸ " s þ ÿr¸ c @ sV e Zd ZU dZded< dejfgZded< ee dddd d
|
d d d dddZ
|
dS )Ú HasPrefixesr ú+Tuple[Tuple[DQLDMLClauseElement, str], ...]Ú _prefixesrS Ú _has_prefixes_traverse_internalsÚprefixesz+:meth:`_expression.HasPrefixes.prefix_with`z.:paramref:`.HasPrefixes.prefix_with.*prefixes`Ú*©Údialectú#_TextCoercedExpressionArgument[Any]Ústrr\ )rØ rÛ r c s" | j t fdd|D | _ | S )a Add one or more expressions following the statement keyword, i.e.
|
SELECT, INSERT, UPDATE, or DELETE. Generative.
|
|
This is used to support backend-specific prefix keywords such as those
|
provided by MySQL.
|
|
E.g.::
|
|
stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")
|
|
# MySQL 5.7 optimizer hints
|
stmt = select(table).prefix_with(
|
"/*+ BKA(t1) */", dialect="mysql")
|
|
Multiple prefixes can be specified by multiple calls
|
to :meth:`_expression.HasPrefixes.prefix_with`.
|
|
:param \*prefixes: textual or :class:`_expression.ClauseElement`
|
construct which
|
will be rendered following the INSERT, UPDATE, or DELETE
|
keyword.
|
:param dialect: optional string dialect name which will
|
limit rendering of this prefix to only that dialect.
|
|
c s g | ]}t tj|¡ fqS r ©r Úexpectr ZStatementOptionRole©Ú.0ÚprÚ r r Ú
|
<listcomp> s ÿz+HasPrefixes.prefix_with.<locals>.<listcomp>)rÖ Útuple)r rÛ rØ r rÚ r Ú prefix_withy s $
|
þÿzHasPrefixes.prefix_withN) r r r rÖ Ú__annotations__rT Údp_prefix_sequencer× r5 rD rå r r r r rÔ r s
|
ÿ ýýrÔ c @ sV e Zd ZU dZded< dejfgZded< ee dddd d
|
d d d dddZ
|
dS )Ú HasSuffixesr rÕ Ú _suffixesrS Ú _has_suffixes_traverse_internalsÚsuffixesz+:meth:`_expression.HasSuffixes.suffix_with`z.:paramref:`.HasSuffixes.suffix_with.*suffixes`rÙ rÚ rÜ rÝ r\ )rë rÛ r c s" | j t fdd|D | _ | S )aÎ Add one or more expressions following the statement as a whole.
|
|
This is used to support backend-specific suffix keywords on
|
certain constructs.
|
|
E.g.::
|
|
stmt = select(col1, col2).cte().suffix_with(
|
"cycle empno set y_cycle to 1 default 0", dialect="oracle")
|
|
Multiple suffixes can be specified by multiple calls
|
to :meth:`_expression.HasSuffixes.suffix_with`.
|
|
:param \*suffixes: textual or :class:`_expression.ClauseElement`
|
construct which
|
will be rendered following the target clause.
|
:param dialect: Optional string dialect name which will
|
limit rendering of this suffix to only that dialect.
|
|
c s g | ]}t tj|¡ fqS r rÞ rà rÚ r r rã Í s ÿz+HasSuffixes.suffix_with.<locals>.<listcomp>)ré rä )r rÛ rë r rÚ r Ú suffix_with s
|
þÿzHasSuffixes.suffix_withN) r r r ré ræ rT rç rê r5 rD rì r r r r rè ¦ s
|
ÿ ýýrè c @ s e Zd ZU e ¡ Zded< dZded< dej fdej
|
fgZ ded< dd d d
|
d d d Z e ddd d d
|
dddZdd d d
|
dddZdS )ÚHasHintsz/util.immutabledict[Tuple[FromClause, str], str]Ú_hintsr zTuple[Tuple[str, str], ...]Ú_statement_hintsrS Ú _has_hints_traverse_internalsrÙ rÝ r\ )ÚtextÚ dialect_namer c C s | d||¡S )aØ Add a statement hint to this :class:`_expression.Select` or
|
other selectable object.
|
|
This method is similar to :meth:`_expression.Select.with_hint`
|
except that
|
it does not require an individual table, and instead applies to the
|
statement as a whole.
|
|
Hints here are specific to the backend database and may include
|
directives such as isolation levels, file directives, fetch directives,
|
etc.
|
|
.. seealso::
|
|
:meth:`_expression.Select.with_hint`
|
|
:meth:`_expression.Select.prefix_with` - generic SELECT prefixing
|
which also can suit some database-specific HINT syntaxes such as
|
MySQL optimizer hints
|
|
N©Ú
|
_with_hint)r rñ rò r r r Úwith_statement_hintà s z HasHints.with_statement_hintra )r rñ rò r c C s | |||¡S )aý Add an indexing or other executional context hint for the given
|
selectable to this :class:`_expression.Select` or other selectable
|
object.
|
|
The text of the hint is rendered in the appropriate
|
location for the database backend in use, relative
|
to the given :class:`_schema.Table` or :class:`_expression.Alias`
|
passed as the
|
``selectable`` argument. The dialect implementation
|
typically uses Python string substitution syntax
|
with the token ``%(name)s`` to render the name of
|
the table or alias. E.g. when using Oracle, the
|
following::
|
|
select(mytable).\
|
with_hint(mytable, "index(%(name)s ix_mytable)")
|
|
Would render SQL as::
|
|
select /*+ index(mytable ix_mytable) */ ... from mytable
|
|
The ``dialect_name`` option will limit the rendering of a particular
|
hint to a particular backend. Such as, to add hints for both Oracle
|
and Sybase simultaneously::
|
|
select(mytable).\
|
with_hint(mytable, "index(%(name)s ix_mytable)", 'oracle').\
|
with_hint(mytable, "WITH INDEX ix_mytable", 'mssql')
|
|
.. seealso::
|
|
:meth:`_expression.Select.with_statement_hint`
|
|
ró ©r r rñ rò r r r Ú with_hintø s *zHasHints.with_hintz Optional[_FromClauseArgument]c C sB |d kr | j ||ff7 _ n | j t tj|¡|f|i¡| _| S r )rï rî Úunionr rß r ÚFromClauseRolerö r r r rô $ s þüÿzHasHints._with_hintN)rÙ )rÙ )r r r rX Z immutabledictrî ræ rï rT Zdp_statement_hint_listZdp_table_hint_listrð rõ r5 r÷ rô r r r r rí Õ s
|
þ
|
þ ü+rí c @ s e Zd ZU dZdZdZejddddZde d < d
|
e d < d Z
|
d e d< dZ dZ dZ dZddddZdZddddddddZd[ddddddd Zd\d dd d d d Zd]d!d d"d#d$d%d&Zddd'd(d)Zd dd*d+d,Zejd-dd.d/Zd d0d'd1d2Zejd3dd4d5Zejd3dd6d7Zejd3dd8d9Zejd:dd;d<Z ejd=dd>d?Z ejd@ddAdBZ d0ddCdDZ ejdEddFdGZ d0ddHdIZ!e"dddJdKZ#d0ddLdMZ$dNd0dOdPdQZ%d ddRd dd d dSdTZ&e'r
|
d^dUdVdWdXdYZ(d S )_r
aE Represent an element that can be used within the ``FROM``
|
clause of a ``SELECT`` statement.
|
|
The most common forms of :class:`_expression.FromClause` are the
|
:class:`_schema.Table` and the :func:`_expression.select` constructs. Key
|
features common to all :class:`_expression.FromClause` objects include:
|
|
* a :attr:`.c` collection, which provides per-name access to a collection
|
of :class:`_expression.ColumnElement` objects.
|
* a :attr:`.primary_key` attribute, which is a collection of all those
|
:class:`_expression.ColumnElement`
|
objects that indicate the ``primary_key`` flag.
|
* Methods to generate various derivations of a "from" clause, including
|
:meth:`_expression.FromClause.alias`,
|
:meth:`_expression.FromClause.join`,
|
:meth:`_expression.FromClause.select`.
|
|
|
r¥ FúIterable[FromClause]r c C s dS ©Nr r r r r r Ú _hide_fromsP s zFromClause._hide_fromsr¢ Ú _is_clone_ofzColumnCollection[Any, Any]Ú_columnsNr¹ ÚschemaTú Select[Any]c C s t | S )a# Return a SELECT of this :class:`_expression.FromClause`.
|
|
|
e.g.::
|
|
stmt = some_table.select().where(some_table.c.id == 5)
|
|
.. seealso::
|
|
:func:`_expression.select` - general purpose
|
method which allows for arbitrary column lists.
|
|
©ÚSelectr r r r Úselectg s zFromClause.selectra z)Optional[_ColumnExpressionArgument[bool]]r£ ÚJoin)ÚrightÚonclauseÚisouterÚfullr c C s t | ||||S )aa Return a :class:`_expression.Join` from this
|
:class:`_expression.FromClause`
|
to another :class:`FromClause`.
|
|
E.g.::
|
|
from sqlalchemy import join
|
|
j = user_table.join(address_table,
|
user_table.c.id == address_table.c.user_id)
|
stmt = select(user_table).select_from(j)
|
|
would emit SQL along the lines of::
|
|
SELECT user.id, user.name FROM user
|
JOIN address ON user.id = address.user_id
|
|
:param right: the right side of the join; this is any
|
:class:`_expression.FromClause` object such as a
|
:class:`_schema.Table` object, and
|
may also be a selectable-compatible object such as an ORM-mapped
|
class.
|
|
:param onclause: a SQL expression representing the ON clause of the
|
join. If left at ``None``, :meth:`_expression.FromClause.join`
|
will attempt to
|
join the two tables based on a foreign key relationship.
|
|
:param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
|
|
:param full: if True, render a FULL OUTER JOIN, instead of LEFT OUTER
|
JOIN. Implies :paramref:`.FromClause.join.isouter`.
|
|
.. seealso::
|
|
:func:`_expression.join` - standalone function
|
|
:class:`_expression.Join` - the type of object produced
|
|
©r )r r r r r r r r Újoinw s 0zFromClause.join)r r r r c C s t | ||d|S )aë Return a :class:`_expression.Join` from this
|
:class:`_expression.FromClause`
|
to another :class:`FromClause`, with the "isouter" flag set to
|
True.
|
|
E.g.::
|
|
from sqlalchemy import outerjoin
|
|
j = user_table.outerjoin(address_table,
|
user_table.c.id == address_table.c.user_id)
|
|
The above is equivalent to::
|
|
j = user_table.join(
|
address_table,
|
user_table.c.id == address_table.c.user_id,
|
isouter=True)
|
|
:param right: the right side of the join; this is any
|
:class:`_expression.FromClause` object such as a
|
:class:`_schema.Table` object, and
|
may also be a selectable-compatible object such as an ORM-mapped
|
class.
|
|
:param onclause: a SQL expression representing the ON clause of the
|
join. If left at ``None``, :meth:`_expression.FromClause.join`
|
will attempt to
|
join the two tables based on a foreign key relationship.
|
|
:param full: if True, render a FULL OUTER JOIN, instead of
|
LEFT OUTER JOIN.
|
|
.. seealso::
|
|
:meth:`_expression.FromClause.join`
|
|
:class:`_expression.Join`
|
|
Tr )r r r r r r r Ú outerjoin© s /zFromClause.outerjoinÚNamedFromClause©r¼ Úflatr c C s t j| |dS )at Return an alias of this :class:`_expression.FromClause`.
|
|
E.g.::
|
|
a2 = some_table.alias('a2')
|
|
The above code creates an :class:`_expression.Alias`
|
object which can be used
|
as a FROM clause in any SELECT statement.
|
|
.. seealso::
|
|
:ref:`tutorial_using_aliases`
|
|
:func:`_expression.alias`
|
|
r¾ )rÆ rÀ ©r r¼ r r r r rÈ Ú s zFromClause.aliasúUnion[float, Function[Any]]ú*Optional[roles.ExpressionElementRole[Any]]Ú TableSample)Úsamplingr¼ Úseedr c C s t j| |||dS )aW Return a TABLESAMPLE alias of this :class:`_expression.FromClause`.
|
|
The return value is the :class:`_expression.TableSample`
|
construct also
|
provided by the top-level :func:`_expression.tablesample` function.
|
|
.. seealso::
|
|
:func:`_expression.tablesample` - usage guidelines and parameters
|
|
)r r¼ r )r rÀ )r r r¼ r r r r Ú tablesampleñ s ÿzFromClause.tablesampler¤ c C s
|
|| j kS )zÂReturn ``True`` if this :class:`_expression.FromClause` is
|
'derived' from the given ``FromClause``.
|
|
An example would be an Alias of a Table is derived from that Table.
|
|
)Ú _cloned_setr¦ r r r r§ s
|
zFromClause.is_derived_from©Úotherr c C s t | j |j¡S )zïReturn ``True`` if this :class:`_expression.FromClause` and
|
the other represent the same lexical identity.
|
|
This tests if either one is a copy of the other, or
|
if they are the same via annotation identity.
|
|
)r£ r Ú intersection©r r r r r Ú_is_lexical_equivalent s z!FromClause._is_lexical_equivalentrÝ c C s t | d| jjd S )z|A brief description of this :class:`_expression.FromClause`.
|
|
Used primarily for error message formatting.
|
|
r¼ z object)ÚgetattrÚ __class__r r r r r Ú description s zFromClause.descriptionr¨ c s j fdd| jD ¡ d S )Nc 3 s | ]}| ¡V qd S r ©Ú _make_proxy©rá Úcol©r¥ r r Ú <genexpr>( s zAFromClause._generate_fromclause_column_proxies.<locals>.<genexpr>)rþ Ú_populate_separate_keysÚcr¦ r r# r r© % s ÿz.FromClause._generate_fromclause_column_proxiesú6ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]c C s | j S )aé A :class:`_expression.ColumnCollection`
|
that represents the "exported"
|
columns of this :class:`_expression.Selectable`.
|
|
The "exported" columns for a :class:`_expression.FromClause`
|
object are synonymous
|
with the :attr:`_expression.FromClause.columns` collection.
|
|
.. versionadded:: 1.4
|
|
.. seealso::
|
|
:attr:`_expression.Selectable.exported_columns`
|
|
:attr:`_expression.SelectBase.exported_columns`
|
|
|
©r& r r r r r® , s zFromClause.exported_columnsc C s | j S )a¶ A named-based collection of :class:`_expression.ColumnElement`
|
objects maintained by this :class:`_expression.FromClause`.
|
|
The :attr:`.columns`, or :attr:`.c` collection, is the gateway
|
to the construction of SQL expressions using table-bound or
|
other selectable-bound columns::
|
|
select(mytable).where(mytable.c.somecolumn == 5)
|
|
:return: a :class:`.ColumnCollection` object.
|
|
r( r r r r ÚcolumnsD s zFromClause.columnsc C s$ d| j kr| ¡ | ¡ | j ¡ S )zk
|
A synonym for :attr:`.FromClause.columns`
|
|
:return: a :class:`.ColumnCollection`
|
|
rþ )Ú__dict__Ú_init_collectionsÚ_populate_column_collectionrþ Ú as_readonlyr r r r r& V s
|
z FromClause.cr1 c C s | j S )a Return a namespace used for name-based access in SQL expressions.
|
|
This is the namespace that is used to resolve "filter_by()" type
|
expressions, such as::
|
|
stmt.filter_by(address='some address')
|
|
It defaults to the ``.c`` collection, however internally it can
|
be overridden using the "entity_namespace" annotation to deliver
|
alternative results.
|
|
r( r r r r r c s zFromClause.entity_namespaceúIterable[NamedColumn[Any]]c C s | ¡ | ¡ | jS )ap Return the iterable collection of :class:`_schema.Column` objects
|
which comprise the primary key of this :class:`_selectable.FromClause`.
|
|
For a :class:`_schema.Table` object, this collection is represented
|
by the :class:`_schema.PrimaryKeyConstraint` which itself is an
|
iterable collection of :class:`_schema.Column` objects.
|
|
)r+ r, Ú primary_keyr r r r r/ s s
|
zFromClause.primary_keyúIterable[ForeignKey]c C s | ¡ | ¡ | jS )aV Return the collection of :class:`_schema.ForeignKey` marker objects
|
which this FromClause references.
|
|
Each :class:`_schema.ForeignKey` is a member of a
|
:class:`_schema.Table`-wide
|
:class:`_schema.ForeignKeyConstraint`.
|
|
.. seealso::
|
|
:attr:`_schema.Table.foreign_key_constraints`
|
|
)r+ r, Ú foreign_keysr r r r r1 s zFromClause.foreign_keysc C s dD ]}| j |d¡ qdS )a Reset the attributes linked to the ``FromClause.c`` attribute.
|
|
This collection is separate from all the other memoized things
|
as it has shown to be sensitive to being cleared out in situations
|
where enclosing code, typically in a replacement traversal scenario,
|
has already established strong relationships
|
with the exported columns.
|
|
The collection is cleared for the case where a table is having a
|
column added to it as well as within a Join during copy internals.
|
|
)rþ r) r& r/ r1 N)r* Úpop)r Úkeyr r r Ú_reset_column_collection s z#FromClause._reset_column_collectionr c C s dd | j D S )Nc s s | ]}t |s|V qd S r r6 ©rá r& r r r r$ ¦ s z.FromClause._select_iterable.<locals>.<genexpr>r( r r r r Ú_select_iterable¤ s zFromClause._select_iterablec C sF d| j kstd| j ks td| j ks*tt | _t | _t | _d S )Nrþ r/ r1 )r* ÚAssertionErrorr; rþ r< r/ Úsetr1 r r r r r+ ¨ s z FromClause._init_collectionsc C s
|
d| j kS )Nrþ )r* r r r r Ú_cols_populated± s zFromClause._cols_populatedc C s dS )zCalled on subclasses to establish the .c collection.
|
|
Each implementation has a different way of establishing
|
this collection.
|
|
Nr r r r r r, µ s z&FromClause._populate_column_collectionr rª c C s | ¡ dS )aN Given a column added to the .c collection of an underlying
|
selectable, produce the local version of that column, assuming this
|
selectable ultimately should proxy this column.
|
|
this is used to "ping" a derived selectable to add a new column
|
to its .c. collection when a Column has been added to one of the
|
Table objects it ultimately derives from.
|
|
If the given selectable hasn't populated its .c. collection yet,
|
it should at least pass on the message to the contained selectables,
|
but it will return None.
|
|
This method is currently used by Declarative to allow Table
|
columns to be added to a partially constructed inheritance
|
mapping that may have already produced joins. The method
|
isn't public right now, as the full span of implications
|
and/or caveats aren't yet clear.
|
|
It's also possible that this functionality could be invoked by
|
default via an event, which would require that
|
selectables maintain a weak referencing collection of all
|
derivations.
|
|
N)r4 r¬ r r r r ½ s z"FromClause._refresh_for_new_column©r¼ r c C s | j |dS ©Nr¾ )rÈ r r r r Ú_anonymous_fromclauseØ s z FromClause._anonymous_fromclauseúOptional[OperatorType]zUnion[FromGrouping, Self]©Úagainstr c C s d S r r ©r r? r r r Ú
|
self_groupß s zFromClause.self_group)NFF)NF)NF)NN)N))r r r r¯ rÐ Únamed_with_columnrX r rü ræ rÿ rÑ r° Ú_is_joinZ_use_schema_mapr r
|
r rÈ r r§ r r r© r® r) Úro_memoized_propertyr& r r/ r1 r4 r6 r+ r r9 r, r r< r rA r r r r r
8 sr
|
û5 ü2 ÿ ü
|
ÿ ÿc @ s6 e Zd ZU dZdZded< e d¡dddd Zd
|
S ) r zA :class:`.FromClause` that has a name.
|
|
Examples include tables, subqueries, CTEs, aliased tables.
|
|
.. versionadded:: 2.0
|
|
TrÝ r¼ zsqlalchemy.sql.sqltypesúTableValuedColumn[Any]r c C s t | tjS )ap Return a :class:`_sql.TableValuedColumn` object for this
|
:class:`_expression.FromClause`.
|
|
A :class:`_sql.TableValuedColumn` is a :class:`_sql.ColumnElement` that
|
represents a complete row in a table. Support for this construct is
|
backend dependent, and is supported in various forms by backends
|
such as PostgreSQL, Oracle and SQL Server.
|
|
E.g.:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import select, column, func, table
|
>>> a = table("a", column("id"), column("x"), column("y"))
|
>>> stmt = select(func.row_to_json(a.table_valued()))
|
>>> print(stmt)
|
{printsql}SELECT row_to_json(a) AS row_to_json_1
|
FROM a
|
|
.. versionadded:: 1.4.0b2
|
|
.. seealso::
|
|
:ref:`tutorial_functions` - in the :ref:`unified_tutorial`
|
|
)rO r Ú
|
TABLEVALUEr r r r Ú table_valuedò s z NamedFromClause.table_valuedN) r r r r¯ rB ræ rX rÓ rG r r r r r å s
|
|
r c @ s$ e Zd ZdZdZdZdZeZdZdS )ÚSelectLabelStylezTLabel style constants that may be passed to
|
:meth:`_sql.Select.set_label_style`.r r rV é N) r r r r¯ ÚLABEL_STYLE_NONEÚ LABEL_STYLE_TABLENAME_PLUS_COLÚ LABEL_STYLE_DISAMBIGUATE_ONLYÚLABEL_STYLE_DEFAULTZLABEL_STYLE_LEGACY_ORMr r r r rH s rH c sò e Zd ZU dZdZdejfdejfdejfdejfdejfgZde d < d
|
Z
|
d e d< d e d< d e d< d e d< d e d< dSdddd d dddZ e j ddddZdd ddd ZdTd d d d d!Ze d"¡d#dd$d%Zefd&d'd#d( fd)d* Zd+d#d, fd-d. Zd d d/d0d1d2Zeddd3d d dd4d/d5d6d7Zedd8d d d4d d9d:d;Zee d"¡d dd d4d<d=d>d?Zed d d@dAd#dBdCdDZdEddFdGZ e d"¡dUdHd dIdJdKdLZ e j dMddNdOZ e j dPddQdRZ Z S )Vr aÛ Represent a ``JOIN`` construct between two
|
:class:`_expression.FromClause`
|
elements.
|
|
The public constructor function for :class:`_expression.Join`
|
is the module-level
|
:func:`_expression.join()` function, as well as the
|
:meth:`_expression.FromClause.join` method
|
of any :class:`_expression.FromClause` (e.g. such as
|
:class:`_schema.Table`).
|
|
.. seealso::
|
|
:func:`_expression.join`
|
|
:meth:`_expression.FromClause.join`
|
|
r
|
Úleftr r r r rS Ú_traverse_internalsTr
z Optional[ColumnElement[bool]]r£ NFra úOptional[_OnClauseArgument])rN r r r r c C sj t tj|¡| _t tj|¡ ¡ | _|dkr@| | j| j¡| _nt tj |¡jt
|
j d| _|| _ || _ dS )zñConstruct a new :class:`_expression.Join`.
|
|
The usual entrypoint here is the :func:`_expression.join`
|
function or the :meth:`_expression.FromClause.join` method of any
|
:class:`_expression.FromClause` object.
|
|
N©r? )r rß r rù rN rA r Ú_match_primariesr Ú OnClauseRoler Z_asboolr r )r rN r r r r r r r Ú__init__ s$ þþ
|
ÿþz Join.__init__rÝ r c C s$ d| j jt| j | jjt| jf S )Nz Join object on %s(%d) and %s(%d))rN r Úidr r r r r r Í s üzJoin.descriptionr¢ r¤ c C s( t |t | kp&| j |¡p&| j |¡S r )ÚhashrN r§ r r¦ r r r r§ Ö s
|
|
ÿ
|
ûzJoin.is_derived_fromr= Ú FromGroupingr> c C s t | S r )rW r@ r r r rA ß s zJoin.self_grouprÅ r¨ c C s t jj}dd | jjD dd | jjD }| j | dd |D | j ¡¡ | j
|
dd |D ¡ | j tjdd |D ¡ d S )Nc S s g | ]}|qS r r r5 r r r rã è s z4Join._populate_column_collection.<locals>.<listcomp>c s s | ]}|j r|V qd S r )r/ r5 r r r r$ î s z3Join._populate_column_collection.<locals>.<genexpr>c s s | ]}|j |fV qd S r )Ú _tq_key_labelr! r r r r$ ñ s c S s g | ]
|
}|j qS r )r1 r! r r r rã õ s )rX rÉ rÊ rN r& r r/ ÚextendÚreduce_columnsr rþ r% r1 ÚupdateÚ itertoolsÚchain)r Úsqlutilr) r r r r, å s ÿ ÿÿ ÿÿz Join._populate_column_collectionr r ©ÚcloneÚkwr c sp t t t| jt| j¡} fdd|D ddddfdd }|d < t jf d
|
i | ¡ d S ) Nc s i | ]}| |fqS r r ©rá Úf©r` ra r r Ú
|
<dictcomp> s z(Join._copy_internals.<locals>.<dictcomp>ú/Union[BinaryExpression[Any], ColumnClause[Any]]r ú0Optional[KeyedColumnElement[ColumnElement[Any]]]©Úobjra r c s, t | tr(| j kr( | j | ¡}|S d S r ©Ú
|
isinstancerJ ÚtablerÏ ©ri ra Znewelem©Ú new_fromsr r Úreplace s z%Join._copy_internals.<locals>.replacerp r` ) r8 r\ r] r4 rN r ÚsuperÚ_copy_internalsÚ_reset_memoizations)r r` ra Ú all_the_fromsrp ©r ©r` ra ro r rr ø s þÿ zJoin._copy_internalsr rª c s( t |¡ | j |¡ | j |¡ d S r )rq r rN r r¬ ru r r r s z Join._refresh_for_new_columnr )rN r r c C s& t |tr|j}nd }| j|||dS )N)Úa_subset)rk r r Ú_join_condition)r rN r Ú
|
left_rightr r r rR $ s
|
zJoin._match_primaries)rw Úconsider_as_foreign_keysz(Optional[AbstractSet[ColumnClause[Any]]])ÚaÚbrw rz r c C s | ||||¡}t|dkr,| ||||¡ t|dkrdt|trHd}nd}t d|j|j|f ¡dd t| ¡ d D }t|dkr|d S t
|
| S dS ) z
Create a join condition between two tables or selectables.
|
|
See sqlalchemy.sql.util.join_condition() for full docs.
|
|
r r zI Perhaps you meant to convert the right side to a subquery using alias()?Ú zACan't find any foreign key relationships between '%s' and '%s'.%sc S s g | ]\}}||kqS r r )rá ÚxÚyr r r rã V s z(Join._join_condition.<locals>.<listcomp>N) Ú_joincond_scan_left_rightÚlenÚ_joincond_trim_constraintsrk rW rW ZNoForeignKeysErrorr ÚlistÚvaluesÚand_)Úclsr{ r| rw rz Ú constraintsÚhintÚcritr r r rx / s6 ÿ ÿ
|
ÿ þÿ zJoin._join_condition)rz )rN r rz r c C s0 t |tr|j}nd }| j||||d}t|S )N)r{ r| rw rz )rk r r r r£ )r rN r rz ry r r r r Ú _can_join\ s
|
|
üzJoin._can_joinzjcollections.defaultdict[Optional[ForeignKeyConstraint], List[Tuple[ColumnClause[Any], ColumnClause[Any]]]])r{ rw r| rz r c C sÀ t jj}t tj|¡}t tj|¡}t t ¡}||fD ]}|d krFq6t
|
|j dd dD ]}|d k rp|j |krpqXz| |¡} W nN tjk
|
rÌ }
|
z.dd | |¡D } |
|
j| kr´ nW Y ¢qXW 5 d }
|
~
|
X Y nX | d k rX||j | |j f¡ qX||k r°t
|
|j dd dD ]¤}|d k r(|j |kr(q
|
z| |¡} W nT tjk
|
r }
|
z2dd | |¡D } |
|
j| krp n
|
W Y ¢q
|
W 5 d }
|
~
|
X Y nX | d k r
|
||j | |j f¡ q
|
|r6 q¼q6|S )Nc S s | j jS r ©ÚparentZ_creation_order©Úfkr r r Ú<lambda> ó z0Join._joincond_scan_left_right.<locals>.<lambda>©r3 c S s h | ]
|
}|j qS r r¾ ©rá Útr r r Ú <setcomp> s z1Join._joincond_scan_left_right.<locals>.<setcomp>c S s | j jS r r r r r r r ¤ r c S s h | ]
|
}|j qS r r¾ r r r r r ® s )rX rÉ rÊ r rß r rù Ú collectionsÚ defaultdictr Úsortedr1 r Z get_referentrW ZNoReferenceErrorZ find_tablesZ
|
table_nameÚ
|
constraintÚappend) r r{ rw r| rz rÊ r rN r r" ZnrteZ table_namesr r r r t s` ýþ
|
ÿþ
|
|
þ
|
ÿþ
|
z Join._joincond_scan_left_rightzDict[Any, Any]z Optional[Any])r{ r| r rz r c C s |r0t |D ]"}dd |jD t|kr ||= q t|dkrrdd | ¡ D }t|dkrrt |d }||| i}t|dkrt d|j|jf ¡d S )Nc S s h | ]
|
}|j qS r )r rb r r r r Ç s z2Join._joincond_trim_constraints.<locals>.<setcomp>r c S s h | ] }t |qS r )rä )rá r r r r r Ï s r z®Can't determine join between '%s' and '%s'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.)r Úelementsr8 r r rW ZAmbiguousForeignKeysErrorr )r r{ r| r rz ÚconstÚdeduper3 r r r r º s" ÿ
|
üÿz Join._joincond_trim_constraintsr c C s t | j| j | ¡S )a Create a :class:`_expression.Select` from this
|
:class:`_expression.Join`.
|
|
E.g.::
|
|
stmt = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
|
|
stmt = stmt.select()
|
|
The above will produce a SQL string resembling::
|
|
SELECT table_a.id, table_a.col, table_b.id, table_b.a_id
|
FROM table_a JOIN table_b ON table_a.id = table_b.a_id
|
|
)r rN r Ú select_fromr r r r r Ý s z Join.selectr¹ ÚTODO_Anyr c C s t jj}|rp|d k r t d¡| jjdd| jjdd }}| |¡ | |¡¡}|j
|
|| | j ¡| j | jdS | ¡ t¡ d ¡ |¡S d S )Nz"Can't send name argument with flatT)r ©r r )rX rÉ rÊ rW Ú ArgumentErrorrN r< r rË r] r
|
rÌ r r r r Úset_label_stylerK Ú correlaterÈ )r r¼ r r^ Zleft_aZright_aÚadapterr r r r< ï s0
|
þ
|
ÿ
|
üÿþýÿzJoin._anonymous_fromclauserú c C s t jdd | jD S )Nc S s g | ]}t |j|jqS r )r4 rN r )rá r~ r r r rã s z$Join._hide_froms.<locals>.<listcomp>)r\ r] r r r r r rü s ÿzJoin._hide_fromsr c C s | g}|| j j | jj S r )rN r4 r )r Z self_listr r r r4 s zJoin._from_objects)NFF)N)NF)!r r r r¯ rÐ rT Údp_clauseelementÚ
|
dp_booleanrO ræ rC rT rX r r r§ rA rÓ r, r+ rr r rR Ú classmethodrx r r r r r< rü r4 Ú __classcell__r r ru r r y sd
|
û ú.
|
ÿÿ' ø ,ùD" ÿ r c @ s e Zd ZdddddZdS )ÚNoInitr )Úargra c O s* t d| jj| jj ¡ | jj ¡ f d S )Nz£The %s class is not intended to be constructed directly. Please use the %s() standalone function or the %s() method available from appropriate selectable objects.)r r r Úlower©r r© ra r r r rT s
|
|
ýüÿzNoInit.__init__N)r r r rT r r r r r¨ s r¨ c @ s e Zd ZdZdS )rº z>mark a FROM clause as being able to render directly as LATERALNr¶ r r r r rº ( s rº c s e Zd ZU dZdZdZded< dejfdej fgZ
|
ded< e d d
|
d d d d dddZ d d
|
d d ddddZ ddd fdd ZddddZejd dd d Zejd dd d!Zeddd"d#Zd$d d%d&d'Zefd(d dd) fd*d+ Zed,dd-d.Z ZS )/ÚAliasedReturnsRowszLBase class of aliases against tables, subqueries, and other
|
selectables.TFr Úelementr¼ rS rO Nr¾ r r¹ r\ )r r¼ ra r c K s$ | | ¡}|j|fd|i| |S )Nr¼ )Ú__new__Ú_init)r r r¼ ra ri r r r rÀ E s
|
z AliasedReturnsRows._constructr¨ ©r r¼ r c C sp t jtj|| d| _|| _|| _|d krft|trR|jrRt |dd }t|t
|
rRd }t
|
t | |pbd¡}|| _ d S )N©Zapply_propagate_attrsr¼ Úanon)r rß r ÚReturnsRowsRoler Z
|
_orig_namerk r
rB r rE Úsafe_constructrU r¼ )r r r¼ r r r r¯ Q s" ÿÿþ
|
zAliasedReturnsRows._initr rª c s t |¡ | j |¡ d S r )rq r r r¬ ru r r r b s z*AliasedReturnsRows._refresh_for_new_columnr c C s | j | ¡ d S r ©r r© r r r r r, f s z.AliasedReturnsRows._populate_column_collectionrÝ c C s | j }t|trd}|S )NZanon_1)r¼ rk rE rÁ r r r r i s
|
z AliasedReturnsRows.descriptionr£ c C s | j jS r )r Úimplicit_returningr r r r r¶ q s z%AliasedReturnsRows.implicit_returningc C s | j S )z9Legacy for dialects that are referring to Alias.original.©r r r r r Úoriginalu s zAliasedReturnsRows.originalr¢ r¤ c C s || j krdS | j |¡S ©NT)r r r§ r¦ r r r r§ z s
|
z"AliasedReturnsRows.is_derived_fromr r_ c s2 | j }t jf d|i| || j k r.| ¡ d S )Nr` )r rq rr r4 )r r` ra Zexisting_elementru r r rr s
|
z"AliasedReturnsRows._copy_internalsr c C s | gS r r r r r r r4 s z AliasedReturnsRows._from_objects)r r r r¯ Ú_is_from_containerÚ_supports_derived_columnsræ rT r¤ Ú dp_anon_namerO r¦ rÀ r¯ r r, rX r r r¶ r r¸ r§ r+ rr r4 r§ r r ru r r¬ 6 s0
|
þ ü ÿr¬ c @ s e Zd ZU ded< dS )ÚFromClauseAliasr
r N©r r r ræ r r r r r½ s
|
r½ c @ s< e Zd ZU dZdZdZded< edddd d
|
d d d ZdS )rÆ a Represents an table or selectable alias (AS).
|
|
Represents an alias, as typically applied to any table or
|
sub-select within a SQL statement using the ``AS`` keyword (or
|
without the keyword on certain databases such as Oracle).
|
|
This object is constructed from the :func:`_expression.alias` module
|
level function as well as the :meth:`_expression.FromClause.alias`
|
method available
|
on all :class:`_expression.FromClause` subclasses.
|
|
.. seealso::
|
|
:meth:`_expression.FromClause.alias`
|
|
rÈ Tr
r NFr¹ r£ r )r r¼ r r c C s t jtj|ddj||dS )NT)Z allow_selectr: )r rß r rù rÈ )r r r¼ r r r r Ú_factory® s ÿ þzAlias._factory)NF) r r r r¯ rÐ r ræ r¦ r¿ r r r r rÆ s
|
ürÆ c sÐ e Zd ZU dZdZdZdZdZdZde j
|
fde j fde j fde j fd e j fgZd
|
ed < d d dd dddddd fddZejddddZd$ddd ddd Zd%dd d d d Zd&ddd d!d"d#Z ZS )'ÚTableValuedAliasaè An alias against a "table valued" SQL function.
|
|
This construct provides for a SQL function that returns columns
|
to be used in the FROM clause of a SELECT statement. The
|
object is generated using the :meth:`_functions.FunctionElement.table_valued`
|
method, e.g.:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import select, func
|
>>> fn = func.json_array_elements_text('["one", "two", "three"]').table_valued("value")
|
>>> print(select(fn.c.value))
|
{printsql}SELECT anon_1.value
|
FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1
|
|
.. versionadded:: 1.4.0b2
|
|
.. seealso::
|
|
:ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
|
|
Ztable_valued_aliasTFr r¼ Ú_tableval_typeÚ_render_derivedÚ_render_derived_w_typesrS rO N©r¼ Útable_value_typeÚjoins_implicitlyr r¹ zOptional[TableValueType]r£ r¨ )r r¼ rÅ rÆ r c s. t j||d || _|d kr$tjn|| _d S r; )rq r¯ rÆ r rF rÁ )r r r¼ rÅ rÆ ru r r r¯ á s ÿýzTableValuedAlias._initrE r c C s t | | jS )a Return a column expression representing this
|
:class:`_sql.TableValuedAlias`.
|
|
This accessor is used to implement the
|
:meth:`_functions.FunctionElement.column_valued` method. See that
|
method for further details.
|
|
E.g.:
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(select(func.some_func().table_valued("value").column))
|
{printsql}SELECT anon_1 FROM some_func() AS anon_1
|
|
.. seealso::
|
|
:meth:`_functions.FunctionElement.column_valued`
|
|
)rO rÁ r r r r r« ò s zTableValuedAlias.columnr c C s. t j| || j| jd}| jr*d|_| j|_|S )zÈReturn a new alias of this :class:`_sql.TableValuedAlias`.
|
|
This creates a distinct FROM object that will be distinguished
|
from the original one when used in a SQL statement.
|
|
rÄ T)rÀ rÀ rÁ rÆ r rà )r r¼ r Útvar r r rÈ
|
s
|
üzTableValuedAlias.aliasrº r» c C s | j |d}d|_|S )z¶Return a new :class:`_sql.TableValuedAlias` with the lateral flag
|
set, so that it renders as LATERAL.
|
|
.. seealso::
|
|
:func:`_expression.lateral`
|
|
r¾ T)rÈ r³ )r r¼ rÇ r r r r ! s zTableValuedAlias.lateral)r¼ Ú
|
with_typesr c C s( t j| j|| j| jd}d|_||_|S )a¾ Apply "render derived" to this :class:`_sql.TableValuedAlias`.
|
|
This has the effect of the individual column names listed out
|
after the alias name in the "AS" sequence, e.g.:
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(
|
... select(
|
... func.unnest(array(["one", "two", "three"])).
|
table_valued("x", with_ordinality="o").render_derived()
|
... )
|
... )
|
{printsql}SELECT anon_1.x, anon_1.o
|
FROM unnest(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) WITH ORDINALITY AS anon_1(x, o)
|
|
The ``with_types`` keyword will render column types inline within
|
the alias expression (this syntax currently applies to the
|
PostgreSQL database):
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(
|
... select(
|
... func.json_to_recordset(
|
... '[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]'
|
... )
|
... .table_valued(column("a", Integer), column("b", String))
|
... .render_derived(with_types=True)
|
... )
|
... )
|
{printsql}SELECT anon_1.a, anon_1.b FROM json_to_recordset(:json_to_recordset_1)
|
AS anon_1(a INTEGER, b VARCHAR)
|
|
:param name: optional string name that will be applied to the alias
|
generated. If left as None, a unique anonymizing name will be used.
|
|
:param with_types: if True, the derived columns will include the
|
datatype specification with each column. This is a special syntax
|
currently known to be required by PostgreSQL for some SQL functions.
|
|
rÄ T)rÀ rÀ r rÁ rÆ r rà )r r¼ rÈ Z new_aliasr r r Úrender_derived. s 8üz TableValuedAlias.render_derived)NF)N)NF)r r r r¯ rÐ r» r rà rÆ rT r¤ r¼ Údp_typer¥ rO ræ r¯ rB Úmemoized_attributer« rÈ r rÉ r§ r r ru r rÀ º s2
|
û ú ÿ ýrÀ c @ s4 e Zd ZdZdZdZdZed ddddd d
|
ZdS ) r¿ aÞ Represent a LATERAL subquery.
|
|
This object is constructed from the :func:`_expression.lateral` module
|
level function as well as the :meth:`_expression.FromClause.lateral`
|
method available
|
on all :class:`_expression.FromClause` subclasses.
|
|
While LATERAL is part of the SQL standard, currently only more recent
|
PostgreSQL versions provide support for this keyword.
|
|
.. seealso::
|
|
:ref:`tutorial_lateral_correlation` - overview of usage.
|
|
r TNz&Union[SelectBase, _FromClauseArgument]r¹ rº r° c C s t jtj|ddj|dS )NT)Zexplicit_subqueryr¾ )r rß r rù r ©r r r¼ r r r r¿ s ÿþzLateral._factory)N) r r r r¯ rÐ r³ r r¦ r¿ r r r r r¿ q s ýr¿ c s e Zd ZU dZdZejdejfdejfg Zde d< e
|
ddd d
|
d d d d dZ e d¡ddddd
|
d d dd fddZddddZ ZS )r aI Represent a TABLESAMPLE clause.
|
|
This object is constructed from the :func:`_expression.tablesample` module
|
level function as well as the :meth:`_expression.FromClause.tablesample`
|
method
|
available on all :class:`_expression.FromClause` subclasses.
|
|
.. seealso::
|
|
:func:`_expression.tablesample`
|
|
r r r rS rO Nra r r¹ r )r r r¼ r r c C s t tj|¡j|||dS )N©r¼ r )r rß r rù r )r r r r¼ r r r r r¿ ª s
|
ÿzTableSample._factoryzsqlalchemy.sql.functionsrÍ r r¨ )r r¼ r r r c sL |d k s t tjj}t||js,|j |¡}|| _|| _ t
|
j ||d d S r; ) r7 rX rÉ Z sql_functionsrk r ÚfuncÚsystemr r rq r¯ )r r r¼ r r Ú functionsru r r r¯ ¶ s zTableSample._initz Function[Any]r c C s | j S r )r r r r r Ú _get_methodÈ s zTableSample._get_method)NN)r r r r¯ rÐ r¬ rO rT r¤ ræ r¦ r¿ rX rÓ r¯ rÑ r§ r r ru r r s"
|
þÿÿ
|
û ú r c sþ e Zd ZU dZdZejdejfdejfdej fdej fg e
|
j e j Zded< d ed
|
< ed(d d dd dddZd d d d d d d ddd dddddddd fddZdddd Zd)d dd d d d Zd d d!d"d#Zd d d!d$d%Zd dd&d'Z ZS )*ÚCTEaU Represent a Common Table Expression.
|
|
The :class:`_expression.CTE` object is obtained using the
|
:meth:`_sql.SelectBase.cte` method from any SELECT statement. A less often
|
available syntax also allows use of the :meth:`_sql.HasCTE.cte` method
|
present on :term:`DML` constructs such as :class:`_sql.Insert`,
|
:class:`_sql.Update` and
|
:class:`_sql.Delete`. See the :meth:`_sql.HasCTE.cte` method for
|
usage details on CTEs.
|
|
.. seealso::
|
|
:ref:`tutorial_subqueries_ctes` - in the 2.0 tutorial
|
|
:meth:`_sql.HasCTE.cte` - examples of calling styles
|
|
ÚcteÚ
|
_cte_aliasÚ _restatesÚ recursiveÚnestingrS rO ÚHasCTEr NFr¹ r£ )r r¼ rÖ r c C s t tj|¡j||dS )z©Return a new :class:`_expression.CTE`,
|
or Common Table Expression instance.
|
|
Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage.
|
|
)r¼ rÖ )r rß r Ú
|
HasCTERolerÓ )r r r¼ rÖ r r r r¿ ö s ÿz CTE._factory)r¼ rÖ r× rÔ rÕ rÖ ré r z Optional[CTE]zOptional[Tuple[()]]r¨ ) r r¼ rÖ r× rÔ rÕ rÖ ré r c s@ || _ || _|| _|| _|r"|| _|r,|| _t j||d d S r; )rÖ r× rÔ rÕ rÖ ré rq r¯ ) r r r¼ rÖ r× rÔ rÕ rÖ ré ru r r r¯ s z CTE._initr c C s( | j d k r| j | ¡ n | j | ¡ d S r )rÔ r© r r r r r r, s
|
z CTE._populate_column_collectionr c C s" t j| j|| j| j| | j| jdS )a2 Return an :class:`_expression.Alias` of this
|
:class:`_expression.CTE`.
|
|
This method is a CTE-specific specialization of the
|
:meth:`_expression.FromClause.alias` method.
|
|
.. seealso::
|
|
:ref:`tutorial_using_aliases`
|
|
:func:`_expression.alias`
|
|
)r¼ rÖ r× rÔ rÖ ré )rÒ rÀ r rÖ r× rÖ ré r r r r rÈ $ s ùz CTE.aliasrg r c G sF t | js td| j dtj| jj| | j| j| j| | j | j
|
dS )a Return a new :class:`_expression.CTE` with a SQL ``UNION``
|
of the original CTE against the given selectables provided
|
as positional arguments.
|
|
:param \*other: one or more elements with which to create a
|
UNION.
|
|
.. versionchanged:: 1.4.28 multiple elements are now accepted.
|
|
.. seealso::
|
|
:meth:`_sql.HasCTE.cte` - examples of calling styles
|
|
ú CTE element fz does not support union()©r¼ rÖ r× rÕ rÖ ré ) r% r r7 rÒ rÀ rø r¼ rÖ r× rÖ ré r r r r rø < s ÿ þ
|
ùz CTE.unionc G sF t | js td| j dtj| jj| | j| j| j| | j | j
|
dS )a¢ Return a new :class:`_expression.CTE` with a SQL ``UNION ALL``
|
of the original CTE against the given selectables provided
|
as positional arguments.
|
|
:param \*other: one or more elements with which to create a
|
UNION.
|
|
.. versionchanged:: 1.4.28 multiple elements are now accepted.
|
|
.. seealso::
|
|
:meth:`_sql.HasCTE.cte` - examples of calling styles
|
|
rÚ z does not support union_all()rÛ ) r% r r7 rÒ rÀ Ú union_allr¼ rÖ r× rÖ ré r r r r rÜ Y s ÿ þ
|
ùz CTE.union_allc C s | j dk r| j S | S )zÀ
|
A recursive CTE is updated to attach the recursive part.
|
Updated CTEs should still refer to the original CTE.
|
This function returns this reference identifier.
|
N)rÕ r r r r Ú_get_reference_ctew s zCTE._get_reference_cte)NF)NF)r r r r¯ rÐ r¬ rO rT r¤ r¥ rÔ r× rè rê ræ r¦ r¿ r¯ r, rÈ rø rÜ rÝ r§ r r ru r rÒ Ì s@
|
üÿùøÿ
|
üö& rÒ c @ s e Zd ZU ded< dS )Ú_CTEOptsr£ r× Nr¾ r r r r rÞ s
|
rÞ c @ s6 e Zd ZU ded< ded< ded< ded< ded< d S )
|
Ú_ColumnsPlusNamesr¹ Úrequired_label_nameÚ proxy_keyÚfallback_label_nameú%Union[ColumnElement[Any], TextClause]r« r£ ÚrepeatedNr¾ r r r r rß s
|
rß c @ s2 e Zd ZU dZeZded< d ddddd d
|
ZdS ) Ú SelectsRowszvSub-base of ReturnsRows for elements that deliver rows
|
directly, namely SELECT and INSERT/UPDATE/DELETE..RETURNINGrH Ú _label_styleNr£ zOptional[_SelectIterable]zList[_ColumnsPlusNames])Úanon_for_dupe_keyÚcolsr c C sj |dkr| j }t | j¡}i }g }|j}| jtk}| jtk}d} |D ] }
|
d} |
|
jsbd } } }nÚ|rtrvt |
|
svt
|
d } } |
|
j p|
|
j }n°trt |
|
st
|
|r°|
|
j } } }n|
|
j } }d} | dkr<|
|
j}|dkr0|
|
j |k} |
|
||
|
j < d } } | r(|r|
|
| ¡}| d7 } n|
|
| ¡}| d7 } n|
|
j }n | } } }| dk rLtrZt |
|
sZt
|
| |krDt|| t|
|
kr|r|
|
j } }n
|
|
|
j } }|rú| |krút|| t|
|
ksÀt
|
|rÞ|
|
| ¡ } }| d7 } n|
|
| ¡ } }| d7 } d} n|
|
|| < n>|rL|r(|
|
| ¡ } }| d7 } n|
|
| ¡ } }| d7 } d} n|
|
|| < |t| ||
|
||
|
| qD|S )ac 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.
|
|
Nr FT)r¡ Ú SelectStateÚ_column_naming_conventionræ r rK rJ Z _render_label_in_columns_clauser r$ r7 Z_non_anon_labelZ_anon_name_labelÚ _tq_labelZ_expression_labelZ_dedupe_anon_tq_label_idxZ_dedupe_anon_label_idxrV Z_anon_tq_labelrß )r rç rè Zkey_naming_conventionÚnamesÚresultZ result_appendÚtable_qualifiedZlabel_style_noneZ dedupe_hashr& rä Zeffective_namerà râ Z
|
expr_labelr r r Ú _generate_columns_plus_names« sÌ ÿ
|
|
|
þþ þþ
|
|
|
|
|
ÿ
|
ÿ
|
þ
|
|
þÿþþ
|
þ
|
þ
|
þûÿ
|
z(SelectsRows._generate_columns_plus_names)N)r r r r¯ rJ ræ ræ rï r r r r rå ¥ s
|
ýrå c @ sx e Zd ZU dZdejfdejfgZded< dZ ded< dZ
|
ded< e d d
|
d d d dddZ ddd d d dddZ dS )rØ z3Mixin that declares a class to include CTE support.Ú_independent_ctesÚ_independent_ctes_optsrS Ú _has_ctes_traverse_internalsr zTuple[CTE, ...]zTuple[_CTEOpts, ...]F)Ú nest_hererÒ r£ r\ )Úctesró r c G sD t |}|D ]2}t tj|¡}| j|f7 _| j|f7 _q | S )aS Add one or more :class:`_sql.CTE` constructs to this statement.
|
|
This method will associate the given :class:`_sql.CTE` constructs with
|
the parent statement such that they will each be unconditionally
|
rendered in the WITH clause of the final statement, even if not
|
referenced elsewhere within the statement or any sub-selects.
|
|
The optional :paramref:`.HasCTE.add_cte.nest_here` parameter when set
|
to True will have the effect that each given :class:`_sql.CTE` will
|
render in a WITH clause rendered directly along with this statement,
|
rather than being moved to the top of the ultimate rendered statement,
|
even if this statement is rendered as a subquery within a larger
|
statement.
|
|
This method has two general uses. One is to embed CTE statements that
|
serve some purpose without being referenced explicitly, such as the use
|
case of embedding a DML statement such as an INSERT or UPDATE as a CTE
|
inline with a primary statement that may draw from its results
|
indirectly. The other is to provide control over the exact placement
|
of a particular series of CTE constructs that should remain rendered
|
directly in terms of a particular statement that may be nested in a
|
larger statement.
|
|
E.g.::
|
|
from sqlalchemy import table, column, select
|
t = table('t', column('c1'), column('c2'))
|
|
ins = t.insert().values({"c1": "x", "c2": "y"}).cte()
|
|
stmt = select(t).add_cte(ins)
|
|
Would render::
|
|
WITH anon_1 AS
|
(INSERT INTO t (c1, c2) VALUES (:param_1, :param_2))
|
SELECT t.c1, t.c2
|
FROM t
|
|
Above, the "anon_1" CTE is not referred towards in the SELECT
|
statement, however still accomplishes the task of running an INSERT
|
statement.
|
|
Similarly in a DML-related context, using the PostgreSQL
|
:class:`_postgresql.Insert` construct to generate an "upsert"::
|
|
from sqlalchemy import table, column
|
from sqlalchemy.dialects.postgresql import insert
|
|
t = table("t", column("c1"), column("c2"))
|
|
delete_statement_cte = (
|
t.delete().where(t.c.c1 < 1).cte("deletions")
|
)
|
|
insert_stmt = insert(t).values({"c1": 1, "c2": 2})
|
update_statement = insert_stmt.on_conflict_do_update(
|
index_elements=[t.c.c1],
|
set_={
|
"c1": insert_stmt.excluded.c1,
|
"c2": insert_stmt.excluded.c2,
|
},
|
).add_cte(delete_statement_cte)
|
|
print(update_statement)
|
|
The above statement renders as::
|
|
WITH deletions AS
|
(DELETE FROM t WHERE t.c1 < %(c1_1)s)
|
INSERT INTO t (c1, c2) VALUES (%(c1)s, %(c2)s)
|
ON CONFLICT (c1) DO UPDATE SET c1 = excluded.c1, c2 = excluded.c2
|
|
.. versionadded:: 1.4.21
|
|
:param \*ctes: zero or more :class:`.CTE` constructs.
|
|
.. versionchanged:: 2.0 Multiple CTE instances are accepted
|
|
:param nest_here: if True, the given CTE or CTEs will be rendered
|
as though they specified the :paramref:`.HasCTE.cte.nesting` flag
|
to ``True`` when they were added to this :class:`.HasCTE`.
|
Assuming the given CTEs are not referenced in an outer-enclosing
|
statement as well, the CTEs given should render at the level of
|
this statement when this flag is given.
|
|
.. versionadded:: 2.0
|
|
.. seealso::
|
|
:paramref:`.HasCTE.cte.nesting`
|
|
|
)rÞ r rß r Ú IsCTERolerð rñ )r ró rô ÚoptrÓ r r r Úadd_cte_ s `ÿzHasCTE.add_cteNr¹ )r¼ rÖ r× r c C s t j| |||dS )aý% Return a new :class:`_expression.CTE`,
|
or Common Table Expression instance.
|
|
Common table expressions are a SQL standard whereby SELECT
|
statements can draw upon secondary statements specified along
|
with the primary statement, using a clause called "WITH".
|
Special semantics regarding UNION can also be employed to
|
allow "recursive" queries, where a SELECT statement can draw
|
upon the set of rows that have previously been selected.
|
|
CTEs can also be applied to DML constructs UPDATE, INSERT
|
and DELETE on some databases, both as a source of CTE rows
|
when combined with RETURNING, as well as a consumer of
|
CTE rows.
|
|
SQLAlchemy detects :class:`_expression.CTE` objects, which are treated
|
similarly to :class:`_expression.Alias` objects, as special elements
|
to be delivered to the FROM clause of the statement as well
|
as to a WITH clause at the top of the statement.
|
|
For special prefixes such as PostgreSQL "MATERIALIZED" and
|
"NOT MATERIALIZED", the :meth:`_expression.CTE.prefix_with`
|
method may be
|
used to establish these.
|
|
.. versionchanged:: 1.3.13 Added support for prefixes.
|
In particular - MATERIALIZED and NOT MATERIALIZED.
|
|
:param name: name given to the common table expression. Like
|
:meth:`_expression.FromClause.alias`, the name can be left as
|
``None`` in which case an anonymous symbol will be used at query
|
compile time.
|
:param recursive: if ``True``, will render ``WITH RECURSIVE``.
|
A recursive common table expression is intended to be used in
|
conjunction with UNION ALL in order to derive rows
|
from those already selected.
|
:param nesting: if ``True``, will render the CTE locally to the
|
statement in which it is referenced. For more complex scenarios,
|
the :meth:`.HasCTE.add_cte` method using the
|
:paramref:`.HasCTE.add_cte.nest_here`
|
parameter may also be used to more carefully
|
control the exact placement of a particular CTE.
|
|
.. versionadded:: 1.4.24
|
|
.. seealso::
|
|
:meth:`.HasCTE.add_cte`
|
|
The following examples include two from PostgreSQL's documentation at
|
https://www.postgresql.org/docs/current/static/queries-with.html,
|
as well as additional examples.
|
|
Example 1, non recursive::
|
|
from sqlalchemy import (Table, Column, String, Integer,
|
MetaData, select, func)
|
|
metadata = MetaData()
|
|
orders = Table('orders', metadata,
|
Column('region', String),
|
Column('amount', Integer),
|
Column('product', String),
|
Column('quantity', Integer)
|
)
|
|
regional_sales = select(
|
orders.c.region,
|
func.sum(orders.c.amount).label('total_sales')
|
).group_by(orders.c.region).cte("regional_sales")
|
|
|
top_regions = select(regional_sales.c.region).\
|
where(
|
regional_sales.c.total_sales >
|
select(
|
func.sum(regional_sales.c.total_sales) / 10
|
)
|
).cte("top_regions")
|
|
statement = select(
|
orders.c.region,
|
orders.c.product,
|
func.sum(orders.c.quantity).label("product_units"),
|
func.sum(orders.c.amount).label("product_sales")
|
).where(orders.c.region.in_(
|
select(top_regions.c.region)
|
)).group_by(orders.c.region, orders.c.product)
|
|
result = conn.execute(statement).fetchall()
|
|
Example 2, WITH RECURSIVE::
|
|
from sqlalchemy import (Table, Column, String, Integer,
|
MetaData, select, func)
|
|
metadata = MetaData()
|
|
parts = Table('parts', metadata,
|
Column('part', String),
|
Column('sub_part', String),
|
Column('quantity', Integer),
|
)
|
|
included_parts = select(\
|
parts.c.sub_part, parts.c.part, parts.c.quantity\
|
).\
|
where(parts.c.part=='our part').\
|
cte(recursive=True)
|
|
|
incl_alias = included_parts.alias()
|
parts_alias = parts.alias()
|
included_parts = included_parts.union_all(
|
select(
|
parts_alias.c.sub_part,
|
parts_alias.c.part,
|
parts_alias.c.quantity
|
).\
|
where(parts_alias.c.part==incl_alias.c.sub_part)
|
)
|
|
statement = select(
|
included_parts.c.sub_part,
|
func.sum(included_parts.c.quantity).
|
label('total_quantity')
|
).\
|
group_by(included_parts.c.sub_part)
|
|
result = conn.execute(statement).fetchall()
|
|
Example 3, an upsert using UPDATE and INSERT with CTEs::
|
|
from datetime import date
|
from sqlalchemy import (MetaData, Table, Column, Integer,
|
Date, select, literal, and_, exists)
|
|
metadata = MetaData()
|
|
visitors = Table('visitors', metadata,
|
Column('product_id', Integer, primary_key=True),
|
Column('date', Date, primary_key=True),
|
Column('count', Integer),
|
)
|
|
# add 5 visitors for the product_id == 1
|
product_id = 1
|
day = date.today()
|
count = 5
|
|
update_cte = (
|
visitors.update()
|
.where(and_(visitors.c.product_id == product_id,
|
visitors.c.date == day))
|
.values(count=visitors.c.count + count)
|
.returning(literal(1))
|
.cte('update_cte')
|
)
|
|
upsert = visitors.insert().from_select(
|
[visitors.c.product_id, visitors.c.date, visitors.c.count],
|
select(literal(product_id), literal(day), literal(count))
|
.where(~exists(update_cte.select()))
|
)
|
|
connection.execute(upsert)
|
|
Example 4, Nesting CTE (SQLAlchemy 1.4.24 and above)::
|
|
value_a = select(
|
literal("root").label("n")
|
).cte("value_a")
|
|
# A nested CTE with the same name as the root one
|
value_a_nested = select(
|
literal("nesting").label("n")
|
).cte("value_a", nesting=True)
|
|
# Nesting CTEs takes ascendency locally
|
# over the CTEs at a higher level
|
value_b = select(value_a_nested.c.n).cte("value_b")
|
|
value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))
|
|
The above query will render the second CTE nested inside the first,
|
shown with inline parameters below as::
|
|
WITH
|
value_a AS
|
(SELECT 'root' AS n),
|
value_b AS
|
(WITH value_a AS
|
(SELECT 'nesting' AS n)
|
SELECT value_a.n AS n FROM value_a)
|
SELECT value_a.n AS a, value_b.n AS b
|
FROM value_a, value_b
|
|
The same CTE can be set up using the :meth:`.HasCTE.add_cte` method
|
as follows (SQLAlchemy 2.0 and above)::
|
|
value_a = select(
|
literal("root").label("n")
|
).cte("value_a")
|
|
# A nested CTE with the same name as the root one
|
value_a_nested = select(
|
literal("nesting").label("n")
|
).cte("value_a")
|
|
# Nesting CTEs takes ascendency locally
|
# over the CTEs at a higher level
|
value_b = (
|
select(value_a_nested.c.n).
|
add_cte(value_a_nested, nest_here=True).
|
cte("value_b")
|
)
|
|
value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))
|
|
Example 5, Non-Linear CTE (SQLAlchemy 1.4.28 and above)::
|
|
edge = Table(
|
"edge",
|
metadata,
|
Column("id", Integer, primary_key=True),
|
Column("left", Integer),
|
Column("right", Integer),
|
)
|
|
root_node = select(literal(1).label("node")).cte(
|
"nodes", recursive=True
|
)
|
|
left_edge = select(edge.c.left).join(
|
root_node, edge.c.right == root_node.c.node
|
)
|
right_edge = select(edge.c.right).join(
|
root_node, edge.c.left == root_node.c.node
|
)
|
|
subgraph_cte = root_node.union(left_edge, right_edge)
|
|
subgraph = select(subgraph_cte)
|
|
The above query will render 2 UNIONs inside the recursive CTE::
|
|
WITH RECURSIVE nodes(node) AS (
|
SELECT 1 AS node
|
UNION
|
SELECT edge."left" AS "left"
|
FROM edge JOIN nodes ON edge."right" = nodes.node
|
UNION
|
SELECT edge."right" AS "right"
|
FROM edge JOIN nodes ON edge."left" = nodes.node
|
)
|
SELECT nodes.node FROM nodes
|
|
.. seealso::
|
|
:meth:`_orm.Query.cte` - ORM version of
|
:meth:`_expression.HasCTE.cte`.
|
|
)r¼ rÖ r× )rÒ rÀ )r r¼ rÖ r× r r r rÓ È s ÿz
|
HasCTE.cte)NFF)r r r r¯ rT Údp_clauseelement_listÚ dp_plain_objrò ræ rð rñ r5 r÷ rÓ r r r r rØ T s
|
þ j ürØ c @ sX e Zd ZU dZdZdZdZded< edddd dd d
|
Z e
|
d d ¡d dddZ dS )ÚSubquerya Represent a subquery of a SELECT.
|
|
A :class:`.Subquery` is created by invoking the
|
:meth:`_expression.SelectBase.subquery` method, or for convenience the
|
:meth:`_expression.SelectBase.alias` method, on any
|
:class:`_expression.SelectBase` subclass
|
which includes :class:`_expression.Select`,
|
:class:`_expression.CompoundSelect`, and
|
:class:`_expression.TextualSelect`. As rendered in a FROM clause,
|
it represents the
|
body of the SELECT statement inside of parenthesis, followed by the usual
|
"AS <somename>" that defines all "alias" objects.
|
|
The :class:`.Subquery` object is very similar to the
|
:class:`_expression.Alias`
|
object and can be used in an equivalent way. The difference between
|
:class:`_expression.Alias` and :class:`.Subquery` is that
|
:class:`_expression.Alias` always
|
contains a :class:`_expression.FromClause` object whereas
|
:class:`.Subquery`
|
always contains a :class:`_expression.SelectBase` object.
|
|
.. versionadded:: 1.4 The :class:`.Subquery` class was added which now
|
serves the purpose of providing an aliased version of a SELECT
|
statement.
|
|
ÚsubqueryTÚ
|
SelectBaser Nr¹ r° c C s t tj|¡j|dS )z#Return a :class:`.Subquery` object.r¾ )r rß r ÚSelectStatementRolerû rÌ r r r r¿ s ÿþzSubquery._factoryrà ax The :meth:`.Subquery.as_scalar` method, which was previously ``Alias.as_scalar()`` prior to version 1.4, is deprecated and will be removed in a future release; Please use the :meth:`_expression.Select.scalar_subquery` method of the :func:`_expression.select` construct before constructing a subquery object, or with the ORM use the :meth:`_query.Query.scalar_subquery` method.úScalarSelect[Any]r c C s | j t¡ ¡ S r )r r¡ rJ Úscalar_subqueryr r r r Ú as_scalar
|
s zSubquery.as_scalar)N) r r r r¯ rÐ Z _is_subqueryr ræ r¦ r¿ rX rÒ r r r r r rú Û
|
s
|
ÿ þ
|
rú c @ s e Zd ZU dZdejfgZded< ded< ddddZd d
|
d d Z e
|
j d d
|
ddZ e
|
j d d
|
ddZ edd
|
ddZedd
|
ddZddddd Zd4d dd d!d"d#Zd$d d%d&d'Ze
|
j d(d
|
d)d*Ze
|
j d+d
|
d,d-Zd.d
|
d/d0Zd.d d1d2d3Zd S )5rW z%Represent a grouping of a FROM clauser rS rO r
r· c C s t tj|¡| _d S r )r rß r rù r ©r r r r r rT ! s zFromGrouping.__init__r¨ r c C s d S r r r r r r r+ $ s z FromGrouping._init_collectionsr' c C s | j jS r ©r r) r r r r r) ' s zFromGrouping.columnsc C s | j jS r r r r r r r& - s zFromGrouping.cr. c C s | j jS r )r r/ r r r r r/ 1 s zFromGrouping.primary_keyr0 c C s | j jS r )r r1 r r r r r1 5 s zFromGrouping.foreign_keysr¢ r£ r¤ c C s | j |¡S r )r r§ r¦ r r r r§ 9 s z FromGrouping.is_derived_fromNFr¹ ÚNamedFromGroupingr c C s t | jj||dS )Nr: )r r rÈ r r r r rÈ < s zFromGrouping.aliasr ©ra r c K s t | jjf |S r )rW r r< ©r ra r r r r< A s z"FromGrouping._anonymous_fromclauserú c C s | j jS r )r rü r r r r rü D s zFromGrouping._hide_fromsr c C s | j jS r ©r r4 r r r r r4 H s zFromGrouping._from_objectszDict[str, FromClause]c C s
|
d| j iS ©Nr r· r r r r Ú __getstate__L s zFromGrouping.__getstate__©Ústater c C s |d | _ d S r r· ©r r
|
r r r Ú __setstate__O s zFromGrouping.__setstate__)NF)r r r r¯ rT r¤ rO ræ rT r+ rX r r) r& r r/ r1 r§ rÈ r< rü r4 r r r r r r rW s2
|
ÿ ÿrW c @ s e Zd ZdZdZdS )r zLrepresent a grouping of a named FROM clause
|
|
.. versionadded:: 2.0
|
|
TN)r r r r¯ r r r r r r S s r c sP e Zd ZU dZdZdejfdejfdejfgZde d< dZ
|
d e d
|
< d Z e j d d ddZd ddd fdd Zere jdd ddZe jdd ddZd d ddZd d d d d Zd d d!d"Ze j d d d#d$Zdd d%d&d'Ze d(¡d)d d*d+Ze d(¡d,d d-d.Ze d(¡d/d d0d1Z e jd2d d3d4Z Z S )5Ú TableClausea- Represents a minimal "table" construct.
|
|
This is a lightweight table object that has only a name, a
|
collection of columns, which are typically produced
|
by the :func:`_expression.column` function, and a schema::
|
|
from sqlalchemy import table, column
|
|
user = table("user",
|
column("id"),
|
column("name"),
|
column("description"),
|
)
|
|
The :class:`_expression.TableClause` construct serves as the base for
|
the more commonly used :class:`_schema.Table` object, providing
|
the usual set of :class:`_expression.FromClause` services including
|
the ``.c.`` collection and statement generation methods.
|
|
It does **not** provide all the additional schema-level services
|
of :class:`_schema.Table`, including constraints, references to other
|
tables, or support for :class:`_schema.MetaData`-level services.
|
It's useful
|
on its own as an ad-hoc construct used to generate quick SQL
|
statements when a more fully fledged :class:`_schema.Table`
|
is not on hand.
|
|
rl r) r¼ rÿ rS rO TrÝ ÚfullnameFzOptional[ColumnClause[Any]]r c C s dS )z4No PK or default support so no autoincrement column.Nr r r r r Ú_autoincrement_column s z!TableClause._autoincrement_columnúColumnClause[Any]r )r¼ r) ra c s t ¡ || _t | _t | _t | _|D ]}| |¡ q,|
|
dd ¡}|d k rV|| _ | j d k rtd| j | jf | _ n| j| _ |rt dt| ¡d S )Nrÿ z%s.%szUnsupported argument(s): %s)rq rT r¼ r> rþ r< r/ r8 r1 Ú append_columnr2 rÿ r rW r r )r r¼ r) ra r& rÿ ru r r rT s
|
|
zTableClause.__init__z0ReadOnlyColumnCollection[str, ColumnClause[Any]]c C s d S r r r r r r r) © s zTableClause.columnsc C s d S r r r r r r r& s z TableClause.cc C s$ | j d k r| j d | j S | jS d S )NÚ.)rÿ r¼ r r r r Ú__str__± s
|
zTableClause.__str__r r¨ rª c C s d S r r r¬ r r r r · s z#TableClause._refresh_for_new_columnc C s d S r r r r r r r+ º s z TableClause._init_collectionsc C s | j S r r¾ r r r r r ½ s zTableClause.description)r& r c C s@ |j }|d k r*|| k r*t d|j|f ¡| j |¡ | |_ d S )Nz1column object '%s' already assigned to table '%s')rl rW r r3 rþ Úadd)r r& Úexistingr r r r Á s ÿÿ zTableClause.append_columnzsqlalchemy.sql.dmlz util.preloaded.sql_dml.Insertc C s t jj | ¡S )zöGenerate an :class:`_sql.Insert` construct against this
|
:class:`_expression.TableClause`.
|
|
E.g.::
|
|
table.insert().values(name='foo')
|
|
See :func:`_expression.insert` for argument and usage information.
|
|
)rX rÉ Úsql_dmlZInsertr r r r ÚinsertÌ s zTableClause.insertry c C s t jj | ¡S )a Generate an :func:`_expression.update` construct against this
|
:class:`_expression.TableClause`.
|
|
E.g.::
|
|
table.update().where(table.c.id==7).values(name='foo')
|
|
See :func:`_expression.update` for argument and usage information.
|
|
)rX rÉ r ry r r r r r[ Û s ÿzTableClause.updaterx c C s t jj | ¡S )zýGenerate a :func:`_expression.delete` construct against this
|
:class:`_expression.TableClause`.
|
|
E.g.::
|
|
table.delete().where(table.c.id==7)
|
|
See :func:`_expression.delete` for argument and usage information.
|
|
)rX rÉ r rx r r r r Údeleteë s zTableClause.deleter c C s | gS r r r r r r r4 ù s zTableClause._from_objects) r r r r¯ rÐ rT Z)dp_fromclause_canonical_column_collectionÚ dp_stringrO ræ Z _is_tabler¶ rX rD r rT r r r) r& r r r+ r r rÓ r r[ r r4 r§ r r ru r r ] sB
|
þú r Ú ForUpdateArgc @ sÀ e Zd ZU dejfdejfdejfdejfgZded< ded< ded< ded< ded< ed d
|
d d d Z dddddZ
|
dddddZ ddddZ ddddddddddddd d Z dS ) r ÚofÚnowaitÚreadÚ skip_lockedrS rO z!Optional[Sequence[ClauseElement]]r£ ÚForUpdateParameterúOptional[ForUpdateArg])Úwith_for_updater c C s< t |tr|S |dkrd S |dkr(t S tf td|S d S )N)NFTúDict[str, Any])rk r r )r r! r r r Ú_from_argument s
|
zForUpdateArg._from_argumentr r c C sF t |toD|j| jkoD|j| jkoD|j| jkoD|j| jkoD|j| jkS r )rk r r r r Ú key_sharer r r r r Ú__eq__ s
|
|
ÿ
|
þ
|
ý
|
ü
|
úzForUpdateArg.__eq__c C s | |¡ S r )r% r r r r Ú__ne__% s zForUpdateArg.__ne__Úintr c C s t | S r )rU r r r r Ú__hash__( s zForUpdateArg.__hash__FN©r r r r r$ ú Optional[_ForUpdateOfArgument]c C sB || _ || _|| _|| _|dk r8dd t |¡D | _nd| _dS )zZRepresents arguments specified to
|
:meth:`_expression.Select.for_update`.
|
|
Nc S s g | ]}t tj|¡qS r ©r rß r ZColumnsClauseRole©rá Úelemr r r rã > s ÿz)ForUpdateArg.__init__.<locals>.<listcomp>)r r r r$ rX Úto_listr ©r r r r r r$ r r r rT + s þ
|
zForUpdateArg.__init__)r r r rT rø r¥ rO ræ r¦ r# r% r& r( rT r r r r r s(
|
ü
|
ùc sü e Zd ZU dZdZdZded< ded< dejfdej fd ej
|
fd
|
ej fgZ d ed < d dddddd fddZ eddddZed.dddddd Zed/dd d d d Zed!dd"d#d$Zd%dd&d'Zd(dd)d*Zejd+dd,d-Z ZS )0ÚValueszáRepresent a ``VALUES`` construct that can be used as a FROM element
|
in a statement.
|
|
The :class:`_expression.Values` object is created from the
|
:func:`_expression.values` function.
|
|
.. versionadded:: 1.4
|
|
r r ú!Tuple[List[Tuple[Any, ...]], ...]Ú_datar£ Ú_unnamedÚ _column_argsr¼ Ú literal_bindsrS rO NF)r¼ r5 r r¹ )r) r¼ r5 c sR t ¡ || _|d kr2d| _t t| d¡| _n d| _|| _|| _| j | _ d S )NTr² F)
|
rq rT r4 r3 rE r´ rU r¼ r5 rB )r r¼ r5 r) ru r r rT ] s
|
zValues.__init__úList[TypeEngine[Any]]r c C s dd | j D S )Nc S s g | ]
|
}|j qS r ©Útyper! r r r rã p s z(Values._column_types.<locals>.<listcomp>©r4 r r r r Ú _column_typesn s zValues._column_typesr\ r c C s4 |dkrt t| d¡}n|}|| _d| _d| _| S )aY Return a new :class:`_expression.Values`
|
construct that is a copy of this
|
one with the given name.
|
|
This method is a VALUES-specific specialization of the
|
:meth:`_expression.FromClause.alias` method.
|
|
.. seealso::
|
|
:ref:`tutorial_using_aliases`
|
|
:func:`_expression.alias`
|
|
Nr² TF)rE r´ rU r¼ rB r3 )r r¼ r Ú non_none_namer r r rÈ r s z Values.aliasrº r» c C s* |dkr| j }n|}d| _|| _ d| _| S )z»Return a new :class:`_expression.Values` with the lateral flag set,
|
so that
|
it renders as LATERAL.
|
|
.. seealso::
|
|
:func:`_expression.lateral`
|
|
NTF)r¼ r³ r3 )r r¼ r; r r r r s zValues.lateralzList[Tuple[Any, ...]])r r c C s | j |f7 _ | S )aj Return a new :class:`_expression.Values` construct,
|
adding the given data to the data list.
|
|
E.g.::
|
|
my_values = my_values.data([(1, 'value 1'), (2, 'value2')])
|
|
:param values: a sequence (i.e. list) of tuples that map to the
|
column expressions given in the :class:`_expression.Values`
|
constructor.
|
|
)r2 )r r r r r Údata¦ s z Values.dataÚ ScalarValuesc C s t | j| j| jS )zReturns a scalar ``VALUES`` construct that can be used as a
|
COLUMN element in a statement.
|
|
.. versionadded:: 2.0.0b4
|
|
)r= r4 r2 r5 r r r r Ú scalar_values¸ s zValues.scalar_valuesr¨ c C s" | j D ]}| j |¡ | |_qd S r )r4 rþ r rl )r r& r r r r, Á s
|
z"Values._populate_column_collectionr c C s | gS r r r r r r r4 Æ s zValues._from_objects)NF)N)r r r r¯ rÐ r2 ræ rT rø Údp_dml_multi_valuesr r¥ rO rT r r: r5 rÈ r r< r> r, rX r r4 r§ r r ru r r0 F s0
|
|
ü
|
ü r0 c st e Zd ZU dZdZdejfdejfdejfgZ de
|
d< dd d
|
d fd d Z e ddddZ d dddZ ZS )r= a{ Represent a scalar ``VALUES`` construct that can be used as a
|
COLUMN element in a statement.
|
|
The :class:`_expression.ScalarValues` object is created from the
|
:meth:`_expression.Values.scalar_values` method. It's also
|
automatically generated when a :class:`_expression.Values` is used in
|
an ``IN`` or ``NOT IN`` condition.
|
|
.. versionadded:: 2.0.0b4
|
|
r> r4 r2 r5 rS rO zSequence[ColumnClause[Any]]r1 r£ )r) r< r5 c s t ¡ || _|| _|| _d S r )rq rT r4 r2 r5 )r r) r< r5 ru r r rT à s
|
zScalarValues.__init__r6 r c C s dd | j D S )Nc S s g | ]
|
}|j qS r r7 r! r r r rã í s z.ScalarValues._column_types.<locals>.<listcomp>r9 r r r r r: ë s zScalarValues._column_typesc C s | S r r r r r r Ú__clause_element__ï s z ScalarValues.__clause_element__)r r r r¯ rÐ rT rø r? r¥ rO ræ rT r r: r@ r§ r r ru r r= Ë s
|
ý r= c @ s¢ e Zd ZU dZdZdZeZded< ddddd Z e
|
j d
|
d d d Z ddddddddZ e
|
j dd ddZedd ddZee
|
dd ¡d d d d Zed d d d!Zdd d"d#Zdd$d%d&d'Ze
|
dd(¡d)d)d*d+d,d-Zejd.d d/d0Zd1d d2d3Ze
|
dd4¡d5d d6d7Zd8d d9d:Z d5d d;d<Z d=d>d?d@dAZ dNd=dBd?dCdDZ dOd=d.d?dEdFZ d$d dGdHZ!dPd=dJd.dKdLdMZ"dS )Qrü zBase class for SELECT statements.
|
|
|
This includes :class:`_expression.Select`,
|
:class:`_expression.CompoundSelect` and
|
:class:`_expression.TextualSelect`.
|
|
|
TrH ræ r r¨ rª c C s | ¡ d S r )rs r¬ r r r r s z"SelectBase._refresh_for_new_columnú)ColumnCollection[str, ColumnElement[Any]]r c C s
|
t dS )a$ A :class:`_expression.ColumnCollection`
|
representing the columns that
|
this SELECT statement or similar construct returns in its result set.
|
|
This collection differs from the :attr:`_expression.FromClause.columns`
|
collection of a :class:`_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.
|
|
.. note::
|
|
The :attr:`_sql.SelectBase.selected_columns` collection does not
|
include expressions established in the columns clause using the
|
:func:`_sql.text` construct; these are silently omitted from the
|
collection. To use plain textual column expressions inside of a
|
:class:`_sql.Select` construct, use the :func:`_sql.literal_column`
|
construct.
|
|
.. seealso::
|
|
:attr:`_sql.Select.selected_columns`
|
|
.. versionadded:: 1.4
|
|
Nr r r r r Úselected_columns s zSelectBase.selected_columnsN©Úproxy_compound_columnsr
ú0Optional[Iterable[Sequence[ColumnElement[Any]]]]©rû rD r c C s
|
t d S r r ©r rû rD r r r r© . s z.SelectBase._generate_fromclause_column_proxiesr c C s
|
t dS )a A sequence of expressions that correspond to what is rendered
|
in the columns clause, including :class:`_sql.TextClause`
|
constructs.
|
|
.. versionadded:: 1.4.12
|
|
.. seealso::
|
|
:attr:`_sql.SelectBase.exported_columns`
|
|
Nr r r r r r¡ 8 s z SelectBase._all_selected_columnsz1ReadOnlyColumnCollection[str, ColumnElement[Any]]c C s
|
| j ¡ S )af A :class:`_expression.ColumnCollection`
|
that represents the "exported"
|
columns of this :class:`_expression.Selectable`, not including
|
:class:`_sql.TextClause` constructs.
|
|
The "exported" columns for a :class:`_expression.SelectBase`
|
object are synonymous
|
with the :attr:`_expression.SelectBase.selected_columns` collection.
|
|
.. versionadded:: 1.4
|
|
.. seealso::
|
|
:attr:`_expression.Select.exported_columns`
|
|
:attr:`_expression.Selectable.exported_columns`
|
|
:attr:`_expression.FromClause.exported_columns`
|
|
|
)rB r- r r r r r® G s zSelectBase.exported_columnsrà a× The :attr:`_expression.SelectBase.c` and :attr:`_expression.SelectBase.columns` attributes are deprecated and will be removed in a future release; these attributes implicitly create a subquery that should be explicit. Please call :meth:`_expression.SelectBase.subquery` first in order to create a subquery, which then contains this attribute. To access the columns that this SELECT object SELECTs from, use the :attr:`_expression.SelectBase.selected_columns` attribute.r' c C s | j jS r )Ú_implicit_subqueryr) r r r r r& b s z SelectBase.cc C s | j S r r( r r r r r) s s zSelectBase.columnsc C s
|
t dS )zX
|
Retrieve the current label style.
|
|
Implemented by subclasses.
|
|
Nr r r r r Úget_label_styley s zSelectBase.get_label_styler\ ©Ústyler c C s
|
t dS )zeReturn a new selectable with the specified label style.
|
|
Implemented by subclasses.
|
|
Nr ©r rK r r r r¡ s zSelectBase.set_label_stylea The :meth:`_expression.SelectBase.select` method is deprecated and will be removed in a future release; this method implicitly creates a subquery that should be explicit. Please call :meth:`_expression.SelectBase.subquery` first in order to create a subquery, which then can be selected.r r )r© ra r c O s | j j||S r )rH r r« r r r r s
|
zSelectBase.selectrú c C s | ¡ S r ©rû r r r r rH s z SelectBase._implicit_subqueryúTypeEngine[Any]c C s
|
t d S r r r r r r Ú _scalar_type s zSelectBase._scalar_typez«The :meth:`_expression.SelectBase.as_scalar` method is deprecated and will be removed in a future release. Please refer to :meth:`_expression.SelectBase.scalar_subquery`.rþ c C s | ¡ S r )rÿ r r r r r s zSelectBase.as_scalarÚExistsc C s t | S )aa Return an :class:`_sql.Exists` representation of this selectable,
|
which can be used as a column expression.
|
|
The returned object is an instance of :class:`_sql.Exists`.
|
|
.. seealso::
|
|
:func:`_sql.exists`
|
|
:ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.
|
|
.. versionadded:: 1.4
|
|
)rP r r r r Úexists¨ s zSelectBase.existsc C s | j tk r| t¡} t| S )aX Return a 'scalar' representation of this selectable, which can be
|
used as a column expression.
|
|
The returned object is an instance of :class:`_sql.ScalarSelect`.
|
|
Typically, a select statement which has only one column in its columns
|
clause is eligible to be used as a scalar expression. The scalar
|
subquery can then be used in the WHERE clause or columns clause of
|
an enclosing SELECT.
|
|
Note that the scalar subquery differentiates from the FROM-level
|
subquery that can be produced using the
|
:meth:`_expression.SelectBase.subquery`
|
method.
|
|
.. versionchanged: 1.4 - the ``.as_scalar()`` method was renamed to
|
:meth:`_expression.SelectBase.scalar_subquery`.
|
|
.. seealso::
|
|
:ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
|
|
)ræ rJ r¡ Ú ScalarSelectr r r r rÿ ¹ s
|
|
zSelectBase.scalar_subqueryr¹ z
|
Label[Any]r» c C s | ¡ |¡S )z¾Return a 'scalar' representation of this selectable, embedded as a
|
subquery with a label.
|
|
.. seealso::
|
|
:meth:`_expression.SelectBase.scalar_subquery`.
|
|
)rÿ ÚlabelrÁ r r r rS Ö s zSelectBase.labelrº c C s t | |¡S )r½ )r¿ r¿ rÁ r r r r á s zSelectBase.lateralc C s t j| ¡ |dS )aÈ Return a subquery of this :class:`_expression.SelectBase`.
|
|
A subquery is from a SQL perspective a parenthesized, named
|
construct that can be placed in the FROM clause of another
|
SELECT statement.
|
|
Given a SELECT statement such as::
|
|
stmt = select(table.c.id, table.c.name)
|
|
The above statement might look like::
|
|
SELECT table.id, table.name FROM table
|
|
The subquery form by itself renders the same way, however when
|
embedded into the FROM clause of another SELECT statement, it becomes
|
a named sub-element::
|
|
subq = stmt.subquery()
|
new_stmt = select(subq)
|
|
The above renders as::
|
|
SELECT anon_1.id, anon_1.name
|
FROM (SELECT table.id, table.name FROM table) AS anon_1
|
|
Historically, :meth:`_expression.SelectBase.subquery`
|
is equivalent to calling
|
the :meth:`_expression.FromClause.alias`
|
method on a FROM object; however,
|
as a :class:`_expression.SelectBase`
|
object is not directly FROM object,
|
the :meth:`_expression.SelectBase.subquery`
|
method provides clearer semantics.
|
|
.. versionadded:: 1.4
|
|
r¾ )rú rÀ Ú_ensure_disambiguated_namesrÁ r r r rû î s ( ÿzSelectBase.subqueryc C s
|
t dS )ztEnsure that the names generated by this selectbase will be
|
disambiguated in some way, if possible.
|
|
Nr r r r r rT s z&SelectBase._ensure_disambiguated_namesFr£ r c C s | j |dS )a. Return a named subquery against this
|
:class:`_expression.SelectBase`.
|
|
For a :class:`_expression.SelectBase` (as opposed to a
|
:class:`_expression.FromClause`),
|
this returns a :class:`.Subquery` object which behaves mostly the
|
same as the :class:`_expression.Alias` object that is used with a
|
:class:`_expression.FromClause`.
|
|
.. versionchanged:: 1.4 The :meth:`_expression.SelectBase.alias`
|
method is now
|
a synonym for the :meth:`_expression.SelectBase.subquery` method.
|
|
r¾ rM r r r r rÈ " s zSelectBase.alias)N)N)NF)#r r r r¯ r± Ú is_selectrJ ræ ræ r rX r rB r© r¡ r r® rÒ r& r) rI r¡ r rB rË rH rO r rQ rÿ rS r rû rT rÈ r r r r rü ó sZ
|
|
%ú
|
þ þ þ , ÿrü Ú_SBc @ sü e Zd ZU dZdZdejfgZded< dZ ded< ddd d
|
d Z
|
d d ddZ dd ddZ dd dddZ edd ddZd/dddd d Zer¤dd d d Zdd d!d"dd#d$d%Zejd&d d'd(Zejd)d d*d+Zejd,d d-d.ZdS )0ÚSelectStatementGroupingzÞRepresent a grouping of a :class:`_expression.SelectBase`.
|
|
This differs from :class:`.Subquery` in that we are still
|
an "inner" SELECT statement, this is strictly for grouping inside of
|
compound selects.
|
|
Zselect_statement_groupingr rS rO TrV r¨ ©r r c C s t tt tj|¡| _d S r )r rV r rß r rý r r r r r rT K s ÿz SelectStatementGrouping.__init__z SelectStatementGrouping[_SB]r c C s$ | j ¡ }|| j k r t|S | S d S r )r rT rW )r Ú new_elementr r r rT P s
|
|
z3SelectStatementGrouping._ensure_disambiguated_namesrH c C s
|
| j ¡ S r )r rI r r r r rI W s z'SelectStatementGrouping.get_label_style©Ú label_styler c C s t | j |¡S r )rW r r¡ )r r[ r r r r¡ Z s
|
ÿz'SelectStatementGrouping.set_label_stylec C s | j S r r· r r r r Úselect_statementa s z(SelectStatementGrouping.select_statementNr= r\ r> c C s | S r r r@ r r r rA e s z"SelectStatementGrouping.self_groupc C s d S r r r r r r Ú_ungroupk s z SelectStatementGrouping._ungrouprC r
rE rF c C s | j j||d d S )NrC rµ rG r r r r© s s ÿz;SelectStatementGrouping._generate_fromclause_column_proxiesr c C s | j jS r )r r¡ r r r r r¡ s z-SelectStatementGrouping._all_selected_columnsrA c C s | j jS )a: A :class:`_expression.ColumnCollection`
|
representing the columns that
|
the embedded SELECT statement returns in its result set, not including
|
:class:`_sql.TextClause` constructs.
|
|
.. versionadded:: 1.4
|
|
.. seealso::
|
|
:attr:`_sql.Select.selected_columns`
|
|
)r rB r r r r rB s z(SelectStatementGrouping.selected_columnsr c C s | j jS r r r r r r r4 s z%SelectStatementGrouping._from_objects)N)r r r r¯ rÐ rT r¤ rO ræ Z_is_select_containerrT rT rI r¡ r r\ rA r r] r© rX r r¡ rB r4 r r r r rW 9 s.
|
ÿ ú rW c @ s e Zd ZU dZdZded< dZded< dZded< dZded < dZ ded
|
< dZ
|
d ed < dZ d ed< e fddddZ edddddddddddddddZddd d Zddd d d Zed!dd"d#Zed!dd$d%ZdVd&d'd(d)d*d+d,Zed)d-d.d/d0d1Zed2d-d3d/d4d1Zdd-d5d/d6d1Zed3dd7d8Zd9dd:d;d<Zed3dd=d>Zeddd?d@Zed&ddAdBdCZ edWd&ddddDdEdFZ ed&ddGdHdIZ ee dJ¡dKdKddLdMdNZ!ee"j#fdOdPddQdRdSZ$ee"j#fdOdPddQdTdUZ%dS )XÚGenerativeSelecta& Base class for SELECT statements where additional elements can be
|
added.
|
|
This serves as the base for :class:`_expression.Select` and
|
:class:`_expression.CompoundSelect`
|
where elements such as ORDER BY, GROUP BY can be added and column
|
rendering can be controlled. Compare to
|
:class:`_expression.TextualSelect`, which,
|
while it subclasses :class:`_expression.SelectBase`
|
and is also a SELECT construct,
|
represents a fixed textual string which cannot be altered at this level,
|
only wrapped as a subquery.
|
|
r ú Tuple[ColumnElement[Any], ...]Ú_order_by_clausesÚ_group_by_clausesNú Optional[ColumnElement[Any]]Ú _limit_clauseÚ_offset_clauseÚ _fetch_clausezOptional[Dict[str, bool]]Ú_fetch_clause_optionsr Ú_for_update_argrH ©ræ c C s
|
|| _ d S r rh )r ræ r r r rT ° s zGenerativeSelect.__init__Fr) r£ r* r\ )r r r r r$ r c C s t |||||d| _| S )a" Specify a ``FOR UPDATE`` clause for this
|
:class:`_expression.GenerativeSelect`.
|
|
E.g.::
|
|
stmt = select(table).with_for_update(nowait=True)
|
|
On a database like PostgreSQL or Oracle, the above would render a
|
statement like::
|
|
SELECT table.a, table.b FROM table FOR UPDATE NOWAIT
|
|
on other backends, the ``nowait`` option is ignored and instead
|
would produce::
|
|
SELECT table.a, table.b FROM table FOR UPDATE
|
|
When called with no arguments, the statement will render with
|
the suffix ``FOR UPDATE``. Additional arguments can then be
|
provided which allow for common database-specific
|
variants.
|
|
:param nowait: boolean; will render ``FOR UPDATE NOWAIT`` on Oracle
|
and PostgreSQL dialects.
|
|
:param read: boolean; will render ``LOCK IN SHARE MODE`` on MySQL,
|
``FOR SHARE`` on PostgreSQL. On PostgreSQL, when combined with
|
``nowait``, will render ``FOR SHARE NOWAIT``.
|
|
:param of: SQL expression or list of SQL expression elements,
|
(typically :class:`_schema.Column` objects or a compatible expression,
|
for some backends may also be a table expression) which will render
|
into a ``FOR UPDATE OF`` clause; supported by PostgreSQL, Oracle, some
|
MySQL versions and possibly others. May render as a table or as a
|
column depending on backend.
|
|
:param skip_locked: boolean, will render ``FOR UPDATE SKIP LOCKED``
|
on Oracle and PostgreSQL dialects or ``FOR SHARE SKIP LOCKED`` if
|
``read=True`` is also specified.
|
|
:param key_share: boolean, will render ``FOR NO KEY UPDATE``,
|
or if combined with ``read=True`` will render ``FOR KEY SHARE``,
|
on the PostgreSQL dialect.
|
|
r) )r rg r/ r r r r! ³ s 7ûz GenerativeSelect.with_for_updater c C s | j S )zS
|
Retrieve the current label style.
|
|
.. versionadded:: 1.4
|
|
rh r r r r rI ó s z GenerativeSelect.get_label_stylerJ c C s | j |k r| ¡ } || _ | S )a| Return a new selectable with the specified label style.
|
|
There are three "label styles" available,
|
:attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`,
|
:attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`, and
|
:attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`. The default style is
|
:attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`.
|
|
In modern SQLAlchemy, there is not generally a need to change the
|
labeling style, as per-expression labels are more effectively used by
|
making use of the :meth:`_sql.ColumnElement.label` method. In past
|
versions, :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` was used to
|
disambiguate same-named columns from different tables, aliases, or
|
subqueries; the newer :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` now
|
applies labels only to names that conflict with an existing name so
|
that the impact of this labeling is minimal.
|
|
The rationale for disambiguation is mostly so that all column
|
expressions are available from a given :attr:`_sql.FromClause.c`
|
collection when a subquery is created.
|
|
.. versionadded:: 1.4 - the
|
:meth:`_sql.GenerativeSelect.set_label_style` method replaces the
|
previous combination of ``.apply_labels()``, ``.with_labels()`` and
|
``use_labels=True`` methods and/or parameters.
|
|
.. seealso::
|
|
:data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`
|
|
:data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`
|
|
:data:`_sql.LABEL_STYLE_NONE`
|
|
:data:`_sql.LABEL_STYLE_DEFAULT`
|
|
)ræ Ú _generaterL r r r r¡ ü s &
|
z GenerativeSelect.set_label_stylerI c C s t tj| j¡S )z9ClauseList access to group_by_clauses for legacy dialects)rI Ú_construct_rawr Úcomma_opra r r r r Ú_group_by_clause' s ÿz!GenerativeSelect._group_by_clausec C s t tj| j¡S )z9ClauseList access to order_by_clauses for legacy dialects)rI rj r rk r` r r r r Ú_order_by_clause. s ÿz!GenerativeSelect._order_by_clauserc r¹ z"Optional[_TypeEngineArgument[int]]r )r r¼ Útype_r c C s t jtj|||dS )zÉConvert the given value to an "offset or limit" clause.
|
|
This handles incoming integers and converts to an expression; if
|
an expression is already given, it is passed through.
|
|
)r¼ rn )r rß r ZLimitOffsetRole)r r r¼ rn r r r Ú_offset_or_limit_clause5 s ÿz(GenerativeSelect._offset_or_limit_clauserÝ r )ÚclauseÚattrnamer c C s d S r r ©r rp rq r r r Ú _offset_or_limit_clause_asintE s z.GenerativeSelect._offset_or_limit_clause_asintzOptional[_OffsetLimitParam]r c C s d S r r rr r r r rs K s z Union[NoReturn, Optional[int]]c
|
C sX |dkr dS z
|
|j }W n2 tk
|
rH } zt d| ¡|W 5 d}~X Y n X t |¡S dS )zàConvert the "offset or limit" clause of a select construct to an
|
integer.
|
|
This is only possible if the value is stored as a simple bound
|
parameter. Otherwise, a compilation error is raised.
|
|
Nz@This SELECT structure does not use a simple integer value for %s)r ÚAttributeErrorrW Ú CompileErrorrX Zasint)r rp rq ÚvalueÚerrr r r rs Q s
|
|
ÿÿýc C s | | jd¡S )zûGet an integer value for the limit. This should only be used
|
by code that cannot support a limit as a BindParameter or
|
other custom clause as it will throw an exception if the limit
|
isn't currently set to an integer.
|
|
Úlimit)rs rc r r r r Ú_limitg s zGenerativeSelect._limitrH ©rp r c C s
|
t |tS )zkTrue if the clause is a simple integer, False
|
if it is not present or is a SQL expression.
|
)rk r )r rp r r r Ú_simple_int_clauseq s z#GenerativeSelect._simple_int_clausec C s | | jd¡S )zÿGet an integer value for the offset. This should only be used
|
by code that cannot support an offset as a BindParameter or
|
other custom clause as it will throw an exception if the
|
offset isn't currently set to an integer.
|
|
Úoffset)rs rd r r r r Ú_offsetw s ÿzGenerativeSelect._offsetc C s | j d k p | jd k p | jd k S r )rc rd re r r r r Ú_has_row_limiting_clause s
|
|
ÿýz)GenerativeSelect._has_row_limiting_clause)rx r c C s d | _ | _| |¡| _| S )aÑ Return a new selectable with the given LIMIT criterion
|
applied.
|
|
This is 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.
|
|
.. note::
|
|
The :meth:`_sql.GenerativeSelect.limit` method will replace
|
any clause applied with :meth:`_sql.GenerativeSelect.fetch`.
|
|
:param limit: an integer LIMIT parameter, or a SQL expression
|
that provides an integer result. Pass ``None`` to reset it.
|
|
.. seealso::
|
|
:meth:`_sql.GenerativeSelect.fetch`
|
|
:meth:`_sql.GenerativeSelect.offset`
|
|
N)re rf ro rc )r rx r r r rx s zGenerativeSelect.limit)ÚcountÚ with_tiesÚpercentr c C s8 d| _ |dkr d | _| _n| |¡| _||d| _| S )a Return a new selectable with the given FETCH FIRST criterion
|
applied.
|
|
This is a numeric value which usually renders as
|
``FETCH {FIRST | NEXT} [ count ] {ROW | ROWS} {ONLY | WITH TIES}``
|
expression in the resulting select. This functionality is
|
is currently implemented for Oracle, PostgreSQL, MSSQL.
|
|
Use :meth:`_sql.GenerativeSelect.offset` to specify the offset.
|
|
.. note::
|
|
The :meth:`_sql.GenerativeSelect.fetch` method will replace
|
any clause applied with :meth:`_sql.GenerativeSelect.limit`.
|
|
.. versionadded:: 1.4
|
|
:param count: an integer COUNT parameter, or a SQL expression
|
that provides an integer result. When ``percent=True`` this will
|
represent the percentage of rows to return, not the absolute value.
|
Pass ``None`` to reset it.
|
|
:param with_ties: When ``True``, the WITH TIES option is used
|
to return any additional rows that tie for the last place in the
|
result set according to the ``ORDER BY`` clause. The
|
``ORDER BY`` may be mandatory in this case. Defaults to ``False``
|
|
:param percent: When ``True``, ``count`` represents the percentage
|
of the total number of selected rows to return. Defaults to ``False``
|
|
.. seealso::
|
|
:meth:`_sql.GenerativeSelect.limit`
|
|
:meth:`_sql.GenerativeSelect.offset`
|
|
N)r r )rc re rf ro )r r r r r r r Úfetch© s - þzGenerativeSelect.fetch)r| r c C s | |¡| _| S )a2 Return a new selectable with the given OFFSET criterion
|
applied.
|
|
|
This is 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.
|
|
:param offset: an integer OFFSET parameter, or a SQL expression
|
that provides an integer result. Pass ``None`` to reset it.
|
|
.. seealso::
|
|
:meth:`_sql.GenerativeSelect.limit`
|
|
:meth:`_sql.GenerativeSelect.fetch`
|
|
)ro rd )r r| r r r r| á s zGenerativeSelect.offsetrÅ r' )ÚstartÚstopr c C s4 t jj}d | _| _| | j| j||¡\| _| _| S )a Apply LIMIT / OFFSET to this statement based on a slice.
|
|
The start and stop indices behave like the argument to Python's
|
built-in :func:`range` function. This method provides an
|
alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
|
query.
|
|
For example, ::
|
|
stmt = select(User).order_by(User).id.slice(1, 3)
|
|
renders as
|
|
.. sourcecode:: sql
|
|
SELECT users.id AS users_id,
|
users.name AS users_name
|
FROM users ORDER BY users.id
|
LIMIT ? OFFSET ?
|
(2, 1)
|
|
.. note::
|
|
The :meth:`_sql.GenerativeSelect.slice` method will replace
|
any clause applied with :meth:`_sql.GenerativeSelect.fetch`.
|
|
.. versionadded:: 1.4 Added the :meth:`_sql.GenerativeSelect.slice`
|
method generalized from the ORM.
|
|
.. seealso::
|
|
:meth:`_sql.GenerativeSelect.limit`
|
|
:meth:`_sql.GenerativeSelect.offset`
|
|
:meth:`_sql.GenerativeSelect.fetch`
|
|
N)rX rÉ rÊ re rf Z _make_slicerc rd )r r r rÊ r r r Úsliceú s - ÿ zGenerativeSelect.slicezMUnion[Literal[None, _NoArg.NO_ARG], _ColumnExpressionOrStrLabelArgument[Any]]z(_ColumnExpressionOrStrLabelArgument[Any])Ú_GenerativeSelect__firstÚclausesr c G sD |s|dkrd| _ n,|tjk r@| j tdd |f| D 7 _ | S )a¥ Return a new selectable with the given list of ORDER BY
|
criteria applied.
|
|
e.g.::
|
|
stmt = select(table).order_by(table.c.id, table.c.name)
|
|
Calling this method multiple times is equivalent to calling it once
|
with all the clauses concatenated. All existing ORDER BY criteria may
|
be cancelled by passing ``None`` by itself. New ORDER BY criteria may
|
then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::
|
|
# will erase all ORDER BY and ORDER BY new_col alone
|
stmt = stmt.order_by(None).order_by(new_col)
|
|
:param \*clauses: a series of :class:`_expression.ColumnElement`
|
constructs
|
which will be used to generate an ORDER BY clause.
|
|
.. seealso::
|
|
:ref:`tutorial_order_by` - in the :ref:`unified_tutorial`
|
|
:ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
|
|
Nr c s s | ]}t tj|¡V qd S r )r rß r Z OrderByRole©rá rp r r r r$ U s ÿz,GenerativeSelect.order_by.<locals>.<genexpr>)r` r8 ÚNO_ARGrä ©r r r r r r Úorder_by. s $
|
þ zGenerativeSelect.order_byc G sD |s|dkrd| _ n,|tjk r@| j tdd |f| D 7 _ | S )a~ Return a new selectable with the given list of GROUP BY
|
criterion applied.
|
|
All existing GROUP BY settings can be suppressed by passing ``None``.
|
|
e.g.::
|
|
stmt = select(table.c.name, func.max(table.c.stat)).\
|
group_by(table.c.name)
|
|
:param \*clauses: a series of :class:`_expression.ColumnElement`
|
constructs
|
which will be used to generate an GROUP BY clause.
|
|
.. seealso::
|
|
:ref:`tutorial_group_by_w_aggregates` - in the
|
:ref:`unified_tutorial`
|
|
:ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
|
|
Nr c s s | ]}t tj|¡V qd S r )r rß r Z GroupByRoler r r r r$ ~ s ÿz,GenerativeSelect.group_by.<locals>.<genexpr>)ra r8 r rä r r r r Úgroup_by[ s
|
þ zGenerativeSelect.group_by)NN)FF)&r r r r¯ r` ræ ra rc rd re rf rg rM rT r5 r! rI r¡ r rl rm ro r rs ry r{ r} r~ rx r r| rX rÓ r
r8 r r r r r r r r^ sl
|
ù ? + ü ü72û,ûr^ ÚdefaultÚcompound_selectc @ s e Zd ZejddddZdS )ÚCompoundSelectStateúbTuple[Dict[str, ColumnElement[Any]], Dict[str, ColumnElement[Any]], Dict[str, ColumnElement[Any]]]r c C s* | j ¡ }d|_dd |jD }|||fS )NFc S s i | ] }|j |qS r r r5 r r r re s z;CompoundSelectState._label_resolve_dict.<locals>.<dictcomp>)Ú statementrû rB r& )r Zhacky_subqueryÚdr r r Ú_label_resolve_dict s
|
z'CompoundSelectState._label_resolve_dictN)r r r rX Zmemoized_propertyr r r r r r
s r c @ s$ e Zd ZdZdZdZdZdZdZdS )Ú_CompoundSelectKeywordÚUNIONz UNION ALLÚEXCEPTz
|
EXCEPT ALLÚ INTERSECTz INTERSECT ALLN) r r r r Ú UNION_ALLr Ú
|
EXCEPT_ALLr Ú INTERSECT_ALLr r r r r s r c
|
s® e Zd ZU dZdZdejfdejfdejfdejfdejfdejfd ejfd
|
ejfd ej fg e
|
j Z d e d < de d< dZdZdddddZedd dddZedd dddZedd ddd Zedd dd d Zedd dd d Zedd dd!d"Zd#d$d%d&ZdId(d)d*d+d,Zd-d.d/d0d1Zd2d d3d4d5Zd d$d6d7Z d'd8d9d:d;d<d=d>Z d?d;d@ fdAdB Z e j dCd$dDdEZ!e j dFd$dGdHZ" Z#S )JÚCompoundSelectaX Forms the basis of ``UNION``, ``UNION ALL``, and other
|
SELECT-based set operations.
|
|
|
.. seealso::
|
|
:func:`_expression.union`
|
|
:func:`_expression.union_all`
|
|
:func:`_expression.intersect`
|
|
:func:`_expression.intersect_all`
|
|
:func:`_expression.except`
|
|
:func:`_expression.except_all`
|
|
r Úselectsrc rd re rf r` ra rg ÚkeywordrS rO zList[SelectBase]TFr rg )r r c s( | _ fdd|D _t ¡ d S )Nc s" g | ]}t tj|¡j d qS )rQ )r rß r ÚCompoundElementRolerA )rá Úsr r r rã Í s ýÿz+CompoundSelect.__init__.<locals>.<listcomp>)r r r^ rT )r r r r r r rT Ç s
|
|
üzCompoundSelect.__init__)r r c G s t tjf| S r )r r r ©r r r r r Ú _create_unionÖ s z CompoundSelect._create_unionc G s t tjf| S r )r r r r r r r Ú_create_union_allÜ s z CompoundSelect._create_union_allc G s t tjf| S r )r r r r r r r Ú_create_exceptâ s z CompoundSelect._create_exceptc G s t tjf| S r )r r r r r r r Ú_create_except_allè s z!CompoundSelect._create_except_allc G s t tjf| S r )r r r r r r r Ú_create_intersectî s z CompoundSelect._create_intersectc G s t tjf| S r )r r r r r r r Ú_create_intersect_allô s z$CompoundSelect._create_intersect_allrN r c C s | j d ¡ S ©Nr )r rO r r r r rO ú s zCompoundSelect._scalar_typeNr= rM r> c C s t | S r )rW r@ r r r rA ý s zCompoundSelect.self_groupr¢ r£ r¤ c C s | j D ]}| |¡r dS qdS ©NTF)r r§ )r r¥ r r r r r§ s
|
|
z CompoundSelect.is_derived_fromrH rJ c C s< | j |k r8| ¡ } | jd |¡}|g| jdd
| _| S ©Nr r )ræ ri r r¡ )r rK Úselect_0r r r r¡ s
|
|
z CompoundSelect.set_label_stylec C s> | j d ¡ }|| j d k r:| ¡ } |g| j dd
| _ | S r© )r rT ri )r Z
|
new_selectr r r rT s
|
z*CompoundSelect._ensure_disambiguated_namesrC r
rE r¨ rF c C sT | j d }| jtk r | | j¡}tdd dd t| j D D }|j||d d S )Nr c s$ g | ] \ } fd d|j D qS )c s g | ]}t |r| ¡qS r )r$ Z _annotater5 ©Úddr r rã 2 s þzQCompoundSelect._generate_fromclause_column_proxies.<locals>.<listcomp>.<listcomp>)r¡ )rá Ústmtr r« r rã 1 s û
|
þzFCompoundSelect._generate_fromclause_column_proxies.<locals>.<listcomp>c S s g | ]\}}d |d i|fqS )Zweightr r )rá Úir r r r rã 7 s ÿrC )r ræ rM r¡ ÚzipÚ enumerater© )r rû rD rª Úextra_col_iteratorr r r r© s
|
|
þúÿ ÿz2CompoundSelect._generate_fromclause_column_proxiesr rª c s& t |¡ | jD ]}| |¡ qd S r )rq r r )r r« r ru r r r G s
|
z&CompoundSelect._refresh_for_new_columnr c C s | j d jS r§ )r r¡ r r r r r¡ L s z$CompoundSelect._all_selected_columnsrA c C s | j d jS )a\ A :class:`_expression.ColumnCollection`
|
representing the columns that
|
this SELECT statement or similar construct returns in its result set,
|
not including :class:`_sql.TextClause` constructs.
|
|
For a :class:`_expression.CompoundSelect`, the
|
:attr:`_expression.CompoundSelect.selected_columns`
|
attribute returns the selected
|
columns of the first SELECT statement contained within the series of
|
statements within the set operation.
|
|
.. seealso::
|
|
:attr:`_sql.Select.selected_columns`
|
|
.. versionadded:: 1.4
|
|
r )r rB r r r r rB P s z CompoundSelect.selected_columns)N)$r r r r¯ rÐ rT rø r¤ Ú dp_plain_dictr r* Ú%_clone_annotations_traverse_internalsrO ræ rº Ú_auto_correlaterT r¦ r¡ r¢ r£ r¤ r¥ r¦ rO rA r§ r¡ rT r© r rX r r¡ rB r§ r r ru r r sV
|
÷
|
ö ÿú/r r c @ s~ e Zd ZU dZer ded< nG dd deZerDeddddd Z d
|
d d d ddZ
|
eddddZ ed
|
ddddZ ed
|
dddddZ ed
|
d dd d Zed d d!d"d#Zd
|
d dd$d%ZedNd'd(d)d d*d+d,ZdOd-d-d d.d/d0Zd1dd2d3Zed
|
d4d5d6d7Zed
|
d8dd9d:Zd;d<d=d>d?d@Ze dA¡d<dBdCdDdEdFdGdHZe dA¡dIdJdKdLdMZd&S )Pré )Ú from_clausesÚfromsÚcolumns_plus_namesr r: Ú default_select_compile_optionsc @ s e Zd Zg ZdS )z*SelectState.default_select_compile_optionsN)r r r Ú_cache_key_traversalr r r r r¸ { s r? úType[SelectState]©r r c C s d S r r ©r r r r r Úget_plugin_class s z SelectState.get_plugin_classr zOptional[SQLCompiler]r )r Úcompilerra c K s\ || _ |j| _|jD ]}| |j|j¡ q|jr@| |j|j¡ | |¡| _| d¡| _ d S r¹ )
|
r Ú _from_objrµ Ú_memoized_select_entitiesÚ _setup_joinsÚ _raw_columnsÚ
|
_get_fromsr¶ rï r· )r r r¾ ra Zmemoized_entitiesr r r rT s
|
ÿ zSelectState.__init__r r c C s t dd S )NzLThe default SELECT construct without plugins does not implement this method.r )r r r r Ú_plugin_not_implemented s ÿz#SelectState._plugin_not_implementedzList[Dict[str, Any]]c C s dd | d¡D S )Nc S s$ g | ] \}}}}}||j |d qS ))r¼ r8 Úexprr7 )rá Ú_r¼ r r r r rã ¤ s
|
üýz7SelectState.get_column_descriptions.<locals>.<listcomp>F)rï r¼ r r r Úget_column_descriptions s ùz#SelectState.get_column_descriptionsúroles.ReturnsRowsRoler´ )r Úfrom_statementr c C s | ¡ d S r )rÄ )r r rÉ r r r rÉ ¯ s zSelectState.from_statementr c C s | tj dd |jD ¡¡S )Nc s s | ] }|j V qd S r r3 ©rá r r r r r$ º s z7SelectState.get_columns_clause_froms.<locals>.<genexpr>)Ú_normalize_fromsr\ r] Ú from_iterabler r¼ r r r Úget_columns_clause_fromsµ s
|
ÿÿz$SelectState.get_columns_clause_fromsrH Ú_LabelConventionCallablerZ c s> |t k|tk t t ddddd fdd }|S )Nrã r¹ )r& Úcol_namer c sª t | r d S tr t| s t s6| j}|d kr2d}|S r@| jn| j}|d krxd}|krh| |¡ S |¡ |S n.|krr| j S | j S |¡ |S d S )NZ _no_label)
|
r( r r$ r7 Z
|
_proxy_keyrX Z _anon_labelr Z_anon_tq_key_labelZ_anon_key_label)r& rÏ r¼ ©r rì Úparî r r ÚgoÊ s.
|
ÿ ý
|
z1SelectState._column_naming_convention.<locals>.go)N)rK rJ rU r8 )r r[ rÒ r rÐ r rê ¿ s þ #z%SelectState._column_naming_conventionc C sN i | _ }| jt | jtj dd |jD ¡tj dd |jD ¡¡||dS )Nc S s g | ]
|
}|j qS r r3 rÊ r r r rã ÷ s ÿz*SelectState._get_froms.<locals>.<listcomp>c S s g | ]
|
}|j qS r r3 rÊ r r r rã ý s ÿ)Úcheck_statementÚambiguous_table_name_map)Z_ambiguous_table_name_maprË r\ r] rµ rÌ r Ú_where_criteria)r r rÔ r r r rà ï s$
|
þÿþÿøïzSelectState._get_fromsNrú zOptional[Select[Any]]z Optional[_AmbiguousTableNameMap])Úiterable_of_fromsrÓ rÔ r c sª t }g }|D ]B}t|r.|j|kr.t d¡| |j¡s| |¡ | |j¡ q|r¦t t j
|
dd |D ¡rfdd|D } dk r¦ fdd|D ¡ |S )a0 given an iterable of things to select FROM, reduce them to what
|
would actually render in the FROM clause of a SELECT.
|
|
This does the job of checking for JOINs, tables, etc. that are in fact
|
overlapping due to cloning, adaption, present in overlapping joins,
|
etc.
|
|
z-select() construct refers to itself as a FROMc S s g | ]}t |jqS r )r2 rü rb r r r rã ' s z0SelectState._normalize_froms.<locals>.<listcomp>c s g | ]}| kr|qS r r rb )Útoremover r rã - s Nc 3 sL | ]D}|j D ]8}t|r |jr |j kr |jt t|j|j¡fV q qd S r )r4 r' rÿ r¼ rE r´ rV )rá ÚitemÚfr)rÔ r r r$ 0 s
|
÷ ÿþz/SelectState._normalize_froms.<locals>.<genexpr>) r8 r& r rW ÚInvalidRequestErrorr r r r[ r\ r] rÌ )r rÖ rÓ rÔ Úseenr¶ rØ r )rÔ r× r rË s. ÿ
|
ÿÿùz SelectState._normalize_fromsz Optional[Sequence[FromClause]])Úexplicit_correlate_fromsÚimplicit_correlate_fromsr c s j jjr0jjr0 fddD jjdk rR fddD jjrrtdkrfddD tst dj ¡S )a Return the full list of 'from' clauses to be displayed.
|
|
Takes into account a set of existing froms which may be
|
rendered in the FROM clause of enclosing selects; this Select
|
may want to leave those absent if it is automatically
|
correlating.
|
|
c s( g | ] }|t t pd kr|qS ©r r- rb )rÜ r¶ Ú to_correlater r rã S s ÿüÿþz2SelectState._get_display_froms.<locals>.<listcomp>Nc s, g | ]$}|t t pd jjkr|qS rÞ )r, r. r Ú_correlate_exceptrb )rÜ r¶ r r r rã a s ÿüÿþr c s g | ]}|t kr|qS r r- rb )r¶ rÝ r r rã s s
|
ÿþzSelect statement '%r' returned no FROM clauses due to auto-correlation; specify correlate(<tables>) to control correlation manually.)r¶ r Ú
|
_correlaterà r´ r rW rÚ )r rÜ rÝ r )rÜ r¶ rÝ r rß r Ú_get_display_froms@ s6 þ þ ÿþ
|
ý þûÿ z SelectState._get_display_fromsr c C sV dd | j jD }dd t| jD }| ¡ }| ¡ D ]\}}| ||¡ q6|||fS )Nc S s i | ]}|j r|jp|j|qS r )Ú_allow_label_resolverë r3 r5 r r r re s þ
|
zBSelectState._memoized_attr__label_resolve_dict.<locals>.<dictcomp>c S s i | ]}|j r|j|qS r )rã r3 r5 r r r re s þ )r r¡ r9 r¶ ÚcopyÚitemsÚ
|
setdefault)r Z with_colsZ
|
only_fromsZ only_colsr3 rv r r r Ú"_memoized_attr__label_resolve_dict s þþz.SelectState._memoized_attr__label_resolve_dictz Optional[_JoinTargetElement])r r c C s |j r|j d d S d S d S )Néÿÿÿÿr )rÁ )r r r r r Ú determine_last_joined_entity s z(SelectState.determine_last_joined_entityr c C s dd t |jD S )Nc S s g | ]}|qS r r r5 r r r rã ¨ s z4SelectState.all_selected_columns.<locals>.<listcomp>)r9 r r¼ r r r Úall_selected_columns¦ s z SelectState.all_selected_columnsú Tuple[_SetupJoinsElement, ...]úList[_ColumnsClauseElement]r¨ )ÚargsÚ raw_columnsr c C s |D ]ø\}}}}t r*|d k r*t|ts*t|d }|d }|d krX| ||||¡\}} n
|
| |¡} t rt|tstt|d k rt|tst| d k rÔ| j| }
|
| jd |
t|
|
||||df | j| d d
| _q|d k sàt| jt|||||df | _qd S )Nr r r r ) r rk rK r7 Ú"_join_determine_implicit_left_sideÚ _join_place_explicit_left_sider
rµ r ) r rí rî r r rN Úflagsr r Úreplace_from_obj_indexZ left_clauser r r rÁ ª sV ÿýþÿ
|
ûÿÿ
|
öÿ ÿzSelectState._setup_joinsrÅ r¢ Ú_JoinTargetElementrb z*Tuple[Optional[FromClause], Optional[int]])rî rN r r r c C sò t jj}d}| j}|rB| |||¡}t|dkr¾|d }|| }n|i } | j}
|
t tj dd |D ¡tj dd |
|
j
|
D ¡¡D ] } d| | < q~t | ¡ } | | ||¡}t|dkr¾| |d }t|dkrÖt d¡n|sêt d |f ¡||fS )
|
zWhen join conditions don't express the left side explicitly,
|
determine if an existing FROM or entity in this query
|
can serve as the left hand side.
|
|
Nr r c S s g | ]
|
}|j qS r r3 rÊ r r r rã s zBSelectState._join_determine_implicit_left_side.<locals>.<listcomp>c S s g | ]
|
}|j qS r r3 rÊ r r r rã s ÿr a Can't determine which FROM clause to join from, there are multiple FROMS which can join to this entity. Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity.zÇDon't know how to join to %r. Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity.)rX rÉ rÊ rµ Z find_left_clause_to_join_fromr r r\ r] rÌ rÕ r ÚkeysrW rÚ ) r rî rN r r rÊ rò rµ ÚindexesZ potentialr Z from_clauseZ all_clausesr r r rï å sX ÿ
|
ÿþÿü
|
ÿ ÿüÿz.SelectState._join_determine_implicit_left_sider
r )rN r c C sX d }t jj}t| j ¡ }|r.| | j|¡}ng }t|dkrHt
|
d¡|rT|d }|S )Nr zrCan't identify which entity in which to assign the left side of this join. Please use a more specific ON clause.r ) rX rÉ rÊ r r Ú_iterate_from_elementsZ#find_left_clause_that_matches_givenrµ r rW rÚ )r rN rò rÊ rµ rõ r r r rð / s ÿ ÿz*SelectState._join_place_explicit_left_side)NN)NN)r r r Ú __slots__r ræ r: r¸ r¦ r½ rT rÄ rÇ rÉ rÍ rê rà rË râ rç ré rê rÁ rX rÓ rï rð r r r r ré n sH
|
|
/ ü: ýF;Iré c @ s8 e Zd ZU dZded< ded< ded< dd d
|
d Zd S ) Ú_SelectFromElementsr rì r r_ rÕ úTuple[FromClause, ...]r¿ zIterator[FromClause]r c c s t }| jD ]*}|jD ] }||kr$q| |¡ |V qq | jD ]*}|jD ] }||krVqH| |¡ |V qHq>| jD ] }||kr~qp| |¡ |V qpd S r )r8 r r4 r rÕ r¿ )r rÛ r rÙ r r r rö Y s$
|
|
|
|
|
|
|
|
|
|
z*_SelectFromElements._iterate_from_elementsN)r r r r÷ ræ rö r r r r rø R s
|
|
rø c @ s e Zd ZU dZdZdejfdejfdejfgZ de
|
d< de
|
d < d
|
e
|
d< d e
|
d< d e
|
d< e j Z d ddddZedddddZdS )Ú_MemoizedSelectEntitiesa represents partial state from a Select object, for the case
|
where Select.columns() has redefined the set of columns/entities the
|
statement will be SELECTing from. This object represents
|
the entities from the SELECT before that transformation was applied,
|
so that transformations that were made in terms of the SELECT at that
|
time, such as join() as well as options(), can access the correct context.
|
|
In previous SQLAlchemy versions, this wasn't needed because these
|
constructs calculated everything up front, like when you called join()
|
or options(), it did everything to figure out how that would translate
|
into specific SQL constructs that would be ready to send directly to the
|
SQL compiler when needed. But as of
|
1.4, all of that stuff is done in the compilation phase, during the
|
"compile state" portion of the process, so that the work can all be
|
cached. So it needs to be able to resolve joins/options2 based on what
|
the list of entities was when those methods were called.
|
|
|
Zmemoized_select_entitiesr rÁ Ú _with_optionsrS rO zOptional[ClauseElement]rý rì rë z Tuple[ExecutableOption, ...]r r\ r c K s8 | j | j ¡}dd | j ¡ D |_| j d| ¡|_|S )Nc S s i | ]\}}||qS r r )rá ÚkÚvr r r re s z2_MemoizedSelectEntities._clone.<locals>.<dictcomp>rý )r r® r* rå Úgetrý )r ra r& r r r r+ s z _MemoizedSelectEntities._cloner r¨ )Ú select_stmtr c C sP |j s |jrLt }|j|_|j |_ |j|_| j|f7 _g |_d |_ |_d S rû )rÁ rû rú r rÀ )r rÿ r r r r Ú_generate_for_statement s z/_MemoizedSelectEntities._generate_for_statementN)r r r r¯ rÐ rT rø Údp_setup_join_tupleZdp_executable_optionsrO ræ rX Ú
|
EMPTY_DICTZ _annotationsr+ r¦ r r r r r rú q s
|
ý rú c s, e Zd ZU dZdZdZded< dZded< ded < d
|
Zd ed < dZ d ed< dZ
|
ded< dZ ded< dZ d ed< dZ d ed< dZded< dZdZejZded< d ejfdejfdejfdejfdejfdejfdejfdejfdejfdejfd ejfd ejfd ejfd ejfd ejfd ejfdejfd!ej fge j e j e!j" e#j$ e%j& e'j( Z)d"ed#< e)dej*fg Z+d$ed%< d&ed'< e,d(d)d*d+d,Z-d-d.d/d0Z.d1d2d3d4Z/d5d6d7d8d9Z0d:d2d;d<Z1e2re3d=d>d?d@dAZ4e3dBdCd?dDdAZ4e3d>d2dEdAZ4d>d2dFdAZ4d(d6dGdHdIZ5e6d(d2dJdKZ7dLdMdNdOdPZ8e9dèd
|
d
|
dQdRdSd d d6dTdUdVZ:déd
|
dWdXdRdSd d6dYdZd[Z;e9dêd
|
d
|
dQdXdRdSd d d6d\d]d^Z<dëd
|
dWdRdSd d6d_d`daZ=dbd2dcddZ>e6e? @dedf¡dbd2dgdhZAe6did2djdkZBe6dld2dmdnZCdod dpdqdrZDeEfdsd(dtdu fdvdw ZFd(dxd* fdydz ZGe9d-d)d{d|d}ZHd~dtd{ddZIe? @dd¡d-d)ddd
ZJe? Kd¡dìd d)dddZLe3dddddZMe3ddddddZMe3dddddddZMe3ddddddddZMe3dddddddddZMe3ddddddd d¡d¢dZMe3ddddddd£d¤d¥d¦dZMe3ddddddd£d§d¨d© dªdZMe3d
|
d«d-d d(d)d¬ddZMe9d
|
d«d-d d(d)d¬d®dZMe6d¯d2d°d±ZNeNZOe9d5d6d²d³d´ZPe9d5d6dµd¶d·ZQe9d¸d6d¹dºd»ZRe9dXd6d¼d½d¾ZSe9d¿d6dÀdÁdÂZTe9d¿d6dÀdÃdÄZUeVdÅd2dÆdÇZWeVdld2dÈdÉZXd)d2dÊdËZYddÌdÍdÎdtdÏdÐdÑZZd d2dÒdÓZ[dídÔdÕdÖd×dØZ\dÙdÚdÛdÜdÝZ]dÙdÚdÛdÞdßZ^dÙdÚdÛdàdáZ_dÙdÚdÛdâdãZ`dÙdÚdÛdädåZadÙdÚdÛdædçZb ZcS )îr a Represents a ``SELECT`` statement.
|
|
The :class:`_sql.Select` object is normally constructed using the
|
:func:`_sql.select` function. See that function for details.
|
|
.. seealso::
|
|
:func:`_sql.select`
|
|
:ref:`tutorial_selecting_data` - in the 2.0 tutorial
|
|
r r rë rÁ zTuple[TODO_Any, ...]rÀ rì r Fr£ Ú _distinctr_ Ú _distinct_onrù rá Nz Optional[Tuple[FromClause, ...]]rà rÕ Ú_having_criteriar¿ Tr: Ú_compile_optionsr` ra rc rd re rf rg ræ rS rO rv r¹ rº Ú_compile_state_factoryr r r c K s t t ¡}|j |¡ |S )z±Create a :class:`.Select` using raw ``__new__`` with no coercions.
|
|
Used internally to build up :class:`.Select` constructs with
|
pre-established state.
|
|
)r r® r* r[ )r ra r r r r Ú_create_raw_selectû s
|
zSelect._create_raw_selectz_ColumnsClauseArgument[Any])Úentitiesc s" fdd|D _ t ¡ dS )z£Construct a new :class:`_expression.Select`.
|
|
The public constructor for :class:`_expression.Select` is the
|
:func:`_sql.select` function.
|
|
c s g | ]}t jtj| d qS ©r± r+ ©rá Úentr r r rã s ý ÿz#Select.__init__.<locals>.<listcomp>N)r r^ rT ©r r r r r rT s
|
üzSelect.__init__rN r c C s( | j s
|
tS | j d }t|j}|d jS r§ )r rR r r6 r8 )r r- rè r r r rO s
|
|
|
zSelect._scalar_typeú _ColumnExpressionArgument[bool]r\ )Úcriteriar c G s
|
| j | S )z3A synonym for the :meth:`_sql.Select.where` method.©Úwhere)r r r r r Úfilter s z Select.filterzFUnion[FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause]c C s@ | j r&t | ¡j}|| }|d k r&|S | jr6| jd S | jd S r§ )rÁ ré r½ ré r¿ r )r ÚmethZ_last_joined_entityr r r Ú_filter_by_zero$ s ÿ
|
zSelect._filter_by_zeroz Select[Tuple[_MAYBE_ENTITY]]rþ )r r c C s d S r r r r r r rÿ 8 s zSelect.scalar_subqueryzSelect[Tuple[_NOT_ENTITY]]zScalarSelect[_NOT_ENTITY]c C s d S r r r r r r rÿ > s c C s d S r r r r r r rÿ D s c C s d S r r r r r r rÿ H s )Úkwargsr c s( | ¡ fdd| ¡ D }| j| S )zWapply the given filtering criterion as a WHERE clause
|
to this select.
|
|
c s g | ]\}}t ||kqS r r/ )rá r3 rv ©Z from_entityr r rã R s ÿz$Select.filter_by.<locals>.<listcomp>)r rå r )r r r r r r Ú filter_byK s
|
|
þzSelect.filter_byc C s t | ¡j}|| S )a5 Return a :term:`plugin-enabled` 'column descriptions' structure
|
referring to the columns which are SELECTed by this statement.
|
|
This attribute is generally useful when using the ORM, as an
|
extended structure which includes information about mapped
|
entities is returned. The section :ref:`queryguide_inspection`
|
contains more background.
|
|
For a Core-only statement, the structure returned by this accessor
|
is derived from the same objects that are returned by the
|
:attr:`.Select.selected_columns` accessor, formatted as a list of
|
dictionaries which contain the keys ``name``, ``type`` and ``expr``,
|
which indicate the column expressions to be selected::
|
|
>>> stmt = select(user_table)
|
>>> stmt.column_descriptions
|
[
|
{
|
'name': 'id',
|
'type': Integer(),
|
'expr': Column('id', Integer(), ...)},
|
{
|
'name': 'name',
|
'type': String(length=30),
|
'expr': Column('name', String(length=30), ...)}
|
]
|
|
.. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`
|
attribute returns a structure for a Core-only set of entities,
|
not just ORM-only entities.
|
|
.. seealso::
|
|
:attr:`.UpdateBase.entity_description` - entity information for
|
an :func:`.insert`, :func:`.update`, or :func:`.delete`
|
|
:ref:`queryguide_inspection` - ORM background
|
|
)ré r½ rÇ ©r r r r r Úcolumn_descriptionsX s ) zSelect.column_descriptionsrÈ r´ r» c C s t | ¡j}|| |S )a Apply the columns which this :class:`.Select` would select
|
onto another statement.
|
|
This operation is :term:`plugin-specific` and will raise a not
|
supported exception if this :class:`_sql.Select` does not select from
|
plugin-enabled entities.
|
|
|
The statement is typically either a :func:`_expression.text` or
|
:func:`_expression.select` construct, and should return the set of
|
columns appropriate to the entities represented by this
|
:class:`.Select`.
|
|
.. seealso::
|
|
:ref:`orm_queryguide_selecting_text` - usage examples in the
|
ORM Querying Guide
|
|
)ré r½ rÉ )r r r r r r rÉ s zSelect.from_statementr rb rP )Útargetr r r r c C sP t jtj|| d}|dk r*t tj|¡}nd}| j||d||dff7 _| S )ah Create a SQL JOIN against this :class:`_expression.Select`
|
object's criterion
|
and apply generatively, returning the newly resulting
|
:class:`_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
|
|
.. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates
|
a :class:`_sql.Join` object between a :class:`_sql.FromClause`
|
source that is within the FROM clause of the existing SELECT,
|
and a given target :class:`_sql.FromClause`, and then adds
|
this :class:`_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
|
:class:`_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 :meth:`_orm.Query.join` method in the
|
ORM, in order to support the functionality of :class:`_orm.Query`
|
being available by using a :class:`_sql.Select` object with an
|
:class:`_orm.Session`.
|
|
See the notes for this change at :ref:`change_select_join`.
|
|
|
:param target: target table to join towards
|
|
:param onclause: ON clause of the join. If omitted, an ON clause
|
is generated automatically based on the :class:`_schema.ForeignKey`
|
linkages between the two tables, if one can be unambiguously
|
determined, otherwise an error is raised.
|
|
:param isouter: if True, generate LEFT OUTER join. Same as
|
:meth:`_expression.Select.outerjoin`.
|
|
:param full: if True, generate FULL OUTER join.
|
|
.. seealso::
|
|
:ref:`tutorial_select_join` - in the :doc:`/tutorial/index`
|
|
:ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`
|
|
:meth:`_expression.Select.join_from`
|
|
:meth:`_expression.Select.outerjoin`
|
|
r± Nr )r rß r ÚJoinTargetRolerS rÁ )r r r r r Ú join_targetÚonclause_elementr r r r
|
s B ÿüÿz Select.join)r ra )Úfrom_r r r r c C s | j |||d|dS )a Create a SQL LEFT OUTER JOIN against this
|
:class:`_expression.Select` object's criterion and apply generatively,
|
returning the newly resulting :class:`_expression.Select`.
|
|
Usage is the same as that of :meth:`_selectable.Select.join_from`.
|
|
T©r r r )Ú join_from)r r r r r r r r Úouterjoin_fromñ s ÿzSelect.outerjoin_from)r r r r r r c C sb t jtj|| d}t jtj|| d}|dk r<t tj|¡}nd}| j|||||dff7 _| S )a Create a SQL JOIN against this :class:`_expression.Select`
|
object's criterion
|
and apply generatively, returning the newly resulting
|
:class:`_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
|
|
.. versionadded:: 1.4
|
|
:param from\_: the left side of the join, will be rendered in the
|
FROM clause and is roughly equivalent to using the
|
:meth:`.Select.select_from` method.
|
|
:param target: target table to join towards
|
|
:param onclause: ON clause of the join.
|
|
:param isouter: if True, generate LEFT OUTER join. Same as
|
:meth:`_expression.Select.outerjoin`.
|
|
:param full: if True, generate FULL OUTER join.
|
|
.. seealso::
|
|
:ref:`tutorial_select_join` - in the :doc:`/tutorial/index`
|
|
:ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`
|
|
:meth:`_expression.Select.join`
|
|
r± Nr )r rß r rù r rS rÁ )r r r r r r r r r r r r s* 7 ÿ ÿüÿzSelect.join_from)r r r r c C s | j ||d|dS )añ Create a left outer join.
|
|
Parameters are the same as that of :meth:`_expression.Select.join`.
|
|
.. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now
|
creates a :class:`_sql.Join` object between a
|
:class:`_sql.FromClause` source that is within the FROM clause of
|
the existing SELECT, and a given target :class:`_sql.FromClause`,
|
and then adds this :class:`_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
|
:class:`_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 :meth:`_orm.Query.join` method in the
|
ORM, in order to support the functionality of :class:`_orm.Query`
|
being available by using a :class:`_sql.Select` object with an
|
:class:`_orm.Session`.
|
|
See the notes for this change at :ref:`change_select_join`.
|
|
.. seealso::
|
|
:ref:`tutorial_select_join` - in the :doc:`/tutorial/index`
|
|
:ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`
|
|
:meth:`_expression.Select.join`
|
|
Tr )r
|
)r r r r r r r r P s )zSelect.outerjoinzSequence[FromClause]c C s | | d¡ ¡ S )aG Compute the final displayed list of :class:`_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
|
:meth:`_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 :class:`_sql.Select` originally, use the
|
:attr:`_sql.Select.columns_clause_froms` accessor.
|
|
To select from an alternative set of columns while maintaining the
|
FROM list, use the :meth:`_sql.Select.with_only_columns` method and
|
pass the
|
:paramref:`_sql.Select.with_only_columns.maintain_column_froms`
|
parameter.
|
|
.. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`
|
method replaces the previous :attr:`_sql.Select.froms` accessor,
|
which is deprecated.
|
|
.. seealso::
|
|
:attr:`_sql.Select.columns_clause_froms`
|
|
N)r râ r r r r Úget_final_froms{ s &zSelect.get_final_fromsz1.4.23zqThe :attr:`_expression.Select.froms` attribute is moved to the :meth:`_expression.Select.get_final_froms` method.c C s | ¡ S )zYReturn the displayed list of :class:`_expression.FromClause`
|
elements.
|
|
|
)r" r r r r r¶ £ s z Select.fromsr c C s t | ¡ | ¡S )a¡ Return the set of :class:`_expression.FromClause` objects implied
|
by the columns clause of this SELECT statement.
|
|
.. versionadded:: 1.4.23
|
|
.. seealso::
|
|
:attr:`_sql.Select.froms` - "final" FROM list taking the full
|
statement into account
|
|
:meth:`_sql.Select.with_only_columns` - makes use of this
|
collection to set up a new FROM list
|
|
)ré r½ rÍ r r r r Úcolumns_clause_froms± s
|
ÿzSelect.columns_clause_fromsr c C s
|
t | jS )a0 An iterator of all :class:`_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
|
:attr:`_expression.Select.exported_columns` collection.
|
|
)Úiterr¡ r r r r Ú inner_columnsÆ s zSelect.inner_columnsr¢ r¤ c C s8 |d k r| |j krdS | ¡ D ]}| |¡r dS q dS r¨ )r rö r§ )r r¥ rc r r r r§ Ó s
|
zSelect.is_derived_fromr r¨ r_ c sÊ t t t| j t| j tdd | jD ¡} fdd|D fdd| jD }dd ¡ D |¡ |¡}t
|
|t
|
| | _dd d
|
d fd d }|d< t j f dd | ¡ d S )Nc S s g | ] }|d qS )r r r, r r r rã ë s z*Select._copy_internals.<locals>.<listcomp>c s i | ]}| |fqS r r rb rd r r re õ s z*Select._copy_internals.<locals>.<dictcomp>c s g | ]} |fqS r r rb rd r r rã ø s c S s h | ]}t |tr|qS r )rk r rb r r r r ú s
|
z)Select._copy_internals.<locals>.<setcomp>rf r rg rh c s, t | tr(| j kr( | j | ¡}|S d S r rj rm rn r r rp s z'Select._copy_internals.<locals>.replacerp )r¿ )r` Ú
|
omit_attrs)r8 r\ r] r4 r rÕ rÁ r¿ r Ú
|
differencerä rq rr rs )r r` ra rt Zexisting_from_objZ add_fromsrp ru rv r rr Ü s( ýÿÿþÿ zSelect._copy_internalszIterable[ClauseElement]c s" t t jf ddi|| ¡ ¡S )Nr& )r¿ rá rà )r\ r] rq Ú get_childrenrö r ru r r r( s
|
ÿþûzSelect.get_children)r r c s& ¡ j fdd|D _ S )aú Return a new :func:`_expression.select` construct with
|
the given entities appended to its columns clause.
|
|
E.g.::
|
|
my_select = my_select.add_columns(table.c.new_column)
|
|
The original expressions in the columns clause remain in place.
|
To replace the original expressions with new ones, see the method
|
:meth:`_expression.Select.with_only_columns`.
|
|
:param \*entities: column, table, or other entity expressions to be
|
added to the columns clause
|
|
.. seealso::
|
|
:meth:`_expression.Select.with_only_columns` - replaces existing
|
expressions rather than appending.
|
|
:ref:`orm_queryguide_select_multiple_entities` - ORM-centric
|
example
|
|
c s g | ]}t jtj| d qS r
|
r+ )rá r« r r r rã < s ý ÿz&Select.add_columns.<locals>.<listcomp>)rs r r r r r Ú add_columns s
|
ü
|
zSelect.add_columnsz%Iterable[_ColumnsClauseArgument[Any]]c s fddt |¡D _d S )Nc s g | ]}t jtj| d qS r
|
r+ r r r r rã G s ý ÿz(Select._set_entities.<locals>.<listcomp>)rX r. r r r r r Ú _set_entitiesD s
|
üzSelect._set_entitiesrà zThe :meth:`_expression.Select.column` method is deprecated and will be removed in a future release. Please use :meth:`_expression.Select.add_columns`rª c C s
|
| |¡S )a Return a new :func:`_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
|
:meth:`_expression.Select.with_only_columns`
|
for guidelines on adding /replacing the columns of a
|
:class:`_expression.Select` object.
|
|
)r) r¬ r r r r« N s z Select.columnrÅ )Ú only_synonymsr c C s. | j tjjj| jf| j| j d|i }|S )a" Return a new :func:`_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
|
:meth:`_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.
|
|
:param 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.
|
|
r+ )Úwith_only_columnsrX rÉ rÊ rZ r¡ rÕ r¿ )r r+ Zwocr r r rZ d s ÿ
|
ýþÿzSelect.reduce_columnsz
|
_TCCA[_T0]zSelect[Tuple[_T0]])Ú _Select__ent0r c C s d S r r )r r- r r r r, s úSelect.with_only_columnsz
|
_TCCA[_T1]zSelect[Tuple[_T0, _T1]])r- Ú _Select__ent1r c C s d S r r )r r- r/ r r r r, s z
|
_TCCA[_T2]z Select[Tuple[_T0, _T1, _T2]])r- r/ Ú _Select__ent2r c C s d S r r )r r- r/ r0 r r r r, s z
|
_TCCA[_T3]z!Select[Tuple[_T0, _T1, _T2, _T3]])r- r/ r0 Ú _Select__ent3r c C s d S r r )r r- r/ r0 r1 r r r r, s z
|
_TCCA[_T4]z&Select[Tuple[_T0, _T1, _T2, _T3, _T4]])r- r/ r0 r1 Ú _Select__ent4r c C s d S r r )r r- r/ r0 r1 r2 r r r r, ¥ s z
|
_TCCA[_T5]z+Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]])r- r/ r0 r1 r2 Ú _Select__ent5r c C s d S r r )r r- r/ r0 r1 r2 r3 r r r r, ° s
|
z
|
_TCCA[_T6]z0Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]])r- r/ r0 r1 r2 r3 Ú _Select__ent6r c C s d S r r )r r- r/ r0 r1 r2 r3 r4 r r r r, ¼ s z
|
_TCCA[_T7]z5Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]) r- r/ r0 r1 r2 r3 r4 Ú _Select__ent7r c C s d S r r ) r r- r/ r0 r1 r2 r3 r4 r5 r r r r, É s )Úmaintain_column_froms)r r6 Ú _Select__kwr c O s d S r r ©r r6 r r7 r r r r, Ù s c O sR |r
|
t | ¡ |r*| jj| f| j t | ¡ dd t dd|¡D | _ | S )aÊ Return a new :func:`_expression.select` construct with its columns
|
clause replaced with the given entities.
|
|
By default, this method is exactly equivalent to as if the original
|
:func:`_expression.select` had been called with the given entities.
|
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, :meth:`_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 :meth:`_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 :attr:`_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)
|
|
:param \*entities: column expressions to be used.
|
|
:param maintain_column_froms: boolean parameter that will ensure the
|
FROM list implied from the current columns clause will be transferred
|
to the :meth:`_sql.Select.select_from` method first.
|
|
.. versionadded:: 1.4.23
|
|
c S s g | ]}t tj|¡qS r r+ r5 r r r rã * s ÿz,Select.with_only_columns.<locals>.<listcomp>r r. )
|
r! Z_assert_no_memoizationsr Znon_generativer# rú r r Z!_expression_collection_was_a_listr r8 r r r r, â s$ 8ÿÿ
|
ÿþrb c C s t | j¡S )a Return the completed WHERE clause for this
|
:class:`_expression.Select` statement.
|
|
This assembles the current collection of WHERE criteria
|
into a single :class:`_expression.BooleanClauseList` construct.
|
|
|
.. versionadded:: 1.4
|
|
)rG Z_construct_for_whereclauserÕ r r r r Ú whereclause2 s ÿzSelect.whereclause)r9 r c G s< t | jtst|D ]"}t tj|¡}| j|f7 _q| S )z¯Return a new :func:`_expression.select` construct with
|
the given expression added to
|
its WHERE clause, joined to the existing clause via AND, if any.
|
|
)rk rÕ rä r7 r rß r ÚWhereHavingRole)r r9 Ú criterionZwhere_criteriar r r r E s ÿz Select.where)Úhavingr c G s, |D ]"}t tj|¡}| j|f7 _q| S )z°Return a new :func:`_expression.select` construct with
|
the given expression added to
|
its HAVING clause, joined to the existing clause via AND, if any.
|
|
)r rß r r: r )r r< r; Zhaving_criteriar r r r< V s ÿz Select.havingr )rÅ r c G s0 |r&d| _ | jtdd |D | _nd| _ | S )a± Return a new :func:`_expression.select` construct which
|
will apply DISTINCT to its columns clause.
|
|
:param \*expr: optional column expressions. When present,
|
the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>>)``
|
construct.
|
|
.. deprecated:: 1.4 Using \*expr in other dialects is deprecated
|
and will raise :class:`_exc.CompileError` in a future version.
|
|
Tc s s | ]}t tj|¡V qd S r )r rß r ZByOfRole)rá Úer r r r$ t s z"Select.distinct.<locals>.<genexpr>)r r rä )r rÅ r r r Údistincte s ÿzSelect.distinct©r¶ r c s$ j t fdd|D 7 _ S )a Return a new :func:`_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 :class:`_schema.Table`
|
or other selectable
|
will have no effect. Passing a :class:`_expression.Join` that refers
|
to an already present :class:`_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 :meth:`_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)
|
|
c 3 s | ]}t jtj| d V qdS )r± N©r rß r rù )rá r¥ r r r r$ s ý ÿz%Select.select_from.<locals>.<genexpr>)r¿ rä )r r¶ r r r r { s $ü zSelect.select_fromú2Union[Literal[(None, False)], _FromClauseArgument]©Ú fromclausesr c G sR d| _ |r|d dkr4t|dkr,t d¡d| _n| jtdd |D | _| S ) a Return a new :class:`_expression.Select`
|
which will correlate the given FROM
|
clauses to that of an enclosing :class:`_expression.Select`.
|
|
Calling this method turns off the :class:`_expression.Select` object's
|
default behavior of "auto-correlation". Normally, FROM elements
|
which appear in a :class:`_expression.Select`
|
that encloses this one via
|
its :term:`WHERE clause`, ORDER BY, HAVING or
|
:term:`columns clause` will be omitted from this
|
:class:`_expression.Select`
|
object's :term:`FROM clause`.
|
Setting an explicit correlation collection using the
|
:meth:`_expression.Select.correlate`
|
method provides a fixed list of FROM objects
|
that can potentially take place in this process.
|
|
When :meth:`_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
|
:class:`_expression.Select`
|
object is, relative to an enclosing :class:`_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
|
:class:`_expression.Select`.
|
Multi-level correlation ensures that the link
|
between enclosed and enclosing :class:`_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 :class:`_expression.Select`
|
object will correlate
|
none of its FROM entries, and all will render unconditionally
|
in the local FROM clause.
|
|
:param \*fromclauses: one or more :class:`.FromClause` or other
|
FROM-compatible construct such as an ORM mapped entity to become part
|
of the correlate collection; alternatively pass a single value
|
``None`` to remove all existing correlations.
|
|
.. seealso::
|
|
:meth:`_expression.Select.correlate_except`
|
|
:ref:`tutorial_scalar_subquery`
|
|
Fr ¾ NFr zKadditional FROM objects not accepted when passing None/False to correlate()r c s s | ]}t tj|¡V qd S r r@ rb r r r r$ ë s z#Select.correlate.<locals>.<genexpr>)r´ r rW r rá rä ©r rC r r r r¢ § s ; ÿ ÿ zSelect.correlatec G sV d| _ |r|d dkr4t|dkr,t d¡d| _n | jp<dtdd |D | _| S ) aÿ Return a new :class:`_expression.Select`
|
which will omit the given FROM
|
clauses from the auto-correlation process.
|
|
Calling :meth:`_expression.Select.correlate_except` turns off the
|
:class:`_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, or no arguments are passed,
|
the :class:`_expression.Select` object will correlate all of its
|
FROM entries.
|
|
:param \*fromclauses: a list of one or more
|
:class:`_expression.FromClause`
|
constructs, or other compatible constructs (i.e. ORM-mapped
|
classes) to become part of the correlate-exception collection.
|
|
.. seealso::
|
|
:meth:`_expression.Select.correlate`
|
|
:ref:`tutorial_scalar_subquery`
|
|
Fr rD r zRadditional FROM objects not accepted when passing None/False to correlate_except()r c s s | ]}t tj|¡V qd S r r@ rb r r r r$ s z*Select.correlate_except.<locals>.<genexpr>)r´ r rW r rà rä rE r r r Úcorrelate_exceptð s ! ÿÿ zSelect.correlate_exceptrA c s2 t dt | j¡ t fdd| jD }| ¡ S )a- A :class:`_expression.ColumnCollection`
|
representing the columns that
|
this SELECT statement or similar construct returns in its result set,
|
not including :class:`_sql.TextClause` constructs.
|
|
This collection differs from the :attr:`_expression.FromClause.columns`
|
collection of a :class:`_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 :func:`_expression.select` construct, the collection here is
|
exactly what would be rendered inside the "SELECT" statement, and the
|
:class:`_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 :class:`_schema.Table` or other
|
:class:`_expression.FromClause`, the collection will use the
|
:class:`_expression.ColumnElement` objects that are in the
|
:attr:`_expression.FromClause.c` collection of the from element.
|
|
.. note::
|
|
The :attr:`_sql.Select.selected_columns` collection does not
|
include expressions established in the columns clause using the
|
:func:`_sql.text` construct; these are silently omitted from the
|
collection. To use plain textual column expressions inside of a
|
:class:`_sql.Select` construct, use the :func:`_sql.literal_column`
|
construct.
|
|
|
.. versionadded:: 1.4
|
|
zCallable[[Any], str]c s g | ]}t |r ||fqS r r# r5 ©Úconvr r rã W s þz+Select.selected_columns.<locals>.<listcomp>)r ré rê ræ r; r¡ r- )r Úccr rG r rB s 1
|
þ
|
þÿzSelect.selected_columnsc C s t | ¡j}t|| S r )ré r½ rê r r r r r r¡ _ s z Select._all_selected_columnsc C s | j tkr| t¡} | S r )ræ rJ r¡ rL r r r r rT d s
|
|
z"Select._ensure_disambiguated_namesrC r
rE rF c sP |r(|} fddt | d¡|D }n fdd| d¡D } j |¡ dS )zYGenerate column proxies to place in the exported ``.c``
|
collection of a subquery.c s6 g | ].\\}}}}}}t |r|j ||d |dqS )T)r3 r¼ Úname_is_truncatableÚcompound_select_cols©r$ r )rá rà rá râ r& rä Ú
|
extra_colsrM r r rã v s êûz>Select._generate_fromclause_column_proxies.<locals>.<listcomp>Fc s0 g | ](\}}}}}t |r|j ||d dqS )T)r3 r¼ rJ rL )rá rà rá râ r& rä rM r r rã s óüN)r¯ rï rþ r% )r rû rD r± Zproxr rM r r© i s
|
þî
|
óz*Select._generate_fromclause_column_proxiesc C s | j pt| jjS r )r~ r£ rm r r r r r Ú_needs_parens_for_grouping¤ s ÿz!Select._needs_parens_for_groupingr= z*Union[SelectStatementGrouping[Self], Self]r> c C s" t |tr| ¡ s| S t| S d S r )rk r rN rW r@ r r r rA © s ÿþzSelect.self_grouprg r r c G s t j| f| S )aÀ Return a SQL ``UNION`` of this select() construct against
|
the given selectables provided as positional arguments.
|
|
:param \*other: one or more elements with which to create a
|
UNION.
|
|
.. versionchanged:: 1.4.28
|
|
multiple elements are now accepted.
|
|
:param \**kwargs: keyword arguments are forwarded to the constructor
|
for the newly created :class:`_sql.CompoundSelect` object.
|
|
)r r¡ r r r r rø ½ s z Select.unionc G s t j| f| S )aÄ Return a SQL ``UNION ALL`` of this select() construct against
|
the given selectables provided as positional arguments.
|
|
:param \*other: one or more elements with which to create a
|
UNION.
|
|
.. versionchanged:: 1.4.28
|
|
multiple elements are now accepted.
|
|
:param \**kwargs: keyword arguments are forwarded to the constructor
|
for the newly created :class:`_sql.CompoundSelect` object.
|
|
)r r¢ r r r r rÜ Ð s zSelect.union_allc G s t j| f| S )a. Return a SQL ``EXCEPT`` of this select() construct against
|
the given selectable provided as positional arguments.
|
|
:param \*other: one or more elements with which to create a
|
UNION.
|
|
.. versionchanged:: 1.4.28
|
|
multiple elements are now accepted.
|
|
)r r£ r r r r Úexcept_ã s zSelect.except_c G s t j| f| S )a3 Return a SQL ``EXCEPT ALL`` of this select() construct against
|
the given selectables provided as positional arguments.
|
|
:param \*other: one or more elements with which to create a
|
UNION.
|
|
.. versionchanged:: 1.4.28
|
|
multiple elements are now accepted.
|
|
)r r¤ r r r r Ú
|
except_alló s zSelect.except_allc G s t j| f| S )aÄ Return a SQL ``INTERSECT`` of this select() construct against
|
the given selectables provided as positional arguments.
|
|
:param \*other: one or more elements with which to create a
|
UNION.
|
|
.. versionchanged:: 1.4.28
|
|
multiple elements are now accepted.
|
|
:param \**kwargs: keyword arguments are forwarded to the constructor
|
for the newly created :class:`_sql.CompoundSelect` object.
|
|
)r r¥ r r r r Ú intersect s zSelect.intersectc G s t j| f| S )aÈ Return a SQL ``INTERSECT ALL`` of this select() construct
|
against the given selectables provided as positional arguments.
|
|
:param \*other: one or more elements with which to create a
|
UNION.
|
|
.. versionchanged:: 1.4.28
|
|
multiple elements are now accepted.
|
|
:param \**kwargs: keyword arguments are forwarded to the constructor
|
for the newly created :class:`_sql.CompoundSelect` object.
|
|
)r r¦ r r r r Ú intersect_all s zSelect.intersect_all)N)N)N)N)T)N)dr r r r¯ rÐ rÁ ræ rÀ r r rá rà rÕ r r¿ r´ r² ré r¸ r rT rø Zdp_memoized_select_entitiesZdp_clauseelement_tupler r¤ r² r¥ rù rØ rò rÔ r× rè rê rí rð r* r³ r? Z _executable_traverse_internalsrO Zdp_has_cache_keyr¹ r¦ r rT rO r r r r rÿ r r r rÉ r5 r
|
r! r r r" rX rÒ r¶ r# r% r§ r+ rr r( r) r* r« rÓ rZ r, r9 Z _whereclauser r< r> r r¢ rF rY rB r¡ rT r© rN rA rø rÜ rO rP rQ rR r§ r r ru r r « sT
|
ÿ
|
þëéèçæå äÿ
|
ÿ + ýú W üú üù N ýû+(þ
|
ÿ: $
|
þ&
|
ýýO+H/> ú; ÿr c @ s8 e Zd ZU dZdejfdejfgZded< g Z ded< dZ
|
e sBd Z dZ d
|
ed< d
|
d d d dZdddddZddddZdd dddZeddd d ZeZed d d d!d"Zed6d$d%d$d&d'd(Zed7d)d%d)d&d*d(Zd8d%d)d+d,d(Ze r d-dd.d/Zed0d d1d2d3Zed0d d1d4d5Zd#S )9rR a£ Represent a scalar subquery.
|
|
|
A :class:`_sql.ScalarSelect` is created by invoking the
|
:meth:`_sql.SelectBase.scalar_subquery` method. The object
|
then participates in other SQL expressions as a SQL column expression
|
within the :class:`_sql.ColumnElement` hierarchy.
|
|
.. seealso::
|
|
:meth:`_sql.SelectBase.scalar_subquery`
|
|
:ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
|
|
r r8 rS rO r r4 TFrü r¨ rX c C s || _ | ¡ | _d S r )r rO r8 r r r r rT J s zScalarSelect.__init__rÝ r )Úattrr c C s t | j|S r )r r )r rS r r r Ú __getattr__N s zScalarSelect.__getattr__r" r c C s | j | jdS )N©r r8 rU r r r r r Q s zScalarSelect.__getstate__r c C s |d | _ |d | _d S )Nr r8 rU r r r r r T s
|
zScalarSelect.__setstate__r c C s t d¡d S )NzcScalar Select expression has no columns; use this object directly within a column-level expression.)rW rÚ r r r r r) X s ÿzScalarSelect.columnsr r\ )r r c C s t d| j |¡| _| S )zuApply a WHERE clause to the SELECT statement referred to
|
by this :class:`_expression.ScalarSelect`.
|
|
r )r r r )r r r r r r b s zScalarSelect.whereNrþ r= )r r? r c C s d S r r r@ r r r rA k s zScalarSelect.self_groupr c C s d S r r r@ r r r rA q s r> c C s | S r r r@ r r r rA w s r c C s d S r r r r r r r] s zScalarSelect._ungrouprA rB c G s t d| jj| | _| S )aÓ Return a new :class:`_expression.ScalarSelect`
|
which will correlate the given FROM
|
clauses to that of an enclosing :class:`_expression.Select`.
|
|
This method is mirrored from the :meth:`_sql.Select.correlate` method
|
of the underlying :class:`_sql.Select`. The method applies the
|
:meth:_sql.Select.correlate` method, then returns a new
|
:class:`_sql.ScalarSelect` against that statement.
|
|
.. versionadded:: 1.4 Previously, the
|
:meth:`_sql.ScalarSelect.correlate`
|
method was only available from :class:`_sql.Select`.
|
|
:param \*fromclauses: a list of one or more
|
:class:`_expression.FromClause`
|
constructs, or other compatible constructs (i.e. ORM-mapped
|
classes) to become part of the correlate collection.
|
|
.. seealso::
|
|
:meth:`_expression.ScalarSelect.correlate_except`
|
|
:ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
|
|
|
r )r r r¢ rE r r r r¢ s ÿzScalarSelect.correlatec G s t d| jj| | _| S )aÜ Return a new :class:`_expression.ScalarSelect`
|
which will omit the given FROM
|
clauses from the auto-correlation process.
|
|
This method is mirrored from the
|
:meth:`_sql.Select.correlate_except` method of the underlying
|
:class:`_sql.Select`. The method applies the
|
:meth:_sql.Select.correlate_except` method, then returns a new
|
:class:`_sql.ScalarSelect` against that statement.
|
|
.. versionadded:: 1.4 Previously, the
|
:meth:`_sql.ScalarSelect.correlate_except`
|
method was only available from :class:`_sql.Select`.
|
|
:param \*fromclauses: a list of one or more
|
:class:`_expression.FromClause`
|
constructs, or other compatible constructs (i.e. ORM-mapped
|
classes) to become part of the correlate-exception collection.
|
|
.. seealso::
|
|
:meth:`_expression.ScalarSelect.correlate`
|
|
:ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
|
|
|
r )r r rF rE r r r rF ¦ s ! ÿz ScalarSelect.correlate_except)N)N)N) r r r r¯ rT r¤ rÊ rO ræ r4 rº r Z_is_implicitly_booleanr rT rT r r r r) r& r5 r r rA r] r¢ rF r r r r rR * sB
|
þ ÿ ÿ ÿ#rR c @ s e Zd ZU dZdZded< d%dddd Zejd
|
d d d Z dddddZ
|
dd ddZ dddddZ ddddd Z d dd d d Zd!dd"d#d$ZdS )&rP zãRepresent an ``EXISTS`` clause.
|
|
See :func:`_sql.exists` for a description of usage.
|
|
An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`
|
instance by calling :meth:`_sql.SelectBase.exists`.
|
|
Tz>Union[SelectStatementGrouping[Select[Any]], ScalarSelect[Any]]r NzKOptional[Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]])Ú_Exists__argumentc C sn |d krt td ¡ }n8t|tr6| ¡ }|j|_n t|trF|}n t | ¡ }tj| |t j
|
t j dd d S )NrÙ T)Úoperatorrn Zwraps_column_expression) r rN rÿ rk rü Z_propagate_attrsrR rP rT r rQ r Z BOOLEANTYPE)r rV r r r r rT Ú s
|
|
|
ûzExists.__init__r r c C s g S r r r r r r r4 õ s zExists._from_objectsz$Callable[[Select[Any]], Select[Any]]z$SelectStatementGrouping[Select[Any]])Úfnr c C s2 | j ¡ }||}|jtjd}t|ts.t|S )NrQ )r r] rA r rQ rk rW r7 )r rX r rY Z return_valuer r r Ú_regroupù s
|
|
zExists._regroupr c C s t | S )aº Return a SELECT of this :class:`_expression.Exists`.
|
|
e.g.::
|
|
stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()
|
|
This will produce a statement resembling::
|
|
SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1
|
|
.. seealso::
|
|
:func:`_expression.select` - general purpose
|
method which allows for arbitrary column lists.
|
|
r r r r r r s z Exists.selectrA r\ rB c s | ¡ }| fdd¡|_|S )zApply correlation to the subquery noted by this
|
:class:`_sql.Exists`.
|
|
.. seealso::
|
|
:meth:`_sql.ScalarSelect.correlate`
|
|
c s
|
| j S r )r¢ r· ©rC r r r % r z"Exists.correlate.<locals>.<lambda>©r+ rY r ©r rC r= r rZ r r¢ s
|
|
ÿzExists.correlatec s | ¡ }| fdd¡|_|S )z¥Apply correlation to the subquery noted by this
|
:class:`_sql.Exists`.
|
|
.. seealso::
|
|
:meth:`_sql.ScalarSelect.correlate_except`
|
|
c s
|
| j S r )rF r· rZ r r r 8 r z)Exists.correlate_except.<locals>.<lambda>r[ r\ r rZ r rF ) s
|
|
ÿzExists.correlate_exceptr
r? c s | ¡ }| fdd¡|_|S )aÝ Return a new :class:`_expression.Exists` construct,
|
applying the given
|
expression to the :meth:`_expression.Select.select_from`
|
method of the select
|
statement contained.
|
|
.. note:: it is typically preferable to build a :class:`_sql.Select`
|
statement first, including the desired WHERE clause, then use the
|
:meth:`_sql.SelectBase.exists` method to produce an
|
:class:`_sql.Exists` object at once.
|
|
c s
|
| j S r )r r· ©r¶ r r r J r z$Exists.select_from.<locals>.<lambda>r[ )r r¶ r= r r] r r < s zExists.select_fromr rz c s | ¡ }| fdd¡|_|S )aº Return a new :func:`_expression.exists` construct with the
|
given expression added to
|
its WHERE clause, joined to the existing clause via AND, if any.
|
|
|
.. note:: it is typically preferable to build a :class:`_sql.Select`
|
statement first, including the desired WHERE clause, then use the
|
:meth:`_sql.SelectBase.exists` method to produce an
|
:class:`_sql.Exists` object at once.
|
|
c s
|
| j S r r r· ©rp r r r Z r z Exists.where.<locals>.<lambda>r[ )r rp r= r r^ r r M s z Exists.where)N)r r r r¯ r ræ rT rX r r4 rY r r¢ rF r r r r r r rP Í s
|
ü
|
rP c @ sô e Zd ZU dZdZeZdejfdej fge
|
j Z de d< dZdZdZd0d d
|
d d d ddZd1d dd d d ddZeddddZejddddZdd dd d Zd dd d Zed d!d"d#d$d%Zd&d'd(d)d d*d+d,Z d-dd.d/Z d&S )2Ú TextualSelectaF Wrap a :class:`_expression.TextClause` construct within a
|
:class:`_expression.SelectBase`
|
interface.
|
|
This allows the :class:`_expression.TextClause` object to gain a
|
``.c`` collection
|
and other FROM-like capabilities such as
|
:meth:`_expression.FromClause.alias`,
|
:meth:`_expression.SelectBase.cte`, etc.
|
|
The :class:`_expression.TextualSelect` construct is produced via the
|
:meth:`_expression.TextClause.columns`
|
method - see that method for details.
|
|
.. versionchanged:: 1.4 the :class:`_expression.TextualSelect`
|
class was renamed
|
from ``TextAsFrom``, to more correctly suit its role as a
|
SELECT-oriented object and not a FROM clause.
|
|
.. seealso::
|
|
:func:`_expression.text`
|
|
:meth:`_expression.TextClause.columns` - primary creation interface.
|
|
Ztextual_selectr Ú column_argsrS rO TFr~ z$List[_ColumnExpressionArgument[Any]]r£ r¨ )rñ r) Ú
|
positionalr c C s | |dd |D |¡ d S )Nc S s g | ]}t tj|¡qS r )r rß r ZLabeledColumnExprRoler5 r r r rã s ÿz*TextualSelect.__init__.<locals>.<listcomp>)r¯ ©r rñ r) ra r r r rT s þùzTextualSelect.__init__zList[NamedColumn[Any]]c C s || _ || _|| _d S r )r r` ra rb r r r r¯ s zTextualSelect._initz.ColumnCollection[str, KeyedColumnElement[Any]]r c C s t dd | jD ¡ S )am A :class:`_expression.ColumnCollection`
|
representing the columns that
|
this SELECT statement or similar construct returns in its result set,
|
not including :class:`_sql.TextClause` constructs.
|
|
This collection differs from the :attr:`_expression.FromClause.columns`
|
collection of a :class:`_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 :class:`_expression.TextualSelect` construct, the collection
|
contains the :class:`_expression.ColumnElement` objects that were
|
passed to the constructor, typically via the
|
:meth:`_expression.TextClause.columns` method.
|
|
|
.. versionadded:: 1.4
|
|
c s s | ]}|j |fV qd S r r r5 r r r r$ » s z1TextualSelect.selected_columns.<locals>.<genexpr>)r; r` r- r r r r rB £ s ÿz TextualSelect.selected_columnsr c C s | j S r )r` r r r r r¡ ¿ s z#TextualSelect._all_selected_columnsrH rJ c C s | S r r rL r r r r¡ Ã s z TextualSelect.set_label_stylec C s | S r r r r r r rT Æ s z)TextualSelect._ensure_disambiguated_nameszBindParameter[Any]r r\ )ÚbindsÚbind_as_valuesr c O s | j j||| _ | S r )r Ú
|
bindparams)r rc rd r r r re É s zTextualSelect.bindparamsNrC r
rE )r¥ rD r c sZ t rt tst|r: j fddt| j|D ¡ n j fdd| jD ¡ d S )Nc 3 s | ]\}}|j |d V qdS ))rK Nr )rá r& rM r# r r r$ Þ s ÿzDTextualSelect._generate_fromclause_column_proxies.<locals>.<genexpr>c 3 s | ]}| ¡V qd S r r r5 r# r r r$ å s )r rk rú r7 rþ r% r¯ r` )r r¥ rD r r# r r© Ò s ÿþ
|
ÿz1TextualSelect._generate_fromclause_column_proxieszUnion[TypeEngine[Any], Any]c C s | j d jS r§ )r` r8 r r r r rO é s zTextualSelect._scalar_type)F)F) r r r r¯ rÐ rJ ræ rT r¤ rø r* r³ rO ræ Z _is_textualZis_textrU rT r¯ rY rB rX r r¡ r¡ rT r5 re r© rO r r r r r_ ^ s4
|
þý ü ü
|
úr_ c s8 e Zd Zddd fdd Zejdddd Z ZS )
|
ÚAnnotatedFromClauser r¨ r c s4 t jf | | dd¡r0| j}|jj | ¡| _d S )NZind_cols_on_fromclauseF)rq rr rþ Ú_Annotated__elementr r& Úfget)r ra Úeeru r r rr ò s z#AnnotatedFromClause._copy_internalsr' r c C s | j }|jS )ao proxy the .c collection of the underlying FromClause.
|
|
Originally implemented in 2008 as a simple load of the .c collection
|
when the annotated construct was created (see d3621ae961a), in modern
|
SQLAlchemy versions this can be expensive for statements constructed
|
with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy
|
it, which works just as well.
|
|
Two different use cases seem to require the collection either copied
|
from the underlying one, or unique to this AnnotatedFromClause.
|
|
See test_selectable->test_annotated_corresponding_column
|
|
)rg r& )r ri r r r r& ù s zAnnotatedFromClause.c)r r r rr rX rD r& r§ r r ru r rf ñ s rf )âr¯ Ú
|
__future__r r Úenumr r\ Útypingr r r r r r r r
|
r r r r r r r r r r r r r r} r r r r r r r Ú_typingr r! r" r$ r% r& r' r( Ú
|
annotationr) r* Úbaser+ r, r. r0 r1 r2 r4 r5 r7 r8 r9 r: r; r< r= r> r? r@ rA rB rC rD r rE rF rG rH rI rJ rK rL rM rN rO rP rQ ZsqltypesrR rS rT rU rW rX rY Z util.typingrZ r[ r\ r
r] r_ r` ra rb rc rd re rf rg rh ri rj rk rl rm rn ro rp rq Z_TCCArr rs rt ru rv r¾ rw Zdmlrx ry rz r{ r| r} r~ rÐ r rÿ r r r r r Z_ColumnsClauseElementrÝ rÎ r ró Z_OnClauseElementZ_ForUpdateOfArgumentZ_SetupJoinsElementr r' r r³ r r´ r· r¸ rÔ rè rí ZAnonymizedFromClauseRoler
r rH r rJ rK rL rÆ rM Z DMLTableRoler r¨ rº r¬ r½ rÆ rÀ r¿ r rõ rÒ rÞ rß rå rÙ rØ rú rW r r r£ r r Z InElementRoler0 r= rý Z DMLSelectRoler rü rV rW r^ Z
|
plugin_forr r r r- Úsetattrr¼ Z MemoizedSlotsré rø Z HasCacheKeyZHasCopyInternalsZ Traversiblerú r rR rP r_ Z
|
TextAsFromrf r r r r Ú<module> s. ÿ
|
ÿûþ
|
ýÿ TP4/c 0,cû #\$ 8!:
|
ú 5! 0
|
=;
|
"E (
|
ù E _ p
|
L
|
f
|
ÿ:
|
ù
|
|
ÿ $
|
