Python连接MySQL时EXPLAIN命令使用及无结果问题求助

我在Python中连接MySQL执行EXPLAIN命令时遇到了一个奇怪的状况：普通查询语句能正常返回结果并通过DataFrame展示，但执行EXPLAIN后的结果却完全没有输出。以下是我从Visual Studio导出的代码（保留原有缩进，注释为葡萄牙语）：

import pandas

print("#########################################################################################")
# ANTES DE EXECUTAR CADA UMA DAS QUERYS VEREFICAR QUAIS AS TABELAS QUE SAO UTLIZADAS EM CADA UMA
# VERIFICAR QUE TABELAS EXISTEM PARA O PROFESSOR
query = "EXPLAIN " + sqlqueryprof
print("QUERY EXPLAIN DO PROFESSOR")
print(query)
#connection.commit()
cursor = connection.cursor()
cursor.execute(query)
explain_prof = cur.fetchall()
print("TABELA EXPLICATIVA DA QUERY DO PROFESSOR")
data_frame_expl_prof = pandas.DataFrame(list(explain_prof))
print(data_frame_expl_prof)
# VERIFICAR QUE TABELAS EXISTEM PARA O ALUNO
query = "EXPLAIN " + sqlqueryalun
print("QUERY EXPLAIN DO ALUNO")
print(query)
#connection.commit()
cursor = connection.cursor()
cursor.execute(query)
explain_alun = cur.fetchall()
print("TABELA EXPLICATIVA DA QUERY DO ALUNO")
data_frame_expl_alun = pandas.DataFrame(list(explain_alun))
print(data_frame_expl_alun)
print("#################################################################")
# EXECUTAR CADA UMA DAS QUERYS
cursor_prof = connection.cursor()
cursor_prof.execute(sqlqueryprof)
records_prof = cursor_prof.fetchall()
data_frame_prof = pandas.DataFrame(list(records_prof))
print("IMPRIMIR O DATA FRAME RESULTANTE DO PROFESSOR A CONEXAO")
print(data_frame_prof)
cursor_alun = connection.cursor()
cursor_alun.execute(sqlqueryalun)
records_query = cursor_alun.fetchall()
data_frame_alun = pandas.DataFrame(list(records_query))
print("IMPRIMIR O DATA FRAME RESULTANTE DO ALUNO A CONEXAO")
print(data_frame_alun)

问题根源与修复方案 #

我仔细检查了代码，发现最关键的错误是变量名不一致：

你创建了游标对象cursor = connection.cursor()，但后续获取结果时却用了cur.fetchall()——cur这个变量在代码里根本没有定义！这会导致explain_prof和explain_alun实际上是空的（或者抛出NameError，如果之前没定义过cur的话），自然就没有输出了。

修复后的代码片段

把所有cur.fetchall()替换为cursor.fetchall()即可：

# ... 前面代码不变
cursor = connection.cursor()
cursor.execute(query)
explain_prof = cursor.fetchall()  # 这里把cur改成cursor
print("TABELA EXPLICATIVA DA QUERY DO PROFESSOR")
data_frame_expl_prof = pandas.DataFrame(list(explain_prof))
print(data_frame_expl_prof)

# ... 中间代码不变
cursor = connection.cursor()
cursor.execute(query)
explain_alun = cursor.fetchall()  # 这里同样把cur改成cursor
print("TABELA EXPLICATIVA DA QUERY DO ALUNO")
data_frame_expl_alun = pandas.DataFrame(list(explain_alun))
print(data_frame_expl_alun)
# ... 后面代码不变

额外优化建议 #

1. 使用pandas直接读取结果：可以用pandas.read_sql()简化代码，它会自动处理游标和结果转换，避免手动fetchall的失误：

   import pandas as pd
   
   # 执行EXPLAIN并生成DataFrame
   data_frame_expl_prof = pd.read_sql("EXPLAIN " + sqlqueryprof, connection)
   print("TABELA EXPLICATIVA DA QUERY DO PROFESSOR")
   print(data_frame_expl_prof)
   

2. 验证原查询的有效性：先在MySQL客户端（比如Navicat、MySQL Workbench）中执行EXPLAIN + 你的查询语句，确认是否能返回结果。如果原查询本身有语法错误，EXPLAIN也不会返回任何内容。
3. 关闭游标资源：建议使用with语句来管理游标，避免资源泄漏：

   with connection.cursor() as cursor:
       cursor.execute("EXPLAIN " + sqlqueryprof)
       explain_prof = cursor.fetchall()
   

内容的提问来源于stack exchange，提问作者Joao Gracio