Flask Web Application

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 I will create new record, read existing records, update the existing record and delete the existing record.

I will use MySQL database as a permanent storage for performing such basic CRUD operations. I 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 – 3.9.1/3.11.5, Flask 1.1.1 – 1.1.2/2.3.x, MySQL 8.0.17 – 8.0.22/8.1.0

Related Posts:

• REST API CRUD Example in PHP and MongoDB
• AngularJS Codeigniter REST CRUD Example
• AngularJS PHP REST CRUD Example
• REST API CRUD Example in PHP, MySQL
• Python web application CRUD example using Flask and MySQL

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, you 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

The image shows old version of module installation and this app was also tested recently with the latest version of flask-table.

MySQL Module Installation

The image shows old version of module installation and this app was also tested recently with the latest version of flask-mysql.

Let’s move on to the example…

CRUD Example

In the below image you see I have opened a cmd (command line tool) 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, is python-flask-mysql-crud directory. Inside this python-flask-mysql-crud directory create templates directory. This templates directory will contain all html template files, such as you need several HTML files for creating CRUD operations.

2. Create the following app.py script(py is the extension to indicate Python script) where I import the flask module. This file should be created under python-flask-mysql-crud directory. Notice how I create flask instance. I 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. I create the below db_config.py Python script under python-flask-mysql-crud to setup the MySQL database configurations for connecting to database. I need to configure database connection with flask module and that’s why I 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 you need main.py script under python-flask-mysql-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 I perform CRUD operations with user interaction, I display all records, I add new record, I update existing record and I delete record from the tabular data as I wish.

I first import required modules into the script. I then define the end-point /new_user for displaying a view where user will add new record. Next I need to validate user input data and save those input data into MySQL database, so I define another end-point /add. I 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. I 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. I do not want to store password as a plain text so I am masking the password using generate_password_hash() function.

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

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

Next I 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 I add, update or delete user then I 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 python-flask-mysql-crud directory, that shows a list of user data into tabular format on HTML template file in home page. Notice how I have added two links for updating and deleting records from table data using LinkCol from flask module.

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

I 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 I are using flask EL expression to use variable to show data into HTML file.

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

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

Finally I 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 I 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>

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

Testing the Application

Now navigate to the python-flask-mysql-crud directory and execute the command python main.py or simply 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','contact@roytuts.com','pbkdf2:sha256:50000$obX7AAZv$61ba4f743eff5113433a3fd249896deed4120e9a83deaf166477ca5fb74fcd49');

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

Source Code

Download