from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'USER'

    id = Column(Integer, Sequence('USER_SEQ'), primary_key=True, autoincrement=True)
    name = Column(String(30))
    age = Column(Integer)
    status = Column(String(30))

engine = create_engine(str(db_url), encoding=b'utf-8', echo=echo, convert_unicode=True)
Session = sessionmaker(bind=engine)
session = Session()

user = User(name='John', age=30)

print user.name


Traceback (most recent call last):
  File "E:\WorkCopy\test\test.py", line 21, in test_something
    print user.name
  File "C:\Python27\lib\site-packages\sqlalchemy\orm\attributes.py", line 237, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "C:\Python27\lib\site-packages\sqlalchemy\orm\attributes.py", line 573, in get
    value = state._load_expired(state, passive)
  File "C:\Python27\lib\site-packages\sqlalchemy\orm\state.py", line 480, in _load_expired
    self.manager.deferred_scalar_loader(self, toload)
  File "C:\Python27\lib\site-packages\sqlalchemy\orm\loading.py", line 610, in load_scalar_attributes
DetachedInstanceError: Instance <User at 0x32768d0> is not bound to a Session; attribute refresh operation cannot proceed

后记:貌似 session.expunge() 就是做这个的。

user = User(name='John', age=30)



print user.name

使用 session.refresh(user) 是为了读取自增字段值(如果有的话)到 user 对象。

链接文章: https://segmentfault.com/q/1010000004530775

在使用 SQLAlchemy 的过程中,有时会出现下列错误:

  • Parent instance '<User at 0x2b45b53509d0>' is not bound to a Session; lazy load operation of attribute cannot proceed
  • Instance '<User at 0x2b45b53509d0>' is not bound to a Session; attribute refresh operation cannot proceed

出现以上错误的原因是因为:session 已经被提交,导致操作的 model 对象已经不在当前 session 中了。 解决的办法就是:把对象重新加入到当前 session 中:

def foo(user):
    # do something...

user = session.query(User).filter_by(name='Jim').first()
print user in session  # False
print user.name  # DetachedInstanceError: Instance <User at 0x2b45b53509d0> is not bound to a Session

user = session.merge(user)
print user in session  # True
print user.name  # Jim
print user.name  # Eric

参考资料 https://flask-webtest.readthedocs.org/en/latest/#using-flask-webtest-with-flask-sqlalchemy


I found the root cause while trying to narrow down the code that caused the exception. I placed the same attribute access code at different places after session close and found that it definitely doesn't cause any issue immediately after the close of query session. It turns out the problem starts appearing after closing a fresh session that is opened to update the object. Once I understood that the state of the object is unusable after a session close, I was able to find this thread that discussed this same issue. Two solutions that come out of the thread are:

  • Keep a session open (which is obvious)
  • Specify expire_on_commit=False to sessionmaker().

The 3rd option is to manually set expire_on_commit to False on the session once it is created, something like: session.expire_on_commit = False. I verified that this solves my issue.

Would session.expunge_all() help at all? 
    How does that help? It seems to actually clear the session of all objects loaded. 
    session.expire_on_commit = False is bad, since if the database has converted the values to something, you'll never know it 
    session.flush() should do what you're after