SQLAlchemy的ORM方式将数据库中的记录映射成了我们定义好的模型类，但是带来一个问题是，这些类对象的实例只在数据库会话（session）的生命期内有效，假如我将数据库会话关闭了，再访问数据表类的对象就会报错。
如下面这段简单的示例代码：

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)
session.add(user)
session.commit()
session.close()

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
    (state_str(state)))
DetachedInstanceError: Instance <User at 0x32768d0> is not bound to a Session; attribute refresh operation cannot proceed

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

user = User(name='John', age=30)
session.add(user)
session.commit()

session.refresh(user)
session.expunge(user)

session.close()

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...
    session.commit()

user = session.query(User).filter_by(name='Jim').first()
foo(user)
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
session.refresh(user)
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.

•  
  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 

  https://stackoverflow.com/questions/3039567/sqlalchemy-detachedinstanceerror-with-regular-attribute-not-a-relation