From 8bf22c0dcf53076bf1fde18ba636da3919e91b76 Mon Sep 17 00:00:00 2001 From: Eric Masseran Date: Mon, 12 Jul 2021 11:54:11 +0200 Subject: [PATCH] Support Delete --- lib/sqlalchemy/sql/compiler.py | 5 +++-- test/sql/test_cte.py | 28 ++++++++++++++++++++++++++++ 2 files changed, 31 insertions(+), 2 deletions(-) diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 15faf350d5..e0c1028190 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -3892,8 +3892,9 @@ class SQLCompiler(Compiled): delete_stmt, delete_stmt._returning ) - if self.ctes and toplevel: - text = self._render_cte_clause() + text + if self.ctes: + nesting_level = len(self.stack) if not toplevel else None + text = self._render_cte_clause(nesting_level=nesting_level) + text self.stack.pop(-1) diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py index 887738d5f5..09404dafb2 100644 --- a/test/sql/test_cte.py +++ b/test/sql/test_cte.py @@ -1650,3 +1650,31 @@ class NestingCTETest(fixtures.TestBase, AssertsCompiledSQL): "RETURNING table_1.id, table_1.price) " "SELECT update_cte.id, update_cte.price FROM update_cte", ) + + def test_select_from_delete_cte_with_nesting(self): + t1 = table("table_1", column("id"), column("price")) + + generator_cte = select([literal(1).label("id")]).cte( + "generator", nesting=True + ) + + dlt = ( + t1.delete() + .where(t1.c.id == generator_cte.c.id) + .returning(t1.c.id, t1.c.price) + ) + + cte = dlt.cte("delete_cte") + + qry = select(cte) + + self.assert_compile( + qry, + "WITH delete_cte AS " + "(WITH generator AS " + "(SELECT %(param_1)s AS id) " + "DELETE FROM table_1 USING generator " + "WHERE table_1.id = generator.id RETURNING table_1.id, " + "table_1.price) SELECT delete_cte.id, delete_cte.price " + "FROM delete_cte", + ) -- 2.47.3