scoped_session(sessionmaker()) or plain sessionmaker() in sqlalchemy?

user_820917 0
Viewed 41027

I am using SQlAlchemy in my web project. What should I use - scoped_session(sessionmaker()) or plain sessionmaker() - and why? Or should I use something else? 

## model.py
from sqlalchemy import *
from sqlalchemy.orm import *

engine = create_engine('mysql://dbUser:dbPassword@dbServer:dbPort/dbName',
pool_recycle=3600, echo=False)
metadata = MetaData(engine)
Session = scoped_session(sessionmaker())
Session.configure(bind=engine)
user = Table('user', metadata, autoload=True)

class User(object):
pass

usermapper = mapper(User, user)

## some other python file called abc.py
from models import *

def getalluser():
   session = Session()  
   session.query(User).all()
   session.flush()
   session.close()

## onemore file defg.py
from models import *

def updateuser():
   session = Session()  
   session.query(User).filter(User.user_id == '4').update({User.user_lname: 'villkoo'})
   session.commit()
   session.flush()
   session.close()

I create a session = Session() object for each request and I close it. Am I doing the right thing or is there a better way to do it?

flask-sqlalchemy sqlalchemy orm django python
5 Answers
user_18895740

Don't use scoped_session and don't use Flask-SQLAlchemy.

Just use Session = sessionmaker() held in a singleton/service class, and use session = Session() on every HTTP request to guarantee that a fresh connection is provided.

Thread Local storage is clumsy and involves holding state which doesn't play nicely with different web-server threading models. Better to stay stateless. See for example SqlAlchemy's documentation here mentioning not to forget to call .remove() if you are using scoped_session. Will anyone remember to do that?

Below is an excerpt from https://docs.sqlalchemy.org/en/14/orm/contextual.html#using-thread-local-scope-with-web-applications:

Using the above flow, the process of integrating the Session with the web application has exactly two requirements:

  • Create a single scoped_session registry when the web application first starts, ensuring that this object is accessible by the rest of the application.

  • Ensure that scoped_session.remove() is called when the web request ends, usually by integrating with the web framework’s event system to establish an “on request end” event.

user_3679880
Related