数据库上机1（以文件系统实现数据管理）

注：

①各上机报告均根据《数据库技术与应用》课程的上机任务所做。

②课程教材为 《数据库系统概论（第五版）》/王珊, 萨师煊编著/北京:高等教育出版社,2014

 

上机要求：

1.采用所熟悉的编程语言与编程环境，实现使用文件管理课本中P9-1.1.3节 [例]中的学生信息，学生信息包括学号、姓名、性别、年龄、专业和奖励。功能包括录入学生信息、根据学号和姓名查询学生信息、删除指定学生的信息、按照学号修改学生性别、学生信息浏览功能。学生信息中，要求学生的学号是唯一的，允许重名，学生性别非男即女，学生年龄只能在15岁到45岁之间。

2.通常，学生奖励信息和学生信息分别存储，有无界面均可。

 

重难点：

①个别属性要加约束条件

②读写文件管理数据时，尽量不要将所有文件内容调入内存进行相应操作后再全部写回文件。

表信息：

学生基本信息表

学号	姓名	性别	年龄	专业
20100001	史玉明	女	20	计算机
20100100	李明虎	男	21	机械
20100234	张翔	男	21	化工
12112121	史玉明	男	21	通信

学生奖励信息表

学号	姓名	奖励
20100001	史玉明	2011校奖学金，2012国家奖学金
20100100	李明虎	2012校优秀学生
20100234	张翔
12112121	史玉明

具体实现：

 

Version 1

• 实现了单独在一张表里操作数据
• 将所有文件所有内容读入内存，修改完毕后写回文件
• 操作excel时，行和列的序号的起始值为1
• 建立 位置序号-行信息 的键值对来确定相应信息

import xlrd                                #读xls文件,xlsx文件不行
import xlwt                                #写xls文件
from xlutils.copy import copy              #从xlutils模块导入copy

data = xlrd.open_workbook('stu_inf.xlsx')  # 打开xls文件，学生信息表
table1 = data.sheets()[0]                  # 打开第一张表
nrows = table1.nrows                       # 获取表的行数
whole = {}
for i in range(1, nrows):                  # 循环将文件所有内容读入内存
    row = table1.row_values(i)             # row为列表，内容为一行的信息
    for j in [0, 3]:
        row[j] = int(row[j])               #将学号和年龄转为int型
    whole[i] = row                         # 按信息在xls里的位置序号建立键值对

while(1):                                  #具体功能实现由此开始
    fun = input('请按相应键继续:\n1，查询；2，插入;3，修改;4，删除;0，退出\n')
    #查询
    if fun == '1':
        flag = 0                            # 作为是否按请求查询到信息的标志
        fin = input('请输入学号或姓名:\n')
        for key in whole.keys():
            if (fin == whole[key][0] or fin == whole[key][1]):
                print(whole[key])
                flag = 1
        if (flag == 0):
            print('查无此人！')
    # 插入
    elif fun == '2':
        nrows = nrows + 1 #行数，即表中的人数（表头？）
        flag = 0
        while(1):
            id = int(input('请输入学号：\n'))
            for key in whole.keys():
                if (id == whole[key][0]):
                    print('已有此人！')
                    flag = 1
            if (flag == 0):
                break
        name = input('请输入姓名：\n')
        while(1):
            sex = input('请输入性别，男/女：\n')
            if (not (sex in ['男', '女'])):        # 对性别的约束
                print('格式不正确')
            else:
                break
        while(1):                                
            years = int(input('请输入年龄：\n'))
            if (years >= 45 or years <= 15):
                print('年龄不符实际')
            else:
                break
        maj = input('请输入专业：\n')
        inf = [id, name, sex, years, maj]
        whole[nrows - 1] = inf
        print('插入成功！\n', nrows)
    # 修改
    elif fun == '3':
        fin = input('请输入学号:\n')
        for key in whole.keys():
            if (fin == whole[key]):
                while(1):
                    sex == input('性别修改为，男/女：\n')
                    if sex in ['男', '女']:
                        whole[key][2] == sex
                    else:
                        print('格式不正确')
            else:
                print('查无此人！')
        print('修改成功！')
    # 删除
    elif fun == '4':
        fin = input('请输入学号:\n')
        for key in whole.keys():
            if (fin == whole[key][0]):
                for i in range(key, nrows - 1):
                    whole[key] == whole[key -1]
                nrows -= nrows
        print('删除成功！')

    elif fun == '0':
        book = xlwt.Workbook()                  # 新建一个excel
        sheet = book.add_sheet('sheet1')        # 添加一个sheet页，并命名为“sheet1”
        sheet.write(0, 0, '学号')               # excel第一行第一列写入
        sheet.write(0, 1, '姓名')               
        sheet.write(0, 2, '性别')               
        sheet.write(0, 3, '年龄')               
        sheet.write(0, 4, '专业')               
        for i in range(1, nrows):
            for j in range(5):
                sheet.write(i, j, whole[i][j])    # 将内存中修改好的信息写回文件
        print(nrows)
        book.save('学生基本信息.xls')              # 微软的office不能用xlsx结尾的，wps随意
        break

    else:
        print('输入错误！')

 

