import sqlite3
from datetime import datetime
import sys
class SportsMeetSystem:
def __init__(self):
self.conn = sqlite3.connect('sports_meet.db')
self.cursor = self.conn.cursor()
self.create_tables()
def create_tables(self):
"""创建数据库表"""
self.cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
gender TEXT NOT NULL,
class TEXT NOT NULL,
contact TEXT
)
''')
self.cursor.execute('''
CREATE TABLE IF NOT EXISTS events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
event_name TEXT NOT NULL,
event_type TEXT NOT NULL, # 田赛/径赛/球类等
gender_limit TEXT, # 男子/女子/不限
max_participants INTEGER,
start_time DATETIME,
location TEXT
)
''')
self.cursor.execute('''
CREATE TABLE IF NOT EXISTS registrations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id TEXT NOT NULL,
event_id INTEGER NOT NULL,
register_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) REFERENCES students (student_id),
FOREIGN KEY (event_id) REFERENCES events (id)
)
''')
self.cursor.execute('''
CREATE TABLE IF NOT EXISTS results (
id INTEGER PRIMARY KEY AUTOINCREMENT,
registration_id INTEGER NOT NULL,
score TEXT, # 成绩/结果
ranking INTEGER,
remarks TEXT,
FOREIGN KEY (registration_id) REFERENCES registrations (id)
)
''')
self.conn.commit()
def add_student(self):
"""添加学生信息"""
print("\n添加学生信息")
student_id = input("学号: ")
name = input("姓名: ")
gender = input("性别(男/女): ")
class_name = input("班级: ")
contact = input("联系方式(可选): ")
try:
self.cursor.execute(
"INSERT INTO students (student_id, name, gender, class, contact) VALUES (?, ?, ?, ?, ?)",
(student_id, name, gender, class_name, contact)
)
self.conn.commit()
print("学生信息添加成功!")
except sqlite3.IntegrityError:
print("错误: 该学号已存在!")
def add_event(self):
"""添加运动项目"""
print("\n添加运动项目")
event_name = input("项目名称: ")
event_type = input("项目类型(田赛/径赛/球类等): ")
gender_limit = input("性别限制(男子/女子/不限): ")
max_participants = input("最大参赛人数(可选): ")
start_time = input("开始时间(YYYY-MM-DD HH:MM,可选): ")
location = input("比赛地点: ")
try:
self.cursor.execute(
"INSERT INTO events (event_name, event_type, gender_limit, max_participants, start_time, location) VALUES (?, ?, ?, ?, ?, ?)",
(event_name, event_type, gender_limit, max_participants or None, start_time or None, location)
)
self.conn.commit()
print("运动项目添加成功!")
except Exception as e:
print(f"添加失败: {e}")
def register_for_event(self):
"""学生报名参赛"""
print("\n学生报名参赛")
student_id = input("请输入学号: ")
event_id = input("请输入项目ID: ")
self.cursor.execute("SELECT id FROM students WHERE student_id=?", (student_id,))
if not self.cursor.fetchone():
print("错误: 该学号不存在!")
return
self.cursor.execute("SELECT id FROM events WHERE id=?", (event_id,))
if not self.cursor.fetchone():
print("错误: 该项目不存在!")
return
self.cursor.execute(
"SELECT id FROM registrations WHERE student_id=? AND event_id=?",
(student_id, event_id)
)
if self.cursor.fetchone():
print("错误: 该学生已报名此项目!")
return
try:
self.cursor.execute(
"INSERT INTO registrations (student_id, event_id) VALUES (?, ?)",
(student_id, event_id)
)
self.conn.commit()
print("报名成功!")
except Exception as e:
print(f"报名失败: {e}")
def record_result(self):
"""记录比赛成绩"""
print("\n记录比赛成绩")
registration_id = input("请输入报名记录ID: ")
score = input("请输入成绩/结果: ")
ranking = input("请输入名次(可选): ")
remarks = input("请输入备注(可选): ")
try:
self.cursor.execute(
"INSERT INTO results (registration_id, score, ranking, remarks) VALUES (?, ?, ?, ?)",
(registration_id, score, ranking or None, remarks or None)
)
self.conn.commit()
print("成绩记录成功!")
except Exception as e:
print(f"记录失败: {e}")
def query_students(self):
"""查询学生信息"""
print("\n学生信息查询")
keyword = input("请输入学号或姓名(留空查询所有): ")
if keyword:
self.cursor.execute(
"SELECT * FROM students WHERE student_id LIKE ? OR name LIKE ?",
(f"%{keyword}%", f"%{keyword}%")
)
else:
self.cursor.execute("SELECT * FROM students")
students = self.cursor.fetchall()
if not students:
print("没有找到匹配的学生记录")
return
print("\n学生列表:")
print("ID\t学号\t姓名\t性别\t班级\t联系方式")
for student in students:
print(f"{student[0]}\t{student[1]}\t{student[2]}\t{student[3]}\t{student[4]}\t{student[5] or '无'}")
def query_events(self):
"""查询运动项目"""
print("\n运动项目查询")
keyword = input("请输入项目名称或类型(留空查询所有): ")
if keyword:
self.cursor.execute(
"SELECT * FROM events WHERE event_name LIKE ? OR event_type LIKE ?",
(f"%{keyword}%", f"%{keyword}%")
)
else:
self.cursor.execute("SELECT * FROM events")
events = self.cursor.fetchall()
if not events:
print("没有找到匹配的运动项目")
return
print("\n运动项目列表:")
print("ID\t项目名称\t类型\t性别限制\t最大人数\t开始时间\t地点")
for event in events:
print(f"{event[0]}\t{event[1]}\t{event[2]}\t{event[3]}\t{event[4] or '无'}\t{event[5] or '未定'}\t{event[6]}")
def query_registrations(self):
"""查询报名情况"""
print("\n报名情况查询")
event_id = input("请输入项目ID(留空查询所有): ")
if event_id:
self.cursor.execute('''
SELECT r.id, s.student_id, s.name, s.class, e.event_name
FROM registrations r
JOIN students s ON r.student_id = s.student_id
JOIN events e ON r.event_id = e.id
WHERE r.event_id=?
''', (event_id,))
else:
self.cursor.execute('''
SELECT r.id, s.student_id, s.name, s.class, e.event_name
FROM registrations r
JOIN students s ON r.student_id = s.student_id
JOIN events e ON r.event_id = e.id
''')
registrations = self.cursor.fetchall()
if not registrations:
print("没有找到报名记录")
return
print("\n报名记录:")
print("报名ID\t学号\t姓名\t班级\t项目名称")
for reg in registrations:
print(f"{reg[0]}\t{reg[1]}\t{reg[2]}\t{reg[3]}\t{reg[4]}")
def query_results(self):
"""查询比赛结果"""
print("\n比赛结果查询")
event_id = input("请输入项目ID(留空查询所有): ")
if event_id:
self.cursor.execute('''
SELECT res.id, s.name, s.class, e.event_name, res.score, res.ranking, res.remarks
FROM results res
JOIN registrations reg ON res.registration_id = reg.id
JOIN students s ON reg.student_id = s.student_id
JOIN events e ON reg.event_id = e.id
WHERE reg.event_id=?
''', (event_id,))
else:
self.cursor.execute('''
SELECT res.id, s.name, s.class, e.event_name, res.score, res.ranking, res.remarks
FROM results res
JOIN registrations reg ON res.registration_id = reg.id
JOIN students s ON reg.student_id = s.student_id
JOIN events e ON reg.event_id = e.id
''')
results = self.cursor.fetchall()
if not results:
print("没有找到比赛结果记录")
return
print("\n比赛结果:")
print("记录ID\t姓名\t班级\t项目名称\t成绩\t名次\t备注")
for res in results:
print(f"{res[0]}\t{res[1]}\t{res[2]}\t{res[3]}\t{res[4]}\t{res[5] or '无'}\t{res[6] or '无'}")
def generate_report(self):
"""生成班级积分报表"""
print("\n班级积分报表")
self.cursor.execute('''
SELECT s.class, SUM(
CASE
WHEN r.ranking = 1 THEN 9
WHEN r.ranking = 2 THEN 7
WHEN r.ranking = 3 THEN 6
WHEN r.ranking = 4 THEN 5
WHEN r.ranking = 5 THEN 4
WHEN r.ranking = 6 THEN 3
WHEN r.ranking = 7 THEN 2
WHEN r.ranking = 8 THEN 1
ELSE 0
END
) as total_score
FROM results r
JOIN registrations reg ON r.registration_id = reg.id
JOIN students s ON reg.student_id = s.student_id
GROUP BY s.class
ORDER BY total_score DESC
''')
class_scores = self.cursor.fetchall()
if not class_scores:
print("没有找到积分数据")
return
print("\n班级积分排名:")
print("排名\t班级\t总积分")
for rank, (class_name, score) in enumerate(class_scores, start=1):
print(f"{rank}\t{class_name}\t{score}")
def menu(self):
"""显示主菜单"""
while True:
print("\n学校运动会编排系统")
print("1. 添加学生信息")
print("2. 添加运动项目")
print("3. 学生报名参赛")
print("4. 记录比赛成绩")
print("5. 查询学生信息")
print("6. 查询运动项目")
print("7. 查询报名情况")
print("8. 查询比赛结果")
print("9. 生成班级积分报表")
print("0. 退出系统")
choice = input("请选择操作: ")
if choice == "1":
self.add_student()
elif choice == "2":
self.add_event()
elif choice == "3":
self.register_for_event()
elif choice == "4":
self.record_result()
elif choice == "5":
self.query_students()
elif choice == "6":
self.query_events()
elif choice == "7":
self.query_registrations()
elif choice == "8":
self.query_results()
elif choice == "9":
self.generate_report()
elif choice == "0":
print("感谢使用运动会编排系统，再见!")
self.conn.close()
sys.exit()
else:
print("无效的选择，请重新输入!")
if __name__ == "__main__":
system = SportsMeetSystem()
system.menu()