What's the recommended scoped_session usage pattern in a multithreaded sqlalchemy webapp?

Question

I'm writing an application with python and sqlalchemy-0.7. It starts by initializing the sqlalchemy orm (using declarative) and then it starts a multithreaded web server - I'm currently using web.py for rapid prototyping but that could change in the future. I will also add other "threads" for scheduled jobs and so on, probably using other python threads.

From SA documentation I understand I have to use scoped_session() to get a thread-local session, so my web.py app should end up looking something like:

import web
from myapp.model import Session  # scoped_session(sessionmaker(bind=engine))
from myapp.model import This, That, AndSoOn
urls = blah...
app  = web.application(urls, globals())

class index:
    def GET(self):
        s = Session()
        # get stuff done
        Session().remove()
        return(stuff)

class foo:
    def GET(self):
        s = Session()
        # get stuff done
        Session().remove()
        return(stuff)

Is that the Right Way to handle the session?

As far as I understand, I should get a scoped_session at every method since it'll give me a thread local session that I could not obtain beforehand (like at the module level).

Also, I should call .remove() or .commit() or something like them at every method end, otherwise the session will still contain Persistent objects and I would not be able to query/access the same objects in other threads?

If that pattern is the correct one, it could probably be made better by writing it only once, maybe using a decorator? Such a decorator could get the session, invoke the method and then make sure to dispose the session properly. How would that pass the session to the decorated function?

Are you sure it's `Session().remove()` and not `Session.remove()`? — rr-, Apr 18 '16 at 20:07

score 24 · Accepted Answer · edited Mar 16 '19 at 15:41

24

Yes, this is the right way.

Example:

The Flask microframework with Flask-sqlalchemy extension does what you described. It also does .remove() automatically at the end of each HTTP request ("view" functions), so the session is released by the current thread. Calling just .commit() is not sufficient, you should use .remove().

When not using Flask views, I usually use a "with" statement:

@contextmanager
def get_db_session():
    try:
        yield session
    finally:
        session.remove()

with get_db_session() as session:
    # do something with session

You can create a similar decorator.

Scoped session creates a DBMS connection pool, so this approach will be faster than opening/closing session at each HTTP request. It also works nice with greenlets (gevent or eventlet).

edited Mar 16 '19 at 15:41

sleblanc

3,821
1
34
42

answered Jul 18 '12 at 18:26

Alex Lokk

2,056
2
16
19

thanks for the idea but what is the type of your session variable ? is it the sqlalchemy.orm.scoping.scoped_session or the sqlalchemy.orm.session.Session (and in that case when do you used the scoped_session) ? – rloth Jan 29 '16 at 17:46
2

I think the example should be `with get_db_session() as session:` , or you can't not use session. – Mithril Nov 23 '16 at 01:12
For the sake of documentation, this is essentially what `flask_sqlalchemy` does. On application context teardown (`shutdown_session()`, from inside `SQLAlchemy.init_app`), the app will auto-commit the session if asked to, then it will remove it. Your session context could then have an additional parameter, `autocommit=False` and have `if autocommit: session.commit()` right before removing the session. – sleblanc Mar 16 '19 at 15:53

score 2 · Answer 2 · answered Apr 05 '11 at 07:59

2

You don't need to create a scoped session if you create new session for each request and each request is handled by single thread.

You have to call s.commit() to make pending objects persistent, i.e. to save changes into database.

You may also want to close session by calling s.close().

answered Apr 05 '11 at 07:59

Denis Otkidach

32,032
8
79
100

3

as far as I know, I *do* have to use a scoped session since SQLAlchemy doesn't permit me to have the same object in multiple open transactions (sessions): if I try I only get errors like "sqlalchemy.exc.InvalidRequestError: Object FOO is already attached to session '1234' (this is '5678')" – Luke404 Apr 11 '11 at 15:25
I see `close` being called in `commit` and `rollback`. – nurettin Sep 06 '19 at 16:24

What's the recommended scoped_session usage pattern in a multithreaded sqlalchemy webapp?

2 Answers2

Linked