Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Python: Just Write SQL

joaodlf.com

Post reply on HN
Print view View original

251–260 of 288 posts

Re: Python: Just Write SQL

#251

Post by BeefWellington »

zzzeek wrote: post #249
BeefWellington wrote: post #158

Earlier quoted context omitted.

While I somewhat agree that a lot of these articles are people who just don't actually try/use the full feature set of ORMs, I don't agree with the overall premise you're presenting that they really do more than write SQL for you. The other things they provide are largely just abstractions around how the queried data is returned and some additional metadata tracking of the relationships. Your estimation of those part…

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 th…

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.

Re: Python: Just Write SQL

#252

Post by BeefWellington »

ubercore wrote: post #246
TX81Z wrote: post #237

Earlier quoted context omitted.

Agree, my datasets have multiple billions of rows and if I don’t know the details of the query, or have the ability to tune it, it’s utterly insufficient for my needs. I still fail to see how anybody who actually knows sql and works with “Big Kid” datasets would use an ORM.

When I've worked with datasets that size, the common operations tend towards the analytic or bulk operations, IME. That's not the sweet spot for an ORM, and SQL will often make a lot of sense. But, and again this is my experience, you often want to work with some more traditionally sized slice of that data, doing CRUDish things, and ORM can exist alongside the nicely tuned bulk or analytic operations. If you have the…

Yeah I agree here. If you're doing mostly transactional work then it can work for the 99%. As soon as you step towards analytics queries you start to see things fall apart and it's better to hand-tune your queries.

In my recent projects that's essentially what I do - I use SQLAlchemy for very basic CRUD and then anything analytical gets handcrafted.

Re: Python: Just Write SQL

#253

Post by zzzeek »

BeefWellington wrote: post #251
zzzeek wrote: post #249

Earlier quoted context omitted.

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 th…

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…

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).

Re: Python: Just Write SQL

#254

Post by hnfong »

zzzeek wrote: post #200
lelanthran wrote: post #194

Earlier quoted context omitted.

Okay, you're the expert here, and I'll happily concede that I am not (and apologise in advance if I seem to be disrespectful), but ... > The SQL is not really the point. It's about the rows and objects, moving the data from the objects to the INSERT statement, moving the data from the rows you SELECTed back to the objects. I think that that is the problem: mapping a relational dataset to a hierarchical dataset is the…

yah I read Neward's thing, and it was one of the main reasons I wrote SQLAlchemy in the first place, because he was just so wrong. It read like he tried to write some object relational thing and it didn't work out, so he goes off and rant rant ORMs are wrong. Kind of proving that post wrong was one of the primary goals of SQLAlchemy, really, where I sought to change the question of "impedance mismatch" and "leaky abs…

> I mean, that was really an important point in time when there really werent ORMs that were easy to work with, there was Hibernate in a very early stage and there were overly simplistic things for Perl, so I thought it was important that this "better way" I had in mind could be put out there, before the idea that "yeah let's all avoid ORMs unconditionally" could take hold.

I totally remember this time. It was around 2002~2005.

I started off with writing a discussion forum and a calendar booking webapp for my high school in PHP + MySQL. After working on it for a year or so, I realized I was basically re-typing a lot of SQL and boilerplate code, which repeated no matter whether I was working on a Post object, a User object, or a Event object. I almost wrote a half-assed ORM in PHP.

