Desktop database application
使用tkinter创建软件界面
frontend
Label,Text,Button,Entry;
Entry可由textvariable得到输入;
Title_text=StringVar()
e1=Entry(window,textvariable=Title_text)
下面为初始interface代码;
"""
A program that stores this book information:
Title, Author
year,ISBN
user can:
View all records
Search an entry
Add entry
Update entry
Delete
Close
"""
from tkinter import *
import backend
window=Tk()
l1=Label(window,text="Title")
l1.grid(row=0,column=0)
l2=Label(window,text="Author")
l2.grid(row=0,column=2)
l3=Label(window,text="Year")
l3.grid(row=1,column=0)
l4=Label(window,text="ISBN")
l4.grid(row=1,column=2)
Title_text=StringVar()
e1=Entry(window,textvariable=Title_text)
e1.grid(row=0,column=1)
Author_text=StringVar()
e2=Entry(window,textvariable=Author_text)
e2.grid(row=0,column=3)
Year_text=StringVar()
e3=Entry(window,textvariable=Year_text)
e3.grid(row=1,column=1)
ISBN_text=StringVar()
e4=Entry(window,textvariable=ISBN_text)
e4.grid(row=1,column=3)
list1=Listbox(window,height = 10,width=40)
list1.grid(row=2,column=0,rowspan=6,columnspan=2)
#scrollbar
sb1=Scrollbar(window)
sb1.grid(row=2,column=2,rowspan=6)
list1.configure(yscrollcommand=sb1.set) #list1的y轴受sb1的y轴控制
sb1.configure(command=list1.yview) #sb1的y轴控制list1的y轴
#6个按钮
b1=Button(window,text="View all",width=12) #commanda设置功能
b1.grid(row=2,column=3)
b2=Button(window,text="Search entry",width=12)
b2.grid(row=3,column=3)
b3=Button(window,text="Add entry",width=12)
b3.grid(row=4,column=3)
b4=Button(window,text="Update",width=12)
b4.grid(row=5,column=3)
b5=Button(window,text="Delete",width=12)
b5.grid(row=6,column=3)
b6=Button(window,text="Close",width=12)
b6.grid(row=7,column=3)
window.mainloop()
backend
将对SQL进行处理的函数放在backend中;
connect创建table(含有title,author,year,ISBN四个column);
insert插入新书;
view()查看当前状态;
search查找输入的书;
delete删除所选的书;
udpate修改所选的书的信息;
import sqlite3
def connect():
conn = sqlite3.connect("books.db")
cur=conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY,title text, author text, year integer, isbn integer)")
conn.commit()
conn.close()
#插入func
def insert(title,author,year,isbn):
conn = sqlite3.connect("books.db") #connect to database
cur=conn.cursor()
cur.execute("INSERT INTO book VALUES (NULL,?,?,?,?)",(title,author,year,isbn))
conn.commit()
conn.close()
def view():
conn = sqlite3.connect("books.db") #connect to database
cur=conn.cursor()
cur.execute("SELECT * FROM book")
rows=cur.fetchall()
conn.close()
return rows
def search(title="",author="",year="",isbn=""):
conn = sqlite3.connect("books.db") #connect to database
cur=conn.cursor()
cur.execute("SELECT * FROM book WHERE title=? OR author=? OR year=? OR isbn=?",(title,author,year,isbn))
rows=cur.fetchall() #cursor 来fetchall
conn.close()
return rows
#选中一行删除
def delete(id): #得到id,去SQL删除该id
conn = sqlite3.connect("books.db") #connect to database
cur=conn.cursor()
cur.execute("DELETE FROM book WHERE id=?",(id,))
conn.commit()
conn.close()
#选中一行修改
def update(id,title,author,year,isbn):
conn = sqlite3.connect("books.db") #connect to database
cur=conn.cursor()
cur.execute("UPDATE book SET title=?, author=?, year=?, isbn=? WHERE id =?",(title,author,year,isbn,id))
conn.commit()
conn.close()
连接frontend和backend
frontend
(1)view函数+按钮
设置button的command参数;
清空list1list1.delete(0,END)
将backend中的view函数得到的元组数组,按行插入,位置为END;
def view_command():
list1.delete(0,END) #清除list1中所有的东西
for row in backend.view():
list1.insert(END,row) #End表示放在listbox的最后
b1=Button(window,text="View all",width=12,command=view_command) #commanda设置功能 若加(),则编译时就会执行这段代码
b1.grid(row=2,column=3)
(2)查找函数+按钮
类似view,先清空list;
然后按Title_text等所得到的,text框中的信息来查找;
def search_command():
list1.delete(0,END)
for row in backend.search(Title_text.get(),Author_text.get(),Year_text.get(),ISBN_text.get()):
list1.insert(END,row)
b2=Button(window,text="Search entry",width=12,command=search_command) #不能加括号,但是要传入参数
b2.grid(row=3,column=3)
(3)添加函数+按钮
将四个text框中的str对象加入SQL,清空屏幕并且输出刚加入的对象;
def add_command():
backend.insert(Title_text.get(),Author_text.get(),Year_text.get(),ISBN_text.get())
list1.delete(0,END)
list1.insert(END,(Title_text.get(),Author_text.get(),Year_text.get(),ISBN_text.get()))
b3=Button(window,text="Add entry",width=12,command=add_command)
b3.grid(row=4,column=3)
(4)删除函数+按钮
删除函数需要删除鼠标选中的一栏;
得到鼠标选中的一栏:
def get_selected_row(event): #固定的传入
global selected_tuple #全局变量
index = list1.curselection()[0] #得到被选中的那行的行数
selected_tuple=list1.get(index) #get得到地index行
#return selected_tuple #有了全局变量,则无需返回
#在上面显示被选中的内容
list对象的curselection成员函数,可得到鼠标点击的那行的行数tuple,取[0]即为行数;
list1.get(index)得到该行的tuple,并赋值给全局变量selected_tuple:
删除操作
删除函数传入id即可完成删除,调用view来刷新list;
def delete_command():
#backend.delete(get_selected_row()[0]) #无法调用函数,因为无法传入参数
backend.delete(selected_tuple[0])
view_command()
b5=Button(window,text="Delete",width=12,command=delete_command) #需要得到list1被选中的那行
b5.grid(row=6,column=3)
(5)update函数+按钮
update传入的id为鼠标选择所得到的id;
其他为空格输入的名称;
def update_command():
#backend.delete(get_selected_row()[0]) #无法调用函数,因为无法传入参数
backend.update(selected_tuple[0],Title_text.get(),Author_text.get(),Year_text.get(),ISBN_text.get())
view_command()
b4=Button(window,text="Update",width=12,command=update_command)
b4.grid(row=5,column=3)
(6)close按钮
执行window.destroy即可;
b6=Button(window,text="Close",width=12,command=window.destroy) #关闭窗口
b6.grid(row=7,column=3)
Bookstore1.0
"""
A program that stores this book information:
Title, Author
year,ISBN
user can:
View all records
Search an entry
Add entry
Update entry
Delete
Close
"""
from tkinter import *
import backend
def get_selected_row(event): #固定的传入
global selected_tuple #全局变量
index = list1.curselection()[0] #得到被选中的那行的行数
selected_tuple=list1.get(index) #get得到地index行
#return selected_tuple #有了全局变量,则无需返回
#在上面显示被选中的内容
e1.delete(0,END)
e1.insert(END,selected_tuple[1])
e2.delete(0,END)
e2.insert(END,selected_tuple[2])
e3.delete(0,END)
e3.insert(END,selected_tuple[3])
e4.delete(0,END)
e4.insert(END,selected_tuple[4])
def view_command():
list1.delete(0,END) #清除list1中所有的东西
for row in backend.view():
list1.insert(END,row) #End表示放在listbox的最后
def search_command():
list1.delete(0,END)
for row in backend.search(Title_text.get(),Author_text.get(),Year_text.get(),ISBN_text.get()):
list1.insert(END,row)
def add_command():
backend.insert(Title_text.get(),Author_text.get(),Year_text.get(),ISBN_text.get())
list1.delete(0,END)
list1.insert(END,(Title_text.get(),Author_text.get(),Year_text.get(),ISBN_text.get()))
def delete_command():
#backend.delete(get_selected_row()[0]) #无法调用函数,因为无法传入参数
backend.delete(selected_tuple[0])
view_command()
def update_command():
#backend.delete(get_selected_row()[0]) #无法调用函数,因为无法传入参数
backend.update(selected_tuple[0],Title_text.get(),Author_text.get(),Year_text.get(),ISBN_text.get())
view_command()
window=Tk()
window.wm_title("BookStore") #设置名称
l1=Label(window,text="Title")
l1.grid(row=0,column=0)
l2=Label(window,text="Author")
l2.grid(row=0,column=2)
l3=Label(window,text="Year")
l3.grid(row=1,column=0)
l4=Label(window,text="ISBN")
l4.grid(row=1,column=2)
Title_text=StringVar()
e1=Entry(window,textvariable=Title_text)
e1.grid(row=0,column=1)
Author_text=StringVar()
e2=Entry(window,textvariable=Author_text)
e2.grid(row=0,column=3)
Year_text=StringVar()
e3=Entry(window,textvariable=Year_text)
e3.grid(row=1,column=1)
ISBN_text=StringVar()
e4=Entry(window,textvariable=ISBN_text)
e4.grid(row=1,column=3)
list1=Listbox(window,height = 10,width=40)
list1.grid(row=2,column=0,rowspan=6,columnspan=2)
#scrollbar
sb1=Scrollbar(window)
sb1.grid(row=2,column=2,rowspan=6)
list1.configure(yscrollcommand=sb1.set) #list1的y轴受sb1的y轴控制
sb1.configure(command=list1.yview) #sb1的y轴控制list1的y轴
#bind 用于将函数作用于list,函数为get_selected_row
list1.bind("<<ListboxSelect>>",get_selected_row)
#6个按钮
b1=Button(window,text="View all",width=12,command=view_command) #commanda设置功能 若加(),则编译时就会执行这段代码
b1.grid(row=2,column=3)
b2=Button(window,text="Search entry",width=12,command=search_command) #不能加括号,但是要传入参数
b2.grid(row=3,column=3)
b3=Button(window,text="Add entry",width=12,command=add_command)
b3.grid(row=4,column=3)
b4=Button(window,text="Update",width=12,command=update_command)
b4.grid(row=5,column=3)
b5=Button(window,text="Delete",width=12,command=delete_command) #需要得到list1被选中的那行
b5.grid(row=6,column=3)
b6=Button(window,text="Close",width=12,command=window.destroy) #关闭窗口
b6.grid(row=7,column=3)
window.mainloop()
backend(没有变化)
import sqlite3
def connect():
conn = sqlite3.connect("books.db")
cur=conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY,title text, author text, year integer, isbn integer)")
conn.commit()
conn.close()
#插入func
def insert(title,author,year,isbn):
conn = sqlite3.connect("books.db") #connect to database
cur=conn.cursor()
cur.execute("INSERT INTO book VALUES (NULL,?,?,?,?)",(title,author,year,isbn))
conn.commit()
conn.close()
def view():
conn = sqlite3.connect("books.db") #connect to database
cur=conn.cursor()
cur.execute("SELECT * FROM book")
rows=cur.fetchall()
conn.close()
return rows
def search(title="",author="",year="",isbn=""):
conn = sqlite3.connect("books.db") #connect to database
cur=conn.cursor()
cur.execute("SELECT * FROM book WHERE title=? OR author=? OR year=? OR isbn=?",(title,author,year,isbn))
rows=cur.fetchall() #cursor 来fetchall
conn.close()
return rows
#选中一行删除
def delete(id): #得到id,去SQL删除该id
conn = sqlite3.connect("books.db") #connect to database
cur=conn.cursor()
cur.execute("DELETE FROM book WHERE id=?",(id,))
conn.commit()
conn.close()
#选中一行修改
def update(id,title,author,year,isbn):
conn = sqlite3.connect("books.db") #connect to database
cur=conn.cursor()
cur.execute("UPDATE book SET title=?, author=?, year=?, isbn=? WHERE id =?",(title,author,year,isbn,id))
conn.commit()
conn.close()
#connect()
#insert("The Sun","John Smith",1918,9132131)
#delete(3)
#update(2,"The shy","SJ",2020,123213)
# print(view())
#print(search(author="John Smith"))
BUG1:
单击空list会报错
解决1:if判断list是否为空
def get_selected_row(event): #固定的传入
global selected_tuple #全局变量
if not len(list1.curselection()):
return;
解决2:try判断是否出错
def get_selected_row(event): #固定的传入
global selected_tuple #全局变量
try:
index = list1.curselection()[0] #得到被选中的那行的行数
selected_tuple=list1.get(index) #get得到地index行
#return selected_tuple #有了全局变量,则无需返回
#在上面显示被选中的内容
e1.delete(0,END)
e1.insert(END,selected_tuple[1])
e2.delete(0,END)
e2.insert(END,selected_tuple[2])
e3.delete(0,END)
e3.insert(END,selected_tuple[3])
e4.delete(0,END)
e4.insert(END,selected_tuple[4])
except:
pass
创建桌面exe文件
pip install pyinstaller
cmd中运行——创建exe文件
pyinstaller --onefile --windowed frontend.py