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

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

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

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

代码）

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

数据库名：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()