Checking sqlite connection error in sqlalchemy

Question

I am using sqlalchemy to connect to a local sqlite database.

This is the code I am using -

db = sqlalchemy.create_engine('sqlite:///Latencydb.db')
metadata = sqlalchemy.MetaData(db)

No exceptions are raised even when the file does not exist. Even db.connect() does not throw any exceptions.

Is there a way to check if the database connection succeeded? (Other than checking for the existence of a particular table in the database?)

I'm using sqlite 3.7.7 and sqlalchemy 0.6.8 on Python 2.7.2 (Ubuntu)

Answer 1

On Python versions < 3.4 (including Python 2.7) you can't prevent SQLite from creating the file for you when you connect to a SQLite database file that doesn't yet exist.

So on older Python versions you'll have to use different means to test for the existence of the file first. A simple os.path.exists should suffice:

import os.path

database = '/path/to/database.db'
if not os.path.exists(database):
    raise ValueError('Invalid database path: %s' % (database,)
db = sqlalchemy.create_engine('sqlite:///' + database)

On newer Python versions, the sqlite3 library supports the SQLite URI syntax, so you can specify a mode=rw parameter to disable the default rwc (read-write-create) mode, provided you set uri=True on the sqlite3.connect() call.

SQLAlchemy doesn't support SQLite URI parameters (yet), but you can make use of the creator parameter to pass in the URI flag and your own connection string:

import sqlite3

uri = 'file:/path/to/database.db?mode=rw'

creator = lambda: sqlite3.connect(uri, uri=True)

db = sqlalchemy.create_engine('sqlite:////', creator=creator)