diff options
Diffstat (limited to 'lib/sqlalchemy/sql/functions.py')
| -rw-r--r-- | lib/sqlalchemy/sql/functions.py | 382 |
1 files changed, 343 insertions, 39 deletions
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index a9ea98d04..78f7ead2e 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -17,6 +17,7 @@ from . import sqltypes from . import util as sqlutil from .base import ColumnCollection from .base import Executable +from .base import Generative from .base import HasMemoized from .elements import _type_from_args from .elements import BinaryExpression @@ -28,11 +29,12 @@ from .elements import Extract from .elements import FunctionFilter from .elements import Grouping from .elements import literal_column +from .elements import NamedColumn from .elements import Over from .elements import WithinGroup -from .selectable import Alias from .selectable import FromClause from .selectable import Select +from .selectable import TableValuedAlias from .visitors import InternalTraversal from .visitors import TraversibleType from .. import util @@ -63,7 +65,7 @@ def register_function(identifier, fn, package="_default"): reg[identifier] = fn -class FunctionElement(Executable, ColumnElement, FromClause): +class FunctionElement(Executable, ColumnElement, FromClause, Generative): """Base for SQL function-oriented constructs. .. seealso:: @@ -80,11 +82,17 @@ class FunctionElement(Executable, ColumnElement, FromClause): """ - _traverse_internals = [("clause_expr", InternalTraversal.dp_clauseelement)] + _traverse_internals = [ + ("clause_expr", InternalTraversal.dp_clauseelement), + ("_with_ordinality", InternalTraversal.dp_boolean), + ("_table_value_type", InternalTraversal.dp_has_cache_key), + ] packagenames = () _has_args = False + _with_ordinality = False + _table_value_type = None def __init__(self, *clauses, **kwargs): r"""Construct a :class:`.FunctionElement`. @@ -123,27 +131,169 @@ class FunctionElement(Executable, ColumnElement, FromClause): self, multiparams, params, execution_options ) + def scalar_table_valued(self, name, type_=None): + """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.:: + + >>> from sqlalchemy import func, select + >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key") + >>> print(select(fn)) + 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` + + """ # noqa E501 + + return ScalarFunctionColumn(self, name, type_) + + def table_valued(self, *expr, **kw): + """Return a :class:`_sql.TableValuedAlias` representation of this + :class:`_functions.FunctionElement` with table-valued expressions added. + + e.g.:: + + >>> fn = ( + ... func.generate_series(1, 5). + ... table_valued("value", "start", "stop", "step") + ... ) + + >>> print(select(fn)) + 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 + + >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2)) + 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 + + A WITH ORDINALITY expression may be generated by passing the keyword + argument "with_ordinality":: + + >>> fn = func.generate_series(4, 1, -1).table_valued("gen", with_ordinality="ordinality") + >>> print(select(fn)) + 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`. + + .. 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, ...)`` + + """ # noqa 501 + + new_func = self._generate() + + with_ordinality = kw.pop("with_ordinality", None) + name = kw.pop("name", None) + + if with_ordinality: + expr += (with_ordinality,) + new_func._with_ordinality = True + + new_func.type = new_func._table_value_type = sqltypes.TableValueType( + *expr + ) + + return new_func.alias(name=name) + + def column_valued(self, name=None): + """Return this :class:`_functions.FunctionElement` as a column expression that + selects from itself as a FROM clause. + + E.g.:: + + >>> from sqlalchemy import select, func + >>> gs = func.generate_series(1, 5, -1).column_valued() + >>> print(select(gs)) + 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 + + + .. 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` + + """ # noqa 501 + + return self.alias(name=name).column + @property def columns(self): r"""The 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. + This is a placeholder collection that allows the function to be + placed in the FROM clause of a statement:: - An interim approach to providing named columns for a function - as a FROM clause is to build a :func:`_expression.select` with the - desired columns:: + >>> from sqlalchemy import column, select, func + >>> stmt = select(column('x'), column('y')).select_from(func.myfunction()) + >>> print(stmt) + SELECT x, y FROM myfunction() - from sqlalchemy.sql import column + 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. - stmt = select(column('x'), column('y')).\ - select_from(func.myfunction()) + .. seealso:: + :meth:`_functions.FunctionElement.table_valued` - generates table-valued + SQL function expressions. - """ - col = self.label(None) - return ColumnCollection(columns=[(col.key, col)]) + """ # noqa E501 + if self.type._is_table_value: + cols = self.type._elements + else: + cols = [self.label(None)] + + return ColumnCollection(columns=[(col.key, col) for col in cols]) @HasMemoized.memoized_attribute def clauses(self): @@ -170,6 +320,12 @@ class FunctionElement(Executable, ColumnElement, FromClause): See :func:`_expression.over` for a full description. + .. seealso:: + + :func:`_expression.over` + + :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial` + """ return Over( self, @@ -191,6 +347,12 @@ class FunctionElement(Executable, ColumnElement, FromClause): .. versionadded:: 1.1 + .. seealso:: + + :ref:`tutorial_functions_within_group` - + in the :ref:`unified_tutorial` + + """ return WithinGroup(self, *order_by) @@ -213,6 +375,9 @@ class FunctionElement(Executable, ColumnElement, FromClause): .. seealso:: + :ref:`tutorial_functions_within_group` - + in the :ref:`unified_tutorial` + :class:`.FunctionFilter` :func:`.funcfilter` @@ -226,6 +391,9 @@ class FunctionElement(Executable, ColumnElement, FromClause): def as_comparison(self, left_index, right_index): """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:: @@ -250,9 +418,8 @@ class FunctionElement(Executable, ColumnElement, FromClause): 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`. + with the :paramref:`_orm.relationship.primaryjoin` parameter. The + return value is a containment object called :class:`.FunctionAsBinary`. An ORM example is as follows:: @@ -286,6 +453,11 @@ class FunctionElement(Executable, ColumnElement, FromClause): .. versionadded:: 1.3 + .. seealso:: + + :ref:`relationship_custom_operator_sql_function` - + example use within the ORM + """ return FunctionAsBinary(self, left_index, right_index) @@ -305,37 +477,65 @@ class FunctionElement(Executable, ColumnElement, FromClause): return None - def alias(self, name=None, flat=False): + def alias(self, name=None): r"""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. + 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:`_function.FunctionElement.table_valued` method first to + establish named columns. e.g.:: - from sqlalchemy.sql import column + >>> from sqlalchemy import func, select, column + >>> data_view = func.unnest([1, 2, 3]).alias("data_view") + >>> print(select(data_view.column)) + SELECT data_view + FROM unnest(:unnest_1) AS data_view - stmt = select(column('data_view')).\ - select_from(SomeTable).\ - select_from(func.unnest(SomeTable.data).alias('data_view') - ) + The :meth:`_functions.FunctionElement.column_valued` method provides + a shortcut for the above pattern:: - Would produce: + >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view") + >>> print(select(data_view)) + SELECT data_view + FROM unnest(:unnest_1) AS data_view - .. sourcecode:: sql + .. versionadded:: 1.4.0b2 Added the ``.column`` accessor - SELECT data_view - FROM sometable, unnest(sometable.data) AS data_view + .. 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` - .. 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. """ - return Alias._construct(self, name) + return TableValuedAlias._construct( + self, name, table_value_type=self.type + ) def select(self): """Produce a :func:`_expression.select` construct @@ -351,6 +551,14 @@ class FunctionElement(Executable, ColumnElement, FromClause): s = s.execution_options(**self._execution_options) return s + @util.deprecated_20( + ":meth:`.FunctionElement.scalar`", + alternative="Scalar execution in SQLAlchemy 2.0 is performed " + "by the :meth:`_engine.Connection.scalar` method of " + ":class:`_engine.Connection`, " + "or in the ORM by the :meth:`.Session.scalar` method of " + ":class:`.Session`.", + ) def scalar(self): """Execute this :class:`.FunctionElement` against an embedded 'bind' and return a scalar value. @@ -365,6 +573,14 @@ class FunctionElement(Executable, ColumnElement, FromClause): """ return self.select().execute().scalar() + @util.deprecated_20( + ":meth:`.FunctionElement.execute`", + alternative="All statement execution in SQLAlchemy 2.0 is performed " + "by the :meth:`_engine.Connection.execute` method of " + ":class:`_engine.Connection`, " + "or in the ORM by the :meth:`.Session.execute` method of " + ":class:`.Session`.", + ) def execute(self): """Execute this :class:`.FunctionElement` against an embedded 'bind'. @@ -441,6 +657,24 @@ class FunctionAsBinary(BinaryExpression): self.sql_function.clauses.clauses[self.right_index - 1] = value +class ScalarFunctionColumn(NamedColumn): + __visit_name__ = "scalar_function_column" + + _traverse_internals = [ + ("name", InternalTraversal.dp_anon_name), + ("type", InternalTraversal.dp_type), + ("fn", InternalTraversal.dp_clauseelement), + ] + + is_literal = False + table = None + + def __init__(self, fn, name, type_=None): + self.fn = fn + self.name = name + self.type = sqltypes.to_instance(type_) + + class _FunctionGenerator(object): """Generate SQL function expressions. @@ -586,10 +820,9 @@ class Function(FunctionElement): func.mypackage.some_function(col1, col2) - .. seealso:: - :ref:`coretutorial_functions` + :ref:`tutorial_functions` - in the :ref:`unified_tutorial` :data:`.func` - namespace which produces registered or ad-hoc :class:`.Function` instances. @@ -607,6 +840,23 @@ class Function(FunctionElement): ("type", InternalTraversal.dp_type), ] + type = sqltypes.NULLTYPE + """A :class:`_types.TypeEngine` object which refers to the SQL return + type represented by this SQL function. + + This datatype may be configured when generating a + :class:`_functions.Function` object by passing the + :paramref:`_functions.Function.type_` parameter, e.g.:: + + >>> select(func.lower("some VALUE", type_=String)) + + The small number of built-in classes of :class:`_functions.Function` come + with a built-in datatype that's appropriate to the class of function and + its arguments. For functions that aren't known, the type defaults to the + "null type". + + """ + @util.deprecated_params( bind=( "2.0", @@ -825,6 +1075,8 @@ class next_value(GenericFunction): class AnsiFunction(GenericFunction): + """Define a function in "ansi" format, which doesn't render parenthesis.""" + inherit_cache = True def __init__(self, *args, **kwargs): @@ -856,29 +1108,61 @@ class coalesce(ReturnTypeFromArgs): inherit_cache = True -class max(ReturnTypeFromArgs): # noqa +class max(ReturnTypeFromArgs): # noqa A001 + """The SQL MAX() aggregate function.""" + inherit_cache = True -class min(ReturnTypeFromArgs): # noqa +class min(ReturnTypeFromArgs): # noqa A001 + """The SQL MIN() aggregate function.""" + inherit_cache = True -class sum(ReturnTypeFromArgs): # noqa +class sum(ReturnTypeFromArgs): # noqa A001 + """The SQL SUM() aggregate function.""" + inherit_cache = True -class now(GenericFunction): # noqa +class now(GenericFunction): + """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``. + + """ + type = sqltypes.DateTime inherit_cache = True class concat(GenericFunction): + """The SQL CONCAT() function, which concatenates strings. + + E.g.:: + + >>> print(select(func.concat('a', 'b'))) + 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 :: + + >>> print(select(literal("a") + "b")) + SELECT :param_1 || :param_2 AS anon_1 + + + """ + type = sqltypes.String inherit_cache = True class char_length(GenericFunction): + """The CHAR_LENGTH() SQL function.""" + type = sqltypes.Integer inherit_cache = True @@ -887,6 +1171,8 @@ class char_length(GenericFunction): class random(GenericFunction): + """The RANDOM() SQL function.""" + _has_args = True inherit_cache = True @@ -922,46 +1208,64 @@ class count(GenericFunction): class current_date(AnsiFunction): + """The CURRENT_DATE() SQL function.""" + type = sqltypes.Date inherit_cache = True class current_time(AnsiFunction): + """The CURRENT_TIME() SQL function.""" + type = sqltypes.Time inherit_cache = True class current_timestamp(AnsiFunction): + """The CURRENT_TIMESTAMP() SQL function.""" + type = sqltypes.DateTime inherit_cache = True class current_user(AnsiFunction): + """The CURRENT_USER() SQL function.""" + type = sqltypes.String inherit_cache = True class localtime(AnsiFunction): + """The localtime() SQL function.""" + type = sqltypes.DateTime inherit_cache = True class localtimestamp(AnsiFunction): + """The localtimestamp() SQL function.""" + type = sqltypes.DateTime inherit_cache = True class session_user(AnsiFunction): + """The SESSION_USER() SQL function.""" + type = sqltypes.String inherit_cache = True class sysdate(AnsiFunction): + """The SYSDATE() SQL function.""" + type = sqltypes.DateTime inherit_cache = True class user(AnsiFunction): + """The USER() SQL function.""" + type = sqltypes.String inherit_cache = True |