sqlalchemy: sessionmaker context manager is not fully compatible with scoped_session

Describe the bug

When using scoped_session() to create Session objects (e.g., for a threaded app), using the combined context manager with Session.begin() as session results in an exception.

To Reproduce

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, scoped_session, sessionmaker


Base = declarative_base()


class Test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    value = Column(String)


engine = create_engine("sqlite://", future=True)
Base.metadata.create_all(engine)

Session = scoped_session(sessionmaker(bind=engine, future=True))
# Session = sessionmaker(bind=engine, future=True)

with Session.begin() as session:
    session.add(Test(value="foo"))

# with Session() as session:
#     with session.begin():
#         session.add(Test(value="foo"))

Error

Traceback (most recent call last):
  File "<stdin>", line 4, in <module>
AttributeError: 'SessionTransaction' object has no attribute 'add'

The same type of exception occurs regardless of the method called for session (e.g., .get(), .execute())

Workarounds

Replace:

with Session.begin() as session:

with:

with Session() as session:
    with session.begin():

Or (if thread support is not required) replace:

Session = scoped_session(sessionmaker(bind=engine, future=True))

with:

Session = sessionmaker(bind=engine, future=True)

Versions

OS: Ubuntu 21.04 (5.11.0-17-generic x86_64)
Python: 3.9.4
SQLAlchemy: 1.4.15
Database: SQLite 3.34.1
DBAPI: pysqlite

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 15 (13 by maintainers)

Commits related to this issue

work around for the sessionmaker bug in https://github.com/sqlalchemy/sqlalchemy/issues/6519 — committed to yuyiguo/rucio by yuyiguo 2 years ago
upgrade to SQLAlchemy 2.0 step 1 and code tidying up. update sesstion due to the scoped_session vs context manager problem in https://github.com/sqlalchemy/sqlalchemy/issues/6519 update sesstion due ... — committed to yuyiguo/rucio by yuyiguo 2 years ago
upgrade to SQLAlchemy 2.0 step 1 and code tidying up. update sesstion due to the scoped_session vs context manager problem in https://github.com/sqlalchemy/sqlalchemy/issues/6519 update sesstion due ... — committed to yuyiguo/rucio by yuyiguo 2 years ago
update db/sqla to sqlAlchemy 2; session manage using future=true; 2.0 sql and code reformat using VSC. Deprecated method replaces the Engine.has_table() method with inspector.has_table(). scoped_sess... — committed to yuyiguo/rucio by yuyiguo 2 years ago
bmrt cache: use new SQLAlchemy session with every refresh I don't take the time to explain all details here. I have worked on this for quite a while, was a little lost between SQLAlchemy docs and 1.4... — committed to conbench/conbench by jgehrcke a year ago
SQLAlchemy database session isolation: per-request and per-BMRT cache update (#1301) * add dbsession.py for request-local SQLAlchemy session handling * db.py: one session obj for out-of-request-c... — committed to conbench/conbench by jgehrcke a year ago

Most upvoted comments

the scoped session is a means of sharing a single Session globally without needing to pass it around, and the context manager pattern is one of how to frame out the beginning and ending of the usage of a particular Session and /or a specific transaction on that Session.

The way that these patterns might be used together would be that a single enclosing function would be responsible for the"open/close" phase, but then other functions would access this session using the global scope. That’s a valid pattern, and looks like:

Session = scoped_session(sessionmaker(engine))

def worker_one():
     Session.execute(text("select 1"))

def worker_two():
     Session.execute(text("select 2"))

def main():
    with Session.begin():
        worker_one()
        worker_two()

main()

that’s a completely valid pattern to use.

zzzeek on May 13, 2022

I think any change would be best as a next release, so that in can be in the migration notes etc.

Also changed from bug to use case, since the current behaviour is as documented, so there is no bug.

however that said it is nice to get at the per-thread session

well you already have it though. With the normal session the code is:

session = my_session_maker()
with session.begin():
  session.add(...)
  ...

with a scoped session you have two options:

# same as session maker
session = my_scoped_session()
with session.begin():
  session.add(...)
  ...
# use as proxy
with my_scoped_session.begin():
  my_scoped_session.add(...)
  ...

CaselIT on May 21, 2021

i had a thought that why would one even be using scoped_session() if they are using context managers for transactions. The pattern of “some_sessionmaker.begin()” means something completely different than “some_scoped_session.begin()”. the former is a creational pattern that gives you a new Session() right there. The latter is assumed there is some session attached to a thread local that’s already in process and you’re using it as a proxy, it would not be generally safe to call “begin()” on it as there is likely already a transaction in progress on it.

really the big pattern here is that scoped_session() is a legacy “thread local” thing and it’s not really the way to do things going forward as context/thread locals have proven to be confusing to people as opposed to explicitly passing a context/session into methods that need it. At most the code that uses a scoped_session() it would get the actual Session from it and assume it’s already in a transactional state.

zzzeek on May 22, 2021