Export data from Mysql to the Excel sheet through Python

In this tutorial, you are going to learn how to export the data from MySQL to the excel sheet through Python. To export the data, we need the inbuilt modules of Python that will help us with the export or import of data. The modules that will be needed to import are xlwt, pandas.io.sql and pymysql.

from pymysql import* 
import xlwt 
import pandas.io.sql as sql

The pymysql module is a pure – Python Mysql client library. It will aid us to connect the Mysql server to the Python.
The xlwt module is a library that will write the data and arrange the information for the excel files.
The pandas.io.sql module will read the data from MySQL.

Note: If the above module does not exist on your Python version, then you can install it from the command prompt by writing the command pip3 install module_name. For installing the module you should have an internet connection.

A program to export the data from MySQL to the excel sheet using Python

1. Import all the modules (pymysql, xlwt, pandas.io.sql).
2. connect() method of pymysql will connect the mysql with the python. The arguments of connect are:
   user = Username to login ,i.e., root.
   password = Password of mysql. (Mysql password is ‘apoo06’ so I have mentioned here apoo06.)
   host = Host where the database server is located (‘localhost’).
   database = Name of the database whose data has to be used.
3. read_sql() method of pandas.io.sql returns the dataframe corresponding to the given query string.
   Select * from emp is a query string where emp is the table name. This query string will take all the data from the table and save it in the data frame. The con is the object of connect method.
4. Print the data frame.
5. to_excel() method of xlwt module will save the data in the excel sheet where ds.xls is the name of the excel sheet.

# import the modules
from pymysql import*
import xlwt
import pandas.io.sql as sql

# connect the mysql with the python
con=connect(user="root",password="apoo06",host="localhost",database="ds")

# read the data
df=sql.read_sql('select * from emp',con)

# print the data
print(df)

# export the data into the excel sheet
df.to_excel('ds.xls')

Output:-

      emp_id          emp_name          emp_sal
0       101           Saurab             10000
1       102           Arpit              20000
2       103           Alok               6000
3       104           Preeti             9000
4       105           Sukriti            19000

Note: The excel sheet will be saved where your program has been saved (for ex: my program has been saved on the desktop so my excel sheet will be saved on the desktop).

Check out the other tutorials on Python:

Python program to print numeric pattern
Remove multiple spaces from a string in Python

One response to “Export data from Mysql to the Excel sheet through Python”

1. Gevorg says:

   December 17, 2019 at 12:35 pm

   it’s okay,but how can I add a new sheet?

   Reply