disk I/O error with SQLite3 in Python 3 when writing to a database

Question

i am a student just starting out with python, and i was tasked with creating a relational database management system. I think i came pretty far, but i seem to have hit a wall. This is my code:

import csv
import sqlite3

conn = sqlite3.connect('unfccc.db')
c = conn.cursor()

c.execute('''CREATE TABLE unfccc (
        Country TEXT, 
        CodeCountryFormat TEXT, 
        NamePollutant TEXT, 
        NameYearSector TEXT, 
        NameParent TEXT, 
        Sector TEXT, 
        CodeSector TEXT, 
        CNUEDSPD TEXT
        )''')

def insert_row(Country, CodeCountryFormat, NamePollutant, NameYearSector, NameParent, Sector, CodeSector, CNUEDSPD):
    c.execute("INSERT INTO unfccc VALUES (?, ?, ?, ?, ?, ?, ?, ?)", (Country, CodeCountryFormat, NamePollutant, NameYearSector, NameParent, Sector, CodeSector, CNUEDSPD))
    conn.commit()

with open('UNFCCC_v20.csv') as csvfile:
    readCSV = csv.reader(csvfile, delimiter='\t')

    counter = 0

    for row in readCSV:
        insert_row(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7])
        counter = counter+1
        print('%d down, more to go' % counter)
conn.close()

when i run it with line 4 directing the input towards :memory: it works perfectly and i have myself what i think is a relational database.

However, when i try to run the code like this, writing the data to a db file, i get this error:

 File "<ipython-input-13-4c50216842bc>", line 19, in insert_row
    c.execute("INSERT INTO unfccc VALUES (?, ?, ?, ?, ?, ?, ?, ?)", (Country, CodeCountryFormat, NamePollutant, NameYearSector, NameParent, Sector, CodeSector, CNUEDSPD))

OperationalError: disk I/O error

I've searched stackoverflow, and i've used google, but i don't think any of the cases i found match up to what i'm trying to do here (or i don't have the knowledge to figure out whats going on). One other thing i noticed about my code is that it inputs the data into memory super fast, but when i write to a db file it is really slow, it shouldn't be a hardware limit as i am using an SSD. Any help will be greatly appreciated!

Are you writing to an NFS? See this: [link](https://stackoverflow.com/questions/29244788/error-disk-i-o-error-on-a-newly-created-database) — Josh Dinsdale, Nov 28 '17 at 20:56
Looks like a trivial problem like write permissions, try to write to a directory where you are sure to have write permission. — Marco, Nov 28 '17 at 21:25

score 13 · Accepted Answer · answered Jan 27 '18 at 19:40

13

Setting Backup/Sync to pause on the system tray icon while working with a project stored on Google Drive will prevent disk i/o errors.

This is because when the file is written to or changed, backup & sync attempts to upload the new version to your Google Drive, while it is doing this; the file becomes a 'Read-Only' file.

While sync is paused your Google Drive folder acts more like a normal directory.

(click -> settings -> pause/resume)

answered Jan 27 '18 at 19:40

Owen Easter

195
1
8

3

I had a similar issue operating off of a file synced by Dropbox. Pausing the Dropbox sync solved the issue. – chenware Mar 31 '18 at 17:12
1

@chenware - Same here. I can confirm that this issue occurs when using Dropbox as well, which makes sense because it uses roughly the same process as Google Drive. – Pikamander2 Dec 10 '19 at 10:54

score 3 · Answer 2 · answered Mar 10 '21 at 22:04

3

I know I might be answering really late, but for others... I had the same problem, and the problem I found was that a python code was already using that database. When I stoped the code and ran the main code again, it did worked..

answered Mar 10 '21 at 22:04

RaPiDfire69

95
1
6

score 2 · Answer 3 · answered Nov 12 '18 at 23:03

Another cause for this problem is if the journal file is not writable, but the SQLite data file is writable. If the SQLite data file isn't writable, it will tell you you're trying to write to a read-only database. But if the database file is writable, but the journal file (filename same as the SQLite data file, but ending in -journal) isn't writable, it will give you an I/O error instead.

score 0 · Answer 4 · answered Dec 26 '20 at 03:49

0

Not an issue with yours but I was getting an error using uppercase characters in my db file name. Changed it and fixed this issue... Worth a try for others!

sqlite3.connect('lowercase_name.db')

answered Dec 26 '20 at 03:49

Tanner Clark

533
7
16

score -2 · Answer 5 · answered Dec 08 '17 at 14:11

-2

The answer was simple, i was working from my Google Drive directory on my personal computer, i was offline at the time of the error and the Backup and Sync program was not running therefore it took me a while to realize that i still don't have full permissions over the folder.

Moving my script and necessary files to a folder in my Documents fixed the error. Thanks for the help Marco and Josh!

answered Dec 08 '17 at 14:11

Xander Blaauw

119
1
1
4

You may have found a solution but your answer is quite misleading and not well informed - and your advice is not helpful for others. – Owen Easter Jul 11 '20 at 15:41

disk I/O error with SQLite3 in Python 3 when writing to a database

5 Answers5

Linked