Introduction

The tutorial on Python web application CRUD example using Flask and MySQL will show you the basic CRUD operations.

CRUD means Create, Read, Update and Delete operations. In the below example we will create new record, read existing records, update the existing record and delete the existing record.

We will use MySQL database as a permanent storage for performing such basic CRUD operations. We will use Flask module to handle the requests and responses from end user or clients and this module will work as a web application framework for Python.

By default Flask module does not come with the Python installation and you have to install it separately using the command pip install flask from the cmd prompt (open in administrator mode) in Windows environment.

Prerequisites

Python 3.8.0, Flask 1.1.1, Flask Table 0.5.0, MySQL 8.0.17, Windows 10 64 bit

Related Posts:

Preparing your workspace

Preparing your workspace is one of the first things that you can do to make sure that you start off well. The first step is to check your working directory.

When you are working in the Python terminal, you need first navigate to the directory, where your file is located and then start up Python, i.e., you have to make sure that your file is located in the directory where you want to work from.

For this, Python Web Application CRUD Example using Flask and MySQL, we need modules, such as, table, flask, mysql. The module table is required to show data in tabular format on HTML view, the module flask works as a web framework and mysql module is required to establish connection with MySQL database and query the database using Python programming language.

If you find your any of the required modules does not exist then you need to install it. You may find below screen-shots how I installed the below required modules – table and mysql.

Installation of Modules – Table and MySQL

Table module installation

MySQL module installation

Let’s move on to the example…

Implementation of Example

In the below image you see I have opened a cmd prompt and navigated to the directory where I have to create Python script for implementing Python web application CRUD example using Flask MySQL.

Please go through the following steps in order to implement Python web application CRUD example using Flask MySQL:

1. First create directories. The top level directory or project root directory, which is shown in the above figure under Preparing your workspace section. Then inside this directory create user_crud directory. Inside this user_crud directory create templates directory. This templates directory will contain all html template files, such as we need several HTML files for creating CRUD operations.

2. Create the below app.py script(py is the extension to indicate Python script) where we import the flask module. This file should be created under user_crud directory. Notice how we create flask instance. We have configured a secret key, which is required for your application’s session.

from flask import Flask app = Flask(__name__) app.secret_key = "secret key"

3. We create the below db_config.py Python script under user_crud to setup the MySQL database configurations for connecting to database. We need to configure database connection with flask module and that’s why we have imported app module and setup the MySQL configuration with flask module.

from app import app from flaskext.mysql import MySQL mysql = MySQL() # MySQL configurations app.config['MYSQL_DATABASE_USER'] = 'root' app.config['MYSQL_DATABASE_PASSWORD'] = 'root' app.config['MYSQL_DATABASE_DB'] = 'roytuts' app.config['MYSQL_DATABASE_HOST'] = 'localhost' mysql.init_app(app)

4. Next we need main.py script under user_crud directory, that will define all URIs or Action paths for performing CRUD operations. It will also connect to MySQL database server and query the database to read, insert, update and delete.

This script is the perfect instance of Python Web Application CRUD Example using Flask and MySQL. In this script we perform CRUD operations with user interaction, we display all records, we add new record, we update existing record and we delete record from the tabular data as we wish.

We first import required modules into the script. We then define the end-point /new_user for displaying a view where user will add new record. Next we need to validate user input data and save those input data into MySQL database, so we define another end-point /add. We use http method GET for displaying view and POST method for sending data to server side. By default http method is GET if you do not specify http method. We use render_template function from flask to show the view. Initially when there is no user information then you won’t see any user information on the home page.

You can add new user from the Add User link shown on home page and you will see data get displayed on home page. We do not want to store password as a plain text so we are masking the password using generate_password_hash() function.

