在 SELECT 查询后进行 SQLAlchemy COMMIT 而不是 ROLLBACK [英] Make SQLAlchemy COMMIT instead of ROLLBACK after a SELECT query

问题描述

我正在与合作伙伴一起开发应用.我负责数据库部分 (PostgreSQL)，我的合作伙伴使用 SQLAlchemy 在带有 python 的网络服务器上实现了该应用程序.我们大量使用存储过程.在数据库日志中对其中之一的 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屋！