Using python sqlalchemy to execute raw queries with WITH statement

Question

I'm trying to insert values into a Postgres11 database with raw sqlalchemy text() queries. 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 runs successfully but no rows are inserted:

>>> 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)

In what way is running the query through the psql-client different from executing it through sqlalchemy?

Related: stackoverflow.com/questions/45347565/…

Ilja Everilä
– Ilja Everilä

2020-01-12 08:44:30 +00:00
Commented Jan 12, 2020 at 8:44 — Ilja Everilä
– Ilja Everilä, Commented Jan 12, 2020 at 8:44

Sephor · Accepted Answer · 2020-01-12 09:41:50Z

3

My question got answered on github.

The solution is to wrap the execute in a transaction context:

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

answered Jan 12, 2020 at 9:41

Sephor

411 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jim Jones Over a year ago

Consider closing the question as answered :-) Thanks for reporting.

sameli74 · Accepted Answer · 2020-10-15 18:46:07Z

1

I had a similar issue and the following code helped me. Without the conn.execute("COMMIT;") part, I was not able to see all my changes reflected in my database.

with self.engine.begin() as conn:
    conn.execute(sql_query)
    conn.execute("COMMIT;")

answered Oct 15, 2020 at 18:46

sameli74

111 bronze badge

Collectives™ on Stack Overflow

Using python sqlalchemy to execute raw queries with WITH statement

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related