学生信息及成绩管理系统（Python+Sqlite）数据库版

import os import sqlite3 class StuDB: def __init__(self): self.con = '' self.cur = '' def connect(self,db): self.con = sqlite3.connect(db) self.cur = self.con.cursor() try: sql = ''' create table student ( no text, name text, chinese integer, math integer, english integer ) ''' self.cur.execute(sql) except: pass def close(self): self.con.commit() self.cur.close() self.con.close() def show(self): format_head = '{:8}\t{:8}\t{:8}\t{:8}\t{:8}' print(format_head.format('学号','姓名','语文','数学','英语')) sql = 'select * from student' self.cur.execute(sql) rows = self.cur.fetchall() format_con = '{:8}\t{:8}\t{:<8}\t{:<8}\t{:<8}' for row in rows: print(format_con.format(row[0],row[1],row[2],row[3],row[4])) def __enterScore(self,message): while True: try: score = input(message) if 0 <= int(score) <= 100: break else: print('输入错误，成绩应在0到100之间') except: print('输入错误，成绩应在0到100之间') return int(score) def __exists(self,no): sql = 'select * from student where no = ?' result = self.cur.execute(sql,(no,)) rows = result.fetchall() if len(rows) > 0: return True else: return False def __insert(self,no,name,chinese,math,english): sql='insert into student(no,name,chinese,math,english) values(?,?,?,?,?)' self.cur.execute(sql,(no,name,chinese,math,english)) if self.cur.rowcount > 0: print('插入成功') else: print('插入失败') def __update(self,no,name,chinese,math,english): sql='update student set name=?,chinese=?,math=?,english=? where no=?' self.cur.execute(sql,(name,chinese,math,english,no)) if self.cur.rowcount > 0: print('修改成功') else: print('修改失败') def __delete(self,no): sql = 'delete from student where no = ?' self.cur.execute(sql,(no,)) if self.cur.rowcount > 0: print('删除成功') else: print('删除失败') def insert(self): while True: no = input('学号：') if self.__exists(no): print('该学号已存在') else: name = input('姓名：') chinese = self.__enterScore('语文成绩:') math = self.__enterScore('数学成绩:') english = self.__enterScore('英语成绩:') if no != '' and name != '': self.__insert(no,name,chinese,math,english) else: print('请将信息输入完整') choice = input('继续添加(y/n)?').lower() if choice == 'n': break def delete(self): while True: no = input('请输入要删除的学号：') if not self.__exists(no): print('该学号不存在') else: self.__delete(no) choice = input('继续删除(y/n)?').lower() if choice == 'n': break def update(self): while True: no = input('请输入要修改的学号:') if not self.__exists(no): print('该学号不存在') else: name = input('姓名：') chinese = self.__enterScore('语文成绩:') math = self.__enterScore('数学成绩:') english = self.__enterScore('英语成绩:') if no != '' and name != '' : self.__update(no,name,chinese,math,english) else: print('请将信息输入完整') choice = input('继续修改(y/n)?').lower() if choice == 'n': break def save(self): fn = input('请输入要导出的文件名:') with open(fn,'w',encoding = 'utf-8') as fp: self.cur.execute('select * from student') rows = self.cur.fetchall() for row in rows: fp.write(row[0] + ',' ) fp.write(row[1] + ',' ) fp.write(str(row[2]) + ',' ) fp.write(str(row[3]) + ',' ) fp.write(str(row[4])+ '\n') print('导出完毕') def load(self): fn = input('请输入要导入的文件名:') if os.path.exists(fn): with open(fn,'r',encoding = 'utf-8') as fp: while True: s = fp.readline().strip('\n') if s == '': break stu = s.split(',') no = stu[0] name = stu[1] chinese = int(stu[2]) math = int(stu[3]) english = int(stu[4]) if self.__exists(no): print('该学生已存在') else: self.__insert(no,name,chinese,math,english) print('导入完毕') else: print('要导入的文件不存在') def scoreavg(self): sql = 'select avg(chinese),avg(math),avg(english) from student' self.cur.execute(sql) result = self.cur.fetchone() print('语文成绩平均分是：%.2f'%result[0]) print('数学成绩平均分是：%.2f'%result[1]) print('英语成绩平均分是：%.2f'%result[2]) def scoremax(self): sql = 'select max(chinese),max(math),max(english) from student' self.cur.execute(sql) result = self.cur.fetchone() print('语文成绩最高分是：%d'%result[0]) print('数学成绩最高分是：%d'%result[1]) print('英语成绩最高分是：%d'%result[2]) def scoremin(self): sql = 'select min(chinese),min(math),min(english) from student' self.cur.execute(sql) result = self.cur.fetchone() print('语文成绩最低分是：%d'%result[0]) print('数学成绩最低分是：%d'%result[1]) print('英语成绩最低分是：%d'%result[2]) def main(self,db): while True: print('学生信息管理系统(数据库版)'.center(20,'=')) print('info -------学生基本信息管理') print('score -------学生成绩统计') print('exit -------退出系统') print(''.center(32,'=')) s = input('main>').strip().lower() if s == 'info': self.infoprocess(db) elif s == 'score': self.scoreprocess(db) elif s == 'exit': break else: print('输入错误') def infoprocess(self,db): self.connect(db) print('学生基本信息管理'.center(24,'=')) print('load -----------导入学生数据') print('insert -----------插入学生信息') print('delete -----------删除学生信息') print('update -----------修改学生信息') print('show -----------显示学生信息') print('save -----------导