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

user2651804
  • 1,464
  • 4
  • 22
  • 45

2 Answers2

95

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))
r-m-n
  • 14,192
  • 4
  • 69
  • 68
  • 7
    Thank you my lord. Finally an answer that does not involve messing around with the orm. – Eric Martin Jul 26 '18 at 12:43
  • the query is type of upon which I am not able to call .all() to get the results. Can you explain how do I use the query object ? – thanos.a Dec 06 '20 at 21:58
  • the query is type . How can you get the data from it. I can see that it has the .all() method but once I call it flask returns me the error such as: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: appointments.done – thanos.a Dec 07 '20 at 19:54
  • 1
    It's not clear to me how I would bind subquery "parameters" to the main query by using this method (as the subquery is defined before the query itself). – castarco Oct 15 '21 at 06:44
  • 1
    In SQLAlchemy 1.4 this use case gives me a warning: `SAWarning: Coercing Subquery object into a select() for use in IN(); please pass a select() construct explicitly`. Is there an example formatting for this issue? I haven't found one in the docs yet. – Zoupah Feb 14 '22 at 16:44
  • 2
    Answered my own simple question. Just write it as follows to resolve the warning: `from sqlalchemy.sql import select` and `query = session.query(Residents).filter(Residents.apartment_id.in_(select(subquery)))` – Zoupah Feb 14 '22 at 16:59
  • @Zoupah better use [scalar_subquery](https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.scalar_subquery) – Olzhas Arystanov Jun 15 '22 at 21:53
2

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.

Kevin Postlethwait
  • 67
  • 5
  • 11
    what kind of issues, care to expand? – hjpotter92 Oct 01 '20 at 07:31
  • 2
    @hjpotter92 You can read this in docs. But in general, if you already have some Resident objects in memory, this update also updated these objects in memory. Otherwise, these objects will be out of date, which can lead to errors. But, synchronize_session also is slow which is also not good – Andrio Skur Aug 11 '21 at 09:56