SQLAlchemy with single table inheritance exception error

Question

I had a problem with inheriting a single python SQLAlchemy table.

Model:

class User(Base): __tablename__ = 'user' userID = Column(String(64), primary_key=True) name = Column(String(64)) type = Column('type', String(50)) __mapper_args__ = {'polymorphic_on': type} class PasswordUser(User): __mapper_args__ = {'polymorphic_identity': 'puser'} password = Column(String(64)) def validatePassword(self, password): return (self.password == password) 

In userManger.py, I have the following:

 def userGet(userID): with DBStore.Session(db) as sess: user = sess.query(User).filter(User.userID==userID).one() sess.expunge(user) return user 

In the main testing method:

 myUser = userManager.userGet('123') myUser.validatePassword("password321') 

This causes an error:

sqlalchemy.orm.exc.DetachedInstanceError: instance is not tied to a session; attribute update operation cannot continue

I checked that myUser is of type "PasswordUser" and it calls the correct validatePassword method.

Outsider is that when the code is executed slowly (PyDev), it works without errors.

It also works if my userGet method executes sess.query (PasswordUser). But I want this method to be general so that it can return any type of "User".

Any ideas?

Answer 1

The problem is that when querying only for User , only attributes / columns for the User class (base) are requested from the database. To load other columns (e.g. password ) for subclasses, you need to tell query to do this. You can do this using with_polymorphic , in which case your code might look like this:

 def userGet(userID): with DBStore.Session(db) as sess: user = sess.query(User).with_polymorphic('*').filter(User.userID==userID).one() sess.expunge(user) return user 

If you do not, sqlalchemy will try to automatically load the missing attribute (in your case password ) with a session, and therefore it complains that it cannot work with a single object.