数据库实验五：数据库编程

2、设计一个小型的数据库应用程序

 可利用现有的数据库，也可重新设计数据库。

 要求实现数据的增加、删除、修改、查询的功能。

 在报告中描述清楚使用的数据库、数据表及实现的功能（要求截图，并附

代码）

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194

设计一个小型的数据库应用程序   数据库名：student   表名：infor   字段：     Sno：学号   Sname：姓名   Ssex：性别   Sage：年龄   Smaj：专业   Srew：奖项   功能实现   1. 添加学生信息：向表中插入学生的学号、姓名、性别、年龄、专业、奖项。 2. 修改学生信息：根据学号修改学生的姓名、性别、年龄、专业、奖项。 3. 删除学生信息：根据学号删除学生的所有信息。 4. 查询学生信息：根据学号查询学生的所有信息。      程序实现（Python连接MySQL） import pymysql  # python连接mysql的驱动 import tkinter as tk  # 图形化界面的模块 import tkinter.ttk as ttk import tkinter.messagebox  # 要使用messagebox先要导入模块   table = "infor"   # 建立连接，在每个按钮的函数里创建游标 db = pymysql.connect(host="192.168.163.241", user="root", passwd="123456", db="student", port=3306, charset='utf8')   # 清空输入框内容 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()     student_id = entryId.get()       if student_id != '':         try:             student_id = int(student_id)             sql = "SELECT *, Srew FROM {} WHERE Sno = {}".format(table, student_id)             cursor.execute(sql)             result = cursor.fetchone()             if result is not None:                 data = list(result)                 tk.messagebox.showinfo(title='Info', message=tuple(data))             else:                 tk.messagebox.showerror(title='错误！', message='查无此人！请重新输入！')         except:             tk.messagebox.showerror(title='错误！', message='输入错误！请重新输入！')       clearAll()     cursor.close()   # 修改 信息 def alter():     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(table, id)             cursor.execute(sql)             if (cursor.fetchone() != None):                   sql1 = "update infor set Sname = '{}', Ssex = '{}', Sage = {}, Smaj = '{}', Srew = '{}' where Sno = {}".format(                     name, sex, age, maj, rew, id)                 cursor.execute(sql1)                 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(table, id)             cursor.execute(sql)             if (cursor.fetchone() != None):                 sql1 = "delete from {} where Sno = {}".format(table, id)                 cursor.execute(sql1)                 sql2 = "delete from {} where Sno = {}".format(table, 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()   # !!!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 != ''):         id = int(id)         sql = "select * from {} where Sno = {}".format(table, id)         cursor.execute(sql)         if (cursor.fetchone() != None):             tk.messagebox.showerror(title='错误！', message='已有此人！请重新输入！')         else:             sql1 = "insert into infor (Sno, Sname, Ssex, Sage, Smaj, Srew) values({}, \"{}\", \"{}\", {}, \"{}\", \"{}\")".format(                 id, name, sex, age, maj, rew)             cursor.execute(sql1)             tk.messagebox.showinfo(title='Info', 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()