Sqlite python sqlite3.OperationalError：数据库被锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27160987/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sqlite python sqlite3.OperationalError: database is locked
提问by Technopolice
I have written the following code, which is showing the sqlite3.OperationalError: database is lockederror. Any help to debug would be much appreciated.
我写了下面的代码，它显示了sqlite3.OperationalError: database is locked错误。任何调试帮助将不胜感激。
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.
基本上，我试图将数据从 table1 复制到 table2，并根据其他应用程序对 table1 发生的更改将数据插入到 table2。
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]
EditBelow is the traceback
编辑下面是回溯
Traceback (most recent call last):
File "try2.py", line 28, in <module>
conn2.execute(InsertTest)
sqlite3.OperationalError: database is locked
回答by CL.
"Database is locked" means that some other connection has an active connection.
“数据库被锁定”意味着其他某个连接具有活动连接。
Use PRAGMA busy_timeoutto wait some time for the other transaction to finish:
使用PRAGMA busy_timeout等待其他事务完成一段时间：
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.
但是，如果其他应用程序故意保持打开的事务以保持数据库锁定，则您无能为力。
回答by Steven B
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!
我使用 PyCharm 并发现我正在处理的脚本的几个实例都在运行。这通常是由于我正在测试的代码中的错误，但它保持活动状态（因此与数据库的连接仍然处于活动状态）。关闭那些（停止所有进程）并重试 - 它每次都对我有用！
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.
如果有人知道在一段时间后使其超时的方法，请评论此解决方案。我尝试过cur.execute("PRAGMA busy_timeout = 30000")（从另一个线程上的类似问题中找到）但它似乎没有做任何事情。
回答by Shinto Joseph
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()
回答by SAM123
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
确保在每个 SQL 语句之后包含 conn.close()
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
conn.commit()
conn.close() #This is the one you need
