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

Neil Cerutti neilc at norwich.edu
Mon Oct 16 09:10:16 EDT 2017

On 2017-10-15, boB Stepp <robertvstepp at gmail.com> wrote:
> Some things I am still pondering:
> 1)  If I adopt the incremental approach to creating and
> initializing the working db, then it seems that the list,
> "sql_scripts", should not be hard-coded into the program.  It
> seems to me it should be off somewhere by itself with perhaps
> other things that might evolve/change over time in its own file
> where it (and its brethren) are easy to locate and update.

An incremental approach is not recommended if you are using the
sqlite3 module. In sqlite modifying table definitions is limited
to the simple addition of a new row of data. Any other change
requires you to create a new table, copy the old data into it,
and then drop the old table.

> 3)  I am supposed to be delving into writing classes on this
> project. Should the code so far stay as a function or get
> incorporated into a class?  My original intent was to do a
> class for the BloodPressureReadings table, but I am not at the
> point of going there yet.

I don't recommend cobbling together your own ORM, if that's what
you are asking. ;)

> 4)  I wish there was a PEP 8 for SQL!  I have several SQL books
> I have consulted, but I have gotten conflicting suggestions for
> SQL code style.  I have tried to adopt something that seems to
> me to be both consistent and reasonable, but is it good enough?

As long as you can imagine yourself reading it two years from
now, and being able to understand it and make changes, it's
probably an OK style. The two big hurdles for me were acquiescing
to uppercase all the SQL keywords, and learning the quoting
rules. My impression is that SQL is very old, and so lots of
different styles are valid and just fine to use. Your main goal,
at first, should be to stick with standard SQL as much as you
possibly can, to make it easier to switch database engines should
you ever wish to do so.

Neil Cerutti

More information about the Tutor mailing list