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

[boB Stepp]

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
stage.

> 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:

blood_pressures/
|----.git/
|----blood_pressures/
|--------|--__init__.py
|--------|--main.py
|----database/
|--------|--__init__.py    # Is this really needed in this folder?
|--------|--blood_pressure.db
|--------|--create_sqlite3_db.sql
|----tests/
|--------|--__init__.py    # Oh, no!  No tests yet!!
.gitignore

The current SQL file, create_sqlite3_db.sql:

================================================================================
-- This file will create version 1 of the database.
--------------------------------------------------------------------------------
CREATE TABLE BloodPressureReadings (
    ReadingID INTEGER PRIMARY KEY,
    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()

    try:
        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

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

    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
anyway?

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
yet.

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!

Cheers!
-- 
boB