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:
So let’s create RESTful API using Python and MySQL. We have project directory restful-api-python and the major files are:
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:
You can download full the script from the Download link below.
