Create a pagination API with FastAPI

Splitting larger content into distinct pages is known as pagination. This approach significantly enhances the user experience and speeds up the loading of web pages. This example will demonstrate how to create a pagination API using FastAPI and MySQL as the database.

Prerequisites

• Python 3.10
• MySQL

Setup project

pip install fastapi sqlalchemy pymysql uvicorn python-dotenv

Create a testing database named "example" and run the database.sql file to import the table and data.

Project structure

├─ .env
└─ app
   ├─ db.py
   ├─ main.py
   ├─ models
   │  └─ product.py
   ├─ routers
   │  └─ product.py
   ├─ static
   │  └─ index.html
   └─ __init__.py

__init__.py is used to mark the directory as a Python package.

Project files

.env

DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=example
DB_USER=root
DB_PASSWORD=

This file contains the database connection information.

db.py

import os
from dotenv import load_dotenv
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

load_dotenv()
url = f"mysql+pymysql://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}:{os.getenv('DB_PORT')}/{os.getenv('DB_DATABASE')}"
engine = create_engine(url)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

The db.py module sets up the database connection for a FastAPI application using SQLAlchemy. It loads database credentials from environment variables, creates a SQLAlchemy engine for a MySQL database, and establishes a session factory for database interactions. The get_db function provides a database session that can be used in dependency injection, ensuring proper session management by closing the session after use.

models\product.py

from sqlalchemy import *
from app.db import Base

class Product(Base):
    __tablename__ = "Product"
    id = Column(INTEGER, primary_key=True)
    name = Column(VARCHAR)
    price = Column(DECIMAL)

The models\product.py module defines a SQLAlchemy model for the Product table, mapping its columns for the product's ID, name, and price. It extends the Base class from the db module, enabling easy interaction with product data in the database.

routers\product.py

from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session
from sqlalchemy.sql import select, asc, desc
from app.db import get_db
from app.models.product import Product

router = APIRouter()

@router.get("/products")
def index(page: int = 1, size: int = 10, order = "id", direction = "asc", db: Session = Depends(get_db)):
    sort_direction = asc if direction == "asc" else desc
    products = (
        db.query(Product)
        .order_by(sort_direction(getattr(Product, order)))
        .offset((page - 1) * size)
        .limit(size)
        .all()
    )
    return products

The product.py module defines a FastAPI router for handling product listings with pagination and sorting. It retrieves product data from the database using SQLAlchemy, allowing clients to specify pagination parameters (page, size) and sorting options (order, direction). The index function queries the Product model, orders the results based on the specified column and direction, and returns a limited number of products per page to improve performance for large datasets.

main.py

import uvicorn
from fastapi import FastAPI
from fastapi.responses import FileResponse
from app.routers.product import router

app = FastAPI()
app.include_router(router, prefix="/api")

@app.get("/")
async def index():
    return FileResponse("app/static/index.html")

if __name__ == " __main__":
    uvicorn.run(app, host="127.0.0.1")

The main.py module initializes a FastAPI application and includes a router for handling product-related API requests under the /api prefix. It also serves a static index.html file from the app/static directory at the root URL (/). When run as the main program, it starts the FastAPI server using Uvicorn, listening on 127.0.0.1. This setup provides the structure for both API endpoints and static file delivery in the application.

index.html

<!DOCTYPE html>
<head>
</head>
<body>
    <ul>
        <li><a target="_blank" href="/api/products">Default</a></li>
        <li><a target="_blank" href="/api/products?page=2">Page 2</a></li>
        <li><a target="_blank" href="/api/products?page=2&size=25">Page 2 and Size 25</a></li>
        <li><a target="_blank" href="/api/products?page=2&size=25&order=name">Page 2 and Size 25 and Order by name</a></li>
        <li><a target="_blank" href="/api/products?page=2&size=25&order=name&direction=desc">Page 2 and Size 25 and Order by name descending</a></li>
    </ul>
</body>
</html>

Instead of entering the URL manually to test our API, we used this file to create links for easier testing.

Run project

uvicorn app.main:app

Open the web browser and goto http://localhost:8000

You will find this test page.

Testing

Testing without any parameters

Click the "Default" link, and it will open the URL http://localhost:8000/api/products

The API will return paginated data with default parameters (page = 1 and size = 10).

Page index test

Click the "Page 2" link, and it will open the URL http://localhost:8000/api/products?page=2

The API will return paginated data on the second page, starting with product id 11.

Page size test

Click the "Page 2 and Size 25" link, and it will open the URL http://localhost:8000/api/products?page=2&size=25

The API will return paginated data on the second page by starting with product id 26 because the page size is 25.

Order test

Click the "Page 2 and Size 25 and Order by name" link, and it will open the URL http://localhost:8000/api/products?page=2&size=25&order=name

The API will return paginated data on the second page, but the product order is based on the product name.

Descending order test

Click the "Page 2 and Size 25 and Order by name descending" link, and it will open the URL http://localhost:8000/api/products?page=2&size=25&order=name&direction=desc

The API will return paginated data on the second page, but the product order is based on the product name in descending order.

Conclusion

In this article, you have learned how to set up a FastAPI server and implement a pagination API using SQLAlchemy for efficient database interaction. SQLAlchemy allows us to define models and perform database queries seamlessly, enabling sorting and pagination in a Pythonic way. This approach enhances user experience and optimizes the performance of your FastAPI application by retrieving data in manageable chunks. If you found the article helpful, please feel free to share it.

Source code: https://github.com/stackpuz/Example-Pagination-FastAPI

Create a CRUD Web App in Minutes: https://stackpuz.com