cursor.execute blocking or not blocking?

[Anonymous]

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]

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.

[Ilguiz Latypov]

My monkey patch of MySQLdb 1.2.2 and a smaller change to 1.2.3 & headrev may
be helpful in automatic cyclying through result sets.

http://sourceforge.net/support/tracker.php?aid=3046730

http://sourceforge.net/support/tracker.php?aid=3046728

[Andy Dustman]

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.