I ran a small test regarding insertion speed with a sqlite3 database on my HDD. This is the code.
import sqlite3
import json
import time
db_conn = sqlite3.connect("test_database.db")
db_cursor = db_conn.cursor()
db_cursor.execute("CREATE TABLE IF NOT EXISTS DataTable (json_data TEXT)")
test_start = time.time()
for i in range(0,500):
dict_to_add = {"number": str(i), "value": "sadhashdaszdas9dzas0ds9d69ad6adsadsasda6"}
db_cursor.execute("INSERT INTO DataTable (json_data) VALUES(?)", (json.dumps(dict_to_add),))
db_conn.commit()
test_end = time.time()
db_cursor.close()
db_conn.close()
print ("Duration:", round(test_end-test_start, 2), "seconds.")
It took 41 seconds for these 500 really small inserts. That's how I usually insert values to a database and on SSD it seems decently fast, but on HDD it is slow even for these small inserts.
Is there a way to make it go faster without having invalid entries (I read about PRAGMA synchronous = OFF, but if it gets interrupted there would be invalid entries)?
EDIT
Here is what I ended up using, it is executemany method, which seems to work much faster (0.08 seconds).
def multitransaction_test():
db_conn = sqlite3.connect("test_database.db")
db_cursor = db_conn.cursor()
db_cursor.execute("CREATE TABLE IF NOT EXISTS DataTable (json_data TEXT)")
test_start = time.time()
vals_to_insert = []
for i in range(0,500):
dict_to_add = {"number": str(i), "value": "sadhashdaszdas9dzas0ds9d69ad6adsadsasda6"}
vals_to_insert.append( (json.dumps(dict_to_add),) )
#db_cursor.execute("INSERT INTO DataTable (json_data) VALUES(?)", (json.dumps(dict_to_add),))
#db_conn.commit()
print ('adding')
try:
db_cursor.executemany("INSERT INTO DataTable (json_data) VALUES (?)", tuple(vals_to_insert))
db_conn.commit()
except:
print ("An exception during insertion!")
db_conn.rollback()
test_end = time.time()
db_cursor.close()
db_conn.close()
print ("Duration:", round(test_end-test_start, 2), "seconds.")
return