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

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

try pattern for database connection with the close method

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

Contents

  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

#86006 — try pattern for database connection with the close method

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

[toc] | [next] | [standalone]

#86008

[Multipart message — attachments visible in raw view] — view raw

On Fri, Feb 20, 2015 at 6:42 PM, Mario Figueiredo <marfig@gmail.com> wrote:
>         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()

Two comments:

You could remove the "else" statement, as it will work exactly the same
with or without it. This will reduce the indentation of the bulk of the
code by 1 level.
You MIGHT be able to remove the finally...close as the with-statement
probably does the same thing. I do not know sqlite3, however, so it may do
something different, such as committing, but that would normally be on some
transition object you get from a call.

Basically, you could probably get the same result with (untested):
try:
    db = lite.connect('data.db')
except lite.DatabaseError:
    raise OSError('database file corrupt or not found.')
try:
    with db:
        db.execute(sql, parms)
except lite.IntegrityError:
    raise ValueError('invalid data')
# You may still need the finally, depending on what the with statement does
in sqlite3 - you'd have to check the documentation.

Chris

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

#86026

On 21/02/2015 02:42, 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, 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.
>

Use your context manager at the outer level.

import sqlite3 as lite

try:
     with lite.connect('data.db') as db:
     try:
         db.execute(sql, parms)
     except lite.IntegrityError:
         raise ValueError('invalid data')
except lite.DatabaseError:
     raise OSError('database file corrupt or not found.')

-- 
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.

Mark Lawrence

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

#86143

On Sat, 21 Feb 2015 12:22:58 +0000, Mark Lawrence
<breamoreboy@yahoo.co.uk> wrote:

>
>Use your context manager at the outer level.
>
>import sqlite3 as lite
>
>try:
>     with lite.connect('data.db') as db:
>     try:
>         db.execute(sql, parms)
>     except lite.IntegrityError:
>         raise ValueError('invalid data')
>except lite.DatabaseError:
>     raise OSError('database file corrupt or not found.')

The sqlite context manager doesn't close a database connection on
exit. It only ensures, commits and rollbacks are performed.

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

#86145

On 22/02/2015 18:41, Mario Figueiredo wrote:
> On Sat, 21 Feb 2015 12:22:58 +0000, Mark Lawrence
> <breamoreboy@yahoo.co.uk> wrote:
>
>>
>> Use your context manager at the outer level.
>>
>> import sqlite3 as lite
>>
>> try:
>>      with lite.connect('data.db') as db:
>>      try:
>>          db.execute(sql, parms)
>>      except lite.IntegrityError:
>>          raise ValueError('invalid data')
>> except lite.DatabaseError:
>>      raise OSError('database file corrupt or not found.')
>
> The sqlite context manager doesn't close a database connection on
> exit. It only ensures, commits and rollbacks are performed.
>

Where in the documentation does it state that?  If it does, it certainly 
breaks my expectations, as I understood the whole point of Python 
context managers is to do the tidying up for you.  Or have you misread 
what it says here 
https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager 
?

 >>> import sqlite3
 >>> with 
sqlite3.connect(r'C:\Users\Mark\Documents\Cash\Data\cash.sqlite') as db:
...     db.execute('select count(*) from accounts')
...
<sqlite3.Cursor object at 0x00000000032C70A0>
 >>> db.close()
 >>>

Looks like you're correct.  Knock me down with a feather, Clevor Trevor.

-- 
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.

Mark Lawrence

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

#86169

On Sun, 22 Feb 2015 19:07:03 +0000, Mark Lawrence
<breamoreboy@yahoo.co.uk> wrote:

>
>Looks like you're correct.  Knock me down with a feather, Clevor Trevor.

It took me by surprise when I first encountered it too. The rationale
apparently is that the context manager is strictly a transactional
feature, allowing for multiple context managers within the same
connection to properly perform commits and rollbacks on multiple
transactions.

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

#86150

On Sun, Feb 22, 2015 at 12:41 PM, Mario Figueiredo <marfig@gmail.com> wrote:
> The sqlite context manager doesn't close a database connection on
> exit. It only ensures, commits and rollbacks are performed.

Sorry, I haven't paid careful attention to this thread, so perhaps
this has already been suggested, however... Can't you write your own
class which delegates to the necessary sqlite3 bits and has a context
manager with the desired behavior? Thinking out loud, you could define
a ConnectionMgr class which accepts a sqlite3 connection as a
parameter:

class ConnectionMgr(object):
  def __init__(self, conn):
    self.conn = conn

  def __enter__(self):
    ...

  def __exit__(self, type, value, exception):
    if self.conn is not None:
      ... close self.conn connection here ...
    self.conn = None

  def __getattr__(self, attr):
    return getattr(self.conn, attr)

