1

Is it possible to build a SQLAlchemy relationship that will accept any instance that inherits from the same Base? Ideally, it maintains one foreign key for the instance's ID and a second foreign key for the instance's type.

As an example, this Variable class has a relationship applies_to between itself and the Car class.

# some overhead, for completeness
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, Session
from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:')
session = Session(bind=engine)
Base = declarative_base()

# defining my classes
class Variable(Base):
    __tablename__ = 'variables'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    value = Column(Integer)

    applies_to_id = Column(Integer, ForeignKey('cars.id'))
    applies_to = relationship('Car')

class Car(Base):
    __tablename__ = 'cars'
    id = Column(Integer, primary_key=True)

class Truck(Base):
    __tablename__ = 'trucks'
    id = Column(Integer, primary_key=True)

Base.metadata.create_all(engine)

With it, I can make a Car and a Variable for that car and commit to the database.

car = Car()
var1 = Variable(name="speed", value=50, applies_to=car)
session.add(car)
session.add(var1)
session.commit()

But I cannot do a similar thing with Truck and Variable.

truck = Truck()
var2 = Variable(name="speed", value=50, applies_to=truck)
session.add(truck)
session.add(var2)
session.commit()

AssertionError: Attribute 'applies_to' on class '<class '__main__.Variable'>' doesn't handle objects of type '<class '__main__.Truck'>'

I would like to edit the Variable class to support Truck and any other classes that inherit from Base through the applies_to relationship.

EDIT: Similar to sqlalchemy generic foreign key (like in django ORM), but that solution requires all classes to inherit from the HasAddresses mixin and handles a backref via addresses on those classes. Is there a solution that only edits the Variable class and doesn't maintain a backref to the other classes?

Bryce93
  • 451
  • 7
  • 11
  • Possible duplicate of [sqlalchemy generic foreign key (like in django ORM)](https://stackoverflow.com/questions/17703239/sqlalchemy-generic-foreign-key-like-in-django-orm) – Ilja Everilä Jun 07 '18 at 04:44
  • Also since a truck is a car, you could have a look at the inheritance patterns, but "and any other classes" means you're after the generic association, one way or another -- though it's considered an anti pattern by many. – Ilja Everilä Jun 07 '18 at 04:46
  • @IljaEverilä, that solution requires all classes to inherit from `HasAddresses`. Is it possible to achieve a similar outcome without needing to edit the other classes? I don't need a backref – Bryce93 Jun 07 '18 at 17:00

0 Answers0