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