Webware for Python

Huy wrote:
> IMO Python does not have a powerful enough ORM. Although most of these 
> solutons provide the simple selects, updates, inserts and joins none of 
> them provide good handling of legacy databases or flexibility of using 
> the sql of your choosing (which I think is crucial, there is no point 
> doing certain things one way and other things another way).

I think in all of them you can use your own SQL, to a degree.  In 
SQLObject the limit is usually in *what* you select, not *how* you 
select it, so everything after WHERE is up for grabs.

> The only ORM tool I know of which provides a good solution to ORM is a 
> java product called ibatis sqlmaps. Any python ORM would do well to have 
> a similar design.
> 
> In fact I rolled my own ORM for a client which provides pretty much the 
> same as the ORMs above (although I think it's nicer because it has a 
> beautiful metadata model which can be accessed for generating guis, code 
> etc. and has not restriction of database schema, can handle multi key 
> joins) and after using it for a while I still prefer ibatis for more 
> complex applications.
> 
> I think any ORM which enforces a database design (eg. key has to be 
> integer id etc.) is of no use except for the simplest applications.

Well, to the degree that ORMs imply certain conventions, they are 
usually good conventions, and if you are starting from scratch or have 
the ability to refactor your database then those conventions aren't that 
burdensome.  And in most cases (at least for SQLObject) there are ways 
to deal with legacy databases, or ways to create a heterogeneous system 
where you use an ORM for some tables, and custom SQL for other situations.

If you are thinking in terms of heavily relational queries -- lots of 
joins, subselects, aggregating, etc -- an ORM generally will feel 
clunky.  Many relational concepts don't map well to Python ORMs.  Some 
of those concepts could be represented in an ORM (but just aren't 
currently), and some are very difficult to represent.

Looking just briefly at sqlmaps, it looks like a thin object interface 
on top of custom SQL queries.  I'd find that tedious, but if you are 
going to write custom SQL then I'm sure that's a helpful way to organize 
the SQL.  It's a lot different from Python ORMs, and I think I'd find it 
a bit clunky.  In a way it almost reminds me of Zope's Z SQL Methods, 
which I hate.

-- 
Ian Bicking  /  ianb@...  / http://blog.ianbicking.org

Ian Bicking wrote:
> Huy wrote:
> 
>> IMO Python does not have a powerful enough ORM. Although most of these 
>> solutons provide the simple selects, updates, inserts and joins none 
>> of them provide good handling of legacy databases or flexibility of 
>> using the sql of your choosing (which I think is crucial, there is no 
>> point doing certain things one way and other things another way).
> 
> 
> I think in all of them you can use your own SQL, to a degree.  In 
> SQLObject the limit is usually in *what* you select, not *how* you 
> select it, so everything after WHERE is up for grabs.

I don't know why most ORM try and hide the "select * from table"  and 
leave the where configurable. It's then worst if they then use a special 
syntax to emulate SQL for the where expressions rather then just plain 
SQL, especially when the special syntax doesn't even deal with anything 
remotely complex i.e just simple "and", "or" type things.

I don't know about others, but I find myself needing the full power of 
SQL; functions, grouping, ordering, subselects etc. and I want my ORM 
solution to provide a simple, consistent and explicit interface between 
my database data/interaction and my application domain objects. I really 
don't like doing certain things one way and other things another way. 
Isn't this the python way ?

>> The only ORM tool I know of which provides a good solution to ORM is a 
>> java product called ibatis sqlmaps. Any python ORM would do well to 
>> have a similar design.
>>
>> In fact I rolled my own ORM for a client which provides pretty much 
>> the same as the ORMs above (although I think it's nicer because it has 
>> a beautiful metadata model which can be accessed for generating guis, 
>> code etc. and has not restriction of database schema, can handle multi 
>> key joins) and after using it for a while I still prefer ibatis for 
>> more complex applications.
>>
>> I think any ORM which enforces a database design (eg. key has to be 
>> integer id etc.) is of no use except for the simplest applications.
> 
> 
> Well, to the degree that ORMs imply certain conventions, they are 
> usually good conventions, and if you are starting from scratch or have 
> the ability to refactor your database then those conventions aren't that 
> burdensome.  

Thats a great many "ifs" there. It's true that I would love to redesign 
many of the databases I get to work with but almost 90% of the time it 
is impossible due to the massive amount of legacy applications still 
relying on it. True for start from scratch applications (which usually 
start off very simple) ORMs like sqlobject can help you get up and 
running quickly but I'm not so sure what they can do for you once you 
get into harder stuff which are straight CRUD operations.

On the other hand, I would hate for any ORM to dictate the way I design 
my databases. The principle of the thing just doesn't make sense. I use 
an ORM to help me map my database to my domain objects, not to help me 
design my database so that I can map them to my domain objects.

