WHCSRL 技术网

python mysql 学生成绩管理系统

这学期在学python,感觉想写一个东西来巩固自己的基础,因为大二的时候我看过python,所以还是一共花了几个小时写了一个基于mysql的成绩管理系统,这个东西其实拿不出手,不过就当复习基本了
1 首先如果你python中没安装mysql的驱动,还是要打开cmd命令行安装一下才可以使用:

pip3 install PyMySQL
在这里插入图片描述
2 创建数据库studentdb,你可以在图形化界面sqlyog中创建:
在这里插入图片描述

3 然后在数据库中创建表st
在这里插入图片描述
4 python连接数据库的核心代码:

 db = pymysql.connect(host='localhost',
                         user='root',
                         password='333',
                         database='studentdb')
    cursor = db.cursor()
  • 1
  • 2
  • 3
  • 4
  • 5

user就是你自己设置的用户名,password就是你自己设置的密码,database就是你刚刚设置的数据库

5 另外你也可以选择mysql命令行创建数据库以及数据表都是可以的在这里插入图片描述

6 完整代码如下:

import pymysql
def menu():
    print("		学生信息管理系统		")
    print("		1 添加学生		")
    print("		2 删除学生		")
    print("		3 修改学生		")
    print("		4 按成绩降序排序:		")
    print("		5 按成绩升序排序:		")
    print("		6 查询:		")
    print("		7 退出程序		")
    pass

def insert():
    print("插入学生信息")
    name = input("请输入学生姓名:")
    sex = input("请输入学生性别:")
    snum = input("请输入学生学号:")
    chinese = int(input("请输入学生语文成绩:"))
    math = int(input("请输入学生数学成绩:"))
    stotal = int(input("请输入学生总成绩:"))
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='333',
                         database='studentdb')
    cursor = db.cursor()

    sql = "insert into st(sname,ssex,snum,chinese,math,stotal) values('%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s','%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s','%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s',%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d,%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d,%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d)"%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%(name,sex,snum,chinese,math,stotal)
    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()
        print("error")
    db.close()
    pass


def delete():
    while 1:
        print("删除学生信息")
        choose1 = int(input("请输入您的删除方式:1 按姓名 2 按学号: "))
        if choose1 == 1:
            print("按姓名删除")
            strname = input("请输入删除学生的名字:")
            db = pymysql.connect(host='localhost',
                                 user='root',
                                 password='333',
                                 database='studentdb')
            cursor = db.cursor()
            sql1 = "select *from st where sname='%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s'"%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%(strname)
            cursor.execute(sql1)
            if cursor.rowcount>0:
                try:
                    sql = "DELETE FROM st WHERE sname = '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s'" %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% (strname)
                    cursor.execute(sql)
                    db.commit()
                    print("删除姓名为%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s的学生信息成功" %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% (strname))
                    break
                except:
                    db.rollback()
                    print("error")
                db.close()
            else:
                print("没有这个学生的信息,输入错误")
                break
        elif choose1 == 2:
            print("按学号删除")
            strnum = input("请输入删除学生的学号:")
            db = pymysql.connect(host='localhost',
                                 user='root',
                                 password='333',
                                 database='studentdb')
            cursor = db.cursor()
            sql1 = "select *from st where snum='%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s'"%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%(strnum)
            cursor.execute(sql1)
            if cursor.rowcount>0:
                sql = "DELETE FROM st WHERE snum = '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s'" %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% (strnum)
                try:
                    cursor.execute(sql)
                    db.commit()
                    print("删除学号为%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s学生的信息成功" %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% (strnum))
                    break
                except:
                    db.rollback()
                    print("error")
                db.close()
            else:
                print("没有这个学生的信息,输入错误")
                break
        else:
            print("输入错误,重新输入")
            break
            pass
        pass
    pass


def change():
    while 1:
        print("改变学生信息")
        myname = input("请输入改变学生的名字:")
        db = pymysql.connect(host='localhost',
                             user='root',
                             password='333',
                             database='studentdb')
        cursor = db.cursor()
        sql1 = "select *from st where sname='%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s'"%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%(myname)
        cursor.execute(sql1)
        results = cursor.fetchall()
        name12='0'
        for row in results:
            name12 = row[0]
        if myname == name12:
            try:
                sex = input("请输入学生性别:")
                snum = input("请输入学生学号:")
                chinese = int(input("请输入学生语文成绩:"))
                math = int(input("请输入学生数学成绩:"))
                stotal = int(input("请输入学生总成绩:"))
                #有问题解决不了
                sql3 = "update st set ssex ='%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s',snum='%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s',chinese=%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d,math=%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d,stotal=%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d where sname='%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s'"%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%(sex,snum,chinese,math,stotal,name12)
                cursor.execute(sql3)
                db.commit()
                print("修改姓名为%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s学生的信息成功"%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%(myname))
                break
            except:
                db.rollback()
                print("error")
            db.close()
            break
        else:
            print("没有这个学生的信息,输入错误")
            break
            pass
        pass
    pass

