查看: 33|回复: 3

Python+SQLite学生成绩管理:聚合统计与CSV导出BOM

[复制链接]
发表于 12 分钟前 | 显示全部楼层 |阅读模式
一个班几十号人,三科成绩,期末要算班级平均分和及格率。用 Excel 也能算,但每次录入新成绩都要重新拉公式,还担心别人改了哪个格子。下面用 Python 标准库从零实现一个能跑起来的小系统:录入、按班级统计、导出 CSV,整个项目只有一个 app.py,零第三方依赖。服务用 http.server,数据存 sqlite3,导出用 csv。默认从 8100 端口启动,首次运行自动建表并灌 3 条演示数据,数据库文件是 scores.db,删掉重启就是全新状态。

一、需求拆解与表设计

需求可以拆成三件事:录入姓名、班级、学号和三科分数,学号可以自动生成;按班级统计人数、平均分、及格率(≥60 分)、最高分和最低分;导出一份 CSV,教务处双击用 Excel 打开不能乱码。

数据量不大,但结构不能将就。把班级名直接写成学生表里的字符串字段,一开始能跑,等班级改名或要统计班级本身属性时就会难受。所以直接拆成三张表:
  1. CREATE TABLE IF NOT EXISTS classes (
  2.   id INTEGER PRIMARY KEY AUTOINCREMENT,
  3.   name TEXT NOT NULL UNIQUE,
  4.   grade TEXT NOT NULL DEFAULT ''
  5. );
  6. CREATE TABLE IF NOT EXISTS students (
  7.   id INTEGER PRIMARY KEY AUTOINCREMENT,
  8.   name TEXT NOT NULL,
  9.   sno TEXT NOT NULL UNIQUE,
  10.   class_id INTEGER NOT NULL,
  11.   FOREIGN KEY (class_id) REFERENCES classes(id)
  12. );
  13. CREATE TABLE IF NOT EXISTS scores (
  14.   student_id INTEGER PRIMARY KEY,
  15.   chinese REAL NOT NULL DEFAULT 0,
  16.   math REAL NOT NULL DEFAULT 0,
  17.   english REAL NOT NULL DEFAULT 0,
  18.   FOREIGN KEY (student_id) REFERENCES students(id)
  19. );
复制代码

classes 存班级本身的名字和年级,班级改名只改这一行,不用扫学生表。students 只存“人”和“这个人属于哪个班”,不存成绩,转班只改 class_id。scores 单独一张,主键就是 student_id,将来加物理、化学加列即可,不用动学生表;缺考的学生可以暂时没有成绩行,用 LEFT JOIN 出来按 0 分处理。拆开的代价是查询要 JOIN,但这正是 SQL 擅长的事:
  1. SELECT c.name AS klass,
  2.        COUNT(s.id) AS cnt,
  3.        AVG(sc.chinese + sc.math + sc.english) AS avg_total,
  4.        MAX(sc.chinese + sc.math + sc.english) AS max_total,
  5.        MIN(sc.chinese + sc.math + sc.english) AS min_total,
  6.        SUM(CASE WHEN sc.chinese >= 60 AND sc.math >= 60 AND sc.english >= 60
  7.                 THEN 1 ELSE 0 END) AS passed
  8. FROM classes c
  9. LEFT JOIN students s ON s.class_id = c.id
  10. LEFT JOIN scores sc ON sc.student_id = s.id
  11. GROUP BY c.id, c.name
  12. ORDER BY c.name
复制代码

这段 SQL 是系统的核心:平均分、及格率、最高最低分全部在一次查询里算完,没有把数据捞到 Python 里循环。LEFT JOIN 是刻意的,新建成还没录成绩的班级也要出现在统计里,人数为 0,不能因为没成绩就整班消失。

二、关键实现细节

1. 统计用 SQL 聚合,不在 Python 里循环

最初是把学生列表捞出来在 Python 里 sum() 和 len(),问题是修改一条成绩后,页面统计要么忘记重算,要么重算了但顺序错。改成 SQL 聚合后,每次请求 /api/stats 都直接问数据库,天然不会过期:
  1. def class_stats():
  2.     conn = get_conn()
  3.     try:
  4.         rows = conn.execute(
  5.             '''
  6.             SELECT c.name AS klass, COUNT(s.id) AS cnt,
  7.                    AVG(sc.chinese + sc.math + sc.english) AS avg_total,
  8.                    SUM(CASE WHEN sc.chinese >= ? AND sc.math >= ? AND sc.english >= ?
  9.                             THEN 1 ELSE 0 END) AS passed
  10.             FROM classes c
  11.             LEFT JOIN students s ON s.class_id = c.id
  12.             LEFT JOIN scores sc ON sc.student_id = s.id
  13.             GROUP BY c.id, c.name
  14.             ''',
  15.             (PASS_SCORE, PASS_SCORE, PASS_SCORE),
  16.         ).fetchall()
