On Tue, 2010-10-12 at 14:20 -0400, Michael Bayer wrote:
> On Oct 12, 2010, at 1:59 PM, Adam Tauno Williams wrote:
> > Database: PostgreSQL 8.4.4
> > SQLAlchemy: 0.6
> > I attempting to query the database for contacts with a recent or
> > upcoming birthday.
> > So --
> > doy = datetime.today().timetuple().tm_yday
> > floor = doy - 2
> > if (floor < 1): floor +=365
> > ceiling = doy + 14
> > if (ceiling > 365): ceiling -= 365
> > db.query(Contact).\
> > filter(func.extract('doy', Contact.birth_date) == doy).all()
> > -- except this fails with --
> > sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or
> > near ","
> > LINE 3: WHERE extract(E'doy', person.birthday) = 285 ORDER BY object..
> > -- the query should be:
> > SELECT company_id FROM person WHERE extract('doy' from birthday)=285
> > I assume I'm just missing something.
> EXTRACT has a "FROM" in there so func. is not enough. We have an
> extract() function by itself that takes care of this: [1]
Yep, that was it; I was blurring between funcs and actual expressions.
Arg!
This works perfectly --
from sqlalchemy import *
import sqlalchemy.sql as sql
from datetime import datetime
....
current_doy = datetime.today().timetuple().tm_yday
floor = current_doy - 2
if (floor < 1): floor +=365
ceiling = current_doy + 14
if (ceiling > 365): ceiling -= 365
orm_doy = sql.expression.extract('doy', Contact.birth_date)
db.query(Contact).\
filter(sql.expression.between(orm_doy,
floor,
ceiling)).all()
[1]
http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=extract#sqlalchemy.sql.expression.extract
--
Adam Tauno Williams <[email protected]> LPIC-1, Novell CLA
<http://www.whitemiceconsulting.com>
OpenGroupware, Cyrus IMAPd, Postfix, OpenLDAP, Samba
--
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.
