Combine inserts into one transaction Python SQLite3

Question

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.

this is a sample:

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.

Answer 1

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

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')