SQLAlchemy - 使用 DateTime 列查询以按月/日/年过滤 [英] SQLAlchemy - Querying with DateTime columns to filter by month/day/year

问题描述

我正在构建一个涉及跟踪付款的 Flask 网站，但我遇到了一个问题，我似乎无法按日期过滤我的数据库模型之一.

例如，如果这是我的表的样子:

payment_to、amount、due_date(一个 DateTime 对象)A公司, 3000, 7-20-2018公司 B, 3000, 7-21-2018C公司, 3000, 8-20-2018

我想过滤它，以便获得 7 月 20 日之后的所有行，或 8 月的所有行，等等.

我可以想到一种粗暴的方式来过滤所有付款，然后遍历列表以按月/年进行过滤，但我宁愿远离这些方法.

这是我的付款数据库模型:

class Payment(db.Model, UserMixin):id = db.Column(db.Integer, unique = True, primary_key = True)payment_to = db.Column(db.String, nullable = False)数量 = db.Column(db.Float, nullable = False)Due_date = db.Column(db.DateTime, nullable = False, default = datetime.strftime(datetime.today(), "%b %d %Y"))week_of = db.Column(db.String, nullable = False)

这是我尝试按日期过滤Payment:

Payment.query.filter(Payment.due_date.month == today.month, Payment.due_date.year == today.year, Payment.due_date.day >= today.day).all()

其中 today 就是 datetime.today().

我假设 due_date 列在我调用它时将具有所有 DateTime 属性(例如 .month)，但似乎我错了.

按日期过滤 Payment 列的最佳方法是什么?感谢您的帮助.

解决方案

SQLAlchemy 有效地将用 Python 表达的查询转换为 SQL.但它是在相对肤浅的层面上做到这一点的，具体取决于您在定义模型时分配给 Column 的数据类型.

这意味着它不一定会在其 DateTime 构造上复制 Python 的 datetime.datetime API - 毕竟，这两个类旨在做非常不同的事情！(datetime.datetime 为 Python 提供日期时间功能，而 SQLAlchemy 的 DateTime 告诉它的 SQL 转换逻辑它正在处理 SQL DATETIME 或 TIMESTAMP 列).

不过别担心！有很多不同的方法可以让你实现你想要做的事情，其中​​一些非常简单.我认为最简单的三个是:

1. 使用完整的 datetime 实例而不是其组成部分(日、月、年)构建您的过滤器.
2. 在过滤器中使用 SQLAlchemy 的 extract 构造.
3. 在您的模型中定义三个混合属性，返回付款月、日和年，然后您可以对其进行过滤.

过滤 datetime 对象

这是实现您正在尝试的三种(简单)方法中最简单的一种，而且它的执行速度也应该是最快的.基本上，不要尝试在查询中分别过滤每个组件(日、月、年)，只需使用单个 datetime 值.

基本上，以下内容应该等同于您在上面的查询中尝试执行的操作:

from datetime import datetimetodays_datetime = datetime(datetime.today().year, datetime.today().month, datetime.today().day)付款 = Payment.query.filter(Payment.due_date >= todays_datetime).all()

现在，payments 应该是到期日期在系统当前日期开始(时间 00:00:00)之后的所有付款.

如果您想变得更复杂，例如过滤过去 30 天内的付款.您可以使用以下代码执行此操作:

from datetime import datetime, timedeltafilter_after = datetime.today() - timedelta(days = 30)付款 = Payment.query.filter(Payment.due_date >= filter_after).all()

您可以使用 and_ 和 or_ 组合多个过滤器目标.例如，要退回过去 30 天内到期的和到期时间超过 15 天的付款，您可以使用:

from datetime import datetime, timedelta从 sqlalchemy 导入和_三十天前 = datetime.today() - timedelta(days = 30)十五天前 = datetime.today() - timedelta(days = 15)# 使用 and_ 隐式:付款 = Payment.query.filter(Payment.due_date >=三十天前，Payment.due_date <=十五天前).all()# 显式使用 and_:付款 = Payment.query.filter(and_(Payment.due_date >=三十天前，Payment.due_date <=十五天前)).all()

