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

[Peter Otten]

boB Stepp wrote:

> On Sat, Oct 14, 2017 at 2:11 AM, boB Stepp <robertvstepp at gmail.com> wrote:
> 
>> So I get "None" as a result if the target table has not been created
>> yet.  But if I *do* create the table I want:
>>
>> py3: with open('create_sqlite3_db.sql') as f:
>> ...     sql = f.read()
>> ...
>> py3: c.executescript(sql)
>> <sqlite3.Cursor object at 0x00000000026B4490>
>> py3: tb_exists = "select name from sqlite_master where type='table'
>> and name='BloodPressureReadings'"
>> py3: tb_ck = c.execute(tb_exists).fetchone()
>> py3: print(tb_ck)
>> ('BloodPressureReadings',)
>>
>> So it is looking like I can use this technique to determine if I need
>> to create the BloodPressureReadings table or not.  Am I on track here
>> or is there a better technique?
> 
> It just occurred to me after sending the above, does something like
> "sqlite_master" exist for other database programs than sqlite3?  Can I
> abstract out "sqlite_master" and replace it with a variable so that I
> can handle any kind of database?

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.

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)