Hi Bryan,
the only tricky bit in your SQL is the dangling 'DAY', because there's
no operator to tie it to the rest. Otherwise you should be able to
write (schema.AppDcRpe2 is just a Table object I'm using as an
example):
>>> q =
>>> session.query(schema.AppDcRpe2).filter(func.date_add(schema.AppDcRpe2.asof,
>>> func.interval(1 - func.dayofweek(schema.AppDcRpe2.asof))) < func.sysdate)
>>> print q
SELECT kdb_app_dc_rpe2.id AS kdb_app_dc_rpe2_id, kdb_app_dc_rpe2.asof
AS kdb_app_dc_rpe2_asof, kdb_app_dc_rpe2.instance_name AS
kdb_app_dc_rpe2_instance_name, kdb_app_dc_rpe2.dc AS
kdb_app_dc_rpe2_dc, kdb_app_dc_rpe2.rpe2_total AS
kdb_app_dc_rpe2_rpe2_total, kdb_app_dc_rpe2.rpe2_used AS
kdb_app_dc_rpe2_rpe2_used, kdb_app_dc_rpe2.rpe2_unused AS
kdb_app_dc_rpe2_rpe2_unused
FROM kdb_app_dc_rpe2
WHERE date_add(kdb_app_dc_rpe2.asof, interval(:dayofweek_1 -
dayofweek(kdb_app_dc_rpe2.asof))) < :date_add_1
which is *almost* what you need. Can MySQL 'INTERVAL' perhaps be
written in 'function form', i.e. something like interval(x, 'DAY')? In
that case you should be able to translate it fully.
Or maybe there's a way to 'abuse' the alias method, like so:
>>> q =
>>> session.query(schema.AppDcRpe2).filter(func.date_add(schema.AppDcRpe2.asof,
>>> func.interval(1 - func.dayofweek(schema.AppDcRpe2.asof)).alias('DAY')) <
>>> func.sysdate)
>>> print q
>>> SELECT kdb_app_dc_rpe2.id AS kdb_app_dc_rpe2_id,
>>> kdb_app_dc_rpe2.asof AS kdb_app_dc_rpe2_asof, kdb_app_dc_rpe2.instance_name
>>> AS kdb_app_dc_rpe2_instance_name, kdb_app_dc_rpe2.dc AS kdb_app_dc_rpe2_dc,
>>> kdb_app_dc_rpe2.rpe2_total AS kdb_app_dc_rpe2_rpe2_total,
>>> kdb_app_dc_rpe2.rpe2_used AS kdb_app_dc_rpe2_rpe2_used,
>>> kdb_app_dc_rpe2.rpe2_unused AS kdb_app_dc_rpe2_rpe2_unused
FROM kdb_app_dc_rpe2, interval(:dayofweek_1 -
dayofweek(kdb_app_dc_rpe2.asof)) "DAY"
WHERE date_add(kdb_app_dc_rpe2.asof, interval(:dayofweek_1 -
dayofweek(kdb_app_dc_rpe2.asof))) < :date_add_1
Except for the double quotes that looks to be pretty close to what you
want. But then again rather than massaging that into place you may as
well build a text SQL from your bits, I guess. The above would also be
MySQL specific, I believe. (BTW I have not tried to run any of this,
this is just the output of the parsed statements.)
-sas
On Oct 5, 4:45 pm, Bryan Vicknair <bryanv...@gmail.com> wrote:
> On Tue, Oct 5, 2010 at 1:58 AM, Chris Withers <ch...@simplistix.co.uk> wrote:
> > Are you looking for something database agnostic or something that just works
> > for MySQL?
>
> > If the latter, look at text:
> >http://www.sqlalchemy.org/docs/core/tutorial.html#using-text
>
> > If the former, then you'll want a database agnostic implementation. So,
> > what's the above sql actually trying to achieve?
>
> > Chris
>
> I'm fine with a MySQL-only solution. The text construct is always the
> fallback,
> but I'm wondering if there is a way that I can use the attributes of my class
> for the column name, instead of just a string. My column names are going to
> change soon, but my object model will stay the same, so I am trying not to
> explicitly use the column names in my code.
>
> Can I do something like this?
> 'DATE_ADD(' + Class.dateAttr + ', INTERVAL(1 - ' \
> + 'DAYOFWEEK(' + Class.dateAttr + ')) DAY)'
>
> If I can't use my class's attributes, is there a way I can at least use the
> table object's columns like this:
> 'DATE_ADD(' + table.c.date_col.name + ', INTERVAL(1 - ' \
> + 'DAYOFWEEK(' + table.c.date_col.name + ')) DAY)'
>
> I prefer one of these to a string because I will get an error during testing
> when the statement is encountered. With a string, I will only get an error if
> the statement actually runs in the DB.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
