On 5/8/15 12:10 PM, Mike Bayer wrote:
On 5/8/15 3:47 AM, Kristi Tsukida wrote:
It looks like in sqlalchemy >= 0.9.9 that a insert from cte doesn't
get rendered properly.
Code:
from sqlalchemy import *
table = Table("my_table", MetaData(),
Column("id", Integer, primary_key=True),
Column("name", String(30)),
)
c = table.select().cte("c")
query = c.select()
insert = table.insert().from_select(table.columns, query)
print(insert)
====
In sqlalchemy 0.9.8 this produces:
INSERT INTO my_table (id, name) WITH c AS
(SELECT my_table.id AS id, my_table.name AS name
FROM my_table)
SELECT c.id, c.name
FROM c
So this is not any behavior that's ever been supported. The
overarching issue to add CTE support to DML is
https://bitbucket.org/zzzeek/sqlalchemy/issue/2551/. It's undefined
behavior that this happened to stick the WITH clause in there like that.
This syntax is surprising, as PG's syntax for INSERT shows the WITH
clause on top, which is #2551. However, both are apparently accepted;
so one thing that is important is if there is any semantic difference
between the WITH part of this being after the INSERT or on top of
it. And it per
http://www.postgresql.org/docs/9.4/interactive/sql-insert.html it
appears there is: " It is possible for the query (SELECT statement) to
also contain a WITH clause. In such a case both sets of with_query can
be referenced within the query, but the second one takes precedence
since it is more closely nested."
So it seems that in 2551, while we need to add cte() to insert(),
update(), and delete(), and that will allow the WITH clause to be on
top of the INSERT etc., if the SELECT itself sent to
insert.from_select() has a CTE it is probably appropriate that the CTE
render as it is in 0.9.8.
I can probably add this feature to Insert.from_select() as a separate
thing from the rest of #2551, e.g. that if there is a SELECT-based CTE
in play referred to by the SELECT, the CTE can render, but it's for
the 1.0 series.
This is
https://bitbucket.org/zzzeek/sqlalchemy/issue/3418/preserve-cte-from-a-select-when-doing
and I was able to backport it for 0.9.10.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
