[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 (
    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?


More information about the Tutor mailing list