Python Web Application CRUD Example using Flask and MySQL

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 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, Flask 1.1.1 – 1.1.2, Flask Table 0.5.0, MySQL 8.0.17 – 8.0.22, 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, 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 versions Babel-2.9.0, Flask-Babel-2.0.0, flask-table-0.5.0.

python crud web application example using flask and mysql

MySQL Module Installation

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

python crud web application example using flask and mysql

Let’s move on to the example…

CRUD 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.

python web application crud example using flask and 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 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 below 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.

python web application crud example using flask and mysql

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

python web application crud example using flask and mysql

Now you can add a new user with below information:

python web application crud example using flask and mysql

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

python web application crud example using flask and mysql

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

python web application crud example using flask and mysql

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

python web application crud example using flask and mysql

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

26 thoughts on “Python Web Application CRUD Example using Flask and MySQL

  1. I am getting below error: Any help?

    TypeError: The view function for ‘users’ did not return a valid response. The function either returned None or ended without a return statement.
    127.0.0.1 – – [16/Oct/2021 21:58:40] “GET / HTTP/1.1” 500 –

  2. Hi Roy,

    I have followed the above instructions for the Flask/MySQL CRUD app but I don’t seem to have any tables created in the MySQL database. I can’t see anywhere you have included the SQL statement to create the tables. Can you help please?

    Thanks

    Sarah

      1. Sorry Soumitra, I spotted the SQL script after I’d sent the question! (It must have been hiding!) ;)

        Thanks and regards

        Sarah.

  3. ya its working pretty fine. as i have done create and read operation by myself but for updating and deleting i followed your tutorial by leaving mine one but i am got that error.

  4. i have followed the tutorial given by you and in my mysql db i have created database and table also and it’s working fine there now i am not getting the meaning of “your MySQL connection has not been established”

  5. File “c:/Users/rkumarsh/Documents/flask_mysql/main.py”, line 56, in users
    cursor.close()
    AttributeError: ‘NoneType’ object has no attribute ‘close’

    i am getting error in this line what will be the solution.

  6. Hi
    Thank you for your very good example of accessing MySQL tables using Python and Flask.
    I am getting the following error
    Internal Server Error
    The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.
    —-
    * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
    [2020-01-09 09:55:47,911] ERROR in app: Exception on / [GET]
    Traceback (most recent call last):

    File “C:\Users\P.V.SUBRAMANIAN\Anaconda3\lib\site-packages\flask\app.py”, line 2446, in wsgi_app
    response = self.full_dispatch_request()
    File “C:\Users\P.V.SUBRAMANIAN\Anaconda3\lib\site-packages\flask\app.py”, line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
    File “C:\Users\P.V.SUBRAMANIAN\Anaconda3\lib\site-packages\flask\app.py”, line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
    File “C:\Users\P.V.SUBRAMANIAN\Anaconda3\lib\site-packages\flask\_compat.py”, line 39, in reraise
    raise value
    File “C:\Users\P.V.SUBRAMANIAN\Anaconda3\lib\site-packages\flask\app.py”, line 1949, in full_dispatch_request
    rv = self.dispatch_request()
    File “C:\Users\P.V.SUBRAMANIAN\Anaconda3\lib\site-packages\flask\app.py”, line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
    File “E:/Flaskapp/main.py”, line 87, in users
    cursor.close()
    AttributeError: ‘NoneType’ object has no attribute ‘close’
    127.0.0.1 – – [09/Jan/2020 09:55:47] “GET / HTTP/1.1” 500 –
    ‘MYSQL_DATABASE_SOCKET’

  7. thank you for the tutorial
    when running the app under the i am encountering the following error
    UnboundLocalError: local variable ‘cursor’ referenced before assignment
    How can i correct my error?

  8. Hi Soumitra,
    Thanks for your example. It is really great. I followed through all the steps. When I ran it, I got the following message at my browser:

    “Internal Server Error
    The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.”

    Is there anything that I should open/activate so that the server can be found? Please let me know what you think. Thank you so much.

      1. [root@osboxes python_flask_user_crud]# python ./main.py
        * Serving Flask app “app” (lazy loading)
        * Environment: production
        WARNING: This is a development server. Do not use it in a production deployment.
        Use a production WSGI server instead.
        * Debug mode: off
        * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
        (2003, “Can’t connect to MySQL server on ‘localhost’ ([Errno 111] Connection refused)”)
        [2019-09-08 15:43:38,997] ERROR in app: Exception on / [GET]
        Traceback (most recent call last):
        File “/usr/lib64/python3.6/site-packages/flask/app.py”, line 2446, in wsgi_app
        response = self.full_dispatch_request()
        File “/usr/lib64/python3.6/site-packages/flask/app.py”, line 1951, in full_dispatch_request
        rv = self.handle_user_exception(e)
        File “/usr/lib64/python3.6/site-packages/flask/app.py”, line 1820, in handle_user_exception
        reraise(exc_type, exc_value, tb)
        File “/usr/lib64/python3.6/site-packages/flask/_compat.py”, line 39, in reraise
        raise value
        File “/usr/lib64/python3.6/site-packages/flask/app.py”, line 1949, in full_dispatch_request
        rv = self.dispatch_request()
        File “/usr/lib64/python3.6/site-packages/flask/app.py”, line 1935, in dispatch_request
        return self.view_functions[rule.endpoint](**req.view_args)
        File “./main.py”, line 52, in users
        cursor.close()
        UnboundLocalError: local variable ‘cursor’ referenced before assignment
        127.0.0.1 – – [08/Sep/2019 15:43:39] “GET / HTTP/1.1” 500 –

  9. Hi Soumitra,

    I am getting the below error while I am trying to run main.py. Kindly help

    from tables import Results
    ModuleNotFoundError: No module named ‘tables’

    Process finished with exit code 1

  10. Hello Sumitra
    when I try to edit or delete the item , the following error is made
    TypeError: edit_view() missing 1 required positional argument: ‘id’

    web browser shows> Internal Server Error
    The same for delete
    How can it be fixed?
    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *