How to make a subquery in sqlalchemy

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.