基于python与mysql交互的学生管理系统

"""
名片管理系统：
- 需要完成的基本功能：
  1. 添加名片
  2. 删除名片
  3. 修改名片
  4. 查询名片
  5. 退出系统
- 程序运行后，除非选择退出系统，否则重复执行功能
"""
#************************************************************************************************************
import pymysql
#函数定义
#************************************************************
#二选一选择界面函数
def func_judge2(strr):
    print("=" * 30)
    str1 = "名片"+strr
    print(str1.center(20))
    print("1.继续"+strr)
    print("2.返回主菜单")
    print("=" * 30)
#************************************************************
#三选一选择界面函数
def func_judge3(strr):
    print("=" * 30)
    str1 = "名片"+strr
    print(str1.center(20))
    print("1.确认"+strr)
    print("2.重新输入")
    print("3.返回主菜单")
    print("=" * 30)
#************************************************************
#n选项选择防用户输入错误导致报错的  操作输入函数
def num_judge1(n):
    str1 = "1"
    for i in range(n):
        str1 = str1 + str(i + 1)
    while True:
        judge1 = input("请输入要执行操作：")
        if (judge1 in str1) and (len(judge1) == 1):
            return  judge1
            break
        else:
            print("操作有误，请重新选择：")
#************************************************************
#登录函数
def denglu():
    flag = 1
    sql_select = "select name,pwd from teacher;"
    name = input("请输入用户名：")
    pwd = input("请输入密码：")
    try:
        cur.execute(sql_select)
        T = cur.fetchall()
        j = 0
        L_name = []
        for i in T:
            L_name.append(i[0])
        if name in L_name:
            for i in T:
                # print("用户名是：%s,密码是:%s"%(i[0],i[1]))
                if name == i[0]:
                    if i[1] == pwd:
                        print("登录成功！")
                        flag = 0
                        break
                    else:
                        print("密码输入错误！")
                        break
        else:
            print("用户不存在！请注册！")
    except Exception as e:
        print("操作有误，请稍后再试！")
    return flag
#************************************************************
#注册函数
def zhuce():
    aql_insert = "insert into teacher(name,pwd) VALUES(%s,%s)"
    name = input("请输入需要注册的昵称：")
    pwd = input("请输入密码：")
    pwd1 = input("请再次输入密码：")
    if pwd == pwd1:
        try:
            cur.execute(aql_insert,[name,pwd])
            conn.commit()
            print("注册成功!")
        except Exception as e:
            conn.rollback()
            print("用户已存在，请重新输入！")
    else:
        print("两次密码输入错误！")
#************************************************************
#查询函数
def chaxun(by,strr,str1):
    success = 0
    if by == "ID":
        sql_select = "select * from student where ID = %s;"
    elif by == "name":
        sql_select = "select * from student where name = %s;"
    elif by == "class1":
        sql_select = "select * from student where class1 = %s;"
    elif by == "xingshi":
        sql_select ="select * from student where name like %s;"
    try:
        cur.execute(sql_select,[strr])
        L = cur.fetchall()
        if L != ():
            success = 1
            print("你要%s的学生名片信息为："%str1)
            print("+"+("-"*15 + "+")*3)
            print("|" + " " * 5 + "name" + " " * 6 + "|" + " " * 6 + "ID" + " " * 7 + "|" + " " * 4 + "class1" + " " * 5 + "|")
            for i in range(len(L)):
                print("|",end = "")
                for j in range(3):
                    x = 15 - len(L[i][j])
                    x1 = x // 2
                    x2 = x - x1
                    print(" " * x1 + L[i][j] + " " * x2 + "|", end="")
                print()
            print("+"+("-" * 15 + "+") * 3)
        else:
            print("该学生不存在！")
    except Exception as e:
        conn.rollback()
        print("数据调取失败，请稍后再试！")
    return success
#************************************************************
#删除函数
def shanchu(id):
    sql_delete = "delete from student where ID = %s;"
    try:
        cur.execute(sql_delete,[id])
        conn.commit()
    except Exception as e:
        conn.rollback()
        print("数据删改失败，请稍后再试！")
#************************************************************************************************************
#系统准备与介绍
print("*"*40)
print("该系统为测试系统".center(30))
print("初始用户名：zs")
print("初始用户密码：123")
print("运行前请确保有数据库：python")
print("首次使用将会自动建立:teacher 与 student表")
print("*"*40)
#************************************************************************************************************
tea = 0
stu = 0
conn = pymysql.connect(host="localhost",
                           user="root",
                           passwd="123456",
                           port=3306,
                           db="python",
                           charset="utf8")
