<p dir="auto"><strong>Migrated issue, originally created by Justin Solms (<a href="https://github.com/justinsolms">@justinsolms</a>)</strong></p>
<h1 dir="auto">Problem</h1>
<p dir="auto">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.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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',
            }
"><pre class="notranslate"><code class="notranslate">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',
            }

</code></pre></div>
<h1 dir="auto">Test code snippets</h1>
<p dir="auto">There are 5 instances of the same class in each test.</p>
<p dir="auto">The unexpected behavior:</p>
<ul dir="auto">
<li>Note test 2 and test 3 the last output line. The class appears different from the previous 4 lines.</li>
<li>The 1st test I believe should deliver the type <code class="notranslate">test.Equity</code>. It does not. Instead it is <code class="notranslate">test.Asset</code>.</li>
</ul>
<h2 dir="auto">Test 1</h2>
<ul dir="auto">
<li>I believe should deliver the type <code class="notranslate">test.Equity</code>. It does not. Instead it is <code class="notranslate">test.Asset</code>.</li>
</ul>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="print 'Test 1'
entities = session.query(Entity)
for ent in entities:
    print '&lt;Type:%s, entity_type:%s, asset_type:%s&gt;' % (type(ent), ent.entity_type, ent.asset_type)"><pre class="notranslate"><code class="notranslate">print 'Test 1'
entities = session.query(Entity)
for ent in entities:
    print '&lt;Type:%s, entity_type:%s, asset_type:%s&gt;' % (type(ent), ent.entity_type, ent.asset_type)
</code></pre></div>
<p dir="auto">Output:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Test 1
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;"><pre class="notranslate"><code class="notranslate">Test 1
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;
</code></pre></div>
<h2 dir="auto">Test 2</h2>
<ul dir="auto">
<li>I believe should deliver the type <code class="notranslate">test.Equity</code>. It does not. Instead it is <code class="notranslate">test.Listed</code>.</li>
<li>Note the last output line. The class appears different from the previous 4 lines.</li>
</ul>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="print 'Test 2'
entities = session.query(Asset)
for ent in entities:
    print '&lt;Type:%s, entity_type:%s, asset_type:%s&gt;' % (type(ent), ent.entity_type, ent.asset_type)"><pre class="notranslate"><code class="notranslate">print 'Test 2'
entities = session.query(Asset)
for ent in entities:
    print '&lt;Type:%s, entity_type:%s, asset_type:%s&gt;' % (type(ent), ent.entity_type, ent.asset_type)
</code></pre></div>
<p dir="auto">Output:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&lt;Type:&lt;class 'test.Listed'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Listed'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Listed'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Listed'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;"><pre class="notranslate"><code class="notranslate">&lt;Type:&lt;class 'test.Listed'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Listed'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Listed'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Listed'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;
</code></pre></div>
<h2 dir="auto">Test 3</h2>
<ul dir="auto">
<li>I believe should deliver the type <code class="notranslate">test.Equity</code>. It does except for the last output line which is <code class="notranslate">test.Asset</code>. The class appears different from the previous 4 lines.</li>
</ul>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="print 'Test 3'
entities = session.query(Listed)
for ent in entities:
    print '&lt;Type:%s, entity_type:%s, asset_type:%s&gt;' % (type(ent), ent.entity_type, ent.asset_type)
"><pre class="notranslate"><code class="notranslate">print 'Test 3'
entities = session.query(Listed)
for ent in entities:
    print '&lt;Type:%s, entity_type:%s, asset_type:%s&gt;' % (type(ent), ent.entity_type, ent.asset_type)

</code></pre></div>
<p dir="auto">Output</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&lt;Type:&lt;class 'test.Equity'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Equity'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Equity'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Equity'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;"><pre class="notranslate"><code class="notranslate">&lt;Type:&lt;class 'test.Equity'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Equity'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Equity'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Equity'&gt;, entity_type:asset, asset_type:listed&gt;
&lt;Type:&lt;class 'test.Asset'&gt;, entity_type:asset, asset_type:listed&gt;
</code></pre></div>
<h1 dir="auto">The self-contained test case</h1>
<p dir="auto">This is a very minimal case of the actual schema I am trying to map to Python.</p>
<p dir="auto">Please re-construct the test case in a MySQL or other database. I have attached files that re-construct the test case for you.</p>
<p dir="auto">The test files are:</p>
<ol dir="auto">
<li><code class="notranslate">test.py</code> - The test code.</li>
<li><code class="notranslate">test_db_create.sql</code> - Creates the simple test schema</li>
<li><code class="notranslate">test_result.txt</code> - The odd output of the test.</li>
<li><code class="notranslate">table_listing.txt</code> - A quick glance at the test schema tables.</li>
</ol>
<h1 dir="auto">The Platfrom</h1>
<ul dir="auto">
<li>Python 2.7.6</li>
<li>SQLAlchemy  0.8.4</li>
<li>mysql  Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.3</li>
<li>The test example uses the MySQL InnoDB engine.</li>
<li>Ubuntu 14.04.01</li>
</ul>
<hr>
<p dir="auto">Feel free to contact me. This issue is critical to my project and I cannot re-design the dependent table schema.</p>
<p dir="auto">Kind regards,<br>
Justin</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/3214/table_listing.txt">table_listing.txt</a> | <a href="../wiki/imported_issue_attachments/3214/test_db_create.sql">test_db_create.sql</a> | <a href="../wiki/imported_issue_attachments/3214/test_result.txt">test_result.txt</a> | <a href="../wiki/imported_issue_attachments/3214/test.py">test.py</a></p>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">have the polymorphic_on check during loading continue to check on the type returned so that polymorphic loads can cascade.   Other complexities here regard getting the multiple discriminators assigned during <strong>init</strong>, as well as considering if polymorphic_map can be local to each sub-hierarchy.</p>
<p dir="auto">"workaround" example below.</p>
<p dir="auto">This is only tentatively on the 0.8 milestone.   can move to 0.9.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&quot;&quot;&quot;
mixed single and joined table inheritance.
&quot;&quot;&quot;