> And in most cases (at least for SQLObject) there are ways 
> to deal with legacy databases, or ways to create a heterogeneous system 
> where you use an ORM for some tables, and custom SQL for other situations.

SQLObject is pretty good for CRUD applications. But I can imagine that 
if it evolves into providing more features for advanced usage (in terms 
of database interaction) you may start seeing things get more and more 
complex with its current design.

> If you are thinking in terms of heavily relational queries -- lots of 
> joins, subselects, aggregating, etc -- an ORM generally will feel 
> clunky.  Many relational concepts don't map well to Python ORMs.  Some 
> of those concepts could be represented in an ORM (but just aren't 
> currently), and some are very difficult to represent.

Which is why sqlmaps is so good. It does not feel clunky and I can do 
anything sql allows me to do and map it any which way i choose to do so. 
The true power of sqlmaps comes into play when you use it with a 
framework like the spring framework which provides declarative 
transactions for your DAOs. Although I dislike Java and love Python, I 
have to admit that python is very far behind when dealing with databases 
and transactions. It's definitely possible in python but it's so much 
more clean in Java (using spring).

> Looking just briefly at sqlmaps, it looks like a thin object interface 
> on top of custom SQL queries.  
You're pretty much correct there. It's a very thin interface but the 
beauty of it is it's mapping features both for database queries (where 
statements) and database results (mapping resultsets to domain objects).

> I'd find that tedious, but if you are 
> going to write custom SQL then I'm sure that's a helpful way to organize 
> the SQL.  

It's not much more tedious then SQLObject but it is far more powerful 
when it comes to mapping then SQLObject. Most of the mapping is only 
done once and can be considered equivalent to declaring SQLObject 
classes. You don't even need to do any mapping if you don't want to. You 
can treat the sql statements (select, insert, delete, update) like 
method/functions which take named paramters via a dictionary or straight 
parameters for simple usages.

Some of my applications are used by multiple clients with very different 
database schemas (although most of the information is the same, the 
structure is very different (including different column names)). SQLMAP 
allows me to keep my application code the same no matter which database 
schema I am using. I just need to of course provide the two different 
mappings (to the same sets of domain objects) and configure the one to 
use depending on the client.

> It's a lot different from Python ORMs, and I think I'd find it 
> a bit clunky.
> In a way it almost reminds me of Zope's Z SQL Methods, 
> which I hate.

I don't think it's clunky but to each his own opinion. I haven't looked 
at Zopes Z SQL but I've never liked Zope, mainly because of that ZODB 
that they are always marketing.

*** WARNING OT RANTING, SKIP IF NOT INTERESTED  ****
What the heck is so special about ZODB or any object oriented database. 
They are all just glorified session stores just like the one in Webware 
:-) except webware doesn't pretend it's some u beaut state of the art 
thingy.
*** END RANTING ****

Cheers,

huy

Hi Huy,

Huy wrote:
>> In a way it almost reminds me of Zope's Z SQL Methods, which I hate.
> 
> I don't think it's clunky but to each his own opinion. I haven't looked 
> at Zopes Z SQL but I've never liked Zope, mainly because of that ZODB 
> that they are always marketing.
> 
> *** WARNING OT RANTING, SKIP IF NOT INTERESTED  ****
> What the heck is so special about ZODB or any object oriented database. 
> They are all just glorified session stores just like the one in Webware 
> :-) except webware doesn't pretend it's some u beaut state of the art 
> thingy.
> *** END RANTING ****
> 

Well, I'm a Zope user interested in Webware, and I'm specifically an 
RDBMS developer so the perceived "tension" between the ZODB and an RDBMS 
  is of interest to me. FWIW, I think it's a non-issue, really - I use 
the RDBMS for holding the kind of structured data that RDBMSs are all 
about handling, I use Zope (or Plone) to hold relatively unstructured 
"content" (e.g. document) type data. In practice, this means that I hold 
very little in the ZODB except the application itself - templates, 
scripts, ZSQL methods etc., *BUT* while I'm developing a straight RDBMS 
application with a web UI, I can drop forums, document libraries and all 
kinds of stuff into the application without any cost at the RDBMS level.

The ZODB is very clever, a lot faster than you would expect, and if you 
hold content in it you get to use Zope's wonderful "acquisition" feature 
- but you need never store any of your actual data in it if that does 
not suit your use case.

As for ZSQL methods, well they're hard work to get up to speed with, but 
they are fabulous - as much as anything a template driven SQL generation 
system, which can be very powerful, and which suits my approach to 
development much more than would an ORM system.

Don't let anyone else's perception of the ZODB put you off - if it 
helps, think of it as a sandboxed file system for TTW development...


-- 

Regards,

PhilK

