python基于mysql数据库实现无页面学生信息管理系统以及简单操作mysql
前言一、分步讲解0.创建两张数据表1.大概思路2.首先连接数据库3.查看所有学生成绩信息4.查看所有学生个人信息5.查看指定学生成绩信息6.添加学生个人信息7.添加学生成绩信息8.修改学生个人信息9.修改学生成绩信息10.删除学生成绩信息11.删除学生个人信息 二.完整系统代码总结
前言
本篇文章能让你快速掌握利用python对数据库的增删改查。以及一个简单的案例。本节案例共有两个sql数据表，一个是学生个人信息的存储表，一个是学生成绩的存储表。
一、分步讲解
0.创建两张数据表
1.大概思路
我们会基于这些功能，一步一步进行功能的实现。
2.首先连接数据库
import pymysql # python对mysql进行操作的库# connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', # 填写你真实的 password='', # 填写你的密码 db='students', # 填写你的 charset='utf8')print("连接成功")3.查看所有学生成绩信息
import pymysqlconnection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='students', charset='utf8')c1 = connection.cursor()count = c1.execute('select id,st_uid,name,chinese,math,english,total from students.student_all where id>=1')for _ in range(1): result = c1.fetchall() print(result) # 元组对象4.查看所有学生个人信息
import pymysqlconnection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='students', charset='utf8')c1 = connection.cursor()count = c1.execute('select id,st_uid,name,sex,birthday,tel,path,remark from students.student_data where id>=1')print(count)for _ in range(1): result = c1.fetchall() print(result) 5.查看指定学生成绩信息
根据输入的姓名，进行查找
import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='students',charset='utf8')print("连接成功。")name = input("请输入学生姓名:")cursor = conn.cursor()sql = cursor.execute(f'select id,st_uid,name,sex,date_format(birthday,"%Y-%m-%d"),tel,path,remark from students.student_data where name like "%'+name+'%";')print(cursor.fetchone( ))6.添加学生个人信息
import pymysqlconnection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='students', charset='utf8')print("连接成功。")id= int(input("请输入id:"))st_uid= int(input("请输入uid:"))name= input("请输入名字:")sex = input("请输入性别:")birthday = input("请输入生日:")tel = input("请输入电话(11位):")path = input("请输入地址:")remark = input("请输入简介:")cursor = connection.cursor()one_data = f"insert into students.student_data values (%s,%s,%s,%s,%s,%s,%s,%s); "cursor.execute(one_data,(id,st_uid,name,sex,birthday,tel,path,remark))print("写入成功")7.添加学生成绩信息
import pymysqlconnection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='students', charset='utf8')print("连接成功。")id= int(input("请输入id:"))st_uid= int(input("请输入uid:"))name= input("请输入名字:")chinese = int(input("请输入语文成绩"))math = int(input("请输入数学成绩"))english = int(input("请输入英语成绩"))total = chinese + math + englishcursor = connection.cursor()one_data = f"insert into students.student_all values (%s,%s,%s,%s,%s,%s,%s); "cursor.execute(one_data,(id,st_uid,name,chinese,math,english,total))print("写入成绩成功")8.修改学生个人信息
import pymysqlconnection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='students', charset='utf8')print("连接成功。")cursor = connection.cursor()st_uid = input("请输入要修改的学号:")sql0 = cursor.execute(f'select id,st_uid,name,sex,date_format(birthday,"%Y-%m-%d"),tel,path,remark from students.student_data where st_uid like "%'+st_uid+'%";')print(cursor.fetchone())name= input("请输入名字:")sex = input("请输入性别:")birthday = input("请输入生日:")tel = input("请输入电话(11位):")path = input("请输入地址:")remark = input("请输入简介:")sql = 'UPDATE students.student_data SET name="%s", sex="%s", birthday="%s" , tel="%s" , path="%s", remark="%s" WHERE st_uid="%s"' % (name,sex,birthday,tel,path,remark,st_uid)rows = cursor.execute(sql)print("修改完成")9.修改学生成绩信息
import pymysqlconnection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='students', charset='utf8')print("连接成功。")cursor = connection.cursor()st_uid = input("请输入要修改的学号:")sql0 = cursor.execute(f'select * from students.student_all where st_uid like "%'+st_uid+'%";')print(cursor.fetchone())name= input("请输入名字:")chinese = int(input("请输入语文成绩:"))math = int(input("请输入数学成绩:"))english = int(input("请输入英语成绩:"))total = chinese + math + englishprint()sql = 'UPDATE students.student_all SET name="%s",chinese="%s",math="%s",english="%s",total="%s" WHERE st_uid="%s"' % (name,chinese,math,english,total,st_uid)rows = cursor.execute(sql)print("修改完成")10.删除学生成绩信息
import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='students',charset='utf8')print("连接成功。")st_uid = int(input("请输入要删除的学号:"))cursor = conn.cursor()sql = 'delete from students.student_all where st_uid="%d"'% st_uidcursor.execute(sql)conn.commit()print("删除成功")cursor.close()conn.close()11.删除学生个人信息
import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='students',charset='utf8')print("连接成功。")st_uid = int(input("请输入要删除的学号:"))cursor = conn.cursor()sql = 'delete from students.student_data where st_uid="%d"'% st_uidcursor.execute(sql)conn.commit()print("删除成功")cursor.close()conn.close()二.完整系统代码
import timeimport pymysql# 1. 连接数据库def connect(): try: connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='students', charset='utf8') return connection except Exception: print("连接数据库有问题，请检查配置文件。")def cursor(): print("连接成功。") c1 = connect( ).cursor( ) return c1def m_close(): cursor().close( ) connect().close( ) print("已退出")# 00_1查看所有学生成绩信息def view_all(): c1 = cursor() try: count = c1.execute('select id,st_uid,name,chinese,math,english,total from students.student_all where id>=1') for _ in range(1): result = c1.fetchall( ) print(result) # 元组对象 except Exception: print("出问题了，请检查")#01_2查看所有学生个人信息def data_all(): c1 = cursor() try: count = c1.execute('select id,st_uid,name,sex,date_format(birthday,"%Y-%m-%d"),tel,path,remark from students.student_data where id>=1') for _ in range(1): result = c1.fetchall( ) print(result) except Exception: print("出问题了，请检查")#03_3 查看指定学生成绩信息def one_achievement(): name = input("请输入学生姓名:") c1 = cursor() try: sql = c1.execute( f'select id,st_uid,name,sex,date_format(birthday,"%Y-%m-%d"),tel,path,remark from students.student_data where name like "%' + name + '%";') print(c1.fetchone( )) except Exception: print("出问题了，请检查")#04添加学生个人信息def add_st_data(): id = int(input("请输入id:")) st_uid = int(input("请输入uid:")) name = input("请输入名字:") sex = input("请输入性别:") birthday = input("请输入生日:") tel = input("请输入电话(11位):") path = input("请输入地址:") remark = input("请输入简介:") c1 = cursor() try: one_data = f"insert into students.student_data values (%s,%s,%s,%s,%s,%s,%s,%s); " c1.execute(one_data, (id, st_uid, name, sex, birthday, tel, path, remark)) print("写入成功") except Exception: print("出问题了，请检查")# 05添加学生成绩信息def add_achievement(): id = int(input("请输入id:")) st_uid = int(input("请输入uid:")) name = input("请输入名字:") chinese = int(input("请输入语文成绩")) math = int(input("请输入数学成绩")) english = int(input("请输入英语成绩")) total = chinese + math + english c1 = cursor() try: one_data = f"insert into students.student_all values (%s,%s,%s,%s,%s,%s,%s); " c1.execute(one_data, (id, st_uid, name, chinese, math, english, total)) print("写入成绩成功") except Exception: print("出问题了，请检查")#06修改学生个人信息def edit_one_grades(): c1 = cursor() st_uid = input("请输入要修改的学号:") sql0 = c1.execute(f'select * from students.student_data where st_uid like "%' + st_uid + '%";') print(c1.fetchone( )) name = input("请输入名字:") sex = input("请输入性别:") birthday = input("请输入生日:") tel = input("请输入电话(11位):") path = input("请输入地址:") remark = input("请输入简介:") try: sql = 'UPDATE students.student_data SET name="%s",sex="%s",birthday="%s",tel="%s",path="%s",remark="%s" WHERE st_uid="%s"' % ( name, sex, birthday, tel, path, remark, st_uid) rows = c1.execute(sql) print("修改完成") except Exception: print("出问题了，请检查")#07.修改学生成绩信息def edit_data_grades(): c1 = cursor() st_uid = input("请输入要修改的学号:") sql0 = c1.execute(f'select * from students.student_all where st_uid like "%' + st_uid + '%";') print(c1.fetchone( )) name = input("请输入名字:") chinese = int(input("请输入语文成绩:")) math = int(input("请输入数学成绩:")) english = int(input("请输入英语成绩:")) total = chinese + math + english try: sql = 'UPDATE students.student_all SET name="%s",chinese="%s",math="%s",english="%s",total="%s" WHERE st_uid="%s"' % ( name, chinese, math, english, total, st_uid) rows = c1.execute(sql) print("修改完成") except Exception: print("出问题了，请检查")#08.删除学生成绩信息def remove_achievement(): st_uid = int(input("请输入要删除的学号:")) c1 = cursor() try: sql = 'delete from students.student_all where st_uid="%d"' % st_uid c1.execute(sql) connect().commit( ) print("删除成功") except Exception: print("出问题了，请检查")#09.删除学生个人信息def remove_one_st(): st_uid = int(input("请输入要删除的学号:")) c1 = cursor() try: sql = 'delete from students.student_data where st_uid="%d"' % st_uid c1.execute(sql) connect( ).commit( ) print("删除成功") except Exception: print("出问题了，请检查")if __name__ == '__main__': print(f""" |---------------------------------------------------------------| | 欢迎来到学生信息管理系统 | | 现在是北京时间: {time.strftime('%Y-%m-%d %H:%M:%S')} | | 请选择你要操作的命令 | | 0.退出系统 | | 1.查看所有学生成绩信息 | | 2.查看所有学生个人信息 | | 3.查看指定学生成绩信息 | | 4.添加学生个人信息 | | 5.添加学生成绩信息 | | 6.修改学生个人信息 | | 7.修改学生成绩信息 | | 8.删除学生成绩信息 | | 9.删除学生个人信息 | |_______________________________________________________________| """) while True: n = int(input("请输入要操作的命令:")) if n == 1: view_all() elif n == 2: data_all() elif n == 3: one_achievement() elif n == 4: add_st_data() elif n == 5: add_achievement() elif n == 6: edit_one_grades() elif n == 7: edit_data_grades() elif n == 8: remove_achievement() elif n == 9: remove_one_st() elif n == 0: m_close() break总结
一切前提条件就是需要有两张sql表，博主不建议一味的复制粘贴，有问题点击这里提问吧