大二的一名菜鸡程序员完成的数据库实验报告…就自己用tkinker做的窗口对接了本地的数据库,界面很丑。。但基本功能能够实现哈哈哈~
首先需要两个库,pymysql和tkinter,pymysql可以用其他连接数据库的代替。
import pymysql
from tkinter import *
from tkinter import ttk
先连接数据库
con = pymysql.connect(host='localhost', port=3306, user='root', password='******', db='test')
cur = con.cursor()
写一个数据库类(其实可以直接用函数,我想的是统一的数据库类方便调试的找位置)
class Mysql(object):
num = '' # 添加内容
name = ''
major = ''
def insert(self, num, name, major):
sql = """
insert into 学生信息表 (学号,姓名,专业) values ("{num}","{name}","{major}")
""".format(num=num, name=name, major=major)
return sql
def delete(self, num, name, major):
sql1 = """
delete from 学生信息表 where 学号= "{num}" ;
""".format(num=num)
sql2 = """
delete from 学生信息表 where 姓名= "{name}" ;
""".format(name=name)
sql3 = """
delete from 学生信息表 where 专业= "{major}" ;
""".format(major=major)
All = """
delete from 学生信息表;
"""
return sql1, sql2, sql3, All
def select(self, num, name, major):
sql = """
select * from 学生信息表
"""
sql1 = """
select 学号 from 学生信息表
"""
sql2 = """
select 姓名 from 学生信息表
"""
sql3 = """
select 专业 from 学生信息表
"""
sql4 = """
select * from 学生信息表 where 学号="{num}";
""".format(num=num)
sql5 = """
select * from 学生信息表 where 姓名="{name}";
""".format(name=name)
sql6 = """
select * from 学生信息表 where 专业="{major}";
""".format(major=major)
return sql, sql1, sql2, sql3, sql4, sql5, sql6
def change(self, num, name, major, oldName):
sql = """
update 学生信息表 set 学号="{num}" where 姓名="{oldName}";
""".format(num=num, oldName=oldName)
sql2 = """
update 学生信息表 set 姓名="{name}" where 姓名="{oldName}";
""".format(name=name, oldName=oldName)
sql3 = """
update 学生信息表 set 专业="{major}" where 姓名="{oldName}";
""".format(major=major, oldName=oldName)
return sql, sql2, sql3
再写一个窗口所需要的类Choose
class Choose:
num = ''
name = ''
major = ''
size = '600x300'
Width = 20
Height = 1
代码的主体部分就全部放在这个类里面的,包含了连接数据库的操作和窗口创立,一部分一部分贴代码吧
增加选项
def intoInsert(self):
top2 = Tk(className='增加选项')
y1 = 0
y2 = y1 + 100
y3 = y2 + 100
x = 150
def getDate():
mysql = Mysql()
Choose.num = text.get('0.0', 'end')
Choose.name = text2.get('0.0', 'end')[:-1]
Choose.major = text3.get('0.0', 'end')[:-1]
print(Choose.num,Choose.name,1,Choose.major)
top3 = Tk(className='OK')
Label(top3, text='传值成功!', width=Choose.Width, height=Choose.Height, command=top2.destroy(), bg='#32FFFF',
fg='#FF6666', font=('宋体', 20), relief=SUNKEN).pack()
top3.geometry('200x50')
top3.resizable(0, 0)
top3.mainloop()
cur.execute(mysql.insert(Choose.num, Choose.name, Choose.major))
con.commit()
text = Text(top2, width=30, height=1, font=('宋体', 20))
# Button(top2, text='确定', width=Choose.Width, height=Choose.Height).place(x=x, y=30)
Label(top2, text='请输入学号', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y1)
text2 = Text(top2, width=30, height=1, font=('宋体', 20))
# Button(top2, text='确定', width=Choose.Width, height=Choose.Height).place(x=x, y=30+y2)
Label(top2, text='请输入姓名', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y2)
text3 = Text(top2, width=30, height=1, font=('宋体', 20))
Button(top2, text='确定', width=Choose.Width, height=Choose.Height, command=getDate).place(x=x + 80, y=60 + y3)
Label(top2, text='请输入专业', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y3)
top2.geometry(Choose.size)
top2.resizable(0, 0)
text.place(x=x, y=y1)
text2.place(x=x, y=y2)
text3.place(x=x, y=y3)
top2.mainloop()
整体为增加选项的窗口代码,在其中有一个方法getDate是表示在窗口输入的数据通过其方法将连接到数据库中,实现在数据库中的数据添加内容
删除选项
def intoDelete(self):
mysql = Mysql()
top2 = Tk(className='删除选项')
y1 = 0
y2 = y1 + 70
y3 = y2 + 70
y4 = y3 + 70
x = 150
def getNum():
Choose.num = text.get('0.0', 'end')
num, name, major, All = mysql.delete(Choose.num, Choose.name, Choose.major)
top3 = Tk(className='OK')
Label(top3, text='传值成功!', width=Choose.Width, height=Choose.Height, command=top2.destroy(),
bg='#32FFFF',
fg='#FF6666', font=('宋体', 20), relief=SUNKEN).pack()
top3.geometry('200x50')
top3.resizable(0, 0)
top3.mainloop()
cur.execute(num)
con.commit()
def getName():
Choose.name = text2.get('0.0', 'end')[:-1]
num, name, major, All = mysql.delete(Choose.num, Choose.name, Choose.major)
top3 = Tk(className='OK')
Label(top3, text='传值成功!', width=Choose.Width, height=Choose.Height, command=top2.destroy(),
bg='#32FFFF',
fg='#FF6666', font=('宋体', 20), relief=SUNKEN).pack()
top3.geometry('200x50')
top3.resizable(0, 0)
top3.mainloop()
cur.execute(name)
con.commit()
def getMajor():
Choose.major = text3.get('0.0', 'end')[:-1]
num, name, major, All = mysql.delete(Choose.num, Choose.name, Choose.major)
top3 = Tk(className='OK')
Label(top3, text='传值成功!', width=Choose.Width, height=Choose.Height, command=top2.destroy(),
bg='#32FFFF',
fg='#FF6666', font=('宋体', 20), relief=SUNKEN).pack()
top3.geometry('200x50')
top3.resizable(0, 0)
top3.mainloop()
cur.execute(major)
con.commit()
def getAll():
num, name, major, All = mysql.delete(Choose.num, Choose.name, Choose.major)
top3 = Tk(className='OK')
Label(top3, text='删除成功!', width=Choose.Width, height=Choose.Height, command=top2.destroy(),
bg='#32FFFF',
fg='#FF6666', font=('宋体', 20), relief=SUNKEN).pack()
top3.geometry('200x50')
top3.resizable(0, 0)
top3.mainloop()
cur.execute(All)
con.commit()
text = Text(top2, width=30, height=1, font=('宋体', 20))
Button(top2, text='确定', width=Choose.Width, height=Choose.Height, command=getNum).place(x=x + 80, y=30)
Label(top2, text='请输入学号', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y1)
text2 = Text(top2, width=30, height=1, font=('宋体', 20))
Button(top2, text='确定', width=Choose.Width, height=Choose.Height, command=getName).place(x=x + 80, y=30 + y2)
Label(top2, text='或输入姓名', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y2)
text3 = Text(top2, width=30, height=1, font=('宋体', 20))
Button(top2, text='确定', width=Choose.Width, height=Choose.Height, command=getMajor).place(x=x + 80, y=30 + y3)
Label(top2, text='或输入专业', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y3)
Button(top2, text='点击删除所有数据', width=35, height=2, fg='#C24AC6', bg='#FAFF5F', font=('楷体', 15)
, command=getAll).place(x=x - 30, y=30 + y4)
top2.geometry(Choose.size)
top2.resizable(0, 0)
text.place(x=x, y=y1)
text2.place(x=x, y=y2)
text3.place(x=x, y=y3)
top2.mainloop()
整体结构和增加差不多,多了一个 “删除所有数据选项”,以数据库类传下来的参数调用数据库实现功能。
更改选项
def intoUpdate(self):
oldName = ''
top2 = Tk(className='更改选项')
y1 = 0
y2 = y1 + 70
y3 = y2 + 70
y4 = y3 + 70
x = 150
def getNum():
mysql = Mysql()
Choose.num = text2.get('0.0', 'end')
oldName = text.get('0.0', 'end')[:-1]
num, name, major = mysql.change(Choose.num, Choose.name, Choose.major, oldName)
top3 = Tk(className='OK')
Label(top3, text='传值成功!', width=Choose.Width, height=Choose.Height, command=top2.destroy(), bg='#32FFFF',
fg='#FF6666', font=('宋体', 20), relief=SUNKEN).pack()
top3.geometry('200x50')
top3.resizable(0, 0)
top3.mainloop()
cur.execute(num)
con.commit()
def getName():
mysql = Mysql()
Choose.name = text3.get('0.0', 'end')[:-1]
Choose.oldName = text.get('0.0', 'end')[:-1]
num, name, major = mysql.change(Choose.num, Choose.name, Choose.major, Choose.oldName)
top3 = Tk(className='OK')
Label(top3, text='传值成功!', width=Choose.Width, height=Choose.Height, command=top2.destroy(), bg='#32FFFF',
fg='#FF6666', font=('宋体', 20), relief=SUNKEN).pack()
top3.geometry('200x50')
top3.resizable(0, 0)
top3.mainloop()
cur.execute(name)
con.commit()
def getMajor():
mysql = Mysql()
Choose.major = text4.get('0.0', 'end')[:-1]
Choose.oldName = text.get('0.0', 'end')[:-1]
num, name, major = mysql.change(Choose.num, Choose.name, Choose.major, Choose.oldName)
top3 = Tk(className='OK')
Label(top3, text='传值成功!', width=Choose.Width, height=Choose.Height, command=top2.destroy(), bg='#32FFFF',
fg='#FF6666', font=('宋体', 20), relief=SUNKEN).pack()
top3.geometry('200x50')
top3.resizable(0, 0)
top3.mainloop()
cur.execute(major)
con.commit()
text = Text(top2, width=30, height=1, font=('宋体', 20))
Label(top2, text='输入原姓名', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y1)
text2 = Text(top2, width=30, height=1, font=('宋体', 20))
Button(top2, text='确定', width=Choose.Width, height=Choose.Height, command=getNum).place(x=x + 60, y=30+y2)
Label(top2, text='请输入学号', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y2)
text3 = Text(top2, width=30, height=1, font=('宋体', 20))
Button(top2, text='确定', width=Choose.Width, height=Choose.Height, command=getName).place(x=x + 60, y=30+y3)
Label(top2, text='请输入姓名', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y3)
text4 = Text(top2, width=30, height=1, font=('宋体', 20))
Button(top2, text='确定', width=Choose.Width, height=Choose.Height, command=getMajor).place(x=x + 60, y=30 + y4)
Label(top2, text='请输入专业', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y4)
top2.geometry(Choose.size)
top2.resizable(0, 0)
text.place(x=x, y=y1)
text2.place(x=x, y=y2)
text3.place(x=x, y=y3)
text4.place(x=x, y=y4)
top2.mainloop()
目前只能支持一个数据一个数据的修改…
查询选项
数据库最重要和复杂的功能我只实现了最基础的几个选项以后有机会会更新函数计算的功能
def intoSelect(self):
mysql = Mysql()
width = 100
top2 = Tk(className='查询选项')
y1 = 0
y2 = y1 + 70
y3 = y2 + 70
y4 = y3 + 70
x = 150
# All, AllNum, AllName, AllMajor, num, name, major = mysql.select(Choose.num, Choose.name, Choose.major)
def getAll():
All, AllNum, AllName, AllMajor, num, name, major = mysql.select(Choose.num, Choose.name, Choose.major)
top3 = Tk(className='整表查询')
top3.geometry('500x300')
top3.resizable(0, 0)
tree = ttk.Treeview(top3)
tree['columns'] = ('学号', '姓名', '专业')
tree.column('学号', width=120)
tree.column('姓名', width=width)
tree.column('专业', width=width)
tree.heading('#0', text='数量', anchor='w')
tree.column('#0', anchor='w')
tree.heading('学号', text='学号')
tree.heading('姓名', text='姓名')
tree.heading('专业', text='专业')
cpt = 1
cur.execute(All)
for row in cur:
tree.insert('', 'end', text=str(cpt), values=(row[0], row[1], row[2]))
cpt += 1
con.commit()
tree.pack()
top3.mainloop()
def getAllNum():
Choose.num = text.get('0.0', 'end')
All, AllNum, AllName, AllMajor, num, name, major = mysql.select(Choose.num, Choose.name, Choose.major)
top3 = Tk(className='所有学号查询')
top3.geometry('500x300')
tree = ttk.Treeview(top3)
tree['columns'] = ('学号')
tree.column('学号', width=120)
tree.heading('#0', text='数量', anchor='w')
tree.column('#0', anchor='w')
tree.heading('学号', text='学号')
cur.execute(AllNum)
cpt = 1
for row in cur:
tree.insert('', 'end', text=str(cpt), values=(row[0]))
cpt += 1
con.commit()
tree.pack()
top3.resizable(0, 0)
top3.mainloop()
def getAllName():
Choose.num = text.get('0.0', 'end')
All, AllNum, AllName, AllMajor, num, name, major = mysql.select(Choose.num, Choose.name, Choose.major)
top3 = Tk(className='所有姓名查询')
top3.geometry('500x300')
tree = ttk.Treeview(top3)
tree['columns'] = ('姓名')
tree.column('姓名', width=120)
tree.heading('#0', text='数量', anchor='w')
tree.column('#0', anchor='w')
tree.heading('姓名', text='姓名')
cur.execute(AllName)
cpt = 1
for row in cur:
tree.insert('', 'end', text=str(cpt), values=(row[0]))
cpt += 1
con.commit()
tree.pack()
top3.resizable(0, 0)
top3.mainloop()
def getAllMajor():
Choose.num = text2.get('0.0', 'end')
All, AllNum, AllName, AllMajor, num, name, major = mysql.select(Choose.num, Choose.name, Choose.major)
top3 = Tk(className='所有专业查询')
top3.geometry('500x300')
tree = ttk.Treeview(top3)
tree['columns'] = ('专业')
tree.column('专业', width=120)
tree.heading('#0', text='数量', anchor='w')
tree.column('#0', anchor='w')
tree.heading('专业', text='专业')
cur.execute(AllMajor)
cpt = 1
for row in cur:
tree.insert('', 'end', text=str(cpt), values=(row[0]))
cpt += 1
con.commit()
tree.pack()
top3.resizable(0, 0)
top3.mainloop()
def getName():
Choose.name = text2.get('0.0', 'end')[:-1]
All, AllNum, AllName, AllMajor, num, name, major = mysql.select(Choose.num, Choose.name, Choose.major)
top3 = Tk(className='姓名查询')
top3.geometry('500x300')
top3.resizable(0, 0)
tree = ttk.Treeview(top3)
tree['columns'] = ('学号', '姓名', '专业')
tree.column('学号', width=120)
tree.column('姓名', width=width)
tree.column('专业', width=width)
tree.heading('#0', text='数量', anchor='w')
tree.column('#0', anchor='w')
tree.heading('学号', text='学号')
tree.heading('姓名', text='姓名')
tree.heading('专业', text='专业')
cpt = 1
cur.execute(name)
for row in cur:
tree.insert('', 'end', text=str(cpt), values=(row[0], row[1], row[2]))
cpt += 1
con.commit()
tree.pack()
top3.mainloop()
def getMajor():
Choose.major = text3.get('0.0', 'end')[:-1]
All, AllNum, AllName, AllMajor, num, name, major = mysql.select(Choose.num, Choose.name, Choose.major)
top3 = Tk(className='专业查询')
top3.geometry('500x300')
top3.resizable(0, 0)
tree = ttk.Treeview(top3)
tree['columns'] = ('学号', '姓名', '专业')
tree.column('学号', width=120)
tree.column('姓名', width=width)
tree.column('专业', width=width)
tree.heading('#0', text='数量', anchor='w')
tree.column('#0', anchor='w')
tree.heading('学号', text='学号')
tree.heading('姓名', text='姓名')
tree.heading('专业', text='专业')
cpt = 1
cur.execute(major)
for row in cur:
tree.insert('', 'end', text=str(cpt), values=(row[0], row[1], row[2]))
cpt += 1
con.commit()
tree.pack()
top3.mainloop()
def getNum():
Choose.num = text.get('0.0', 'end')
All, AllNum, AllName, AllMajor, num, name, major = mysql.select(Choose.num, Choose.name, Choose.major)
top3 = Tk(className='学号查询')
top3.geometry('500x300')
top3.resizable(0, 0)
tree = ttk.Treeview(top3)
tree['columns'] = ('学号', '姓名', '专业')
tree.column('学号', width=120)
tree.column('姓名', width=width)
tree.column('专业', width=width)
tree.heading('#0', text='数量', anchor='w')
tree.column('#0', anchor='w')
tree.heading('学号', text='学号')
tree.heading('姓名', text='姓名')
tree.heading('专业', text='专业')
cpt = 1
cur.execute(num)
for row in cur:
tree.insert('', 'end', text=str(cpt), values=(row[0], row[1], row[2]))
cpt += 1
con.commit()
tree.pack()
top3.mainloop()
text = Text(top2, width=14, height=1, font=('宋体', 20))
Button(top2, text='确定', width=Choose.Width, height=Choose.Height, command=getNum).place(x=x, y=30)
Label(top2, text='请输入学号', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y1)
text2 = Text(top2, width=14, height=1, font=('宋体', 20))
Button(top2, text='确定', width=Choose.Width, height=Choose.Height, command=getName).place(x=x, y=30 + y2)
Label(top2, text='或输入姓名', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y2)
text3 = Text(top2, width=14, height=1, font=('宋体', 20))
Button(top2, text='确定', width=Choose.Width, height=Choose.Height, command=getMajor).place(x=x, y=30 + y3)
Label(top2, text='或输入专业', bg='#32FFFF', fg='#FF6666', font=('宋体', 20), width=10, height=1, relief=SUNKEN) \
.place(x=0, y=y3)
Button(top2, text='查询所有学号', width=20, height=2, fg='#795106', bg='#86AEF9', font=('黑体', 12),
command=getAllNum).place(x=400, y=0)
Button(top2, text='查询所有姓名', width=20, height=2, fg='#795106', bg='#86AEF9', font=('黑体', 12),
command=getAllName).place(x=400, y=70)
Button(top2, text='查询所有专业', width=20, height=2, fg='#795106', bg='#86AEF9', font=('黑体', 12),
command=getAllMajor).place(x=400, y=140)
Button(top2, text='查询所有数据', width=35, height=2, fg='#C24AC6', bg='#FAFF5F', font=('楷体', 15), command=getAll)\
.place(x=x-30, y=30 + y4)
top2.geometry(Choose.size)
top2.resizable(0, 0)
text.place(x=x, y=y1)
text2.place(x=x, y=y2)
text3.place(x=x, y=y3)
top2.mainloop()
choose = Choose()
虽然是简单的实现查询功能,但确实方法写的最多的一个项目
调用部分
def hitButton():
global top
top.destroy()
top2 = Tk()
top2.title('操作选项')
width = 72
height = 1
Button(top2, text='增加', width=width, height=height, command=choose.intoInsert, bg='light green', font='楷体') \
.grid(row=0, column=1, padx=10, pady=20)
Button(top2, text='删除', width=width, height=height, command=choose.intoDelete, bg='light yellow', font='楷体') \
.grid(row=10, column=1, padx=10, pady=20)
Button(top2, text='修改', width=width, height=height, command=choose.intoUpdate, bg='light pink', font='楷体') \
.grid(row=20, column=1, padx=10, pady=20)
Button(top2, text='查询', width=width, height=height, command=choose.intoSelect, bg='red', font='楷体') \
.grid(row=30, column=1, padx=10, pady=20)
top2.geometry('600x300')
top2.resizable(0, 0) # 锁定窗口大小
top2.mainloop()
bt = Button(width=10, height=2, command=hitButton)
bt['text'] = '登陆'
lb.pack()
bt.pack()
top.geometry('320x100')
top.resizable(0, 0)
top.mainloop()
print('Yes')
只是最基本的实现了功能肯定有很多缺点不足,希望有大佬指教。。