Difference between SQLAlchemy Select and Query API

Question

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='[email protected]').first()

In Select API, the same leads to more code:

from sqlalchemy import select

query = select(Users).filter_by(name='name', email='[email protected]')
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?

Answer 1

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 query
q = select(Users).filter_by(name='name', email='[email protected]')
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.

Answer 2

Since 1.4 SQLAlchemy internally has implemented query() by the select() API, so in terms of performance there should be very little difference.

In version 1.4, all Core and ORM SELECT statements are rendered from a Select object directly; when the Query object is used, at statement invocation time it copies its state to a Select which is then invoked internally using 2.0 style execution.

https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#change-5159

Historically the difference between query() and select() was query() was used for ORM and select() for Core. Version 2.0 removes many differences between ORM and Core and makes working with them more uniform. Comparing select() and query() doesn't really make sense anymore.

Although there is some backwards compatibility and you're not forced to adopt the 2.0 style immediately, I think it's wise to start adopting it, both in 1.4 and 2.0. I've been doing so for a while now and found it easy to get used to and soon more intuitively compared to the 1.x style. But I've been using SQLAlchemy only for about a year now and have many more years experience with native SQL.