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

181–190 of 288 posts

Re: Python: Just Write SQL

#181

Post by drdaeman »

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…

IMHO the real proper solution is to have an SQL parser, so you can have your SQL represented as an AST, do some operations on it, then compile it back to a query.

Sadly, I'm not aware of any good solutions to this. SQLAlchemy Core can build an operates on an AST, but it doesn't parse raw SQL into a query (so one has to write their queries in Python, not SQL). Some parser libraries I've seen were able to parse the query but didn't have much in terms of manipulations and compiling it back.

Re: Python: Just Write SQL

#182

Post by waffletower »

dagmx wrote: post #159
waffletower wrote: post #154

Earlier quoted context omitted.

Enough people have had bad experiences with ORMs who have decided to evolve and find alternatives. ORMs are bulky and do not provide value to many modern functional, data-first development paradigms.

Why speak in absolutes? Swift data for example is a data first ORM https://developer.apple.com/documentation/SwiftData Nobody is saying only use an ORM, but the anti-ORM crowd seem to think that there’s only one true way to do things. I don’t know why so many programmers think purely in binary “good or bad”. Different projects may have different requirements.

Why hear in absolutes? I said essentially what you did: "Different projects may have different requirements"

You find ORMs useful, I do not, they aren't mutually exclusive.

Also, An ORM is inappropriate for certain forms of inter-team collaboration. Data scientists often prepare SQL that we need to integrate into our services. Asking all at a company to learn a language specific ORM isn't practical. SQL is, for better and worse, an important common tool.

Re: Python: Just Write SQL

#183

Post by tracker1 »

joaodlf wrote: post #32
bbojan wrote: post #18

The article is missing the code for creating the "users" database table. What about indexes? Migrations? Relations to other tables? I mean you can just write SQL instead of using the ORM if your project consists of a single table with no indexes that will never change, sure.

When it comes to migrations, I've been fine with https://github.com/golang-migrate/migrate There are a multitude of extra things to consider, but none of those things are, in my opinion, imperative to having success with SQL in Python. Will it be hard to achieve the same level of convenience that modern ORMs provide? Absolutely. But there is always a cost. I firmly believe that for most projects (especially in the ag…

Agreed, I've seen plenty of what wind up being very byzantine and complex migration strategies over the years, and in the end simple SQL scripts tends to work the best. I will note, that it's sometimes easier to do a DB dump for the likes of sprocs, functions, etc, if you want the "current" database bits to search through.

Re: Python: Just Write SQL

#184

Post by tantaman »

I was sad to find that the author proclaimed "just write SQL" then fell into the trap of modeling his data as objects.

If you're going to model your data this way... you might as well use an ORM.

A better way is to just just write SQL (or datalog) and model our data, from DB all the way up to the application, as relations rather than objects.

Rather than re-hash, this idea has previously been discussed on HN here: https://news.ycombinator.com/item?id=34948816

Re: Python: Just Write SQL

#185

Post by waffletower »

m000 wrote: post #37
bbojan wrote: post #18

The article is missing the code for creating the "users" database table. What about indexes? Migrations? Relations to other tables? I mean you can just write SQL instead of using the ORM if your project consists of a single table with no indexes that will never change, sure.

And good luck with writing tests for your sql code.

I really dislike SQL, but recognize its importance for many organizations. I also understand that SQL is definitely testable, particularly if managed by environments such as DBT (https://github.com/dbt-labs/dbt-core). Those who arrived here with preference to python will note that dbt is largely implemented in python, adds Jinja macros and iterative forms to SQL, and adds code testing capabilities. No ORM required whatsoever.

Re: Python: Just Write SQL

#186

Post by stuaxo »

Django isn't just about the ability to programmatically stick together things to make your query or the migrations. It's that, combined with tools that help you debug database issues, and most importantly the patterns that it imposes.

As a Django developer it's straightforward to go from one Django project to another, which isn't the case with other stuff as you don't know where everything is going to be.

Re: Python: Just Write SQL

#187

Post by Too »

ilovetux wrote: post #72
rtpg wrote: post #7

If you're going to end up querying all the fields and putting them into a model like this dataclass anyways... Django can do that for you. If you're going to later pick and choose what fields you query on the first load, and defer other data til later.... Django can do that for you. If you're going to have some foreign relations you want to easily query.... Django can do that for you. If you're doing a bunch of joins…

The only problem I have with using the django ORM is that it relies on the django project structure. While there are ways to use the ORM independently, they are full of hacks and trade-offs. Granted, this problem goes away if you are building a web app or a REST API, but if I just want an ORM for a command line application, I am using django's management command functionality which is OK, but it doesn't really scale…

Yuck indeed. The way it dictates the order of imports, forcing you to import settings before any models can be imported, is reason enough not to use it. This problem spreads to any of your other files, leaving you in the end with everything depending on being launched in a full Django context. Shame, given it’s otherwise very user friendly.

Re: Python: Just Write SQL

#188

Post by lijok »

baq wrote: post #165
lijok wrote: post #52

Earlier quoted context omitted.

> I am curious as to what a larger codebase with "just SQL queries all over" ends up looking like. I have to imagine they all end up with some (granted, specialized) query builder pattern. But I think my bias is influenced by always working on software where there are just so many columns per table that it would be way too much busywork to not use something. You end up writing a query per usecase, rather than writing…

Have this right now. Fortunately no custom query builder. It's enough of a hell as is. Cleanup will take years.

What problems are you finding with it?

Re: Python: Just Write SQL

#189

Post by boxed »

nicoburns wrote: post #88
boxed wrote: post #83

Earlier quoted context omitted.

Sounds like just begging for SQL injection attacks.

Values were still provided separately. The string-interpolated SQL would include a placeholder just like static SQL does. That's pretty easy to audit for in code review: no variables in interpolated code.

That makes no sense. What are you interpolating? Some variable. And you now have to audit that THAT VARIABLE is safe.

Re: Python: Just Write SQL

#190

Post by baq »

lijok wrote: post #188
baq wrote: post #165

Earlier quoted context omitted.

Have this right now. Fortunately no custom query builder. It's enough of a hell as is. Cleanup will take years.

What problems are you finding with it?

Injection potential through the roof, copypasta galore, refactoring the same join pattern in a hundred different queries gets old rather fast… the usual suspects.
Post reply on HN