Python SQLite - 慢的更新记录

Question

我有一段脚本，可以将数据库中存储的Unix时间（epoch）转换为人类可读格式。共有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()

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

- Dr.Pepper

---

这会有任何变化吗：https://sqlite.org/pragma.html#pragma_synchronous - Klaus D.

数据库文件有多大？您可以尝试将其全部导入pandas，进行操作，然后导出到sqlite。 - ryanskeith

可能是因为该字段未被索引？ - asiviero

Atime 是什么类型？看起来它是一个文本字段，这是个问题。 - Schwern

我已经关闭了Pragma Synchronous并将Atime字段更改为NUMERIC，但没有任何变化。 - Dr.Pepper

Answer 1

主要的性能问题在于你从SQLite中提取数据，加载到Python中，在Python中进行转换，然后逐个日期将其放回数据库。这永远不会高效。

相反，使用SQLite自己内置的日期和时间函数。看起来atime是Unix纪元时间。

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

但是你可能不想将日期存储在本地时区。时区变得复杂，还有夏令时，其中有缺失和重叠的时间...这只会导致麻烦。如果没有指定该时区，绝对不要将日期时间存储在本地时区中！除非你有一个非常好的理由，否则请将其存储为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服务器，所有代码都在您的进程中运行，因此此函数是每个进程的函数。

请参见sqlite3.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作为datetime。

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

导入转储。

sqlite> .import hash.out hash

- Schwern

---

谢谢提供的信息，伙计 - 我会仔细查看并进行一些测试，然后回报！ - Dr.Pepper

谢谢你的帮助 - 我最终使用了以下命令：cur.execute("UPDATE Hash SET Atime = datetime(Atime, (?))", ('unixepoch'))。非常顺利！ - Dr.Pepper