are there pros or contras, keeping a connection to a (sqlite) database ?

[CM]

On Sep 9, 4:41 am, News123 <news1... at free.fr> wrote:
> On 09/09/2010 12:29 AM, CM wrote:
>
> > On Sep 8, 1:09 pm, Stef Mientki <stef.mien... at gmail.com> wrote:
> >>  hello,
>
> >> I wrap my database in some class, and on creation of the instance, a connection to the database is
> >> created,
> >> and will stay connected until the program exists, something like this:
>
> >>     self.conn = sqlite3.connect ( self.filename )
>
> >> Now I wonder if there are pros or contras to keep the connection to the database continuously  "open" ?
>
> >> thanks,
> >> Stef Mientki
>
> > I do the same thing--good to hear from John that keeping it open is
> > OK.
>
> > But another question that this provokes, at least for me is:  what
> > happens when you call .connect() on the same database multiple times
> > from within different parts of the same app?  Is that bad?  And is it
> > that there now multiple connections to the database, or one connection
> > that has multiple names in different namespaces within the app?
>
> CM,
>
> Do you talk about a multithreaded environment?
> or only about an environment with logically separate blocks (or with
> generators),
> and multiple objects each keeping their own connection.

More the latter.  My calls to make a connection to the
database are from within different classes that serve different
aspects of the GUI.  I have something like an anti-MVC
pattern here, like ravioli code, where each GUI element gets
what it needs from the database.  They are all running in the
main GUI thread.  There is also, though, a wxPython timer
(wxTimer) that will occasionally initiate a read or write to the
database, though it will already have an  open connection.

Why?  How would this matter?

> As far as I know sqlite can be compiled to be thread safe, but is not
> necessarily be default.
> No idea about the library used b python.

I haven't dealt with the issue of thread safety before, haven't
thought about it really.

> I personally just started sqlite in one of my apps with multithrading
> and in order to be safe I went for the conservative approach
> connect, perform transactions, commit and close.
> However this is probably overkill and later in time I might also ty to
> keep connections open in order to increse performance.

So far the "many calls to .connect() approach" has not seemed
problematic for my app.  But I have nothing to compare it to.

Che