U
|
¸ý°dåÛ ã @ sê U d Z ddlmZ ddlZddlZddlmZ ddlmZ ddlmZ ddlm Z ddlm
|
Z
|
dd lm Z dd
|
lm Z dd lm Z dd lmZ dd lmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddl m Z ddl m 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*l0m1Z1 dd+l0m2Z2 dd,l0m3Z3 dd-lm4Z4 dd.lm5Z5 dd/l6m7Z7 d0dlmZ ertdd1lm8Z8 d0d2l9m:Z: d0d3l;m<Z< d0d4l=m>Z> d0d5l=m?Z? ed6ed7Z@e AeB¡ZCd8eDd9< dd;d<ZEG d=d> d>e e(e@ e1e ZFG d?d@ d@e$e ZGG dAdB dBe-e@ ZHG dCdD dDZIeI ZJeIj eJ_ eIdEdFZKG dGdH dHeFe@ ZLG dIdJ dJeLe@ ZMeEdKe& eEdLe) G dMdN dNeMeN ZOG dOdP dPeMe@ ZPG dQdR dReMe@ ZQG dSdT dTeQe@ ZRG dUdV dVeQe@ ZSG dWdX dXeQe@ ZTG dYdZ dZeQe@ ZUG d[d\ d\eMej ZVG d]d^ d^eMeW ZXG d_d` d`eMeN ZYG dadb dbeMeZ Z[G dcdd ddeMeN Z\G dedf dfePej] Z^G dgdh dhePej_ Z`G didj djePej ZaG dkdl dlePeW ZbG dmdn dnePej ZcG dodp dpePej ZdG dqdr drePeW ZeG dsdt dtePej ZfG dudv dvePeW ZgG dwdx dxeMe@ ZhG dydz dzeMe@ ZiG d{d| d|eie@ ZjG d}d~ d~eie@ ZkG dd deie@ ZlG dd deMeN ZmG dd deMeN ZnG d
d deMejo ZpG dd deMejo ZqG dd deMe@ ZrG dd deMe@ ZsG dd deMe@ ZtdS )z6SQL function API, factories, and built-in functions.
|
|
é )Ú annotationsN)ÚAny)Úcast)ÚDict)ÚMapping)ÚOptional)Úoverload)ÚTuple)ÚType)Ú TYPE_CHECKING)ÚTypeVaré )Ú
|
annotation)Ú coercions)Ú operators)Úroles)Úschema)Úsqltypes)Útype_api)Úutil)Úis_table_value_type)Ú_entity_namespace)ÚColumnCollection)Ú
|
Executable)Ú
|
Generative)Ú HasMemoized)Ú_type_from_args)ÚBinaryExpression)Ú BindParameter)ÚCast)Ú
|
ClauseList)Ú ColumnElement)ÚExtract©ÚFunctionFilter)ÚGrouping)Úliteral_column)Ú NamedColumn©ÚOver©Ú WithinGroup)Ú
|
FromClause)ÚSelect)ÚTableValuedAlias)ÚTableValueType)Ú
|
TypeEngine)ÚInternalTraversalé )Ú_TypeEngineArgument)Ú
|
Connection)Ú CursorResult)Ú_CoreMultiExecuteParams)ÚCoreExecuteOptionsParameterÚ_T)Úboundz5util.defaultdict[str, Dict[str, Type[Function[Any]]]]Ú _registryÚ_defaultc C s8 t | }t| ¡ } | |kr,t d | ¡¡ ||| < dS )zþAssociate a callable with a particular func. name.
|
|
This is normally called by GenericFunction, but is also
|
available by itself so that a non-Function construct
|
can be associated with the :data:`.func` accessor (i.e.
|
CAST, EXTRACT).
|
|
zMThe GenericFunction '{}' is already registered and is going to be overridden.N)r: ÚstrÚlowerr ÚwarnÚformat)Ú
|
identifierÚfnÚpackageÚreg© rD úOd:\z\workplace\vscode\pyvenv\venv\Lib\site-packages\sqlalchemy/sql/functions.pyÚregister_functionN s ÿÿrF c sv e Zd ZU dZdejfdejfdejfgZdZ de
|
d< dZ dZ d Z d
|
e
|
d< d e
|
d < d e
|
d < de
|
d< d dddZd Ze fddZdddddddZdDdd Zd d ZdEd d Zejd!d" Zejd#d$ Zed%d& Zed'd( Z e j d)d*d+d,Z dFd-d.Z d/d0 Z!d1d2 Z"d3d4 Z#ed5d6 Z$d7d8 Z%dGd9d:Z&d;d*d<d=Z'dHd>d?Z(dI fd@dA Z)edBdC Z* Z+S )JÚFunctionElementa Base for SQL function-oriented constructs.
|
|
This is a `generic type <https://peps.python.org/pep-0484/#generics>`_,
|
meaning that type checkers and IDEs can be instructed on the types to
|
expect in a :class:`_engine.Result` for this function. See
|
:class:`.GenericFunction` for an example of how this is done.
|
|
.. seealso::
|
|
:ref:`tutorial_functions` - in the :ref:`unified_tutorial`
|
|
:class:`.Function` - named SQL function.
|
|
:data:`.func` - namespace which produces registered or ad-hoc
|
:class:`.Function` instances.
|
|
:class:`.GenericFunction` - allows creation of registered function
|
types.
|
|
Ú clause_exprÚ_with_ordinalityÚ_table_value_typerD zTuple[str, ...]Ú packagenamesFNzOptional[TableValueType]r Z primary_keyZ _is_clone_ofz Grouping[Any])Úclausesc s> fdd|D } j p t| _ tt|tjdd _dS )aO Construct a :class:`.FunctionElement`.
|
|
:param \*clauses: list of column expressions that form the arguments
|
of the SQL function call.
|
|
:param \**kwargs: additional kwargs are typically consumed by
|
subclasses.
|
|
.. seealso::
|
|
:data:`.func`
|
|
:class:`.Function`
|
|
c s( g | ] }t jtj|t d d dqS )ÚnameN©rM Úapply_propagate_attrs)r Úexpectr ÚExpressionElementRoleÚgetattr©Ú.0Úc©ÚselfrD rE Ú
|
<listcomp> s ú
|
üz,FunctionElement.__init__.<locals>.<listcomp>T©ÚoperatorZgroup_contentsN)Ú _has_argsÚboolr% r r Úcomma_oprH )rW rL ÚargsrD rV rE Ú__init__ s
|
ù ÿzFunctionElement.__init__c s t jpt| dd S )NrM )ÚsuperÚ
|
_proxy_keyrR rV ©Ú __class__rD rE ra s zFunctionElement._proxy_keyr4 r6 r7 zCursorResult[Any])Ú
|
connectionÚdistilled_paramsÚexecution_optionsÚreturnc C s | | ||¡S ©N)Z_execute_function)rW rd re rf rD rD rE Ú_execute_on_connection± s
|
ÿz&FunctionElement._execute_on_connectionc C s t | ||S )aª Return a column expression that's against this
|
:class:`_functions.FunctionElement` as a scalar
|
table-valued expression.
|
|
The returned expression is similar to that returned by a single column
|
accessed off of a :meth:`_functions.FunctionElement.table_valued`
|
construct, except no FROM clause is generated; the function is rendered
|
in the similar way as a scalar subquery.
|
|
E.g.:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import func, select
|
>>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
|
>>> print(select(fn))
|
{printsql}SELECT (jsonb_each(:jsonb_each_1)).key
|
|
.. versionadded:: 1.4.0b2
|
|
.. seealso::
|
|
:meth:`_functions.FunctionElement.table_valued`
|
|
:meth:`_functions.FunctionElement.alias`
|
|
:meth:`_functions.FunctionElement.column_valued`
|
|
)ÚScalarFunctionColumn)rW rM Útype_rD rD rE Úscalar_table_valued» s z#FunctionElement.scalar_table_valuedc O s^ | ¡ }| dd¡}| dd¡}| dd¡}|r@||f7 }d|_t| |_|_|j||dS )aÁ Return a :class:`_sql.TableValuedAlias` representation of this
|
:class:`_functions.FunctionElement` with table-valued expressions added.
|
|
e.g.:
|
|
.. sourcecode:: pycon+sql
|
|
>>> fn = (
|
... func.generate_series(1, 5).
|
... table_valued("value", "start", "stop", "step")
|
... )
|
|
>>> print(select(fn))
|
{printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
|
FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1{stop}
|
|
>>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
|
{printsql}SELECT anon_1.value, anon_1.stop
|
FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
|
WHERE anon_1.value > :value_1{stop}
|
|
A WITH ORDINALITY expression may be generated by passing the keyword
|
argument "with_ordinality":
|
|
.. sourcecode:: pycon+sql
|
|
>>> fn = func.generate_series(4, 1, -1).table_valued("gen", with_ordinality="ordinality")
|
>>> print(select(fn))
|
{printsql}SELECT anon_1.gen, anon_1.ordinality
|
FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1
|
|
:param \*expr: A series of string column names that will be added to the
|
``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
|
construct as columns. :func:`_sql.column` objects with or without
|
datatypes may also be used.
|
|
:param name: optional name to assign to the alias name that's generated.
|
If omitted, a unique anonymizing name is used.
|
|
:param with_ordinality: string name that when present results in the
|
``WITH ORDINALITY`` clause being added to the alias, and the given
|
string name will be added as a column to the .c collection
|
of the resulting :class:`_sql.TableValuedAlias`.
|
|
:param joins_implicitly: when True, the table valued function may be
|
used in the FROM clause without any explicit JOIN to other tables
|
in the SQL query, and no "cartesian product" warning will be generated.
|
May be useful for SQL functions such as ``func.json_each()``.
|
|
.. versionadded:: 1.4.33
|
|
.. versionadded:: 1.4.0b2
|
|
|
.. seealso::
|
|
:ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
|
|
:ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation
|
|
:meth:`_functions.FunctionElement.scalar_table_valued` - variant of
|
:meth:`_functions.FunctionElement.table_valued` which delivers the
|
complete table valued expression as a scalar column expression
|
|
:meth:`_functions.FunctionElement.column_valued`
|
|
:meth:`_sql.TableValuedAlias.render_derived` - renders the alias
|
using a derived column clause, e.g. ``AS name(col1, col2, ...)``
|
|
Úwith_ordinalityNÚjoins_implicitlyrM T©rM rn )Z _generateÚpoprI r/ ÚtyperJ Úalias)rW ÚexprÚkwÚnew_funcrm rn rM rD rD rE Ú table_valuedÜ s H
|
z FunctionElement.table_valuedc C s | j ||djS )aQ Return this :class:`_functions.FunctionElement` as a column expression that
|
selects from itself as a FROM clause.
|
|
E.g.:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import select, func
|
>>> gs = func.generate_series(1, 5, -1).column_valued()
|
>>> print(select(gs))
|
{printsql}SELECT anon_1
|
FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
|
|
This is shorthand for::
|
|
gs = func.generate_series(1, 5, -1).alias().column
|
|
:param name: optional name to assign to the alias name that's generated.
|
If omitted, a unique anonymizing name is used.
|
|
:param joins_implicitly: when True, the "table" portion of the column
|
valued function may be a member of the FROM clause without any
|
explicit JOIN to other tables in the SQL query, and no "cartesian
|
product" warning will be generated. May be useful for SQL functions
|
such as ``func.json_array_elements()``.
|
|
.. versionadded:: 1.4.46
|
|
.. seealso::
|
|
:ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
|
|
:ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
|
|
:meth:`_functions.FunctionElement.table_valued`
|
|
ro )rr Úcolumn©rW rM rn rD rD rE Ú column_valued2 s 'z FunctionElement.column_valuedc C s | j S )a The set of columns exported by this :class:`.FunctionElement`.
|
|
This is a placeholder collection that allows the function to be
|
placed in the FROM clause of a statement:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import column, select, func
|
>>> stmt = select(column('x'), column('y')).select_from(func.myfunction())
|
>>> print(stmt)
|
{printsql}SELECT x, y FROM myfunction()
|
|
The above form is a legacy feature that is now superseded by the
|
fully capable :meth:`_functions.FunctionElement.table_valued`
|
method; see that method for details.
|
|
.. seealso::
|
|
:meth:`_functions.FunctionElement.table_valued` - generates table-valued
|
SQL function expressions.
|
|
)rU rV rD rD rE Úcolumns[ s zFunctionElement.columnsc C s t dd | jD dS )z-synonym for :attr:`.FunctionElement.columns`.c S s g | ]}|j |fqS rD )Úkey)rT ÚcolrD rD rE rX z s z%FunctionElement.c.<locals>.<listcomp>©rz )r Ú_all_selected_columnsrV rD rD rE rU u s ÿzFunctionElement.cc C s$ t | jr| jj}n | d ¡g}|S rh )r rq Z _elementsÚlabel)rW ÚcolsrD rD rE r~ } s
|
|
z%FunctionElement._all_selected_columnsc C s | j S rh r} rV rD rD rE Úexported_columns s z FunctionElement.exported_columnsr ©rg c C s t t| jjS )z}Return the underlying :class:`.ClauseList` which contains
|
the arguments for this :class:`.FunctionElement`.
|
|
)r r rH ÚelementrV rD rD rE rL s zFunctionElement.clausesc C s t | ||||dS )aA Produce an OVER clause against this function.
|
|
Used against aggregate or so-called "window" functions,
|
for database backends that support window functions.
|
|
The expression::
|
|
func.row_number().over(order_by='x')
|
|
is shorthand for::
|
|
from sqlalchemy import over
|
over(func.row_number(), order_by='x')
|
|
See :func:`_expression.over` for a full description.
|
|
.. seealso::
|
|
:func:`_expression.over`
|
|
:ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
|
|
)Ú partition_byÚorder_byÚrowsÚrange_r( )rW r r
r r rD rD rE Úover s ûzFunctionElement.overc G s t | f| S )aÐ Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
|
|
Used against so-called "ordered set aggregate" and "hypothetical
|
set aggregate" functions, including :class:`.percentile_cont`,
|
:class:`.rank`, :class:`.dense_rank`, etc.
|
|
See :func:`_expression.within_group` for a full description.
|
|
.. seealso::
|
|
:ref:`tutorial_functions_within_group` -
|
in the :ref:`unified_tutorial`
|
|
|
r* )rW r
rD rD rE Ú within_group² s z FunctionElement.within_groupc G s |s| S t | f| S )a& Produce a FILTER clause against this function.
|
|
Used against aggregate and window functions,
|
for database backends that support the "FILTER" clause.
|
|
The expression::
|
|
func.count(1).filter(True)
|
|
is shorthand for::
|
|
from sqlalchemy import funcfilter
|
funcfilter(func.count(1), True)
|
|
.. seealso::
|
|
:ref:`tutorial_functions_within_group` -
|
in the :ref:`unified_tutorial`
|
|
:class:`.FunctionFilter`
|
|
:func:`.funcfilter`
|
|
|
r# )rW Z criterionrD rD rE ÚfilterÄ s zFunctionElement.filterc C s t | ||S )a^ Interpret this expression as a boolean comparison between two
|
values.
|
|
This method is used for an ORM use case described at
|
:ref:`relationship_custom_operator_sql_function`.
|
|
A hypothetical SQL function "is_equal()" which compares to values
|
for equality would be written in the Core expression language as::
|
|
expr = func.is_equal("a", "b")
|
|
If "is_equal()" above is comparing "a" and "b" for equality, the
|
:meth:`.FunctionElement.as_comparison` method would be invoked as::
|
|
expr = func.is_equal("a", "b").as_comparison(1, 2)
|
|
Where above, the integer value "1" refers to the first argument of the
|
"is_equal()" function and the integer value "2" refers to the second.
|
|
This would create a :class:`.BinaryExpression` that is equivalent to::
|
|
BinaryExpression("a", "b", operator=op.eq)
|
|
However, at the SQL level it would still render as
|
"is_equal('a', 'b')".
|
|
The ORM, when it loads a related object or collection, needs to be able
|
to manipulate the "left" and "right" sides of the ON clause of a JOIN
|
expression. The purpose of this method is to provide a SQL function
|
construct that can also supply this information to the ORM, when used
|
with the :paramref:`_orm.relationship.primaryjoin` parameter. The
|
return value is a containment object called :class:`.FunctionAsBinary`.
|
|
An ORM example is as follows::
|
|
class Venue(Base):
|
__tablename__ = 'venue'
|
id = Column(Integer, primary_key=True)
|
name = Column(String)
|
|
descendants = relationship(
|
"Venue",
|
primaryjoin=func.instr(
|
remote(foreign(name)), name + "/"
|
).as_comparison(1, 2) == 1,
|
viewonly=True,
|
order_by=name
|
)
|
|
Above, the "Venue" class can load descendant "Venue" objects by
|
determining if the name of the parent Venue is contained within the
|
start of the hypothetical descendant value's name, e.g. "parent1" would
|
match up to "parent1/child1", but not to "parent2/child1".
|
|
Possible use cases include the "materialized path" example given above,
|
as well as making use of special SQL functions such as geometric
|
functions to create join conditions.
|
|
:param left_index: the integer 1-based index of the function argument
|
that serves as the "left" side of the expression.
|
:param right_index: the integer 1-based index of the function argument
|
that serves as the "right" side of the expression.
|
|
.. versionadded:: 1.3
|
|
.. seealso::
|
|
:ref:`relationship_custom_operator_sql_function` -
|
example use within the ORM
|
|
)ÚFunctionAsBinary)rW Ú
|
left_indexÚ right_indexrD rD rE Ú as_comparisonâ s Hz FunctionElement.as_comparisonc C s | j jS rh )rL Ú _from_objectsrV rD rD rE r , s z FunctionElement._from_objectsc C s dS )a For types that define their return type as based on the criteria
|
within a WITHIN GROUP (ORDER BY) expression, called by the
|
:class:`.WithinGroup` construct.
|
|
Returns None by default, in which case the function's normal ``.type``
|
is used.
|
|
NrD )rW r rD rD rE Úwithin_group_type0 s
|
z!FunctionElement.within_group_typec C s t j| || j|dS )a+
|
Produce a :class:`_expression.Alias` construct against this
|
:class:`.FunctionElement`.
|
|
.. tip::
|
|
The :meth:`_functions.FunctionElement.alias` method is part of the
|
mechanism by which "table valued" SQL functions are created.
|
However, most use cases are covered by higher level methods on
|
:class:`_functions.FunctionElement` including
|
:meth:`_functions.FunctionElement.table_valued`, and
|
:meth:`_functions.FunctionElement.column_valued`.
|
|
This construct wraps the function in a named alias which
|
is suitable for the FROM clause, in the style accepted for example
|
by PostgreSQL. A column expression is also provided using the
|
special ``.column`` attribute, which may
|
be used to refer to the output of the function as a scalar value
|
in the columns or where clause, for a backend such as PostgreSQL.
|
|
For a full table-valued expression, use the
|
:meth:`_functions.FunctionElement.table_valued` method first to
|
establish named columns.
|
|
e.g.:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import func, select, column
|
>>> data_view = func.unnest([1, 2, 3]).alias("data_view")
|
>>> print(select(data_view.column))
|
{printsql}SELECT data_view
|
FROM unnest(:unnest_1) AS data_view
|
|
The :meth:`_functions.FunctionElement.column_valued` method provides
|
a shortcut for the above pattern:
|
|
.. sourcecode:: pycon+sql
|
|
>>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
|
>>> print(select(data_view))
|
{printsql}SELECT data_view
|
FROM unnest(:unnest_1) AS data_view
|
|
.. versionadded:: 1.4.0b2 Added the ``.column`` accessor
|
|
:param name: alias name, will be rendered as ``AS <name>`` in the
|
FROM clause
|
|
:param joins_implicitly: when True, the table valued function may be
|
used in the FROM clause without any explicit JOIN to other tables
|
in the SQL query, and no "cartesian product" warning will be
|
generated. May be useful for SQL functions such as
|
``func.json_each()``.
|
|
.. versionadded:: 1.4.33
|
|
.. seealso::
|
|
:ref:`tutorial_functions_table_valued` -
|
in the :ref:`unified_tutorial`
|
|
:meth:`_functions.FunctionElement.table_valued`
|
|
:meth:`_functions.FunctionElement.scalar_table_valued`
|
|
:meth:`_functions.FunctionElement.column_valued`
|
|
|
)rM Ztable_value_typern )r. Z
|
_constructrq rx rD rD rE rr < s GüzFunctionElement.aliasz Select[Any]c C s t | }| jr |jf | j}|S )z³Produce a :func:`_expression.select` construct
|
against this :class:`.FunctionElement`.
|
|
This is shorthand for::
|
|
s = select(function_element)
|
|
)r- Z_execution_optionsrf )rW ÚsrD rD rE Úselect s zFunctionElement.selectc K s t d |f|| jd|d|S )NT)Ú_compared_to_operatorÚ_compared_to_typeÚuniquerk )r rq ©rW rZ Úobjrk rt rD rD rE Ú _bind_param s þúùzFunctionElement._bind_paramc s2 |t jkr t| jtjr t| S t j|dS d S )N)Úagainst) r ÚgetitemÚ
|
isinstancerq r ÚARRAYr% r` Ú
|
self_group)rW r rb rD rE r £ s ÿzFunctionElement.self_groupc C s
|
t | jS )zzoverrides FromClause.entity_namespace as functions are generally
|
column expressions and not FromClauses.
|
|
)r rH rV rD rD rE Úentity_namespace¯ s z FunctionElement.entity_namespace)N)NF)NNNN)NF)N)N),Ú__name__Ú
|
__module__Ú __qualname__Ú__doc__r1 Údp_clauseelementZ
|
dp_booleanZdp_has_cache_keyÚ_traverse_internalsrK Ú__annotations__r[ rI rJ r_ Z_non_anon_labelÚpropertyra ri rl rv ry r Zro_non_memoized_propertyrz Zro_memoized_propertyrU r~ r r Zmemoized_attributerL r r r r r r rr r r r r Ú __classcell__rD rD rb rE rG d sV
|
ý
|
|
!V
|
)
|
|
|
|
|
J
|
|
N
|
rG c @ sÀ e Zd ZU dejfdejfdejfdejfgZded< ded< ded< dd Z dddd d
|
d Z
|
e d d ddZ e j d ddddZ e d d ddZej d ddddZes¼e ZeZdS )r Ú sql_functionr r Ú modifierszFunctionElement[Any]Úintc C s t | ||¡S rh )r! Ú_gen_cache_key)rW Zanon_mapZ
|
bindparamsrD rD rE r« Æ s z FunctionAsBinary._gen_cache_key)rA r r c C s8 || _ || _|| _tj| _tj| _d | _ d| _
|
i | _ d S )NT) r¨ r r r Zfunction_as_comparison_oprZ r Z BOOLEANTYPErq ÚnegateZ_is_implicitly_booleanr© )rW rA r r rD rD rE r_ É s zFunctionAsBinary.__init__zColumnElement[Any]r c C s | j jj| jd S ©Nr ©r¨ rL r rV rD rD rE Ú left_exprÖ s zFunctionAsBinary.left_exprÚNone)Úvaluerg c C s || j jj| jd < d S r r® ©rW r± rD rD rE r¯ Ú s c C s | j jj| jd S r ©r¨ rL r rV rD rD rE Ú
|
right_exprÞ s zFunctionAsBinary.right_exprc C s || j jj| jd < d S r r³ r² rD rD rE r´ â s N)r r r¡ r1 r£ Ú dp_plain_objZ dp_plain_dictr¤ r¥ r« r_ r¦ r¯ Úsetterr´ r ÚleftÚrightrD rD rD rE r º s*
|
ü r c @ sH e Zd ZdZdejfdejfdejfgZdZ dZ
|
d ddd d
|
d d Z dS )rj Zscalar_function_columnrM rq rA FNzFunctionElement[_T]r< ú!Optional[_TypeEngineArgument[_T]])rA rM rk c C s || _ || _t |¡| _d S rh )rA rM r Ú to_instancerq )rW rA rM rk rD rD rE r_ ú s z ScalarFunctionColumn.__init__)N) r r r¡ Ú__visit_name__r1 Z dp_anon_nameÚdp_typer£ r¤ Z
|
is_literalÚtabler_ rD rD rD rE rj î s ý ürj c @ sä e Zd ZdZdd Zdd dddZedd dd
|
d d d Zedddddd Zdddddd Zeràe ddddZ
|
e ddddZ e ddddZ e d dd d Z e d dd d!Ze d"dd#d$Ze d%dd&d'Ze d(dd)d*Ze d+dd,d-Ze d.dd/d0Ze d1dd2d3Ze d4dd5d6Ze d7dd8d9Ze d:dd;d<Ze d=dd>d?Ze d@ddAdBZe dCddDdEZe dFddGdHZe dIddJdKZ e dLddMdNZ e dOddPdQZ e dRddSdTZ e dUddVdWZ e dXddYdZZ!e d[dd\d]Z"e d^dd_d`Z#e daddbdcZ$e ddddedfZ%e dgddhdiZ&e djddkdlZ'e dmddndoZ(e dpddqdrZ)e dsddtduZ*e dvddwdxZ+e dyddzd{Z,d|S )}Ú_FunctionGeneratora Generate SQL function expressions.
|
|
:data:`.func` is a special object instance which generates SQL
|
functions based on name-based attributes, e.g.:
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(func.count(1))
|
{printsql}count(:param_1)
|
|
The returned object is an instance of :class:`.Function`, and is a
|
column-oriented SQL element like any other, and is used in that way:
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(select(func.count(table.c.id)))
|
{printsql}SELECT count(sometable.id) FROM sometable
|
|
Any name can be given to :data:`.func`. If the function name is unknown to
|
SQLAlchemy, it will be rendered exactly as is. For common SQL functions
|
which SQLAlchemy is aware of, the name may be interpreted as a *generic
|
function* which will be compiled appropriately to the target database:
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(func.current_timestamp())
|
{printsql}CURRENT_TIMESTAMP
|
|
To call functions which are present in dot-separated packages,
|
specify them in the same manner:
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(func.stats.yield_curve(5, 10))
|
{printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
|
|
SQLAlchemy can be made aware of the return type of functions to enable
|
type-specific lexical and result-based behavior. For example, to ensure
|
that a string-based function returns a Unicode value and is similarly
|
treated as a string in expressions, specify
|
:class:`~sqlalchemy.types.Unicode` as the type:
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(func.my_string(u'hi', type_=Unicode) + ' ' +
|
... func.my_string(u'there', type_=Unicode))
|
{printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
|
|
The object returned by a :data:`.func` call is usually an instance of
|
:class:`.Function`.
|
This object meets the "column" interface, including comparison and labeling
|
functions. The object can also be passed the :meth:`~.Connectable.execute`
|
method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
|
where it will be
|
wrapped inside of a SELECT statement first::
|
|
print(connection.execute(func.current_timestamp()).scalar())
|
|
In a few exception cases, the :data:`.func` accessor
|
will redirect a name to a built-in expression such as :func:`.cast`
|
or :func:`.extract`, as these names have well-known meaning
|
but are not exactly the same as "functions" from a SQLAlchemy
|
perspective.
|
|
Functions which are interpreted as "generic" functions know how to
|
calculate their return type automatically. For a listing of known generic
|
functions, see :ref:`generic_functions`.
|
|
.. note::
|
|
The :data:`.func` construct has only limited support for calling
|
standalone "stored procedures", especially those with special
|
parameterization concerns.
|
|
See the section :ref:`stored_procedures` for details on how to use
|
the DBAPI-level ``callproc()`` method for fully traditional stored
|
procedures.
|
|
.. seealso::
|
|
:ref:`tutorial_functions` - in the :ref:`unified_tutorial`
|
|
:class:`.Function`
|
|
c K s g | _ || _d S rh )Ú_FunctionGenerator__namesÚopts)rW rÀ rD rD rE r_ _ s z_FunctionGenerator.__init__r< )rM rg c C sn | d¡r6z | j| W S tk
|
r2 t|Y qLX n| d¡rL|dd
}tf | j}t| j|g |_|S )NÚ__Ú_r éÿÿÿÿ) Ú
|
startswithÚ__dict__ÚKeyErrorÚAttributeErrorÚendswithr¾ rÀ Úlistr¿ )rW rM ÚfrD rD rE Ú __getattr__c s
|
|
z _FunctionGenerator.__getattr__r z_TypeEngineArgument[_T]z Function[_T])rU rk Úkwargsrg c O s d S rh rD )rW rk rU rÌ rD rD rE Ú__call__q s z_FunctionGenerator.__call__z Function[Any])rU rÌ rg c O s d S rh rD )rW rU rÌ rD rD rE rÍ w s c O s¨ | j ¡ }| |¡ t| j}|dkr2| j\}}n |dkrLd| jd }}nd }|d k r|t| | ¡ ¡}|d k r||||S t| jd f|dt | jdd
i|S )Nr2 r r; r rà rK )
|
rÀ ÚcopyÚupdateÚlenr¿ r: Úgetr= ÚFunctionÚtuple)rW rU rÌ ÚoÚtokensrB ÚfnameÚfuncrD rD rE rÍ { s*
|
|
|
|
ÿÿÿÿzType[AnsiFunction[Any]]r c C s d S rh rD rV rD rD rE Ú ansifunction s z _FunctionGenerator.ansifunctionzType[array_agg[Any]]c C s d S rh rD rV rD rD rE Ú array_agg s z _FunctionGenerator.array_aggzType[Cast[Any]]c C s d S rh rD rV rD rD rE r s z_FunctionGenerator.castzType[char_length]c C s d S rh rD rV rD rD rE Ú char_length¤ s z _FunctionGenerator.char_lengthzType[coalesce[Any]]c C s d S rh rD rV rD rD rE Úcoalesce¨ s z_FunctionGenerator.coalescez Type[concat]c C s d S rh rD rV rD rD rE Úconcat¬ s z_FunctionGenerator.concatz Type[count]c C s d S rh rD rV rD rD rE Úcount° s z_FunctionGenerator.countzType[cube[Any]]c C s d S rh rD rV rD rD rE Úcube´ s z_FunctionGenerator.cubezType[cume_dist]c C s d S rh rD rV rD rD rE Ú cume_dist¸ s z _FunctionGenerator.cume_distzType[current_date]c C s d S rh rD rV rD rD rE Ú current_date¼ s z _FunctionGenerator.current_datezType[current_time]c C s d S rh rD rV rD rD rE Ú current_timeÀ s z _FunctionGenerator.current_timezType[current_timestamp]c C s d S rh rD rV rD rD rE Úcurrent_timestampÄ s z$_FunctionGenerator.current_timestampzType[current_user]c C s d S rh rD rV rD rD rE Ú current_userÈ s z _FunctionGenerator.current_userzType[dense_rank]c C s d S rh rD rV rD rD rE Ú
|
dense_rankÌ s z _FunctionGenerator.dense_rankz Type[Extract]c C s d S rh rD rV rD rD rE ÚextractÐ s z_FunctionGenerator.extractzType[grouping_sets[Any]]c C s d S rh rD rV rD rD rE Ú grouping_setsÔ s z _FunctionGenerator.grouping_setszType[localtime]c C s d S rh rD rV rD rD rE Ú localtimeØ s z _FunctionGenerator.localtimezType[localtimestamp]c C s d S rh rD rV rD rD rE ÚlocaltimestampÜ s z!_FunctionGenerator.localtimestampzType[max[Any]]c C s d S rh rD rV rD rD rE Úmaxà s z_FunctionGenerator.maxzType[min[Any]]c C s d S rh rD rV rD rD rE Úminä s z_FunctionGenerator.minzType[mode[Any]]c C s d S rh rD rV rD rD rE Úmodeè s z_FunctionGenerator.modezType[next_value]c C s d S rh rD rV rD rD rE Ú
|
next_valueì s z _FunctionGenerator.next_valuez Type[now]c C s d S rh rD rV rD rD rE Únowð s z_FunctionGenerator.nowzType[OrderedSetAgg[Any]]c C s d S rh rD rV rD rD rE Ú orderedsetaggô s z _FunctionGenerator.orderedsetaggzType[percent_rank]c C s d S rh rD rV rD rD rE Ú percent_rankø s z _FunctionGenerator.percent_rankzType[percentile_cont[Any]]c C s d S rh rD rV rD rD rE Úpercentile_contü s z"_FunctionGenerator.percentile_contzType[percentile_disc[Any]]c C s d S rh rD rV rD rD rE Úpercentile_disc s z"_FunctionGenerator.percentile_discz Type[random]c C s d S rh rD rV rD rD rE Úrandom s z_FunctionGenerator.randomz
|
Type[rank]c C s d S rh rD rV rD rD rE Úrank s z_FunctionGenerator.rankz Type[ReturnTypeFromArgs[Any]]c C s d S rh rD rV rD rD rE Úreturntypefromargs s z%_FunctionGenerator.returntypefromargszType[rollup[Any]]c C s d S rh rD rV rD rD rE Úrollup s z_FunctionGenerator.rollupzType[session_user]c C s d S rh rD rV rD rD rE Ú session_user s z _FunctionGenerator.session_userzType[sum[Any]]c C s d S rh rD rV rD rD rE Úsum s z_FunctionGenerator.sumz Type[sysdate]c C s d S rh rD rV rD rD rE Úsysdate s z_FunctionGenerator.sysdatez
|
Type[user]c C s d S rh rD rV rD rD rE Úuser s z_FunctionGenerator.userN)-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è ré rê rë rì rí rî rï rð rñ rò ró rô rõ rö r÷ rø rù rD rD rD rE r¾ s Vr¾ F)Úgroupc @ sv e Zd ZU dZdZejdejfdej fdej
|
fg Zde d< de d< de d< d d d
|
dd d d dddZ dddZ d S )rÒ aá Describe a named SQL function.
|
|
The :class:`.Function` object is typically generated from the
|
:data:`.func` generation object.
|
|
|
:param \*clauses: list of column expressions that form the arguments
|
of the SQL function call.
|
|
:param type\_: optional :class:`.TypeEngine` datatype object that will be
|
used as the return value of the column expression generated by this
|
function call.
|
|
:param packagenames: a string which indicates package prefix names
|
to be prepended to the function name when the SQL is generated.
|
The :data:`.func` generator creates these when it is called using
|
dotted format, e.g.::
|
|
func.mypackage.some_function(col1, col2)
|
|
.. seealso::
|
|
:ref:`tutorial_functions` - in the :ref:`unified_tutorial`
|
|
:data:`.func` - namespace which produces registered or ad-hoc
|
:class:`.Function` instances.
|
|
:class:`.GenericFunction` - allows creation of registered function
|
types.
|
|
ÚfunctionrK rM rq r< r@ zTypeEngine[_T]N)rk rK r r¹ zOptional[Tuple[str, ...]])rM rL rk rK c G s0 |pd| _ || _t |¡| _tj| f| dS )zConstruct a :class:`.Function`.
|
|
The :data:`.func` construct is normally used to construct
|
new :class:`.Function` instances.
|
|
rD N)rK rM r rº rq rG r_ )rW rM rk rK rL rD rD rE r_ k s
|
zFunction.__init__c K s t | j|f|| j|dd|S )NT)r r rk r )r rM rq r rD rD rE r s þúùzFunction._bind_param)N)r r r¡ r¢ r» rG r¤ r1 rµ Z dp_stringr¼ r¥ r_ r rD rD rD rE rÒ - s
|
ýûrÒ c sZ e Zd ZU dZdZdZded< d Zdd fdd Ze d d
|
dd d d Z
|
dd Z Z S )ÚGenericFunctionaq Define a 'generic' function.
|
|
A generic function is a pre-established :class:`.Function`
|
class that is instantiated automatically when called
|
by name from the :data:`.func` attribute. Note that
|
calling any name from :data:`.func` has the effect that
|
a new :class:`.Function` instance is created automatically,
|
given that name. The primary use case for defining
|
a :class:`.GenericFunction` class is so that a function
|
of a particular name may be given a fixed return type.
|
It can also include custom argument parsing schemes as well
|
as additional methods.
|
|
Subclasses of :class:`.GenericFunction` are automatically
|
registered under the name of the class. For
|
example, a user-defined function ``as_utc()`` would
|
be available immediately::
|
|
from sqlalchemy.sql.functions import GenericFunction
|
from sqlalchemy.types import DateTime
|
|
class as_utc(GenericFunction):
|
type = DateTime()
|
inherit_cache = True
|
|
print(select(func.as_utc()))
|
|
User-defined generic functions can be organized into
|
packages by specifying the "package" attribute when defining
|
:class:`.GenericFunction`. Third party libraries
|
containing many functions may want to use this in order
|
to avoid name conflicts with other systems. For example,
|
if our ``as_utc()`` function were part of a package
|
"time"::
|
|
class as_utc(GenericFunction):
|
type = DateTime()
|
package = "time"
|
inherit_cache = True
|
|
The above function would be available from :data:`.func`
|
using the package name ``time``::
|
|
print(select(func.time.as_utc()))
|
|
A final option is to allow the function to be accessed
|
from one name in :data:`.func` but to render as a different name.
|
The ``identifier`` attribute will override the name used to
|
access the function as loaded from :data:`.func`, but will retain
|
the usage of ``name`` as the rendered name::
|
|
class GeoBuffer(GenericFunction):
|
type = Geometry()
|
package = "geo"
|
name = "ST_Buffer"
|
identifier = "buffer"
|
inherit_cache = True
|
|
The above function will render as follows:
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(func.geo.buffer())
|
{printsql}ST_Buffer()
|
|
The name will be rendered as is, however without quoting unless the name
|
contains special characters that require quoting. To force quoting
|
on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
|
construct::
|
|
from sqlalchemy.sql import quoted_name
|
|
class GeoBuffer(GenericFunction):
|
type = Geometry()
|
package = "geo"
|
name = quoted_name("ST_Buffer", True)
|
identifier = "buffer"
|
inherit_cache = True
|
|
The above function will render as:
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(func.geo.buffer())
|
{printsql}"ST_Buffer"()
|
|
Type parameters for this class as a
|
`generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
|
and should match the type seen in a :class:`_engine.Result`. For example::
|
|
class as_utc(GenericFunction[datetime.datetime]):
|
type = DateTime()
|
inherit_cache = True
|
|
The above indicates that the following expression returns a ``datetime``
|
object::
|
|
connection.scalar(select(func.as_utc()))
|
|
.. versionadded:: 1.3.13 The :class:`.quoted_name` construct is now
|
recognized for quoting when used with the "name" attribute of the
|
object, so that quoting can be forced on or off for the function
|
name.
|
|
|
Tr\ Ú _registerr° r c s* t j| jkr | | j| j¡ t ¡ d S rh )r Z AnnotatedÚ__mro__Ú_register_generic_functionr rÅ r` Ú__init_subclass__)Úclsrb rD rE r s z!GenericFunction.__init_subclass__r< zMapping[str, Any])ÚclsnameÚclsdictrg c C sn | d|¡ | _}| d|¡ | _}| dd¡}d|krB|d | _t| dd| _| jrdt|| | nd| _d S )NrM r@ rB r; Z__return_type__rý T)rÑ rM r@ rq rR rý rF )r r r rM r@ rB rD rD rE rÿ s
|
z*GenericFunction._register_generic_functionc sx | dd ¡}|d kr& fdd|D } jp2t| _d _tt|tjdd _t
|
| dd ¡pnt dd ¡ _ d S ) NÚ _parsed_argsc s" g | ]}t jtj| j d qS ©rN ©r rP r rQ rM rS rV rD rE rX s úüz,GenericFunction.__init__.<locals>.<listcomp>rD TrY rk rq ) rp r[ r\ rK r% r r r] rH r rº rR rq )rW r^ rÌ Z parsed_argsrD rV rE r_ s"
|
ù ÿÿÿzGenericFunction.__init__) r r r¡ r¢ Zcoerce_argumentsÚ inherit_cacher¥ rM r Ú classmethodrÿ r_ r§ rD rD rb rE rü s
|
krü r rå c @ sD e Zd ZdZe ¡ Zd Zdej fgZ
|
dd Z dd Z e dd Zd S )
|
rì a Represent the 'next value', given a :class:`.Sequence`
|
as its single argument.
|
|
Compiles into the appropriate function on each backend,
|
or will raise NotImplementedError if used on a backend
|
that does not provide support for sequences.
|
|
Úsequencec K s8 t |tjstd|| _t |jp.t| dd ¡| _ d S )Nz0next_value() accepts a Sequence object as input.rq )
|
r r ÚSequenceÚAssertionErrorr r rº Z data_typerR rq )rW Úseqrt rD rD rE r_ I s ÿþÿznext_value.__init__c K s t |to| jj|jjkS rh )r rì r rM )rW Úotherrt rD rD rE ÚcompareR s
|
þznext_value.comparec C s g S rh rD rV rD rD rE r X s znext_value._from_objectsN)r r r¡ r¢ r ÚIntegerrq rM r1 Zdp_named_ddl_elementr¤ r_ r r¦ r rD rD rD rE rì 8 s ÿ rì c @ s e Zd ZdZdZdd ZdS )Ú AnsiFunctionzEDefine a function in "ansi" format, which doesn't render parenthesis.Tc O s t j| f|| d S rh )rü r_ )rW r^ rÌ rD rD rE r_ b s zAnsiFunction.__init__N)r r r¡ r¢ r r_ rD rD rD rE r ] s r c s$ e Zd ZdZdZ fddZ ZS )ÚReturnTypeFromArgszADefine a function whose return type is the same as its arguments.Tc s< fdd|D }| dt|¡ ||d< t j|| d S )Nc s" g | ]}t jtj| j d qS r r rS rV rD rE rX l s úüz/ReturnTypeFromArgs.__init__.<locals>.<listcomp>rk r )Ú
|
setdefaultr r` r_ )rW r^ rÌ Úfn_argsrb rV rE r_ k s
|
ù zReturnTypeFromArgs.__init__©r r r¡ r¢ r r_ r§ rD rD rb rE r f s r c @ s e Zd ZdZdZdS )rÛ TN)r r r¡ r[ r rD rD rD rE rÛ z s rÛ c @ s e Zd ZdZdZdS )ré z!The SQL MAX() aggregate function.TN©r r r¡ r¢ r rD rD rD rE ré s ré c @ s e Zd ZdZdZdS )rê z!The SQL MIN() aggregate function.TNr rD rD rD rE rê
s rê c @ s e Zd ZdZdZdS )r÷ z!The SQL SUM() aggregate function.TNr rD rD rD rE r÷ s r÷ c @ s e Zd ZdZe ¡ ZdZdS )rí zºThe SQL now() datetime function.
|
|
SQLAlchemy dialects will usually render this particular function
|
in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
|
|
TN©r r r¡ r¢ r ÚDateTimerq r rD rD rD rE rí s rí c @ s e Zd ZdZe ¡ ZdZdS )rÜ a* The SQL CONCAT() function, which concatenates strings.
|
|
E.g.:
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(select(func.concat('a', 'b')))
|
{printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
|
|
String concatenation in SQLAlchemy is more commonly available using the
|
Python ``+`` operator with string datatypes, which will render a
|
backend-specific concatenation operator, such as :
|
|
.. sourcecode:: pycon+sql
|
|
>>> print(select(literal("a") + "b"))
|
{printsql}SELECT :param_1 || :param_2 AS anon_1
|
|
|
TN©r r r¡ r¢ r ÚStringrq r rD rD rD rE rÜ s rÜ c s, e Zd ZdZe ¡ ZdZ fddZ Z S )rÚ z The CHAR_LENGTH() SQL function.Tc s t j|f| d S rh )r` r_ )rW Úargrt rb rD rE r_ ½ s zchar_length.__init__©
|
r r r¡ r¢ r r rq r r_ r§ rD rD rb rE rÚ · s rÚ c @ s e Zd ZdZdZdZdS )rò zThe RANDOM() SQL function.TN©r r r¡ r¢ r[ r rD rD rD rE rò Ã s rò c s. e Zd ZdZe ¡ ZdZd fdd Z Z S )rÝ a§ The ANSI COUNT aggregate function. With no arguments,
|
emits COUNT \*.
|
|
E.g.::
|
|
from sqlalchemy import func
|
from sqlalchemy import select
|
from sqlalchemy import table, column
|
|
my_table = table('some_table', column('id'))
|
|
stmt = select(func.count()).select_from(my_table)
|
|
Executing ``stmt`` would emit::
|
|
SELECT count(*) AS count_1
|
FROM some_table
|
|
|
TNc s$ |d krt d}t j|f| d S )NÚ*)r& r` r_ )rW Z
|
expressionrÌ rb rD rE r_ â s zcount.__init__)Nr rD rD rb rE rÝ Ê s rÝ c @ s e Zd ZdZe ¡ ZdZdS )rà z The CURRENT_DATE() SQL function.TN)r r r¡ r¢ r ÚDaterq r rD rD rD rE rà è s rà c @ s e Zd ZdZe ¡ ZdZdS )rá z The CURRENT_TIME() SQL function.TN)r r r¡ r¢ r ZTimerq r rD rD rD rE rá ï s rá c @ s e Zd ZdZe ¡ ZdZdS )râ z%The CURRENT_TIMESTAMP() SQL function.TNr rD rD rD rE râ ö s râ c @ s e Zd ZdZe ¡ ZdZdS )rã z The CURRENT_USER() SQL function.TNr rD rD rD rE rã ý s rã c @ s e Zd ZdZe ¡ ZdZdS )rç z The localtime() SQL function.TNr rD rD rD rE rç s rç c @ s e Zd ZdZe ¡ ZdZdS )rè z"The localtimestamp() SQL function.TNr rD rD rD rE rè s rè c @ s e Zd ZdZe ¡ ZdZdS )rö z The SESSION_USER() SQL function.TNr rD rD rD rE rö s rö c @ s e Zd ZdZe ¡ ZdZdS )rø zThe SYSDATE() SQL function.TNr rD rD rD rE rø s rø c @ s e Zd ZdZe ¡ ZdZdS )rù zThe USER() SQL function.TNr rD rD rD rE rù s rù c s$ e Zd ZdZdZ fddZ ZS )rÙ a Support for the ARRAY_AGG function.
|
|
The ``func.array_agg(expr)`` construct returns an expression of
|
type :class:`_types.ARRAY`.
|
|
e.g.::
|
|
stmt = select(func.array_agg(table.c.values)[2:5])
|
|
.. seealso::
|
|
:func:`_postgresql.array_agg` - PostgreSQL-specific version that
|
returns :class:`_postgresql.ARRAY`, which has PG-specific operators
|
added.
|
|
Tc sp fdd|D }| dtj¡}d|krVt|}t|tjrF||d< n||dd|d< ||d< t j|| d S )Nc s g | ]}t jtj| d qS ))rO )r rP r rQ rS rV rD rE rX < s ý ÿz&array_agg.__init__.<locals>.<listcomp>Z_default_array_typerk r )Z
|
dimensionsr )rp r r r r r` r_ )rW r^ rÌ r Zdefault_array_typeZtype_from_argsrb rV rE r_ ; s
|
ü
|
ÿ
|
zarray_agg.__init__r rD rD rb rE rÙ ' s rÙ c @ s e Zd ZdZdZdZdd ZdS )Ú OrderedSetAggzDefine a function where the return type is based on the sort
|
expression type as defined by the expression passed to the
|
:meth:`.FunctionElement.within_group` method.FTc C sL t t| jj}t |j¡}| jr>t|j dkr>t
|
|d j ¡S |d j S d S )Nr r ) r r rH r ÚsqlutilZunwrap_order_byr
Úarray_for_multi_clauserÐ rL r r rq )rW r Z func_clausesr
rD rD rE r Y s
|
z OrderedSetAgg.within_group_typeN)r r r¡ r¢ r! r r rD rD rD rE r Q s r c @ s e Zd ZdZdZdS )rë a Implement the ``mode`` ordered-set aggregate function.
|
|
This function must be used with the :meth:`.FunctionElement.within_group`
|
modifier to supply a sort expression to operate upon.
|
|
The return type of this function is the same as the sort expression.
|
|
TNr rD rD rD rE rë b s rë c @ s e Zd ZdZdZdZdS )rð a| Implement the ``percentile_cont`` ordered-set aggregate function.
|
|
This function must be used with the :meth:`.FunctionElement.within_group`
|
modifier to supply a sort expression to operate upon.
|
|
The return type of this function is the same as the sort expression,
|
or if the arguments are an array, an :class:`_types.ARRAY` of the sort
|
expression's type.
|
|
TN©r r r¡ r¢ r! r rD rD rD rE rð o s rð c @ s e Zd ZdZdZdZdS )rñ a| Implement the ``percentile_disc`` ordered-set aggregate function.
|
|
This function must be used with the :meth:`.FunctionElement.within_group`
|
modifier to supply a sort expression to operate upon.
|
|
The return type of this function is the same as the sort expression,
|
or if the arguments are an array, an :class:`_types.ARRAY` of the sort
|
expression's type.
|
|
TNr" rD rD rD rE rñ s rñ c @ s e Zd ZdZe ¡ ZdZdS )ró a Implement the ``rank`` hypothetical-set aggregate function.
|
|
This function must be used with the :meth:`.FunctionElement.within_group`
|
modifier to supply a sort expression to operate upon.
|
|
The return type of this function is :class:`.Integer`.
|
|
TN©r r r¡ r¢ r r rq r rD rD rD rE ró s ró c @ s e Zd ZdZe ¡ ZdZdS )rä a Implement the ``dense_rank`` hypothetical-set aggregate function.
|
|
This function must be used with the :meth:`.FunctionElement.within_group`
|
modifier to supply a sort expression to operate upon.
|
|
The return type of this function is :class:`.Integer`.
|
|
TNr# rD rD rD rE rä s rä c @ s& e Zd ZU dZe ¡ Zded< dZdS )rï a Implement the ``percent_rank`` hypothetical-set aggregate function.
|
|
This function must be used with the :meth:`.FunctionElement.within_group`
|
modifier to supply a sort expression to operate upon.
|
|
The return type of this function is :class:`.Numeric`.
|
|
ú!sqltypes.Numeric[decimal.Decimal]rq TN© r r r¡ r¢ r ZNumericrq r¥ r rD rD rD rE rï « s
|
rï c @ s& e Zd ZU dZe ¡ Zded< dZdS )rß a Implement the ``cume_dist`` hypothetical-set aggregate function.
|
|
This function must be used with the :meth:`.FunctionElement.within_group`
|
modifier to supply a sort expression to operate upon.
|
|
The return type of this function is :class:`.Numeric`.
|
|
r$ rq TNr% rD rD rD rE rß ¹ s
|
rß c @ s e Zd ZdZdZdZdS )rÞ aS Implement the ``CUBE`` grouping operation.
|
|
This function is used as part of the GROUP BY of a statement,
|
e.g. :meth:`_expression.Select.group_by`::
|
|
stmt = select(
|
func.sum(table.c.value), table.c.col_1, table.c.col_2
|
).group_by(func.cube(table.c.col_1, table.c.col_2))
|
|
.. versionadded:: 1.2
|
|
TNr rD rD rD rE rÞ Ç s rÞ c @ s e Zd ZdZdZdZdS )rõ aW Implement the ``ROLLUP`` grouping operation.
|
|
This function is used as part of the GROUP BY of a statement,
|
e.g. :meth:`_expression.Select.group_by`::
|
|
stmt = select(
|
func.sum(table.c.value), table.c.col_1, table.c.col_2
|
).group_by(func.rollup(table.c.col_1, table.c.col_2))
|
|
.. versionadded:: 1.2
|
|
TNr rD rD rD rE rõ Ø s rõ c @ s e Zd ZdZdZdZdS )ræ a Implement the ``GROUPING SETS`` grouping operation.
|
|
This function is used as part of the GROUP BY of a statement,
|
e.g. :meth:`_expression.Select.group_by`::
|
|
stmt = select(
|
func.sum(table.c.value), table.c.col_1, table.c.col_2
|
).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
|
|
In order to group by multiple sets, use the :func:`.tuple_` construct::
|
|
from sqlalchemy import tuple_
|
|
stmt = select(
|
func.sum(table.c.value),
|
table.c.col_1, table.c.col_2,
|
table.c.col_3
|
).group_by(
|
func.grouping_sets(
|
tuple_(table.c.col_1, table.c.col_2),
|
tuple_(table.c.value, table.c.col_3),
|
)
|
)
|
|
|
.. versionadded:: 1.2
|
|
TNr rD rD rD rE ræ é s ræ )r; )ur¢ Ú
|
__future__r ÚdatetimeÚdecimalÚtypingr r r r r r r r
|
r r Ú r r r r r r r r r Ú_typingr Úbaser r r r r Úelementsr r r r r r! r" r$ r% r& r' r) r+ Z
|
selectabler, r- r. r/ r0 Zvisitorsr1 r3 Z engine.baser4 Z engine.cursorr5 Zengine.interfacesr6 r7 r8 Ú defaultdictÚdictr: r¥ rF rG r rj r¾ r× ÚmodifierrÒ rü rª rì r r rÛ ré rê r÷ rí r< rÜ rÚ Úfloatrò rÝ Údaterà Útimerá râ rã rç rè rö rø rù rÙ r rë rð rñ ró rä ÚDecimalrï rß rÞ rõ ræ rD rD rD rE Ú<module> sØ þ
|
|
Z4 !
|
` (
|
|
% *
|
