如何检查 Python 中是否存在 SQLite3 数据库？

How to check if a SQLite3 database exists in Python?

提问by maxim

I am trying to create a function in Python 2.7.3 to open a SQLite database.

我正在尝试在 Python 2.7.3 中创建一个函数来打开 SQLite 数据库。

This is my code at the moment:

这是我目前的代码：

import sqlite3 as lite
import sys

db = r'someDb.sqlite'

def opendb(db):
    try:
        conn = lite.connect(db)
    except sqlite3.Error:
        print "Error open db.\n"
        return False
    cur = conn.cursor()
    return [conn, cur]

I have tried the code above and I have observed that the sqlite3library opens the database declared if exists, or creates a new database if this one doesn't exist.

我已经尝试了上面的代码，我观察到该sqlite3库打开声明的数据库如果存在，或者如果这个数据库不存在则创建一个新数据库。

Is there a way to check if the database exists with sqlite3methods or I have to use file operation like os.path.isfile(path)?

有没有办法用sqlite3方法检查数据库是否存在，或者我必须使用文件操作os.path.isfile(path)？

采纳答案by Martijn Pieters

In Python 2, you'll have to explicitly test for the existence using os.path.isfile:

在 Python 2 中，您必须使用以下命令显式测试是否存在os.path.isfile：

if os.path.isfile(db):

There is no way to force the sqlite3.connectfunction to not create the file for you.

无法强制sqlite3.connect函数不为您创建文件。

---

---

For those that are using Python 3.4 or newer, you can use the newer URI path feature to set a different mode when opening a database. The sqlite3.connect()function by default will open databases in rwc, that is Read, Write & Createmode, so connecting to a non-existing database will cause it to be created.

对于使用 Python 3.4 或更新版本的用户，您可以使用较新的 URI 路径功能在打开数据库时设置不同的模式。sqlite3.connect()默认情况下，该函数将打开数据库rwc，即读取、写入和创建模式，因此连接到一个不存在的数据库将导致它被创建。

Using a URI, you can specify a different mode instead; if you set it to rw, so Read & Writemode, an exception is raised when trying to connect to a non-existing database. You can set different modes when you set the uri=Trueflag when connecting and pass in a file:URI, and add a mode=rwquery parameter to the path:

使用 URI，您可以指定不同的模式；如果将其设置为rw，那么在读取和写入模式下，尝试连接到不存在的数据库时会引发异常。uri=True连接时设置flag，传入一个file:URI，可以设置不同的模式，并mode=rw在路径中添加一个查询参数：

from urllib.request import pathname2url

try:
    dburi = 'file:{}?mode=rw'.format(pathname2url(db))
    conn = lite.connect(dburi, uri=True)
except sqlite3.OperationalError:
    # handle missing database case

See the SQLite URI Recognized Query Parametersdocumentationfor more details on what parameters are accepted.

有关接受哪些参数的更多详细信息，请参阅SQLite URI Recognized Query Parameters文档。

回答by Tom Horen

os.path.isfile()is just telling you if a file exists, not if it exists AND is a SQLite3 database! Knowing http://www.sqlite.org/fileformat.html, you could do this :

os.path.isfile()只是告诉你一个文件是否存在，而不是它是否存在并且是一个 SQLite3 数据库！知道http://www.sqlite.org/fileformat.html，你可以这样做：

def isSQLite3(filename):
    from os.path import isfile, getsize

    if not isfile(filename):
        return False
    if getsize(filename) < 100: # SQLite database file header is 100 bytes
        return False

    with open(filename, 'rb') as fd:
        header = fd.read(100)

    return header[:16] == 'SQLite format 3\x00'

and subsequently use it like :

然后像这样使用它：

for file in files:
    if isSQLite3(file):
        print "'%s' is a SQLite3 database file" % file
    else:
        print "'%s' is not a SQLite3 database file" % file

回答by user24390

