Flask can make use of the SQLite3 module of the python to create the database web applications. In this section of the tutorial, we will create a CRUD (create - read - update - delete) application.
from flask import Flask, render_template, request import sqlite3 app = Flask(__name__) #con = sqlite3.connect("employee.db") #print("Database opened successfully") #con.execute("create table Employees (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, address TEXT NOT NULL)") #print("Table created successfully") @app.route("/") def index(): return render_template("index.html"); @app.route("/add") def add(): return render_template("add.html") @app.route("/savedetails",methods = ["POST","GET"]) def saveDetails(): msg = "msg" if request.method == "POST": try: name = request.form["name"] email = request.form["email"] address = request.form["address"] with sqlite3.connect("employee.db") as con: cur = con.cursor() cur.execute("INSERT into Employees (name, email, address) values (?,?,?)",(name,email,address)) con.commit() msg = "Employee successfully Added" except: con.rollback() msg = "We can not add the employee to the list" finally: return render_template("success.html",msg = msg) con.close() @app.route("/view") def view(): con = sqlite3.connect("employee.db") con.row_factory = sqlite3.Row cur = con.cursor() cur.execute("select * from Employees") rows = cur.fetchall() return render_template("view.html",rows = rows) @app.route("/delete") def delete(): return render_template("delete.html") @app.route("/deleterecord",methods = ["POST"]) def deleterecord(): id = request.form["id"] with sqlite3.connect("employee.db") as con: try: cur = con.cursor() cur.execute("delete from Employees where id = ?",id) msg = "record successfully deleted" except: msg = "can't be deleted" finally: return render_template("delete_record.html",msg = msg) if __name__ == '__main__': app.run(debug = True)
//index.html <html> <head> <title>Flask Home</title> </head> <body> <h2>Hi, welcome to the website</h2> <a href="/add">Add Employee</a><br><br> <a href ="/view">List Records</a><br><br> <a href="/delete">Delete Record</a><br><br> </body> </html>
//add.html <!DOCTYPE html> <html> <head> <title>Add Employee</title> </head> <body> <h2>Employee Information</h2> <form action = "/savedetails" method="post"> <table> <tr><td>Name</td><td><input type="text" name="name"></td></tr> <tr><td>Email</td><td><input type="email" name="email"></td></tr> <tr><td>Address</td><td><input type="text" name="address"></td></tr> <tr><td><input type="submit" value="Submit"></td></tr> </table> </form> </body> </html>
<!DOCTYPE html> <html> <head> <title>save details</title> </head> <body> <h3>Hi Admin, {{msg}}</h3> <a href="/view">View Employees</a> </body> </html>
//view.html <!DOCTYPE html> <html> <head> <title>List</title> </head> <body> <h3>Employee Information</h3> <table border=5> <thead> <td>ID</td> <td>Name</td> <td>Email</td> <td>Address</td> </thead> {% for row in rows %} <tr> <td>{{row["id"]}}</td> <td>{{row["name"]}}</td> <td>{{row["email"]}}</td> <td>{{row["address"]}}</td> </tr> {% endfor %} </table> <br><br> <a href="/">Go back to home page</a> </body> </html>
//delete.html <!DOCTYPE html> <html> <head> <title>delete record</title> </head> <body> <h3>Remove Employee from the list</h3> <form action="/deleterecord" method="post"> Employee Id <input type="text" name="id"> <input type="submit" value="Submit"> </form> </body> </html>
//delete_record.html <!DOCTYPE html> <html> <head> <title>delete record</title> </head> <body> <h3>{{msg}}</h3> <a href="/view">View List</a> </body> </html>