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

Peter Otten __peter__ at web.de
Sun Oct 15 12:00:26 EDT 2017

boB Stepp wrote:

> On Sun, Oct 15, 2017 at 3:09 AM, Peter Otten <__peter__ at web.de> wrote:
>> boB Stepp wrote:
>>> I have not used a "finally" block before.  I just had the thought that
>>> maybe it would run even if an uncaught exception might occur.  I tried
>>> to test this thought by generating a deliberate NameError in the "try"
>>> block and added a print to the "finally" clause.  I got the intended
>>> NameError with no evidence of the added print printing.  But I thought
>>> I would ask just to be sure:  If an uncaught exception occurs, will
>>> the "finally" clause execute?
>> Yes.
>>>>> try:
>> ...     1/0
>> ... except ValueError:
>> ...     print("not triggered")
>> ... finally:
>> ...     print("ALWAYS TRIGGERED")
>> ...
>> Traceback (most recent call last):
>>   File "<stdin>", line 2, in <module>
>> ZeroDivisionError: division by zero
> That is what I thought after reading about "finally".  But look what
> happens if I modify my actual code to generate a NameError:

> def ensure_db(filename):
>     """Open the database, "filename", if it exists; otherwise, create a
>     database named "filename"."""
>     db = sqlite3.connect(filename)
>     cur = db.cursor()
>     try:
>         sql_cmd = "SELECT VersionNumber FROM CurrentDBVersion"
>         a    # This should generate a NameError

Note that at this point `current_db_version` is not yet defined.

>         # First element of returned tuple will be the db version number:
>         current_db_version = int(cur.execute(sql_cmd).fetchone()[0])
>     except sqlite3.OperationalError:
>         # This means that the database and the table, "CurrentDBVersion",
>         # has not yet been created, implying "version 0".
>         current_db_version = 0
>     finally:
>         sql_scripts = ["../database/create_sqlite3_db.sql"]
>         for sql_scriptname in sql_scripts[current_db_version:]:

The finally suite was entered, but now there's another NameError (an 
UnboundLocalError, to be precise), for current_db_version, inside it. Code 
inside the finally suite is executed like it would be anywhere else, so 
anything after the point where the exception was triggered is not run.

>             with open(sql_scriptname) as f:
>                 cur.executescript(f.read())
>         print("THIS IS THE FINALLY BLOCK!!!")  # And this *should* print
>     return db
> This results in the following Traceback:

>> py main.py
> Traceback (most recent call last):
>   File "main.py", line 16, in ensure_db
>     a
> NameError: name 'a' is not defined
> During handling of the above exception, another exception occurred:
> Traceback (most recent call last):
>   File "main.py", line 36, in <module>
>     ensure_db(db_filename)
>   File "main.py", line 27, in ensure_db
>     for sql_scriptname in sql_scripts[current_db_version:]:
> UnboundLocalError: local variable 'current_db_version' referenced
> before assignment

While I tend to find chained exceptions annoying they *do* provide all the 
necessary information.
> So what is going on here?  Why does "finally" not have its print
> executed?  Does the "...another exception occurred:..." interrupt the
> normal flow of the "try/except/finally" structure and prevent the
> "finally" block from executing?

More information about the Tutor mailing list