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

171–180 of 288 posts

Re: Python: Just Write SQL

#171

Post by hot_gril »

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

> writing SQL for CRUD is really repetitive and tedious

If you think of relational DBs as just CRUD machines, an ORM makes total sense, but that's the original mistake.

Re: Python: Just Write SQL

#172

Post by tracker1 »

zknill wrote: post #67

Seems like there's 3 groups of opinions on ORMs: Firstly (1); "I want to use the ORM for everything (table definitions, indexes, and queries)" Then second (2), on the other extreme: "I don't want an ORM, I want to do everything myself, all the SQL and reading the data into objects". Then thirdly (3) the middle ground: "I want the ORM to do the boring reading/writing data between the database and the code's objects".…

I'm pretty firmly in #2... it's relatively straight forward in a scripting language, and easy enough with something like C# with Dapper. In the end ORMs tend to over-consume, and often poorly. And even when they don't in most cases, they start to in more difficult cases. That doesn't even get into the amount of boilerplate for ORMs. You have to buy in to far more than their query model(s).

Re: Python: Just Write SQL

#173

Post by radus »

Izkata wrote: post #98
agumonkey wrote: post #64

Earlier quoted context omitted.

I realized too late that objects, as of now, are not capable of synthetizing a new class/type based on joins.

Django lets you define an abstract model for the resulting set of columns, then you can use raw SQL on that model to get something that looks like a normal model to the rest of the code. As long as the raw query has the right number of columns, and of the right data type, Django doesn't care how it's populated. Then you can just stick the query behind a classmethod on the abstract model so you don't have to worry abo…

I occasionally do this with views managed by https://github.com/xelixdev/django-pgviews-redux/.

Re: Python: Just Write SQL

#174

Post by tracker1 »

NewEntryHN wrote: post #27

Any serious application beyond the example given in this article will include conditional SQL constructs which go beyond SQL query parameters and will therefore require string formatting to build the SQL. Think a simple UI switch to sort some result either ascending or descending, which will require you format either an `ASC` or a `DESC` in your SQL string. The moment you build SQL with string formatting is the momen…

Depends on the abstraction... for example .Net's extensions for LINQ are pretty good at this, I haven't generally used the LINQ syntax, but the abstraction for query constructs are pretty good, combined with Entity Framework. Of course, there's a lot that I don't care for and would prefer Dapper. In the end, the general environment of .Net dev being excessively "enterprisey" has kept me at bay the past several years.

Re: Python: Just Write SQL

#175

Post by radus »

davidthewatson wrote: post #71

I'm happy to see someone mention peewee, having used it for numerous startup prototypes since its inception. Coleifer does not get enough credit IMHO: https://github.com/coleifer/ Peewee has been solid since I began using it a decade ago. Coleifer's stewardship is hard to see at once, but I've interacted with him numerous times back then and the software reflects the mindset of its creator.

Peewee is excellent! I've especially enjoyed using it with SQLite - there are a number of handy extensions and very good support for user defined functions.

Re: Python: Just Write SQL

#176

Post by tracker1 »

felipetrz wrote: post #109
baq wrote: post #93

Earlier quoted context omitted.

Last project we've explicitly decided to not have any stored procedures ever since you basically can't test nor deploy them in any sane way. I'm all ears how you make it work.

You can test them very easily by using database containers.

The setup/teardown, and even working with schema migrations can get complex, and potentially excessively so for the benefit of doing everything in SPs. Also, the developer experience and discoverability are definitely less than ideal.

Re: Python: Just Write SQL

#177

Post by hot_gril »

You don't need an ORM, but this isn't how you avoid one. If you're thinking of your DB as a mere object store / OOP connector like this article is, you're better off with an ORM or NoSQL than this basically equivalent DIY solution. It's best to instead learn how to use a relational DB like a relational DB, and the rest will follow.

Also, I'm not one of those people who dislike easy things (and will often whine about JS or Python existing). I'm all for ease and focusing on the business goals. It's just that ORMs and bad schema design will make things harder.

Re: Python: Just Write SQL

#178

Post by tracker1 »

zero_shift wrote: post #80
masklinn wrote: post #48

Earlier quoted context omitted.

And from my experience, forcing people who know nothing about databases to write SQL will not make them learn about databases, all you end up with is worse SQL and more injections. Although the worse offenders by far are those which decide ORM = bad and bypass it at every opportunity.

That's fair, ORMs can be slow but they rule out a whole category of basic mistakes

And introduce plenty more. over-fetching, unindexed joins across many tables being two of the more common.

Re: Python: Just Write SQL

#179

Post by tantaman »

ruuda wrote: post #170

One challenge working with SQL from statically typed languages (including Python + Mypy) is that you have to convert the query inputs/outputs to/from types and it's a lot of boilerplate. I started an experiment to generate this from annotated queries. [1] Python support is still incomplete, but I'm using it somewhat successfully for using SQLite from Rust so far. [1]: https://github.com/ruuda/squiller

similar project that generates types for queries into an intermediate representation that can be consumed by, say TypeScript, to get static Types: https://github.com/vlcn-io/typed-sql

Re: Python: Just Write SQL

#180

Post by tantaman »

zknill wrote: post #67

Seems like there's 3 groups of opinions on ORMs: Firstly (1); "I want to use the ORM for everything (table definitions, indexes, and queries)" Then second (2), on the other extreme: "I don't want an ORM, I want to do everything myself, all the SQL and reading the data into objects". Then thirdly (3) the middle ground: "I want the ORM to do the boring reading/writing data between the database and the code's objects".…

I'm in a 4th camp: we should be writing our applications against a relational data model and _not_ marshaling query results into and out of Objects at all.

Elaborations on this approach:

- https://news.ycombinator.com/item?id=34948816

- https://github.com/papers-we-love/papers-we-love/blob/main/d...

- https://riffle.systems/essays/prelude/

Post reply on HN