diff options
| -rw-r--r-- | doc/build/changelog/unreleased_13/4509.rst | 10 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/__init__.py | 1 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 8 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/functions.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 276 | ||||
| -rw-r--r-- | test/sql/test_cte.py | 27 | ||||
| -rw-r--r-- | test/sql/test_lateral.py | 16 | ||||
| -rw-r--r-- | test/sql/test_selectable.py | 13 | ||||
| -rw-r--r-- | test/sql/test_tablesample.py | 17 |
9 files changed, 243 insertions, 127 deletions
diff --git a/doc/build/changelog/unreleased_13/4509.rst b/doc/build/changelog/unreleased_13/4509.rst new file mode 100644 index 000000000..deb886b91 --- /dev/null +++ b/doc/build/changelog/unreleased_13/4509.rst @@ -0,0 +1,10 @@ +.. change:: + :tags: bug, sql + :tickets: 4509 + + The :class:`.Alias` class and related subclasses :class:`.CTE`, + :class:`.Lateral` and :class:`.TableSample` have been reworked so that it is + not possible for a user to construct the objects directly. These constructs + require that the standalone construction function or selectable-bound method + be used to instantiate new objects. + diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py index 8968d2993..fb5639ef3 100644 --- a/lib/sqlalchemy/sql/__init__.py +++ b/lib/sqlalchemy/sql/__init__.py @@ -21,6 +21,7 @@ from .expression import column # noqa from .expression import ColumnCollection # noqa from .expression import ColumnElement # noqa from .expression import CompoundSelect # noqa +from .expression import cte # noqa from .expression import Delete # noqa from .expression import delete # noqa from .expression import desc # noqa diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 82fe93029..f381879ce 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -39,6 +39,7 @@ __all__ = [ "case", "cast", "column", + "cte", "delete", "desc", "distinct", @@ -148,7 +149,6 @@ from .functions import FunctionElement # noqa from .functions import modifier # noqa from .selectable import _interpret_as_from # noqa from .selectable import Alias # noqa -from .selectable import alias # noqa from .selectable import CompoundSelect # noqa from .selectable import CTE # noqa from .selectable import Exists # noqa @@ -160,7 +160,6 @@ from .selectable import HasPrefixes # noqa from .selectable import HasSuffixes # noqa from .selectable import Join # noqa from .selectable import Lateral # noqa -from .selectable import lateral # noqa from .selectable import ScalarSelect # noqa from .selectable import Select # noqa from .selectable import Selectable # noqa @@ -168,7 +167,6 @@ from .selectable import SelectBase # noqa from .selectable import subquery # noqa from .selectable import TableClause # noqa from .selectable import TableSample # noqa -from .selectable import tablesample # noqa from .selectable import TextAsFrom # noqa from .visitors import Visitable # noqa from ..util.langhelpers import public_factory # noqa @@ -182,6 +180,9 @@ from ..util.langhelpers import public_factory # noqa all_ = public_factory(CollectionAggregate._create_all, ".expression.all_") any_ = public_factory(CollectionAggregate._create_any, ".expression.any_") and_ = public_factory(BooleanClauseList.and_, ".expression.and_") +alias = public_factory(Alias._factory, ".expression.alias") +tablesample = public_factory(TableSample._factory, ".expression.tablesample") +lateral = public_factory(Lateral._factory, ".expression.lateral") or_ = public_factory(BooleanClauseList.or_, ".expression.or_") bindparam = public_factory(BindParameter, ".expression.bindparam") select = public_factory(Select, ".expression.select") @@ -193,6 +194,7 @@ within_group = public_factory(WithinGroup, ".expression.within_group") label = public_factory(Label, ".expression.label") case = public_factory(Case, ".expression.case") cast = public_factory(Cast, ".expression.cast") +cte = public_factory(CTE._factory, ".expression.cte") extract = public_factory(Extract, ".exp # noqaression.extract") tuple_ = public_factory(Tuple, ".expression.tuple_") except_ = public_factory(CompoundSelect._create_except, ".expression.except_") diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 075a2f826..fcc843d91 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -301,7 +301,7 @@ class FunctionElement(Executable, ColumnElement, FromClause): """ - return Alias(self, name) + return Alias._construct(self, name) def select(self): """Produce a :func:`~.expression.select` construct diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index ac08604f5..cc6d2bcc5 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -133,117 +133,6 @@ def subquery(alias, *args, **kwargs): return Select(*args, **kwargs).alias(alias) -def alias(selectable, name=None, flat=False): - """Return an :class:`.Alias` object. - - An :class:`.Alias` represents any :class:`.FromClause` - with an alternate name assigned within SQL, typically using the ``AS`` - clause when generated, e.g. ``SELECT * FROM table AS aliasname``. - - Similar functionality is available via the - :meth:`~.FromClause.alias` method - available on all :class:`.FromClause` subclasses. - - When an :class:`.Alias` is created from a :class:`.Table` object, - this has the effect of the table being rendered - as ``tablename AS aliasname`` in a SELECT statement. - - For :func:`.select` objects, the effect is that of creating a named - subquery, i.e. ``(select ...) AS aliasname``. - - The ``name`` parameter is optional, and provides the name - to use in the rendered SQL. If blank, an "anonymous" name - will be deterministically generated at compile time. - Deterministic means the name is guaranteed to be unique against - other constructs used in the same statement, and will also be the - same name for each successive compilation of the same statement - object. - - :param selectable: any :class:`.FromClause` subclass, - such as a table, select statement, etc. - - :param name: string name to be assigned as the alias. - If ``None``, a name will be deterministically generated - at compile time. - - :param flat: Will be passed through to if the given selectable - is an instance of :class:`.Join` - see :meth:`.Join.alias` - for details. - - .. versionadded:: 0.9.0 - - """ - return _interpret_as_from(selectable).alias(name=name, flat=flat) - - -def lateral(selectable, name=None): - """Return a :class:`.Lateral` object. - - :class:`.Lateral` is an :class:`.Alias` subclass that represents - a subquery with the LATERAL keyword applied to it. - - The special behavior of a LATERAL subquery is that it appears in the - FROM clause of an enclosing SELECT, but may correlate to other - FROM clauses of that SELECT. It is a special case of subquery - only supported by a small number of backends, currently more recent - PostgreSQL versions. - - .. versionadded:: 1.1 - - .. seealso:: - - :ref:`lateral_selects` - overview of usage. - - """ - return _interpret_as_from(selectable).lateral(name=name) - - -def tablesample(selectable, sampling, name=None, seed=None): - """Return a :class:`.TableSample` object. - - :class:`.TableSample` is an :class:`.Alias` subclass that represents - a table with the TABLESAMPLE clause applied to it. - :func:`~.expression.tablesample` - is also available from the :class:`.FromClause` class via the - :meth:`.FromClause.tablesample` method. - - The TABLESAMPLE clause allows selecting a randomly selected approximate - percentage of rows from a table. It supports multiple sampling methods, - most commonly BERNOULLI and SYSTEM. - - e.g.:: - - from sqlalchemy import func - - selectable = people.tablesample( - func.bernoulli(1), - name='alias', - seed=func.random()) - stmt = select([selectable.c.people_id]) - - Assuming ``people`` with a column ``people_id``, the above - statement would render as:: - - SELECT alias.people_id FROM - people AS alias TABLESAMPLE bernoulli(:bernoulli_1) - REPEATABLE (random()) - - .. versionadded:: 1.1 - - :param sampling: a ``float`` percentage between 0 and 100 or - :class:`.functions.Function`. - - :param name: optional alias name - - :param seed: any real-valued SQL expression. When specified, the - REPEATABLE sub-clause is also rendered. - - """ - return _interpret_as_from(selectable).tablesample( - sampling, name=name, seed=seed - ) - - class Selectable(ClauseElement): """mark a class as being selectable""" @@ -535,7 +424,7 @@ class FromClause(Selectable): """ - return Alias(self, name) + return Alias._construct(self, name) def lateral(self, name=None): """Return a LATERAL alias of this :class:`.FromClause`. @@ -550,7 +439,7 @@ class FromClause(Selectable): :ref:`lateral_selects` - overview of usage. """ - return Lateral(self, name) + return Lateral._construct(self, name) def tablesample(self, sampling, name=None, seed=None): """Return a TABLESAMPLE alias of this :class:`.FromClause`. @@ -565,7 +454,7 @@ class FromClause(Selectable): :func:`~.expression.tablesample` - usage guidelines and parameters """ - return TableSample(self, sampling, name, seed) + return TableSample._construct(self, sampling, name, seed) def is_derived_from(self, fromclause): """Return True if this FromClause is 'derived' from the given @@ -1329,7 +1218,69 @@ class Alias(FromClause): _is_from_container = True - def __init__(self, selectable, name=None): + def __init__(self, *arg, **kw): + raise NotImplementedError( + "The %s class is not intended to be constructed " + "directly. Please use the %s() standalone " + "function or the %s() method available from appropriate " + "selectable objects." + % ( + self.__class__.__name__, + self.__class__.__name__.lower(), + self.__class__.__name__.lower(), + ) + ) + + @classmethod + def _construct(cls, *arg, **kw): + obj = cls.__new__(cls) + obj._init(*arg, **kw) + return obj + + @classmethod + def _factory(cls, selectable, name=None, flat=False): + """Return an :class:`.Alias` object. + + An :class:`.Alias` represents any :class:`.FromClause` + with an alternate name assigned within SQL, typically using the ``AS`` + clause when generated, e.g. ``SELECT * FROM table AS aliasname``. + + Similar functionality is available via the + :meth:`~.FromClause.alias` method + available on all :class:`.FromClause` subclasses. + + When an :class:`.Alias` is created from a :class:`.Table` object, + this has the effect of the table being rendered + as ``tablename AS aliasname`` in a SELECT statement. + + For :func:`.select` objects, the effect is that of creating a named + subquery, i.e. ``(select ...) AS aliasname``. + + The ``name`` parameter is optional, and provides the name + to use in the rendered SQL. If blank, an "anonymous" name + will be deterministically generated at compile time. + Deterministic means the name is guaranteed to be unique against + other constructs used in the same statement, and will also be the + same name for each successive compilation of the same statement + object. + + :param selectable: any :class:`.FromClause` subclass, + such as a table, select statement, etc. + + :param name: string name to be assigned as the alias. + If ``None``, a name will be deterministically generated + at compile time. + + :param flat: Will be passed through to if the given selectable + is an instance of :class:`.Join` - see :meth:`.Join.alias` + for details. + + .. versionadded:: 0.9.0 + + """ + return _interpret_as_from(selectable).alias(name=name, flat=flat) + + def _init(self, selectable, name=None): baseselectable = selectable while isinstance(baseselectable, Alias): baseselectable = baseselectable.element @@ -1437,6 +1388,28 @@ class Lateral(Alias): __visit_name__ = "lateral" _is_lateral = True + @classmethod + def _factory(cls, selectable, name=None): + """Return a :class:`.Lateral` object. + + :class:`.Lateral` is an :class:`.Alias` subclass that represents + a subquery with the LATERAL keyword applied to it. + + The special behavior of a LATERAL subquery is that it appears in the + FROM clause of an enclosing SELECT, but may correlate to other + FROM clauses of that SELECT. It is a special case of subquery + only supported by a small number of backends, currently more recent + PostgreSQL versions. + + .. versionadded:: 1.1 + + .. seealso:: + + :ref:`lateral_selects` - overview of usage. + + """ + return _interpret_as_from(selectable).lateral(name=name) + class TableSample(Alias): """Represent a TABLESAMPLE clause. @@ -1455,10 +1428,56 @@ class TableSample(Alias): __visit_name__ = "tablesample" - def __init__(self, selectable, sampling, name=None, seed=None): + @classmethod + def _factory(cls, selectable, sampling, name=None, seed=None): + """Return a :class:`.TableSample` object. + + :class:`.TableSample` is an :class:`.Alias` subclass that represents + a table with the TABLESAMPLE clause applied to it. + :func:`~.expression.tablesample` + is also available from the :class:`.FromClause` class via the + :meth:`.FromClause.tablesample` method. + + The TABLESAMPLE clause allows selecting a randomly selected approximate + percentage of rows from a table. It supports multiple sampling methods, + most commonly BERNOULLI and SYSTEM. + + e.g.:: + + from sqlalchemy import func + + selectable = people.tablesample( + func.bernoulli(1), + name='alias', + seed=func.random()) + stmt = select([selectable.c.people_id]) + + Assuming ``people`` with a column ``people_id``, the above + statement would render as:: + + SELECT alias.people_id FROM + people AS alias TABLESAMPLE bernoulli(:bernoulli_1) + REPEATABLE (random()) + + .. versionadded:: 1.1 + + :param sampling: a ``float`` percentage between 0 and 100 or + :class:`.functions.Function`. + + :param name: optional alias name + + :param seed: any real-valued SQL expression. When specified, the + REPEATABLE sub-clause is also rendered. + + """ + return _interpret_as_from(selectable).tablesample( + sampling, name=name, seed=seed + ) + + def _init(self, selectable, sampling, name=None, seed=None): self.sampling = sampling self.seed = seed - super(TableSample, self).__init__(selectable, name=name) + super(TableSample, self)._init(selectable, name=name) @util.dependencies("sqlalchemy.sql.functions") def _get_method(self, functions): @@ -1479,7 +1498,18 @@ class CTE(Generative, HasSuffixes, Alias): __visit_name__ = "cte" - def __init__( + @classmethod + def _factory(cls, selectable, name=None, recursive=False): + r"""Return a new :class:`.CTE`, or Common Table Expression instance. + + Please see :meth:`.HasCte.cte` for detail on CTE usage. + + """ + return _interpret_as_from(selectable).cte( + name=name, recursive=recursive + ) + + def _init( self, selectable, name=None, @@ -1493,7 +1523,7 @@ class CTE(Generative, HasSuffixes, Alias): self._restates = _restates if _suffixes: self._suffixes = _suffixes - super(CTE, self).__init__(selectable, name=name) + super(CTE, self)._init(selectable, name=name) def _copy_internals(self, clone=_clone, **kw): super(CTE, self)._copy_internals(clone, **kw) @@ -1513,7 +1543,7 @@ class CTE(Generative, HasSuffixes, Alias): col._make_proxy(self) def alias(self, name=None, flat=False): - return CTE( + return CTE._construct( self.original, name=name, recursive=self.recursive, @@ -1522,7 +1552,7 @@ class CTE(Generative, HasSuffixes, Alias): ) def union(self, other): - return CTE( + return CTE._construct( self.original.union(other), name=self.name, recursive=self.recursive, @@ -1531,7 +1561,7 @@ class CTE(Generative, HasSuffixes, Alias): ) def union_all(self, other): - return CTE( + return CTE._construct( self.original.union_all(other), name=self.name, recursive=self.recursive, @@ -1705,7 +1735,7 @@ class HasCTE(object): :meth:`.HasCTE.cte`. """ - return CTE(self, name=name, recursive=recursive) + return CTE._construct(self, name=name, recursive=recursive) class FromGrouping(FromClause): diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py index ed2787b0f..7008bc1cc 100644 --- a/test/sql/test_cte.py +++ b/test/sql/test_cte.py @@ -4,12 +4,14 @@ from sqlalchemy.exc import CompileError from sqlalchemy.sql import and_ from sqlalchemy.sql import bindparam from sqlalchemy.sql import column +from sqlalchemy.sql import cte from sqlalchemy.sql import exists from sqlalchemy.sql import func from sqlalchemy.sql import literal from sqlalchemy.sql import select from sqlalchemy.sql import table from sqlalchemy.sql.elements import quoted_name +from sqlalchemy.sql.selectable import CTE from sqlalchemy.sql.visitors import cloned_traverse from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL @@ -1126,3 +1128,28 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): "UPDATE products SET id=:id, price=:price FROM pd " "WHERE products.price = pd.price", ) + + def test_standalone_function(self): + a = table("a", column("x")) + a_stmt = select([a]) + + stmt = select([cte(a_stmt)]) + + self.assert_compile( + stmt, + "WITH anon_1 AS (SELECT a.x AS x FROM a) " + "SELECT anon_1.x FROM anon_1", + ) + + def test_no_alias_construct(self): + a = table("a", column("x")) + a_stmt = select([a]) + + assert_raises_message( + NotImplementedError, + "The CTE class is not intended to be constructed directly. " + r"Please use the cte\(\) standalone function", + CTE, + a_stmt, + "foo", + ) diff --git a/test/sql/test_lateral.py b/test/sql/test_lateral.py index ee43475c8..ee9b13d1d 100644 --- a/test/sql/test_lateral.py +++ b/test/sql/test_lateral.py @@ -1,14 +1,18 @@ from sqlalchemy import Column +from sqlalchemy import column from sqlalchemy import ForeignKey from sqlalchemy import Integer from sqlalchemy import join from sqlalchemy import lateral from sqlalchemy import String from sqlalchemy import Table +from sqlalchemy import table from sqlalchemy import true from sqlalchemy.engine import default from sqlalchemy.sql import func from sqlalchemy.sql import select +from sqlalchemy.sql.selectable import Lateral +from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import fixtures @@ -150,3 +154,15 @@ class LateralTest(fixtures.TablesTest, AssertsCompiledSQL): "LATERAL generate_series(:generate_series_1, " "bookcases.bookcase_shelves) AS anon_1 ON true", ) + + def test_no_alias_construct(self): + a = table("a", column("x")) + + assert_raises_message( + NotImplementedError, + "The Lateral class is not intended to be constructed directly. " + r"Please use the lateral\(\) standalone", + Lateral, + a, + "foo", + ) diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py index 04c0e6102..a4d3e1b40 100644 --- a/test/sql/test_selectable.py +++ b/test/sql/test_selectable.py @@ -28,6 +28,7 @@ from sqlalchemy import type_coerce from sqlalchemy import TypeDecorator from sqlalchemy import union from sqlalchemy import util +from sqlalchemy.sql import Alias from sqlalchemy.sql import column from sqlalchemy.sql import elements from sqlalchemy.sql import expression @@ -871,6 +872,18 @@ class SelectableTest( Table("t1", MetaData(), c1) eq_(c1._label, "t1_c1") + def test_no_alias_construct(self): + a = table("a", column("x")) + + assert_raises_message( + NotImplementedError, + "The Alias class is not intended to be constructed directly. " + r"Please use the alias\(\) standalone function", + Alias, + a, + "foo", + ) + class RefreshForNewColTest(fixtures.TestBase): def test_join_uninit(self): diff --git a/test/sql/test_tablesample.py b/test/sql/test_tablesample.py index d2c57c930..7e0600a66 100644 --- a/test/sql/test_tablesample.py +++ b/test/sql/test_tablesample.py @@ -1,12 +1,16 @@ from sqlalchemy import Column +from sqlalchemy import column from sqlalchemy import Integer from sqlalchemy import String from sqlalchemy import Table +from sqlalchemy import table from sqlalchemy import tablesample from sqlalchemy.engine import default from sqlalchemy.sql import func from sqlalchemy.sql import select from sqlalchemy.sql import text +from sqlalchemy.sql.selectable import TableSample +from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import fixtures @@ -59,3 +63,16 @@ class TableSampleTest(fixtures.TablesTest, AssertsCompiledSQL): "SELECT alias.people_id FROM " "people AS alias TABLESAMPLE system(1)", ) + + def test_no_alias_construct(self): + a = table("a", column("x")) + + assert_raises_message( + NotImplementedError, + "The TableSample class is not intended to be constructed " + "directly. " + r"Please use the tablesample\(\) standalone", + TableSample, + a, + "foo", + ) |