Skip to content

Instantly share code, notes, and snippets.

@17twenty
Created February 5, 2014 19:32
Show Gist options
  • Save 17twenty/8831301 to your computer and use it in GitHub Desktop.
Save 17twenty/8831301 to your computer and use it in GitHub Desktop.
How to fix problems with sqlite3.OperationalError: disk I/O error in Python
"""
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)
@obskyr
Copy link

obskyr commented Dec 2, 2016

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment