are there pros or contras, keeping a connection to a (sqlite) database ?
Stef Mientki
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
John Nagle
Open is OK. Open is good, because the database system
gets to cache some data. Open with an uncommitted transaction
may leave the file locked, preventing access by other processes.
So make sure you commit before you go idle.
John Nagle
CM
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?
I'm not even sure what a "connection" really is; I assumed it was
nothing more than a rule that says to write to the database with the
file named in the parentheses.
Further elaboration from the community would be helpful.
Thanks,
Che
News123
> On Sep 8, 1:09 pm, Stef Mientki <stef.mien...@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?
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.
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 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.
Gerhard Häring
> [...]
> I'm not even sure what a "connection" really is; I assumed it was
> nothing more than a rule that says to write to the database with the
The following list is not exclusive, but these are the first things
that I can think of.
- SQLite connections have state like uncommitted transactions
- SQLite connections have page caches and metadata caches (tables,
structures and indices)
Opening and closing SQLite connections is very cheap, but keeping
connections open is usually a better approach. You have to have a
"global" setting like the path to the database file anyway; so you can
wrap a "global" connection object in a factory function just as well.
In database applications that use the raw DB-APi i usually start with
something like:
def get_con():
# return database connection
...
HTH
-- Gerhard
CM
> On 09/09/2010 12:29 AM, CM wrote:
>
> > On Sep 8, 1:09 pm, Stef Mientki <stef.mien...@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