Creating a RESTful API for CRUD (Create, Read, Update, Delete) operations is an essential component of web development.
In this article, you will learn how to create CRUD REST APIs in Python using the Flask web framework and MySQL as the database. You will get an in-depth knowledge of the steps involved in the design, implementation, and testing of these APIs.
REST (Representational State Transfer) is an architectural approach that allows diverse software systems to communicate with one another. RESTful APIs are used to transmit data between a client (front-end) and a server (back-end), and they support a wide variety of web and mobile apps.
Read More: Create HTTP Web Server in Node js Using Express js
Let’s get started.
REST API Project Setup
Create a project folder name “student-apis“. In this folder create two files,
- db.py (To store MySQL database connection)
- app.py (Main application file to handle API routes)
Installation of Python Packages
Open project terminal and run these command to install the needed python packages to your system.
We will install packages using pip (PIP is a package manager for Python)
Install “flask”
pip install flask
Install “MySQL Connector”
pip install mysql-connector-python
Now, let’s create a database.
Setup Database and Table
Open PhpMyAdmin and create a database with name “py_students_manage”.
Next, run this command to create students table inside it.
CREATE TABLE `students` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
`phone_no` varchar(25) DEFAULT NULL,
`gender` enum('male','female','other') NOT NULL,
`status` int NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
So, you have now a database and a table inside it right.
Setup CRUD REST APIs Routes with Code
Open db.py file and write this complete code into it.
import mysql.connector # MySQL configuration mysql = mysql.connector.connect( host="localhost", user="your_username", password="your_password", database="py_students_manage" ) cursor = mysql.cursor(buffered=True)
Next,
Open app.py file and write this complete code into it.
from flask import Flask, request, jsonify from db import mysql, cursor app = Flask(__name__) # Welcome API Route @app.route('/', methods=['GET']) def welcome_message(): return jsonify({ "status": 1, "message": "Welcome to Landing Page of API" }) # API Routes if __name__ == '__main__': app.run(debug=True)
List of APIs that we will create,
- Add Student API
- List All Students API
- Read a Single Student Data API
- Update Student API
- Delete Student API
Add Student API
Function: This API will take data in json and save these data into students table.
Fields: name, email, phone_no, gender, status
# Create a New Student Route
@app.route('/add-student', methods=['POST'])
def create_student():
data = request.get_json()
name = data['name']
email = data['email']
phone_no = data['phone_no']
gender = data['gender']
status = data['status']
insert_query = "INSERT INTO students (name, email, phone_no, gender, status) VALUES (%s, %s, %s, %s, %s)"
cursor.execute(insert_query, (name, email, phone_no, gender, status))
mysql.commit()
return jsonify({
"status": 1,
"message": "Student created successfully"
})
Read More: Creating CRUD APIs with Node.js and Sequelize CLI
List All Students API
Function: When you call this API, It will list all rows from students table.
# Get All Students Route
@app.route('/students', methods=['GET'])
def get_students():
cursor.execute("SELECT * FROM students")
students = cursor.fetchall()
student_list = []
for student in students:
student_data = {
"id": student[0],
"name": student[1],
"email": student[2],
"phone_no": student[3],
"gender": student[4],
"status": student[5]
}
student_list.append(student_data)
return jsonify({
"status": 1,
"students": student_list
})
Read a Single Student Data API
Function: This API will return single student data by getting student id from URL.
# Get a Specific Student Data
@app.route('/student/<int:student_id>', methods=['GET'])
def get_student(student_id):
cursor.execute("SELECT * FROM students WHERE id = %s", (student_id))
student = cursor.fetchone()
if student:
student_data = {
"id": student[0],
"name": student[1],
"email": student[2],
"phone_no": student[3],
"gender": student[4],
"status": student[5]
}
return jsonify({
"status": 1,
"student": student_data
})
else:
return jsonify({
"status": 0,
"message": "Student not found"
})
Update Student API
Function: To update existing student data, this API will be used.
# Update a Specific Student Data
@app.route('/student/<int:student_id>', methods=['PUT'])
def update_student(student_id):
data = request.get_json()
name = data['name']
phone_no = data['phone_no']
update_query = "UPDATE students SET name = %s, phone_no = %s WHERE id = %s"
cursor.execute(update_query, (name, phone_no, student_id))
mysql.commit()
return jsonify({
"status": 1,
"message": "Student updated successfully"
})
Delete Student API
Function: It will delete student from table by student id.
# Delete a Specific Student
@app.route('/student/<int:student_id>', methods=['DELETE'])
def delete_student(student_id):
cursor.execute("DELETE FROM students WHERE id = %s", (student_id))
mysql.commit()
return jsonify({
"status": 1,
"message": "Student deleted successfully"
})
Read More: Nodejs Express REST APIs with JWT Authentication Tutorial
Here, is the complete code for this CRUD REST APIs in Python, MySQL and Flask
from flask import Flask, request, jsonify from db import mysql, cursor app = Flask(__name__) # Welcome API Route @app.route('/', methods=['GET']) def welcome_message(): return jsonify({ "status": 1, "message": "Welcome to Landing Page of API" }) # Create a New Student Route @app.route('/add-student', methods=['POST']) def create_student(): data = request.get_json() name = data['name'] email = data['email'] phone_no = data['phone_no'] gender = data['gender'] status = data['status'] insert_query = "INSERT INTO students (name, email, phone_no, gender, status) VALUES (%s, %s, %s, %s, %s)" cursor.execute(insert_query, (name, email, phone_no, gender, status)) mysql.commit() return jsonify({ "status": 1, "message": "Student created successfully" }) # Get All Students Route @app.route('/students', methods=['GET']) def get_students(): cursor.execute("SELECT * FROM students") students = cursor.fetchall() student_list = [] for student in students: student_data = { "id": student[0], "name": student[1], "email": student[2], "phone_no": student[3], "gender": student[4], "status": student[5] } student_list.append(student_data) return jsonify({ "status": 1, "students": student_list }) # Get a Specific Student Data @app.route('/student/<int:student_id>', methods=['GET']) def get_student(student_id): cursor.execute("SELECT * FROM students WHERE id = %s", (student_id)) student = cursor.fetchone() if student: student_data = { "id": student[0], "name": student[1], "email": student[2], "phone_no": student[3], "gender": student[4], "status": student[5] } return jsonify({ "status": 1, "student": student_data }) else: return jsonify({ "status": 0, "message": "Student not found" }) # Update a Specific Student Data @app.route('/student/<int:student_id>', methods=['PUT']) def update_student(student_id): data = request.get_json() name = data['name'] phone_no = data['phone_no'] update_query = "UPDATE students SET name = %s, phone_no = %s WHERE id = %s" cursor.execute(update_query, (name, phone_no, student_id)) mysql.commit() return jsonify({ "status": 1, "message": "Student updated successfully" }) # Delete a Specific Student @app.route('/student/<int:student_id>', methods=['DELETE']) def delete_student(student_id): cursor.execute("DELETE FROM students WHERE id = %s", (student_id)) mysql.commit() return jsonify({ "status": 1, "message": "Student deleted successfully" }) if __name__ == '__main__': app.run(debug=True)
Everything is done now.
Application Testing
Open project terminal and run this command,
python app.py
Above command will start development server.
Add Student API
URL – http://127.0.0.1:5000/add-student
Method – POST
Header –
Accept:application/json
Content-Type: application/json
Form data –
{
"name": "Vikas Sharma",
"email": "vikas.example@gmail.com",
"phone_no": "7896325410",
"gender": "male",
"status": 1
}
Screenshot –
List Student API
URL – http://127.0.0.1:5000/students
Method – GET
Header –
Accept:application/json
Screenshot –
Single Student Details API
URL – http://127.0.0.1:5000/student/1
Method – GET
Header –
Accept:application/json
Screenshot –
Update Student API
URL – http://127.0.0.1:5000/student/2
Method – PUT
Header –
Accept:application/json
Content-Type: application/json
Form data –
{
"name": "Vikas Singh",
"phone_no": "8888888888"
}
Screenshot –
Delete Employee API
URL – http://127.0.0.1:5000/student/2
Method – DELETE
Header –
Accept:application/json
That’s it.
We hope this article helped you to learn about Create CRUD REST APIs in Python Using MySQL and Flask in a very detailed way.
Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.
If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.