Better approach to handling sqlalchemy disconnects

Question

We've been experimenting with sqlalchemy's disconnect handling, and how it integrates with ORM. We've studied the docs, and the advice seems to be to catch the disconnect exception, issue a rollback() and retry the code.

eg:

import sqlalchemy as SA  retry = 2 while retry:     retry -= 1     try:         for name in session.query(Names):             print name         break     except SA.exc.DBAPIError as exc:         if retry and exc.connection_invalidated:             session.rollback()         else:             raise 

I follow the rationale -- you have to rollback any active transactions and replay them to ensure a consistent ordering of your actions.

BUT -- this means a lot of extra code added to every function that wants to work with data. Furthermore, in the case of SELECT, we're not modifying data and the concept of rollback/re-request is not only unsightly, but a violation of the principle of DRY (don't repeat yourself).

I was wondering if others would mind sharing how they handle disconnects with sqlalchemy.

FYI: we're using sqlalchemy 0.9.8 and Postgres 9.2.9

Answer 1

The way I like to approach this is place all my database code in a lambda or closure, and pass that into a helper function that will handle catching the disconnect exception, and retrying.

So with your example:

import sqlalchemy as SA  def main():     def query():         for name in session.query(Names):             print name      run_query(query)  def run_query(f, attempts=2):     while attempts > 0:         attempts -= 1         try:             return f() # "break" if query was successful and return any results          except SA.exc.DBAPIError as exc:             if attempts > 0 and exc.connection_invalidated:                 session.rollback()             else:                 raise 

You can make this more fancy by passing a boolean into run_query to handle the case where you are only doing a read, and therefore want to retry without rolling back.

This helps you satisfy the DRY principle since all the ugly boiler-plate code for managing retries + rollbacks is placed in one location.