97

Question: Why can't I open the database?

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

/tmp/cer/could.db

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

OperationalError: unable to open database file

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 unittest can do it just fine, but the actual program can't.

Any clues as to what on earth is going on?

shiva
  • 5,083
  • 5
  • 23
  • 42
Narcolapser
  • 5,895
  • 15
  • 46
  • 56
  • 1
    Is that the full path? Are you sure you aren't passing the path via environment variables or `~` or some other short form? – chrisaycock Jan 09 '11 at 00:31
  • 1
    Does the /tmp/cer/ directory exist? – thkala Jan 09 '11 at 00:33
  • @chrisaycook: yes. I'm not. @thkala: it does. – Narcolapser Jan 09 '11 at 01:53
  • possible duplicate of [sqlite3.OperationalError: unable to open database file](http://stackoverflow.com/questions/7670289/sqlite3-operationalerror-unable-to-open-database-file) – Peter O. Mar 28 '13 at 02:22
  • Came across the same error, turned out i had a colon ' : ' in the file name and sqlite didn't like it. Took it out, works like a charm. – Stevo Dec 05 '21 at 09:17

19 Answers19

97

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

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 /tmp full? (You're on Unix, so use df /tmp to find out.)
  • Does the /tmp/cer directory 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 are possible with a modern-enough SQLite and when in the right filesystem — though /tmp is virtually always on the right sort of FS so it's probably not that — but it's still not recommended.)
  • Is the development code really trying 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?

If you're not on the same machine, it's quite possible that the production system doesn't have a /tmp/cer directory. 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).

Donal Fellows
  • 133,037
  • 18
  • 149
  • 215
  • 1
    yes. yes. no. not that I know of. It shouldn't be. no. the database is made by the same system, the only difference is that UTFile.py calls it instead of cSystem.py. yes, I'm using the built in version SQLite that comes with python. – Narcolapser Jan 09 '11 at 02:22
  • 8
    found my problem. the strings were not completely identical. There was a slight difference because of my config file. the config file was leaving a line break in my path so i was trying to open: '/tmp/cer/\ncloud.db' there was my problem. but this was a very very good answer. thank you kindly! – Narcolapser Jan 09 '11 at 02:45
  • 1
    I thought it would create the folders in the path if they didn't exist, but it wouldn't. I specified a path that existed already and it worked. – Rock Lee Mar 26 '15 at 19:43
45

This worked for me:

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

Note: Double slashes in the full path

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

Hope this helps someone.

Dmitriyevich
  • 3
  • 3
jhc
  • 451
  • 4
  • 2
  • on ubuntu, the backslashes also helped: `create_engine("sqlite:///data\db_folder\example.db")` – Rick Jun 10 '20 at 09:58
  • There's no slash at all in your code, just backslashes, and actually only one of them, because they need to be escaped. – Thomas Weller Jul 13 '21 at 08:26
14

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

Jacquot
  • 1,750
  • 15
  • 25
  • Same here although I would love to know why this makes sense. Is sqlite not allowing ~ as home reference? – Sergio Lucero Feb 14 '21 at 03:03
  • 1
    @SergioLucero `~` is interpreted by your shell, not by the OS's file handling. The argument to `sqlite3.connect()` never goes through a shell, so `~` has no special meaning. – Soren Bjornstad Dec 19 '22 at 17:37
9

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_A or other places that doesn't have a folder_A it will raise such error. The reason is that SQLite will create the database file if it doesn't exist not the folder.

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

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')
Mazdak
  • 105,000
  • 18
  • 159
  • 188
  • Thanks! This is what I was looking for. Moreover, isn't the `else` redundant here since the `conn` object always be created whenever the clause is triggered? Also, would it be more idiomatic to move the statement in the `else` clause to the `except` part and add a `finally` with `conn.close()`? – Kshitij Saraogi Nov 05 '17 at 13:41
  • Yes thanks for note. The ‘else’ part should actually be ‘finally’. Also there is no need to close the connection right after creating one. – Mazdak Nov 05 '17 at 14:42
  • +1 This was the problem for me too, but it was a bit hard to find since sometimes I used folders that had been created before (especially `/tmp`). btw, an alternative to the `try/except` is to just always create before using (and with parents incase there are multiple levels of nesting): `import pathlib; pathlib.Path(folder).mkdir(parents=True, exist_ok=True)`. – teichert Aug 11 '21 at 03:36
6

in my case, i tried creating the sqlite db in /tmp folder and from all the slashes i missed a single slash

Instead of sqlite:///tmp/mydb.sqlite -> sqlite:////tmp/mydb.sqlite ...

Ricky Levi
  • 7,298
  • 1
  • 57
  • 65
  • 1
    Look at: https://docs.sqlalchemy.org/en/13/core/engines.html#sqlite, relative path is 3 slashes: `'sqlite:///foo.db'` and absolute path is 4 slashes: `'sqlite:////absolute/path/to/foo.db'` ... – Ricky Levi Nov 03 '20 at 08:31
  • I generally have to use: ```sqlite3.connect(r'database/contracts.db')``` where r is for raw string. [stack overflow post on r''](https://stackoverflow.com/questions/33729045/what-does-an-r-represent-before-a-string-in-python) – Jesse Mar 29 '22 at 21:24
2

Ran into this issue while trying to create an index on a perfectly valid database. Turns out it will throw this error (in addition to other reasons described here) if the sqlite temp_store_directory variable/directory is unwritable.

Solution: change temp_store_directory with c.execute(f'PRAGMA temp_store_directory = "{writable_directory}"'). Note that this pragma is being deprecated and I am not yet sure what the replacement will be.

Alopex
  • 323
  • 2
  • 8
1

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

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

I replaced test with test.db and and all went smooth.

Fallen
  • 4,435
  • 2
  • 26
  • 46
1

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!

Shreya Bisen
  • 19
  • 2
1

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.

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

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.

pj.dewitte
  • 472
  • 3
  • 10
1
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

fmw42
  • 46,825
  • 10
  • 62
  • 80
Leiradk
  • 21
  • 4
1

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

tzndr
  • 27
  • 3
  • 2
    I would suggest the opposite: if you find find an application database is unexpectedly owned by root you should establish the correct permissions and fix them. Running userland applications with sudo is a bad haabit to get into. – snakecharmerb May 15 '22 at 15:28
1

Use the fully classified name of database file

Use- /home/ankit/Desktop/DS/Week-7-MachineLearning/Week-7-MachineLearning/soccer/database.sqlite

instead-

ANKIT CHOPADE
  • 511
  • 4
  • 2
1

If this happens randomly after correctly being able to access your database (and no settings have changed), it could be a result of a corrupted database.

I got this error after trying to write to my database from two processes at the same time and it must have corrupted my db.sqlite3 file.

My solution was to revert back to a previous commit before the corruption happened.

crazycoder65
  • 341
  • 3
  • 7
1

Run into the error on Windows, added assert os.path.exists, double checked the path, run the script as administrator, nothing helped.

Turns out if you add your folders to the Windows Defender's Ransomware Protection, you can no longer use other programs to write there unless you add these programs to the Controlled Folder Access' whitelist.

Solution - check if your folder has been added to the Windows Defender's Ransomware Protection and remove it for faster fix.

TH Todorov
  • 1,129
  • 11
  • 26
0

1) Verify your database path, check in your settings.py 

DATABASES = {
    'default': {
        'CONN_MAX_AGE': 0,
        'ENGINE': 'django.db.backends.sqlite3',
        'HOST': 'localhost',
        'NAME': os.path.join(BASE_DIR, 'project.db'),
        'PASSWORD': '',
        'PORT': '',
        'USER':''

some times there wont be NAME': os.path.join(BASE_DIR, 'project.db'),

2)Be sure for the permission and ownership to destination folder

it worked for me,

Hemanta Adhikari
  • 9
  • 2
0

My reason was very foolish. I had dropped the manage.py onto the terminal so it was running using the full path. And I had changed the name of the folder of the project. So now, the program was unable to find the file with the previous data and hence the error.

Make sure you restart the software in such cases.

The Doctor
  • 486
  • 1
  • 6
  • 16
0

For any one who has a problem with airflow linked to this issue.

In my case, I've initialized airflow in /root/airflow and run its scheduler as root. I used the run_as_user parameter to impersonate the web user while running task instances. However airflow was always failing to trigger my DAG with the following errors in logs:

sqlite3.OperationalError: unable to open database file
...
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file

I also found once I triggered a DAG manually, a new airflow resource directory was automatically created under /home/web. I'm not clear about this behavior, but I make it work by removing the entire airflow resources from /root, reinitializing airflow database under /home/web and running the scheduler as web under:

[root@host ~]# rm -rf airflow
[web@host ~]$ airflow initdb
[web@host ~]$ airflow scheduler -D

If you want to try this approach, I may need to backup your data before doing anything.

micmia
  • 1,371
  • 1
  • 14
  • 29
0

In case someone gets here again after being greeted by that error, the valid SQLite URL forms are any one of the following:

sqlite:///:memory: (or, sqlite://)
sqlite:///relative/path/to/file.db
sqlite:////absolute/path/to/file.db
majid bhatti
  • 83
  • 12
0

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

self.connection = Database.connect(**kwargs)
sqlite3.OperationalError: unable to open database file
Doug Porter
  • 7,721
  • 4
  • 40
  • 55
Thayne Trevenen
  • 9
  • 1