基于mysql的学生管理系统（python）版本

mysql 数据库：

在mysql数据库中新建一个数据库test，在test数据库中建立一个表名字叫做testmy
表中的字段如图所示。

import pymysql

# 连接数据库
# 验证是否能成功连接数据库
# def connection():
#     try:
#         conn = pymysql.Connect(
#             host='localhost',
#             port=3306,
#             user='root',
#             password='root',
#             db='test',
#             charset='utf8'
#         )
#         print('数据库连接成功！！')
#     except pymysql.Error as e:
#         print('数据库连接失败！！+str(e)')
#     #关闭数据库
#     conn.close()
# connection()

# 1.添加数据
# 数据库连接

# 数据的插入
def menu_show():
    print("*" * 45)
    print('1.添加人员数据')
    print('2.删除人员数据')
    print('3.查询人员数据')
    print('4.修改人员数据')
    print('0.退出系统')
    print("*" * 45)


# 数据的插入
def add_data():
    conn = pymysql.Connect(
        host='localhost',
        port=3306,
        user='root',
        password='root',
        db='test',
        charset='utf8'
    )
    # 拿到游标
    cursor = conn.cursor()
    name = input('请输入姓名:')
    sex = input('输入性别:')
    age = int(input('请输入年龄:'))
    sql = 'insert into testmy(name,sex,age) value(%s,%s,%s)'
    value = (name, sex, age)
    try:
        cursor.execute(sql, value)
        conn.commit()
        # 提交操作
        print('数据插入成功!!')
    except pymysql.Error as e:
        print("数据插入失败：" + e)
        # Rollback就是与commit相反，不提交事务，回到最初的时候
        conn.rollback()
        # 关闭操作
    conn.close()


# add_data()

# 数据的删除
def del_data():
    conn = pymysql.Connect(
        host='localhost',
        port=3306,
        user='root',
        password='root',
        db='test',
        charset='utf8'
    )
    # 拿到游标
    cursor = conn.cursor()
    del_name = input('请输入要删除的姓名:')
    sql = 'delete from testmy where name=%s'
    value = (del_name)
    try:
        cursor.execute(sql, value)
        print("删除成功!!!")
        # 提交数据操作
        conn.commit()
    except pymysql.Error as e:
        print('删除失败!' + e)
        conn.rollback()
    conn.close()


# del_data()

# 数据的查询
def find_data():
    conn = pymysql.Connect(
        host='localhost',
        port=3306,
        user='root',
        password='root',
        db='test',
        charset='utf8'
    )
    # 拿到游标
    cursor = conn.cursor()
    # 单个查询
    sql = 'select * from testmy where name=%s'
    find_name = input('请输入要查询的姓名:')
    value = (find_name)
    try:
        cursor.execute(sql, value)
        print('Count:', cursor.rowcount)
        # 这里可以看到数据库存储的都是元组
        one = cursor.fetchone()
        print('One:', one)
    except:
        print('Error')
    print('数据库已关闭')
    conn.close()
    # 查询全部
    # sql='select * from testmy'
    # try:
    #     cursor.execute(sql)
    #     results=cursor.fetchall()
    #     for row in results:
    #         #放到字典中
    #         name=row[1]
    #         sex=row[2]
    #         age=row[3]
    #         print('name:%s,sex:%s,age:%s'%(name,sex,age))
    #         # print('name:',row[1])
    #         # print('sex:', row[2])
    #         # print('age:', row[3])
    # except:
    #     print('Error')
    
# find_data()

# 修改数据
def mod_data():
    conn = pymysql.Connect(
        host='localhost',
        port=3306,
        user='root',
        password='root',
        db='test',
        charset='utf8'
    )
    # 拿到游标
    cursor = conn.cursor()
    id = input("请输入要更新的id:")
    key = input("请输入要更新的字段名：")
    value = input("请输入更新后的值:")
    try:
        sql = "update testmy set " + key + "='" + value + "' where id=" + id
        print(sql)
        cursor.execute(sql)
        conn.commit()  # 提交数据
        print('数据更新成功')

    except:
        print('数据更新失败')
    conn.rollback()
    cursor.close()
    conn.close()
# mod_data()

# 退出系统
def exit_data():
    print('欢迎下次使用')
    exit()


menu_show()
while True:
    num = int(input('请输入要进行的操作序号:'))
    if num == 1:
        add_data()
    if num == 2:
        del_data()
    if num == 3:
        find_data()
    if num == 4:
        mod_data()
    if num == 0:
        exit_data()