from sqlalchemy import *
from sqlalchemy import types
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base,  declared_attr
from sqlalchemy import event

Base = declarative_base()

class Product(Base):
    __tablename__ = 'products'
    id = Column(types.Integer, primary_key=True)
    discriminator = Column('product_type', types.String(50), nullable=False)

    _discriminator = &quot;discriminator&quot;

    def price_history(self):
        return [PhysicalProduct(Product):
    p_discr = Column(types.String(50))

    _discriminator = &quot;p_discr&quot;

    @declared_attr
    def __mapper_args__(cls):
        return {'polymorphic_identity': 'physical_product'}

    def inventory(self):
        return &quot;computed inventory&quot;

class NonPhysicalProduct(Product):
    np_discr = Column(types.String(50))

    _discriminator = &quot;np_discr&quot;

    @declared_attr
    def __mapper_args__(cls):
        return {'polymorphic_identity': 'nonphysical_product'}

    def somefunc(self):
        return &quot;someval&quot;

# set polymorphic on as a coalesce of those three
# columns.  It's after the fact beacuse p_discr and np_discr
# are defined after Product, but if you move them up then
# this can be inline inside of Product.__mapper_args__.
# this would improve loads too as it appears the p_discr/np_discr columns
# aren't loaded directly when you query for Product
for mp in Product.__mapper__.self_and_descendants:
    mp._set_polymorphic_on(
            func.coalesce(
                    Product.__table__.c.p_discr,
                    Product.__table__.c.np_discr,
                    Product.__table__.c.product_type
            ))

# build our own system of assigning polymorphic identities
# to instances; use the 'init' event.
# Add a &quot;print&quot; for the &quot;identity&quot; dict to see what it's doing.
@event.listens_for(Product, &quot;init&quot;, propagate=True)
def init(target, args, kwargs):
    identity = {}
    for cls, supercls in zip(type(target).__mro__, type(target).__mro__[1:](]

class)):
        if not hasattr(supercls, '_discriminator'):
            break
        discriminator_attr = supercls._discriminator
        poly_identity = cls.__mapper__.polymorphic_identity
        identity.setdefault(discriminator_attr, poly_identity)
    for key in identity:
        setattr(target, key, identity[key](key))


class Newspaper(PhysicalProduct):
    __tablename__ = 'newspapers'
    __mapper_args__ = {'polymorphic_identity': 'newspaper'}

    id = Column(types.Integer,
                ForeignKey('products.id'),
                primary_key=True
                )
    title = Column(types.String(50))

    def __init__(self, title):
        self.title = title


class NewspaperDelivery(NonPhysicalProduct):
    __tablename__ = 'deliveries'
    __mapper_args__ = {'polymorphic_identity': 'delivery'}

    id = Column(types.Integer,
                ForeignKey('products.id'),
                primary_key=True
                )
    destination = Column(types.String(50))

    def __init__(self, destination):
        self.destination = destination


# note here how the polymorphic map works out:
print Product.__mapper__.polymorphic_map
# {'newspaper': &lt;Mapper at 0x1014d8890; Newspaper&gt;,
# 'delivery': &lt;Mapper at 0x1014dec90; NewspaperDelivery&gt;,
# 'nonphysical_product': &lt;Mapper at 0x1014d2350; NonPhysicalProduct&gt;,
# 'physical_product': &lt;Mapper at 0x1014d00d0; PhysicalProduct&gt;}


e = create_engine('sqlite:///:memory:', echo='debug')
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

session = Session(e, autoflush=True, autocommit=False)

session.add_all([   Newspaper(title=&quot;Financial Times&quot;),
    NewspaperDelivery(destination=&quot;__somewhere__&quot;),
    PhysicalProduct(),
    NonPhysicalProduct()
](
))

session.commit()

# the important part - that a row only known as Product can
# interpret as a specific subclass
assert [   type(c) for c in session.query(Product).order_by(Product.id)
](
) == [NewspaperDelivery, PhysicalProduct, NonPhysicalProduct](Newspaper,)

