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

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 k

Solution 1:

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.

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)

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

Solution 2:

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