then...

  try:
    with MyConnection(lite.connect('data.db')) as db:
      ...
  except lite.DatabaseError:
    ...

Might also have to __enter__ and __exit__ self.conn as appropriate.

Skip

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

#86170

On Sun, 22 Feb 2015 13:15:09 -0600, Skip Montanaro
<skip.montanaro@gmail.com> wrote:

>
>Sorry, I haven't paid careful attention to this thread, so perhaps
>this has already been suggested, however... Can't you write your own
>class which delegates to the necessary sqlite3 bits and has a context
>manager with the desired behavior? Thinking out loud, you could define
>a ConnectionMgr class which accepts a sqlite3 connection as a
>parameter

Indeed I could. Thank you. 

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

#86046

On Sat, Feb 21, 2015 at 5:22 AM, Mark Lawrence <breamoreboy@yahoo.co.uk> wrote:
> On 21/02/2015 02:42, 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, 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.
>>
>
> Use your context manager at the outer level.
>
> import sqlite3 as lite
>
> try:
>     with lite.connect('data.db') as db:
>     try:
>         db.execute(sql, parms)
>     except lite.IntegrityError:
>         raise ValueError('invalid data')
> except lite.DatabaseError:
>     raise OSError('database file corrupt or not found.')

This could result in the OSError being misleadingly raised due to some
DatabaseError raised by the execute rather than the connect.

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

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

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

#86171

On Sat, 21 Feb 2015 16:22:36 +0100, Peter Otten <__peter__@web.de>
wrote:

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

Thanks for the suggestions that followed.

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

#86048

Ian Kelly wrote:

> On Sat, Feb 21, 2015 at 5:22 AM, Mark Lawrence <breamoreboy@yahoo.co.uk>
> wrote:
>> On 21/02/2015 02:42, 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, 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.
>>>
>>
>> Use your context manager at the outer level.
>>
>> import sqlite3 as lite
>>
>> try:
>>     with lite.connect('data.db') as db:
>>     try:
>>         db.execute(sql, parms)
>>     except lite.IntegrityError:
>>         raise ValueError('invalid data')
>> except lite.DatabaseError:
>>     raise OSError('database file corrupt or not found.')
> 
> This could result in the OSError being misleadingly raised due to some
> DatabaseError raised by the execute rather than the connect.

The OP probably wants to catch these DatabaseErrors, too. Also, the chance 
of a misleading traceback has been greatly reduced with the advent of 
chained exceptions.

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

#86050

On Sat, Feb 21, 2015 at 8:27 AM, Peter Otten <__peter__@web.de> wrote:
> Ian Kelly wrote:
>
>> On Sat, Feb 21, 2015 at 5:22 AM, Mark Lawrence <breamoreboy@yahoo.co.uk>
>> wrote:
>>> try:
>>>     with lite.connect('data.db') as db:
>>>     try:
>>>         db.execute(sql, parms)
>>>     except lite.IntegrityError:
>>>         raise ValueError('invalid data')
>>> except lite.DatabaseError:
>>>     raise OSError('database file corrupt or not found.')
>>
>> This could result in the OSError being misleadingly raised due to some
>> DatabaseError raised by the execute rather than the connect.
>
> The OP probably wants to catch these DatabaseErrors, too. Also, the chance
> of a misleading traceback has been greatly reduced with the advent of
> chained exceptions.
>

Yes, but the point is that OSError is probably inappropriate in that case.

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

#86054

Ian Kelly wrote:

> On Sat, Feb 21, 2015 at 8:27 AM, Peter Otten <__peter__@web.de> wrote:
>> Ian Kelly wrote:
>>
>>> On Sat, Feb 21, 2015 at 5:22 AM, Mark Lawrence <breamoreboy@yahoo.co.uk>
>>> wrote:
>>>> try:
>>>>     with lite.connect('data.db') as db:
>>>>     try:
>>>>         db.execute(sql, parms)
>>>>     except lite.IntegrityError:
>>>>         raise ValueError('invalid data')
>>>> except lite.DatabaseError:
>>>>     raise OSError('database file corrupt or not found.')
>>>
>>> This could result in the OSError being misleadingly raised due to some
>>> DatabaseError raised by the execute rather than the connect.
>>
>> The OP probably wants to catch these DatabaseErrors, too. Also, the
>> chance of a misleading traceback has been greatly reduced with the advent
>> of chained exceptions.
>>
> 
> Yes, but the point is that OSError is probably inappropriate in that case.

Perhaps, but the example I gave in my other post:

>>> with open("data.db", "wb") as f:
...     f.write(os.urandom(1024)) # put random bytes into data.db

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

matches the "database file corrupt" part of the error message provided by 
the OP.

[toc] | [prev] | [standalone]

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

csiph-web