项目环境
开发环境：python3.12、
开发工具：VS Code
数据库：mysql5.6
运行环境：Windows 11
数据库连接
我利用的是pymysql库对数据库进行操作，首先是和数据库建立连接
#导入pymysql模块，用于连接数据库
import pymysql
# 连接参数
connection_params = {
'host': 'localhost', # 指定数据库服务器的主机地址
'user': 'root', # 指定用于连接数据库的用户名
'password': '123456', # 指定与用户名root关联的密码
'charset': 'utf8mb4' # 指定数据库连接使用的字符集
}
#创建数据库连接
try:
connection = pymysql.connect(**connection_params)
#创建游标
with connection.cursor() as cursor:
create_db_sql = 'create database if not exists students'
cursor.execute(create_db_sql)
connection.select_db('students')
print('数据库连接成功')
except pymysql.MySQLError as e:
print(f'创建失败:{e}')
finally:
connection.close()
表的建立
student表存储学生个人信息，student_score表存储学生成绩信息
user表存储用户的账户信息，teacher表存储老师的个人信息
#创建student表
create_student_table_sql ='''
create table if not exists student(
student_id int auto_increment,
name varchar(20) not null,
sex enum('男','女') not null,
classname varchar(20) not null,
primary key (student_id)
);
'''
cursor.execute(create_student_table_sql)
connection.commit()
print('student表创建成功')
#创建成绩表
create_student_score_table_sql ='''
create table if not exists student_score(
student_id int not null,
china float(2) not null,
english float(2) not null,
math float(2) not null,
total float(2) not null,
average float(2) not null
);
'''
cursor.execute(create_student_score_table_sql)
connection.commit()
print('student_score表创建成功')
#创建用户表
create_user_table_sql = '''
create table if not exists user(
user_id int auto_increment,
username varchar(20) not null,
password varchar(20) not null,
user_type enum('老师','学生') not null,
primary key (user_id)
);'''
cursor.execute(create_user_table_sql)
connection.commit()
print('user表创建成功')
#创建老师表
create_teacher_table_sql = '''
create table if not exists teacher(
teacher_id int auto_increment,
name varchar(20) not null,
sex enum('男','女') not null,
telephone varchar(20) not null,
primary key (teacher_id)
);'''
注册
注册分为两种角色，一个是老师，一个是学生，首先根据用户输入的数字判断注册用户是老师还是学生
#注册
def register(connection):
try:
#连接数据库
connection = pymysql.connect(**connection_params)
connection.select_db("students")
with connection.cursor() as cursor:
print('请选择注册方式:1.老师注册 2.学生注册')
c_num = int(input())
if c_num == 1:
#老师注册
print('请老师注册:')
username = input('请输入用户名:')
password = input('请输入密码:')
insert_sql ="insert into user(username,password,user_type) values('{}','{}','{}');".format(username,password,'老师')
cursor.execute(insert_sql)
connection.commit()
print('注册成功')
return True
elif c_num == 2:
#学生注册
print('请学生注册:')
username = input('请输入用户名:')
password = input('请输入密码:')
insert_sql ="insert into user(username,password,user_type) values('{}','{}','{}');".format(username,password,'学生')
cursor.execute(insert_sql)
connection.commit()
print('注册成功')
return True
else:
print('注册失败')
return False
except pymysql.MySQLError as e:
print(f'注册失败:{e}')
return False
finally:
connection.close()
登录
#登录
def main():
#登录
print('欢迎来到学生管理系统')
while True:
print('请选择登录方式:1.登录 2.注册 0.退出')
c_num = int(input())
if c_num == 1:
username = input('请输入用户名:')
password = input('请输入密码:')
user_type = input('请输入用户类型:')
#连接数据库
try:
connection = pymysql.connect(**connection_params)
connection.select_db('students')
with connection.cursor() as cursor:
#查询用户是否存在
select_user_sql = "select * from user where username = '{}' and password = '{}' and user_type ='{}';".format(username,password,user_type)
cursor.execute(select_user_sql)
result = cursor.fetchone()
if result:
print('登录成功')
if user_type == "学生":
while True:
print("欢迎来到学生界面")
print('1.查询学生个人信息')
print('2.查询学生个人成绩')
print('0.退出')
choice = input('请选择:')
if choice == '1':
query_student_all(connection)
continue
elif choice == '2':
query_student_score(connection)
continue
elif choice == '0':
print('退出成功')
return False
else:
print('输入错误，请重新输入')
continue
elif user_type == "老师":
while True:
print("欢迎来到老师界面")
print('1.添加学生个人信息')
print('2.添加学生成绩')
print('3.修改学生个人信息')
print('4.修改学生成绩分数')
print('5.学生成绩统计与分析')
print('6.删除学生个人信息和成绩')
print('0.退出')
choice = input('请选择:')
if choice == '1':
insert_student(connection)
continue
elif choice == '2':
insert_student_score(connection)
continue
elif choice == '3':
change_student(connection)
continue
elif choice == '4':
change_student_score(connection)
continue
elif choice == '5':
student_score_statistics(connection)
continue
elif choice == '6':
delete_student_all(connection)
continue
elif choice == '0':
print('退出成功')
return False
else:
print('输入错误，请重新输入')
continue
return True
else:
print('用户名或密码错误，请重新输入')
continue
except pymysql.MySQLError as e:
print(f'登录失败:{e}')
finally:
connection.close()
elif c_num == 2:
connection = pymysql.connect(**connection_params)
connection.select_db('students')
register(connection)
continue
elif c_num == 0:
print('退出成功')
return False
else:
print('输入错误，请重新输入')
continue
添加老师信息
#添加老师个人信息
def insert_teacher(connection):
"""
插入老师个人信息到teacher表
参数：
connection:数据库连接
name:老师姓名
sex:老师性别
telephone:老师电话
"""
try:
connection = pymysql.connect(**connection_params)
connection.select_db('students')
with connection.cursor() as cursor:
print('请输入老师信息:')
name = input('姓名:')
sex = input('性别:')
telephone = input('电话:')
insert_sql = "insert into teacher(name,sex,telephone) values('{}','{}','{}');".format(name,sex,telephone)
cursor.execute(insert_sql)
connection.commit()
print('添加成功')
except pymysql.MySQLError as e:
print(f'添加失败:{e}')
return False
finally:
connection.close()
添加学生个人信息
#添加学生个人信息
def insert_student(connection):
"""
插入学生个人信息到student表
参数：
connection:数据库连接
name:学生姓名
sex:学生性别
classname:学生班级
"""
try:
#连接数据库
connection = pymysql.connect(**connection_params)
connection.select_db('students')
with connection.cursor() as cursor:
#输入学生信息
print('请输入学生信息:')
name = input('姓名:')
sex = input('性别:')
class_name = input('班级:')
#执行插入操作
insert_sql = "insert into student(name,sex,classname) values('{}','{}','{}');".format(name,sex,class_name)
cursor.execute(insert_sql)
connection.commit()
#获取学生id
last_row_id = cursor.lastrowid
#查询新插入记录的所有字段和值，并返回
select_sql = "select * from student where student_id = '{}';".format(last_row_id)
cursor.execute(select_sql)
new_student = cursor.fetchone()
print(new_student)
print('插入成功')
return True
except pymysql.MySQLError as e:
print(f'插入失败:{e}')
return False
finally:
connection.close()
添加学生个人成绩
#添加学生成绩
def insert_student_score(connection):
"""
插入学生成绩到student_score表
参数：
connection:数据库连接
student_id:学生id
name:学生姓名
china:语文成绩
english:英语成绩
math:数学成绩
total:总成绩
average:平均成绩
"""
#连接数据库
connection = pymysql.connect(**connection_params)
connection.select_db('students')
with connection.cursor() as cursor:
try:
#输入学生成绩
print('请输入学生姓名')
name = input('姓名:')
#查询学生表中学生id
select_student_name_sql = "select student_id from student where name = '{}';".format(name)
cursor.execute(select_student_name_sql)
result = cursor.fetchone()
#判断学生是否存在
if result:
student_id = result[0]
#检测学生成绩是否存在
check_score_sql = "select * from student_score where student_id = '{}';".format(student_id)
cursor.execute(check_score_sql)
existing_score = cursor.fetchone()
if existing_score:
print("该学生已存在成绩")
else:
print("请输入学生成绩:")
china = float(input('语文成绩:'))
english = float(input('英语成绩:'))
math = float(input('数学成绩:'))
total = float(china + english + math)
average = float(total / 3)
#执行插入操作
insert_student_score_sql = "insert into student_score(student_id,china,english,math,total,average) values('{}','{}','{}','{}','{:.2f}','{:.2f}');".format(student_id,china,english,math,total,average)
cursor.execute(insert_student_score_sql)
connection.commit()
print('插入成功')
return True
except pymysql.MySQLError as e:
print(f'插入失败:{e}')
return False
finally:
connection.close()
修改学生个人信息
#修改学生个人信息
def change_student(connection):
'''
修改学生表中的信息
参数：
connection:数据库连接
id:学生id
name:学生姓名
sex:学生性别
classname:学生班级
'''
#连接数据库
connection = pymysql.connect(**connection_params)
connection.select_db('students')
with connection.cursor() as cursor:
#修改学生信息
try:
# 获取学生 ID
student_id = input('请输入学生ID:')
# 查询学生信息
find_student_sql = "SELECT * FROM student WHERE student_id = {};".format(student_id)
with connection.cursor() as cursor:
# 查询学生信息
cursor.execute(find_student_sql)
result = cursor.fetchone()
if result:
student_id = result[0]
#修改学生信息
name = input('请输入修改后的姓名:')
sex = input('请输入修改后的性别:')
class_name = input('请输入修改后的班级:')
change_student_sql = 'update student set name = "{}",sex = "{}",classname = "{}" where student_id = "{}";'.format(name,sex,class_name,student_id)
cursor.execute(change_student_sql)
connection.commit()
change_student_score_sql = '''update student_score
inner join student
on student_score.student_id = student.student_id
set student.student_id =student_score.student_id;'''
cursor.execute(change_student_score_sql)
connection.commit()
print('修改成功')
return True
else:
print('学生id不存在,请检查id是否存在。')
return False
except pymysql.MySQLError as e:
print(f'修改失败:{e}')
return False
finally:
connection.close()
修改学生成绩
#修改学生成绩分数
def change_student_score(connection):
'''
修改学生成绩表中的信息
参数：
connection:数据库连接
student_id:学生id
name:学生姓名
china:语文成绩
english:英语成绩
math:数学成绩
total:总成绩
average:平均成绩
'''
#连接数据库
connection = pymysql.connect(**connection_params)
connection.select_db('students')
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
try:
#修改学生成绩
name = input('请输入要修改成绩的姓名:')
find_student_id_sql = """select student.student_id as student_id,
student_score.china as china,
student_score.english as english,
student_score.math as math,
student_score.total as total,
student_score.average as average
from student
inner join student_score
on student.student_id = student_score.student_id
where name = '{}';""".format(name)
cursor.execute(find_student_id_sql)
result = cursor.fetchone()
if result is not None:
print(f"原始成绩：{result}")
while True:
print("1.语文成绩")
print("2.英语成绩")
print("3.数学成绩")
print("0.无需修改")
i = int(input("请输入要修改的学科数字:"))
if i == 1:
china = float(input('请输入修改后的语文成绩:'))
updata_sql = "update student_score set china = '{}' where student_id = '{}';".format(china,result['student_id'])
cursor.execute(updata_sql)
connection.commit()
total = china + result['english'] + result['math']
new_average = total / 3
updata_sql = "update student_score set total = '{}',average = '{:.2f}' where student_id = '{}';".format(total,new_average,result['student_id'])
cursor.execute(updata_sql)
connection.commit()
print('修改成功')
continue
elif i == 2:
english = float(input('请输入修改后的英语成绩:'))
updata_sql = "update student_score set english = '{}' where student_id = '{}';".format(english,result['student_id'])
cursor.execute(updata_sql)
connection.commit()
total = result['china'] + english + result['math']
new_average = total / 3
updata_sql = "update student_score set total = '{}',average = '{:.2f}' where student_id = '{}';".format(total,new_average,result['student_id'])
cursor.execute(updata_sql)
connection.commit()
print('修改成功')
continue
elif i == 3:
math = float(input('请输入修改后的数学成绩:'))
updata_sql = "update student_score set math = '{}' where student_id = '{}';".format(math,result['student_id'])
cursor.execute(updata_sql)
connection.commit()
total = result['china'] + result['english'] + math
new_average = total / 3
updata_sql = "update student_score set total = '{}',average = '{:.2f}' where student_id = '{}';".format(total,new_average,result['student_id'])
cursor.execute(updata_sql)
connection.commit()
print('修改成功')
continue
elif i == 0:
break
else:
print('输入错误,请重新输入。')
continue
print('修改成功')
return True
else:
print('学生不存在,请检查姓名是否正确。')
return False
except pymysql.MySQLError as e:
print(f'修改失败:{e}')
return False
finally:
connection.close()
学生成绩统计与分析
#学生成绩统计与分析
def student_score_statistics(connection):
'''
学生成绩统计与分析
参数：
connection:数据库连接
'''
#连接数据库
connection = pymysql.connect(**connection_params)
connection.select_db('students')
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
try:
student_score_all_sql ='''
select student.name as name,
student.classname as classname,
student_score.china as china,
student_score.english as english,
student_score.math as math,
student_score.total as total
from student
join student_score
on student.student_id =student_score.student_id
where china< 60 or english<60 or math<60;'''
cursor.execute(student_score_all_sql)
result = cursor.fetchall()
for i in result:
print(i)
connection.commit()
print('统计成功')
except pymysql.MySQLError as e:
print(f'分析失败:{e}')
finally:
connection.close()
查询学生个人成绩
#查询学生个人的成绩
def query_student_score(connection):
'''
查询学生个人的成绩
参数：
connection:数据库连接
student_table(student_id,name,sex,classname)
student_score_table(student_id,china,english,math,total,average)
student_id:学生id
name:学生姓名
ranking:排名
'''
#连接数据库
connection = pymysql.connect(**connection_params)
connection.select_db('students')
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
try:
name = input('请输入学生姓名:')
all_student_score_sql = '''
select student.student_id as student_id,
student.name,
student_score.china,
student_score.english,
student_score.math,
student_score.total,
student_score.average
from student
inner join student_score
on student.student_id = student_score.student_id
order by student_score.total desc;'''
cursor.execute(all_student_score_sql)
students = cursor.fetchall()
for index,student in enumerate(students,start = 1):
student['ranking'] = index
target = None
for student in students:
if student['name'] == name:
target = student
break
print(target)
print("查询成功")
return target
except pymysql.MySQLError as e:
print(f'查询失败:{e}')
finally:
connection.close()
查询学生个人信息和成绩
#查询学生个人信息和成绩
def query_student_all(connection):
'''
查询学生个人信息和成绩
参数：
connection:数据库连接
input_id:输入的学生id
student_name:输入的学生姓名
'''
#连接数据库
connection = pymysql.connect(**connection_params)
connection.select_db('students')
with connection.cursor() as cursor:
try:
name = input('请输入学生姓名:')
find_student_id_sql = "select student_id from student where name = '{}';".format(name)
cursor.execute(find_student_id_sql)
#将主表中的学生id赋值给result
result = cursor.fetchone()
if result:
#student_id = result[0]
#查询学生个人信息和成绩的sql语句
student_all_sql = '''
select *
from student
join student_score
on student.student_id = student_score.student_id
where name="{}";''' .format(name)
cursor.execute(student_all_sql)
connection.commit()
student_all = cursor.fetchone()
print(student_all)
print("查询成功")
return True
else:
print("学生不存在,请检查姓名是否正确。")
return False
except pymysql.MySQLError as e:
print(f'查询失败:{e}')
finally:
connection.close()
删除学生个人信息
#删除学生个人信息
def delete_student_all(connection):
'''
删除学生个人信息
参数：
connection:数据库连接
student_name:输入的学生姓名
'''
#连接数据库
connection = pymysql.connect(**connection_params)
connection.select_db('students')
with connection.cursor() as cursor:
try:
name = input('请输入学生姓名:')
find_student_id_sql = "select student_id from student where name = '{}';".format(name)
cursor.execute(find_student_id_sql)
#将主表中的学生信息赋值给result
result = cursor.fetchone()
if result:
student_id = result[0]
delete_student_score_sql = '''
-- 删除学生成绩表中的记录
DELETE s
FROM student_score s
join student st ON s.student_id = st.student_id
WHERE st.student_id = '{}';
'''.format(student_id)
cursor.execute(delete_student_score_sql)
connection.commit()
delete_student_sql = '''
-- 删除学生表中的记录
DELETE s
FROM student s
WHERE s.student_id = '{}';
'''.format(student_id)
cursor.execute(delete_student_sql)
connection.commit()
print("删除成功")
return True
else:
print("学生不存在,请检查姓名是否正确。")
return False
except pymysql.MySQLError as e:
print(f'删除失败:{e}')
finally:
connection.close()
总结：该系统基本实现了对数据库的增删改查操作，功能相对完善，也有不足。在开始写该项目时，没有真正的进行需求分析步骤，数据库设计不够规范，导致后续对数据的处理更加复杂了，所以项目开始前要尽可能对项目进行需求分析，对数据库表的设计要满足规范性、可维护性等。
如有问题请私信。。。
扫一扫
761
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