cur = conn.cursor()
try:
    sql_select = "create table teacher(" \
                 "name varchar(50) primary key," \
                 "pwd varchar(50)" \
                 ")CHARACTER set utf8;"
    cur.execute(sql_select)
    print("teacher表创建成功！")
    # ************************************************************
    # 用来存储用户信息：1.用户名 2.密码    并将信息存入数据库中
    dic = {"name": "zs", "pwd": "123"}
    str1 = dic['name']
    str2 = dic['pwd']
    sql_insert = "insert into teacher VALUE (%s,%s);"
    cur.execute(sql_insert, [str1, str2])
    tea = 1
except Exception as e:
    pass
conn.commit()
cur.close()
conn.close()
#************************************************************************************************************
conn = pymysql.connect(host="localhost",
                           user="root",
                           passwd="123456",
                           port=3306,
                           db="python",
                           charset="utf8")
cur = conn.cursor()
try:
    sql_select = "create table student(" \
                    "name varchar(50)," \
                    "ID varchar(50) primary key," \
                     "class1 varchar(50)" \
                    ")character set utf8;"
    cur.execute(sql_select)
    print("student表创建成功！")
    # ************************************************************
    # 学生信息 并将学生信息存入数据库中
    card_list = [{'name': 'zs', 'ID': '001', 'class1': '1701'}, {'name': 'ls', 'ID': '002', 'class1': '1701'},{'name': 'ww', 'ID': '003', 'class1': '1701'}]
    for i in range(3):
        str1 = card_list[i]['name']
        str2 = card_list[i]['ID']
        str3 = card_list[i]['class1']
        sql_insert = "insert into student VALUE (%s,%s,%s);"
        cur.execute(sql_insert, [str1, str2, str3])
    tea = 1
except Exception as e:
    pass
conn.commit()
cur.close()
conn.close()

if tea == 1 or stu == 1:
    print("欢迎首次使用名片管理系统V8.0 !")
#************************************************************************************************************
#设定用户是否成功登录的标志
flag = 1
#登录界面
while flag:
    # 登录界面
    print("=" * 40)
    print("名片管理系统 V8.0".center(30))
    print("1.登录")
    print("2.注册")
    print("=" * 40)
    caozuo = input("请输入要执行的操作：")
# ************************************************************************************************************
    #登录操作
    if caozuo == "1":
        conn = pymysql.connect(host="localhost",
                               user="root",
                               passwd="123456",
                               port=3306,
                               db="python",
                               charset="utf8")
        cur = conn.cursor()
        # *********************************************************
        flag = denglu()
        # **********************************************************
        cur.close()
        conn.close()
#************************************************************************************************************
    #注册操作
    elif caozuo == '2':
        conn = pymysql.connect(host="localhost",
                               user="root",
                               passwd="123456",
                               port=3306,
                               db="python",
                               charset="utf8")
        cur = conn.cursor()
        # *********************************************************
        zhuce()
        # **********************************************************
        cur.close()
        conn.close()
#************************************************************************************************************
# 登录之后的界面
while True:
    print("="*40)
    print("名片管理系统 V1.0".center(30))
    print("1.添加名片")
    print("2.删除名片")
    print("3.修改名片")
    print("4.查询名片")
    print("5.退出")
    print("="*40)
    caozuo = input("请输入要执行的操作：")
# ************************************************************************************************************
    #这是添加操作
    if caozuo == "1":
       judge1 = "1"
       while judge1 == "1":
            ID = input("请输入学号：")
            name = input("请输入姓名：")
            class1 = input("请输入班级：")
            # *********************************************************
            conn = pymysql.connect(host="localhost",
                                   user="root",
                                   passwd="123456",
                                   port=3306,
                                   db="python",
                                   charset="utf8")
            cur = conn.cursor()
            # ******************
            aql_insert = "insert into student(name,ID,class1) VALUES(%s,%s,%s)"
            try:
                cur.execute(aql_insert, [name,ID,class1])
                conn.commit()
                print("添加成功！")
            except Exception as e:
                conn.rollback()
                print("录入失败！该学生ID已存在！")
            # ******************
            cur.close()
            conn.close()
            # **********************************************************
            func_judge2("输入")
            judge1 = num_judge1(2)
