From Elixir to SqlAlchemy Declarative, Polymorphic Nodes with Child Inheritance

Question

I have already finished a good bit of my python/Elixir interface on my existing database. I am now considering to drop Elixir and move everything into pure SQLAlchemy, most likely wanting to use Declarative methods.

I am not sure where to even start with this particular inheritance relationship. I don't think sqlalchemy performs inheritance in this manner (or as "magically"), and I am a bit confused how the same would look in sqlalchemy:

This is a polymorphic multi-table join, with each class mapped to its own database table. When finished, another class (not included here) will have a OneToMany with 'Comp'. The Comp subclasses have a Primary Key that is a Foreign key to Comp.id.

class Comp(Entity):
    using_options(inheritance='multi')
    parent = ManyToOne('Assembly', onupdate='cascade', ondelete='set null')
    quantity = Field(Numeric(4), default=1)

    def __repr__(self):
        return "<Comp>"

## If not familiar with Elixir, each of the following "refid" point to a different
## table depending on its class. This is the primary need for polymorphism.    

class CompAssm(Comp):
    using_options(inheritance='multi')
    refid = ManyToOne('Assembly', onupdate='cascade', ondelete='set null')

    def __repr__(self):
        return "<CompAssm>"

class CompItem(Comp):
    using_options(inheritance='multi')
    refid = ManyToOne('Item', onupdate='cascade')

    def __repr__(self):
        return "<CompItem>"

class CompLabor(Comp):
    using_options(inheritance='multi')
    refid = ManyToOne('Labor', onupdate='cascade')

    def __repr__(self):
        return "<CompLabor>"

user2097818 · Answer 1 · 2013-04-28T21:03:43.137

I think this is the general direction, but may still need tweaking.

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Assembly(Base):
    __tablename__ = 'assembly'
    assm_id = Column(Integer, primary_key=True)
    children = relationship('Comp')    

    ### other assembly stuff

class Comp(Base):
    __tablename__ = 'components'
    id = Column(Integer, primary_key=True)
    comp_type = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': comp_type}
    parent = Column(Integer, ForeignKey('assembly.assm_id'))
    quantity = Column(Integer)

class CompAssm(Comp):
    __tablename__ = 'compassm'
    __mapper_args__ = {'polymorphic_identity': 'compassm'}
    id = Column(Integer, ForeignKey('components.id'), primary_key=True)
    refid = Column(Integer, ForeignKey('assembly.assm_id'))

 class CompItem(Comp):
    __tablename__ = 'compitem'
    __mapper_args__ = {'polymorphic_identity': 'compitem'}
    id = Column(Integer, ForeignKey('components.id'), primary_key=True)
    refid = Column(Integer, ForeignKey('items.id'))

class CompLabor(Comp):
    __tablename__ = 'complabor'
    __mapper_args__ = {'polymorphic_identity': 'complabor'}
    id = Column(Integer, ForeignKey('components.id'), primary_key=True)
    refid = Column(Integer, ForeignKey('labors.id'))

From Elixir to SqlAlchemy Declarative, Polymorphic Nodes with Child Inheritance

1 Answers1