在教务管理系统的开发过程中学生成绩和课表管理是核心且复杂的模块。传统的手工记录方式效率低下且容易出错而市面上的成熟系统往往过于庞大不适合学校内部快速部署。本文将基于 Flask MySQL 技术栈从零构建一个轻量级的学生成绩课表管理系统涵盖需求分析、数据库设计、后端 API 开发到前端界面集成的完整流程并提供可复用的代码示例和常见问题解决方案。1. 系统需求与功能设计1.1 业务场景分析学生成绩课表管理系统主要涉及三类用户角色学生、教师和管理员。学生需要查询课表、查看成绩教师需要管理所授课程的成绩录入与修改管理员则负责课程安排、用户管理等功能。系统需解决课程冲突检查、成绩权限控制、数据一致性等典型问题。1.2 核心功能模块系统主要包含以下功能模块用户管理支持学生、教师、管理员三类角色登录和权限控制课程管理课程信息的增删改查包括课程名称、学分、授课教师等课表管理班级课表安排支持时间冲突检测选课管理学生在线选课/退课支持容量控制成绩管理教师录入/修改成绩学生查询成绩数据统计成绩分布分析、课程选修情况统计2. 技术选型与环境准备2.1 技术栈说明后端框架Flask 2.0轻量级、灵活、扩展性强数据库MySQL 8.0关系型数据库适合结构化数据存储ORMFlask-SQLAlchemy数据库操作抽象层前端HTML5 Bootstrap 5 jQuery快速构建响应式界面认证授权Flask-Login会话管理 自定义权限装饰器2.2 开发环境配置# 创建虚拟环境 python -m venv venv source venv/bin/activate # Linux/Mac venv\Scripts\activate # Windows # 安装核心依赖 pip install flask2.3.3 pip install flask-sqlalchemy3.0.5 pip install flask-login0.6.3 pip install pymysql1.1.0 pip install bootstrap-flask2.3.0 # MySQL数据库安装以Ubuntu为例 sudo apt update sudo apt install mysql-server sudo mysql_secure_installation2.3 数据库初始化-- 创建数据库 CREATE DATABASE school_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 创建用户并授权 CREATE USER school_userlocalhost IDENTIFIED BY secure_password; GRANT ALL PRIVILEGES ON school_management.* TO school_userlocalhost; FLUSH PRIVILEGES;3. 数据库设计与模型定义3.1 数据表结构设计系统主要包含以下核心表users用户基础信息表courses课程信息表classes班级信息表schedules课表安排表selections选课记录表scores成绩记录表3.2 SQLAlchemy模型定义# models.py from flask_sqlalchemy import SQLAlchemy from flask_login import UserMixin from datetime import datetime db SQLAlchemy() class User(UserMixin, db.Model): __tablename__ users id db.Column(db.Integer, primary_keyTrue) username db.Column(db.String(80), uniqueTrue, nullableFalse) password db.Column(db.String(200), nullableFalse) role db.Column(db.Enum(student, teacher, admin), nullableFalse) real_name db.Column(db.String(100), nullableFalse) created_at db.Column(db.DateTime, defaultdatetime.utcnow) class Course(db.Model): __tablename__ courses id db.Column(db.Integer, primary_keyTrue) course_code db.Column(db.String(20), uniqueTrue, nullableFalse) course_name db.Column(db.String(100), nullableFalse) credits db.Column(db.Integer, nullableFalse) teacher_id db.Column(db.Integer, db.ForeignKey(users.id)) max_students db.Column(db.Integer, default50) description db.Column(db.Text) teacher db.relationship(User, backreftaught_courses) class Schedule(db.Model): __tablename__ schedules id db.Column(db.Integer, primary_keyTrue) course_id db.Column(db.Integer, db.ForeignKey(courses.id)) class_id db.Column(db.Integer, db.ForeignKey(classes.id)) week_day db.Column(db.Integer, nullableFalse) # 1-7代表周一到周日 start_time db.Column(db.Time, nullableFalse) end_time db.Column(db.Time, nullableFalse) classroom db.Column(db.String(50)) course db.relationship(Course, backrefschedules) class Selection(db.Model): __tablename__ selections id db.Column(db.Integer, primary_keyTrue) student_id db.Column(db.Integer, db.ForeignKey(users.id)) course_id db.Column(db.Integer, db.ForeignKey(courses.id)) selected_at db.Column(db.DateTime, defaultdatetime.utcnow) student db.relationship(User, backrefcourse_selections) course db.relationship(Course, backrefstudent_selections) class Score(db.Model): __tablename__ scores id db.Column(db.Integer, primary_keyTrue) selection_id db.Column(db.Integer, db.ForeignKey(selections.id)) score db.Column(db.Float) graded_at db.Column(db.DateTime) comment db.Column(db.Text) selection db.relationship(Selection, backrefscore_record)4. Flask应用架构与配置4.1 应用工厂模式配置# app/__init__.py from flask import Flask from flask_bootstrap import Bootstrap5 from flask_login import LoginManager bootstrap Bootstrap5() login_manager LoginManager() login_manager.login_view auth.login def create_app(): app Flask(__name__) app.config[SECRET_KEY] your-secret-key-here app.config[SQLALCHEMY_DATABASE_URI] mysqlpymysql://school_user:secure_passwordlocalhost/school_management app.config[SQLALCHEMY_TRACK_MODIFICATIONS] False from app.models import db db.init_app(app) bootstrap.init_app(app) login_manager.init_app(app) # 注册蓝图 from app.auth import auth_bp from app.main import main_bp from app.courses import courses_bp from app.scores import scores_bp app.register_blueprint(auth_bp) app.register_blueprint(main_bp) app.register_blueprint(courses_bp, url_prefix/courses) app.register_blueprint(scores_bp, url_prefix/scores) return app4.2 用户认证与权限控制# app/auth.py from flask import Blueprint, render_template, redirect, url_for, flash, request from flask_login import login_user, logout_user, login_required, current_user from werkzeug.security import check_password_hash, generate_password_hash from app.models import User, db auth_bp Blueprint(auth, __name__) auth_bp.route(/login, methods[GET, POST]) def login(): if request.method POST: username request.form.get(username) password request.form.get(password) user User.query.filter_by(usernameusername).first() if user and check_password_hash(user.password, password): login_user(user) next_page request.args.get(next) return redirect(next_page or url_for(main.dashboard)) else: flash(用户名或密码错误, danger) return render_template(auth/login.html) auth_bp.route(/logout) login_required def logout(): logout_user() return redirect(url_for(auth.login)) # 权限装饰器 def role_required(role): def decorator(f): wraps(f) def decorated_function(*args, **kwargs): if not current_user.is_authenticated or current_user.role ! role: flash(权限不足, danger) return redirect(url_for(main.dashboard)) return f(*args, **kwargs) return decorated_function return decorator5. 核心功能实现5.1 课程管理模块# app/courses.py from flask import Blueprint, render_template, request, flash, redirect, url_for from flask_login import login_required, current_user from app.models import Course, Schedule, Selection, db from app.auth import role_required courses_bp Blueprint(courses, __name__) courses_bp.route(/) login_required def course_list(): page request.args.get(page, 1, typeint) courses Course.query.paginate(pagepage, per_page10) return render_template(courses/list.html, coursescourses) courses_bp.route(/int:course_id) login_required def course_detail(course_id): course Course.query.get_or_404(course_id) is_selected False if current_user.role student: is_selected Selection.query.filter_by( student_idcurrent_user.id, course_idcourse_id ).first() is not None return render_template(courses/detail.html, coursecourse, is_selectedis_selected) courses_bp.route(/select/int:course_id, methods[POST]) login_required role_required(student) def select_course(course_id): course Course.query.get_or_404(course_id) # 检查是否已选 existing_selection Selection.query.filter_by( student_idcurrent_user.id, course_idcourse_id ).first() if existing_selection: flash(您已经选修了这门课程, warning) return redirect(url_for(courses.course_detail, course_idcourse_id)) # 检查课程容量 current_count Selection.query.filter_by(course_idcourse_id).count() if current_count course.max_students: flash(该课程已满员, danger) return redirect(url_for(courses.course_detail, course_idcourse_id)) # 创建选课记录 selection Selection(student_idcurrent_user.id, course_idcourse_id) db.session.add(selection) db.session.commit() flash(选课成功, success) return redirect(url_for(courses.course_detail, course_idcourse_id))5.2 成绩管理模块# app/scores.py from flask import Blueprint, render_template, request, flash, redirect, url_for from flask_login import login_required, current_user from app.models import Score, Selection, Course, db from app.auth import role_required scores_bp Blueprint(scores, __name__) scores_bp.route(/manage/int:course_id) login_required role_required(teacher) def manage_scores(course_id): course Course.query.get_or_404(course_id) if course.teacher_id ! current_user.id: flash(无权管理此课程成绩, danger) return redirect(url_for(main.dashboard)) selections Selection.query.filter_by(course_idcourse_id).all() return render_template(scores/manage.html, coursecourse, selectionsselections) scores_bp.route(/update/int:selection_id, methods[POST]) login_required role_required(teacher) def update_score(selection_id): selection Selection.query.get_or_404(selection_id) course selection.course if course.teacher_id ! current_user.id: flash(无权修改此成绩, danger) return redirect(url_for(main.dashboard)) score_value request.form.get(score) comment request.form.get(comment, ) try: score_float float(score_value) if score_value else None if score_float is not None and (score_float 0 or score_float 100): raise ValueError(成绩必须在0-100之间) except ValueError as e: flash(f成绩格式错误: {str(e)}, danger) return redirect(url_for(scores.manage_scores, course_idcourse.id)) # 更新或创建成绩记录 score_record Score.query.filter_by(selection_idselection_id).first() if score_record: score_record.score score_float score_record.comment comment score_record.graded_at datetime.utcnow() else: score_record Score( selection_idselection_id, scorescore_float, commentcomment, graded_atdatetime.utcnow() ) db.session.add(score_record) db.session.commit() flash(成绩更新成功, success) return redirect(url_for(scores.manage_scores, course_idcourse.id))5.3 课表查询功能# app/main.py from flask import Blueprint, render_template from flask_login import login_required, current_user from app.models import Schedule, Selection from datetime import time main_bp Blueprint(main, __name__) main_bp.route(/dashboard) login_required def dashboard(): if current_user.role student: # 获取学生课表 selections Selection.query.filter_by(student_idcurrent_user.id).all() course_ids [s.course_id for s in selections] schedules Schedule.query.filter(Schedule.course_id.in_(course_ids)).all() # 按星期和时间排序整理课表 week_schedule {day: [] for day in range(1, 8)} for schedule in schedules: week_schedule[schedule.week_day].append({ course: schedule.course, start_time: schedule.start_time, end_time: schedule.end_time, classroom: schedule.classroom }) # 每个时间段按开始时间排序 for day in week_schedule: week_schedule[day].sort(keylambda x: x[start_time]) return render_template(student/dashboard.html, scheduleweek_schedule) elif current_user.role teacher: # 教师仪表盘显示所授课程 courses current_user.taught_courses return render_template(teacher/dashboard.html, coursescourses) else: # 管理员仪表盘 return render_template(admin/dashboard.html)6. 前端界面实现6.1 基础模板布局!-- templates/base.html -- !DOCTYPE html html langzh-CN head meta charsetUTF-8 meta nameviewport contentwidthdevice-width, initial-scale1.0 title{% block title %}学生成绩管理系统{% endblock %}/title {{ bootstrap.load_css() }} /head body nav classnavbar navbar-expand-lg navbar-dark bg-primary div classcontainer a classnavbar-brand href{{ url_for(main.dashboard) }}成绩管理系统/a div classnavbar-nav ms-auto {% if current_user.is_authenticated %} span classnavbar-text me-3欢迎{{ current_user.real_name }}/span a classnav-link href{{ url_for(auth.logout) }}退出/a {% else %} a classnav-link href{{ url_for(auth.login) }}登录/a {% endif %} /div /div /nav div classcontainer mt-4 {% with messages get_flashed_messages(with_categoriestrue) %} {% if messages %} {% for category, message in messages %} div classalert alert-{{ category }} alert-dismissible fade show {{ message }} button typebutton classbtn-close>!-- templates/student/dashboard.html -- {% extends base.html %} {% block title %}学生仪表盘 - 成绩管理系统{% endblock %} {% block content %} div classrow div classcol-md-12 h2我的课表/h2 div classtable-responsive table classtable table-bordered thead tr th时间/th th星期一/th th星期二/th th星期三/th th星期四/th th星期五/th th星期六/th th星期日/th /tr /thead tbody {% set time_slots [ (08:00, 09:40), (10:00, 11:40), (14:00, 15:40), (16:00, 17:40), (19:00, 20:40) ] %} {% for start_time, end_time in time_slots %} tr td{{ start_time }}br{{ end_time }}/td {% for day in range(1, 8) %} td {% for item in schedule[day] %} {% if item.start_time|string start_time :00 %} div classcourse-item strong{{ item.course.course_name }}/strongbr {{ item.classroom }}br {{ item.course.teacher.real_name }} /div {% endif %} {% endfor %} /td {% endfor %} /tr {% endfor %} /tbody /table /div /div /div div classrow mt-4 div classcol-md-6 h3我的课程/h3 div classlist-group a href{{ url_for(courses.course_list) }} classlist-group-item list-group-item-action 查看所有课程 /a /div /div /div {% endblock %}7. 系统部署与配置7.1 生产环境配置# config.py import os class Config: SECRET_KEY os.environ.get(SECRET_KEY) or dev-secret-key SQLALCHEMY_DATABASE_URI os.environ.get(DATABASE_URL) or \ mysqlpymysql://school_user:secure_passwordlocalhost/school_management SQLALCHEMY_TRACK_MODIFICATIONS False class ProductionConfig(Config): DEBUG False # 生产环境特定配置 class DevelopmentConfig(Config): DEBUG True # 开发环境配置7.2 Gunicorn部署配置# gunicorn_config.py bind 0.0.0.0:5000 workers 4 worker_class sync accesslog - errorlog -8. 常见问题与解决方案8.1 数据库连接问题问题现象启动应用时报错2003: Cant connect to MySQL server on localhost解决方案检查MySQL服务是否启动sudo systemctl status mysql验证数据库连接参数是否正确检查防火墙设置确保3306端口可访问8.2 权限控制问题问题现象用户越权访问其他角色功能解决方案在所有路由添加login_required装饰器使用自定义的role_required装饰器进行细粒度控制在模板中根据用户角色显示不同的功能菜单8.3 性能优化建议数据库查询优化使用lazydynamic避免N1查询问题添加适当的数据库索引CREATE INDEX idx_selection_student ON selections(student_id); CREATE INDEX idx_selection_course ON selections(course_id); CREATE INDEX idx_score_selection ON scores(selection_id);使用Flask-Caching对静态数据进行缓存8.4 安全注意事项密码使用werkzeug.security进行哈希处理所有用户输入进行验证和转义防止SQL注入和XSS攻击生产环境使用HTTPS设置安全的Cookie选项定期备份数据库制定数据恢复预案9. 系统扩展与优化方向9.1 功能扩展建议添加消息通知系统及时向学生发送成绩发布通知实现数据导入导出功能支持Excel格式的成绩单开发移动端适配界面方便手机访问添加数据统计分析模块生成学习报告9.2 技术架构优化考虑使用Redis缓存热门数据提升系统响应速度实现API版本管理为未来移动应用开发做准备添加日志记录和监控系统便于故障排查使用Celery实现异步任务处理如邮件发送本系统采用FlaskMySQL技术栈实现了学生成绩课表管理的核心功能代码结构清晰易于理解和扩展。在实际部署时请根据具体需求调整配置参数并做好数据备份和安全防护工作。
