python界面实现数据库增删改查

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)

 

6.完整代码