Python/MySQL - Export MySQL data to excel

Question

I am wanting to create a function to export data from one of my MySQL tables to excel (csv) by clicking a button. The connection to the bank works normally, as does the graphical interface, but when I click the button, it results in the following error:

Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\TESTES\AppData\Local\Programs\Python\Python38-32\lib\tkinter\__init__.py", line 1883, in __call__
    return self.func(*args)
  File "C:/Users/TESTES/PycharmProjects/Heiken/A_FUNCTIONS_CADASTRO/Excel.py", line 25, in exp_rel_con_pag
    cursor.execute(statm)
  File "C:\Users\TESTES\PycharmProjects\Heiken\venv\lib\site-packages\pymysql\cursors.py", line 170, in execute
    result = self._query(query)
  File "C:\Users\TESTES\PycharmProjects\Heiken\venv\lib\site-packages\pymysql\cursors.py", line 328, in _query
    conn.query(q)
  File "C:\Users\TESTES\PycharmProjects\Heiken\venv\lib\site-packages\pymysql\connections.py", line 516, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "C:\Users\TESTES\PycharmProjects\Heiken\venv\lib\site-packages\pymysql\connections.py", line 750, in _execute_command
    raise err.InterfaceError("(0, '')")
pymysql.err.InterfaceError: (0, '')

Below is the code:

import pymysql
from tkinter import *

excel = Tk()
excel.geometry("")
excel.title("excel teste")


conn = pymysql.connect(host="localhost", port=3306, user="root", password="", database="omnia")
print("connect successfull!")
cursor = conn.cursor()
cursor.execute("SELECT VERSION()")
versao = cursor.fetchone()
print("Versão do gerenciador Maria DB: %s" % versao)
conn.close()

def exp_rel_con_pag():

    conn = pymysql.connect(host="localhost", port=3306, user="root", password="", database="omnia")

    statm = "SELECT * FROM omniacademp INTO OUTFILE '/TMP/OMNIACADEMP.CSV' FIELDS TERMINATED BY ',' ENCLOSED BY "" LINES TERMINATED BY '\n'"
    cursor.execute(statm)

bt = Button(excel, width=15, text="run", command=exp_rel_con_pag)
bt.place(x=10, y=10)



excel.mainloop()

Answer 1

1st I would use with conn: instead of using conn and close(). You fail to close your 2nd connection and the with method will close it for you.

2nd you need to close/recreate your cursor. This is the problem. You can do cursor.close() before conn.close() in the first connection or use with cursor: instead. Your function is trying to use the original cursor from the first connection that has closed and this is why it fails.

All that said you do not "Need" to close the cursor if you close connection and then open a new connection and assign a new cursor you should not run into any problems. I would argue however it is good practice to close out your cursor once it is no longer being used. I have yet to run into a problem with cursor but better safe than sorry as my guess is that the close() method exist for cursor for a reason.

try this and let me know if you have any questions:

import tkinter as tk
import pymysql


root = tk.Tk()
root.geometry("")
root.title("excel teste")

conn = pymysql.connect(host="localhost", port=3306, user="root", password="", database="omnia")
with conn:
    print("connect successfull!")
    cursor = conn.cursor()
    with cursor:
        cursor.execute("SELECT VERSION()")
        versao = cursor.fetchone()
        print("Versão do gerenciador Maria DB: %s" % versao)


def exp_rel_con_pag():
    conn = pymysql.connect(host="localhost", port=3306, user="root", password="", database="omnia")
    with conn:
        statm = "SELECT * FROM omniacademp INTO OUTFILE '/TMP/OMNIACADEMP.CSV' FIELDS TERMINATED BY ',' ENCLOSED BY "" LINES TERMINATED BY '\n'"
        cursor = conn.cursor()
        with cursor:
            cursor.execute(statm)
            results = cursor.fetchone()
            print(results)


tk.Button(root, width=15, text="run", command=exp_rel_con_pag).place(x=10, y=10)
root.mainloop()