Try-except for flow control in reading Sqlite

[Victor Hooi]

Hi,

I'd like to double-check something regarding using try-except for controlling flow.

I have a script that needs to lookup things in a SQLite database.

If the SQLite database file doesn't exist, I'd like to create an empty database, and then setup the schema.

Is it acceptable to use try-except in order to achieve this? E.g.:

try:
# Try to open up the SQLite file, and lookup the required entries
except OSError:
# Open an empty SQLite file, and create the schema


My thinking is that it is (easier to ask forgiveness than permission), but I just wanted to check if there is a better way of achieving this?

I'd also be doing the same thing for checking if a file is gzipped or not - we try to open it as a gzip, then as an ordinary text file, and if that also fails, raise a parsing error.


Cheers,
Victor

 

[Steven D'Aprano]

Hi,

I'd like to double-check something regarding using try-except for
controlling flow.

I have a script that needs to lookup things in a SQLite database.

If the SQLite database file doesn't exist, I'd like to create an empty
database, and then setup the schema.

Is it acceptable to use try-except in order to achieve this? E.g.:

try:
# Try to open up the SQLite file, and lookup the required
entries
except OSError:
# Open an empty SQLite file, and create the schema

Yes, that's the right way to do it.

My thinking is that it is (easier to ask forgiveness than permission),
but I just wanted to check if there is a better way of achieving this?

I'd also be doing the same thing for checking if a file is gzipped or
not - we try to open it as a gzip, then as an ordinary text file, and if
that also fails, raise a parsing error.

Correct.

The problem with checking in advance is that there is a race condition
between checking and the using the file:


if database exists: # at this moment, the file is guaranteed to exist
# but a moment later, guarantee is no longer valid
open database


In a multitasking operating system, some other process may have deleted
the database. Or changed its name, removed your access privileges, even
replaced it with a different file. Apart from hard-to-diagnose bugs, this
is also the source of some security vulnerabilities:

https://www.owasp.org/index.php/Race_Conditions

Scroll down and read the section on "Time of check, time of use race
condition".

So using a try...except block is precisely the right solution.

 

[Chris Angelico]

Is it acceptable to use try-except in order to achieve this? E.g.:

try:
# Try to open up the SQLite file, and lookup the required entries
except OSError:
# Open an empty SQLite file, and create the schema


My thinking is that it is (easier to ask forgiveness than permission), but I just wanted to check if there is a better way of achieving this?

That looks fine as a model, but is OSError what you want to be
catching? I'd go with FileNotFoundError if that's what you're looking
for - OSError would also catch quite a bit else, like permissions
errors.

ChrisA

 

[Victor Hooi]

Hi,

We're on Python 2.6 (RHEL based system...) - I don't believe this exposes FileNotFoundError =(.

Cheers,
Victor

 

[Chris Angelico]

Hi,

We're on Python 2.6 (RHEL based system...) - I don't believe this exposes FileNotFoundError =(.

Ah! I forgot about 2.x, sorry for the nose. Yep, catching OSError
would be the thing to do, then!

ChrisA

 

[Chris Angelico]

Ah! I forgot about 2.x, sorry for the nose. Yep, catching OSError
would be the thing to do, then!

A "nose", apparently, is what happens when your typing stinks. I meant
"noise", of course.

ChrisA

 

[Steven D'Aprano]

Ah! I forgot about 2.x, sorry for the nose. Yep, catching OSError would
be the thing to do, then!

I believe that in 2.x the error will be IOError, not OSError, at least
for the built-in open() function.

But in either case, you should still check the errno, and if it isn't the
error number you expect, re-raise.

try:
...
except OSError as e:
if e.errno == 2:
# recover from file not found
...
else:
# any other error
raise


See the "errno" module for symbolic names for the various constants.

http://docs.python.org/2/library/errno.html

 

[Burak Arslan]

Hi,

I'd like to double-check something regarding using try-except for controlling flow.

I have a script that needs to lookup things in a SQLite database.

If the SQLite database file doesn't exist, I'd like to create an empty database, and then setup the schema.

Is it acceptable to use try-except in order to achieve this? E.g.:

try:
# Try to open up the SQLite file, and lookup the required entries
except OSError:
# Open an empty SQLite file, and create the schema

this doesn't protect against a partially-created schema. do you have
something like a "version" table in your database as the last created
table? you can check for its existence in the except block and if it's
not there, you should remove the file and re-create it.

to get a list of tables:

select * from sqlite_master where type='table';

best,
burak

 

[Antoon Pardon]

Op 28-10-13 07:18, Steven D'Aprano schreef:

Yes, that's the right way to do it.



Correct.

The problem with checking in advance is that there is a race condition
between checking and the using the file:

There is also a race condition here. You open the SQLite file and it
fails. Then another process creates the SQLite file and stores things
in it and then when you open the SQLite file as an empty file and create
the schema, the previous work is lost.

 

[Dennis Lee Bieber]

Hi,

I'd like to double-check something regarding using try-except for controlling flow.

I have a script that needs to lookup things in a SQLite database.

If the SQLite database file doesn't exist, I'd like to create an empty database, and then setup the schema.

Is it acceptable to use try-except in order to achieve this? E.g.:

try:
# Try to open up the SQLite file, and lookup the required entries
except OSError:
# Open an empty SQLite file, and create the schema

In my experience, SQLite will /create/ an empty database file if the
specified name does not exit. So just executing the connect() call is all
that is needed. After all, checking for data IN the database will either
return something or fail at that point in which case you can now populate
the schema.

-=-=-=-=-=-.... print ln
........ print ln
........ print ln
....
(0, u'junk', u'varchar', 0, None, 0)

No try/except needed -- just an a conditional testing the length of the
result returned by the pragma instruction on the table you expect to find
in the database.

 

[Victor Hooi]

Hi,

You're right, if the databse doesn't exist, the sqlite3 library will simply create it.

Hmm, in that case, what is the Pythonic way to handle this then?

If the database is new, then it won't have the table I need, and it will return something like:

sqlite3.OperationalError: no such table: my_table

I suppose I can try the query, and catch OperationalError, and if so, create the new schema then?

However, that seems a bit ugly, as I'm guessing OperationalError could be caused by a number of other reasons?

Should I perhaps be using some kind of version table as Burak Aslan suggested?

Cheers,
victor

 

[Dennis Lee Bieber]

Hi,

You're right, if the databse doesn't exist, the sqlite3 library will simply create it.

Hmm, in that case, what is the Pythonic way to handle this then?

If the database is new, then it won't have the table I need, and it will return something like:

sqlite3.OperationalError: no such table: my_table

I suppose I can try the query, and catch OperationalError, and if so, create the new schema then?

However, that seems a bit ugly, as I'm guessing OperationalError could be caused by a number of other reasons?

Should I perhaps be using some kind of version table as Burak Aslan suggested?

Cheers,
victor

I'd use the capabilities of the database engine to query it for any
existing schema. As shown in my prior post...