exception handling with sqlite db errors

[Aahz]

In article <2a47b306-45d1-474a-9f8e-5b71eba629c9 at p11g2000prf.googlegroups.com>,
CM  <cmpython at gmail.com> wrote:
>
>Maybe it's not much of an issue, but I think it would be a shame if
>occasional hangs/crashes could be caused by these (rare?) database
>conflicts if there is a good approach for avoiding them.  I guess I
>could put every last write to the db in a try/except block but I
>thought there should be a more general solution, since that will
>require many such exceptions and seems inelegant.

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 (aahz 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