1.需求
管理员：
添加教师：一个教师作为一个班级的班主任，同时也作为一个或多个课程的代课老师
添加课程：指定课程名，学分，任课老师。
添加班级：指定班级名称，班主任
老师：
添加学生：作为一个班级的班主任，该老师为其班级添加学生
添加课程：作为一个班级的班主任，该老师为其班级添加课程
给出成绩：作为一门课程的任课老师，该老师给出他所带课程的学生成绩
查看学生成绩：作为一门课程的任课老师，查看学生成绩
学生：
查看自己成绩单
查看已修学分情况
2.代码
工具：Utils.py
#date:2019/9/20
import pymysql
import hashlib
class SqlUtils:
@staticmethod
def getConn():
# 获取连接
conn = pymysql.connect(host='localhost',user='root',password='root',database='sc')
return conn
@staticmethod
def close(cursor,conn):
cursor.close()
conn.close()
class MD5Utils:
@staticmethod
def getMD5(string):
md5 = hashlib.md5(bytes('salt',encoding='utf-8'))
md5.update(bytes(string))
return md5.hexdigest()
页面：View.py
#date:2019/9/20
import Service
def Mainframe():
while True:
print('*****************')
print('1.管理员登录')
print('2.教师登录')
print('3.学生登录')
print("*****************")
op = input('请选择: ')
if op=='1':
username = input('请输入账号：')
password = input('请输入密码: ')
if Service.Login.login(username,password,1)==0:
print('用户名或密码错误！')
continue
adminFrame()
elif op=='2':
username = input('请输入账号：')
password = input('请输入密码: ')
if Service.Login.login(username,password,2)==0:
print('用户名或密码错误！')
continue
teacherFrame(username)
elif op=='3':
username = input('请输入账号：')
password = input('请输入密码: ')
if Service.Login.login(username, password, 3) == 0:
print('用户名或密码错误！')
continue
stuFrame(username)
else:
print("无效重新输入")
def teacherFrame(teacherName):
while True:
print('********老师*******')
print('1.添加学生')
print('2.录入成绩')
print('3.查看学生成绩')
print('4.给学生选课')
print('0.退出')
op = input('请选择: ')
if op == '1':
Service.Teacher.addStudent(teacherName)
elif op == '2':
Service.Teacher.addScore(teacherName)
elif op == '3':
Service.Teacher.findScore(teacherName)
elif op == '4':
Service.Teacher.selectCourse(teacherName)
elif op =='0':
break
else:
print("无效重新输入")
def adminFrame():
while True:
print('********管理员*******')
print('1.添加老师')
print('2.添加班级')
print('3.添加课程')
print('0.退出')
op = input('请选择: ')
if op == '1':
Service.Admin.addTeatcher()
elif op == '2':
Service.Admin.addClass()
elif op == '3':
Service.Admin.addCourse()
elif op =='0':
break
else:
print("无效重新输入")
def stuFrame(studentName):
while True:
print('********学生端*******')
print('1.查看个人成绩')
print('2.个人学分情况')
print('0.退出')
op = input('请选择: ')
if op == '1':
Service.Student.selScore(studentName)
elif op == '2':
Service.Student.count(studentName)
elif op == '0':
break
else:
print("无效重新输入")
if '__main__'==__name__:
Mainframe()
业务：Service.py
#date:2019/9/20
import pymysql
import Utils
class Login:
@staticmethod
def login(username ,passwrod,role):
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor()
sql = 'select * from user where name=%s and password=%s and role=%s'
res = cursor.execute(sql,(username,passwrod,role))
return res
#### 管理员添加老师
class Admin:
@staticmethod
def addTeatcher():
list=[]
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor()
while True:
teacherName = input("请输入老师名字：")
teacherSex = input('请输入老师性别:')
teacherPasswd = input('请输入教师登录密码:')
list.append((teacherName,teacherSex,teacherPasswd,2))
op = input("是否继续输入Y/y? ")
if op!='Y' or op!='y':
break
sql = 'insert into user (name,sex,password,role)values (%s,%s,%s,%s)'
res = cursor.executemany(sql,list)
if res>=1:
print("添加老师成功!")
conn.commit()
Utils.SqlUtils.close(cursor,conn)
### 管理员添加课程
@staticmethod
def addCourse():
list = []
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor()
while True:
courseName = input('请输入课程名字：')
courseScore = input('请输入课程学分：')
courTeacher = input('请输入任课老师: ')
sql = 'select userid from user where name = %s and role = 2'
res = cursor.execute(sql,courTeacher)
if res == 0:
print('该老师不存在请重新输入！')
continue
list.append((courseName,courseScore,cursor.fetchone()[0]))
op = input("是否继续添加Y/y? ")
if op != 'Y' and op != 'y':
break
sql = 'insert into course (coursename,coursescore,teacher)values (%s,%s,%s)'
res = cursor.executemany(sql,list)
if res>=1:
print('添课程成功！')
conn.commit()
Utils.SqlUtils.close(cursor, conn)
##管理员添加班级
@staticmethod
def addClass():
list = []
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor()
while True:
className = input('请输入班级名字：')
classTeacher = input('请输入班主任: ')
sql = 'select userid from user where name = %s and role = 2'
res = cursor.execute(sql, classTeacher)
if res == 0:
print('该老师不存在请重新输入！')
continue
list.append((className, cursor.fetchone()[0]))
op = input("是否继续添加Y/y? ")
if op != 'Y' and op != 'y':
break
sql = 'insert into classes (classname,classteacher)values (%s,%s)'
res = cursor.executemany(sql, list)
if res >= 1:
print('添课班级成功！')
conn.commit()
Utils.SqlUtils.close(cursor, conn)
class Teacher:
# 根据老师名字查找班级
@staticmethod
def findClassIdByTeacherName(teacherName):
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor()
sql ='select classid from classes , user where user.name=%s and user.userid=classes.classteacher'
res = cursor.execute(sql,teacherName)
Utils.SqlUtils.close(cursor,conn)
if res!=0:
return cursor.fetchone()[0]
else: return 0
# 根据班级查学生
@staticmethod
def findStudentByClass(classid):
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor()
sql = 'select studentid from student where studentclass=%s'
res = cursor.execute(sql, classid)
Utils.SqlUtils.close(cursor, conn)
if res != 0:
return cursor.fetchall()
else:
return 0
@staticmethod
def findCourseByTeahcerName(teahcerName):
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select courseid,coursename from course,user where user.userid=course.teacher and user.name=%s'
res = cursor.execute(sql, teahcerName)
Utils.SqlUtils.close(cursor, conn)
if res != 0:
return cursor.fetchall()
else:
return 0
#添加学生
@staticmethod
def addStudent(teachername):
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor()
while True:
stuName = input("请输入学生名字：")
stuSex = input('请输入学生性别:')
stuPasswd = input('请输入学生登录密码:')
sqlUser = 'insert into user (name,sex,password,role)values (%s,%s,%s,%s)'# 往user表中插入学生
res1 = cursor.execute(sqlUser, (stuName, stuSex, stuPasswd,3) )
studentid = cursor.lastrowid # user表中stu的id
sqlstu = 'insert into student (studentid,studentclass)values (%s,%s)'# 往stu表中插入
classid = Teacher.findClassIdByTeacherName(teachername)# 班级id
res2 = cursor.execute(sqlstu,(studentid,classid))
if res1 >= 1 and res2>=1:
print("添加学生成功!")
op = input("是否继续添加Y/y? ")
if op != 'Y' and op != 'y':
break
conn.commit()
Utils.SqlUtils.close(cursor, conn)
# 选课
@staticmethod
def selectCourse(teachername):
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor()
classid = Teacher.findClassIdByTeacherName(teachername) # 该老师带的班级id
student = Teacher.findStudentByClass(classid)#该班级的所有学生
while True:
courseName = input("请输入要选的课程: ")
sqlCourse = 'select courseid from course where coursename=%s'
res = cursor.execute(sqlCourse,courseName)
if res==0:
print("课程名不存在，请重新输入！")
continue
courseid = cursor.fetchone()[0] # 要选的课程id
for stuItem in student: #为该班的所有学生选课
sqlSelCourse = 'insert into socre (userid,courseid ,score)values (%s,%s,%s)'
res = cursor.execute(sqlSelCourse,(stuItem,courseid,0))
if res>=1:
conn.commit()
op = input("选课完成！是否继续添加Y/y? ")
if op != 'Y' and op != 'y':
break
Utils.SqlUtils.close(cursor,conn)
# 给成绩
@staticmethod
def addScore(teachername):
course = Teacher.findCourseByTeahcerName(teachername)#该老师带的所有课程
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
for item in course:#该老师带的一门课
sql = 'select user.name,user.userid from user,socre where user.userid=socre.userid and socre.courseid=%s'
res = cursor.execute(sql,item['courseid'])
if res!=0:
student = cursor.fetchall()#这门课的所有学生
for stu in student:# 一个学生
score = input('%s的%s成绩为：'%(stu['name'],item['coursename']))
sql = 'update socre SET score=%s where userid=%s and courseid=%s'
res = cursor.execute(sql,(score,stu['userid'],item['courseid']))
if res>=1:
conn.commit()
Utils.SqlUtils.close(cursor,conn)
print("成绩录入成功")
# 任课老师查看学生成绩
@staticmethod
def findScore(teachername):
course = Teacher.findCourseByTeahcerName(teachername) # 该老师带的所有课程
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
print('***********************')
for item in course: # 该老师带的一门课
sql = 'select user.name,socre.score from user,socre where user.userid=socre.userid and socre.courseid=%s'
res = cursor.execute(sql, item['courseid'])
if res != 0:
student = cursor.fetchall() # 这门课的所有学生
for stu in student: # 一个学生
print('%s的%s成绩为：%s' % (stu['name'], item['coursename'],stu['score']))
print("*******************")
Utils.SqlUtils.close(cursor, conn)
class Student:
@staticmethod #
def selScore(studentname):
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'SELECT user.name,socre.score,course.coursename from user,socre ,course where user.userid=socre.userid and user.name =%s and course.courseid = socre.courseid'
res = cursor.execute(sql,studentname)
if res>0:
scores = cursor.fetchall()
print('*******%s 的成绩如下*******'%studentname)
for item in scores:
print('%s的成绩为：%s' % (item['coursename'], item['score']))
print("*******************")
Utils.SqlUtils.close(cursor, conn)
@staticmethod
def count(studentname):
conn = Utils.SqlUtils.getConn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql='SELECT sum(course.coursescore) as score from user,socre ,course where user.userid=socre.userid and user.name =%s and course.courseid = socre.courseid and socre.score>=60'
res = cursor.execute(sql, studentname)
if res>0:
scores = cursor.fetchone()
print('*******%s 的累计学分为%s分*******'%(studentname,scores['score']) )
sql='SELECT user.name,socre.score,course.coursename from user,socre ,course where user.userid=socre.userid and user.name =%s and course.courseid = socre.courseid and socre.score<=60 ;'
res = cursor.execute(sql, studentname)
if res>0:
print('*******%s未通过的课程如下********'%studentname)
nopass=cursor.fetchall()
for item in nopass:
print('%s的成绩为：%s' % (item['coursename'], item['score']))
Utils.SqlUtils.close(cursor, conn)
if '__main__'==__name__:
print(Login.login('stu1','stu1',3))
3.运行结果
附sql
/*
Navicat Premium Data Transfer
Source Server : MySQL
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost:3306
Source Schema : sc
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : 65001
Date: 25/09/2019 15:35:50
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for classes
-- ----------------------------
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
`classid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`classname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级名字',
`classteacher` int(11) NULL DEFAULT NULL COMMENT '班主任',
`classnum` int(11) NULL DEFAULT NULL COMMENT '班级人数',
PRIMARY KEY (`classid`) USING BTREE,
INDEX `classteacher`(`classteacher`) USING BTREE,
CONSTRAINT `classes_ibfk_1` FOREIGN KEY (`classteacher`) REFERENCES `user` (`userid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of classes
-- ----------------------------
INSERT INTO `classes` VALUES (1, '软工', 24, NULL);
INSERT INTO `classes` VALUES (2, '计科', 25, NULL);
INSERT INTO `classes` VALUES (3, '大数据', 33, NULL);
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`courseid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`coursename` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程名',
`coursescore` int(20) NULL DEFAULT NULL COMMENT '课程学分',
`teacher` int(255) NULL DEFAULT NULL COMMENT '任课老师',
PRIMARY KEY (`courseid`) USING BTREE,
INDEX `teacher`(`teacher`) USING BTREE,
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`teacher`) REFERENCES `user` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (3, '数据库', 3, 24);
INSERT INTO `course` VALUES (4, '操作系统', 4, 24);
INSERT INTO `course` VALUES (5, '编译原理', 2, 25);
INSERT INTO `course` VALUES (6, '计算机网络', 5, 33);
-- ----------------------------
-- Table structure for socre
-- ----------------------------
DROP TABLE IF EXISTS `socre`;
CREATE TABLE `socre` (
`socreid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`userid` int(20) NOT NULL COMMENT '学生id',
`courseid` int(20) NULL DEFAULT NULL COMMENT '课程id',
`score` int(20) NULL DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`socreid`) USING BTREE,
INDEX `userid`(`userid`) USING BTREE,
INDEX `courseid`(`courseid`) USING BTREE,
CONSTRAINT `socre_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `socre_ibfk_2` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of socre
-- ----------------------------
INSERT INTO `socre` VALUES (9, 30, 4, 60);
INSERT INTO `socre` VALUES (10, 31, 4, 40);
INSERT INTO `socre` VALUES (11, 30, 3, 60);
INSERT INTO `socre` VALUES (12, 31, 3, 70);
INSERT INTO `socre` VALUES (13, 28, 5, 70);
INSERT INTO `socre` VALUES (14, 29, 5, 80);
INSERT INTO `socre` VALUES (15, 34, 6, 99);
INSERT INTO `socre` VALUES (16, 34, 5, 78);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`studentid` int(11) NOT NULL,
`studentclass` int(11) NOT NULL,
PRIMARY KEY (`studentid`, `studentclass`) USING BTREE,
INDEX `studentclass`(`studentclass`) USING BTREE,
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`studentid`) REFERENCES `user` (`userid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `student_ibfk_2` FOREIGN KEY (`studentclass`) REFERENCES `classes` (`classid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (28, 1);
INSERT INTO `student` VALUES (29, 1);
INSERT INTO `student` VALUES (30, 2);
INSERT INTO `student` VALUES (31, 2);
INSERT INTO `student` VALUES (34, 3);
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '123456',
`role` int(2) NOT NULL DEFAULT 2,
PRIMARY KEY (`userid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (24, 'teacher1', '男', 'teacher1', 2);
INSERT INTO `user` VALUES (25, 'teacher2', '女', 'teacher2', 2);
INSERT INTO `user` VALUES (28, 'stu1', '男', 'stu1', 3);
INSERT INTO `user` VALUES (29, 'stu2', '女', 'stu2', 3);
INSERT INTO `user` VALUES (30, 'stu3', '男', 'stu3', 3);
INSERT INTO `user` VALUES (31, 'stu4', '男', 'stu4', 3);
INSERT INTO `user` VALUES (32, 'admin', '男', 'admin', 1);
INSERT INTO `user` VALUES (33, 'teacher3', '女', 'teacher3', 2);
INSERT INTO `user` VALUES (34, 'stu5', '男', 'stu5', 3);
SET FOREIGN_KEY_CHECKS = 1;
3400
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
