Making Web Application CRUD Using Flask and MySQL

In today’s blog, we will discuss how to develop the web application using Flask and MySQL and also show the CRUD operation.

What Is CRUD

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

We will use the MySQL database as permanent storage for performing such basic CRUD operations. We will use the 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, the 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 the Windows environment.

Prerequisites

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

Preparing Your Workspace

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

When you are working in the Python terminal, you need to 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.

Using MySQL Table

For this, Python Web Application CRUD example using Flask and MySQL, we need modules, such as a tableflaskMySQL. 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 a connection with MySQL database and query the database using Python programming language.

If you find that any of the required modules do not exist, then you need to install it. You can refer to the below-mentioned screenshots on how to install the below-required modules – table and MySQL.

Installation of Modules – Table and MySQL

Table Module Installation

MySQL Module Installation

Implementation of Example

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

1. Create Directories

First, create directories. The top-level directory or project root directory, which is shown in the figure above under the section “Preparing your workspace.” 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 File app.py

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 the 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. Create the Database File

We create the below db_config.py Python script under user_crud to set up the MySQL database configurations for connecting to the base. We need to configure database connection with the k module, and that’s why we have imported the module and set up the MySQL configuration with the k 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. Create a main.py

Next, we need main.py script under user_crud directory, which will define all URIs or Action paths for performing CRUD operations. It will also connect to the 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, undoubtedly, we display all records, we add a new record, we update existing record, and we delete record from the tabular data as we wish.

Below are the steps:

We first import the required modules into the script. We then define the end-point /new_user for displaying a view where the user will add a 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 the HTTP method GET for displaying view and POST method for sending data to the server-side. By default, the Http method is GET if you do not specify the 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.

Add User

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

Show All Record

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.

Edit Information

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

Delete

Next, we define delete end-point where the 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. Create a File tables.py

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

Subsequently, in the below script, we declare a class results, where the 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 an 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. Create A File users.html

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

Moreover, we have added a link using which a user will be able to add a new user.

Also, you can check for any message and display. We display messages from the flash scope, and for this, we need session and for a session, a secret key, and therefore, we have configured Secret Key in app.py script.

Finally, we show the user data into an 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 An add.html File

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 a user clicks on the 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 an edit.html File

Create below edit.html file with the following code and put it under templates directory. Notice how we show data from the MySQL database when the user wants to update the existing information. It will help them know what data already exists in the database, and if the user does not want to update 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. As we delete using the end-point operation and redirect it to the home page.

Please feel free to share your feedback and your opinion in the comments section below. To know more about our services please visit https://www.loginworks.com/.

Get in Touch

Leave a Comment