FAQ
I am using SQLite with Python 2.5 for an app and every now and then
get an error when trying to write to the database. So far I haven't
been careful about noting these down, but now I would like to address
them. The two errors I've noticed are:

1) "database is locked" errors (probably due to write conflicts when
using SQLite Database Browser to check and change the data while my
app was trying to write to the db; this might be a problem for users
if they too want to edit their data that way while running the app,
though that scenario is pretty unlikely).

2) Today for the first time,"sqlite3.OperationalError: attempt to
write a readonly database"--though I never set this database to be
read-only. When I tried it a second time, it worked and didn't give
this error.

What I would like is a way to innoculate my app from these errors such
that if they occur they don't cause any problems with the app. I'm
not even sure if they do cause problems, since I can't figure out a
way to reproduce the errors and see whether it causes the app to hang
or not update the GUI properly, etc.

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.

Any suggestions for addressing this are appreciated.

Che

Search Discussions

  • Aahz at Aug 12, 2010 at 7:31 pm
    In article <2a47b306-45d1-474a-9f8e-5b71eba629c9 at p11g2000prf.googlegroups.com>,
    CM 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
  • CM at Aug 18, 2010 at 10:07 pm

    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:
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedAug 1, '10 at 8:34p
activeAug 18, '10 at 10:07p
posts3
users2
websitepython.org

2 users in discussion

CM: 2 posts Aahz: 1 post

People

Translate

site design / logo © 2022 Grokbase