Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

cursor.execute blocking or not blocking?

Anonymous
2010-08-13
2012-09-19

  • Anonymous
    2010-08-13

    If I call cursor.execute() with a single sql statement, then it is a blocking
    call. But if my SQL includes more than one statement, cursor.execute() returns
    before all of them are executed. What's going on?

    In the following example, if I run the long-running query query0, then the
    line "print c.execute" waits until the query is finished. But if run query1,
    which is the same except has the additional set @bh_dataform = ... in it,
    then the call to cursor.execute returns quickly, and I see "after c.execute"
    long before the query actually completes.

    import MySQLdb
    
    query0 = '''SELECT SUM(LengthOfStay) FROM FactInpatientStayPart0 WHERE HospitalKey %2 = 1 ;'''
    
    query1 = '''
    set @bh_dataformat = 'txt_variable';
    SELECT SUM(LengthOfStay) FROM FactInpatientStayPart1 WHERE HospitalKey %2 = 1 INTO OUTFILE '/tmp/job1';
    '''
    
    db = MySQLdb.connect(host='localhost', user='root', unix_socket='/tmp/mysql-ib.sock', db='nis_explorer_pilot')
    
    c = db.cursor()
    
    print "before c.execute"
    c.execute(query1)
    print "after c.execute"
    
     
  • Andy Dustman
    Andy Dustman
    2010-08-13

    Best guess: When you execute multiple statements, you get multiple result
    sets. Yes, your set statement returns a result set. It's zero rows, but still
    a result set. Try executing c.nextset() after c.execute() and see what
    happens.

     
  • Andy Dustman
    Andy Dustman
    2010-08-23

    I really dislike this patch. Who knows what you are throwing away. Presumably
    one is getting result sets for a reason. However, if you really want to throw
    them away, and not keep track of them, you can do this before db.commit():

    while c.nextset(): pass
    

    Not-so-coincidentally, cursor.close() does this too.