Here we will see Python REST API CRUD Example using Flask and MySQL. We 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

Have Python installed in Windows (or Unix)
Pyhton version and Packages
Here I am using Python 3.6.6 version

Unlike our previous tutorial on Python web application CRUD example using Flask and MySQL, we do not have front-end or User Interface (UI) here. We are 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 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 REST API CRUD Example using Flask and MySQL, we 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.

Example Implementation

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 rest api crud example using flask and mysql

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 we import the flask module. This file should be created under user_crud directory. Notice how we create flask instance.

from flask import Flask

app = Flask(__name__)

Step 2. We create the below db_config.py Python script under user_crud to setup the MySQL database configurations for connecting to database. We need to configure database connection with flask module and that’s why we 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'] = ''
app.config['MYSQL_DATABASE_DB'] = 'roytuts'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

Step 3. Next we need main.py script under user_crud 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 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 json format using flask’s jsonify API.

import pymysql
from app import app
from db_config import mysql
from flask import jsonify
from flask import flash, request
from werkzeug import generate_password_hash, check_password_hash
		
@app.route('/add', methods=['POST'])
def add_user():
	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():
	try:
		conn = mysql.connect()
		cursor = conn.cursor(pymysql.cursors.DictCursor)
		cursor.execute("SELECT * 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/')
def user(id):
	try:
		conn = mysql.connect()
		cursor = conn.cursor(pymysql.cursors.DictCursor)
		cursor.execute("SELECT * 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=['POST'])
def update_user():
	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 == 'POST':
			#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/')
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()
		
@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(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Enough coding… let’s move on to testing…

Testing the Application

Now navigate to the user_crud directory and execute the command python 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 crud example using flask and mysql

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

[
    {
        "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

[
    {
        "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

[
    {
        "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

{
    "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. Hope you got idea on Python REST API CRUD Example using Flask and MySQL.

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

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