Python Fast Api With Mysql Connector : Crud Operations.

Before delving into the intricate details, it's essential to ensure you have the following prerequisites in place:

• - Python (version 3.7 or higher)
• - FastAPI (Install using: `pip install fastapi`)
• - Uvicorn (ASGI server, Install using: `pip install uvicorn`)
• - MySQL Connector (Install using: `pip install mysql-connector-python`)

Furthermore, make sure you have  access to a MySQL database server with the requisite credentials. I have taken below table for this article. 

CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `status` enum('1','0') NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Let's do practical

STEP #1: 

Create a file "Connection.py"  and past below code.

 

import mysql.connector

class DB_Connection:
    def __init__(self):
        self.connection = None

    def connect(self):
        self.connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="",
            database="fastapi"
        )

    def disconnect(self):
        if self.connection:
            self.connection.close()

db = DB_Connection()

 

STEP #2: 

Create a file "mysql_fastapi"  and past below code.

from fastapi import FastAPI, HTTPException,Body
from Connection import db

app = FastAPI()

@app.on_event("startup")
async def startup():
    print("Connected")
    db.connect()

@app.on_event("shutdown")
async def shutdown():
    print("DisConnected")
    db.disconnect()


@app.get("/getEmployeesList")
async def getEmployeesList():
    cursor = db.connection.cursor()
    cursor.execute("SELECT * from employees")
    result = cursor.fetchall()
    return {"result":result}

#by ID
@app.get("/getEmployeesList/{id}")
async def getEmployeesList():
    cursor = db.connection.cursor()
    cursor.execute("SELECT * from employees where id={id}")
    result = cursor.fetchone()
    return {"result":result}

@app.post("/SaveData", status_code=201)
async def SaveData(name:str=Body(...),email:str=Body(...),mobile:str=Body(...)):
    cursor = db.connection.cursor()
    query = "INSERT INTO employees (name,email,mobile) VALUES (%s, %s,%s)"
    values = (name,email,mobile)
    cursor.execute(query, values)
    db.connection.commit()
    return {"message": "Data Saved Successfully"}


@app.post("/updateData/{id}", status_code=201)
async def SaveData(name:str=Body(...),email:str=Body(...),mobile:str=Body(...)):
    cursor = db.connection.cursor()
    query = "UPDATE employees set name=%s,email=%s,mobile=%s) where id={id}"
    values = (name,email,mobile)
    cursor.execute(query, values)
    db.connection.commit()
    return {"message": "Data Updated Successfully"}


@app.post("/deleteEmployee/{id}", status_code=201)
async def SaveData():
    cursor = db.connection.cursor()
    query = "DELETE TABLE employees where id={id}"
    cursor.execute(query)
    db.connection.commit()
    return {"message": "Employee Deleted Successfully"}

 

Above code brings below distinct endpoints:

NOTE : http://127.0.0.1:8000/  => Change Base URL as per your configuration

1. Endpoint (`/getEmployeesList`) : Fetch All Employee List
2. Endpoint (`/getEmployeesList/{id}`) : Get Employee Details by ID
3. Endpoint (`/SaveData`) : Insert Employee Informaton into Database
4. Endpoint (`/updateData/{id}`) : Edit Employee details by ID
5. Endpoint (`/deleteEmployee/{id}`) : Edit Employee details by ID 

STEP #3: 

To set your FastAPI application in motion ( Start UviCorn Server ), execute the following command in your terminal:

uvicorn mysql_fastapi:app --reload

Your Server will run on port 8000 ( you can change it as well )

STEP #4: 

Execute EndPoints in Postman

Get All Employee List

Save Employee Data

Get  Employee Details by Id

Update Employee Data By ID

Delete Employee Data By ID

 

In Conclusion, FastAPI emerges as great Python Framework to build Resful APIs. I hope you understood the idea about Fast API and its usage with MySql.