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.
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"
print "after c.execute"
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
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.
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.