import pymysql

#定義數(shù)據(jù)庫鏈接參數(shù)
host = '127.0.0.1'  #或者用local host
port = 3306
db = 'student'
user = 'root'
password = 'lyt2529165097'
conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
def main():
    cursor = conn.cursor(pymysql.cursors.SSCursor)  #流式游標(biāo),默認(rèn)返回元組
    return cursor
通過在spyder里面輸入主機(jī)號 端口號 主機(jī)名 密碼等就可以登錄進(jìn)去了

while True:
    print('請選擇以下菜單號:')
    print('========='*3)
    print('1、登錄學(xué)生信息管理系統(tǒng)')
    print('2、退出學(xué)生信息管理系統(tǒng)')
    print('========='*3)
    mc1 = int(input('輸入菜單號：'))
    if mc1 == 1:
        login()
    elif mc1 == 2:
        print('感謝使用學(xué)生信息管理系統(tǒng)！')
        break

def login():
    administartor = input('請輸入用戶名：')
    password = input('請輸入密碼：')
    if administartor == '雷云騰' and password == 'lyt2529165097':

#登入后的菜單----展開
def login():
    administartor = input('請輸入用戶名：')
    password = input('請輸入密碼：')
    if administartor == '雷云騰' and password == 'lyt2529165097':
        print("恭喜你成功登錄系統(tǒng)!!")
        while True:
            print('學(xué)生信息管理系統(tǒng)')
            print('================')
            print('1、增加學(xué)生記錄')
            print('2、查詢學(xué)生記錄')
            print('3、修改學(xué)生記錄')
            print('4、刪除學(xué)生記錄')
            print('5、顯示所有的學(xué)生記錄')
            print('6、返回上級菜單')
            print('=================')
            mc2 = int(input('輸入菜單號：'))
            if mc2 == 1:
                add_student()
            elif mc2 == 2:
                query_student()
            elif mc2 == 3:
                update_student
            elif mc2 == 4:
                delete_student()
            elif mc2==5:
                print_student()
            else:
                break
    else:
        print('賬號或密碼錯誤！')

#插入學(xué)生記錄
def add_student():
    cursor = main()
    id = int(input('學(xué)號：'))
    name = input('姓名：')
    gender = input('性別：')
    age = int(input('年齡：'))
    class1 = input('班級：')
    major = input('專業(yè)：')
    college = input('學(xué)院：')
    add = cursor.execute('insert into stu (id, name, gender, age, class1, major, college)\
                   values(%s,%s,%s,%s,%s,%s,%s)',(id, name, gender, age, class1, major, college))
    if add == 1:
        conn.commit()
        print('插入成功！')
    else:
        print('插入失?。?)

#按學(xué)號查詢
def Q_by_id():
    cursor = main()
    choice_id = int(input('請輸入學(xué)號：'))
    cursor.execute('select * from stu where id =%s',(choice_id))
    students = cursor.fetchall()
    for stu in students:
        print(stu[0], stu[1], stu[2], stu[3], stu[4], stu[5], stu[6])
        print('查詢成功')
         re = input('是否繼續(xù)查詢(yes/no)：')
        if re == 'yes':
            Q_by_id()
        else:
            query_student()

#按姓名查詢（以防學(xué)號輸入錯誤）
def Q_by_name():
    cursor = main()
    choose_name = input('請輸入姓名：')
    cursor.execute('select * from stu where name =%s',(choose_name))
    students = cursor.fetchall()
    for stu in students:
        print(stu[0], stu[1], stu[2], stu[3], stu[4], stu[5], stu[6])
        print()
        re = input('是否繼續(xù)查詢yes/no：')
        if re == 'yes':
            Q_by_name()
        else:
            query_student()

#查詢所有學(xué)生
def Q_all():
    cursor = main()
    cursor.execute('select * from stu')
    students = cursor.fetchall()
    for student in students:
        print('\t{}\t{}\t{}\t{}\t{}\t{}\t{}' .format(student[0], student[1], student[2], student[3], student[4], student[5], student[6]))

 re = input('是否繼續(xù)查詢(yes/no)：')
        if re == 'yes':
            Q_by_id()
        else:
            query_student()

#查詢的菜單
def query_student():
    while True:
        print('查詢學(xué)生記錄')
        print('================')
        print('1、按學(xué)號查詢學(xué)生記錄')
        print('2、按姓名查詢學(xué)生記錄')
        print('3、查詢?nèi)繉W(xué)生記錄')
        print('4、返回上級菜單')
        print('=================')
        mc3 = int(input('請輸入查詢的菜單號：'))
        if mc3 == 1:
            Q_by_id()
        elif mc3 == 2:
            Q_by_name()
        elif mc3 == 3:
            Q_all()
        else:
            break

#刪除的菜單
def delete1_student():
    print('============================')
    print('1、刪除學(xué)生所有信息')
    print('2、回到初始界面')
    print('============================')
    mc4 = int(input('Input menu number:'))
    if mc4 == 1:
        delete_student()
    elif mc4 == 3:
        login()

def update_student():
        cursor = main()
        cur= int(input('請輸入想要修改學(xué)生的學(xué)號：'))
        cursor.execute('select * from stu where id = %s', (cur))
        if cursor.fetchall() == []:
            print('未查找到學(xué)號是{}的學(xué)生'.format(cur))
            mc3 = input('是否重新查詢？(yes/no)')
            if mc3 != 'no':
                update_student()
            else:
                login()
        else:
            print('==============')
            print('1、修改姓名')
            print('2、修改性別')
            print('3、修改年齡')
            print('4、修改班級')
            print('5、修改專業(yè)')
            print('6、修改學(xué)院')
            print('7、返回上級菜單')
            print('==============')
            mc2 = int(input('請輸入菜單號：'))
            if mc2 == 1:
                name = input('請輸入修改后的名字：')
                a = cursor.execute('update stu set name = %s where id = %s', (name, cur))
                if a == 1:
                    conn.commit()
                    print('修改成功！')
                else:
                    print('修改失??！')
            elif mc2 == 2:
                gender1 = input('請輸入修改后的性別：')
                a = cursor.execute('update stu set genden = %s where id = %s', (gender1, cur))
                if a > 1:
                    conn.commit()
                    print('修改成功！')
                else:
                    print('修改失?。?)
            elif mc2 == 3:
                age1 = int(input('請輸入修改后的年齡：'))
                a = cursor.execute('update stu set age = %s where id = %s', (age1, cur))
                if a > 1:
                    conn.commit()
                    print('修改成功！')
                else:
                    print('修改失敗！')
            elif mc2 == 4:
                class1 = input('請輸入修改后的班級：')
                a = cursor.execute('update stu set class = %s where id = %s', (class1, cur))
                if a > 1:
                    conn.commit()
                    print('修改成功！')
                else:
                    print('修改失?。?)
            elif mc2 == 5:
                major1 = input('請輸入修改后的專業(yè)：')
                a = cursor.execute('update stu set major = %s where id = %s', (major1, cur))
                if a > 1:
                    conn.commit()
                    print('修改成功！')
                else:
                    print('修改失?。?)
            elif mc2 == 6:
                college1 = input('請輸入修改后的學(xué)院：')
                a = cursor.execute('update stu set college = %s where id = %s', (college1, cur))
                if a > 1:
                    conn.commit()
                    print('修改成功！')
                else:
                    print('修改失??！')
            else:
                pass#占一個空位符