复制代码

这里用参数传 PASS_SCORE,而不是把 60 写死在 SQL 里,后面调整及格线时只改一处。统计不缓存,数据量小,多查一次的开销远小于数据不一致带来的困扰。

2. 及格率要防除零

SUM(...) / COUNT(*) 在班级人数为 0 时会抛异常,所以除之前先判断:
  1. cnt = r['cnt'] or 0
  2. passed = r['passed'] or 0
  3. 'pass_rate': round(passed * 100.0 / cnt, 1) if cnt else 0.0
复制代码

这个 if cnt else 0.0 不是防御性编程的洁癖。刚建完班、还没录入学生时刷新页面,就真的会走到这条分支。

3. 录入时班级不存在要自动建

三表结构下,录一个“高一(3)班”的学生时,classes 表里可能还没有这一行,直接插 students 会因为没有对应 class_id 而失败。所以录入函数里先查后建:
  1. row = conn.execute('SELECT id FROM classes WHERE name=?', (klass,)).fetchone()
  2. if row:
  3.     class_id = row['id']
  4. else:
  5.     cur_c = conn.execute(
  6.         'INSERT INTO classes(name, grade) VALUES (?,?)',
  7.         (klass, klass[:2] if len(klass) >= 2 else klass),
  8.     )
  9.     class_id = cur_c.lastrowid
  10. cur = conn.execute(
  11.     'INSERT INTO students(name, sno, class_id) VALUES (?,?,?)',
  12.     (name, sno, class_id),
  13. )
  14. sid = cur.lastrowid
  15. conn.execute(
  16.     'INSERT INTO scores(student_id, chinese, math, english) VALUES (?,?,?,?)',
  17.     (sid, grades[0], grades[1], grades[2]),
  18. )
复制代码

注意这里是两次 INSERT,不是一次。三表拆开后写一条学生数据必然涉及两张表,所以必须放在同一个事务里,中途失败要能整体回滚,不能留下有学生没成绩的半条数据。

4. CSV 导出必须带 BOM

这是导出功能唯一的坑,也是最重要的一个:
  1. def export_csv_bytes():
  2.     buf = io.StringIO(newline='')
  3.     w = csv.writer(buf)
  4.     w.writerow(['学号', '姓名', '班级', '语文', '数学', '英语', '总分'])
  5.     for s in list_students():
  6.         w.writerow([s['sno'], s['name'], s['klass'],
  7.                     s['chinese'], s['math'], s['english'], s['total']])
  8.     return buf.getvalue().encode('utf-8-sig')
复制代码

关键是最后那行 encode('utf-8-sig')。用普通 utf-8 编码,中文内容在 Excel 里打开就是乱码,因为 Excel 不会去猜 UTF-8,它默认按本地代码页解码,中文 Windows 上就是 GBK。加上 BOM 之后,Excel 看到文件头 EF BB BF 就知道这是 UTF-8。另外 io.StringIO(newline='') 里的 newline='' 也不能省,否则 csv.writer 写出来的每行会多一个空行。验证方法是看文件前三个字节:
  1. body[:3] == b'\xef\xbb\xbf'  # True 才算带上 BOM
复制代码

5. 端口占用自动顺延

开发时经常忘了上次的服务还在跑,Address already in use 一报就得手动改端口。所以启动前先探测:
  1. def pick_port(host, start, tries=20):
  2.     for p in range(start, start + tries):
  3.         if not port_in_use(host, p):
  4.             return p
  5.     raise RuntimeError('没有可用端口')
  6. def port_in_use(host, port):
  7.     with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
  8.         s.settimeout(0.3)
  9.         return s.connect_ex((host, port)) == 0
复制代码

默认从 8100 起,避开产品自身占用的 8899,被占用就往后找 20 个。

6. 服务端校验,不只靠前端

前端把三科成绩输入框设成 type='number' min='0' max='100',但那只是体验,不是防线,curl 一个 POST 就能绕过。所以服务端照样校验:
  1. for k in SUBJECTS:
  2.     v = payload.get(k, 0)
  3.     try:
  4.         v = float(v)
  5.     except (TypeError, ValueError):
  6.         raise ValueError('%s 成绩必须是数字' % SUBJECT_LABELS[k])
  7.     if v < 0 or v > 100:
  8.         raise ValueError('%s 成绩需在 0-100 之间' % SUBJECT_LABELS[k])
