Multiple / split class associations in sqlalchemy

Question

I have the following objects and relationships. This is actually a fairly simple case, and I provide all of these fields to show why I believe that inhalation and injection anesthesia should be defined by two different classes.

class InhalationAnesthesia(Base): __tablename__ = "inhalation_anesthesias" id = Column(Integer, primary_key=True) anesthetic = Column(String) concentration = Column(Float) concentration_unit = Column(String) duration = Column(Float) duration_unit = Column(String) class TwoStepInjectionAnesthesia(Base): __tablename__ = "twostep_injection_anesthesias" id = Column(Integer, primary_key=True) anesthetic = Column(String) solution_concentration = Column(Float) solution_concentration_unit = Column(String) primary_dose = Column(Float) primary_rate = Column(Float) primary_rate_unit = Column(String) secondary_rate = Column(Float) secondary_rate_unit = Column(String) class Operation(Base): __tablename__ = "operations" id = Column(Integer, primary_key=True) anesthesia_id = Column(Integer, ForeignKey('inhalation_anesthesias.id')) anesthesia = relationship("InhalationAnesthesia", backref="used_in_operations") 

However, I would like to define the anesthesia attribute of the Operation class in such a way that any Operation object can point to either a TwoStepInjectionAnesthesia object or an InhalationAnesthesia object.

How can i do this?

Answer 1

I suggest you use inheritance. This is very, very well explained in the SqlAlchemy docs here and here.

My recommendation is to create an Anesthesia class and inherit from it both InhalationAnesthesia and TwoStepInjectionAnesthesia . This is your call to decide which type of table inheritance to use:

• single table inheritance
• White table inheritance
• attached table inheritance

The most common forms of inheritance are a single and combined table, while specific inheritance creates more configuration problems.

---

In your case, I assume the attached table inheritance is the choices:

 class Anesthesia(Base) __tablename__ = 'anesthesias' id = Column(Integer, primary_key=True) anesthetic = Column(String) # ... # every common field goes here # ... discriminator = Column('type', String(50)) __mapper_args__ = {'polymorphic_on': discriminator} 

The purpose of the discriminator field is:

... should act as a discriminator and store a value that indicates the type of object represented in the string. A column can be any type of data, although the row and integer are the most common.

Key

__mapper_args__ polymorphic_on determines which field is used as the discriminator. In child classes (below), the polymorphic_identity key determines the value that will be stored in the polymorphic discriminator column for class instances.

 class InhalationAnesthesia(Anesthesia): __tablename__ = 'inhalation_anesthesias' __mapper_args__ = {'polymorphic_identity': 'inhalation'} id = Column(Integer, ForeignKey('anesthesias.id'), primary_key=True) # ... # specific fields definition # ... class TwoStepInjectionAnesthesia(Anesthesia): __tablename__ = 'twostep_injection_anesthesias' __mapper_args__ = {'polymorphic_identity': 'twostep_injection'} id = Column(Integer, ForeignKey('anesthesias.id'), primary_key=True) # ... # specific fields definition # ... 

---

Finally, the Operation class can reference the Anesthesia parent table with a typical relationship:

 class Operation(Base): __tablename__ = 'operations' id = Column(Integer, primary_key=True) anesthesia_id = Column(Integer, ForeignKey('anesthesias.id')) anesthesia = relationship('Anesthesia', backref='used_in_operations') 

Hope this is what you are looking for.