Flask REST API CRUD Example

FLAK REST CRUD

Here you will see Flask REST API CRUD Example. You might have also seen how to create web application CRUD example using Python, Flak and MySQL. REST is an acronym that represents Representational State Transfer.

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/3.11.5, Flask 1.1.1 – 1.1.2/2.3.3, Flask-Cors 4.0.0, Flask-MySQL 1.5.2, MySQL 8.0.17 – 8.0.22/8.1.0

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.

MySQL Database

The table tbl_user is created under roytuts database in the MySQL server:

CREATE DATABASE IF NOT EXISTS `roytuts`;
USE `roytuts`;

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

INSERT INTO `tbl_user` (`user_id`, `user_name`, `user_email`, `user_password`) VALUES
	(1, 'Soumitra Roy', 'contact@roytuts.com', 'pbkdf2:sha256:600000$903OlqZ3mj0aTdSG$4bc69b01c4b09750a3b7bab2ba647f8161790fbe5cc54a6dc46ab6a5459ee474');

App Config

Application config is required to work with Flask web application. You need to create an instance of the Flask API.

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__)

Database Config

Database configuration is required to connect to the database and working with database.

I have created 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)

Building REST API

Finally you need to publish REST endpoints for the clients. So, you need rest.py script under the project directory. It defines all REST URIs or endpoints 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()

MySQL Table

Create MySQL database table – tbl_user with the following structure. This table will store the related information for the users.

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 Flask REST API 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

3 thoughts on “Flask REST API CRUD Example

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

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

    @app.route(‘/test/’)

Leave a Reply

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