multiple/split class associations in sqlalchemy

Question

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?

Roberto Damian Alfonso · Accepted Answer · 2015-11-26T19:45:23.990

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.

So using this set-up I should be able to reference both `InhalationAnesthesia` and `TwoStepInjectionAnesthesia` objects under `Operation.anesthesia`? Would using `Operation.anesthesia = [TwoStepInjectionAnesthesia(..), InhalationAnesthesia(..), Inhalationanesthesia(..)]` mess anything up? — TheChymera, Dec 01 '15 at 16:30
@TheChymera Yes and Yes. In the example provided an `Operation` instance will reference only one `Anesthesia` object. If you need it to be a list you should change the code a little bit. — Roberto Damian Alfonso, Dec 01 '15 at 23:03
One more thing, what is the advantage of using the above set-up (other than being slightly better organized on the python end)? will the actual sql table that gets generated contain certain column types only for rows with certain discrminators? (thereby saving disk space?) — TheChymera, Dec 02 '15 at 10:58

multiple/split class associations in sqlalchemy

1 Answers1

Linked