Generate CSV Report File using Python Flask MySQL
Database Table
CREATE TABLE `employees` (
`id` int(10) UNSIGNED NOT NULL,
`first_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
`last_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`mgr_id` int(11) DEFAULT NULL,
`designation` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `employees`
ADD PRIMARY KEY (`id`);
ALTER TABLE `employees`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
#app.py
from flask import Flask, render_template, url_for, Response
from flaskext.mysql import MySQL
import pymysql
import io
import csv
app = Flask(__name__)
#Database Configuration
mysql = MySQL()
# MySQL configurations
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 download():
return render_template('download_csv.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 id, first_name, last_name, designation FROM employees")
result = cursor.fetchall()
output = io.StringIO()
writer = csv.writer(output)
line = ['Id, First Name, Last Name, Designation']
writer.writerow(line)
for row in result:
line = [str(row['id']) + ',' + row['first_name'] + ',' + row['last_name'] + ',' + row['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(debug=True)
//download_csv.html
<html>
<head>
<meta charset="UTF-8">
<title>Generate CSV Report File using Python Flask MySQL</title>
</head>
<body>
<h2>Generate CSV Report File using Python Flask MySQL</h2>
<p>
<a href="{{ url_for('download_report') }}">Generate CSV Report File</a>
</p>
</body>
</html>
