Hi all,
I've been thinking about using mako as a sql templating engine to
dynamically generate sql. The reason for this is that recently I've
realized 90% of time is really spent on generating some kind of
analytic views (materialized into materialized views or tables) on
existing oracle tables. Since oracle has this magic of rowid, most of
the tables i deal with has no real keys or foreign constraints. Much
of the "model" machinery is really useless to me. In addition, the
model paradigm seems to fit only transactional type of work. For
analytic work, very often i find myself using the "analytic functions"
and some "model queries" built into Oracle sql. However, as anyone has
worked with both python (as a frame of reference) and pl-sql, the
latter is disgusting and quirky to the extreme. Sometimes I wish
sqlalchemy could provide the flexibility to allow me to do all my work
purely in python. But as due to the above reasons, I really can't
quite get there yet.
So as i was playing around with pylons, I found that templating with
mako really provide that extra flexibility lacked in sqlalchemy. For
example, pivoting and unpivoting existing tables is something that I
found myself do pretty often. For pivoting it's easy to write a
template like:
select col1
% for val in col2_distinct_vals:
, max(decode(col2, ${val}, col3, null) as col_${val}
% endfor
from some_table
group by col1
and fill col2_distinct_vals with
select distinct col2 from some_table
The above is quite intuitive compare to the awkwardness and the
insanity of hardcoded 500 line sql queries via union and maintained
via find/replace (if tried hard enough, 95% of the stuff done in
databases can somehow be cramped into 1 (oracle) query. But I guess
only 1 person in only 1 instance of time would be capable of
understanding it...). These can even be kept nicely packaged and
documented. The sqlalchemy expression language can't really do very
well here either (at least too proprietary in some ways...sort of like
working with pylons versus working with a CMS like Plone, which both
can be used to create a blog, but one gives more control, and the
knowledge is more readily out there).
Anyway, to keep this short, can anyone find a way to somehow integrate
such a templating idea into sqlalchemy while leveraging the convention
already established in ansi-sql and some python voodoo? (Of course,
the existing stuff is great for creating transactional type of
backend, like a blog or wiki, which then again, really occupies 10% of
my time, and hopefully (or not) others as well.)
Frank
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