Version 2

• 实现了对两个表进行操作
• 换用了更方便的openpyxl操作excel
• 思路上依旧是按照信息出现的位置进行定位

import openpyxl                        # 可以读写一体地操作excel，包括xls、xlsx

wb1 = "stu_bainf.xlsx"
wb2 = 'stu_reinf.xlsx'
shead = ['学号：', '姓名：', '性别：', '年龄：', '专业：', '奖励：']

class Stu:                            # 使用类是为了操作全局变量的方便
    rows = 0
    cols = 0
    ids = []
    names = []

    def openAndGet(self, wbname):
        self.workbook = openpyxl.load_workbook(wbname)
        self.sheet = self.workbook.worksheets[0]
        self.rows = self.sheet.max_row
        self.cols = self.sheet.max_column

    def getIDs(self, wbname):
        self.openAndGet(wbname)
        for i in range(1, self.rows):
            self.ids.append(self.sheet.cell(i + 1, 1).value)    # 将所有学号存入列表，用于
                                                                                  # 查询

    def getNames(self, wbname):
        self.openAndGet(wbname)
        for i in range(1, self.rows):
            self.names.append(self.sheet.cell(i + 1, 2).value)

    def setSex(self):
        while (1):
            sex = input('请输入性别，男/女：\n')
            if (sex in ['男', '女']):
                return sex
                break
            else:
                print('格式不正确！')

    def setAge(self):
        while(1):
            try:
                age = input('请输入年龄：\n')
                if(int(age) <= 45 and int(age) >= 15):
                    return age
                    break
                else:
                    print('不符实际！')
            except:
                print('输入有误！')

sw = Stu()
sw2 = Stu()
while(1):
    fun = input('请按相应键继续:\n1，查询；2，插入;3，修改;4，删除;0，退出\n')
    if fun == '0':
        break

    elif fun == '1':
        sw.getIDs(wb1)
        sw.getNames(wb1)
        while(1):
            fin = input('请输入学号或姓名：\n')
            if (fin in map(str, sw.ids)):                # map将每个人学号变为string型
                loc = sw.ids.index(int(fin))             # 还是按照信息位置进行定位
                for i in range(sw.cols):
                    print(shead[i], sw.sheet.cell(loc + 2, i + 1).value)
                print(shead[-1], sw2.sheet.cell(loc + 2, 3).value)
                break
            elif (fin in sw.names):
                loc = [idx for idx, i in enumerate(sw.names) if fin == i]
                for k in loc:
                    for j in range(sw.cols):
                        print(shead[j], sw.sheet.cell(k + 2, j + 1).value)
                    print(shead[-1], sw2.sheet.cell(k + 2, 3).value)
                    print()
                break
            else:
                print('查无此人！')

    elif fun == "2":
        sw.getIDs(wb1)
        while(1):
            id = input('请输入学号：\n')
            if (int(id) in sw.ids):
                print('已有此人！')
            else:
                break
        name = input('请输入姓名：\n')
        sex = sw.setSex()
        age = sw.setAge()
        maj = input('请输入专业：\n')
        rew = input('请输入奖励：\n')
        inf = [int(id), name, sex, int(age), maj, rew]
        inf2 = [inf[0], inf[1], inf[-1]]
        # for i in range(len(inf)):
        #     sw.sheet.cell(sw.rows, i + 1, inf[i])
        sw.sheet.append(inf[0: 5])
        sw2.sheet.append(inf2)
        sw.workbook.save(wb1)
        sw2.workbook.save(wb2)
        print('添加成功！')

    elif fun == '3':
        sw.getIDs(wb1)
        while (1):
            id = input('请输入学号：\n')
            if (int(id) in sw.ids):
                loc = sw.ids.index(int(id))
                while (1):
                    sexx = input('性别修改为，男/女：\n')
                    if sexx in ['男', '女']:
                        sw.sheet.cell(loc + 2, 3, sexx)
                        sw.workbook.save(wb1)
                        break
                    else:
                        print("格式不正确！")
                print('修改成功！')
                break
            else:
                print('查无此人！')

    elif fun == '4':
        sw.getIDs(wb1)
        while(1):
            id = input('请输入学号：\n')
            if (int(id) in sw.ids):
                loc = sw.ids.index(int(id))
                sw.sheet.delete_rows(loc + 2, 1)
                sw2.sheet.delete_rows(loc + 2, 1)
                sw.workbook.save(wb1)
                sw2.workbook.save(wb2)
                print('删除成功！')
                break
            else:
                print('查无此人！')

 

