hi matt -
here's that query generated using lexical tables, which are just like
Table objects but require less boilerplace for SQL prototyping purposes:
from sqlalchemy import *
from sqlalchemy.sql import table, column
import datetime
shift = table('shift', column('employee_id', Integer),
column('shift_time_id', Integer))
shift_time = table('shift_time', column('id', Integer),
column('start_time', DateTime), column('stop_time', DateTime))
s = select([shift.c.employee_id, func.sum(shift_time.c.stop_time -
shift_time.c.start_time).label('hours')]).\
where(shift.c.employee_id.in_([28630, 28648])).\
where(shift.c.shift_time_id==shift_time.c.id).\
where(shift_time.c.start_time.between(
datetime.datetime(2008, 11, 2, 0, 0, 0),
datetime.datetime(2008, 11, 9, 0, 0, 0)
)).\
group_by(shift.c.employee_id).\
order_by(desc("hours"))
print s
print s.compile().params
On Dec 4, 2008, at 11:23 AM, Matt Wilson wrote:
>
> Right now, I'm writing this query as a string. I want to know if it
> can be expressed with SQLAlchemy's expressions instead.
>
> Here's the query:
>
> select sh.employee_id, sum(st.stop_time - st.start_time) as hours
> from shift sh, shift_time st
> where sh.employee_id in (28630, 28648)
> and sh.shift_time_id = st.id
> and st.start_time between timestamp '2008-11-02 00:00:00' and
> timestamp '2008-11-09 00:00:00'
> group by sh.employee_id
> order by hours desc;
>
> Any ideas?
>
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
