本站消息

  出租广告位,需要合作请联系站长

  今日名言-想象你自己对困难作出的反应,不是逃避或绕开它们,而是面对它们,同它们打交道,以一种进取的和明智的方式同它们奋斗 。——马克斯威尔·马尔兹

  今日名言-用谅解、宽恕的目光和心理看人、待人。人就会觉得葱笼的世界里,春意盎然,到处充满温暖。——蔡文甫


+关注
已关注

分类  

暂无分类

标签  

暂无标签

日期归档  

暂无数据

学生管理系统-数据库篇

发布于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);

  1. # 学生信息系统
  2. import pymysql, xlwt
  3. import numpy as np
  4. import pandas as pd
  5. import time, sys
  6. def main():
  7. while True:
  8. print('+-----------------------------------+')
  9. print('|1) 添加学生信息 ')
  10. print('|2) 查询学生信息 ')
  11. print('|3) 修改学生信息 ')
  12. print('|4) 删除学生信息 ')
  13. print('|5) 按成绩高-->低显示学生信息 ')
  14. print('|6) 按成绩低-->高显示学生信息 ')
  15. print('|7) 按年龄高-->低显示学生信息 ')
  16. print('|8) 按年龄底-->高显示学生信息 ')
  17. print('|9) 将学生信息写入文件 ')
  18. print('|10) 查看导出的文件信息 ')
  19. print('|11) 查看所有成绩 ')
  20. print('|q) 退出 ')
  21. print('+-----------------------------------+')
  22. my_number = input('请选择:')
  23. try:
  24. if my_number == '1':
  25. add_student()
  26. elif my_number == '2':
  27. cat_student()
  28. elif my_number == '3':
  29. set_student()
  30. elif my_number == '4':
  31. del_student()
  32. elif my_number == '5':
  33. resort_score_student()
  34. elif my_number == '6':
  35. sort_score_student()
  36. elif my_number == '7':
  37. resort_age_student()
  38. elif my_number == '8':
  39. sort_age_student()
  40. elif my_number == '9':
  41. write_student()
  42. elif my_number == '10':
  43. read_student()
  44. elif my_number == '11':
  45. cat_student_all()
  46. elif my_number == 'q':
  47. break
  48. except:
  49. print('输入错误')
  50. #进度条显示
  51. def speeds():
  52. print("正在执行")
  53. for i in range(11):
  54. if i != 10:
  55. sys.stdout.write("......")
  56. else:
  57. sys.stdout.write(". " + str(i*10)+"%/100%")
  58. sys.stdout.flush()
  59. time.sleep(0.1)
  60. print("\n" + "执行完成")
  61. #增加信息
  62. def add_student():
  63. #连接数据库
  64. host, user, passwd, db='127.0.0.1','root','123.com','student'
  65. conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
  66. while True:
  67. print('按Enter结束')
  68. name = str(input('请输入学生姓名:'))
  69. if not name:
  70. break
  71. age = int(input('请输入学生年龄:'))
  72. ywscore = int(input('请输入学生语文成绩:'))
  73. sxscore = int(input('请输入学生数学成绩:'))
  74. yyscore = int(input('请输入学生英语成绩:'))
  75. speeds()
  76. # 得到一个可以执行SQL语句的光标对象
  77. cursor = conn.cursor()
  78. sql = "insert into grades VALUES(%s,%s,%s,%s,%s,%s);"
  79. names = name
  80. ages = int(age)
  81. ywscores = int(ywscore)
  82. sxscores = int(sxscore)
  83. yyscores = int(yyscore)
  84. count = ywscores+sxscores+yyscores
  85. # 执行SQL语句
  86. cursor.execute(sql, [names, ages, ywscores, sxscores, yyscores, count])
  87. # 提交事务
  88. conn.commit()
  89. cursor.close()
  90. conn.close()
  91. return
  92. # 查看所有学生成绩
  93. def cat_student_all():
  94. host, user, passwd, db='127.0.0.1','root','123.com','student'
  95. conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
  96. cursor = conn.cursor()
  97. cat_sql = "select * from grades;"
  98. cursor.execute(cat_sql)
  99. # 获取多条查询数据
  100. ret = cursor.fetchall()
  101. rets = list(ret)
  102. speeds()
  103. print('+---------+---------+---------+---------+---------+---------+')
  104. print('| name | age | ywscore | sxscore | yyscore | count |')
  105. print('+---------+---------+---------+---------+---------+---------+')
  106. for i in range(len(rets)):
  107. info = ''
  108. for j in range(len(rets[i])):
  109. if j == 0:
  110. if len(rets[i][j]) == 2:
  111. name_0=' '
  112. name_1=((rets[i][j])[0])
  113. name_3=' '
  114. name_2=((rets[i][j])[1])
  115. name_4=name_0+name_1+name_3+name_2
  116. info += ('%4s |' %name_4)
  117. else:
  118. info += ('%4s |' %rets[i][j])
  119. else:
  120. info += ('%7s |' %rets[i][j])
  121. print('|',end='')
  122. print(info)
  123. del info
  124. print('+---------+---------+---------+---------+---------+---------+')
  125. cursor.close()
  126. conn.close()
  127. return
  128. #查看信息
  129. def cat_student():
  130. host, user, passwd, db='127.0.0.1','root','123.com','student'
  131. conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
  132. while True:
  133. print('按Enter结束')
  134. s_name = input('请输入要查询的姓名:')
  135. if not s_name:
  136. break
  137. speeds()
  138. print('+---------+---------+---------+---------+---------+---------+')
  139. print('| name | age | ywscore | sxscore | yyscore | count |')
  140. print('+---------+---------+---------+---------+---------+---------+')
  141. cursor = conn.cursor()
  142. cat_sql = "select * from grades where name=%s;"
  143. # 执行SQL语句
  144. cursor.execute(cat_sql, [s_name])
  145. ret = cursor.fetchone()
  146. rets = list(ret)
  147. print('|', end='')
  148. info = ''
  149. for i in range(len(rets)):
  150. if i == 0:
  151. if len(rets[i]) == 2:
  152. name_1=((rets[i])[0])
  153. name_2=((rets[i])[1])
  154. name_4=' '+name_1+' '+name_2
  155. info += ('%4s |' %name_4)
  156. else:
  157. info += ('%4s |' %rets[i])
  158. else:
  159. info += ('%6s |' %rets[i])
  160. print(info)
  161. print('+---------+---------+---------+---------+---------+---------+')
  162. del info
  163. # 提交事务
  164. conn.commit()
  165. cursor.close()
  166. conn.close()
  167. return
  168. #修改信息
  169. def set_student():
  170. host, user, passwd, db='127.0.0.1','root','123.com','student'
  171. conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
  172. while True:
  173. print('按Enter结束')
  174. s_name = str(input('请输入要修改成绩的学生姓名:'))
  175. if not s_name:
  176. break
  177. set_score = str(input('请输入需要修改的哪科成绩:(ywscore,sxscore,yyscore)'))
  178. s_core = int(input('请输入成绩:'))
  179. if set_score == 'ywscore':
  180. cursor = conn.cursor()
  181. cat_sql = "UPDATE grades SET ywscore=%s WHERE name=%s;"
  182. cursor.execute(cat_sql, [s_core, s_name])
  183. conn.commit()
  184. speeds()
  185. elif set_score == 'sxscore':
  186. cursor = conn.cursor()
  187. cat_sql = "UPDATE grades SET sxscore=%s WHERE name=%s;"
  188. cursor.execute(cat_sql, [s_core, s_name])
  189. conn.commit()
  190. speeds()
  191. elif set_score == 'yyscore':
  192. cursor = conn.cursor()
  193. cat_sql = "UPDATE grades SET yyscore=%s WHERE name=%s;"
  194. cursor.execute(cat_sql, [s_core, s_name])
  195. conn.commit()
  196. speeds()
  197. cursor = conn.cursor()
  198. cat_sql = "select * from grades where name=%s;"
  199. cursor.execute(cat_sql, [s_name])
  200. ret = cursor.fetchone()
  201. cun = ret[2]+ret[3]+ret[4]
  202. cursor = conn.cursor()
  203. cat_sql = "UPDATE grades SET count=%s WHERE name=%s;"
  204. cursor.execute(cat_sql, [cun, s_name])
  205. conn.commit()
  206. cursor.close()
  207. conn.close()
  208. return
  209. #删除信息
  210. def del_student():
  211. host, user, passwd, db='127.0.0.1','root','123.com','student'
  212. conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
  213. while True:
  214. print('按Enter结束')
  215. s_name = str(input('请输入要删除的学生姓名:'))
  216. if not s_name:
  217. conn.commit()
  218. cursor.close()
  219. conn.close()
  220. break
  221. cursor = conn.cursor()
  222. cat_sql = "DELETE FROM grades WHERE name=%s;"
  223. cursor.execute(cat_sql, [s_name])
  224. speeds()
  225. conn.commit()
  226. cursor.close()
  227. conn.close()
  228. return
  229. #按成绩低-->高显示学生信息
  230. def sort_score_student():
  231. host, user, passwd, db='127.0.0.1','root','123.com','student'
  232. conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
  233. cursor = conn.cursor()
  234. cat_sql = "select * from grades;"
  235. cursor.execute(cat_sql)
  236. # 获取多条查询数据
  237. ret = cursor.fetchall()
  238. rets = list(ret)
  239. retss = (sorted(rets,key=lambda x:x[5], reverse=False))
  240. speeds()
  241. print()
  242. print('+---------+---------+---------+---------+---------+---------+')
  243. print('| name | age | ywscore | sxscore | yyscore | count |')
  244. print('+---------+---------+---------+---------+---------+---------+')
  245. for i in range(len(retss)):
  246. info = ''
  247. for j in range(len(retss[i])):
  248. if j == 0:
  249. if len(retss[i][j]) == 2:
  250. name_0=' '
  251. name_1=((retss[i][j])[0])
  252. name_3=' '
  253. name_2=((retss[i][j])[1])
  254. name_4=name_0+name_1+name_3+name_2
  255. info += ('%4s |' %name_4)
  256. else:
  257. info += ('%4s |' %retss[i][j])
  258. else:
  259. info += ('%7s |' %retss[i][j])
  260. print('|',end='')
  261. print(info)
  262. del info
  263. print('+---------+---------+---------+---------+---------+---------+')
  264. cursor.close()
  265. conn.close()
  266. return
  267. #按成绩高-->低显示学生信息
  268. def resort_score_student():
  269. # my_list = my_list.sort(key='score',reverse=True)
  270. host, user, passwd, db='127.0.0.1','root','123.com','student'
  271. conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
  272. cursor = conn.cursor()
  273. cat_sql = "select * from grades;"
  274. cursor.execute(cat_sql)
  275. # 获取多条查询数据
  276. ret = cursor.fetchall()
  277. rets = list(ret)
  278. retss = (sorted(rets,key=lambda x:x[5], reverse=True))
  279. speeds()
  280. print()
  281. print('+---------+---------+---------+---------+---------+---------+')
  282. print('| name | age | ywscore | sxscore | yyscore | count |')
  283. print('+---------+---------+---------+---------+---------+---------+')
  284. for i in range(len(retss)):
  285. info = ''
  286. for j in range(len(retss[i])):
  287. if j == 0:
  288. if len(retss[i][j]) == 2:
  289. name_0=' '
  290. name_1=((retss[i][j])[0])
  291. name_3=' '
  292. name_2=((retss[i][j])[1])
  293. name_4=name_0+name_1+name_3+name_2
  294. info += ('%4s |' %name_4)
  295. else:
  296. info += ('%4s |' %retss[i][j])
  297. else:
  298. info += ('%7s |' %retss[i][j])
  299. print('|',end='')
  300. print(info)
  301. del info
  302. print('+---------+---------+---------+---------+---------+---------+')
  303. cursor.close()
  304. conn.close()
  305. return
  306. #按年龄底-->高显示学生信息
  307. def sort_age_student():
  308. # my_list = my_list.sort(key='age',reverse=False)
  309. host, user, passwd, db='127.0.0.1','root','123.com','student'
  310. conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
  311. cursor = conn.cursor()
  312. cat_sql = "select * from grades;"
  313. cursor.execute(cat_sql)
  314. # 获取多条查询数据
  315. ret = cursor.fetchall()
  316. rets = list(ret)
  317. retss = (sorted(rets,key=lambda x:x[1], reverse=False))
  318. speeds()
  319. print()
  320. print('+---------+---------+---------+---------+---------+---------+')
  321. print('| name | age | ywscore | sxscore | yyscore | count |')
  322. print('+---------+---------+---------+---------+---------+---------+')
  323. for i in range(len(retss)):
  324. info = ''
  325. for j in range(len(retss[i])):
  326. if j == 0:
  327. if len(retss[i][j]) == 2:
  328. name_0=' '
  329. name_1=((retss[i][j])[0])
  330. name_3=' '
  331. name_2=((retss[i][j])[1])
  332. name_4=name_0+name_1+name_3+name_2
  333. info += ('%4s |' %name_4)
  334. else:
  335. info += ('%4s |' %retss[i][j])
  336. else:
  337. info += ('%7s |' %retss[i][j])
  338. print('|',end='')
  339. print(info)
  340. del info
  341. print('+---------+---------+---------+---------+---------+---------+')
  342. cursor.close()
  343. conn.close()
  344. return
  345. #按年龄高-->低显示学生信息
  346. def resort_age_student():
  347. host, user, passwd, db='127.0.0.1','root','123.com','student'
  348. conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
  349. cursor = conn.cursor()
  350. cat_sql = "select * from grades;"
  351. cursor.execute(cat_sql)
  352. # 获取多条查询数据
  353. ret = cursor.fetchall()
  354. rets = list(ret)
  355. retss = (sorted(rets,key=lambda x:x[1], reverse=True))
  356. speeds()
  357. print()
  358. print('+---------+---------+---------+---------+---------+---------+')
  359. print('| name | age | ywscore | sxscore | yyscore | count |')
  360. print('+---------+---------+---------+---------+---------+---------+')
  361. for i in range(len(retss)):
  362. info = ''
  363. for j in range(len(retss[i])):
  364. # info += rets[i][j]
  365. if j == 0:
  366. if len(retss[i][j]) == 2:
  367. name_0=' '
  368. name_1=((retss[i][j])[0])
  369. name_3=' '
  370. name_2=((retss[i][j])[1])
  371. name_4=name_0+name_1+name_3+name_2
  372. info += ('%4s |' %name_4)
  373. else:
  374. info += ('%4s |' %retss[i][j])
  375. else:
  376. info += ('%7s |' %retss[i][j])
  377. print('|',end='')
  378. print(info)
  379. del info
  380. print('+---------+---------+---------+---------+---------+---------+')
  381. cursor.close()
  382. conn.close()
  383. return
  384. #将学生信息表写入文件
  385. def write_student():
  386. host, user, passwd, db='127.0.0.1','root','123.com','student'
  387. conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
  388. cur = conn.cursor()
  389. sql = 'select * from grades;'
  390. cur.execute(sql) # 返回受影响的行数
  391. fields = [field[0] for field in cur.description] # 获取所有字段名
  392. all_data = cur.fetchall() # 所有数据
  393. # 写入excel
  394. book = xlwt.Workbook()
  395. sheet = book.add_sheet('sheet1')
  396. for col,field in enumerate(fields):
  397. sheet.write(0,col,field)
  398. row = 1
  399. for data in all_data:
  400. for col,field in enumerate(data):
  401. sheet.write(row,col,field)
  402. row += 1
  403. book.save("/flq/python/jupyter/student.xlsx")
  404. speeds()
  405. return
  406. #以规格读文件的学生信息
  407. def read_student():
  408. df = pd.read_excel('/flq/python/jupyter/student.xlsx')
  409. data=df.head(99999999999999)
  410. speeds()
  411. print('获取到所有数据:\n{0}'.format(data))
  412. if __name__ == '__main__':
  413. main()

原文链接:https://blog.csdn.net/flq18210105507/article/details/119040839



所属网站分类: 技术文章 > 博客

作者:听见那声音

链接:http://www.pythonpdf.com/blog/article/468/317470d4a4ad0f4665cb/

来源:编程知识网

任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任

29 0
收藏该文
已收藏

评论内容:(最多支持255个字符)