使用SQLAlchemy在两秒钟内筛选对象 [英] Filter objects within two seconds of one another using SQLAlchemy

问题描述

我有两个表的日期列。一个保留（名称，日期），另一个保留（日期，p1，p2）。给定一个名称，我想使用表1中的日期从表2中查询p1和p2。如果表1中的日期位于表2中的日期的两秒钟之内，则应该进行匹配。

I have two tables with a column 'date'. One holds (name, date) and the other holds (date, p1, p2). Given a name, I want to use the date in table 1 to query p1 and p2 from table two; the match should happen if date in table one is within two seconds of date in table two.

如何使用SQLAlchemy完成此操作？

How can you accomplish this using SQLAlchemy?

我尝试（未成功）使用 ween 运算符并使用以下子句：

I've tried (unsuccessfully) to use the between operator and with a clause like:

td = datetime.timedelta(seconds=2)
q = session.query(table1, table2).filter(table1.name=='my_name').\
    filter(between(table1.date, table2.date - td, table2.date + td))

有什么想法吗？

编辑：
我设法使用以下方法解决了这个问题：

I've managed to solve the problem using the following approach:

from sqlalchemy.sql import between
import datetime
# [all other relevant imports]

td = datetime.timedelta(seconds=2)
t1_entry = session.query(table_1).filter(table_1.name == 'the_name').first()
if t1_entry is not None:
 tmin = t1_entry.date - td
 tmax = t1_entry.date + td
 t2_entry = session.query(table_2).filter(between(table_2.date, tmin, tmax)).first()
 return (t1_entry, t2_entry)
return None

可以进行比较，但是我不确定这种方法是否有效。

So the comparison can be done, but I'm not sure the approach is efficient.

推荐答案

在打开此问题一段时间后，到目前为止我已经提出了一种方法：

After some time with this question open, the approach so far is the one I've come up with:

from sqlalchemy.sql import between
import datetime
# [all other relevant imports]

td = datetime.timedelta(seconds=2)
t1_entry = session.query(table_1).filter(table_1.name == 'the_name').first()
if t1_entry is not None:
 tmin = t1_entry.date - td
 tmax = t1_entry.date + td
 t2_entry = session.query(table_2).filter(between(table_2.date, tmin, tmax)).first()
 return (t1_entry, t2_entry)
return None

如果您有更好的主意，我会接受您的回答。

If you have a better idea, I will accept your answer.

这篇关于使用SQLAlchemy在两秒钟内筛选对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！