article

Saturday, February 22, 2020

Generate CSV Report File using Python Flask MySQL


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>

Related Post