I then got an internship job, and they had a simple Java webapp project. Hibernate. In retrospect it was a huge pain, and we could probably have completed the project in 30% of the time if we used something more effective (but then, web frameworks weren't a thing in 2004), but still Hibernate was probably better than writing raw SQL for our cases.

Then later, at the same company, they had another webapp project in Perl. I don't remember the name of the ORM but it was kind of "magic" - as you said, overly simplistic, everything was implicit but seems to worked.

Shortly after I encountered Django, which seems to have solved all my problems with ORMs and I been happy with the way it works for the almost 20 years of using it.

To me the utility of ORMs is just obvious once you've been through writing CRUD without ORMs on a deadline, and tried anything that's not called Hibernate.

I'm not sure why there are so many people who apparently develops or maintains a codebase that deals with only a table or two ranting about ORMs. To me this isn't an issue with ORMs at all, but rather the curious phenomenon in the industry there is extreme specialization to the point that businesses can afford to put a person (or even a team), full time, working on a small part of the business only involving one table or two. (Can't say I'm not in this situation in my professional work today either!)

To those who say don't use ORMs -- try implementing HN in a week, without an ORM. It can probably be done either way, but with an ORM you end up with 50% less code and probably less sore fingers.

Re: Python: Just Write SQL

#255

Post by hnfong »

dagmx wrote: post #122
zzzeek wrote: post #113

ORMs do much more than "write SQL". This is about 40% of the value they add. As this argument comes up over, and over, and over, and over again, writers of the "bah ORM" club continuously thinking, well I'm not sure, that ORMs are just going to go "poof" one day? I wrote some years back the "SQL is Just As Easy as an ORM Challenge" which demonstrates maybe a few little things that ORMs do for you besides "write SQL",…

I completely agree with you. Every single time someone says: “Just write your own abstraction over an sql generator ”, it eventually devolves into a full blown ORM. I swear the majority of these opinion posts against ORMs are from people who must have worked in a badly implemented project that left them with a bad experience and they blamed the pattern rather than the technology. One of the best bits about an ORM is…

> I swear the majority of these opinion posts against ORMs are from people who must have ...

"microservices" - https://news.ycombinator.com/item?id=37125636

That cleared things up, at least for me. I totally agree that if your job is to essentially write a custom, RESTful, fault tolerant, 99.999% uptime accessor for a table or two, basically you're hired to be the ORM, so you don't want to just suggest people to use a ORM framework and effectively make yourself redundant.

Re: Python: Just Write SQL

#256

Post by aidos »

lelanthran wrote: post #202
dagmx wrote: post #146

Earlier quoted context omitted.

My point with the personal anecdote is precisely that you can write bad or good code with both. however that an ORM can allow for more consistent experience across many more people. My point is different than your takeaway. In my point I’m not blaming the technology, I’m blaming the people involved with using it in production. I specifically point out that a well versed engineer can write a SQL based system well. An…

> My point with the personal anecdote is precisely that you can write bad or good code with both. however that an ORM can allow for more consistent experience across many more people. I find it interesting that you say that; my takes is that it's the other way around! SQL join statements look the same no matter what programming language the reader is used to, but each ORM differs in the way the join looks to the read…

To be fair, in sqlalchemy you could just do “print(query)” and you’d see your sql.

Re: Python: Just Write SQL

#257

Post by aidos »

TX81Z wrote: post #236
zzzeek wrote: post #113

ORMs do much more than "write SQL". This is about 40% of the value they add. As this argument comes up over, and over, and over, and over again, writers of the "bah ORM" club continuously thinking, well I'm not sure, that ORMs are just going to go "poof" one day? I wrote some years back the "SQL is Just As Easy as an ORM Challenge" which demonstrates maybe a few little things that ORMs do for you besides "write SQL",…

I’m sorry but 90% of the time I encounter somebody who swears by an ORM I’ll figure out the reason they use it is because they didn’t know SQL to start with and didn’t commit to learning a new language. The number of people who know SQL well and still choose an ORM seems to be very, very low in my experience.

Low, maybe, but we’re out there. Sqla just saves me so much time in general. Sometimes it’s easier to drop down a level, but for 99% of my db interactions the code will be significantly shorter using sqla, so I choose to use it.

Re: Python: Just Write SQL

#258

Post by lelanthran »

aidos wrote: post #256
lelanthran wrote: post #202

Earlier quoted context omitted.

> My point with the personal anecdote is precisely that you can write bad or good code with both. however that an ORM can allow for more consistent experience across many more people. I find it interesting that you say that; my takes is that it's the other way around! SQL join statements look the same no matter what programming language the reader is used to, but each ORM differs in the way the join looks to the read…

To be fair, in sqlalchemy you could just do “print(query)” and you’d see your sql.

> To be fair, in sqlalchemy you could just do “print(query)” and you’d see your sql.

I wasn't saying that you couldn't see the raw SQL, I'm saying that the ORM syntax does not result in a consistent experience for everyone.

Re: Python: Just Write SQL

#259

Post by slotrans »

Yes. Just write SQL. SQL is good.

Never use an (active record) ORM. Ever. For any purpose. They are a disastrous idea that should be un-invented.

I have this discussion with a lot of people who claim they cannot imagine working without an ORM and that "surely" using SQL is so much more work blah blah blah. Yet they have never tried! And aren't willing to try!

You should try it.

Re: Python: Just Write SQL

#260

Post by baq »

javcasas wrote: post #245
baq wrote: post #217

Earlier quoted context omitted.

Raw sql doesn’t compose, so it’s a no go for me except in special cases, but the tool would be a great addition to sqlalchemy.core for when those special cases occur.

Here, go and read about the WITH statement https://modern-sql.com/feature/with

That’s exactly when I write raw sql but being stuck on old Postgres versions has performance implications.
Post reply on HN