编写一个简单学生情况录入界面,配合后端数据库,实现数据的增、改、删功能
数据库准备:
import pymysql
class Mysql:
def __init__(self):
self.content = pymysql.Connect(
host='127.0.0.1', # mysql的主机ip
port=3306, # 端口
user='root', # 用户名
passwd='123456', # 数据库密码
db='test', # 数据库名
charset='utf8', # 字符集
)
self.cursor = self.content.cursor()
def query(self):
sql = "select id,name,grade from stu;"
self.cursor.execute(sql)
for row in self.cursor.fetchall():
print("id:%s\t name:%s\t grade:%s" % row)
print(f"一共查找到:{self.cursor.rowcount}")
def query_stu(self):
sql = "select id,name,grade from stu;"
self.cursor.execute(sql)
stu = []
for row in self.cursor.fetchall():
stu.append(row)
#print("id:%s\t name:%s\t grade:%s" % row)
return stu
def query_grade(self):
sql = "select grade from grade;"
self.cursor.execute(sql)
grades = []
for row in self.cursor.fetchall():
grades.append(row)
return grades
def insert(self,id,name,grade):
sql = """INSERT INTO stu(id,name,grade) VALUES(%s,%s,%s)"""
values=(int(id),str(name),int(grade))
try:
self.cursor.execute(sql,values)
self.content.commit()
return "插入成功"
except:
self.content.rollback
return "插入失败"
def update(self,id,grade):
sql = """update stu set grade=%s where id =%s"""
values=(int(grade),int(id))
try:
self.cursor.execute(sql,values)
self.content.commit()
print("更新成功")
except:
self.content.rollback
print("更新失败")
def delete(self,id):
sql = """delete from stu where id =%s"""
#values=(int(id))
try:
self.cursor.execute(sql,int(id))
self.content.commit()
return "删除成功"
except:
self.content.rollback
return '删除失败'
def end(self):
self.cursor.close()
self.content.close()
界面程序:
import dbmysql as mysql1
import tkinter as tk
from tkinter import messagebox
from tkinter import ttk
def treeview_sort_column(tv, col, reverse):#Treeview、列名、排列方式
selected_item = tree.selection()
for item in selected_item:
item_t=tree.item(item,"values")
l = [(tv.set(k, col), k) for k in tv.get_children('')]
#print(tv.get_children(''))
l.sort(reverse=reverse)#排序方式
# rearrange items in sorted positions
for index, (val, k) in enumerate(l):#根据排序后索引移动
tv.move(k, '', index)
#print(k)
tv.heading(col, command=lambda: treeview_sort_column(tv, col, not reverse))#重写标题,使之成为再点倒序的标题
def treeviewClick(event):
selected_item = tree.selection()
if len(selected_item)>0: #单击空白处不触发事件
for item in selected_item:
item_text=tree.item(item,"values")
T1.delete(0)
T1.insert(0,item_text[0])
T2.delete(0,tk.END)
T2.insert(0,item_text[1])
def stu_insert():
s_num=T1.get()
s_name=T2.get()
s_grade=cmb.get()
insert_flag=mysql.insert(s_num,s_name,s_grade)
if insert_flag == "插入成功":
tree.insert('','end',values=[s_num,s_name,s_grade]) ## 显示插入行
messagebox.showinfo(title='提示', message=insert_flag)
else:
messagebox.showinfo(title='提示', message=insert_flag)
def stu_show():
stu = mysql.query_stu()
i=0
#定义列
#tree['columns'] = ('学号','姓名','年级')
#设置列
tree.column('学号',width=100)
tree.column('姓名',width=100)
tree.column('年级',width=100)
#设置表头
tree.heading('学号',text='学号')
tree.heading('姓名',text='姓名')
tree.heading('年级',text='年级')
x=tree.get_children()
for item in x:
tree.delete(item)
for row in stu:
tree.insert('','end',values=row)
def stu_delete():
selected_item = tree.selection()
for item in selected_item:
item_text=tree.item(item,"values")
#print(item_text)
delete_flag = mysql.delete(item_text[0])
if delete_flag == "删除成功":
tree.delete(selected_item)## 显示上删除选中行
messagebox.showinfo(title='提示', message=delete_flag)
else:
messagebox.showinfo(title='提示', message=delete_flag)
def stu_update():
selected_item = tree.selection()
for item in selected_item:
item_text=tree.item(item,"values")
#print(item_text)
delete_flag = mysql.delete(item_text[0])
if delete_flag == "删除成功":
tree.delete(selected_item)## 显示上删除选中行
s_num=T1.get()
s_name=T2.get()
s_grade=cmb.get()
insert_flag=mysql.insert(s_num,s_name,s_grade)
if insert_flag == "插入成功":
tree.insert('','end',values=[s_num,s_name,s_grade]) ## 显示插入行
messagebox.showinfo(title='提示', message="更新成功")
else:
messagebox.showinfo(title='提示', message="更新失败")
window = tk.Tk()
window.title('INSERT STUDENT')
window.geometry('1000x600')
mysql = mysql1.Mysql()
frame1 = tk.Frame(window)
frame1.pack()
tk.Label(frame1, text="请输入学生信息").grid(row=0,column=0)
tk.Label(frame1, text="学号").grid(row=1, column=0)
dafalt_var =tk.StringVar(value='请输入')
T1 = tk.Entry(frame1, bd =5,textvariable=dafalt_var)
T1.grid(row=1, column=1)
tk.Label(frame1, text="姓名").grid(row=2, column=0)
T2 = tk.Entry(frame1, bd =5,textvariable=dafalt_var)
T2.grid(row=2, column=1)
tk.Label(frame1, text="年级").grid(row=3, column=0)
cmb = ttk.Combobox(frame1)
#cmb['value']=(1,2,3)
cmb['value']=mysql.query_grade()
cmb.current(0)
cmb.grid(row=3, column=1)
frame2 = tk.Frame(window)
frame2.pack()
tk.Button(frame2, text='查询', command=stu_show).grid(row=4, column=0)
tk.Button(frame2, text='插入', command=stu_insert).grid(row=4, column=1)
tk.Button(frame2, text='修改', command=stu_update).grid(row=4, column=2)
tk.Button(frame2, text='删除', command=stu_delete).grid(row=4, column=3)
tk.Button(frame2, text='退出', command=window.quit).grid(row=4, column=4)
#添加查询结果界面
frame3 = tk.Frame(window)
frame3.pack()
colums=('学号','姓名','年级')
tree =ttk.Treeview(frame3,show="headings",columns=colums)
#定义排序
for col in colums: #给所有标题加(循环上边的“手工”)
tree.heading(col, text=col, command=lambda _col=col: treeview_sort_column(tree, _col, False))
#绑定左键单击选中行事件
tree.bind('<ButtonRelease-1>', treeviewClick)
tree.pack()
window.mainloop()
mysql.end()