将插入合并到一个事务中Python SQLite3 [英] Combine inserts into one transaction Python SQLite3

问题描述

我正在尝试使用插入在SQLite3上输入1000行，但是插入所需的时间太长.我听说，如果将插入项合并为一个事务，则速度会大大提高.但是，我似乎无法让SQlite3跳过检查文件是否已写入硬盘的操作.

I am trying to input 1000's of rows on SQLite3 with insert however the time it takes to insert is way too long. I've heard speed is greatly increased if the inserts are combined into one transactions. However, i cannot seem to get SQlite3 to skip checking that the file is written on the hard disk.

这是一个示例:

if repeat != 'y':
    c.execute('INSERT INTO Hand (number, word) VALUES (null, ?)', [wordin[wordnum]])
    print wordin[wordnum]

data.commit()

这是我一开始所拥有的.

This is what i have at the begining.

data = connect('databasenew')
data.isolation_level = None
c = data.cursor()  
c.execute('begin')

但是，这似乎没有什么不同.一种提高插入速度的方法将不胜感激.

However, it does not seem to make a difference. A way to increase the insert speed would be much appreciated.

推荐答案

根据Sqlite文档， BEGIN 事务应以 COMMIT

According to Sqlite documentation, BEGIN transaction should be ended with COMMIT

可以使用BEGIN命令手动启动事务.这样的事务通常会持续到下一次COMMIT或ROLLBACK命令.但是，如果数据库是已关闭或发生错误且ROLLBACK冲突已解决指定了算法.请参阅关于ON CONFLICT的文档子句，以获取有关ROLLBACK冲突的更多信息解析算法.

Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.

因此，您的代码应如下所示:

So, your code should be like this:

data = connect('databasenew')
data.isolation_level = None
c = data.cursor()  
c.execute('begin')

if repeat != 'y':
    c.execute('INSERT INTO Hand (number, word) VALUES (null,?)', [wordin[wordnum]])
    print wordin[wordnum]

    data.commit()

    c.execute('commit')

这篇关于将插入合并到一个事务中Python SQLite3的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！