使用Python sqlite3，我应该在任何SQL操作和commit（）之后使用sleep（）吗？

I was doing some scripted update to the sqlite database of Clementine media player (60,000 entries) and found that in the end the database was corrupted.

我正在对Clementine媒体播放器的sqlite数据库(60,000个条目)进行一些脚本更新,并发现最终数据库已损坏。

So I strongly suspected that it was that my write operations in the for-loop didn't have enough time to complete before next loop starts. I tested by time.sleep() for 2 seconds after my UPDATE call, and 15 seconds after my periodic commit(). This seems to work but the whole process became really slow. Sample code:

所以我强烈怀疑在下一个循环开始之前,我在for循环中的写操作没有足够的时间来完成。在我的UPDATE调用之后,我通过time.sleep()测试了2秒,在我的定期commit()之后15秒。这似乎有效,但整个过程变得非常缓慢。示例代码:

CommitInterval = 1000
artistCounter = 0
for artist in allArtists
    artistCounter += 1

    for record in albumRatings:
        album = record[0]
        rating = record[1]

        dbCursor.execute('UPDATE songs SET rating = ? WHERE LOWER(songs.artist) == ? AND   LOWER(songs.album) == ? AND rating < ?', (rating, artist, album, rating))

        # short sleep
        ShortSleepSec = 2
        time.sleep(ShortSleepSec)

    if artistCounter == CommitInterval:
        db.commit()
        artistCounter = 0

        # long sleep
        SleepSec = 15
        print 'Sleep %d seconds...' % (SleepSec)
        time.sleep(SleepSec)

Here are my questions: Should I really sleep after both the UPDATE and commit() or just one of them? How should I calculate how long I should sleep after these calls?

以下是我的问题:我应该在UPDATE和commit()之后真正睡觉吗?还是只有其中一个?我应该如何计算这些电话后我应该睡多久?

Thanks very much!

非常感谢!

2 个解决方案

#1

Sqlite doesn't need to sleep after a commit. Sqlite is synchronous, in-process, so by the time commit() returns, the operation is completed. But: it can be dangerous to use sqlite on more than one thread.

提交后Sqlite不需要休眠。 Sqlite在进程中是同步的,所以当commit()返回时,操作就完成了。但是:在多个线程上使用sqlite会很危险。

#2

Maybe a bit late, but

也许有点晚了,但是

you do not need to sleep between database operations

您不需要在数据库操作之间休眠

you should not insert single records over and over

你不应该反复插入单个记录

The sqlite3 module provides you with cursor.executemany(). This will heavily reduce runtime. Use it like so:

sqlite3模块为您提供了cursor.executemany()。这将大大减少运行时间。像这样使用它:

par = [ (row[1], artist, row[0], row[1]) for row in albumRatings ]
sql = 'UPDATE songs SET rating = ? WHERE LOWER(songs.artist) == ? AND   LOWER(songs.album) == ? AND rating < ?'
dbCursor.executemany(sql, par)

I would db.commit() immediately after that round.

那轮之后我会立即db.commit()。

Also, make sure that the media player does not access the database during the update process, aso not from some else invisible demon job.

此外,确保媒体播放器在更新过程中不访问数据库,因为不是来自其他隐形恶魔作业。

#1