def sortbyscoredesc():
    print("按成绩降序")
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='333',
                         database='studentdb')
    cursor = db.cursor()

    sql = "select * from st order by stotal desc"
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        for row in results:
            name1 = row[0]
            sex1 = row[1]
            num1 = row[2]
            chinses1 = row[3]
            math1 = row[4]
            total1 = row[5]
            # 打印结果
            print("学生姓名:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s	,学生性别:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s	,学生学号:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s	,语文成绩:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d	,数学成绩:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d	,总成绩:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d	" %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 
                  (name1, sex1, num1, chinses1, math1, total1))
        db.commit()
    except:
        db.rollback()
        print("error")
    db.close()
    pass

def sortbyscore():
    print("按成绩升序排序")
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='333',
                         database='studentdb')
    cursor = db.cursor()

    sql = "select * from st order by stotal asc "
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        for row in results:
            name1 = row[0]
            sex1 = row[1]
            num1 = row[2]
            chinses1 = row[3]
            math1 = row[4]
            total1 = row[5]
            # 打印结果
            print("学生姓名:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s	,学生性别:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s	,学生学号:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s	,语文成绩:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d	,数学成绩:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d	,总成绩:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d	" %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 
                  (name1, sex1, num1, chinses1, math1, total1))
        db.commit()
    except:
        db.rollback()
        print("error")
    db.close()
    pass
def select():
    print("查询学生信息")
    db = pymysql.connect(host='localhost',user='root',password='333',database='studentdb')
    cursor = db.cursor()
    sql = "select * from st"
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        for row in results:
            name1 = row[0]
            sex1 = row[1]
            num1 = row[2]
            chinses1 = row[3]
            math1 = row[4]
            total1 = row[5]
            # 打印结果
            print("学生姓名:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s	,学生性别:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s	,学生学号:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%s	,语文成绩:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d	,数学成绩:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d	,总成绩:%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%d	" %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 
                  (name1,sex1,num1,chinses1,math1,total1))
        db.commit()
    except:
        db.rollback()
        print("error")
    db.close()
    pass

def exitexe():
    while 1:
        print("确定要退出系统吗?退出(y) 不退出(n)")
        c = input("请输入您的选择aaaa:	")
        if c == 'y':
            exit()
        elif c == 'n':
            break
        else:
            print("输入有误 重新输入")
        pass
    pass
if __name__ == '__main__':
    while True:
        menu()
        choose = int(input("请输入您的选择:	"))
        if choose == 1:
            insert()
        elif choose == 2:
            delete()
        elif choose == 3:
            change()
        elif choose == 4:
            sortbyscoredesc()
        elif choose == 5:
            sortbyscore()
        elif choose == 6:
            select()
        elif choose == 7:
            exitexe()
            pass
        pass











  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190
  • 191
  • 192
  • 193
  • 194
  • 195
  • 196
  • 197
  • 198
  • 199
  • 200
  • 201
  • 202
  • 203
  • 204
  • 205
  • 206
  • 207
  • 208
  • 209
  • 210
  • 211
  • 212
  • 213
  • 214
  • 215
  • 216
  • 217
  • 218
  • 219
  • 220
  • 221
  • 222
  • 223
  • 224
  • 225
  • 226
  • 227
  • 228
  • 229
  • 230
  • 231
  • 232
  • 233
  • 234
  • 235
  • 236
  • 237
  • 238
  • 239
  • 240
  • 241
  • 242
  • 243
  • 244
  • 245
  • 246
  • 247
  • 248
  • 249
  • 250
  • 251
  • 252
  • 253
  • 254
  • 255
  • 256
  • 257
  • 258
  • 259
  • 260
  • 261
  • 262

7 运行结果如下:
在这里插入图片描述

8 总结:
在这个案例中,我遇到的问题就是在sql语句和python间,有时候忘记commit,就要测试十几分钟来解决,然后就是如何设置用户输入错误指令的问题,我用了while循环和break pass来解决,总体上这个项目不难,对初学python的人很有用,我仅用了最基本的语法,元组,列表集合基本没用,如果你有更好的想法私信我,下一步我将它开发为一个图形化界面的系统

推荐阅读