发布于2021-07-25 07:17 阅读(1251) 评论(0) 点赞(29) 收藏(2)
请先安装:pymysql, xlwt,numpy,pandas
pip3 install pymysql xlwt numpy pandas
注意:请先把数据库数据类型修改为utf-8:
创建库:
create databases student;
创建表:
create table grades values(name varchar(50), age int, ywscore int, sxscore int, yyscore int, count int);
- # 学生信息系统
- import pymysql, xlwt
- import numpy as np
- import pandas as pd
- import time, sys
-
- def main():
- while True:
- print('+-----------------------------------+')
- print('|1) 添加学生信息 ')
- print('|2) 查询学生信息 ')
- print('|3) 修改学生信息 ')
- print('|4) 删除学生信息 ')
- print('|5) 按成绩高-->低显示学生信息 ')
- print('|6) 按成绩低-->高显示学生信息 ')
- print('|7) 按年龄高-->低显示学生信息 ')
- print('|8) 按年龄底-->高显示学生信息 ')
- print('|9) 将学生信息写入文件 ')
- print('|10) 查看导出的文件信息 ')
- print('|11) 查看所有成绩 ')
- print('|q) 退出 ')
- print('+-----------------------------------+')
- my_number = input('请选择:')
- try:
- if my_number == '1':
- add_student()
- elif my_number == '2':
- cat_student()
- elif my_number == '3':
- set_student()
- elif my_number == '4':
- del_student()
- elif my_number == '5':
- resort_score_student()
- elif my_number == '6':
- sort_score_student()
- elif my_number == '7':
- resort_age_student()
- elif my_number == '8':
- sort_age_student()
- elif my_number == '9':
- write_student()
- elif my_number == '10':
- read_student()
- elif my_number == '11':
- cat_student_all()
- elif my_number == 'q':
- break
- except:
- print('输入错误')
- #进度条显示
- def speeds():
- print("正在执行")
- for i in range(11):
- if i != 10:
- sys.stdout.write("......")
- else:
- sys.stdout.write(". " + str(i*10)+"%/100%")
- sys.stdout.flush()
- time.sleep(0.1)
- print("\n" + "执行完成")
-
- #增加信息
- def add_student():
- #连接数据库
- host, user, passwd, db='127.0.0.1','root','123.com','student'
- conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
- while True:
- print('按Enter结束')
- name = str(input('请输入学生姓名:'))
- if not name:
- break
- age = int(input('请输入学生年龄:'))
- ywscore = int(input('请输入学生语文成绩:'))
- sxscore = int(input('请输入学生数学成绩:'))
- yyscore = int(input('请输入学生英语成绩:'))
- speeds()
-
- # 得到一个可以执行SQL语句的光标对象
- cursor = conn.cursor()
- sql = "insert into grades VALUES(%s,%s,%s,%s,%s,%s);"
- names = name
- ages = int(age)
- ywscores = int(ywscore)
- sxscores = int(sxscore)
- yyscores = int(yyscore)
- count = ywscores+sxscores+yyscores
-
- # 执行SQL语句
- cursor.execute(sql, [names, ages, ywscores, sxscores, yyscores, count])
-
- # 提交事务
- conn.commit()
- cursor.close()
- conn.close()
- return
-
- # 查看所有学生成绩
- def cat_student_all():
- host, user, passwd, db='127.0.0.1','root','123.com','student'
- conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
- cursor = conn.cursor()
- cat_sql = "select * from grades;"
- cursor.execute(cat_sql)
- # 获取多条查询数据
- ret = cursor.fetchall()
- rets = list(ret)
- speeds()
-
- print('+---------+---------+---------+---------+---------+---------+')
- print('| name | age | ywscore | sxscore | yyscore | count |')
- print('+---------+---------+---------+---------+---------+---------+')
- for i in range(len(rets)):
- info = ''
- for j in range(len(rets[i])):
- if j == 0:
- if len(rets[i][j]) == 2:
- name_0=' '
- name_1=((rets[i][j])[0])
- name_3=' '
- name_2=((rets[i][j])[1])
- name_4=name_0+name_1+name_3+name_2
- info += ('%4s |' %name_4)
- else:
- info += ('%4s |' %rets[i][j])
- else:
- info += ('%7s |' %rets[i][j])
- print('|',end='')
- print(info)
- del info
- print('+---------+---------+---------+---------+---------+---------+')
- cursor.close()
- conn.close()
- return
-
- #查看信息
- def cat_student():
- host, user, passwd, db='127.0.0.1','root','123.com','student'
- conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
- while True:
- print('按Enter结束')
- s_name = input('请输入要查询的姓名:')
-
- if not s_name:
- break
- speeds()
- print('+---------+---------+---------+---------+---------+---------+')
- print('| name | age | ywscore | sxscore | yyscore | count |')
- print('+---------+---------+---------+---------+---------+---------+')
- cursor = conn.cursor()
- cat_sql = "select * from grades where name=%s;"
- # 执行SQL语句
- cursor.execute(cat_sql, [s_name])
- ret = cursor.fetchone()
- rets = list(ret)
- print('|', end='')
- info = ''
- for i in range(len(rets)):
- if i == 0:
- if len(rets[i]) == 2:
- name_1=((rets[i])[0])
- name_2=((rets[i])[1])
- name_4=' '+name_1+' '+name_2
- info += ('%4s |' %name_4)
- else:
- info += ('%4s |' %rets[i])
- else:
- info += ('%6s |' %rets[i])
- print(info)
- print('+---------+---------+---------+---------+---------+---------+')
- del info
- # 提交事务
- conn.commit()
- cursor.close()
- conn.close()
- return
-
- #修改信息
- def set_student():
- host, user, passwd, db='127.0.0.1','root','123.com','student'
- conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
- while True:
- print('按Enter结束')
- s_name = str(input('请输入要修改成绩的学生姓名:'))
- if not s_name:
- break
- set_score = str(input('请输入需要修改的哪科成绩:(ywscore,sxscore,yyscore)'))
- s_core = int(input('请输入成绩:'))
- if set_score == 'ywscore':
- cursor = conn.cursor()
- cat_sql = "UPDATE grades SET ywscore=%s WHERE name=%s;"
- cursor.execute(cat_sql, [s_core, s_name])
- conn.commit()
- speeds()
- elif set_score == 'sxscore':
- cursor = conn.cursor()
- cat_sql = "UPDATE grades SET sxscore=%s WHERE name=%s;"
- cursor.execute(cat_sql, [s_core, s_name])
- conn.commit()
- speeds()
- elif set_score == 'yyscore':
- cursor = conn.cursor()
- cat_sql = "UPDATE grades SET yyscore=%s WHERE name=%s;"
- cursor.execute(cat_sql, [s_core, s_name])
- conn.commit()
- speeds()
- cursor = conn.cursor()
- cat_sql = "select * from grades where name=%s;"
- cursor.execute(cat_sql, [s_name])
- ret = cursor.fetchone()
- cun = ret[2]+ret[3]+ret[4]
- cursor = conn.cursor()
- cat_sql = "UPDATE grades SET count=%s WHERE name=%s;"
- cursor.execute(cat_sql, [cun, s_name])
-
- conn.commit()
- cursor.close()
- conn.close()
- return
-
- #删除信息
- def del_student():
- host, user, passwd, db='127.0.0.1','root','123.com','student'
- conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
- while True:
- print('按Enter结束')
- s_name = str(input('请输入要删除的学生姓名:'))
- if not s_name:
- conn.commit()
- cursor.close()
- conn.close()
- break
- cursor = conn.cursor()
- cat_sql = "DELETE FROM grades WHERE name=%s;"
- cursor.execute(cat_sql, [s_name])
- speeds()
- conn.commit()
- cursor.close()
- conn.close()
- return
-
- #按成绩低-->高显示学生信息
- def sort_score_student():
- host, user, passwd, db='127.0.0.1','root','123.com','student'
- conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
- cursor = conn.cursor()
- cat_sql = "select * from grades;"
- cursor.execute(cat_sql)
- # 获取多条查询数据
- ret = cursor.fetchall()
- rets = list(ret)
- retss = (sorted(rets,key=lambda x:x[5], reverse=False))
-
- speeds()
- print()
- print('+---------+---------+---------+---------+---------+---------+')
- print('| name | age | ywscore | sxscore | yyscore | count |')
- print('+---------+---------+---------+---------+---------+---------+')
- for i in range(len(retss)):
- info = ''
- for j in range(len(retss[i])):
- if j == 0:
- if len(retss[i][j]) == 2:
- name_0=' '
- name_1=((retss[i][j])[0])
- name_3=' '
- name_2=((retss[i][j])[1])
- name_4=name_0+name_1+name_3+name_2
- info += ('%4s |' %name_4)
- else:
- info += ('%4s |' %retss[i][j])
- else:
- info += ('%7s |' %retss[i][j])
- print('|',end='')
- print(info)
- del info
- print('+---------+---------+---------+---------+---------+---------+')
- cursor.close()
- conn.close()
- return
-
- #按成绩高-->低显示学生信息
- def resort_score_student():
- # my_list = my_list.sort(key='score',reverse=True)
- host, user, passwd, db='127.0.0.1','root','123.com','student'
- conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
- cursor = conn.cursor()
- cat_sql = "select * from grades;"
- cursor.execute(cat_sql)
- # 获取多条查询数据
- ret = cursor.fetchall()
- rets = list(ret)
- retss = (sorted(rets,key=lambda x:x[5], reverse=True))
-
- speeds()
- print()
- print('+---------+---------+---------+---------+---------+---------+')
- print('| name | age | ywscore | sxscore | yyscore | count |')
- print('+---------+---------+---------+---------+---------+---------+')
- for i in range(len(retss)):
- info = ''
- for j in range(len(retss[i])):
- if j == 0:
- if len(retss[i][j]) == 2:
- name_0=' '
- name_1=((retss[i][j])[0])
- name_3=' '
- name_2=((retss[i][j])[1])
- name_4=name_0+name_1+name_3+name_2
- info += ('%4s |' %name_4)
- else:
- info += ('%4s |' %retss[i][j])
- else:
- info += ('%7s |' %retss[i][j])
-
- print('|',end='')
- print(info)
- del info
- print('+---------+---------+---------+---------+---------+---------+')
- cursor.close()
- conn.close()
- return
-
- #按年龄底-->高显示学生信息
- def sort_age_student():
- # my_list = my_list.sort(key='age',reverse=False)
- host, user, passwd, db='127.0.0.1','root','123.com','student'
- conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
- cursor = conn.cursor()
- cat_sql = "select * from grades;"
- cursor.execute(cat_sql)
- # 获取多条查询数据
- ret = cursor.fetchall()
- rets = list(ret)
- retss = (sorted(rets,key=lambda x:x[1], reverse=False))
-
- speeds()
- print()
- print('+---------+---------+---------+---------+---------+---------+')
- print('| name | age | ywscore | sxscore | yyscore | count |')
- print('+---------+---------+---------+---------+---------+---------+')
- for i in range(len(retss)):
- info = ''
- for j in range(len(retss[i])):
- if j == 0:
- if len(retss[i][j]) == 2:
- name_0=' '
- name_1=((retss[i][j])[0])
- name_3=' '
- name_2=((retss[i][j])[1])
- name_4=name_0+name_1+name_3+name_2
- info += ('%4s |' %name_4)
- else:
- info += ('%4s |' %retss[i][j])
- else:
- info += ('%7s |' %retss[i][j])
- print('|',end='')
- print(info)
- del info
- print('+---------+---------+---------+---------+---------+---------+')
- cursor.close()
- conn.close()
- return
-
- #按年龄高-->低显示学生信息
- def resort_age_student():
- host, user, passwd, db='127.0.0.1','root','123.com','student'
- conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
- cursor = conn.cursor()
- cat_sql = "select * from grades;"
- cursor.execute(cat_sql)
- # 获取多条查询数据
- ret = cursor.fetchall()
- rets = list(ret)
- retss = (sorted(rets,key=lambda x:x[1], reverse=True))
-
- speeds()
- print()
- print('+---------+---------+---------+---------+---------+---------+')
- print('| name | age | ywscore | sxscore | yyscore | count |')
- print('+---------+---------+---------+---------+---------+---------+')
- for i in range(len(retss)):
- info = ''
- for j in range(len(retss[i])):
- # info += rets[i][j]
- if j == 0:
- if len(retss[i][j]) == 2:
- name_0=' '
- name_1=((retss[i][j])[0])
- name_3=' '
- name_2=((retss[i][j])[1])
- name_4=name_0+name_1+name_3+name_2
- info += ('%4s |' %name_4)
- else:
- info += ('%4s |' %retss[i][j])
- else:
- info += ('%7s |' %retss[i][j])
- print('|',end='')
- print(info)
- del info
- print('+---------+---------+---------+---------+---------+---------+')
- cursor.close()
- conn.close()
- return
-
- #将学生信息表写入文件
- def write_student():
- host, user, passwd, db='127.0.0.1','root','123.com','student'
- conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
- cur = conn.cursor()
- sql = 'select * from grades;'
- cur.execute(sql) # 返回受影响的行数
- fields = [field[0] for field in cur.description] # 获取所有字段名
- all_data = cur.fetchall() # 所有数据
-
- # 写入excel
- book = xlwt.Workbook()
- sheet = book.add_sheet('sheet1')
-
- for col,field in enumerate(fields):
- sheet.write(0,col,field)
-
- row = 1
- for data in all_data:
- for col,field in enumerate(data):
- sheet.write(row,col,field)
- row += 1
- book.save("/flq/python/jupyter/student.xlsx")
- speeds()
- return
-
- #以规格读文件的学生信息
- def read_student():
- df = pd.read_excel('/flq/python/jupyter/student.xlsx')
- data=df.head(99999999999999)
- speeds()
- print('获取到所有数据:\n{0}'.format(data))
-
- if __name__ == '__main__':
- main()
原文链接:https://blog.csdn.net/flq18210105507/article/details/119040839
作者:听见那声音
链接:http://www.pythonpdf.com/blog/article/468/317470d4a4ad0f4665cb/
来源:编程知识网
任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任
昵称:
评论内容:(最多支持255个字符)
投诉与举报,广告合作请联系vgs_info@163.com或QQ3083709327
免责声明:网站文章均由用户上传,仅供读者学习交流使用,禁止用做商业用途。若文章涉及色情,反动,侵权等违法信息,请向我们举报,一经核实我们会立即删除!