在python中用数据库进行增删改查

在python中用数据库进行增删改查

'''
标题：在python中用数据库进行数据的增删改查
作者：温某人
日期：2021.12.29
'''
import pymysql

host = '127.0.0.1'  
port = 3306
db = 'student'
user = 'root'
password = 'wenxin200243.'
conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
def main():
    cursor = conn.cursor(pymysql.cursors.SSCursor)  
    return cursor

def add_student():#插入学生记录
    cursor = main()
    id1 = int(input('学号：'))
    name = input('姓名：')
    gem = input('性别：')
    age = int(input('年龄：'))
    class1 = input('班级：')
    major1 = input('专业：')
    college = input('学院：')
    add = cursor.execute('insert into stu (id, name, genden, age, class, major, collage) values(%s,%s,%s,%s,%s,%s,%s)',(id1, name, gem, age, class1, major1, college))
    if add >= 1:
        conn.commit()
        print('插入成功！') 
    else:
        print('插入失败！')

def Q_by_id():
    cursor = main()
    choice_id = int(input('请输入学号：'))
    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('是否继续查询(yes/no)：')
        if re == 'yes':
            Q_by_id()
        else:
            query_student()

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('是否继续查询yes/no：')
        if re == 'yes':
            Q_by_name()
        else:
            query_student()

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]))

def query_student():  #查询的菜单
    while True:
        print('查询学生记录')
        print('================')
        print('1、按学号查询学生记录')
        print('2、按姓名查询学生记录')
        print('3、查询全部学生记录')
        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 print_student():#显示所有的学生记录
    cursor=main()
    cursor.execute('select * from stu')
    students = cursor.fetchall()
    for stu in students:
        print(stu[0], stu[1], stu[2], stu[3], stu[4], stu[5], stu[6])
    
def delete_student():
    cursor = main()
    id = int(input('输入想要删除学生的学号：'))
    delete = cursor.execute('delete from stu where id = {}' .format(id))
    if delete == 1:
        conn.commit()
        print('删除成功！')
    else:
        print('删除失败！')

def delete1_student():
    print('============================')
    print('1、删除学生所有信息')
    print('2、回到初始界面')
    print('============================')
    mc4 = int(input('Input menu number:'))
    if mc4 == 1:
        delete_student()
    elif mc4 == 3:
        login()

def login():
    administartor = input('请输入用户名：')
    password = input('请输入密码：')
    if administartor == '温鑫' and password == 'wx200243':
        print("恭喜你成功登录系统!!")
        while True:
            print('学生信息管理系统')
            print('================')
            print('1、增加学生记录')
            print('2、查询学生记录')
            print('3、修改学生记录')
            print('4、删除学生记录')
            print('5、显示所有的学生记录')
            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('账号或密码错误！')
def update_student():
        cursor = main()
        cur= int(input('请输入想要修改学生的学号：'))
        cursor.execute('select * from stu where id = %s', (cur))
        print('==============')
        print('1、修改姓名')
        print('2、修改性别')
        print('3、修改年龄')
        print('4、修改班级')
        print('5、修改专业')
        print('6、修改学院')
        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('请输入修改后的专业：')
            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('请输入修改后的学院：')
            a = cursor.execute('update stu set college = %s where id = %s', (college1, cur))
            if a > 1:
                conn.commit()
                print('修改成功！')
            else:
                print('修改失败！')
        else:
            login()

while True:
    print('请选择以下菜单号:')
    print('========='*3)
    print('1、登录学生信息管理系统')
    print('2、退出学生信息管理系统')
    print('========='*3)
    mc1 = int(input('输入菜单号：'))
    if mc1 == 1:
        login()
    elif mc1 == 2:
        print('感谢使用学生信息管理系统！')
        break

运行后：