# test sub-table load.  The load for &quot;title&quot; apparently emits a JOIN still because
# in order to refresh the subclass of &quot;Product&quot; it also wants to get
# at p_discr.
np = session.query(Product).filter_by(id=1).first()
assert np.title == &quot;Financial Times&quot;

session.close()

# in this version, it emits two separate, single table SELECT statements,
# since the first query loads the full set of columns for PhysicalProduct.
np = session.query(PhysicalProduct).filter_by(id=1).first()
assert np.title == &quot;Financial Times&quot;"><pre class="notranslate"><code class="notranslate">"""
mixed single and joined table inheritance.
"""

from sqlalchemy import *
from sqlalchemy import types
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base,  declared_attr
from sqlalchemy import event

Base = declarative_base()

class Product(Base):
    __tablename__ = 'products'
    id = Column(types.Integer, primary_key=True)
    discriminator = Column('product_type', types.String(50), nullable=False)

    _discriminator = "discriminator"

    def price_history(self):
        return [PhysicalProduct(Product):
    p_discr = Column(types.String(50))

    _discriminator = "p_discr"

    @declared_attr
    def __mapper_args__(cls):
        return {'polymorphic_identity': 'physical_product'}

    def inventory(self):
        return "computed inventory"

class NonPhysicalProduct(Product):
    np_discr = Column(types.String(50))

    _discriminator = "np_discr"

    @declared_attr
    def __mapper_args__(cls):
        return {'polymorphic_identity': 'nonphysical_product'}

    def somefunc(self):
        return "someval"

# set polymorphic on as a coalesce of those three
# columns.  It's after the fact beacuse p_discr and np_discr
# are defined after Product, but if you move them up then
# this can be inline inside of Product.__mapper_args__.
# this would improve loads too as it appears the p_discr/np_discr columns
# aren't loaded directly when you query for Product
for mp in Product.__mapper__.self_and_descendants:
    mp._set_polymorphic_on(
            func.coalesce(
                    Product.__table__.c.p_discr,
                    Product.__table__.c.np_discr,
                    Product.__table__.c.product_type
            ))

# build our own system of assigning polymorphic identities
# to instances; use the 'init' event.
# Add a "print" for the "identity" dict to see what it's doing.
@event.listens_for(Product, "init", propagate=True)
def init(target, args, kwargs):
    identity = {}
    for cls, supercls in zip(type(target).__mro__, type(target).__mro__[1:](]

class)):
        if not hasattr(supercls, '_discriminator'):
            break
        discriminator_attr = supercls._discriminator
        poly_identity = cls.__mapper__.polymorphic_identity
        identity.setdefault(discriminator_attr, poly_identity)
    for key in identity:
        setattr(target, key, identity[key](key))


class Newspaper(PhysicalProduct):
    __tablename__ = 'newspapers'
    __mapper_args__ = {'polymorphic_identity': 'newspaper'}

    id = Column(types.Integer,
                ForeignKey('products.id'),
                primary_key=True
                )
    title = Column(types.String(50))

    def __init__(self, title):
        self.title = title


class NewspaperDelivery(NonPhysicalProduct):
    __tablename__ = 'deliveries'
    __mapper_args__ = {'polymorphic_identity': 'delivery'}

    id = Column(types.Integer,
                ForeignKey('products.id'),
                primary_key=True
                )
    destination = Column(types.String(50))

    def __init__(self, destination):
        self.destination = destination


# note here how the polymorphic map works out:
print Product.__mapper__.polymorphic_map
# {'newspaper': &lt;Mapper at 0x1014d8890; Newspaper&gt;,
# 'delivery': &lt;Mapper at 0x1014dec90; NewspaperDelivery&gt;,
# 'nonphysical_product': &lt;Mapper at 0x1014d2350; NonPhysicalProduct&gt;,
# 'physical_product': &lt;Mapper at 0x1014d00d0; PhysicalProduct&gt;}


e = create_engine('sqlite:///:memory:', echo='debug')
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

session = Session(e, autoflush=True, autocommit=False)

session.add_all([   Newspaper(title="Financial Times"),
    NewspaperDelivery(destination="__somewhere__"),
    PhysicalProduct(),
    NonPhysicalProduct()
](
))

session.commit()

# the important part - that a row only known as Product can
# interpret as a specific subclass
assert [   type(c) for c in session.query(Product).order_by(Product.id)
](
) == [NewspaperDelivery, PhysicalProduct, NonPhysicalProduct](Newspaper,)

# test sub-table load.  The load for "title" apparently emits a JOIN still because
# in order to refresh the subclass of "Product" it also wants to get
# at p_discr.
np = session.query(Product).filter_by(id=1).first()
assert np.title == "Financial Times"

session.close()

# in this version, it emits two separate, single table SELECT statements,
# since the first query loads the full set of columns for PhysicalProduct.
np = session.query(PhysicalProduct).filter_by(id=1).first()
assert np.title == "Financial Times"
</code></pre></div>