1.效果图
2.关键代码
1.tkinter窗口设置
#窗口
window=tk.Tk()
# 设置窗口大小
winWidth = 800
winHeight = 600
# 获取屏幕分辨率
screenWidth = window.winfo_screenwidth()
screenHeight = window.winfo_screenheight()
x = int((screenWidth - winWidth) / 2)
y = int((screenHeight - winHeight) / 2)
# 设置主窗口标题
window.title("学生管理数据库系统")
# 设置窗口初始位置在屏幕居中
window.geometry("%sx%s+%s+%s" % (winWidth, winHeight, x, y))
# 设置窗口图标
window.iconbitmap("./icon.ico")
# 设置窗口宽高固定
window.resizable(0, 0)
#界面分割
frm_root=tk.Frame(window)
frm_head=tk.Frame(frm_root,height=50,width=800)
frm_infoShow=tk.Frame(frm_root,height=200,width=800)
frm_widget=tk.Frame(frm_root,height=300,width=800)
frm_head.pack_propagate(0)
frm_infoShow.pack_propagate(0)
frm_widget.pack_propagate(0)
frm_root.pack()
frm_head.pack()
frm_infoShow.pack()
frm_widget.pack()
2.数据库操作
数据库初始化
#建立连接
conn = sqlite3.connect('Info.db')
cur = conn.cursor()
cur.execute("PRAGMA foreign_keys = ON")
#建表
def create_table(conn, cur,tab_name, col_prop_list, txt_path):
col_name_props = ','.join(col_prop_list)
cur.execute('CREATE TABLE IF NOT EXISTS %s(%s)'%(tab_name, col_name_props))
f = open(txt_path,'r')
for x in f:
x = x.rstrip().split(',')
a = ["'%s'"%x[i] for i in range(len(x))]
x = ','.join(a)
cur.execute('INSERT INTO %s VALUES(%s)'%(tab_name, x))
f.close()
# print('%s创建成功'%tab_name)
# print('%s导入成功'%txt_path)
#查询表结构
def table_struct(cur, tab_name):
cur.execute("PRAGMA table_info( %s )" % tab_name)
t_stust = cur.fetchall()
for item in t_stust:
for x in item:
x = str(x)
print(x+'\t')
print()
#单表查询
def table_query(cur, tab_name, col_names = '*', num_line = None):
cur.execute('SELECT %s FROM %s' % (col_names, tab_name))
Li = cur.fetchall()
return Li[:num_line]
#删除表中数据
def table_delete(cur, tab_name,Id):
cur.execute('DELETE FROM %s WHERE Id = %s' % (tab_name, Id))
#更新表中数据
def table_update(cur, tab_name, cols_list, values_list):
list_value=[]
for i in range(1,len(cols_list)):
tmp = cols_list[i] + "=" + values_list[i]
list_value.append(tmp)
update_value=','.join(list_value)
cur.execute('UPDATE %s SET %s WHERE Id = %s' % (tab_name, update_value, values_list[0]))
#插入数据
def table_insert(cur, tab_name, values_list):
insert_values=','.join(values_list)
cur.execute('INSERT INTO %s VALUES(%s)' % (tab_name, insert_values))
#多表查询
def comprehensive_query(cur, Id, num_line = None):
if Id=="\'\'":
cur.execute('SELECT Student.Id, StudentName, CourseId, CourseName, grade FROM Student,Course,Grade WHERE Student.Id == StudentId AND Course.Id==CourseId')
else:
cur.execute('SELECT Student.Id, StudentName, CourseId, CourseName, grade FROM Student,Course,Grade WHERE Student.Id == StudentId AND Course.Id==CourseId AND StudentId = %s' % Id)
Li = cur.fetchall()
return Li[:num_line]
3.treeData树结构显示数据
#回显表格,默认回显学生信息表
treeDate=ttk.Treeview(frm_infoShow,
show='headings',
selectmode=tk.BROWSE)
treeDate.pack(side=LEFT)
#清空列表
def clearForm():
children=treeDate.get_children()
for child in children:
treeDate.delete(child)
#显示列名称
columns=[
("id","学号"),
("name","姓名"),
("sex","性别"),
("birth","生日"),
("majorId","专业编号"),
("scholar","奖学金"),
("party","党员"),
("photo","照片"),
("remark","备注"),]
treeDate['columns']=[column[0] for column in columns]
for column in columns:
treeDate.column(column[0],width=90)
treeDate.heading(column[0],text=column[1])
#单击某条记录显示至输入框
form_record=[]
def record_show_to_input():
global form_record
clearInput()
if(radioIndex.get()==1):
input1.insert('end',form_record[0])
input2.insert('end',form_record[1])
elif(radioIndex.get()==2):
input1.insert('end',form_record[0])
input2.insert('end',form_record[1])
input3.insert('end',form_record[2])
input4.insert('end',form_record[3])
input5.insert('end',form_record[4])
input6.insert('end',form_record[5])
input7.insert('end',form_record[6])
input8.insert('end',form_record[7])
input9.insert('end',form_record[8])
elif(radioIndex.get()==3):
input1.insert('end',form_record[0])
input2.insert('end',form_record[1])
input3.insert('end',form_record[2])
input4.insert('end',form_record[3])
input5.insert('end',form_record[4])
elif(radioIndex.get()==4):
input1.insert('end',form_record[0])
input2.insert('end',form_record[1])
input3.insert('end',form_record[2])
else:
input1.insert('end',form_record[0])
input2.insert('end',form_record[1])
input3.insert('end',form_record[2])
input4.insert('end',form_record[3])
input5.insert('end',form_record[4])
#表格点击响应函数
def selectTree(event):
global form_record
form_record = treeDate.item(treeDate.selection(),"values")
#显示至输入框
record_show_to_input()
treeDate.bind('<<TreeviewSelect>>',selectTree)
4.单选按钮
#单选按钮,选择当前需要查询的表,默认为学生信息表
radioIndex=IntVar()
radioIndex.set(2)
curTable="Student"
curTableCols=["StudentId","StudentName","Sex","Birthday","MarjorId","Scholarship","PartyMember","Photo","Remark"]
label1=tk.Label(frm_widget,
text='请选择需要查询的表')
label1.place(x=30, y=80, anchor=W)
5.增删改查按钮
#查询按钮
def sql_query():
clearForm()
if(radioIndex.get()!=5):
list=table_query(cur, curTable, col_names = '*', num_line = 10)
else:
list=comprehensive_query(cur, "\'"+input1.get()+"\'", num_line = 10)
i=0
for data in list:
treeDate.insert('',i,text='date',values=data)
i=i+1
btn_query = tk.Button(frm_widget,
text="查询",
width=10,height=1,
command=sql_query)
btn_query.place(x=150,y=80)
#插入按钮
def sql_insert():
getInput()
table_insert(cur, curTable, input_values)
sql_query()
btn_insert = tk.Button(frm_widget,
text="插入",
width=10,height=1,
command=sql_insert)
btn_insert.place(x=150,y=120)
#更新按钮
def sql_update():
getInput()
table_update(cur, curTable, curTableCols, input_values)
sql_query()
btn_update = tk.Button(frm_widget,
text="更新",
width=10,height=1,
command=sql_update)
btn_update.place(x=150,y=160)
#删除按钮
def sql_delete():
table_delete(cur, curTable, "\'"+input1.get()+"\'")
sql_query()
btn_delete = tk.Button(frm_widget,
text="删除",
width=10,height=1,
command=sql_delete)
btn_delete.place(x=150,y=200)