使用SQLAlchemy Integer字段创建timedelta对象以进行过滤 [英] Using a SQLAlchemy Integer field to create a timedelta object for filtering
问题描述
我有一个名为Item的对象.它具有2个字段:日期时间行"created_on"和整数行"days".
I have an object called Item. It has 2 fields: a datetime row called "created_on," and an integer row called "days."
我想查询许多天前创建的所有对象.
I want to query for all objects that were created "days" many days ago.
这是我认为应该完成的方式:
Here's how I thought it should be done:
now = utcnow()
session.query(Item).filter(Item.created_on + Interval(timedelta(days=Item.days)) <= now)
但是我无法创建这样的时间增量.我收到此错误:
But I'm unable to create a timedelta like this. I'm getting this error:
TypeError: unsupported type for timedelta minutes component: InstrumentedAttribute
更新:
多亏了van,我应该使用内置函数.我正在使用Mysql 5.1,因此它是timestampadd
.我的新查询是这样:
Thanks to van, I should be using a built-in function. I'm using Mysql 5.1, so it would be timestampadd
. My new query is this:
now = utcnow()
session.query(Item).filter(func.timestampadd('DAY', Item.days, Item.created_on) <= now)
但是,我遇到的新错误是:
However, the new error I'm getting is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''DAY', items.days, items.created_on) <= '2015-07-12 ' at line 3
推荐答案
我认为Interval
不能为您提供帮助,因为它无法强制使用特定于DB的date [time]函数.因此,您可能应该使用平台特定的date [time]函数来解决此问题.
I do not think that Interval
will help you there, as it would not be able to coerce into DB-specific date[time] functions. So you should probably use the platform specific date[time] functions to solve this.
如果您使用的是 postgresql ,则下面的代码应该可以工作(假设Items.days
是整数):
If you are using postgresql, the code below should work (assuming Items.days
is integer):
q = (session.query(Item)
.filter(func.age(now, Item.created_on) <=
func.make_interval(0, 0, 0, Item.days)
)
)
请参见postgres'
日期/时间函数和运算符了解更多信息.
See postgres'
Date/Time Functions and Operators for more information.
这篇关于使用SQLAlchemy Integer字段创建timedelta对象以进行过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!