Best practice for connections and cursors

[Ian Kelly]

On Thu, Aug 1, 2013 at 6:49 PM, Dennis Lee Bieber <wlfraed at ix.netcom.com> wrote:
> On Thu, 1 Aug 2013 15:05:08 +0000, "Joseph L. Casale"
> <jcasale at activenetwerx.com> declaimed the following:
>
>>
>>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.
>>
>
>         You probably should NOT be opening connections /during/ the import
> operation... For one thing, if it is a module level connection, multiple
> imports are replacing earlier connections with their fresh one.

That's not true.  Multiple imports of the same module will only
execute the module-level code the first time.

I agree though that simply importing the module should not trigger a
connection to be opened.  That should be an explicit step.

Speaking to the OP:  personally, I don't like the approach of putting
data access methods at the module level to begin with.  I'd rather use
a class.  Just because it makes sense to have a singleton connection
now doesn't mean it will always make sense as your application grows.
In fact, the conflict you describe where one cursor is interfering
with another cursor suggests that you may already be at the point of
needing multiple connections.  The operation that is creating a temp
table and messing things up should ideally be pulling an unused
connection from a pool, so as to avoid potentially contaminating a
connection that may already be in use elsewhere in the code.