How to Make a Subquery in Sqlalchemy
Select * from Residents Where Apartment_Id in (Select Id from Apartments Where Postcode = 2000) I'm Using Sqlalchemy and Am Trying to Execute the Above Query...
SELECT *
FROM Residents
WHERE apartment_id IN (SELECT ID
FROM Apartments
WHERE postcode = 2000)
I'm using sqlalchemy and am trying to execute the above query. I haven't been able to execute it as raw SQL using db.engine.execute(sql) since it complains that my relations doesn't exist... But I succesfully query my database using this format: session.Query(Residents).filter_by(???).
I cant not figure out how to build my wanted query with this format, though.
2 Answers
You can create subquery with subquery method
subquery = session.query(Apartments.id).filter(Apartments.postcode==2000).subquery()
query = session.query(Residents).filter(Residents.apartment_id.in_(subquery))
I just wanted to add, that if you are using this method to update your DB, make sure you add the synchronize_session='fetch' kwarg. So it will look something like:
subquery = session.query(Apartments.id).filter(Apartments.postcode==2000).subquery()
query = session.query(Residents).\
filter(Residents.apartment_id.in_(subquery)).\
update({"key": value}, synchronize_session='fetch')
Otherwise you will run into issues.
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
