Mailing List Archive

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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.

Modifying the database schema (adding a new field) requires usually
changes in tons of SQL methods..updating your ORM schema definition
usually takes only one or a few lines of code.

Or why the hell do you want to write complicated SQL code (I
remember legacy code of a co-worker to tons of complex JOINs)
yourself while you could implement the same functionality in much
nicer way using Python?

If you ask me: writing SQL is for masochists.

- -aj
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQGUBAEBAgAGBQJMdOvsAAoJEADcfz7u4AZjqyALwNWRbc5Pz3keM2x1wkDQfwfU
WdtHLdNvLO6jIRMadMjlO4lOgPU0/ANIlkrUFgmstiEkpEt4zj8IIBWYZfdkrryo
6Cb5OeYN+/stFURDSdqGPViJYxybbPFd2pmcX5HW9W9msxIsZsC7mmjLWgUWQEY4
XXZBLbSTWB3DQt1gOXXNrntPzuSopad6QUb/Pqb/JWF7dtE3WYFY2/ar0YWOd/iU
I7K3rKSTnQaBiDBwFrdrm/hHgeA34zlCs4M0qb8tWKCLsafwYNojAxdF28ScLSBD
pHbxmcQY3aRqkHFPC1DZLqJNmMqbDitIpeUqgx6oLIc73trk2znRmT/HHFP+UhWU
K72Qs6fBfEu1dvJYCA2iKFk2aE83ckqbdQnpwYtL0S3pVY3V5s1Z1ZQ4T1rz4+19
BnuClXsZEWxfn8nn3uNZ0kpv9RI3Tw2qRgd4L5vk1s/mCFCRUi1riCKkEb/U8ygI
kiJw/Zv6hJrgY74ZA67sfaymJ6hrSfE=
=JGcb
-----END PGP SIGNATURE-----

Hmmm..If the database was modeled properly in the first place if
wouldn't be referenced in many different places therefore it would not
require changes in tons of SQL methods.

SQL code isn't complicated, it does exactly what it says on the tin!
(most of the time :)). ORM encourages lazy programming = problems later
down the line.


On 25/08/10 11:09, Andreas Jung wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> 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.
>>
> Modifying the database schema (adding a new field) requires usually
> changes in tons of SQL methods..updating your ORM schema definition
> usually takes only one or a few lines of code.
>
> Or why the hell do you want to write complicated SQL code (I
> remember legacy code of a co-worker to tons of complex JOINs)
> yourself while you could implement the same functionality in much
> nicer way using Python?
>
> If you ask me: writing SQL is for masochists.
>
> - -aj
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (Darwin)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iQGUBAEBAgAGBQJMdOvsAAoJEADcfz7u4AZjqyALwNWRbc5Pz3keM2x1wkDQfwfU
> WdtHLdNvLO6jIRMadMjlO4lOgPU0/ANIlkrUFgmstiEkpEt4zj8IIBWYZfdkrryo
> 6Cb5OeYN+/stFURDSdqGPViJYxybbPFd2pmcX5HW9W9msxIsZsC7mmjLWgUWQEY4
> XXZBLbSTWB3DQt1gOXXNrntPzuSopad6QUb/Pqb/JWF7dtE3WYFY2/ar0YWOd/iU
> I7K3rKSTnQaBiDBwFrdrm/hHgeA34zlCs4M0qb8tWKCLsafwYNojAxdF28ScLSBD
> pHbxmcQY3aRqkHFPC1DZLqJNmMqbDitIpeUqgx6oLIc73trk2znRmT/HHFP+UhWU
> K72Qs6fBfEu1dvJYCA2iKFk2aE83ckqbdQnpwYtL0S3pVY3V5s1Z1ZQ4T1rz4+19
> BnuClXsZEWxfn8nn3uNZ0kpv9RI3Tw2qRgd4L5vk1s/mCFCRUi1riCKkEb/U8ygI
> kiJw/Zv6hJrgY74ZA67sfaymJ6hrSfE=
> =JGcb
> -----END PGP SIGNATURE-----
>

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
https://mail.zope.org/mailman/listinfo/zope-db

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Richard Harley wrote:
> Hmmm..If the database was modeled properly in the first place if
> wouldn't be referenced in many different places therefore it would not
> require changes in tons of SQL methods.

