SQLAlchemy Subclass / Relationship Inheritance

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 am confused about how to set up id and subclass relationships in SQLAlchemy (I use Flask-SQLAlchemy). My overall design is for the superscript Pin to be a high-level representation of everything that has geolocation (i.e. user, location, etc.).

There is a one-to-one relationship between the Pin object and Geolocation, so geolocation does not contain the location of two users (or user and location) at the same time. Now I want to subclass Pin to create a User class. The user object must have a name, hash_password, salt, and I also want to be able to search for the user's geolocation through userObj.geolocation . However, I later want to create a Place class, which is also a subclass of Pin, and I should be able to search for Place geolocation through placeObj.geolocation . Given the geolocation object, I have to use geolocationObj.pin to search for user / location / etc. which corresponds to the geolocation object. The whole reason I entered the superscript pin was to make sure that there is a clean connection between the Pin and Geolocation objects, and not the geolocation binding to the user or person, which requires the Geolocation table to have user_id and place_id , one of which would always be zero.

I expected each user to automatically get the .geolocation property through the parent class Pin, which refers to Geolocation, but it looks like SQLAlchemy does not. How can I make the subclass relationships work in order to accomplish my task of creating a subclass of "User" and "Place" and a potentially different class "Pin", do each of these classes have a geolocation property through Pin and have a one-to-one relationship between Pin and geolocation?

Answer 1

The solution I came across. This serves as a complete example of subclasses in SQLAlchemy in a declarative style and uses inheritance union.

 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 displaying inheritance hierarchies and does it declaratively in SQLAlchemy.

I believe that you will need a unified taste for inheritance, which means that each class in your chain of parent classes has its own table with columns unique to it. Basically, you need to add a discriminator column to the pin table to indicate the type of subclass for each Pin and some double underscores for your classes to describe the SQLAlchemy inheritance configuration.