Problem

I get unexpected inheritance instances from the multi-level polymorphic example below. I have attached self-contained test (see attached files) for anyone to re-create the problem.

class Entity(Base):
    __tablename__ = 'entity'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    entity_type = Column('type',String)

    __mapper_args__ = {
        'polymorphic_identity':'entity',
        'polymorphic_on':entity_type,
        }

class Asset(Entity):    
    __tablename__ = 'asset'

    id = Column('entity_id',Integer, ForeignKey('entity.id'), primary_key=True)
    asset_type = Column('asset_type',String)

    __mapper_args__ = {
        'polymorphic_identity':'asset',
        'polymorphic_on':asset_type,
        }

class Listed(Asset):
    __tablename__ = 'listed'

    id = Column('asset_entity_id',Integer, ForeignKey('asset.entity_id'), primary_key=True)
    ticker = Column(String)
    asset_class = Column(String)

    __mapper_args__ = {
        'polymorphic_identity':'listed',
        'polymorphic_on':asset_class,
        }

class Equity(Listed):

    __mapper_args__ = {
            'polymorphic_identity':'equity',
            }

Test code snippets

There are 5 instances of the same class in each test.

The unexpected behavior: * Note test 2 and test 3 the last output line. The class appears different from the previous 4 lines. * The 1st test I believe should deliver the type test.Equity. It does not. Instead it is test.Asset.

Test 1

I believe should deliver the type test.Equity. It does not. Instead it is test.Asset.

print 'Test 1'
entities = session.query(Entity)
for ent in entities:
    print '<Type:%s, entity_type:%s, asset_type:%s>' % (type(ent), ent.entity_type, ent.asset_type)

Output:

Test 1
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>

Test 2

I believe should deliver the type test.Equity. It does not. Instead it is test.Listed.
Note the last output line. The class appears different from the previous 4 lines.

print 'Test 2'
entities = session.query(Asset)
for ent in entities:
    print '<Type:%s, entity_type:%s, asset_type:%s>' % (type(ent), ent.entity_type, ent.asset_type)

Output:

<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>

Test 3

I believe should deliver the type test.Equity. It does except for the last output line which is test.Asset. The class appears different from the previous 4 lines.

print 'Test 3'
entities = session.query(Listed)
for ent in entities:
    print '<Type:%s, entity_type:%s, asset_type:%s>' % (type(ent), ent.entity_type, ent.asset_type)

Output

<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>

The self-contained test case

This is a very minimal case of the actual schema I am trying to map to Python.

Please re-construct the test case in a MySQL or other database. I have attached files that re-construct the test case for you.

The test files are:

test.py - The test code.
test_db_create.sql - Creates the simple test schema
test_result.txt - The odd output of the test.
table_listing.txt - A quick glance at the test schema tables.

The Platfrom

Python 2.7.6
SQLAlchemy 0.8.4
mysql Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.3
The test example uses the MySQL InnoDB engine.
Ubuntu 14.04.01

Feel free to contact me. This issue is critical to my project and I cannot re-design the dependent table schema.

Kind regards, Justin

Multi-level polymorphic inheritance broken instances.