Flask 成绩管理系统（Web）

from flask import Flask, render_template, request, redirect, session, jsonify
import sqlite3
import os

app = Flask(__name__)
app.secret_key = os.urandom(24).hex()
DB_PATH = 'grades.db'

def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

# ── 登录 ──
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        role = request.form['role']  # 'teacher' or 'student'
        db = get_db()
        user = db.execute(
            'SELECT * FROM users WHERE username=? AND password=? AND role=?',
            (username, password, role)
        ).fetchone()
        db.close()
        if user:
            session['user_id'] = user['id']
            session['username'] = user['username']
            session['role'] = user['role']
            return redirect('/dashboard')
        return render_template('login.html', error='用户名或密码错误')
    return render_template('login.html')

# ── 成绩 CRUD ──
@app.route('/grades')
def list_grades():
    if 'user_id' not in session:
        return redirect('/login')
    search = request.args.get('q', '')
    db = get_db()
    if search:
        grades = db.execute(
            'SELECT * FROM grades WHERE student_name LIKE ? OR student_id LIKE ?',
            (f'%{search}%', f'%{search}%')
        ).fetchall()
    else:
        grades = db.execute('SELECT * FROM grades ORDER BY student_id').fetchall()
    db.close()
    return render_template('grades.html', grades=grades, search=search)

@app.route('/grade/add', methods=['POST'])
def add_grade():
    if session.get('role') != 'teacher':
        return jsonify({'error': '无权限'}), 403
    db = get_db()
    db.execute('INSERT INTO grades (student_id, student_name, course, score) VALUES (?,?,?,?)',
        (request.form['student_id'], request.form['student_name'],
         request.form['course'], float(request.form['score'])))
    db.commit()
    db.close()
    return jsonify({'ok': True})

# ── 统计 ──
@app.route('/api/stats')
def stats():
    db = get_db()
    stats = db.execute('''
        SELECT COUNT(*) as count, ROUND(AVG(score),1) as avg,
               MAX(score) as max, MIN(score) as min,
               ROUND(SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as pass_rate
        FROM grades''').fetchone()
    distribution = db.execute('''
        SELECT CASE
            WHEN score>=90 THEN '90-100'
            WHEN score>=80 THEN '80-89'
            WHEN score>=70 THEN '70-79'
            WHEN score>=60 THEN '60-69'
            ELSE '0-59' END as range,
            COUNT(*) as count
        FROM grades GROUP BY range ORDER BY range DESC''').fetchall()
    db.close()
    return jsonify({
        'stats': dict(stats),
        'distribution': [dict(r) for r in distribution]
    })