Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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]
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
                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
                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
                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
                Nov 27, 2014 at 0:13
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.
"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
– Nuno André
                Oct 4, 2016 at 11:50
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
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()
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.