By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,018 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,018 IT Pros & Developers. It's quick & easy.

MySQLDB multiple cursor question

P: n/a
I am trying to use threads and mysqldb to retrieve data from multiple
asynchronous queries.

My basic strategy is as follows, create two cursors, attach them to the
appropriate databases and then spawn worker functions to execute sql
queries and process the results.

This works occasionally, but fails a lot taking python down with it.
Sometimes it also loses connection to the database. Sometimes I get an
error, "Commands out of sync; You can't run this command now" which
makes me suspicious. Of course, I could be doing things completely
wrong. If I can't have multiple cursors by the way, that's just fine
with me. I just thought that I could ;)

I only have one thread or no threads at all it works just fine. I have
tried using thread safe Queues to bundle results and also lists with the
same results.

Can anyone notice anything in the toy code I have attached that would
cause this effect? Thanks for any input.

import MySQLdb, thread, time

def cursoriterate(cursor, buffer=100):
res = cursor.fetchmany(buffer)
while res:
for record in res:
yield record
res = cursor.fetchmany(buffer)

def worker(cursor, sql, result):
try:
print "executing", sql
cursor.execute(sql)
output = []
for record in cursoriterate(cursor):
output.append(cursor)

result.append(output)
print "done"
except:
# just for testing
result.append(None)
raise

for i in range(100):
sql = "select target, result, evalue from BLAST_RESULT where evalue
< 0.001"
db = MySQLdb.connect(user="mergedgraph", host="localhost")
cursor = db.cursor()
cursor.execute("USE HPYLORI_YEAST")
cursor2 = db.cursor()
cursor2.execute("USE HPYLORI_YEAST")

result = []

thread.start_new_thread(worker, (cursor, sql, result))
thread.start_new_thread(worker, (cursor2, sql, result))

while len(result)< 2:
time.sleep(1)

print "results are full"
res = result.pop()
res2 = result.pop()

if res: print len(res)
if res2: print len(res2)
cursor.close()
cursor2.close()
db.close()

Jul 18 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Brian Kelley wrote:
I am trying to use threads and mysqldb to retrieve data from multiple
asynchronous queries.

My basic strategy is as follows, create two cursors, attach them to the
appropriate databases and then spawn worker functions to execute sql
queries and process the results.


The problem goes away if I have only one cursor per connection and just
use multiple connections. This seems like a bug but I don't know for sure.

Brian

Jul 18 '05 #2

P: n/a
Brian Kelley fed this fish to the penguins on Thursday 08 January 2004
07:58 am:

The problem goes away if I have only one cursor per connection and
just
use multiple connections. This seems like a bug but I don't know for
sure.
f The DB-API specifies a common method for accessing data -- this means
"cursors".

MySQL itself does not implement that type of cursor.

Therefore, MySQLdb has to emulate cursors locally. That emulation may
be tied to one per connection (or, at least, one active per connection
-- maybe doing a conn.commit()?) [This is all hypothesis at this time]

-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Bestiaria Home Page: http://www.beastie.dm.net/ <
Home Page: http://www.dm.net/~wulfraed/ <


Jul 18 '05 #3

P: n/a
Dennis Lee Bieber wrote:
f The DB-API specifies a common method for accessing data -- this means
"cursors".

MySQL itself does not implement that type of cursor.

Therefore, MySQLdb has to emulate cursors locally. That emulation may
be tied to one per connection (or, at least, one active per connection
-- maybe doing a conn.commit()?) [This is all hypothesis at this time]


Guess I'll have to crack open the mysqldb source code and fire up a
debugger. The main problem with using multiple connections is that I
have to cache the user's password in order to repoen the connection
which makes me feel very queasy.

The error is very reproducible but that fact that it works sometimes and
not others means that it is probably a bug in mysqldb.

Brian

Jul 18 '05 #4

P: n/a
Brian Kelley <bk*****@wi.mit.edu> wrote:
Brian Kelley wrote:
I am trying to use threads and mysqldb to retrieve data from multiple
asynchronous queries.

My basic strategy is as follows, create two cursors, attach them to the
appropriate databases and then spawn worker functions to execute sql
queries and process the results.


The problem goes away if I have only one cursor per connection and just
use multiple connections. This seems like a bug but I don't know for sure.

Brian


See PEP 249, read about the "threadsafety" global variable.

HTH,

AdSR
Jul 18 '05 #5

P: n/a
AdSR wrote:

See PEP 249, read about the "threadsafety" global variable.
There you have it. MySQLdb has a threadsafety level of 1 which means
that connections can't be shared but the module can.

I guess I'm doing it the right way now :)

HTH,

AdSR


Jul 18 '05 #6

P: n/a
Brian Kelley fed this fish to the penguins on Thursday 08 January 2004
16:28 pm:
There you have it. MySQLdb has a threadsafety level of 1 which means
that connections can't be shared but the module can.
I'd run into a reference to that attribute in the Nutshell, but the
section on DB-API only mentioned that 0 meant not-thread-safe; no
explanation of what different positive values might mean (and I didn't
have time this morning to try to find it via google).
-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Bestiaria Home Page: http://www.beastie.dm.net/ <
Home Page: http://www.dm.net/~wulfraed/ <


Jul 18 '05 #7

P: n/a
Dennis Lee Bieber wrote:
Brian Kelley fed this fish to the penguins on Thursday 08 January 2004
16:28 pm:

There you have it. MySQLdb has a threadsafety level of 1 which means
that connections can't be shared but the module can.


I'd run into a reference to that attribute in the Nutshell, but the
section on DB-API only mentioned that 0 meant not-thread-safe; no
explanation of what different positive values might mean (and I didn't
have time this morning to try to find it via google).


If you google for PEP 249 you'll find the description.

Brian

Jul 18 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.