Reputation: 751
I have a database to which I am inserting rows and populating with values. I am currently using sqlite3 on Python 3. What I find surprising is that if I simply insert the rows/values manually one at a time (e.g. iterations = 1
), this will work. Additionally, if I simply keep iterations
below (approximately) 100, it will work as well! But as I increase the number of iterations, there tends to be some randomly varying number (typically below 1000) of iterations that I cannot exceed for some reason, and I observe the error copied below each time.
What is causing this error? How can it be overcome so that I can make iterations
as large as necessary (e.g. 1000000
)? Below is a small and simplified snippet from a larger code:
column1 = 'id'
column2 = 'shot'
column3 = 'time'
column4 = 'psi'
column5 = 'temp'
column6 = 'dens'
column7 = 'temp_err'
column8 = 'dens_err'
iterations = 1000
timeout = 100
i = 0
while i < iterations:
try:
time = 3
psi = 2
unique_id = 232
temp = 0.4
dens = 0.2
temp_err = 0.02
dens_err = 0.01
values = [str(unique_id),str(shot),time,psi,temp,dens,temp_err,dens_err]
conn = sqlite3.connect(sqlite_file,timeout=timeout)
cursor = conn.cursor()
cursor.execute("INSERT INTO {tn} ({c1},{c2},{c3},{c4},{c5},{c6},{c7},{c8}) VALUES ({o1},{o2},{o3},{o4},{o5},{o6},{o7},{o8})".\
format(tn=table_name,c1=column1,c2=column2,c3=column3,c4=column4,c5=column5,c6=column6,c7=column7,c8=column8,o1=values[0],\
o2=values[1],o3=values[2],o4=values[3],o5=values[4],o6=values[5],o7=values[6],o8=values[7]))
conn.commit()
conn.close()
except sqlite3.IntegrityError:
print('ERROR: ID already exists in PRIMARY KEY column {}'.format(column1))
i = i + 1
The error I observe is:
Traceback (most recent call last):
File "<ipython-input-27-59d2691987a1>", line 18, in <module>
o2=values[1],o3=values[2],o4=values[3],o5=values[4],o6=values[5],o7=values[6],o8=values[7]))
OperationalError: disk I/O error
I've tried increasing timeout
and connecting/committing/closing the connection to the database outside of the loop, but these approaches have not worked.
Another possible solution that worked for me:
column1 = 'id'
column2 = 'shot'
column3 = 'time'
column4 = 'psi'
column5 = 'temp'
column6 = 'dens'
column7 = 'temp_err'
column8 = 'dens_err'
iterations = 10000
timeout = 100
with sqlite3.connect(sqlite_file,timeout=timeout) as conn:
cursor = conn.cursor()
i = 0
while i < iterations:
try:
time = 3
psi = 2
unique_id = 232
temp = 0.4
dens = 0.2
temp_err = 0.02
dens_err = 0.01
values = [str(unique_id),str(shot),time,psi,temp,dens,temp_err,dens_err]
cursor.execute("INSERT INTO {tn} ({c1},{c2},{c3},{c4},{c5},{c6},{c7},{c8}) VALUES ({o1},{o2},{o3},{o4},{o5},{o6},{o7},{o8})".\
format(tn=table_name,c1=column1,c2=column2,c3=column3,c4=column4,c5=column5,c6=column6,c7=column7,c8=column8,o1=values[0],\
o2=values[1],o3=values[2],o4=values[3],o5=values[4],o6=values[5],o7=values[6],o8=values[7]))
except sqlite3.IntegrityError:
print('ERROR: ID already exists in PRIMARY KEY column {}'.format(column1))
i = i + 1
print(i)
conn.commit()
Upvotes: 1
Views: 1369
Reputation: 427
How is the database set up on the drive? It's possible that the drive is full and it can't write anymore, or your system is having trouble handling the throughput.
Check to see if the drive sqlite3 uses is full, and if not, try adding in a short delay between insertions. Alternatively, you can try pre-calculating all the data you want to insert and running one large bulk insert rather than multiple small ones.
It shouldn't be related to your Python code and more on the sqlite3/hardware side of things.
Edit
As per @Jonathan Willcock 's comment, you can also try wrapping all the inserts into one transaction.
To do this, you would have to shuffle some things around. The flow is supposed to be like this:
open connection > start transaction > run queries > commit transaction on success / rollback transaction on error > close connection
try:
conn = sqlite3.connect(sqlite_file, timeout=timeout, isolation_level=None)
cursor = conn.cursor()
while i < iterations:
# your loop here
conn.commit()
except sqlite3.IntegrityError:
conn.rollback()
# other error handling here
finally:
conn.close()
Upvotes: 1