--
-- Table structure for table `employee`
--
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`position` varchar(100) NOT NULL,
`office` varchar(100) NOT NULL,
`age` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`photo` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `employee`
--
INSERT INTO `employee` (`id`, `name`, `position`, `office`, `age`, `salary`, `photo`) VALUES
(1, 'Tiger Wood', 'Accountant', 'Tokyo', 36, 5689, '01.jpg'),
(2, 'Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London', 56, 5648, '02.jpg'),
(3, 'Jacob thompson', 'Junior Technical Author', 'San Francisco', 23, 5689, '03.jpg'),
(4, 'cylde Ednalan', 'Software Engineer', 'Olongapo', 23, 54654, '04.jpg'),
(5, 'Angelica Ramos', 'Software Engineer', 'San Francisco', 26, 5465, '05.jpg'),
(6, 'Airi Satou', 'Integration Specialist', 'New York', 53, 56465, '06.jpg'),
(8, 'Tiger Nixon', 'Software Engineer', 'London', 45, 456, '07.jpg'),
(9, 'Airi Satou', 'Pre-Sales Support', 'New York', 25, 4568, '08.jpg'),
(10, 'Angelica Ramos', 'Sales Assistant', 'New York', 45, 456, '09.jpg'),
(11, 'Ashton updated', 'Senior Javascript Developer', 'Olongapo', 45, 54565, '01.jpg'),
(12, 'Bradley Greer', 'Regional Director', 'San Francisco', 27, 5485, '02.jpg'),
(13, 'Brenden Wagner', 'Javascript Developer', 'San Francisco', 38, 65468, '03.jpg'),
(14, 'Brielle Williamson', 'Personnel Lead', 'Olongapo', 56, 354685, '04.jpg'),
(15, 'Bruno Nash', 'Customer Support', 'New York', 36, 65465, '05.jpg'),
(16, 'cairocoders', 'Sales Assistant', 'Sydney', 45, 56465, '06.jpg'),
(17, 'Zorita Serrano', 'Support Engineer', 'San Francisco', 38, 6548, '07.jpg'),
(18, 'Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco', 39, 545, '08.jpg');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `employee`
--
ALTER TABLE `employee`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `employee`
--
ALTER TABLE `employee`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;
install from fpdf import FPDF #pip install fpdf
app.py
#app.py
from flask import Flask, render_template, url_for, Response
from flaskext.mysql import MySQL #pip install flask-mysql
import pymysql
from fpdf import FPDF #pip install fpdf
app = Flask(__name__)
mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'testingdb'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)
@app.route('/')
def home():
return render_template('index.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 * 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['id']), border=1)
pdf.cell(col_width, th, row['name'], border=1)
pdf.cell(col_width, th, row['position'], border=1)
pdf.cell(col_width, th, row['office'], 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()
templates/index.html
//templates/index.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Python Flask Generate PDF Report from MySQL</title>
</head>
<body>
<div style="margin: 10px 0 0 10px;width: 600px">
<h2>Python Flask Generate PDF Report from MySQL</h2>
<p>
<a href="{{ url_for('.download_report') }}">Generate Pdf Report</a>
</p>
</div>
</body>
</html>
