Sharing MySQLdb Transactions Through Functions
I am using MySQLdb to connect to MySQL using python. My tables are all InnoDB and I am using transactions.
I am struggling to come up with a way to "share" transactions between functions. Consider the following pseudocode:
def foo():
db = connect()
cur = db.cursor()
try:
cur.execute(...)
conn.commit()
except:
conn.rollback()
def bar():
db = connect()
cur = db.cursor()
try:
cur.execute(...)
foo() # note this call
conn.commit()
except:
conn.rollback()
In some cases in my code I need to call foo()
, and in some cases I need to call bar()
. What's the best practice here? How can I make a call to a call foo()
in commit()
if called outside bar()
but not inside bar()
? This is obviously more difficult if there are multiple threads calling foo()
and bar()
, and calls to connect()
not return the same connection object.
UPDATE
I found a solution that works for me. I wrapped connect()
to increase the value when called. The call commit()
decreases this value. If commit()
this counter is also called > 0, then no commit occurs and the value is decremented. So you get this:
def foo():
db = connect() # internal counter = 1
...
db.commit() # internal counter = 0, so commit
def bar():
db = connect() # internal counter = 1
...
foo() # internal counter goes to 2, then to 1 when commit() is called, so no commit happens
db.commit() # internal counter = 0, so commit
source to share
In this case, you can use the Python default function arguments:
def foo(cur=None):
inside_larger_transaction = False
if cursor is None:
db = connect()
cur = db.cursor()
inside_larger_transaction = True
try:
cur.execute(...)
if not inside_larger_transaction:
conn.commit()
except:
conn.rollback()
So, if bar
it foo
does, it just passes the cursor object as a parameter.
Not that I don't see much point in creating a different cursor object for every little function - you must either write your multiple functions as methods of the object, have a cursor attribute, or explicitly skip cursors (in this case, use a different named parameter to indicate if the current function is part of a large transaction or not)
Another option is to create a context-manager class to commit your commits and encapsulate all transactions within it - therefore none of your functions should commit a transaction - you should support both transactional calls and transactional calls to a method on __exit__
that object.
class Transaction(object):
def __enter__(self):
self.db = connect()
cursor = self.db.cursor()
return cursor
def __exit__(self, exc_type, exc_value, traceback):
if exc_type is None:
self.db.commit()
else:
self.db.rollback()
And just use it like this:
def foo(cursor):
...
def foo(cur):
cur.execute(...)
def bar(cur):
cur.execute(...)
foo(cur)
with Transaction() as cursor:
foo(cursor)
with Transaction() as cursor:
bar(cursor)
source to share