Sqlite python sqlite3.OperationalError: database is locked
debugging, multithreading, python, sqlite
Solution
I'm not sure if this will help anyone, but I figured out a solution to my own Locked Database problem.
I use PyCharm and found that several instances of the script I was working on were all running. This was usually due to errors in the code I was testing, but it stayed active (and therefore the connection to the db was still active). Close out of those (stop all the processes) and try again - it has worked every time for me!
If anyone knows a way to make it timeout after a little while, please comment this solution. I tried `cur.execute("PRAGMA busy_timeout = 30000")` (found from another thread on a similar question) but it didn't seem to do anything.
Problem
I have written the following code, which is showing the `sqlite3.OperationalError: database is locked` error. Any help to debug would be much appreciated. Basically I am trying to copy data from table1 to table2 and inserting data to table2 based on changes happening to table1 by some other application. Looks like I am missing some part. ``` import sqlite3 conn = sqlite3.connect("/home/sid/.Skype/testmasterut/main.db") cursor = conn.cursor() createLogTableSql = """create table IF NOT EXISTS sid_log as select id as "s_id",author as "s_author",timestamp as "s_timestamp",edited_by as "s_editedby",edited_timestamp as "s_edited_timestamp",body_xml as "s_body_xml" from Messages""" cursor.execute(createLogTableSql) conn.commit() print "Table to save the old messages has been created" selectLog = """ select * from sid_log """ original_table = cursor.execute(selectLog) cursor2 = conn.cursor() cursor3 = conn.cursor() cursor4 = conn.cursor() InsertTest = """ insert or ignore into sid_log (s_id,s_author,s_timestamp,s_editedby,s_edited_timestamp,s_body_xml) select id,author,timestamp,edited_by,edited_timestamp,body_xml from Messages where id not in (select s_id from sid_log where s_id = id) and edited_by is NULL and edited_timestamp is NULL """ EditedTest = """ select * from Messages where id in (select s_id from sid_log where s_id = id) and edited_by is not NULL and edited_timestamp is not NULL""" conn.close() while True: conn2 = sqlite3.connect("/home/sid/.Skype/testmasterut/main.db",timeout=3) conn2.execute(InsertTest) print "Total number of rows changed:", conn.total_changes EditedTest2 = """ select * from Messages where id in (select s_id from sid_log where s_id = id) and edited_by is not NULL and edited_timestamp is not NULL""" edited_list = conn2.execute(EditedTest2) conn2.commit() conn2.close() # for row in edited_list: # queryString = "SELECT * FROM sid_log WHERE s_id IN (%s)" % str(row[0]) # original_message = conn.execute(queryString) # for org_row in original_message: # print "Message edited from", org_row[5], "to", row[5] ``` Edit Below is the traceback ``` Traceback (most recent call last): File "try2.py", line 28, in <module> conn2.execute(InsertTest) sqlite3.OperationalError: database is locked ```