数据库上机3（小型数据库应用程序开发）

注：

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

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

上机要求：

1、自学上层应用访问数据库的方式（如ODBC、ADO、JDBC或者其它），根据您使用的上层语言（不限语言（但要求与自己完成实验一开发语言一致）），不限数据库（除ACESS以外），选择并学习使用一种合适的访问数据库的方式。

2、基于1，实现第五版教材中9页的[例]。注意实现时使用两个表存放数据——学生基本信息表和奖励表，表具有相应的完整性约束。程序功能与用文件系统存储和管理这些数据所需实现的数据操作功能相同(即实验一中要求的功能与约束)。

3、对比文件系统与数据库系统实现相同功能操作时，各自优缺点。

4、要求实验三与实验一界面相同。

具体实现：

所用DBMS为MySQL，上层开发语言为Python

import pymysql    #python连接mysql的驱动
import tkinter as tk    #图形化界面的模块
import tkinter.ttk as ttk
import tkinter.messagebox  # 要使用messagebox先要导入模块table1 = "sbasicinf"
table2 = "srewardinf"#建立连接，在每个按钮的函数里创建游标
db = pymysql.Connect(host = "127.0.0.1", user = "root", passwd = "QAZwsxedc123@", db = "elective", port = 3306, charset ='utf8')    #括号内的“db =”虽然此处标为数据库名称，但实际为模式（SCHEMA）名称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():cursor = db.cursor()name = entryName.get()id = entryId.get()if(id != ''):try:id = int(id)sql1 = "select * from {} where Sno = {}".format(table1, id)cursor.execute(sql1)a = cursor.fetchone()if(a != None):      #此处将a换为cursor.fetchone()不行。原因待日后补充list1 = []for value in a:print(value)list1.append(value)sql2 = "select Srew from {} where Sno = {}".format(table2, id)cursor.execute(sql2)list1.append(cursor.fetchone()[0])tk.messagebox.showinfo(title='Info', message=tuple(list1))else:tk.messagebox.showerror(title='错误！', message='查无此人！请重新输入！')except:tk.messagebox.showerror(title='错误！', message='输入错误！请重新输入！')elif(id == '' and name != ""):sql1 = "select * from {} where Sname = \"{}\"".format(table1, name)cursor.execute(sql1)a = cursor.fetchall()if (a != ()):   #如果未查询到结果，返回一个空元组()list1 = []for value in a:value = list(value)list1.append([*value])sql2 = "select Srew from {} where Sname = \"{}\"".format(table2, name)cursor.execute(sql2)i = 0for value in cursor.fetchall():value = list(value)list1[i].append(value[-1])list1[i].append('\n')i += 1tk.messagebox.showinfo(title='Info', message = list1)else:tk.messagebox.showerror(title='错误！', message='查无此人！请重新输入！')clearAll()cursor.close()
#!!!python对MySQL进行数据的插入、更新和删除之后需要commit，数据库才会真的有数据操作。
def insert():cursor = db.cursor()id = entryId.get()name = entryName.get()sex = spinboxSex.get()age = spinboxAge.get()maj = entryMaj.get()rew = entryRew.get()if (id != ''):try:id = int(id)sql = "select * from {} where Sno = {}".format(table1, id)cursor.execute(sql)if(cursor.fetchone() != None):tk.messagebox.showerror(title='错误！', message='已有此人！请重新输入！')else:sql1 = "insert into {} values({}, \"{}\", \"{}\", {}, \"{}\")".format(table1, id, name, sex, age, maj)cursor.execute(sql1)sql2 = "insert into {} values({}, \"{}\", \"{}\")".format(table2, id, name, maj)cursor.execute(sql2)tk.messagebox.showinfo(title='Info', message='添加成功！')except:tk.messagebox.showerror(title='错误！', message='输入错误！请重新输入！')else:tk.messagebox.showerror(title='错误！', message='学号不能为空！请重新输入！')db.commit()clearAll()cursor.close()def alter():cursor = db.cursor()id = entryId.get()if(id != ''):try:id = int(id)sql = "select * from {} where Sno = {}".format(table1, id)cursor.execute(sql)if(cursor.fetchone() != None):sex = spinboxSex.get()sql = "update {} set Ssex = \"{}\" where Sno = {}".format(table1, sex, id)cursor.execute(sql)tk.messagebox.showinfo(title = 'Info', message='修改成功')else:tk.messagebox.showerror(title='错误！', message='查无此人！请重新输入！')except:tk.messagebox.showerror(title='错误！', message='输入错误！请重新输入！')else:tk.messagebox.showerror(title='错误！', message='学号不能为空！请重新输入！')db.commit()clearAll()cursor.close()def delete():cursor = db.cursor()id = entryId.get()if(id != ''):try:id = int(id)sql = "select * from {} where Sno = {}".format(table1, id)cursor.execute(sql)if(cursor.fetchone() != None):sql1 = "delete from {} where Sno = {}".format(table1, id)cursor.execute(sql1)sql2 = "delete from {} where Sno = {}".format(table2, id)cursor.execute(sql2)tk.messagebox.showinfo(title = 'Info', message='删除成功')else:tk.messagebox.showerror(title='错误！', message='查无此人！请重新输入！')except:tk.messagebox.showerror(title='错误！', message='输入错误！请重新输入！')else:tk.messagebox.showerror(title='错误！', message='学号不能为空！请重新输入！')db.commit()clearAll()cursor.close()#控件的布局
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)
btnSer.grid(row = 4, column = 0)
btnIdx = tk.Button(text = '插入', command = insert)
btnIdx.grid(row = 4, column = 1)
btnRep = tk.Button(text = '修改', command = alter)
btnRep.grid(row = 4, column = 2)
btnDel = tk.Button(text = '删除', command = delete)
btnDel.grid(row = 4, column = 3)windows.mainloop()

关系模式结构:

sbasicinf(Sno,Sname,Ssex,Sage,Sdept)

srewardinf(Sno,Sname,Srew)

sbasicinf表

属性名	类型	是否为主码	是否允许空
Sno	char（8）	是	否
Sname	char（10）	否	否
Ssex	char（2）	否	是
Sage	smallint	否	是
Sdept	char（10）	否	是