Version 3

• version 2 的可视化版本，定位的思路继续沿用version 2
• 控件Spinbox并不能限制输入的取值

import tkinter as tk
import tkinter.ttk as ttk
import tkinter.messagebox                  # 要使用messagebox（弹出的提示框）先要导入模块
import openpyxl

wb1 = "stu_bainf.xlsx"
wb2 = 'stu_reinf.xlsx'
shead = ['学号：', '姓名：', '性别：', '年龄：', '专业：', '奖励：']

class Stu:
    rows = 0
    cols = 0
    ids = []
    names = []

    def openAndGet(self, wbname):
        self.workbook = openpyxl.load_workbook(wbname)
        self.sheet = self.workbook.worksheets[0]
        self.rows = self.sheet.max_row
        self.cols = self.sheet.max_column

    def getIDs(self, wbname):
        self.openAndGet(wbname)
        for i in range(1, self.rows):
            self.ids.append(self.sheet.cell(i + 1, 1).value)

    def getNames(self, wbname):
        self.openAndGet(wbname)
        for i in range(1, self.rows):
            self.names.append(self.sheet.cell(i + 1, 2).value)

def clearAll():                                # 清空文本框
    entryId.delete(0, 'end')
    entryName.delete(0, 'end')
    entryMaj.delete(0, 'end')
    entryRew.delete(0, 'end')
    spinboxSex.delete(0, 'end')
    spinboxAge.delete(0, 'end')

def search():
    sw.getIDs(wb1)
    sw.getNames(wb1)
    name = entryName.get()
    id = entryId.get()
    if (id in map(str, sw.ids) and name == ''):
        info = []
        loc = sw.ids.index(int(id))
        for i in range(sw.cols):
            info.append(shead[i] + str(sw.sheet.cell(loc + 2, i + 1).value))
        info.append(shead[-1] + str(sw2.sheet.cell(loc + 2, 3).value))
        tk.messagebox.showinfo(title='Info', message=info)
        clearAll()
    elif (name in sw.names and id == ''):
        infoX = []
        loc = [idx for idx, i in enumerate(sw.names) if name == i]
        for k in loc:
            info = []
            for j in range(sw.cols):
                info.append(shead[j] + str(sw.sheet.cell(k + 2, j + 1).value))
            info.append(shead[-1] + str(sw2.sheet.cell(k + 2, 3).value))
            info = ''.join(info)
            infoX.append(info + '\n')
        tk.messagebox.showinfo(title='Info', message=infoX)
        clearAll()
    else:
        tk.messagebox.showerror(title = '错误！', message='查无此人！请重新输入！')
        clearAll()

