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>
