sqlite3_backend.py

import sqlite3
import time

def modify_db(conn,
              query,
              *args):
    """Wrapper for modifications (INSERT, UPDATE, DELETE or REPLACE) of Sqlite3 database.
    Executes given query and commits it - in case of lock it retries the commit
    Params
    ------
    connection: sqlite3 connection
        existing sqlite3 connection to use
    query: sql
        query to run
    args: array
        additional arguments for execute query, optional
    """
    upperbound = 11

    with conn:
        c = conn.cursor()
        # retry db execute
        for x in range(1, upperbound):
            try:
                if args:
                    c.execute(query, *args)
                else:
                    c.execute(query)
            except Exception as e:
                print("Sqlite3 execute unsuccessful, reason: \"%s\" \nRetrying after %s sec...." % (str(e), x))
                time.sleep(x)
                pass
            else:
                print("Sqlite3 execute successful, breaking the retry cycle.")
                break
            # max attempts achieved, quit
            if x == upperbound - 1:
                raise Exception("Sqlite3 - achieved max retries to execute with no success, giving up...")

    # retry db commit
    for x in range(1, upperbound):
        try:
            conn.commit()
        except Exception as e:
            print("Sqlite3 commit unsuccessful, reason: \"%s\" \nRetrying after %s sec...." % (str(e), x))
            time.sleep(x)
            pass
        else:
            print("Sqlite3 commit successful, breaking the retry cycle.")
            break
        # max attempts achieved, quit            
        if x == upperbound - 1:
            raise Exception("Sqlite3 - achieved max retries to commit with no success, giving up...")