CODEWITHSUNDEEP
pythonsqlalchemy
user2651804
user2651804

Reputation: 1614

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

Answers (2)

r-m-n
r-m-n

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

Kevin Postlethwait
Kevin Postlethwait

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

Related Questions