undefined | Better HN

0 pointszzzeek2y ago0 comments

Would need to know what versions of SQLAlchemy you spent time with and what exactly were these "asinine" queries you refer towards. SQLAlchemy generates very good queries these days for the information its given. Years ago there was heavy use of large complicated subqueries for many cases and those days are long gone.

Also my 40% / 60% breakdown is based on the SQLAlchemy source code itself regarding what parts of the code deal with generating SQL and what parts deal with all the rest. 40% is likely a large overestimate.

0 comments

BeefWellington2y ago

40% LOC != 40% of the value, which is what your comment stated.

My most recent experience of needing to hand-craft queries because SQLAlchemy was doing something truly hilarious was on 1.4.40-something from 2022 (I think possibly .41 or .42?).

I don't have the specifics in front of me as it was for a prior job but rough outline of what I observed:

A simple select including several related tables with filters on each table resulted in an individual subquery for each *filter* being applied. The resulting SQL was incredibly large, selected data in a poor way, and when replaced with a simplified single query that let the DB do its thing sped up about 100x.

Those days may be gone now with 2.0 but I don't consider barely a year ago "long gone".

I think there's a great place for Create/Update/Delete, and generally when doing trivial Reads. But the lesson I've learned with basically every ORM is that once a third table becomes involved you probably want to write your own SQL because they'll break in some weird way.

This is essentially how I use SQLAlchemy in current projects and it's been great in that regard.

zzzeekOP2y ago

this sounds like the select() object was being used incorrectly, using a pattern that currently emits very prominent deprecation warnings which is:

   stmt = select(...)
   stmt = stmt.where(stmt.c.foo == 'bar')
   stmt = stmt.where(stmt.c.bar == 'xyz')

That above pattern is one I've seen people do even recently, using the "select().c" attribute which from very early versions of SQLAlchemy is defined as "the columns from a subquery of the SELECT" ; this usage began raising deprecation warnings in 1.4 and should be fully removed by 2.1 as it was a remnant of a much earlier version of SQLAlchemy. it will do exactly as you say, "make a subquery for each filter condition" and will produce disastrous queries. people have started tripping over it recently as a result of 1.4 / 2.0's move away from the ORM `Query` object back towards `select()`.

the moment you see SQLAlchemy doing something you see that seems "asinine", send an example to https://github.com/sqlalchemy/sqlalchemy/discussions and I will clarify what's going on, correct the usage so that the query you have is what you expect, and quite often we will add new warnings or documentation when we see people doing things we didn't anticipate (such as in this case, I thought we had removed .c by 2.0 but apparently it's still just deprecated. it will be gone in 2.1).

j / k navigate · click thread line to collapse