本次实验是大二数据库期末大作业，我采用的是python语言和mysql8.0.27制作的基于cs构架的数据库框架，由于之前从未了解过python语言，所以代码部分语言较为粗暴，未成年人请在父母陪同下观看（狗头） ，至于mysql的安装与基本使用，在网络上的教程较多，可自行查找。
本次代码编写，我分成了3个部分，即分别编写登录界面、用户界面和管理员界面的代码，最后在利用一个统一的变量参数来串联起来，而由于代码能力的限制，不是我懒 ，最后仅仅实现了本地使用，至于联机的应用，交给各位自行探索，顺便学会后给我发份代码 。
前言与准备登录界面用户界面
公告书库用户 合并备注 登录界面
登录界面的设计较为简单，或者说整个实验的代码都是同一个道理的堆砌，并没有太多不同。
首先是学会python与mysql的连接，我使用的是pymysql这个模块，之后便在界面对应按钮上提供函数即可，如登录按钮，则在读取数据库后穷举其中的账号密码与输入的账号密码做比较即可，如下：
def auto_login(): # 连接数据库 db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) # 获取操作游标 cur = db.cursor() # 查询数据库 sql = 'select * from user' entry1 = input1.get() entry2 = input2.get() flag = False cur.execute(sql) # 执行查询 results = cur.fetchall() # 获取所有查询数据 global username for row in results: uid = row[3] # 账号 pwd = row[4] # 密码 # 判断输入的账号和密码是否正确 if entry1 == uid and entry2 == pwd: print('登录成功') username = row[0] success(username) flag = True break if flag == False: sql = 'select * from admin' cur.execute(sql) # 执行查询 results = cur.fetchall() # 获取所有查询数据 for row in results: uid = row[0] # 账号 pwd = row[1] # 密码 # 判断输入的账号和密码是否正确 if entry1 == uid and entry2 == pwd: print('登录成功') username = row[0] success(username) flag = True break if flag == False: fail() cur.close() db.commit() db.close()
代码中之所以穷举了两次，是因为我的代码分别考虑了用户和管理员两套不同的账号，但其实原理一样。不要忙着复制上方代码，因为下面有全套的。
from tkinter import *import tkinter.ttkfrom tkinter.messagebox import *from ttkbootstrap import Styleimport pymysqlimport datetimefrom PIL import Image, ImageTkdef success(username): root.destroy() root1 = Tk() root1.withdraw() showinfo('登录成功', username + ',欢迎您') root1.destroy() return Truedef fail(): showerror('登录失败', '用户名或密码错误') return Falsedef auto_login(): # 连接数据库 db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) # 获取操作游标 cur = db.cursor() # 查询数据库 sql = 'select * from user' entry1 = input1.get() entry2 = input2.get() flag = False cur.execute(sql) # 执行查询 results = cur.fetchall() # 获取所有查询数据 global username for row in results: uid = row[3] # 账号 pwd = row[4] # 密码 # 判断输入的账号和密码是否正确 if entry1 == uid and entry2 == pwd: print('登录成功') username = row[0] success(username) flag = True break if flag == False: sql = 'select * from admin' cur.execute(sql) # 执行查询 results = cur.fetchall() # 获取所有查询数据 for row in results: uid = row[0] # 账号 pwd = row[1] # 密码 # 判断输入的账号和密码是否正确 if entry1 == uid and entry2 == pwd: print('登录成功') username = row[0] success(username) flag = True break if flag == False: fail() cur.close() db.commit() db.close()def exit_login(): root.destroy() passdef regist(ip1, ip21, ip22, ip23, ip3, ip4, ip5): if ip1.get() == '' or ip21.get() == '' or ip22.get() == '' or ip23.get() == '' or ip3.get() == '' or ip4.get() == '' or ip5.get() == '': showerror('注册失败', '请填写全部内容') return if ip4.get() != ip5.get(): showerror('注册失败', '密码输入不一致') return if ip22.get() == 1 or ip22.get() == 3 or ip22.get() == 5 or ip22.get() == 7 or ip22.get() == 8 or ip22.get() == 10 or ip22.get() == 12: if ip23.get() < 1 or ip23.get() > 31: showerror('注册失败', '日期有误') if ip22.get() == 4 or ip22.get() == 6 or ip22.get() == 9 or ip22.get() == 11: if ip23.get() < 1 or ip23.get() > 30: showerror('注册失败', '日期有误') if ip22.get() == 2: if ip23.get() < 1: showerror('注册失败', '日期有误') if ip21.get() % 4 == 0 and ip23.get() > 29: showerror('注册失败', '日期有误') if ip21.get() % 4 != 0 and ip23.get() > 28: showerror('注册失败', '日期有误') today = datetime.datetime.today() if today.year - int(ip21.get()) > 100: showerror('注册失败', '日期有误') if ip3.get() != '男' and ip3.get() != '女': showerror('注册失败', '性别有误') bir = datetime.datetime.date( datetime.datetime.strptime(ip21.get() + '-' + ip22.get() + '-' + ip23.get(), '%Y-%m-%d')) db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from user' cur.execute(sql) results = cur.fetchall() i = 0 for row in results: i = i + 1 if row[0] == ip1.get() and row[1] == ip3.get() and row[2] == bir: showerror('注册失败', '该账号已存在') return id = str(int(results[-1][3]) + 1) sql = "insert into user values (%s, %s, %s, %s, %s,%s,%s)" data = (ip1.get(), ip3.get(), bir, id, ip4.get(),None,None) cur.execute(sql, data) showinfo('注册成功', ip1.get() + ',n您的账号为' + id) cur.close() db.commit() db.close()def regist_login(): root1 = Toplevel(root) root1.title('用户注册') root1.geometry('400x400+500+300') root1.iconbitmap('start.ico') Label(root1, text='欢迎注册本系统').place(relx=0.2, rely=0.04) Label(root1, text='姓名:').place(relx=0.05, rely=0.15) Label(root1, text='出生日期:').place(relx=0.05, rely=0.3) Label(root1, text='年').place(relx=0.2, rely=0.36) Label(root1, text='月').place(relx=0.5, rely=0.36) Label(root1, text='日').place(relx=0.8, rely=0.36) Label(root1, text='性别:').place(relx=0.05, rely=0.45) Label(root1, text='请输入密码:').place(relx=0.05, rely=0.6) Label(root1, text='请再次输入密码:').place(relx=0.05, rely=0.75) v1 = StringVar() v21 = StringVar() v22 = StringVar() v23 = StringVar() v3 = StringVar() v4 = StringVar() v5 = StringVar() input1 = Entry(root1, textvariable=v1) input1.place(relx=0.05, rely=0.21) input21 = Entry(root1, textvariable=v21) input21.place(relx=0.05, rely=0.36, width=55) input22 = Entry(root1, textvariable=v22) input22.place(relx=0.3, rely=0.36, width=55) input23 = Entry(root1, textvariable=v23) input23.place(relx=0.55, rely=0.36, width=55) input3 = tkinter.ttk.Combobox(root1, textvariable=v3, values=['男', '女']) input3.place(relx=0.05, rely=0.51, relwidth=0.2, relheight=0.08) input3.current(0) input4 = Entry(root1, textvariable=v4, show='*') input4.place(relx=0.05, rely=0.66) input5 = Entry(root1, textvariable=v5, show='*') input5.place(relx=0.05, rely=0.81) Button(root1, text='注册', width=10, command=lambda: regist(input1, input21, input22, input23, input3, input4, input5)).place(relx=0.1, rely=0.9) Button(root1, text='返回', width=10, command=root1.destroy).place(relx=0.5, rely=0.9)def pwd(ip1, ip21, ip22, ip23, ip3, ip4): bir = datetime.datetime.date( datetime.datetime.strptime(ip21.get() + '-' + ip22.get() + '-' + ip23.get(), '%Y-%m-%d')) db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from user' cur.execute(sql) results = cur.fetchall() flag = False for row in results: if row[0] == ip1.get() and row[1] == ip3.get() and row[2] == bir and row[3] == ip4.get(): flag = True showinfo('成功找到', '您的密码为:' + row[4]) root2.destroy() break if flag == False: showerror('找回失败', '请核实信息') cur.close() db.commit() db.close()def pwd_login(): global root2 root2 = Toplevel(root) root2.title('找回密码') root2.geometry('400x400+500+300') root2.iconbitmap('start.ico') Label(root2, text='姓名:').place(relx=0.05, rely=0.15) Label(root2, text='出生日期:').place(relx=0.05, rely=0.3) Label(root2, text='年').place(relx=0.2, rely=0.36) Label(root2, text='月').place(relx=0.5, rely=0.36) Label(root2, text='日').place(relx=0.8, rely=0.36) Label(root2, text='性别:').place(relx=0.05, rely=0.45) Label(root2, text='id:').place(relx=0.05, rely=0.6) v1 = StringVar() v21 = StringVar() v22 = StringVar() v23 = StringVar() v3 = StringVar() v4 = StringVar() input1 = Entry(root2, textvariable=v1) input1.place(relx=0.05, rely=0.21) input21 = Entry(root2, textvariable=v21) input21.place(relx=0.05, rely=0.36, width=55) input22 = Entry(root2, textvariable=v22) input22.place(relx=0.3, rely=0.36, width=55) input23 = Entry(root2, textvariable=v23) input23.place(relx=0.55, rely=0.36, width=55) input3 = tkinter.ttk.Combobox(root2, textvariable=v3, values=['男', '女']) input3.place(relx=0.05, rely=0.51, relwidth=0.2, relheight=0.08) input3.current(0) input4 = Entry(root2, textvariable=v4, ) input4.place(relx=0.05, rely=0.66) Button(root2, text='确定', width=10, command=lambda: pwd(input1, input21, input22, input23, input3, input4)).place(relx=0.1, rely=0.9) Button(root2, text='返回', width=10, command=root2.destroy).place(relx=0.5, rely=0.9)def frame(): global root style = Style(theme="yeti") root = style.master root.title('登录窗口') root.geometry('960x640+420+200') root.resizable(0, 0) root.iconbitmap('start.ico') v1 = StringVar() v2 = StringVar() img = Image.open('登录.jpg') img = img.resize((960, 640)) # 修改图片大小 photo = ImageTk.PhotoImage(img) Label(root, image=photo, compound=CENTER).pack() # 账号标签，位置在第0行第0列 Label(root, text='账号:', bg='lightskyblue', fg='white', font=(16)).place(relx=0.35, rely=0.3) # 密码标签，位置在第1行第0列 Label(root, text='密码:', bg='lightskyblue', fg='white', font=(16)).place(relx=0.35, rely=0.4) # 账号输入框 global input1 input1 = Entry(root, textvariable=v1, highlightthickness=1, highlightcolor='lightskyblue', relief='groove') input1.place(relx=0.42, rely=0.31) # 密码输入框 global input2 input2 = Entry(root, textvariable=v2, highlightthickness=1, highlightcolor='lightskyblue', relief='groove', show='*') input2.place(relx=0.42, rely=0.41) # 登录按钮 Button(root, text='登录', width=10, command=auto_login).place(relx=0.35, rely=0.5) # 退出按钮 Button(root, text='退出', width=10, command=exit_login).place(relx=0.55, rely=0.5) # 注册按钮 Button(root, text='注册', width=10, command=regist_login).place(relx=0.35, rely=0.65) # 找回密码 Button(root, text='找回密码', width=10, command=pwd_login).place(relx=0.55, rely=0.65) root.mainloop()if __name__ == '__main__': frame()
上方为登录界面的代码，每个函数的作用分别在按钮处名字体现，其中需要注意的是在输入日期时要考虑到mysql的日期格式问题，因此在注册处的日期，包括之后的借书日期等都有格式转化的过程。
如果你认真分析了上面的代码，并认为做到这个地步已经够了或不够的兄弟们，可以关闭该文章了。因为剩下两部分的代码本质上只是在重复这个过程，只是将图片、按钮进行更改，你可以自己设计了，而不满足与此的兄弟，我也帮不了你，你可以不要浪费时间了。
以下内容均为水字数
用户界面整体上我分为3个部分，分别是公告、书库、用户，这3部分的实现是利用ttk.Notebook实现的，具体的用法可以自行查阅。
公告 本部分是利用了mysql存储长文本的能力实现的，利用tkinter的text在管理员处存入文本，再在数据库中读出利用text显示在用户处（增加不可修改的状态），下方为图片，代码再用户界面最后。
书库中用到最重要的一个功能是tkinter中的tree，它可以在界面中增加一个表格，具体功能请自行查阅。书库所实现的两大部分之一查书，就是利用不同条件查询的书籍显示在表格中。其中在搜书过程中，实现了一次搜书与多次搜书，所谓多次搜书，是利用上一次搜书的结果进行再次搜索。一次与多次搜索均为模糊搜索，即支持关键字乃至间隔字（如输入“三义”出现三国演义）。一次搜索是利用了数据库查询的like与%，而多次搜索则是将当前表格中的书籍与搜索内容比较，python中的fuzzywuzzy模块实现。
另外一大部分是借书，我利用鼠标绑定，在鼠标按键中获取书名，并根据其是否已被借阅、是否丢失判断是否可借，可借则在借阅数据库中增加数据，如获取电脑时间确定结束时间，增加30天作为应还书时间。
用户界面在左侧边栏设置按钮指向不同的界面，其本质是右侧的标签是否显示，利用pack进行控制，在点击某一按钮时，就将其对应标签设置为可见，其他隐藏即可。特附代码：
def changeTag(user, tag=0): frame0.pack_forget() frame2.pack_forget() if tag == 0: frame0.pack(padx=1, pady=1, ipadx=500, ipady=500) elif tag == 2: frame2.pack(padx=1, pady=1, ipadx=500, ipady=500) x = tree2.get_children() for item in x: tree2.delete(item) jie_book(user)
其他的部分只需将自己的想法写出即可。如还书部分可增加异常还书，选择书籍丢失或损坏后赔偿相应的损失，在例如根据其还书时间与设置的应还书时间判断是否违约，违约还可设置对应标志来减少其可借数量（我未实现，懒）。
下方为用户部分全代码（当时懒得写注释，尽力观看）
from tkinter import *from tkinter import ttkfrom tkinter.messagebox import *import tkinter.ttkfrom ttkbootstrap import Styleimport pymysqlfrom fuzzywuzzy import fuzzfrom PIL import Image, ImageTkimport datetimedef text_1(): text1.tag_config("tag_1", foreground="blue", font=(20)) text1.tag_config("tag_2", foreground="blue", font=(12)) db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from texts' cur.execute(sql) data = cur.fetchall() if data == (): text1.insert(INSERT, '暂无新公告', 'tag_1') else: text1.insert(INSERT, data[-1][1], 'tag_2') text1.insert(INSERT, 'n') text1.insert(INSERT, data[-1][0], 'tag_1') cur.close() db.close()def text_3(): text3.tag_config("tag_1", foreground="blue", font=(20)) text3.tag_config("tag_2", foreground="blue", font=(12)) db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from recommend' cur.execute(sql) data = cur.fetchall() if data == (): text3.insert(INSERT, '暂无新公告', 'tag_1') else: text3.insert(INSERT, data[-1][1], 'tag_2') text3.insert(INSERT, 'n') text3.insert(INSERT, data[-1][0], 'tag_1') cur.close() db.close()def find(t): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() if var.get() == '书名': sql = """ select * from book where bname like %s """ t2 = '' for i in range(len(t.get())): t2 = t2 + t.get()[i] + '%' params = '%' + t2 if cur.execute(sql, params) == 0: showerror('查询失败', '请核实您输入的内容') else: x = tree.get_children() for item in x: tree.delete(item) result = cur.fetchall() for i in result: tree.insert('', 'end', values=i) if var.get() == 'ISBN': sql = """ select * from book where ISBN like %s """ t2 = '' for i in range(len(t.get())): t2 = t2 + t.get()[i] + '%' params = '%' + t2 if cur.execute(sql, params) == 0: showerror('查询失败', '请核实您输入的内容') else: x = tree.get_children() for item in x: tree.delete(item) result = cur.fetchall() for i in result: tree.insert('', 'end', values=i) if var.get() == '作者': sql = """ select * from book where author like %s """ t2 = '' for i in range(len(t.get())): t2 = t2 + t.get()[i] + '%' params = '%' + t2 if cur.execute(sql, params) == 0: showerror('查询失败', '请核实您输入的内容') else: x = tree.get_children() for item in x: tree.delete(item) result = cur.fetchall() for i in result: tree.insert('', 'end', values=i) if var.get() == '出版社': sql = """ select * from book where publisher like %s """ t2 = '' for i in range(len(t.get())): t2 = t2 + t.get()[i] + '%' params = '%' + t2 if cur.execute(sql, params) == 0: showerror('查询失败', '请核实您输入的内容') else: x = tree.get_children() for item in x: tree.delete(item) result = cur.fetchall() for i in result: tree.insert('', 'end', values=i) if var.get() == '分类': sql = """ select * from book where sort like %s """ t2 = '' for i in range(len(t.get())): t2 = t2 + t.get()[i] + '%' params = '%' + t2 if cur.execute(sql, params) == 0: showerror('查询失败', '请核实您输入的内容') else: x = tree.get_children() for item in x: tree.delete(item) result = cur.fetchall() for i in result: tree.insert('', 'end', values=i) cur.close() db.close()def find2(t): x = tree.get_children() if x == (): showerror('查询失败', '请输入前置条件') else: if var.get() == '书名': for i in x: if fuzz.ratio(tree.item(i, 'values')[0], t.get()) == 0: tree.delete(i) if var.get() == 'ISBN': for i in x: if fuzz.ratio(tree.item(i, 'values')[1], t.get()) == 0: tree.delete(i) if var.get() == '作者': for i in x: if fuzz.ratio(tree.item(i, 'values')[2], t.get()) == 0: tree.delete(i) if var.get() == '出版社': for i in x: if fuzz.ratio(tree.item(i, 'values')[3], t.get()) == 0: tree.delete(i) if var.get() == '分类': for i in x: if fuzz.ratio(tree.item(i, 'values')[4], t.get()) == 0: tree.delete(i)def Empty(): t1.delete(0, END) x = tree.get_children() for item in x: tree.delete(item)def All(): x = tree.get_children() for item in x: tree.delete(item) db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from book' cur.execute(sql) result = cur.fetchall() x = tree.get_children() for i in result: tree.insert('', 'end', values=i) cur.close() db.close()def changeTag(user, tag=0): frame0.pack_forget() frame2.pack_forget() if tag == 0: frame0.pack(padx=1, pady=1, ipadx=500, ipady=500) elif tag == 2: frame2.pack(padx=1, pady=1, ipadx=500, ipady=500) x = tree2.get_children() for item in x: tree2.delete(item) jie_book(user)def setpwd2(i1, i2, i3, user): if i2.get() != i3.get(): showerror('修改失败', '两次输入密码应当一致') elif i1.get() == i2.get(): showerror('修改失败', '密码未修改') else: db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from user where name=%s' cur.execute(sql, user) result = cur.fetchall() if i1.get() != result[0][4]: showerror('修改失败', '请核实原密码') else: sql1 = 'update user set password=%s where name=%s' data = (i2.get(), user) cur.execute(sql1, data) rpwd.destroy() showinfo('修改成功', '密码已修改') cur.close() db.commit() db.close()def setpwd(user): global rpwd rpwd = Toplevel(root) rpwd.title('修改密码') rpwd.geometry('300x200+500+300') rpwd.wm_attributes('-topmost', 1) rpwd.iconbitmap('start.ico') v1 = StringVar() v2 = StringVar() v3 = StringVar() Label(rpwd, text='原密码：').place(relx=0.09, rely=0.1) input1 = Entry(rpwd, textvariable=v1, show='*') input1.place(relx=0.3, rely=0.1) Label(rpwd, text='新密码：').place(relx=0.09, rely=0.35) input2 = Entry(rpwd, textvariable=v2, show='*') input2.place(relx=0.3, rely=0.35) Label(rpwd, text='确认密码：').place(relx=0.04, rely=0.6) input3 = Entry(rpwd, textvariable=v3, show='*') input3.place(relx=0.3, rely=0.6) Button(rpwd, text='确定', width=10, command=lambda: setpwd2(input1, input2, input3, user)).place(relx=0.1, rely=0.8) Button(rpwd, text='取消', width=10, command=rpwd.destroy).place(relx=0.5, rely=0.8)def setxin2(i1, i2, user): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from user' cur.execute(sql) data = cur.fetchall() flag = True flag = True for i in data: if i[5] == i1.get(): showerror('警告', '手机号已被绑定') flag = False if i[6] == i2.get(): showerror('警告', '电子邮箱已被绑定') flag = False if flag == True: if i1.get() != '': sql = 'update user set tel=%s where name=%s' data = (i1.get(), user) cur.execute(sql, data) if i2.get() != '': sql = 'update user set email=%s where name=%s' data = (i2.get(), user) cur.execute(sql, data) if i1.get() != '' or i2.get() != '': showinfo('绑定成功', '感谢您的参与') cur.close() db.commit() db.close()def setxin(user): global xpwd xpwd = Toplevel(root) xpwd.title('完善信息') xpwd.geometry('300x200+500+300') xpwd.wm_attributes('-topmost', 1) xpwd.iconbitmap('start.ico') v1 = StringVar() v2 = StringVar() Label(xpwd, text='手机号：').place(relx=0.09, rely=0.1) input1 = Entry(xpwd, textvariable=v1) input1.place(relx=0.3, rely=0.1) Label(xpwd, text='邮箱地址：').place(relx=0.04, rely=0.35) input2 = Entry(xpwd, textvariable=v2) input2.place(relx=0.3, rely=0.35) db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from user where name=%s' cur.execute(sql, user) data = cur.fetchall() if data[0][5] != None and data[0][6] != None: Label(xpwd, text='您已完善信息：100%', font=(20), fg='green').place(relx=0.1, rely=0.53) elif data[0][5] == None and data[0][6] == None: Label(xpwd, text='您已完善信息：0%', font=(20), fg='green').place(relx=0.1, rely=0.53) else: Label(xpwd, text='您已完善信息：50%', font=(20), fg='green').place(relx=0.1, rely=0.53) cur.close() db.commit() db.close() Button(xpwd, text='确定', width=10, command=lambda: setxin2(input1, input2, user)).place(relx=0.1, rely=0.8) Button(xpwd, text='取消', width=10, command=xpwd.destroy).place(relx=0.5, rely=0.8)def yijie(user): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select count(*) from utob where id in (select id from user where name=%s)' cur.execute(sql, user) result = cur.fetchall() cur.close() db.close() return result[0][0]def treeviewClick(event): global tojie0 global tojie1 global tojie2 tojie0 = StringVar() tojie1 = StringVar() tojie2 = StringVar() if not tree.selection(): return item = tree.selection()[0] tojie0.set(tree.item(item, 'values')[0]) tojie1.set(tree.item(item, 'values')[1]) tojie2.set(tree.item(item, 'values')[5])def jie(user): if tojie2.get() == '已借出': showerror('借阅失败', '该书已被借阅') return db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql1 = 'select state from book where isbn=%s' cur.execute(sql1, tojie1.get()) result = cur.fetchall() state = result[0][0] flag=1 if state == '丢失': showinfo('警告', '抱歉，此书暂时丢失') return if state == '损坏': flag=0 answer2 = tkinter.messagebox.askokcancel('提示', '此书有所损坏，n是否继续借阅') if not answer2: return else: flag=1 answer = tkinter.messagebox.askokcancel('提示', '您是否确认借阅《%s》' % tojie0.get()) # 均可为空 if answer and flag==1: db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select id from user where name=%s' cur.execute(sql, user) data = cur.fetchall() id = data[0][0] today = datetime.datetime.today() start = today.date() end = today.date() + datetime.timedelta(days=30) sql = 'insert into utob values (%s,%s,%s,%s,%s)' text = (tojie1.get(), id, start, end, 0) cur.execute(sql, text) showinfo('提示', '借阅成功') sql = 'update book set note=%s where isbn=%s' text = ('已借出', tojie1.get()) cur.execute(sql, text) cur.close() db.commit() db.close() Empty() All() text_jie.set('目前已借：%d' % yijie(user)) else: returndef sex(user): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select sex from user where name=%s' cur.execute(sql, user) data = cur.fetchall() return data[0][0]def age(user): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select birthday from user where name=%s' cur.execute(sql, user) data = cur.fetchall() today = datetime.datetime.today() return today.year - int(str(data[0][0])[0:4])def jie_book(user): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from utob where id in (select id from user where name=%s)' cur.execute(sql, user) result = cur.fetchall() sql2 = 'select bname from book where isbn in (select isbn from utob where id in (select id from user where name=%s))' cur.execute(sql2, user) book = cur.fetchall() x = tree2.get_children() number = 0 for i in result: if datetime.datetime.date(datetime.datetime.today()).__ge__(i[3]): tree2.insert('', 'end', values=(book[number], i[2], i[3], '已过期')) number = number + 1 else: tree2.insert('', 'end', values=(book[number], i[2], i[3], '未过期')) number = number + 1 cur.close() db.close()def tree2viewClick(event): global togui1 global togui2 global togui3 togui1 = StringVar() togui2 = StringVar() togui3 = StringVar() if not tree2.selection(): return item = tree2.selection()[0] togui1.set(tree2.item(item, 'values')[0]) togui2.set(tree2.item(item, 'values')[2]) togui3.set(tree2.item(item, 'values')[3])def gui2(user): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select isbn from book where bname=%s' cur.execute(sql, togui1.get()) gui_isbn = cur.fetchall() sql2 = 'delete from utob where isbn=%s' cur.execute(sql2, gui_isbn) sql3 = "update book set note='未借阅' where bname=%s" cur.execute(sql3, togui1.get()) cur.close() db.commit() db.close() showinfo('提示', '《%s》已归还成功' % togui1.get()) x = tree2.get_children() for item in x: tree2.delete(item) jie_book(user) text_jie.set('目前已借：%d' % yijie(user))def gui(user): answer = tkinter.messagebox.askokcancel('提示', '您是否确认归还n《%s》' % togui1.get()) # 均可为空 if answer: if togui3.get() == '已过期': showwarning('警告', '《%s》已超过借书期限，须额外付款' % togui1.get()) win = Toplevel(root) win.geometry('300x300+500+300') win.title('过期支付') win.iconbitmap('start.ico') global img1, photo1, b1 img1 = Image.open('支付.jpg') img1 = img1.resize((150, 150)) # 修改图片大小 photo1 = ImageTk.PhotoImage(img1) Label(win, image=photo1).pack() b1 = Button(win, text='支付完成', width=10, command=lambda: gui2(user)) b1.pack() else: gui2(user) else: returndef xu(user): if togui1.get() == '': return answer = tkinter.messagebox.askokcancel('提示', '续借次数仅有一次，您确定使用吗？') # 均可为空 if answer: db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select isbn from book where bname=%s' cur.execute(sql, togui1.get()) gui_isbn = cur.fetchall() sql1 = 'select times from utob where isbn=%s' cur.execute(sql1, gui_isbn) res = cur.fetchall() if res[0][0] != 0: showwarning('警告', '您的续借次数已用光，n如有需求请联系管理员') cur.close() db.commit() db.close() return sql2 = 'select etime from utob where isbn=%s' cur.execute(sql2, gui_isbn) data = cur.fetchall() etime_new = data[0][0] + + datetime.timedelta(days=30) sql3 = "update utob set etime=%s where isbn=%s" values = (etime_new, gui_isbn) cur.execute(sql3, values) sql4 = 'update utob set times=times+1 where isbn=%s' cur.execute(sql4, gui_isbn) showinfo('续借成功', '您的截止日期已推迟30天') cur.close() db.commit() db.close() x = tree2.get_children() for item in x: tree2.delete(item) jie_book(user)def yi_gui3(user): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select isbn from book where bname=%s' cur.execute(sql, togui1.get()) gui_isbn = cur.fetchall() sql2 = 'update book set state=%s where isbn=%s' value = (v.get(), gui_isbn) cur.execute(sql2, value) cur.close() db.commit() db.close() gui2(user) text_jie.set('目前已借：%d' % yijie(user))def yi_gui2(user): win2.destroy() win = Toplevel(root) win.geometry('400x400+500+300') win.title('异常赔偿') win.iconbitmap('start.ico') global img1, photo1, b1 img1 = Image.open('支付.jpg') img1 = img1.resize((150, 150)) # 修改图片大小 photo1 = ImageTk.PhotoImage(img1) Label(win, image=photo1).pack() b1 = Button(win, text='支付完成', width=10, command=lambda: yi_gui3(user)) b1.pack()def yi_gui(user): global win2, v, c, b2 win2 = Toplevel(root) win2.title('异常还书') win2.iconbitmap('start.ico') win2.geometry('200x160+500+500') win2.wm_attributes('-topmost', 1) v = StringVar() c = tkinter.ttk.Combobox(win2, textvariable=v, values=['丢失', '损坏']) c.pack() c.current(0) b2 = Button(win2, text='确定', width=10, command=lambda: yi_gui2(user)) b2.place(relx=0.28, rely=0.5) showinfo('提示', '感谢您的诚实，n请选择情况')def mymain(user): global root style = Style(theme="yeti") root = style.master root.title('图书管理系统') root.geometry('900x600+500+300') root.iconbitmap('start.ico') tabControl = ttk.Notebook(root) tab1 = ttk.frame(tabControl) tab2 = ttk.frame(tabControl) tab3 = ttk.frame(tabControl) tabControl.add(tab1, text='首页') tabControl.add(tab2, text='书库') tabControl.add(tab3, text='我的') tabControl.pack(expand=1, fill='both', padx=5) labelframe01 = Labelframe(tab1, text="公告", font=('宋体', 16), fg='red') labelframe01.pack(padx=2, pady=5, ipadx=240, ipady=22, anchor='nw') global text1 text1 = Text(labelframe01, height=9, width=60) text1.pack(anchor='nw') text_1() text1.config(state='disabled') labelframe02 = Labelframe(tab1, text="推荐", font=('宋体', 16), fg='red') labelframe02.pack(padx=2, pady=5, ipadx=240, ipady=25, anchor='nw') # img2 = Image.open('.jpg') # img2 = img2.resize((300, 300)) # 修改图片大小 # photo2 = ImageTk.PhotoImage(img2) # Label(labelframe02, image=photo2, compound=CENTER).pack(side=RIGHT) global text3 text3 = Text(labelframe02, height=10, width=60) text3.pack(anchor='nw') text_3() text3.config(state='disabled') img1 = Image.open('书库.jpg') img1 = img1.resize((900, 600)) # 修改图片大小 photo1 = ImageTk.PhotoImage(img1) Label(tab2, image=photo1, compound=CENTER).pack() global var var = StringVar() comb = tkinter.ttk.Combobox(tab2, textvariable=var, values=['书名', 'ISBN', '作者', '出版社', '分类']) comb.place(relx=0.08, rely=0.05, relwidth=0.2) comb.current(0) var1 = StringVar() global t1 t1 = Entry(tab2, textvariable=var1) t1.place(relx=0.29, rely=0.05, relheight=0.06) Button(tab2, text='搜索', width=10, command=lambda: find(t1)).place(relx=0.55, rely=0.06) Button(tab2, text='再次搜索', width=10, command=lambda: find2(t1)).place(relx=0.67, rely=0.06) Button(tab2, text='清空', width=10, command=Empty).place(relx=0.55, rely=0.15) Button(tab2, text='显示全部', width=10, command=All).place(relx=0.67, rely=0.15) global tree tree = ttk.Treeview(tab2, height=18, show="headings") # 表格 tree["columns"] = ('书名', 'ISBN', '作者', '出版社', '分类', '状态') tree.column("书名", width=148, anchor='center') # 表示列,不显示 tree.column("ISBN", width=148, anchor='center') tree.column("作者", width=148, anchor='center') tree.column("出版社", width=148, anchor='center') tree.column("分类", width=148, anchor='center') tree.column("状态", width=148, anchor='center') tree.heading("书名", text='书名') # 表示列,不显示 tree.heading("ISBN", text='ISBN') tree.heading("作者", text='作者') tree.heading("出版社", text='出版社') tree.heading("分类", text='分类') tree.heading("状态", text='状态') tree.place(rely=0.3) tree.bind('
此外，在管理员界面，管理员也可以为借阅信息进行无限期续期，导出借阅信息和用户信息，终极管理员，也就是我，还可以实现管理员的增加和删除（我有个大胆的想法，管理员可以设置分级，各级管理员可以对应设置低一级管理员的账号，若真按此操作，删除管理员时，请注意其下属管理员的管理）
下为全部代码，同样谨慎观看：
from tkinter import *from tkinter import ttkfrom tkinter.messagebox import *from tkinter.filedialog import *import tkinter.ttkfrom ttkbootstrap import Styleimport pymysqlfrom fuzzywuzzy import fuzzfrom PIL import Image, ImageTkimport datetimeimport tkinter.filedialogimport in_excel_book as in_bimport out_excel_book as out_bimport in_excel_user as in_uimport out_excel_user as out_udef outtext1(): text2.config(state='normal') text2.tag_config("tag_1", foreground="blue", font=(20)) text2.tag_config("tag_2", foreground="blue", font=(12)) db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from texts' cur.execute(sql) data = cur.fetchall() if data == (): text2.insert(INSERT, '暂无新公告', 'tag_1') else: text2.insert(INSERT, data[-1][1], 'tag_2') text2.insert(INSERT, 'n') text2.insert(INSERT, data[-1][0], 'tag_1') cur.close() db.close() text2.config(state='disabled')def intext1(): if text1.get("0.0", "end") == 'n': return text2.config(state='normal') db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'insert into texts values (%s,%s)' today = datetime.datetime.today() values = (text1.get("0.0", "end"), today) cur.execute(sql, values) cur.close() db.commit() db.close() showinfo('提示', '公告已成功发布') text2.delete('1.0', 'end') outtext1() text2.config(state='disabled')def deltext1(): text2.config(state='normal') db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from texts order by time desc limit 0,1' # SQL语句 cur.execute(sql) data = cur.fetchall() sql = 'delete from texts where word=%s' cur.execute(sql, data[0][0]) cur.close() db.commit() db.close() showinfo('提示', '公告已删除') text2.delete('1.0', 'end') outtext1() text2.config(state='disabled')def outtext3(): text4.config(state='normal') text4.tag_config("tag_1", foreground="blue", font=(20)) text4.tag_config("tag_2", foreground="blue", font=(12)) db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from recommend' cur.execute(sql) data = cur.fetchall() if data == (): text4.insert(INSERT, '暂无新推荐', 'tag_1') else: text4.insert(INSERT, data[-1][1], 'tag_2') text4.insert(INSERT, 'n') text4.insert(INSERT, data[-1][0], 'tag_1') cur.close() db.close() text4.config(state='disabled')def intext3(): if text3.get("0.0", "end") == 'n': return text4.config(state='normal') db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'insert into recommend values (%s,%s)' today = datetime.datetime.today() values = (text3.get("0.0", "end"), today) cur.execute(sql, values) cur.close() db.commit() db.close() showinfo('提示', '推荐已成功发布') text4.delete('1.0', 'end') outtext3() text4.config(state='disabled')def deltext3(): text4.config(state='normal') db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from recommend order by time desc limit 0,1' # SQL语句 cur.execute(sql) data = cur.fetchall() sql = 'delete from recommend where word=%s' cur.execute(sql, data[0][0]) cur.close() db.commit() db.close() showinfo('提示', '推荐已删除') text4.delete('1.0', 'end') outtext3() text4.config(state='disabled')def find(t): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() if var.get() == '书名': sql = """ select * from book where bname like %s """ t2 = '' for i in range(len(t.get())): t2 = t2 + t.get()[i] + '%' params = '%' + t2 if cur.execute(sql, params) == 0: showerror('查询失败', '请核实您输入的内容') else: x = tree.get_children() for item in x: tree.delete(item) result = cur.fetchall() for i in result: tree.insert('', 'end', values=i) if var.get() == 'ISBN': sql = """ select * from book where ISBN like %s """ t2 = '' for i in range(len(t.get())): t2 = t2 + t.get()[i] + '%' params = '%' + t2 if cur.execute(sql, params) == 0: showerror('查询失败', '请核实您输入的内容') else: x = tree.get_children() for item in x: tree.delete(item) result = cur.fetchall() for i in result: tree.insert('', 'end', values=i) if var.get() == '作者': sql = """ select * from book where author like %s """ t2 = '' for i in range(len(t.get())): t2 = t2 + t.get()[i] + '%' params = '%' + t2 if cur.execute(sql, params) == 0: showerror('查询失败', '请核实您输入的内容') else: x = tree.get_children() for item in x: tree.delete(item) result = cur.fetchall() for i in result: tree.insert('', 'end', values=i) if var.get() == '出版社': sql = """ select * from book where publisher like %s """ t2 = '' for i in range(len(t.get())): t2 = t2 + t.get()[i] + '%' params = '%' + t2 if cur.execute(sql, params) == 0: showerror('查询失败', '请核实您输入的内容') else: x = tree.get_children() for item in x: tree.delete(item) result = cur.fetchall() for i in result: tree.insert('', 'end', values=i) if var.get() == '分类': sql = """ select * from book where sort like %s """ t2 = '' for i in range(len(t.get())): t2 = t2 + t.get()[i] + '%' params = '%' + t2 if cur.execute(sql, params) == 0: showerror('查询失败', '请核实您输入的内容') else: x = tree.get_children() for item in x: tree.delete(item) result = cur.fetchall() for i in result: tree.insert('', 'end', values=i) cur.close() db.close()def find2(t): x = tree.get_children() if x == (): showerror('查询失败', '请输入前置条件') else: if var.get() == '书名': for i in x: if fuzz.ratio(tree.item(i, 'values')[0], t.get()) == 0: tree.delete(i) if var.get() == 'ISBN': for i in x: if fuzz.ratio(tree.item(i, 'values')[1], t.get()) == 0: tree.delete(i) if var.get() == '作者': for i in x: if fuzz.ratio(tree.item(i, 'values')[2], t.get()) == 0: tree.delete(i) if var.get() == '出版社': for i in x: if fuzz.ratio(tree.item(i, 'values')[3], t.get()) == 0: tree.delete(i) if var.get() == '分类': for i in x: if fuzz.ratio(tree.item(i, 'values')[4], t.get()) == 0: tree.delete(i)def Empty(): t1.delete(0, END) x = tree.get_children() for item in x: tree.delete(item)def All(): x = tree.get_children() for item in x: tree.delete(item) db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from book' cur.execute(sql) result = cur.fetchall() x = tree.get_children() for i in result: tree.insert('', 'end', values=i) cur.close() db.close()def earse(): if tojie2.get() == '已借出': showerror('警告', '《%s》已被借出，n请勿删除' % tojie0.get()) return answer = tkinter.messagebox.askokcancel('提示', '确认删除《%s》吗？' % tojie0.get()) if answer: db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'delete from book where bname=%s' cur.execute(sql, tojie0.get()) cur.close() db.commit() db.close() showinfo('删除成功', '《%s》已被删除' % tojie0.get()) Empty() All() else: returndef modif2(i1, i2, i3, i4, i5, i6): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() if not i1.get() == '': sql = 'update book set bname=%s where bname=%s' values = (i1.get(), tojie0.get()) cur.execute(sql, values) if not i2.get() == '': sql = 'update book set isbn=%s where bname=%s' values = (i2.get(), tojie0.get()) cur.execute(sql, values) if not i3.get() == '': sql = 'update book set author=%s where bname=%s' values = (i3.get(), tojie0.get()) cur.execute(sql, values) if not i4.get() == '': sql = 'update book set publisher=%s where bname=%s' values = (i4.get(), tojie0.get()) cur.execute(sql, values) if not i5.get() == '': sql = 'update book set sort=%s where bname=%s' values = (i5.get(), tojie0.get()) cur.execute(sql, values) if not i6.get() == '': if i6.get() == '完好': sql = 'update book set state=%s where bname=%s' values = (None, tojie0.get()) cur.execute(sql, values) else: sql = 'update book set state=%s where bname=%s' values = (i6.get(), tojie0.get()) cur.execute(sql, values) cur.close() db.commit() db.close() showinfo('提示', '书籍信息已修改成功') Empty() All()def modif(): if tojie0.get() == '': return winNew = Toplevel(root) winNew.geometry('280x420+500+300') winNew.title('修改书籍') winNew.iconbitmap('start.ico') Label(winNew, text='书名:').place(relx=0.05, rely=0.1) Label(winNew, text='isbn:').place(relx=0.05, rely=0.23) Label(winNew, text='作者:').place(relx=0.05, rely=0.36) Label(winNew, text='出版社:').place(relx=0, rely=0.49) Label(winNew, text='种类:').place(relx=0.05, rely=0.62) Label(winNew, text='状态:').place(relx=0.05, rely=0.75) v1 = StringVar() v2 = StringVar() v3 = StringVar() v4 = StringVar() v5 = StringVar() v6 = StringVar() input1 = Entry(winNew, textvariable=v1) input1.place(relx=0.2, rely=0.1) input2 = Entry(winNew, textvariable=v2) input2.place(relx=0.2, rely=0.23) input3 = Entry(winNew, textvariable=v3) input3.place(relx=0.2, rely=0.36) input4 = Entry(winNew, textvariable=v4) input4.place(relx=0.2, rely=0.49) input5 = Entry(winNew, textvariable=v5) input5.place(relx=0.2, rely=0.62) comb = tkinter.ttk.Combobox(winNew, textvariable=v6, values=['完好', '丢失', '损坏']) comb.place(relx=0.2, rely=0.75) Button(winNew, text='确定', command=lambda: modif2(input1, input2, input3, input4, input5, v6)).place(relx=0.2, rely=0.85) Button(winNew, text='关闭', command=winNew.destroy).place(relx=0.65, rely=0.85)def insert(): def insert2(i1, i2, i3, i4, i5): if i1.get() == '' or i2.get() == '' or i3.get() == '' or i4.get() == '' or i5.get() == '': showwarning('警告', '请填写全部信息') db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select isbn from book' cur.execute(sql) result = cur.fetchall() for i in result: if i[0] == i2.get(): showwarning('警告', '该书已存在') return sql = 'insert into book values (%s, %s, %s, %s, %s,%s,%s)' values = (i1.get(), i2.get(), i3.get(), i4.get(), i5.get(), '未借阅', None) cur.execute(sql, values) cur.close() db.commit() db.close() showinfo('提示', '书籍信息已添加成功') Empty() All() winNew = Toplevel(root) winNew.geometry('280x420+500+300') winNew.title('添加书籍') winNew.iconbitmap('start.ico') winNew.wm_attributes('-topmost', 1) Label(winNew, text='书名:').place(relx=0.05, rely=0.1) Label(winNew, text='isbn:').place(relx=0.05, rely=0.23) Label(winNew, text='作者:').place(relx=0.05, rely=0.36) Label(winNew, text='出版社:').place(relx=0, rely=0.49) Label(winNew, text='种类:').place(relx=0.05, rely=0.62) v1 = StringVar() v2 = StringVar() v3 = StringVar() v4 = StringVar() v5 = StringVar() input1 = Entry(winNew, textvariable=v1) input1.place(relx=0.2, rely=0.1) input2 = Entry(winNew, textvariable=v2) input2.place(relx=0.2, rely=0.23) input3 = Entry(winNew, textvariable=v3) input3.place(relx=0.2, rely=0.36) input4 = Entry(winNew, textvariable=v4) input4.place(relx=0.2, rely=0.49) input5 = Entry(winNew, textvariable=v5) input5.place(relx=0.2, rely=0.62) Button(winNew, text='确定', command=lambda: insert2(input1, input2, input3, input4, input5)).place(relx=0.2, rely=0.85) Button(winNew, text='关闭', command=winNew.destroy).place(relx=0.65, rely=0.85)def in_book(): filename = tkinter.filedialog.askopenfilename() if filename != '': in_b.in_excel_b(filename) showinfo('提示', '书籍导入成功') Empty() All()def out_book(): filenewpath = asksaveasfilename(defaultextension='.xlsx') # 设置保存文件，并返回文件名，指定文件名后缀为.png out_b.read_mysql_to_xlsx(filenewpath) showinfo('提示', '书籍信息已导出成功')def changeTag(tag): frame0.pack_forget() frame2.pack_forget() frame3.pack_forget() if tag == 0: frame0.pack(padx=1, pady=1, ipadx=500, ipady=500) elif tag == 2: frame2.pack(padx=1, pady=1, ipadx=500, ipady=500) elif tag == 3: frame3.pack(padx=1, pady=1, ipadx=500, ipady=500)def yijie(user): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select count(*) from utob where id in (select id from user where name=%s)' cur.execute(sql, user) result = cur.fetchall() cur.close() db.close() return result[0][0]def treeviewClick(event): global tojie0 global tojie1 global tojie2 tojie0 = StringVar() tojie1 = StringVar() tojie2 = StringVar() if not tree.selection(): return item = tree.selection()[0] tojie0.set(tree.item(item, 'values')[0]) tojie1.set(tree.item(item, 'values')[1]) tojie2.set(tree.item(item, 'values')[5])def user_xin(): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from user' cur.execute(sql) result = cur.fetchall() today = datetime.datetime.today() for i in result: age = today.year - int(str(i[2])[0:4]) if i[5] != None and i[6] != None: tree2.insert('', 'end', values=(i[0], i[1], age, i[3], i[5], i[6])) elif i[5] == None and i[6] != None: tree2.insert('', 'end', values=(i[0], i[1], age, i[3], '暂无', i[6])) elif i[5] != None and i[6] == None: tree2.insert('', 'end', values=(i[0], i[1], age, i[3], i[5], '暂无')) elif i[5] == None and i[6] == None: tree2.insert('', 'end', values=(i[0], i[1], age, i[3], '暂无', '暂无')) cur.close() db.close()def modif_user(): def modif_user2(i1, i2, i31, i32, i33, i4, i5): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() if i1.get() != '': sql = 'update user set name=%s where id=%s' values = (i1.get(), xin_u.get()) cur.execute(sql, values) if i2.get() != '': sql = 'update user set sex=%s where id=%s' values = (i2.get(), xin_u.get()) cur.execute(sql, values) if i31.get() != '' and i32.get() != '' and i33.get() != '': bir = datetime.datetime.date( datetime.datetime.strptime(i31.get() + '-' + i32.get() + '-' + i33.get(), '%Y-%m-%d')) sql = 'update user set birthday=%s where id=%s' values = (bir, xin_u.get()) cur.execute(sql, values) if i4.get() != '': sql = 'update user set tel=%s where id=%s' values = (i4.get(), xin_u.get()) cur.execute(sql, values) if i5.get() != '': sql = 'update user set email=%s where id=%s' values = (i5.get(), xin_u.get()) cur.execute(sql, values) cur.close() db.commit() db.close() showinfo('提示', '信息已修改完毕') winNew.destroy() x = tree2.get_children() for item in x: tree2.delete(item) user_xin() if xin_u.get() == '': return winNew = Toplevel(root) winNew.geometry('320x420+500+300') winNew.title('修改信息') winNew.wm_attributes('-topmost', 1) winNew.iconbitmap('start.ico') Label(winNew, text='名字:').place(relx=0.1, rely=0.1) Label(winNew, text='性别:').place(relx=0.1, rely=0.23) Label(winNew, text='出生日期:').place(relx=0.01, rely=0.36) Label(winNew, text='年').place(relx=0.43, rely=0.36) Label(winNew, text='月').place(relx=0.68, rely=0.36) Label(winNew, text='日').place(relx=0.93, rely=0.36) Label(winNew, text='电话号码:').place(relx=0.01, rely=0.49) Label(winNew, text='邮箱地址:').place(relx=0.01, rely=0.62) v1 = StringVar() v2 = StringVar() v31 = StringVar() v32 = StringVar() v33 = StringVar() v4 = StringVar() v5 = StringVar() input1 = Entry(winNew, textvariable=v1) input1.place(relx=0.25, rely=0.1) input2 = Entry(winNew, textvariable=v2) input2.place(relx=0.25, rely=0.23) input31 = Entry(winNew, textvariable=v31) input31.place(relx=0.25, rely=0.36, width=55) input32 = Entry(winNew, textvariable=v32) input32.place(relx=0.49, rely=0.36, width=55) input33 = Entry(winNew, textvariable=v33) input33.place(relx=0.74, rely=0.36, width=55) input4 = Entry(winNew, textvariable=v4) input4.place(relx=0.25, rely=0.49) input5 = Entry(winNew, textvariable=v5) input5.place(relx=0.25, rely=0.62) Button(winNew, text='确定', command=lambda: modif_user2(input1, input2, input31, input32, input33, input4, input5)).place(relx=0.2, rely=0.85) Button(winNew, text='关闭', command=winNew.destroy).place(relx=0.65, rely=0.85)def del_user(): if xin_u.get() == '': return answer = tkinter.messagebox.askokcancel('提示', '您是否确认删除n用户%s' % xin_u.get()) # 均可为空 if answer: db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select id from utob' cur.execute(sql) data = cur.fetchall() for i in data: if i[0] == xin_u.get(): showwarning('警告', '该用户仍有书籍未归还，请勿删除') return sql = 'delete from user where id=%s' cur.execute(sql, xin_u.get()) cur.close() db.commit() db.close() showinfo('提示', '删除成功') x = tree2.get_children() for item in x: tree2.delete(item) user_xin()def in_user(): filename = tkinter.filedialog.askopenfilename() if filename != '': in_u.in_excel_u(filename) showinfo('提示', '用户信息导入成功') x = tree2.get_children() for item in x: tree2.delete(item) user_xin()def out_user(): filenewpath = asksaveasfilename(defaultextension='.xlsx') # 设置保存文件，并返回文件名，指定文件名后缀为.png out_u.read_mysql_to_xlsx(filenewpath) showinfo('提示', '用户信息已导出成功')def tree2viewClick(event): global xin_u xin_u = StringVar() if not tree2.selection(): return item = tree2.selection()[0] xin_u.set(tree2.item(item, 'values')[3])def tree3viewClick(event): global xin_ub xin_ub = StringVar() if not tree3.selection(): return item = tree3.selection()[0] xin_ub.set(tree3.item(item, 'values')[0])def utob_xin(): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from utob' cur.execute(sql) result = cur.fetchall() today = datetime.datetime.today() for i in result: sql2 = 'select bname from book where isbn=%s' cur.execute(sql2, i[0]) bname = cur.fetchall() if datetime.datetime.date(datetime.datetime.today()).__ge__(i[3]): tree3.insert('', 'end', values=(bname[0][0], i[1], i[2], i[3], '已过期', i[4])) else: tree3.insert('', 'end', values=(bname[0][0], i[1], i[2], i[3], '未过期', i[4])) cur.close() db.close()def insert_ub(): def insert_ub2(i1, i2): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql23 = 'select count(*) from book where bname=%s' cur.execute(sql23, i1.get()) number = cur.fetchall() if number[0][0] == 0: showwarning('警告', '该书不存在') return sql = 'select isbn from book where bname=%s' cur.execute(sql, i1.get()) result = cur.fetchall() sql21 = 'select count(*) from utob where isbn=%s' cur.execute(sql21, result[0][0]) number = cur.fetchall() if number[0][0] == 1: showwarning('警告', '该书已被借阅') return sql22 = 'select count(*) from user where id=%s' cur.execute(sql22, i2.get()) number = cur.fetchall() if number[0][0] == 0: showwarning('警告', '该用户不存在') return today = datetime.datetime.today() start = today.date() end = today.date() + datetime.timedelta(days=30) sql3 = 'insert into utob values (%s,%s,%s,%s,%s)' values = (result[0][0], i2.get(), start, end, 0) cur.execute(sql3, values) sql = 'update book set note=%s where isbn=%s' text = ('已借出', result[0][0]) cur.execute(sql, text) cur.close() db.commit() db.close() showinfo('提示', '借阅信息添加完毕') winNew.destroy() x = tree3.get_children() for item in x: tree3.delete(item) utob_xin() winNew = Toplevel(root) winNew.geometry('320x300+500+300') winNew.title('添加借阅') winNew.iconbitmap('start.ico') winNew.wm_attributes('-topmost', 1) Label(winNew, text='书名:').place(relx=0.1, rely=0.25) Label(winNew, text='id:').place(relx=0.1, rely=0.55) v1 = StringVar() v2 = StringVar() input1 = Entry(winNew, textvariable=v1) input1.place(relx=0.25, rely=0.25) input2 = Entry(winNew, textvariable=v2) input2.place(relx=0.25, rely=0.55) Button(winNew, text='确定', command=lambda: insert_ub2(input1, input2)).place(relx=0.2, rely=0.8) Button(winNew, text='关闭', command=winNew.destroy).place(relx=0.65, rely=0.8)def del_ub(): if xin_ub.get() == '': return answer = tkinter.messagebox.askokcancel('提示', '您是否确认删除有关《%s》的借阅信息' % xin_ub.get()) # 均可为空 if answer: db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select isbn from book where bname=%s' cur.execute(sql, xin_ub.get()) gui_isbn = cur.fetchall() sql2 = 'delete from utob where isbn=%s' cur.execute(sql2, gui_isbn) sql3 = "update book set note='未借阅' where bname=%s" cur.execute(sql3, xin_ub.get()) cur.close() db.commit() db.close() showinfo('提示', '《%s》的记录已删除' % xin_ub.get()) x = tree3.get_children() for item in x: tree3.delete(item) utob_xin()def xu_ub(): if xin_ub.get() == '': return answer = tkinter.messagebox.askokcancel('提示', '您确定对《%s》的信息续借？' % xin_ub.get()) # 均可为空 if answer: db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select isbn from book where bname=%s' cur.execute(sql, xin_ub.get()) gui_isbn = cur.fetchall() sql1 = 'select times from utob where isbn=%s' cur.execute(sql1, gui_isbn) res = cur.fetchall() sql2 = 'select etime from utob where isbn=%s' cur.execute(sql2, gui_isbn) data = cur.fetchall() etime_new = data[0][0] + + datetime.timedelta(days=30) sql3 = "update utob set etime=%s where isbn=%s" values = (etime_new, gui_isbn) cur.execute(sql3, values) sql4 = 'update utob set times=times+1 where isbn=%s' cur.execute(sql4, gui_isbn) showinfo('续借成功', '截止日期已推迟30天') cur.close() db.commit() db.close() x = tree3.get_children() for item in x: tree3.delete(item) utob_xin()def admin_xin(): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select * from admin' cur.execute(sql) result = cur.fetchall() for i in result: if i[0] == 'xwss': continue tree4.insert('', 'end', values=(i[0], i[2])) cur.close() db.close()def tree4viewClick(event): global xin_ad xin_ad = StringVar() if not tree4.selection(): return item = tree4.selection()[0] xin_ad.set(tree4.item(item, 'values')[0])def insert_ad(): def insert_ad2(i1): db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'select id from admin' cur.execute(sql) data = cur.fetchall() if data[-1][0] == 'xwss': admin_id = 'admin1' else: admin_id = 'admin' + str(int(data[-1][0][5:]) + 1) sql = 'insert into admin values (%s,%s,%s)' values = (admin_id, admin_id, i1.get()) cur.execute(sql, values) cur.close() db.commit() db.close() showinfo('提示', '管理员信息添加完毕n账号为：%s,密码为：%s' % (admin_id, admin_id)) winNew.destroy() x = tree4.get_children() for item in x: tree4.delete(item) admin_xin() winNew = Toplevel(root) winNew.geometry('320x260+500+300') winNew.title('添加管理员') winNew.iconbitmap('start.ico') winNew.wm_attributes('-topmost', 1) Label(winNew, text='姓名:').place(relx=0.1, rely=0.25) v1 = StringVar() input1 = Entry(winNew, textvariable=v1) input1.place(relx=0.25, rely=0.25) Button(winNew, text='确定', command=lambda: insert_ad2(input1)).place(relx=0.2, rely=0.8) Button(winNew, text='关闭', command=winNew.destroy).place(relx=0.65, rely=0.8)def del_ad(): if xin_ad.get() == '': return answer = tkinter.messagebox.askokcancel('提示', '您是否确认删除管理员%s' % xin_ad.get()) # 均可为空 if answer: db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'delete from admin where id=%s' cur.execute(sql, xin_ad.get()) cur.close() db.commit() db.close() showinfo('提示', '管理员%s的信息已删除' % xin_ad.get()) x = tree4.get_children() for item in x: tree4.delete(item) admin_xin()def xiu_ad(): def xiu_ad2(i2, i3): if i2.get() != i3.get(): showwarning('警告', '请核实两次输入的密码') return db = pymysql.connect(host='localhost', user='root', password='159357', db='sdusql', port=3306) cur = db.cursor() sql = 'update admin set password=%s where id=%s' values = (i2.get(), xin_ad.get()) cur.execute(sql, values) cur.close() db.commit() db.close() showinfo('提示', '管理员%s密码已修改完毕' % xin_ad.get()) rpwd.destroy() if xin_ad.get() == '': return rpwd = Toplevel(root) rpwd.title('修改密码') rpwd.iconbitmap('start.ico') rpwd.geometry('300x200+500+300') rpwd.wm_attributes('-topmost', 1) v2 = StringVar() v3 = StringVar() Label(rpwd, text='新密码：').place(relx=0.09, rely=0.25) input2 = Entry(rpwd, textvariable=v2, show='*') input2.place(relx=0.3, rely=0.25) Label(rpwd, text='确认密码：').place(relx=0.04, rely=0.55) input3 = Entry(rpwd, textvariable=v3, show='*') input3.place(relx=0.3, rely=0.55) Button(rpwd, text='确定', width=10, command=lambda: xiu_ad2(input2, input3)).place(relx=0.1, rely=0.8) Button(rpwd, text='取消', width=10, command=rpwd.destroy).place(relx=0.5, rely=0.8)def mymain(user): global root style = Style(theme="yeti") root = style.master root.title('图书管理系统') root.geometry('900x600+500+300') root.iconbitmap('start.ico') tabControl = ttk.Notebook(root) tab1 = ttk.frame(tabControl) tab2 = ttk.frame(tabControl) tab3 = ttk.frame(tabControl) tabControl.add(tab1, text='首页') tabControl.add(tab2, text='书库') tabControl.add(tab3, text='我的') tabControl.pack(expand=1, fill='both', padx=5) labelframe01 = Labelframe(tab1, text="公告", font=('宋体', 16), fg='red') labelframe01.pack(padx=2, pady=5, ipadx=450, ipady=120, anchor='nw') global text1 text1 = Text(labelframe01, height=10, width=40) text1.place(relx=0.01) global text2 text2 = Text(labelframe01, height=10, width=40) text2.place(relx=0.55) outtext1() Button(labelframe01, text='发布', width=10, command=intext1).place(relx=0.44, rely=0.1) Button(labelframe01, text='删除', width=10, command=deltext1).place(relx=0.44, rely=0.3) labelframe02 = Labelframe(tab1, text="推荐", font=('宋体', 16), fg='red') labelframe02.pack(padx=2, pady=5, ipadx=450, ipady=120, anchor='nw') global text3 text3 = Text(labelframe02, height=10, width=40) text3.place(relx=0.01) global text4 text4 = Text(labelframe02, height=10, width=40) text4.place(relx=0.55) outtext3() Button(labelframe02, text='发布', width=10, command=intext3).place(relx=0.44, rely=0.1) Button(labelframe02, text='删除', width=10, command=deltext3).place(relx=0.44, rely=0.3) img = Image.open('书库.jpg') img = img.resize((900, 600)) # 修改图片大小 photo = ImageTk.PhotoImage(img) Label(tab2, image=photo, compound=CENTER).pack() global var var = StringVar() comb = tkinter.ttk.Combobox(tab2, textvariable=var, values=['书名', 'ISBN', '作者', '出版社', '分类']) comb.place(relx=0.08, rely=0.05, relwidth=0.2) comb.current(0) var1 = StringVar() global t1 t1 = Entry(tab2, textvariable=var1) t1.place(relx=0.29, rely=0.05, relheight=0.06) Button(tab2, text='搜索', width=10, command=lambda: find(t1)).place(relx=0.55, rely=0.06) Button(tab2, text='再次搜索', width=10, command=lambda: find2(t1)).place(relx=0.67, rely=0.06) Button(tab2, text='清空', width=10, command=Empty).place(relx=0.79, rely=0.06) Button(tab2, text='显示全部', width=10, command=All).place(relx=0.55, rely=0.15) global tree tree = ttk.Treeview(tab2, height=18, show="headings") # 表格 tree["columns"] = ('书名', 'ISBN', '作者', '出版社', '分类', '状态') tree.column("书名", width=148, anchor='center') # 表示列,不显示 tree.column("ISBN", width=148, anchor='center') tree.column("作者", width=148, anchor='center') tree.column("出版社", width=148, anchor='center') tree.column("分类", width=148, anchor='center') tree.column("状态", width=148, anchor='center') tree.heading("书名", text='书名') # 表示列,不显示 tree.heading("ISBN", text='ISBN') tree.heading("作者", text='作者') tree.heading("出版社", text='出版社') tree.heading("分类", text='分类') tree.heading("状态", text='状态') tree.place(rely=0.3) tree.bind('
我是利用用户或者管理员的名字作为串联条件，但其实考虑到重名情况，其实账号更为合理。
import start as simport menu_user as muimport menu_admin as mas.frame()if s.username != '': if s.username == 'xwss' or s.username[0:5] == 'admin': ma.mymain(s.username) else: mu.mymain(s.username)
备注 1.列表处我未设置滑轮，请各位注意；
2.代码中存在大量global，是因为之前从未使用过python，思考不周出现的，后来想到的是函数套函数解决，其他更高明的方法敬请指出。
3.代码的三部分分在三个py文件中，又引用在总结的py文件中。