From 0c31059a1d6303d2d16b59eb6a5c2c751acdf14a Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 27 Feb 2021 12:48:54 -0500 Subject: [PATCH] support stringify for plain CTE Altered the compilation for the :class:`.CTE` construct so that a string is returned representing the inner SELECT statement if the :class:`.CTE` is stringified directly, outside of the context of an enclosing SELECT; This is the same behavior of :meth:`_FromClause.alias` and :meth:`_SelectStatement.subquery`. Previously, a blank string would be returned as the CTE is normally placed above a SELECT after that SELECT has been generated, which is generally misleading when debugging. Change-Id: Id3007c28e4a7a56d867e850bb890752946bd8f6f References: #5988 --- doc/build/changelog/unreleased_14/cte_str.rst | 11 ++++++++++ lib/sqlalchemy/sql/compiler.py | 21 ++++++++++++++----- test/sql/test_compiler.py | 11 ++++++++++ 3 files changed, 38 insertions(+), 5 deletions(-) create mode 100644 doc/build/changelog/unreleased_14/cte_str.rst diff --git a/doc/build/changelog/unreleased_14/cte_str.rst b/doc/build/changelog/unreleased_14/cte_str.rst new file mode 100644 index 0000000000..1dfaad27ec --- /dev/null +++ b/doc/build/changelog/unreleased_14/cte_str.rst @@ -0,0 +1,11 @@ +.. change:: + :tags: change, sql + + Altered the compilation for the :class:`.CTE` construct so that a string is + returned representing the inner SELECT statement if the :class:`.CTE` is + stringified directly, outside of the context of an enclosing SELECT; This + is the same behavior of :meth:`_FromClause.alias` and + :meth:`_SelectStatement.subquery`. Previously, a blank string would be + returned as the CTE is normally placed above a SELECT after that SELECT has + been generated, which is generally misleading when debugging. + diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 763b4cabbd..ff1eb686bc 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -2550,12 +2550,23 @@ class SQLCompiler(Compiled): kwargs["positional_names"] = self.cte_positional[cte] = [] assert kwargs.get("subquery", False) is False - text += " AS %s\n(%s)" % ( - self._generate_prefixes(cte, cte._prefixes, **kwargs), - cte.element._compiler_dispatch( + + if not self.stack: + # toplevel, this is a stringify of the + # cte directly. just compile the inner + # the way alias() does. + return cte.element._compiler_dispatch( + self, asfrom=asfrom, **kwargs + ) + else: + prefixes = self._generate_prefixes( + cte, cte._prefixes, **kwargs + ) + inner = cte.element._compiler_dispatch( self, asfrom=True, **kwargs - ), - ) + ) + + text += " AS %s\n(%s)" % (prefixes, inner) if cte._suffixes: text += " " + self._generate_prefixes( diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 140de86223..acf16ca1c3 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -4309,6 +4309,17 @@ class StringifySpecialTest(fixtures.TestBase): "SELECT anon_1.myid FROM anon_1", ) + @testing.combinations(("cte",), ("alias",), ("subquery",)) + def test_grouped_selectables_print_alone(self, modifier): + stmt = select(table1).where(table1.c.myid == 10) + + grouped = getattr(stmt, modifier)() + eq_ignore_whitespace( + str(grouped), + "SELECT mytable.myid, mytable.name, " + "mytable.description FROM mytable WHERE mytable.myid = :myid_1", + ) + def test_next_sequence_value(self): # using descriptive text that is intentionally not compatible # with any particular backend, since all backends have different -- 2.47.2