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.)
On Sunday, July 3, 2022 at 1:24:20 PM UTC-4 Mike Bayer wrote: > > > 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 > <https://stackoverflow.com/questions/72828293/difference-between-sqlalchemy-select-and-query-api> > Not sure if this has been asked before, but in the SQLAlchemy docs > <https://docs.sqlalchemy.org/en/14/tutorial/index.html#unified-tutorial> 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 > <https://docs.sqlalchemy.org/en/14/core/selectable.html#sqlalchemy.sql.expression.select> > which > is more dynamic since it can be constructed from other select statements, > without > explicit subquery definition > <https://docs.sqlalchemy.org/en/14/core/selectable.html#selectable-foundational-constructors> > > The outcome is more "native sql" construction of querying, as per the > latest selectable API > <https://docs.sqlalchemy.org/en/14/core/selectable.html#sqlalchemy.sql.expression.Select>. > > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/eda0b7be-e500-4a61-86ab-efa59797976an%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/eda0b7be-e500-4a61-86ab-efa59797976an%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/107f04ea-79e4-4529-9efe-0513a821293bn%40googlegroups.com.
