Python Sqlite3，OperationalError：无法打开数据库文件

Sqlite3, OperationalError: unable to open database file

提问by Narcolapser

Question: Why can't I open the database?

问：为什么打不开数据库？

---

---

Info: I'm working on a project using sqlite3database. I wrote a test program that runs and passes it the database:

信息：我正在使用sqlite3数据库进行项目。我编写了一个测试程序，该程序运行并将其传递给数据库：

/tmp/cer/could.db

/tmp/cer/could.db

The unit test program can make the dbwithout any problem. But, when I actually use the program passing the same location to it, i got below error:

单元测试程序可以使db没有任何问题。但是，当我实际使用将相同位置传递给它的程序时，出现以下错误：

OperationalError: unable to open database file

OperationalError: 无法打开数据库文件

I've tried doing it with:

我试过这样做：

1) an empty database.
2) the database and the unit test left behind.
3) no database at all.

In three cases, I got the above error. The most frustrating part has to be the fact that the unittestcan do it just fine, but the actual program can't.

在三种情况下，我得到了上述错误。最令人沮丧的部分必须是这样的事实，即unittest可以做得很好，但实际程序却不能。

Any clues as to what on earth is going on?

关于地球上发生了什么的任何线索？

采纳答案by Donal Fellows

Primary diagnosis: SQLite is unable to open that file for some reason.

初步诊断：由于某种原因，SQLite 无法打开该文件。

Checking the obvious reasons why, and in approximate order that I recommend checking:

检查明显的原因，并按照我建议检查的大致顺序：