Perhaps you haven't work with large installation using SQL heavily?
After having ported a project with roughly 200 ZSQL methods to
Sqlalchemy: it was a *huge* win working with the backend on the Python
level and dealing with database entities like Python objects.

>
> SQL code isn't complicated, it does exactly what it says on the tin!
> (most of the time :)).

SQL can become *very complicated* - ever tried to understand complex
SQL queries of other people? Working with a proper database model
on the Python level is much easier. Yes, you are moving complexity from
SQL to Python and ORM configuration can be confusing as well. However
a descriptive model of a database in Python with its relations is much
easier to handle than dealing with it on the SQL layer directly.

> ORM encourages lazy programming = problems later
> down the line.

Nonsense - using SQLAlchemy for more than four years now in four or five
larger projects and it is a major win regarding productivity, software
architecture, implementation and testing.

- -aj


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQGUBAEBAgAGBQJMdPPnAAoJEADcfz7u4AZj6dULv13bzwtodOh1mM0hdl/OamKE
zBlXNMbcKWlZ0pFErFGWuhCQDjgFBTI5nXuNE/py8Ckmzdzrss2Opk1fselyv6qT
AC/++/FX/Zv5HA7yzVKeBGJ88KIoc/XcFm/5syQrbik67mSCtJcQyjoIOrjGXIy/
HmlbWzRYOYM7hORTkWEmsjPFEjlNfQFUx01CkAstkOp2nAsQe0LH2X9N2gs6BI1H
IORI0u0GkNPvkwXJTgHEoR62bPmrWS+MANdLORQI62G7Rm1WG8cZ/jMzUH6yT0r6
p0bGnca2eux3JyvBSm4p+8Ap64zEbjxhB/Iww5qzojSY+xgZmG0DUJKUAMeXMYZU
dd5es9CmoDRNsMjDfBsnciSRcHw9S+T/eRdcf8YEiUPkeSC1P/4RIbgk7vIWnyef
KtlNYoHCVL8IUIYAGOJNytiTp1Oxg9Q581KM2auFZpUSlZBLafovqGvJSCudxYbs
SlSiXx7aI6uv5vPLuOjMF/ZXPvfKp6E=
=Lrga
-----END PGP SIGNATURE-----

Hi,

I think the discussion is always useful but prefer it to be less polemic.

Am 25.08.10 12:43, schrieb Andreas Jung:

> SQL can become *very complicated* - ever tried to understand complex
> SQL queries of other people? Working with a proper database model
> on the Python level is much easier.

When mapping classes to relations, sure. OTOH. I have no problem
understanding properly modelled relational schema. As Chris Date points
out "a relation is an object".

I'm not a big fan of SQL per se - I think it manages to make a travesty
of the declarative approach and makes some things which should be easy
tricky. Things are improving with things like windowing functions.

> Yes, you are moving complexity from
> SQL to Python and ORM configuration can be confusing as well. However
> a descriptive model of a database in Python with its relations is much
> easier to handle than dealing with it on the SQL layer directly.

Depending on how the "mapping" (it's actually the generation of SQL on
the fly) happens this can lead to extremely weird modelling. The SA
modelling of subclasses is weird, incorrect in my view. At Europython
Hannu Krosing showed some stats of direct access over the active record
approach. From what I recall of Trac and Django the iterating over large
sets can indeed present a bottleneck which only a good SQL query can remove.

>> > ORM encourages lazy programming = problems later
>> > down the line.

> Nonsense - using SQLAlchemy for more than four years now in four or five
> larger projects and it is a major win regarding productivity, software
> architecture, implementation and testing.

