How to Export MySQL Table to Excel Using Panda in Python

In this article, I will show you how to fetch MySQL Table into a data frame and then export the data to Microsoft Excel format in Python. We will need two modules in Python, mysql-connector and pandas. With these two modules, we will be able to read MySQL Table and then export to Excel (.xlsx).

Here is an example:

#Create a new MySQL Connection
import mysql.connector
import pandas as pd
try:
    conn = mysql.connector.connect(host='192.168.100.11',
                                   user='dhani',
                                   password='test.1234',
                                   database='test')
    
    #Create a new query
    myquery = 'select * from Tbl_DHSample order by HoleID, From_m'
    #Create a new dataframe and load the data into dataframe
    mydataframe = pd.read_sql(myquery,conn)
    #Export to excel
    mydataframe.to_excel(r'F:\test.xlsx', index=False)
    
    #Catch the error
except mysql.connector.Error as e:
      print(r"Error: {e}")

Due take note of the following line:

mydataframe.to_excel(r'F:\test.xlsx', index=False)

Make sure to change the F:\test.xlsx with your path.

CategoryProgramming

Tagsmysql python

Leave a Reply Cancel reply