Create RESTful API using Python & MySQL

RESTFul API is an advance concept of web development that implemented at server and used with HTTP method (GET/ POST/ PUT/ DELETE) to handle the data. The HTTP request methods and resources are identified using URIs and handled functionality accordingly.

If you’re thinking about implementing Python RESTful API, then you’re here at right place. In our previous tutorial, you have learned about implementing RESTFul API with CodeIgniter. In this tutorial, you will learn how to create Python RESTFul API using MYSQL.

We will cover this tutorial with live example with HTTP methods like (GET/ POST/ PUT/ DELETE) to implement Python RESTFul API with CRUD operations.

We hope you have installed Python in Your Windows or Linux with Python and its packages. Here we are using Python 3.10.4 version. We will use flask, flask-mysql and flask-cors module.

Also, read:

• Create RESTful API using CodeIgniter
• Creating REST API with Golang
• Create Simple REST API with PHP & MySQL

So let’s create RESTful API using Python and MySQL. We have project directory restful-api-python and the major files are:

• config.py
• app.py
• main.py

Step1: Create MySQL Database Table

As we will implement RESTful API to perform CRUD operations, so we will create MySQL database table emp to perform operations. So first we will create MySQL database rest-api and then create table emp using below table create statement.

CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(16) DEFAULT NULL,
  `address` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `emp`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `emp`
 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Step2: Import Flask Modules

As we handle handle REST API functionality using Flask and MySQL, so we will need both the modules. The module Flask works as web framework while MySQL module require to make connection with MySQL database.

So first we will create project directory restful-api-python and move inside using cd command.

Then we will install flask module by running below command.

pip install Flask

we will also install flask-cors extension for handling Cross Origin Resource Sharing (CORS), making cross-origin possible.

pip install -U flask-cors

So now we will create the app.py Python script and import the flask module and create the flask instance to use with MySQL module. We will also import code>flask-cors extension for cross-origin.

from flask import Flask
from flask_cors import CORS, cross_origin

app = Flask(__name__)
CORS(app)

Ste3: Create MySQL Connection

We need to install Flask-MySQL extension that allows you to access a MySQL database. We will install Flask-MySQL extension using below command.

pip install flask-mysql

We will create config.py Python file to initialize MySQL database connection details to make connection with MySQL database. We will import the app script to handle MySQL database connection with Flask-MySQL module.

As we have already created MySQL database rest-api, so we will connect by providing connection details.

from app import app
from flaskext.mysql import MySQL

mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'rest-api'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

Step4: Create REST API CRUD Operation

We will create main.py script and import the app and config modules. We will connect to MySQL database and implement CRUD operations by defining all REST URIs.

Here we have used POST HTTP method to create new employee records to MySQL database. We have used GET HTTP method to get all employee records or individual record and used PUT HTTP method for employee record update. Also implemented DELETE HTTP method to delete record. If there are no record found then defined 404 method to handle not found error.

import pymysql
from app import app
from config import mysql
from flask import jsonify
from flask import flash, request

@app.route('/create', methods=['POST'])
def create_emp():
    try:        
        _json = request.json
        _name = _json['name']
        _email = _json['email']
        _phone = _json['phone']
        _address = _json['address']	
        if _name and _email and _phone and _address and request.method == 'POST':
            conn = mysql.connect()
            cursor = conn.cursor(pymysql.cursors.DictCursor)		
            sqlQuery = "INSERT INTO emp(name, email, phone, address) VALUES(%s, %s, %s, %s)"
            bindData = (_name, _email, _phone, _address)            
            cursor.execute(sqlQuery, bindData)
            conn.commit()
            respone = jsonify('Employee added successfully!')
            respone.status_code = 200
            return respone
        else:
            return showMessage()
    except Exception as e:
        print(e)
    finally:
        cursor.close() 
        conn.close()          
     
@app.route('/emp')
def emp():
    try:
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute("SELECT id, name, email, phone, address FROM emp")
        empRows = cursor.fetchall()
        respone = jsonify(empRows)
        respone.status_code = 200
        return respone
    except Exception as e:
        print(e)
    finally:
        cursor.close() 
        conn.close()  

