[ Python 常見問題 ] SQLAlchemy: how to filter date field?

Source From Here
Question
Here is model:

view plaincopy to clipboardprint?

1. class User(Base):  
2.     ...  
3.     birthday = Column(Date, index=True)   #in database it's like '1987-01-17'  
4.     ...  

I want to filter between two dates, for example to choose all users in interval 18-30 years. How to implement it with SQLAlchemy?

I think of:

view plaincopy to clipboardprint?

1. query = DBSession.query(User).filter(  
2.     and_(User.birthday >= '1988-01-17', User.birthday <= '1985-01-17')  
3. )   
4.   
5. # means age >= 24 and age <= 27  

I know this is not correct, but how to do correct?

How-To
In fact, your query is right except for the typo: your filter is excluding all records: you should change the <= for >= and vice versa:

view plaincopy to clipboardprint?

1. qry = DBSession.query(User).filter(  
2.         and_(User.birthday <= '1988-01-17', User.birthday >= '1985-01-17'))  
3. # or same:  
4. qry = DBSession.query(User).filter(User.birthday <= '1988-01-17').\  
5.         filter(User.birthday >= '1985-01-17')  

Also you can use between:

view plaincopy to clipboardprint?

1. qry = DBSession.query(User).filter(User.birthday.between('1985-01-17', '1988-01-17'))