这里的技巧 - 从您的角度来看 - 是在执行查询之前正确构建过滤器目标 datetime 实例.

使用extract构造

SQLAlchemy 的 extract 表达式(记录在 此处) 用于执行 SQL EXTRACT 语句，这就是在 SQL 中如何从 DATETIME/TIMESTAMP 值中提取月、日或年的方法.>

使用这种方法，SQLAlchemy 告诉您的 SQL 数据库首先，从我的 DATETIME 列中提取月、日和年，然后然后对提取的值进行过滤".请注意，这种方法将比过滤上述 datetime 值慢.但这是它的工作原理:

from sqlalchemy 导入提取Payments = Payment.query.filter(extract('month', Payment.due_date) >= datetime.today().month,extract('year', Payment.due_date) >= datetime.today().year,extract('day', Payment.due_date) >= datetime.today().day).all()

使用混合属性

SQLAlchemy 混合属性 是很棒的东西.它们允许您在不修改数据库的情况下透明地应用 Python 功能.我怀疑对于这个特定用例，它们可能有点矫枉过正，但它们是实现您想要的目标的第三种方式.

基本上，您可以将混合属性视为实际上并不存在于您的数据库中的虚拟列"，但 SQLAlchemy 可以在需要时根据您的数据库列即时计算.

在您的具体问题中，我们将在您的 Paymentdue_date_day、due_date_month、due_date_year> 模型.这是它的工作原理:

... 你现有的导入语句从 sqlalchemy 导入提取从 sqlalchemy.ext.hybrid 导入 hybrid_property付款类(db.Model，UserMixin):id = db.Column(db.Integer, unique = True, primary_key = True)payment_to = db.Column(db.String, nullable = False)数量 = db.Column(db.Float, nullable = False)Due_date = db.Column(db.DateTime, nullable = False, default = datetime.strftime(datetime.today(), "%b %d %Y"))week_of = db.Column(db.String, nullable = False)@hybrid_propertydef Due_date_year(self):返回 self.due_date.year@due_date_year.expressiondef Due_date_year(cls):返回提取物('年份'，cls.due_date)@hybrid_propertydef Due_date_month(self):返回 self.due_date.month@due_date_month.expressiondef Due_date_month(cls):返回提取物('月'，cls.due_date)@hybrid_propertydef Due_date_day(self):返回 self.due_date.day@due_date_day.expressiondef Due_date_day(cls):返回提取物('天'，cls.due_date)付款 = Payment.query.filter(Payment.due_date_year >= datetime.today().year,Payment.due_date_month >= datetime.today().month,Payment.due_date_day >= datetime.today().day).all()

这是上面所做的:

1. 您正在像之前一样定义您的 Payment 模型.
2. 但随后您将添加一些名为 due_date_year、due_date_month 和 due_date_day 的只读实例属性.以 due_date_year 为例，这是一个对 Payment 类的 instances 进行操作的实例属性.这意味着当您执行 one_of_my_payments.due_date_year 时，该属性将从 Python 实例中提取 due_date 值.因为这一切都发生在 Python 中(即不接触您的数据库)，它将对 SQLAlchemy 存储在您的实例中的已经翻译的 datetime.datetime 对象进行操作.它会返回 due_date.year 的结果.
3. 然后您要添加一个 class 属性.这是用 @due_date_year.expression 修饰的位.这个装饰器告诉 SQLAlchemy，当它把对 due_date_year 的引用翻译成 SQL 表达式时，它应该按照这个方法中的定义进行.所以上面的例子告诉 SQLAlchemy如果你需要在 SQL 表达式中使用 due_date_year，那么 extract('year', Payment.due_date) 就是 due_date_year 应该被表达.

(注意:上面的例子假设 due_date_year、due_date_month 和 due_date_day 都是只读属性.你当然可以定义自定义setter 也使用 @due_date_year.setter 接受参数 (self, value) 以及)

