学生管理系统MySQL版

# 第一步必须先要引入pyMySQL模板


import pymysql
import random


def p_in():
    print("1.注册")
    print("2.登录")
    print("3.退出")

def zhuce_1():
    password = input("请输入你的密码")
    if len(password) <6:
        print("密码长度最少为6")
    else:
        str_2 = ""
        while 1:

            num = str(random.randint(1, 9))
            str_2 += num
            if len(str_2) == 4:
                print("您的账号为" + str_2)
                break
        con = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉', charset='utf8')
        # 获取游标
        cur = con.cursor()

        sql = "insert into dome_1 (passwd,users) values (%s,%s)"
        parm = (password,str_2)
        cur.execute(sql, parm)
        con.commit()
        n = input("是否按照此账号登录 y or n")
        if n == "y":
            def main():
                print("欢迎使用学生管理系统")
                print("++++++++++")
                print("1.添加学生信息")
                print("2.删除学生信息")
                print("3.修改学生信息")
                print("4.查询学生信息")
                print("5.查询所有学生信息")
                print("6.退出")
            def add_info():
                # 建立连接
                con =  pymysql.connect(host='39.98.39.173', port=13306, user='root',passwd='root', db='刘烽吉', charset = 'utf8')
                # 获取游标
                cur = con.cursor()
                name = input("请输入添加学生的名字")
                age = int(input("请输入学生的年龄"))
                sex = input("请输入学生的性别")
                sql = "insert into students_2 (name,age,sex) values (%s,%s,%s)"
                parm = (name,age,sex)
                cur.execute(sql,parm)
                con.commit()
                con.close()
                cur.close()
            def del_info():
                # 建立连接
                con = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉', charset='utf8')
                # 获取游标
                cur = con.cursor()
                name = input("请输入删除学生的名字")

                sql = 'delete from students_2 where name = %s'

                cur.execute(sql, name)
                con.commit()
                con.close()
                cur.close()




            def xiu_info():
                name = input("请输入学生的姓名")

                num_6 = input("请输入修改后的name（回车不修改）:")
                if len(num_6) != 0:
                    conn = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉')
                    cur = conn.cursor()
                    sql = ("update student_3 set name = %s where name = %s")
                    cur.execute(sql, (num_6, name))
                    conn.commit()
                    cur.close()
                    conn.close()
                num_7 = input("请输入修改后的age（回车不修改）:")
                if len(num_7) != 0:
                    conn = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉')
                    cur = conn.cursor()
                    sql = ("update student_3 set age = %s where name = %s")
                    cur.execute(sql, (num_7, name))
                    conn.commit()
                    cur.close()
                    conn.close()
                num_8 = input("请输入修改后的sex（回车不修改）:")
                if len(num_8) != 0:
                    conn = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉')
                    cur = conn.cursor()
                    sql = ("update student_3 set sex = %s where name= %s")
                    cur.execute(sql, (num_8, name))
                    conn.commit()
                    cur.close()
                    conn.close()





            def one_info():
                name = input("请输入学生的姓名")
                con = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉', charset='utf8')
                # 获取游标
                cur = con.cursor()
                sql = "select * from students_2 where name = %s"

                num = cur.execute(sql,(name))
                result = cur.fetchall()
                print(result)
                print(num)
                con.commit()

                con.close()
                cur.close()
            def all_infos():
                # 建立连接
                con = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉', charset='utf8')
                # 获取游标
                cur = con.cursor()
                num = cur.execute("select * from students_2")
                print(num)
                result = cur.fetchall()
                print(result)
                con.close()
                cur.close()
            while 1:
                main()
                num = input("请输入你要执行的操作")
                if num =="6":
                    num_2 = input("您确定要退出嘛？ y or n")
                    if num_2 == "y":
                        print("感谢使用")
                        break
                    elif num_2 == "n":
                        continue
                elif num == "1":
                    add_info()
                elif num == "2":
                    del_info()
                elif num == "3":
                    xiu_info()
                elif num == "4":
                    one_info()
                elif num == "5":
                    all_infos()


        con.close()
        cur.close()

