Sqlite python sqlite3.OperationalError: database is locked

Question

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

Please show us the traceback. Without knowing which line raises this exception, it's much harder to debug the problem. — abarnert
– abarnert, Commented Nov 27, 2014 at 0:07
@JoranBeasley: It doesn't handle it efficiently, but it does handle it correctly. Besides, if the OP really only has one connection to the same file at a time, that shouldn't matter anyway. — abarnert
– abarnert, Commented Nov 27, 2014 at 0:09
Meanwhile, is this the only program that's using the database? Given the name, I suspect maybe your Skype app is writing to it at the same time. Maybe it's intentionally keeping the database locked to make sure it can't get confused by other programs screwing with its data in mid-run? What happens if you quit Skype? — abarnert
– abarnert, Commented Nov 27, 2014 at 0:11
@abarnert Yes Skype will write to the database, may be it locks it. But can't I avoid? Or create another database for my Logginf — Technopolice
– Technopolice, Commented Nov 27, 2014 at 0:13

Steven B · Accepted Answer · 2015-09-22 04:00:38Z

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.

CL. · Accepted Answer · 2014-11-27 08:13:57Z

"Database is locked" means that some other connection has an active connection.

Use PRAGMA busy_timeout to wait some time for the other transaction to finish:

conn.execute("PRAGMA busy_timeout = 30000") # 30 s

However, if that other application deliberately keeps an open transaction to keep the database locked, there is nothing you can do.

I have said but he responded that: Once you have a Connection, you can create a Cursor object and call its execute() method to perform SQL commands

SAM123 · Accepted Answer · 2017-07-24 13:39:57Z

I had the same issue but it was resolved when I used the following to close the concurrent connections.

conn.close()

So, if your program begins like this:

import sqlite3 conn = sqlite3.connect('pg_example.db', timeout=10) c = conn.cursor()

Make sure that you're including the conn.close() after each SQL statement

t = ('RHAT',) c.execute('SELECT * FROM stocks WHERE symbol=?', t) conn.commit() conn.close() #This is the one you need

Shinto Joseph · Accepted Answer · 2017-02-28 14:31:58Z

cursor2 = conn.cursor() cursor3 = conn.cursor() cursor4 = conn.cursor()

I think you have to close the connection which you have opened,may be the error is because of that cause you have opened multiple connections.

cursor2 = conn.cursor() """EDIT YOUR DATABASE USING CODE AND CLOSE THE CONNECTION""" connection.close() cursor3 = conn.cursor() """EDIT YOUR DATABASE USING CODE AND CLOSE THE CONNECTION""" connection.close() cursor4 = conn.cursor() """EDIT YOUR DATABASE USING CODE AND CLOSE THE CONNECTION""" connection.close()

Collectives™ on Stack Overflow

Sqlite python sqlite3.OperationalError: database is locked

4 Answers 4

Comments

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Linked

Related