57 lines
2.1 KiB
Python
57 lines
2.1 KiB
Python
from sqlalchemy_fastapi_demo_1.model.Score import Score
|
|
from sqlalchemy_fastapi_demo_1.model.students import Student
|
|
from sqlalchemy.orm import Session
|
|
from sqlalchemy_fastapi_demo_1.scheme.Score import ScoreCreate,ScoreUpdate
|
|
|
|
class ScoreDAO:
|
|
"""用户数据访问对象，所有方法均为静态方法，方便调用"""
|
|
|
|
@staticmethod
|
|
def inspect_stu_id_unq(db:Session,stu_id:int):
|
|
return db.query(Student).filter(Student.stu_id == stu_id,Student.is_deleted==0).first()
|
|
|
|
|
|
# 查所有数据
|
|
@staticmethod
|
|
def get_all(db:Session,skip:int=0,limit:int=100):
|
|
return db.query(Score).offset(skip).limit(limit).all()
|
|
|
|
# 查询单个数据
|
|
@staticmethod
|
|
def get_by_id(db:Session,stu_id:int,exam_id:int):
|
|
return db.query(Score).filter(Score.stu_id == stu_id,Score.exam_id==exam_id).first()
|
|
|
|
# 添加数据
|
|
@staticmethod
|
|
def post_score(db:Session,score_create:ScoreCreate):
|
|
# 将 Pydantic 模型转为字典，并解包构建 SQLAlchemy 模型实例
|
|
db_score=Score(**score_create.model_dump())
|
|
db.add(db_score) #添加到会话
|
|
db.commit() #提交事务，此时会执行 INSERT，并自动填充自增字段
|
|
db.refresh(db_score) #刷新对象，获取数据库生成的默认值（如 created_at）
|
|
return db_score
|
|
|
|
# 更改数据
|
|
@staticmethod
|
|
def put_score(db:Session,score_id:int,exam_id:int,score_update:ScoreUpdate):
|
|
db_score=ScoreDAO.get_by_id(db,score_id,exam_id)
|
|
if not db_score:
|
|
return None
|
|
# 只更新客户端显式传入的字段（exclude_unset=True 排除未设置的字段）
|
|
put_score_data = score_update.model_dump(exclude_unset=True)
|
|
for k,v in put_score_data.items():
|
|
setattr(db_score,k,v) # 动态设置属性
|
|
db.commit()
|
|
db.refresh(db_score)
|
|
return db_score
|
|
|
|
# 删除
|
|
@staticmethod
|
|
def delete_score(db:Session,score_id:int,exam_id:int):
|
|
db_score=ScoreDAO.get_by_id(db,score_id,exam_id)
|
|
if not db_score:
|
|
return False
|
|
db.delete(db_score)
|
|
db.commit()
|
|
return True
|