Reputation: 1614
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.
Upvotes: 56
Views: 96843
Reputation: 15120
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))
Upvotes: 120
Reputation: 85
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.
Upvotes: 3