python - mysql.connector.errors.InternalError: Unread result found.

Question

import pandas as pd
import mysql.connector as cnmsq

try:
    cnkun = cnmsq.connect(user='root',
                          password='123456',
                          host='127.0.0.1',
                          database='dbkun')
except:
    print('failed connect!')

table = pd.read_excel(r'D:\source.xls')
names = list(table['种名'])
acc_or_syn = [None]*len(table)
acc_family = [None]*len(table)
acc_genus = [None]*len(table)
acc_names = [None]*len(table)
acc_fnames = [None]*len(table)
aos_query = "SELECT is_accepted_name, accepted_id FROM kun_species WHERE species_name=%s"
acc_query = "SELECT species_family, species_genus, species_name, species_full_name FROM kun_species WHERE species_id=%s"
cursor = cnkun.cursor()
for i, name in enumerate(names):
    cursor.execute(aos_query, (name.strip(),))
    for w, s in cursor:
        acc_or_syn[i] = w
        cursor.execute(acc_query, (s,))
        acc_tuple = cursor.fetchone()
        acc_family[i], acc_genus[i], acc_names[i], acc_fnames[i] = acc_tuple

上面这段代码的逻辑是从source.xlsx文件中读取某一列，然后根据该列的每一行字符串从mysql数据中找出该字符串及其对应的值，对于绝大部分source.xlsx列中的字符串，在mysql数据表中都仅有一条记录与之对应，也就是说大多数情况下cursor.execute(aos_query, (name.strip(),))内只有一行记录，但当mysql中遇到执行cursor.execute(aos_query, (name.strip(),))出现多于一行记录时，程序就会出错：

Traceback (most recent call last):
  File "D:\...py", line 43, in <module>
    cursor.execute(acc_query, (s,))
  File "C:\Users\...\Anaconda3\lib\site-packages\mysql\connector\cursor.py", line 476, in execute
    raise errors.InternalError("Unread result found.")
mysql.connector.errors.InternalError: Unread result found.

请问这是什么原因。

Answer 1

for i, name in enumerate(names):
    cursor.execute(aos_query, (name.strip(),))  // 假如这里读到多行数据，结果集缓存在某个地方
    // 下面从游标中读取数据，相当于执行了fetchone，如果有多条数据，那游标对应位置之后还有数据
    for w, s in cursor:   // 可以改为**for w, s in cursor.fetchall()**返回所有数据
        acc_or_syn[i] = w
        // 游标对应位置还有数据，这里又进行查询会导致上次查询的数据丢失，所以给你抛异常
        cursor.execute(acc_query, (s,))
        acc_tuple = cursor.fetchone()
        acc_family[i], acc_genus[i], acc_names[i], acc_fnames[i] = acc_tuple