4

I create simple database with sqlalchemy

class BlogPost(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    post = db.Column(db.Text, nullable=False)
    create_date = db.Column(db.DateTime, nullable=False)

create_date is in format (year-month-day h:m:s.ms):

2018-02-20 21:45:37.591733

I used datetime.datetime

I'd like to filter for example all posts with year=2018 and here i got problem:

posts = BlogPost.query.filter_by(create_date.year=2018)

it is possible to use datetime.year but i can't use it as an expression in sqlalchemy filtering, above code returns:

SyntaxError: keyword can't be an expression

Correct syntax would be: create_date=..., but i need to compare only year, not whole data object

Is it possible to do that in a simple way? Any suggestions?

Dawid Żurawski
  • 519
  • 1
  • 5
  • 18
  • 2
    Read https://stackoverflow.com/questions/2128505/whats-the-difference-between-filter-and-filter-by-in-sqlalchemy and any of the other questions and answers that come up, if you search with that error message for why you're getting that. The actual answer to your problem depends a bit on your DB in use, but probably you need the SQL function EXTRACT, or some other method. – Ilja Everilä Mar 31 '18 at 16:04
  • oh I couldn't find that! Thank you very much! – Dawid Żurawski Mar 31 '18 at 16:09
  • 1
    Related: [How to filter query in sqlalchemy by year (datetime column)](https://stackoverflow.com/questions/1453591/how-to-filter-query-in-sqlalchemy-by-year-datetime-column) – Ilja Everilä Mar 31 '18 at 16:16

1 Answers1

3

My approach would be to use either between():

from datetime import datetime
dt_from = datetime(2018, 1, 1)
dt_to = datetime(2018, 12, 31, 23, 59, 59)

BlogPost.query.filter(db.between(BlogPost.create_date, dt_from, dt_to))

or and_() notation:

BlogPost.query.filter(db.and_(BlogPost.create_date >= dt_from, BlogPost.create_date <= dt_to))

Further info in the docs:

abigperson
  • 5,252
  • 3
  • 22
  • 25