Python REST API CRUD Example using Flask and MySQL

Introduction

Here you will see Python REST API CRUD Example using Flask and MySQL. You might have also seen how to create web application CRUD example using Python, Flak and MySQL.

You may also like to read REST APIs in Java and Spring Technologies.

What is REST or RESTful?

The most important concept in REST is resources, which are identified by global IDs — typically using URIs. Client applications use HTTP methods (GET/ POST/ PUT/ DELETE) to manipulate the resource or collection of resources. A RESTful Web service is implemented using HTTP and the principles of REST. Typically, a RESTful Web service should define the following aspects:

  • The base/root URI for the Web service such as http://<host>/<appcontext/contextpath>/<url pattern>/<resources>.
  • The MIME type of the response data supported, which are JSON/XML/TEXT/HTML etc.
  • The set of operations supported by the service. (for example, POST, GET, PUT or DELETE).

REST or RESTful Methods

HTTP methods are mapped to CRUD (create, read, update and delete) actions for a resource. Although you can make slight modifications such as making the PUT method to be create or update, the basic patterns are listed as follows.

  • GET: Get/List/Retrieve an individual resource or a collection of resources.
  • POST: Create a new resource or resources.
  • PUT: Update an existing resource or collection of resources.
  • DELETE: Delete a resource or collection of resources.

Prerequisites

Python 3.6.6 – 3.9.1, Flask 1.1.1 – 1.1.2, MySQL 8.0.17 – 8.0.22

Unlike the previous tutorial on Python web application CRUD example using Flask and MySQL, you do not have front-end or User Interface (UI) here. I am building REST or RESTful web services which could be consumed by any consumer. These services are decoupled from consumer. You can use any UI technologies, such as, ReactJS, AngularJS or even jQuery, HTML to showcase your data for your users.

Preparing 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 REST API CRUD Example using Flask and MySQL, you need modules, such as, flask and mysql. 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.

Python Flask REST CRUD

The project folder’s name is python-flask-rest-api-mysql-crud.

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

Step 1. Create the below app.py script (py is the extension to indicate Python script) where you need to import the flask module. This file should be created under the project’s directory. Notice how I create flask instance.

from flask import Flask

app = Flask(__name__)

Step 2. I create the below db.py Python script under the project directory to setup the MySQL database configurations for connecting to database. You 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)

Step 3. Next you need rest.py script under the project directory. This script is the perfect instance of Python REST API CRUD Example using Flask and MySQL. It defines all REST URIs for performing CRUD operations. It will also connect to MySQL database server and query the database to read, insert, update and delete.

Here you can use http PUT method and http DELETE method for updating and deleting users respectively. I have defined only 404 method to handle not found error. You should basically handle required errors, such as, server errors for http responses 500, occurred during the REST API calls.

Notice also I 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 json format using flask’s jsonify API.

import pymysql
from app import app
from db import mysql
from flask import jsonify
from flask import flash, request
#from werkzeug import generate_password_hash, check_password_hash
from werkzeug.security import generate_password_hash, check_password_hash
		
@app.route('/add', methods=['POST'])
def add_user():
	conn = None
	cursor = None
	try:
		_json = request.json
		_name = _json['name']
		_email = _json['email']
		_password = _json['pwd']
		# 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()
			resp = jsonify('User added successfully!')
			resp.status_code = 200
			return resp
		else:
			return not_found()
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()
		
@app.route('/users')
def users():
	conn = None
	cursor = None
	try:
		conn = mysql.connect()
		cursor = conn.cursor(pymysql.cursors.DictCursor)
		cursor.execute("SELECT user_id id, user_name name, user_email email, user_password pwd FROM tbl_user")
		rows = cursor.fetchall()
		resp = jsonify(rows)
		resp.status_code = 200
		return resp
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()
		
@app.route('/user/<int:id>')
def user(id):
	conn = None
	cursor = None
	try:
		conn = mysql.connect()
		cursor = conn.cursor(pymysql.cursors.DictCursor)
		cursor.execute("SELECT user_id id, user_name name, user_email email, user_password pwd FROM tbl_user WHERE user_id=%s", id)
		row = cursor.fetchone()
		resp = jsonify(row)
		resp.status_code = 200
		return resp
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()

