How to fix problems with sqlite3.OperationalError: disk I/O error in Python

fixSQL.py

"""

You'll most likely notice you have a something.db-journal file - that was my first sign!

I ended up writing a class to abstract stuff away but the key line is, when creating the table, execute the pragma line:
PRAGMA journal_mode = OFF

http://www.stevemcarthur.co.uk/blog/post/some-kind-of-disk-io-error-occurred-sqlite/ and for more information see here http://www.sqlite.org/pragma.html

I found this was due to weird permissions with the default DELETE option. TRUNCATE works as well as OFF

"""


class Notes():
    def __init__(self):
        pass
   
    def createDatabase(self):
        """ The goal here is to ensure we have a sane/working default DB """
        conn = self.__openAndGetConnection()
        cur = conn.cursor()
        # Drop it if it already exists
        cur.execute('''PRAGMA journal_mode = OFF''')
        cur.execute("DROP TABLE IF EXISTS notes")
        # Make a new shiny one
        cur.execute('''CREATE TABLE IF NOT EXISTS notes
             (id INTEGER PRIMARY KEY, hits INT, title TEXT, author TEXT, note TEXT, creation TIMESTAMP, expiration TIMESTAMP)''')
        tempStamp = datetime.datetime.now()

        self.addNote("This is the first note in the database", expiration=tempStamp);
        
        # Commit it all
        conn.commit()
        self.__close(conn)
        
    def addNote(self, note, title="Untitled Masterpiece", expiration=None, author=u"Anonymous"):
        if not expiration:
            # Limit to 1 hour
            expiration = datetime.datetime.now() + (datetime.timedelta(hours=1))
            
        # Make with the unicode
        note = unicode(note)
        title = unicode(title)
        author = unicode(author)
        
        # Open it up and slap it in
        conn = self.__openAndGetConnection()
        cur = conn.cursor()
        
        demoNote = (0, title, author, note, datetime.datetime.now(), expiration) 
        cur.execute('''INSERT INTO notes VALUES (NULL, ?, ?, ?, ?, ?, ?)''', demoNote)
        retVal = cur.lastrowid
        conn.commit()
        self.__close(conn)
        return base62.fromInt(retVal)
        
    def __openAndGetConnection(self):
        """ Open the database and hand back tuples """
        return sqlite3.connect(r"notes_ARGH.db")
        
    def __close(self, connection):
        connection.close()
    
    def getAllNotes(self):
        """ Should be able to get all notes and key value that shit - doesn't update the hit count """
        conn = self.__openAndGetConnection()
        cur = conn.cursor()
        cur.execute('''SELECT * FROM notes''')
        conn.commit()
        retVal = cur.fetchall()
        self.__close(conn)
        return retVal
        
    def getAllNoteIDs(self):
        conn = self.__openAndGetConnection()
        cur = conn.cursor()
        cur.execute('''SELECT id,title FROM notes ORDER BY id DESC''')
        conn.commit()
        retVal = cur.fetchall()
        if retVal:
            retVal = [(base62.fromInt(i[0]),i[1]) for i in retVal]
        self.__close(conn)
        return retVal
    
    def getNoteByID(self, noteID):
        """ 
            Gets a note with from a given hashed ID - updates the hit count
        """
        val = (base62.toInt(str(noteID)),)
        conn = self.__openAndGetConnection()
        cur = conn.cursor()
        cur.execute('''UPDATE notes SET hits = hits + 1 WHERE id=(?)''', val)
        cur.execute('''SELECT hits, title, author, note, creation, expiration FROM notes WHERE id=(?)''', val)
        conn.commit()
        retVal = cur.fetchone()
        self.__close(conn)
        return retVal
        
    def deleteNoteByID(self, noteID):
        """ Should allow the deleting of a specific note by the hashed ID """
        val = (base62.toInt(unicode(noteID)),)
        conn = self.__openAndGetConnection()
        cur = conn.cursor()
        cur.execute('''DELETE FROM notes WHERE id=(?)''', val)
        conn.commit()
        self.__close(conn)

Worth mentioning is that this will disable rollback. Without journal mode, SQLite does not support atomic transactions (or even transactions at all, for that matter). If you don't need those, it's just fine, but for most cases you probably do.