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
- Import all the modules (pymysql, xlwt, pandas.io.sql).
- 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.
- 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.
- Print the data frame.
- 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')
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: