Confusion about URI path to configure SQLite database

Question

Hi I am building a web application using Flask and Sqlite3. I had issues with connecting the database for a while and it did not work when I wrote this:

#version 1
app.config['SQLALCHEMY_DATABASE_URI'] =
'sqlite:////C:/Users/Giang/PyCharmProjects/FlaskWebBlog/FlaskWebBlog/site.db'

Python gave me operational error: can not open database because I wrote with 4 slashes after the colon. After reading sqlalchemy documentation and doing so many trials, I found out this worked:

#with 3 slashes, version 2
app.config['SQLALCHEMY_DATABASE_URI'] = 

 'sqlite:///C:/Users/Giang/PyCharmProjects/FlaskWebBlog/FlaskWebBlog/site.db'

or this with 4 slashes but no C:

#version 3
app.config['SQLALCHEMY_DATABASE_URI'] = 

'sqlite:////Users/Giang/PyCharmProjects/FlaskWebBlog/FlaskWebBlog/site.db'

I am confused because based on the documentation of connecting strings: The file specification for the SQLite database is taken as the “database” portion of the URL. Note that the format of a SQLAlchemy url is:

driver://user:pass@host/database

This means that the actual filename to be used starts with the characters to the right of the third slash. So connecting to a relative filepath looks like:

# relative path
e = create_engine('sqlite:///path/to/database.db')

An absolute path, which is denoted by starting with a slash, means you need four slashes:

# absolute path
e = create_engine('sqlite:////path/to/database.db')

SO according to this, if I use absolute path, I need 4 slashes, but when I did that with version 1, python gave me errors. And when I used 3 slashes for absolute path in version 2, it worked.

So I am really confused. Can anyone explain for me why ? I would really appreciate it. Thank you

Solution 1

You are correct about the database component being read as all the characters after the third slash. Here's the parsed: version 1 URL

>>> import sqlalchemy.engine.url as url
>>> url.make_url('sqlite:////C:/Users/Giang/PyCharmProjects/FlaskWebBlog/FlaskWebBlog/site.db')
sqlite:////C:/Users/Giang/PyCharmProjects/FlaskWebBlog/FlaskWebBlog/site.db

>>> vars(_)
{'drivername': 'sqlite',
 'username': None,
 'password_original': None,
 'host': None,
 'port': None,
 'database': '/C:/Users/Giang/PyCharmProjects/FlaskWebBlog/FlaskWebBlog/site.db',
 'query': {}}

In Windows, a slash at the beginning of a path gets normalized to "the root drive of the current working directory". Using pywin32, we can call GetFullPathName to view the normalized version of a path:

>>> import os
>>> import win32file

>>> os.getcwd()
'C:\\Users\\they4kman'

>>> win32file.GetFullPathName('/C:/test')
'C:\\C:\\test'

>>> win32file.GetFullPathName('/test')
'C:\\test'

>>> win32file.GetFullPathName('C:/test')
'C:\\test'

The reason version 1 doesn't work is because specifying both a leading slash and a drive letter will get normalized by Windows into an invalid path. (More specifically, the path is invalid because colons are not allowed in Windows paths, except at the beginning as a drive specifier.)

To show how a leading slash is normalized differently depending on the environment, let's change the current working directory to one on another drive and check out the normalized path:

>>> os.chdir('D:/')
>>> os.getcwd()
'D:\\'

>>> win32file.GetFullPathName('/test')
'D:\\test'

>>> win32file.GetFullPathName('C:/test')
'C:\\test'

Solution 2

If you are pointing SQLite3 to a location other than the current working directory, I recommend the following to avoid problems where it cannot find the database:

import os
study_and_database_name = "something_unique"
rdb_string_url = "sqlite:///" + os.path.join(dir, (study_and_database_name + ".db"))
rdb_raw_bytes_url = r'{}'.format(rdb_string_url)

It worked for me when resuming an Optuna study stored in RDB storage:

storage_instance = optuna.storages.RDBStorage(url=rdb_raw_bytes_url)
study_instance = optuna.create_study(
    study_name=study_and_database_name,
    storage=storage_instance,
    load_if_exists=True,
)

Comments

[deR_Ed]

this may help: blogs.msdn.microsoft.com/ie/2006/12/06/file-uris-in-windows

[bubble rain]

Thank you. That was such a detailed answer. I understand now why my first version is wrong, but I am still confused why my version 2 works because if 'C:/' full path is 'C:\\', that would still mean I have three slashes before it, and it is the absolute path. But according to the documentation, the absolute path should be preceded by 4 slashes, so do you happen to know why it is still correct then ? Thank you so much

[theY4Kman]

The first two slashes mark the end of the scheme (e.g. sqlite3:// or https://). After that come the username, password, and host to connect to the database with — however, sqlite3 doesn't support authentication, and lives on the filesystem, so has no host to connect to. This means the user:pass@host part is always blank, but still requires the slash to separate it from the database name — which is just the path to the DB file. The docs actually say "for an absolute file path, the three slashes are followed by the absolute path" docs.sqlalchemy.org/en/13/core/engines.html#sqlite