boB Stepp wrote:
> On Sat, Oct 14, 2017 at 2:11 AM, boB Stepp <robertvst...@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)
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
