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

6

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.

answered Sep 22, 2015 at 4:00

Steven B

612 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

4

"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.

answered Nov 27, 2014 at 8:13

CL.

182k18 gold badges241 silver badges282 bronze badges

3 Comments

Nuno André Over a year ago

execute() is a method for cursors not for connections.

CL. Over a year ago

@NunoAndré Tell that to the sqlite3 module.

Nuno André Over a year ago

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

1

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

answered Jul 24, 2017 at 13:39

SAM123

1414 bronze badges

Comments

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

-1

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

answered Feb 28, 2017 at 14:31

Shinto Joseph

3,17131 silver badges25 bronze badges

Collectives™ on Stack Overflow

Sqlite python sqlite3.OperationalError: database is locked

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related