# ************************************************************************************************************
    #删除操作
    elif caozuo == "2":
        while True:
            id = input("请输入要删除学生名片的学号:")
            judge5 = "4"
            judge4 = "1"  # 1继续执行
            # *********************************************************
            conn = pymysql.connect(host="localhost",
                                   user="root",
                                   passwd="123456",
                                   port=3306,
                                   db="python",
                                   charset="utf8")
            cur = conn.cursor()
            # ******************
            success = chaxun("ID",id,"删除")    #先查询
            # ******************
            cur.close()
            conn.close()
            # **********************************************************
            if success == 1:
                func_judge3("删除")
                judge5 = num_judge1(3)
            else:
                func_judge2("删除")
                judge4 = num_judge1(2)
            if judge5 == "1":
                # *********************************************************
                conn = pymysql.connect(host="localhost",
                                       user="root",
                                       passwd="123456",
                                       port=3306,
                                       db="python",
                                       charset="utf8")
                cur = conn.cursor()
                # ******************
                shanchu(id)  #后删除
                # ******************
                cur.close()
                conn.close()
                # **********************************************************
                print("学生信息删除成功!")
                func_judge2("删除")
                judge4 = num_judge1(2)
            elif judge5 == "3":
                break
            if judge4 == "2":
                break
# ************************************************************************************************************
    #修改操作
    elif caozuo == "3":
        while True:
            id = input("请输入要修改学生名片的学号:")
            judge5 = "4"
            judge4 = "1"  # 1继续执行
            # *********************************************************
            conn = pymysql.connect(host="localhost",
                                   user="root",
                                   passwd="123456",
                                   port=3306,
                                   db="python",
                                   charset="utf8")
            cur = conn.cursor()
            # ******************
            success = chaxun("ID",id,"修改")    #先查询
            # ******************
            cur.close()
            conn.close()
            # **********************************************************
            if success == 1:
                func_judge3("修改")
                judge5 = num_judge1(3)
            else:
                func_judge2("修改")
                judge4 = num_judge1(2)
            if judge5 == "1":
                name = input("请输入姓名：")
                class1 = input("请输入班级：")
                # *********************************************************
                conn = pymysql.connect(host="localhost",
                                       user="root",
                                       passwd="123456",
                                       port=3306,
                                       db="python",
                                       charset="utf8")
                cur = conn.cursor()
                # ******************
                #后修改
                sql_update = "update student set name = %s,class1 = %s where ID = %s;"
                cur.execute(sql_update, [name,class1,id])
                conn.commit()
                # ******************
                cur.close()
                conn.close()
                # **********************************************************
                print("学生信息修改成功!")
                func_judge2("修改")
                judge4 = num_judge1(2)
            elif judge5 == "3":
                break
            if judge4 == "2":
                break
# ************************************************************************************************************
    #查询操作
    elif caozuo == "4":
        judge7 = "1"
        while judge7 == "1":
            judge10 = 1
            print("=" * 30)
            print("名片查询".center(20))
            print("1.学生信息一览")
            print("2.按姓名查询")
            print("3.按学号查询")
            print("4.按班级查询")
            print("5.按姓氏查询")
            print("6.返回主菜单")
            print("=" * 30)
            caozuo1 = input("请输入你要执行的操作：")
            # *********************************************************
            conn = pymysql.connect(host="localhost",
                                   user="root",
                                   passwd="123456",
                                   port=3306,
                                   db="python",
                                   charset="utf8")
            cur = conn.cursor()
            # ******************
            if caozuo1 == "1":
                sql_select = "select * from student;"
                cur.execute(sql_select)
                L = cur.fetchall()
                print("+"+("-" * 15 + "+") * 3)
                print("|"+" "*5+"name"+" "*6+"|"+" "*6+"ID"+" "*7+"|"+" "*4+"class1"+" "*5+"|")
                for i in range(len(L)):
                    print("|", end="")
                    for j in range(3):
                        x = 15 - len(L[i][j])
                        x1 = x//2
                        x2 = x - x1
                        print(" "*x1+L[i][j]+" "*x2+"|",end = "")
                    print()
                print("+"+("-" * 15 + "+") * 3)
            elif caozuo1 == "2":
                name = input("请输入查询学生姓名：")
                success = chaxun("name", name, "查询")
            elif caozuo1 == "3":
                ID = input("请输入查询学生学号：")
                success = chaxun("ID", ID, "查询")
            elif caozuo1 == "4":
                class1 = input("请输入查询学生班级：")
                success = chaxun("class1", class1, "查询")
            elif caozuo1 == "5":
                xingshi = input("请输入学生的姓氏：")
                success = chaxun("xingshi", xingshi+"%", "查询")
            elif caozuo1 == "6":
                break
            else:
                print("请输入正确操作！")
            # ******************
            cur.close()
            conn.close()
            # **********************************************************
            func_judge2("查询")
            judge7 = num_judge1(2)
# ************************************************************************************************************
    elif caozuo == "5":
        print("谢谢使用，已成功退出！")
        break
    else:
         print("操作指令有误，请重新输入！")