, , , ,

Cara Mengubah CSV Report File dengan Python Flask MySQL

No comments


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;


?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
#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)
?
1
2
3
4
5
6
7
8
9
10
11
12
13
//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>

0 komentar:

Posting Komentar