In Python, Using pyodbc, How Do You Perform Transactions?

Question

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.

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.

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?

Answer 1

I don't think pyodbc has any specific support for transactions. You need to send the SQL command to start/commit/rollback transactions.

Answer 2

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.
However, it also support autocommit feature, and in that case you cannot have any transaction.

You should check the connection, when it is performed

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

or explicitely turn off the autocommit mode with

cnxn.autocommit = False

Note: you can get more information on the autocommit mode of pyodbc on its wiki

Once autocommit is turned off, then you have to explicitely commit() the transaction, or rollback() the entire transaction.