SQLAlchemy Subclass/Inheritance Relationships

Question

class Geolocation(db.Model):
    __tablename__ = "geolocation"
    id = db.Column(db.Integer, primary_key=True)
    latitude = db.Column(db.Float)
    longitude = db.Column(db.Float)
    elevation = db.Column(db.Float)         # Meters
    # Relationships
    pin = db.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(db.Model):
    __tablename__ = "pin"
    id = db.Column(db.Integer, primary_key=True)
    geolocation_id = db.Column(db.Integer, db.ForeignKey('geolocation.id'))  # True one to one relationship (Implicit child)

    def __init__(self, geolocation_id):
        self.geolocation_id = geolocation_id

    def __repr__(self):
        return '<Pin Object %s>' % id(self)      # Instance id merely useful to differentiate instances.


class User(Pin):
    #id = db.Column(db.Integer, primary_key=True)
    pin_id = db.Column(db.Integer, db.ForeignKey('pin.id'), primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password_hash = db.Column(db.String(120), nullable=False)
    salt = db.Column(db.String(120), nullable=False)
    # Relationships
    #posts = db.relationship('Post', backref=db.backref('user'), lazy='dynamic')               #One User to many Postings.

    def __init__(self, username, password_hash, salt, geolocation_id):
        super(Pin, self).__init__(self, geolocation_id)
        self.username = username
        self.password_hash = password_hash
        self.salt = salt

    def __repr__(self):
        return '<User %r>' % self.username

I'm confused about how to set up id's and relationships with subclasses in SQLAlchemy (I happen to be using Flask-SQLAlchemy). My general design is to have the superclass Pin be a high level representation of anything that has a geolocation (i.e. a User, a Place, etc.).

There is a one to one relationship between a Pin and Geolocation object so a Geolocation does not contain the location of two Users (or a User and a Place) simultaneously for example. Now I want to subclass Pin to create the User class. A User object should have a name, password_hash, salt and I also want to be able to lookup the Geolocation of the User via userObj.geolocation. However, I later want to make a class Place which also subclasses Pin and I should be able to lookup the geolocation of a Place via placeObj.geolocation . Given a geolocation object, I should be able to use geolocationObj.pin to lookup the User/Place/etc. that the geolocation object corresponds to. The whole reason I introduced the superclass Pin was to ensure that there was a pure one to one relationship between Pin and Geolocation objects rather than having a Geolocation be associated with either a User or a Person which would require the Geolocation table to have user_id and place_id columns, one of which would always be null.

I was expecting every User to automatically have a .geolocation property, via the parent Pin class, which referred to a Geolocation but it seems like SQLAlchemy does not do this. How can I make subclassing relationships work to accomplish my goal of having User and Place and potentially other classes subclass Pin, have each of those classes have a geolocation property via Pin, and have a one to one relationship between a Pin and a Geolocation?

Answer 1

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_id


class 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)

Answer 2

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.