Introduction to Databases

• Database are used to store and retrieve the data effeciently
• Databases are of following types
  • Relational
  • NOSQL
    • Document
    • Key Value
    • Column Databases
  • Graph Databases
  • Vector Databases
• Relational: Best suited for transactional data

Relational Databases

• Data gets stored in Databases, A database is collection of Tables
• Each Table represents (Student or Account) and individual row represents a record with different values in each column
• Tables can have relationship between them
• To interact with databases, there is a standard language called as SQL (Structured Query Language)
• To use Relational Databases, we have different vendors offering dbms
  • mysql
  • Oracle (pl/sql)
  • Postgres
  • SQL Server (t-sql)
• We can use Azure Data Studio (vscode for databases) to connect to any database as a client
• Now a days ORM (Object Relational Mapping) frameworks have become popular
  • For every table we have an object
  • we deal with database as if we are dealing with objects
  • orm frameworks work with any database

System Setup

Option 1: System Installation

• Install mysql on your system

Option 2: Docker Desktop

• Download and install docker desktop Refer Here
• Running mysql locally

docker run --name=inventory-db -e MYSQL_ROOT_PASSWORD=rootroot -e MYSQL_DATABASE=inventorydb -e MYSQL_USER=qtdevops -e MYSQL_PASSWORD=qtdevops -p 3306:3306 -d mysql:latest

Adding Relational Databases to FastAPI

• How to connect to databases to retrieve and insert information from fastapi
• SQL Alchemy is a popular ORM framework which works with fast api for relational databases

Example Implmentation

To use MySQL from FastAPI with SQLAlchemy, you can follow these steps to create a simple API that manages a student table. This guide will cover setting up the database connection, defining the model, and creating endpoints for basic CRUD operations.

Step 1: Install Required Packages

Make sure you have the necessary Python libraries installed:

pip install fastapi uvicorn sqlalchemy pymysql

• FastAPI: The web framework.
• Uvicorn: ASGI server for running FastAPI applications.
• SQLAlchemy: ORM for database interaction.
• PyMySQL: MySQL driver for Python.

Step 2: Set Up Database Connection

Create a file named database.py to handle the database connection:

# config/database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "mysql+pymysql://myuser:mypassword@localhost/mydatabase"

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

Replace myuser, mypassword, and mydatabase with your MySQL credentials and database name.

Step 3: Define the Student Model

Create a file named models.py to define the student table structure:

# models.py
from sqlalchemy import Column, Integer, String
from config.database import Base

class Student(Base):
    __tablename__ = "students"

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

Step 4: Create the FastAPI Application

Create a file named main.py for your FastAPI application:

# main.py
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from config.database import SessionLocal, engine
from models import Student, Base

# Create the database tables
Base.metadata.create_all(bind=engine)

app = FastAPI()

# Dependency to get DB session
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.post("/students/", response_model=Student)
def create_student(student: Student, db: Session = Depends(get_db)):
    db.add(student)
    db.commit()
    db.refresh(student)
    return student

@app.get("/students/{student_id}", response_model=Student)
def read_student(student_id: int, db: Session = Depends(get_db)):
    student = db.query(Student).filter(Student.id == student_id).first()
    if student is None:
        raise HTTPException(status_code=404, detail="Student not found")
    return student

@app.get("/students/", response_model=list[Student])
def read_students(skip: int = 0, limit: int = 10, db: Session = Depends(get_db)):
    students = db.query(Student).offset(skip).limit(limit).all()
    return students

@app.delete("/students/{student_id}")
def delete_student(student_id: int, db: Session = Depends(get_db)):
    student = db.query(Student).filter(Student.id == student_id).first()
    if student is None:
        raise HTTPException(status_code=404, detail="Student not found")

    db.delete(student)
    db.commit()
    return {"detail": "Student deleted"}

Step 5: Run the Application

You can run your FastAPI application using Uvicorn:

uvicorn main:app --reload

This command starts the server at http://127.0.0.1:8000. You can access the automatically generated API documentation at http://127.0.0.1:8000/docs.

Conclusion

This setup allows you to interact with a MySQL database using FastAPI and SQLAlchemy. You can create, read, and delete records in the students table through the defined API endpoints. Adjust the model and endpoints as needed to fit your application’s requirements.

Citations:
[1] https://python.plainenglish.io/fastapi-mysql-connections-using-sqlalchemy-d17319d2b7d3?gi=66a8bb14742b
[2] https://www.youtube.com/watch?v=zzOwU41UjTM
[3] https://fastapi.tiangolo.com/tutorial/sql-databases/
[4] https://mattermost.com/blog/building-a-crud-fastapi-app-with-sqlalchemy/
[5] https://blog.balasundar.com/building-a-crud-app-with-fastapi-and-mysql
[6] https://github.com/wpcodevo/fastapi_sqlalchemy
[7] https://www.linkedin.com/pulse/integrating-fastapi-sqlalchemy-flyway-aakash-khanna-sziwc