Difference between SQLAlchemy Select and Query API

[Yaakov Bressler]

I saw the following Q posted on SO:

Difference between SQLAlchemy Select and Query API 

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?

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?

I attempted to answer with the following:

My Answer (Though I am not confident this is 100% correct)

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

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.

My question for you all:

Is my answer above correct? That creation of a select object is "the thing."

[Mike Bayer]

On Sun, Jul 3, 2022, at 10:41 AM, Yaakov Bressler wrote:

I saw the following Q posted on SO:

Difference between SQLAlchemy Select and Query API 

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?

There's an in-depth discussion of this in the migration documentation at https://docs.sqlalchemy.org/en/14/changelog/migration_20.html#orm-query-unified-with-core-select

Is there any significant advantage of using one compared to other, for example, a performance boost?

There's a small performance difference in the overhead of constructing the query, and also when using select() there's a performance increase in fetching rows where Query has an unconditional "uniquing" behavior that's no longer implicit.

beyond that, the select() form provides a much more consistent API, integrating in a consistent way with other constructs such as update() and delete() (and also insert() in 2.0) and provides a clearer, explicit usage pattern, in particular which makes it much more straightforward to compose more complex queries such as UNIONs and CTEs.  the legacy Query object is quite clumsy with these kinds of patterns.   The executional pattern for select() is also clear and consistent, where methods like Session.execute() and Session.scalars() return the same kind of structure every time, without it arbitrarily changing based on what arguments are present in the select().    the new executional pattern is also ideal for asyncio, where it's very important that IO operations like executing statements occur at explicit points in the program. 

I think overall if one looks at SQLAlchemy over the past ten years and observes, "there's a select() object for one kind of SELECT, then there's a Query object for this whole different thing that still just does SELECT", that seems very strange.   Query happened by accident and wasn't really supposed to have a full select() API around it, and as it grew all those features, the whole thing looked more and more mis-designed.   2.0 aims to fix that.

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?

I attempted to answer with the following:

My Answer (Though I am not confident this is 100% correct)

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

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.

My question for you all:

Is my answer above correct? That creation of a select object is "the thing."

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/eda0b7be-e500-4a61-86ab-efa59797976an%40googlegroups.com.

[Yaakov Bressler]

Thanks for the detailed explanation Mike. My overall takeaway -> "select" makes more sense, for a variety of reasons. (It is also slightly faster, but that is an aside.)