SQLAlchemy not including CTEs in DELETE

Question

I'm attempting to utilize a CTE with a DELETE in SQLAlchemy (core). So far, my attempts have been unsuccessful whereby the CTE is not being included in the compiled SQL statement. Ultimately, this will be run against a PostgreSQL database, which supports this kind of statement.

Python code of a contrived example:

from sqlalchemy import *
from sqlalchemy.dialects import postgresql
metadata = MetaData()
tbl = Table('foo', metadata,
        Column('id', Integer, primary_key = True),
        Column('name', String)
    )

mycte = select([tbl.c.name]).where(tbl.c.id == 123).cte('ctetbl')
delete_stmt = tbl.delete().where(tbl.c.name == mycte.c.name)
print("Regular:", delete_stmt.compile())
print("Postgres:", delete_stmt.compile(dialect = postgresql.dialect()))

What I'm expecting is (or something like):

WITH ctetbl AS (
    SELECT name FROM foo
    WHERE id = 123
)
DELETE FROM foo WHERE foo.name = ctetbl.name

What I'm getting is:

DELETE FROM foo WHERE foo.name = ctetbl.name

What am I missing here?

Answer 1

In

WITH ctetbl AS (
    SELECT name FROM foo
    WHERE id = 123
)
DELETE FROM foo WHERE foo.name = ctetbl.name

you've not actually included the CTE as a source table in the DELETE statement. Something similar happens in your SQLAlchemy statement due to no support for USING clause and that throws the compiler off. You could add support for it with a compiler extension:

from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Delete, Update
import re

@compiles(Delete, 'postgresql')
def compile_delete(element, compiler, **kw):
    using_clause = None
    extra_froms = Update._extra_froms.__get__(element)

    if extra_froms:
        # Pre-compile extra_froms in order to populate CTEs before
        # compiling the delete statement itself
        using_clause = "USING %s" % ', '.join(
            compiler.process(fr, asfrom=True, **kw)
            for fr in extra_froms
        )

    text = compiler.visit_delete(element, **kw)

    if using_clause:
        # NOTE: This will blow up badly, if your CTEs also
        # contain DELETE statements.
        text = re.sub(
            r"(DELETE FROM \S+)",
            lambda m: "%s %s" % (m.group(1), using_clause),
            text
        )

    return text

and then

delete_stmt = tbl.delete().where(tbl.c.name == mycte.c.name)

will compile as

WITH ctetbl AS 
(SELECT foo.name AS name 
FROM foo
WHERE foo.id = %(id_1)s)
 DELETE FROM foo USING ctetbl WHERE foo.name = ctetbl.name

Using USING you could do a self-join as well:

   ...: tbl_alias = tbl.alias()
   ...: delete_stmt = tbl.delete().\
   ...:     where(tbl.c.name == tbl_alias.c.name).\
   ...:     where(tbl_alias.c.id == 123)
   ...: 
   ...: print("Postgres:", delete_stmt.compile(dialect = postgresql.dialect()))
   ...: 
Postgres: DELETE FROM foo USING foo AS foo_1 WHERE foo.name = foo_1.name AND foo_1.id = %(id_1)s

---

Of course you could've also referenced the CTE with a scalar sub-select without having the compiler extension in place:

   ...: delete_stmt = tbl.delete().where(tbl.c.name == mycte.select().as_scalar())
   ...: 
   ...: print("Postgres:", delete_stmt.compile(dialect = postgresql.dialect()))
Postgres: WITH ctetbl AS 
(SELECT foo.name AS name 
FROM foo 
WHERE foo.id = %(id_1)s)
 DELETE FROM foo WHERE foo.name = (SELECT ctetbl.name 
FROM ctetbl)

but where's the fun in that...