CODEWITHSUNDEEP
pythonsqlpython-3.xsqlite
iamsnaks
iamsnaks

Reputation: 45

'SQLite' Database is Locked Error

Whilst creating an 'INSERT', I have included the "try and except" approach to alert the user to when a duplicate primary key is entered, using a message box. Nevertheless, when a duplicate key is entered, the next time the 'INSERT' is ran, it gives the following error:

"sqlite3.OperationalError: database is locked"

Is there is another approach to solving this issue at all?

I've added the 'INSERT' below to the post with the relevant table creation.

Insert Function

    try:
        conn=sqlite3.connect("Homework_Planner.db")
        conn.execute("INSERT INTO Student (StudentID, Email, Password) \
                                                   VALUES  (?,?,?);",(student_ID, student_Email, hashedPassword,))
        conn.commit()
        #Commits changes made to the database.
        conn.close()
        #Closes the database..


    except sqlite3.IntegrityError:
        messagebox.showinfo('Setup Error!', 'Student ID already exists.')

        x=windows() 
        x.student_Setup()

Table Creation

conn=sqlite3.connect("Homework_Planner.db")
conn.execute (''' CREATE TABLE IF NOT EXISTS Student  (
 StudentID             TEXT       PRIMARY KEY   NOT NULL,
 Email                 TEXT       NOT NULL,
 Password              TEXT       NOT NULL );''')

Upvotes: 2

Views: 9825

Answers (1)

yarwest
yarwest

Reputation: 990

the sqlite3.IntegrityError is thrown before the connection is closed, this causes your database to lock since it believes that it is still in connection with the application. To prevent this I suggest closing the connection in the catch block too to make sure it also gets closed when the error is thrown. Alternatively, you can use a finally block to close the connection but just as the previous method, this is prone to errors.

The new and improved way of handling all this is by using a try-with block (also known as a context manager). By doing this you open the resource inside the try block and it will automatically be closed at the end of it. In your case this could look as follows:

try:
    with sqlite3.connect("Homework_Planner.db") as conn:
        conn.execute("INSERT INTO Student (StudentID, Email, Password) \ VALUES (?,?,?);",(student_ID, student_Email, hashedPassword,))
except sqlite3.IntegrityError:
    messagebox.showinfo('Setup Error!', 'Student ID already exists.')
    x=windows()
    x.student_Setup()

Upvotes: 4

Related Questions