[Tutor] How to test for the existence of a table in a sqlite3 db?
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
> 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()
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 (
ReadingID INTEGER PRIMARY KEY,
-- 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 (
-- Insert starting version number of database:
INSERT INTO CurrentDBVersion (VersionNumber) VALUES (1);
And my current effort to implement your guidance:
"""This file starts the blood pressure readings program."""
"""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())
# 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:
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