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.
In sqlalchemy 1.0.4 this produces:
INSERT INTO my_table (id, name) SELECT c.id, c.name
FROM c
Run with Python 3.4.3
--
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 sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