Email: phil@... / Voicemail & Facsimile: 07092 070518

"it's very hard to talk quantum using a language originally
designed to tell other monkeys where the ripe fruit is"
- Lu-Tze

Philip Kilner wrote:
> As for ZSQL methods, well they're hard work to get up to speed with, but 
> they are fabulous - as much as anything a template driven SQL generation 
> system, which can be very powerful, and which suits my approach to 
> development much more than would an ORM system.

I never had a problem getting up to speed with them, but I have found 
them hard to maintain, especially when maintaining other people's code. 
  It's way to easy to Just Add Another Method, and soon you have an 
impossible number of SQL Methods laying about with little way to figure 
out what's what.  And then there's a bunch of little things, but I've 
ranged on those parts before (http://blog.ianbicking.org/z-sql-rant). 
It has a lot to do with a combination of through-the-web development, 
DTML, and Acquisition that makes it very difficult for me to work in. 
Good practices help, but even then I find the result frustrating.

-- 
Ian Bicking  /  ianb@...  /  http://blog.ianbicking.org

Hi Ian,

Ian Bicking wrote:
>> As for ZSQL methods, well they're hard work to get up to speed with, 
>> but they are fabulous - as much as anything a template driven SQL 
>> generation system, which can be very powerful, and which suits my 
>> approach to development much more than would an ORM system.
> 
> I never had a problem getting up to speed with them, but I have found 
> them hard to maintain, especially when maintaining other people's code. 

I do make a point of keeping things simple, and using a relatively small 
number of tried and tested patterns. Reading your Blog, it's clear you 
are doing things "downstream" of the database which are more complex 
than I am. I try to address as much complexity as possible in the RDBMS 
itself, and keep the complexity of what I need to do in Zope under 
control in doing so.

>  It's way to easy to Just Add Another Method, and soon you have an 
> impossible number of SQL Methods laying about with little way to figure 
> out what's what.  And then there's a bunch of little things, but I've 
> ranged on those parts before (http://blog.ianbicking.org/z-sql-rant). It 
> has a lot to do with a combination of through-the-web development, DTML, 
> and Acquisition that makes it very difficult for me to work in. Good 
> practices help, but even then I find the result frustrating.
> 

I've never used DTML outside of ZSQL methods - I was lucky enough to 
learn ZPT first, so perhaps that's why I found them harder to get up to 
speed with.

I do agree about the risk of "Just Add Another Method", but consciously 
sitting down and refactoring several methods into one (I find my rapidly 
multiplying methods tend to be variations on a theme) reduces the count 
quite quickly. In the process, I generally also find I am doing work in 
ZSQL which would be better done in the RDBMS, which also tends to make 
things simpler.

WRT Acquisition, the risk of picking up a value in error concerns me, so 
I'm as methodical as possible about using an expression rather than just 
a name - I use Formulator to validate my forms, and a validation script 
which writes the parameters for ZSQL to a named dictionary in the 
request. The ZSQL methods read from that dictionary, and I make it all 
as explicit as possible.

I'm not in a position (yet!) to have either the time or the skill to 
write many of the building blocks of my applications from scratch - I 
come at the problem from the business end of the spectrum, and all my 
technical efforts so far have gone into developing a simple(-ish!) 
methodology which allows me to glue together the building blocks that 
Zope gives me in a reliable and intelligible way.

However, I am gong to hit a wall soon in that not enough of the logic 
for my taste is driven by the logic of the dB schema (I guess this is 
what you refer to as a lack of database introspection?) - but Zope / 
ZSQL are hardly alone in that. My options seem to be to work much more 
extensively in Python (which is inevitable as my skills and style 
develop), or to create something which can spit ZSQL methods out based 
on reading the schema, and highlight/propose changes to them as the 
schema is updated (which may be less than ideal, but could solve my 
immediate problems). Looking much more closely at ORMs will be part of 
finding my direction on that front, but I'm not sure how appropriate 
they are for me.

Given that the various flavours of SQL do such a poor job of 
implementing the relational model articulated by Codd & co. over 30 
years ago (I'm with Fabian Pascal - http://www.dbdebunk.com/ -  on 
this), we are stuck working with much more grievous limitations than any 
ZSQL methods impose! Yes, there is plenty of room for improvement - but 
ZSQL remains a powerful tool in it's proper context, I think. FWIW, 
given that vintage of the code in ZSQL methods, I think they have aged 
pretty well.

I'll be interested to see what, if anything, shows up in later versions 
of Zope - but for now (for me) it's a solid foundation to build on as it is.


-- 

Regards,

PhilK

Email: phil@... / Voicemail & Facsimile: 07092 070518

"it's very hard to talk quantum using a language originally
designed to tell other monkeys where the ripe fruit is"
- Lu-Tze