Python mysql.connector InternalError: Unread result found

MySQLdb 在 Mac OS X 會有問題，改成 mysql.connector 就解決了，但新的問題建立 connection 時就會發生 exception…

Unread result found

---

解法：

import mysql.connector
import csv

cnx = mysql.connector.connect(user='user', password='password',
                          host='server',
                          port='3306',
                          database='db',
                          connect_timeout=2000,
                          buffered=True)

try:

    query = "...large query that returns > 500 records..."

    cursor = cnx.cursor()
    cursor.execute(query)

    print 'this will print'
    results = cursor.fetchall()
    print 'this will not print'

    with open("data.csv", "wb") as csv_file:

        csv_writer = csv.writer(csv_file)
        csv_writer.writerows(results)

finally:
    cursor.close()
    cnx.close()

---

connection 建立時，多帶  buffered=True參數。

 

It would appear that you need:

cursor = conn.cursor(buffered=True,dictionary=true)

in order to abandon a resultset mid-stream.

Full disclosure, I am a mysql dev, not a python dev.

See the Python Manual Page MySQLConnection.cursor() Method and cursor.MySQLCursorBuffered Class.

All rows are read immediately, true. Fantasic for small to mid-sized resultsets.

The latter reference above states:

For queries executed using a buffered cursor, row-fetching methods such as fetchone() return rows from the set of buffered rows. For nonbuffered cursors, rows are not fetched from the server until a row-fetching method is called. In this case, you must be sure to fetch all rows of the result set before executing any other statements on the same connection, or an InternalError (Unread result found) exception will be raised.

As a side note, you can modify your strategy by using pagination. The MySQL LIMIT clausesupports this with the offset,pageSize settings:

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

---

在 cursor.exeucte 裡加入 buffer=True：

cursor = cnx.cursor(buffered=True)

The reason is that without a buffered cursor, the results are “lazily” loaded, meaning that “fetchone” actually only fetches one row from the full result set of the query. When you will use the same cursor again, it will complain that you still have n-1 results (where n is the result set amount) waiting to be fetched. However when you use a buffered cursor the connector fetches ALL rows behind the scenes and you just take one from the connector so the mysql db won’t complain. Hope it helps.

然後在 cursor 使用完記得要 curosr.close() 避免不必要的 lock.