Flask SQLAlchemy - set expire_on_commit=False only for current session

Question

How can I set the option expire_on_commit=False only for the current session in Flask-SQLAlchemy?

I can set the option at the inizialization of the SQLAlchemy object with:

db = SQLAlchemy(app, session_options={"expire_on_commit": False})

but in this way all sessions created by Flask-SQLAlchemy will have the option set to False, instead i want to set it only for one session.

I tried db.session.expire_on_commit = False but it does not seem to have any effect.

Answer 1

expire_on_commit is a parameter to the sqlalchemy.orm.session.Session class.

The preferred method of acquiring a Session instance, is through the sqlalchemy.orm.session.sessionmaker class. Instances of sessionmaker are configured with settings that will be used to create Session instances. E.g.:

>>> from sqlalchemy import create_engine
>>> from sqlalchemy.orm import sessionmaker
>>> engine = create_engine('sqlite:///:memory:')
>>> Session = sessionmaker(bind=engine)
>>> type(Session)
<class 'sqlalchemy.orm.session.sessionmaker'>
>>> session = Session()
>>> type(session)
<class 'sqlalchemy.orm.session.Session'>

So, calling a sessionmaker instance returns a Session instance.

With this configuration, each time we call our sessionmaker instance, we get a new Session instance back each time.

>>> session1 = Session()
>>> session2 = Session()
>>> session1 is session2
False

A scoped_session changes the above behaviour:

>>> from sqlalchemy.orm import scoped_session
>>> Session = scoped_session(sessionmaker(bind=engine))
>>> type(Session)
<class 'sqlalchemy.orm.scoping.scoped_session'>
>>> session1 = Session()
>>> session2 = Session()
>>> session1 is session2
True

This is what Flask-SQLAlchemy uses 'under the hood' (and why @CodeLikeBeaker's comment directing you to the Session API was valid). It means that every time you call db.session when handling a request you are using the same underlying session. Here's the same example as above but using the Flask-SQLAlchemy extension.

>>> type(db.session)
<class 'sqlalchemy.orm.scoping.scoped_session'>
>>> session1 = db.session()
>>> session2 = db.session()
>>> session1 is session2
True

Notice that type(db.session) in this example yields the exact same result as type(Session) in the previous example.

all sessions created by Flask-SQLAlchemy will have the option set to False, instead i want to set it only for one session.

Given the fact that Flask-SQLAlchemy only creates one session per request, I take this to mean that you sometimes want a session to expire_on_commit and sometimes not, when handling a request.

One way that you can achieve that is by using a context manager to temporarily turn expire_on_commit off:

from contextlib import contextmanager

@contextmanager
def no_expire():
    s = db.session()
    s.expire_on_commit = False
    try:
        yield
    finally:
        s.expire_on_commit = True

And here is my test model:

class Person(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16))

Configure logging to see what SQLAlchemy is doing:

import logging
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.basicConfig(level=logging.INFO)

Create some test data:

db.drop_all()
db.create_all()
names = ('Jane', 'Tarzan')
db.session.add_all([Person(name=n) for n in names])
db.session.commit()

This is the function I've used for testing:

def test_func():
    # query the db
    people = Person.query.all()
    # commit the session
    db.session.commit()
    # iterate through people accessing name to see if sql is emitted
    for person in people:
        print(f'Person is {person.name}')
    db.session.rollback()

The I run the test function once without the context manager:

test_func()

Here is the stdout:

INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:SELECT person.id AS person_id, person.name AS person_name
FROM person
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:SELECT person.id AS person_id, person.name AS person_name
FROM person
WHERE person.id = %(param_1)s
INFO:sqlalchemy.engine.base.Engine:{'param_1': 1}
*****Person is Jane*****
INFO:sqlalchemy.engine.base.Engine:SELECT person.id AS person_id, person.name AS person_name
FROM person
WHERE person.id = %(param_1)s
INFO:sqlalchemy.engine.base.Engine:{'param_1': 2}
*****Person is Tarzan*****

It can be seen that after the commit, sql is reissued to refresh the object attributes.

And once using the context manager:

db.session.rollback()
with no_expire():
    test_func()

And here is stdout with the context manager:

INFO:sqlalchemy.engine.base.Engine:ROLLBACK
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:SELECT person.id AS person_id, person.name AS person_name
FROM person
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:COMMIT
*****Person is Jane*****
*****Person is Tarzan*****