Sqlite3: Using try: except: to establish db table & default data set - kosher?

Question

I have written up the following initializing routine for an sqlite3 db table, but something tells me this is a) fragile, and/or b) Just a Bad Idea(tm).

The notion is that if the table is not present or it has not been initiailized, the try blocks will fault and the data will be created. In initial testing this works - although I am not seeing the defaults printed to console when the script runs: I get an empty tuple printed. Examining the database using the sqlite shell I see the data is present.

But that niggling feeling lingers that there is something very wrong with this approach. Thoughts? Opinions? Advice?

import sqlite3 as lite
import sys


def insert_defaults():

conn = lite.connect('db.sqlite')
with conn:
    cur = conn.cursor()
    defaults = (
        ('mykey','value for key one'),
        ('anotherkey','value for key two')
    )
    cur.executemany("INSERT INTO Settings(key,value) VALUES ( ?, ? )", defaults)


def initialize():

conn = lite.connect('db.sqlite')

settings = ()

try:
    conn.row_factory = lite.Row
    cur = conn.cursor()

    cur.execute("SELECT * FROM Settings")

    if cur.rowcount < 1:
        insert_defaults()
        cur.execute("SELECT * FROM Settings")

    settings = cur.fetchall()

except lite.Error, e:

    print "Error: %s" % e.args[0]
    print "Trying to create missing table"

    try:
        cur.execute( "DROP TABLE IF EXISTS Settings" )
        cur.execute("CREATE TABLE IF NOT EXISTS Settings (id INTEGER PRIMARY KEY, key TEXT NOT NULL, value TEXT)")

        insert_defaults()

    except lite.Error, e:

        if conn:
            conn.rollback()

        print "Error: %s" % e.args[0]
        sys.exit(1)

finally:
    if conn:
        conn.close()

return settings




if __name__ == "__main__":

print initialize()

• Erik

Answer 1

Relying on exceptions to detect that the table does not exist is not very reliable because there could be other errors that have nothing to do with what you want to check.

The easiest way to ensure that the table is created is to just execute CREATE TABLE IF NOT EXISTS ... when your program is started; this will be ignored if the table already exists.

To check for some records existing, using a SELECT is fine. However, if you have a UNIQUE or PRIMARY KEY constraint on the key column, you could just execute INSERT OR IGNORE INTO Settings....

You should not use a separate connection in insert_defaults(); this will lead to problems if you don't get your transaction commits correct.