jeudi 4 février 2016

Select linked objects from Query in SQLAlchemy

I have a database where I store mouse IDs and FMRI measurement sessions, the classes (with greatly reduced columns, for convenience) look as follows:

class FMRIMeasurement(Base):
    __tablename__ = "fmri_measurements"
    id = Column(Integer, primary_key=True)
    date = Column(DateTime)
    animal_id = Column(Integer, ForeignKey('animals.id'))

class Animal(Base):
    __tablename__ = "animals"
    id = Column(Integer, primary_key=True)
    id_eth = Column(Integer)
    fmri_measurements = relationship("FMRIMeasurement", backref="animal")

I would like to create a pandas dataframe cntaining all of the details of all of the FMRIMeasurements assigned to one particular animal. Selecting data from that animal works fine:

mystring = str(session.query(Animal).filter(Animal.id_eth == 1))
print pd.read_sql_query(mystring, engine, params=[4001])

But as soon as I try to select the FMRIMeasurements it blows up. None of the follwing work.

mystring = str(session.query(Animal.fmri_measurements).filter(Animal.id_eth == 1))

mystring = str(session.query(FMRIMeasurement).filter(FMRIMeasurement.animal.id_eth == 1))

mystring = str(session.query(Animal.fmri_measurements.date).filter(Animal.id_eth == 1))

I guess I'm just using SQLAlchemy wrong, but I couldn't find anything to help me with my use case in the docs (perhaps I don't know how wthat I want to do is actually called) :-/

Aucun commentaire:

Enregistrer un commentaire