When should I be calling flush() on SQLAlchemy?

Question

I'm new to SQLAlchemy and have inherited a somewhat messy codebase without access to the original author.

The code is litered with calls to DBSession.flush(), seemingly any time the author wanted to make sure data was being saved. At first I was just following patterns I saw in this code, but as I'm reading docs, it seems this is unnecessary - that autoflushing should be in place. Additionally, I've gotten into a few cases with AJAX calls that generate the error "InvalidRequestError: Session is already flushing".

Under what scenarios would I legitimately want to keep a call to flush()?

This is a Pyramid app, and SQLAlchemy is being setup with:

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension(), expire_on_commit=False))
Base = declarative_base()

Answer 1

The ZopeTransactionExtension on the DBSession in conjunction with the pyramid_tm being active on your project will handle all commits for you. The situations where you need to flush are:

• You want to create a new object and get back the primary key.

  DBSession.add(obj)
  DBSession.flush()
  log.info('look, my new object got primary key %d', obj.id)
  

• You want to try to execute some SQL in a savepoint and rollback if it fails without invalidating the entire transaction.

  sp = transaction.savepoint()
  try:
      foo = Foo()
      foo.id = 5
      DBSession.add(foo)
      DBSession.flush()
  except IntegrityError:
      log.error('something already has id 5!!')
      sp.rollback()
  

In all other cases involving the ORM, the transaction will be aborted for you upon exception, or committed upon success automatically by pyramid_tm. If you execute raw SQL, you will need to execute transaction.commit() yourself or mark the session as dirty via zope.sqlalchemy.mark_changed(DBSession) otherwise there is no way for the ZTE to know the session has changed.

Also you should leave expire_on_commit at the default of True unless you have a really good reason.