My database gets locked all the time and I don't know why.

Here's my DBHelper class:

class DBHelper():
    def __init__(self, db = 'db1.sqlite'):
        try:
            self.conn = sqlite3.connect(db)
            self.curs = self.conn.cursor()
            print('Connection is established')
            self.conn.commit()
        except:
            return 'Connection error'
        #finally:
        #    self.conn.close()
            
    def sql_tables(self):
        self.curs.execute("CREATE TABLE IF NOT EXISTS ARTICLES(Link text PRIMARY KEY, Tittle TEXT, Time TEXT, Article TEXT)")
        self.curs.execute('CREATE TABLE IF NOT EXISTS TAGS(tag_id integer PRIMARY KEY, Tag text)')
        self.curs.execute("CREATE TABLE IF NOT EXISTS ARTICLES_TAG(Article_id text PRIMARY KEY, tag_id integer,  FOREIGN KEY (Article_id) REFERENCES ARTICLE(Link),FOREIGN KEY (tag_id) REFERENCES TAGS(tag_id))")
        self.conn.commit()

I get OperationalError: database is locked

How can I fix it?

CodePudding user response：

Sqlite is great database, but it is normally intended to be used from a single connection. The error is a hint that you tried to open it more than once, either because you create more that one DBHelper object, or because it is already used by another process.

If you really need multiple accesses you should consider using a heavier product like MariaDB or PostgreSQL.

CodePudding user response：

I suggest using threading locks if you have multiple threads that are trying to access the same database.

    def __init__(self, db = 'db1.sqlite'):
        try:
            self.conn = sqlite3.connect(db)
            self.curs = self.conn.cursor()
            print('Connection is established')
            self.conn.commit()
            self.lock = threading.Lock()   # added this line
        except:
            return 'Connection error'
        
    # use this when you want to execute a line.
    # like self.execute(line)
    def execute(self, line, fetch=None):
        """
        :param line: SQL command
        :param fetch: Number to of results to return, if none or -1 returns all
        :return: The results
        """
        ret = None
        try:
            self.lock.acquire(True)  # lock
            self.cursor.execute(line)  # run command
            if not fetch or fetch == -1:
                ret = self.cursor.fetchall()
                self.data.commit()
            else:
                ret = self.cursor.fetchmany(fetch)
                self.data.commit()
            # do something
        finally:
            self.lock.release()   # release the lock after we're done
            print(f"Returning {ret}, {line}")
            return ret

    # then maybe have a function to get stuff
    def get(self, table, column, condition=None, limit=None, first=True):
        """
        :param table: database table
        :param column: What column?
        :param condition: condition of search
        :param limit: Limit the search to X results
        :param first: Return first of every result
        :return: The results
        """

        s = f"SELECT {column} FROM {table}"
        if condition: s  = f" WHERE {condition}"
        if limit: s  = f" LIMIT {limit}"  # create line to execute
        result = self.execute(s)  # get result
        return result if result else []  #return result