python实现选课系统/学生信息管理系统

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;

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页