Compiling INSERT/DELETE statements containing WITH clauses

[JoergRittinger]

If you have an insert statement containing a common table expression the compiling to string will fail. In a very simplified way:

from sqlalchemy import MetaData, Table, Column, Integer, select
metadata = MetaData()

table_A = Table("table_A", metadata,
              Column("int", Integer))
table_B = Table("table_B", metadata,
              Column("int", Integer))
table_C = Table("table_C", metadata,
              Column("int", Integer))

cte = select(
    columns=[table_A.c.int],
    from_obj=table_A).cte("cte")
query = select(
    columns=[table_B.c.int],
    from_obj=table_B.join(cte, onclause=table_B.c.int == cte.c.int))
insert = table_C.insert().from_select(['int'], query)

If you print the insert statement you will end up with

WITH cte AS (SELECT "table_A".int AS int FROM "table_A") INSERT INTO "table_C" (int) SELECT "table_B".int FROM "table_B" JOIN cte ON "table_B".int = cte.int

But the EXASOL dialect will complain (syntax error, unexpected INSERT, expecting SELECT or TABLE or VALUES or '(') because the expected syntax would be the WITH statement before the SELECT directly:

INSERT INTO "table_C" (int) WITH cte AS (SELECT "table_A".int AS int FROM "table_A") SELECT "table_B".int FROM "table_B" JOIN cte ON "table_B".int = cte.int

[JoergRittinger]

I wrote to the sqlalchemy mailing list to address this issue: https://groups.google.com/forum/#!topic/sqlalchemy/fCJ7MTe6lKc

[exaSR]

I took the liberty and mentioned your post in Exasol's IDEA area:

IDEA-193: Allow using CTE as source for MERGE, DELETE, UPDATE, SELECT INTO TABLE

[vamega]

@JoergRittinger - Do you have an example for the bad delete syntax? I have a small PR that I know fixes things for the insert case, and if you have a sqlalchemy expression that demonstrates the bad DELETE syntax, I can see if I can fix that as well as part of my PR #81

[JoergRittinger]

I would suppose that you just replace the last line of the above example into something like delete = table_C.delete(whereclause=table_C.c.int == query.c.int)

[vamega]

@JoergRittinger sorry for taking so long to get back to this.

I tried writing a few SQL queries that perform a DELETE using a CTE and wasn't able to get Exasol to accept any version of the syntax I could come up with.

I looked at the Exasol documentation, and from my reading it doesn't seem like Exasol supports a CTE based DELETE.

Given that I think PR #81 fixes all the issues that can be addressed by SQLAlchemy.