automatically casting a class to a subclass via polymorphic discriminator?

[Gerald Thibault]

I have a base class which has two subclasses specified via a polymorphic discriminator, and I'm wondering how to set things up so the returned results are instances of the subclass, when I create an instance of the base class and assign the relevant value to the discriminator column. Here's my example code:

import sys

from sqlalchemy import *

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm.attributes import instance_dict

from sqlalchemy.orm import relation, backref, class_mapper, create_session

e = create_engine('sqlite:////tmp/foo.db', echo=True)

Base = declarative_base(bind=e)

class Test(Base):

    __tablename__ = 'test'

    id = Column(Integer, primary_key=True)

    type = Column(String(12))

    name = Column(String(50))

    __mapper_args__ = {

        'polymorphic_identity': 'base',

        'polymorphic_on': type,

        'with_polymorphic': '*',

    }

class TestOne(Test):

    __tablename__ = 'test1'

    id = Column(Integer, ForeignKey('test.id'), primary_key=True)

    value1 = Column(String(16))

    __mapper_args__ = {

        'polymorphic_identity': 'one',

        }

class TestTwo(Test):

    __tablename__ = 'test2'

    id = Column(Integer, ForeignKey('test.id'), primary_key=True)

    value2 = Column(String(16))

    __mapper_args__ = {

        'polymorphic_identity': 'two',

        }

if __name__ == '__main__':

    Base.metadata.drop_all()

    Base.metadata.create_all()

    session = create_session(bind=e, autocommit=False)    

    

    test1 = Test(type='one', name='a test')

    session.add(test1)

    print test1

This returns an instance of Test, not Test1, despite the discriminator indicating that it should be cast as Test1.

Is it possible to handle this in a way which will yield an instance of Test1?

Also, if i create test1, and then do session.expunge_all, then immediately requery for session.query(Site).get(1), the returned instance is of type Test1, but trying to set the value for value1 yields 'Can't update table using NULL for primary key value'. Is there a way to fix this, so the necessary table row is created with the same primary key, instead of leaving it blank? It seems that all the info needed to handle the auto-creation of the sub-table is present, but it just fails.

Thanks

[Michael Bayer]

On Nov 13, 2012, at 7:59 PM, Gerald Thibault wrote:

> I have a base class which has two subclasses specified via a polymorphic discriminator, and I'm wondering how to set things up so the returned results are instances of the subclass, when I create an instance of the base class and assign the relevant value to the discriminator column. Here's my example code:

that's not how it usually works. You'd create instances of the subclasses, and leave the discriminator column alone; SQLAlchemy assigns that.

>
> test1 = Test(type='one', name='a test')
> session.add(test1)
> print test1
>
> This returns an instance of Test, not Test1, despite the discriminator indicating that it should be cast as Test1.

OK here, you've said "test = Test()". That is now the Python version of the class, SQLAlchemy has nothing to do with changing __class__ on your Test object or anything like that. That object is going to stay just like it is, except for it getting a new primary key value. SQLAlchemy's job here is to persist your row in the database and return it back for you later. Here, the session hasn't even flushed any data, and you can see in your echo there's not any INSERT. If you were to allow the row to go out to the database and come back, you'd see your class:

test1 = Test(type='one', name='a test')
session.add(test1)

session.commit() # flush + commit transaction
session.close() # expunge the "Test()" object totally so we get a new one back

print session.query(Test).one()

above, it's important that "test1" is removed from the Session totally, so that when we query again for that row, the Test object isn't returned; otherwise, it still points to that row.

However, the way it's supposed to work is, just use the classes as designed (note also, we use Session, or sessionmaker() - not create_session() which is a legacy function used for internal testing - see http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html for recommended usage patterns):

from sqlalchemy.orm import Session
session = Session(e)

test1 = TestOne(name='a test')
session.add(test1)
print session.query(Test).one() # autoflushes, then gets our TestOne back

> Also, if i create test1, and then do session.expunge_all, then immediately requery for session.query(Site).get(1), the returned instance is of type Test1, but trying to set the value for value1 yields 'Can't update table using NULL for primary key value'.

works for me:


test1 = TestOne(name='a test')
session.add(test1)
print session.query(Test).one()
session.expunge_all()

t = session.query(Test).one()
t.value1 = "value1"
session.commit()

output:

BEGIN (implicit)
INSERT INTO test (type, name) VALUES (?, ?)
('one', 'a test')
INSERT INTO test1 (id, value1) VALUES (?, ?)
(1, None)
SELECT test.id AS test_id, test.type AS test_type, test.name AS test_name, test1.id AS test1_id, test1.value1 AS test1_value1, test2.id AS test2_id, test2.value2 AS test2_value2
FROM test LEFT OUTER JOIN test1 ON test.id = test1.id LEFT OUTER JOIN test2 ON test.id = test2.id
()
<__main__.TestOne object at 0x1014d7910>
SELECT test.id AS test_id, test.type AS test_type, test.name AS test_name, test1.id AS test1_id, test1.value1 AS test1_value1, test2.id AS test2_id, test2.value2 AS test2_value2
FROM test LEFT OUTER JOIN test1 ON test.id = test1.id LEFT OUTER JOIN test2 ON test.id = test2.id
()
UPDATE test1 SET value1=? WHERE test1.id = ?
('value1', 1)
COMMIT