[Zope-DB] ORMs (Zope-DB Digest, Vol 72, Issue 1)

[M.-A. Lemburg]

Richard Harley wrote:
> Please can someone explain why ORM is better than speaking directly to 
> the database? Isn't this just  adding another layer of complexity which 
> in huge databases is certainly not needed.

It really all depends on what your needs are. This is my
experience:

If you just need to work with a simple database schema, change
schemas frequently, have straight-forward queries and don't
like or know SQL, you're probably better off with an ORM hiding SQL
away from you.

If you're working with more complex schemas and queries, need to
care about execution performance, want to use advanced database
techniques and can manage SQL (which really isn't all that difficult),
then you're better off writing straight SQL.

As for managing SQL statements and queries, this is usually best
done using an application specific thin abstraction layer. That
way you keep the queries in one place and can easily make
changes, if required.

Whether ZSQLMethods are a good way of implementing such a layer
is questionable - having those methods in the ZODB makes changes
difficult to implement. OTOH, they are well integrated into
Zope's object database and very easy to setup and use.

I'd suggest to use a Python class as abstraction layer and have
that use a Zope database connection object(s) for executing and
running the queries within a standard Zope transaction.

This approach then also lets you benefit from the better performance
and more security you get from using standard DB-API binding
parameters. ZSQLMethods only support inlining parameters into
the SQL statements which can result in SQL injection problems
as well as slower performance due to the fact that you're making
it harder for the database to cache access plans.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Aug 26 2010)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/