Path: csiph.com!usenet.pasdenom.info!gegeweb.org!usenet-fr.net!nerim.net!novso.com!newsfeed.xs4all.nl!newsfeed2.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Peter Otten <__peter__@web.de>
Subject: Re: try pattern for database connection with the close method
Date: Sat, 21 Feb 2015 16:22:36 +0100
Organization: None
References: <6trfeate2ppvm1mcapgr0g4g2fd3vceab6@4ax.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 7Bit
User-Agent: KNode/4.13.3
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.18970.1424532167.18130.python-list@python.org>
Lines: 88
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:86047

Mario Figueiredo wrote:

> Hello all,
> 
> I'm using the following pattern for db access  that requires me to
> close the connection as soon as it is not needed:
> 
>         import sqlite3 as lite
> 
>         try:
>             db = lite.connect('data.db')
>         except lite.DatabaseError:
>             raise OSError('database file corrupt or not found.')
>         else:
>             try:
>                 with db:
>                     db.execute(sql, parms)
>             except lite.IntegrityError:
>                 raise ValueError('invalid data')
>             finally:
>                 db.close()
> 
> Since it's a bit verbose, 

Why would you care about a few lines? You don't repeat them, do you? Put the 
code into a function or a context manager and invoke it with

>>> my_execute(sql, parms)

or

>>> with my_db() as db:
...     db.execute(sql, parms)


> is there a better way?
> 
> Note: The user of this API has the whole database functionality
> abstracted away. Hence the exception channeling in the except clauses.

db.execute() may trigger other sqlite-related errors including 
DatabaseError:

>>> import sqlite3
>>> db = sqlite3.connect("/dev/full")
>>> db.execute("create table foo (bar, baz);")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: unable to open database file


>>> import os
>>> with open("data.db", "wb") as f:
...     f.write(os.urandom(1024)) # put random bytes into data.db
...                               # chances of creating a valid db
...                               # left as an exercise ;)
... 
1024
>>> db = sqlite3.connect("data.db")
>>> db.execute("create table foo (bar, baz);")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.DatabaseError: file is encrypted or is not a database

If you want to catch these, too:

@contextlib.contextmanager
def my_db():
    db = None
    try:
        db = sqlite3.connect("data.db")
        with db:
            yield db  # db.execute() if you don't buy 
                      # into the contextmanager idea
    except sqlite3.IntegrityError:
        raise ValueError
    except sqlite3.DatabaseError:
        raise OSError
    except sqlite3.Error:
        raise WhateverYouNeed
    finally:
        if db is not None:
            db.close()

with my_db() as db:
    db.execute("select * from sqlite_master;")