def deng_1():
    zhanghao = input("请输入您的账号")
    mima = input("请输入您的密码")

    con = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉', charset='utf8')
    # 获取游标
    cur = con.cursor()

    sql = "select passwd from dome_1 where users = %s"
    parm = (zhanghao)
    cur.execute(sql, parm)
    result = cur.fetchone()
    if mima in result:
        print("登录成功")
        def main():
            print("++++++++++")
            print("1.添加学生信息")
            print("2.删除学生信息")
            print("3.修改学生信息")
            print("4.查询学生信息")
            print("5.查询所有学生信息")
            print("6.退出")
        def add_info():
            # 建立连接
            con =  pymysql.connect(host='39.98.39.173', port=13306, user='root',passwd='root', db='刘烽吉', charset = 'utf8')
            # 获取游标
            cur = con.cursor()
            name = input("请输入添加学生的名字")
            age = int(input("请输入学生的年龄"))
            sex = input("请输入学生的性别")
            sql = "insert into students_2 (name,age,sex) values (%s,%s,%s)"
            parm = (name,age,sex)
            cur.execute(sql,parm)
            con.commit()
            con.close()
            cur.close()
        def del_info():
            # 建立连接
            con = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉', charset='utf8')
            # 获取游标
            cur = con.cursor()
            name = input("请输入删除学生的名字")

            sql = 'delete from students_2 where name = %s'

            cur.execute(sql, name)
            con.commit()

            con.close()
            cur.close()

        def xiu_info():
            name = input("请输入学生的姓名")

            num_6 = input("请输入修改后的name（回车不修改）:")
            if len(num_6) != 0:
                conn = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉')
                cur = conn.cursor()
                sql = ("update students_2 set name = %s where name = %s")
                cur.execute(sql, (num_6, name))
                conn.commit()
                cur.close()
                conn.close()
            num_7 = input("请输入修改后的age（回车不修改）:")
            if len(num_7) != 0:
                conn = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉')
                cur = conn.cursor()
                sql = ("update student_s2 set age = %s where name = %s")
                cur.execute(sql, (num_7, name))
                conn.commit()
                cur.close()
                conn.close()
            num_8 = input("请输入修改后的sex（回车不修改）:")
            if len(num_8) != 0:
                conn = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉')
                cur = conn.cursor()
                sql = ("update students_2 set sex = %s where name= %s")
                cur.execute(sql, (num_8, name))
                conn.commit()
                cur.close()
                conn.close()


        def one_info():
            name = input("请输入学生的姓名")
            con = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉', charset='utf8')
            # 获取游标
            cur = con.cursor()
            sql = "select * from students_2 where name = %s"

            num = cur.execute(sql,(name))
            result = cur.fetchall()
            print(result)
            print(num)
            con.commit()

            con.close()
            cur.close()
        def all_infos():
            # 建立连接
            con = pymysql.connect(host='39.98.39.173', port=13306, user='root', passwd='root', db='刘烽吉', charset='utf8')
            # 获取游标
            cur = con.cursor()
            num = cur.execute("select * from students_2")
            print(num)
            result = cur.fetchall()
            print(result)
            con.close()
            cur.close()
        while 1:
            main()
            num = input("请输入你要执行的操作")
            if num == "1":
                add_info()
            elif num == "2":
                del_info()
            elif num == "3":
                xiu_info()
            elif num == "4":
                one_info()
            elif num == "5":
                all_infos()
    else:
        print("登录失败")
    con.close()
    cur.close()
while 1:
    p_in()
    num = input("请选择你要执行的操作")
    if num == "1":
        zhuce_1()
    elif num == "2":
        deng_1()
    elif num == "3":
        break