[Tutor] How to test for the existence of a table in a sqlite3 db?

boB Stepp robertvstepp at gmail.com
Sun Oct 15 01:07:19 EDT 2017

On Sat, Oct 14, 2017 at 4:45 AM, Peter Otten <__peter__ at web.de> wrote:

> If this is a long term project there will be changes in the schema.
> However, I don't think it is necessary to check for individual tables. You
> typically start with a few tables
> create table alpha
> create table beta
> create table gamma
> and later add a few more or change columns
> alter table alpha
> create table delta
> In this example you have three versions of the database
> version 0: empty
> version 1: three tables
> version 2: four tables, one table modified
> If you add a table for your own metadata you ownly need to store that
> version number. The necessary steps when you open the database are then
> - read version from bobs_metadata (if that fails you are at version 0)
> If you are at version 0 "migrate" to version one:
> - execute script that creates alpha, beta, gamma, and bobs_metadata, the
>   latter with one row containing version=1
> If you are at version 1 migrate to version two:
> - execute migration script from 1 to 2 modifying alpha, creating delta, and
>   updating to version=2
> If you are at version 2:
> - do nothing, database is already in the state required by your application.

I am puzzled.  If one is using version control, then what is the
advantage of this incremental approach in creating and populating the
database?  Instead, why not just have a single SQL file that generates
the finished initial state of the db for the current version?  One can
always use the version control system to roll back to an earlier

> Pseudo-code:
> scripts = ["0to1.sql", "1to2.sql"]
> current_version = get_schema_version() # this has to catch the
>                                        # OperationalError
> for scriptname in scripts[current_version:]:
>     with open(scriptname) as f:
>         script = f.read()
>     executescript(script)

My current project structure for this program:

|--------|--__init__.py    # Is this really needed in this folder?
|--------|--__init__.py    # Oh, no!  No tests yet!!

The current SQL file, create_sqlite3_db.sql:

-- This file will create version 1 of the database.
CREATE TABLE BloodPressureReadings (
    Date TEXT,
    Time TEXT,
    SystolicBP INTEGER,
    DiastolicBP INTEGER,
    Comments TEXT);

-- This table is intended to only have one row with one entry:  the current
-- database version.  Whenever the program is started this entry will be checked
-- to determine if the database needs to be updated (or not) to the current
-- version from the earlier SQL database creation files.
CREATE TABLE CurrentDBVersion (
    VersionNumber INTEGER);

-- Insert starting version number of database:
INSERT INTO CurrentDBVersion (VersionNumber) VALUES (1);

And my current effort to implement your guidance:

#!/usr/bin/env python3

"""This file starts the blood pressure readings program."""

import sqlite3

def ensure_db(filename):
    """Open the database, "filename", if it exists; otherwise, create a
    database named "filename"."""

    db = sqlite3.connect(filename)
    cur = db.cursor()

        sql_cmd = "SELECT VersionNumber FROM CurrentDBVersion"

        # First element of returned tuple will be the db version number:
        current_db_version = int(cur.execute(sql_cmd).fetchone()[0])

    except sqlite3.OperationalError:
        # This means that the database and the table, "CurrentDBVersion", has
        # not yet been created, implying "version 0".
        current_db_version = 0

        sql_scripts = ["../database/create_sqlite3_db.sql"]
        for sql_scriptname in sql_scripts[current_db_version:]:
            with open(sql_scriptname) as f:

    return db

if __name__ == "__main__":
    db_filename = "../database/blood_pressure.db"
    ensure_db(db_filename)    # Not doing anything with returned db yet.

I have chickened out and not done a TDD approach yet.  I will probably
pause here, wait for feedback from this list, and try to figure out
how I should test what I have so far.  And how do you test SQL scripts

Some things I am still pondering:

1)  If I adopt the incremental approach to creating and initializing
the working db, then it seems that the list, "sql_scripts", should not
be hard-coded into the program.  It seems to me it should be off
somewhere by itself with perhaps other things that might evolve/change
over time in its own file where it (and its brethren) are easy to
locate and update.

2)  Likewise, "db_filename", is currently hard-coded in the if block
to start the program.  I have not decided yet what the end result will
be, but I might want to allow for the possibility of allowing the user
(me) to create multiple databases.  Also, when I figure out how to
test this database stuff, I imagine I will be using a test db for the
testing, not the actual one.  Again, this argues for not hard-coding
the database name.

3)  I am supposed to be delving into writing classes on this project.
Should the code so far stay as a function or get incorporated into a
class?  My original intent was to do a class for the
BloodPressureReadings table, but I am not at the point of going there

4)  I wish there was a PEP 8 for SQL!  I have several SQL books I have
consulted, but I have gotten conflicting suggestions for SQL code
style.  I have tried to adopt something that seems to me to be both
consistent and reasonable, but is it good enough?

I await the arrival of the list's wisdom!


More information about the Tutor mailing list