Flask SQLite

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.

Since we have given a detailed overview of how a python application can interact with the SQLite database, to revise the concept, please visit the link: Python SQLite.

CRUD Application in flask

Here, we will manage the employee information in the SQLite database using a flask script to which the admin can interact. For this purpose, database employee.db contains the related tables whereas the table Employees contains information about the employees.

First, let us create a database employee.DB and the table Employees in SQLite using the following python script.

EmoloyeeDB.py

import sqlite3

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")

con.close()

To build a CRUD application in the flask, we must focus on the view functions (to take the input) and the controllers (to save that data into the database).

Let us look at the view function: index() which is associated with the URL (/). It renders a template index.html.

@app.route("/")
def index():
	return render_template("index.html");

The following HTML template (index.html) is considered as the home page of our application. It provides the links using which we can add, view, and delete the data stored in the database.

index.html

<!DOCTYPE html>
<html>
<head>
	<title>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>

The view function add() which is associated with the URL (/add) renders the template add.html given below. It provides the form to enter the employee information.

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>

All the details entered by the Admin is posted to the URL /savedetails which is associated with the function saveDetails(). The function saveDetails() is given below which contains the code for extracting the data entered by the admin and save that data into the table Employees.

It also generates the message depending upon the cases in which the data is successfully inserted, or some error occurred.

@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()

It renders a template success.html to display the message to the admin. It also contains a link to view the records entered by the user.

success.html

<!DOCTYPE html>
<html>
<head>
	<title>save details</title>
</head>
<body>
	<h3>Hi Admin, {{msg}}</h3>
	<a href="/view">View Employees</a>
</body>
</html>

The function delete() is associated to the URL /delete. It renders an HTML template delete.html which provides the form to the admin that prompts to enter the Employee_Id of which the records are to be deleted. It also contains a link to the /view URL that shows all the records to the admin.

The HTML template delete.html is given below.

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>

The Employee_Id entered by the admin is posted to the URL /deleterecord which contains the python code to establish the connection to the database and then delete all the records for the specified Employee ID. The URL /deleterecord is associated with the function deleterecord() which is given below.

@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)

The function deleterecord() generates a message depending upon the scenario whether the data is successfully deleted or some error occurred. It renders an HTML template delete_record.html to show the message to the admin.

delete_record.html

<!DOCTYPE html>
<html>
<head>
	<title>delete record</title>
</head>
<body>
<h3>{{msg}}</h3>
<a href="/view">View List</a>
</body>
</html>

The template delete_record.html contains a link to the URL /view which shows the Employee records to the admin.

It is associated with the function view() which establishes the connection to the database, fetch all the information and pass that information to the HTML template view.html to display on the client side browser.

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) 

The HTML template view.html which shows all the information on the browser is given below.

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>

The full python script is given below.

crud.py

from flask import *
import sqlite3

app = Flask(__name__)

@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)

Run the python script EmployeeDB.py to create the database and the Employees table using the following command on the terminal.

Now, run the flask script crud.py and visit https://localhost:5000 on the browser.

Click on the link Add Employee to add a new employee to the database.

Fill this form and click submit to save the details into the database.

Now, click on the view employee to list all the employees of the database. Till now, we have only one employee in the list as shown in the below image.

Click on the link given at the bottom of the page to go back to the home page.

Now click on the Delete Record to check how the script deletes the record for the specific employee_id.

Enter any employee id for which the records are to be deleted. Here, we must notice that if the entered Employee Id doesn't exist in the database, then an error message will be displayed. Let's enter the employee id 1 to delete the employee john from the database.