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

[srinivas devaki]

SQL has "CREATE TABLE IF NOT EXISTS"

so you change your instructions to
```python
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute("""
CREATE TABLE IF NOT EXISTS BloodPressureReadings (
    ReadingID INTEGER PRIMARY KEY,
    Date TEXT,
    Time TEXT,
    SystolicBP INTEGER,
    DiastolicBP INTEGER,
    Comments TEXT);
""")
c.execute("SELECT * FROM BloodPressureReadings")
```


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