Improve SQLite Write Speed in Python
SQLite is well-known for its speed, but the default implementation of SQLite in the
sqlite3 module for Python chokes when faced with writing large volumes of data, often writing at close to a tenth of the maximum write speed for the disk.
The best way around this bottleneck is to switch to asynchronous mode:
import sqlite3 conn=sqlite3.connect("database.db") curs=conn.cursor() curs.execute("PRAGMA synchronous = OFF") curs.execute("BEGIN TRANSACTION") #Perform a series of UPDATE or INSERT operations. conn.commit() conn.close()
This instructs the SQLite engine to wait until
conn.commit() is called to actually write to the disk, bypassing a long series of serial seek-writes.
For obvious reasons, this fix is not appropriate when other clients are likely to be reading from or writing to the database at the same time, but in those cases you’re probably better off using a database engine that’s designed for multiple clients.