[Baypiggies] Python ORM?

[daveg]

On Thu, Feb 16, 2006 at 11:32:44PM -0800, Laurence Clark wrote:
> So what is the Python version of Hibernate? Or is this the wrong
> question? Do serious Python users believe in relational databases? If
> not what is the preferred persistence solution for a midsize data model
> -- the kind of thing that would use a hundred tables or so in an old
> fashioned client-server database application with a normalized
> relational database.

As a sometime dba and sometime python purist, I tend to dislike ORMs. They
either run out of power or lead to patterns that fail to take advantage of
what relational databases are good at, or become so complex that it is
difficult to predict what they will do. Notice that all of them let you
escape to writing your own SQL, a hint if ever there was one.

Usually people seek more transparency and simplicity in code, but when
it comes to ORMs some want a whole complicated layer of obsfucatory
library filled with hidden metadata and covert code generators. It is
almost as if SQL was all icky somehow, something to be ashamed of,
covered up and hidden.

Ok, the truth is, SQL is all icky. And historically relational databases were
stuffed down our gullets by the likes of IBM and Oracle. Still the failure of
ANY of the proposed alternatives over the last quarter century must signify
something. Probably that everything else is either ickier or simply
insufficient, or even that SQL systems are the best tool we have for certain
problems.

SQL is about sets. To use it effectively you want to express as much of
your processing as possible in terms of sets and operations on sets.
Instead of writing python to do something to one object, write SQL to do it
to all of the objects at once. The more work you can force the SQL engine to
do in batches and the less interaction with it you have, the better. To do
this effectively, I think you need to understand SQL and have good control
of the queries you write.

One specific problem with ORMs is that they encourage "one at a time"
thinking:

    I have a customer Object, I'll just instantiate it here with a call
    to the ORM ... now I'll have a loop to call the ORM to instantiate all
    the Order objects... 

Which is just a relational join that ends up hand coded in python ignoring
25 years of industrial R & D on how to process joins efficiently.

Think of Python as a car or truck and SQL as a freight train. The train only
goes certain places and its all rusty and ugly, but if you have ten million
tons of coal to move across the country it beats a million separate truck
trips. And (to be completely unfair to ORMs) it really really beats dividing
the coal up onto a million trains painted up to look like a truck.

SQL is your friend, don't hide from it, learn to love it.

-dg


--
David Gould                      daveg at sonic.net
If simplicity worked, the world would be overrun with insects.