From 22f65156bbe846dea2fb9f87fe4187abe0ed790a Mon Sep 17 00:00:00 2001 From: Gord Thompson Date: Sat, 9 Jan 2021 14:56:38 -0700 Subject: [PATCH] Replace with_labels() and apply_labels() in ORM/Core Replace :meth:`_orm.Query.with_labels` and :meth:`_sql.GenerativeSelect.apply_labels` with explicit getters and setters ``get_label_style`` and ``set_label_style`` to accommodate the three supported label styles: ``LABEL_STYLE_DISAMBIGUATE_ONLY`` (default), ``LABEL_STYLE_TABLENAME_PLUS_COL``, and ``LABEL_STYLE_NONE``. In addition, for Core and "future style" ORM queries, ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now the default label style. This style differs from the existing "no labels" style in that labeling is applied in the case of column name conflicts; with ``LABEL_STYLE_NONE``, a duplicate column name is not accessible via name in any case. For legacy ORM queries using :class:`_query.Query`, the table-plus-column names labeling style applied by ``LABEL_STYLE_TABLENAME_PLUS_COL`` continues to be used so that existing test suites and logging facilities see no change in behavior by default, however this style of labeling is no longer required for SQLAlchemy queries to function, as result sets are commonly matched to columns using a positional approach since SQLAlchemy 1.0. Within test suites, all use of apply_labels() / use_labels now uses the new methods. New tests added to test/sql/test_deprecations.py nad test/orm/test_deprecations.py to cover just the old apply_labels() method call. Tests in ORM that made explicit use apply_labels()/ etc. where it isn't needed for the ORM to work correctly use default label style now. Co-authored-by: Mike Bayer Fixes: #4757 Change-Id: I5fdcd2ed4ae8c7fe62f8be2b6d0e8f66409b6a54 --- doc/build/changelog/unreleased_14/4757.rst | 29 +++ doc/build/core/selectable.rst | 25 +++ doc/build/core/tutorial.rst | 10 +- doc/build/tutorial/data.rst | 2 +- lib/sqlalchemy/__init__.py | 4 + lib/sqlalchemy/orm/context.py | 6 +- lib/sqlalchemy/orm/loading.py | 5 +- lib/sqlalchemy/orm/mapper.py | 7 +- lib/sqlalchemy/orm/persistence.py | 5 +- lib/sqlalchemy/orm/query.py | 57 +++-- lib/sqlalchemy/orm/session.py | 11 +- lib/sqlalchemy/orm/strategies.py | 11 +- lib/sqlalchemy/sql/__init__.py | 4 + lib/sqlalchemy/sql/coercions.py | 8 +- lib/sqlalchemy/sql/expression.py | 4 + lib/sqlalchemy/sql/selectable.py | 208 ++++++++++++++++--- test/aaa_profiling/test_compiler.py | 13 +- test/dialect/mssql/test_compiler.py | 2 +- test/dialect/mssql/test_deprecations.py | 22 +- test/dialect/oracle/test_compiler.py | 7 +- test/dialect/postgresql/test_compiler.py | 12 +- test/dialect/postgresql/test_dialect.py | 9 +- test/orm/inheritance/_poly_fixtures.py | 4 +- test/orm/inheritance/test_basic.py | 6 +- test/orm/inheritance/test_concrete.py | 3 +- test/orm/inheritance/test_poly_loading.py | 3 +- test/orm/inheritance/test_polymorphic_rel.py | 1 - test/orm/inheritance/test_relationship.py | 74 ++++--- test/orm/inheritance/test_single.py | 14 +- test/orm/test_deprecations.py | 72 ++++--- test/orm/test_eager_relations.py | 14 +- test/orm/test_froms.py | 122 +++++++---- test/orm/test_joins.py | 7 +- test/orm/test_lambdas.py | 11 +- test/orm/test_lazy_relations.py | 10 +- test/orm/test_query.py | 120 +++++------ test/profiles.txt | 16 -- test/sql/test_compare.py | 5 +- test/sql/test_compiler.py | 181 +++++++++++----- test/sql/test_cte.py | 61 +++--- test/sql/test_deprecations.py | 40 ++-- test/sql/test_external_traversal.py | 77 +++++-- test/sql/test_functions.py | 6 +- test/sql/test_labels.py | 93 +++++++-- test/sql/test_operators.py | 13 +- test/sql/test_query.py | 57 ++--- test/sql/test_quote.py | 25 ++- test/sql/test_resultset.py | 52 +++-- test/sql/test_selectable.py | 170 +++++++++++---- test/sql/test_text.py | 21 +- test/sql/test_type_expressions.py | 17 +- 51 files changed, 1209 insertions(+), 547 deletions(-) create mode 100644 doc/build/changelog/unreleased_14/4757.rst diff --git a/doc/build/changelog/unreleased_14/4757.rst b/doc/build/changelog/unreleased_14/4757.rst new file mode 100644 index 0000000000..867642135e --- /dev/null +++ b/doc/build/changelog/unreleased_14/4757.rst @@ -0,0 +1,29 @@ +.. change:: + :tags: sql + :tickets: 4757 + + Replace :meth:`_orm.Query.with_labels` and + :meth:`_sql.GenerativeSelect.apply_labels` with explicit getters and + setters :meth:`_sql.GenerativeSelect.get_label_style` and + :meth:`_sql.GenerativeSelect.set_label_style` to accommodate the three + supported label styles: :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`, + :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`, and + :data:`_sql.LABEL_STYLE_NONE`. + + In addition, for Core and "future style" ORM queries, + ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now the default label style. This + style differs from the existing "no labels" style in that labeling is + applied in the case of column name conflicts; with ``LABEL_STYLE_NONE``, a + duplicate column name is not accessible via name in any case. + + For cases where labeling is significant, namely that the ``.c`` collection + of a subquery is able to refer to all columns unambiguously, the behavior + of ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now sufficient for all + SQLAlchemy features across Core and ORM which involve this behavior. + Result set rows since SQLAlchemy 1.0 are usually aligned with column + constructs positionally. + + For legacy ORM queries using :class:`_query.Query`, the table-plus-column + names labeling style applied by ``LABEL_STYLE_TABLENAME_PLUS_COL`` + continues to be used so that existing test suites and logging facilities + see no change in behavior by default. diff --git a/doc/build/core/selectable.rst b/doc/build/core/selectable.rst index 147e372be6..e6ff9412e0 100644 --- a/doc/build/core/selectable.rst +++ b/doc/build/core/selectable.rst @@ -145,3 +145,28 @@ The classes here are generated using the constructors listed at .. autoclass:: Values :members: + +Label Style Constants +--------------------- + +Constants used with the :meth:`_sql.GenerativeSelect.set_label_style` +method. + +.. autodata:: LABEL_STYLE_DISAMBIGUATE_ONLY + +.. autodata:: LABEL_STYLE_NONE + +.. autodata:: LABEL_STYLE_TABLENAME_PLUS_COL + +.. data:: LABEL_STYLE_DEFAULT + + The default label style, refers to :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`. + + .. versionadded:: 1.4 + + .. seealso:: + + :meth:`_sql.Select.set_label_style` + + :meth:`_sql.Select.get_label_style` + diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst index 14782742e0..1cec7134a2 100644 --- a/doc/build/core/tutorial.rst +++ b/doc/build/core/tutorial.rst @@ -579,7 +579,7 @@ our :func:`_expression.select` statement: {sql}>>> for row in conn.execute(select(users, addresses)): ... print(row) - SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address + SELECT users.id, users.name, users.fullname, addresses.id AS id_1, addresses.user_id, addresses.email_address FROM users, addresses [...] () {stop}(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com') @@ -602,7 +602,7 @@ WHERE clause. We do that using :meth:`_expression.Select.where`: >>> s = select(users, addresses).where(users.c.id == addresses.c.user_id) {sql}>>> for row in conn.execute(s): ... print(row) - SELECT users.id, users.name, users.fullname, addresses.id, + SELECT users.id, users.name, users.fullname, addresses.id AS id_1, addresses.user_id, addresses.email_address FROM users, addresses WHERE users.id = addresses.user_id @@ -1277,8 +1277,8 @@ by a column name that appears more than once: ... order_by(u1a.c.name) # using "name" here would be ambiguous {sql}>>> conn.execute(stmt).fetchall() - SELECT users_1.id, users_1.name, users_1.fullname, users_2.id, - users_2.name, users_2.fullname + SELECT users_1.id, users_1.name, users_1.fullname, users_2.id AS id_1, + users_2.name AS name_1, users_2.fullname AS fullname_1 FROM users AS users_1, users AS users_2 WHERE users_1.name > users_2.name ORDER BY users_1.name [...] () @@ -1597,7 +1597,7 @@ single named value is needed in the execute parameters: ... select_from(users.outerjoin(addresses)).\ ... order_by(addresses.c.id) {sql}>>> conn.execute(s, {"name": "jack"}).fetchall() - SELECT users.id, users.name, users.fullname, addresses.id, + SELECT users.id, users.name, users.fullname, addresses.id AS id_1, addresses.user_id, addresses.email_address FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id WHERE users.name LIKE ? || '%' OR addresses.email_address LIKE ? || '@%' diff --git a/doc/build/tutorial/data.rst b/doc/build/tutorial/data.rst index bc81cb0274..b6c8260a86 100644 --- a/doc/build/tutorial/data.rst +++ b/doc/build/tutorial/data.rst @@ -879,7 +879,7 @@ below for example returns all unique pairs of user names:: ... select(user_alias_1.c.name, user_alias_2.c.name). ... join_from(user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id) ... ) - {opensql}SELECT user_account_1.name, user_account_2.name + {opensql}SELECT user_account_1.name, user_account_2.name AS name_1 FROM user_account AS user_account_1 JOIN user_account AS user_account_2 ON user_account_1.id > user_account_2.id diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py index 6075854609..2d092be653 100644 --- a/lib/sqlalchemy/__init__.py +++ b/lib/sqlalchemy/__init__.py @@ -54,6 +54,10 @@ from .sql import insert # noqa from .sql import intersect # noqa from .sql import intersect_all # noqa from .sql import join # noqa +from .sql import LABEL_STYLE_DEFAULT # noqa +from .sql import LABEL_STYLE_DISAMBIGUATE_ONLY # noqa +from .sql import LABEL_STYLE_NONE # noqa +from .sql import LABEL_STYLE_TABLENAME_PLUS_COL # noqa from .sql import lambda_stmt # noqa from .sql import lateral # noqa from .sql import literal # noqa diff --git a/lib/sqlalchemy/orm/context.py b/lib/sqlalchemy/orm/context.py index 584a079700..f9a0b72fe2 100644 --- a/lib/sqlalchemy/orm/context.py +++ b/lib/sqlalchemy/orm/context.py @@ -373,9 +373,11 @@ class ORMFromStatementCompileState(ORMCompileState): if ( isinstance(statement, expression.SelectBase) and not statement._is_textual - and not statement.use_labels + and statement._label_style is util.symbol("LABEL_STYLE_NONE") ): - self.statement = statement.apply_labels() + self.statement = statement.set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) else: self.statement = statement self.order_by = None diff --git a/lib/sqlalchemy/orm/loading.py b/lib/sqlalchemy/orm/loading.py index 3a85a2d7f1..6c38931b45 100644 --- a/lib/sqlalchemy/orm/loading.py +++ b/lib/sqlalchemy/orm/loading.py @@ -31,6 +31,7 @@ from ..engine.result import ChunkedIteratorResult from ..engine.result import FrozenResult from ..engine.result import SimpleResultMetaData from ..sql import util as sql_util +from ..sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL _new_runid = util.counter() @@ -1396,7 +1397,9 @@ def load_scalar_attributes(mapper, state, attribute_names, passive): result = load_on_ident( session, - future.select(mapper).apply_labels(), + future.select(mapper).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), identity_key, refresh_state=state, only_load_props=attribute_names, diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py index 9ac0c85c64..5a9bc102e7 100644 --- a/lib/sqlalchemy/orm/mapper.py +++ b/lib/sqlalchemy/orm/mapper.py @@ -54,6 +54,7 @@ from ..sql import operators from ..sql import roles from ..sql import util as sql_util from ..sql import visitors +from ..sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from ..util import HasMemoized @@ -3008,7 +3009,11 @@ class Mapper( cols = [] for key in col_attribute_names: cols.extend(props[key].columns) - return sql.select(*cols).where(cond).apply_labels() + return ( + sql.select(*cols) + .where(cond) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ) def _iterate_to_target_viawpoly(self, mapper): if self.isa(mapper): diff --git a/lib/sqlalchemy/orm/persistence.py b/lib/sqlalchemy/orm/persistence.py index 42b7875802..7ba3a26d0f 100644 --- a/lib/sqlalchemy/orm/persistence.py +++ b/lib/sqlalchemy/orm/persistence.py @@ -40,6 +40,7 @@ from ..sql.base import Options from ..sql.dml import DeleteDMLState from ..sql.dml import UpdateDMLState from ..sql.elements import BooleanClauseList +from ..sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL def _bulk_insert( @@ -1521,7 +1522,9 @@ def _finalize_insert_update_commands(base_mapper, uowtransaction, states): if toload_now: state.key = base_mapper._identity_key_from_state(state) - stmt = future.select(mapper).apply_labels() + stmt = future.select(mapper).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) loading.load_on_ident( uowtransaction.session, stmt, diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py index 5dd2a8a214..1422fd6a14 100644 --- a/lib/sqlalchemy/orm/query.py +++ b/lib/sqlalchemy/orm/query.py @@ -470,7 +470,7 @@ class Query( """ q = self.enable_eagerloads(False) if with_labels: - q = q.with_labels() + q = q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) q = q.statement @@ -576,7 +576,11 @@ class Query( return self.enable_eagerloads(False).statement.scalar_subquery() def __clause_element__(self): - return self.enable_eagerloads(False).with_labels().statement + return ( + self.enable_eagerloads(False) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .statement + ) @_generative def only_return_tuples(self, value): @@ -637,8 +641,27 @@ class Query( """ self._compile_options += {"_enable_eagerloads": value} - @_generative + @util.deprecated_20( + ":meth:`_orm.Query.with_labels` and :meth:`_orm.Query.apply_labels`", + alternative="Use set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) " + "instead.", + ) def with_labels(self): + return self.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + + apply_labels = with_labels + + @property + def get_label_style(self): + """ + Retrieve the current label style. + + .. versionadded:: 1.4 + + """ + return self._label_style + + def set_label_style(self, style): """Apply column labels to the return value of Query.statement. Indicates that this Query's `statement` accessor should return @@ -650,26 +673,28 @@ class Query( When the `Query` actually issues SQL to load rows, it always uses column labeling. - .. note:: The :meth:`_query.Query.with_labels` method *only* applies + .. note:: The :meth:`_query.Query.set_label_style` method *only* applies the output of :attr:`_query.Query.statement`, and *not* to any of the result-row invoking systems of :class:`_query.Query` itself, e.g. :meth:`_query.Query.first`, :meth:`_query.Query.all`, etc. To execute - a query using :meth:`_query.Query.with_labels`, invoke the + a query using :meth:`_query.Query.set_label_style`, invoke the :attr:`_query.Query.statement` using :meth:`.Session.execute`:: - result = session.execute(query.with_labels().statement) - - - """ - self._label_style = LABEL_STYLE_TABLENAME_PLUS_COL + result = session.execute( + query + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .statement + ) - apply_labels = with_labels + .. versionadded:: 1.4 - @property - def use_labels(self): - return self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL + """ # noqa + if self._label_style is not style: + self = self._generate() + self._label_style = style + return self @_generative def enable_assertions(self, value): @@ -1259,7 +1284,7 @@ class Query( def _from_self(self, *entities): fromclause = ( - self.with_labels() + self.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .enable_eagerloads(False) .correlate(None) .subquery() @@ -2903,7 +2928,7 @@ class Query( inner = ( self.enable_eagerloads(False) .add_columns(sql.literal_column("1")) - .with_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .statement.with_only_columns(1) ) diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py index 8a7a64f78c..06a69b425a 100644 --- a/lib/sqlalchemy/orm/session.py +++ b/lib/sqlalchemy/orm/session.py @@ -37,6 +37,7 @@ from ..sql import dml from ..sql import roles from ..sql import visitors from ..sql.base import CompileState +from ..sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL __all__ = ["Session", "SessionTransaction", "sessionmaker"] @@ -2741,15 +2742,17 @@ class Session(_SessionClassMethods): elif instance is attributes.PASSIVE_CLASS_MISMATCH: return None - # apply_labels() not strictly necessary, however this will ensure that - # tablename_colname style is used which at the moment is asserted - # in a lot of unit tests :) + # set_label_style() not strictly necessary, however this will ensure + # that tablename_colname style is used which at the moment is + # asserted in a lot of unit tests :) load_options = context.QueryContext.default_load_options if populate_existing: load_options += {"_populate_existing": populate_existing} - statement = sql.select(mapper).apply_labels() + statement = sql.select(mapper).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) if with_for_update is not None: statement._for_update_arg = query.ForUpdateArg._from_argument( with_for_update diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py index 837d4d5488..c80b8f5a2d 100644 --- a/lib/sqlalchemy/orm/strategies.py +++ b/lib/sqlalchemy/orm/strategies.py @@ -41,6 +41,7 @@ from .. import sql from .. import util from ..sql import util as sql_util from ..sql import visitors +from ..sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL def _register_attribute( @@ -487,7 +488,9 @@ class DeferredColumnLoader(LoaderStrategy): if ( loading.load_on_ident( session, - sql.select(localparent).apply_labels(), + sql.select(localparent).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), state.key, only_load_props=group, refresh_state=state, @@ -901,7 +904,7 @@ class LazyLoader(AbstractRelationshipLoader, util.MemoizedSlots): stmt = sql.lambda_stmt( lambda: sql.select(self.entity) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) ._set_compile_options(ORMCompileState.default_compile_options), global_track_bound_values=False, lambda_cache=self._query_cache, @@ -1304,7 +1307,7 @@ class SubqueryLoader(PostLoader): # which we'll join onto. # LEGACY: as "q" is a Query, the before_compile() event is invoked # here. - embed_q = q.apply_labels().subquery() + embed_q = q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).subquery() left_alias = orm_util.AliasedClass( leftmost_mapper, embed_q, use_mapper_path=True ) @@ -2751,7 +2754,7 @@ class SelectInLoader(PostLoader, util.MemoizedSlots): lambda: sql.select( orm_util.Bundle("pk", *pk_cols), effective_entity ) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) ._set_compile_options(ORMCompileState.default_compile_options) ._set_propagate_attrs( { diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py index 11795c3b24..7ee0bd8f42 100644 --- a/lib/sqlalchemy/sql/__init__.py +++ b/lib/sqlalchemy/sql/__init__.py @@ -47,6 +47,10 @@ from .expression import intersect_all # noqa from .expression import Join # noqa from .expression import join # noqa from .expression import label # noqa +from .expression import LABEL_STYLE_DEFAULT # noqa +from .expression import LABEL_STYLE_DISAMBIGUATE_ONLY # noqa +from .expression import LABEL_STYLE_NONE # noqa +from .expression import LABEL_STYLE_TABLENAME_PLUS_COL # noqa from .expression import lambda_stmt # noqa from .expression import LambdaElement # noqa from .expression import lateral # noqa diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py index 05e0a4fcf0..3b972be41d 100644 --- a/lib/sqlalchemy/sql/coercions.py +++ b/lib/sqlalchemy/sql/coercions.py @@ -67,7 +67,13 @@ def _deep_is_literal(element): return ( not isinstance( element, - (Visitable, schema.SchemaEventTarget, HasCacheKey, Options), + ( + Visitable, + schema.SchemaEventTarget, + HasCacheKey, + Options, + util.langhelpers._symbol, + ), ) and not hasattr(element, "__clause_element__") and ( diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 359f5e533f..3eb1443a34 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -158,6 +158,10 @@ from .selectable import HasCTE # noqa from .selectable import HasPrefixes # noqa from .selectable import HasSuffixes # noqa from .selectable import Join # noqa +from .selectable import LABEL_STYLE_DEFAULT # noqa +from .selectable import LABEL_STYLE_DISAMBIGUATE_ONLY # noqa +from .selectable import LABEL_STYLE_NONE # noqa +from .selectable import LABEL_STYLE_TABLENAME_PLUS_COL # noqa from .selectable import Lateral # noqa from .selectable import ReturnsRows # noqa from .selectable import ScalarSelect # noqa diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 8e478583f6..e1cf367c84 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -825,7 +825,7 @@ class FromClause(roles.AnonymizedFromClauseRole, Selectable): this is used to "ping" a derived selectable to add a new column to its .c. collection when a Column has been added to one of the - Table objects it ultimtely derives from. + Table objects it ultimately derives from. If the given selectable hasn't populated its .c. collection yet, it should at least pass on the message to the contained selectables, @@ -849,6 +849,96 @@ class FromClause(roles.AnonymizedFromClauseRole, Selectable): return self.alias(name=name) +LABEL_STYLE_NONE = util.symbol( + "LABEL_STYLE_NONE", + """Label style indicating no automatic labeling should be applied to the + columns clause of a SELECT statement. + + Below, the columns named ``columna`` are both rendered as is, meaning that + the name ``columna`` can only refer to the first occurrence of this name + within a result set, as well as if the statement were used as a subquery:: + + >>> from sqlalchemy import table, column, select, true, LABEL_STYLE_NONE + >>> table1 = table("table1", column("columna"), column("columnb")) + >>> table2 = table("table2", column("columna"), column("columnc")) + >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_NONE)) + SELECT table1.columna, table1.columnb, table2.columna, table2.columnc + FROM table1 JOIN table2 ON true + + Used with the :meth:`_sql.Select.set_label_style` method. + + .. versionadded:: 1.4 + +""", # noqa E501 +) + +LABEL_STYLE_TABLENAME_PLUS_COL = util.symbol( + "LABEL_STYLE_TABLENAME_PLUS_COL", + """Label style indicating all columns should be labeled as + ``_`` when generating the columns clause of a SELECT + statement, to disambiguate same-named columns referenced from different + tables, aliases, or subqueries. + + Below, all column names are given a label so that the two same-named + columns ``columna`` are disambiguated as ``table1_columna`` and + ``table2_columna`:: + + >>> from sqlalchemy import table, column, select, true, LABEL_STYLE_TABLENAME_PLUS_COL + >>> table1 = table("table1", column("columna"), column("columnb")) + >>> table2 = table("table2", column("columna"), column("columnc")) + >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)) + SELECT table1.columna AS table1_columna, table1.columnb AS table1_columnb, table2.columna AS table2_columna, table2.columnc AS table2_columnc + FROM table1 JOIN table2 ON true + + Used with the :meth:`_sql.GenerativeSelect.set_label_style` method. + Equivalent to the legacy method ``Select.apply_labels()``; + :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` is SQLAlchemy's legacy + auto-labeling style. :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` provides a + less intrusive approach to disambiguation of same-named column expressions. + + + .. versionadded:: 1.4 + +""", # noqa E501 +) + + +LABEL_STYLE_DISAMBIGUATE_ONLY = util.symbol( + "LABEL_STYLE_DISAMBIGUATE_ONLY", + """Label style indicating that columns with a name that conflicts with + an existing name should be labeled with a semi-anonymizing label + when generating the columns clause of a SELECT statement. + + Below, most column names are left unaffected, except for the second + occurrence of the name ``columna``, which is labeled using the + label ``columna_1`` to disambiguate it from that of ``tablea.columna``:: + + >>> from sqlalchemy import table, column, select, true, LABEL_STYLE_DISAMBIGUATE_ONLY + >>> table1 = table("table1", column("columna"), column("columnb")) + >>> table2 = table("table2", column("columna"), column("columnc")) + >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)) + SELECT table1.columna, table1.columnb, table2.columna AS columna_1, table2.columnc + FROM table1 JOIN table2 ON true + + Used with the :meth:`_sql.GenerativeSelect.set_label_style` method, + :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` is the default labeling style + for all SELECT statements outside of :term:`1.x style` ORM queries. + + .. versionadded:: 1.4 + +""", # noqa: E501, +) + + +LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY +"""The default label style, refers to +:data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`. + +.. versionadded:: 1.4 + +""" + + class Join(roles.DMLTableRole, FromClause): """Represent a ``JOIN`` construct between two :class:`_expression.FromClause` @@ -1277,7 +1367,12 @@ class Join(roles.DMLTableRole, FromClause): full=self.full, ) else: - return self.select().apply_labels().correlate(None).alias(name) + return ( + self.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .correlate(None) + .alias(name) + ) @util.deprecated_20( ":meth:`_sql.Join.alias`", @@ -1312,7 +1407,7 @@ class Join(roles.DMLTableRole, FromClause): j = alias( select(j.left, j.right).\ select_from(j).\ - apply_labels().\ + set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).\ correlate(False), name=name ) @@ -2054,9 +2149,7 @@ class Subquery(AliasedReturnsRows): "use the :meth:`_query.Query.scalar_subquery` method.", ) def as_scalar(self): - return self.element._set_label_style( - LABEL_STYLE_NONE - ).scalar_subquery() + return self.element.set_label_style(LABEL_STYLE_NONE).scalar_subquery() class FromGrouping(GroupedElement, FromClause): @@ -2633,7 +2726,7 @@ class SelectBase( """ if self._label_style is not LABEL_STYLE_NONE: - self = self._set_label_style(LABEL_STYLE_NONE) + self = self.set_label_style(LABEL_STYLE_NONE) return ScalarSelect(self) @@ -2760,6 +2853,14 @@ class SelectStatementGrouping(GroupedElement, SelectBase): else: return self + def get_label_style(self): + return self._label_style + + def set_label_style(self, label_style): + return SelectStatementGrouping( + self.element.set_label_style(label_style) + ) + @property def _label_style(self): return self.element._label_style @@ -2854,11 +2955,6 @@ class DeprecatedSelectBaseGenerations(object): self.group_by.non_generative(self, *clauses) -LABEL_STYLE_NONE = util.symbol("LABEL_STYLE_NONE") -LABEL_STYLE_TABLENAME_PLUS_COL = util.symbol("LABEL_STYLE_TABLENAME_PLUS_COL") -LABEL_STYLE_DISAMBIGUATE_ONLY = util.symbol("LABEL_STYLE_DISAMBIGUATE_ONLY") - - class GenerativeSelect(DeprecatedSelectBaseGenerations, SelectBase): """Base class for SELECT statements where additional elements can be added. @@ -2892,7 +2988,7 @@ class GenerativeSelect(DeprecatedSelectBaseGenerations, SelectBase): ) def __init__( self, - _label_style=LABEL_STYLE_NONE, + _label_style=LABEL_STYLE_DEFAULT, use_labels=False, limit=None, offset=None, @@ -2901,6 +2997,15 @@ class GenerativeSelect(DeprecatedSelectBaseGenerations, SelectBase): bind=None, ): if use_labels: + if util.SQLALCHEMY_WARN_20: + util.warn_deprecated_20( + "The use_labels=True keyword argument to GenerativeSelect " + "is deprecated and will be removed in version 2.0. Please " + "use " + "select.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) " + "if you need to replicate this legacy behavior.", + stacklevel=4, + ) _label_style = LABEL_STYLE_TABLENAME_PLUS_COL self._label_style = _label_style @@ -2979,29 +3084,66 @@ class GenerativeSelect(DeprecatedSelectBaseGenerations, SelectBase): key_share=key_share, ) - @property - def use_labels(self): - return self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL + def get_label_style(self): + """ + Retrieve the current label style. - def apply_labels(self): - """Return a new selectable with the 'use_labels' flag set to True. + .. versionadded:: 1.4 - This will result in column expressions being generated using labels - against their table name, such as "SELECT somecolumn AS - tablename_somecolumn". This allows selectables which contain multiple - FROM clauses to produce a unique set of column names regardless of - name conflicts among the individual FROM clauses. + """ + return self._label_style + + def set_label_style(self, style): + """Return a new selectable with the specified label style. + + There are three "label styles" available, + :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`, + :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`, and + :data:`_sql.LABEL_STYLE_NONE`. The default style is + :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`. + + In modern SQLAlchemy, there is not generally a need to change the + labeling style, as per-expression labels are more effectively used by + making use of the :meth:`_sql.ColumnElement.label` method. In past + versions, :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` was used to + disambiguate same-named columns from different tables, aliases, or + subqueries; the newer :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` now + applies labels only to names that conflict with an existing name so + that the impact of this labeling is minimal. + + The rationale for disambiguation is mostly so that all column + expressions are available from a given :attr:`_sql.FromClause.c` + collection when a subquery is created. + + .. versionadded:: 1.4 - the + :meth:`_sql.GenerativeSelect.set_label_style` method replaces the + previous combination of ``.apply_labels()``, ``.with_labels()`` and + ``use_labels=True`` methods and/or parameters. + .. seealso:: - """ - return self._set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` - def _set_label_style(self, style): + :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` + + :data:`_sql.LABEL_STYLE_NONE` + + :data:`_sql.LABEL_STYLE_DEFAULT` + + """ if self._label_style is not style: self = self._generate() self._label_style = style return self + @util.deprecated_20( + ":meth:`_sql.GenerativeSelect.apply_labels`", + alternative="Use set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) " + "instead.", + ) + def apply_labels(self): + return self.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + @property def _group_by_clause(self): """ClauseList access to group_by_clauses for legacy dialects""" @@ -3525,8 +3667,9 @@ class CompoundSelect(HasCompileState, GenerativeSelect): # ForeignKeys in. this would allow the union() to have all # those fks too. select_0 = self.selects[0] - if self._label_style is not LABEL_STYLE_NONE: - select_0 = select_0._set_label_style(self._label_style) + + if self._label_style is not LABEL_STYLE_DEFAULT: + select_0 = select_0.set_label_style(self._label_style) select_0._generate_fromclause_column_proxies(subquery) # hand-construct the "_proxies" collection to include all @@ -4347,12 +4490,12 @@ class Select( This parameter can also be specified on an existing :class:`_expression.Select` object using the - :meth:`_expression.Select.apply_labels` + :meth:`_expression.Select.set_label_style` method. .. seealso:: - :meth:`_expression.Select.apply_labels` + :meth:`_expression.Select.set_label_style` """ self = cls.__new__(cls) @@ -4917,7 +5060,8 @@ class Select( comparison in the WHERE clause of the statement. The primary purpose of this method is to automatically construct a select statement with all uniquely-named columns, without the need to use - table-qualified labels as :meth:`_expression.Select.apply_labels` + table-qualified labels as + :meth:`_expression.Select.set_label_style` does. When columns are omitted based on foreign key, the referred-to @@ -5263,7 +5407,7 @@ class Select( def _ensure_disambiguated_names(self): if self._label_style is LABEL_STYLE_NONE: - self = self._set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY) + self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY) return self def _generate_columns_plus_names(self, anon_for_dupe_key): diff --git a/test/aaa_profiling/test_compiler.py b/test/aaa_profiling/test_compiler.py index 968a747008..5db422fd52 100644 --- a/test/aaa_profiling/test_compiler.py +++ b/test/aaa_profiling/test_compiler.py @@ -5,6 +5,7 @@ from sqlalchemy import select from sqlalchemy import String from sqlalchemy import Table from sqlalchemy.engine import default +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import AssertsExecutionResults from sqlalchemy.testing import fixtures from sqlalchemy.testing import profiling @@ -87,12 +88,20 @@ class CompileTest(fixtures.TestBase, AssertsExecutionResults): def test_select_labels(self): # give some of the cached type values # a chance to warm up - s = select(t1).where(t1.c.c2 == t2.c.c1).apply_labels() + s = ( + select(t1) + .where(t1.c.c2 == t2.c.c1) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ) s.compile(dialect=self.dialect) @profiling.function_call_count(variance=0.15, warmup=1) def go(): - s = select(t1).where(t1.c.c2 == t2.c.c1).apply_labels() + s = ( + select(t1) + .where(t1.c.c2 == t2.c.c1) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ) s.compile(dialect=self.dialect) go() diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py index f0bb66aa9f..759833ecae 100644 --- a/test/dialect/mssql/test_compiler.py +++ b/test/dialect/mssql/test_compiler.py @@ -123,7 +123,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( join, - "SELECT t1.a, t1.b, t1.c, t2.a, t2.b, t2.c " + "SELECT t1.a, t1.b, t1.c, t2.a AS a_1, t2.b AS b_1, t2.c AS c_1 " "FROM t1 WITH (NOLOCK) JOIN t2 ON t1.a = t2.a", ) diff --git a/test/dialect/mssql/test_deprecations.py b/test/dialect/mssql/test_deprecations.py index 27709beb05..354107e811 100644 --- a/test/dialect/mssql/test_deprecations.py +++ b/test/dialect/mssql/test_deprecations.py @@ -10,6 +10,7 @@ from sqlalchemy import testing from sqlalchemy.dialects.mssql import base as mssql from sqlalchemy.sql import column from sqlalchemy.sql import table +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import assertions from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import engines @@ -89,7 +90,7 @@ class LegacySchemaAliasingTest(fixtures.TestBase, AssertsCompiledSQL): assert self.t2.c.a in set(c._create_result_map()["a"][1]) def test_result_map_use_labels(self): - s = self.t2.select(use_labels=True) + s = self.t2.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) c = s.compile(dialect=self._legacy_dialect()) assert self.t2.c.a in set(c._create_result_map()["schema_t2_a"][1]) @@ -103,7 +104,7 @@ class LegacySchemaAliasingTest(fixtures.TestBase, AssertsCompiledSQL): def test_straight_select_use_labels(self): self._assert_sql( - self.t2.select(use_labels=True), + self.t2.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT t2_1.a AS schema_t2_a, t2_1.b AS schema_t2_b, " "t2_1.c AS schema_t2_c FROM [schema].t2 AS t2_1", "SELECT [schema].t2.a AS schema_t2_a, " @@ -115,18 +116,25 @@ class LegacySchemaAliasingTest(fixtures.TestBase, AssertsCompiledSQL): t1, t2 = self.t1, self.t2 self._assert_sql( t1.join(t2, t1.c.a == t2.c.a).select(), - "SELECT t1.a, t1.b, t1.c, t2_1.a, t2_1.b, t2_1.c FROM t1 " + "SELECT t1.a, t1.b, t1.c, t2_1.a AS a_1, t2_1.b AS b_1, " + "t2_1.c AS c_1 FROM t1 " "JOIN [schema].t2 AS t2_1 ON t2_1.a = t1.a", - "SELECT t1.a, t1.b, t1.c, [schema].t2.a, [schema].t2.b, " - "[schema].t2.c FROM t1 JOIN [schema].t2 ON [schema].t2.a = t1.a", + "SELECT t1.a, t1.b, t1.c, [schema].t2.a AS a_1, " + "[schema].t2.b AS b_1, " + "[schema].t2.c AS c_1 FROM t1 JOIN [schema].t2 " + "ON [schema].t2.a = t1.a", ) def test_union_schema_to_non(self): t1, t2 = self.t1, self.t2 s = ( select(t2.c.a, t2.c.b) - .apply_labels() - .union(select(t1.c.a, t1.c.b).apply_labels()) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .union( + select(t1.c.a, t1.c.b).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) + ) .alias() .select() ) diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py index f09346eb32..21be331a5f 100644 --- a/test/dialect/oracle/test_compiler.py +++ b/test/dialect/oracle/test_compiler.py @@ -31,6 +31,7 @@ from sqlalchemy.sql import column from sqlalchemy.sql import ddl from sqlalchemy.sql import quoted_name from sqlalchemy.sql import table +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import eq_ @@ -706,7 +707,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( select(other_table, anon) .select_from(other_table.outerjoin(anon)) - .apply_labels(), + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT other_thirty_characters_table_.id " "AS other_thirty_characters__1, " "other_thirty_characters_table_.thirty_char" @@ -724,7 +725,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( select(other_table, anon) .select_from(other_table.outerjoin(anon)) - .apply_labels(), + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT other_thirty_characters_table_.id " "AS other_thirty_characters__1, " "other_thirty_characters_table_.thirty_char" @@ -1054,7 +1055,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( s, "SELECT address_types_1.id, " - "address_types_1.name, addresses.id, " + "address_types_1.name, addresses.id AS id_1, " "addresses.user_id, addresses.address_type_" "id, addresses.email_address FROM " "addresses LEFT OUTER JOIN address_types " diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index b2c7ce1c2a..8c8a84c4ec 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -1551,13 +1551,15 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): stmt = select(tbl1, tbl2).with_hint(tbl1, "ONLY", "postgresql") expected = ( - "SELECT testtbl1.id, testtbl2.id FROM ONLY testtbl1, " "testtbl2" + "SELECT testtbl1.id, testtbl2.id AS id_1 FROM ONLY testtbl1, " + "testtbl2" ) self.assert_compile(stmt, expected) stmt = select(tbl1, tbl2).with_hint(tbl2, "ONLY", "postgresql") expected = ( - "SELECT testtbl1.id, testtbl2.id FROM testtbl1, ONLY " "testtbl2" + "SELECT testtbl1.id, testtbl2.id AS id_1 FROM testtbl1, ONLY " + "testtbl2" ) self.assert_compile(stmt, expected) @@ -1565,7 +1567,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): stmt = stmt.with_hint(tbl1, "ONLY", "postgresql") stmt = stmt.with_hint(tbl2, "ONLY", "postgresql") expected = ( - "SELECT testtbl1.id, testtbl2.id FROM ONLY testtbl1, " + "SELECT testtbl1.id, testtbl2.id AS id_1 FROM ONLY testtbl1, " "ONLY testtbl2" ) self.assert_compile(stmt, expected) @@ -2387,7 +2389,7 @@ class DistinctOnTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( q, - "SELECT DISTINCT ON (anon_1.id) t.id, anon_1.id " + "SELECT DISTINCT ON (anon_1.id) t.id, anon_1.id AS id_1 " "FROM t, (SELECT t.id AS id, t.a AS a, t.b " "AS b FROM t) AS anon_1 WHERE t.id = anon_1.id", ) @@ -2401,7 +2403,7 @@ class DistinctOnTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( q, - "SELECT DISTINCT ON (sq.id) t.id, sq.id " + "SELECT DISTINCT ON (sq.id) t.id, sq.id AS id_1 " "FROM t, (SELECT t.id AS id, t.a AS a, " "t.b AS b FROM t) AS sq WHERE t.id = sq.id", ) diff --git a/test/dialect/postgresql/test_dialect.py b/test/dialect/postgresql/test_dialect.py index 075994cb96..f407f2655a 100644 --- a/test/dialect/postgresql/test_dialect.py +++ b/test/dialect/postgresql/test_dialect.py @@ -36,6 +36,7 @@ from sqlalchemy.dialects.postgresql.psycopg2 import EXECUTEMANY_VALUES from sqlalchemy.engine import cursor as _cursor from sqlalchemy.engine import engine_from_config from sqlalchemy.engine import url +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import config from sqlalchemy.testing import engines from sqlalchemy.testing import fixtures @@ -1122,7 +1123,9 @@ $$ LANGUAGE plpgsql; ) eq_( connection.execute( - users.select().apply_labels().where(users.c.name == "name2") + users.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .where(users.c.name == "name2") ).fetchall(), [(2, "name2")], ) @@ -1138,7 +1141,9 @@ $$ LANGUAGE plpgsql; ) eq_( connection.execute( - users.select().apply_labels().where(users.c.id == 4) + users.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .where(users.c.id == 4) ).fetchall(), [(4, "newname")], ) diff --git a/test/orm/inheritance/_poly_fixtures.py b/test/orm/inheritance/_poly_fixtures.py index 7a70810a15..7efc99913a 100644 --- a/test/orm/inheritance/_poly_fixtures.py +++ b/test/orm/inheritance/_poly_fixtures.py @@ -428,14 +428,14 @@ class _PolymorphicAliasedJoins(_PolymorphicFixtureBase): people.outerjoin(engineers) .outerjoin(managers) .select() - ._set_label_style(cls.label_style) + .set_label_style(cls.label_style) .subquery("pjoin") ) manager_join = ( people.join(managers) .outerjoin(boss) .select() - ._set_label_style(cls.label_style) + .set_label_style(cls.label_style) .subquery("mjoin") ) person_with_polymorphic = ([Person, Manager, Engineer], person_join) diff --git a/test/orm/inheritance/test_basic.py b/test/orm/inheritance/test_basic.py index da07b4941b..d145fef79b 100644 --- a/test/orm/inheritance/test_basic.py +++ b/test/orm/inheritance/test_basic.py @@ -29,6 +29,7 @@ from sqlalchemy.orm import relationship from sqlalchemy.orm import Session from sqlalchemy.orm import synonym from sqlalchemy.orm.util import instance_str +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import eq_ @@ -2589,7 +2590,10 @@ class OptimizedLoadTest(fixtures.MappedTest): polymorphic_identity="sub", with_polymorphic=( "*", - base.outerjoin(sub).select().apply_labels().alias("foo"), + base.outerjoin(sub) + .select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .alias("foo"), ), ) sess = fixture_session() diff --git a/test/orm/inheritance/test_concrete.py b/test/orm/inheritance/test_concrete.py index f2f8d629b1..de0c72f68f 100644 --- a/test/orm/inheritance/test_concrete.py +++ b/test/orm/inheritance/test_concrete.py @@ -443,7 +443,7 @@ class ConcreteTest(fixtures.MappedTest): assert ( len( session.connection() - .execute(session.query(Employee).with_labels().statement) + .execute(session.query(Employee).statement) .fetchall() ) == 3 @@ -521,7 +521,6 @@ class ConcreteTest(fixtures.MappedTest): .execute( session.query(Employee) .with_polymorphic("*", pjoin, pjoin.c.type) - .with_labels() .statement ) .fetchall() diff --git a/test/orm/inheritance/test_poly_loading.py b/test/orm/inheritance/test_poly_loading.py index 2f31ab0c42..35822a29e9 100644 --- a/test/orm/inheritance/test_poly_loading.py +++ b/test/orm/inheritance/test_poly_loading.py @@ -10,6 +10,7 @@ from sqlalchemy.orm import selectin_polymorphic from sqlalchemy.orm import selectinload from sqlalchemy.orm import Session from sqlalchemy.orm import with_polymorphic +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures from sqlalchemy.testing.assertsql import AllOf @@ -434,7 +435,7 @@ class TestGeometries(GeometryFixtureBase): .select_from( a_table.join(c_table).outerjoin(d_table).outerjoin(e_table) ) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .alias("poly") ) diff --git a/test/orm/inheritance/test_polymorphic_rel.py b/test/orm/inheritance/test_polymorphic_rel.py index 581fa45fd9..de7723125a 100644 --- a/test/orm/inheritance/test_polymorphic_rel.py +++ b/test/orm/inheritance/test_polymorphic_rel.py @@ -156,7 +156,6 @@ class _PolymorphicTestBase(object): .order_by(Person.person_id) .limit(2) .offset(1) - .with_labels() .subquery() ) ), diff --git a/test/orm/inheritance/test_relationship.py b/test/orm/inheritance/test_relationship.py index 214be5e9aa..d983ddac94 100644 --- a/test/orm/inheritance/test_relationship.py +++ b/test/orm/inheritance/test_relationship.py @@ -16,6 +16,7 @@ from sqlalchemy.orm import Session from sqlalchemy.orm import sessionmaker from sqlalchemy.orm import subqueryload from sqlalchemy.orm import with_polymorphic +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures @@ -728,8 +729,10 @@ class SelfReferentialM2MTest(fixtures.MappedTest, AssertsCompiledSQL): sess.flush() # test that the join to Child2 doesn't alias Child1 in the select + + stmt = select(Child1).join(Child1.left_child2) eq_( - set(sess.query(Child1).join(Child1.left_child2)), + set(sess.execute(stmt).scalars().unique()), set([c11, c12, c13]), ) @@ -739,12 +742,14 @@ class SelfReferentialM2MTest(fixtures.MappedTest, AssertsCompiledSQL): ) # test __eq__() on property is annotating correctly + + stmt = ( + select(Child2) + .join(Child2.right_children) + .where(Child1.left_child2 == c22) + ) eq_( - set( - sess.query(Child2) - .join(Child2.right_children) - .filter(Child1.left_child2 == c22) - ), + set(sess.execute(stmt).scalars().unique()), set([c22]), ) @@ -753,17 +758,16 @@ class SelfReferentialM2MTest(fixtures.MappedTest, AssertsCompiledSQL): sess.query(Child2) .join(Child2.right_children) .filter(Child1.left_child2 == c22) - .with_labels() .statement, - "SELECT child2.id AS child2_id, parent.id AS parent_id, " - "parent.cls AS parent_cls FROM secondary AS secondary_1, " - "parent JOIN child2 ON parent.id = child2.id JOIN secondary AS " - "secondary_2 ON parent.id = secondary_2.left_id JOIN " - "(parent AS parent_1 JOIN child1 AS child1_1 " - "ON parent_1.id = child1_1.id) " - "ON parent_1.id = secondary_2.right_id WHERE " - "parent_1.id = secondary_1.right_id AND :param_1 = " - "secondary_1.left_id", + "SELECT child2.id, parent.id AS id_1, parent.cls " + "FROM secondary AS secondary_1, parent " + "JOIN child2 ON parent.id = child2.id " + "JOIN secondary AS secondary_2 ON parent.id = secondary_2.left_id " + "JOIN (parent AS parent_1 JOIN child1 AS child1_1 " + "ON parent_1.id = child1_1.id) ON parent_1.id = " + "secondary_2.right_id " + "WHERE parent_1.id = secondary_1.right_id " + "AND :param_1 = secondary_1.left_id", ) def test_query_crit_core_workaround(self): @@ -797,7 +801,7 @@ class SelfReferentialM2MTest(fixtures.MappedTest, AssertsCompiledSQL): ) self.assert_compile( - stmt.apply_labels(), + stmt.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT child2.id AS child2_id, parent.id AS parent_id, " "parent.cls AS parent_cls " "FROM secondary AS secondary_1, " @@ -822,16 +826,14 @@ class SelfReferentialM2MTest(fixtures.MappedTest, AssertsCompiledSQL): # the middle of "parent join child1" q = sess.query(Child1).options(joinedload("left_child2")) self.assert_compile( - q.limit(1).with_labels().statement, - "SELECT child1.id AS child1_id, parent.id AS parent_id, " - "parent.cls AS parent_cls, child2_1.id AS child2_1_id, " - "parent_1.id AS parent_1_id, parent_1.cls AS parent_1_cls " + q.limit(1).statement, + "SELECT child1.id, parent.id AS id_1, parent.cls, " + "child2_1.id AS id_2, parent_1.id AS id_3, parent_1.cls AS cls_1 " "FROM parent JOIN child1 ON parent.id = child1.id " - "LEFT OUTER JOIN (secondary AS secondary_1 JOIN " - "(parent AS parent_1 JOIN child2 AS child2_1 " - "ON parent_1.id = child2_1.id) " - "ON parent_1.id = secondary_1.left_id) " - "ON parent.id = secondary_1.right_id " + "LEFT OUTER JOIN (secondary AS secondary_1 " + "JOIN (parent AS parent_1 JOIN child2 AS child2_1 " + "ON parent_1.id = child2_1.id) ON parent_1.id = " + "secondary_1.left_id) ON parent.id = secondary_1.right_id " "LIMIT :param_1", checkparams={"param_1": 1}, ) @@ -839,9 +841,7 @@ class SelfReferentialM2MTest(fixtures.MappedTest, AssertsCompiledSQL): # another way to check eq_( sess.scalar( - select(func.count("*")).select_from( - q.limit(1).with_labels().subquery() - ) + select(func.count("*")).select_from(q.limit(1).subquery()) ), 1, ) @@ -1761,9 +1761,17 @@ class SubClassToSubClassMultiTest(AssertsCompiledSQL, fixtures.MappedTest): stmt = select(Sub2) - subq = aliased(Sub2, stmt.apply_labels().subquery()) + subq = aliased( + Sub2, + stmt.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).subquery(), + ) - stmt = select(subq).join(subq.ep1).join(Sub2.ep2).apply_labels() + stmt = ( + select(subq) + .join(subq.ep1) + .join(Sub2.ep2) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ) self.assert_compile( stmt, "SELECT anon_1.sub2_id AS anon_1_sub2_id, " @@ -1832,7 +1840,7 @@ class SubClassToSubClassMultiTest(AssertsCompiledSQL, fixtures.MappedTest): select(Parent, Sub2) .join(Parent.sub1) .join(Sub1.sub2) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .subquery() ) @@ -1845,7 +1853,7 @@ class SubClassToSubClassMultiTest(AssertsCompiledSQL, fixtures.MappedTest): select(palias, sub2alias) .join(sub2alias.ep1) .join(sub2alias.ep2) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) ) self.assert_compile( diff --git a/test/orm/inheritance/test_single.py b/test/orm/inheritance/test_single.py index 11c6bb2128..ececaf882f 100644 --- a/test/orm/inheritance/test_single.py +++ b/test/orm/inheritance/test_single.py @@ -18,6 +18,7 @@ from sqlalchemy.orm import relationship from sqlalchemy.orm import Session from sqlalchemy.orm import subqueryload from sqlalchemy.orm import with_polymorphic +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures @@ -312,7 +313,10 @@ class SingleInheritanceTest(testing.AssertsCompiledSQL, fixtures.MappedTest): Engineer = self.classes.Engineer stmt = select(Engineer) - subq = aliased(Engineer, stmt.apply_labels().subquery()) + subq = aliased( + Engineer, + stmt.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).subquery(), + ) # so here we have an extra "WHERE type in ()", because # both the inner and the outer queries have the Engineer entity. @@ -321,7 +325,7 @@ class SingleInheritanceTest(testing.AssertsCompiledSQL, fixtures.MappedTest): # legacy from_self() takes care of this because it applies # _enable_single_crit at that moment. - stmt = select(subq).apply_labels() + stmt = select(subq).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( stmt, "SELECT anon_1.employees_employee_id AS " @@ -526,7 +530,7 @@ class SingleInheritanceTest(testing.AssertsCompiledSQL, fixtures.MappedTest): ), ) ) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .subquery() ) eq_( @@ -1638,7 +1642,7 @@ class SingleFromPolySelectableTest( null().label("manager_id"), ) .select_from(employee.join(manager)) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .union_all( select( employee.c.id, @@ -1649,7 +1653,7 @@ class SingleFromPolySelectableTest( engineer.c.manager_id, ) .select_from(employee.join(engineer)) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) ) .alias() ) diff --git a/test/orm/test_deprecations.py b/test/orm/test_deprecations.py index 90bb291f8c..e15bb4674d 100644 --- a/test/orm/test_deprecations.py +++ b/test/orm/test_deprecations.py @@ -455,9 +455,7 @@ class DeprecatedQueryTest(_fixtures.FixtureTest, AssertsCompiledSQL): .union(users.select(users.c.id > 7)) .alias("ulist") .outerjoin(addresses) - .select( - use_labels=True, order_by=[text("ulist.id"), addresses.c.id] - ) + .select(order_by=[text("ulist.id"), addresses.c.id]) ) sess = fixture_session() @@ -492,7 +490,7 @@ class DeprecatedQueryTest(_fixtures.FixtureTest, AssertsCompiledSQL): .union(users.select(users.c.id > 7)) .alias("ulist") .outerjoin(adalias) - .select(use_labels=True, order_by=[text("ulist.id"), adalias.c.id]) + .select(order_by=[text("ulist.id"), adalias.c.id]) ) def go(): @@ -513,16 +511,45 @@ class DeprecatedQueryTest(_fixtures.FixtureTest, AssertsCompiledSQL): sess = fixture_session() with self._expect_implicit_subquery(): - self.assert_compile( - sess.query(users) - .select_entity_from(users.select()) - .with_labels() - .statement, - "SELECT users.id AS users_id, users.name AS users_name " - "FROM users, " - "(SELECT users.id AS id, users.name AS name FROM users) " - "AS anon_1", - ) + stmt = sess.query(users).select_entity_from(users.select()) + + with testing.expect_deprecated_20(r"The Query.with_labels\(\)"): + stmt = stmt.apply_labels().statement + self.assert_compile( + stmt, + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users, " + "(SELECT users.id AS id, users.name AS name FROM users) " + "AS anon_1", + ) + + def test_apply_labels(self): + User = self.classes.User + + with testing.expect_deprecated_20( + r"The Query.with_labels\(\) and Query.apply_labels\(\) " + "method is considered legacy" + ): + q = fixture_session().query(User).apply_labels().statement + + self.assert_compile( + q, + "SELECT users.id AS users_id, users.name AS users_name FROM users", + ) + + def test_with_labels(self): + User = self.classes.User + + with testing.expect_deprecated_20( + r"The Query.with_labels\(\) and Query.apply_labels\(\) " + "method is considered legacy" + ): + q = fixture_session().query(User).with_labels().statement + + self.assert_compile( + q, + "SELECT users.id AS users_id, users.name AS users_name FROM users", + ) def test_join(self): users, Address, User = ( @@ -3058,9 +3085,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): .union(users.select(users.c.id > 7)) .alias("ulist") .outerjoin(addresses) - .select( - use_labels=True, order_by=[text("ulist.id"), addresses.c.id] - ) + .select(order_by=[text("ulist.id"), addresses.c.id]) ) sess = fixture_session() q = sess.query(User) @@ -3094,9 +3119,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): .union(users.select(users.c.id > 7)) .alias("ulist") .outerjoin(addresses) - .select( - use_labels=True, order_by=[text("ulist.id"), addresses.c.id] - ) + .select(order_by=[text("ulist.id"), addresses.c.id]) ) sess = fixture_session() q = sess.query(User) @@ -3128,7 +3151,6 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): selectquery = users.outerjoin(addresses).select( users.c.id < 10, - use_labels=True, order_by=[users.c.id, addresses.c.id], ) q = sess.query(User) @@ -3173,7 +3195,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): adalias = addresses.alias("adalias") selectquery = users.outerjoin(adalias).select( - use_labels=True, order_by=[users.c.id, adalias.c.id] + order_by=[users.c.id, adalias.c.id] ) # note this has multiple problems because we aren't giving Query @@ -3207,7 +3229,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): adalias = addresses.alias("adalias") selectquery = users.outerjoin(adalias).select( - use_labels=True, order_by=[users.c.id, adalias.c.id] + order_by=[users.c.id, adalias.c.id] ) # note this has multiple problems because we aren't giving Query @@ -3243,7 +3265,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): users.outerjoin(oalias) .outerjoin(order_items) .outerjoin(ialias) - .select(use_labels=True) + .select() .order_by(users.c.id, oalias.c.id, ialias.c.id) ) @@ -3284,7 +3306,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): users.outerjoin(oalias) .outerjoin(order_items) .outerjoin(ialias) - .select(use_labels=True) + .select() .order_by(users.c.id, oalias.c.id, ialias.c.id) ) diff --git a/test/orm/test_eager_relations.py b/test/orm/test_eager_relations.py index 0f67aa94db..d575b85f1b 100644 --- a/test/orm/test_eager_relations.py +++ b/test/orm/test_eager_relations.py @@ -28,6 +28,7 @@ from sqlalchemy.orm import relationship from sqlalchemy.orm import Session from sqlalchemy.orm import undefer from sqlalchemy.sql import operators +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import eq_ @@ -1081,9 +1082,9 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL): # more rows. u2 = users.alias("u2") s = sa.union_all( - u2.select(use_labels=True), - u2.select(use_labels=True), - u2.select(use_labels=True), + u2.select(), + u2.select(), + u2.select(), ).alias("u") mapper( @@ -1103,10 +1104,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL): def go(): result = ( - q.filter(s.c.u2_id == User.id) - .distinct() - .order_by(User.id) - .all() + q.filter(s.c.id == User.id).distinct().order_by(User.id).all() ) eq_(self.static.user_address_result, result) @@ -4791,7 +4789,7 @@ class MixedEntitiesTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL): sess.query(User, oalias) .join(User.orders) .options(joinedload(oalias.items)) - .with_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .statement, "SELECT users.id AS users_id, users.name AS users_name, " "orders_1.id AS orders_1_id, " diff --git a/test/orm/test_froms.py b/test/orm/test_froms.py index f622bff025..1464cfc284 100644 --- a/test/orm/test_froms.py +++ b/test/orm/test_froms.py @@ -30,6 +30,7 @@ from sqlalchemy.orm import Session from sqlalchemy.orm.util import join from sqlalchemy.sql import column from sqlalchemy.sql import table +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL @@ -274,19 +275,26 @@ class QueryCorrelatesLikeSelect(QueryTest, AssertsCompiledSQL): "FROM users) AS anon_1", ) - def test_correlate_to_union_newstyle(self): + def test_correlate_to_union_w_labels_newstyle(self): User = self.classes.User - q = select(User).apply_labels() + q = select(User).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) - q = select(User).union(q).apply_labels().subquery() + q = ( + select(User) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .union(q) + .subquery() + ) u_alias = aliased(User) raw_subq = exists().where(u_alias.id > q.c[0]) self.assert_compile( - select(q, raw_subq).apply_labels(), + select(q, raw_subq).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT anon_1.users_id AS anon_1_users_id, " "anon_1.users_name AS anon_1_users_name, " "EXISTS (SELECT * FROM users AS users_1 " @@ -297,6 +305,27 @@ class QueryCorrelatesLikeSelect(QueryTest, AssertsCompiledSQL): "FROM users) AS anon_1", ) + def test_correlate_to_union_newstyle(self): + User = self.classes.User + + q = select(User) + + q = select(User).union(q).subquery() + + u_alias = aliased(User) + + raw_subq = exists().where(u_alias.id > q.c[0]) + + self.assert_compile( + select(q, raw_subq), + "SELECT anon_1.id, anon_1.name, EXISTS " + "(SELECT * FROM users AS users_1 WHERE users_1.id > anon_1.id) " + "AS anon_2 FROM (SELECT users.id AS id, users.name AS name " + "FROM users " + "UNION SELECT users.id AS id, users.name AS name FROM users) " + "AS anon_1", + ) + class RawSelectTest(QueryTest, AssertsCompiledSQL): """compare a bunch of select() tests with the equivalent Query using @@ -317,7 +346,7 @@ class RawSelectTest(QueryTest, AssertsCompiledSQL): self.assert_compile( sess.query(users) .select_entity_from(users.select().subquery()) - .with_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .statement, "SELECT users.id AS users_id, users.name AS users_name " "FROM users, " @@ -326,7 +355,7 @@ class RawSelectTest(QueryTest, AssertsCompiledSQL): self.assert_compile( sess.query(users, exists([1], from_obj=addresses)) - .with_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .statement, "SELECT users.id AS users_id, users.name AS users_name, EXISTS " "(SELECT 1 FROM addresses) AS anon_1 FROM users", @@ -347,7 +376,7 @@ class RawSelectTest(QueryTest, AssertsCompiledSQL): self.assert_compile( sess.query(users, s.c.email) .select_entity_from(users.join(s, s.c.id == users.c.id)) - .with_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .statement, "SELECT users.id AS users_id, users.name AS users_name, " "anon_1.email AS anon_1_email " @@ -480,7 +509,7 @@ class EntityFromSubqueryTest(QueryTest, AssertsCompiledSQL): select(User.id) .group_by(User.id) .having(User.id > 5) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .subquery() ) @@ -501,7 +530,7 @@ class EntityFromSubqueryTest(QueryTest, AssertsCompiledSQL): subq = ( select(User) .options(joinedload(User.addresses)) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .subquery() ) @@ -724,7 +753,7 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): q1 = select(c1, c2).where(c1 == "dog") q2 = select(c1, c2).where(c1 == "cat") subq = q1.union(q2).subquery() - q3 = select(subq).apply_labels() + q3 = select(subq).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( q3.order_by(subq.c.c1), @@ -738,20 +767,31 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): from sqlalchemy.sql import column t1 = table("t1", column("c1"), column("c2")) - stmt = select(t1.c.c1, t1.c.c2).where(t1.c.c1 == "dog").apply_labels() + stmt = ( + select(t1.c.c1, t1.c.c2) + .where(t1.c.c1 == "dog") + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ) - subq1 = stmt.subquery("anon_2").select().apply_labels() + subq1 = ( + stmt.subquery("anon_2") + .select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ) subq2 = subq1.subquery("anon_1") - q1 = select(subq2).apply_labels() + q1 = select(subq2).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( # as in test_anonymous_expression_from_self_twice_newstyle_wlabels, - # apply_labels() means the subquery cols have long names. however, - # here we illustrate if they did use apply_labels(), but they also + # set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) means the + # subquery cols have long names. however, + # here we illustrate if they did use + # set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), but they also # named the subqueries explicitly as one would certainly do if they - # were using apply_labels(), we can get at that column based on how + # were using set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), + # we can get at that column based on how # it is aliased, no different than plain SQL. q1.order_by(subq2.c.anon_2_t1_c1), "SELECT anon_1.anon_2_t1_c1 " @@ -769,9 +809,13 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): c1, c2 = column("c1"), column("c2") subq = select(c1, c2).where(c1 == "dog").subquery() - subq2 = select(subq).apply_labels().subquery() + subq2 = ( + select(subq) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) - stmt = select(subq2).apply_labels() + stmt = select(subq2).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( # because of the apply labels we don't have simple keys on @@ -824,7 +868,7 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): q1 = select(c1.label("foo"), c2.label("bar")).where(c1 == "dog") q2 = select(c1.label("foo"), c2.label("bar")).where(c1 == "cat") subq = union(q1, q2).subquery() - q3 = select(subq).apply_labels() + q3 = select(subq).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( q3.order_by(subq.c.foo), "SELECT anon_1.foo AS anon_1_foo, anon_1.bar AS anon_1_bar FROM " @@ -842,7 +886,9 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): sess = fixture_session() q1 = sess.query(User.id).filter(User.id > 5) - uq = aliased(User, q1.apply_labels().subquery()) + uq = aliased( + User, q1.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).subquery() + ) aa = aliased(Address) q1 = ( @@ -869,7 +915,12 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): addresses = self.tables.addresses sess = fixture_session() - q1 = sess.query(User.id).filter(User.id > 5).apply_labels().subquery() + q1 = ( + sess.query(User.id) + .filter(User.id > 5) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) uq = aliased(User, q1) @@ -1029,9 +1080,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): .union(users.select(users.c.id > 7)) .alias("ulist") .outerjoin(addresses) - .select( - use_labels=True, order_by=[text("ulist.id"), addresses.c.id] - ) + .select(order_by=[text("ulist.id"), addresses.c.id]) ) sess = fixture_session() q = sess.query(User) @@ -1058,9 +1107,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): .union(users.select(users.c.id > 7)) .alias("ulist") .outerjoin(addresses) - .select( - use_labels=True, order_by=[text("ulist.id"), addresses.c.id] - ) + .select(order_by=[text("ulist.id"), addresses.c.id]) ) sess = fixture_session() q = sess.query(User) @@ -1088,9 +1135,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): .union(users.select(users.c.id > 7)) .alias("ulist") .outerjoin(addresses) - .select( - use_labels=True, order_by=[text("ulist.id"), addresses.c.id] - ) + .select(order_by=[text("ulist.id"), addresses.c.id]) ) sess = fixture_session() @@ -1124,7 +1169,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): .union(users.select(users.c.id > 7)) .alias("ulist") .outerjoin(adalias) - .select(use_labels=True, order_by=[text("ulist.id"), adalias.c.id]) + .select(order_by=[text("ulist.id"), adalias.c.id]) ) def go(): @@ -1151,7 +1196,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): .order_by(User.id, addresses.c.id) ) self.assert_compile( - q.with_labels().statement, + q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).statement, "SELECT addresses.id AS addresses_id, " "addresses.user_id AS addresses_user_id, " "addresses.email_address AS " @@ -1201,7 +1246,6 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): selectquery = users.outerjoin(addresses).select( users.c.id < 10, - use_labels=True, order_by=[users.c.id, addresses.c.id], ) @@ -1228,7 +1272,6 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): selectquery = users.outerjoin(addresses).select( users.c.id < 10, - use_labels=True, order_by=[users.c.id, addresses.c.id], ) @@ -1286,7 +1329,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): users.outerjoin(oalias) .outerjoin(order_items) .outerjoin(ialias) - .select(use_labels=True) + .select() .order_by(users.c.id, oalias.c.id, ialias.c.id) ) @@ -2135,9 +2178,12 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): Order.id, Order.description, literal_column("'q'").label("foo") ).where(Order.description == "order 3") - subq = aliased(Order, stmt.apply_labels().subquery()) + subq = aliased( + Order, + stmt.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).subquery(), + ) - stmt = select(subq).apply_labels() + stmt = select(subq).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( stmt, "SELECT anon_1.orders_id AS " @@ -2182,7 +2228,7 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): sess = fixture_session(future=True) selectquery = users.outerjoin(addresses).select( - use_labels=True, order_by=[users.c.id, addresses.c.id] + order_by=[users.c.id, addresses.c.id] ) result = sess.execute( diff --git a/test/orm/test_joins.py b/test/orm/test_joins.py index 79d3db8f0b..de5a5c92d5 100644 --- a/test/orm/test_joins.py +++ b/test/orm/test_joins.py @@ -24,6 +24,7 @@ from sqlalchemy.orm import outerjoin from sqlalchemy.orm import relationship from sqlalchemy.orm import Session from sqlalchemy.orm import synonym +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL @@ -386,7 +387,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): subq = ( sess.query(User) .filter(User.name == "ed") - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .subquery() ) @@ -2199,7 +2200,7 @@ class SelfReferentialTest(fixtures.MappedTest, AssertsCompiledSQL): subq = ( sess.query(Node) .filter(Node.data == "n122") - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .subquery() ) @@ -2226,7 +2227,7 @@ class SelfReferentialTest(fixtures.MappedTest, AssertsCompiledSQL): .filter(Node.data == "n122") .filter(parent.data == "n12") .filter(grandparent.data == "n1") - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .subquery() ) na = aliased(Node, subq) diff --git a/test/orm/test_lambdas.py b/test/orm/test_lambdas.py index 7591f844ff..a1657d746c 100644 --- a/test/orm/test_lambdas.py +++ b/test/orm/test_lambdas.py @@ -14,6 +14,7 @@ from sqlalchemy.orm import relationship from sqlalchemy.orm import selectinload from sqlalchemy.orm import Session from sqlalchemy.orm import subqueryload +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import eq_ @@ -341,19 +342,19 @@ class LambdaTest(QueryTest, AssertsCompiledSQL): ), lambda User, Address: select(lambda: User) .join(lambda: Address) - .apply_labels(), + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), lambda User, Address: select(lambda: User) .join(lambda: User.addresses) - .apply_labels(), + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), lambda User, Address: select(lambda: User) .join(lambda: Address, lambda: User.addresses) - .apply_labels(), + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), lambda User, Address: select(lambda: User) .join(Address, lambda: User.addresses) - .apply_labels(), + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), lambda User, Address: select(lambda: User) .join(lambda: Address, User.addresses) - .apply_labels(), + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), argnames="test_case", ) def test_join_entity_arg(self, plain_fixture, test_case): diff --git a/test/orm/test_lazy_relations.py b/test/orm/test_lazy_relations.py index 921a1b15de..653bf22db2 100644 --- a/test/orm/test_lazy_relations.py +++ b/test/orm/test_lazy_relations.py @@ -336,13 +336,11 @@ class LazyTest(_fixtures.FixtureTest): # use a union all to get a lot of rows to join against u2 = users.alias("u2") s = sa.union_all( - u2.select(use_labels=True), - u2.select(use_labels=True), - u2.select(use_labels=True), + u2.select(), + u2.select(), + u2.select(), ).alias("u") - result = ( - q.filter(s.c.u2_id == User.id).order_by(User.id).distinct().all() - ) + result = q.filter(s.c.id == User.id).order_by(User.id).distinct().all() eq_(self.static.user_all_result, result) def test_uselist_false_warning(self): diff --git a/test/orm/test_query.py b/test/orm/test_query.py index 7546ba1626..0fb5e7dd65 100644 --- a/test/orm/test_query.py +++ b/test/orm/test_query.py @@ -1723,7 +1723,8 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): a1 = ( session.query(User.id) .filter(User.id == 7) - .subquery(with_labels=True) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() ) assert a1.c.users_id is not None @@ -1804,8 +1805,8 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): s = fixture_session() - q1 = s.query(User).filter(User.name == "ed").with_labels() - q2 = s.query(User).filter(User.name == "fred").with_labels() + q1 = s.query(User).filter(User.name == "ed") + q2 = s.query(User).filter(User.name == "fred") eq_( s.query(User) .from_statement(union(q1, q2).order_by("users_name")) @@ -1818,15 +1819,12 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): s = fixture_session() - # this is actually not legal on most DBs since the subquery has no - # alias q1 = s.query(User).filter(User.name == "ed") self.assert_compile( - select(q1.with_labels().subquery()), - "SELECT anon_1.users_id, anon_1.users_name FROM " - "(SELECT users.id AS users_id, " - "users.name AS users_name " + select(q1.subquery()), + "SELECT anon_1.id, anon_1.name FROM " + "(SELECT users.id AS id, users.name AS name " "FROM users WHERE users.name = :name_1) AS anon_1", ) @@ -1857,11 +1855,9 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): q1 = s.query(User.id, User.name).group_by(User.name) self.assert_compile( - select(q1.with_labels().subquery()), - "SELECT anon_1.users_id, anon_1.users_name FROM " - "(SELECT users.id AS users_id, " - "users.name AS users_name FROM users GROUP BY users.name) " - "AS anon_1", + select(q1.subquery()), + "SELECT anon_1.id, anon_1.name FROM (SELECT users.id AS id, " + "users.name AS name FROM users GROUP BY users.name) AS anon_1", ) def test_group_by_append(self): @@ -1872,11 +1868,10 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): # test append something to group_by self.assert_compile( - select(q1.group_by(User.id).with_labels().subquery()), - "SELECT anon_1.users_id, anon_1.users_name FROM " - "(SELECT users.id AS users_id, " - "users.name AS users_name FROM users " - "GROUP BY users.name, users.id) AS anon_1", + select(q1.group_by(User.id).subquery()), + "SELECT anon_1.id, anon_1.name FROM " + "(SELECT users.id AS id, users.name AS name " + "FROM users GROUP BY users.name, users.id) AS anon_1", ) def test_group_by_cancellation(self): @@ -1886,20 +1881,17 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): q1 = s.query(User.id, User.name).group_by(User.name) # test cancellation by using None, replacement with something else self.assert_compile( - select( - q1.group_by(None).group_by(User.id).with_labels().subquery() - ), - "SELECT anon_1.users_id, anon_1.users_name FROM " - "(SELECT users.id AS users_id, " - "users.name AS users_name FROM users GROUP BY users.id) AS anon_1", + select(q1.group_by(None).group_by(User.id).subquery()), + "SELECT anon_1.id, anon_1.name FROM " + "(SELECT users.id AS id, users.name AS name " + "FROM users GROUP BY users.id) AS anon_1", ) # test cancellation by using None, replacement with nothing self.assert_compile( - select(q1.group_by(None).with_labels().subquery()), - "SELECT anon_1.users_id, anon_1.users_name FROM " - "(SELECT users.id AS users_id, " - "users.name AS users_name FROM users) AS anon_1", + select(q1.group_by(None).subquery()), + "SELECT anon_1.id, anon_1.name FROM (SELECT users.id AS id, " + "users.name AS name FROM users) AS anon_1", ) def test_group_by_cancelled_still_present(self): @@ -1916,11 +1908,10 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): q1 = s.query(User.id, User.name).order_by(User.name) self.assert_compile( - select(q1.with_labels().subquery()), - "SELECT anon_1.users_id, anon_1.users_name FROM " - "(SELECT users.id AS users_id, " - "users.name AS users_name FROM users ORDER BY users.name) " - "AS anon_1", + select(q1.subquery()), + "SELECT anon_1.id, anon_1.name FROM " + "(SELECT users.id AS id, users.name AS name " + "FROM users ORDER BY users.name) AS anon_1", ) def test_order_by_append(self): @@ -1931,11 +1922,10 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): # test append something to order_by self.assert_compile( - select(q1.order_by(User.id).with_labels().subquery()), - "SELECT anon_1.users_id, anon_1.users_name FROM " - "(SELECT users.id AS users_id, " - "users.name AS users_name FROM users " - "ORDER BY users.name, users.id) AS anon_1", + select(q1.order_by(User.id).subquery()), + "SELECT anon_1.id, anon_1.name FROM " + "(SELECT users.id AS id, users.name AS name " + "FROM users ORDER BY users.name, users.id) AS anon_1", ) def test_order_by_cancellation(self): @@ -1945,20 +1935,16 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): q1 = s.query(User.id, User.name).order_by(User.name) # test cancellation by using None, replacement with something else self.assert_compile( - select( - q1.order_by(None).order_by(User.id).with_labels().subquery() - ), - "SELECT anon_1.users_id, anon_1.users_name FROM " - "(SELECT users.id AS users_id, " - "users.name AS users_name FROM users ORDER BY users.id) AS anon_1", + select(q1.order_by(None).order_by(User.id).subquery()), + "SELECT anon_1.id, anon_1.name FROM (SELECT users.id AS id, " + "users.name AS name FROM users ORDER BY users.id) AS anon_1", ) # test cancellation by using None, replacement with nothing self.assert_compile( - select(q1.order_by(None).with_labels().subquery()), - "SELECT anon_1.users_id, anon_1.users_name FROM " - "(SELECT users.id AS users_id, " - "users.name AS users_name FROM users) AS anon_1", + select(q1.order_by(None).subquery()), + "SELECT anon_1.id, anon_1.name FROM (SELECT users.id AS id, " + "users.name AS name FROM users) AS anon_1", ) def test_order_by_cancellation_false(self): @@ -1968,20 +1954,16 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): q1 = s.query(User.id, User.name).order_by(User.name) # test cancellation by using None, replacement with something else self.assert_compile( - select( - q1.order_by(False).order_by(User.id).with_labels().subquery() - ), - "SELECT anon_1.users_id, anon_1.users_name FROM " - "(SELECT users.id AS users_id, " - "users.name AS users_name FROM users ORDER BY users.id) AS anon_1", + select(q1.order_by(False).order_by(User.id).subquery()), + "SELECT anon_1.id, anon_1.name FROM (SELECT users.id AS id, " + "users.name AS name FROM users ORDER BY users.id) AS anon_1", ) # test cancellation by using None, replacement with nothing self.assert_compile( - select(q1.order_by(False).with_labels().subquery()), - "SELECT anon_1.users_id, anon_1.users_name FROM " - "(SELECT users.id AS users_id, " - "users.name AS users_name FROM users) AS anon_1", + select(q1.order_by(False).subquery()), + "SELECT anon_1.id, anon_1.name FROM (SELECT users.id AS id, " + "users.name AS name FROM users) AS anon_1", ) def test_order_by_cancelled_allows_assertions(self): @@ -2114,7 +2096,9 @@ class ColumnPropertyTest(_fixtures.FixtureTest, AssertsCompiledSQL): assert_raises_message( sa.exc.CompileError, "Can't resolve label reference for ORDER BY / GROUP BY", - q.with_labels().statement.compile, + q.set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ).statement.compile, ) def test_order_by_column_labeled_prop_attr_aliased_one(self): @@ -4032,7 +4016,7 @@ class DistinctTest(QueryTest, AssertsCompiledSQL): ) .distinct() .order_by(User.id, User.name, Address.email_address) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .subquery() ) @@ -4192,7 +4176,7 @@ class DistinctTest(QueryTest, AssertsCompiledSQL): ) .distinct(Address.email_address) .order_by(User.id, User.name, Address.email_address) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .subquery() ) @@ -4252,7 +4236,9 @@ class DistinctTest(QueryTest, AssertsCompiledSQL): sa_exc.CompileError, "Can't resolve label reference for ORDER BY / " "GROUP BY / DISTINCT etc.", - q.with_labels().statement.compile, + q.set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ).statement.compile, dialect=postgresql.dialect(), ) @@ -5099,7 +5085,9 @@ class TextTest(QueryTest, AssertsCompiledSQL): assert_raises_message( sa_exc.CompileError, "Can't resolve label reference for ORDER BY / GROUP BY.", - q.with_labels().statement.compile, + q.set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ).statement.compile, ) def test_order_by_w_eager_two(self): @@ -5115,7 +5103,9 @@ class TextTest(QueryTest, AssertsCompiledSQL): assert_raises_message( sa_exc.CompileError, "Can't resolve label reference for ORDER BY / GROUP BY.", - q.with_labels().statement.compile, + q.set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ).statement.compile, ) def test_order_by_w_eager_three(self): diff --git a/test/profiles.txt b/test/profiles.txt index d96f802f08..2a43da63aa 100644 --- a/test/profiles.txt +++ b/test/profiles.txt @@ -48,22 +48,6 @@ test.aaa_profiling.test_compiler.CompileTest.test_insert x86_64_linux_cpython_3. # TEST: test.aaa_profiling.test_compiler.CompileTest.test_select -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_mariadb_mysqldb_dbapiunicode_cextensions 168 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_mariadb_mysqldb_dbapiunicode_nocextensions 168 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_mariadb_pymysql_dbapiunicode_cextensions 168 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_mariadb_pymysql_dbapiunicode_nocextensions 168 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_mssql_pyodbc_dbapiunicode_cextensions 168 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_mssql_pyodbc_dbapiunicode_nocextensions 168 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_mysql_mysqldb_dbapiunicode_cextensions 162 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_mysql_mysqldb_dbapiunicode_nocextensions 162 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_mysql_pymysql_dbapiunicode_cextensions 162 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_mysql_pymysql_dbapiunicode_nocextensions 162 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_oracle_cx_oracle_dbapiunicode_cextensions 152 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_oracle_cx_oracle_dbapiunicode_nocextensions 165 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_postgresql_psycopg2_dbapiunicode_cextensions 168 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_postgresql_psycopg2_dbapiunicode_nocextensions 168 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_sqlite_pysqlite_dbapiunicode_cextensions 168 -test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_2.7_sqlite_pysqlite_dbapiunicode_nocextensions 168 test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_3.8_mariadb_mysqldb_dbapiunicode_cextensions 183 test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_3.8_mariadb_mysqldb_dbapiunicode_nocextensions 183 test.aaa_profiling.test_compiler.CompileTest.test_select x86_64_linux_cpython_3.8_mariadb_pymysql_dbapiunicode_cextensions 183 diff --git a/test/sql/test_compare.py b/test/sql/test_compare.py index 1ac3613f74..fc9fd0926c 100644 --- a/test/sql/test_compare.py +++ b/test/sql/test_compare.py @@ -61,6 +61,7 @@ from sqlalchemy.sql.lambdas import LambdaOptions from sqlalchemy.sql.selectable import _OffsetLimitParam from sqlalchemy.sql.selectable import AliasedReturnsRows from sqlalchemy.sql.selectable import FromGrouping +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.sql.selectable import Select from sqlalchemy.sql.selectable import Selectable from sqlalchemy.sql.selectable import SelectStatementGrouping @@ -363,7 +364,9 @@ class CoreFixtures(object): select(table_a.c.b, table_a.c.a) .limit(literal_column("foobar")) .offset(10), - select(table_a.c.b, table_a.c.a).apply_labels(), + select(table_a.c.b, table_a.c.a).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), select(table_a.c.a).where(table_a.c.b == 5), select(table_a.c.a) .where(table_a.c.b == 5) diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 4429753ecb..fea75d6791 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -81,6 +81,8 @@ from sqlalchemy.sql.elements import BooleanClauseList from sqlalchemy.sql.expression import ClauseElement from sqlalchemy.sql.expression import ClauseList from sqlalchemy.sql.expression import HasPrefixes +from sqlalchemy.sql.selectable import LABEL_STYLE_NONE +from sqlalchemy.sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL @@ -577,7 +579,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): sq = ( select(table1, table2) .where(and_(table1.c.myid == 7, table2.c.otherid == table1.c.myid)) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .alias("sq") ) @@ -597,7 +599,11 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "sq.myothertable_othername FROM (%s) AS sq" % sqstring, ) - sq2 = select(sq).apply_labels().alias("sq2") + sq2 = ( + select(sq) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .alias("sq2") + ) self.assert_compile( sq2.select(), @@ -622,22 +628,27 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): def test_use_labels(self): self.assert_compile( - select(table1.c.myid == 5).apply_labels(), + select(table1.c.myid == 5).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT mytable.myid = :myid_1 AS anon_1 FROM mytable", ) self.assert_compile( - select(func.foo()).apply_labels(), "SELECT foo() AS foo_1" + select(func.foo()).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), + "SELECT foo() AS foo_1", ) # this is native_boolean=False for default dialect self.assert_compile( - select(not_(True)).apply_labels(), + select(not_(True)).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT :param_1 = 0 AS anon_1", ) self.assert_compile( - select(cast("data", Integer)).apply_labels(), + select(cast("data", Integer)).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT CAST(:param_1 AS INTEGER) AS anon_1", ) @@ -653,13 +664,17 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( - select(keyed).apply_labels(), + select(keyed).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT keyed.x AS keyed_x, keyed.y AS " "keyed_y, keyed.z AS keyed_z FROM keyed", ) self.assert_compile( - select(select(keyed).apply_labels().subquery()).apply_labels(), + select( + select(keyed) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT anon_1.keyed_x AS anon_1_keyed_x, " "anon_1.keyed_y AS anon_1_keyed_y, " "anon_1.keyed_z AS anon_1_keyed_z " @@ -768,7 +783,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): foo = table("foo", column("id"), column("bar_id")) foo_bar = table("foo_bar", column("id")) - stmt = select(foo, foo_bar).apply_labels() + stmt = select(foo, foo_bar).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) self.assert_compile( stmt, "SELECT foo.id AS foo_id, foo.bar_id AS foo_bar_id, " @@ -812,7 +829,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): foo.c.bar_id, foo_bar.c.id, foo_bar.c.id, - ).apply_labels() + ).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( stmt, "SELECT foo.id AS foo_id, " @@ -830,7 +847,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): # of the same column are not used. only the label applied to the # first occurrence of each column is used self.assert_compile( - select(stmt.subquery()), + select(stmt.subquery()).set_label_style(LABEL_STYLE_NONE), "SELECT " "anon_1.foo_id, " # from 1st foo.id in derived (line 1) "anon_1.foo_bar_id, " # from 1st foo.bar_id in derived (line 2) @@ -856,17 +873,23 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): def test_dupe_columns_use_labels(self): t = table("t", column("a"), column("b")) self.assert_compile( - select(t.c.a, t.c.a, t.c.b, t.c.a).apply_labels(), + select(t.c.a, t.c.a, t.c.b, t.c.a).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, " "t.a AS t_a__1 FROM t", ) def test_dupe_columns_use_labels_derived_selectable(self): t = table("t", column("a"), column("b")) - stmt = select(t.c.a, t.c.a, t.c.b, t.c.a).apply_labels().subquery() + stmt = ( + select(t.c.a, t.c.a, t.c.b, t.c.a) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) self.assert_compile( - select(stmt), + select(stmt).set_label_style(LABEL_STYLE_NONE), "SELECT anon_1.t_a, anon_1.t_a, anon_1.t_b, anon_1.t_a FROM " "(SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, t.a AS t_a__1 " "FROM t) AS anon_1", @@ -877,19 +900,25 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): a, b, a_a = t.c.a, t.c.b, t.c.a._annotate({"some_orm_thing": True}) self.assert_compile( - select(a, a_a, b, a_a).apply_labels(), + select(a, a_a, b, a_a).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, " "t.a AS t_a__1 FROM t", ) self.assert_compile( - select(a_a, a, b, a_a).apply_labels(), + select(a_a, a, b, a_a).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, " "t.a AS t_a__1 FROM t", ) self.assert_compile( - select(a_a, a_a, b, a).apply_labels(), + select(a_a, a_a, b, a).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, " "t.a AS t_a__1 FROM t", ) @@ -897,10 +926,14 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): def test_dupe_columns_use_labels_derived_selectable_mix_annotations(self): t = table("t", column("a"), column("b")) a, b, a_a = t.c.a, t.c.b, t.c.a._annotate({"some_orm_thing": True}) - stmt = select(a, a_a, b, a_a).apply_labels().subquery() + stmt = ( + select(a, a_a, b, a_a) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) self.assert_compile( - select(stmt), + select(stmt).set_label_style(LABEL_STYLE_NONE), "SELECT anon_1.t_a, anon_1.t_a, anon_1.t_b, anon_1.t_a FROM " "(SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, t.a AS t_a__1 " "FROM t) AS anon_1", @@ -918,7 +951,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): foo_bar.c.id, foo_bar__id, foo_bar__id, - ).apply_labels() + ).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( stmt, @@ -935,7 +968,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): # second and third occurrences of a.c.a are labeled, but are # dupes of each other. self.assert_compile( - select(a.c.a, a.c.a, a.c.b, a.c.a).apply_labels(), + select(a.c.a, a.c.a, a.c.b, a.c.a).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT t_1.a AS t_1_a, t_1.a AS t_1_a__1, t_1.b AS t_1_b, " "t_1.a AS t_1_a__1 " "FROM t AS t_1", @@ -945,9 +980,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): """test nested anonymous label generation.""" s1 = table1.select() s2 = s1.alias() - s3 = select(s2).apply_labels() + s3 = select(s2).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) s4 = s3.alias() - s5 = select(s4).apply_labels() + s5 = select(s4).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( s5, "SELECT anon_1.anon_2_myid AS " @@ -966,7 +1001,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): def test_nested_label_targeting_keyed(self): s1 = keyed.select() s2 = s1.alias() - s3 = select(s2).apply_labels() + s3 = select(s2).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( s3, "SELECT anon_1.x AS anon_1_x, " @@ -977,7 +1012,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): ) s4 = s3.alias() - s5 = select(s4).apply_labels() + s5 = select(s4).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( s5, "SELECT anon_1.anon_2_x AS anon_1_anon_2_x, " @@ -997,7 +1032,11 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): jj = select(table1.c[name]).subquery() jjj = join(table1, jj, table1.c[name] == jj.c[name]) - j2 = jjj.select().apply_labels().subquery("foo") + j2 = ( + jjj.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery("foo") + ) self.assert_compile( j2.select(), @@ -1172,8 +1211,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( select(s, table1), "SELECT sq2.myid, sq2.name, " - "sq2.description, mytable.myid, " - "mytable.name, mytable.description FROM " + "sq2.description, mytable.myid AS myid_1, " + "mytable.name AS name_1, " + "mytable.description AS description_1 FROM " "(SELECT ta.myid AS myid, ta.name AS name, " "ta.description AS description FROM " "mytable AS ta WHERE EXISTS (SELECT 1 FROM " @@ -1934,7 +1974,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): q = ( select(table1, table2.c.otherid) .where(table1.c.myid == table2.c.otherid) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) ) # make an alias of the "selectable". column names @@ -1945,7 +1985,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): # should produce two underscores. # also, reference the column "mytable_myid" off of the t2view alias. self.assert_compile( - a.select().where(a.c.mytable_myid == 9).apply_labels(), + a.select() + .where(a.c.mytable_myid == 9) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT t2view.mytable_myid AS t2view_mytable_myid, " "t2view.mytable_name " "AS t2view_mytable_name, " @@ -2099,7 +2141,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): column("spaces % more spaces"), ) self.assert_compile( - t.select().apply_labels(), + t.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), """SELECT "table%name"."percent%" AS "table%name_percent%", """ """"table%name"."%(oneofthese)s" AS """ """"table%name_%(oneofthese)s", """ @@ -2146,7 +2188,8 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): users, addresses, users.c.user_id == addresses.c.user_id ).select(), "SELECT users.user_id, users.user_name, users.password, " - "addresses.address_id, addresses.user_id, addresses.street, " + "addresses.address_id, addresses.user_id AS user_id_1, " + "addresses.street, " "addresses.city, addresses.state, addresses.zip " "FROM users JOIN addresses " "ON users.user_id = addresses.user_id", @@ -2221,12 +2264,12 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): ), ]: stmt = select(table1).select_from(spec) - self.assert_compile( - stmt, - "SELECT mytable.myid, mytable.name, mytable.description FROM " - "mytable FULL OUTER JOIN myothertable " - "ON mytable.myid = myothertable.otherid", - ) + self.assert_compile( + stmt, + "SELECT mytable.myid, mytable.name, mytable.description FROM " + "mytable FULL OUTER JOIN myothertable " + "ON mytable.myid = myothertable.otherid", + ) def test_compound_selects(self): assert_raises_message( @@ -2375,10 +2418,14 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): s2 = select(table1, table2.c.otherid).where( table1.c.myid == table2.c.otherid ) + + # note myid__1 is a dedupe of same column, same table. see + # test/sql/test_labels.py for the double underscore thing self.assert_compile( union(s1, s2).order_by(s1.selected_columns.myid), "SELECT mytable.myid, mytable.name, mytable.description, " - "mytable.myid FROM mytable WHERE mytable.myid = :myid_1 " + "mytable.myid AS myid__1 FROM mytable " + "WHERE mytable.myid = :myid_1 " "UNION SELECT mytable.myid, mytable.name, mytable.description, " "myothertable.otherid FROM mytable, myothertable " "WHERE mytable.myid = myothertable.otherid ORDER BY myid", @@ -2563,12 +2610,36 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): # is now officially completely ridiculous AND non-obviously omits # coverage on other dialects. sel = select(tbl, cast(tbl.c.v1, Numeric)).compile(dialect=dialect) + + # TODO: another unusual result from disambiguate only + if isinstance(dialect, type(mysql.dialect())): + eq_( + str(sel), + "SELECT casttest.id, casttest.v1, casttest.v2, " + "casttest.ts, " + "CAST(casttest.v1 AS DECIMAL) AS casttest_v1__1 \n" + "FROM casttest", + ) + else: + eq_( + str(sel), + "SELECT casttest.id, casttest.v1, casttest.v2, " + "casttest.ts, CAST(casttest.v1 AS NUMERIC) AS " + "casttest_v1__1 \nFROM casttest", + ) + + sel = ( + select(tbl, cast(tbl.c.v1, Numeric)) + .set_label_style(LABEL_STYLE_NONE) + .compile(dialect=dialect) + ) if isinstance(dialect, type(mysql.dialect())): eq_( str(sel), "SELECT casttest.id, casttest.v1, casttest.v2, " "casttest.ts, " - "CAST(casttest.v1 AS DECIMAL) AS v1 \nFROM casttest", + "CAST(casttest.v1 AS DECIMAL) AS v1 \n" + "FROM casttest", ) else: eq_( @@ -4771,7 +4842,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): s = ( table4.select() .where(and_(table4.c.datatype_id == 7, table4.c.value == "hi")) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) ) self.assert_compile( s, @@ -4797,7 +4868,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): # multi-part schema name labels - convert '.' to '_' self.assert_compile( - table5.select().apply_labels(), + table5.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), 'SELECT "dbo.remote_owner".remotetable.rem_id AS' " dbo_remote_owner_remotetable_rem_id, " '"dbo.remote_owner".remotetable.datatype_id' @@ -5012,7 +5083,9 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( - select(t1).select_from(t1).apply_labels(), + select(t1) + .select_from(t1) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT here.baz.id AS here_baz_id, here.baz.name AS " "here_baz_name, here.baz.meta AS here_baz_meta FROM here.baz", ) @@ -5091,7 +5164,7 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): def _assert_where_all_correlated(self, stmt): self.assert_compile( stmt, - "SELECT t1.a, t2.a FROM t1, t2 WHERE t2.a = " + "SELECT t1.a, t2.a AS a_1 FROM t1, t2 WHERE t2.a = " "(SELECT t1.a WHERE t1.a = t2.a)", ) @@ -5118,7 +5191,7 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): def _assert_column_all_correlated(self, stmt): self.assert_compile( stmt, - "SELECT t1.a, t2.a, " + "SELECT t1.a, t2.a AS a_1, " "(SELECT t1.a WHERE t1.a = t2.a) AS anon_1 FROM t1, t2", ) @@ -5132,14 +5205,14 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): def _assert_from_uncorrelated(self, stmt): self.assert_compile( stmt, - "SELECT t2.a, anon_1.a FROM t2, " + "SELECT t2.a, anon_1.a AS a_1 FROM t2, " "(SELECT t1.a AS a FROM t1, t2 WHERE t1.a = t2.a) AS anon_1", ) def _assert_from_all_uncorrelated(self, stmt): self.assert_compile( stmt, - "SELECT t1.a, t2.a, anon_1.a FROM t1, t2, " + "SELECT t1.a, t2.a AS a_1, anon_1.a AS a_2 FROM t1, t2, " "(SELECT t1.a AS a FROM t1, t2 WHERE t1.a = t2.a) AS anon_1", ) @@ -5335,7 +5408,7 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): t1, t2, s1 = self._fixture() self.assert_compile( select(t1, t2, s1.subquery()), - "SELECT t1.a, t2.a, anon_1.a FROM t1, t2, " + "SELECT t1.a, t2.a AS a_1, anon_1.a AS a_2 FROM t1, t2, " "(SELECT t1.a AS a FROM t1, t2 WHERE t1.a = t2.a) AS anon_1", ) @@ -5463,7 +5536,7 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( s3, - "SELECT t1.x, anon_1.y, anon_1.x FROM t1, " + "SELECT t1.x, anon_1.y, anon_1.x AS x_1 FROM t1, " "(SELECT t2.y AS y, anon_2.x AS x FROM t2, " "(SELECT t1.x AS x FROM t1, t2 WHERE t1.x = t2.y) " "AS anon_2) AS anon_1", @@ -5601,7 +5674,7 @@ class ResultMapTest(fixtures.TestBase): stmt = ( select(t1, t1_alias) .select_from(t1.join(union, t1.c.a == union.c.t1_a)) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) ) comp = stmt.compile() eq_( @@ -5770,7 +5843,9 @@ class ResultMapTest(fixtures.TestBase): # create the statement with some duplicate columns. right now # the behavior is that these redundant columns are deduped. - stmt = select(t.c.x, t.c.y, l1, t.c.y, l2, t.c.x, l3) + stmt = select(t.c.x, t.c.y, l1, t.c.y, l2, t.c.x, l3).set_label_style( + LABEL_STYLE_NONE + ) # so the statement has 7 inner columns... eq_(len(list(stmt.selected_columns)), 7) @@ -5794,7 +5869,9 @@ class ResultMapTest(fixtures.TestBase): ).alias() # so when we wrap here we're going to have only 5 columns - wrapped_again = select(*[c for c in wrapped.c]) + wrapped_again = select(*[c for c in wrapped.c]).set_label_style( + LABEL_STYLE_NONE + ) # so the compiler logic that matches up the "wrapper" to the # "select_wraps_for" can't use inner_columns to match because diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py index 4ebfdc7acf..92f3b215ff 100644 --- a/test/sql/test_cte.py +++ b/test/sql/test_cte.py @@ -296,10 +296,10 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): s2, "WITH RECURSIVE cte(x) AS " "(SELECT :param_1 AS x UNION ALL " - "SELECT cte.x + :x_1 AS anon_1 " - "FROM cte WHERE cte.x < :x_2), " + "SELECT cte.x + :x_2 AS anon_1 " + "FROM cte WHERE cte.x < :x_3), " "bar AS (SELECT cte.x AS x FROM cte) " - "SELECT cte.x, bar.x FROM cte, bar", + "SELECT cte.x, bar.x AS x_1 FROM cte, bar", ) def test_recursive_union_alias_three(self): @@ -321,10 +321,10 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): s2, "WITH RECURSIVE cte(x) AS " "(SELECT :param_1 AS x UNION ALL " - "SELECT cte.x + :x_1 AS anon_1 " - "FROM cte WHERE cte.x < :x_2), " + "SELECT cte.x + :x_2 AS anon_1 " + "FROM cte WHERE cte.x < :x_3), " "bar AS (SELECT cs1.x AS x FROM cte AS cs1) " - "SELECT cs1.x, cs2.x FROM cte AS cs1, bar AS cs2", + "SELECT cs1.x, cs2.x AS x_1 FROM cte AS cs1, bar AS cs2", ) def test_recursive_union_no_alias_four(self): @@ -346,10 +346,10 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): s2, "WITH RECURSIVE cte(x) AS " "(SELECT :param_1 AS x UNION ALL " - "SELECT cte.x + :x_1 AS anon_1 " - "FROM cte WHERE cte.x < :x_2), " + "SELECT cte.x + :x_2 AS anon_1 " + "FROM cte WHERE cte.x < :x_3), " "bar AS (SELECT cte.x AS x FROM cte) " - "SELECT cte.x, bar.x FROM cte, bar", + "SELECT cte.x, bar.x AS x_1 FROM cte, bar", ) # bar rendered, only includes "inner" cte, @@ -371,9 +371,9 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): "WITH RECURSIVE bar AS (SELECT cte.x AS x FROM cte), " "cte(x) AS " "(SELECT :param_1 AS x UNION ALL " - "SELECT cte.x + :x_1 AS anon_1 " - "FROM cte WHERE cte.x < :x_2) " - "SELECT bar.x, cte.x FROM bar, cte", + "SELECT cte.x + :x_2 AS anon_1 " + "FROM cte WHERE cte.x < :x_3) " + "SELECT bar.x, cte.x AS x_1 FROM bar, cte", ) def test_recursive_union_alias_four(self): @@ -397,10 +397,10 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): s2, "WITH RECURSIVE cte(x) AS " "(SELECT :param_1 AS x UNION ALL " - "SELECT cte.x + :x_1 AS anon_1 " - "FROM cte WHERE cte.x < :x_2), " + "SELECT cte.x + :x_2 AS anon_1 " + "FROM cte WHERE cte.x < :x_3), " "bar AS (SELECT cte.x AS x FROM cte) " - "SELECT cs2.x, cs1.x FROM cte AS cs2, bar AS cs1", + "SELECT cs2.x, cs1.x AS x_1 FROM cte AS cs2, bar AS cs1", ) # bar rendered, only includes "inner" cte, @@ -422,9 +422,9 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): "WITH RECURSIVE bar AS (SELECT cte.x AS x FROM cte), " "cte(x) AS " "(SELECT :param_1 AS x UNION ALL " - "SELECT cte.x + :x_1 AS anon_1 " - "FROM cte WHERE cte.x < :x_2) " - "SELECT cs1.x, cs2.x FROM bar AS cs1, cte AS cs2", + "SELECT cte.x + :x_2 AS anon_1 " + "FROM cte WHERE cte.x < :x_3) " + "SELECT cs1.x, cs2.x AS x_1 FROM bar AS cs1, cte AS cs2", ) def test_conflicting_names(self): @@ -615,7 +615,7 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( s, 'WITH "CTE" AS (SELECT :param_1 AS id) ' - "SELECT anon_1.id, anon_2.id FROM " + "SELECT anon_1.id, anon_2.id AS id_1 FROM " '(SELECT "CTE".id AS id FROM "CTE") AS anon_1, ' '(SELECT "CTE".id AS id FROM "CTE") AS anon_2', ) @@ -630,7 +630,7 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( s, "WITH cte1 AS (SELECT :param_1 AS id) " - "SELECT anon_1.id, anon_2.id FROM " + "SELECT anon_1.id, anon_2.id AS id_1 FROM " "(SELECT aa.id AS id FROM cte1 AS aa) AS anon_1, " "(SELECT aa.id AS id FROM cte1 AS aa) AS anon_2", ) @@ -667,7 +667,8 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): "JOIN cte1 AS aa ON b.fid = aa.id), " "cte3 AS (SELECT c.id AS id FROM c " "JOIN cte1 AS aa ON c.fid = aa.id) " - "SELECT cte3.id, cte2.id FROM cte2 JOIN cte3 ON cte2.id = cte3.id", + "SELECT cte3.id, cte2.id AS id_1 " + "FROM cte2 JOIN cte3 ON cte2.id = cte3.id", ) def test_named_alias_no_quote(self): @@ -788,13 +789,13 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): 'FROM orders WHERE orders."order" = :1), regional_sales_2 AS ' '(SELECT orders."order" = :2 AS anon_1, ' 'anon_2."order" AS "order", ' - 'orders."order" AS "order", ' - 'regional_sales_1."order" AS "order" FROM orders, ' + 'orders."order" AS order_1, ' + 'regional_sales_1."order" AS order_2 FROM orders, ' "regional_sales_1 " "AS anon_2, regional_sales_1 " 'WHERE orders."order" = :3) SELECT regional_sales_2.anon_1, ' - 'regional_sales_2."order", regional_sales_2."order", ' - 'regional_sales_2."order" FROM regional_sales_2', + 'regional_sales_2."order", regional_sales_2.order_1, ' + "regional_sales_2.order_2 FROM regional_sales_2", checkpositional=("x", "y", "z"), dialect=dialect, ) @@ -835,13 +836,13 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): "WHERE orders.\"order\" = 'x'), " "regional_sales_2 AS " "(SELECT orders.\"order\" = 'y' AS anon_1, " - 'anon_2."order" AS "order", orders."order" AS "order", ' - 'regional_sales_1."order" AS "order" ' + 'anon_2."order" AS "order", orders."order" AS order_1, ' + 'regional_sales_1."order" AS order_2 ' "FROM orders, regional_sales_1 AS anon_2, regional_sales_1 " "WHERE orders.\"order\" = 'z') " "SELECT regional_sales_2.anon_1, " - 'regional_sales_2."order", regional_sales_2."order", ' - 'regional_sales_2."order" FROM regional_sales_2', + 'regional_sales_2."order", regional_sales_2.order_1, ' + "regional_sales_2.order_2 FROM regional_sales_2", checkpositional=(), dialect=dialect, literal_binds=True, @@ -860,7 +861,7 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): s2, 'WITH regional_sales AS (SELECT "order"."order" ' 'AS "order" FROM "order") ' - 'SELECT anon_1."order", anon_2."order" ' + 'SELECT anon_1."order", anon_2."order" AS order_1 ' "FROM regional_sales AS anon_1, " 'regional_sales AS anon_2 WHERE anon_1."order" > anon_2."order"', ) diff --git a/test/sql/test_deprecations.py b/test/sql/test_deprecations.py index 903da2f344..35633a1a36 100644 --- a/test/sql/test_deprecations.py +++ b/test/sql/test_deprecations.py @@ -34,12 +34,14 @@ from sqlalchemy import util from sqlalchemy import VARCHAR from sqlalchemy.engine import default from sqlalchemy.sql import coercions +from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.sql import literal from sqlalchemy.sql import operators from sqlalchemy.sql import quoted_name from sqlalchemy.sql import roles from sqlalchemy.sql import update from sqlalchemy.sql import visitors +from sqlalchemy.sql.selectable import LABEL_STYLE_NONE from sqlalchemy.sql.selectable import SelectStatementGrouping from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message @@ -492,9 +494,9 @@ class SelectableTest(fixtures.TestBase, AssertsCompiledSQL): "ORDER BY table1.col1", ), ( - lambda table1, table2: table1.join(table2).select( - table1.c.col1 == 5 - ), + lambda table1, table2: table1.join(table2) + .select(table1.c.col1 == 5) + .set_label_style(LABEL_STYLE_NONE), "Join", "whereclause", "SELECT table1.col1, table1.col2, table1.col3, table1.colx, " @@ -503,9 +505,9 @@ class SelectableTest(fixtures.TestBase, AssertsCompiledSQL): "WHERE table1.col1 = :col1_1", ), ( - lambda table1, table2: table1.join(table2).select( - whereclause=table1.c.col1 == 5 - ), + lambda table1, table2: table1.join(table2) + .select(whereclause=table1.c.col1 == 5) + .set_label_style(LABEL_STYLE_NONE), "Join", "whereclause", "SELECT table1.col1, table1.col2, table1.col3, table1.colx, " @@ -514,9 +516,9 @@ class SelectableTest(fixtures.TestBase, AssertsCompiledSQL): "WHERE table1.col1 = :col1_1", ), ( - lambda table1, table2: table1.join(table2).select( - order_by=table1.c.col1 - ), + lambda table1, table2: table1.join(table2) + .select(order_by=table1.c.col1) + .set_label_style(LABEL_STYLE_NONE), "Join", "kwargs", "SELECT table1.col1, table1.col2, table1.col3, table1.colx, " @@ -758,8 +760,12 @@ class SelectableTest(fixtures.TestBase, AssertsCompiledSQL): ) .alias("analias") ) - s1 = self.table1.select().apply_labels() - s2 = self.table2.select().apply_labels() + s1 = self.table1.select().set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) + s2 = self.table2.select().set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) with self._c_deprecated(): assert u.corresponding_column(s1.c.table1_col2) is u.c.col2 assert u.corresponding_column(s2.c.table2_col2) is u.c.col2 @@ -823,7 +829,9 @@ class SelectableTest(fixtures.TestBase, AssertsCompiledSQL): ) def test_select_labels(self): - a = self.table1.select().apply_labels() + a = self.table1.select().set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) j = join(a._implicit_subquery, self.table2) criterion = a._implicit_subquery.c.table1_col1 == self.table2.c.col2 @@ -1557,7 +1565,9 @@ class CursorResultTest(fixtures.TablesTest): self.metadata.create_all(testing.db) connection.execute(content.insert().values(type="t1")) - row = connection.execute(content.select().apply_labels()).first() + row = connection.execute( + content.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ).first() in_(content.c.type, row._mapping) not_in(bar.c.content_type, row) with testing.expect_deprecated( @@ -1615,7 +1625,9 @@ class CursorResultTest(fixtures.TablesTest): for use_labels in False, True: stmt = users.select() if use_labels: - stmt = stmt.apply_labels() + stmt = stmt.set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) result = conn.execute( stmt.order_by(users.c.user_id) diff --git a/test/sql/test_external_traversal.py b/test/sql/test_external_traversal.py index a6001ba9da..bc60137913 100644 --- a/test/sql/test_external_traversal.py +++ b/test/sql/test_external_traversal.py @@ -19,6 +19,7 @@ from sqlalchemy import tuple_ from sqlalchemy import union from sqlalchemy.sql import ClauseElement from sqlalchemy.sql import column +from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.sql import operators from sqlalchemy.sql import table from sqlalchemy.sql import util as sql_util @@ -807,7 +808,8 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( s2, "SELECT table1.col1, table1.col2, table1.col3, " - "table2.col1, table2.col2, table2.col3 FROM table1 " + "table2.col1 AS col1_1, table2.col2 AS col2_1, " + "table2.col3 AS col3_1 FROM table1 " "JOIN table2 ON table1.col1 = table2.col2 JOIN table3 " "ON table3.col1 = table1.col1", ) @@ -821,7 +823,8 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( s4, "SELECT table1.col1, table1.col2, table1.col3, " - "table2.col1, table2.col2, table2.col3 FROM table1 " + "table2.col1 AS col1_1, table2.col2 AS col2_1, " + "table2.col3 AS col3_1 FROM table1 " "JOIN table2 ON table1.col1 = table2.col2 JOIN table3 " "ON table3.col1 = table1.col1", ) @@ -833,7 +836,8 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( s6, "SELECT table1.col1, table1.col2, table1.col3, " - "table2.col1, table2.col2, table2.col3 FROM table1 " + "table2.col1 AS col1_1, table2.col2 AS col2_1, " + "table2.col3 AS col3_1 FROM table1 " "JOIN table2 ON table1.col1 = table2.col2 JOIN table3 " "ON table3.col1 = table1.col1", ) @@ -851,7 +855,8 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( s2, "SELECT table1.col1, table1.col2, table1.col3, " - "table2.col1, table2.col2, table2.col3 FROM table1 " + "table2.col1 AS col1_1, table2.col2 AS col2_1, " + "table2.col3 AS col3_1 FROM table1 " "JOIN table2 ON table1.col1 = table2.col2 JOIN table3 " "ON table3.col1 = table1.col1", ) @@ -863,7 +868,8 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( s4, "SELECT table1.col1, table1.col2, table1.col3, " - "table2.col1, table2.col2, table2.col3 FROM table1 " + "table2.col1 AS col1_1, table2.col2 AS col2_1, " + "table2.col3 AS col3_1 FROM table1 " "JOIN table2 ON table1.col1 = table2.col2 JOIN table3 " "ON table3.col1 = table1.col1", ) @@ -875,7 +881,8 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( s6, "SELECT table1.col1, table1.col2, table1.col3, " - "table2.col1, table2.col2, table2.col3 FROM table1 " + "table2.col1 AS col1_1, table2.col2 AS col2_1, " + "table2.col3 AS col3_1 FROM table1 " "JOIN table2 ON table1.col1 = table2.col2 JOIN table3 " "ON table3.col1 = table1.col1", ) @@ -1055,7 +1062,11 @@ class ColumnAdapterTest(fixtures.TestBase, AssertsCompiledSQL): t2a = t2.alias(name="t2a") a1 = sql_util.ColumnAdapter(t1a) - s1 = select(t1a.c.col1, t2a.c.col1).apply_labels().alias() + s1 = ( + select(t1a.c.col1, t2a.c.col1) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .alias() + ) a2 = sql_util.ColumnAdapter(s1) a3 = a2.wrap(a1) a4 = a1.wrap(a2) @@ -1103,7 +1114,11 @@ class ColumnAdapterTest(fixtures.TestBase, AssertsCompiledSQL): """ - stmt = select(t1.c.col1, t2.c.col1).apply_labels().subquery() + stmt = ( + select(t1.c.col1, t2.c.col1) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) sa = stmt.alias() stmt2 = select(t2, sa).subquery() @@ -1165,7 +1180,11 @@ class ColumnAdapterTest(fixtures.TestBase, AssertsCompiledSQL): t1a, include_fn=lambda col: "a1" in col._annotations ) - s1 = select(t1a, t2a).apply_labels().alias() + s1 = ( + select(t1a, t2a) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .alias() + ) a2 = sql_util.ColumnAdapter( s1, include_fn=lambda col: "a2" in col._annotations ) @@ -1360,7 +1379,7 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): "SELECT " "(SELECT table2.col1 FROM table1 " "WHERE table2.col1 = table1.col1) AS anon_1, " - "table1.col1, table2.col1 " + "table1.col1, table2.col1 AS col1_1 " "FROM table1 " "JOIN table2 ON table1.col1 = table2.col1", ) @@ -1369,7 +1388,7 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): "SELECT " "(SELECT table2.col1 FROM table1 " "WHERE table2.col1 = table1.col1) AS anon_1, " - "table1.col1, table2.col1 " + "table1.col1, table2.col1 AS col1_1 " "FROM table1 " "JOIN table2 ON table1.col1 = table2.col1", ) @@ -1384,7 +1403,7 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): "(SELECT table2.col1 FROM " "table1 JOIN table1 AS t1alias ON table1.col1 = t1alias.col2 " "WHERE table2.col1 = table1.col1) AS anon_1, " - "table1.col1, table2.col1 " + "table1.col1, table2.col1 AS col1_1 " "FROM table1 JOIN table1 AS t1alias ON table1.col1 = t1alias.col2 " "JOIN table2 ON table1.col1 = table2.col1", ) @@ -1398,7 +1417,7 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): "(SELECT table2.col1 FROM " "table1 JOIN table1 AS t1alias ON table1.col1 = t1alias.col2 " "WHERE table2.col1 = table1.col1) AS anon_1, " - "table1.col1, table2.col1 " + "table1.col1, table2.col1 AS col1_1 " "FROM table1 JOIN table1 AS t1alias ON table1.col1 = t1alias.col2 " "JOIN table2 ON table1.col1 = table2.col1", ) @@ -1484,7 +1503,8 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): ) ), "SELECT t1alias.col1, t1alias.col2, t1alias.col3, " - "table2.col1, table2.col2, table2.col3 " + "table2.col1 AS col1_1, table2.col2 AS col2_1, " + "table2.col3 AS col3_1 " "FROM table1 AS t1alias, table2 WHERE t1alias.col1 = " "(SELECT * FROM table2 WHERE t1alias.col1 = table2.col2)", ) @@ -1504,7 +1524,8 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): ) ), "SELECT t1alias.col1, t1alias.col2, t1alias.col3, " - "table2.col1, table2.col2, table2.col3 " + "table2.col1 AS col1_1, table2.col2 AS col2_1, " + "table2.col3 AS col3_1 " "FROM table1 AS t1alias, table2 " "WHERE t1alias.col1 = " "(SELECT * FROM table1 AS t1alias " @@ -1612,7 +1633,8 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): ) ), "SELECT t1alias.col1, t1alias.col2, t1alias.col3, " - "t2alias.col1, t2alias.col2, t2alias.col3 " + "t2alias.col1 AS col1_1, t2alias.col2 AS col2_1, " + "t2alias.col3 AS col3_1 " "FROM table1 AS t1alias, table2 AS t2alias " "WHERE t1alias.col1 = " "(SELECT * FROM table2 AS t2alias " @@ -1720,7 +1742,11 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): ) j1 = a.outerjoin(b) - j2 = select(j1).apply_labels().subquery() + j2 = ( + select(j1) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) j3 = c.join(j2, j2.c.b_id == c.c.bid) @@ -1732,7 +1758,11 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): "JOIN b ON a.id = b.aid) AS anon_1 ON anon_1.b_id = c.bid " "LEFT OUTER JOIN d ON anon_1.a_id = d.aid", ) - j5 = j3.select().apply_labels().subquery("foo") + j5 = ( + j3.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery("foo") + ) j6 = sql_util.ClauseAdapter(j5).copy_and_process([j4])[0] # this statement takes c join(a join b), wraps it inside an @@ -1786,8 +1816,8 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( sql_util.ClauseAdapter(s2).traverse(j).select(), "SELECT anon_1.col1, anon_1.col2, " - "anon_1.col3, table2.col1, table2.col2, " - "table2.col3 FROM (SELECT foo.col1 AS " + "anon_1.col3, table2.col1 AS col1_1, table2.col2 AS col2_1, " + "table2.col3 AS col3_1 FROM (SELECT foo.col1 AS " "col1, foo.col2 AS col2, foo.col3 AS col3 " "FROM (SELECT table1.col1 AS col1, " "table1.col2 AS col2, table1.col3 AS col3 " @@ -1819,7 +1849,8 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( sql_util.ClauseAdapter(s2).traverse(j).select(), "SELECT anon_1.col1, anon_1.col2, " - "anon_1.col3, bar.col1, bar.col2, bar.col3 " + "anon_1.col3, bar.col1 AS col1_1, bar.col2 AS col2_1, " + "bar.col3 AS col3_1 " "FROM (SELECT foo.col1 AS col1, foo.col2 " "AS col2, foo.col3 AS col3 FROM (SELECT " "table1.col1 AS col1, table1.col2 AS col2, " @@ -1866,8 +1897,8 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): ) u = union( - a.join(b).select().apply_labels(), - a.join(d).select().apply_labels(), + a.join(b).select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), + a.join(d).select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), ).alias() self.assert_compile( diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index 50f50f0f0f..19562dade0 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -28,6 +28,7 @@ from sqlalchemy.dialects import postgresql from sqlalchemy.dialects import sqlite from sqlalchemy.sql import column from sqlalchemy.sql import functions +from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.sql import quoted_name from sqlalchemy.sql import table from sqlalchemy.sql.compiler import BIND_TEMPLATES @@ -95,7 +96,8 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): def test_use_labels(self): self.assert_compile( - select(func.foo()).apply_labels(), "SELECT foo() AS foo_1" + select(func.foo()).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), + "SELECT foo() AS foo_1", ) def test_use_labels_function_element(self): @@ -109,7 +111,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): return "max(%s)" % compiler.process(element.clauses, **kw) self.assert_compile( - select(max_(5, 6)).apply_labels(), + select(max_(5, 6)).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT max(:max_2, :max_3) AS max_1", ) diff --git a/test/sql/test_labels.py b/test/sql/test_labels.py index e3fdff8069..3aa0a85239 100644 --- a/test/sql/test_labels.py +++ b/test/sql/test_labels.py @@ -13,11 +13,13 @@ from sqlalchemy.engine import default from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql import coercions from sqlalchemy.sql import column +from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.sql import roles from sqlalchemy.sql import table from sqlalchemy.sql.elements import _truncated_label from sqlalchemy.sql.elements import ColumnElement from sqlalchemy.sql.elements import WrapsColumnExpression +from sqlalchemy.sql.selectable import LABEL_STYLE_NONE from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL @@ -200,7 +202,8 @@ class MaxIdentTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( select(table1, ta) .select_from(table1.join(ta, on)) - .where(ta.c.this_is_the_data_column == "data3"), + .where(ta.c.this_is_the_data_column == "data3") + .set_label_style(LABEL_STYLE_NONE), "SELECT " "some_large_named_table.this_is_the_primarykey_column, " "some_large_named_table.this_is_the_data_column, " @@ -261,7 +264,7 @@ class MaxIdentTest(fixtures.TestBase, AssertsCompiledSQL): table1 = self.table1 s = ( table1.select() - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .order_by(table1.c.this_is_the_primarykey_column) ) @@ -275,7 +278,7 @@ class MaxIdentTest(fixtures.TestBase, AssertsCompiledSQL): # select query s = ( table1.select() - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .order_by(table1.c.this_is_the_primarykey_column) .limit(2) ) @@ -312,7 +315,7 @@ class MaxIdentTest(fixtures.TestBase, AssertsCompiledSQL): .where(table1.c.this_is_the_primarykey_column == 4) .alias() ) - s = select(q).apply_labels() + s = select(q).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( s, @@ -579,7 +582,7 @@ class LabelLengthTest(fixtures.TestBase, AssertsCompiledSQL): .where(table1.c.this_is_the_primarykey_column == 4) .alias() ) - x = select(q).apply_labels() + x = select(q).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) compile_dialect = default.DefaultDialect(label_length=10) self.assert_compile( @@ -610,7 +613,7 @@ class LabelLengthTest(fixtures.TestBase, AssertsCompiledSQL): .where(table1.c.this_is_the_primarykey_column == 4) .alias() ) - x = select(q).apply_labels() + x = select(q).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) compile_dialect = default.DefaultDialect(label_length=4) self.assert_compile( @@ -640,7 +643,7 @@ class LabelLengthTest(fixtures.TestBase, AssertsCompiledSQL): q = ( table1.select() .where(table1.c.this_is_the_primarykey_column == 4) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .alias("foo") ) @@ -714,7 +717,9 @@ class LabelLengthTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( - select(other_table, anon).select_from(j1).apply_labels(), + select(other_table, anon) + .select_from(j1) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT " "other_thirty_characters_table_.id " "AS other_thirty_characters__1, " @@ -756,7 +761,7 @@ class LabelLengthTest(fixtures.TestBase, AssertsCompiledSQL): ) # column still there, but short label - s = select(a1).apply_labels() + s = select(a1).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) self.assert_compile( s, "SELECT asdf.abcde AS _1 FROM a AS asdf", dialect=dialect ) @@ -772,7 +777,7 @@ class LabelLengthTest(fixtures.TestBase, AssertsCompiledSQL): "tablename", column("columnname_one"), column("columnn_1") ) - stmt = select(table1).apply_labels() + stmt = select(table1).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) dialect = default.DefaultDialect(label_length=23) self.assert_compile( @@ -817,7 +822,7 @@ class ColExprLabelTest(fixtures.TestBase, AssertsCompiledSQL): return SomeColThing - def test_column_auto_label_dupes(self): + def test_column_auto_label_dupes_label_style_none(self): expr = self._fixture() table1 = self.table1 @@ -827,12 +832,29 @@ class ColExprLabelTest(fixtures.TestBase, AssertsCompiledSQL): table1.c.name, expr(table1.c.name), expr(table1.c.name), - ), + ).set_label_style(LABEL_STYLE_NONE), "SELECT some_table.name, some_table.name, " "SOME_COL_THING(some_table.name) AS name, " "SOME_COL_THING(some_table.name) AS name FROM some_table", ) + def test_column_auto_label_dupes_label_style_disambiguate(self): + expr = self._fixture() + table1 = self.table1 + + self.assert_compile( + select( + table1.c.name, + table1.c.name, + expr(table1.c.name), + expr(table1.c.name), + ), + "SELECT some_table.name, some_table.name AS name__1, " + "SOME_COL_THING(some_table.name) AS some_table_name__1, " + "SOME_COL_THING(some_table.name) AS some_table_name__2 " + "FROM some_table", + ) + def test_anon_expression_fallback(self): expr = self._fixture() table1 = self.table1 @@ -851,7 +873,7 @@ class ColExprLabelTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( select( table1.c.name + "foo", expr(table1.c.name + "foo") - ).apply_labels(), + ).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT some_table.name || :name_1 AS anon_1, " "SOME_COL_THING(some_table.name || :name_2) AS anon_2 " "FROM some_table", @@ -871,7 +893,7 @@ class ColExprLabelTest(fixtures.TestBase, AssertsCompiledSQL): "some_table.name AS bar, some_table.value FROM some_table", ) - def test_cast_auto_label(self): + def test_cast_auto_label_label_style_none(self): table1 = self.table1 self.assert_compile( @@ -879,13 +901,27 @@ class ColExprLabelTest(fixtures.TestBase, AssertsCompiledSQL): cast(table1.c.name, Integer), cast(table1.c.name, String), table1.c.name, - ), + ).set_label_style(LABEL_STYLE_NONE), "SELECT CAST(some_table.name AS INTEGER) AS name, " "CAST(some_table.name AS VARCHAR) AS name, " "some_table.name FROM some_table", ) - def test_type_coerce_auto_label(self): + def test_cast_auto_label_label_style_disabmiguate(self): + table1 = self.table1 + + self.assert_compile( + select( + cast(table1.c.name, Integer), + cast(table1.c.name, String), + table1.c.name, + ), + "SELECT CAST(some_table.name AS INTEGER) AS name, " + "CAST(some_table.name AS VARCHAR) AS some_table_name__1, " + "some_table.name AS name_1 FROM some_table", + ) + + def test_type_coerce_auto_label_label_style_none(self): table1 = self.table1 self.assert_compile( @@ -893,13 +929,28 @@ class ColExprLabelTest(fixtures.TestBase, AssertsCompiledSQL): type_coerce(table1.c.name, Integer), type_coerce(table1.c.name, String), table1.c.name, - ), + ).set_label_style(LABEL_STYLE_NONE), # ideally type_coerce wouldn't label at all... "SELECT some_table.name AS name, " "some_table.name AS name, " "some_table.name FROM some_table", ) + def test_type_coerce_auto_label_label_style_disambiguate(self): + table1 = self.table1 + + self.assert_compile( + select( + type_coerce(table1.c.name, Integer), + type_coerce(table1.c.name, String), + table1.c.name, + ), + # ideally type_coerce wouldn't label at all... + "SELECT some_table.name AS name, " + "some_table.name AS some_table_name__1, " + "some_table.name AS name_1 FROM some_table", + ) + def test_boolean_auto_label(self): col = column("value", Boolean) @@ -919,7 +970,7 @@ class ColExprLabelTest(fixtures.TestBase, AssertsCompiledSQL): expr(table1.c.name.label("foo")), table1.c.name.label("bar"), table1.c.value, - ).apply_labels(), + ).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), # the expr around label is treated the same way as plain column # with label "SELECT SOME_COL_THING(some_table.name) AS foo, " @@ -937,7 +988,7 @@ class ColExprLabelTest(fixtures.TestBase, AssertsCompiledSQL): table1.c.name, expr(table1.c.name), expr(table1.c.name), - ).apply_labels(), + ).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT some_table.name AS some_table_name, " "some_table.name AS some_table_name__1, " "SOME_COL_THING(some_table.name) AS some_table_name_1, " @@ -950,7 +1001,9 @@ class ColExprLabelTest(fixtures.TestBase, AssertsCompiledSQL): table1 = self.table1 self.assert_compile( - select(table1.c.name, expr(table1.c.value)).apply_labels(), + select(table1.c.name, expr(table1.c.value)).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT some_table.name AS some_table_name, " "SOME_COL_THING(some_table.value) " "AS some_table_value FROM some_table", diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index 270e79ba16..a19eb20bc0 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -34,6 +34,7 @@ from sqlalchemy.sql import column from sqlalchemy.sql import compiler from sqlalchemy.sql import desc from sqlalchemy.sql import false +from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.sql import literal from sqlalchemy.sql import null from sqlalchemy.sql import operators @@ -1234,15 +1235,21 @@ class ConjunctionTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_constant_render_distinct_use_labels(self): self.assert_compile( - select(null(), null()).apply_labels(), + select(null(), null()).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT NULL AS anon_1, NULL AS anon__1", ) self.assert_compile( - select(true(), true()).apply_labels(), + select(true(), true()).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT true AS anon_1, true AS anon__1", ) self.assert_compile( - select(false(), false()).apply_labels(), + select(false(), false()).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ), "SELECT false AS anon_1, false AS anon__1", ) diff --git a/test/sql/test_query.py b/test/sql/test_query.py index 903a6f23e4..913b7f4d1f 100644 --- a/test/sql/test_query.py +++ b/test/sql/test_query.py @@ -26,6 +26,8 @@ from sqlalchemy import union from sqlalchemy import union_all from sqlalchemy import VARCHAR from sqlalchemy.engine import default +from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL +from sqlalchemy.sql.selectable import LABEL_STYLE_NONE from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures @@ -383,22 +385,28 @@ class QueryTest(fixtures.TablesTest): eq_(got, wanted) for labels in False, True: + label_style = ( + LABEL_STYLE_NONE + if labels is False + else LABEL_STYLE_TABLENAME_PLUS_COL + ) def go(stmt): if labels: - stmt = stmt.apply_labels() + stmt = stmt.set_label_style(label_style) return stmt a_eq( - users.select(order_by=[users.c.user_id], use_labels=labels), + users.select(order_by=[users.c.user_id]).set_label_style( + label_style + ), [(1, "c"), (2, "b"), (3, "a")], ) a_eq( users.select( order_by=[users.c.user_name, users.c.user_id], - use_labels=labels, - ), + ).set_label_style(label_style), [(3, "a"), (2, "b"), (1, "c")], ) @@ -423,9 +431,8 @@ class QueryTest(fixtures.TablesTest): a_eq( users.select( distinct=True, - use_labels=labels, order_by=[users.c.user_id], - ), + ).set_label_style(label_style), [(1, "c"), (2, "b"), (3, "a")], ) @@ -452,9 +459,8 @@ class QueryTest(fixtures.TablesTest): a_eq( users.select( distinct=True, - use_labels=labels, order_by=[desc(users.c.user_id)], - ), + ).set_label_style(label_style), [(3, "a"), (2, "b"), (1, "c")], ) @@ -485,67 +491,64 @@ class QueryTest(fixtures.TablesTest): eq_(got, wanted) for labels in False, True: + label_style = ( + LABEL_STYLE_NONE + if labels is False + else LABEL_STYLE_TABLENAME_PLUS_COL + ) a_eq( users.select( order_by=[users.c.user_name.nulls_first()], - use_labels=labels, - ), + ).set_label_style(label_style), [(1, None), (3, "a"), (2, "b")], ) a_eq( users.select( order_by=[users.c.user_name.nulls_last()], - use_labels=labels, - ), + ).set_label_style(label_style), [(3, "a"), (2, "b"), (1, None)], ) a_eq( users.select( order_by=[asc(users.c.user_name).nulls_first()], - use_labels=labels, - ), + ).set_label_style(label_style), [(1, None), (3, "a"), (2, "b")], ) a_eq( users.select( order_by=[asc(users.c.user_name).nulls_last()], - use_labels=labels, - ), + ).set_label_style(label_style), [(3, "a"), (2, "b"), (1, None)], ) a_eq( users.select( order_by=[users.c.user_name.desc().nulls_first()], - use_labels=labels, - ), + ).set_label_style(label_style), [(1, None), (2, "b"), (3, "a")], ) a_eq( users.select( order_by=[users.c.user_name.desc().nulls_last()], - use_labels=labels, - ), + ).set_label_style(label_style), [(2, "b"), (3, "a"), (1, None)], ) a_eq( users.select( order_by=[desc(users.c.user_name).nulls_first()], - use_labels=labels, - ), + ).set_label_style(label_style), [(1, None), (2, "b"), (3, "a")], ) a_eq( users.select( order_by=[desc(users.c.user_name).nulls_last()], - use_labels=labels, - ), + ).set_label_style(label_style), [(2, "b"), (3, "a"), (1, None)], ) @@ -555,16 +558,14 @@ class QueryTest(fixtures.TablesTest): users.c.user_name.nulls_first(), users.c.user_id, ], - use_labels=labels, - ), + ).set_label_style(label_style), [(1, None), (3, "a"), (2, "b")], ) a_eq( users.select( order_by=[users.c.user_name.nulls_last(), users.c.user_id], - use_labels=labels, - ), + ).set_label_style(label_style), [(3, "a"), (2, "b"), (1, None)], ) diff --git a/test/sql/test_quote.py b/test/sql/test_quote.py index c743918c8f..bf885a7f55 100644 --- a/test/sql/test_quote.py +++ b/test/sql/test_quote.py @@ -16,6 +16,7 @@ from sqlalchemy import testing from sqlalchemy import util from sqlalchemy.engine import default from sqlalchemy.sql import compiler +from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.sql.elements import _anonymous_label from sqlalchemy.sql.elements import quoted_name from sqlalchemy.testing import AssertsCompiledSQL @@ -176,11 +177,15 @@ class QuoteExecTest(fixtures.TablesTest): table1.c.MixedCase, table1.c.a123, ] - result = connection.execute(select(columns).apply_labels()).fetchall() + result = connection.execute( + select(columns).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ).fetchall() assert result == [(1, 2, 3, 4), (2, 2, 3, 4), (4, 3, 2, 1)] columns = [table2.c.d123, table2.c.u123, table2.c.MixedCase] - result = connection.execute(select(columns).apply_labels()).all() + result = connection.execute( + select(columns).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ).all() assert result == [(1, 2, 3), (2, 2, 3), (4, 3, 2)] @@ -556,7 +561,7 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( t2.join(t1).select(), "SELECT " - "t2.col1, t2.t1col1, t1.col1 " + "t2.col1, t2.t1col1, t1.col1 AS col1_1 " "FROM " "t2 " "JOIN " @@ -578,7 +583,7 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( t2.join(t1).select(), "SELECT " - '"t2"."col1", "t2"."t1col1", "t1"."col1" ' + '"t2"."col1", "t2"."t1col1", "t1"."col1" AS col1_1 ' "FROM " '"t2" ' "JOIN " @@ -597,7 +602,7 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( t2.join(t1).select(), "SELECT " - '"T2"."Col1", "T2"."T1Col1", "T1"."Col1" ' + '"T2"."Col1", "T2"."T1Col1", "T1"."Col1" AS "Col1_1" ' "FROM " '"T2" ' "JOIN " @@ -619,7 +624,7 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( t2.join(t1).select(), "SELECT " - "T2.Col1, T2.T1Col1, T1.Col1 " + 'T2.Col1, T2.T1Col1, T1.Col1 AS "Col1_1" ' "FROM " "T2 " "JOIN " @@ -756,7 +761,7 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL): t1 = Table("T1", metadata, Column("Col1", Integer), schema="Foo") self.assert_compile( - t1.select().apply_labels(), + t1.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT " '"Foo"."T1"."Col1" AS "Foo_T1_Col1" ' "FROM " @@ -778,7 +783,7 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL): # TODO: is this what we really want here ? # what if table/schema *are* quoted? self.assert_compile( - t1.select().apply_labels(), + t1.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT " "Foo.T1.Col1 AS Foo_T1_Col1 " "FROM " "Foo.T1", ) @@ -804,13 +809,13 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL): t = Table("t", m, Column("x", Integer, quote=True)) self.assert_compile( - select(t.alias()).apply_labels(), + select(t.alias()).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), 'SELECT t_1."x" AS "t_1_x" FROM t AS t_1', ) t2 = Table("t2", m, Column("x", Integer), quote=True) self.assert_compile( - select(t2.c.x).apply_labels(), + select(t2.c.x).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), 'SELECT "t2".x AS "t2_x" FROM "t2"', ) diff --git a/test/sql/test_resultset.py b/test/sql/test_resultset.py index 23aa703d36..e99ce881ca 100644 --- a/test/sql/test_resultset.py +++ b/test/sql/test_resultset.py @@ -31,6 +31,8 @@ from sqlalchemy.engine import Row from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql import ColumnElement from sqlalchemy.sql import expression +from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL +from sqlalchemy.sql.selectable import LABEL_STYLE_NONE from sqlalchemy.sql.selectable import TextualSelect from sqlalchemy.sql.sqltypes import NULLTYPE from sqlalchemy.sql.util import ClauseAdapter @@ -209,7 +211,9 @@ class CursorResultTest(fixtures.TablesTest): self.metadata.create_all(connection) connection.execute(content.insert().values(type="t1")) - row = connection.execute(content.select(use_labels=True)).first() + row = connection.execute( + content.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ).first() in_(content.c.type, row._mapping) not_in(bar.c.content_type, row._mapping) @@ -238,8 +242,12 @@ class CursorResultTest(fixtures.TablesTest): for pickle in False, True: for use_labels in False, True: result = connection.execute( - users.select(use_labels=use_labels).order_by( - users.c.user_id + users.select() + .order_by(users.c.user_id) + .set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + if use_labels + else LABEL_STYLE_NONE ) ).fetchall() @@ -755,7 +763,11 @@ class CursorResultTest(fixtures.TablesTest): addresses = self.tables.addresses connection.execute(users.insert(), dict(user_id=1, user_name="john")) - result = connection.execute(users.outerjoin(addresses).select()) + result = connection.execute( + users.outerjoin(addresses) + .select() + .set_label_style(LABEL_STYLE_NONE) + ) r = result.first() assert_raises_message( @@ -801,9 +813,9 @@ class CursorResultTest(fixtures.TablesTest): ua = users.alias() u2 = users.alias() result = connection.execute( - select(users.c.user_id, ua.c.user_id).select_from( - users.join(ua, true()) - ) + select(users.c.user_id, ua.c.user_id) + .select_from(users.join(ua, true())) + .set_label_style(LABEL_STYLE_NONE) ) row = result.first() @@ -976,7 +988,9 @@ class CursorResultTest(fixtures.TablesTest): users.select().alias("foo"), users.select().alias(users.name), ): - row = connection.execute(s.select(use_labels=True)).first() + row = connection.execute( + s.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ).first() eq_(row._mapping[s.c.user_id], 7) eq_(row._mapping[s.c.user_name], "ed") @@ -1499,7 +1513,9 @@ class KeyTargetingTest(fixtures.TablesTest): def test_keyed_accessor_single_labeled(self, connection): keyed1 = self.tables.keyed1 - row = connection.execute(keyed1.select().apply_labels()).first() + row = connection.execute( + keyed1.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + ).first() eq_(row.keyed1_b, "a1") eq_(row.keyed1_q, "c1") @@ -1567,7 +1583,9 @@ class KeyTargetingTest(fixtures.TablesTest): keyed2 = self.tables.keyed2 row = connection.execute( - select(keyed1, keyed2).select_from(keyed1.join(keyed2, true())) + select(keyed1, keyed2) + .select_from(keyed1.join(keyed2, true())) + .set_label_style(LABEL_STYLE_NONE) ).first() # column access is unambiguous @@ -1604,7 +1622,7 @@ class KeyTargetingTest(fixtures.TablesTest): row = connection.execute( select(keyed1, keyed2) .select_from(keyed1.join(keyed2, true())) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) ).first() # column access is unambiguous @@ -1631,7 +1649,9 @@ class KeyTargetingTest(fixtures.TablesTest): keyed3 = self.tables.keyed3 row = connection.execute( - select(keyed1, keyed3).select_from(keyed1.join(keyed3, true())) + select(keyed1, keyed3) + .select_from(keyed1.join(keyed3, true())) + .set_label_style(LABEL_STYLE_NONE) ).first() eq_(row.q, "c1") @@ -1657,7 +1677,7 @@ class KeyTargetingTest(fixtures.TablesTest): row = connection.execute( select(keyed1, keyed2) .select_from(keyed1.join(keyed2, true())) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) ).first() eq_(row.keyed1_b, "a1") eq_(row.keyed1_a, "a1") @@ -1696,7 +1716,7 @@ class KeyTargetingTest(fixtures.TablesTest): keyed3.c.d, ) .select_from(keyed2.join(keyed3, true())) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) ) result = connection.execute(stmt) @@ -1793,7 +1813,7 @@ class KeyTargetingTest(fixtures.TablesTest): keyed1 = self.tables.keyed1 stmt = ( select(keyed1.c.b, keyed1.c.q.label("foo")) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .subquery() ) @@ -1813,7 +1833,7 @@ class KeyTargetingTest(fixtures.TablesTest): def _adapt_result_columns_fixture_four(self): keyed1 = self.tables.keyed1 - stmt1 = select(keyed1).apply_labels() + stmt1 = select(keyed1).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) a1 = keyed1.alias() stmt2 = ClauseAdapter(a1).traverse(stmt1) diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py index c75e8886de..ce33ed10e5 100644 --- a/test/sql/test_selectable.py +++ b/test/sql/test_selectable.py @@ -32,10 +32,12 @@ from sqlalchemy.sql import annotation from sqlalchemy.sql import base from sqlalchemy.sql import column from sqlalchemy.sql import elements +from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.sql import operators from sqlalchemy.sql import table from sqlalchemy.sql import util as sql_util from sqlalchemy.sql import visitors +from sqlalchemy.sql.selectable import LABEL_STYLE_NONE from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL @@ -265,7 +267,7 @@ class SelectableTest( assert sel2.corresponding_column(keyed.c.z) is sel2.c.z def test_keyed_label_gen(self): - s = select(keyed).apply_labels() + s = select(keyed).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) assert ( s.selected_columns.corresponding_column(keyed.c.colx) @@ -371,8 +373,12 @@ class SelectableTest( def test_distance_on_aliases(self): a1 = table1.alias("a1") for s in ( - select(a1, table1).apply_labels().subquery(), - select(table1, a1).apply_labels().subquery(), + select(a1, table1) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery(), + select(table1, a1) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery(), ): assert s.corresponding_column(table1.c.col1) is s.c.table1_col1 assert s.corresponding_column(a1.c.col1) is s.c.a1_col1 @@ -397,7 +403,11 @@ class SelectableTest( # test alias of the join - j2 = jjj.select().apply_labels().subquery("foo") + j2 = ( + jjj.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery("foo") + ) assert j2.corresponding_column(table1.c.col1) is j2.c.table1_col1 def test_clone_append_column(self): @@ -505,7 +515,11 @@ class SelectableTest( self.assert_compile(group, "b / (y * w)") def test_subquery_on_table(self): - sel = select(table1, table2).apply_labels().subquery() + sel = ( + select(table1, table2) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) assert sel.corresponding_column(table1.c.col1) is sel.c.table1_col1 assert ( @@ -609,8 +623,8 @@ class SelectableTest( table2.c.coly, ) ) - s1 = table1.select(use_labels=True) - s2 = table2.select(use_labels=True) + s1 = table1.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + s2 = table2.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) assert ( u.corresponding_column(s1.selected_columns.table1_col2) @@ -705,8 +719,16 @@ class SelectableTest( ) .alias("analias") ) - s1 = table1.select(use_labels=True).subquery() - s2 = table2.select(use_labels=True).subquery() + s1 = ( + table1.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) + s2 = ( + table2.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) assert u.corresponding_column(s1.c.table1_col2) is u.c.col2 assert u.corresponding_column(s2.c.table2_col2) is u.c.col2 assert u.corresponding_column(s2.c.table2_coly) is u.c.coly @@ -837,8 +859,12 @@ class SelectableTest( d = table("d", column("id"), column("aid")) u1 = union( - a.join(b, a.c.id == b.c.aid).select().apply_labels(), - a.join(d, a.c.id == d.c.aid).select().apply_labels(), + a.join(b, a.c.id == b.c.aid) + .select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), + a.join(d, a.c.id == d.c.aid) + .select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), ).alias() eq_(u1.c.keys(), ["a_id", "b_id", "b_aid"]) @@ -927,8 +953,16 @@ class SelectableTest( .alias("analias") ) s = select(u).subquery() - s1 = table1.select(use_labels=True).subquery() - s2 = table2.select(use_labels=True).subquery() + s1 = ( + table1.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) + s2 = ( + table2.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) assert s.corresponding_column(s1.c.table1_col2) is s.c.col2 assert s.corresponding_column(s2.c.table2_col2) is s.c.col2 @@ -961,7 +995,7 @@ class SelectableTest( def test_join(self): a = join(table1, table2) - print(str(a.select().apply_labels())) + print(str(a.select().set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL))) b = table2.alias("b") j = join(a, b) print(str(j)) @@ -976,7 +1010,11 @@ class SelectableTest( self.assert_(criterion.compare(j.onclause)) def test_subquery_labels_join(self): - a = table1.select().apply_labels().subquery() + a = ( + table1.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) j = join(a, table2) criterion = a.c.table1_col1 == table2.c.col2 @@ -1020,7 +1058,11 @@ class SelectableTest( eq_(s.corresponding_column(l1), s.c.foo) def test_select_alias_labels(self): - a = table2.select(use_labels=True).alias("a") + a = ( + table2.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .alias("a") + ) j = join(a, table1) criterion = table1.c.col1 == a.c.table2_col2 @@ -1046,7 +1088,11 @@ class SelectableTest( t2 = Table("t2", m, Column("id", Integer, ForeignKey("t1.id"))) t3 = Table("t3", m2, Column("id", Integer, ForeignKey("t1.id2"))) - s = select(t2, t3).apply_labels().subquery() + s = ( + select(t2, t3) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) assert_raises(exc.NoReferencedTableError, s.join, t1) @@ -1076,7 +1122,7 @@ class SelectableTest( # style to the select, eliminating the self-referential call unless # the select already had labeling applied - s = select(t).apply_labels() + s = select(t).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) with testing.expect_deprecated("The SelectBase.c"): s.where.non_generative(s, s.c.t_x > 5) @@ -1280,7 +1326,7 @@ class RefreshForNewColTest(fixtures.TestBase): def test_select_samename_init(self): a = table("a", column("x")) b = table("b", column("y")) - s = select(a, b).apply_labels() + s = select(a, b).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) s.selected_columns q = column("x") b.append_column(q) @@ -1290,7 +1336,11 @@ class RefreshForNewColTest(fixtures.TestBase): def test_alias_alias_samename_init(self): a = table("a", column("x")) b = table("b", column("y")) - s1 = select(a, b).apply_labels().alias() + s1 = ( + select(a, b) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .alias() + ) s2 = s1.alias() s1.c @@ -1309,7 +1359,11 @@ class RefreshForNewColTest(fixtures.TestBase): def test_aliased_select_samename_uninit(self): a = table("a", column("x")) b = table("b", column("y")) - s = select(a, b).apply_labels().alias() + s = ( + select(a, b) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .alias() + ) q = column("x") b.append_column(q) s._refresh_for_new_column(q) @@ -1318,7 +1372,11 @@ class RefreshForNewColTest(fixtures.TestBase): def test_aliased_select_samename_init(self): a = table("a", column("x")) b = table("b", column("y")) - s = select(a, b).apply_labels().alias() + s = ( + select(a, b) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .alias() + ) s.c q = column("x") b.append_column(q) @@ -1329,7 +1387,11 @@ class RefreshForNewColTest(fixtures.TestBase): a = table("a", column("x")) b = table("b", column("y")) c = table("c", column("z")) - s = select(a, b).apply_labels().alias() + s = ( + select(a, b) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .alias() + ) s.c q = column("x") c.append_column(q) @@ -1338,7 +1400,11 @@ class RefreshForNewColTest(fixtures.TestBase): def test_aliased_select_no_cols_clause(self): a = table("a", column("x")) - s = select(a.c.x).apply_labels().alias() + s = ( + select(a.c.x) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .alias() + ) s.c q = column("q") a.append_column(q) @@ -1616,7 +1682,13 @@ class JoinConditionTest(fixtures.TestBase, AssertsCompiledSQL): (t1, t4, None), (t1t2, t2t3, None), (t5, t1, None), - (t5.select(use_labels=True).subquery(), t1, None), + ( + t5.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery(), + t1, + None, + ), ]: assert_raises( exc.ArgumentError, @@ -1705,7 +1777,11 @@ class JoinConditionTest(fixtures.TestBase, AssertsCompiledSQL): t1t2 = t1.join(t2) t2t3 = t2.join(t3) - st2t3 = t2t3.select().apply_labels().subquery() + st2t3 = ( + t2t3.select() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .subquery() + ) j = t1t2.join(st2t3) assert j.onclause.compare(t2.c.id == st2t3.c.t3_t2id) self.assert_compile( @@ -2142,7 +2218,7 @@ class ReduceTest(fixtures.TestBase, AssertsExecutionResults): people.outerjoin(engineers) .outerjoin(managers) .select() - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .alias("pjoin") ) eq_( @@ -2850,7 +2926,7 @@ class WithLabelsTest(fixtures.TestBase): m = MetaData() t1 = Table("t1", m, Column("x", Integer)) t2 = Table("t2", m, Column("x", Integer)) - return select(t1, t2) + return select(t1, t2).set_label_style(LABEL_STYLE_NONE) def test_names_overlap_nolabel(self): sel = self._names_overlap() @@ -2859,7 +2935,9 @@ class WithLabelsTest(fixtures.TestBase): self._assert_subq_result_keys(sel, ["x", "x_1"]) def test_names_overlap_label(self): - sel = self._names_overlap().apply_labels() + sel = self._names_overlap().set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) eq_(list(sel.selected_columns.keys()), ["t1_x", "t2_x"]) eq_(list(sel.subquery().c.keys()), ["t1_x", "t2_x"]) self._assert_result_keys(sel, ["t1_x", "t2_x"]) @@ -2868,7 +2946,7 @@ class WithLabelsTest(fixtures.TestBase): m = MetaData() t1 = Table("t1", m, Column("x", Integer, key="a")) t2 = Table("t2", m, Column("x", Integer, key="b")) - return select(t1, t2) + return select(t1, t2).set_label_style(LABEL_STYLE_NONE) def test_names_overlap_keys_dont_nolabel(self): sel = self._names_overlap_keys_dont() @@ -2878,7 +2956,9 @@ class WithLabelsTest(fixtures.TestBase): self._assert_result_keys(sel, ["x"]) def test_names_overlap_keys_dont_label(self): - sel = self._names_overlap_keys_dont().apply_labels() + sel = self._names_overlap_keys_dont().set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) eq_(list(sel.selected_columns.keys()), ["t1_a", "t2_b"]) eq_(list(sel.subquery().c.keys()), ["t1_a", "t2_b"]) self._assert_result_keys(sel, ["t1_x", "t2_x"]) @@ -2896,7 +2976,9 @@ class WithLabelsTest(fixtures.TestBase): self._assert_result_keys(sel, ["x_id", "id"]) def test_labels_overlap_label(self): - sel = self._labels_overlap().apply_labels() + sel = self._labels_overlap().set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) eq_( list(sel.selected_columns.keys()), ["t_x_id", "t_x_id_1"], @@ -2922,7 +3004,9 @@ class WithLabelsTest(fixtures.TestBase): self._assert_result_keys(sel, ["x_id", "id"]) def test_labels_overlap_keylabels_dont_label(self): - sel = self._labels_overlap_keylabels_dont().apply_labels() + sel = self._labels_overlap_keylabels_dont().set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) eq_(list(sel.selected_columns.keys()), ["t_a", "t_x_b"]) eq_(list(sel.subquery().c.keys()), ["t_a", "t_x_b"]) self._assert_result_keys(sel, ["t_x_id", "t_x_id_1"]) @@ -2940,7 +3024,9 @@ class WithLabelsTest(fixtures.TestBase): self._assert_result_keys(sel, ["a", "b"]) def test_keylabels_overlap_labels_dont_label(self): - sel = self._keylabels_overlap_labels_dont().apply_labels() + sel = self._keylabels_overlap_labels_dont().set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) eq_( list(sel.selected_columns.keys()), ["t_x_id", "t_x_b_1"], @@ -2966,7 +3052,9 @@ class WithLabelsTest(fixtures.TestBase): self._assert_subq_result_keys(sel, ["x_id", "id"]) def test_keylabels_overlap_labels_overlap_label(self): - sel = self._keylabels_overlap_labels_overlap().apply_labels() + sel = self._keylabels_overlap_labels_overlap().set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) eq_( list(sel.selected_columns.keys()), ["t_x_a", "t_x_id_1"], @@ -2992,7 +3080,9 @@ class WithLabelsTest(fixtures.TestBase): self._assert_result_keys(sel, ["a", "b"]) def test_keys_overlap_names_dont_label(self): - sel = self._keys_overlap_names_dont().apply_labels() + sel = self._keys_overlap_names_dont().set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) eq_(list(sel.selected_columns.keys()), ["t1_x", "t2_x"]) eq_(list(sel.subquery().c.keys()), ["t1_x", "t2_x"]) self._assert_result_keys(sel, ["t1_a", "t2_b"]) @@ -3041,7 +3131,11 @@ class ResultMapTest(fixtures.TestBase): def test_select_alias_column_apply_labels(self): t = self._fixture() x, y = t.c.x, t.c.y - s = select(x, y).apply_labels().alias() + s = ( + select(x, y) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .alias() + ) mapping = self._mapping(s) assert t.c.x in mapping @@ -3124,7 +3218,7 @@ class ResultMapTest(fixtures.TestBase): def test_unary_boolean(self): - s1 = select(not_(True)).apply_labels() + s1 = select(not_(True)).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) eq_( [type(entry[-1]) for entry in s1.compile()._result_columns], [Boolean], diff --git a/test/sql/test_text.py b/test/sql/test_text.py index 0cd6f8cf37..6dccf3be60 100644 --- a/test/sql/test_text.py +++ b/test/sql/test_text.py @@ -20,6 +20,7 @@ from sqlalchemy import text from sqlalchemy import union from sqlalchemy import util from sqlalchemy.sql import column +from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.sql import quoted_name from sqlalchemy.sql import sqltypes from sqlalchemy.sql import table @@ -119,7 +120,7 @@ class SelectCompositionTest(fixtures.TestBase, AssertsCompiledSQL): table1.c.myid, ) .select_from(table1) - .apply_labels(), + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT column1, column2, column3 AS bar, " "mytable.myid AS mytable_myid " "FROM mytable", @@ -135,7 +136,7 @@ class SelectCompositionTest(fixtures.TestBase, AssertsCompiledSQL): table1.c.myid, ) .select_from(table1) - .apply_labels(), + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT column1 AS foobar, column2 AS hoho, " "mytable.myid AS mytable_myid FROM mytable", ) @@ -700,7 +701,11 @@ class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL): def test_order_by_alias_colname(self): t1 = table1.alias() - stmt = select(t1.c.myid).apply_labels().order_by("name") + stmt = ( + select(t1.c.myid) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .order_by("name") + ) self.assert_compile( stmt, "SELECT mytable_1.myid AS mytable_1_myid " @@ -803,7 +808,11 @@ class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL): def test_group_by_subquery(self): stmt = select(table1).alias() - stmt = select(stmt).apply_labels().group_by("myid") + stmt = ( + select(stmt) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .group_by("myid") + ) self.assert_compile( stmt, "SELECT anon_1.myid AS anon_1_myid, anon_1.name AS anon_1_name, " @@ -923,7 +932,7 @@ class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL): s1 = ( select(*[adapter.columns[expr] for expr in exprs]) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .order_by("myid", "t1name", "x") ) @@ -955,7 +964,7 @@ class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL): s1 = ( select(*[adapter.columns[expr] for expr in exprs]) - .apply_labels() + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) .order_by("myid", "t1name", "x") ) diff --git a/test/sql/test_type_expressions.py b/test/sql/test_type_expressions.py index e6f7dbdaff..5f278fb559 100644 --- a/test/sql/test_type_expressions.py +++ b/test/sql/test_type_expressions.py @@ -8,6 +8,7 @@ from sqlalchemy import Table from sqlalchemy import testing from sqlalchemy import TypeDecorator from sqlalchemy import union +from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures @@ -128,7 +129,7 @@ class SelectTest(_ExprFixture, fixtures.TestBase, AssertsCompiledSQL): table = self._fixture() self.assert_compile( - select(table).apply_labels(), + select(table).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL), "SELECT test_table.x AS test_table_x, " "lower(test_table.y) AS test_table_y FROM test_table", ) @@ -136,7 +137,11 @@ class SelectTest(_ExprFixture, fixtures.TestBase, AssertsCompiledSQL): def test_select_cols_use_labels_result_map_targeting(self): table = self._fixture() - compiled = select(table).apply_labels().compile() + compiled = ( + select(table) + .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) + .compile() + ) assert table.c.y in compiled._create_result_map()["test_table_y"][1] assert table.c.x in compiled._create_result_map()["test_table_x"][1] @@ -323,8 +328,8 @@ class DerivedTest(_ExprFixture, fixtures.TestBase, AssertsCompiledSQL): s3 = j.select() self.assert_compile( s3, - "SELECT anon_1.x, lower(anon_1.y) AS y, anon_2.x, " - "lower(anon_2.y) AS y " + "SELECT anon_1.x, lower(anon_1.y) AS y, anon_2.x AS x_1, " + "lower(anon_2.y) AS y_1 " "FROM (SELECT test_table.x AS x, test_table.y AS y " "FROM test_table) AS anon_1 JOIN (SELECT " "test_table.x AS x, test_table.y AS y " @@ -380,7 +385,9 @@ class RoundTripTestBase(object): self.tables.test_table.insert(), {"x": "X1", "y": "Y1"} ) row = connection.execute( - select(self.tables.test_table).apply_labels() + select(self.tables.test_table).set_label_style( + LABEL_STYLE_TABLENAME_PLUS_COL + ) ).first() eq_(row._mapping[self.tables.test_table.c.y], "Y1") -- 2.47.2