Create REST API Using FastAPI, Python & MySQL

In our previous REST API tutorial, we have explained to create a REST APIs using Python and MySQL. In this tutorial, we will create REST APIs using FastAPI.

We will create REST API with common CRUD functions like getting data, insert new data, update and delete data.

What is REST API?

A RESTful API, or REST API is an application interface that enables applications to communicate with each other by using
HTTP requests with methods (GET, POST, PUT, DELETE) to access and use data. They are commonly used in web and mobile applications.

What is FastAPI?

FastAPI is a high-performing modern web framework for building APIs quickly with Python. The framework is develoed to be easy to use, efficient, and reliable. These features makes it a first choice for developing RESTful APIs and web applications.

So let’s proceed with creating RESTful API using FastAPI. The project structure and major file is:

rest-api-fastapi
- main.py

Also, read:

Step1: Prerequisites

To create REST APIs using FastAPI, we need following to be installed.

Python: Need to install Python3.7+
MySQL: Need to install MySQL server.
FastAPI:
We need to install FastAPI web framework to create APIs. To install FastAPI module, run this command:
```
pip install fastapi
```
Uvicorn:
FastAPI is a framework for building APIs, but need a local web server to test APIs. The Uvicorn is a lightning-fast Asynchronous Server Gateway Interface (ASGI) web server. To install Uvicorn, run this command:
```
pip install uvicorn
```
MySQL Connector Python: Need to install this module to connect to MySQL database. To install this module, run this command:
```
pip install mysql-connector-python
```

Step2: Create MySQL Database and Table

As we will create API with MySQL database, so we will create MySQL database APIs and create table employee using below query.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `password` varchar(100) NOT NULL,
  `email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `employee`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `employee`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

Step3: Implement Rest API Methods

We will create main.py file and start implementing.

First we will import modules that we have already installed.

from fastapi import FastAPI, HTTPException, status
from pydantic import BaseModel
import mysql.connector
import hashlib

We will setting up to connect to MySQL database.

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="passcode",
    database="mydatabase"
)
cursor = mydb.cursor()

We will create FastAPI framework object and also define a Pydantic model for our database table schema.

app = FastAPI()

class DBModel(BaseModel):
    name: str
    password: str
    email: str

Now we will implement end points for our API to play with data. We will define end point /employee and implement function getEmployee() to get all records from employee table with HTTP GET method.

@app.get("/employee", status_code=status.HTTP_302_FOUND)
def getEmployee():
    query = "SELECT * FROM employee"
    cursor.execute(query)
    results = cursor.fetchall()
    return results

We will define end point /employee/{employee_id} and implement function getEmployeeById() to get records by id from employee table with HTTP GET method.

@app.get("/employee/{employee_id}", status_code=status.HTTP_200_OK)
def getEmployeeById(employee_id: int):
    query = "SELECT * FROM employee WHERE id = %s"
    cursor.execute(query, (employee_id,))
    result = cursor.fetchone()
    if result:
        return result
    else:
        raise HTTPException(status_code=404, detail="Employee not found")

We will define end point /employee and implement function insert() to insert records into employee table with HTTP POST method.

@app.post("/employee", status_code=status.HTTP_201_CREATED)
def insert(employee: DBModel):    
    hashedPassword = hashlib.sha256(employee.password.encode()).hexdigest()

    insertQuery = """
    INSERT INTO employee (name, password, email)
    VALUES (%s, %s, %s)
    """
    values = (employee.name, hashedPassword, employee.email)

    try:
        cursor.execute(insertQuery, values)
        mydb.commit()
    except mysql.connector.Error as err:
        raise HTTPException(status_code=400, detail=f"Error: {err}")

    return {"message": "employee inserted successfully"}

We will define end point /employee/{employee_id} and implement function update() to update record into employee table with HTTP PUT method.

@app.put("/employee/{employee_id}", status_code=status.HTTP_200_OK)
def update(employee_id: int, employee: DBModel):    
    hashedPassword = hashlib.sha256(employee.password.encode()).hexdigest()
    updateQuery = """
    UPDATE employee
    SET name = %s, password = %s, email = %s
    WHERE id = %s
    """
    values = (employee.name, hashedPassword, employee.email, employee_id)
    cursor.execute(updateQuery, values)
    mydb.commit()
    if cursor.rowcount == 0:
        raise HTTPException(status_code=404, detail="Employee not found")
    return {"message": "Employee updated successfully"}

and we will define end point /employee/{employee_id} and implement function delete() to delete record from employee table with HTTP DELETE method.

@app.delete("/employee/{employee_id}", status_code=status.HTTP_200_OK)
def delete(employee_id: int):
    deleteQuery = "DELETE FROM employee WHERE id = %s"
    cursor.execute(deleteQuery, (employee_id,))
    mydb.commit()
    if cursor.rowcount == 0:
        raise HTTPException(status_code=404, detail="User not found")
    return {"message": "Employee deleted successfully"}

Step4: Running the FastAPI Application

Execute below command to run the application.

uvicorn main:app --reload

The REST API end points will be availble and can accessed : http://localhost:8000/employee

Step5: Tesing API with POSTMAN

We will test our REST API using POSTMAN application.

1. Get All Employee Record:

Method: GET
URL: http://localhost:8000/employee

2. Get Employee Record By Id:

Method: GET
URL: http://localhost:8000/employee{employee_id}

3. Insert a New Employee Record:

Method: POST
URL: http://localhost:8000/employee

Body: JSON

{
  "name": "test",
  "password": "testpass",
  "email": "test@webdamn.com"
}

4. Update Employee Record:

Method: PUT
URL: http://localhost:8000/employee/{employee_id}

Body: JSON

{
  "name": "test1",
  "password": "testpass1",
  "email": "test1@webdamn.com"
}

5. Delete a Employee Record:

Method: DELETE
URL: http://localhost:8000/employee/{employee_id}

Conclusion

In this tutorial, we have explained how to create a REST API usign FastAPI with Python and MySQL. We have run the API with POSTMAN application to test API end points. The FastAPI combined with MySQL, provides a powerful to build advanced web APIs. You can extend this further with more complex operations and security measures.