Generate CSV Report from MySQL Database using Python Flask

Introduction

Here I am going to show you how to generate CSV report from MySQL database using Python Flask. We will create a web application using light-weight web framework called Flask. We will generate the CSV (comma separated values) 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 CSV file as per their chosen location.

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

Why do we need CSV data?

There might be number of other reasons to use CSV data but mostly the following reasons may be sufficient:

  • CSV format is considered to be standard format
  • CSV is smaller in size and faster to handle
  • CSV is simple to implement and easy to parse
  • CSV is human readable and easy to edit manually
  • CSV is processed by almost all applications

Prerequisites

Python 3.8.0, Flask 1.1.1, MySQL 8.0.17

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-csv-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 CSV file.

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

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

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

@app.route('/download/report/csv')
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 = io.StringIO()
		writer = csv.writer(output)
		
		line = ['Emp Id, Emp First Name, Emp Last Name, Emp Designation']
		writer.writerow(line)

		for row in result:
			line = [str(row['emp_id']) + ',' + row['emp_first_name'] + ',' + row['emp_last_name'] + ',' + row['emp_designation']]
			writer.writerow(line)

		output.seek(0)
		
		return Response(output, mimetype="text/csv", headers={"Content-Disposition":"attachment;filename=employee_report.csv"})
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()

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

In the above Python script, the download() function renders the template page on UI.

The download_report() function fetches data from employee table.

Then writes to CSV string and finally sends Response as a file attachment. The name of the file is employee_report.csv.

Finally users will be given option to save the CSV file.

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 CSV Report from MySQL</title>
<h2>Generate CSV Report from MySQL</h2>

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

This view page displays with a link and clicking on this link will generate the CSV 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:

csv report from mysql database using python flask

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

Source Code

Download

Thanks for reading.

1 thought on “Generate CSV Report from MySQL Database using Python Flask

Leave a Reply

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