[Python] exception handling with sqlite db errors

On Aug 12, 3:31?pm, a... at pythoncraft.com (Aahz) wrote:
In article <2a47b306-45d1-474a-9f8e-5b71eba62... at p11g2000prf.googlegroups.com>,

CM?wrote:
Wrap all your uses of sqlite into a function that does the try/except;
you only write the code once, then. ?As you progress, you can also
change the code to retry operations. ?Here's some ugly code I wrote on
top of SQLObject:

from sqlobject.dbconnection import registerConnection
from sqlobject.sqlite.sqliteconnection import SQLiteConnection

class RetrySQLiteConnection(SQLiteConnection):
? ? """
? ? Because SQLite is not really concurrent, having multiple processes
? ? read/write can result in locked DB failures. ?In addition, SQLObject
? ? doesn't properly protect operations in transations, so you can get
? ? spurious DB errors claiming that the DB is corrupt because of
? ? foreign key integrity failures.

? ? This subclass retries DatabaseError and OperationalError
? ? exceptions.
? ? """
? ? MAX_RETRIES = 4
? ? SAFE_DB_ERROR = [
? ? ? ? 'database disk image is malformed',
? ? ? ? 'file is encrypted or is not a database',
? ? ? ? ]

? ? def _safe_db_error(self,exception):
? ? ? ? err = str(exception).lower()
? ? ? ? for safe_err in self.SAFE_DB_ERROR:
? ? ? ? ? ? if safe_err in err:
? ? ? ? ? ? ? ? return True
? ? ? ? return False

? ? def _check_integrity(self):
? ? ? ? conn = self.getConnection()
? ? ? ? try:
? ? ? ? ? ? i = 0
? ? ? ? ? ? while True:
? ? ? ? ? ? ? ? i += 1
? ? ? ? ? ? ? ? try:
? ? ? ? ? ? ? ? ? ? cursor = conn.cursor()
? ? ? ? ? ? ? ? ? ? query = "pragma integrity_check"
? ? ? ? ? ? ? ? ? ? SQLiteConnection._executeRetry(self, conn, cursor, query)
? ? ? ? ? ? ? ? ? ? result = cursor.fetchall()
? ? ? ? ? ? ? ? ? ? if result == [('ok',)]:
? ? ? ? ? ? ? ? ? ? ? ? return True
? ? ? ? ? ? ? ? ? ? else:
? ? ? ? ? ? ? ? ? ? ? ? logging.error("Bad integrity result: %s", result)
? ? ? ? ? ? ? ? ? ? ? ? return False
? ? ? ? ? ? ? ? except DatabaseError, e:
? ? ? ? ? ? ? ? ? ? if i < self.MAX_RETRIES:
? ? ? ? ? ? ? ? ? ? ? ? logging.info('integrity_check, try #%s: %s', i, e)
? ? ? ? ? ? ? ? ? ? ? ? time.sleep(2)
? ? ? ? ? ? ? ? ? ? else:
? ? ? ? ? ? ? ? ? ? ? ? logging.error('integrity_check, try #%s: %s', i, e)
? ? ? ? ? ? ? ? ? ? ? ? raise
? ? ? ? finally:
? ? ? ? ? ? self.releaseConnection(conn)

? ? def _executeRetry(self, conn, cursor, query):
? ? ? ? i = 0
? ? ? ? while True:
? ? ? ? ? ? i += 1
? ? ? ? ? ? try:
? ? ? ? ? ? ? ? return SQLiteConnection._executeRetry(self, conn, cursor, query)
? ? ? ? ? ? except OperationalError, e:
? ? ? ? ? ? ? ? if i < self.MAX_RETRIES:
? ? ? ? ? ? ? ? ? ? logging.warn('OperationalError, try #%s: %s', i, e)
? ? ? ? ? ? ? ? ? ? time.sleep(10)
? ? ? ? ? ? ? ? else:
? ? ? ? ? ? ? ? ? ? logging.error('OperationalError, try #%s: %s', i, e)
? ? ? ? ? ? ? ? ? ? raise
? ? ? ? ? ? except DatabaseError, e:
? ? ? ? ? ? ? ? if e.__class__ is not DatabaseError:
? ? ? ? ? ? ? ? ? ? # Don't retry e.g. IntegrityError
? ? ? ? ? ? ? ? ? ? raise
? ? ? ? ? ? ? ? if not self._safe_db_error(e):
? ? ? ? ? ? ? ? ? ? # Only retry specific errors
? ? ? ? ? ? ? ? ? ? raise
? ? ? ? ? ? ? ? if not self._check_integrity():
? ? ? ? ? ? ? ? ? ? raise
? ? ? ? ? ? ? ? if i < self.MAX_RETRIES:
? ? ? ? ? ? ? ? ? ? logging.warn('DatabaseError, try #%s: %s', i, e)
? ? ? ? ? ? ? ? ? ? time.sleep(0.5)
? ? ? ? ? ? ? ? else:
? ? ? ? ? ? ? ? ? ? logging.error('DatabaseError, try #%s: %s', i, e)
? ? ? ? ? ? ? ? ? ? raise

def conn_builder():
? ? return RetrySQLiteConnection

registerConnection(['retrysqlite'], conn_builder)

def init():
? ? dbpath = os.path.join(common.getSyncDataPath(), app.dbname)
? ? connection_string = "retrysqlite:" + dbpath
? ? global _connection
? ? _connection = connectionForURI(connection_string)
--
Aahz (a... at pythoncraft.com) ? ? ? ? ? <*> ? ? ? ?http://www.pythoncraft.com/

"...if I were on life-support, I'd rather have it run by a Gameboy than a
Windows box." ?--Cliff Wells