Cara Mengubah CSV Report File dengan Python Flask MySQL

2/27/2020

Cara Mengubah CSV Report File dengan Python Flask MySQL

2/27/2020

Berikut ini adalah cara untuk Generate CSV Report File menggunakan 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>