我已经编写了以下代码,它显示了sqlite3.OperationalError: database is locked
错误。任何调试帮助将非常感谢。
基本上,我试图将数据从表1复制到表2,并根据其他应用程序对表1所做的更改将数据插入表2。
我好像少了一部分。
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]
编辑 下面是回溯
Traceback (most recent call last):
File "try2.py", line 28, in <module>
conn2.execute(InsertTest)
sqlite3.OperationalError: database is locked
我不确定这是否对任何人都有帮助,但我找到了解决我自己的数据库锁定问题的方法。
我使用PyCharm,发现我正在处理的脚本的几个实例都在运行。这通常是由于我正在测试的代码中的错误造成的,但它仍然处于活动状态(因此与数据库的连接仍然处于活动状态)。关闭这些(停止所有进程)然后再试一次-每次都对我有效!
如果有人知道一种方法,使它在一段时间后超时,请评论这个解决方案。我试过
cur.execute("PRAGMA busy_timeout = 30000")
(从另一个关于类似问题的线程中找到),但它似乎什么也没做。我认为你必须关闭你打开的连接,可能是因为这个错误导致你打开了多个连接。
“数据库已锁定”表示其他连接具有活动连接。
使用PRAGMA busy_timeout等待其他事务完成一段时间:
但是,如果另一个应用程序故意保持一个打开的事务以保持数据库锁定,则您将无能为力。
相关问题 更多 >
编程相关推荐