# main.py
import data_manager
import stats_manager
import visualization
import exception_handler
def main():
"""主程序入口"""
while True:
print("\n" + "=" * 50)
print("学生成绩数据分析系统")
print("=" * 50)
print("1. 学生成绩管理")
print("2. 成绩统计分析")
print("3. 数据可视化展示")
print("4. 退出系统")
print("=" * 50)
try:
choice = int(input("请选择操作(1-4): "))
if choice == 1:
data_manager.manage_students()
elif choice == 2:
stats_manager.analyze_data()
elif choice == 3:
visualization.display_stats()
elif choice == 4:
print("\n感谢使用系统，再见！")
break
else:
print("输入错误，请选择1-4之间的数字！")
except ValueError:
print("输入错误，请输入数字！")
except Exception as e:
exception_handler.handle_db_error(e)
if __name__ == "__main__":
print("=" * 50)
print("欢迎使用学生成绩数据分析系统")
print("系统初始化中...")
# 确保数据库表存在
data_manager.create_table()
print("系统初始化完成！")
print("=" * 50)
main()
# data_manager.py
import mysql.connector
from mysql.connector import Error
from config import DB_CONFIG
from exception_handler import handle_db_error, validate_score, validate_student_id
def create_connection():
"""创建数据库连接"""
try:
connection = mysql.connector.connect(**DB_CONFIG)
return connection
except Error as e:
handle_db_error(e)
return None
def create_table():
"""创建学生数据表"""
connection = create_connection()
if connection is None:
return False
try:
cursor = connection.cursor()
create_table_query = """
CREATE TABLE IF NOT EXISTS students (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
math FLOAT NOT NULL,
chinese FLOAT NOT NULL,
english FLOAT NOT NULL
)
"""
cursor.execute(create_table_query)
connection.commit()
return True
except Error as e:
handle_db_error(e)
return False
finally:
if connection.is_connected():
cursor.close()
connection.close()
def load_data():
"""加载所有学生数据"""
connection = create_connection()
if connection is None:
return []
students = []
try:
cursor = connection.cursor(dictionary=True)
select_query = "SELECT * FROM students ORDER BY id"
cursor.execute(select_query)
students = cursor.fetchall()
except Error as e:
handle_db_error(e)
finally:
if connection.is_connected():
cursor.close()
connection.close()
return students
def display_students(students):
"""显示学生数据表格"""
if not students:
print("\n没有找到学生数据")
return
print("\n{:<5} {:<10} {:<8} {:<8} {:<8}".format("ID", "姓名", "数学", "语文", "英语"))
print("-" * 40)
for student in students:
print("{:<5} {:<10} {:<8.1f} {:<8.1f} {:<8.1f}".format(
student['id'], student['name'],
student['math'], student['chinese'], student['english']
))
def add_student(student):
"""添加学生数据"""
connection = create_connection()
if connection is None:
return False
try:
cursor = connection.cursor()
add_query = """
INSERT INTO students (id, name, math, chinese, english)
VALUES (%s, %s, %s, %s, %s)
"""
student_data = (
student['id'],
student['name'],
student['math'],
student['chinese'],
student['english']
)
cursor.execute(add_query, student_data)
connection.commit()
return True
except Error as e:
handle_db_error(e)
return False
finally:
if connection.is_connected():
cursor.close()
connection.close()
def add_student_ui():
"""添加学生UI交互"""
print("\n--- 添加学生成绩 ---")
try:
student_id = validate_student_id(input("请输入学生ID: "))
name = input("请输入学生姓名: ").strip()
math = validate_score(input("请输入数学成绩: "))
chinese = validate_score(input("请输入语文成绩: "))
english = validate_score(input("请输入英语成绩: "))
student = {
'id': student_id,
'name': name,
'math': math,
'chinese': chinese,
'english': english
}
if add_student(student):
print(f"成功添加学生: {name} (ID: {student_id})")
else:
print("添加学生失败")
except ValueError as e:
print(f"输入错误: {str(e)}")
def modify_student(student_id, updated_data):
"""修改学生数据"""
connection = create_connection()
if connection is None:
return False
try:
cursor = connection.cursor()
update_query = """
UPDATE students
SET name = %s, math = %s, chinese = %s, english = %s
WHERE id = %s
"""
update_data = (
updated_data['name'],
updated_data['math'],
updated_data['chinese'],
updated_data['english'],
student_id
)
cursor.execute(update_query, update_data)
connection.commit()
return cursor.rowcount > 0
except Error as e:
handle_db_error(e)
return False
finally:
if connection.is_connected():
cursor.close()
connection.close()
def modify_student_ui():
"""修改学生UI交互"""
print("\n--- 修改学生成绩 ---")
try:
student_id = validate_student_id(input("请输入要修改的学生ID: "))
name = input("请输入新姓名 (留空保持不变): ").strip()
math = input("请输入新数学成绩 (留空保持不变): ").strip()
chinese = input("请输入新语文成绩 (留空保持不变): ").strip()
english = input("请输入新英语成绩 (留空保持不变): ").strip()
# 获取当前数据
students = load_data()
current = next((s for s in students if s['id'] == student_id), None)
if not current:
print("未找到该学生")
return
# 准备更新数据
updated_data = {
'name': name if name else current['name'],
'math': validate_score(math) if math else current['math'],
'chinese': validate_score(chinese) if chinese else current['chinese'],
'english': validate_score(english) if english else current['english']
}
if modify_student(student_id, updated_data):
print(f"成功更新学生: {updated_data['name']} (ID: {student_id})")
else:
print("更新学生失败")
except ValueError as e:
print(f"输入错误: {str(e)}")
def delete_student(student_id):
"""删除学生数据"""
connection = create_connection()
if connection is None:
return False
try:
cursor = connection.cursor()
delete_query = "DELETE FROM students WHERE id = %s"
cursor.execute(delete_query, (student_id,))
connection.commit()
return cursor.rowcount > 0
except Error as e:
handle_db_error(e)
return False
finally:
if connection.is_connected():
cursor.close()
connection.close()
def delete_student_ui():
"""删除学生UI交互"""
print("\n--- 删除学生成绩 ---")
try:
student_id = validate_student_id(input("请输入要删除的学生ID: "))
if delete_student(student_id):
print(f"成功删除ID为 {student_id} 的学生")
else:
print("删除失败，可能该学生不存在")
except ValueError as e:
print(f"输入错误: {str(e)}")
def manage_students():
"""学生成绩管理主界面"""
# 确保表存在
create_table()
while True:
print("\n" + "-" * 30)
print("学生成绩管理")
print("-" * 30)
print("1. 查看所有学生成绩")
print("2. 添加学生成绩")
print("3. 修改学生成绩")
print("4. 删除学生成绩")
print("5. 返回主菜单")
print("-" * 30)
choice = input("请选择操作(1-5): ").strip()
if choice == "1":
students = load_data()
display_students(students)
elif choice == "2":
add_student_ui()
elif choice == "3":
modify_student_ui()
elif choice == "4":
delete_student_ui()
elif choice == "5":
break
else:
print("输入错误，请选择1-5之间的数字！")# config.py
DB_CONFIG = {
'host': 'localhost',
'user': 'root',
'password': '123456', # 请替换为实际密码
'database': 'student_db',
'port': 3306
}
# exception_handler.py
import mysql.connector
from mysql.connector import Error
def handle_db_error(e):
"""处理数据库操作异常"""
error_msg = f"数据库错误: {str(e)}"
if isinstance(e, Error):
if e.errno == 2003:
error_msg = "无法连接到数据库服务器，请检查服务是否运行"
elif e.errno == 1049:
error_msg = "数据库不存在，请先创建数据库"
elif e.errno == 1045:
error_msg = "数据库用户名或密码错误"
print(f"错误: {error_msg}")
return error_msg
def validate_score(score):
"""验证分数有效性"""
try:
score = float(score)
if 0 <= score <= 100:
return score
raise ValueError("分数必须在0-100之间")
except (ValueError, TypeError):
raise ValueError("请输入有效的数字分数")
def validate_student_id(student_id):
"""验证学生ID有效性"""
if not str(student_id).isdigit():
raise ValueError("学生ID必须是数字")
return int(student_id)
# visualization.py
from stats_manager import analyze_data
def display_stats():
"""数据可视化展示（文本形式）"""
print("\n" + "=" * 50)
print("数据可视化展示")
print("=" * 50)
# 调用统计模块获取数据
analyze_data()
# 文本形式可视化
print("\n" + "=" * 50)
print("成绩分布可视化 (文本形式)")
print("=" * 50)
# 简单的文本直方图
bins = [0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100]
counts = [0] * 10
# 获取所有成绩
from data_manager import load_data
students = load_data()
if not students:
print("没有数据可供可视化")
return
all_scores = []
for s in students:
all_scores.extend([s['math'], s['chinese'], s['english']])
# 计算各分数段人数
for score in all_scores:
for i in range(10):
if bins[i] <= score < bins[i + 1]:
counts[i] += 1
break
# 打印文本直方图
print("\n成绩分布直方图 (每*代表2人):")
for i in range(10):
bar = '*' * (counts[i] // 2)
print(f"{bins[i]}-{bins[i + 1]}: {bar} ({counts[i]}人)")
print("\n提示: 此为文本形式可视化，后续可扩展使用matplotlib进行图形化展示")
def plot_stats():
"""预留的图形化可视化接口"""
print("\n" + "=" * 50)
print("高级数据可视化 (预留接口)")
print("=" * 50)
print("提示: 此功能需要安装matplotlib，将在后续课程中实现")
print("当前仅提供文本形式可视化，请使用选项3查看")
# stats_manager.py
from data_manager import load_data
def calculate_average(scores):
"""计算平均分"""
return sum(scores) / len(scores) if scores else 0
def calculate_pass_rate(scores):
"""计算及格率 (>=60)"""
if not scores:
return 0
passed = sum(1 for score in scores if score >= 60)
return (passed / len(scores)) * 100
def analyze_data():
"""成绩统计分析主界面"""
students = load_data()
if not students:
print("\n没有学生成绩数据，请先添加数据")
return
# 提取各科成绩
math_scores = [s['math'] for s in students]
chinese_scores = [s['chinese'] for s in students]
english_scores = [s['english'] for s in students]
# 计算统计指标
stats = {
'math': {
'avg': calculate_average(math_scores),
'max': max(math_scores),
'min': min(math_scores),
'pass_rate': calculate_pass_rate(math_scores)
},
'chinese': {
'avg': calculate_average(chinese_scores),
'max': max(chinese_scores),
'min': min(chinese_scores),
'pass_rate': calculate_pass_rate(chinese_scores)
},
'english': {
'avg': calculate_average(english_scores),
'max': max(english_scores),
'min': min(english_scores),
'pass_rate': calculate_pass_rate(english_scores)
}
}
# 显示统计结果
print("\n" + "=" * 40)
print("成绩统计分析结果")
print("=" * 40)
print("{:<8} {:<10} {:<10} {:<10} {:<10}".format(
"科目", "平均分", "最高分", "最低分", "及格率"
))
print("-" * 40)
for subject, data in stats.items():
subject_name = "数学" if subject == 'math' else "语文" if subject == 'chinese' else "英语"
print("{:<8} {:<10.1f} {:<10.1f} {:<10.1f} {:<10.1f}%".format(
subject_name, data['avg'], data['max'], data['min'], data['pass_rate']
))
print("\n统计完成！共分析 {} 名学生成绩".format(len(students)))
本文介绍了一种使用方方格子插件在Excel中快速对比两行数据的方法，通过单区域列内对比，可以轻松找到未达到满分的成绩。
扫一扫
2247
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
