Python MySQL Connector executing second sql statement within cursor loop?

Question

The following logic works with the mysqldb module (see python mysqldb multiple cursors for one connection), but I am getting the following error with mysql.connector on cursor2.execute(sql)

"Unread result found."

I realize that I can use a join to combine these 2 simple sql statements and avoid the need for a second cursor, but my real world example is more complex and requires a second sql statement.

Assuming I need to execute 2 separate sql statements (1 for the loop and 1 inside the loop), how should this be done with the mysql.connector module?

import datetime
import mysql.connector

db = mysql.connector.connect(user='alan', password='please', host='machine1', database='mydb')

cursor1 = db.cursor()
cursor2 = db.cursor()

sql = """
SELECT userid, 
       username,
       date
  FROM user
 WHERE date BETWEEN %s AND %s
"""

start_date = datetime.date(1999, 1, 1)
end_date   = datetime.date(2014, 12, 31)

cursor1.execute(sql, (start_date, end_date))

for (userid, username, date) in cursor1:

    sql = """
        select count(*)
        from request
        where assigned = '%s'
    """ % (userid)

    cursor2.execute(sql)
    requestcount = cursor2.fetchone()[0]

    print userid, requestcount

cursor2.close()
cursor1.close()
db.close()

This mysqldb version works just fine:

import datetime
import MySQLdb 

db = MySQLdb.connect(user='alan', passwd='please', host='machine1', db='mydb')

cursor1 = db.cursor()
cursor2 = db.cursor()

sql = """
SELECT userid, 
       username,
       date
  FROM user
 WHERE date BETWEEN %s AND %s
"""

start_date = datetime.date(1999, 1, 1)
end_date   = datetime.date(2014, 12, 31)

cursor1.execute(sql, (start_date, end_date))

for (userid, username, date) in cursor1:

    sql = """
        select count(*)
        from request
        where assigned = '%s'
    """ % (userid)

    cursor2.execute(sql)
    requestcount = cursor2.fetchone()[0]

    print userid, requestcount

cursor2.close()
cursor1.close()
db.close()

Answer 1

MySQL Connector/Python is, by default, non-buffering. This means the data is not fetched automatically and you need to 'consume' all rows. (It works with MySQLdb because that driver is buffering by default.)

Using Connector/Python you have to use the buffered-argument set to True for cursor you use as iterator. In the OP's question, this would be cursor1:

cursor1 = db.cursor(buffered=True)
cursor2 = db.cursor()

You can also use buffered=True as connection argument to make all cursor buffering instantiated by this connection buffering.