GitHub Copilot CLI 体验

from flask import Flask, g, render_template, request, redirect, url_for
import sqlite3
import os

BASE_DIR = os.path.dirname(__file__)
DB_PATH = os.path.join(BASE_DIR, 'student.db')

app = Flask(__name__, instance_path=BASE_DIR)

def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DB_PATH)
        db.row_factory = sqlite3.Row
    return db

def init_db():
    db = get_db()
    cur = db.cursor()
    cur.execute('''
        CREATE TABLE IF NOT EXISTS students (
            id TEXT PRIMARY KEY,
            name TEXT,
            gender TEXT,
            birth TEXT,
            address TEXT,
            phone TEXT,
            email TEXT
        )
    ''')
    cur.execute('''
        CREATE TABLE IF NOT EXISTS courses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            sid TEXT,
            cid TEXT,
            name TEXT,
            credit REAL
        )
    ''')
    cur.execute('''
        CREATE TABLE IF NOT EXISTS scores (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ssid TEXT,
            sname TEXT,
            scid TEXT,
            cname TEXT,
            score REAL
        )
    ''')
    db.commit()

@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

with app.app_context():
    init_db()

@app.route('/')
def index():
    return render_template('index.html')

# Students
@app.route('/students')
def students():
    db = get_db()
    cur = db.execute('SELECT * FROM students ORDER BY id')
    rows = cur.fetchall()
    return render_template('students.html', students=rows)

@app.route('/students/add', methods=['GET', 'POST'])
def add_student():
    if request.method == 'POST':
        form = request.form
        db = get_db()
        try:
            db.execute('INSERT INTO students(id,name,gender,birth,address,phone,email) VALUES (?,?,?,?,?,?,?)',
                       (form['id'], form['name'], form.get('gender',''), form.get('birth',''), form.get('address',''), form.get('phone',''), form.get('email','')))
            db.commit()
        except sqlite3.IntegrityError:
            return '学号已存在', 400
        return redirect(url_for('students'))
    return render_template('student_form.html', student=None)

@app.route('/students/edit/<sid>', methods=['GET','POST'])
def edit_student(sid):
    db = get_db()
    if request.method == 'POST':
        form = request.form
        db.execute('UPDATE students SET id=?,name=?,gender=?,birth=?,address=?,phone=?,email=? WHERE id=?',
                   (form['id'], form['name'], form.get('gender',''), form.get('birth',''), form.get('address',''), form.get('phone',''), form.get('email',''), sid))
        db.commit()
        return redirect(url_for('students'))
    cur = db.execute('SELECT * FROM students WHERE id=?', (sid,))
    student = cur.fetchone()
    return render_template('student_form.html', student=student)

@app.route('/students/delete/<sid>', methods=['POST'])
def delete_student(sid):
    db = get_db()
    db.execute('DELETE FROM students WHERE id=?', (sid,))
    db.execute('DELETE FROM courses WHERE sid=?', (sid,))
    db.execute('DELETE FROM scores WHERE ssid=?', (sid,))
    db.commit()
    return redirect(url_for('students'))

# Courses
@app.route('/courses')
def courses():
    db = get_db()
    cur = db.execute('SELECT * FROM courses ORDER BY sid, cid')
    rows = cur.fetchall()
    return render_template('courses.html', courses=rows)

@app.route('/courses/add', methods=['GET','POST'])
def add_course():
    if request.method == 'POST':
        f = request.form
        db = get_db()
        db.execute('INSERT INTO courses(sid,cid,name,credit) VALUES (?,?,?,?)', (f['sid'], f['cid'], f['name'], float(f.get('credit',0))))
        db.commit()
        return redirect(url_for('courses'))
    return render_template('course_form.html', course=None)

@app.route('/courses/delete/<int:id>', methods=['POST'])
def delete_course(id):
    db = get_db()
    # delete related scores
    cur = db.execute('SELECT sid,cid,name FROM courses WHERE id=?', (id,))
    row = cur.fetchone()
    if row:
        db.execute('DELETE FROM scores WHERE scid=? AND ssid=?', (row['cid'], row['sid']))
    db.execute('DELETE FROM courses WHERE id=?', (id,))
    db.commit()
    return redirect(url_for('courses'))

# Scores
@app.route('/scores')
def scores():
    db = get_db()
    cur = db.execute('SELECT * FROM scores ORDER BY ssid')
    rows = cur.fetchall()
    return render_template('scores.html', scores=rows)

@app.route('/scores/add', methods=['GET','POST'])
def add_score():
    if request.method == 'POST':
        f = request.form
        db = get_db()
        db.execute('INSERT INTO scores(ssid,sname,scid,cname,score) VALUES (?,?,?,?,?)', (f['ssid'], f['sname'], f['scid'], f['cname'], float(f.get('score',0))))
        db.commit()
        return redirect(url_for('scores'))
    return render_template('score_form.html', score=None)

@app.route('/scores/delete/<int:id>', methods=['POST'])
def delete_score(id):
    db = get_db()
    db.execute('DELETE FROM scores WHERE id=?', (id,))
    db.commit()
    return redirect(url_for('scores'))

# Stats
@app.route('/stats')
def stats():
    sid = request.args.get('sid')
    db = get_db()
    if not sid:
        return render_template('stats.html', sid=None, stats=None)
    # calculate avg, total credits passed
    cur = db.execute('SELECT score, scid FROM scores WHERE ssid=?', (sid,))
    rows = cur.fetchall()
    if not rows:
        return render_template('stats.html', sid=sid, stats=None)
    total = 0.0
    cnt = 0
    credits = 0.0
    for r in rows:
        total += r['score']
        cnt += 1
        if r['score'] >= 60:
            c = db.execute('SELECT credit FROM courses WHERE sid=? AND cid=?', (sid, r['scid'])).fetchone()
            if c:
                credits += c['credit']
    avg = total / cnt if cnt else 0
    return render_template('stats.html', sid=sid, stats={'avg':avg,'credits':credits,'count':cnt})

# Course ranking
@app.route('/rank/<cid>')
def rank(cid):
    db = get_db()
    cur = db.execute('SELECT sname, score FROM scores WHERE scid=? ORDER BY score DESC', (cid,))
    rows = cur.fetchall()
    return render_template('rank.html', cid=cid, rows=rows)

if __name__ == '__main__':
    app.run(host='127.0.0.1', port=5000, debug=True)