Ask HN: Python and ORM's? | Hacker News Reader

Ask HN: Python and ORM's?

3 pointstgriesser2 comments

Inspired by some recent posts on HN, I am attempting to try a few projects in Python, moving over from PHP.

One thing that I'm noticing as I'm browsing through the docs for different frameworks, Django, Pylons, and Turbo Gears is that one of the big features that they mention is the support of ORM's out of the box.

I have never really used any ORM's in PHP... I tried one once and I found it to be too cumbersome, seemed like a lot of work defining database tables in a particular fashion when some simple queries would do the trick.

I didn't see any benefit, other than turning everything into an object which I guess could fit some cases, but never seemed to benefit projects I was working with. I'd also read that too much reliance on ORM's could be a problem, and sometimes were considerably less efficient when raw SQL would do the trick. I may be completely missing the boat though.

I guess I'm wondering what the benefits of using ORM's are in Python (or PHP) frameworks in comparison to raw SQL, how and in what situations are they best suited, how much of a time saver is it really, and whether anyone uses raw SQL as their primary means within these frameworks.

They can be useful if you need database independence. In Django it is literally a one line code change to swap out the DB you are using.

Sure you can do the same thing with raw SQL but then you have deal with result sets, pulling data back out into objects etc. Why not automate that? ORMs still supply the ability to throw raw SQL when necessary.

I like the fact that I have one set of data objects to deal with and that they are in my language of choice.

The benefit of an ORM (apart from database independence) is that it lets you use the database as an object, something more natural to the particular programming language than raw SQL queries.

While on smaller scale projects ORMs might seem like an overkill, once you're dealing with a more complex setup (not neccessarily complex on the database level, but on the application level), you will want something that makes the database easily accessible in a consistent way.

Consistency is another key point. If you write raw SQL, there will be different queries that do similar things all over the place, unless you collect them into a wrapper class of some sort. At which point, you laid the foundations of your DIY ORM.

It is less efficient than hand-optimised SQL in most cases, but on the up side, ORMs often make the application development easier and faster, which, in my opinion, is a good trade off.

To name an example... Lets say I have a table of Authors, and another with Books, and I want to retrieve a list of books by a particular author, and display that in a template.

Without an ORM, I'd write the SQL, stuff the results into some variable the template can then process, and display it. With an ORM, I the query and stuffing into a variable part is done by the ORM. I get back a result object with clearly defined properties, which is easy to display.

In SQL, I'd do a 'SELECT * FROM Books WHERE author_id = (SELECT id FROM Authors WHERE name = "Famous Guy")', and stuff the results into a simple table, on which the template can iterate over.

In - say - Django ORM, that'd be:

books_by_author = Book.objects.filter (author__name = 'Famous Guy')

Simpler, isn't it?

And once you want to add pagination, displaying 10 results per page, you will have to tweak your SQL to add LIMIT support. With - again, with Django - a good ORM, which does lazy evaluation, you can just do:

books = Books.objects.filter (author__name = 'Famous Guy')[0:10]

Or something similar, and it will Do The Right Thing.

This is why I love ORMs, they already implemented all the neat tricks I want.

Reply on news.ycombinator.com