fastapi + aiomysql 实现增删改查

from fastapi import FastAPI, Depends, HTTPException
from aiomysql import create_pool
import aiomysql
import asyncio

app = FastAPI()

# 数据库配置信息
DB_CONFIG = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'your_username',
    'password': 'your_password',
    'db': 'your_database',
}


# 创建数据库连接池
@app.on_event("startup")
async def startup():
    app.state.db_pool = await create_pool(**DB_CONFIG)


# 关闭数据库连接池
@app.on_event("shutdown")
async def shutdown():
    app.state.db_pool.close()
    await app.state.db_pool.wait_closed()


# 依赖项，用于从连接池中获取连接
async def get_db_connection():
    async with app.state.db_pool.acquire() as connection:
        async with connection.cursor() as cursor:
            yield cursor


# 批量插入数据
@app.post("/items/")
async def create_items(items: list[dict], cursor: aiomysql.Cursor = Depends(get_db_connection)):
    values = ', '.join(['(%s, %s)'] * len(items))
    args = [val for item in items for val in (item['name'], item['description'])]
    query = f"INSERT INTO items (name, description) VALUES {values}"
    await cursor.execute(query, args)
    await cursor.connection.commit()
    return {"status": "items created"}


# 批量删除数据
@app.delete("/items/")
async def delete_items(ids: list[int], cursor: aiomysql.Cursor = Depends(get_db_connection)):
    placeholders = ', '.join(['%s'] * len(ids))
    query = f"DELETE FROM items WHERE id IN ({placeholders})"
    await cursor.execute(query, ids)
    await cursor.connection.commit()
    return {"status": "items deleted"}


# 批量更新数据
@app.put("/items/")
async def update_items(items: list[dict], cursor: aiomysql.Cursor = Depends(get_db_connection)):
    updates = ', '.join(["%s = %s"] * len(items))
    args = [val for pair in items for val in (pair['id'], pair['description'])]
    query = f"UPDATE items SET description = CASE id {updates} END WHERE id IN (%s)"
    await cursor.execute(query, args + [tuple(pair['id'] for pair in items)])
    await cursor.connection.commit()
    return {"status": "items updated"}


if __name__ == "__main__":
    import uvicorn

    uvicorn.run(app, host="0.0.0.0", port=8000)