Sqlite python sqlite3.OperationalError: database i

问题:

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

回答1:

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.

回答2:

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

回答3:

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

回答4:

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