SQLAlchemy Subclass/Inheritance Relationships

The solution I came up with. This serves as a full example of subclassing in SQLAlchemy in the declarative style and using Join inheritance.

class Geolocation(Base):    __tablename__ = "geolocation"    id = Column(Integer, primary_key=True)    latitude = Column(Float)    longitude = Column(Float)    elevation = Column(Float)         # Meters    # Relationships    person = relationship('Pin', uselist=False, backref="geolocation")    def __init__(self, latitude, longitude, elevation):        self.latitude = latitude        self.longitude = longitude        self.elevation = elevation    def __repr__(self):        return '<Geolocation %s, %s>' % (self.latitude, self.longitude)class Pin(Base):    __tablename__ = 'pin'    id = Column(Integer, primary_key=True)    geolocation_id = Column(Integer, ForeignKey('geolocation.id'), unique=True, nullable=False)  # True one to one relationship (Implicit child)    type = Column('type', String(50))              # discriminator    __mapper_args__ = {'polymorphic_on': type}    def __init__(self, geolocation_id):        self.geolocation_id = geolocation_idclass User(Pin):    __tablename__ = 'user'    id = Column(Integer, ForeignKey('pin.id'), primary_key=True)    __mapper_args__ = {'polymorphic_identity': 'user',                       'inherit_condition': (id == Pin.id)}    user_id = Column(Integer, autoincrement=True, primary_key=True, unique=True)    username = Column(String(80), unique=True)    password_hash = Column(String(120))    salt = Column(String(120))    posts = relationship('Posting', primaryjoin="(User.user_id==Posting.user_id)", backref=backref('user'), lazy='dynamic')   #One User to many Postings.    def __init__(self, username, password_hash, salt, geo_id):        super(User, self).__init__(geo_id)        self.username = username        self.password_hash = password_hash        self.salt = salt    def __repr__(self):        return '<User %s>' % (self.username)class Posting(Pin):    __tablename__ = 'posting'    id = Column(Integer, ForeignKey('pin.id'), primary_key=True)    __mapper_args__ = {'polymorphic_identity': 'posting',                        'inherit_condition': (id == Pin.id)}    posting_id = Column(Integer, autoincrement=True, primary_key=True, unique=True)    creation_time = Column(DateTime)    expiration_time = Column(DateTime)    user_id = Column(Integer, ForeignKey('user.user_id'))              # One User to many Postings    def __init__(self, creation_time, expiration_time, user_id, geo_id):        super(Posting, self).__init__(geo_id)        # For now, require creation time to be passed in. May make this default to current time.        self.creation_time = creation_time        self.expiration_time = expiration_time        self.user_id = user_id    def __repr__(self):        #TODO come up with a better representation        return '<Post %s>' % (self.creation_time)

Here's the documentation for mapping inheritance hierarchies and for doing it declaratively in SQLAlchemy.

I believe you'll want the joined table inheritance flavour, meaning that every class in your parent class chain has its own table with the columns unique to it. Basically, you need to add a discriminator column to the pin table to denote the subclass type for each Pin, and some double underscore properties to your classes to describe the inheritance configuration to SQLAlchemy.