Introduction

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 and Source Code

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.

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/<int:id>') 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/<int:id>') 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…

Related Posts:

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.

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

Assuming I already had one user in the MySQL table.

Add new user

POST http://localhost:5000/add

Request Body:

Response:

“User added successfully!”

Display all users

GET http://localhost:5000/users

Update user

POST http://localhost:5000/update

Request Body:

Response:

“User updated successfully!”

Display all users

GET http://localhost:5000/users

Display single user

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

Delete a user

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

Response:

“User deleted successfully!”

Display all users

GET http://localhost:5000/users

Source Code

download source code

Thanks for reading.