UnboundExecutionError gotcha when working with scoped_session

[Steven Wei]

I ran into an interesting behavior when working with SQLAlchemy 0.6.2
and scoped_sessions: if you instantiate a Session before you call
Session.configure(bind=engine), none of the Sessions you create will
be properly bound to the engine you configured.

Here's a short sample snippet that illustrates the issue:

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

engine = create_engine('sqlite:///:memory:',echo=True)
Base = declarative_base()

class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)

Base.metadata.create_all(engine)

Session = scoped_session(sessionmaker())
s1 = Session()
Session.configure(bind=engine)
s2 = Session()

user = User(name='Bob')

s2.add(user)
s2.flush()

Running this produces the following error:

Traceback (most recent call last):
File "sqlalchemy_bind.py", line 23, in <module>
s2.flush()
File "/opt/virtualenv/lib/python2.6/site-packages/sqlalchemy/orm/
session.py", line 1346, in flush
self._flush(objects)
File "/opt/virtualenv/lib/python2.6/site-packages/sqlalchemy/orm/
session.py", line 1427, in _flush
flush_context.execute()
File "/opt/virtualenv/lib/python2.6/site-packages/sqlalchemy/orm/
unitofwork.py", line 299, in execute
rec.execute(self)
File "/opt/virtualenv/lib/python2.6/site-packages/sqlalchemy/orm/
unitofwork.py", line 443, in execute
uow
File "/opt/virtualenv/lib/python2.6/site-packages/sqlalchemy/orm/
mapper.py", line 1573, in _save_obj
connection = uowtransaction.transaction.connection(self)
File "/opt/virtualenv/lib/python2.6/site-packages/sqlalchemy/orm/
session.py", line 246, in connection
engine = self.session.get_bind(bindkey, **kwargs)
File "/opt/virtualenv/lib/python2.6/site-packages/sqlalchemy/orm/
session.py", line 868, in get_bind
', '.join(context)))
sqlalchemy.exc.UnboundExecutionError: Could not locate a bind
configured on mapper Mapper|User|users or this Session

Presumably this is because first session (before the bind was
configured) gets stored in the thread local registry, and gets
returned on all future Session() instantiations.

I've found two workarounds for this issue:

1) Don't instantiate Session objects until after you've called
Session.configure(bind=engine). In some cases this might not be
feasible, depending on the structure of your application.
2) Call Session.remove() after you call Session.configure(bind=engine)
to clear the thread local registry, so you'll get a fresh instance of
Session with the proper bind defined.

Hope this helps someone, it drove me crazy for a while trying to
figure it out.

[Michael Bayer]

i wouldn't call those "workarounds", those are the correct steps to take, as configure() is a proxy to the configure() of the underlying sessionmaker(). Docs seem to be a little light regarding configure(), the various docs are:

http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.orm.sessionmaker
http://www.sqlalchemy.org/docs/session.html?highlight=scoped_session#binding-session-to-an-engine.
http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.orm.scoping.ScopedSession.configure