@app.route('/update', methods=['PUT'])
def update_user():
	conn = None
	cursor = None
	try:
		_json = request.json
		_id = _json['id']
		_name = _json['name']
		_email = _json['email']
		_password = _json['pwd']		
		# validate the received values
		if _name and _email and _password and _id and request.method == 'PUT':
			#do not save password as a plain text
			_hashed_password = generate_password_hash(_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()
			resp = jsonify('User updated successfully!')
			resp.status_code = 200
			return resp
		else:
			return not_found()
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()
		
@app.route('/delete/<int:id>', methods=['DELETE'])
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()
		resp = jsonify('User deleted successfully!')
		resp.status_code = 200
		return resp
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()
		
@app.errorhandler(404)
def not_found(error=None):
    message = {
        'status': 404,
        'message': 'Not Found: ' + request.url,
    }
    resp = jsonify(message)
    resp.status_code = 404

    return resp
		
if __name__ == "__main__":
    app.run()

Step 4. Create MySQL database table – tbl_user with the following structure.

CREATE TABLE `tbl_user` (
  `user_id` bigint COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_email` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Enough coding… let’s move on to testing…

Related Posts:

Testing the Application

Now navigate to the project directory and execute the command python main.py or main.py if your Python is configured in the environment variable, 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.

You can use Postman, REST Client etc. to test your REST or RESTful APIs. Here I used Postman to test the above REST APIs.

Display All Users

GET : http://localhost:5000/users

Response:

[
    {
        "user_email": "contact@roytuts.com",
        "user_id": 1,
        "user_name": "Soumitra Roy",
        "user_password": "pbkdf2:sha256:50000$zQLJxLem$40a50a43592f9643a335ef53dc1cdfe312292c428dd4f20c36f8627ef40ee43b"
    }
]

Assuming I already had one user in the MySQL table.

Add New User

POST  : http://localhost:5000/add

Request Body:

{
	"name":"Soumitra",
	"email":"contact@roytuts.com",
	"pwd":"pwd"
}

Response:

“User added successfully!”

Display All Users

GET : http://localhost:5000/users

Response:

[
    {
        "user_email": "contact@roytuts.com",
        "user_id": 1,
        "user_name": "Soumitra Roy",
        "user_password": "pbkdf2:sha256:50000$zQLJxLem$40a50a43592f9643a335ef53dc1cdfe312292c428dd4f20c36f8627ef40ee43b"
    },
    {
        "user_email": "contact@roytuts.com",
        "user_id": 2,
        "user_name": "Soumitra",
        "user_password": "pbkdf2:sha256:50000$JAYj9hea$ce6e0589539eeebba812bf2a7e732afcbe2a3a298248f7f94878804b40709743"
    }
]

Update User

POST : http://localhost:5000/update

Request Body:

{
	"id":2,
	"name":"Soumitra Roy",
	"email":"contact@roytuts.com",
	"pwd":"pwd"
}

Response:

“User updated successfully!”

Display All Users

GET : http://localhost:5000/users

Response:

[
    {
        "user_email": "contact@roytuts.com",
        "user_id": 1,
        "user_name": "Soumitra Roy",
        "user_password": "pbkdf2:sha256:50000$zQLJxLem$40a50a43592f9643a335ef53dc1cdfe312292c428dd4f20c36f8627ef40ee43b"
    },
    {
        "user_email": "contact@roytuts.com",
        "user_id": 2,
        "user_name": "Soumitra Roy",
        "user_password": "pbkdf2:sha256:50000$16NmgrDj$910f8a743ca9e1df81718bb25b1cfadcd3d3e31050234b86ea622fa47818fea7"
    }
]

Display Single User

GET : http://localhost:5000/user/2

Response:

{
    "user_email": "contact@roytuts.com",
    "user_id": 2,
    "user_name": "Soumitra Roy",
    "user_password": "pbkdf2:sha256:50000$16NmgrDj$910f8a743ca9e1df81718bb25b1cfadcd3d3e31050234b86ea622fa47818fea7"
}

Delete a User

GET : http://localhost:5000/delete/2

Response:

“User deleted successfully!”

Display All Users

GET : http://localhost:5000/users

[
    {
        "user_email": "contact@roytuts.com",
        "user_id": 1,
        "user_name": "Soumitra Roy",
        "user_password": "pbkdf2:sha256:50000$zQLJxLem$40a50a43592f9643a335ef53dc1cdfe312292c428dd4f20c36f8627ef40ee43b"
    }
]

That’s all about building REST API CRUD application using Python Flask and MySQL.

Source Code

Download

37 thoughts on “Python REST API CRUD Example using Flask and MySQL

  1. I am unable to test add & update. In postman I do the following.

    172.26.127.4:5001/update
    When I select “body” It shows following options.

    none
    form data
    x-www-frm-urlencoded
    raw
    binary
    GraphQl

    Which option should I choose?
    I tries form data, x-www-form-urlencoded and raw but always I am getting following error
    ===
    AttributeError
    AttributeError: ‘NoneType’ object has no attribute ‘close’
    ===
    On server side
    ===
    File “/home/common/flask-master/python-flask-rest-api-mysql-crud/rest.py”, line 105, in update_user
    cursor.close()
    ====

    Similarly my /add option testing also fails.

  2. In rest.py, please refer the following line
    @app.route(‘/update’, methods=[‘PUT’])

    but in update testing method is given “POST”

    When I try to test “update” using postman it gives following error.
    ===
    AttributeError
    AttributeError: ‘NoneType’ object has no attribute ‘close’

    Traceback (most recent call last)
    File “/home/common/flask-master/python-flask-rest-api-mysql-crud/venv/lib/python3.8/site-packages/flask/app.py”, line 2091, in __call__

  3. when i update with that id which donot exist still getting sucess message. How can we check whether id exist or not then perform that query

  4. Thanks for this tutorial, everything works perfectly ! Just made my first api with python, it’s really cool :)

  5. I am getting the same error like Santiago even after putting those 2 lines above the try block. Please help.

  6. When i try to send get/post request in postman i receive this message below. Can anyone help me out with this?

    500 Internal Server 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.

  7. @app.route(‘/user/’)
    def user(id):
    try:
    conn = mysql.connect()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute(“SELECT * FROM rfid_log WHERE ser_no=%s”, id)
    row = cursor.fetchone()
    resp = jsonify(row)
    resp.status_code = 200
    return resp
    except Exception as e:
    print(e)
    finally:
    cursor.close()
    conn.close()

    this is the code when i run it with another database i have it gives me 404 error but when i pass the argument as http://localhost/users the code works fine but when i try to run the above function it shows me 404 is ther any problem with fetching one single row?

  8. It is working guys, you should specify “Content-Type: application/json” header in your POST request.

      1. its not on python3 and latest version of flask.
        there is need to change router to:

        @app.route(‘/test/’)

  9. my app can’t run, the Traceback message is:
    Traceback (most recent call last):
    File “main.py”, line 3, in
    from db_config import mysql
    ModuleNotFoundError: No module named ‘db_config’.

    i installed flask, flask-mysql, falsk-tables modules and used a virtual environment

  10. Hi Soumitra, Your article is very good except the point that none of the method for add, delete and updates are working and giving the error as mentioned by Santiago. It would be helpful if you share the updated version of the code.

    1. Dear Rahul,
      All methods are working,just you have to modify your request.
      Change parameter type text to json in boby->raw.

    1. Replace json code :

      _json = request.json
      _name = _json[‘name’]
      _email = _json[’email’]
      _password = _json[‘pwd’]

      With request args:

      _name = request.args.get(‘name’)
      _email = request.args.get(’email’)
      _password =request.args.get(‘pwd’)

    2. @app.route(‘/delete/’) here is the error pls change it
      def delete_user(id):
      try:
      conn = mysql.connect()
      cursor = conn.cursor()
      cursor.execute(“DELETE FROM tbl_user WHERE user_id=%s”, (id,))
      conn.commit()
      resp = jsonify(‘User deleted successfully!’)
      resp.status_code = 200
      return resp
      except Exception as e:
      print(e)
      finally:
      cursor.close()
      conn.close()

Leave a Reply

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