SA: can I have a 'year' column_property for a Date column?

Question

I have a class with a Date column in sqlalchemy-0.7. Can I have a column_property or anything similar that gets me the year and lets me easily filter by it? How would I write it?

IE, I'd like to have (declarative syntax):

class Foo(Base):
    id   = Column(Integer, primary_key=True)
    date = Column(Date(), nullable=False)
    year = column_property(something here)

# later on
q = session().query(Foo).filter_by(year=2011)

Answer 1

Sure you can define such property:

year = column_property(extract('year', date))

But do you realy need it? You can filter by year without such property defined by rewriting filter condition:

query(Foo).filter(extract('year', Foo.date)==2011)

Update

Although this solution looks simple it also have a drawback: such condition in WHERE clause will never use index on date field. Having a lot of rows and high selecivity of condition this will cause high impact on perfomance. So you may want to rewrite condition that will cause RANGE INDEX SCAN instead of FULL TABLE SCAN (as suggested in Simon's comment):

start = datetime.date(year, 1, 1)
end = datetime.date(year, 12, 31)
query(Foo).filter(Foo.date.between(start, end))

Defining a property with such behavior is possible too, you just have to redefine comparator:

class YearComparator(ColumnProperty.Comparator):
    def __eq__(self, year):
        if isinstance(year, int):
            column = self.prop.columns[0].get_children()[0].expr
            start = datetime.date(year, 1, 1)
            end = datetime.date(year, 12, 31)
            return column.between(start, end)
        else:
            # It can be a column or exression which we can't handle such way
            return ColumnProperty.Comparator.__eq__(self, year)
    # __lt__, __gt__ etc. are very similar to __eq__

def year_property(date_column, **kwargs):
    kwargs.setdefault('comparator_factory', YearComparator)
    return column_property(extract('year', date_column), **kwargs)

class Foo(Base):
    __tablename__ = 'Foo'
    id = Column(Integer, primary_key=True)
    date = Column(Date, index=True)
    year = year_property(date)