MySQLDB multiple cursor question

Discussion in 'Python' started by Brian Kelley, Jan 7, 2004.

  1. Brian Kelley

    Brian Kelley Guest

    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()
    Brian Kelley, Jan 7, 2004
    #1
    1. Advertising

  2. Brian Kelley

    Brian Kelley Guest

    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
    Brian Kelley, Jan 8, 2004
    #2
    1. Advertising

  3. 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]

    --
    > ============================================================== <
    > | Wulfraed Dennis Lee Bieber KD6MOG <
    > | Bestiaria Support Staff <
    > ============================================================== <
    > Bestiaria Home Page: http://www.beastie.dm.net/ <
    > Home Page: http://www.dm.net/~wulfraed/ <
    Dennis Lee Bieber, Jan 8, 2004
    #3
  4. Brian Kelley

    Brian Kelley Guest

    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
    Brian Kelley, Jan 8, 2004
    #4
  5. Brian Kelley

    AdSR Guest

    Brian Kelley <> 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
    AdSR, Jan 8, 2004
    #5
  6. Brian Kelley

    Brian Kelley Guest

    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
    Brian Kelley, Jan 9, 2004
    #6
  7. 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).


    --
    > ============================================================== <
    > | Wulfraed Dennis Lee Bieber KD6MOG <
    > | Bestiaria Support Staff <
    > ============================================================== <
    > Bestiaria Home Page: http://www.beastie.dm.net/ <
    > Home Page: http://www.dm.net/~wulfraed/ <
    Dennis Lee Bieber, Jan 9, 2004
    #7
  8. Brian Kelley

    Brian Kelley Guest

    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
    Brian Kelley, Jan 16, 2004
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Michel Combe

    MySQLdb and Cursor

    Michel Combe, Oct 9, 2003, in forum: Python
    Replies:
    3
    Views:
    386
    Andy Todd
    Oct 10, 2003
  2. Tim Williams
    Replies:
    2
    Views:
    628
    Tim Williams
    May 28, 2004
  3. John Nagle
    Replies:
    4
    Views:
    9,844
    John Nagle
    Feb 4, 2008
  4. Paul O'Sullivan
    Replies:
    2
    Views:
    1,053
    Lie Ryan
    Nov 29, 2009
  5. dmaziuk
    Replies:
    3
    Views:
    543
    Chris Gonnerman
    Jan 25, 2011
Loading...

Share This Page