在 Python 中，使用 pyodbc，如何执行事务? [英] In Python, Using pyodbc, How Do You Perform Transactions?

问题描述

我有一个用户名，我必须在许多(最多约 25 个)表中更改该用户名.(是的，我知道.)原子事务似乎是处理此类事情的方法.但是，我不知道如何用 pyodbc 做到这一点.之前看过各种关于原子事务的教程，但都没有用过.

I have a username which I must change in numerous (up to ~25) tables. (Yeah, I know.) An atomic transaction seems to be the way to go for this sort of thing. However, I do not know how to do this with pyodbc. I've seen various tutorials on atomic transactions before, but have never used them.

设置:Windows 平台、Python 2.6、pyodbc、Microsoft SQL 2005.我已经将 pyodbc 用于单个 SQL 语句，但没有使用复合语句或事务.

The setup: Windows platform, Python 2.6, pyodbc, Microsoft SQL 2005. I've used pyodbc for single SQL statements, but no compound statements or transactions.

SQL 的最佳实践似乎表明，为此创建存储过程非常好.我对执行存储过程的担忧如下，按重要性递增:1)我从来没有写过存储过程.2) 我听说 pyodbc 还没有从存储过程返回结果.3) 这绝对不是我的数据库.它由供应商提供、供应商更新，等等.

Best practices for SQL seem to suggest that creating a stored procedure is excellent for this. My fears about doing a stored procedure are as follows, in order of increasing importance: 1) I have never written a stored procedure. 2) I heard that pyodbc does not return results from stored procedures as of yet. 3) This is most definitely Not My Database. It's vendor-supplied, vendor-updated, and so forth.

那么，解决这个问题的最佳方法是什么?

So, what's the best way to go about this?

推荐答案

通过其 文档，pyodbc 确实支持事务，但前提是 odbc 驱动程序支持它.此外，由于 pyodbc 符合 PEP 249，因此仅当手动提交完成.
这意味着您必须明确地commit() 事务，或rollback() 整个事务.

By its documentation, pyodbc does support transactions, but only if the odbc driver support it. Furthermore, as pyodbc is compliant with PEP 249, data is stored only when a manual commit is done.
This means that you have to explicitely commit() the transaction, or rollback() the entire transaction.

注意pyodbc也支持自动提交功能，在这种情况下你不能有任何事务.
默认情况下，自动提交处于关闭状态，但您的代码库可能已将其打开.你应该检查连接，当它被执行

Note that pyodbc also support autocommit feature, and in that case you cannot have any transaction.
By default, autocommit is off, but your codebase might have tuerned it on. You should check the connection, when it is performed

cnxn = pyodbc.connect(cstring, autocommit=True)

或者，您也可以使用

cnxn.autocommit = False

但这可能会对您的系统产生相当大的影响.

but this might have quite a big impact on your system.

注意:您可以在其 wiki 上获得有关 pyodbc 自动提交模式的更多信息

这篇关于在 Python 中，使用 pyodbc，如何执行事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！