RE: [cx-oracle-users] Creating a large number of database connections

[Moore, P. <Pau...@at...>]

From: Anthony Tuininga [mailto:an...@co...]
> Could you explain about the "cursors only used from a single
> thread" problem is?

Sorry. Basically, I'm running a query against lots of databases,
and storing the results in a central database. So I have a single
connection to the central DB, against which I'm running a series
of inserts.

Hmm, maybe I should be sharing the central *connection* between
the multiple threads, and creating individual cursors to do the
inserts on a per-thread basis. Is that OK? (Am I reading the
documentation right that I can share a connection, but not a
cursor?)

> I am assuming here that you have one connection per
> thread and therefore one cursor per thread so there shouldn't
> be a problem here, right?

It's passing the data back to a central update thread that hurts.
Mucking about with queues, knowing when the threads have completed,
etc :-(

> This code:
[...]
> will definitely be slower than this code:

> def PerformQuery(db):
>     connection =3D cx_Oracle.connect("user", "pw", db)
>     cursor =3D connection.cursor()
>     cursor.execute("some_query")

> for db in listOfDbs:
>     thread =3D thread.start_new_thread(PerformQuery, (db,))

> Of course, you need to add synchronization code which is completely
> missing but I can help you with that, if needed. I've written a few
> threaded programs and I know the pitfalls that can suddenly open up
> beneath your feet. :-)

Thanks for the offer. I've managed to find a lot of thread pitfalls
myself :-)

How likely is the following sketch to work? (I'll try it, but one of
the things I have learned is that running a threaded program twice
exposes two different sets of bugs :-))

cx_Oracle.OPT_Threading =3D 1 # I think I need this?

def PerformQuery(db, cn):
    connection =3D cx_Oracle.connect("user", "pw", db)
    cursor =3D connection.cursor()
    cursor.execute("some_query")
    ins =3D cn.cursor()
    insert_data =3D process(cursor.fetchall())
    ins.executemany("insert_sql", insert_data)

cn =3D cx_Oracle.connect("user", "pw", "centraldb")
threadlist =3D []
for db in listOfDbs:
    thread =3D thread.start_new_thread(PerformQuery, (db, cn))
    threadlist.append(thread)

for thread in threadlist:
    thread.join()

cn.commit()
cn.close()

Paul