Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLAlchemy: Single Table Inheritance, same column in childs

Im currently mapping a class hierarchy using the single table inheritance strategy (it is not possible for me to use joined). This hierarchy might look like this:

class Parent(Base):
    __tablename__ = 'mytable'
    __mapper_args__ = {
        'polymorphic_on' : type,
        'polymorphic_identity' : 'parent'
    }

    id = Column(Integer, primary_key = True)
    type = Column(String(32), nullable = False)

class Child1(Parent):
    __mapper_args__ = { 'polymorphic_identity' : 'child1' }

    property1 = Column(Integer)

class Child2(Parent):
    __mapper_args__ = { 'polymorphic_identity' : 'child2' }

    property1 = Column(Integer)

class Child3(Parent):
    __mapper_args__ = { 'polymorphic_identity' : 'child3' }

    other_property = Column(Integer)

Well the problem is that I want to have a property1 on both Child1 and Child2 but not on Child3. The current code above results in an error:

sqlalchemy.exc.ArgumentError: Column 'property1' on class <class
'__main__.Child2'>  conflicts with existing column 'mytable.property1'

I could of course add another layer to the inheritance hierarchy which Child1 and Child2 derive from and contains the property1 column, but Child1 and Child2 are hardly related to each other, though I want to reuse the same database column for both classes.

I already tried to add property1 = Child1.property1 to Child2 but that didnt worked (the instance values were not stored in the database for Child2)

Can anyone point out how to reuse a column already defined by another child class?

like image 352
Sebastian Hoffmann Avatar asked Jun 14 '13 15:06

Sebastian Hoffmann


1 Answers

Adapting directly from the documentation at Resolving Column Conflicts:

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

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'mytable'

    id = Column(Integer, primary_key = True)
    type = Column(String(32), nullable = False)
    __mapper_args__ = {
        'polymorphic_on' : type,
        'polymorphic_identity' : 'parent'
    }

class Child1(Parent):
    __mapper_args__ = { 'polymorphic_identity' : 'child1' }

    @declared_attr
    def property1(cls):
        return Parent.__table__.c.get('property1', Column(Integer))

class Child2(Parent):
    __mapper_args__ = { 'polymorphic_identity' : 'child2' }

    @declared_attr
    def property1(cls):
        return Parent.__table__.c.get('property1', Column(Integer))

class Child3(Parent):
    __mapper_args__ = { 'polymorphic_identity' : 'child3' }

    other_property = Column(Integer)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)
s.add_all([Child1(property1=1), Child2(property1=2), Child3(other_property=3)])
s.commit()

for p in s.query(Parent):
    if isinstance(p, (Child1, Child2)):
        print p.property1
    elif isinstance(p, Child3):
        print p.other_property
like image 78
zzzeek Avatar answered Oct 09 '22 10:10

zzzeek