复制代码

这两道校验各有各的用途:前端拦住手滑,服务端拦住脚本。

三、实际踩过的坑

坑 1:CSV 中文全是乱码。第一版导出直接 encode('utf-8'),自己用文本编辑器打开完全正常,交给教务处用 Excel 一开全是 å¼ ä¼Ÿ 这种。折腾了半天以为是内容有问题,其实是 Excel 根本没按 UTF-8 解码。改成 utf-8-sig 后正常。教训是:“我这儿能打开”不等于“用户那儿能打开”。

坑 2:统计数字看起来对,其实是过期的。早期版本统计在页面加载时算一次,存成全局变量。改完分数点保存,表格里的分数变了,但上面的及格率还是老的,因为统计函数没被重新调用。用户看到“改了分但及格率没变”,会以为保存失败。修法是干脆不缓存,/api/stats 每次请求都现场查库聚合。

坑 3:AVG 遇到 NULL 会把整行吞掉。AVG(sc.chinese + sc.math + sc.english) 里只要有一个是 NULL,整个加法表达式就是 NULL,AVG 会直接跳过这一行,不报错,只是悄悄少算一个人。现象是班级 3 个人,平均分却像是按 2 个人算的。最后定位到某条记录的成绩列是 NULL,早期建表时没写 DEFAULT 0。两个修法都做了:建表时给三科都加 NOT NULL DEFAULT 0;查询里用 COALESCE(sc.chinese, 0) 兜底。聚合函数遇到 NULL 从来不报错,它只是默默算错,这比抛异常难查得多。

坑 4:三表拆分后,改分改到了不存在的学生。改成三表结构后,改分接口从 UPDATE students SET chinese=? 变成了 UPDATE scores SET chinese=? WHERE student_id=?。测试时传了一个不存在的 id,接口居然返回 200 成功。原因是代码里有个“成绩行不存在就先插入”的逻辑,它给一个根本不存在的 student_id 建了一行孤儿成绩,然后 UPDATE 自然命中一行,rowcount == 1,返回成功。修法是先查学生存不存在,再动成绩表:
  1. who = conn.execute('SELECT id FROM students WHERE id = ?', (sid,)).fetchone()
  2. if who is None:
  3.     raise ValueError('学生不存在:id=%s' % sid)
复制代码

这个坑典型的点在于:拆表之后,“操作成功”的判断依据变了。单表时代 UPDATE students 的 rowcount 天然能证明学生存在;拆表之后,rowcount 只证明“成绩表里有一行被改了”,跟学生存不存在没关系了。

坑 5:Content-Length 没写导致浏览器一直转圈。自定义 BaseHTTPRequestHandler 时,如果手动 wfile.write(body) 却不写 Content-Length 头,浏览器会一直等待连接关闭来判断内容结束,表现为页面卡在加载中。curl 反而正常,因为它读到 EOF 就返回。所以每次响应都要显式写:
  1. self.send_header('Content-Length', str(len(body)))
复制代码

这个坑只在浏览器里复现,光用 curl 测接口是发现不了的。

四、小结与后续方向

这个系统总共 600 多行,零第三方依赖。回过头看,真正花时间的不是写功能,而是几个“看起来能跑”的细节:CSV 的 BOM 不加就是乱码;SQL 聚合里的 NULL 不报错只是算错;拆表之后的 rowcount 证明的东西和单表时代完全不是一回事;Content-Length 缺失时 curl 能过但浏览器卡死。

表设计一句话总结:班级、学生、成绩是三件不同的事,就该是三张表。合在一张表里能跑,但每次需求变化,比如改班级名、加科目、转班,都要改数据本身,那才是真正的技术债。代码在 app.py 一个文件里,python app.py 启动,默认 8100 端口,数据库就是 scores.db。

如果继续做下去,下一步大概是:加分科目配置表,现在三科是写死的列;加成绩导入,现在只能一条条录;加历史考试批次,现在只有当前成绩,没有“期中”“期末”的区分。第三个尤其重要,真实场景下成绩一定按考试批次存,这也是表设计从三张变四张的地方。
回复

使用道具 举报

发表于 7 分钟前 | 显示全部楼层

Re: Python+SQLite学生成绩管理:聚合统计与CSV导出BOM

