How to check if a SQLite3 database exists in Python?

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

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

This is a fork from Tom Horen's answer, with a context manager for opening/closing the resource (file), using Python 3.

Also, the 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:

#!/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.")

Building on a couple of other answers above. Here is a clean solution that works in Python 3.7.7:

def isSqlite3Db(db):
    if not os.path.isfile(db): return False
    sz = os.path.getsize(db)

    # file is empty, give benefit of the doubt that its sqlite
    # New sqlite3 files created in recent libraries are empty!
    if sz == 0: return True

    # SQLite database file header is 100 bytes
    if sz < 100: return False
    
    # Validate file header
    with open(db, 'rb') as fd: header = fd.read(100)    

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

Usage:

if isSqlite3Db('<path_to_db>'):
    # ... <path_to_db> is a Sqlite 3 DB

Notes:

• The answers checking file size is > 100 does not work as a new sqlite3 db created in recent python creates an file with length of 0.
• Other examples reading file header returned bytes in Python 3.7.7 and not string so comparison would fail.
• Examples that use sqlite3.connect(dburl, uri=True) did not work for me in Python 3.7.7 as it gave false positives.

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