WITH statement in raw text query does nothing

[mmEissen]

The following SQL query works correctly when I run it through psql-client:

WITH a AS (
  INSERT INTO person (id) 
       VALUES ('a')
    RETURNING id
) 
INSERT INTO person_info (person_id) 
     SELECT id 
       FROM a;

All rows are correctly inserted:

# select id from person;
 id 
----
 a
(1 row)

However, if I create an engine in sqlalchemy and call execute on it with an identical query, it returns successfully:

>>> engine.execute("WITH a AS (INSERT INTO person (id) VALUES ('b') RETURNING id) INSERT INTO person_info (person_id) SELECT id from a")
<sqlalchemy.engine.result.ResultProxy object at 0x7f25e6c2a090>

But no new row was inserted:

# select id from person;
 id 
----
 a
(1 row)

[zzzeek]

hi there -

this statement will not autocommit automatically, you should use a transaction:

with engine.begin() as conn:
   conn.execute("whatever")

while the above documentation refers to using the autocommit=True option, long term the whole "autocommit" concept is going away as well as connectionless execution (e.g. engine.execute()) because of this confusion, so it's best to just use "with engine.begin() as conn": for all your operations as that will be the API going forward long term.

[mmEissen]

Thank you so much! This has caused me so much headache.