Next we display all records from MySQL database in the view users.html using the root end-point /. Notice here when we render view, we also send table data as a second argument to the render_template function. Notice also we have configured the MySQL connection with pymysql.cursors.DictCursor to fetch rows as a data dictionary so that we retrieve each column value as a key/value pair (column name/column value) that will help us to display data in tabular format using flask’ table API.

Then we show edit form where user update his information while click on Edit link in the HTML table data. We define /update end-point for saving updated information into MySQL database. Once updated we redirect to the home page where a list of users are shown in HTML table.

Next we define delete end-point where user deletes his information while clicks on Delete link in the HTML table data. Finally redirect to the home page.

When we add, update or delete user then we redirect to the home page and show the appropriate message to the user.

import pymysql from app import app from tables import Results from db_config import mysql from flask import flash, render_template, request, redirect from werkzeug.security import generate_password_hash, check_password_hash @app.route('/new_user') def add_user_view(): return render_template('add.html') @app.route('/add', methods=['POST']) def add_user(): conn = None cursor = None try: _name = request.form['inputName'] _email = request.form['inputEmail'] _password = request.form['inputPassword'] # validate the received values if _name and _email and _password and request.method == 'POST': #do not save password as a plain text _hashed_password = generate_password_hash(_password) # save edits sql = "INSERT INTO tbl_user(user_name, user_email, user_password) VALUES(%s, %s, %s)" data = (_name, _email, _hashed_password,) conn = mysql.connect() cursor = conn.cursor() cursor.execute(sql, data) conn.commit() flash('User added successfully!') return redirect('/') else: return 'Error while adding user' except Exception as e: print(e) finally: cursor.close() conn.close() @app.route('/') def users(): conn = None cursor = None try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM tbl_user") rows = cursor.fetchall() table = Results(rows) table.border = True return render_template('users.html', table=table) except Exception as e: print(e) finally: cursor.close() conn.close() @app.route('/edit/<int:id>') def edit_view(id): conn = None cursor = None try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM tbl_user WHERE user_id=%s", id) row = cursor.fetchone() if row: return render_template('edit.html', row=row) else: return 'Error loading #{id}'.format(id=id) except Exception as e: print(e) finally: cursor.close() conn.close() @app.route('/update', methods=['POST']) def update_user(): conn = None cursor = None try: _name = request.form['inputName'] _email = request.form['inputEmail'] _password = request.form['inputPassword'] _id = request.form['id'] # validate the received values if _name and _email and _password and _id and request.method == 'POST': #do not save password as a plain text _hashed_password = generate_password_hash(_password) print(_hashed_password) # save edits sql = "UPDATE tbl_user SET user_name=%s, user_email=%s, user_password=%s WHERE user_id=%s" data = (_name, _email, _hashed_password, _id,) conn = mysql.connect() cursor = conn.cursor() cursor.execute(sql, data) conn.commit() flash('User updated successfully!') return redirect('/') else: return 'Error while updating user' except Exception as e: print(e) finally: cursor.close() conn.close() @app.route('/delete/<int:id>') def delete_user(id): conn = None cursor = None try: conn = mysql.connect() cursor = conn.cursor() cursor.execute("DELETE FROM tbl_user WHERE user_id=%s", (id,)) conn.commit() flash('User deleted successfully!') return redirect('/') except Exception as e: print(e) finally: cursor.close() conn.close() if __name__ == "__main__": app.run()

5. Now create below tables.py script under user_crud directory, that shows a list of user data into tabular format on HTML template file in home page. Notice how we have added two links for updating and deleting records from table data using LinkCol from flask module.

In the below script we declare a class Results, where left side of the assignments indicate table column names and right side of the assignment indicates What we want to show as a header in the HTML table. We don’t want to show user_id and user_password and that’s why we use show=False as a second argument.

We have added two links for update and delete at the rightmost column for updating and deleting existing user.

from flask_table import Table, Col, LinkCol class Results(Table): user_id = Col('Id', show=False) user_name = Col('Name') user_email = Col('Email') user_password = Col('Password', show=False) edit = LinkCol('Edit', 'edit_view', url_kwargs=dict(id='user_id')) delete = LinkCol('Delete', 'delete_user', url_kwargs=dict(id='user_id'))

