How To Manage This Commit/rollback Pattern In Sqlalchemy

I find myself repeating a lot of this in sqlalchemy, I wonder what's the best way to handle it? try: #do some database query db_session.commit() except: #some exception han

Solution 1:

This is right from my working code, a method of a session registry wrapper.

It is used this way:

# dblink is an object that knows how to connect to the database
with dblink.CommittingSession() as session:
  session.add(...)
  # do anything else.
# at this point, session.commit() has been called.

Or:

try:
  with dblink.CommittingSession() as session:
    session.add(...)
except ...:
  # at this point, session.rollback has been called.
  log.error('We failed!')

The implementation:

from contextlib import contextmanager

classDbLink(object):
    """This class knows how to connect to the database."""# ...# Basically we wrap a sqlalchemy.orm.sessionmaker value here, in session_registry.# You might want to create sessions differently.      @contextmanagerdefCommittingSession(self, **kwargs):
      """Creates a session, commits at the end, rolls back on exception, removes.

      Args:
        **kwargs: optional; supplied to session_registry while asking
          to construct a session (mostly for testing).

      Yields:
        a session object. The session will .commit() when a `with CommittingSession()`
        statement terminates normally, or .rollback() on an exception.
      """try:
        session = self.session_registry(**kwargs)  # this gives us a session.# transaction has already begun here, so no explicit .begin().yield session
      except:
        session.rollback()
        raiseelse:
        session.commit()
      finally:
        # Note: close() unbinds model objects, but keeps the DB connection.
        session.close()
        self.session_registry.remove()

Solution 2:

You could design a function to manage error handle and you should be evaluating them and consider if you need a performance optimization.

defcommit_or_rollback(my_session, do_something, error_type):
    try:
        do_something(my_session)
        my_session.commit()
        returnTrueexcept error_type as err:
        my_session.rollback()
        print(err)
        returnFalsedefdo_something(my_session):
    # do something

commit_result = commit_or_rollback(my_session, do_something, NoResultFound)

Be careful session control and performance. This method could keep the code clearly.