If you get this working please post a quick recipe. I use SA for orm
type stuff, but when it comes to read-only reports
I tend to use raw sql.
gniquil wrote:
> 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
> >
>
>
--
David Gardner
Pipeline Tools Programmer, "Sid the Science Kid"
Jim Henson Creature Shop
(323) 802-1717 [EMAIL PROTECTED]
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
