按时间筛选 sqlalchemy sqlite 日期时间列

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

问题是我正在将带有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()
        )

Answer 1

这似乎是可能的，但有几个条件。

 
目标1：做（完全）。

使用名为 Thing 的类来保存"对象"表中的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 数据库的 "对象" 表中测试数据

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:__tmptest.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:__tmptest.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将能够避免表扫描。 不幸的是，即使是目前最新版本的CPython 2.7（2.7.11）仍然附带了一个太旧的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.数据库错误） 格式错误的数据库架构 （time_idx） - 靠近 "（"： 语法错误 [SQL： "从对象中选择 ID WHERE substr（begin_time，12）<'17：00：00'"]

因此，如果"有效"部分非常重要，那么您可能需要说服Python使用更新版本的SQLite。可以在问题中找到一些指导

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