在 Python 中，使用 pyodbc，您如何执行事务？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1063770/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In Python, Using pyodbc, How Do You Perform Transactions?
提问by rob
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.
我有一个用户名，我必须在许多（最多约 25 个）表中更改该用户名。（是的，我知道。）原子事务似乎是处理此类事情的方法。但是，我不知道如何用 pyodbc 做到这一点。我以前看过有关原子事务的各种教程，但从未使用过它们。
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.
设置：Windows 平台、Python 2.6、pyodbc、Microsoft SQL 2005。我将 pyodbc 用于单个 SQL 语句，但没有使用复合语句或事务。
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.
SQL 的最佳实践似乎表明，为此创建存储过程非常好。我对执行存储过程的恐惧如下，按重要性递增： 1) 我从未编写过存储过程。2) 我听说 pyodbc 还没有从存储过程返回结果。3) 这绝对不是我的数据库。它是供应商提供的、供应商更新的，等等。
So, what's the best way to go about this?
那么，最好的方法是什么？
回答by rob
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 确实支持事务，但前提是 odbc 驱动程序支持它。此外，由于 pyodbc 符合PEP 249，因此仅在完成手动提交时才存储数据。
这意味着您必须明确commit()表示交易或rollback()整个交易。
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
请注意，pyodbc 也支持自动提交功能，在这种情况下，您不能有任何事务。
默认情况下，自动提交处于关闭状态，但您的代码库可能已将其打开。您应该在执行时检查连接
cnxn = pyodbc.connect(cstring, autocommit=True)
Alternatively, you can also explicitely turn off the autocommit mode with
或者，您也可以明确地关闭自动提交模式
cnxn.autocommit = False
but this might have quite a big impact on your system.
但这可能会对您的系统产生相当大的影响。
Note: you can get more information on the autocommit mode of pyodbc on its wiki
注意：您可以在其wiki上获得有关 pyodbc 的自动提交模式的更多信息
回答by Lennart Regebro
I don't think pyodbc has any specific support for transactions. You need to send the SQL command to start/commit/rollback transactions.
我不认为 pyodbc 对事务有任何特定的支持。您需要发送 SQL 命令来启动/提交/回滚事务。
