Working with Python FastAPI and MySQL

FastAPI is a modern, fast (high-performance), web framework for building APIs with Python. It’s built on standard Python type hints, making it easy to use, while providing a lot of advanced features for developers.

When building a FastAPI application, it’s crucial to separate configuration from the code, especially database connection details, for security and modularity. Using .env files and structured code can make this process more secure and maintainable.

In this article, we will look at integrating FastAPI with MySQL to handle database operations.

Setting up FastAPI with MySQL

1. Setting up the environment:

• Create a new virtual environment:

  python3 -m venv env 
  source env/bin/activate 
  

• Install FastAPI, Uvicorn, and necessary packages:

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

2. Setting up .env:

Create a `.env` file in your project’s root directory with the following:

MYSQL_HOST=localhost
MYSQL_USER=your_user
MYSQL_PASSWORD=your_password
MYSQL_DB=your_database

3. Connecting to MySQL:

Let’s create a separate `database.py` file to handle all database-related operations:

`database.py`:

import mysql.connector
from dotenv import load_dotenv
import os

# Load environment variables
load_dotenv()

MYSQL_HOST = os.getenv("MYSQL_HOST")
MYSQL_USER = os.getenv("MYSQL_USER")
MYSQL_PASSWORD = os.getenv("MYSQL_PASSWORD")
MYSQL_DB = os.getenv("MYSQL_DB")

# Connect to MySQL
def connect():
    return mysql.connector.connect(
        host=MYSQL_HOST,
        user=MYSQL_USER,
        password=MYSQL_PASSWORD,
        database=MYSQL_DB
    )

Now, in `main.py`, import the connect function:

from fastapi import FastAPI
from database import connect

app = FastAPI()
conn = connect()
cursor = conn.cursor()

Creating a Table

Inside `database.py`, create a function to initialize tables:

def initialize_db():
    conn = connect()
    cursor = conn.cursor()
    query = """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255),
        age INT
    )
    """
    cursor.execute(query)
    conn.close()

Invoke this function during the startup of your FastAPI application:

@app.on_event("startup")
async def startup_event():
    initialize_db()

CRUD Operations

Continue to encapsulate database operations within `database.py`. For instance:

# Inside database.py
def create_user(name, age):
    conn = connect()
    cursor = conn.cursor()
    query = "INSERT INTO users (name, age) VALUES (%s, %s)"
    values = (name, age)
    cursor.execute(query, values)
    conn.commit()
    conn.close()
    return cursor.lastrowid

# ... [other CRUD functions]

Then, in `main.py`, invoke these functions:

from fastapi import HTTPException
from database import create_user, ...

@app.post("/users/")
async def add_user(name: str, age: int):
    user_id = create_user(name, age)
    if user_id:
        return {"id": user_id, "name": name, "age": age}
    else:
        raise HTTPException(status_code=400, detail="User not created")

# ... [other endpoint handlers]

Running the Application

Run the FastAPI app using Uvicorn:

uvicorn main:app --reload 

This setup ensures a clear separation of concerns, keeps sensitive data out of your main codebase (using .env), and organizes database operations neatly in a separate module. As your application grows, consider adopting more advanced practices like connection pooling, ORM tools, and thorough error handling.

Conclusion

With this, you have a basic FastAPI application integrated with MySQL. Remember to properly manage and close database connections, handle exceptions, and implement other best practices in production environments. This guide is meant to be a starting point, and there’s much more you can do with both FastAPI and MySQL!