Comparing Dates with SQLAlchemy, SQLite

Question

I am writing an application that uses SQLAlchemy with an SQLite database. I believe my database, tables, and mapping are configured correctly because other operations work as expected. I am trying to write a function that retrieves all objects whose date field matches a datetime.date() supplied as a parameter to the function. Here is my first try:

def get_objects_matching_date(session,my_date):  
    return session.query(Table).filter(Table.date_field == my_date).all()

Event though I know that Table contains objects matching the criteria, the function returns nothing.

I know from reading the SA documentation that sqlite has no native support for date or datetime types and that they are stored as strings. But SA is supposed to handle the conversion to (when returning results) and from (when inserting records) date or datetime objects. I assume that it should be able to handle this when running a comparison filter as well. I've read several different SO threads and have considered using between() to filter out the objects matching my_date, but that doesn't seem like it should be necessary when an exact == is what I am looking for. I've also looked into using .filter(cast(Table.date_field,DATE) == my_date) to ensure that I am getting a comparison of objects, but that did not seem to work either.

Clearly I am missing something about the way that SQLAlchemy handles dates, especially with SQLite databases. How can I get an exact match between a Date stored in the SQLite db by SQLAlchemy and the datetime.date() object supplied as a parameter? Thanks for any help.

score 4 · Answer 1 · answered Aug 06 '19 at 09:45

4

None of the above answers worked for me, but I found a solution via this link: https://gist.github.com/danielthiel/8374607

Use sqlachemy's func.DATE() function as follows:

from sqlalchemy import func

session.query(Table).filter(func.DATE(Table.date_field) == my_date).all()

answered Aug 06 '19 at 09:45

stuartm

923
1
6
7

I can confirm this has worked wonders! Also works on SQLAlchemy 1.4 – gregoltsov Dec 15 '21 at 17:44

score 3 · Answer 2 · edited Dec 03 '16 at 03:44

3

This might be very late, but it better late than never, so it might benefit others :)

Try casting them both to Date:

from sqlalchemy import Date, cast
from datetime import datetime

created_at = datetime.now().date()
query.filter(cast(Model.created_at, Date) == cast(created_at,Date))

edited Dec 03 '16 at 03:44

Nick

341
3
8

answered Mar 05 '15 at 15:49

cackharot

688
1
6
13

Worked for me! Thank you! – fatman13 Mar 24 '17 at 06:57
3

Sqlite doesn't support cast to date, it returns only year instead. This may return in incorrect result. – ziima Mar 23 '20 at 16:57

score 0 · Answer 3 · answered Apr 26 '13 at 13:37

The problem did indeed lie with conversion between strings (the representation of Date objects in SQLite) and date objects in python. The solution below seems to work just fine, despite following a rather long sequence of conversions between datatypes (date->datetime->string). Maybe there is another way, but this works.

def get_objects_matching_date(session,my_date):  
    return session.query(Table).\
        filter((strftime("%Y-%m-%d",datetime.combine(my_date,time()).timetuple())).all()

Comparing Dates with SQLAlchemy, SQLite

3 Answers3

Linked