Python实现简单学生信息管理程序并保存数据至Mysql数据库(附源码)
文章目录
- Python实现简单学生信息管理程序并保存数据至Mysql数据库(附源码)
- 前言
- 成果展示
- Pymysql用法
- 源代码
前言
基于上篇文章程序改进而来，之前数据是存在本地文件中，逻辑繁琐且不安全，在我2小时学完pymysql后，将数据保存到数据库中，复杂逻辑交给sql语句去实现
成果展示
可以参考我上一篇文章
Python实现简单学生信息管理程序(附源码)
Pymysql用法
参考大佬的的文章
python3基础：操作mysql数据库
源代码
值得一提的是，这里的连接信息要换成自己数据库的信息，否则会连接超时
代码如下
import pymysql
from prettytable import PrettyTable
u = PrettyTable(['ID', '姓名', 'Go', 'Python', 'English'])
def menum():
print('======================学生信息管理系统==========================')
print('=========================功能菜单=============================')
print('1、录入学生信息'.center(50))
print('2、查找学生信息'.center(50))
print('3、删除学生信息'.center(50))
print('4、修改学生信息'.center(50))
print('5、排序'.center(45))
print('6、统计学生总人数'.center(52))
print('7、显示所有学生信息'.center(52))
print('0、退出'.center(45))
print('==============================================================')
def main():
while True:
menum()
try:
choice = int(input('请选择[0-7]:'))
if choice in range(0, 8):
if choice == 0:
answer = input('您确定要退出系统吗?y/n')
if answer == 'y' or answer == 'Y':
print('欢迎下次使用！')
break
else:
print('下次摁错就揍你!')
time.sleep(1)
continue
elif choice == 1:
insert_data()
elif choice == 2:
search()
elif choice == 3:
delete()
elif choice == 4:
modify()
elif choice == 5:
sort()
elif choice == 6:
total()
elif choice == 7:
show()
except Exception as e:
print('错误类型是', e.__class__.__name__)
print('错误明细是', e)
print('您输入的命令格式有误，请重新输入')
continue
def insert_data():
insert = "insert into t_student values (%s,%s,%s,%s,%s);"
while True:
opendb()
name = input("请输入学生姓名:")
if not name:
print('您输入的姓名为空，请重新输入')
continue
try:
id = int(input("请输入学生ID:"))
golang = int(input("Go语言成绩是:"))
python = int(input('python语言成绩是:'))
english = int(input('英语成绩是:'))
try:
cur.execute(insert, (id, name, golang, python, english))
print(f'{name}信息添加成功')
closedb()
show()
answer = input("是否继续添加学生信息?y/n")
if answer == 'y' or answer == 'Y':
opendb()
continue
else:
break
except Exception as e:
print('错误类型是', e.__class__.__name__)
print('错误明细是', e)
print("数据库插入错误，检查")
break
finally:
closedb()
except ValueError as e:
print('错误类型是', e.__class__.__name__)
print('错误明细是', e)
print('请输入有效的整数，重新输入')
continue
except pymysql.err.IntegrityError:
print('您输入的ID重复，请重新输入')
continue
def search():
while True:
try:
stu_id = int(input("请输入要查询的ID："))
if stu_id:
opendb()
searchsql = f"select * from t_student where id = {stu_id}"
result = cur.execute(searchsql)
if result == 0:
print("未找到该编号学生信息信息，请重新输入")
continue
else:
resTuple = cur.fetchone()
u.add_row([resTuple[0], resTuple[1], resTuple[2], resTuple[3], resTuple[4]])
print(u)
u.clear_rows()
else:
print("您输入的ID为空，请重新输入")
continue
except Exception as e:
print('错误类型是', e.__class__.__name__)
print('错误明细是', e)
print("您输入的ID有误，请重新输入")
break
finally:
closedb()
def total():
opendb()
totalsql="select count(*) from t_student"
cur.execute(totalsql)
print(f'当前共录入：{cur.fetchone()[0]}人')
def show():
d = {}
opendb()
cur.execute("select * from t_student;")
resTuple = cur.fetchall()
for res in resTuple:
u.add_row([res[0], res[1], res[2], res[3], res[4]])
print(u)
u.clear_rows()
closedb()
def delete():
show()
try:
tag = True
while True:
stu_id = int(input("请输入要删除的ID(ID为0时退出)："))
opendb()
if tag:
answer = input(f'您确定要删除ID={stu_id}这条信息吗?(y/n)')
if answer == 'y' or answer == 'Y':
deletesql = f"delete from t_student where id = {stu_id};"
num = cur.execute(deletesql)
conn.commit()
print(f'您已成功删除{num}条数据')
show()
else:
break
else:
deletesql = f"delete from t_student where id = {stu_id};"
num = cur.execute(deletesql)
conn.commit()
print(f'您已成功删除{num}条数据')
show()
answer = input('您是否要继续删除其他信息?(y/n)')
if answer == 'y' or answer == 'Y':
tag = False
continue
else:
break
except:
print('您输入的格式不正确请重新输入')
finally:
closedb()
def modify():
show()
try:
stu_id = int(input("请输入要修改的ID"))
opendb()
while True:
try:
golang = int(input("Golang成绩："))
python = int(input("Python成绩："))
english = int(input("英语成绩："))
update = f"update t_student set golang={golang},python = {python},english = {english} where id = {stu_id};"
print(update)
cur.execute(update)
except:
print("您输入成绩有误,请重新输入！")
continue
finally:
closedb()
show()
break
except Exception as e:
print('错误类型是', e.__class__.__name__)
print('错误明细是', e)
print('您输入的格式不正确请重新输入')
def sort():
show()
opendb()
try:
while True:
answer = int(input("请选择降序或者升序(1:降序2：升序0:退出)"))
if answer == 1:
sc = 'desc'
elif answer == 2:
sc = 'asc'
elif answer == 0:
break
else:
print('您输入的格式有误，请重新输入')
continue
answer = input('请选择根据哪门成绩排序？(1:golang,2:python;3:english)')
if answer == '1':
subject = 'golang'
elif answer == '2':
subject = 'python'
elif answer == '3':
subject = 'english'
else:
print('您输入的格式有误，请重新输入')
continue
sortsql = f"select * from t_student order by {subject} {sc}"
cur.execute(sortsql)
resTuple = cur.fetchall()
for res in resTuple:
print(res)
u.add_row([res[0], res[1], res[2], res[3], res[4]])
print(u)
u.clear_rows()
answer = input('您是否要继续排序其他信息?(y/n)')
if answer == 'y' or answer == 'Y':
continue
else:
break
except Exception as e:
print('错误类型是', e.__class__.__name__)
print('错误明细是', e)
print('您输入的格式不正确请重新输入')
finally:
closedb()
def opendb():
global cur, conn
conn = pymysql.connect(host="101.42.222.161", user='root', passwd='1qaz@WSX', port=32306)
conn.select_db('Student')
cur = conn.cursor()
def closedb():
cur.close()
conn.commit()
conn.close()
def create_mysql_env():
opendb()
cur.execute('create database if not exists Student default charset utf8 collate utf8_general_ci;')
create_table = """Create table if not exists `t_student` (`id` int(11) not null,`name` varchar(10) not null ,`golang` int(11) not null ,`python` int(11),`english` int(11),primary key (`id`))default character set 'utf8';"""
cur.execute(create_table)
closedb()
create_mysql_env()
if __name__ == '__main__':
main()
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