@app.route('/emp/')
def emp_details(emp_id):
    try:
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute("SELECT id, name, email, phone, address FROM emp WHERE id =%s", emp_id)
        empRow = cursor.fetchone()
        respone = jsonify(empRow)
        respone.status_code = 200
        return respone
    except Exception as e:
        print(e)
    finally:
        cursor.close() 
        conn.close() 

@app.route('/update', methods=['PUT'])
def update_emp():
    try:
        _json = request.json
        _id = _json['id']
        _name = _json['name']
        _email = _json['email']
        _phone = _json['phone']
        _address = _json['address']
        if _name and _email and _phone and _address and _id and request.method == 'PUT':			
            sqlQuery = "UPDATE emp SET name=%s, email=%s, phone=%s, address=%s WHERE id=%s"
            bindData = (_name, _email, _phone, _address, _id,)
            conn = mysql.connect()
            cursor = conn.cursor()
            cursor.execute(sqlQuery, bindData)
            conn.commit()
            respone = jsonify('Employee updated successfully!')
            respone.status_code = 200
            return respone
        else:
            return showMessage()
    except Exception as e:
        print(e)
    finally:
        cursor.close() 
        conn.close() 

@app.route('/delete/', methods=['DELETE'])
def delete_emp(id):
	try:
		conn = mysql.connect()
		cursor = conn.cursor()
		cursor.execute("DELETE FROM emp WHERE id =%s", (id,))
		conn.commit()
		respone = jsonify('Employee deleted successfully!')
		respone.status_code = 200
		return respone
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()
        
       
@app.errorhandler(404)
def showMessage(error=None):
    message = {
        'status': 404,
        'message': 'Record not found: ' + request.url,
    }
    respone = jsonify(message)
    respone.status_code = 404
    return respone
        
if __name__ == "__main__":
    app.run()

Step5: Run Application

Now we will go the project directory restful-api-python and execute the the command python main.py and the server will start on default port 5000. Now we will use Postman to run our Python RESTful API with (POST, GET, PUT or DELETE) methods to test it.

We will run the below URL with HTTP GET method to get the all employee and display data in JSON format.

http://localhost:5000/emp

The following JSON data response will be returned:

We will get the employee record in JSON data with id 1 using below URL with GET HTTP method.

http://localhost:5000/emp/1

The response will be JSON data:

We will create new employee record with POST HTTP method.

http://localhost:5000/create

The request body will be following:

The response will JSON data employee add message.

We will update existing employee record with #1 using PUT HTTP method.

http://localhost:5000/update

The request body will be following:

The response will employee record update message.

We will delete existing employee with id #3 using DELETE HTTP method.

http://localhost:5000/delete/4

The response will employee record delete message.

You have completed tutorial about Python RESTFul API using Flask and MySQL with examples. You can further implement this into your project according to your requirement. If you have any query, you can submit your precious comments. Thanks!

You may also like:

• User Management System with PHP & MySQL
• Datatables Add Edit Delete with Ajax, PHP & MySQL
• Build Helpdesk System with jQuery, PHP & MySQL
• Build Online Voting System with PHP & MySQL
• School Management System with PHP & MySQL
• DataTables Add Edit Delete with CodeIgniter
• Create RESTful API using CodeIgniter
• Build Reusable Captcha Script with PHP
• Product Search Filtering using Ajax, PHP & MySQL
• Image Upload and Crop in Modal with jQuery, PHP & MySQL
• Build Push Notification System with PHP & MySQL
• Project Management System with PHP and MySQL
• Hospital Management System with PHP & MySQL
• Build Newsletter System with PHP and MySQL
• Skeleton Screen Loading Effect with Ajax and PHP
• Build Discussion Forum with PHP and MySQL
• Customer Relationship Management (CRM) System with PHP & MySQL
• Online Exam System with PHP & MySQL
• Expense Management System with PHP & MySQL

You can download full the script from the Download link below.

Download