python - Better approach to handling sqlalchemy disconnects -

we've been experimenting sqlalchemy's disconnect handling, , how integrates orm. we've studied docs, , advice seems to catch disconnect exception, issue rollback() , retry code.

eg:

import sqlalchemy sa  retry = 2 while retry:     retry -= 1     try:         name in session.query(names):             print name         break     except sa.exc.dbapierror exc:         if retry , exc.connection_invalidated:             session.rollback()         else:             raise 

i follow rationale -- have rollback active transactions , replay them ensure consistent ordering of actions.

but -- means lot of code added every function wants work data. furthermore, in case of select, we're not modifying data , concept of rollback/re-request not unsightly, violation of principle of dry (don't repeat yourself).

i wondering if others mind sharing how handle disconnects sqlalchemy.

fyi: we're using sqlalchemy 0.9.8 , postgres 9.2.9

the way approach place database code in lambda or closure, , pass helper function handle catching disconnect exception, , retrying.

so example:

import sqlalchemy sa  def main():     def query():         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 successful , return results          except sa.exc.dbapierror exc:             if attempts > 0 , exc.connection_invalidated:                 session.rollback()             else:                 raise 

you can make more fancy passing boolean run_query handle case doing read, , therefore want retry without rolling back.

this helps satisfy dry principle since ugly boiler-plate code managing retries + rollbacks placed in 1 location.