将日期与SQLAlchemy，SQLite进行比较 [英] Comparing Dates with SQLAlchemy, SQLite

问题描述

我正在编写一个将SQLAlchemy与SQLite数据库一起使用的应用程序。我相信我的数据库，表和映射已正确配置，因为其他操作可以正常进行。我正在尝试编写一个函数，以检索其日期字段与作为函数参数提供的datetime.date（）匹配的所有对象。这是我的第一次尝试：

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.

我从阅读 SA文档：sqlite没有对 date 或 datetime 类型，并将它们存储为字符串。但是SA应该能够处理（返回结果时）和（插入记录时）日期或日期时间的转换对象。我假设它在运行比较过滤器时也应该能够处理此问题。我已经阅读了几个不同的SO线程，并考虑使用 between（）来过滤出匹配 my_date 的对象，但是当我要查找精确的 == 时，似乎没有必要。我还研究了使用 .filter（cast（cast（Table.date_field，DATE）== my_date）来确保获得对象的比较，但这并没有

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.

很显然，我缺少有关SQLAlchemy处理日期的方式的一些信息，尤其是对于SQLite数据库。如何获得SQLAlchemy存储在SQLite数据库中的日期与 datetime.date（）的精确匹配对象作为参数提供？谢谢您的帮助。

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.

推荐答案

这可能已经很晚了，但总比没有好，所以它可能会使其他人受益:)

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

这篇关于将日期与SQLAlchemy，SQLite进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！