How to get a SQLAlchemy session managed by zope.transaction that has the same scope as a http request but that does not close automatically on commit?

pyramid, python, sqlalchemy, transactions, zope

Solution

You can detach the session from the request via the `keep_session=True` on the ZTE. However, you probably also want to use the objects after the session is committed if this is the case, otherwise you'd be happy with a new session. Thus, you'll also want `expire_on_commit=False` on your session. After that, you've successfully detached the session from the lifecycle of pyramid_tm and you can commit/abort as you please. So how do we reattach it back to the request lifecycle without using pyramid_tm? Well, if you wrap your `DBSession` in something that's a little less global which will make it more manageable as a request-scoped variable, that'd help. From there, it's obvious when the session is created, and when it should be destroyed via a request-finished callback. Here's a summary of my prose:

def get_db(request):
    session = request.registry['db_session_factory']()
    def _closer(request):
        session.close()
    request.add_finished_callback(_closer)
    return session

def main(global_conf, **settings):
    config = Configurator()

    DBSession = sessionmaker(expire_on_commit=False, extension=ZopeTransactionExtension(keep_session=True))
    # look we don't even need a scoped_session anymore because it's not global, it's local to the request!

    config.registry['db_session_factory'] = DBSession

    config.add_request_method('db', get_db, reify=True)

def myview(request):
    db = request.db # creates, or reuses the session created for this request
    model = db.query(MyModel).first();
    transaction.commit()
    # model is still valid here
    return {}

Of course, if we're doing all this, the ZTE may not be helping you at all and you just want to use `db.commit()` and handle things yourself. The finished callbacks will still be invoked if an exception occurs, so you don't need pyramid_tm to cleanup after you.

Problem

I have a Pyramid web application with some form pages that reads data from database and write to it as well. The application uses SQLAlchemy with a PostgreSQL database and here is how I setup the SQLAlchemy session: ``` from sqlalchemy.orm import scoped_session from sqlalchemy.orm import sessionmaker from zope.sqlalchemy import ZopeTransactionExtension DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) ``` When I process a form, I need to perform an explicit commit surrounded in a `try` and see if the commit worked. I need this explicit commit because I have deferrable triggers in the PostgreSQL database (checks that are performed at commit time) and there are some cases where the absence of error is not predictable. Once I successfully committed a transaction, for example adding an instance of MyClass, I would like to get some attributes on this instance and also some attributes on linked instances. Indeed, I cannot get those data before committing because they are computed by the database itself. My problem is that, when I use `transaction.commit()` (in `transaction` package) the session is automatically closed and I cannot use the instance anymore because it is in Detached state. Documentation confirms this point. So, as mentioned in the documentation, I tried to use the following session setup instead: ``` DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension(keep_session=True))) ``` However, now the session scope is not the same as the http request scope anymore : no ROLLBACK is sent at the end of my http requests that just perform read queries. So, is there a way to have a session that have the same scope as the http request but that does not close automatically on commit?

Original source