Building a FastAPI REST API with MySQL Database in Python for CRUD Operations, including Swagger documentation and SQLAlchemy Integration

In this tutorial, we will be building a REST API using FastAPI and MySQL for CRUD operations like Create, Read, Update, and Delete. We will also be using Swagger for API documentation and SQLAlchemy as an ORM for database operations.

Prerequisites:

• Python installed on your machine
• FastAPI, SQLAlchemy, and mysql-connector-python library installed
• Basic knowledge of Python and REST API

Let’s start by setting up a virtual environment and installing the required libraries.

Step 1: Create a new directory for your project and navigate to it.

mkdir fastapi-mysql-api
cd fastapi-mysql-api

Step 2: Create a virtual environment and activate it.

python -m venv venv
source venv/bin/activate  # for Unix-based systems
venvScriptsactivate  # for Windows

Step 3: Install the required libraries.

pip install fastapi[all] sqlalchemy mysql-connector-python

Step 4: Create a new Python file, e.g., main.py, and import the necessary modules.

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

app = FastAPI()

# Database connection
SQLALCHEMY_DATABASE_URL = "mysql+mysqlconnector://username:password@localhost/dbname"
engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Step 5: Define the data model for our application.

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)

Step 6: Create the database and tables.

Base.metadata.create_all(bind=engine)

Now, let’s implement the CRUD operations for our API.

Step 7: Create a request model for POST requests.

class ItemCreate(BaseModel):
    name: str

Step 8: Implement the Create operation.

@app.post("/items/", response_model=Item)
def create_item(item: ItemCreate, db: Session = Depends(get_db)):
    db_item = Item(name=item.name)
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

Step 9: Implement the Read operation.

@app.get("/items/{item_id}", response_model=Item)
def read_item(item_id: int, db: Session = Depends(get_db)):
    item = db.query(Item).filter(Item.id == item_id).first()
    if item is None:
        raise HTTPException(status_code=404, detail="Item not found")
    return item

Step 10: Implement the Update operation.

class ItemUpdate(BaseModel):
    name: str

@app.put("/items/{item_id}", response_model=Item)
def update_item(item_id: int, item: ItemUpdate, db: Session = Depends(get_db)):
    db_item = db.query(Item).filter(Item.id == item_id).first()
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found")
    db_item.name = item.name
    db.commit()
    db.refresh(db_item)
    return db_item

Step 11: Implement the Delete operation.

@app.delete("/items/{item_id}")
def delete_item(item_id: int, db: Session = Depends(get_db)):
    db_item = db.query(Item).filter(Item.id == item_id).first()
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found")
    db.delete(db_item)
    db.commit()
    return {"message": "Item deleted successfully"}

Step 12: Run the FastAPI application.

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app, host="0.0.0.0", port=8000)

Now that we have implemented the CRUD operations, let’s set up Swagger documentation for our API.

Step 13: Install and import the necessary modules for Swagger.

from fastapi import FastAPI
from fastapi.openapi.utils import get_openapi

app = FastAPI()

def custom_openapi():
    if app.openapi_schema:
        return app.openapi_schema
    openapi_schema = get_openapi(
        title="FastAPI MySQL CRUD API",
        version="0.1.0",
        description="This is a simple CRUD API using FastAPI and MySQL",
        routes=app.routes,
    )
    app.openapi_schema = openapi_schema
    return app.openapi_schema

app.openapi = custom_openapi

Step 14: Now, run the application.

python main.py

Step 15: Open your browser and navigate to http://localhost:8000/docs, where you will see the Swagger documentation for your API.

That’s it! You have successfully created a FastAPI MySQL REST API with CRUD operations, Swagger documentation, and SQLAlchemy. You can now start making requests to your API using tools like Postman or curl.