Reading attributes of a detached sqlalchemy object raises DetachedInstanceError

Viewed 1010

I am using short lived sqlalchemy sessions to add objects to a sqlite database. A few objects outlive the session in a readonly, detached state. Unfortunately, accessing attributes of a detached object throws an exception if the session has been closed. Here is a simplified code example

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

Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

engine = create_engine('sqlite:///foo.db', echo=False)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

f = Foo(name='foo1')
print('state=transient : name=', f.name)
session.add(f)
print('state=pending : name=', f.name)
session.commit()
session.close()
print('state=detached : name=', f.name)
# output
state=transient : name= foo1
state=pending : name= foo1
Traceback (most recent call last):
  File "scratch_46.py", line 23, in <module>
    print('state=detached : name=', f.name)
  File "lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 282, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 705, in get
    value = state._load_expired(state, passive)
  File "lib/python3.7/site-packages/sqlalchemy/orm/state.py", line 660, in _load_expired
    self.manager.deferred_scalar_loader(self, toload)
  File "lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 913, in load_scalar_attributes
    "attribute refresh operation cannot proceed" % (state_str(state))
sqlalchemy.orm.exc.DetachedInstanceError: Instance <Foo at 0x7f266c758ac8> is not bound to a Session; attribute refresh operation cannot proceed (Background on this error at: http://sqlalche.me/e/bhk3)

Oddly enough, the error is not thrown if I do either of these

  • Read the name attribute between the commit and close calls while the object is in the persistent state
  • Expunge the object from the session and leave the session open. Still detached state, but I can access the attribute.

Should I be able to read attributes of a detached object? Is there a way to have objects safely outlive transient sessions? I read the suggested link in the output, but it talks mostly about parent/child relationships.

I created a an issue on the sqlalchemy repo because I thought this was a bug at first, but now I am not so certain.

1 Answers

Dug into this more and found that I was getting bit by the default value of expire_on_commit being true on the session. When that is on, the commit call expires the object, which forced sqlalchemy to reload it the next time someone reads an attribute. If that is delayed until after session close, then the attributes can't be fetched.

I don't really want this auto expiration, since I know my objects are in a good state and are readonly after being saved. Setting expire_on_commit to false in the session maker resolves the issue.

Related