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()

Mike - SMT · Accepted Answer · 2019-12-17 19:14:50Z

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()

edited Dec 17, 2019 at 19:14

answered Dec 17, 2019 at 19:09

Mike - SMT

15.3k5 gold badges47 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

gdn Over a year ago

Apparently the only error it gives me now is one of syntax: pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LINES TERMINATED BY '\n'' at line 1")

Mike - SMT Over a year ago

@gdn that is an issue with your sql statement. You should probably not be trying to write to CSV file from the select statement. Instead get results then use those results to write to CSV using something like openpyxl.

gdn Over a year ago

In the end I did it, thanks for your help. Thank you very much. You are only giving a warning now, but the report is generated.

Mike - SMT Over a year ago

@gdn I typically use pyodbc for my SQL connections so I am not sure what kind of warning you are getting as I do not use pymysql.

gdn Over a year ago

This one:

C:\Users\TESTES\PycharmProjects\Heiken\venv\lib\site-packages\pymysql\cursors.py:170: Warning: (1287, "'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead")   result = self._query(query)

Robert Miles · Accepted Answer · 2019-12-17 19:09:23Z

The connection is dead. As you can see in the code for PyMySQL, that error is returned when there isn't a connection to the server (if not self._sock).

You need to create a cursor for your conn object. It's reusing the old cursor object from when you got the MariaDB version.

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")

    cursor = conn.cursor()

    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()

Collectives™ on Stack Overflow

Python/MySQL - Export MySQL data to excel

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related