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)

Have you considered that it might be creating the sqlite db file if it doesn't exist? That would go some way towards explaining the lack of exceptions. — JosefAssad
– JosefAssad, Commented Aug 1, 2012 at 19:34
Yeah, thats what I thought. I was looking for a switch or argument for an 'open-only' mode, but I couldnt find that. — vineet
– vineet, Commented Aug 1, 2012 at 19:39

Ross Patterson · Accepted Answer · 2019-09-28 21:49:49Z

10

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)

edited Sep 28, 2019 at 21:49

Ross Patterson

5,7371 gold badge23 silver badges38 bronze badges

answered Aug 1, 2012 at 19:48

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

vineet Over a year ago

ok.. this is really a prototype code.. I need to use a mysql database ultimately.. However if this is the only way it works for SQLite, i suppose it will have to do..

jjmontes Over a year ago

what if your argument is an SQLAlchemy database URL, or a SQLite in-memory DB? doesn't' seem right that we have to parse the argument in order to check if the file exists... I'm looking for some SQLAlchemy option to not create the file if it doesn't exist.

Martijn Pieters Over a year ago

@jjmontes: I've updated the answer to show how to do this using a SQLite3 ?mode=rw parameter.

jjmontes Over a year ago

This is great. For my use case, it'd be great if SQLAlchemy leveraged this and introduced some explicit way to avoid file creation for DB drivers for which this makes sense (in my use case, it could be any database system, so I'll still need to check first whether the driver is sqlite or not). Also, very glad to be answered by a master: kudos to you and thanks!

Collectives™ on Stack Overflow

Checking sqlite connection error in sqlalchemy

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related