Thanks! This is really helpful.
On Dec 4, 11:43 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
