75

I'm a bit confused about inheritance under sqlalchemy, to the point where I'm not even sure what type of inheritance (single table, joined table, concrete) I should be using here. I've got a base class with some information that's shared amongst the subclasses, and some data that are completely separate. Sometimes, I'll want data from all the classes, and sometimes only from the subclasses. Here's an example:

class Building:
    def __init__(self, x, y):
        self.x = x
        self.y = y

class Commercial(Building):
    def __init__(self, x, y, business):
        Building.__init__(self, x, y)
        self.business = business

class Residential(Building):
    def __init__(self, x, y, numResidents):
        Building.__init__(self, x, y, layer)
        self.numResidents = numResidents

How would I convert this to SQLAlchemy using declarative? How, then, would I query which buildings are within x>5 and y>3? Or which Residential buildings have only 1 resident?

Improve this question

2 Answers 2

Reset to default
117

Choosing how to represent the inheritance is mostly a database design issue. For performance single table inheritance is usually best. From a good database design point of view, joined table inheritance is better. Joined table inheritance enables you to have foreign keys to subclasses enforced by the database, it's a lot simpler to have non-null constraints for subclass fields. Concrete table inheritance is kind of worst of both worlds.

Single table inheritance setup with declarative looks like this:

class Building(Base):
    __tablename__ = 'building'
    id = Column(Integer, primary_key=True)
    building_type = Column(String(32), nullable=False)
    x = Column(Float, nullable=False)
    y = Column(Float, nullable=False)
    __mapper_args__ = {'polymorphic_on': building_type}

class Commercial(Building):
    __mapper_args__ = {'polymorphic_identity': 'commercial'}
    business = Column(String(50))

class Residential(Building):
    __mapper_args__ = {'polymorphic_identity': 'residential'}
    num_residents = Column(Integer)

To make it joined table inheritance, you'll need to add

__tablename__ = 'commercial'
id = Column(None, ForeignKey('building.id'), primary_key=True)

to the subclasses.

Querying is mostly the same with both approaches:

# buildings that are within x>5 and y>3
session.query(Building).filter((Building.x > 5) & (Building.y > 3))
# Residential buildings that have only 1 resident
session.query(Residential).filter(Residential.num_residents == 1)

To control which fields are loaded you can use the query.with_polymorphic() method.

The most important thing to think about using inheritance for the datamapping, is whether you actually need inheritance or can do with aggregation. Inheritance will be a pain if you will ever need to change the type of an building, or your buildings can have both commercial and residential aspects. In those cases it's usually better to have the commercial and residential aspects as related objects.

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

5 Comments

Wow, this is a great answer. Thanks! So I've compared performance between the single and joined table options, and find that the second query [filter(Residential.num_residents == n).count()] runs ~2x faster in the single table scenario (as expected). However, for some reason the first query against Building [filter((Building.x > x) & (Building.y > y)).count()] is about 10% slower with the single table, though actually loading all the elements is pretty comparable (.all()).
For a more specific problem regarding joined table inheritance see stackoverflow.com/questions/8389606/…
The only thing missing is an example using concrete table inheritance -- the one I am seeking help with, naturally :-) Since this is an old question, maybe concrete table inheritance was added after this question was answered.
This might be a silly question, but how do the underlying SQL tables look like in Single Table inheritance ? Is there just 1 building SQL table which includes the business and num_residents columns ?
@LymanZerga: Yes, that is how it looks in the DB. It creates all the columns on the single table and simply creates the correct model in Python based on the value of the field referenced by polymorphic_on.
19

Ants Aasma's solution is much more elegant, but if you are keeping your Class definitions separate from your table definitions intentionally, you need to map your classes to your tables with the mapper function. After you have defined your classes, you need to define your tables:

building = Table('building', metadata,
    Column('id', Integer, primary_key=True),
    Column('x', Integer),
    Column('y', Integer),
)
commercial = Table('commercial', metadata,
    Column('building_id', Integer, ForeignKey('building.id'), primary_key=True),
    Column('business', String(50)),
)
residential = Table('residential', metadata,
    Column('building_id', Integer, ForeignKey('building.id'), primary_key=True),
    Column('numResidents', Integer),
)

Then you can map the tables to the classes:

mapper(Building, building)
mapper(Commercial, commercial, inherits=Building, polymorphic_identity='commercial')
mapper(Residential, residential, inherits=Building, polymorphic_identity='residential')

Then interact with the classes the exact same way Ants Aasma described.

Improve this answer

4 Comments

Would you specify what is mapper coming from? Is it sqlalchemy.orm.mapper?
This is awesome! I was searching for something like this for implementing Clean Architecture (by Uncle Bob) in Python Any pointers to reading more about this method and mapper function will be really good!
If anybody wants to read more about this technique of mapping objects to classes, it's called classical mapping, which is different from declarative mapping used commonly. To read more about this, check out the following link: docs.sqlalchemy.org/en/13/orm/mapping_styles.html
There is a beautiful (in my opinion) book on that implements a repository pattern based on classical mapping there : cosmicpython.com/book/chapter_02_repository.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.