fastapi项目 06-新增项目接口，序列化查询结果
前言
新增项目，一般都会记录是哪个用户新建的，往往一个用户可以新建多个项目，于是数据库模型就可以建立一对多的关系。
1. 新建数据库模型(一对多)
# apps/model/models.py
class User(DbModel)
......
# 一对多
projects = relationship('Project', backref='user', cascade='all,delete')
class Project(DbModel):
"""项目信息"""
__tablename__ = 'project'
id = Column(Integer, primary_key=True, autoincrement=True)
project_name = Column(String(100), unique=True, nullable=False)
publish_app = Column(String(100), nullable=True)
project_des = Column(String(600), nullable=True)
active = Column(Enum("1", "0"), default="1", nullable=True)
# 所属用户
user_id = Column(Integer, ForeignKey('user.id'))
新增数据库模型后，就可以进行表的迁移：
- alembic revision --autogenerate -m "test" # 自定义迁移文件名
- alembic upgrade head
迁移成功后，就可以在数据库中查看到新建了数据库表project。
1.1 新增crud
# apps/routers/crud.py
from apps.model.models import User, Project
def create_project(db: Session, body):
db_project = Project(**body.dict())
# 先判断是否存在，在入库
if db.query(Project).filter_by(project_name=db_project.project_name).count():
# 项目名称重复添加
raise HTTPException(status_code=400, detail=f'项目名称：{body.project_name},已存在')
try:
db.add(db_project)
db.commit()
db.refresh(db_project)
except Exception as e:
print(f"数据写入失败：{e}")
raise HTTPException(status_code=400, detail='添加项目失败！')
return db_project
创建新建项目的接口函数，并约定数据输入和输出模型。
我们先建立数据的输入和输出模型。
# apps/model/schemas.py
class ProjectIn(BaseModel):
project_name: str = Field(..., max_length=100, description="项目名称")
publish_app: str = Field(None, max_length=100)
project_des: str = Field(None, max_length=600)
user_id: int = Field(None)
class ProjectOut(BaseModel):
id: int
project_name: str
project_app: str = ''
project_des: str = ''
active: str = '1'
user_id: int = ''
class Config:
orm_mode = True # 将查询到的orm对象，转换成json
# apps/routers/view_project.py
from fastapi import APIRouter, Depends
from fastapi_jwt_auth import AuthJWT
from apps.dependency import get_db
from sqlalchemy.orm import Session
from apps.model.schemas import ProjectIn, ProjectOut
from apps.model.crud import create_project
router = APIRouter()
@router.post("/project", response_model=ProjectOut)
async def create_project_demo(
body: ProjectIn,
Authorize: AuthJWT = Depends(),
db: Session = Depends(get_db)
):
"""新增项目数据"""
Authorize.jwt_required() # 需要先登录才能请求该接口
# 获取额外的数据
user_claims= Authorize.get_raw_jwt()
# 得到用户名
print(f'用户名：{user_claims["username"]}')
body.user_id = user_claims["id"]
res = create_project(db, body)
return res
工厂函数create_app中，注册路由。
# 注册路由
app.include_router(view_project.router, prefix="/api/v1", tags=["项目"])
1.2 设置全局response model
正常在接口返回的内容一般都是以{"code": 0, "msg": "success", "data": data}的形式返回的。于是我们可以设置全局response model。
"""定义统一的返回格式"""
# apps/model/schema.py
from typing import Any, Optional, Dict
from starlette.background import BackgroundTask
from starlette.responses import JSONResponse
from fastapi.encoders import jsonable_encoder
class FormatJSONResponse(JSONResponse):
def __init__(self,
data: Any = None,
code: int = 0,
msg: str = 'success',
status_code: int = 200,
headers: Optional[Dict[str, str]] = None,
background: Optional[BackgroundTask] = None
) -> None:
content = jsonable_encoder({
'code': code,
'msg': msg,
'data': data
})
super().__init__(content=content, status_code=status_code,
headers=headers, media_type='application/json', background=background)
from apps.model.schemas import FormatJSONResponse
def create_app():
"""工厂函数"""
app = FastAPI(default_response_class=FormatJSONResponse)
重新运行后，发现返回的数据，已经加上了code和msg。
2.1 新增查询接口
查询项目的方式有多种，一种是传一个路径参数p_id，一种是传查询参数，查询多个或全部。
@router.get("/project/{p_id}", response_model=ProjectOut)
async def get_all_demo(
p_id: int,
Authorize: AuthJWT = Depends(),
db: Session = Depends(get_db)
):
Authorize.jwt_required() # 需要先登录才能请求该接口
user_claims = Authorize.get_raw_jwt()
print(f'用户传进来的p_id: {p_id}')
# 查询数据库
res = query_project_by_id(db, p_id)
return res
在crud中创建查询项目的函数。
# crud.py
def query_project_by_id(db: Session, p_id: int):
query_project = db.query(Project).filter_by(id=p_id)
if query_project.count() > 0:
return query_project.first()
else:
raise HTTPException(status_code=404, detail="project 项目id未找到！")
继续我们设置谁创建的项目谁才有权限查询，admin账号我们让它都能查询,由于在数据库中未添加user的is_admin字段，于是我们在登录的时候，指定一个is_admin参数，传入。
def query_project_by_id(db: Session, p_id: int, user_claims):
# 权限，每个人只能查询自己创建的，admin账号查询全部
if user_claims.get('is_admin'):
query_project = db.query(Project).filter_by(id=p_id)
else:
query_project = db.query(Project).filter_by(id=p_id, user_id=user_claims['id'])
if query_project.count() > 0:
return query_project.first()
else:
raise HTTPException(status_code=404, detail="project 项目id未找到！")
2.2 新增删除接口
我们直接通过id去删除项目。
@router.delete("/project/{p_id}")
async def del_project(
p_id: int,
Authorize: AuthJWT = Depends(),
db: Session = Depends(get_db)
):
Authorize.jwt_required()
user_claims = Authorize.get_raw_jwt()
print(f'用户传进来的p_id：{p_id}')
# 执行数据库删除
delete_project_by_id(db, p_id, user_claims)
在crud中，我们只允许admin账号才有删除的权限。
def delete_project_by_id(db: Session, p_id: int, user_claims):
project =db.query(Project).filter_by(id=p_id)
if not project.count() > 0:
raise HTTPException(status_code=404, detail="project id not found!")
if user_claims.get('is_admin'):
db.query(Project).filter_by(id=p_id).delete()
db.commit()
else:
raise HTTPException(status_code=403, detail="无权限操作，请联系管理员")
2.3 新增更新接口
话不多说，直接上代码。
class UpdateProjectIn(BaseModel):
project_name: str = Field(default='', title='项目名称', description='项目名称', min_length=1, max_length=100)
publish_app: str = Field(default='', title='发布应用', description='项目名称', max_length=100)
project_des: str = Field(default='', title='描述', description='描述', max_length=100)
# crud.py
def update_project_by_id(db: Session, p_id: int, user_claims, body):
project = db.query(Project).filter_by(id=p_id)
if project.count() < 1:
raise HTTPException(status_code=404, detail="project id not found!")
if not user_claims.get('is_admin'):
if project.first().user_id != user_claims.get('id'):
raise HTTPException(status_code=403, detail="当前用户无权限操作，请联系管理员")
# 修改
project_name = body.project_name
count = db.query(Project) \
.filter(Project.id!=p_id) \
.filter_by(project_name=project_name) \
.count()
if count > 0: # 查询判断不能与数据库已有的项目名称重复
raise HTTPException(status_code=403, detail=f"project name:{project_name},已存在")
try:
# exclude_unset=True 表示去掉未传值字段，避免未传值将原始的值更新为空
project.update(body.dict(exclude_unset=True))
db.commit()
except Exception as e:
print(f'----update error-------:{e}')
raise HTTPException(status_code=409, detail='update error')
@router.put("/project/{p_id}")
async def put_project(
p_id: int,
body: UpdateProjectIn,
Authorize: AuthJWT = Depends(),
db: Session = Depends(get_db)
):
Authorize.jwt_required()
user_claims = Authorize.get_raw_jwt()
print(f'用户传进来的p_id：{p_id}')
# 执行数据库操作
update_project_by_id(db, p_id, user_claims, body)
return {'message': 'update success!'}
2.4 新增条件过滤查询
查询数据接口，一般都支持条件过滤，根据输入的条件，选择过滤的信息。
# crud.py
def query_all_projects(db: Session, user_claims, project_name=''):
if user_claims.get('is_admin'):
objs = db.query(Project)
else:
objs = db.query(Project).filter_by(user_id=user_claims.get('id'))
return {"total": objs.count(), "data": objs.all()}
class AllProjectOut(BaseModel):
total: int
data: List[ProjectOut]
class Config:
orm_mode = True # 将查询到的orm对象，转换成json
@router.get("/project", response_model=AllProjectOut)
async def get_all_projects(
Authorize: AuthJWT = Depends(),
db: Session = Depends(get_db)
):
Authorize.jwt_required()
user_claims = Authorize.get_raw_jwt()
all_projects = query_all_projects(db, user_claims)
return all_projects
这样即可实现查询全部的项目信息。后面我们增加条件过滤参数，根据条件查询出指定的项目信息。只需要在crud中增加project_name筛选即可。
# crud.py
def query_all_projects(db: Session, user_claims, project_name=''):
if user_claims.get('is_admin'):
objs = db.query(Project).order_by(desc(Project.id))
else:
objs = db.query(Project).filter_by(user_id=user_claims.get('id')).order_by(desc(Project.id))
# 过滤条件查询
objs = objs.filter(
or_(Project.project_name.contains(project_name), project_name=='')
)
return {"total": objs.count(), "data": objs.all()}
@router.get("/project", response_model=AllProjectOut)
async def get_all_projects(
project_name: str = Query(default='', example='项目'), # 增加查询参数
Authorize: AuthJWT = Depends(),
db: Session = Depends(get_db)
):
Authorize.jwt_required()
user_claims = Authorize.get_raw_jwt()
all_projects = query_all_projects(db, user_claims, project_name)
return all_projects
2.5 增加分页查询
分页查询一般都是以page=1&size=10这种形式进行传递参数的，于是我们修改接口。
def paginate_by_page_size(objs, page=1, size=None):
"""分页查询"""
# crud.py
if (page if page else 0) > 0 and (size if size else 0) > 0:
offset_data = (page - 1) * (size if size else 0)
objs = objs.offset(offset_data).limit(size)
return objs
def query_all_projects(db: Session, user_claims, project_name='', page=1, size=None):
if user_claims.get('is_admin'):
objs = db.query(Project).order_by(desc(Project.id))
else:
objs = db.query(Project).filter_by(user_id=user_claims.get('id')).order_by(desc(Project.id))
# 过滤条件查询
objs = objs.filter(
or_(Project.project_name.contains(project_name), project_name=='')
)
# 分页查询
objs = paginate_by_page_size(objs=objs, page=page, size=size)
return {"total": objs.count(), "data": objs.all()}
@router.get("/project", response_model=AllProjectOut)
async def get_all_projects(
project_name: str = Query(default='', example='项目'), # 增加查询参数
page: int = Query(default=1, example='1'),
size: int = Query(default=10, example='10'),
Authorize: AuthJWT = Depends(),
db: Session = Depends(get_db)
):
Authorize.jwt_required()
user_claims = Authorize.get_raw_jwt()
all_projects = query_all_projects(db, user_claims, project_name, page, size)
return all_projects
