Flask SQLite

Flask SQLite

Flask allows us to use the SQLite3 which is one of the python modules. It is used for creating database web applications. To understand this, we will be going to create a CRUD application.

CRUD Application in the Flask

 Here we will manage the student information in the SQLite using the flask script, and admin can interact with it. This database named student.db contains related tables, and these tables contain information about the students.

  1. Create a database named student.db and table  “student” in the SQLite using the python script given below.

StudentDb.py

import sqlite3  
 con = sqlite3.connect("student.db")  
 print("Database opened successfully")  
 con.execute("create table Student (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, address TEXT NOT NULL)")  
 print("Table created successfully")  
 con.close()   
  •  In order to build a crud application in the Flask, we have to focus on the view functions like index()  and controllers to save that data in the database.
@app.route("/")  
 def index():  
 return render_template("index.html"); 

In the above-given code, the index(), the named method, is a view function that is associated with the URL (\). It displays the template  index.html.

This index.html  template is the hompage  of the crud application. It contains the several links  by using them  we can add ,delete and view  the data saved 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()  is associated with the URL (/add). It renders the template add.html given below. It provides a basic form to enter the student information.

add.html

<!DOCTYPE html>  
 <html>  
 <head>  
 <title>Add Student</title>  
 </head>  
 <body>  
  <h2>Student 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 information entered by the admin is sent to the “save details” URL that is associated with the savedetails() methods. This method contains the code for the data entered by admin and store that data in the student table. It also produced several messages like data is saved successfully, and there is 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("student.db") as con:  
 cur = con.cursor()  
 cur.execute("INSERT into Student (name, email, address) values (?,?,?)",(name,email,address))   
 con.commit()  
 msg = "Student  successfully Added"  
 except:  
 con.rollback()  
 msg = "We can not add the student to the list"  
 finally:   
 return render_template("success.html",msg = msg)  
 con.close() 

It renders a success.html template in which a message is displayed to the admin. It also contains a link to view all records input by the users.

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 method delete() is associated with the URL/delete. It displays the delete.html template that provides a form to the admin, which prompts to enter the student_id to delete that record of that specific student.It also contains a link to view all records input by the users.

Delete.html

<!DOCTYPE html>  
 <html>  
 <head>  
     <title>delete record</title>  
 </head>  
 <body>  
     <h3>Remove Student 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 student_id entered by the admin is posted to the URL /deleterecord that  contains the python code to establish the connection with the database and then delete that record for the specified Student ID. The URL /deleterecord is associated with the method deleterecord() which is given below.

@app.route("/deleterecord",methods = ["POST"])  
 def deleterecord():  
 id = request.form["id"]  
 with sqlite3.connect("student.db") as con:  
 try:  
 cur = con.cursor()   
 cur.execute("delete from Student where id = ?",id) 
 msg = "record successfully deleted"  
 except:  
 msg = "can not be deleted"  
 finally:  
 return render_template("delete_record.html",msg = msg) 

The method deleterecord() generates a message depending upon the condition whether the data is successfully deleted or any error occurred. It displayed an HTML template delete_record.html that shows a 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 that shows the Student records to the admin.

It is associated with the method view() that establishes the connection with the database, fetches all the information, and passes that data to the view.html  template to display on the client side web-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 ) that displays all the information on the browser is given below. 

view.html

<!DOCTYPE html>  
 <html>  
 <head>  
     <title>List</title>  
 </head>  
 <body>  
 <h3>Student  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 complete 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("Student.db") as con:  
 cur = con.cursor()   
 cur.execute("INSERT into Student (name, email, address) values (?,?,?)",(name,email,address))  
 con.commit()  
 msg = "Student successfully Added"   
 except:  
 con.rollback()  
 msg = "We can not add the Student to the list"  
 finally:  
 return render_template("success.html",msg = msg)  
 con.close()  
 @app.route("/view")  
 def view():  
 con = sqlite3.connect("Student.db")  
 con.row_factory = sqlite3.Row  
 cur = con.cursor()  
 cur.execute("select * from Student")   
 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("Student.db") as con:  
 try:  
 cur = con.cursor()  
 cur.execute("delete from Student 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)  

Now run the server and execute the above-given python scripts using the following commands shown in the below screenshot.

  • Py –m  vene env
  • env\scripts\activate
  • Set Flask_app=crud.py
  • Flask run
Flask SQLite

Now copy  URL address from the cmd and paste it in the url section in the web-browser as given above.

Output

Flask SQLite

Now click on the add student record to add the record of the student.

Flask SQLite

Fill the details of the student and click on the submit button.

Flask SQLite

Now click on the View Students to view the student records.

Flask SQLite

Click on the “Go to home page” to navigate the home page.

Flask SQLite

Click on the “delete Record” button.

Now enter the student id to delete the record of that specific student and click on the submit button.

Flask SQLite

To confirm the delete operation is executed successfully, click on the View List button.

Flask SQLite