I am using a function like the following at the beginning of my script so that I can try and figure out why a sqlite3 db script might not be working. Like the comments say, it uses 3 phases, checks if a path exist, checks if the path is a file, checks if that file's header is a sqlite3 header.

我在脚本的开头使用了如下所示的函数，以便我可以尝试找出 sqlite3 db 脚本可能无法正常工作的原因。就像评论说的那样，它使用 3 个阶段，检查路径是否存在，检查路径是否是文件，检查该文件的头是否是 sqlite3 头。

def checkdbFileforErrors():

    #check if path exists
    try:
        with open('/path/to/your.db'): pass
    except IOError:
        return 1

    #check if path if a file
    if not isfile('/path/to/your.db'):
        return 2

    #check if first 100 bytes of path identifies itself as sqlite3 in header
    f = open('/path/to/your.db', "rx")
    ima = f.read(16).encode('hex')
    f.close()
    #see http://www.sqlite.org/fileformat.html#database_header magic header string
    if ima != "53514c69746520666f726d6174203300": 
        return 3

    return 0

回答by ivanleoncz

This is a fork (using Python 3) based on Tom Horen's answer, which presents a solution more complete and reliable that the elected answer.

这是一个基于Tom Horen's answer的分支（使用 Python 3），它提供了一个比所选答案更完整和可靠的解决方案。

The elected answer, does not evaluate any content, header, etc., in order to determine whether the file actually contains any data related to a SQLite3 database or not.

所选答案不评估任何内容、标题等，以确定文件是否实际包含与 SQLite3 数据库相关的任何数据。

I tried to present something more pragmatic here:

我试图在这里提出一些更务实的东西：

#!/usr/bin/python3

import os
import sys

if os.path.isfile('test.sqlite3'):
    if os.path.getsize('test.sqlite3') > 100:
        with open('test.sqlite3','r', encoding = "ISO-8859-1") as f:
            header = f.read(100)
            if header.startswith('SQLite format 3'):
                print("SQLite3 database has been detected.")

回答by Jurko Gospodneti?

Yes, there is a way to do what you want with Python 3.4+.

是的，有一种方法可以用 Python 3.4+ 做你想做的事。

Use the sqlite3.connect()function to connect, but pass it a URI instead of a file path, and add mode=rwto its query string.

使用该sqlite3.connect()函数进行连接，但向其传递 URI 而不是文件路径，并添加mode=rw到其查询字符串中。

Here is a complete working code example:

这是一个完整的工作代码示例：

import sqlite3
con = sqlite3.connect('file:aaa.db?mode=rw', uri=True)

This will open an existing database from a file named aaa.dbin the current folder, but will raise an error in case that file can not be opened or does not exist:

这将从aaa.db当前文件夹中命名的文件中打开现有数据库，但如果该文件无法打开或不存在，则会引发错误：

Traceback (most recent call last):
  File "aaa.py", line 2, in <module>
    con = sqlite3.connect('file:aaa.db?mode=rw', uri=True)
sqlite3.OperationalError: unable to open database file

Python sqlite.connect() docsstate that:

Python sqlite.connect() 文档指出：

If uri is true, database is interpreted as a URI. This allows you to specify options. For example, to open a database in read-only mode you can use:

db = sqlite3.connect('file:path/to/database?mode=ro', uri=True)

More information about this feature, including a list of recognized options, can be found in the SQLite URI documentation.

如果 uri 为真，则将数据库解释为 URI。这允许您指定选项。例如，要以只读模式打开数据库，您可以使用：

db = sqlite3.connect('file:path/to/database?mode=ro', uri=True)

有关此功能的更多信息，包括已识别选项的列表，可以在SQLite URI 文档 中找到。

Here's an excerpt of all the relevant URI option information collected from http://www.sqlite.org/c3ref/open.html:

以下是从http://www.sqlite.org/c3ref/open.html收集的所有相关 URI 选项信息的摘录：

