Generate Excel Report from MySQL Database using Python Flask

Introduction

Here I am going to show you how to generate excel report from MySQL database using Python Flask. We will create a web application using light-weight web framework called Flask. We will generate the excel file on the fly from MySQL database in this web application. A download link will be provided on front-end or UI on which end users will click and will get an option to save the generated excel file as per their chosen location.

We are going to use xlwt API for generating excel report. As we are not saving this excel report anywhere in the physical location of the server, so we will need to write the data as a byte. Therefore we will use here io package from Python 3.

Why Excel Reporting?

  • Most people in the organization are likely to be familiar with excel, hence it ensures a short (or nonexistent) learning curve during implementation.
  • It allows you to use templates and formulas to aggregate data.
  • It has very easy-to-use charting functionality compared to other software, and several helpful built-in features.
  • It is also great because you have the ability to cut and paste into reports. Most people write reports in Word, so it’s easy to cut and paste data or charts from Excel into Word.

Prerequisites

Python 3.8.0, Flask 1.1.1, xlwt 1.3.0 (pip install xlwt)

Creating MySQL Table

As we are going to generate CSV report from MySQL database, so we need a table and data into this table to be able to generate report from the table.

We will create an employee table and dump some sample data into it.

CREATE TABLE IF NOT EXISTS `employee` (
  `emp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_first_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `emp_last_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `emp_mgr_id` int(11) DEFAULT NULL,
  `emp_designation` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7975 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `employee` (`emp_id`, `emp_first_name`, `emp_last_name`, `emp_mgr_id`, `emp_designation`) VALUES
	(7369, 'SMITH', 'JHON', 7902, 'CLERK'),
	(7499, 'ALLEN', 'BORDER', 7698, 'SALESMAN'),
	(7521, 'WARD', 'SPACE', 7698, 'SALESMAN'),
	(7654, 'MARTIN', 'FOWLER', 7698, 'SALESMAN'),
	(7698, 'BLAKE', 'RAY', NULL, 'MANAGER'),
	(7782, 'CLARK', 'MICHAEL', NULL, 'MANAGER'),
	(7788, 'SCOTT', 'TIGER', 7566, 'ANALYST'),
	(7839, 'KING', 'ROY', NULL, 'VICE PRESIDENT'),
	(7844, 'TURNER', 'RICK', 7698, 'SALESMAN'),
	(7876, 'ADAMS', 'EVE', 7788, 'CLERK'),
	(7900, 'JAMES', 'BOND', 7698, 'CLERK'),
	(7902, 'FORD', 'LAMBDA', 7566, 'ANALYST'),
	(7934, 'MILLER', 'JOHN', 7782, 'CLERK'),
	(7954, 'FRANK', 'JOHN', 7782, 'MANAGER'),
	(7964, 'MARTIN', 'HIKMAN', NULL, 'CLERK'),
	(7974, 'APRIL', 'HICKMAN', 7782, 'SALESMAN');

Creating Project Directory

Create a project root directory called python-flask-mysql-excel-report as per your chosen location.

Related Posts:

We may not mention the project’s root directory name in the subsequent sections but we will assume that we are creating files with respect to the project’s root directory.

Configure Flask

We here configure application through flask framework. Create a file called app.py with the below code.

from flask import Flask

app = Flask(__name__)

Database Configuration

We create the below db.py Python script to setup the MySQL database configurations for connecting to database and storing user information into employee table under roytuts database.

We need to configure database connection with flask module and that’s why we have imported app module and setup the MySQL configuration with flask module.

Make sure to change the database configuration values according to your database setup.

from app import app
from flaskext.mysql import MySQL

mysql = MySQL()
 
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'roytuts'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

Configuring URLs and Retrieving Records

Now we will configure URLs for rendering template file for front-end or UI which will be displayed to the end users.

We will also retrieve data from MySQL database table employee and write to the downloadable excel file.

Create a Python script called main.py with the following source code.

import io
import xlwt
import pymysql
from app import app
from db import mysql
from flask import Flask, Response, render_template

@app.route('/')
def upload_form():
	return render_template('download.html')

@app.route('/download/report/excel')
def download_report():
	conn = None
	cursor = None
	try:
		conn = mysql.connect()
		cursor = conn.cursor(pymysql.cursors.DictCursor)
		
		cursor.execute("SELECT emp_id, emp_first_name, emp_last_name, emp_designation FROM employee")
		result = cursor.fetchall()
		
		#output in bytes
		output = io.BytesIO()
		#create WorkBook object
		workbook = xlwt.Workbook()
		#add a sheet
		sh = workbook.add_sheet('Employee Report')
		
		#add headers
		sh.write(0, 0, 'Emp Id')
		sh.write(0, 1, 'Emp First Name')
		sh.write(0, 2, 'Emp Last Name')
		sh.write(0, 3, 'Designation')
		
		idx = 0
		for row in result:
			sh.write(idx+1, 0, str(row['emp_id']))
			sh.write(idx+1, 1, row['emp_first_name'])
			sh.write(idx+1, 2, row['emp_last_name'])
			sh.write(idx+1, 3, row['emp_designation'])
			idx += 1
		
		workbook.save(output)
		output.seek(0)
		
		return Response(output, mimetype="application/ms-excel", headers={"Content-Disposition":"attachment;filename=employee_report.xls"})
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()

if __name__ == "__main__":
    app.run()

In the above Python script, we fetch dat from MySQL database.

We create excel WorkBook object. We add a sheet called “Employee Report” to it. We add headers to the sheet. We iterate over fetched data rows and write to the sheet one by one.

We save the data into byte array. We need to seek(0) to set the file’s current position at the beginning.

Finally we send the data as an attachment in the response so that users can download it in a file called employee_report.xls.

Template File

The template or view file is kept into templates directory. templates is the standard directory in Flask framework, where you need to put your view or template files.

<!doctype html>
<title>Python Flask File Generate Excel Report from MySQL</title>
<h2>Generate Excel Report from MySQL</h2>

<p>
	<a href="{{ url_for('.download_report') }}">Generate Excel Report</a>
</p>

This view page displays with a link and clicking on this link will generate the excel file and asks users to save the file.

Testing the Application

Now navigate to the project root directory and execute the command python main.py or simply main.py, your server will be started on default port 5000.

If you want to change the port then you can change the line app.run() to app.run(port=5001), where 5001 is the new port.

Accessing home page using http://localhost:5000 will give you the following output:

excel report from mysql database using python flask

Now you can click on the link to generate excel report and save the generated excel file.

Source Code

Download

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *