CODEWITHSUNDEEP
pythondatabasesqlitesqlalchemy
crlane
crlane

Reputation: 521

Comparing Dates with SQLAlchemy, SQLite

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.

Upvotes: 3

Views: 6969

Answers (3)

stuartm
stuartm

Reputation: 953

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()

Upvotes: 5

cackharot
cackharot

Reputation: 698

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))

Upvotes: 3

crlane
crlane

Reputation: 521

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()

Upvotes: 0

Related Questions