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

问题描述

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

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

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

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)

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

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

其中today就是datetime.today().

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

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

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

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

推荐答案

SQLAlchemy有效地将以Python表示的查询转换为SQL.但这是基于定义模型时分配给Column的数据类型而在相对肤浅的层次上进行的.

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.

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

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. 使用完整的datetime实例而不是其组成部分(日，月，年)来构造过滤器.
2. 在过滤器中使用SQLAlchemy的extract构造.
3. 在模型中定义三个混合属性，这些属性返回可以进行过滤的付款月份，日期和年份.

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.

在datetime对象上过滤

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

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

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

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

如果您想变得更复杂，例如最近30天进行的过滤付款.您可以使用以下代码来做到这一点:

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

您可以使用and_和or_组合多个过滤器目标.例如，要退回在过去30天内到期的 AND 款项应于15天内支付，您可以使用:

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

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

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

SQLAlchemy的extract表达式(在此处)用于执行SQL EXTRACT语句，这是在SQL中可以从DATETIME/TIMESTAMP值中提取月，日或年的方式.

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.

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

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

使用混合属性

SQLAlchemy 混合属性是一件很了不起的事情.它们使您可以透明地应用Python功能，而无需修改数据库.我怀疑在这种特定的用例中，它们可能会过大，但是它们是实现所需目标的第三种方法.

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.

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

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.

在您的特定问题中，我们将定义三个混合属性:Payment模型中的due_date_day，due_date_month，due_date_year.这是如何工作的:

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

这是上面的操作:

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的表达方式.

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.

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

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

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

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屋！