Create CRUD REST APIs in Python Using MySQL and Flask

Create CRUD REST APIs in Python Using MySQL and Flask

by
Reading Time: 11 minutes
140 Views

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.

Sanjay KumarHello friends, I am Sanjay Kumar a Web Developer by profession. Additionally I'm also a Blogger, Youtuber by Passion. I founded Online Web Tutor and Skillshike platforms. By using these platforms I am sharing the valuable knowledge of Programming, Tips and Tricks, Programming Standards and more what I have with you all. Read more