• Is the program running on the same machine as you're testing it?
• Is it running as you (or at least the same user as you're testing it as)?
• Is the disk containing /tmpfull? (You're on Unix, so use df /tmpto find out.)
• Does the /tmp/cerdirectory have “odd” permissions? (SQLite needs to be able to create additional files in it in order to handle things like the commit log.)
• Is the unit test code still using that database? (Concurrent opens arepossible with a modern-enough SQLite and when in the right filesystem — though /tmpis virtually always on the right sort of FS so it's probably not that — but it's still not recommended.)
• Is the development code reallytrying to write to that database, or is something “clever” catching you out and causing it to try to open something else? (I've been caught out by this in my code in the past; don't think it can't happen to you…)
• Are you using the same version of the SQLite library in the unit tests and the production code?

• 该程序是否与您正在测试的同一台机器上运行？
• 它是否以您的身份运行（或至少与您正在测试的用户身份相同）？
• 磁盘是否/tmp已满？（您使用的是 Unix，因此请使用它df /tmp来查找。）
• 该/tmp/cer目录是否具有“奇数”权限？（SQLite 需要能够在其中创建其他文件以处理提交日志之类的事情。）
• 单元测试代码是否仍在使用该数据库？（使用足够现代的 SQLite 并且在正确的文件系统中可以进行并发打开- 尽管/tmp实际上总是在正确的 FS 类型上，所以可能不是那样 - 但仍然不推荐这样做。）
• 开发代码是否真的试图写入该数据库，还是某些“聪明”的东西让您发现并导致它尝试打开其他东西？（我过去在我的代码中被这个发现了；不要认为它不会发生在你身上......）
• 您是否在单元测试和生产代码中使用相同版本的 SQLite 库？

If you're not on the same machine, it's quite possible that the production system doesn't have a /tmp/cerdirectory. Obvious to fix that first. Similarly, if you're on the same machine but running as different users, you're likely to have permissions/ownership problems. Disk space is another serious gotcha, but less likely. I don't think it's the last three, but they're worth checking if the more obvious deployment problems are sorted. If it's none of the above, you've hit an exotic problem and will have to report much more info (it might even be a bug in SQLite, but knowing the developers of it, I believe that to be quite unlikely).

如果你不在同一台机器上，很可能生产系统没有/tmp/cer目录。显然要先解决这个问题。同样，如果您在同一台机器上但以不同的用户身份运行，您可能会遇到权限/所有权问题。磁盘空间是另一个严重的问题，但可能性较小。我不认为是最后三个，但它们值得检查是否对更明显的部署问题进行了排序。如果以上都不是，你就遇到了一个奇怪的问题，将不得不报告更多的信息（它甚至可能是 SQLite 中的一个错误，但了解它的开发人员，我相信这是不太可能的）。

回答by Thayne Trevenen

Make sure you are not editing the settings.py file while trying to run syncdb, you will get the same error!!!

确保您在尝试运行 syncdb 时没有编辑 settings.py 文件，您会得到同样的错误！！！

self.connection = Database.connect(**kwargs)
sqlite3.OperationalError: unable to open database file

回答by jhc

This worked for me:

这对我有用：

conn = sqlite3.connect("C:\users\guest\desktop\example.db")

Note: Double slashes in the full path

注意：完整路径中的双斜线

Using python v2.7on Win 7 enterprise and Win Xp Pro

在 Win 7 Enterprise 和 Win Xp Pro 上使用python v2.7

Hope this helps someone.

希望这可以帮助某人。

回答by Fallen

I faced the same problem on Windows 7. My database name was testand I got the error:

我在 Windows 7 上遇到了同样的问题。我的数据库名称是test，我收到了错误：

self.connection = Database.connect(**kwargs)
sqlite3.OperationalError: unable to open database file

I replaced testwith test.dband and all went smooth.

我test用test.dband替换了，一切都很顺利。

回答by Kasramvd

One reason might be running the code in a path that doesn't match with your specified path for the database. For example if in your code you have:

一个原因可能是在与您为数据库指定的路径不匹配的路径中运行代码。例如，如果在您的代码中您有：

conn = lite.connect('folder_A/my_database.db')

And you run the code inside the folder_Aor other places that doesn't have a folder_Ait will raise such error. The reason is that SQLite will create the database file if it doesn't exist not the folder.

并且您在folder_A没有folder_A它的地方或其他地方运行代码会引发此类错误。原因是 SQLite 将创建数据库文件，如果它不存在而不是文件夹。

One other way for getting around this problem might be wrapping your connecting command in a try-exceptexpression and creating the directory if it raises sqlite3.OperationalError.

解决此问题的另一种方法可能是将您的连接命令包装在一个try-except表达式中，如果它引发sqlite3.OperationalError.

from os import mkdir import sqlite3 as lite

from os import mkdir import sqlite3 as lite

try:
    conn = lite.connect('folder_A/my_database.db')
except lite.OperationalError:
    mkdir('folder_A')
finally:
    conn = lite.connect('folder_A/my_database.db')

回答by Jacquot

On unix I got that error when using the ~shortcut for the user directory. Changing it to /home/userresolved the error.

在 unix 上，我在使用~用户目录的快捷方式时遇到了该错误。更改它以/home/user解决错误。

回答by Shreya Bisen

The only thing you need to do is create the folder (as it doesn't exist already), only the database file will be created by the program. This really worked for me!

您唯一需要做的就是创建文件夹（因为它不存在），程序只会创建数据库文件。这真的对我有用！

回答by pj.dewitte

In my case, the solution was to use an absolute path, to find an existing file:

就我而言，解决方案是使用绝对路径来查找现有文件：

import os.path
filepath = os.path.abspath(filepath)
# Leave this out if the file doesn't exist yet
assert os.path.exists(filepath), "The file doesn't exist"
conn = sqlite3.connect(filepath)

I don't know why this fix works: the path only contained ASCII characters and no spaces. Still it made the difference.

我不知道为什么这个修复有效：路径只包含 ASCII 字符，没有空格。尽管如此，它还是有所作为。

For reference: Windows 7, Python 3.6.5 (64-bit).

供参考：Windows 7、Python 3.6.5（64 位）。

I was not able to reproduce the issue on another machine (also Windows 7, Python 3.6.4 64-bit), so I have no idea why this fix works.

我无法在另一台机器（也是 Windows 7，Python 3.6.4 64 位）上重现这个问题，所以我不知道为什么这个修复程序有效。

回答by Leiradk

import sqlite3

connection = sqlite3.connect("d:\pythonAPI\data.db")
cursor = connection.cursor()
create_table = "CREATE TABLE users (id int, username text, password text)"
cursor.execute(create_table)

for clearer full path if you didn't get it clear

如果您没有弄清楚，以获得更清晰的完整路径

回答by tzndr

This is definitely a permissions issue. If someone is getting this error on linux, make sure you're running the command with sudoas the file most likely is owned by root. Hope that helps!

这绝对是权限问题。如果有人在 linux 上收到此错误，请确保您正在运行该命令，sudo因为该文件很可能由 root 拥有。希望有帮助！