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

Groups > comp.lang.python > #86047

Re: try pattern for database connection with the close method

Show all headers | View raw

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;")

Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

try pattern for database connection with the close method Mario Figueiredo <marfig@gmail.com> - 2015-02-21 03:42 +0100
  Re: try pattern for database connection with the close method Chris Kaynor <ckaynor@zindagigames.com> - 2015-02-20 18:59 -0800
  Re: try pattern for database connection with the close method Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-21 12:22 +0000
    Re: try pattern for database connection with the close method Mario Figueiredo <marfig@gmail.com> - 2015-02-22 19:41 +0100
      Re: try pattern for database connection with the close method Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-22 19:07 +0000
        Re: try pattern for database connection with the close method Mario Figueiredo <marfig@gmail.com> - 2015-02-23 00:25 +0100
      Re: try pattern for database connection with the close method Skip Montanaro <skip.montanaro@gmail.com> - 2015-02-22 13:15 -0600
        Re: try pattern for database connection with the close method Mario Figueiredo <marfig@gmail.com> - 2015-02-23 00:30 +0100
  Re: try pattern for database connection with the close method Ian Kelly <ian.g.kelly@gmail.com> - 2015-02-21 08:16 -0700
  Re: try pattern for database connection with the close method Peter Otten <__peter__@web.de> - 2015-02-21 16:22 +0100
    Re: try pattern for database connection with the close method Mario Figueiredo <marfig@gmail.com> - 2015-02-23 00:35 +0100
  Re: try pattern for database connection with the close method Peter Otten <__peter__@web.de> - 2015-02-21 16:27 +0100
  Re: try pattern for database connection with the close method Ian Kelly <ian.g.kelly@gmail.com> - 2015-02-21 08:50 -0700
  Re: try pattern for database connection with the close method Peter Otten <__peter__@web.de> - 2015-02-21 18:02 +0100

csiph-web