学员管理系统
环境：windows或者Linux，python3.6，mysql5.7
要求：
用户角色，讲师＼学员， 用户登陆后根据角色不同，能做的事情不同，分别如下
讲师视图
管理班级，可创建班级，根据学员qq号把学员加入班级
可创建指定班级的上课纪录，注意一节上课纪录对应多条学员的上课纪录， 即每节课都有整班学员上， 为了纪录每位学员的学习成绩，需在创建每节上课纪录是，同时 为这个班的每位学员创建一条上课纪录
为学员批改成绩， 一条一条的手动修改成绩
学员视图
提交作业
查看作业成绩
一个学员可以同时属于多个班级，就像报了Linux的同时也可以报名Python一样， 所以提交作业时需先选择班级，再选择具体上课的节数
附加：学员可以查看自己的班级成绩排名
项目结构：
rpc_client ---|
bin ---|
start.py ......启动
conf---|
config.py ......配置参数
system.ini ......配置文件
core---|
entrance.py ......登录认证的方法
main.py ......学生视图，导师视图
db ---|
mysqldb.py ......连接执行mysql
数据库结构：
student：
id , name姓名 , password密码 , qq
teacher:
id , name , password , qq
class:
id , class_name 班级名称
class_record:
id , class_id , course_num 班级课程节数 # class_id与表class中的id字段做外键关联
score：
id , class_record_id(class_record表中的id) , student_id(student表中的id) , socre(成绩) , sign(作业提交标志，0为未提交，1为提交)
# class_record_id与表class_record中的id字段做外键关联，student_id与表student中的id字段做外键关联
class_union_student (学生与班级多对多关联表)
id , class_id , student_id
# student_id与表student中的id字段做外键关联，class_id与表class中的id字段做外键关联
用法：
启动start.py
为方便测试，自动注册两个学员和一个老师，学员1账号,密码，qq号为 zh,123,123456,学员2为zs,123,654321，老师账号密码:alex,123
qq号作为加入班级是需要输入的
老师视图：
"创建班级"：输入班级名称创建班级（在class中添加记录）
"加入班级"：输入学员qq号加入班级（可添加多个，用,隔开）（在class_union_student中添加记录）
"创建上课记录"：创建上课记录，同时给学员创建学习记录（class_record 中添加一条记录，score中添加一条记录）
"批改成绩"：需要在学员提交了作业后才可批改成绩（在score中修改score成绩记录）
学生视图：
"提交作业"：在创建了上课记录后学员即可提交作业（在score中修改sign标志）
"查看成绩"：在老师批改成绩后学员可查看成绩
"查看班级排名"：在老师批改成绩后学员可查看班级排名
输入b返回上一级
bin:
#!/usr/bin/env python # -*-coding:utf-8-*- # Author:zh import os import sys PATH = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) sys.path.append(PATH) from core import entrance from db import mysqldb mysqldb.create_table() mysqldb.test() # 辅助注册用户，方便测试 entrance.run()
conf:
#!/usr/bin/env python # -*-coding:utf-8-*- # _author_=zh import os import configparser PATH = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) class Configuration(object): def __init__(self): self.config = configparser.ConfigParser() self.name = PATH+os.sep+"conf"+os.sep+"system.ini" def init_config(self): # 初始化配置文件，ip ：客户端IP，port：客户端端口 if not os.path.exists(self.name): self.config["config"] = {"host": '192.168.200.128', "port": 3306, "user": 'root', "passwd": '123456', "db": 'mysql', "charset": 'utf8'} self.config.write(open(self.name, "w", encoding="utf-8", )) def get_config(self, head="config"): ''' 获取配置文件数据 :param head: 配置文件的section，默认取初始化文件config的数据 :return:返回head中的所有数据(列表) ''' self.init_config() # 取文件数据之前生成配置文件 self.config.read(self.name, encoding="utf-8") if self.config.has_section(head): section = self.config.sections() return self.config.items(section[0])
core:
#!/usr/bin/env python # -*-coding:utf-8-*- # Author:zh import os import sys import hashlib PATH = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) sys.path.append(PATH) from db import mysqldb from conf import config from .import main class Entrance(object): # 这个类用来提供注册和登陆方法 def __init__(self, enter): self.num = enter obj = config.Configuration() data = obj.get_config() self.sql_obj = mysqldb.SqlServer(data[0][1], data[1][1], data[2][1], data[3][1], data[4][1], data[5][1]) def __del__(self): self.sql_obj.close() def load(self): # 登陆 while True: user_name = input("name:") if user_name == "b": break pwd = input("password:") pwd_hash = hashlib.md5() pwd_hash.update(pwd.encode()) pwd = pwd_hash.hexdigest() if self.num == "1": table_name = "student" elif self.num == "2": table_name = "teacher" sql_data = "select password from %s where name = '%s';" % (table_name, user_name) try: self.sql_obj.run(sql_data) answer = self.sql_obj.get() if answer == (): print("没有此用户，请重新登陆") else: if pwd == answer[0][0]: return user_name print("welcome") break else: print("密码错误！") except Exception as e1: print("输入错误,错误：%s" % e1) def register(self): # 注册 while True: user_name = input("name:") if user_name == "b": break pwd = input("password:") pwd_hash = hashlib.md5() pwd_hash.update(pwd.encode()) pwd = pwd_hash.hexdigest() qq = input("qq:") try: qq = int(qq) except ValueError: print("qq号格式不正确") continue if self.num == "1": table_name = "student" elif self.num == "2": table_name = "teacher" sql_data = "insert into %s (name,password,qq) values('%s','%s',%s);" % (table_name, user_name, pwd, qq) try: self.sql_obj.run(sql_data) self.sql_obj.commit() print("welcome") return user_name except Exception as e1: print("执行失败,错误：%s" % e1) def show(n): # 这个方法用来展示数据 for count, i in enumerate(n): print("%s: %s" % (count+1, i)) def run(): while True: info = ["学生", "老师"] show(info) choose = input("请选择入口：") if choose == 'b': break if choose is "": continue if int(choose) in (1, len(info)): start_obj = Entrance(choose) while True: info = ["注册", "登陆"] show(info) num = input("请选择入口：") if num is "": continue elif num == "1": name = start_obj.register() break elif num == "2": name = start_obj.load() break else: print("输入错误！") continue if choose == "2": info = ["创建班级", "加入班级", "创建上课记录", "批改成绩"] obj = main.TeacherView() data = {1: "create_class", 2: "join_class", 3: "class_record", 4: "alter_score"} elif choose == "1": info = ["提交作业", "查看成绩", "查看班级排名"] obj = main.StudentView(name) data = {1: "submit", 2: "view", 3: "rank"} while True: show(info) choose = input("选择操作:") if choose == 'b': break if choose is "": continue try: choose = int(choose) except ValueError as e: print("请输入数字！错误：%s" % e) continue if hasattr(obj, data[choose]): func = getattr(obj, data[choose]) func() else: print("类中无此方法，请查看！") else: print("输入错误！")
#!/usr/bin/env python # -*-coding:utf-8-*- # Author:zh import pymysql from db import mysqldb from conf import config class TeacherView(object): # 这个类用来处理讲师视图 def __init__(self): obj = config.Configuration() data = obj.get_config() self.sql_obj = mysqldb.SqlServer(data[0][1], data[1][1], data[2][1], data[3][1], data[4][1], data[5][1]) def __del__(self): self.sql_obj.close() def create_class(self): # 这个方法用来创建班级 while True: class_name = input("请输入班级名:") if class_name == "b": break sql_data = "insert into class (class_name) values('%s');" % class_name try: self.sql_obj.run(sql_data) self.sql_obj.commit() print("创建成功") break except pymysql.err.IntegrityError as e1: print("班级名已存在,错误：%s" % e1) except Exception as e1: print("错误：%s" % e1) def join_class(self): # 这个方法用来加入班级 sign = True while sign: qq = input("请输入qq号(多个用,隔开):") if qq == "b": break sql_data = "select id from student where qq in (%s)" % qq # 查看所有qq号 self.sql_obj.run(sql_data) id_tuple = self.sql_obj.get() if id_tuple == (): print("无使用该qq的学员") continue class_name = show_class(self.sql_obj) if class_name is not None: count = 0 for id in id_tuple: sql_data = "insert into class_union_student(class_id,student_id) select (select id" \ " from class where class_name='%s') as class_id,%s from dual;" % (class_name, id[0]) try: self.sql_obj.run(sql_data) self.sql_obj.commit() count += 1 except Exception as e1: print("插入失败，错误内容：%s" % e1) print("成功%s个" % count) sign = False def class_record(self): # 这个方法用来创建上课记录 class_name = show_class(self.sql_obj) if class_name is not None: sign = True while sign: class_num = input("请输入上课节数：") if class_num == "b": break # 插入上课记录 sql_class_record = "insert into class_record(class_id,course_num) " \ "select id,%s from class where class_name = '%s'" % (class_num, class_name) try: self.sql_obj.run(sql_class_record) except Exception as e1: print("插入上课记录失败，错误:%s" % e1) sql_score = "select student_id from class_union_student" \ " where class_id =(select id from class where class_name = '%s')" % class_name self.sql_obj.run(sql_score) student_tuple = self.sql_obj.get() for student_id in student_tuple: # 给所有班级的学生添加上课记录 sql_score = "insert into score(class_record_id,student_id) " \ "select(select id from class_record where class_id = " \ "(select id from class where class_name = '%s') and course_num = %s ) " \ "as class_record_id,%s from dual;" % (class_name, class_num, student_id[0]) try: self.sql_obj.run(sql_score) except Exception as e1: print("上课记录插入失败，学生ID为%s,错误内容%s" % (student_id[0], e1)) self.sql_obj.commit() sign = False def alter_score(self): # 这个方法用来修改学员成绩 class_name = show_class(self.sql_obj) if class_name is not None: sql_student = "select name from student where id in " \ "(select student_id from class_union_student where class_id = " \ "(select id from class where class_name = '%s') " \ "union select student_id from score where sign = '1' )" % class_name self.sql_obj.run(sql_student) student_tuple = self.sql_obj.get() sign = True while sign: for count, i in enumerate(student_tuple): print("%s: %s" % (count + 1, i[0])) num_student = input("请选择需要修改成绩的学生:") if num_student == "b": break if num_student is "": continue if int(num_student) in (1, len(student_tuple)): student_name = student_tuple[int(num_student)-1][0] sql_score = "select '%s',b.course_num,a.score,a.id from score a " \ "join class_record b on a.class_record_id = b.id where a.sign = '1' and a.student_id=(" \ "select id from student where name = '%s')" % (class_name, student_name) self.sql_obj.run(sql_score) student_score = self.sql_obj.get() if student_score is (): print("没有需要批改的作业") continue else: count = 0 for i in student_score: count += 1 print("%s: 班级:%s,节数:%s,成绩:%s" % (count, i[0], i[1], i[2])) while sign: enter = input("请选择需要修改的成绩：") if enter == "b": break try: enter = int(enter) except ValueError as e1: print("输入错误:%s" % e1) continue if enter is "": continue if enter in range(1, len(student_score) + 1): score_id = student_score[enter-1][3] while sign: change_score = input("请输入修改的成绩:") if change_score == "b": break try: change_score = int(change_score) except ValueError as e1: print("error:%s,请输入数字" % e1) continue sql_change = "update score set score = %s where id = %s" % (change_score, score_id) try: self.sql_obj.run(sql_change) self.sql_obj.commit() print("修改成功") except Exception as e1: print("更新失败，name为：%s,class为:%s,错误内容：%s" % (student_name, class_name, e1)) sign = False else: print("输入错误") else: print("输入错误") class StudentView(object): # 这个类用来处理学生视图 def __init__(self, user_name): # user_name：传入登陆人姓名 self.name = user_name obj = config.Configuration() data = obj.get_config() self.sql_obj = mysqldb.SqlServer(data[0][1], data[1][1], data[2][1], data[3][1], data[4][1], data[5][1]) def __del__(self): self.sql_obj.close() def submit(self): # 这个方法用来提交作业 class_name = show_class(self.sql_obj) if class_name is not None: sql_num = "select id,'%s',course_num from class_record where class_id = " \ "(select id from class where class_name = '%s')" % (class_name, class_name) self.sql_obj.run(sql_num) course_num_tuple = self.sql_obj.get() if course_num_tuple is (): print("未建立上课记录") else: count = 0 for course_num in course_num_tuple: count += 1 print("%s: 班级:%s,节数:%s" % (count, class_name, course_num[2])) sign = True while sign: class_num = input("请选择上课节数:") if class_num is "": continue try: class_num = int(class_num) except ValueError as e1: print("error:%s,请输入数字" % e1) continue if class_num in range(1, len(course_num_tuple) + 1): sql_score = "update score set sign = '1' where class_record_id = %s and student_id =" \ "(select id from student where name = '%s')" \ % (course_num_tuple[class_num-1][0], self.name) try: self.sql_obj.run(sql_score) self.sql_obj.commit() print("提交成功！") except Exception as e1: print("更新失败，name为：%s,class为：%s,上课节数为:%s,错误内容：%s" % (self.name, class_name, class_num, e1)) sign = False def view(self): # 这个方法用来查看成绩 class_name = show_class(self.sql_obj) if class_name is not None: sql_score = "select '%s',a.course_num,b.score,b.sign from class_record a join score b " \ "on b.class_record_id = a.id where b.student_id= (select id from student where name = '%s')" \ % (class_name, self.name) self.sql_obj.run(sql_score) score_tuple = self.sql_obj.get() for score in score_tuple: print("班级:%s,节数:%s,成绩:%s,是否提交:%s" % (score[0], score[1], score[2], score[3])) def rank(self): # 这个方法用来查看班级排名 class_name = show_class(self.sql_obj) if class_name is not None: sql_rank = "select sum(score) a,student_id from score where class_record_id in " \ "(select id from class_record where class_id = " \ "(select id from class where class_name = '%s')) group by student_id order by a desc" % class_name self.sql_obj.run(sql_rank) rank_tuple = self.sql_obj.get() sql_score = "select sum(score) from score where student_id = " \ "(select id from student where name = '%s') and class_record_id in" \ "(select id from class_record where class_id = " \ "(select id from class where class_name = '%s'))" % (self.name, class_name) self.sql_obj.run(sql_score) my_score_tuple = self.sql_obj.get() if my_score_tuple[0][0] is not None: for index, i in enumerate(rank_tuple): if my_score_tuple[0][0] == i[0]: print("第%s名" % str(index+1)) else: print("没有成绩") def show_class(class_obj): # 这个方法用来格式化显示班级 # class_obj:数据库连接 sql_data = "select class_name from class;" class_obj.run(sql_data) answer = class_obj.get() if answer == (): print("请先建立班级") class_name = None else: for count, i in enumerate(answer): print("%s: %s" % (count + 1, i[0])) while True: enter = input("请选择班级:") try: enter = int(enter) except ValueError as e1: print("输入错误:%s" % e1) continue if enter is "": continue if enter in range(1, len(answer)+1): class_name = answer[enter - 1][0] break else: print("输入错误") return class_name
db:
#!/usr/bin/env python # -*-coding:utf-8-*- # Author:zh import pymysql import os import sys PATH = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) sys.path.append(PATH) from conf import config class SqlServer(object): def __init__(self, host, port, user, passwd, db, charset): self.host = host self.port = port self.user = user self.passwd = passwd self.db = db self.charset = charset self.conn = pymysql.connect(host=self.host, port=int(self.port), user=self.user, passwd=self.passwd, db=self.db, charset=self.charset) self.cursor = self.conn.cursor() def run(self, sql): self.cursor.execute(sql) def get(self): answer = self.cursor.fetchall() return answer def commit(self): self.conn.commit() def close(self): self.cursor.close() self.conn.close() table_data = [ "create table class(" "id int auto_increment primary key," "class_name varchar(20) not null unique)", "create table student(id int auto_increment primary key," "name varchar(20) not NULL unique," "password varchar(32) not NULL," "qq varchar(20) UNIQUE not NULL)", "create table teacher(id int auto_increment primary key," "name varchar(20) not NULL unique," "password varchar(32) not NULL," "qq varchar(20) UNIQUE not NULL)", "create table class_record(" "id int auto_increment primary key," "class_id INT not NULL ," "course_num INT not NULL," "UNIQUE key u_class_record (class_id ,course_num)," "foreign key f_class_record (class_id) references class(id) on delete cascade on update cascade)", "create table score(" "id int auto_increment primary key," "class_record_id INT not NULL," "student_id INT not NULL," "score INT DEFAULT NULL ," "sign Enum('0','1') DEFAULT '0'," "UNIQUE key u_score (class_record_id ,student_id )," "foreign key f_score_1 (class_record_id) references class_record(id) on delete cascade on update cascade," "foreign key f_score_2 (student_id) references student(id) on delete cascade on update cascade)", "create table class_union_student(" "id int auto_increment primary key," "class_id INT not NULL," "student_id INT not NULL," "UNIQUE key u_class_union_student (class_id ,student_id )," "foreign key f1_class_union_student (class_id) references class(id) on delete cascade on update cascade," "foreign key f2_class_union_student (student_id) references student(id) on delete cascade on update cascade)" ] def create_table(): obj = config.Configuration() data = obj.get_config() sql_obj = SqlServer(data[0][1], data[1][1], data[2][1], data[3][1], data[4][1], data[5][1]) sql_conn = "select table_name from information_schema.tables " \ "where table_schema= '%s' and table_name = 'class_union_student'" % data[4][1] sql_obj.run(sql_conn) table = sql_obj.get() if table == (): for j in table_data: sql_obj.run(j) sql_obj.close() def test(): obj = config.Configuration() data = obj.get_config() sql_obj = SqlServer(data[0][1], data[1][1], data[2][1], data[3][1], data[4][1], data[5][1]) sql_conn = "select * from student;" sql_obj.run(sql_conn) answer = sql_obj.get() if answer == (): sql_list = ["insert into student(name,password,qq) values('zs','202cb962ac59075b964b07152d234b70','123456')", "insert into student(name,password,qq) values('zh','202cb962ac59075b964b07152d234b70','654321')", "insert into teacher(name,password,qq) values('alex','202cb962ac59075b964b07152d234b70','987654')"] for i in sql_list: sql_obj.run(i) sql_obj.commit() sql_obj.close()
