How to unlock database with sqlite3?

Question

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?

It is likely that you made a previous failed attempt at the code in the same session and so the files are still open. Restart your interpreter and see if that fixes it. — BoarGules
– BoarGules, Commented Jun 1, 2022 at 7:55

Serge Ballesta · Accepted Answer · 2022-06-01 07:42:51Z

1

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.

answered Jun 1, 2022 at 7:42

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rock · Accepted Answer · 2022-06-01 14:03:15Z

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

Collectives™ on Stack Overflow

How to unlock database with sqlite3?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related