How to check if a SQLite3 database exists in Python?

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

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.

Answer 2

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