Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #57782 > unrolled thread

Try-except for flow control in reading Sqlite

Back to article view | Back to comp.lang.python

Contents

  Try-except for flow control in reading Sqlite Victor Hooi <victorhooi@gmail.com> - 2013-10-27 20:43 -0700
    Re: Try-except for flow control in reading Sqlite Steven D'Aprano <steve@pearwood.info> - 2013-10-28 06:18 +0000
      Re: Try-except for flow control in reading Sqlite Antoon Pardon <antoon.pardon@rece.vub.ac.be> - 2013-10-28 10:48 +0100
    Re: Try-except for flow control in reading Sqlite Chris Angelico <rosuav@gmail.com> - 2013-10-28 17:36 +1100
      Re: Try-except for flow control in reading Sqlite Victor Hooi <victorhooi@gmail.com> - 2013-10-27 23:57 -0700
        Re: Try-except for flow control in reading Sqlite Chris Angelico <rosuav@gmail.com> - 2013-10-28 18:01 +1100
          Re: Try-except for flow control in reading Sqlite Steven D'Aprano <steve@pearwood.info> - 2013-10-28 07:19 +0000
        Re: Try-except for flow control in reading Sqlite Chris Angelico <rosuav@gmail.com> - 2013-10-28 18:02 +1100
    Re: Try-except for flow control in reading Sqlite Burak Arslan <burak.arslan@arskom.com.tr> - 2013-10-28 10:17 +0200
    Re: Try-except for flow control in reading Sqlite Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-10-28 19:43 -0400
      Re: Try-except for flow control in reading Sqlite Victor Hooi <victorhooi@gmail.com> - 2013-10-31 16:08 -0700
        Re: Try-except for flow control in reading Sqlite Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-10-31 22:45 -0400

#57782 — Try-except for flow control in reading Sqlite

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

[toc] | [next] | [standalone]

#57789

On Sun, 27 Oct 2013 20:43:07 -0700, Victor Hooi wrote:

> 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.



-- 
Steven

[toc] | [prev] | [next] | [standalone]

#57805

Op 28-10-13 07:18, Steven D'Aprano schreef:
> On Sun, 27 Oct 2013 20:43:07 -0700, Victor Hooi wrote:
> 
>> 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:

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.

-- 
Antoon Pardon

[toc] | [prev] | [next] | [standalone]

#57790

On Mon, Oct 28, 2013 at 2:43 PM, Victor Hooi <victorhooi@gmail.com> wrote:
> 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

[toc] | [prev] | [next] | [standalone]

#57793

Hi,

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

Cheers,
Victor

On Monday, 28 October 2013 17:36:05 UTC+11, Chris Angelico  wrote:
> On Mon, Oct 28, 2013 at 2:43 PM, Victor Hooi <victorhooi@gmail.com> wrote:
> 
> > 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

[toc] | [prev] | [next] | [standalone]

#57794

On Mon, Oct 28, 2013 at 5:57 PM, Victor Hooi <victorhooi@gmail.com> wrote:
> 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

[toc] | [prev] | [next] | [standalone]

#57796

On Mon, 28 Oct 2013 18:01:49 +1100, Chris Angelico wrote:

> On Mon, Oct 28, 2013 at 5:57 PM, Victor Hooi <victorhooi@gmail.com>
> wrote:
>> 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!


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



-- 
Steven

[toc] | [prev] | [next] | [standalone]

#57795

On Mon, Oct 28, 2013 at 6:01 PM, Chris Angelico <rosuav@gmail.com> wrote:
> 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

[toc] | [prev] | [next] | [standalone]

#57798

On 10/28/13 05:43, Victor Hooi wrote:
> 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

[toc] | [prev] | [next] | [standalone]

#57858

On Sun, 27 Oct 2013 20:43:07 -0700 (PDT), Victor Hooi
<victorhooi@gmail.com> declaimed the following:

>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.

-=-=-=-=-=-
>>> import sqlite3 as db
>>> con = db.connect("anUnknown.db")
>>> cur = con.cursor()
>>> rst = cur.execute("pragma table_info('aTable')")
>>> rst
<sqlite3.Cursor object at 0x0000000004725C00>
>>> for ln in rst:
... 	print ln
... 	
>>> for ln in cur:
... 	print ln
... 	
>>> rst = cur.execute("create table aTable ( junk varchar )")
>>> con.commit()
>>> rst = cur.execute("pragma table_info('aTable')")
>>> for ln in rst:
... 	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.
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#58214

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 

On Tuesday, 29 October 2013 10:43:19 UTC+11, Dennis Lee Bieber  wrote:
> On Sun, 27 Oct 2013 20:43:07 -0700 (PDT), Victor Hooi
> 
> <victorhooi@gmail.com> declaimed the following:
> 
> 
> 
> >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.
> 
> 
> 
> -=-=-=-=-=-
> 
> >>> import sqlite3 as db
> 
> >>> con = db.connect("anUnknown.db")
> 
> >>> cur = con.cursor()
> 
> >>> rst = cur.execute("pragma table_info('aTable')")
> 
> >>> rst
> 
> <sqlite3.Cursor object at 0x0000000004725C00>
> 
> >>> for ln in rst:
> 
> ... 	print ln
> 
> ... 	
> 
> >>> for ln in cur:
> 
> ... 	print ln
> 
> ... 	
> 
> >>> rst = cur.execute("create table aTable ( junk varchar )")
> 
> >>> con.commit()
> 
> >>> rst = cur.execute("pragma table_info('aTable')")
> 
> >>> for ln in rst:
> 
> ... 	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.
> 
> -- 
> 
> 	Wulfraed                 Dennis Lee Bieber         AF6VN
> 
>     wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#58230

On Thu, 31 Oct 2013 16:08:38 -0700 (PDT), Victor Hooi
<victorhooi@gmail.com> declaimed the following:

>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...
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web