A thing which I seem to do a lot in my projects with databases is have cursors hanging around, and a distinct lack of transaction management. One evolutionary step I took to correct that is starting every method in my model with a call to the database connection to get a new cursor, like this:

cursor = self._db.cursor()
cursor.execute('BEGIN')
... code ...
cursor.execute('COMMIT')

Of course one needs to take special care to handle exceptions, rollback if needed, etc. And this is a lot of duplicated code, in every method.

One solution I had thought of was to use a decorator to handle this plumbing, something which opens the cursor, and passes it as the first argument to the method. I was reading about this solution when I saw somebody pointed out that it makes the prototype for the method confusing, since it has an extra argument which is magically filled in. I can't help but agree with this: it breaks the self-documentation. After digging a bit on how to have the decorator modify the prototype at decoration time so that the prototype for the method could be 'pure', I discovered the idea of using a context manager to handle the plumbing.

I'm not going to go into the ins and outs of how context managers work, but it comes down to something like this:

@contextlib.contextmanager
def cursor(self):
    try:
        curs = self._db.cursor()
        curs.execute('BEGIN')
        yield curs
        curs.execute('COMMIT')
    except:
        curs.execute('ROLLBACK')
    curs.close()

Then, in the methods that need a cursor, something like this:

with self.cursor() as cursor do:
   ... database stuff  ....

This is a lovely solution in my opinion because we can centralize the database stuff (including handling the connection closing and things) in one spot, and the per-method code is really light, and we keep our method prototypes clean and descriptive.