总结

在这三种方法中，我认为第一种方法(过滤datetime)既最容易理解，也最容易实现，并且执行速度最快.这可能是最好的方法.但是这三种方法的原则非常重要，我认为将帮助您从 SQLAlchemy 中获得最大价值.我希望这证明有帮助！

I'm building a Flask website that involves keeping track of payments, and I've run into an issue where I can't really seem to filter one of my db models by date.

For instance, if this is what my table looks like:

payment_to, amount, due_date (a DateTime object)

company A, 3000, 7-20-2018
comapny B, 3000, 7-21-2018
company C, 3000, 8-20-2018

and I want to filter it so that I get all rows that's after July 20th, or all rows that are in August, etc.

I can think of a crude, brute-force way to filter all payments and THEN iterate through the list to filter by month/year, but I'd rather stay away from those methods.

This is my payment db model:

class Payment(db.Model, UserMixin):
    id = db.Column(db.Integer, unique = True, primary_key = True)

    payment_to = db.Column(db.String, nullable = False)
    amount = db.Column(db.Float, nullable = False)

    due_date = db.Column(db.DateTime, nullable = False, default = datetime.strftime(datetime.today(), "%b %d %Y"))
    week_of = db.Column(db.String, nullable = False)

And this is me attempting to filter Payment by date:

Payment.query.filter(Payment.due_date.month == today.month, Payment.due_date.year == today.year, Payment.due_date.day >= today.day).all()

where today is simply datetime.today().

I assumed the due_date column would have all DateTime attributes when I call it (e.g. .month), but it seems I was wrong.

What is the best way to filter the columns of Payment by date? Thank you for your help.

解决方案

SQLAlchemy effectively translates your query expressed in Python into SQL. But it does that at a relatively superficial level, based on the data type that you assign to the Column when defining your model.

This means that it won't necessarily replicate Python's datetime.datetime API on its DateTime construct - after all, those two classes are meant to do very different things! (datetime.datetime provides datetime functionality to Python, while SQLAlchemy's DateTime tells its SQL-translation logic that it's dealing with a SQL DATETIME or TIMESTAMP column).

But don't worry! There are quite a few different ways for you to do achieve what you're trying to do, and some of them are super easy. The three easiest I think are:

1. Construct your filter using a complete datetime instance, rather than its component pieces (day, month, year).
2. Using SQLAlchemy's extract construct in your filter.
3. Define three hybrid properties in your model that return the payment month, day, and year which you can then filter against.

Filtering on a datetime Object

This is the simplest of the three (easy) ways to achieve what you're trying, and it should also perform the fastest. Basically, instead of trying to filter on each component (day, month, year) separately in your query, just use a single datetime value.

Basically, the following should be equivalent to what you're trying to do in your query above:

from datetime import datetime

todays_datetime = datetime(datetime.today().year, datetime.today().month, datetime.today().day)

payments = Payment.query.filter(Payment.due_date >= todays_datetime).all()

Now, payments should be all payments whose due date occurs after the start (time 00:00:00) of your system's current date.

If you want to get more complicated, like filter payments that were made in the last 30 days. You could do that with the following code:

from datetime import datetime, timedelta

filter_after = datetime.today() - timedelta(days = 30)

payments = Payment.query.filter(Payment.due_date >= filter_after).all()

You can combine multiple filter targets using and_ and or_. For example to return payments that were due within the last 30 days AND were due more than 15 ago, you can use:

from datetime import datetime, timedelta
from sqlalchemy import and_

thirty_days_ago = datetime.today() - timedelta(days = 30)
fifteen_days_ago = datetime.today() - timedelta(days = 15)

# Using and_ IMPLICITLY:
payments = Payment.query.filter(Payment.due_date >= thirty_days_ago,
                                Payment.due_date <= fifteen_days_ago).all()

# Using and_ explicitly:
payments = Payment.query.filter(and_(Payment.due_date >= thirty_days_ago,
                                     Payment.due_date <= fifteen_days_ago)).all()

