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

user_9022375 0
Viewed 23015

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!

disk-io relational-database sqlite python
5 Answers

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)

user_88321680

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..

user_146815120

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.

user_27813500

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')
user_108475850
Related