Building a CRUD Application with Python, FastAPI, and MySQL

CRUD using Python, FastAPI with MySQL database

Introduction

In this tutorial, we will explore how to build a powerful CRUD (Create, Read, Update, Delete) application using Python, FastAPI, and MySQL. FastAPI is a modern, fast (high-performance), web framework for building APIs with Python 3.7+ based on standard Python type hints. MySQL is a popular relational database management system known for its speed, reliability, and scalability. By the end of this tutorial, you’ll have a solid understanding of how to create, retrieve, update, and delete data from a MySQL database using FastAPI.

Prerequisites

Before we begin, make sure you have the following prerequisites installed:

• Python 3.7+
• FastAPI (pip install fastapi)
• Uvicorn (pip install uvicorn)
• MySQL Connector/Python (pip install mysql-connector-python)

Setting Up the Project

1. Create a new directory for your project and navigate to it in your terminal.
2. Initialize a new Python virtual environment by running: python3 -m venv env.
3. Activate the virtual environment: source env/bin/activate (Unix/Mac) or env\Scripts\activate (Windows).
4. Create a new file called main.py and open it in your favorite text editor.

Creating a FastAPI Application:

Let’s start by creating a basic FastAPI application. Open main.py and add the following code:

from fastapi import FastAPI

app = FastAPI()

@app.get("/")
async def root():
    return {"message": "Hello, World!"}

This code initializes a FastAPI application and adds a single route for the root URL (“/”) that returns a simple JSON response.

Running the FastAPI Application:

To run the FastAPI application, open your terminal and navigate to the project directory.

Execute the following command:

uvicorn main:app --reload

You should see the following output:

INFO: Uvicorn running on http://localhost:8000 (Press CTRL+C to quit)
INFO: Started reloader process [1234]
INFO: Started server process [5678]

Open your web browser and navigate to http://localhost:8000. You should see the “Hello, World!” message displayed.

Setting Up MySQL Database:

Next, we need to set up a MySQL database to store our data. Install MySQL Server if you haven’t already, and create a new database with a table to hold our data.

CREATE DATABASE mydatabase;
USE mydatabase;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Connecting to MySQL Database:

To connect to the MySQL database, we need to provide the necessary credentials. Create a new file called database.py and add the following code:

import mysql.connector

def get_database_connection():
    return mysql.connector.connect(
        host="localhost",
        user="your_username",
        password="your_password",
        database="mydatabase"
    )

Replace "your_username" and "your_password" with your MySQL credentials.

Creating the CRUD Routes:

Now that we have our FastAPI application and database connection set up, let’s create the routes for CRUD operations. Update main.py with the following code:

from fastapi import FastAPI
from pydantic import BaseModel
from typing import List
from database import get_database_connection

app = FastAPI()

class User(BaseModel):
    name: str
    email: str

@app.post("/users")
async def create_user(user: User):
    connection = get_database_connection()
    cursor = connection.cursor()
    query = "INSERT INTO users (name, email) VALUES (%s, %s)"
    values = (user.name, user.email)
    cursor.execute(query, values)
    connection.commit()
    connection.close()
    return {"message": "User created successfully"}

@app.get("/users")
async def read_users():
    connection = get_database_connection()
    cursor = connection.cursor()
    query = "SELECT * FROM users"
    cursor.execute(query)
    users = cursor.fetchall()
    connection.close()
    return users

# Implement the update and delete routes similarly

The code above defines two routes: one for creating a new user (/users) and another for retrieving all users (/users). We use the pydantic library to define the structure of the User model.

Testing the CRUD Operations:

To test our CRUD operations, restart the FastAPI application by stopping the currently running process (Ctrl+C) and running the following command:

uvicorn main:app --reload

Now, you can use tools like cURL, Postman, or your web browser to interact with the API endpoints:

To create a new user, make a POST request to http://localhost:8000/users with the following JSON payload:

{
  "name": "John Doe",
  "email": "john.doe@example.com"
}

To retrieve all users, make a GET request to http://localhost:8000/users.

Conclusion:

Congratulations! You have successfully built a CRUD application using Python, FastAPI, and MySQL. We explored how to set up a FastAPI application, connect to a MySQL database, and implement the basic CRUD routes. FastAPI’s powerful features and easy integration with databases like MySQL make it an excellent choice for building scalable and efficient web applications. Feel free to explore additional features and expand the application as per your requirements.

Happy coding!