jeudi 4 février 2016

Filter query by linked object key in SQLAlchemy

Judging by the title this would be the exact same question, but I can't see how any of the answers are applicable to my use case:

I have two classes and a relationship between them:

treatment_association = Table('tr_association', Base.metadata,
    Column('chronic_treatments_id', Integer, ForeignKey('chronic_treatments.code')),
    Column('animals_id', Integer, ForeignKey('animals.id'))
)

class ChronicTreatment(Base):
    __tablename__ = "chronic_treatments"
    code = Column(String, primary_key=True)

class Animal(Base):
    __tablename__ = "animals"
    treatment = relationship("ChronicTreatment", secondary=treatment_association, backref="animals")

I would like to be able to select only the animals which have undergon a treatment which has the code "X". I tried quite a few approaches.

This one fails with an AttributeError:

sql_query = session.query(Animal.treatment).filter(Animal.treatment.code == "chrFlu")
for item in sql_query:
    pass

mystring = str(session.query(Animal))

And this one happily returns a list of unfiltered animals:

sql_query = session.query(Animal.treatment).filter(ChronicTreatment.code == "chrFlu")
for item in sql_query:
    pass

mystring = str(session.query(Animal))

The closest thing to the example from the aforementioned thread I could put together:

subq = session.query(Animal.id).subquery()
sql_query = session.query(ChronicTreatment).join((subq, subq.c.treatment_id=="chrFlu"))
for item in sql_query:
    pass

mystring = str(session.query(Animal))
mydf = pd.read_sql_query(mystring,engine)

Also fails with an AttributeError.

Can you hel me sort this list?

Aucun commentaire:

Enregistrer un commentaire