I/O error while writing to an SQL database

Question

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

I remember reading somewhere that sqlite automatically puts any write SQL statement into a transaction unless a transaction has been explicitly provided. Multiple inserts therefore generate multiple transactions. Try providing an explicit transaction around your insert loop, so that all your inserts happen within one transaction, thus simplifying I/O — Jonathan Willcock
– Jonathan Willcock, Commented Apr 13, 2019 at 0:54
@JonathanWillcock Sorry, quite novel to SQL. How exactly could I provide an explicit transaction around the entire loop? I tried using isolation_levelas described here when beginning each of the connections, but that did not solve it. Would you be able to refer me to an example/code of your suggestion? @Yidna — Mathews24
– Mathews24, Commented Apr 13, 2019 at 1:28
@Mathews24 I edited my answer to include a general overview of how to do this :) — Yidna
– Yidna, Commented Apr 13, 2019 at 1:38
@Yidna Awesome and thank you! I added a solution I was working on to my OP which is similar to the one you provided. — Mathews24
– Mathews24, Commented Apr 13, 2019 at 2:04
Nice! I always make sure to either end on a rollback or commit lest I have a bunch of open transactions locking the tables, so heads up. — Yidna
– Yidna, Commented Apr 13, 2019 at 4:25

Yidna · Accepted Answer · 2019-04-13 01:38:02Z

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

Collectives™ on Stack Overflow

I/O error while writing to an SQL database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related