关于配置 SQLite 数据库的 URI 路径的困惑 [英] Confusion about URI path to configure SQLite database

问题描述

我正在使用 Flask 和 Sqlite3 构建一个 Web 应用程序.有一段时间我在连接数据库时遇到问题，但在我写这篇文章时它不起作用:

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

Python 给了我操作错误:无法打开数据库，因为我在冒号后写了 4 个斜杠.在阅读了 sqlalchemy 文档并进行了多次试验后，我发现这行得通:

#with 3 slashes, version 2app.config['SQLALCHEMY_DATABASE_URI'] ='sqlite:///C:/Users/Giang/PyCharmProjects/FlaskWebBlog/FlaskWebBlog/site.db'

或者这个有 4 个斜杠但没有 C:

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

我很困惑，因为基于连接字符串的文档:SQLite 数据库的文件规范被视为 URL 的数据库"部分.请注意，SQLAlchemy 网址的格式为:

driver://user:pass@host/database

这意味着要使用的实际文件名以第三个斜杠右侧的字符开头.所以连接到相对文件路径看起来像:

# 相对路径e = create_engine('sqlite:///path/to/database.db')

绝对路径，以斜线开头，表示需要四个斜线:

#绝对路径e = create_engine('sqlite:////path/to/database.db')

因此，如果我使用绝对路径，我需要 4 个斜杠，但是当我使用版本 1 时，python 给了我错误.当我在版本 2 中使用 3 个斜杠作为绝对路径时，它起作用了.

所以我真的很困惑.谁能为我解释为什么?我真的很感激.谢谢

解决方案

关于 database 组件被读取为第三个斜杠之后的所有字符，您是正确的.这是解析后的:版本 1 URL

<预><代码>>>>导入 sqlalchemy.engine.url 作为 url>>>url.make_url('sqlite:////C:/Users/Giang/PyCharmProjects/FlaskWebBlog/FlaskWebBlog/site.db')sqlite:////C:/Users/Giang/PyCharmProjects/FlaskWebBlog/FlaskWebBlog/site.db>>>变量(_){'驱动程序名称':'sqlite'，'用户名':无，'password_original':无，'主机':无，'端口':无，'数据库':'/C:/Users/Giang/PyCharmProjects/FlaskWebBlog/FlaskWebBlog/site.db','询问': {}}

在 Windows 中，路径开头的斜杠 标准化 为当前工作目录的根驱动器".使用 pywin32，我们可以调用 GetFullPathName 查看路径的规范化版本:

<预><代码>>>>导入操作系统>>>导入 win32 文件>>>os.getcwd()'C:\\用户\\they4kman'>>>win32file.GetFullPathName('/C:/test')'C:\\C:\\测试'>>>win32file.GetFullPathName('/test')'C:\\测试'>>>win32file.GetFullPathName('C:/test')'C:\\测试'

版本 1 不起作用的原因是，同时指定前导斜杠和驱动器号会被 Windows 规范化为无效路径.(更具体地说，该路径无效，因为 Windows 路径中不允许使用冒号，但作为驱动器说明符的开头除外.)

要显示前导斜杠如何根据环境以不同方式标准化，让我们将当前工作目录更改为另一个驱动器上的工作目录并查看标准化路径:

<预><代码>>>>os.chdir('D:/')>>>os.getcwd()'D:\\'>>>win32file.GetFullPathName('/test')'D:\\测试'>>>win32file.GetFullPathName('C:/test')'C:\\测试'

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

解决方案

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'

这篇关于关于配置 SQLite 数据库的 URI 路径的困惑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！