Create RESTful API using Python and MySQL

REST stands for Representational State Transfer. RESTful Web services are one way of providing interoperability between computer systems on the Internet. Rest API help to communicate between the client app and the server application.REST is an architecture style for designing networked applications. A REST API defines a bunch of functions in which developers can perform requests and receive responses via HTTP protocol such as GET and POST.

In this tutorial, you will learn how to create CRUD (create, read, update, delete) operation REST API using Python and MySQL. This is a very simple example, you can copy-paste, and change it according to your requirement.

Also Read : Build Simple REST API with Slim Framework

Before started to implement the REST API using Python and MySQL, look files structure:

restful-api-using-python-mysql
- app.py
- config.py
- main.py

Understanding REST API

REST provides a block of HTTP methods which are used to alter the data. The following are common HTTP methods:

GET — is used for reading and retrieving data.
POST — is used for inserting data.
PUT/PATCH — is used for updating data.
DELETE — is used for deleting data.

Step 1: Create MySQL Database and Table

For this tutorial, you need a MySQL database with the following table:

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

    ALTER TABLE `employee`
        ADD PRIMARY KEY (`id`);

    ALTER TABLE `employee`
        MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
        COMMIT;

CREATE TABLE `employee` (

`id` int(11) NOT NULL,

`name` varchar(255) NOT NULL,

`email` varchar(255) NOT NULL,

`phone` varchar(16) DEFAULT NULL,

`address` text DEFAULT NULL,

`salary` float DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `employee`

ADD PRIMARY KEY (`id`);

ALTER TABLE `employee`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;

Step 2: Import Flask Modules

We handle REST API functionality using Flask and MySQL, so we will need both the modules. The module Flask works as a web framework while the MySQL module requires making a connection with the MySQL database. We will create a project directory restful-api-using-python-mysql and move inside run cd command. We will install flask module by running below command.

	pip install Flask

1	pip install Flask

Step 3: Run flask-cors

Then we will install the flask-cors extension for handling Cross-Origin Resource Sharing (CORS)

    pip install -U flask-cors

1	pip install -U flask-cors

Step 4: Create file

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

from flask import Flask
from flask_cors import CORS, cross_origin

app = Flask(__name__)
CORS(app)

from flask import Flask

from flask_cors import CORS, cross_origin

app = Flask(__name__)

CORS(app)

Step 5: Create a MySQL Connection

We will install Flask-MySQL extension using below command.

    pip install flask-mysql

1	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.

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'] = 'restapi_DB'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

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'] = 'restapi_DB'

app.config['MYSQL_DATABASE_HOST'] = 'localhost'

mysql.init_app(app)

Step 6: finally create REST API CRUD Operation

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

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

@app.route('/employee/create', methods=['POST'])
def create_employee():
    try:        
        _json = request.json
        _name = _json['name']
        _email = _json['email']
        _phone = _json['phone']
        _address = _json['address']	
        _salary = _json['salary']	

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

@app.route('/employee/<int:employee_id>')
def employee_details(employee_id):
    try:
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute("SELECT id, name, email, phone, address, salary FROM employee WHERE id =%s", employee_id)
        empRow = cursor.fetchone()
        respone = jsonify(empRow)
        respone.status_code = 200
        return respone
    except Exception as err:
        print(err)
    finally:
        cursor.close() 
        conn.close() 

@app.route('/employee/update', methods=['PUT'])
def update_employee():
    try:
        _json = request.json
        _id = _json['id']
        _name = _json['name']
        _email = _json['email']
        _phone = _json['phone']
        _address = _json['address']
        _salary = _json['salary']
        if _name and _email and _phone and _address and _id and request.method == 'PUT':			
            sqlQuery = "UPDATE employee SET name=%s, email=%s, phone=%s, address=%s, salary=%s WHERE id=%s"
            bindData = (_name, _email, _phone, _address, _salary, _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 err:
        print(err)
    finally:
        cursor.close() 
        conn.close() 

@app.route('/employee/delete/<int:employee_id>', methods=['DELETE'])
def delete_employee(employee_id):
    try:
        conn = mysql.connect()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM employee WHERE id =%s", (employee_id,))
        conn.commit()
        respone = jsonify('Employee deleted successfully!')
        respone.status_code = 200
        return respone
    except Exception as err:
        print(err)
    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()

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

import pymysql

from app import app

from config import mysql

from flask import jsonify

from flask import flash, request

@app.route('/employee/create', methods=['POST'])

def create_employee():

try:

_json = request.json

_name = _json['name']

_email = _json['email']

_phone = _json['phone']

_address = _json['address']

_salary = _json['salary']

if _name and _email and _phone and _address and _salary and request.method == 'POST':

conn = mysql.connect()

cursor = conn.cursor(pymysql.cursors.DictCursor)

sqlQuery = "INSERT INTO employee(name, email, phone, address, salary) VALUES(%s, %s, %s, %s, %s)"

bindData = (_name, _email, _phone, _address, _salary)

cursor.execute(sqlQuery, bindData)

conn.commit()

cursor.close()

respone = jsonify('Employee created successfully!')

respone.status_code = 200

return respone

else:

return showMessage()

except Exception as err:

print(err)

finally:

conn.close()

@app.route('/employee')

def employee():

try:

conn = mysql.connect()

cursor = conn.cursor(pymysql.cursors.DictCursor)

cursor.execute("SELECT id, name, email, phone, address, salary FROM employee")

empRows = cursor.fetchall()

respone = jsonify(empRows)

respone.status_code = 200

return respone

except Exception as err:

print(err)

finally:

cursor.close()

conn.close()

@app.route('/employee/<int:employee_id>')

def employee_details(employee_id):

try:

conn = mysql.connect()

cursor = conn.cursor(pymysql.cursors.DictCursor)

cursor.execute("SELECT id, name, email, phone, address, salary FROM employee WHERE id =%s", employee_id)

empRow = cursor.fetchone()

respone = jsonify(empRow)

respone.status_code = 200

return respone

except Exception as err:

print(err)

finally:

cursor.close()

conn.close()

@app.route('/employee/update', methods=['PUT'])

def update_employee():

try:

_json = request.json

_id = _json['id']

_name = _json['name']

_email = _json['email']

_phone = _json['phone']

_address = _json['address']

_salary = _json['salary']

if _name and _email and _phone and _address and _id and request.method == 'PUT':

sqlQuery = "UPDATE employee SET name=%s, email=%s, phone=%s, address=%s, salary=%s WHERE id=%s"

bindData = (_name, _email, _phone, _address, _salary, _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 err:

print(err)

finally:

cursor.close()

conn.close()

@app.route('/employee/delete/<int:employee_id>', methods=['DELETE'])

def delete_employee(employee_id):

try:

conn = mysql.connect()

cursor = conn.cursor()

cursor.execute("DELETE FROM employee WHERE id =%s", (employee_id,))

conn.commit()

respone = jsonify('Employee deleted successfully!')

respone.status_code = 200

return respone

except Exception as err:

print(err)

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

Step 7: Run Application

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

We will run the below URL and create new employee record with the POST HTTP method.

    http://127.0.0.1:5000/employee/create

1	http://127.0.0.1:5000/employee/create