FastAPI – CRUD con SQLAlchemy e costruzione Endpoints

FastAPI is a modern, fast (high-performance) web framework for building APIs with Python 3.6+ based on standard Python type hints. In this article, we will focus on how to perform CRUD operations using SQLAlchemy and building endpoints with FastAPI.

CRUD with SQLAlchemy

SQLAlchemy is a powerful ORM (Object Relational Mapper) that allows us to interact with databases in a Pythonic way. With FastAPI, we can easily integrate SQLAlchemy to perform CRUD operations on our database.

Create

To create a new record in our database using FastAPI and SQLAlchemy, we need to define a model and a POST endpoint. Here’s an example:

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

      app = FastAPI()

      SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
      engine = create_engine(SQLALCHEMY_DATABASE_URL)
      SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
      Base = declarative_base()

      class Item(Base):
          __tablename__ = "items"

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

      Base.metadata.create_all(bind=engine)

      class ItemCreate(BaseModel):
          name: str

      @app.post("/items/")
      def create_item(item: ItemCreate):
          db = SessionLocal()
          new_item = Item(name=item.name)
          db.add(new_item)
          db.commit()
          db.refresh(new_item)
          return new_item
    
  

Read

Reading records from the database is also straightforward with FastAPI and SQLAlchemy. We can define a GET endpoint to retrieve all items or a specific item based on its ID. Here’s an example:

    
      @app.get("/items/")
      def get_items():
          db = SessionLocal()
          items = db.query(Item).all()
          return items

      @app.get("/items/{item_id}")
      def get_item(item_id: int):
          db = SessionLocal()
          item = db.query(Item).filter(Item.id == item_id).first()
          return item
    
  

Update

Updating a record in the database involves defining a PUT endpoint in FastAPI. Here’s an example of how we can update an item based on its ID:

    
      class ItemUpdate(BaseModel):
          name: str

      @app.put("/items/{item_id}")
      def update_item(item_id: int, item: ItemUpdate):
          db = SessionLocal()
          db.query(Item).filter(Item.id == item_id).update({"name": item.name})
          db.commit()
          return {"message": "Item updated successfully"}
    
  

Delete

Deleting a record from the database involves defining a DELETE endpoint in FastAPI. Here’s an example of how we can delete an item based on its ID:

    
      @app.delete("/items/{item_id}")
      def delete_item(item_id: int):
          db = SessionLocal()
          db.query(Item).filter(Item.id == item_id).delete()
          db.commit()
          return {"message": "Item deleted successfully"}
    
  

Building Endpoints

With FastAPI, we can easily define and document our API endpoints using Python type hints. By leveraging FastAPI’s automatic OpenAPI documentation, we can create endpoints that are self-descriptive and easy to understand.

In conclusion, FastAPI combined with SQLAlchemy makes it easy to perform CRUD operations and build endpoints for your APIs. With its performance and ease of use, FastAPI is a great choice for developing web applications and APIs in Python.