Django multi table inheritance represented with SQLAlchemy

Question

How can I convert django's multi table inheritance schema to an SQLAlchemy model?

It seems like the SQLAlchemy joined table inheritance method needs a discriminator column on the parent table. With Django's multi table inheritance, there is no such field.

class CommonParent(models.Model):
     field = models.CharField(max_length=10)

class ChildA(CommonParent):
    pass

class ChildB(CommonParent):
    pass

This results in 3 tables: ChildA, ChildB, and CommonParent. The child tables have an automatic foreign key added which is (parent class name)_ptr. Django uses this to pull the parent table columns.

With Django, a single ChildA instance results in a JOIN on the CommonParent table and all fields are merged into the class instance.

How can I replicate this behavior through an SQLAlchemy introspected model?

Any pointers in the right direction would help greatly.

Update:

In other words, I need the above django model relationship to be accessible through SQLAlchemy by calling

child_b = ChildB.query.first()
assert hasattr(child_b, 'field')  # field is from the parent table, `CommonParent`

In the same way I can do so with Django ORM:

child_b = ChildB.objects.latest()
assert hasattr(child_b, 'field')

knbk · Accepted Answer · 2014-03-07 01:30:57Z

2

+100

There are basically two ways to present the data in CommonParent and ChildA in a single model: in Django, ChildA knows it has a parent in the table for CommonParent. SQLAlchemy uses a different approach, where CommonParent knows it has a child in the table for ChildA. On a database level you use Django's method, so that's what you want to mimic in your SQLAlchemy code.

More specifically, in Django, whenever you query for a CommonParent, you only get the data associated with the CommonParent class, and when you query for a ChildA, you get the data for both ChildA and CommonParent through a join clause.

The CommonParent class works good as it is in SQLAlchemy:

class CommonParent(Base):
  __tablename__ = '<tablename>'

  id = Column(Integer, primary_key=True)
  field = Column(String)

For the ChildA class you want to define a custom mapper that always performs a join statement:

metadata = MetaData()

parent_table = CommonParent.__table__
child_table = Table('<tablename>', metadata,
              Column('id', Integer, primary_key=True),
              Column('commonparent_ptr', Integer, ForeignKey('<tablename>.id'))
             )

childa = join(parent_table, child_table)

class ChildA(Base):
  __table__ = childa

  id = column_property(parent_table.c.id, child_table.c.user_id)
  child_id = child_table.c.id

(Documentation)

answered Mar 7, 2014 at 1:30

knbk

53.9k9 gold badges130 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yuji 'Tomita' Tomita Over a year ago

knbk, thank you! It was there in the docs all along.. Your help is much appreciated!

Collectives™ on Stack Overflow

Django multi table inheritance represented with SQLAlchemy

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related