mode: The mode parameter may be set to either "ro", "rw", "rwc", or "memory". Attempting to set it to any other value is an error. If "ro" is specified, then the database is opened for read-only access, just as if the SQLITE_OPEN_READONLY flag had been set in the third argument to sqlite3_open_v2(). If the mode option is set to "rw", then the database is opened for read-write (but not create) access, as if SQLITE_OPEN_READWRITE (but not SQLITE_OPEN_CREATE) had been set. Value "rwc" is equivalent to setting both SQLITE_OPEN_READWRITE and SQLITE_OPEN_CREATE. If the mode option is set to "memory" then a pure in-memory database that never reads or writes from disk is used. It is an error to specify a value for the mode parameter that is less restrictive than that specified by the flags passed in the third parameter to sqlite3_open_v2().

The sqlite3_open_v2()interface works like sqlite3_open() except that it accepts two additional parameters for additional control over the new database connection. The flags parameter to sqlite3_open_v2() can take one of the following three values, optionally combined with the SQLITE_OPEN_NOMUTEX, SQLITE_OPEN_FULLMUTEX, SQLITE_OPEN_SHAREDCACHE, SQLITE_OPEN_PRIVATECACHE, and/or SQLITE_OPEN_URI flags:

SQLITE_OPEN_READONLY The database is opened in read-only mode. If the database does not already exist, an error is returned.

SQLITE_OPEN_READWRITE The database is opened for reading and writing if possible, or reading only if the file is write protected by the operating system. In either case the database must already exist, otherwise an error is returned.

SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE The database is opened for reading and writing, and is created if it does not already exist. This is the behavior that is always used for sqlite3_open() and sqlite3_open16().

模式：模式参数可以设置为“ro”、“rw”、“rwc”或“memory”。尝试将其设置为任何其他值是错误的。如果指定了“ro”，那么数据库将打开以进行只读访问，就像在 sqlite3_open_v2() 的第三个参数中设置了 SQLITE_OPEN_READONLY 标志一样。如果 mode 选项设置为“rw”，则打开数据库以进行读写（但不是创建）访问，就像设置了 SQLITE_OPEN_READWRITE（但不是 SQLITE_OPEN_CREATE）一样。值“rwc”等效于设置 SQLITE_OPEN_READWRITE 和 SQLITE_OPEN_CREATE。如果模式选项设置为“内存”，则使用从不从磁盘读取或写入的纯内存数据库。

该sqlite3_open_v2（）接口的作用就象sqlite3_open（），但接受用于在新的数据库连接额外的控制两个附加参数。sqlite3_open_v2() 的 flags 参数可以采用以下三个值之一，可选择与 SQLITE_OPEN_NOMUTEX、SQLITE_OPEN_FULLMUTEX、SQLITE_OPEN_SHAREDCACHE、SQLITE_OPEN_PRIVATECACHE 和/或 SQLITE_OPEN_URI 标志组合：

SQLITE_OPEN_READONLY 数据库以只读模式打开。如果数据库尚不存在，则返回错误。

SQLITE_OPEN_READWRITE 如果可能，打开数据库进行读写，或者仅当文件受操作系统写保护时才读取。无论哪种情况，数据库都必须已经存在，否则将返回错误。

SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE 数据库以读写方式打开，如果不存在则创建。这是始终用于 sqlite3_open() 和 sqlite3_open16() 的行为。

For convenience, here's also a Python 3.4+ function for converting a regular path to an URI usable by sqlite.connect():

为方便起见，这里还有一个 Python 3.4+ 函数，用于将常规路径转换为 ​​sqlite.connect() 可用的 URI：

import pathlib
import urllib.parse

def _path_to_uri(path):
    path = pathlib.Path(path)
    if path.is_absolute():
        return path.as_uri()
    return 'file:' + urllib.parse.quote(path.as_posix(), safe=':/')