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

241–250 of 288 posts

Re: Python: Just Write SQL

#241

Post by hot_gril »

TX81Z wrote: post #239
hot_gril wrote: post #171

Earlier quoted context omitted.

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

I think that’s part of why I’m having trouble with this framing - it’s treating CRUD as the entire universe of why you’d need to connect to a database. Some of us do very intense compute in very large datasets, and ORM are not capable in those tasks. At all.

Doesn't even have to get very intense. A simple timeseries schema gets slow/annoying quickly with an ORM.

Re: Python: Just Write SQL

#242

Post by epgui »

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

> 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. Not to mention abstraction

That's actually the problem.

I'm sure you're aware that ORMs have this fundamental problem called the ORM impedance mismatch problem. AFAIK it remains unsolved to this day.

> You can use SQLAlchemy (which I wrote) and write all the SQL 100% yourself as literal strings

This is true, but it's like saying you can disassemble a Boeing 737 to build chopsticks. It's true but completely unnecessary.

Re: Python: Just Write SQL

#243

Post by Izkata »

squeaky-clean wrote: post #225
DangitBobby wrote: post #153

Earlier quoted context omitted.

I actually messed it up a little because I'm not sure you can mix positional and kwargs in filter, and you definitely can't use kwargs first. Still, the idea is there. In JS, theoretically you could design an API like .where({column_a: 1}, Q(column_b__isnull=True).or({column_b: 2})) Which really isn't bad IMO

It's been a bit since I've used Django, but I believe you can just swap the order so the kwarg comes last. filter( Q(column_b__isnull=True)|Q(column_b='value2'), column_a='value1', ) Or just turn it into another Q .filter(Q(column_a='value1'), Q(column_b__isnull=True)|Q(column_b='value2'))

The docs show both of those as working examples, but Q supports using & for "and", and can be combined in any way - you could also do this:

  .filter(Q(column_a='value1') & (Q(column_b__isnull=True) | Q(column_b='value2')))

Re: Python: Just Write SQL

#244

Post by Izkata »

nicoburns wrote: post #89
pydry wrote: post #86

Earlier quoted context omitted.

>But the problem wasn't that it couldn't be done with the ORM, but that the ORM code quickly became unreadable. This is the problem with not using an ORM. If you cut it out and move everything to parameterized SQL queries the SLOC explodes which massively inhibits readability as well as introducing bugs. If your issue with ORMs is just that you're familiar with SQL and you don't like how ORMs look then I think the is…

> If you cut it out and move everything to parameterized SQL queries the SLOC explodes My experience has been the opposite: that raw SQL queries end up much shorter (and consequently more readable) than the equivalent ORM code. The exception to that is INSERT/UPDATE queries, where I do tend to use some kind of ORM/query builder. I have used both, and I prefer raw SQL for anything beyond very simple queries.

Separate chain for a different Django example, it only natively supports joins on explicit foreign keys, but because it has that extra information in the model the syntax for using it is extremely compressed. Let's say you have a "Dog" table with a foreign key to "Owner", and "Owner" has a foreign key to "City". Getting all the dogs that live in New York would be:

   Dog.objects.filter(owner__city__name = 'New York')
The double-underscore follows the foreign keys until the last one, which is a field on the last model.

Re: Python: Just Write SQL

#245

Post by javcasas »

baq wrote: post #217
rav wrote: post #164

Earlier quoted context omitted.

It's fine advice - if you can type check your queries. My colleague wrote a mypy plugin for parsing SQL statements and doing type checking against a database schema file, which helps to identify typos and type errors early: https://github.com/antialize/py-mysql-type-plugin

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

Re: Python: Just Write SQL

#246

Post by ubercore »

TX81Z wrote: post #237
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…

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 pure data operations side of a DB that size, I agree ORM isn't it.

Re: Python: Just Write SQL

#247

Post by monomers »

impulser_ wrote: post #234

If you want to try out something cool, check out https://github.com/sqlc-dev/sqlc It's written in Go and it converts your sql migrations and queries into typesafe code that you use access your database. It currently has a plugin for Python that's in Beta, but what essentially does something similar to what this post is saying. https://github.com/sqlc-dev/sqlc-gen-python You write your migrations, and queries and a co…

There's a whole family of libraries like that. Yesql is the first I became aware of. The repo has an (incomplete) list of ports to other languages: https://github.com/krisajenkins/yesql#other-languages

Re: Python: Just Write SQL

#248

Post by pharmakom »

I no longer feel the need for an ORM. Here's what I do instead:

- immutable record type for each table in the database (could be a data class)

- functions for manipulating the tables that accept or return the immutable record types and directly use SQL

- that's it

you can generate the functions from the database schema if its too much boilerplate.

Re: Python: Just Write SQL

#249

Post by zzzeek »

BeefWellington wrote: post #158
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",…

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 the code deal with generating SQL and what parts deal with all the rest. 40% is likely a large overestimate.

Re: Python: Just Write SQL

#250

Post by OJFord »

Izkata wrote: post #148
OJFord wrote: post #124

Earlier quoted context omitted.

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…

Oh yes I forgot about that, that can be annoying. But of course, when it's annoying because it doesn't matter, it doesn't matter and you can pass anything back `as id`.
Post reply on HN