lundi 21 mars 2016

Join all related tables in SQLAlchemy

I have a table for experimental protocols which includes foreign keys for a number of other tables (most prominently it includes a number of Incubation entries). The structure looks like this, verbatim:

class DNAExtractionProtocol(Base):
    __tablename__ = 'dna_extraction_protocols'
    id = Column(Integer, primary_key=True)
    code = Column(String, unique=True)
    name = Column(String)
    sample_mass = Column(Float)
    mass_unit_id = Column(String, ForeignKey('measurement_units.id'))
    mass_unit = relationship("MeasurementUnit", foreign_keys=[mass_unit_id])
    digestion_buffer_id = Column(String, ForeignKey("solutions.id"))
    digestion_buffer = relationship("Solution", foreign_keys=[digestion_buffer_id])
    digestion_buffer_volume = Column(Float)
    digestion_id = Column(Integer, ForeignKey("incubations.id"))
    digestion = relationship("Incubation", foreign_keys=[digestion_id])
    lysis_buffer_id = Column(String, ForeignKey("solutions.id"))
    lysis_buffer = relationship("Solution", foreign_keys=[lysis_buffer_id])
    lysis_buffer_volume = Column(Float)
    lysis_id = Column(Integer, ForeignKey("incubations.id"))
    lysis = relationship("Incubation", foreign_keys=[lysis_id])
    proteinase_id = Column(String, ForeignKey("solutions.id"))
    proteinase = relationship("Solution", foreign_keys=[proteinase_id])
    proteinase_volume = Column(Float)
    inactivation_id = Column(Integer, ForeignKey("incubations.id"))
    inactivation = relationship("Incubation", foreign_keys=[inactivation_id])
    cooling_id = Column(Integer, ForeignKey("incubations.id"))
    cooling = relationship("Incubation", foreign_keys=[cooling_id])
    centrifugation_id = Column(Integer, ForeignKey("incubations.id"))
    centrifugation = relationship("Incubation", foreign_keys=[centrifugation_id])

    volume_unit_id = Column(String, ForeignKey('measurement_units.id'))
    volume_unit = relationship("MeasurementUnit", foreign_keys=[volume_unit_id])

Now, given the unique code attribution, I would like to get a Pandas dataframe (or rather a Series) which allows me to select not only any of the attributes of the corresponding entry in the "dna_extraction_protocols" table, but also in the related tables.

I am currently selecting a pandas dataframe with:

sql_query = session.query(tables["dna_extraction_protocols"]).order_by(tables["dna_extraction_protocols"].code)
for item in sql_query:
    pass
mystring = str(sql_query)
mydf = pd.read_sql_query(mystring,engine)
print(mydf)

But this only allows me to select the IDs of the related keys. I can select mydf["dna_extraction_protocols_mass_unit_id"] - but I would also like to be able to select mydf["dna_extraction_protocols_mass_unit_long_name"], given the following available keys on the "measurement_units" table:

class MeasurementUnit(Base):
    __tablename__ = "measurement_units"
    id = Column(Integer, primary_key=True)
    code = Column(String, unique=True)
    long_name = Column(String)
    siunitx = Column(String)

Aucun commentaire:

Enregistrer un commentaire