Python学生管理系统（三）

import pymysql
name_list = []
#登录数据库
def get_connect():
    conn = pymysql.connect(host='localhost', user="root", passwd='自己的密码',database='stu')
    return conn
#主页面
def mune():
    print("-" * 30)
    print("   学生管理系统  v8.8  ")
    print("1.添加学生信息")
    print("2.删除学生信息")
    print("3.修改学生信息")
    print("4.查询单个学生信息")
    print("5.查询所有学生信息")
    print("0.退出系统")
    print("-" * 30)
#获取序号
def xh():
    x = int(input('请输入序号：'))
    return x
#增加学生信息
def add_s():
    new_info = {}
    new_id = input('请输入学号：')
    new_info['学号'] = int(new_id)
    new_name  = input('请输入姓名：')
    new_info['姓名'] = new_name
    new_sex = input('请输入性别：')
    new_info['性别'] = new_sex
    new_ihpone = input('请输入电话号码：')
    new_info['电话'] = new_ihpone
    add_sql(new_info)
    name_list.append(new_info)
#删除学生信息
def del_s():
    del_id = int(input('请输入要删除的学生号: '))
    for i in range(len(name_list)):
        if name_list[i]['学号'] == del_id:
            name_list.pop(i)
            del_sql(del_id)
            break
    else:
            print('没有该学生！ ')
def search_stu():
    name = input('请输入学生姓名：')
    for i in range(len(name_list)):
        if name_list[i]['姓名'] == name:
            print(name_list[i])
            search_sql(name)
            break
    else:
            print('查无此人！')
def x_lsit():
    print('*' * 10)
    print('1.修改姓名！')
    print('2.修改性别！')
    print('3.修改电话！')
    print('*' * 10)
    z = int(input('请输入功能序号：'))
    def x_id():
        id = int(input('请输入学号：'))
        name = input('请输入新姓名：')
        f = False
        for i in range(len(name_list)):
            if name_list[i]['学号'] == id :
                name_list[i]['姓名'] = name
                f =True
        if f == True:
            print('修改成功！')
        else:
            print('修改失败!')
        x_sql(1, id)
    def x_xb():
        id = int(input('请输入学号：'))
        sex_id = input('请输入新性别：')
        f = False
        for i in range(len(name_list)):
            if name_list[i]['学号'] == id:
                name_list[i]['性别'] = sex_id
                f = True
        if f == True:
            print('修改成功！')
        else:
            print('修改失败!')
        x_sql(2,id)
    def x_dh(z):
        id = int(input('请输入学号：'))
        dh_id = input('请输入新性别：')
        f = False
        for i in range(len(name_list)):
            if name_list[i]['学号'] == id:
                name_list[i]['电话'] = dh_id
                f = True
        if f == True:
            print('修改成功！')
        else:
            print('修改失败!')
        x_sql(3, id)
    if z == 1:
        x_id()
    elif z == 2:
        x_xb()
    elif z == 3 :
        x_dh()
#在数据库中增加学生信息
def add_sql(stu):
    connection = get_connect()
    cur = connection.cursor(cursor=pymysql.cursors.DictCursor)
    sql = 'insert into student(学号,姓名,性别,电话) values("%s","%s","%s", "%s")'
    sid = stu['学号']
    name = stu['姓名']
    sex = stu['性别']
    iphone = stu['电话']
    try:
        cur.execute(sql%(sid,name,sex,iphone))
        connection.commit()
    except Exception as e:
        connection.rollback()
        raise e
    finally:
        connection.close()
#删除数据库中的学生信息
def del_sql(id):
            connection = get_connect()
            cur = connection.cursor()
            sql = 'delete from student where 学号 = %s '
            try:
                cur.execute(sql%id)
                connection.commit()
            except Exception as e:
                connection.rollback()
                raise e
            finally:
                connection.close()
#查询数据库中的单个
def search_sql(name):
    connection = get_connect()
    cur = connection.cursor()
    sql = 'select * from student where 姓名 ="%s"'
    cur = connection.cursor(cursor=pymysql.cursors.DictCursor)
    cur.execute(sql%name)
    f = cur.fetchall()
    print(f)
    connection.commit()
    cur.close()
    connection.close()
#查询所有学生信息
def search_all():
    connection = get_connect()
    cur = connection.cursor()
    sql = 'select * from student ; '
    #转化字典通格式用
    cur = connection.cursor(cursor=pymysql.cursors.DictCursor)
    cur.execute(sql)
    f = cur.fetchall()
    for i in f:
        # print(i)
        name_list.append(i)
    connection.commit()
    cur.close()
    connection.close()
#对数据库信息的修改
def x_sql(z,xsh):
    conn = pymysql.connect(host='localhost', user="root", passwd="自己的密码", database='数据库名称')
    cur = conn.cursor()
    def xname(xsh):
        # xh = int(input('学号：'))
        name = input('确认姓名：')
        arg = (name, xsh)
        sql = "update  student set 姓名 = '%s' where 学号 = %s "
        result = cur.execute(sql % arg)
        print(result)
        if result == 1:
            print('修改成功！')
        else:
            print('修改失败！')
        conn.commit()
        cur.close()
        conn.close()
    def xsex(xsh):
        # xh = int(input('学号：'))
        sex = input('确认性别：')
        arg = (sex, xsh)
        sql = "update  student set 性别 = '%s' where 学号 = %s "
        result = cur.execute(sql % arg)
        print(result)
        if result == 1:
            print('修改成功！')
        else:
            print('修改失败！')
        conn.commit()
        cur.close()
        conn.close()
    def xiphone(xsh):
        # xh = int(input('学号：'))
        iphone = int(input('确认新电话：'))
        arg = (iphone, xsh)
        sql = "update  student set 电话 = %s where 学号 = %s "
        result = cur.execute(sql % arg)
        if result == 1:
            print('修改成功！')
        else:
            print('修改失败！')
        conn.commit()
        cur.close()
        conn.close()
    def zhu(z):
        # n = int(input('请输入功能序号：'))
        if z == 1:
            xname(xsh)
        elif z == 2:
            xsex(xsh)
        elif z == 3:
            xiphone(xsh)
    zhu(z)
def main(x):
    if x == 1:
        add_s()
    elif x  ==2:
        del_s()
    elif x== 3 :
        x_lsit()
    elif x ==4:
        search_stu()
    elif x == 5:
       #search_all()
        for i in name_list:
            print(i)
search_all()
while 1:
 mune()
 x = xh()
 main(x)
 # print(name_list)
 if x ==0:
     break