How to check if a SQLite3 database exists in Pytho

Question

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

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 sqlite3 library opens the database declared if exists, or creates a new database if this one doesn't exist.

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

Answer 1

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

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

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.db in the current folder, but will raise an error in case that file can not be opened or does not exist:

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() docs state that:

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.

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

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().

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

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=':/')

Answer 2

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.

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.

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.")

Answer 3

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.

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

Answer 4

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 :

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

Answer 5

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

if os.path.isfile(db):

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

---

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 & Create mode, so connecting to a non-existing database will cause it to be created.

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

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 Parameters documentation for more details on what parameters are accepted.