SQLAlchemy

When generating routes, the SQLAlchemyCRUDRouter will automatically tie into your database using your SQLAlchemy models. To use it, you must pass a pydantic model, your SQLAlchemy model to it, and the database dependency.

Warning

To use the SQLAlchemyCRUDRouter, SQLAlchemy must be first installed.

Simple Example

Below is an example assuming that you have already imported and created all the required models.

router = SQLAlchemyCRUDRouter(
    schema=MyPydanticModel,
    create_schema=MyPydanticCreateModel, 
    db_model=MyDBModel,
    db=get_db
)

app.include_router(router)

Note

The create_schema should not include the primary id field as this be generated by the database.

Full Example

from sqlalchemy import Column, String, Float, Integer
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from pydantic import BaseModel
from fastapi import FastAPI
from fastapi_crudrouter import SQLAlchemyCRUDRouter

app = FastAPI()
engine = create_engine(
    "sqlite:///./app.db",
    connect_args={"check_same_thread": False}
)

SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
)

Base = declarative_base()


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


class PotatoCreate(BaseModel):
    thickness: float
    mass: float
    color: str
    type: str


class Potato(PotatoCreate):
    id: int

    class Config:
        orm_mode = True


class PotatoModel(Base):
    __tablename__ = 'potatoes'
    id = Column(Integer, primary_key=True, index=True)
    thickness = Column(Float)
    mass = Column(Float)
    color = Column(String)
    type = Column(String)


Base.metadata.create_all(bind=engine)

router = SQLAlchemyCRUDRouter(
    schema=Potato,
    create_schema=PotatoCreate,
    db_model=PotatoModel,
    db=get_db,
    prefix='potato'
)

app.include_router(router)