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: By its
[documentation](http://code.google.com/p/pyodbc/wiki/FAQs#Connecting%5Ffails%5Fwith%5Fan%5Ferror%5Fabout%5FSQL%5FATTR%5FAUTOCOMMIT),
pyodbc does support transactions, but only if the odbc driver support it.
Furthermore, as pyodbc is compliant with [PEP
249](http://www.python.org/dev/peps/pep-0249/), 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](http://code.google.com/p/pyodbc/wiki/Features)

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

