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

41–50 of 288 posts

Re: Python: Just Write SQL

#41

Post by chucke »

sergioisidoro wrote: post #22

I've used Rails AR and Django/SQL Alchemy orms, and the more I use it, the more I wish for a fusion of both. Django ORM is amazing for Schema management and migrations, but I dislike their query interfaces (using "filter" instead of "where"). I really like Rails AR way of lightly wrapping SQL, with a almost 1-1, and similar names, but does not have a migration manager - and there is always the chance that your schema…

Thea answer to your prayers already exists: http://sequel.jeremyevans.net/.

By far the best database toolkit (ORM, query builder, migration engine) I have seen for any programming language.

Re: Python: Just Write SQL

#42

Post by coldtea »

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…

What you describe just needs a query builder (e.g. in Java something like jOOQ), not necessarily an ORM.

Re: Python: Just Write SQL

#43

Post by TwentyPosts »

robertlagrant wrote: post #2

With SQLAlchemy, I come for the type checking. I stay for the Alembic migrations.

I feel this, sort of. I taught myself how to code by writing a Python bot (among other things), and eventually needed some sort of database handling to make things work. I decided on teaching myself basic SQLite, and just did it raw with `sqlite3`.

Currently 50% of my anxiety when it comes to my bot is related to database matters. There's no type checking so I gotta be careful when writing them, and stuff might blow up weirdly at runtime. Refactoring tables is also a major pain, or at least was until I (sort of) figured out a 'routine' of how to do it.

It's doable, and I assume that teaching myself some basic SQL and using it in production was a great learning experience, but once I'd reached the point where I was inventing database migration tooling from first principles and considering how to implement that, I realized that I probably just want to look at SQLAlchemy again.

Re: Python: Just Write SQL

#44

Post by aforwardslash »

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…

There are plenty of advantages of using a dataclass, being the most obvious the fact that behaves like a pure data object (aka it doesn't have underlying associated resources). Serialization/deserialization of data is dead simple, and a dataclass is a construct you can use as a data object when building 3-tier applications. Having pure data objects also gives way more flexibility when implementing cache strategies.

While this separation isn't common in the Django ecosystem, it is very common in enterprise application design (regardless of usage of an ORM). On complex applications, Django models are often a leaky abstraction (not only because the mentioned resource connection problem, but also issues like for relations they require the inclusion of the target model, it cannot be lazy-loaded; a good example is a nullable foreign key to an optional module that may or may not be loaded), and they actually behave like a variation of the ActiveRecord pattern, that mixes two different scopes - data and operation on data. In many cases this is ok, but in many others this is a problem.

I personally use a repository pattern, coupled with a query builder and something vaguely similar to dataclasses (its a bit more complex in the sense that the data object attribute name can be different from the database field name). It is basically an object mapper with a non-related repository class.

Re: Python: Just Write SQL

#45

Post by pydry »

noirscape wrote: post #23
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 main problem I've encountered with complaints surrounding ORMs usually tend to be the result of trying to overfit the ORM in a certain way. ORMs are, for the most part, good at the CRUD operations - that is to say, they easily translate SELECT, UPDATE, INSERT and DELETE operations between conventional class objects and database rows. Things they usually aren't very good at are when you start trying to do things t…

>Things they usually aren't very good at are when you start trying to do things that require a lot of optimization

I find this ends up being, like, 1 or 2% of queries. It's also very hard if not impossible to guess which queries will end up in that group.

You're better off building it with the ORM first and breaking out SQL later when you are trying to performance optimize.

There is also a small % of queries which use some feature of your database engine which the ORM won't support.

Re: Python: Just Write SQL

#46

Post by masklinn »

williamdclt wrote: post #33
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…

There’s a world between a query builder and an ORM. The point of ORMs isn’t to build queries, if that’s the only need might as well just use a query builder which is a lot more lightweight and doesn’t come with all the downsides of orms

The OP literally says to ignore query builders, not just ORMs. When they state “just write SQL” that’s their actual thesis.

Re: Python: Just Write SQL

#47

Post by masklinn »

coldtea wrote: post #42
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…

What you describe just needs a query builder (e.g. in Java something like jOOQ), not necessarily an ORM.

OK but TFA is not just against ORMs, it’s also against query builders. That’s what GP is replying to.

Re: Python: Just Write SQL

#48

Post by masklinn »

zero_shift wrote: post #39
mickeyp wrote: post #34

Earlier quoted context omitted.

I have decades of experience with databases and I happily use ORMs. You're conflating ORM with people who know nothing about databases. Why?

From my experience, ORMs allow folk who know nothing about databases to continue knowing nothing about databases

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.

Re: Python: Just Write SQL

#50

Post by aforwardslash »

m000 wrote: post #40
somat wrote: post #29

Earlier quoted context omitted.

sometimes the idea is that the database lives it's own life outside the application. Probably not the case here, but under that viewpoint the application is just one of perhaps many that access the data and as such creating tables, indexes, migrations and relations are none of it's business.

But it is the application's business. You may not be altering the database schema from your application, but you still need to make sure that its code is in-sync with it. This means that you will need extra tooling, and if you're DIYing you will need to write it yourself.

The effort is the same, regardless of the approach. If you're consuming a third-party database and the underlying schema changes, you'd have to patch your model definitions accordingly - or in the presented example, the dataclass definition. Creating code to dump dataclasses from a database is actually trivial.

In ORMS like Django, models are defined as code-first, not schema-first. Yeah, you can use inspectdb, but in any sufficiently complex application, odds are you need to add to the generated models any custom behaviors you already implemented, and verify all the names and whatnot, because data definition and operations on data are actually mixed in the same class. More often than not, if the change is profound (eg. imagine switching from reading a User model from the database to fetch it from an external service), you may have to refactor a large portion of your code due to the way it interacts with the model - eg. search operations won't be proxied via orm, but by using external service endpoints, etc. There is no free lunch. And don't even get me started on field names that differ on the database.

Post reply on HN