'SQLite' Database is Locked Error

Question

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 );''')

Did you try closing the connection after the except block? conn.close() — Eytan Avisror
– Eytan Avisror, Commented Dec 31, 2017 at 12:17
I think that was the primary issue in question @EytanAvisror, after using 'try-with' I removed the .close() function call, but looking back it does look like a logic error on my part as it was closing the db before it made it to "except". — iamsnaks
– iamsnaks, Commented Dec 31, 2017 at 18:27

yarwest · Accepted Answer · 2018-05-14 13:43:09Z

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()

if i'm not mistaken what you explained 'try-with' is the way to go! it handles the query just like 'with-open' -file to close when done...
Yeah I think a context manager is a very elegant construct for purposes like these.
Unfortunately my upvote's aren't able to be viewed globally, but accepted the answer! @yarwest
upvoted! just a curious question though, if you have a contextmanager, may I know why you are still calling commit? Also is there a way to specify a callback that gets triggered when the database is not locked anymore
@PirateApp as it turns out you don't explicitly have to call commit, my bad :) The database is unlocked whenever the transaction is committed, so after the try block.

Collectives™ on Stack Overflow

'SQLite' Database is Locked Error

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related