Building web applications that are both robust and scalable is essential in today’s tech landscape. One way to achieve this is by harnessing the power of FastAPI paired with SQLAlchemy. FastAPI is renowned for its speed and simplicity, and SQLAlchemy is a popular Object-Relational Mapping (ORM) tool for Python. Together, they make a super team for developers aiming to build efficient and easily maintainable applications.
To kick things off, forget about diving directly into the nitty-gritty. Just grab the necessary libraries with a simple pip install:
pip install sqlalchemy fastapi uvicorn
This command takes care of everything—SQLAlchemy for dealing with your database, FastAPI for building your web app, and Uvicorn to run it all.
Now, keeping your project structure clean and organized is a game-changer. Here’s a layout that could work wonders:
.
├── config
│ └── database.py
├── main.py
├── models
│ ├── comment.py
├── routers
│ ├── comment.py
├── schemas
│ ├── comment.py
├── services
│ ├── comment.py
With this setup, you neatly separate out the components of your application, making everything more manageable and easier to scale down the line.
Next, setting up your database connection is crucial. In the config folder, create database.py and throw in the following:
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
database_username = 'your_username'
database_password = 'your_password'
database_ip = 'your_database_ip or localhost'
database_name = 'your_db_name'
database_port = 'your_db_port'
engine = create_engine('mysql+mysqlconnector://{0}:{1}@{2}:{3}/{4}'.format(
database_username, database_password, database_ip, database_port, database_name))
Session = sessionmaker(bind=engine)
Base = declarative_base()
Boom! You’ve got your MySQL database connection ready.
Now, let’s talk models—they’re the backbone of any ORM system. Heading over to the models folder, create comment.py and define your table:
from config.database import Base
from sqlalchemy import Column, Integer, String, Float, DateTime
class Comment(Base):
__tablename__ = "comments"
id = Column(Integer, primary_key=True)
company_id = Column(Integer)
date = Column(DateTime)
stars = Column(Integer)
comment = Column(String)
name = Column(String)
email = Column(String)
phone = Column(String)
This structure defines a Comment table, laying out exactly how your data looks.
Moving on, let’s set up some routes to enable interaction with your data. In the routers folder, create a comment.py file:
from fastapi import APIRouter, Depends, Path, Query
from fastapi.responses import JSONResponse
from typing import Optional, List
from config.database import Session
from models.comment import Comment as CommentModel
from fastapi.encoders import jsonable_encoder
from services.comment import CommentService
from schemas.comment import Comment
comment_router = APIRouter()
@comment_router.get("/comments/", response_model=List[Comment])
def get_comments(db: Session = Depends()):
comments = CommentService.get_comments(db)
return comments
@comment_router.get("/comments/{comment_id}", response_model=Comment)
def get_comment(comment_id: int, db: Session = Depends()):
comment = CommentService.get_comment(db, comment_id)
return comment
These routes make it easy to retrieve comments from your database.
But wait, managing database sessions is a piece that can’t be overlooked. Let’s make that smooth with some middleware:
from fastapi import FastAPI, Request, Response
from fastapi.middleware import Middleware
from sqlalchemy.orm import Session
app = FastAPI()
@app.middleware("http")
async def db_session_middleware(request: Request, call_next):
response = Response("Internal server error", status_code=500)
try:
request.state.db = SessionLocal()
response = await call_next(request)
finally:
request.state.db.close()
return response
def get_db(request: Request):
return request.state.db
This middleware ensures a database session is created and closed with each request. Smooth, right?
Here’s where things get critical—keeping a clear separation of concerns. Don’t mix your database models with API endpoints. Use Pydantic models to validate user input:
from pydantic import BaseModel
class CommentCreate(BaseModel):
company_id: int
date: str
stars: int
comment: str
name: str
email: str
phone: str
Meanwhile, SQLAlchemy models should handle database interactions.
For more seamless integration, use FastAPI-SQLAlchemy. Install it with:
pip install fastapi-sqlalchemy
And incorporate it like so:
from fastapi import FastAPI
from fastapi_sqlalchemy import DBSessionMiddleware, db
app = FastAPI()
app.add_middleware(DBSessionMiddleware, db_url="sqlite://")
@app.get("/comments/")
def get_comments():
comments = db.session.query(Comment).all()
return comments
This makes database session handling simple and straightforward.
Ever need to access the database outside of request contexts, say for scheduled tasks? No problem. Here’s an example using FastAPI-SQLAlchemy with a scheduler:
import pytz
from apscheduler.schedulers.asyncio import AsyncIOScheduler
from fastapi import FastAPI
from fastapi_sqlalchemy import db
app = FastAPI()
app.add_middleware(DBSessionMiddleware, db_url="sqlite://")
@app.on_event('startup')
async def startup_event():
scheduler = AsyncIOScheduler(timezone=pytz.utc)
scheduler.start()
scheduler.add_job(count_comments_task, "cron", hour=0) # runs every night at midnight
def count_comments_task():
with db():
comment_count = db.session.query(Comment).count()
# Save the count or perform other operations
db.session.commit()
This scheduler runs a task at midnight every day, and you can easily access the database outside of regular request contexts.
By merging FastAPI with SQLAlchemy, your application not only becomes more effective but also incredibly organized and maintainable. Whether you’re working on a small project or a massive enterprise-level application, this combo will keep your database interactions smooth and efficient. So, go ahead and build that app with confidence, knowing you have a solid, scalable foundation.
