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. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
