Hi, On Sep 4, 2:32 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Sep 4, 2008, at 3:14 PM, Mike wrote: > > > > > > > Hi, > > > I am trying to translate the following SQL into SqlAlchemy session > > syntax: > > > sql = ''' SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), > > SUM(vac), SUM(ct), SUM(conv), SUM(misc) > > FROM tbl_TimeEntries > > WHERE dateworked > = '%s' AND dateworked <= '%s' AND empid > > = %s > > ''' % (start_date, end_date, emp_id) > > > I found the "apply_sum" query method, but this seems to be able to > > only be applied to one column at a time. I haven't found a way to use > > equality operators (>= or <=) yet. According to the SqlAchemy book by > > Copeland, I should be able to use the equality operators with table > > methods and the bitwise "&" operator. I prefer using sessions since > > they seem easier to clean up in wxPython, but I'm pretty flexible. > > > Any hints are welcome. Thanks! > > apply_sum() is totally out; I'd recommend not using it. > > The SUM constructs are expressed using func.sum(). > > The Copeland book only covers SQLA 0.4 through about midway; in that > version, a query such as the above is usually not issued via the ORM > and would instead be via select() construct: > > select([func.sum(table.c.reg), > func.sum(table.c.ot), ...]).where(<criterion>) > > recent versions of 0.4 do support values(), which is probably not > mentioned in the book since its recent: > > session.query(TimeEntry).values(func.sum(TimeEntry.reg), > func.sum(TimeEntry.ot), ...) > > in 0.5, query() can also handle column expressions at the start: > > session.query(func.sum(TimeEntry.reg), func.sum(TimeEntry.ot), ...) > > The WHERE criterion can be "AND" ed together using the bitwise & > operator (though you need to watch your parenthesis), or the and_() > function. The ORM and expression tutorials on the site have plenty > of examples on this.
Thanks for the advice. I had found lots of examples of "and_" in the "SQL Expression Tutorial", but that seems to deal exclusively with table queries. The ORM tutorial has 2 instances of "and_" under the filter operators section, but I missed those before I posted. Anyway, I think you've answered my question. Now I'll just need to take a whack at implementing the details. Thanks again! Mike --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
