o y\Lg@sdZddlmZddlmZddlmZddlmZddlmZddlm Z ddlm Z dd l m Z dd l m Z dd l mZdd l mZdd l mZddl mZddl mZddl mZddl mZddl mZddl mZddl mZddl mZddlmZddlmZddlmZddlmZddlmZee Z!doddZ"Gddde eeZ#Gd d!d!e$Z%e%Z& e%d"d#Z'Gd$d%d%e#Z(Gd&d'd'eZ)Gd(d)d)e*e)e(Z+e"d*ee"d+eGd,d-d-e+Z,Gd.d/d/e+Z-Gd0d1d1e+Z.Gd2d3d3e.Z/Gd4d5d5e.Z0Gd6d7d7e.Z1Gd8d9d9e.Z2Gd:d;d;e+Z3Gdd?d?e+Z5Gd@dAdAe+Z6GdBdCdCe+Z7GdDdEdEe-Z8GdFdGdGe-Z9GdHdIdIe-Z:GdJdKdKe-Z;GdLdMdMe-ZGdRdSdSe-Z?GdTdUdUe-Z@GdVdWdWe+ZAGdXdYdYe+ZBGdZd[d[eBZCGd\d]d]eBZDGd^d_d_eBZEGd`dadae+ZFGdbdcdce+ZGGdddedee+ZHGdfdgdge+ZIGdhdidie+ZJGdjdkdke+ZKGdldmdme+ZLdnS)pz6SQL function API, factories, and built-in functions. ) annotation) operators)schema)sqltypes)util)ColumnCollection) Executable)_clone_literal_as_binds)_type_from_args) BindParameter)Cast) ClauseList) ColumnElement)ExtractFunctionFilter)Grouping)literal_columnOver WithinGroupAlias) FromClause)Select) VisitableType_defaultcCst|}|||<dS)zAssociate a callable with a particular func. name. This is normally called by _GenericMeta, but is also available by itself so that a non-Function construct can be associated with the :data:`.func` accessor (i.e. CAST, EXTRACT). N) _registry) identifierfnpackageregr&J/var/www/html/env/lib/python3.10/site-packages/sqlalchemy/sql/functions.pyregister_function(s r(cseZdZdZdZddZddZeddZe j d d Z d'd d Z ddZ ddZeddZddZefddZddZd(ddZddZdd Zd!d"Zd)d#d$Zd)fd%d& ZZS)*FunctionElementa<Base for SQL function-oriented constructs. .. seealso:: :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. r&cs.fdd|D}t|tjdd_dS)z/Construct a :class:`.FunctionElement`. cg|]}t|jqSr&r name.0cselfr&r' Iz,FunctionElement.__init__..Toperatorgroup_contentsN)rrcomma_op self_group clause_expr)r1clauseskwargsargsr&r0r'__init__Fs zFunctionElement.__init__cCs||||SN)_execute_function)r1 connection multiparamsparamsr&r&r'_execute_on_connectionNsz&FunctionElement._execute_on_connectioncCst|dS)a2The set of columns exported by this :class:`.FunctionElement`. Function objects currently have no result column names built in; this method returns a single-element column collection with an anonymously named column. An interim approach to providing named columns for a function as a FROM clause is to build a :func:`.select` with the desired columns:: from sqlalchemy.sql import column stmt = select([column('x'), column('y')]). select_from(func.myfunction()) N)rlabelr0r&r&r'columnsQszFunctionElement.columnscC|jjS)z}Return the underlying :class:`.ClauseList` which contains the arguments for this :class:`.FunctionElement`. )r9elementr0r&r&r'r:fszFunctionElement.clausesNcCst|||||dS)aProduce 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. ) partition_byorder_byrowsrange_r)r1rHrIrJrKr&r&r'overnszFunctionElement.overcGst|g|RS)ayProduce 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. .. versionadded:: 1.1 r)r1rIr&r&r' within_groups zFunctionElement.within_groupcGs|s|St|g|RS)aProduce 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) .. versionadded:: 1.0.0 .. seealso:: :class:`.FunctionFilter` :func:`.funcfilter` r)r1 criterionr&r&r'filterszFunctionElement.filtercCrFr>)r: _from_objectsr0r&r&r'rPszFunctionElement._from_objectscKs|jfSr>)r9r1r;r&r&r' get_childrenszFunctionElement.get_childrencKs,||jfi||_|tj|dSr>)r9_reset_exportedr)r:_reset)r1clonekwr&r&r'_copy_internalsszFunctionElement._copy_internalscCsdS)aFor 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. Nr&)r1rMr&r&r'within_group_types z!FunctionElement.within_group_typeFcCs t||S)a_Produce a :class:`.Alias` construct against this :class:`.FunctionElement`. This construct wraps the function in a named alias which is suitable for the FROM clause, in the style accepted for example by PostgreSQL. e.g.:: from sqlalchemy.sql import column stmt = select([column('data_view')]).\ select_from(SomeTable).\ select_from(func.unnest(SomeTable.data).alias('data_view') ) Would produce: .. sourcecode:: sql SELECT data_view FROM sometable, unnest(sometable.data) AS data_view .. versionadded:: 0.9.8 The :meth:`.FunctionElement.alias` method is now supported. Previously, this method's behavior was undefined and did not behave consistently across versions. r)r1r,flatr&r&r'aliass zFunctionElement.aliascCs&t|g}|jr|jdi|j}|S)zProduce a :func:`~.expression.select` construct against this :class:`.FunctionElement`. This is shorthand for:: s = select([function_element]) Nr&)r_execution_optionsexecution_options)r1sr&r&r'selects zFunctionElement.selectcCs|S)arExecute this :class:`.FunctionElement` against an embedded 'bind' and return a scalar value. This first calls :meth:`~.FunctionElement.select` to produce a SELECT construct. Note that :class:`.FunctionElement` can be passed to the :meth:`.Connectable.scalar` method of :class:`.Connection` or :class:`.Engine`. )r^executescalarr0r&r&r'r`s zFunctionElement.scalarcCs |S)aYExecute this :class:`.FunctionElement` against an embedded 'bind'. This first calls :meth:`~.FunctionElement.select` to produce a SELECT construct. Note that :class:`.FunctionElement` can be passed to the :meth:`.Connectable.execute` method of :class:`.Connection` or :class:`.Engine`. )r^r_r0r&r&r'r_s zFunctionElement.executecCstd|||jd|dS)NT)_compared_to_operator_compared_to_typeuniquetype_)r typer1r5objrdr&r&r' _bind_paramszFunctionElement._bind_paramcs2|tjurt|jtjrt|Stt|j |dS)N)against) rgetitem isinstancererARRAYrsuperr)r8)r1ri __class__r&r'r8 s zFunctionElement.self_group)NNNN)NFr>)__name__ __module__ __qualname____doc__ packagenamesr=rCpropertyrErmemoized_propertyr:rLrMrOrPrRr rWrXrZr^r`r_rhr8 __classcell__r&r&rnr'r)5s.         r)c@s(eZdZdZddZddZddZdS) _FunctionGeneratorz;Generate :class:`.Function` objects based on getattr calls.cKsg|_||_dSr>)_FunctionGenerator__namesopts)r1rzr&r&r'r=0s z_FunctionGenerator.__init__cCsj|drz|j|WStyt|w|dr"|dd}tdi|j}t|j|g|_|S)N___r&) startswith__dict__KeyErrorAttributeErrorendswithrxrzlistry)r1r,fr&r&r' __getattr__4s     z_FunctionGenerator.__getattr__cOs|j}||t|j}|dkr|j\}}n|dkr&d|jd}}nd}|dur>t||}|dur>||i|St|jdg|Rd|jddi|S)Nrrr r}r~rt)rzcopyupdatelenryr!getFunction)r1r/r;otokensr$fnamefuncr&r&r'__call__Bs*     z_FunctionGenerator.__call__N)rprqrrrsr=rrr&r&r&r'rx-s  rxF)groupc@s&eZdZdZdZddZdddZdS) raYDescribe a named SQL function. See the superclass :class:`.FunctionElement` for a description of public methods. .. seealso:: :data:`.func` - namespace which produces registered or ad-hoc :class:`.Function` instances. :class:`.GenericFunction` - allows creation of registered function types. functioncOsV|ddpg|_||_|dd|_t|dd|_tj |g|Ri|dS)zConstruct a :class:`.Function`. The :data:`.func` construct is normally used to construct new :class:`.Function` instances. rtNbindrd) poprtr,r_bindr to_instancerer)r=)r1r,r:rVr&r&r'r=s zFunction.__init__NcCst|j|||j|ddS)NT)rarbrdrc)r r,rerfr&r&r'rhszFunction._bind_paramr>)rprqrrrs__visit_name__r=rhr&r&r&r'rs rcseZdZfddZZS) _GenericMetacsrtj|jvr-|d||_}|d||_}|dd}d|vr'|d|_t|||t t | |||dS)Nr,r"r$r __return_type__) r Annotated__mro__rr,r"rrer(rmrr=)clsclsnamebasesclsdictr,r"r$rnr&r'r=s    z_GenericMeta.__init__)rprqrrr=rwr&r&rnr'rsrc@eZdZdZdZddZdS)GenericFunctionaDefine 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 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" 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" The above function will render as follows:: >>> print func.geo.buffer() ST_Buffer() Tcsv|dd}|durfdd|D}g_|dd_t|tjdd_t |ddp6t dd_ dS) N _parsed_argscr*r&r+r-r0r&r'r2r3z,GenericFunction.__init__..rTr4rdre) rrtrrrrr7r8r9rrgetattrre)r1r<r; parsed_argsr&r0r'r=s  zGenericFunction.__init__N)rprqrrrscoerce_argumentsr=r&r&r&r'rs> rcastextractc@s0eZdZdZeZdZddZe ddZ dS) next_valueaRepresent 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. cKs,t|tjs Jd|dd|_||_dS)Nz0next_value() accepts a Sequence object as input.r)rkrSequencerrsequence)r1seqrVr&r&r'r=:s znext_value.__init__cCsgSr>r&r0r&r&r'rPAsznext_value._from_objectsN) rprqrrrsrIntegerrer,r=rurPr&r&r&r'r-s rc@seZdZddZdS) AnsiFunctioncKstj|fi|dSr>rr=rQr&r&r'r=GszAnsiFunction.__init__N)rprqrrr=r&r&r&r'rFs rcs eZdZdZfddZZS)ReturnTypeFromArgszADefine a function whose return type is the same as its arguments.csDfdd|D}|dt|||d<ttj|i|dS)Ncr*r&r+r-r0r&r'r2Or3z/ReturnTypeFromArgs.__init__..rdr) setdefaultr rmrr=)r1r<r;rnr0r'r=NszReturnTypeFromArgs.__init__)rprqrrrsr=rwr&r&rnr'rKsrc@ eZdZdS)coalesceNrprqrrr&r&r&r'rUrc@r)maxNrr&r&r&r'rYrrc@r)minNrr&r&r&r'r]rrc@r)sumNrr&r&r&r'rarrc@eZdZejZdS)nowNrprqrrrDateTimerer&r&r&r're rc@r)concatNrprqrrrStringrer&r&r&r'rirrc@seZdZejZddZdS) char_lengthcKstj||fi|dSr>r)r1argr;r&r&r'r=pszchar_length.__init__N)rprqrrrrrer=r&r&r&r'rms rc@r)randomNrr&r&r&r'rtrrcs(eZdZdZejZdfdd ZZS)countaThe 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 Nc s,|durtd}tt|j|fi|dS)N*)rrmrr=)r1 expressionr;rnr&r'r=szcount.__init__r>) rprqrrrsrrrer=rwr&r&rnr'rxsrc@r) current_dateN)rprqrrrDaterer&r&r&r'rrrc@r) current_timeN)rprqrrrTimerer&r&r&r'rrrc@r)current_timestampNrr&r&r&r'rrrc@r) current_userNrr&r&r&r'rrrc@r) localtimeNrr&r&r&r'rrrc@r)localtimestampNrr&r&r&r'rrrc@r) session_userNrr&r&r&r'rrrc@r)sysdateNrr&r&r&r'rrrc@r)userNrr&r&r&r'rrrcs&eZdZdZejZfddZZS) array_aggasupport 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]]) .. versionadded:: 1.1 .. seealso:: :func:`.postgresql.array_agg` - PostgreSQL-specific version that returns :class:`.postgresql.ARRAY`, which has PG-specific operators added. cspdd|D}|dtj}d|vr't|}t|tjr!||d<n|||d<||d<tt|j|i|dS)NcSsg|]}t|qSr&r r-r&r&r'r2sz&array_agg.__init__.._default_array_typerdr)rrrlr rkrmrr=)r1r<r;default_array_typetype_from_argsrnr&r'r=s   zarray_agg.__init__) rprqrrrsrrlrer=rwr&r&rnr'rsrc@r) 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.FcCsB|jj}t|j}|jrt|jdkrt |dj S|dj S)Nrr}) r9rGsqlutilunwrap_order_byrIarray_for_multi_clauserr:rrlre)r1rM func_clausesrIr&r&r'rXs   zOrderedSetAgg.within_group_typeN)rprqrrrsrrXr&r&r&r'rs rc@eZdZdZdS)modea*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. .. versionadded:: 1.1 Nrprqrrrsr&r&r&r'rrc@eZdZdZdZdS)percentile_contaimplement 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. .. versionadded:: 1.1 TNrprqrrrsrr&r&r&r'r rc@r)percentile_discaimplement 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. .. versionadded:: 1.1 TNrr&r&r&r'r rrc@eZdZdZeZdS)ranka!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`. .. versionadded:: 1.1 Nrprqrrrsrrrer&r&r&r'r rc@r) dense_ranka'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`. .. versionadded:: 1.1 Nrr&r&r&r'r,rrc@r) percent_ranka)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`. .. versionadded:: 1.1 NrprqrrrsrNumericrer&r&r&r'r;rrc@r) cume_dista&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`. .. versionadded:: 1.1 Nrr&r&r&r'rJrrc@r)cubeaNImplement the ``CUBE`` grouping operation. This function is used as part of the GROUP BY of a statement, e.g. :meth:`.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 Nrr&r&r&r'rYrrc@r)rollupaNImplement the ``ROLLUP`` grouping operation. This function is used as part of the GROUP BY of a statement, e.g. :meth:`.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 Nrr&r&r&r'rhrrc@r) grouping_setsa%Implement the ``GROUPING SETS`` grouping operation. This function is used as part of the GROUP BY of a statement, e.g. :meth:`.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 Nrr&r&r&r'rwrrN)r )Mrsrrrrrrbaserrelementsr r r r rrrrrrrrr selectablerrrvisitorsr defaultdictdictr!r(r)objectrxrmodifierrrwith_metaclassrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr&r&r&r's                            y, E+ O  %