Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > MySQLDB multiple cursor question

Reply
Thread Tools

MySQLDB multiple cursor question

 
 
Brian Kelley
Guest
Posts: n/a
 
      01-07-2004
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()



 
Reply With Quote
 
 
 
 
Brian Kelley
Guest
Posts: n/a
 
      01-08-2004
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

 
Reply With Quote
 
 
 
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      01-08-2004
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]

--
> ================================================== ============ <
> http://www.velocityreviews.com/forums/(E-Mail Removed) | Wulfraed Dennis Lee Bieber KD6MOG <
> (E-Mail Removed) | Bestiaria Support Staff <
> ================================================== ============ <
> Bestiaria Home Page: http://www.beastie.dm.net/ <
> Home Page: http://www.dm.net/~wulfraed/ <


 
Reply With Quote
 
Brian Kelley
Guest
Posts: n/a
 
      01-08-2004
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

 
Reply With Quote
 
AdSR
Guest
Posts: n/a
 
      01-08-2004
Brian Kelley <(E-Mail Removed)> 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
 
Reply With Quote
 
Brian Kelley
Guest
Posts: n/a
 
      01-09-2004
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


 
Reply With Quote
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      01-09-2004
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).


--
> ================================================== ============ <
> (E-Mail Removed) | Wulfraed Dennis Lee Bieber KD6MOG <
> (E-Mail Removed) | Bestiaria Support Staff <
> ================================================== ============ <
> Bestiaria Home Page: http://www.beastie.dm.net/ <
> Home Page: http://www.dm.net/~wulfraed/ <


 
Reply With Quote
 
Brian Kelley
Guest
Posts: n/a
 
      01-16-2004
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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to tell if cursor is sqlite.Cursor or psycopg2.Cursor dmaziuk Python 3 01-25-2011 04:52 AM
mysqldb cursor returning type along with result ? Paul O'Sullivan Python 2 11-29-2009 03:57 PM
MySQLdb: commit before cursor close, or after? John Nagle Python 4 02-04-2008 09:17 PM
MySQLdb returns "ValueError: invalid literal for float():" for cursor.execute('show databases') Tim Williams Python 2 05-28-2004 12:48 PM
MySQLdb and Cursor Michel Combe Python 3 10-10-2003 02:26 PM



Advertisments