Export Data From Mysql to Excel Sheet Using Python

We are given a MySQL database and our task is to export the data into an excel sheet using Python. In this article, we will see how to export data from MySQL to Excel Sheets using Python.

Export Data From Mysql to Excel Sheet Using Python

Below are some of the ways by which we can export data from MySQL to an Excel sheet using Python:

Export Data From Mysql to Excel Sheet Using Pandas Library

Pandas is a Python-based powerful manipulation tool that can be used for reading/importing data from different sources such as MySQL Database into Excel files. In this example, the below code imports Pandas and PyMySQL to connect to a MySQL database executes a query to retrieve data from a table, loads it into a DataFrame, exports it to an Excel file named ‘output.xlsx’, and closes the database connection.

Python3

import pandas as pd
import pymysql

connection = pymysql.connect(host='localhost',
                             user='username',
                             password='password',
                             database='database_name')

query = "SELECT * FROM table_name"

data = pd.read_sql(query, connection)

connection.close()

data.to_excel('output.xlsx', index=False)

Example:

Let’s create a table in MySQL called python_connector_tb. The following queries will be used to do so:

CREATE TABLE python_connector_tb (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

INSERT INTO python_connector_tb (name, age, email) VALUES
('John Doe', 30, 'john@example.com'),
('Jane Smith', 25, 'jane@example.com'),
('Bob Johnson', 40, 'bob@example.com');

Output:

Mysql table

Now, we will use above code to export this into Excel sheet.

output.xlsx

Export Data From Mysql to Excel Sheet Using Openpyxl Library

In this example, below code imports PyMySQL and openpyxl libraries to interact with MySQL databases and Excel files, respectively. It establishes a connection to a MySQL database, creates a new Excel workbook, executes an SQL query to fetch data from a table, and writes the fetched data to the Excel workbook.

Python3

import pymysql
from openpyxl import Workbook

# Connect to MySQL database
connection = pymysql.connect(host='localhost',
                             user='username',
                             password='password',
                             database='database_name')

# Create a new Excel workbook
wb = Workbook()
ws = wb.active

# Execute SQL query and fetch data
query = "SELECT * FROM table_name"
cursor = connection.cursor()
cursor.execute(query)
data = cursor.fetchall()

# Write data to Excel
for row_index, row_data in enumerate(data, start=1):
    for col_index, cell_data in enumerate(row_data, start=1):
        ws.cell(row=row_index, column=col_index, value=cell_data)

wb.save('output.xlsx')

# Close the connection
connection.close()

Example:

Let’s create a table in MySQL called python_connector_tb. The following queries will be used to do so:

CREATE TABLE python_connector_tb (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

INSERT INTO python_connector_tb (name, age, email) VALUES
('John Doe', 30, 'john@example.com'),
('Jane Smith', 25, 'jane@example.com'),
('Bob Johnson', 40, 'bob@example.com');

Output:

SQL table

Now, we will use above code to export this into Excel sheet.

output.xlsx