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

python transactions pyodbc
MetaHyperBolic picture MetaHyperBolic · Jun 30, 2009 · Viewed 17.8k times · Source

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

rob picture rob · Jun 30, 2009

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

Related questions

Django - Rollback save with transaction atomic

I am trying to create a view where I save an object but I'd like to undo that save if some exception is raised. This is what I tried: class MyView(View): @transaction.atomic def post(self, request, *args, **kwargs): …

Read More
Atomic increment of a counter in django

I'm trying to atomically increment a simple counter in Django. My code looks like this: from models import Counter from django.db import transaction @transaction.commit_on_success def increment_counter(name): counter = Counter.objects.get_or_create(name = name)[0] …

Read More
How to make Flask-SQLAlchemy automatically rollback the session if an exception is raised?

I'd like to setup an app built with Flask-SQLAlchemy to rollback all changes done to the database if the view raises an exception that bubbles outside the view code (i.e. not catched inside). I'd like it to work even …

Read More