Python SQLite - 慢速更新记录

我有一个脚本，可以将数据库中存储的日期从 Unix 时间（纪元）转换为人类可读的格式。有30,000条记录。
从数据库中提取数据、转换数据并将其打印到屏幕上的速度非常快。然而，从数据库中提取数据、转换数据并执行“更新”语句来更新记录的速度非常慢。
是否有办法优化以下代码以加快我拥有的 30,000 条记录的处理速度？

    cur.execute('select Atime from Hash where Atime like (?) ', (test,))
    results = cur.fetchall()
    for row in results:
        convertedtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime((float(row[0]))))
        print convertedtime
        cur.execute('Update Hash set Atime = (?) where Atime = (?)', (convertedtime, row[0]))
    con.commit()

The con.commit()位于 for 循环之外，因此每次迭代后提交记录不是问题。

主要的性能问题是从 SQLite 中提取数据，将其加载到 Python 中，在 Python 中进行转换，然后将其放回到数据库中一次一个约会。这永远不会有效率。

反而，使用 SQLite 自己内置的日期和时间函数 https://sqlite.org/lang_datefunc.html。看起来像atime是 Unix 纪元时间。

update hash set atime = datetime(atime, 'unixepoch', 'localtime');

But you 可能不想在本地时区存储日期。时区变得复杂，夏令时的时间有缺失和重叠……这只会让人流泪。您绝对不想在不指示该时区的情况下将日期时间存储在本地时区！

除非您有充分的理由，否则请将其存储为 UTC。

update hash set atime = datetime(atime, 'unixepoch');

一般来说，如果你想做 SQLite 不支持的事情，创建用户定义的函数并在查询中使用它。这将比使用内置 SQLite 函数效率低，但比选择、转换和更新更有效。

它看起来像这样。

def epoch_to_iso8601(epoch):
    return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime((float(epoch))))

con.create_function("epoch_to_iso8601", 1, epoch_to_iso8601)

然后你可以使用epoch_to_iso8601在查询中。

update hash set atime = epoch_to_iso8601(atime);

请注意，这与存储过程不同。因为没有 SQLite 服务器，所以所有代码都在您的进程中运行，此功能是每个进程的。

See sqlite3.create_function https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.create_function.

这里真正的问题是您将日期时间存储为字符串。这使得与他们合作变得缓慢且尴尬。这意味着您必须选择一种格式。这意味着您必须解析该格式才能对其执行任何操作。这意味着您无法使用内置的 SQLite 日期和时间函数（尽管它们很稀疏）。

你真正想做的是离开atime作为 Unix 纪元时间并根据每个查询的需要对其进行格式化。

select datetime(atime, 'unixepoch') from hash;

幸运的是，SQLite 的类型非常宽松，并且会转换文本atime字段为您提供一个数字，尽管这会导致性能和存储损失。

理想情况下你想改变atime使用datetime类型，但这在 SQLite 中很困难。它不支持删除或修改现有列。相反，您必须转储表中的数据，重新创建表，然后导入数据。仅 30,000 条记录，这应该非常快。

切换到 CSV 模式，将输出发送到文件，然后选择所有内容。

sqlite> .mode csv hash
sqlite> .output hash.out
sqlite> select * from hash;

删除现有表并以相同方式重新创建它，但使用atime as a datetime.

sqlite> drop table hash;
sqlite> create table hash ( atime datetime, and the other columns );

导入转储。

sqlite> .import hash.out hash