CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Sephor
Sephor

Reputation: 41

Using python sqlalchemy to execute raw queries with WITH statement

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?

Upvotes: 1

Views: 3272

Answers (2)

sameli74
sameli74

Reputation: 11

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

Upvotes: 1

Sephor
Sephor

Reputation: 41

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

Upvotes: 3

Related Questions