def insert():
    sw.getIDs(wb1)
    id = entryId.get()
    if (id != '' and int(id) in sw.ids):
        tk.messagebox.showerror(title = '错误！', message='已有此人！请重新输入！')
        clearAll()
    elif (id == ''):
        pass
    else:
        name = entryName.get()
        sex = spinboxSex.get()
        age = spinboxAge.get()
        maj = entryMaj.get()
        rew = entryRew.get()
        inf = [int(id), name, sex, int(age), maj, rew]
        inf2 = [inf[0], inf[1], inf[-1]]
        sw.sheet.append(inf[0: 5])
        sw2.sheet.append(inf2)
        sw.workbook.save(wb1)
        sw2.workbook.save(wb2)
        tk.messagebox.showinfo(title='Info', message='添加成功！')
        clearAll()

def replace():
    sw.getIDs(wb1)
    id = entryId.get()
    sexx = spinboxSex.get()
    if (id != '' and sexx != '' and int(id) in sw.ids):
        loc = sw.ids.index(int(id))
        sw.sheet.cell(loc + 2, 3, sexx)
        sw.workbook.save(wb1)
        tk.messagebox.showinfo(title='Info', message='修改成功！')
        clearAll()
    elif (sexx == ''):
        tk.messagebox.showerror(title = '错误！', message='性别不能为空！请重新输入！')
        clearAll()
    elif (id == ''):
        pass
    else:
        tk.messagebox.showerror(title = '错误！', message='查无此人！请重新输入！')
        clearAll()

def delete():
    sw.getIDs(wb1)
    id = entryId.get()
    if (id != '' and int(id) in sw.ids):
        loc = sw.ids.index(int(id))
        sw.sheet.delete_rows(loc + 2, 1)
        sw2.sheet.delete_rows(loc + 2, 1)
        sw.workbook.save(wb1)
        sw2.workbook.save(wb2)
        tk.messagebox.showinfo(title='Info', message='删除成功！')
        clearAll()
    elif (id == ''):
        pass
    else:
        tk.messagebox.showerror(title = '错误！', message='查无此人！请重新输入！')
        clearAll()

sw = Stu()
sw2 = Stu()
sw.openAndGet(wb1)
sw2.openAndGet(wb2)

windows = tk.Tk()
windows.title('学生信息管理')
#第1行控件
lblId = tk.Label(text = '学号：')
lblId.grid(row = 0, column = 0)
entryId = tk.Entry()
entryId.grid(row = 0, column = 1)
lblName = tk.Label(text = '姓名：')
lblName.grid(row = 0, column = 2)
entryName = tk.Entry()
entryName.grid(row = 0, column = 3)
#第2行控件
lblSex = tk.Label(text = '性别：')
lblSex.grid(row = 1, column = 0)
spinboxSex = tk.Spinbox(windows, value = ('男', '女'))     # 仅仅对点击文本框右侧上下箭头时 
                                                            # 的取值限制，不能限制输入取值
spinboxSex.grid(row = 1, column = 1)
lblAge = tk.Label(text = '年龄：')
lblAge.grid(row = 1, column = 2)
spinboxAge = tk.Spinbox(windows, from_ = 15, to = 40)
spinboxAge.grid(row = 1, column = 3)
#第3行控件
lblMaj = tk.Label(text = '专业：')
lblMaj.grid(row = 2, column = 0)
entryMaj = tk.Entry()
entryMaj.grid(row = 2, column = 1)
lblRew = tk.Label(text = '奖励：')
lblRew.grid(row = 2, column = 2)
entryRew = tk.Entry()
entryRew.grid(row = 2, column = 3)
#分割线
ttk.Separator(orient=tk.HORIZONTAL).grid(row=3, column=0,columnspan=6,pady=10,sticky=tk.W+tk.E)
#按钮控件
btnSer = tk.Button(text = '查询', command = search)        # 需给出search函数
btnSer.grid(row = 4, column = 0)
btnIdx = tk.Button(text = '插入', command = insert)
btnIdx.grid(row = 4, column = 1)
btnRep = tk.Button(text = '修改', command = replace)
btnRep.grid(row = 4, column = 2)
btnDel = tk.Button(text = '删除', command = delete)
btnDel.grid(row = 4, column = 3)

windows.mainloop()                                         # 控件大循环

运行界面

按学号查询

按姓名查询

其他界面类似不再一一给出。