try pattern for database connection with the close method
Peter Otten
__peter__ at web.de
Sat Feb 21 10:22:36 EST 2015
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;")
More information about the Python-list
mailing list