SELECT查询后，使SQLAlchemy COMMIT代替ROLLBACK [英] Make SQLAlchemy COMMIT instead of ROLLBACK after a SELECT query

问题描述

我正在与合作伙伴一起开发应用程序。我负责数据库部分（ PostgreSQL ），我的合作伙伴使用 SQLAlchemy 在python的Web服务器上实现了该应用程序。我们大量使用存储过程。在数据库日志中，对其中一个查询进行 SELECT 查询：

I am developing an app together with a partner. I do the database part (PostgreSQL), my partner implements the app on the web-server with python using SQLAlchemy. We make heavy use of stored procedures. A SELECT query on one of those looks like this in the db log:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT col_a, col_b FROM f_stored_proc(E'myvalue');

ROLLBACK;

在存储过程中，我将某些输入写入日志表。应用程序通过 SELECT 查询，SQLAlchemy仅看到SELECT语句，并坚持使用 ROLLBACK 。记录失败。我需要它来 COMMIT 。我的伙伴声称没有简单的方法，我们将不得不完全删除SQLAlchemy。我认为他一定是错的，但是缺乏主张否则的知识。

In the stored procedures I write certain input to a log table. The app queries by SELECT, SQLAlchemy only sees a SELECT statement and insists on a ROLLBACK. Logging fails. I need it to COMMIT instead. My partner claims there is no easy way, we would have to remove SQLAlchemy altogether. I think he must be wrong but lack the konwledge to claim otherwise.

有没有一种简便的方法可以使SQLAlchemy提交 COMMIT 而不是 ROLLBACK ？

是什么让我无法执行 trans.commit（）？我是否需要为此设置 autoflush = False ？

Is there an easy way to make SQLAlchemy COMMIT instead of ROLLBACK?
What keeps me from just executing trans.commit()? Do I need to set autoflush=False for that?

我已经扫描了常见问题解答，但在那儿找不到答案。

搜索SO发现了一些相关问题如这里和这里，但我不清楚。

也许这食谱会起作用吗？

I have scanned the FAQ, but did not find an answer there.
Searching SO revealed some related questions like here and here, but I am not in the clear.
Maybe this recipe would work?

推荐答案

如果您使用的是SQLAlchemy的连接池，那么您看到的可能是使用后关闭连接时发生的自动回滚。显然有必要确保下次将连接从池中拔出时连接是干净的。有关更多信息，请参见此页面；

If you're using SQLAlchemy's connection pooling, then what you're seeing is probably the automatic rollback that happens when a connection is closed after use. It's apparently necessary to guarantee that the connection is 'clean' for the next time it's pulled out of the pool. See this page for more info; search for 'pooling mechanism' near the top.

根据我的回忆（我上次使用此工具已经有两年了），将隔离级别更改为自动提交将不会成功。不能解决问题，因为它不会将SELECT语句视为需要提交。

From what I recall (it's been a couple years since I last worked with this) changing the isolation level to autocommit won't solve the problem, since it won't see the SELECT statement as requiring a commit.

您真的只想将该语句包装在事务中。我不知道您的代码的结构，但您应该只能使用SQLAlchemy的connection.begin和connection.commit。您甚至可以将BEGIN和COMMIT作为任意SQL执行。

You really just want to wrap that statement in a transaction. I don't know how your code is structured, but you should just be able to use SQLAlchemy's connection.begin and connection.commit. You could even just execute the BEGIN and COMMIT as arbitrary SQL.

这篇关于SELECT查询后，使SQLAlchemy COMMIT代替ROLLBACK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！