FastAPI + MySQL simple REST API Example!

FastAPI is a powerful web framework for building RESTful APIs in Python. With its easy-to-use and intuitive syntax, FastAPI can help you quickly create a robust REST API. In this article, we will explore how to implement a MySQL-backed REST API using FastAPI.

Step 1:

Set up the Database

The first step is to set up the database. For this tutorial, we will be using MySQL. You will need to have MySQL installed on your system and create a database and table to work with. Here is a sample SQL query to create a table called "employees":

CREATE TABLE employees (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT(11) NOT NULL,
    PRIMARY KEY (id)
);

Step 2:

Install Dependencies

The next step is to install the necessary dependencies. You will need to install the following libraries:

pip install fastapi
pip install uvicorn
pip install mysql-connector-python

Step 3:

Create a FastAPI app

Once you have installed the dependencies, create a new file called "main.py" and import the necessary libraries:

from fastapi import FastAPI
import mysql.connector

Next, create a FastAPI app instance:

app = FastAPI()

Step 4:

Create the MySQL connection

Now that the app is set up, create a connection to the MySQL database using the "mysql-connector-python" library:

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

Step 5:

Define the CRUD operations

Next, define the CRUD operations for the API. For this tutorial, we will implement four endpoints:

• GET /employees: Get a list of all employees

• GET /employees/{id}: Get a specific employee by ID

• POST /employees: Add a new employee

• DELETE /employees/{id}: Delete an employee by ID

# Get all employees
@app.get("/employees")
def get_employees():
    cursor = mydb.cursor()
    cursor.execute("SELECT * FROM employees")
    result = cursor.fetchall()
    return {"employees": result}

# Get an employee by ID
@app.get("/employees/{id}")
def get_employee(id: int):
    cursor = mydb.cursor()
    cursor.execute(f"SELECT * FROM employees WHERE id = {id}")
    result = cursor.fetchone()
    return {"employee": result}

# Add a new employee
@app.post("/employees")
def add_employee(name: str, age: int):
    cursor = mydb.cursor()
    sql = "INSERT INTO employees (name, age) VALUES (%s, %s)"
    val = (name, age)
    cursor.execute(sql, val)
    mydb.commit()
    return {"message": "Employee added successfully"}

# Delete an employee by ID
@app.delete("/employees/{id}")
def delete_employee(id: int):
    cursor = mydb.cursor()
    cursor.execute(f"DELETE FROM employees WHERE id = {id}")
    mydb.commit()
    return {"message": "Employee deleted successfully"}

Step 6:

Start the server

Finally, start the server using the "uvicorn" library:

uvicorn main:app --reload

This will start the server on localhost:8000. You can now test the API endpoints using a tool like Postman.

Conclusion

In this article, we have explored how to implement a MySQL-backed REST API using FastAPI. With its easy-to-use syntax and powerful features, FastAPI makes it simple to create a robust REST API in Python.

I hope this helps, you!!

More such articles:

https://medium.com/techwasti

https://www.youtube.com/channel/UCiTaHm1AYqMS4F4L9zyO7qA

https://www.techwasti.com/

\==========================**=========================

If this article adds any value to you then please clap and comment.

Let’s connect on Stackoverflow, LinkedIn, & Twitter.