SQLAlchemy - Define a model subclass as a subset of the table

Question

SQLAlchemy newbie here.

I'm trying to define a model subclass that represents a subset of table data. Specifically, I want the subclass to map the most recent row for a given ID.

For example, suppose I have the following model:

class AddressHistory(Base):
    __table__ = 'address_table'

    date = Column(Date, index=True, nullable=False)
    id = Column(BigInteger, primary_key=True)
    street = Column(String(2000))
    city = Column(String(2000))
    state = Column(String(2000))
    zip = Column(Integer)

What I want to do is define a subclass of this model which represents the most recent address record for a given id:

class MostRecentAddress(Address):
    “””
    Represents a row in AddressHistory with the most recent date for a given id.
    ”””

Is there some sort of subquery I can pass to the mapper_args ? Or is there a way to define the table as a select statement?

Answer 1

You're looking for single table inheritance.

https://docs.sqlalchemy.org/en/13/orm/inheritance.html#single-table-inheritance

Your code sample is very nearly exactly how to go about doing this. You just need to add the mapper.

class Person(Base):
    __tablename__ = 'people'
    id = Column(Integer, primary_key=True)
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}

class Engineer(Person):
    __mapper_args__ = {'polymorphic_identity': 'engineer'}
    primary_language = Column(String(50))