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

[Stef Mientki]

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

 

[John Nagle]

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

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]

 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?

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]

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.

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.

 

[Dennis Lee Bieber]

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.

And from the specification of DB-API compliance (PEP 249):
-=-=-=-=-
threadsafety

Integer constant stating the level of thread safety the
interface supports. Possible values are:

0 Threads may not share the module.
1 Threads may share the module, but not connections.
2 Threads may share the module and connections.
3 Threads may share the module, connections and
cursors.

Sharing in the above context means that two threads may
use a resource without wrapping it using a mutex semaphore
to implement resource locking. Note that you cannot always
make external resources thread safe by managing access
using a mutex: the resource may rely on global variables
or other external sources that are beyond your control.
-=-=-=-=-