from sqlalchemy.orm.query import Query
from sqlalchemy.orm.util import _entity_descriptor
from sqlalchemy import util
from sqlalchemy.sql import operators, extract

ops = {
    'gt':operators.gt,
    'lt':operators.lt,
    'ge':operators.ge,
    'le':operators.le,
    'contains':operators.contains_op,
    'in':operators.in_op,
    'exact':operators.eq,
    'iexact':operators.ilike_op,
    'startswith':operators.startswith_op,
    'endswith':operators.endswith_op,
    'range':operators.between_op,
    'year': lambda c, x: extract('year', c) == x,
    'month': lambda c, x: extract('month', c) == x,
    'day': lambda c, x: extract('day', c) == x,
}
class DoubleUnderMagicalQuery(Query):
    def filter_by(self, **kw):
        q = self
        for k in kw:
            # the basic idea.   Doesn't check for errors as of yet.
            current = None
            tokens = k.split('__')
            for token in tokens:
                if current is None:
                    current = _entity_descriptor(q._joinpoint_zero(), token)
                    if current.impl.uses_objects:
                        q = q.join(current)
                        current = None
                elif token in ops:
                    q = q.filter(ops[token](current, *util.to_list(kw[k])))
                    break
        return q

if __name__ == '__main__':
    from sqlalchemy.ext.declarative import declarative_base, declared_attr
    from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, Date
    from sqlalchemy.orm import Session, relationship
    import datetime

    class Base(object):
        @declared_attr
        def __tablename__(cls):
            return cls.__name__.lower()

        id = Column(Integer, primary_key=True)

    Base = declarative_base(cls=Base)

    class Blog(Base):
        name = Column(String)
        entries = relationship("Entry", backref="blog")

    class Entry(Base):
        blog_id = Column(Integer, ForeignKey('blog.id'))
        date = Column(Date)
        headline = Column(String)
        body = Column(String)

    e = create_engine('sqlite://', echo=True)
    Base.metadata.create_all(e)

    s = Session(e, query_cls=DoubleUnderMagicalQuery)

    b1, b2 = Blog(name='blog1', entries=[
            Entry(headline='b1 headline 1', body='body 1', date=datetime.date(2010, 2, 5)),
            Entry(headline='b1 headline 2', body='body 2', date=datetime.date(2010, 4, 8)),
            Entry(headline='b1 headline 3', body='body 3', date=datetime.date(2010, 9, 14)),
        ]),\
        Blog(name='blog2', entries=[
            Entry(headline='b2 headline 1', body='body 1', date=datetime.date(2010, 5, 12)),
            Entry(headline='b2 headline 2', body='body 2', date=datetime.date(2010, 7, 18)),
            Entry(headline='b2 headline 3', body='body 3', date=datetime.date(2011, 8, 27)),
        ])

    s.add_all([b1, b2])
    s.commit()

# blog with name of 'blog1'
assert s.query(Blog).filter_by(name__exact='blog1').one() is b1

# blog with name  containing 'blog'
assert s.query(Blog).filter_by(name__contains='blog').all() == [b1, b2]

# blog with entry 'b2 headline 2'
assert s.query(Blog).filter_by(entries__headline__exact='b2 headline 2').one() is b2

# blog with entries between 1/10 - 3/10
assert s.query(Blog).filter_by(
                entries__date__range=(datetime.date(2010, 1, 1), datetime.date(2010, 3, 1))
                ).one() is b1

# entries in 2011
assert s.query(Entry).filter_by(date__year=2011).one() is b2.entries[2]