Python-SQLite慢更新记录

Question

我有一个脚本，可以将数据库中存储的日期从Unix时间（epoch）转换为人类可读的格式。有30000条记录 从数据库中提取数据，进行转换，并将其打印到屏幕上非常快。但是，要从数据库中提取数据，转换数据并执行“update”语句来更新记录，速度非常慢。
有没有优化下面的代码，以加快这个过程，为30000个记录，我有？在

    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循环之外，因此在每次迭代之后提交记录不是问题。在

Answer 1

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

相反，use SQLite's own built in date and time functions。看起来atime是Unix纪元时间。在

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

但您可能不想将日期存储在本地时区中。时区变得复杂了，夏令时有缺失和重叠的时间。。。只会让人流泪。你肯定不想在本地时区中存储一个日期时间而不指明该时区是什么！在

除非有很好的理由，否则将其存储为UTC。在

^{pr2}$

---

一般来说，如果您想做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 epoch时间，并根据每个查询的需要对其进行格式化。在

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

幸运的是，SQLite的类型非常松散，它会将文本atime字段转换为数字，尽管这会导致性能和存储的损失。在

---

理想情况下，您希望将atime更改为使用datetime类型，但这在SQLite中很难实现。或不支持删除现有列。相反，您必须转储表中的数据，重新创建表，然后导入数据。这应该是非常快，只有30000个记录。在

切换到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