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.
I ended up with the following:
<code>
query = self.session.query(func.sum(TimeEntries.reg),
func.sum(TimeEntries.ot),
func.sum(TimeEntries.ce),
func.sum(TimeEntries.hol),
func.sum(TimeEntries.sklv),
func.sum(TimeEntries.vac),
func.sum(TimeEntries.ct),
func.sum(TimeEntries.conv),
func.sum(TimeEntries.misc)
)
query = query.filter(and_(TimeEntries.dateworked >= start_date,
TimeEntries.dateworked <= end_date,
TimeEntries.empid==self.emp_id)
)
totals = query.first()
</code>
This works quite well, although it is kind of verbose. Thanks again
for your advice.
Mike
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
