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
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 56 57 | #app.pyfrom flask import Flask, render_template, Responseimport psycopg2 #pip install psycopg2 import psycopg2.extrasimport ioapp = 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) |
1 2 3 4 5 6 7 8 9 10 11 12 | //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> |
