multiple/split class associations in sqlalchemy

I have the following objects and relations defined. This is actually quite a simple case, and I am providing all those fields just to show why I believe 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")

I would, however, like to define the anesthetic 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 that?

I suggest you to use inheritance. It's very, very well explained in SqlAlchemy docs here and here

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

• single table inheritance
• concrete table inheritance
• joined table inheritance

The most common forms of inheritance are single and joined table, while concrete inheritance presents more configurational challenges.

---

For your case I'm asuming joined table inheritance is the election:

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 discriminator field:

... is to act as the discriminator, and stores a value which indicates the type of object represented within the row. The column may be of any datatype, though string and integer are the most common.

__mapper_args__'s polymorphic_on key define which field use as discriminator. In children classes (below), polymorphic_identity key define the value that will be stored in the polymorphic discriminator column for instances of the class.

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 may reference the parent table Anesthesia 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're looking for.