Quick Note: DB Cursor Management Magics in Python
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.