test/seed_data.py

from database import SessionLocal, engine, Base
from datetime import date, datetime


from model.teachers import Teacher
from model.counselor import Counselor
from model.classes_model import Classes
from model.student_info import Student
from model.scores import Scores
from model.employments import Employments

# Base.metadata.drop_all(bind=engine)
# Base.metadata.create_all(bind=engine)

# 教师数据
teachers_data = [
    {"t_name": "周凌云", "t_gender": "男", "t_phone": "13800000001", "t_subject": "python",
     "t_created": date(2023, 1, 1), "t_delete": False},
    {"t_name": "张显", "t_gender": "女", "t_phone": "2783263268242", "t_subject": "mysql",
     "t_created": date(2023, 1, 1), "t_delete": False},
]

# 2. 顾问数据
counselors_data = [
    {"name": "吴振玮", "c_time": date(2023, 1, 1), "c_de": False},
    {"name": "孙波", "c_time": date(2023, 1, 1), "c_de": False},
]

# 班级数据
classes_data = [
    {"class_name": "沃林0803班", "head_teacher_name": "周凌云", "counselor_name": "吴振玮", "create_time": date(2023, 1, 1), "class_delete": False},
    {"class_name": "沃林0814班", "head_teacher_name": "张显", "counselor_name": "孙波", "create_time": date(2023, 1, 1), "class_delete": False},
    {"class_name": "沃林0824班", "head_teacher_name": "周凌云", "counselor_name": "孙波", "create_time": date(2023, 1, 1), "class_delete": False},
]

#  学生数据
students_data = [
    {"id": "2024001", "name": "张伟", "gender": "男", "class_name": "沃林0803班", "age": 22, "hometown": "北京",
     "graduate_school": "北京四中", "major": "计算机科学与技术", "education": "本科",
     "enrollment_date": date(2022, 9, 1), "graduation_date": date(2026, 6, 30), "status": "未就业", "is_deleted": False},
    {"id": "2024002", "name": "王芳", "gender": "女", "class_name": "沃林0803班", "age": 23, "hometown": "上海",
     "graduate_school": "上海中学", "major": "计算机科学与技术", "education": "硕士",
     "enrollment_date": date(2022, 9, 1), "graduation_date": date(2026, 6, 30), "status": "在读", "is_deleted": False},
    {"id": "2024003", "name": "李娜", "gender": "女", "class_name": "沃林0824班", "age": 24, "hometown": "广州",
     "graduate_school": "华南师范大学附属中学", "major": "计算机科学与技术", "education": "本科",
     "enrollment_date": date(2022, 9, 1), "graduation_date": date(2026, 6, 30), "status": "未就业", "is_deleted": False},
    {"id": "2024004", "name": "刘洋", "gender": "男", "class_name": "沃林0824班", "age": 25, "hometown": "深圳",
     "graduate_school": "深圳中学", "major": "计算机科学与技术", "education": "本科",
     "enrollment_date": date(2022, 9, 1), "graduation_date": date(2026, 6, 30), "status": "已就业", "is_deleted": False},
    {"id": "2024005", "name": "陈静", "gender": "女", "class_name": "沃林0803班", "age": 26, "hometown": "杭州",
     "graduate_school": "杭州第二中学", "major": "软件工程", "education": "本科", "enrollment_date": date(2022, 9, 1),
     "graduation_date": date(2026, 6, 30), "status": "在读", "is_deleted": False},
    {"id": "2024006", "name": "杨强", "gender": "男", "class_name": "沃林0814班", "age": 27, "hometown": "成都",
     "graduate_school": "成都七中", "major": "软件工程", "education": "本科", "enrollment_date": date(2022, 9, 1),
     "graduation_date": date(2026, 6, 30), "status": "未就业", "is_deleted": False},
    {"id": "2024007", "name": "赵敏", "gender": "女", "class_name": "沃林0803班", "age": 28, "hometown": "武汉",
     "graduate_school": "武汉外国语学校", "major": "人工智能", "education": "硕士", "enrollment_date": date(2022, 9, 1),
     "graduation_date": date(2026, 6, 30), "status": "已就业", "is_deleted": False},
    {"id": "2024008", "name": "黄磊", "gender": "男", "class_name": "沃林0814班", "age": 29, "hometown": "南京",
     "graduate_school": "南京师范大学附属中学", "major": "人工智能", "education": "本科",
     "enrollment_date": date(2022, 9, 1), "graduation_date": date(2026, 6, 30), "status": "在读", "is_deleted": False},
    {"id": "2024009", "name": "周杰", "gender": "男", "class_name": "沃林0824班", "age": 30, "hometown": "西安",
     "graduate_school": "西安高新第一中学", "major": "网络工程", "education": "硕士",
     "enrollment_date": date(2022, 9, 1), "graduation_date": date(2026, 6, 30), "status": "在读", "is_deleted": False},
    {"id": "2024010", "name": "吴霞", "gender": "女", "class_name": "沃林0814班", "age": 31, "hometown": "重庆",
     "graduate_school": "重庆巴蜀中学", "major": "网络工程", "education": "本科", "enrollment_date": date(2022, 9, 1),
     "graduation_date": date(2026, 6, 30), "status": "已就业", "is_deleted": False},
]

