Crate database table
CREATE TABLE students (
id serial PRIMARY KEY,
fname VARCHAR ( 40 ) NOT NULL,
lname VARCHAR ( 40 ) NOT NULL,
email VARCHAR ( 40 ) NOT NULL
);
SELECT * FROM students
INSERT INTO students (id, fname, lname, email)
VALUES('1','cairocoders','ednalan', 'cairocoders@gmail.com');
Multiple insert
INSERT INTO
students(id,fname,lname,email)
VALUES
('3','Quinn','Flynn'', 'Flynn'@gmail.com'),
('4','Tiger','nizon', 'nizon@gmail.com'),
('5','Airi','sato', 'sato@gmail.com');
ModuleNotFoundError: No module named 'xlwt'
pip install xlwt
import xlwt #pip install xlwt https://pypi.org/project/xlwt/
(venv) PS C:\flaskmyproject> pip install xlwt
#app.py
from flask import Flask, render_template, Response
import psycopg2 #pip install psycopg2
import psycopg2.extras
import io
import xlwt #pip install xlwt https://pypi.org/project/xlwt/
app = Flask(__name__)
DB_HOST = "localhost"
DB_NAME = "sampledb"
DB_USER = "postgres"
DB_PASS = "admin"
conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST)
@app.route('/')
def Index():
return render_template('index.html')
@app.route('/download/report/excel')
def download_report():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("SELECT * FROM students")
result = cur.fetchall()
#for row in result:
# print(row)
#output in bytes
output = io.BytesIO()
#create WorkBook object
workbook = xlwt.Workbook()
#add a sheet
sh = workbook.add_sheet('Student Report')
#add headers
sh.write(0, 0, 'Id')
sh.write(0, 1, 'First Name')
sh.write(0, 2, 'Last Name')
sh.write(0, 3, 'Email')
idx = 0
for row in result:
sh.write(idx+1, 0, str(row['id']))
sh.write(idx+1, 1, row['fname'])
sh.write(idx+1, 2, row['lname'])
sh.write(idx+1, 3, row['email'])
idx += 1
workbook.save(output)
output.seek(0)
return Response(output, mimetype="application/ms-excel", headers={"Content-Disposition":"attachment;filename=student_report.xls"})
if __name__ == "__main__":
app.run(debug=True)
template/index.html
//template/index.html
<html>
<head>
<title>File Generate Excel Report using Python Flask and PostgreSQL</title>
</head>
<body>
<h2>File Generate Excel Report using Python Flask and PostgreSQL</h2>
<p>
<a href="{{ url_for('.download_report') }}">Generate Excel Report</a>
</p>
</body>
</html>
