fastapi: 第二十六章：添加\修改\删除 mysql数据

一，添加数据

代码：

# app/api/products.py
from fastapi import APIRouter, Request, Depends, HTTPException, Form
from pydantic import BaseModel, Field

from app.core.database import get_db
from app.core.logger import logger
from app.models.products import Product
from app.utils.log_util import get_query_param
from app.utils.response import success,fail
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select # 2.0 推荐使用 select 语法
import app.models
from typing import Annotated

router = APIRouter(prefix="/products", tags=["商品管理"])

# 1. 定义请求体数据模型
class ProductCreate(BaseModel):
    name: str = Field(..., min_length=3, max_length=20,description="商品名称，长度是3-20个字符")
    price: int = Field(..., gt=0, description="价格需要大于0")
    stock: int = Field(..., ge=0, description="库存不能小于0")

# form_data: Annotated[UserLoginForm, Form()]

@router.post("/add")
async def create_user(
        prod_data: Annotated[ProductCreate, Form()],
        db: AsyncSession = Depends(get_db)):
    # 检查用户名是否已存在
    result = await db.execute(select(Product).where(Product.name == prod_data.name))
    existing_prod = result.scalar_one_or_none()
    if existing_prod:
        raise HTTPException(status_code=400, detail="产品名已被使用")

    # 创建新产品
    new_prod = Product(
        name=prod_data.name,
        stock=prod_data.stock,
        price = prod_data.price,
    )

    # 添加到数据库
    db.add(new_prod)
    await db.commit()  # 提交事务
    await db.refresh(new_prod)  # 刷新，获取数据库生成的字段（如id）

    return success(data=new_prod)

测试效果:

 

二，修改数据

代码:

# app/api/products.py
from fastapi import APIRouter, Request, Depends, HTTPException, Form
from pydantic import BaseModel, Field

from app.core.database import get_db
from app.core.logger import logger
from app.models.products import Product
from app.utils.log_util import get_query_param
from app.utils.response import success,fail
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select # 2.0 推荐使用 select 语法
import app.models
from typing import Annotated

router = APIRouter(prefix="/products", tags=["商品管理"])

# 1. 定义请求体数据模型
class ProductCreate(BaseModel):
    name: str = Field(..., min_length=3, max_length=20,description="商品名称，长度是3-20个字符")
    price: int = Field(..., gt=0, description="价格需要大于0")
    stock: int = Field(..., ge=0, description="库存不能小于0")

# 更新用户
@router.post("/update/{prod_id}")
async def update_prod(
        prod_id: int,
        prod_data: Annotated[ProductCreate, Form()],
        db: AsyncSession = Depends(get_db)
):
    # 先查询用户是否存在
    result = await db.execute(select(Product).where(Product.id == prod_id))
    prod = result.scalar_one_or_none()

    if not prod:
        raise HTTPException(status_code=404, detail="产品不存在")

    # 更新字段
    prod.name = prod_data.name
    prod.stock = prod_data.stock
    prod.price = prod_data.price

    await db.commit()
    await db.refresh(prod)

    return success(data=prod)

测试效果：

三，删除数据:

# 删除产品
@router.get("/del/{prod_id}")
async def delete_prod(prod_id: int, db: AsyncSession = Depends(get_db)):
    result = await db.execute(select(Product).where(Product.id == prod_id))
    prod = result.scalar_one_or_none()

    if not prod:
        raise HTTPException(status_code=404, detail="产品不存在")

    await db.delete(prod)
    await db.commit()
    data = {
        "id": prod_id,
    }
    return success(data=data)

测试效果: