Difference between SQLAlchemy Select and Query API

user_5094261 0
Viewed 284

Not sure if this has been asked before, but in the SQLAlchemy docs they talk about introducing select() as part of the new 2.0 style for the ORM. Previously (1.x style), the query() method were used to fetch data. What is the difference between these two?

For example, for querying a Users table for a user with email and name we can do something as followed in Query API:

session.query(Users).filter_by(name='name', email='mail@example.com').first()

In Select API, the same leads to more code:

from sqlalchemy import select

query = select(Users).filter_by(name='name', email='mail@example.com')
user = session.execute(query).fetchone()

Is there any significant advantage of using one compared to other, for example, a performance boost? 2.0 API is still in active development yet it seems like their documentation is favoring the select API more than the "legacy" query API. Is this merely attempting to bridge the gap between the ORM and Core functionalities?

sqlalchemy orm sql python
1 Answers

The biggest difference is how the select statement is constructed. The new method creates a select object which is more dynamic since it can be constructed from other select statements, without explicit subquery definition:

# select from a subqeuery styled quert
q = select(Users).filter_by(name='name', email='mail@example.com')
q = select(Users.name, Users.email).select_from(q)

The outcome is more "native sql" construction of querying, as per the latest selectable API. Queries can be defined and passed throughout statements in various functionalities such as where clauses, having, select_from, intersect, union, and so on.

Performance wise, probably some slight benefit in python run time (compiling of query), but negligible compared to network latency + db work.

Great question btw! My response is informed by my experience with the select API. I am curious to hear what others have to say.

user_105219590
Related