Build Simple 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 helps 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 protocols 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 just copy-paste, and change according to your requirements.
Hope you have installed Python in Windows/Linux with Python and its packages. I am using Python 3.7 version. Using
Flask and MySQL module.Before started to implement the REST API using Python, look files structure:
- restful-api-using-python
- config.py
- app.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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE `student` ( `id` int(11) NOT NULL, `roll_no` varchar(10) DEFAULT 'NULL', `first_name` varchar(255) NOT NULL, `last_name` varchar(255) NOT NULL, `class` varchar(255) NOT NULL, `age` int(11) NOT NULL, `address` varchar(255) NOT NULL, `status` int(1) NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `student` (`id`, `roll_no`, `first_name`, `last_name`, `class`, `age`, `address`, `status`) VALUES (1, 'R001', 'Tiger', 'Wood', 'LKG', 3, 'USA', 1); ALTER TABLE `student` ADD PRIMARY KEY (`id`); ALTER TABLE `student` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; |
Step 2: Create file named
app.pyWe import the flask module. Here we are coding flask instance.
|
1 2 3 4 5 |
from flask import Flask from flask_cors import CORS, cross_origin app = Flask(__name__) CORS(app) |
Step 3: MySQL Connection
Create MySQL database configurations for connecting to the database
|
1 2 3 4 5 6 7 8 9 10 11 |
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'] = 'demo_DB' app.config['MYSQL_DATABASE_HOST'] = 'localhost' mysql.init_app(app) |
Step 4: Create REST API CRUD
Create a file named
main.py and and import the app and config modules- Connect to MySQL database and implement CRUD operations
create_student()— Add student Record. (usingPOSTmethods)update_student— Update student Record. (usingPUTmethods)student()— Get all student Records. (usingGETmethods)student(student_id)— Get single student record. (usingGETmethods)delete_student()— Delete Student Record. (usingDELETEmethods)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
import pymysql from app import app from db_config import mysql from flask import jsonify from flask import flash, request # create Student @app.route('/create', methods=['POST']) def create_student(): try: _json = request.json _roll_no = _json['roll_no'] _first_name = _json['first_name'] _last_name = _json['last_name'] _class = _json['class'] _age = _json['age'] _address = _json['address'] _status = _json['status'] # insert record in database sqlQuery = "INSERT INTO student(roll_no, first_name, last_name, class, age, address, status) VALUES(%s, %s, %s, %s, %s, %s, %s)" data = (_roll_no, _first_name, _last_name, _class, _age, _address, _status,) conn = mysql.connect() cursor = conn.cursor() cursor.execute(sqlQuery, data) conn.commit() res = jsonify('Student created successfully.') res.status_code = 200 return res else: return not_found() except Exception as e: print(e) finally: cursor.close() conn.close() @app.route('/student') def student(): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM student") rows = cursor.fetchall() res = jsonify(rows) res.status_code = 200 return res except Exception as e: print(e) finally: cursor.close() conn.close() @app.route('/student/<int:student_id>') def student(student_id): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM student WHERE id=%s", student_id) row = cursor.fetchone() res = jsonify(row) res.status_code = 200 return res except Exception as e: print(e) finally: cursor.close() conn.close() @app.route('/update', methods=['PUT']) def update_student(): try: _json = request.json _student_id = _json['id'] _first_name = _json['first_name'] _last_name = _json['last_name'] _class = _json['class'] _age = _json['age'] _address = _json['address'] # update record in database sql = "UPDATE student SET first_name=%s, last_name=%s, class=%s, age=%s, address=%s WHERE id=%s" data = (_first_name, _last_name, _class, _age, _address, _student_id,) conn = mysql.connect() cursor = conn.cursor() cursor.execute(sql, data) conn.commit() res = jsonify('Student updated successfully.') res.status_code = 200 return res else: return not_found() except Exception as e: print(e) finally: cursor.close() conn.close() # delete student record from database @app.route('/delete/<int:student_id>, methods=['DELETE']') def delete_student(student_id): try: conn = mysql.connect() cursor = conn.cursor() cursor.execute("DELETE FROM student WHERE id=%s", (student_id,)) conn.commit() res = jsonify('Student deleted successfully.') res.status_code = 200 return res except Exception as e: print(e) finally: cursor.close() conn.close() @app.errorhandler(404) def not_found(error=None): message = { 'status': 404, 'message': 'There is no record: ' + request.url, } res = jsonify(message) res.status_code = 404 return res if __name__ == "__main__": app.run() |
Step 5: Run Application
Now we will go the project directory
restful-api-using-python and execute the command python main.py and the server will start on default port 5000. Now we will use Postman to run our Python RESTful APIs.
The output will be like below:
Create student record (using
POST method)|
1 |
http://localhost:5000/create |
Request Body:
|
1 2 3 4 5 6 7 8 9 10 |
{ "roll_no":"R001" "first_name":"Tiger", "last_name":"Wood", "class":"LKG", "age":"3", "address":"USA", "status":"1" } |
JSON Response: “Student created successfully.”;
Update student record (using
PUT method)|
1 |
http://localhost:5000/update |
Request Body:
|
1 2 3 4 5 6 7 8 9 |
{ "id":1 "first_name":"Tiger", "last_name":"Wood", "class":"LKG", "age":"3", "address":"USA", } |
JSON Response: “Student updated successfully.”;
Display all student (using
GET method)|
1 |
http://localhost:5000/student |
Request Body:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[ { "id": 1, "roll_no":"R001" "first_name":"Tiger", "last_name":"Wood", "class":"LKG", "age":"3", "address":"USA", "status":"1" }, { "id": 2, "roll_no":"R002" "first_name":"Will", "last_name":"Smith", "class":"UKG", "age":"4", "address":"London", "status":"1" } ] |
Display single student record (using
GET method)|
1 |
http://localhost:5000/student/1 |
Request Body:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[ { "id": 1, "roll_no":"R001" "first_name":"Tiger", "last_name":"Wood", "class":"LKG", "age":"3", "address":"USA", "status":"1" } ] |
Delete student record (using
DELETE method)|
1 |
http://localhost:5000/delete/2 |
JSON Response: “Student deleted successfully.”;