Tuesday, March 4, 2014

SQLAlchemy - DetachedInstanceError

I got this error when trying to retrieve all the database records in a table:

DetachedInstanceError: Instance <CoursesMerger at 0x37fa950> is not bound to a Session; attribute refresh operation cannot proceed

and this is how I query the database:

rs = session.query(MyTable).all()

To avoid this error, remember to set expire_on_commit = False when initialize the session:

from sqlalchemy import *
from sqlalchemy.orm import *

DB_HOST = 'localhost'
DBMS = 'mysql'
DB_PORT = '3306'
DB_NAME = 'mydatabase'
DB_USER = 'myuser'
DB_PWD = 'mypassword'

DB_ENGINE_URL = '%s://%s:%s@%s:%s/%s' % (DBMS, DB_USER, DB_PWD, DB_HOST, DB_PORT, DB_NAME)

class MyDatabaseConnector(object):
engine = None
Session = None

def __init__(self):

def connect(self):
if self.engine is None:
self.engine = create_engine(DB_ENGINE_URL, echo=False, \
pool_size = 100, pool_recycle=3600)

if self.Session is None:
self.Session = scoped_session(sessionmaker(bind=self.engine,expire_on_commit=False))

def get_session(self):
return self.Session()

def close(self):


[0] http://stackoverflow.com/questions/3039567/sqlalchemy-detachedinstanceerror-with-regular-attribute-not-a-relation
[1] http://docs.sqlalchemy.org/en/latest/orm/session.html