How to make a subquery in sqlalchemy

Question

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.

Erich · Accepted Answer · 2024-01-27 11:41:49Z

123

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

edited Jan 27, 2024 at 11:41

Erich

2,1321 gold badge20 silver badges25 bronze badges

answered Aug 10, 2016 at 17:47

r-m-n

15.3k6 gold badges74 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Eric Martin Over a year ago

Thank you my lord. Finally an answer that does not involve messing around with the orm.

thanos.a Over a year ago

the query is type of <class 'flask_sqlalchemy.BaseQuery'> upon which I am not able to call .all() to get the results. Can you explain how do I use the query object ?

castarco Over a year ago

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

Zoupah Over a year ago

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 Over a year ago

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

|

Kevin Postlethwait · Accepted Answer · 2020-03-03 15:29:28Z

3

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.

answered Mar 3, 2020 at 15:29

Kevin Postlethwait

855 bronze badges

2 Comments

hjpotter92 Over a year ago

what kind of issues, care to expand?

Andrio Skur Over a year ago

@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

Collectives™ on Stack Overflow

How to make a subquery in sqlalchemy

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related