6. Now create users.html file and put it under templates directory. Notice how we are using flask EL expression to use variable to show data into HTML file.

We have added a link using which a user will be able to add new user.

We also check for any success or error message and display them. We display messages from flash scope and for this we need session and for session we need secret key and that’s why I have configured Secret Key in app.py script.

Finally we show the user data into HTML table.

<doctype html> <title>List of users - Python Flask MySQL CRUD</title> <p><a href="{{ url_for('.add_user_view') }}"> Add User </a></p> <p> {% with messages = get_flashed_messages() %} {% if messages %} <ul class=flashes> {% for message in messages %} <li>{{ message }}</li> {% endfor %} </ul> {% endif %} {% endwith %} </p> {{ table }}

7. Create below add.html file with the following code and put it under templates directory. Here the action defines end-point added in main.py script and it will be called when user clicks on Submit button on the form.

<doctype html> <title>Add User - Python Flask MySQL CRUD</title> <h2>Add User</h2> <form method="post" action="/add"> <dl> <p> <input name="inputName" value="" type="text" placeholder="Name" autocomplete="off" required> </p> <p> <input name="inputEmail" value="" type="text" placeholder="Email" autocomplete="off" required> </p> <p> <input name="inputPassword" value="" type="password" placeholder="Password" autocomplete="off" required> </p> </dl> <p> <input type="submit" value="Submit"> </p> </form>

8. Create below edit.html file with the following code and put it under templates directory. Notice how we show data from MySQL database when user wants to update the existing information. It will help them know what data already exist in the database and if user does not want to updated all information then he/she may update only the information he/she needs.

<doctype html> <title>Edit User - Python Flask MySQL CRUD</title> <h2>Edit User</h2> <form method="post" action="/update"> <dl> <p> <input name="inputName" value="{{ row['user_name'] }}" type="text" placeholder="Name" autocomplete="off" required> </p> <p> <input name="inputEmail" value="{{ row['user_email'] }}" type="text" placeholder="Email" autocomplete="off" required> </p> <p> <input name="inputPassword" value="" type="password" placeholder="Password" autocomplete="off" required> </p> </dl> <p> <input name="id" value="{{ row['user_id'] }}" type="hidden"> <input type="submit" value="Submit"> </p> </form>

We don’t need to create any template view for deleting user information because we delete using the end-point operation and redirect to the home page.

Testing the Application

Now navigate to the user_crud directory and execute the command python main.py as shown in the below image, your server will start on default port 5000. If you want to change the port then you can change the line app.run() to app.run(port=50001), where 5001 is the new port.

Now when you open the URL http://localhost:5000 in the browser, you should see similar to the below image:

Now you can add a new user with below information

You will be redirected to the home page with below information:

Now if you update the above user with below information by clicking on Edit link:

You will be redirected to the home page with below information:

Now if you delete the above user by clicking on Delete link you will left with no user in the home page.

Here is the MySQL database table – tbl_user with the following structure and inserted data into it:

CREATE TABLE `tbl_user` ( `user_id` bigint NOT NULL AUTO_INCREMENT, `user_name` varchar(45) DEFAULT NULL, `user_email` varchar(45) DEFAULT NULL, `user_password` varchar(255) DEFAULT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*Data for the table `tbl_user` */ insert into `tbl_user`(`user_id`,`user_name`,`user_email`,`user_password`) values (1,'Soumitra Roy Sarkar','[email protected]','pbkdf2:sha256:50000$obX7AAZv$61ba4f743eff5113433a3fd249896deed4120e9a83deaf166477ca5fb74fcd49');

That’s all. Hope you understood the Python Web Application CRUD Example using Flask and MySQL.

Source Code

download source code

Thanks for reading.