SQLAlchemy & Upcoming Birthdays

OpenGroupware Coils uses SQLAlchemy as it's ORM. One of the desired features was a Logic command that efficiently returns contacts with upcoming birthdays. In raw SQL this query would be very simple to write - but how to do it in SQLAlchemy? The answer: sql.expression.extract which will create an expression column equivalent to EXTRACT. With EXTRACT it is possible to compare to the year-of-day represented by a date. The Python code looks like:

db = self._ctx.db_session()
# Get the current day-of-year
doy = datetime.today().timetuple().tm_yday
# Deal with year wrap-around
floor = doy - 2
if (floor < 1): floor +=365
ceiling = doy + 14
if (ceiling > 365): ceiling -= 365
# Create a field that is the SQL expression DOY(Contact.birth_date)
orm_doy = sql.expression.extract('doy', Contact.birth_date)
# Create the query
query = db.query(Contact).filter(and_(sql.expression.between(orm_doy, floor, ceiling),
                                      Contact.birth_date != None,
                                      Contact.is_account == self.accounts,
                                      Contact.status != 'archived'))

In this example "self.accounts" is an attribute with a value of 0 or 1