Best practice for connections and cursors

[Joseph L. Casale]

I posted this to the sqlite list but I suspect there are more C oriented users on
that list than Python, hopefully someone here can shed some light on this one.

I have created a python module that I import within several other modules that
simply opens a connection to an sqlite file and defines several methods which
each open a cursor before they either select or insert data.

As the module opens a connection, wherever I import it I call a commit against
the connection after invoking any methods that insert or change data.

Seems I've made a proper mess, one of the modules causes a 5 second delay
at import (big indicator there) and one of the modules calls a method that yields
data while calling other methods as it iterates. Each of these methods opens its
own cursor. One of which during some processing calls another method which
opens a cursor and creates a temp table and this corrupts the top level cursor
and causes its yield to exit early.

If I open a debugger just as the top level method begins to yield, I can pull all
the expected records. It seems to be one of the nested methods that leverages
the singleton connection to the sqlite db, once it opens its own cursor andcreates
a temp table, things go south. Comment out its cursor and code and things work.

A bit vague I know, but does anyone see the obvious mistake? I assumed the module
setting up a singleton connection was a perfectly viable way to accomplish this?

Thanks!
jlc

 

[Neil Cerutti]

A bit vague I know, but does anyone see the obvious mistake? I
assumed the module setting up a singleton connection was a
perfectly viable way to accomplish this?

My one db application started out creating a new connection to
the db (sqlite3) for every select and insert. When I converted it
to maintaining it's own single connection and creating new
cursors instead it was a huge speed increase. So I too am
interested in the answers you'll hopefully be getting. I have no
plans for utilizing concurrent access to the db, but maybe I'll
want to someday.