Python SQLAlchemy 和 SQLite：数据库被锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13895176/
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
SQLAlchemy and SQLite: database is locked
提问by tapioco123
I have a python script which uses the latest sqlalchemy. When i use sqlite,only sqlite, other db works well, i get the following error:
我有一个使用最新 sqlalchemy 的 python 脚本。当我使用 sqlite 时，只有 sqlite，其他 db 运行良好，出现以下错误：
sqlalchemy.exc.OperationalError: (OperationalError) database is locked u'SELECT blabla....
Any hint?
任何提示？
Example from my code (simplified), i have several methods like this, to select, update and delete things:
我的代码示例（简化），我有几种这样的方法来选择、更新和删除事物：
class MyDb(object):
def __init__(self):
engine = create_engine("sqlite:///file", poolclass=NullPool, pool_threadlocal=True)
engine.pool_size=1
engine.pool_timeout = 60
self.sess = sessionmaker(bind=engine)
def del_stuff(self):
sess = self.sess()
sess.query(Stuff).delete()
try:
sess.commit()
except:
sess.rollback()
def set_stuff(self, id, bar):
sess = self.sess()
sess.query(Foo).get(id).bar = bar
try:
sess.commit()
except:
sess.rollback()
回答by Y.H Wong
sqlite databases only allow one process to access it at a time. Perhaps you have a separate process using the database?
sqlite 数据库一次只允许一个进程访问它。也许你有一个单独的进程使用数据库？
回答by estin
Check your code for these points:
检查您的代码以了解以下几点：
- Instance of MyDb must be one for all application lifetime. MyDb must be a singleton.
- Try using 'plain' strategy for engine but not
pool_threadlocal=True - Close session on each logical request is done.
- MyDb 的实例对于所有应用程序生命周期都必须是一个。MyDb 必须是单例。
- 尝试对引擎使用“普通”策略，但不要
pool_threadlocal=True - 完成每个逻辑请求的关闭会话。
For example:
例如：
def set_stuff(self, id, bar):
sess = self.sess()
sess.query(Foo).get(id).bar = bar
try:
sess.commit()
except:
sess.rollback()
finally:
sess.close()
回答by andrew cooke
you should use a single session across all objects in a thread. sqlite really doesn't like multiple connections, and sqlalchemy is effectively a connection per session (it looks like you may have a session for each class, which implies multiple sessions in a single thread).
您应该跨线程中的所有对象使用单个会话。sqlite 真的不喜欢多个连接，而 sqlalchemy 实际上是每个会话的一个连接（看起来每个类可能都有一个会话，这意味着单个线程中有多个会话）。
回答by Eric Smith
SQLite locks the database when a write is made to it, such as when an UPDATE, INSERT or DELETE is sent. When using the ORM, these get sent on flush. The database will remain locked until there is a COMMIT or ROLLBACK.
SQLite 会在对数据库进行写入时锁定数据库，例如发送 UPDATE、INSERT 或 DELETE 时。使用 ORM 时，这些会在刷新时发送。数据库将保持锁定状态，直到发生 COMMIT 或 ROLLBACK。
I've mostly seen the "database is locked" error in multi-threading situations. One thread will lock the database and another thread will attempt a write of its own. If the first thread doesn't release the lock within the timeout period (4-5 seconds by default, if I recall) the OperationalError is raised on the second thread.
我经常在多线程情况下看到“数据库被锁定”错误。一个线程将锁定数据库，而另一个线程将尝试写入自己的数据。如果第一个线程在超时期限内（默认情况下为 4-5 秒，如果我记得的话）没有释放锁，则在第二个线程上引发 OperationalError。
It can be tricky to know when a flush, and therefore a write is made to the database when the session has autoflush=True(the default setting) since anyquery will cause a flush. Sometimes turning on the SQL logging can help clarify when things are happening:
知道何时刷新可能很棘手，因此当会话具有autoflush=True（默认设置）时对数据库进行写入，因为任何查询都会导致刷新。有时打开 SQL 日志记录可以帮助澄清事情发生的时间：
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
There is some relevant documentation here: http://docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html#database-locking-behavior-concurrency
这里有一些相关文档：http: //docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html#database-locking-behavior-concurrency
回答by Bino Kochumol Varghese
Check any commits pending in database through any developer tools.
通过任何开发人员工具检查数据库中的任何未决提交。
As everyone told above sqlite databases only allow one process to access it at a time. In my case, I am using DB browser for sqliteand in the same, I didn't commit a query. That's also lock the DB and will not allow the application to write to database.
正如上面每个人所说的，sqlite 数据库一次只允许一个进程访问它。就我而言，我将DB 浏览器用于 sqlite，同样，我没有提交查询。这也会锁定数据库，并且不允许应用程序写入数据库。
回答by Bogdan Korecki
In my case with quite a simple logic and no multithreading the source of the issue appeared to be quite banal...
在我的情况下，逻辑非常简单并且没有多线程，问题的根源似乎很平庸......
'SQLite is not designed for a high level of write concurrency. The database itself, being a file, is locked completely during write operations within transactions, meaning exactly one “connection”(in reality a file handle) has exclusive access to the database during this period - all other “connections” will be blocked during this time.'
'SQLite 不是为高水平的写入并发而设计的。数据库本身是一个文件，在事务中的写操作期间被完全锁定，这意味着在此期间恰好有一个“连接”（实际上是一个文件句柄）对数据库具有独占访问权 - 所有其他“连接”在此期间将被阻止时间。'
... so that 'enlightened' an idea: disconnect DB Browser which I used to check the db during the work. And it worked well. So if that is possibly your case - check if you are not connected to your sqlite via other tool ;)
...这样“启发”了一个想法：断开我在工作期间用来检查数据库的数据库浏览器。它运作良好。因此，如果这可能是您的情况 - 检查您是否没有通过其他工具连接到您的 sqlite ;)
回答by Yashwant Kumar
My answer is only for those who are experimenting with the flask database and they are ready to delete their database for removing the database lock. If you are experimenting, then surely you can add data to tables again by running a python script.
我的回答只适用于那些正在试验 Flask 数据库并且他们准备删除他们的数据库以删除数据库锁的人。如果您正在试验，那么您肯定可以通过运行 python 脚本再次将数据添加到表中。
Here we go....
开始了....
delete the data.sqlite file present in your flask project
delete the migrations folder present in your flask project
Now run following commands to create a new database:
- flask db init
- flask db migrate -m "tables"
- flask db upgrade
Now you can run your python script to add data to the database or you can manually add data to tables using python promt/flask shell.
删除 Flask 项目中存在的 data.sqlite 文件
删除 Flask 项目中存在的迁移文件夹
现在运行以下命令来创建一个新的数据库：
- 烧瓶数据库初始化
- 烧瓶数据库迁移-m“表”
- 烧瓶数据库升级
现在你可以运行你的 python 脚本来向数据库添加数据，或者你可以使用 python promt/flask shell 手动将数据添加到表中。
回答by ccc77
also met the same problem:
也遇到了同样的问题：
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 590, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked
[SQL: DELETE FROM vminds4 WHERE vminds4.id = ?]
[parameters: (2,)]
(Background on this error at: http://sqlalche.me/e/e3q8)
Finally, get here, bacause I just open another terminal open the sqlite3 files and after I closed the window, it works!
最后，到这里，因为我只是打开另一个终端打开 sqlite3 文件，在我关闭窗口后，它可以工作了！
