使用python sqlalchemy通过WITH语句执行原始查询 [英] Using python sqlalchemy to execute raw queries with WITH statement

问题描述

我正在尝试使用原始sqlalchemy text（）查询将值插入Postgres11数据库。
通过psql-client运行以下SQL查询时，它可以正常运行：

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;

正确插入所有行：

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

但是，如果我在sqlalchemy中创建一个引擎，并使用相同的查询在其上调用execute，它会成功运行，但不会插入任何行：

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>

但未插入新行：

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

通过psql-client运行查询与通过sqlalchemy执行查询有何不同？

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

推荐答案

我的问题在github上得到了回答。

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

这篇关于使用python sqlalchemy通过WITH语句执行原始查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！