[Tutor] How to test for the existence of a table in a sqlite3 db?
boB Stepp
robertvstepp at gmail.com
Sat Oct 14 01:43:13 EDT 2017
I want to use Alan's (and others') idea to run a SQL file to create a
table if that table does not exist. Alan suggested using
executescript() to do this. I misunderstood Alan and thought that
this would take a filename and execute it. Instead, it appears that I
must pass to it a string which is a SQL script. So after lots of
fooling around in the interpreter I arrived at:
py3: import sqlite3
py3: conn = sqlite3.connect(':memory:')
py3: c = conn.cursor()
py3: try:
... c.execute('select * from BloodPressureReadings')
... except sqlite3.OperationalError:
... with open('create_sqlite3_db.sql') as f:
... sql = f.read()
... c.executescript(sql)
...
<sqlite3.Cursor object at 0x0000000001E54490>
The file 'create_sqlite3_db.sql' contains:
CREATE TABLE BloodPressureReadings (
ReadingID INTEGER PRIMARY KEY,
Date TEXT,
Time TEXT,
SystolicBP INTEGER,
DiastolicBP INTEGER,
Comments TEXT);
So at this point I am only creating an empty table.
The above "works", but my "try" check is awful! What can I replace it
with to just see if there is *any* table in the chosen database? In
the code Peter supplied in the thread, "How is database creation
normally handled?", he used in his function, "ensure_db(filename)":
cursor.execute("create table if not exists addresses (name, email);")
which is sweet, but I don't see how I can apply this idea if I insist
on using a SQL file to create my table(s).
BTW, in the docs at https://docs.python.org/3/library/sqlite3.html I
found no mention of the actual exception I caught, "OperationalError".
Should not this be in the docs?
--
boB
More information about the Tutor
mailing list