一、简介
该系统主要通过tkinter库实现图书管理系统,包含对SQLite3数据库的增删改查的知识点。主要功能有:登录、注册、图书查询、借书、还书、图书录入、图书删除、图书统计、管理员新增、数据库查询等模块。
Python小论文-图书管理系统+Python+tkinter+sqlite3+PersistentDB资源池
Python桌面应用Demo
Python程序设计课后作业参考
Tkinter Treeview控件使用
Tkinter Frame控件嵌套使用(实现同TK页面内Frame切换)
Tk图形化界面
二、系统实现功能介绍及使用方法
登录功能:
功能说明:普通用户或管理员用户输入账号、密码再点击登录按钮进行登录,系统会根据用户的角色属性不同进入对应的界面,并带入登录用户的信息到下个页面;点击注册可跳转至注册页面,注册页面中点击返回登录按钮可跳转回登录界面。
![](https://i-blog.csdnimg.cn/blog_migrate/8c222749b5322b52c3c3ab56beb7f793.png)
界面绘制方法代码实现:
def create_page(self):
self.page = Frame(self.root) # 创建Frame
self.register_page = Register_Page(self.root)
self.register_page.pack_forget()
self.page.pack()
Label(self.page).grid(row=0, stick=W)
Label(self.page, text='账号').grid(row=1, stick=W, pady=10)
Entry(self.page, textvariable=self.username).grid(row=1, column=1, stick=E)
Label(self.page, text='密码').grid(row=2, stick=W, pady=10)
Entry(self.page, textvariable=self.password, show='*').grid(row=2, column=1, stick=E)
Button(self.page, text='登录', command=self.login_check).grid(row=3, stick=W, pady=5)
Button(self.page, text='注册', command=self.to_register).grid(row=3, stick=E, column=1)
Button(self.register_page, text='返回登录', command=self.to_login).grid(row=6, stick=E, column=1)
登录方法代码实现:
def login_check(self):
user_name = str(self.username.get()).strip()
password = str(self.password.get()).strip()
if user_name and password:
password_md5 = utils.get_md5(password)
user_info = data_manipulation.find_user_info_by_user_name_and_password(user_name, password_md5)
if user_info:
role = user_info.get('role')
# role 等于0为普通用户
if role == 0:
self.page.destroy()
User_MainPage(self.root, user_info)
# role 等于1为管理员账号
elif role == 1:
self.page.destroy()
Admin_MainPage(self.root, user_info)
else:
showwarning(title='登录失败', message='账号或密码错误!')
else:
showwarning(title='登录失败', message='账号或密码未输入!')
注册功能
功能说明:若选择注册功能,进入注册界面,用户输入账号、学号、姓名、密码、确认密码,根据用户输入的信息,系统会判断所有输入框是否已经输入信息,还会判断用户名或学号是否已经存在以及两次输入的密码是否相同,异常则弹窗提示,密码通过MD5加密后存储到数据库中,数据库中密码不明文展示,防止密码泄露。
![](https://i-blog.csdnimg.cn/blog_migrate/e1e9bf5d408ff644fe8c20a1d7f2a415.png)
注册界面绘制代码实现:
def create_page(self):
Label(self, text='账 号:').grid(row=1, stick=W, pady=15)
Entry(self, textvariable=self.username).grid(row=1, column=1, stick=E)
Label(self, text='学 号: ').grid(row=2, stick=W, pady=15)
Entry(self, textvariable=self.user_id).grid(row=2, column=1, stick=E)
Label(self, text='姓 名: ').grid(row=3, stick=W, pady=15)
Entry(self, textvariable=self.nickname).grid(row=3, column=1, stick=E)
Label(self, text='密 码: ').grid(row=4, stick=W, pady=15)
Entry(self, textvariable=self.user_password, show='*').grid(row=4, column=1, stick=E)
Label(self, text='重新输入密码: ').grid(row=5, stick=W, pady=15)
Entry(self, textvariable=self.user_password_re, show='*').grid(row=5, column=1, stick=E)
Button(self, text='注册', command=self.register_admin).grid(row=6, stick=W, column=0)
注册方法代码实现:
def register_admin(self):
user_name = str(self.username.get()).strip()
nickname = str(self.nickname.get()).strip()
user_id = str(self.user_id.get()).strip()
password = str(self.user_password.get()).strip()
password_re = str(self.user_password_re.get()).strip()
print(user_name, nickname, user_id, password, password_re)
if user_id and user_name and nickname and password and password_re:
if password == password_re:
user_info = data_manipulation.find_user_info_by_user_id_or_user_name(user_id, user_name)
if user_info:
showwarning(message='输入的用户名或学号已经存在!')
else:
res = data_manipulation.insert_user_info(user_id, user_name, utils.get_md5(password), nickname)
if res:
showinfo(message='注册成功!')
else:
showwarning(message='注册失败,请稍后重试!')
else:
showwarning(message='两次输入密码不一致!')
else:
showwarning(message='注册信息输入有误!')
普通用户功能:借书记录页面
功能说明:普通用户登录后默认进入借书记录界面,使用Treeview展示当前登录用户的借书记录(不包含已归还记录),用户可以点击刷新按钮刷新借书记录,未选中记录时点击还书按钮,则弹窗提示用户需要选中借书记录。选中借书记录后点击还书,更新图书记录表对应书籍可借数量,已经更新借书记录表中记录的归还时间、借书状态、借书天数,只要有一条数据更新失败则提示用户还书失败,数据库操作不进行提交;当数据库均更新成功时弹出还书成功页面,显示归还书名、用户名、姓名、借出日期、归还日期、借出天数。
![](https://i-blog.csdnimg.cn/blog_migrate/03a46029e56f272fe85125335154d57b.png)
![](https://i-blog.csdnimg.cn/blog_migrate/54028620a37d25114741f7c7ace18cf4.png)
界面绘制代码实现:
def create_page(self):
self.page = Frame(self.root) # 创建Frame
self.queryPage = QueryFrame(self.root, self.user_info) # 查询界面
self.borrowPage = BorrowFrame(self.root, self.user_info) # 借阅界面
menubar = Menu(self.root)
menubar.add_command(label='借书记录', command=self.return_data)
menubar.add_command(label='查询图书', command=self.query_data)
menubar.add_command(label='借阅图书', command=self.borrow_data)
x_scroll = Scrollbar(self.page, orient=HORIZONTAL)
y_scroll = Scrollbar(self.page, orient=VERTICAL)
self.root['menu'] = menubar # 设置菜单栏
columns = ['记录ID', '书名', '图书编号', '借出时间']
self.table = Treeview(master=self.page, # 父容器
height=10, # 表格显示的行数,height行
columns=columns, # 显示的列
show='headings', # 隐藏首列
xscrollcommand=x_scroll.set, # x轴滚动条
yscrollcommand=y_scroll.set, # y轴滚动条
)
for column in columns:
self.table.heading(column=column, text=column, anchor=CENTER, ) # 定义表头
self.table.column(column=column, width=150, minwidth=100, anchor=CENTER, ) # 定义列
x_scroll.config(command=self.table.xview)
x_scroll.pack(side=BOTTOM, fill=X)
y_scroll.config(command=self.table.yview)
y_scroll.pack(side=RIGHT, fill=Y)
self.table.pack(fill=BOTH, expand=True)
self.page.pack()
self.btn_frame = Frame()
self.btn_frame.pack()
Button(self.btn_frame, text='刷 新', font=("宋体", 14, "bold"), command=self.update_table_data).grid(row=1,stick=W,column=1,pady=20,padx=50)
Button(self.btn_frame, text='还 书', font=("宋体", 14, "bold"), command=self.return_book).grid(row=1, stick=E,column=2,pady=20, padx=50)
借书记录表格数据更新方法代码实现:
def delete_table_item(self):
obj = self.table.get_children() # 获取所有对象
for o in obj:
self.table.delete(o) # 删除对象
def update_table_data(self):
self.delete_table_item()
borrow_list = data_manipulation.find_book_borrowing_record_by_user_id(user_id=self.user_info.get('user_id'))
if borrow_list:
borrow_show_list = []
for borrow_info in borrow_list:
borrow = [borrow_info.get('id'), borrow_info.get('book_name'), borrow_info.get('book_id'),
borrow_info.get('borrowing_time')]
borrow_show_list.append(borrow)
for index, data in enumerate(borrow_show_list):
self.table.insert('', END, values=data) # 添加数据到末尾
归还图书方法代码实现:
def return_book(self):
cur_item = self.table.focus()
select_info = self.table.item(cur_item)
print(select_info)
if len(select_info.get('values')) == 0:
showwarning(title='错误', message='请选中需要归还书籍的借书记录!')
else:
book_id = select_info.get('values')[2]
record_id = select_info.get('values')[0]
book_name = select_info.get('values')[1]
borrowing_time = utils.get_str_to_timestamp_ten(select_info.get('values')[3])
user_name = self.user_info.get('user_name')
nickname = self.user_info.get('nickname')
if book_id and record_id:
return_time = utils.get_format_time_by_ten()
lending_days = int((int(time.time()) - borrowing_time) / 86400)
if lending_days == 0:
lending_days = 1
return_res = data_manipulation.update_borrowing_record(book_id, record_id, return_time, 3, lending_days)
if return_res:
self.update_table_data()
borrow_win = Tk()
borrow_win.title('归还成功')
borrow_win.geometry('500x300')
Label(borrow_win, text='书 名:' + book_name, font=("宋体", 14, "bold")).grid(row=1, stick=W, pady=10, padx=100)
Label(borrow_win, text='用 户:' + user_name, font=("宋体", 14, "bold")).grid(row=2, stick=W, pady=10, padx=100)
Label(borrow_win, text='姓 名:' + nickname, font=("宋体", 14, "bold")).grid(row=3, stick=W, pady=10, padx=100)
Label(borrow_win, text='借出日期:' + utils.get_format_time_by_ten(borrowing_time),
font=("宋体", 14, "bold")).grid(row=4, stick=W, pady=10, padx=100)
Label(borrow_win, text='归还日期:' + return_time,
font=("宋体", 14, "bold")).grid(row=5, stick=W, pady=10, padx=100)
Label(borrow_win, text='借出天数:' + str(lending_days) + ' 天', font=("宋体", 14, "bold")).grid(row=6, stick=W, pady=10, padx=100)
else:
showwarning(title='错误', message='还书失败,请稍后重试!')
else:
showwarning(title='错误', message='请确认还书信息是否正确!')
普通用户功能:图书查询功能
功能说明:点击菜单“查询图书”选项,进入查询界面,在输入框输入查找的书名,点击“查找”按钮,系统访问存储图书的books数据库表,对书名进行模糊查询,如果该书存在,则返回该书的所有信息,弹出新的弹窗,使用表格展示图书列表(如下图),若不存在,则弹出警告。
![](https://i-blog.csdnimg.cn/blog_migrate/7d65cb468558cd0ea02cc7eddd192108.png)
![](https://i-blog.csdnimg.cn/blog_migrate/f39420fba233e3383ef60d3bc9333a70.png)
查找方法代码实现:
def find_book(self):
book_name = str(self.book_name.get()).strip()
if book_name:
book_info_list = data_manipulation.find_book_info_by_book_name(book_name)
if book_info_list:
book_window = Tk()
book_window.title('该图书信息')
book_window.geometry('500x400')
x_scroll = Scrollbar(book_window, orient=HORIZONTAL)
y_scroll = Scrollbar(book_window, orient=VERTICAL)
columns = ['编号', '书名', '作者', '数量', '可借数量', '价格/元']
table = Treeview(
master=book_window, # 父容器
height=10, # 表格显示的行数,height行
columns=columns, # 显示的列
show='headings', # 隐藏首列
xscrollcommand=x_scroll.set, # x轴滚动条
yscrollcommand=y_scroll.set, # y轴滚动条
)
for column in columns:
table.heading(column=column, text=column, anchor=CENTER,
command=lambda name=column: showinfo('', '{}描述信息~~~'.format(name))) # 定义表头
table.column(column=column, width=100, minwidth=100, anchor=CENTER, ) # 定义列
x_scroll.config(command=table.xview)
x_scroll.pack(side=BOTTOM, fill=X)
y_scroll.config(command=table.yview)
y_scroll.pack(side=RIGHT, fill=Y)
table.pack(fill=BOTH, expand=True)
for index, data in enumerate(book_info_list):
table.insert('', END, values=data) # 添加数据到末尾
book_window.mainloop()
else:
showwarning(message="您查询的书籍不存在!")
else:
showwarning(message="请输入您要查询的书名!")
普通用户功能:借阅功能
功能说明:点击菜单“借阅图书”菜单选项,进入借阅界面,输入图书编号,点击“借书”按钮,系统访问存储书籍的books表,如果表中存在该书且该书的可借数量不为0,则弹出“借书成功”的信息,并将借书的信息写入借书记录表borrowing_record中,books表中对应书籍可借数量执行数量减一的操作。若输入信息错误或书籍不存在,则弹出相应的错误信息。
![](https://i-blog.csdnimg.cn/blog_migrate/a1200a9b54f0fcef28be365a18030986.png)
![](https://i-blog.csdnimg.cn/blog_migrate/7f8a788cf5205ad0cab15c2d4eeb02e9.png)
借书方法代码实现:
def borrow_book(self):
book_id = str(self.book_id.get()).strip()
user_id = self.user_info.get('user_id')
if book_id and user_id:
book_info = data_manipulation.find_book_info_by_book_id(book_id)
if book_info:
residual_quantity = book_info[4]
if residual_quantity > 0:
borrow_res = data_manipulation.insert_borrowing_record(book_id, user_id)
if borrow_res:
showinfo(title='成功', message='借书成功!')
else:
showwarning(title='错误', message='借书失败,请稍后重试!')
else:
showwarning(title='错误', message='该书已被借完!')
else:
showwarning(title='错误', message='该书不存在!')
else:
showwarning(title='错误', message='请输入图书编号后重试!')
普通用户功能:菜单功能
菜单栏绘制代码及切换代码实现:
self.page = Frame(self.root) # 创建Frame
self.queryPage = QueryFrame(self.root, self.user_info) # 查询界面
self.borrowPage = BorrowFrame(self.root, self.user_info) # 借阅界面
menubar = Menu(self.root)
menubar.add_command(label='借书记录', command=self.return_data)
menubar.add_command(label='查询图书', command=self.query_data)
menubar.add_command(label='借阅图书', command=self.borrow_data)
def query_data(self):
self.queryPage.pack()
self.borrowPage.pack_forget()
self.page.pack_forget()
self.btn_frame.pack_forget()
def borrow_data(self):
self.queryPage.pack_forget()
self.borrowPage.pack()
self.page.pack_forget()
self.btn_frame.pack_forget()
def return_data(self):
self.queryPage.pack_forget()
self.borrowPage.pack_forget()
self.page.pack()
self.btn_frame.pack()
self.update_table_data()
管理员功能:录入书籍功能
功能说明:管理员界面默认为录入界面,输入书籍的书名、作者、数量及价格信息,点击”录入“按钮,系统判断数据库books表中是否有该书,如果有该书信息,则对其数量加上输入的对应数量,如果该书不存在,则新增一条该书的信息。信息填写不完全则弹窗提示。
![](https://i-blog.csdnimg.cn/blog_migrate/7801f5074424b43f8d868086a45d0933.png)
![](https://i-blog.csdnimg.cn/blog_migrate/459b9c5db4fdcc521bc6596afee6ed7b.png)
图书录入代码实现:
def add_books(self):
book_name = str(self.book_name.get()).strip()
book_author = str(self.book_author.get()).strip()
book_total = str(self.book_total.get()).strip()
book_price = str(self.book_price.get()).strip()
if book_name and book_author and book_total and book_price:
book_info = data_manipulation.find_book_info_by_book_name_and_author(book_name, book_author)
if book_info:
book_id = book_info[0]
update_book_info_res = data_manipulation.update_book_info(book_id, book_total)
if update_book_info_res:
showinfo(tatle='录入成功', message='图书数量已增加成功!')
else:
showwarning(title='录入失败', message='图书数量增加失败!')
else:
insert_book_res = data_manipulation.insert_book_info(book_name, book_author, book_total, book_price)
if insert_book_res:
showinfo(title='录入成功', message='图书信息录入成功!')
else:
showwarning(title='录入失败', message='图书信息录入失败!')
else:
showwarning(title='录入失败', message='请输入所有图书信息后重试!')
管理员功能:删除书籍功能
功能说明:点击菜单“删除书籍”选项,进入删除书籍界面,输入图书编号及删除数量,点击“删除”按钮,系统判断books表中是否存在该书的信息,不存在则弹出警告,存在则判断该书可借数量是否比要删除的数量大,如比输入的数量大,则在数据库books表中减少该书的总数及可借数量信息,弹出“删除成功”的信息。若比输入的数量小,则提示删除失败。
![](https://i-blog.csdnimg.cn/blog_migrate/cd95460265247c2448be7e36c51345eb.png)
![](https://i-blog.csdnimg.cn/blog_migrate/2a5f65ade5b59b0ae7d0ca90adbfc10f.png)
删除图书功能代码实现:
def delete_books(self, event):
cur_item = self.table.focus()
select_info = self.table.item(cur_item)
print(select_info)
if len(select_info.get('values')) == 0:
showwarning(title='错误', message='请选中需要查看统计信息的书籍!')
else:
book_id = select_info.get('values')[0]
result = simpledialog.askinteger(title='删除书籍', prompt='请输入您要删除的数量:', initialvalue='0')
book_delete_num = result
if book_delete_num is not None:
if book_delete_num != 0:
book_info = data_manipulation.find_book_info_by_book_id(book_id)
if book_info:
residual_quantity = book_info[4]
if int(residual_quantity) >= int(book_delete_num):
delete_book_res = data_manipulation.delete_book_num(book_id, book_delete_num)
if delete_book_res:
self.find_book()
showinfo(message='图书数量删除成功!')
else:
showwarning(message='图书数量删除失败!')
else:
showwarning(message='需要删除的图书数量大于可借数量,删除失败!')
else:
showwarning(message='未查询到图书信息,请确认图书编号输入正确!')
else:
showwarning(message='请输入需要删除的图书数量!')
管理员功能:统计书籍功能
功能说明:点击菜单“统计书籍”选项,进入统计书籍界面,输入该书的图书编号,点击“统计”按钮,系统判断数据库books表中是否有该书,如果没有弹出相应的提示,如果存在,则显示书的书名、总数、借出数量,剩余数量。
![](https://i-blog.csdnimg.cn/blog_migrate/059b68a1daf623295a4ff3adf0ce8674.png)
统计书籍代码实现:
def count_books(self, *args):
cur_item = self.table.focus()
select_info = self.table.item(cur_item)
print(select_info)
if len(select_info.get('values')) == 0:
showwarning(title='错误', message='请选中需要查看统计信息的书籍!')
else:
book_id = select_info.get('values')[0]
book_info = data_manipulation.find_book_info_by_book_id(book_id)
if book_info:
# id, book_name, author, total, residual_quantity, price, gmt_created
book_name = book_info[1]
book_total = book_info[3]
residual_quantity = book_info[4]
quantity_lent = book_total - residual_quantity
if self.count_win:
self.count_win.destroy()
self.count_win = Tk()
self.count_win.title('统计信息')
self.count_win.geometry('300x300')
Label(self.count_win).grid(row=0, stick=W, pady=5)
Label(self.count_win, text='书 名: ' + book_name).grid(row=1, stick=W, pady=10, padx=90)
Label(self.count_win, text='总 数: ' + str(book_total) + '本').grid(row=2, stick=W, pady=15,
padx=90)
Label(self.count_win, text='借出数量: ' + str(quantity_lent) + '本').grid(row=3, stick=W, pady=15, padx=90)
Label(self.count_win, text='剩余数量: ' + str(residual_quantity) + '本').grid(row=4, stick=W, pady=15,
padx=90)
管理员功能:管理员账号注册功能
功能说明:点击菜单“添加管理员”选项,进入注册界面,输入新账号,姓名,新密码,重新输入新密码,点击“注册”按钮,系统判断该账号是否存在,重新输入密码是否一致,如出现错误,则返回相应的错误信息,否则,则显示注册成功。
![](https://i-blog.csdnimg.cn/blog_migrate/f1ecc9861008adb47f0accaf05bfdcc4.png)
管理员账号注册代码实现:
def register_admin(self):
admin_user_name = str(self.admin_user_name.get()).strip()
admin_nickname = str(self.admin_nickname.get()).strip()
admin_password = str(self.admin_password.get()).strip()
admin_password_re = str(self.admin_password_re.get()).strip()
print(admin_user_name, admin_nickname, admin_password, admin_password_re)
if admin_user_name and admin_nickname and admin_password and admin_password_re:
if admin_password == admin_password_re:
user_info = data_manipulation.find_user_info_by_user_name(admin_user_name)
if user_info:
showwarning(message='输入的用户名已经存在!')
else:
admin_user_count = data_manipulation.find_user_count(role=1)
res = data_manipulation.insert_user_info(admin_user_count + 1, admin_user_name,
utils.get_md5(admin_password), admin_nickname)
if res:
showinfo(message='注册成功!')
else:
showwarning(message='注册失败,请稍后重试!')
else:
showwarning(message='两次输入密码不一致!')
else:
showwarning(title='注册失败', message='注册信息输入有误!')
业务数据查询、插入、修改、删除代码实现:
import db_utils
def find_user_info_by_user_name_and_password(user_name, password):
sql = "select id,user_id,user_name,nickname,role,user_status,gmt_created from user_account where " \
"user_name=? and password=? limit 1"
data = (user_name, password)
res = db_utils.find_one(sql, data)
if res:
user_info = {'id': res[0], 'user_id': res[1], 'user_name': res[2], 'nickname': res[3], 'role': res[4],
'user_status': res[5], 'gmt_created': res[6]}
return user_info
return res
def find_user_info_by_user_id_or_user_name(user_id, user_name):
sql = "select id,user_id,user_name,password,nickname,role,user_status,gmt_created from user_account where " \
"user_id=? or user_name=? limit 1"
data = (user_id, user_name)
res = db_utils.find_one(sql, data)
return res
def find_user_info_by_user_name(user_name):
sql = "select id,user_id,user_name,password,nickname,role,user_status,gmt_created from user_account where " \
"user_name=? limit 1"
data = (user_name,)
res = db_utils.find_one(sql, data)
return res
def insert_user_info(user_id=None, user_name=None, password=None, nickname=None, role=0, user_status=1):
sql = "insert into user_account(user_id,user_name,password,nickname,role,user_status) " \
"values(?,?,?,?,?,?)"
data = (user_id, user_name, password, nickname, role, user_status)
res = db_utils.insert(sql, data)
print(res)
return res
def find_user_count(role=1):
sql = "select count(1) from user_account where role=? limit 1"
data = (role,)
res = db_utils.find_one(sql, data)
return res[0]
def find_book_info_by_book_id(book_id):
sql = "select id,book_name,author,total,residual_quantity,price,gmt_created from books where id=?"
data = (book_id,)
res = db_utils.find_one(sql, data)
return res
def find_book_info_by_book_name_and_author(book_name, author):
sql = "select id,book_name,author,total,residual_quantity,price,gmt_created from books where book_name=? " \
"and author=?"
data = (book_name, author)
res = db_utils.find_one(sql, data)
return res
def find_book_info_by_book_name(book_name):
sql = "select id,book_name,author,total,residual_quantity,price,gmt_created from books where book_name like ?"
data = (f'%{book_name}%',)
res = db_utils.find_list(sql, data)
if res:
print(res)
book_info_list = []
for row in res:
book_info_list.append(list(row))
return book_info_list
return res
def insert_book_info(book_name, author, total, price):
sql = "insert into books(book_name,author,total,residual_quantity,price) values(?,?,?,?,?)"
residual_quantity = total
data = (book_name, author, total, residual_quantity, price)
res = db_utils.insert(sql, data)
return res
def update_book_info(book_id, total):
sql = "update books set total=total+?,residual_quantity=residual_quantity+? where id=?"
residual_quantity = total
data = (total, residual_quantity, book_id)
sql_info = [(sql, data)]
res = db_utils.update(sql_info)
return res
def delete_book_num(book_id, book_delete_num):
sql = "update books set total=total-?,residual_quantity=residual_quantity-? where id=?"
data = (book_delete_num, book_delete_num, book_id)
sql_info = [(sql, data)]
res = db_utils.update(sql_info)
return res
def find_book_borrowing_count(book_id, book_status=2):
sql = "select count(1) from borrowing_record where id=? and book_status=? limit 1"
data = (book_id, book_status)
res = db_utils.find_one(sql, data)
return res[0]
def find_book_borrowing_record_by_user_id(user_id):
sql = "select br.id,br.book_id,b.book_name,br.user_id,br.borrowing_time,br.return_time,br.book_status," \
"br.lending_days,br.gmt_created from " \
"borrowing_record as br left join books as b on br.book_id=b.id where br.user_id=? and br.book_status=2"
data = (user_id,)
res = db_utils.find_list(sql, data)
if res:
borrow_list = []
for row in res:
borrow_info = {'id': row[0], 'book_id': row[1], 'book_name': row[2], 'user_id': row[3],
'borrowing_time': row[4], 'return_time': row[5], 'book_status': row[6],
'lending_days': row[7], 'gmt_created': row[8]}
borrow_list.append(borrow_info)
return borrow_list
return res
def insert_borrowing_record(book_id, user_id, book_status=2, lending_days=0):
sql_borrow = "insert into borrowing_record(book_id,user_id,book_status,lending_days) values(?,?,?,?)"
data_borrow = (book_id, user_id, book_status, lending_days)
sql_book = "update books set residual_quantity=residual_quantity-1 where id=?"
data_book = (book_id,)
sql_info_list = [(sql_borrow, data_borrow), (sql_book, data_book)]
res = db_utils.update(sql_info_list)
return res
def update_borrowing_record(book_id, record_id, return_time=None, book_status=3, lending_days=None):
sql_borrow = "update borrowing_record set return_time=?,book_status=?,lending_days=? where id=?"
data_borrow = (return_time, book_status, lending_days, record_id)
sql_book = "update books set residual_quantity=residual_quantity+1 where id=?"
data_book = (book_id,)
sql_info_list = [(sql_borrow, data_borrow), (sql_book, data_book)]
res = db_utils.update(sql_info_list)
return res
通用工具类代码实现:
import hashlib
import datetime
import time
def get_md5(string):
if string:
return hashlib.md5(string.encode('utf-8')).hexdigest()
else:
raise ValueError('需要MD5加密的字符串为空!')
def get_current_time():
"""获取当前时间戳(秒)"""
return int(time.time())
def get_format_time_by_ten(time_ten=None, fmt="%Y-%m-%d %H:%M:%S"):
"""秒级时间戳转指定格式日期"""
time_ten = time_ten if (time_ten is not None) else get_current_time()
return datetime.datetime.fromtimestamp(time_ten).strftime(fmt)
def get_str_to_timestamp_ten(str_time, fmt="%Y-%m-%d %H:%M:%S"):
"""日期格式转时间戳(秒)"""
return int(datetime.datetime.strptime(str_time, fmt).timestamp())