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 <awill...@whitemice.org> 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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.