Is there any way to get rid of database is locked using Python Sqlite3?

Question

I am new to Python.

def checkstock(self):
    con = sqlite3.connect(database=r'ims.db')
    cur = con.cursor()
    cur.execute("PRAGMA read_uncommitted = true;")
    try:
        cur.execute("Select occode from ordercart")
        occode = cur.fetchall()
        for elem in occode:
            for y in range (20):
                for x in range(1,8):
                    cur.execute(f"Select occolor{x} from ordercart where occode=? limit 1 offset {int(y)}", elem)
                    occolor = cur.fetchone()
                    cur.execute(f"Select ocxs{x} from ordercart where occode=? limit 1 offset {int(y)}", elem)
                    ocxs = cur.fetchone()
                    cur.execute(f"Select nixs{x} from newitem where nicode=? limit 1 offset {int(y)}", elem)
                    nixs = cur.fetchone()
                    for ordercartxs in ocxs or ():
                        for newitemxs in nixs or ():
                            a = int(nixs[0]) - int(ocxs[0])
                            if a < 0 :
                                messagebox.showerror("Error", f"Stock {elem} {occolor} Size XS :{a}", parent = self.root)
                            else:
                                print(a)
                                cur.execute(f"Update newitem set nixs{x}=? where nicode=?",(a, elem[0],))
                                con.commit()
        messagebox.showinfo("Successful","IV and DO created successfully")
    except Exception as ex:
        messagebox.showerror("Error",f"Error due to : {str(ex)}", parent = self.root)

This is the code. But it always shows "database is locked" and the output failed. I realize that there is a ims.db-journal file keep showing up whenever I run the program. Is there any solution for this? Tqsm!

Answer 1

The problem

The ims.db-journal file is a temporary file that should be deleted when the transaction concludes. Therefore its presence indicates a crashed session.

In other words, some process has acquired a lock on the database and the transaction hasn't been closed properly.

That question I've linked above may have some useful information, but I'll focus on the Python code. I would suggest using a context manager with your database connection.

Why?

At the moment, nothing in your code is closing the connection that you've opened to the database, and your exception block isn't calling con.rollback(). Thus, if there's a problem with your code and it never reaches con.commit() (which, importantly, can happen even under non-exceptional operation if a<0) you will be left with an open transaction, plus an open DB connection.

The fix

import contextlib
...

def checkstock(self):

    with contextlib.closing(
        sqlite3.connect(database=r'ims.db')
    ) as con:
    
        cur = con.cursor()
        ...  # your code here

Helpfully, if you use a context block (with) in concert with a sqlite3 connection, any failed transactions will be rolled back when the block exits.

Less helpfully, exiting the 'with block' doesn't close the connection! For this reason I've added contextlib.closing to ensure that also happens.

Now when your code runs (whether the context block exits normally when you reach the end or some exception occurs) the transaction will be closed properly and so will the database connection.

If, when this happens, you haven't committed the transaction (e.g. due to an exception), it should be rolled back.

Further thoughts

What you're doing here is quite 'close to the metal' by Python standards. You might consider using sqlalchemy instead of sqlite3. It comes with lots of goodies like an ORM, better handling of transactions, connection pooling and more.

For example:

from sqlalchemy import create_engine

class YourClass:
    def __init__(self):
        ...
        self.engine = create_engine("sqlite:///ims.db")

    def checkstock(self):
        with self.engine.connect() as con:
            ...  # your code here

I think a sqlalchemy connection should just close itself when you exit the context block. con.execute will actually open a transaction, perform the query, and close the transaction for you, but the documentation will also show you how you can open up a transaction with a context block so that you can execute multiple queries in one transaction.