exception handling with sqlite db errors

[CM]

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

Thanks, Aahz, I'll try to implement something along these lines.

Che