CODEWITHSUNDEEP
pythonflasksqlalchemy
Dawid Żurawski
Dawid Żurawski

Reputation: 579

keyword can't be an expression - filtering in sqlalchemy

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?

Upvotes: 4

Views: 6287

Answers (1)

abigperson
abigperson

Reputation: 5362

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:

Upvotes: 3

Related Questions