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

141–150 of 288 posts

Re: Python: Just Write SQL

#141

Post by DangitBobby »

nicoburns wrote: post #105
pydry wrote: post #97

Earlier quoted context omitted.

I have never seen an ORM in my life that didn't reduce the total amount of code written. Not even the Java monstrosities increased the SLOC.

Perhaps it depends what you're doing? IMO: .where('column_a', '=', 'value1') .and(q => q.isNull('column_b').orWhere('column_b', '=', 'value2'))) is a lot less readable than: WHERE column_a = 'value1' AND (column_b IS NULL OR column_b = 'value2')

In Django that would be .filter(column_a='value1', Q(column_b__isnull=True)|Q(column_b='value2'))

And obviously you can use whatever indentations you like.

Re: Python: Just Write SQL

#142

Post by jacurtis »

jpc0 wrote: post #132
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",…

> and still be using an enormous amount of automation to deal with the database drivers and moving data between your objects and rows I've found I would generally only need the handling of database drivers and query building since I'm already writing a validation layer and to add data marshalling to that is pretty trivial. Likewise practicing YAGNI, what is the chances I need multiple database drivers for different d…

> I would argue to start with writing the basic SQL queries and adding an ORM later when you know you actually need it.

I think you missed the point of the parent comment, which is that ORM's are not about writing SQL queries (although they do that). But ORMs are about moving data around, transforming it from rows and columns into meaningful objects in the project's language and data model.

As the parent comment suggested, if you are dying to write your own SQL (which does often happen as queries get more complex and don't fit into ORM language model) then you can write raw SQL but still let the ORM do the heavy lifting so you can take advantage of those features, which is the majority justification for the ORM in the first place.

You are basically suggesting what the original post author is suggesting. The comment above provided a rebuttal to their argument and you replied by suggesting the same thing they originally rebutted to. Hence a circular argument.

Re: Python: Just Write SQL

#143

Post by siva7 »

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",…

badass mic drop. i will refer to your comment whenever i see this discussion coming up again and it will come up again as long as people are still learning the art of software development.

Re: Python: Just Write SQL

#144

Post by mrj »

boxed wrote: post #82

This is just reimplementing Djangos ORM, but badly. ORM queries compose . That's why [Python] programmers prefers them. You can create a QuerySet in Django, and then later add a filter, and then later another filter, and then later take a slice (pagination). This is hugely important for maintainable and composable code. Another thing that's great about Djangos ORM is that it's THIN. Very thin in fact. The entire impl…

This is what I came here to say.

For example, I'm working on an project now that long ago added a "sellable things" store that used plain sql. There are many, many stores like this one, but it did some logic to figure out what items are sellable and return the set. Easy, developer happy, ticket closed.

Some time later, it was needed to have "sellable items of a specific type." Well the "sellable things" store was too much to clone so the developer simply pulled all the sellables and filtered in memory. Hey it's Go so it's fast right?

This continued for a couple years and now I'm joining a project with a p99 of >15s. It would have been a natural fit to return a query set of sellable things and the other callers could further refine it however they wanted. Now I'm looking at a ball of logic that should have been in the database and it's beginning to break down at scale.

This article is just that pattern with syntax sugar. It will lead to sadness.

Re: Python: Just Write SQL

#145

Post by ansc »

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 using SQLAlchemy in my job, and have worked with Python for many years. Never have I seen a good case of someone using SQLAlchemy to hydrate objects from raw SQL queries. I’ll definitely admit — I have not gone out of my way to search for it. It seems that it is a common want to do this kind of 60% benefit ORM you speak of, but it’s definitely unclear to me how to pick those parts together with the daunting (and fantastic) piece SQLAlchemy is.

Re: Python: Just Write SQL

#146

Post by dagmx »

joaodlf wrote: post #136
dagmx wrote: post #122

Earlier quoted context omitted.

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 worked in a badly implemented project that left them with a bad experience and they blamed the pattern rather than the technology. Fair. But you then proceed to detail a personal experience on the other side of the spectrum: Badly written code, without an ORM, and how it was fixed by introducing an ORM. I think we can all agree t…

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 ORM just means I can diffuse that responsibility over multiple people more reliably.

Re: Python: Just Write SQL

#147

Post by ggregoire »

I've been using PugSQL to write SQL in Python [1].

With this package, you write the SQL inside SQL files so you can benefit from syntax highlighting, auto formatting, static analysis, etc. At the difference of writing strings of SQL inside Python files. I'm surprised this is not more popular.

[1] https://pugsql.org

Re: Python: Just Write SQL

#148

Post by Izkata »

OJFord wrote: post #124
agumonkey wrote: post #117

Earlier quoted context omitted.

how is it called in the docs ?

If I understand GP correctly, it's 'performing raw SQL queries', or specifically here, 'mapping query fields to model fields': https://docs.djangoproject.com/en/4.2/topics/db/sql/#mapping... Basically you call `.raw("...")` from some model's queryset, but there's no requirement you actually query that model's table at all. class SomeModel: name = models.CharField() class OtherModel: foobar = models.CharField() SomeMo…

Yes, but also it's been a long time since I've had any reason to do this, and had gotten "managed = False" mixed up with abstract classes. Abstract classes won't let you do this, but you probably want "managed = False" to prevent migrations from doing stuff in the database, if it's going to be a reporting-only query that doesn't have a backing table.

Also you need to return an "id" column since Django needs a primary key.

On the flipside, you can put that query in the database as a VIEW and point the model at it, also with "managed = False".

Re: Python: Just Write SQL

#149

Post by nicoburns »

DangitBobby wrote: post #141
nicoburns wrote: post #105

Earlier quoted context omitted.

Perhaps it depends what you're doing? IMO: .where('column_a', '=', 'value1') .and(q => q.isNull('column_b').orWhere('column_b', '=', 'value2'))) is a lot less readable than: WHERE column_a = 'value1' AND (column_b IS NULL OR column_b = 'value2')

In Django that would be .filter(column_a='value1', Q(column_b__isnull=True)|Q(column_b='value2')) And obviously you can use whatever indentations you like.

Ah, that's quite a bit nicer. You can't do that in JavaScript on two counts:

- No keyword arguments

- No operator overloading (so you can't override | to get the nice "or" syntax)

Re: Python: Just Write SQL

#150

Post by waffletower »

As a data engineer, the pattern the OP shares is very familiar. I find it much preferable to use of ORMs for wide variety of reasons. However, I view implementing with SQL as an antiquated problem rather than a pragmatic feature. The evolution of this pattern would be to integrate database querying into languages more directly and eliminate SQL entirely. While this could be achieved in Python, I find that a language like Clojure, via functional programming (FP) primitives and transducers, is a natural candidate, particularly for JVM implemented databases. Rather than encapsulating SQL via query building or ORM based APIs, an FP core could be integrated into database engines to allow, via transducers, complex native forms to be executed directly across database clusters. Apache Spark is an analog of this. In particular the Clojure project, powderkeg (https://github.com/HCADatalab/powderkeg), as an Apache Spark interface, demonstrates the potential of utilizing transducers in a database cluster context.
Post reply on HN