python3.6 对数据库的增删改查

i

mport pymysql
from builtins import int
import pymysql.cursors
#我是函数哟！！
#数据库的连接
def connDB():
    conn=pymysql.connect(host="localhost",
                         user="root",
                         passwd="root",
                         db="text",
                         charset="utf8");
    cur=conn.cursor();
    return (conn,cur);
#数据库的更新
def exeUpdate(conn,cur,sql):
    sta=cur.execute(sql);
    conn.commit();
    return (sta);
#数据库的删除
def exeDelete(conn,cur,Ids):
    sta=0;
    for eachID in Ids.split(' '):
        sta += cur.execute("delete from note where class=%d" % (int(Ids)));
        conn.commit();
        return (sta);
#数据库的查找
def exeQuery(cur,sql):
    cur.execute(sql);
    return (cur);
#数据库的关闭连接
def connClose(conn,cur):
    cur.close();
    conn.close();

#主体在这里！！
print("选择如下\n"
      "1.修改修改\n"
      "2.添加添加\n"
      "3.查询查询\n"
      "4.删除删除\n"
      "5.输出输出\n"
      );
conn,cur=connDB();
num=input();
flag=True;
while(flag):
    if(int(num)==1):
        class1=input("您想修改哪班的成绩，请输入班级");
        score1=input("成绩替换成：")
        r = (score1,int(class1))
        print(r)
        sql='UPDATE note SET score="%s" WHERE class=%d'%r
        try:
            exeUpdate(conn,cur,sql);
            print("如你所愿！");
            print('\n' * 2)
        except Exception:
            print("Sorry,失败了呢！");
            print('\n' * 2)
            raise;
    elif(int(num)==2):
        class1=input("请输入班级:");
        score1=input("请输入成绩：");
        r=(int(class1),score1)
        print(r)
        sql='INSERT INTO note(class,score ) VALUES (%d,"%s")'%(r)
        try:
            exeUpdate(conn,cur,sql);
            print("添加成功！");
            print('\n' * 2)
        except Exception:
            print("Sorry,失败了呢！");
            print('\n' * 2)
            raise
    elif(int(num)==3):
        class1=input("请输入要查找的班级：");
        r=int(class1)
        sql="SELECT * FROM note WHERE class=%d"%r
        try:
            cur=exeQuery(cur,sql);
            for item in cur:
                print("class="+str(item[0])+" "+"score="+str(item[1]));
                print('\n' * 2)
        except Exception:
            print("Sorry,失败了呢！");
            print('\n' * 2)
            raise
    elif(int(num)==4):
        Ids=input("请输入要删除class:");
        try:
            exeDelete(conn,cur,Ids);
            print("删除成功！");
            print('\n' * 2)
        except Exception:
            print("Sorry,失败了呢！");
            print('\n' * 2)
            raise
    elif(int(num)==5):
        print("输出当前表！");
        try:
                n="SELECT COUNT(DISTINCT class) FROM note"
                cur.execute(n);
                a=cur.fetchall();
                c = a[0]
                print(a)
                print('数据一共搜索到'+repr(c)+'条');
                sql1 = "SELECT class FROM note";
                cur.execute(sql1);
                result1 = cur.fetchall();
                print(result1)
                sql2 = "SELECT score FROM note";
                cur.execute(sql2);
                result2 = cur.fetchall();
                print(result2)
        except Exception:
            print("Sorry,失败了呢！");
            print('\n' * 2)
            raise
    else:
        print("非法输入，撤出系统！");
        flag=False;
        break
    print("请再次选择服务：1、修改记录，2、增加记录，3、查询记录，4、删除记录，5.输出记录")
    num = input("请选择操作");