I think SA has the advantage over some approaches in that it lets you do
all the schema in the database, gives you some control over whether
queries should be lazy or not, etc. and letting you write handcrafted
SQL when necessary (it's a great convenience not to have to write them
for getters and setters).

Nevertheless, any programmer working with a relational database should
have a good understanding of the relational model and the core SQL
constructs.

Furthermore, in my view any good Python programmer should be prepared to
look at other approaches; indeed, I think this is a core advantage of
Python. The Muldis-D project (which is based on the Tutorial D pseudo
language) and Hannu's proposed DORA both offer significant advantages
over SQL and, therefore, SQL generation.

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> >>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> >>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
https://mail.zope.org/mailman/listinfo/zope-db

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/
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
https://mail.zope.org/mailman/listinfo/zope-db

M.-A. Lemburg wrote:
> I'd suggest to use a Python class as abstraction layer

Sounds like an ORM to me ;-)

Seriously, SQLAlchemy lets you do all of the things you're worried
about, performance and flexibility wise, as well as map your sql onto
objects (or not, if you don't actually want objects!) with ease...

Hmm, I guess what I'm actually saying is that ORM or not is one choice,
either way, I'd *strongly* recommend using SQLAlchemy rather than trying
to roll your own abstraction layer of any sort.

cheers,

Chris

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
https://mail.zope.org/mailman/listinfo/zope-db

Chris Withers wrote:
> M.-A. Lemburg wrote:
>> I'd suggest to use a Python class as abstraction layer
>
> Sounds like an ORM to me ;-)

No, there's no "object-relational mapping" being done, only
interfacing between the data used by the application and the
schema you have in the database.

As nice side-effect, you can also add business logic to the
abstraction layer, optimize queries to partly run in Python
and in SQL, reorder your SQL to tweak the database optimizer into
writing good access plans, etc.

> Seriously, SQLAlchemy lets you do all of the things you're worried
> about, performance and flexibility wise, as well as map your sql onto
> objects (or not, if you don't actually want objects!) with ease...
>
> Hmm, I guess what I'm actually saying is that ORM or not is one choice,
> either way, I'd *strongly* recommend using SQLAlchemy rather than trying
> to roll your own abstraction layer of any sort.

If you want yet another abstraction layer between your code
and the database, that's the way to go, yes :-)

SA does provides a general abstraction layer, not the application
specific one I was talking about.

--
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/
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
https://mail.zope.org/mailman/listinfo/zope-db

M.-A. Lemburg wrote:
> If you want yet another abstraction layer between your code
> and the database, that's the way to go, yes :-)
>
> SA does provides a general abstraction layer, not the application
> specific one I was talking about.

To be honest, I'd prefer to use a powerful, lean, well tested
abstraction layer that's tested by a lot more people than just me rather
than hacking up a specific abstraction layer on each and every project ;-)

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
https://mail.zope.org/mailman/listinfo/zope-db

Chris Withers wrote:
> M.-A. Lemburg wrote:
>> If you want yet another abstraction layer between your code
>> and the database, that's the way to go, yes :-)
>>
>> SA does provides a general abstraction layer, not the application
>> specific one I was talking about.
>
> To be honest, I'd prefer to use a powerful, lean, well tested
> abstraction layer that's tested by a lot more people than just me rather
> than hacking up a specific abstraction layer on each and every project ;-)

That's not what I'm talking about. Each application has its own
database requirements and it's common practice to centralize this
functionality into an application specific database abstraction layer.

You can, of course, have this layer use SA to add another layer
of abstraction between the application database layer and the database,
if you feel uncomfortable with SQL, or want to abstract the queries
from their database specific SQL implementation, e.g. in order to
support more than one database backend.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Aug 27 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/
________________________________________________________________________
2010-08-19: Released mxODBC 3.1.0 http://python.egenix.com/
2010-09-15: DZUG Tagung, Dresden, Germany 18 days to go

::: 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/
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
https://mail.zope.org/mailman/listinfo/zope-db