2019-07-16

from pymysql import *

import hashlib

import time

# import hashlib

zh = None

mm = None

exit = True

a = True

# conn = connect(host = 'localhost', port = 3306, database ='beicai', user = 'root' ,password = '123' ,charset = 'utf8')

# cs1 = conn.cursor()

def login():

    print('请注册!')

    print('请输入账号:')

    ac = input()

    while True:

        print('请输入密码:')

        pw = input()

        print('请确认密码:')

        pw1 = input()

        if pw != pw1:

            print('两次输入不一致,请重新输入！')

        else:

            global zh

            global mm

            sha1 =hashlib.sha1()

            sha1.update(pw.encode('utf-8'))

            pw_jm = sha1.hexdigest()

            zh = ac

            mm = pw_jm

            cs1.execute("insert into ac (account,password) values(%s,%s)",(zh,mm))

            # cs1.execute("insert into ac (account,password) values({},{})".format(zh, pw))

            print('注册成功！')

            break

    conn.commit()

    conn.close()

    cs1.close()

def log_in():

    # conn = connect(host='localhost', port=3306, database='beicai', user='root', password='123', charset='utf8')

    # cs1 = conn.cursor()

    print('请登录:')

    cs1.execute("select account,password from ac ")

    result = cs1.fetchall()

    while a:

        print('请输入账号:')

        ac = input()

        print('请输入密码:')

        pw1 = input()

        sha2 =hashlib.sha1()

        sha2.update(pw1.encode('utf-8'))

        pw1_jm =sha2.hexdigest()

        # cs1.execute("select account,password from ac ")

        # result = cs1.fetchall()

        # print(pw1_jm)

        for i in result :

            if i[0] == ac and i[1] == pw1_jm :

                print('登录成功！')

                global a

                a = False

        else :

            # i[0] != ac or i[1] != pw1_jm :

            print('账号或密码有误,请重新登录！')

def addInfo():

    conn = connect(host='localhost', port=3306, database='beicai', user='root', password='123', charset='utf8')

    cs1 = conn.cursor()

    print('请输入学生信息：')

    n = input('姓名：')

    a = input('年龄:')

    g = input('性别:')

    ad = input('住址:')

    zn = input('专业:')

    xn = input('学院:')

    zid = input('z_id:')

    xid = input('x_id:')

    cs1.execute('insert into student (name,age,sen,z_id,address) values (%s,%s,%s,%s,%s)',(n,a,g,zid,ad))

    cs1.execute('insert into profession (z_name,x_id) values (%s,%s)',(zn,xid))

    cs1.execute('insert into college (x_name) values (%s)',(xn))

    print('输入成功！')

    conn.commit()

    conn.close()

    cs1.close()

def query():

    # a =cs1.execute('select * from student')

    # result = cs1.fetchall()

    # for i in result :

    #    print(i)

    print('选择查询目标：')

    print('1.查询所有男性学员')

    print('2.查询人工智能学员的人数')

    print('3.查询所有学员的全部信息')

    print('4查询年龄在18-20之间的学员人数')

    print('5.查询年龄在20以上的男性并且专业是人工智能的学员')

    n = input()

    if n == '1' :

        cs1.execute("select group_concat(name),sen from student where sen = '男' ")

        result = cs1.fetchall()

        # for i in result :

        print()

        print()

        print('**查询所有男性学员**')

        print(*result)

    elif n == '2' :

        cs1.execute("select count(z_name) as 人工智能学院的人数 from student st ,profession pr, college co where(z_name = '人工智能') and (st.id = pr.id and pr.id = co.id)")

        result = cs1.fetchone()

        print()

        print()

        print('**人工智能学员的人数为**')

        print(*result ,end = '人')

        print()

    elif n == '3' :

        cs1.execute("select st.id,st.age,st.sen,st.address,pr.z_name,co.x_name from student st,profession pr , college co where st.id= pr.id and pr.id = co.id")

        result = cs1.fetchall()

        print()

        print()

        print('**所有学生的信息**')

        for i in result :

            print()

            print(*i)

    elif n == '4' :

        cs1.execute("select count(age) as 18至20岁之间的人数 from student st ,profession pr ,college co where (age between 18 and 20 ) and (st.id = pr.id and pr.id = co.id)")

        result = cs1.fetchone()

        print()

        print()

        print('**18-20岁学员的人数**')

        print(*result,end = '人')

        print()

    elif n == '5':

        cs1.execute("select st.id,st.age,st.sen,st.address,pr.z_name,co.x_name from student st,profession pr,college co where (age >20 )and (sen = '男') and (z_name = '人工智能') and (st.id = pr.id and pr.id = co .id)")

        result = cs1.fetchall()

        print()

        print()

        print('**20岁以上的男性人工智能学员的信息**')

        print()

        for i in result :

            print(*i)

    else :

        print('输入有误')

def option():

    print('请选择:')

    while exit:

        # print('为啥啊 ')

        conn = connect(host='localhost', port=3306, database='beicai', user='root', password='123', charset='utf8')

        cs1 = conn.cursor()

        print('1.添加学生信息')

        print('2.查询学生信息')

        print('3.返回上一层')

        print('4.退出系统')

        a = input()

        if a == '1':

            addInfo()

        elif a == '2':

            query()

        elif a == '3':

            break

        elif a == '4':

            global exit

            exit = False

            print('谢谢使用,再见！')

        else:

            print('输入有误,请重新输入！')

if __name__ =='__main__' :

    print('***北财学生管理系统***')

    print()

    print()

    print()

    while exit:

        conn = connect(host='localhost', port=3306, database='beicai', user='root', password='123', charset='utf8')

        cs1 = conn.cursor()

        print('1.注册')

        print('2.登录')

        print('3.退出')

        n = input()

        if n == '1':

            login()

        elif n == '2':

            log_in()

            option()

        elif n == '3':

            print('谢谢使用，再见！')

            break