按时间过滤 sqlalchemy sqlite datetime 列

Question

我不确定如何仅使用时间字段来过滤我的数据库。现在我有一个名为 DatabasePolgygon 的类。

class DatabasePolygon(dbBase):
    __tablename__ = 'objects'

    begin_time = Column(DateTime) # starting time range of shape
    end_time = Column(DateTime) # ending time range of shape
    # Other entries not relevant to this question

begin_time和end_time的值可以等于2006-06-01 14:45:23这样的值，它们代表了一个对象（在这种情况下是图形）覆盖的X轴范围。我想允许我的用户进行高级搜索，特别是要求所有出现在一定时间范围内的对象。然而，如何使用DateTime字段实现这一点呢？

        # Grab all shapes that appear above this certain time
        query_result = query_result.filter(
            DatabasePolygon.begin_time >= datetime.strptime(rng['btime']), %H:%M:%S')
        )

问题在于我正在将一个datetime对象与一个Y-m-d H-M-S 对象进行比较，而另一个对象只有H-M-S。一个例子是如果一个用户想要所有出现在14:45:24范围之外的对象，无论年/月/日如何，那么我们会有rng['btime']=14:45:24 和 begin_time=2006-06-01 14:45:23 ，但似乎没有过滤任何内容。

是否有一种方式可以高效地比较这个数据列中的时间？ 我很希望能够做一些像这样的事情：

        # Grab all shapes that appear above this certain time
        query_result = query_result.filter(
            DatabasePolygon.begin_time.time() >= datetime.strptime(rng['btime']), %H:%M:%S').time()
        )

- Syntactic Fructose

---

底层数据库索引在日期时间列上的工作方式意味着，除非某些非常特定的条件成立，否则没有有效的方法来实现这一点。例如，如果您的数据跨越了少量的天数，则可以为每个日期范围执行单独的有效查询。除此之外，我认为最好的方法是将所有数据提取到Python中，然后进行过滤。一些数据库支持函数索引-这将允许您做想要的事情，但我不知道SQLAlchemy是否支持它们。 - Tom Dalton

关于索引的评论，@Tom提到SQLite在3.9.0版本中添加了对表达式索引的支持（参考：这里）。因此，如果SQLAlchemy生成适当的SQL查询，则SQLite应该能够有效地处理它们。 - Gord Thompson

@GordThompson，不太确定如何理解这种情况下的索引，如果您打算回答，能否提供一些代码？ - Syntactic Fructose

https://www.sqlite.org/expridx.html - 在您的情况下，表达式将日期时间列转换为仅时间部分（然后进行索引）。 - Tom Dalton

Answer 1

根据一些条件，似乎可以做到。

 
目标1：总之，先做出来。

使用一个名为Thing的类来保存“objects”表中的id和begin_time值：

class Thing(Base):
    __tablename__ = 'objects'

    id = Column(Integer, primary_key=True)
    begin_time = Column(DateTime)

    def __repr__(self):
       return "<Thing(id=%d, begin_time='%s')>" % (self.id, self.begin_time)

在SQLite数据库的“objects”表中进行测试数据。

id  begin_time
--  -------------------
 1  1971-01-14 17:21:53
 2  1985-05-24 10:11:12
 3  1967-07-01 13:14:15

很抱歉，这个不起作用：

engine = create_engine(r'sqlite:///C:\__tmp\test.db', echo=True)

Session = sessionmaker(bind=engine)
session = Session()
for instance in session.query(Thing)\
        .filter(Thing.begin_time[11:]<'17:00:00')\
        .order_by(Thing.id):
    print(instance)

生产

未实现错误: 表达式不支持运算符'getitem'

然而，这个确实可以工作...

engine = create_engine(r'sqlite:///C:\__tmp\test.db', echo=True)

conn = engine.connect()
result = conn.execute("SELECT id FROM objects WHERE substr(begin_time,12)<'17:00:00'")
id_list = [row[0] for row in result.fetchall()]
result.close()
conn.close()

Session = sessionmaker(bind=engine)
session = Session()
for instance in session.query(Thing)\
        .filter(Thing.id.in_(id_list))\
        .order_by(Thing.id):
    print(instance)

目标2：高效地执行。

控制台输出向我们展示了第一个 SELECT 的确被执行了。

SELECT id FROM objects WHERE substr(begin_time,12)<'17:00:00'

如果我们使用的是SQLite 3.9.0或更高版本，并且创建了一个“表达式索引”

，那么...

CREATE INDEX time_idx ON objects(substr(begin_time,12));

如果使用SQLite便可以避免表扫描。不幸的是，即使在此刻最新版本（2.7.11）发布的CPython 2.7中，仍然提供一个过时的sqlite3模块。

Python 2.7.11 (v2.7.11:6d1b6a68f775, Dec  5 2015, 20:32:19) [MSC v.1500 32 bit (Intel)] on win32
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.6.21'

因此，数据库中不应该存在索引，否则SQLAlchemy将无法处理它：

sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) malformed database schema (time_idx) - near "(": syntax error [SQL: "SELECT id FROM objects WHERE substr(begin_time,12)<'17:00:00'"]

因此，如果“高效”这一部分真的很重要，那么您可能需要说服Python使用更当前版本的SQLite。有关如何做到这一点的指导可以在以下问题中找到：

强制Python放弃原生sqlite3并使用（已安装的）最新sqlite3版本

- Gord Thompson