diff options
Diffstat (limited to 'lib/sqlalchemy/sql')
| -rw-r--r-- | lib/sqlalchemy/sql/base.py | 10 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/coercions.py | 21 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 64 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 175 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/functions.py | 382 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/roles.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 178 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/sqltypes.py | 64 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/traversals.py | 5 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/type_api.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/visitors.py | 6 |
12 files changed, 785 insertions, 128 deletions
diff --git a/lib/sqlalchemy/sql/base.py b/lib/sqlalchemy/sql/base.py index fe9455fb9..c76c9ae14 100644 --- a/lib/sqlalchemy/sql/base.py +++ b/lib/sqlalchemy/sql/base.py @@ -935,13 +935,11 @@ class Executable(roles.CoerceTextStatementRole, Generative): @util.deprecated_20( ":meth:`.Executable.scalar`", - alternative="All statement execution in SQLAlchemy 2.0 is performed " - "by the :meth:`_engine.Connection.execute` method of " + 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.execute` method of " - ":class:`.Session`; the :meth:`_future.Result.scalar` " - "method can then be " - "used to return a scalar result.", + "or in the ORM by the :meth:`.Session.scalar` method of " + ":class:`.Session`.", ) def scalar(self, *multiparams, **params): """Compile and execute this :class:`.Executable`, returning the diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py index 3b972be41..d4b208c56 100644 --- a/lib/sqlalchemy/sql/coercions.py +++ b/lib/sqlalchemy/sql/coercions.py @@ -437,6 +437,20 @@ class ExpressionElementImpl(_ColumnCoercions, RoleImpl): except exc.ArgumentError as err: self._raise_for_expected(element, err=err) + def _raise_for_expected(self, element, argname=None, resolved=None, **kw): + if isinstance(element, roles.AnonymizedFromClauseRole): + advice = ( + "To create a " + "column expression from a FROM clause row " + "as a whole, use the .table_valued() method." + ) + else: + advice = None + + return super(ExpressionElementImpl, self)._raise_for_expected( + element, argname=argname, resolved=resolved, advice=advice, **kw + ) + class BinaryElementImpl(ExpressionElementImpl, RoleImpl): @@ -603,6 +617,13 @@ class ColumnArgumentOrKeyImpl(_ReturnsStringKey, RoleImpl): __slots__ = () +class StrAsPlainColumnImpl(_CoerceLiterals, RoleImpl): + __slots__ = () + + def _text_coercion(self, element, argname=None): + return elements.ColumnClause(element) + + class ByOfImpl(_CoerceLiterals, _ColumnCoercions, RoleImpl, roles.ByOfRole): __slots__ = () diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 3925b251d..696b38e64 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -494,6 +494,12 @@ class Compiled(object): else: raise exc.ObjectNotExecutableError(self.statement) + def visit_unsupported_compilation(self, element, err): + util.raise_( + exc.UnsupportedCompilationError(self, type(element)), + replace_context=err, + ) + @property def sql_compiler(self): """Return a Compiled that is capable of processing SQL expressions. @@ -1718,13 +1724,18 @@ class SQLCompiler(Compiled): extract.expr._compiler_dispatch(self, **kwargs), ) + def visit_scalar_function_column(self, element, **kw): + compiled_fn = self.visit_function(element.fn, **kw) + compiled_col = self.visit_column(element, **kw) + return "(%s).%s" % (compiled_fn, compiled_col) + def visit_function(self, func, add_to_result_map=None, **kwargs): if add_to_result_map is not None: add_to_result_map(func.name, func.name, (), func.type) disp = getattr(self, "visit_%s_func" % func.name.lower(), None) if disp: - return disp(func, **kwargs) + text = disp(func, **kwargs) else: name = FUNCTIONS.get(func.__class__, None) if name: @@ -1739,7 +1750,7 @@ class SQLCompiler(Compiled): else name ) name = name + "%(expr)s" - return ".".join( + text = ".".join( [ ( self.preparer.quote(tok) @@ -1752,6 +1763,10 @@ class SQLCompiler(Compiled): + [name] ) % {"expr": self.function_argspec(func, **kwargs)} + if func._with_ordinality: + text += " WITH ORDINALITY" + return text + def visit_next_value_func(self, next_value, **kw): return self.visit_sequence(next_value.sequence) @@ -2533,6 +2548,15 @@ class SQLCompiler(Compiled): else: return self.preparer.format_alias(cte, cte_name) + def visit_table_valued_alias(self, element, **kw): + if element._is_lateral: + return self.visit_lateral(element, **kw) + else: + return self.visit_alias(element, **kw) + + def visit_table_valued_column(self, element, **kw): + return self.visit_column(element, **kw) + def visit_alias( self, alias, @@ -2584,6 +2608,24 @@ class SQLCompiler(Compiled): ret = inner + self.get_render_as_alias_suffix( self.preparer.format_alias(alias, alias_name) ) + + if alias._supports_derived_columns and alias._render_derived: + ret += "(%s)" % ( + ", ".join( + "%s%s" + % ( + col.name, + " %s" + % self.dialect.type_compiler.process( + col.type, **kwargs + ) + if alias._render_derived_w_types + else "", + ) + for col in alias.c + ) + ) + if fromhints and alias in fromhints: ret = self.format_from_hint_text( ret, alias, fromhints[alias], iscrud @@ -2600,9 +2642,9 @@ class SQLCompiler(Compiled): kw["subquery"] = True return self.visit_alias(subquery, **kw) - def visit_lateral(self, lateral, **kw): + def visit_lateral(self, lateral_, **kw): kw["lateral"] = True - return "LATERAL %s" % self.visit_alias(lateral, **kw) + return "LATERAL %s" % self.visit_alias(lateral_, **kw) def visit_tablesample(self, tablesample, asfrom=False, **kw): text = "%s TABLESAMPLE %s" % ( @@ -3737,6 +3779,20 @@ class StrSQLCompiler(SQLCompiler): def _fallback_column_name(self, column): return "<name unknown>" + @util.preload_module("sqlalchemy.engine.url") + def visit_unsupported_compilation(self, element, err, **kw): + if element.stringify_dialect != "default": + url = util.preloaded.engine_url + dialect = url.URL.create(element.stringify_dialect).get_dialect()() + + compiler = dialect.statement_compiler(dialect, None) + if not isinstance(compiler, StrSQLCompiler): + return compiler.process(element) + + return super(StrSQLCompiler, self).visit_unsupported_compilation( + element, err + ) + def visit_getitem_binary(self, binary, operator, **kw): return "%s[%s]" % ( self.process(binary.left, **kw), diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index a9f21cd5f..1bdef1932 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -3856,6 +3856,8 @@ class Over(ColumnElement): .. seealso:: + :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial` + :data:`.expression.func` :func:`_expression.within_group` @@ -4007,6 +4009,9 @@ class WithinGroup(ColumnElement): .. seealso:: + :ref:`tutorial_functions_within_group` - in the + :ref:`unified_tutorial` + :data:`.expression.func` :func:`_expression.over` @@ -4104,8 +4109,10 @@ class FunctionFilter(ColumnElement): .. seealso:: - :meth:`.FunctionElement.filter` + :ref:`tutorial_functions_within_group` - in the + :ref:`unified_tutorial` + :meth:`.FunctionElement.filter` """ self.func = func @@ -4317,11 +4324,82 @@ class Label(roles.LabeledColumnExprRole, ColumnElement): return self.key, e +class NamedColumn(ColumnElement): + is_literal = False + table = None + + def _compare_name_for_result(self, other): + return (hasattr(other, "name") and self.name == other.name) or ( + hasattr(other, "_label") and self._label == other._label + ) + + @util.memoized_property + def description(self): + if util.py3k: + return self.name + else: + return self.name.encode("ascii", "backslashreplace") + + @HasMemoized.memoized_attribute + def _key_label(self): + if self.key != self.name: + return self._gen_label(self.key) + else: + return self._label + + @HasMemoized.memoized_attribute + def _label(self): + return self._gen_label(self.name) + + @HasMemoized.memoized_attribute + def _render_label_in_columns_clause(self): + return True + + def _gen_label(self, name, dedupe_on_key=True): + return name + + def _bind_param(self, operator, obj, type_=None, expanding=False): + return BindParameter( + self.key, + obj, + _compared_to_operator=operator, + _compared_to_type=self.type, + type_=type_, + unique=True, + expanding=expanding, + ) + + def _make_proxy( + self, + selectable, + name=None, + name_is_truncatable=False, + disallow_is_literal=False, + **kw + ): + c = ColumnClause( + coercions.expect(roles.TruncatedLabelRole, name or self.name) + if name_is_truncatable + else (name or self.name), + type_=self.type, + _selectable=selectable, + is_literal=False, + ) + c._propagate_attrs = selectable._propagate_attrs + if name is None: + c.key = self.key + c._proxies = [self] + if selectable._is_clone_of is not None: + c._is_clone_of = selectable._is_clone_of.columns.get(c.key) + return c.key, c + + class ColumnClause( roles.DDLReferredColumnRole, roles.LabeledColumnExprRole, + roles.StrAsPlainColumnRole, Immutable, - ColumnElement, + NamedColumn, ): """Represents a column expression from any textual string. @@ -4360,6 +4438,9 @@ class ColumnClause( """ + table = None + is_literal = False + __visit_name__ = "column" _traverse_internals = [ @@ -4470,27 +4551,6 @@ class ColumnClause( self.type = type_api.to_instance(type_) self.is_literal = is_literal - def _compare_name_for_result(self, other): - if ( - self.is_literal - or self.table is None - or self.table._is_textual - or not hasattr(other, "proxy_set") - or ( - isinstance(other, ColumnClause) - and ( - other.is_literal - or other.table is None - or other.table._is_textual - ) - ) - ): - return (hasattr(other, "name") and self.name == other.name) or ( - hasattr(other, "_label") and self._label == other._label - ) - else: - return other.proxy_set.intersection(self.proxy_set) - def get_children(self, column_tables=False, **kw): # override base get_children() to not return the Table # or selectable that is parent to this column. Traversals @@ -4505,24 +4565,6 @@ class ColumnClause( else: return [] - @util.memoized_property - def description(self): - if util.py3k: - return self.name - else: - return self.name.encode("ascii", "backslashreplace") - - @HasMemoized.memoized_attribute - def _key_label(self): - if self.key != self.name: - return self._gen_label(self.key) - else: - return self._label - - @HasMemoized.memoized_attribute - def _label(self): - return self._gen_label(self.name) - @HasMemoized.memoized_attribute def _render_label_in_columns_clause(self): return self.table is not None @@ -4531,6 +4573,27 @@ class ColumnClause( def _ddl_label(self): return self._gen_label(self.name, dedupe_on_key=False) + def _compare_name_for_result(self, other): + if ( + self.is_literal + or self.table is None + or self.table._is_textual + or not hasattr(other, "proxy_set") + or ( + isinstance(other, ColumnClause) + and ( + other.is_literal + or other.table is None + or other.table._is_textual + ) + ) + ): + return (hasattr(other, "name") and self.name == other.name) or ( + hasattr(other, "_label") and self._label == other._label + ) + else: + return other.proxy_set.intersection(self.proxy_set) + def _gen_label(self, name, dedupe_on_key=True): t = self.table if self.is_literal: @@ -4575,17 +4638,6 @@ class ColumnClause( else: return name - def _bind_param(self, operator, obj, type_=None, expanding=False): - return BindParameter( - self.key, - obj, - _compared_to_operator=operator, - _compared_to_type=self.type, - type_=type_, - unique=True, - expanding=expanding, - ) - def _make_proxy( self, selectable, @@ -4627,6 +4679,25 @@ class ColumnClause( return c.key, c +class TableValuedColumn(NamedColumn): + __visit_name__ = "table_valued_column" + + _traverse_internals = [ + ("name", InternalTraversal.dp_anon_name), + ("type", InternalTraversal.dp_type), + ("scalar_alias", InternalTraversal.dp_clauseelement), + ] + + def __init__(self, scalar_alias, type_): + self.scalar_alias = scalar_alias + self.key = self.name = scalar_alias.name + self.type = type_ + + @property + def _from_objects(self): + return [self.scalar_alias] + + class CollationClause(ColumnElement): __visit_name__ = "collation" diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 3eb1443a3..46f13fedd 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -34,6 +34,7 @@ __all__ = [ "Select", "Selectable", "TableClause", + "TableValuedAlias", "Update", "Values", "alias", @@ -172,6 +173,7 @@ from .selectable import Subquery # noqa from .selectable import subquery # noqa from .selectable import TableClause # noqa from .selectable import TableSample # noqa +from .selectable import TableValuedAlias # noqa from .selectable import TextAsFrom # noqa from .selectable import TextualSelect # noqa from .selectable import Values # noqa 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 diff --git a/lib/sqlalchemy/sql/roles.py b/lib/sqlalchemy/sql/roles.py index 2c4ff75c4..52743bd50 100644 --- a/lib/sqlalchemy/sql/roles.py +++ b/lib/sqlalchemy/sql/roles.py @@ -44,6 +44,10 @@ class ColumnArgumentOrKeyRole(ColumnArgumentRole): _role_name = "Column expression or string key" +class StrAsPlainColumnRole(ColumnArgumentRole): + _role_name = "Column expression or string key" + + class ColumnListRole(SQLRole): """Elements suitable for forming comma separated lists of expressions.""" diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 1bd4f5d81..a273e0c90 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -51,6 +51,7 @@ from .elements import ColumnClause from .elements import GroupedElement from .elements import Grouping from .elements import literal_column +from .elements import TableValuedColumn from .elements import UnaryExpression from .visitors import InternalTraversal from .. import exc @@ -623,6 +624,34 @@ class FromClause(roles.AnonymizedFromClauseRole, Selectable): return Alias._construct(self, name) + @util.preload_module("sqlalchemy.sql.sqltypes") + def table_valued(self): + """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.:: + + >>> 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) + 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` + + """ + return TableValuedColumn(self, type_api.TABLEVALUE) + def tablesample(self, sampling, name=None, seed=None): """Return a TABLESAMPLE alias of this :class:`_expression.FromClause`. @@ -1519,6 +1548,8 @@ class AliasedReturnsRows(NoInit, FromClause): _is_from_container = True named_with_column = True + _supports_derived_columns = False + _traverse_internals = [ ("element", InternalTraversal.dp_clauseelement), ("name", InternalTraversal.dp_anon_name), @@ -1678,6 +1709,151 @@ class Alias(roles.DMLTableRole, AliasedReturnsRows): ).alias(name=name, flat=flat) +class TableValuedAlias(Alias): + """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.:: + + >>> from sqlalchemy import select, func + >>> fn = func.json_array_elements_text('["one", "two", "three"]').table_valued("value") + >>> print(select(fn.c.value)) + 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` + + """ # noqa E501 + + __visit_name__ = "table_valued_alias" + + _supports_derived_columns = True + _render_derived = False + _render_derived_w_types = False + + _traverse_internals = [ + ("element", InternalTraversal.dp_clauseelement), + ("name", InternalTraversal.dp_anon_name), + ("_tableval_type", InternalTraversal.dp_type), + ("_render_derived", InternalTraversal.dp_boolean), + ("_render_derived_w_types", InternalTraversal.dp_boolean), + ] + + def _init(self, selectable, name=None, table_value_type=None): + super(TableValuedAlias, self)._init(selectable, name=name) + + self._tableval_type = ( + type_api.TABLEVALUE + if table_value_type is None + else table_value_type + ) + + @HasMemoized.memoized_attribute + def column(self): + """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.:: + + >>> print(select(func.some_func().table_valued("value").column)) + SELECT anon_1 FROM some_func() AS anon_1 + + .. seealso:: + + :meth:`_functions.FunctionElement.column_valued` + + """ + + return TableValuedColumn(self, self._tableval_type) + + def alias(self, name=None): + """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. + + """ + + tva = TableValuedAlias._construct(self, name=name) + if self._render_derived: + tva._render_derived = True + tva._render_derived_w_types = self._render_derived_w_types + return tva + + def lateral(self, name=None): + """Return a new :class:`_sql.TableValuedAlias` with the lateral flag set, + so that it renders as LATERAL. + + .. seealso:: + + :func:`_expression.lateral` + + """ + tva = self.alias(name=name) + tva._is_lateral = True + return tva + + def render_derived(self, name=None, with_types=False): + """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.:: + + >>> print( + ... select( + ... func.unnest(array(["one", "two", "three"])). + table_valued("x", with_ordinality="o").render_derived() + ... ) + ... ) + 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):: + + >>> 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) + ... ) + ... ) + 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. + + """ # noqa E501 + + # note: don't use the @_generative system here, keep a reference + # to the original object. otherwise you can have re-use of the + # python id() of the original which can cause name conflicts if + # a new anon-name grabs the same identifier as the local anon-name + # (just saw it happen on CI) + new_alias = TableValuedAlias._construct(self, name=name) + new_alias._render_derived = True + new_alias._render_derived_w_types = with_types + return new_alias + + class Lateral(AliasedReturnsRows): """Represent a LATERAL subquery. @@ -2473,7 +2649,7 @@ class Values(Generative, FromClause): value_expr = values( column('id', Integer), - column('name', Integer), + column('name', String), name="my_values" ).data( [(1, 'name1'), (2, 'name2'), (3, 'name3')] diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index d20c8168d..3484b3ac8 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -26,6 +26,8 @@ from .elements import _defer_name from .elements import quoted_name from .elements import Slice from .elements import TypeCoerce as type_coerce # noqa +from .traversals import HasCacheKey +from .traversals import InternalTraversal from .type_api import Emulated from .type_api import NativeForEmulated # noqa from .type_api import to_instance @@ -2049,17 +2051,18 @@ class JSON(Indexable, TypeEngine): JSON types. Since it supports JSON SQL operations, it only works on backends that have an actual JSON type, currently: - * PostgreSQL - see :class:`_postgresql.JSON` and - :class:`_postgresql.JSONB` for backend-specific notes + * PostgreSQL - see :class:`sqlalchemy.dialects.postgresql.JSON` and + :class:`sqlalchemy.dialects.postgresql.JSONB` for backend-specific + notes - * MySQL as of version 5.7 (MariaDB as of the 10.2 series does not) - see - :class:`_mysql.JSON` for backend-specific notes + * MySQL - see + :class:`sqlalchemy.dialects.mysql.JSON` for backend-specific notes - * SQLite as of version 3.9 - see :class:`_sqlite.JSON` for - backend-specific notes + * SQLite as of version 3.9 - see + :class:`sqlalchemy.dialects.sqlite.JSON` for backend-specific notes - * Microsoft SQL Server 2016 and later - see :class:`_mssql.JSON` for - backend-specific notes + * Microsoft SQL Server 2016 and later - see + :class:`sqlalchemy.dialects.mssql.JSON` for backend-specific notes :class:`_types.JSON` is part of the Core in support of the growing popularity of native JSON datatypes. @@ -2102,9 +2105,10 @@ class JSON(Indexable, TypeEngine): .. versionadded:: 1.3.11 Additional operations may be available from the dialect-specific versions - of :class:`_types.JSON`, such as :class:`_postgresql.JSON` and - :class:`_postgresql.JSONB` which both offer additional PostgreSQL-specific - operations. + of :class:`_types.JSON`, such as + :class:`sqlalchemy.dialects.postgresql.JSON` and + :class:`sqlalchemy.dialects.postgresql.JSONB` which both offer additional + PostgreSQL-specific operations. **Casting JSON Elements to Other Types** @@ -2219,13 +2223,13 @@ class JSON(Indexable, TypeEngine): .. seealso:: - :class:`_postgresql.JSON` + :class:`sqlalchemy.dialects.postgresql.JSON` - :class:`_postgresql.JSONB` + :class:`sqlalchemy.dialects.postgresql.JSONB` - :class:`.mysql.JSON` + :class:`sqlalchemy.dialects.mysql.JSON` - :class:`_sqlite.JSON` + :class:`sqlalchemy.dialects.sqlite.JSON` .. versionadded:: 1.1 @@ -2579,11 +2583,11 @@ class ARRAY(SchemaEventTarget, Indexable, Concatenable, TypeEngine): """Represent a SQL Array type. .. note:: This type serves as the basis for all ARRAY operations. - However, currently **only the PostgreSQL backend has support - for SQL arrays in SQLAlchemy**. It is recommended to use the - :class:`_postgresql.ARRAY` type directly when using ARRAY types - with PostgreSQL, as it provides additional operators specific - to that backend. + However, currently **only the PostgreSQL backend has support for SQL + arrays in SQLAlchemy**. It is recommended to use the PostgreSQL-specific + :class:`sqlalchemy.dialects.postgresql.ARRAY` type directly when using + ARRAY types with PostgreSQL, as it provides additional operators + specific to that backend. :class:`_types.ARRAY` is part of the Core in support of various SQL standard functions such as :class:`_functions.array_agg` @@ -2665,7 +2669,7 @@ class ARRAY(SchemaEventTarget, Indexable, Concatenable, TypeEngine): .. seealso:: - :class:`_postgresql.ARRAY` + :class:`sqlalchemy.dialects.postgresql.ARRAY` """ @@ -3110,6 +3114,22 @@ class NullType(TypeEngine): comparator_factory = Comparator +class TableValueType(HasCacheKey, TypeEngine): + """Refers to a table value type.""" + + _is_table_value = True + + _traverse_internals = [ + ("_elements", InternalTraversal.dp_clauseelement_list), + ] + + def __init__(self, *elements): + self._elements = [ + coercions.expect(roles.StrAsPlainColumnRole, elem) + for elem in elements + ] + + class MatchType(Boolean): """Refers to the return type of the MATCH operator. @@ -3131,6 +3151,7 @@ BOOLEANTYPE = Boolean() STRINGTYPE = String() INTEGERTYPE = Integer() MATCHTYPE = MatchType() +TABLEVALUE = TableValueType() _type_map = { int: Integer(), @@ -3183,5 +3204,6 @@ type_api.INTEGERTYPE = INTEGERTYPE type_api.NULLTYPE = NULLTYPE type_api.MATCHTYPE = MATCHTYPE type_api.INDEXABLE = Indexable +type_api.TABLEVALUE = TABLEVALUE type_api._resolve_value_to_type = _resolve_value_to_type TypeEngine.Comparator.BOOLEANTYPE = BOOLEANTYPE diff --git a/lib/sqlalchemy/sql/traversals.py b/lib/sqlalchemy/sql/traversals.py index 2887813ad..51a531000 100644 --- a/lib/sqlalchemy/sql/traversals.py +++ b/lib/sqlalchemy/sql/traversals.py @@ -857,7 +857,10 @@ class _GetChildren(InternalTraversal): with a _traverse_internals collection.""" def visit_has_cache_key(self, element, **kw): - return (element,) + # the GetChildren traversal refers explicitly to ClauseElement + # structures. Within these, a plain HasCacheKey is not a + # ClauseElement, so don't include these. + return () def visit_clauseelement(self, element, **kw): return (element,) diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py index 462a8763b..46751cb22 100644 --- a/lib/sqlalchemy/sql/type_api.py +++ b/lib/sqlalchemy/sql/type_api.py @@ -24,6 +24,7 @@ NULLTYPE = None STRINGTYPE = None MATCHTYPE = None INDEXABLE = None +TABLEVALUE = None _resolve_value_to_type = None @@ -45,6 +46,7 @@ class TypeEngine(Traversible): _sqla_type = True _isnull = False _is_tuple_type = False + _is_table_value = False class Comparator(operators.ColumnOperators): """Base class for custom comparison operations defined at the diff --git a/lib/sqlalchemy/sql/visitors.py b/lib/sqlalchemy/sql/visitors.py index fe0fbf669..8e113849e 100644 --- a/lib/sqlalchemy/sql/visitors.py +++ b/lib/sqlalchemy/sql/visitors.py @@ -75,10 +75,8 @@ def _generate_compiler_dispatch(cls): try: meth = getter(visitor) except AttributeError as err: - util.raise_( - exc.UnsupportedCompilationError(visitor, cls), - replace_context=err, - ) + return visitor.visit_unsupported_compilation(self, err, **kw) + else: return meth(self, **kw) |