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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
The HTML template view.html which shows all the information on the browser is given below.
The full python script is given below.
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.
Hence, the record for the employee with id 1 is deleted. Here, we can confirm this by viewing the list. Click View List to view the list.