# 5. 成绩数据
scores_data = [
    {"id": "2024001", "exam_round": 1, "score": 85, "is_warning": False, "is_deleted": False},
    {"id": "2024002", "exam_round": 1, "score": 55, "is_warning": True, "is_deleted": False},
    {"id": "2024003", "exam_round": 1, "score": 92, "is_warning": False, "is_deleted": False},
    {"id": "2024004", "exam_round": 1, "score": 78, "is_warning": False, "is_deleted": False},
    {"id": "2024005", "exam_round": 1, "score": 45, "is_warning": True, "is_deleted": False},
]

#6. 就业数据
employments_data = [
    {"id": "2024004", "offer_open": datetime(2026, 3, 1, 9, 0), "offer_review": datetime(2026, 3, 15, 14, 30), "emp": "腾讯科技", "sal": 25000.0, "is_deleted": False},
    {"id": "2024007", "offer_open": datetime(2026, 2, 10, 10, 0), "offer_review": datetime(2026, 2, 20, 16, 0), "emp": "阿里巴巴", "sal": 30000.0, "is_deleted": False},
    {"id": "2024010", "offer_open": datetime(2026, 4, 5, 9, 30), "offer_review": datetime(2026, 4, 12, 11, 0), "emp": "字节跳动", "sal": 28000.0, "is_deleted": False},
    {"id": "2024001", "offer_open": datetime(2026, 5, 1, 9, 0), "offer_review": None, "emp": None, "sal": None, "is_deleted": False},
    {"id": "2024002", "offer_open": None, "offer_review": None, "emp": None, "sal": None, "is_deleted": True},
]


def insert_data():
    db = SessionLocal()
    try:
        # 插入教师
        print("正在写入教师数据...")
        for t in teachers_data:
            if not db.query(Teacher).filter(Teacher.t_name == t["t_name"]).first():
                db.add(Teacher(**t))
        db.commit()

        # 插入顾问 (Counselor)
        print("正在写入顾问数据...")
        for c in counselors_data:
            if not db.query(Counselor).filter(Counselor.name == c["name"]).first():
                db.add(Counselor(**c))
        db.commit()

        # 3. 插入班级 (Classes)
        print("正在写入班级数据...")
        for cl in classes_data:
            if not db.query(Classes).filter(Classes.class_name == cl["class_name"]).first():
                # 查找对应老师和顾问的自增 ID
                t = db.query(Teacher).filter(Teacher.t_name == cl["head_teacher_name"]).first()
                c = db.query(Counselor).filter(Counselor.name == cl["counselor_name"]).first()
                if t and c:
                    # 注意：Classes模型有 teachers 多对多关系，这里我们只处理必需的 head_teacher_id 和 counselor_id
                    db.add(Classes(
                        class_name=cl["class_name"],
                        head_teacher_id=t.t_id,
                        counselor_id=c.id,
                        create_time=cl["create_time"],
                        class_delete=cl["class_delete"]
                    ))
                else:
                    print(f" 找不到班级 {cl['class_name']} 对应的老师或顾问，跳过！")
        db.commit()

        # --- 4. 插入学生 (Student) ---
        print("正在写入学生数据...")
        for s in students_data:
            if not db.query(Student).filter(Student.id == s["id"]).first():
                db.add(Student(**s))
        db.commit()

        # 5. 插入成绩 (Scores)
        print("正在写入成绩数据...")
        for sc in scores_data:
            if not db.query(Scores).filter(Scores.id == sc["id"], Scores.exam_round == sc["exam_round"]).first():
                db.add(Scores(**sc))
        db.commit()

        # 6. 插入就业信息 (Employments)
        print("正在写入就业数据...")
        for emp in employments_data:
            if not db.query(Employments).filter(Employments.id == emp["id"]).first():
                db.add(Employments(**emp))
        db.commit()

        # 最终统计
        print(f"\n 数据初始化完成！")
        print(f"班级: {db.query(Classes).count()} 个 | 学生: {db.query(Student).count()} 名")
        print(f"成绩: {db.query(Scores).count()} 条 | 就业: {db.query(Employments).count()} 条")

    except Exception as e:
        db.rollback()
        print(f"\n 插入失败，已回滚：{e}")
    finally:
        db.close()


if __name__ == "__main__":
    insert_data()