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.
2
