Introduction

We will create an example on how to generate PDF report from MySQL database using Python Flask. We will create a web application using light-weight web framework called Flask. We will generate the pdf 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 pdf file as per their chosen location.

We are going to use fpdf API for generating pdf report. As we are not saving this pdf report anywhere in the physical location of the server, so we will send the data in the Response object as an attachment.

Why PDF Reporting?

• PDF format reports allow professionals to edit, share, collaborate and ensure the security of the content within digital documents.
• Reports are mostly generated in PDF format because a PDF file is a “read only” document that cannot be altered without leaving an electronic footprint whereas other formats like image, word, excel etc. can easily be altered without leaving an electronic footprint.
• PDF files are compatible across multiple platforms whereas a word document may not be viewable easily in Mac system.

Prerequisites

Python 3.8.0, Flask 1.1.1, fpdf 1.7.2 (pip install fpdf)

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-pdf-report as per your chosen location.

Related Posts:

• Generate CSV Report from MySQL using Python Flask
• Generate Excel Report from MySQL using Python Flask
• How to write to PDF using Python

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 pdf file.

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

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/pdf')
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()
		
		pdf = FPDF()
		pdf.add_page()
		
		page_width = pdf.w - 2 * pdf.l_margin
		
		pdf.set_font('Times','B',14.0) 
		pdf.cell(page_width, 0.0, 'Employee Data', align='C')
		pdf.ln(10)

		pdf.set_font('Courier', '', 12)
		
		col_width = page_width/4
		
		pdf.ln(1)
		
		th = pdf.font_size
		
		for row in result:
			pdf.cell(col_width, th, str(row['emp_id']), border=1)
			pdf.cell(col_width, th, row['emp_first_name'], border=1)
			pdf.cell(col_width, th, row['emp_last_name'], border=1)
			pdf.cell(col_width, th, row['emp_designation'], border=1)
			pdf.ln(th)
		
		pdf.ln(10)
		
		pdf.set_font('Times','',10.0) 
		pdf.cell(page_width, 0.0, '- end of report -', align='C')
		
		return Response(pdf.output(dest='S').encode('latin-1'), mimetype='application/pdf', headers={'Content-Disposition':'attachment;filename=employee_report.pdf'})
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()

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

In the above Python script we fetch the data from MySQL database table employee.

Then we create FPDF object. We must add a page before we write anything to the pdf page.

Next we get the effective page width omitting the margin of the pdf file.

We must set font before we write any text to the page. We write the heading with label “Employee Data” in the center of the pdf page. We set font Times, size 14, and text type bold.

Then we set font Courier with size 12 and text type normal.

Then we get the column width by dividing the page width. We will write report data into tabular format or in a table.

We set the line break using ln() method and it takes height of the line as a parameter.

We iterate over each row from the results of database and write to the cell of the table.

Finally we write “- end of report -” with different font.

Next we send the data as an attachment in the response and user will get the file as employee_report.pdf.

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

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

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

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

Source Code

Download

Thanks for reading.