Issue 1:
Migrated issue, originally created by Justin Solms (@justinsolms)

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

Attachments: table_listing.txt | test_db_create.sql | test_result.txt | test.py

Issue 2:
Migrated issue, originally created by Michael Bayer (@zzzeek)

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 init, as well as considering if polymorphic_map can be local to each sub-hierarchy.

"workaround" example below.

This is only tentatively on the 0.8 milestone. can move to 0.9.

"""
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': <Mapper at 0x1014d8890; Newspaper>,
# 'delivery': <Mapper at 0x1014dec90; NewspaperDelivery>,
# 'nonphysical_product': <Mapper at 0x1014d2350; NonPhysicalProduct>,
# 'physical_product': <Mapper at 0x1014d00d0; PhysicalProduct>}

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"