这个方案挺适合班级小工具,三表拆分和 SQL 聚合都是对的方向,尤其把及格线做成参数、统计不缓存、LEFT JOIN 保留空班级这些细节,实际维护时省不少事。CSV 导出用带 BOM 的 UTF-8 也很贴近教务处双击打开的场景。 有个统计口径可以再确认一下:帖子里说缺成绩行按 0 分处理,但当前 AVG(sc.chinese + sc.math + sc.english) 遇到 NULL 会跳过该学生,COUNT(s.id) 仍会把人数算进去,这样平均分分母和“按 0 分处理”的说法可能不一致。若真要把缺考按 0 分计入平均分,聚合表达式可以用 COALESCE(sc.chinese,0)+COALESCE(sc.math,0)+COALESCE(sc.english,0),及格判断也相应处理;若只是缺考不参与平均分,那就在说明里明确口径。 另外录入时班级不存在自动建,建议放在事务里,先按 name 查,没有就插入,再拿 id 写学生,避免唯一约束冲突后处理麻烦。整体单文件零依赖很适合上手,期待后面导出和页面部分。
回复 支持 反对

使用道具 举报

发表于 7 分钟前 | 显示全部楼层

Re: Python+SQLite学生成绩管理:聚合统计与CSV导出BOM

三表拆分的思路是对的,班级名不冗余进学生表、成绩单独一张、缺考可以没有成绩行,这几点考虑得比较长远。不过有几个坑想提醒一下。 第一,SQLite 的外键约束默认是关闭的。建表时写的 FOREIGN KEY 在没执行 PRAGMA foreign_keys = ON 之前基本等于注释,删班级、删学生的时候不会拦你,容易留下孤儿成绩行。建议在 get_conn 里每次连接后都把这个 PRAGMA 打开。 第二,“缺考按 0 分处理”和现在这条 SQL 的实际行为有出入。LEFT JOIN 出来 sc.chinese 是 NULL,NULL + NULL + NULL 还是 NULL,AVG 会直接跳过这一行;但 COUNT(s.id) 又把这名学生算进了人数,于是平均分的分母和人数统计对不上,MAX/MIN 也会忽略他。CASE WHEN NULL >= 60 结果为 NULL 会走 ELSE 0,这部分倒是确实按不及格算了,所以口径是不统一的。要真正当 0 分,得在聚合里套 COALESCE(sc.chinese, 0) 之类的,或者录成绩时就写 0 而不是留空行。 第三,及格率口径最好明确一下。现在这版是三科全部过线才算及格,但教务处常说的“及格率”有可能是单科及格率,也可能是总分及格率,数字对不上容易被追问。在返回里把几个口径都算出来,或者至少在页面上标清楚定义,会省很多解释成本
回复 支持 反对

使用道具 举报

发表于 7 分钟前 | 显示全部楼层

Re: Python+SQLite学生成绩管理:聚合统计与CSV导出BOM

楼主这套拆表思路很稳,班级、学生、成绩分开后,改名、转班和以后加科目都舒服很多;统计全交给 SQL 聚合也比在 Python 里循环靠谱,参数化及格线和防除零这些细节都考虑到了。首帖最后一段看起来还没写完,自动建班那里建议用 INSERT OR IGNORE 再按 name 查回 id,这样能避开班级名 UNIQUE 的冲突。 有个地方值得再确认一下:LEFT JOIN 缺成绩行时,AVG(sc.chinese + sc.math + sc.english) 会忽略 NULL,而及格率的 CASE 会把缺考算成不及格,这和“按 0 分处理”不完全一致。如果缺考要按 0 分进平均分,聚合里得用 COALESCE 包一下;如果缺考不计入平均分,最好在统计口径里写清楚。 CSV 导出用 utf-8-sig 写 BOM,配 newline='' 打开文件,Excel 双击基本不会乱码也不会多空行。整体作为一个零依赖的内网小工具很合适。
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

指导单位

江苏省公安厅

江苏省通信管理局

浙江省台州刑侦支队

DEFCON GROUP 86025

Hacking Group 021A

旗下站点

态势感知中心

应急响应中心

红盟安全

联系我们

官方QQ群:112851260

官方邮箱:security#ihonker.org(#改成@)

官方核心成员

关注微信公众号

Archiver|手机版|小黑屋| ( 沪ICP备2021026908号 )

GMT+8, 2026-11-20 05:12 , Processed in 0.021830 second(s), 18 queries , Gzip On, Redis On.

Powered by ihonker.com

Copyright © 2015-现在.

  • 返回顶部