代码之家 › 专栏 › 技术社区 › Codejoy

sqlalchemy在筛选器查询中忽略and_u和func.DATE

flask-sqlalchemy sqlalchemy flask

0

Codejoy · 技术社区 · 6 年前

我有一个查询,它试图查找给定日期和状态的记录:

ld=LunchDay.query.filter(and_(func.DATE(LunchDay.timestamp == datetime.date.today()), LunchDay.status==1))

模型:

class LunchDay(db.Model):
    __tablename__ = 'lunch_day'

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user = relationship("User", foreign_keys=[user_id])
    timestamp = db.Column(db.DateTime, index=True, default=datetime.today())
    status = db.Column(db.Integer)

它运行并且不抛出错误,但它似乎不考虑日期。它将在数据库的DateTimeField中找到日期与yesterdays类似的条目:

2018-11-13 00:00:00.000000

这是昨天的日期,但它只是根据状态来选择它,几乎就像它将其视为OR一样。我使用的导入是:

from sqlalchemy import func, and_

1 回复 | 直到 6 年前

1

SuperShoot npburns224 6 年前

打印出查询生成的实际sql以查看发生了什么。例如。:

ld=LunchDay.query.filter(and_(func.DATE(LunchDay.timestamp == datetime.date.today()), LunchDay.status==1))
print(ld)

印刷品:

SELECT lunch_day.id AS lunch_day_id, lunch_day.timestamp AS lunch_day_timestamp, lunch_day.status AS lunch_day_status
FROM lunch_day
WHERE DATE(lunch_day.timestamp = %(timestamp_1)s) AND lunch_day.status = %(status_1)s

在那里你可以看到 lunch_day.timestamp timestamp_1 正在传递给 DATE 功能。

这在sqlalchemy查询中非常容易看到: func.DATE(LunchDay.timestamp == datetime.date.today()) . 我想你是想改变信仰 LunchDay.timestamp 约会然后与之相比 datetime.date.today() 应该是哪一个 db.func.DATE(LunchDay.timestamp) == date.today() .

print(LunchDay.query.filter(and_(func.DATE(LunchDay.timestamp) == datetime.date.today(), LunchDay.status == 1)))

印刷品:

SELECT lunch_day.id AS lunch_day_id, lunch_day.timestamp AS lunch_day_timestamp, lunch_day.status AS lunch_day_status
FROM lunch_day
WHERE DATE(lunch_day.timestamp) = %(DATE_1)s AND lunch_day.status = %(status_1)s

另一件需要注意的事情是,多个语句传递给 filter() 被自动视为 and_() ,因此您可以通过删除以下内容来简化查询:

LunchDay.query.filter(func.DATE(LunchDay.timestamp) == datetime.date.today(), LunchDay.status == 1)