python学生成绩管理系统（基于MySQL数据库）

# coding=gbk
import pymysql

def createTable():
    try:
        con = pymysql.connect(host="127.0.0.1", port=3306, user="root",
                        password="", db="students",charset="utf8")
        print("连接成功")
        cursor = con.cursor(pymysql.cursors.DictCursor)

        try:
            sql = "create table students (sNo varchar(12) primary key,sName varchar(8),sChinese int,sMath int,sEnglish int)"
            cursor.execute(sql)
        except:
            print("表单已存在")
        con.close()

    except Exception as err:
        print(err)

def inputStudent():
    sNo = input("sNo=")

    sName = input("sName=")

    sChinese = input("sChinese=")
    sChinese = int(sChinese)

    sMath = input("sMath=")
    sMath = int(sMath)

    sEnglish = input("sEnglish=")
    sEnglish = int(sEnglish)

    return {"sNo":sNo,"sName":sName,"sChinese":sChinese,"sMath":sMath,"sEnglish":sEnglish}

def insertDateToTable(sNo,sName,sChinese,sMath,sEnglish):
    try:
        con = pymysql.connect(host="127.0.0.1", port=3306, user="root",
                              password="", db="students", charset="utf8")

        cursor = con.cursor(pymysql.cursors.DictCursor)

        try:
            sql = "insert into students (sNo,sName,sChinese,sMath,sEnglish) values (%s,%s,%s,%s,%s)"
            cursor.execute(sql,[sNo,sName,sChinese,sMath,sEnglish])
            print("添加成功")
        except Exception as err:
            print(err)

        con.commit()
        con.close()

    except Exception as err:
        print(err)

def updateDate(sNo,sName,sChinese,sMath,sEnglish):
    try:
        con = pymysql.connect(host="127.0.0.1", port=3306, user="root",
                              password="", db="students", charset="utf8")

        cursor = con.cursor(pymysql.cursors.DictCursor)

        try:
            sql = "update students set sName=%s,sChinese=%s,sMath=%s,sEnglish=%s where sNo=%s"
            cursor.execute(sql,[sName,sChinese,sMath,sEnglish,sNo])
            print("修改成功")
        except Exception as err:
            print(err)

        con.commit()
        con.close()

    except Exception as err:
        print(err)

def DeleteDate(sNo):
    try:
        con = pymysql.connect(host="127.0.0.1", port=3306, user="root",
                              password="", db="students", charset="utf8")

        cursor = con.cursor(pymysql.cursors.DictCursor)

        try:
            # cursor.execute("delete from students"):删除表单所有信息
            sql = "delete from students where sNo=%s"
            cursor.execute(sql,[sNo])
            print("删除成功")
        except Exception as err:
            print(err)

        con.commit()
        con.close()

    except Exception as err:
        print(err)

def selectDate():
    try:
        con = pymysql.connect(host="127.0.0.1", port=3306, user="root",
                              password="", db="students", charset="utf8")

        cursor = con.cursor(pymysql.cursors.DictCursor)

        try:
            sql = "select * from students order by sNo"
            cursor.execute(sql)
            rows = cursor.fetchall()
            for row in rows:
                print("%-12s%-12s%-4d%-4d%-4d" %(row["sNo"],row["sName"],row["sChinese"],row["sMath"],row["sEnglish"]))
            print("Total:",len(rows))
        except Exception as err:
            print(err)

        con.close()

    except Exception as err:
        print(err)

while True:
    createTable()
    print("1.显示记录" )
    print("2.增加记录" )
    print("3.修改记录" )
    print("4.删除记录" )
    print("5.退出程序")
    s=input("请选择1-5:")
    if s=="1":
        selectDate()
    elif s=="2":
        row= inputStudent()
        insertDateToTable(row["sNo"], row["sName"], row["sChinese"], row["sMath"], row["sEnglish"])
    elif s=="3":
        row= inputStudent()
        updateDate(row["sNo"],row["sName"],row["sChinese"], row["sMath"], row["sEnglish"])
    elif s=="4":
        sNo= input("学号:").strip()
        DeleteDate(sNo)
    elif s=="5":
        break