The trick here - from your perspective - is to construct your filter target datetime instances correctly before executing your query.

Using the extract Construct

SQLAlchemy's extract expression (documented here) is used to execute a SQL EXTRACT statement, which is how in SQL you can extract a month, day, or year from a DATETIME/TIMESTAMP value.

Using this approach, SQLAlchemy tells your SQL database "first, pull the month, day, and year out of my DATETIME column and then filter on that extracted value". Be aware that this approach will be slower than filtering on a datetime value as described above. But here's how this works:

from sqlalchemy import extract

payments = Payment.query.filter(extract('month', Payment.due_date) >= datetime.today().month,
                                extract('year', Payment.due_date) >= datetime.today().year,
                                extract('day', Payment.due_date) >= datetime.today().day).all()

Using Hybrid Attributes

SQLAlchemy Hybrid Attributes are wonderful things. They allow you to transparently apply Python functionality without modifying your database. I suspect for this specific use case they might be overkill, but they are a third way to achieve what you want.

Basically, you can think of hybrid attributes as "virtual columns" that don't actually exist in your database, but which SQLAlchemy can calculate on-the-fly from your database columns when it needs to.

In your specific question, we would define three hybrid properties: due_date_day, due_date_month, due_date_year in your Payment model. Here's how that would work:

... your existing import statements

from sqlalchemy import extract
from sqlalchemy.ext.hybrid import hybrid_property

class Payment(db.Model, UserMixin):
    id = db.Column(db.Integer, unique = True, primary_key = True)

    payment_to = db.Column(db.String, nullable = False)
    amount = db.Column(db.Float, nullable = False)

    due_date = db.Column(db.DateTime, nullable = False, default = datetime.strftime(datetime.today(), "%b %d %Y"))
    week_of = db.Column(db.String, nullable = False)

    @hybrid_property
    def due_date_year(self):
        return self.due_date.year

    @due_date_year.expression
    def due_date_year(cls):
        return extract('year', cls.due_date)

    @hybrid_property
    def due_date_month(self):
        return self.due_date.month

    @due_date_month.expression
    def due_date_month(cls):
        return extract('month', cls.due_date)

    @hybrid_property
    def due_date_day(self):
        return self.due_date.day

    @due_date_day.expression
    def due_date_day(cls):
        return extract('day', cls.due_date)

payments = Payment.query.filter(Payment.due_date_year >= datetime.today().year,
                                Payment.due_date_month >= datetime.today().month,
                                Payment.due_date_day >= datetime.today().day).all()

Here's what the above is doing:

1. You're defining your Payment model as you already do.
2. But then you're adding some read-only instance attributes called due_date_year, due_date_month, and due_date_day. Using due_date_year as an example, this is an instance attribute which operates on instances of your Payment class. This means that when you execute one_of_my_payments.due_date_year the property will extract the due_date value from the Python instance. Because this is all happening within Python (i.e. not touching your database) it will operate on the already-translated datetime.datetime object that SQLAlchemy has stored in your instance. And it will return back the result of due_date.year.
3. Then you're adding a class attribute. This is the bit that is decorated with @due_date_year.expression. This decorator tells SQLAlchemy that when it is translating references to due_date_year into SQL expressions, it should do so as defined in in this method. So the example above tells SQLAlchemy "if you need to use due_date_year in a SQL expression, then extract('year', Payment.due_date) is how due_date_year should be expressed.

(note: The example above assumes due_date_year, due_date_month, and due_date_day are all read-only properties. You can of course define custom setters as well using @due_date_year.setter which accepts arguments (self, value) as well)

In Conclusion

Of these three approaches, I think the first approach (filtering on datetime) is both the easiest to understand, the easiest to implement, and will perform the fastest. It's probably the best way to go. But the principles of these three approaches are very important and I think will help you get the most value out of SQLAlchemy. I hope this proves helpful!

这篇关于SQLAlchemy - 使用 DateTime 列查询以按月/日/年过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！