数据库课程设计python——图书馆管理系统

对我来说挺大一个工作内容了，用户操作界面的代码是从别人的文章里拿来然后改的，文章有时间我找找会放进来

功能与结构

构建数据库（本地）

用的是SQL server，根据逻辑结构来建表，以及触发器

建表

CREATE DATABASE LibraryManage ON 
PRIMARY
(NAME = xsgl_Data, FILENAME = 'D:\DBF\LibraryManage.mdf', FILEGROWTH = 5)
LOG ON
(NAME = xsgl_Log, FILENAME = 'D:\DBF\LibraryManage.ldf', FILEGROWTH = 5)GO
USE LibraryManage
CREATE TABLE Users
(u_id CHAR(10) PRIMARY KEY,
u_password VARCHAR(20) NOT NULL, 
u_Admin BIT DEFAULT 0,
u_name VARCHAR(20) NOT NULL, 
user_phone CHAR(11), 
user_adress VARCHAR(60),
)CREATE TABLE Books
(book_id CHAR(10) PRIMARY KEY, 
book_name VARCHAR(40), 
author VARCHAR(40),
press VARCHAR(40), 
collocation_num INT, 
lendable INT, 
CONSTRAINT check_lendable CHECK (lendable <= collocation_num)
)CREATE TABLE Records
(Record_id INT IDENTITY(100000,1) PRIMARY KEY, 
u_id CHAR(10) FOREIGN KEY (u_id) REFERENCES Users(u_id),
book_id CHAR(10) FOREIGN KEY (book_id) REFERENCES Books(book_id),
lend_time DATETIME, 
return_time DATETIME
)CREATE TABLE Lending
(Record_id INT, 
u_id CHAR(10), 
book_id CHAR(10), 
lend_time DATETIME
FOREIGN KEY (Record_id) REFERENCES Records(Record_id)
)INSERT INTO Users(u_id, u_password, u_Admin, u_name, user_phone)
VALUES('0000000100','666', 1, '巩啊', '13912345678')INSERT INTO Users(u_id, u_password, u_name, user_phone)
VALUES
('0000000101','yqowzrxcwe', '李梓萱', '13812345678'),
('0000000102','mnahsduf', '张婧宸', '13712345678'),
('0000000103','cxnjmqzp', '赵奕涵', '13612345678'),
('0000000104','skwopdtnbf', '刘子怡', '13512345678'),
('0000000105','qweasdzxc', '陈思婷', '13412345678'),
('0000000106','poiuytrewq', '杨晨阳', '13312345678'),
('0000000107','ljhgfdsazx', '吴思涵', '18212345678'),
('0000000108','ytbvfrde', '周俊逸', '18112345678'),
('0000000109','mnbvcxzlkj', '孙雅菲', '18012345678'),
('0000000110','qwertyuiop', '郭雨莹', '15912345678'),
('0000000111','zxcvbnmasd', '唐子涵', '15812345678'),
('0000000112','asdfghjkl', '罗雨柔', '15712345678'),
('0000000113','poiuytrewq', '贾子萱', '15612345678'),
('0000000114','lkjhgfdsa', '尹忆萱', '15512345678'),
('0000000115','zxcvbnm', '段晨雨', '15412345678'),
('0000000116','asdfgh', '黄梓涵', '15312345678'),
('0000000117','qwerty', '董思涵', '15212345678'),
('0000000118','zxcvbn', '梁婧怡', '15112345678'),
('0000000119','asdfghj', '叶雨萱', '15012345678'),
('0000000120','poiuytre', '钟子涵', '14912345678'),
('0000000121','lkjhgf', '姚雅菲', '14812345678'),
('0000000122','zxcvbnm', '贺晨雨', '14712345678'),
('0000000123','qazwsx', '方梓涵', '14612345678'),
('0000000124','rfvtgb', '石晨阳', '14512345678'),
('0000000125','edcrfv', '常子萱', '14412345678'),
('0000000126','tgbnhy', '韩忆萱', '14312345678'),
('0000000127','ujmik,', '龙晨雨', '14212345678'),
('0000000128','yhnmju', '毛梓涵', '14112345678'),
('0000000129','poiuhg', '万雨萱', '14012345678'),
('0000000130','rewqas', '宋子涵', '13912345678')INSERT INTO Books (book_id, book_name, author, press, collocation_num, lendable)
VALUES
('1000000000', '骆驼祥子', '老舍', '北京教育出版社', 20, 18),
('1000000001', '围城', '钱钟书', '人民文学出版社', 30, 28),
('1000000002', '活着', '余华', '作家出版社', 25, 23),
('1000000003', '1984', '乔治·奥威尔', '南海出版公司', 15, 12),
('1000000004', '平凡的世界', '路遥', '人民文学出版社', 40, 37),
('1000000005', '白夜行', '东野圭吾', '南海出版公司', 18, 15),
('1000000006', '红楼梦', '曹雪芹', '人民文学出版社', 35, 32),
('1000000007', '傲慢与偏见', '简·奥斯汀', '外语教学与研究出版社', 22, 19),
('1000000008', '三体', '刘慈欣', '重庆出版社', 28, 25),
('1000000009', '追风筝的人', '卡勒德·胡赛尼', '上海人民出版社', 33, 30),
('1000000010', '小王子', '圣埃克苏佩里', '人民文学出版社', 20, 17),
('1000000012', '嫌疑人X的献身', '东野圭吾', '南海出版公司', 30, 27),
('1000000013', '百年孤独', '加西亚·马尔克斯', '南海出版公司', 38, 34),
('1000000014', '人类简史', '尤瓦尔·赫拉利', '中信出版社', 26, 24),
('1000000015', '解忧杂货店', '东野圭吾', '南海出版公司', 23, 20),
('1000000017', '月亮与六便士', '毛姆', '人民文学出版社', 29, 27),
('1000000018', '白鹿原', '陈忠实', '作家出版社', 27, 24),
('1000000019', '战争与和平', '列夫·托尔斯泰', '人民文学出版社', 32, 29),
('1000000020', '偷影子的人', '马克·李维', '北京联合出版公司', 19, 17),
('1000000021', '囚鸟', '冯内古特', '上海译文出版社', 24, 21),
('1000000022', '乌合之众', '古斯塔夫·勒庞', '中华书局', 28, 25),
('1000000023', '基督山伯爵', '大仲马', '上海译文出版社', 32, 29),
('1000000025', '金字塔原理', '芭芭拉·明托', '机械工业出版社', 20, 17),
('1000000026', '肖申克的救赎', '斯蒂芬·金', '人民文学出版社', 30, 27),
('1000000027', '福尔摩斯探案集', '阿瑟·柯南·道尔', '北京联合出版公司', 22, 19),
('1000000028', '非暴力沟通', '马维·罗森伯格', '中信出版社', 18, 15),
('1000000030', '霍乱时期的爱情', '加西亚·马尔克斯', '南海出版公司', 34, 30),
('1000000031', '时间简史', '斯蒂芬·霍金', '湖南科学技术出版社', 20, 17),
('1000000032', '人间失格', '太宰治', '筑摩书房', 25, 22),
('1000000033', '飘', '玛格丽特·米切尔', '上海人民出版社', 28, 25),
('1000000034', '月亮与六便士', '毛姆', '人民文学出版社', 33, 30),
('1000000035', '杀死一只知更鸟', '哈珀·李', '译林出版社', 24, 21),
('1000000036', '黄金时代', '王小波', '作家出版社', 27, 24),
('1000000038', '盗墓笔记', '南派三叔', '民主与建设出版社', 30, 27)

触发器

借书还书操作通过触发器来设计的，借阅记录表是Records、储存当前借出的书籍的表是Lending

当用户借书时，

当用户还书时，

GO
CREATE TRIGGER when_lend ON RecordsINSTEAD OF INSERT 
AS 
DECLARE @insertUID char(10),@insertBID char(10),@rest INT,@time datetime
SELECT @insertUID=u_id,@insertBID=book_id
FROM inserted
SELECT @rest=lendable
FROM Books
WHERE book_id=@insertBID
IF @rest<=0
BEGIN
PRINT('该书不足')
END
ELSE
BEGIN
SET	@time=getdate()
INSERT INTO Records(u_id,book_id,lend_time)
SELECT	u_id,book_id,@time
FROM inserted
DECLARE @RID INT
SET @RID = SCOPE_IDENTITY()
INSERT INTO Lending
VALUES (@RID,@insertUID,@insertBID,@time)
UPDATE Books
SET lendable=lendable-1
WHERE book_id=@insertBID
ENDGO
CREATE TRIGGER when_return ON LendingAFTER DELETE
AS 
DECLARE @UID char(10),@BID char(10),@RID INT,@time datetime
SELECT @RID=Record_id,@UID=u_id,@BID=book_id
FROM deleted
BEGIN
SET	@time=getdate()
UPDATE Records
SET return_time=@time
WHERE Record_id=@RID
UPDATE Books
SET lendable=lendable+1
WHERE book_id=@BID
END

操作与用户界面python

首页

import pymssql
from tkinter import ttk
import tkinter as tk
import tkinter.font as tkFont
from tkinter import * # 图形界面库
import tkinter.messagebox as messagebox # 弹窗class StartPage:def __init__(self, parent_window):parent_window.destroy() # 销毁子界面self.window = tk.Tk()  # 初始框的声明self.window.title('图书借阅管理系统')self.window.geometry('600x400') # 这里的乘是小xlabel = Label(self.window, text="图书借阅管理系统", font=("Verdana", 20))label.pack(pady=80) # 界面的长度Button(self.window, text="用户登陆", font=tkFont.Font(size=16), command = lambda: UserLogin(self.window), width=30,\height=2,fg='white', bg='gray', activebackground='black', activeforeground='white').pack()Button(self.window, text="注册", font=tkFont.Font(size=16), command = lambda: Register(), width=30, height=2,\fg='white', bg='gray', activebackground='black', activeforeground='white').pack()Button(self.window, text='退出系统', height=2, font=tkFont.Font(size=16), width=30, command=self.window.destroy,\fg='white', bg='gray', activebackground='black', activeforeground='white').pack()self.window.mainloop() # 主消息循环

注册界面

class Register:def __init__(self):self.window = tk.Tk()self.window.title('注册')self.window.geometry('300x450')label = tk.Label(self.window, text='用户注册', bg='green', font=('Verdana', 20), width=30, height=2)label.pack()Label(self.window, text='请输入账号(长度为10)', font=tkFont.Font(size=14)).pack(pady=10)self.u_id = tk.Entry(self.window, width=30, font=tkFont.Font(size=14), bg='Ivory')self.u_id.pack()Label(self.window, text='请设置密码：', font=tkFont.Font(size=14)).pack(pady=10)self.u_password = tk.Entry(self.window, width=30, font=tkFont.Font(size=14), bg='Ivory')self.u_password.pack()Label(self.window, text='请输入姓名：', font=tkFont.Font(size=14)).pack(pady=10)self.u_name = tk.Entry(self.window, width=30, font=tkFont.Font(size=14), bg='Ivory')self.u_name.pack()Label(self.window, text='请输入电话：', font=tkFont.Font(size=14)).pack(pady=10)self.u_phone = tk.Entry(self.window, width=30, font=tkFont.Font(size=14), bg='Ivory')self.u_phone.pack()Button(self.window, text="注册", width=8, font=tkFont.Font(size=12), command=self.register).pack(pady=40)self.window.mainloop()  # 进入消息循环def register(self):db = pymssql.connect(server="localhost",database="LibraryManage", charset='utf8')  # 打开数据库连接cursor = db.cursor()  # 使用cursor()方法获取操作游标sql = "SELECT * FROM Users WHERE u_id = '%s'" % (self.u_id.get())  # SQL 查询语句cursor.execute(sql)results = cursor.fetchall()if len(self.u_id.get()) != 10:messagebox.showinfo('警告！', '请输入十位数字')return Noneif len(list(results)) != 0:messagebox.showinfo('警告！', '账号已被注册')return Nonetry:sql = "INSERT INTO Users(u_id, u_password, u_name, user_phone) VALUES('%s','%s','%s','%s')" \%(self.u_id.get(), self.u_password.get(), self.u_name.get(), self.u_phone.get())cursor.execute(sql)db.commit()messagebox.showinfo('提示！', '注册成功！')self.window.destroy()except:messagebox.showinfo('警告！', '出错了')

登录界面

#登陆页面
class UserLogin:def __init__(self, parent_window):parent_window.destroy() # 销毁主界面self.window = tk.Tk()  # 初始框的声明self.window.title('学生登陆')self.window.geometry('300x450')  # 这里的乘是小xlabel = tk.Label(self.window, text='学生登陆', bg='green', font=('Verdana', 20), width=30, height=2)label.pack()Label(self.window, text='账号：', font=tkFont.Font(size=14)).pack(pady=25)self.u_id = tk.Entry(self.window, width=30, font=tkFont.Font(size=14), bg='Ivory')self.u_id.pack()Label(self.window, text='密码：', font=tkFont.Font(size=14)).pack(pady=25)self.u_password = tk.Entry(self.window, width=30, font=tkFont.Font(size=14), bg='Ivory', show='*')self.u_password.pack()Button(self.window, text="登陆", width=8, font=tkFont.Font(size=12), command=self.login).pack(pady=40)Button(self.window, text="返回首页", width=8, font=tkFont.Font(size=12), command=self.back).pack()self.window.protocol("WM_DELETE_WINDOW", self.back)  # 捕捉右上角关闭点击self.window.mainloop()  # 进入消息循环def login(self):stu_pass = None# 数据库操作 查询管理员表db = pymssql.connect(server="localhost",database="LibraryManage", charset='utf8')  # 打开数据库连接cursor = db.cursor()  # 使用cursor()方法获取操作游标sql = "SELECT * FROM Users WHERE u_id = '%s'" % (self.u_id.get())  # SQL 查询语句try:# 执行SQL语句cursor.execute(sql)# 获取所有记录列表u_password = Noneresults = cursor.fetchall()for row in results:u_id = row[0]u_password = row[1]u_Admin = row[2]u_name = row[3].encode("l1").decode("GBK")except:messagebox.showinfo('警告！', '用户名或密码不正确！')db.close()  # 关闭数据库连接if self.u_password.get() == u_password:UserMenu(self.window, u_id, u_name,u_Admin) # 进入学生信息查看界面else:messagebox.showinfo('警告！', '用户名或密码不正确！')def back(self):StartPage(self.window)  # 显示主窗口 销毁本窗口

用户界面

#学生界面
class UserMenu:def __init__(self, parent_window, u_id, u_name, Admin = 0):parent_window.destroy() # 销毁主界面self.u_id = u_idself.u_name = u_nameself.Admin = Adminself.window = Tk()  # 初始框的声明self.window.title('用户操作界面')#———————————————————————————————————————————————————————————中心列表区域———————————————————————————————————————————self.frame_center = tk.Frame(width=800, height=400)# 定义下方中心列表区域self.center_title = Label(self.frame_center, text="借阅的书", font=('Verdana', 20))self.center_title.grid(row = 0)  # 位置设置 self.columns = ("图书编号", "书名", "作者", "出版社", "借阅时间")self.tree = ttk.Treeview(self.frame_center, show="headings", height=18, columns=self.columns)self.vbar = ttk.Scrollbar(self.frame_center, orient=VERTICAL, command=self.tree.yview)# 定义树形结构与滚动条self.tree.configure(yscrollcommand=self.vbar.set)# 表格的标题self.tree.column("图书编号", width=100, anchor='center')  # 表示列,不显示self.tree.column("书名", width=200, anchor='center')self.tree.column("作者", width=100, anchor='center')self.tree.column("出版社", width=100, anchor='center')self.tree.column("借阅时间", width=200, anchor='center')# 调用方法获取表格内容插入self.tree.grid(row=1, column=0, sticky=NSEW)self.vbar.grid(row=1, column=1, sticky=NS)self.book_id = []self.book_name = []self.auther = []self.press = []self.lend_time = []# 打开数据库连接try:db = pymssql.connect(server="localhost",database="LibraryManage")cursor = db.cursor()  # 使用cursor()方法获取操作游标sql = """SELECT Books.book_id, book_name, author, press, lend_timeFROM Books, LendingWHERE u_id = %s ANDLending.book_id = Books.book_id"""%(self.u_id)cursor.execute(sql)results = cursor.fetchall()self.lendable = len(results)for row in results:self.book_id.append(row[0])self.book_name.append(row[1].encode("l1").decode("GBK"))self.auther.append(row[2].encode("l1").decode("GBK"))self.press.append(row[3].encode("l1").decode("GBK"))self.lend_time.append(row[4])db.close()# 关闭数据库连接except:messagebox.showinfo('警告！', '数据库连接失败！')# 写入数据   n = len(self.book_id)for i in range(n): self.tree.insert('', i, values=(self.book_id[i], self.book_name[i], self.auther[i], self.press[i], self.lend_time[i]))for col in self.columns:  # 绑定函数，使表头可排序self.tree.heading(col, text=col,\command=lambda _col=col: self.tree_sort_column(self.tree, _col, False))       self.tree.bind('', self.click)self.frame_center.grid(row=1, column=0, columnspan=2, padx=4, pady=5)self.frame_center.grid_propagate(0)self.frame_center.tkraise() # 开始显示主菜单#————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————#——————————————————————————————————————————————————————————左侧借书区域—————————————————————————————————————————————————————self.frame_left_top = tk.Frame(width=300, height=200)self.top_title = Label(self.frame_left_top, text="用户信息:", font=('Verdana', 20))self.top_title.grid(row=0, columnspan=2, sticky=NSEW, padx=50, pady=10)# u_idself.left_top_u_id = Label(self.frame_left_top, text="用户编号:" + self.u_id, font=('Verdana', 15))self.left_top_u_id.grid(row=1)  # 位置设置# u_nameself.left_top_u_name = Label(self.frame_left_top, text="姓名：" +self.u_name , font=('Verdana', 15))self.left_top_u_name.grid(row=2)  # 位置设置# buttonself.left_top_button = ttk.Button(self.frame_left_top, text='借书', width=20, \command=lambda: Borrow(self.window, self.u_id, self.u_name, self.lendable, self.Admin))self.left_top_button.grid(row=3, padx=20, pady=10)self.frame_left_top.grid(row=0, padx=2, pady=5)self.frame_left_top.grid_propagate(0)self.frame_left_top.tkraise() # 开始显示主菜单#——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————#————————————————————————————————————————————————————————右侧还书区域—————————————————————————————————————————————————————————self.frame_right_top = tk.Frame(width=400, height=200)self.var_id = StringVar()  # 声明书号self.var_name = StringVar()# 书编号self.right_top_bid_label = Label(self.frame_right_top, text="书编号：", font=('Verdana', 15))self.right_top_bid_entry = Entry(self.frame_right_top, textvariable=self.var_id, font=('Verdana', 15))self.right_top_bid_label.grid(row=0, column=0)  # 位置设置self.right_top_bid_entry.grid(row=0, column=1)# 书名self.right_top_bname_label = Label(self.frame_right_top, text="书名：", font=('Verdana', 15))self.right_top_bname_entry = Entry(self.frame_right_top, textvariable=self.var_name, font=('Verdana', 15))self.right_top_bname_label.grid(row=1, column=0)  # 位置设置self.right_top_bname_entry.grid(row=1, column=1)#还书操作self.right_top_button = ttk.Button(self.frame_right_top, text='还书', width=20, command = self.delete)self.right_top_button.grid(row=2, padx=20, pady=10)if Admin:self.right_top_button = ttk.Button(self.frame_right_top, text='管理用户', width=20, command = lambda:Users(self.window))self.right_top_button.grid(row=3, padx=20, pady=10)self.right_top_button = ttk.Button(self.frame_right_top, text='查询记录', width=20, command = lambda:Records(self.window))self.right_top_button.grid(row=3,column = 1, padx=20, pady=10)self.frame_right_top.grid(row=0, column=1, padx=30, pady=30)self.frame_right_top.grid_propagate(0)self.frame_right_top.tkraise() # 开始显示主菜单#——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————self.window.protocol("WM_DELETE_WINDOW", self.back)  # 捕捉右上角关闭点击self.window.mainloop()  # 进入消息循环def click(self, event):self.col = self.tree.identify_column(event.x)  # 列self.row = self.tree.identify_row(event.y)  # 行self.row_info = self.tree.item(self.row, "values")self.var_id.set(self.row_info[0])self.var_name.set(self.row_info[1])self.right_top_bid_entry = Entry(self.frame_right_top, textvariable=self.var_id, font=('Verdana', 15))self.right_top_bname_entry = Entry(self.frame_right_top, textvariable=self.var_name, font=('Verdana', 15))def back(self):StartPage(self.window) # 显示主窗口 销毁本窗口def delete(self):res = messagebox.askyesnocancel('确认还书', "是否归还此书：《%s》 ？" %(self.var_name.get()))if res == True:try:db = pymssql.connect(server="localhost",database="LibraryManage", charset='utf8')  # 打开数据库连接cursor = db.cursor()  # 使用cursor()方法获取操作游标sql = "DELETE FROM Lending WHERE u_id = '%s' AND book_id = '%s'" %(self.u_id, self.var_id.get())  # SQL DELETE语句cursor.execute(sql)  # 执行sql语句db.commit()  # 提交到数据库执行messagebox.showinfo('提示！', '归还成功！')db.close()  # 关闭数据库连接UserMenu(self.window, self.u_id, self.u_name, self.Admin)except:messagebox.showinfo('警告！', '更新失败，数据库连接失败！')

借书界面

class Borrow:def __init__(self,parent_window, u_id, u_name, num, Admin):parent_window.destroy() # 销毁主界面self.num = numself.u_id = u_idself.u_name = u_nameself.Admin = Adminself.window = Tk()  # 初始框的声明self.window.title('借书')#———————————————————————————————————————————————————————————中心列表区域———————————————————————————————————————————self.frame_center = tk.Frame(width=800, height=400)# 定义下方中心列表区域self.center_title = Label(self.frame_center, text="选择图书", font=('Verdana', 20))self.center_title.grid(row = 0)  # 位置设置 self.columns = ("图书编号", "书名", "作者", "出版社", "剩余数量")self.tree = ttk.Treeview(self.frame_center, show="headings", height=18, columns=self.columns)self.vbar = ttk.Scrollbar(self.frame_center, orient=VERTICAL, command=self.tree.yview)# 定义树形结构与滚动条self.tree.configure(yscrollcommand=self.vbar.set)# 表格的标题self.tree.column("图书编号", width=100, anchor='center')  # 表示列,不显示self.tree.column("书名", width=200, anchor='center')self.tree.column("作者", width=100, anchor='center')self.tree.column("出版社", width=100, anchor='center')self.tree.column("剩余数量", width=100, anchor='center')# 调用方法获取表格内容插入self.tree.grid(row=1, column=0, sticky=NSEW)self.vbar.grid(row=1, column=1, sticky=NS)self.book_id = []self.book_name = []self.auther = []self.press = []self.lendable = []# 打开数据库连接try:db = pymssql.connect(server="localhost",database="LibraryManage")cursor = db.cursor()  # 使用cursor()方法获取操作游标sql = 'SELECT book_id, book_name, author, press, lendable FROM Books'cursor.execute(sql)results = cursor.fetchall()for row in results:self.book_id.append(row[0])self.book_name.append(row[1].encode("l1").decode("GBK"))self.auther.append(row[2].encode("l1").decode("GBK"))self.press.append(row[3].encode("l1").decode("GBK"))self.lendable.append(row[4])db.close()# 关闭数据库连接except:print("Error: unable to fetch data")messagebox.showinfo('警告！', '数据库连接失败！')# 写入数据   n = len(self.book_id)for i in range(n): self.tree.insert('', i, values=(self.book_id[i], self.book_name[i], self.auther[i], self.press[i], self.lendable[i]))for col in self.columns:  # 绑定函数，使表头可排序self.tree.heading(col, text=col,\command=lambda _col=col: self.tree_sort_column(self.tree, _col, False))       self.tree.bind('', self.click)self.frame_center.grid(row=1, column=0, columnspan=2, padx=4, pady=5)self.frame_center.grid_propagate(0)self.frame_center.tkraise() # 开始显示主菜单#————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————#——————————————————————————————————————————————————————————左侧借书区域—————————————————————————————————————————————————————self.frame_left_top = tk.Frame(width=300, height=200)self.top_title = Label(self.frame_left_top, text="用户信息:", font=('Verdana', 20))self.top_title.grid(row=0, columnspan=2, sticky=NSEW, padx=50, pady=10)# u_idself.left_top_u_id = Label(self.frame_left_top, text="用户编号:" + self.u_id, font=('Verdana', 15))self.left_top_u_id.grid(row=1)  # 位置设置# u_nameself.left_top_u_name = Label(self.frame_left_top, text="姓名：" +self.u_name , font=('Verdana', 15))self.left_top_u_name.grid(row=2)  # 位置设置# 可借数量self.left_top_u_name = Label(self.frame_left_top, text="当前借阅数： %s / 5" %(num) , font=('Verdana', 20))self.left_top_u_name.grid(row=3)  # 位置设置self.frame_left_top.grid(row=0, padx=2, pady=5)self.frame_left_top.grid_propagate(0)self.frame_left_top.tkraise() # 开始显示主菜单#——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————#————————————————————————————————————————————————————————右侧借书区域—————————————————————————————————————————————————————————self.frame_right_top = tk.Frame(width=400, height=200)self.var_id = StringVar()  # 声明书号self.var_name = StringVar()self.lendable_book = StringVar()# 书编号self.right_top_bid_label = Label(self.frame_right_top, text="书编号：", font=('Verdana', 15))self.right_top_bid_entry = Entry(self.frame_right_top, textvariable=self.var_id, font=('Verdana', 15))self.right_top_bid_label.grid(row=0, column=0)  # 位置设置self.right_top_bid_entry.grid(row=0, column=1)# 书名self.right_top_bname_label = Label(self.frame_right_top, text="书名：", font=('Verdana', 15))self.right_top_bname_entry = Entry(self.frame_right_top, textvariable=self.var_name, font=('Verdana', 15))self.right_top_bname_label.grid(row=1, column=0)  # 位置设置self.right_top_bname_entry.grid(row=1, column=1)#借书操作self.right_top_button = ttk.Button(self.frame_right_top, text='借书', width=20, command = self.insert)self.right_top_button.grid(row=2, padx=20, pady=10)self.frame_right_top.grid(row=0, column=1, padx=30, pady=30)self.frame_right_top.grid_propagate(0)self.frame_right_top.tkraise() # 开始显示主菜单#——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————self.window.protocol("WM_DELETE_WINDOW", self.back)  # 捕捉右上角关闭点击self.window.mainloop()  # 进入消息循环def click(self, event):self.col = self.tree.identify_column(event.x)  # 列self.row = self.tree.identify_row(event.y)  # 行self.row_info = self.tree.item(self.row, "values")self.var_id.set(self.row_info[0])self.var_name.set(self.row_info[1])self.lendable_book.set(self.row_info[4])self.right_top_bid_entry = Entry(self.frame_right_top, textvariable=self.var_id, font=('Verdana', 15))self.right_top_bname_entry = Entry(self.frame_right_top, textvariable=self.var_name, font=('Verdana', 15))def insert(self):if self.num >= 5:messagebox.showinfo('提示！', '达到最大借阅数量，请先归还书籍。')UserMenu(self.window, self.u_id, self.u_name)return Noneif int(self.lendable_book.get()) <= 0:messagebox.showinfo('提示！', '该书数量不足，换一本书吧！')return Noneres = messagebox.askyesnocancel('确认借书', "是借阅此书：《%s》 ？" %(self.var_name.get()))if res == True:try:db = pymssql.connect(server="localhost",database="LibraryManage", charset='utf8')  # 打开数据库连接cursor = db.cursor()  # 使用cursor()方法获取操作游标sql = "INSERT INTO Records(u_id,book_id) VALUES('%s', '%s')" %(self.u_id, self.var_id.get())  # SQL INSERT语句cursor.execute(sql)  # 执行sql语句db.commit()  # 提交到数据库执行messagebox.showinfo('提示！', '借阅成功！')db.close()  # 关闭数据库连接UserMenu(self.window, self.u_id, self.u_name, self.Admin)except:db.rollback()  # 发生错误时回滚messagebox.showinfo('警告！', '更新失败，数据库连接失败！')def back(self):UserMenu(self.window, self.u_id, self.u_name, self.Admin)

 管理用户

class Users:def __init__(self,parent_Window):parent_Window.destroy()self.window = Tk()  # 初始框的声明self.window.title('管理界面')#———————————————————————————————————————————————————————————中心列表区域———————————————————————————————————————————self.frame_center = tk.Frame(width=800, height=400)# 定义下方中心列表区域self.center_title = Label(self.frame_center, text="用户信息", font=('Verdana', 20))self.center_title.grid(row = 0)  # 位置设置 self.columns = ("用户ID", "姓名", "密码", "电话", "地址", "管理权限")self.tree = ttk.Treeview(self.frame_center, show="headings", height=18, columns=self.columns)self.vbar = ttk.Scrollbar(self.frame_center, orient=VERTICAL, command=self.tree.yview)# 定义树形结构与滚动条self.tree.configure(yscrollcommand=self.vbar.set)# 表格的标题self.tree.column("用户ID", width=100, anchor='center')  # 表示列,不显示self.tree.column("姓名", width=100, anchor='center')self.tree.column("密码", width=100, anchor='center')self.tree.column("电话", width=100, anchor='center')self.tree.column("地址", width=300, anchor='center')self.tree.column("管理权限", width=100, anchor='center')# 调用方法获取表格内容插入self.tree.grid(row=1, column=0, sticky=NSEW)self.vbar.grid(row=1, column=1, sticky=NS)self.u_id = []self.u_name = []self.u_password = []self.user_phone = []self.user_adress = []self.u_Admin = []# 打开数据库连接try:db = pymssql.connect(server="localhost",database="LibraryManage")cursor = db.cursor()  # 使用cursor()方法获取操作游标sql = 'SELECT u_id, u_password, u_name, user_phone, user_adress, u_Admin FROM Users'cursor.execute(sql)results = cursor.fetchall()for row in results:self.u_id.append(row[0])self.u_password.append(row[1].encode("l1").decode("GBK"))self.u_name.append(row[2].encode("l1").decode("GBK"))self.user_phone.append(row[3].encode("l1").decode("GBK"))self.user_adress.append(row[4])if row[5]:self.u_Admin.append('管理员')else:self.u_Admin.append('')db.close()# 关闭数据库连接except:print("Error: unable to fetch data")messagebox.showinfo('警告！', '数据库连接失败！')# 写入数据   n = len(self.u_id)for i in range(n): self.tree.insert('', i, values=(self.u_id[i], self.u_name[i], self.u_password[i], self.user_phone[i], \self.user_adress[i], self.u_Admin[i]))for col in self.columns:  # 绑定函数，使表头可排序self.tree.heading(col, text=col,\command=lambda _col=col: self.tree_sort_column(self.tree, _col, False))       self.tree.bind('', self.click)self.frame_center.grid(row=1, column=0, columnspan=2, padx=4, pady=5)self.frame_center.grid_propagate(0)self.frame_center.tkraise() # 开始显示主菜单#————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————#————————————————————————————————————————————————————————右侧还书区域—————————————————————————————————————————————————————————self.frame_right_top = tk.Frame(width=600, height=100)self.var_id = StringVar()  # 声明书号self.var_name = StringVar()# 书编号self.right_top_bid_label = Label(self.frame_right_top, text="用户ID：", font=('Verdana', 15))self.right_top_bid_entry = Entry(self.frame_right_top, textvariable=self.var_id, font=('Verdana', 15))self.right_top_bid_label.grid(row=0, column=0)  # 位置设置self.right_top_bid_entry.grid(row=0, column=1)# 书名self.right_top_bname_label = Label(self.frame_right_top, text="姓名：", font=('Verdana', 15))self.right_top_bname_entry = Entry(self.frame_right_top, textvariable=self.var_name, font=('Verdana', 15))self.right_top_bname_label.grid(row=1, column=0)  # 位置设置self.right_top_bname_entry.grid(row=1, column=1)#还书操作self.right_top_button = ttk.Button(self.frame_right_top, text='修改信息', width=20, command = self.update)self.right_top_button.grid(row=1, column=4, padx=20, pady=10)self.frame_right_top.grid(row=0, padx=30, pady=30)self.frame_right_top.grid_propagate(0)self.frame_right_top.tkraise() # 开始显示主菜单#——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————self.window.protocol("WM_DELETE_WINDOW", self.back)  # 捕捉右上角关闭点击    self.window.mainloop()  # 进入消息循环def click(self, event):self.col = self.tree.identify_column(event.x)  # 列self.row = self.tree.identify_row(event.y)  # 行self.row_info = self.tree.item(self.row, "values")self.var_id.set(self.row_info[0])self.var_name.set(self.row_info[1])self.right_top_bid_entry = Entry(self.frame_right_top, textvariable=self.var_id, font=('Verdana', 15))self.right_top_bname_entry = Entry(self.frame_right_top, textvariable=self.var_name, font=('Verdana', 15))def update(self):self.update_window = tk.Tk()self.update_window.title('修改')self.update_window.geometry('600x150')Label(self.update_window, text='设置密码', font=tkFont.Font(size=14)).grid(row = 0, column = 0)self.new_password = tk.Entry(self.update_window, width=30, font=tkFont.Font(size=14), bg='Ivory')self.new_password.grid(row = 0, column = 1)Label(self.update_window, text='设置电话', font=tkFont.Font(size=14)).grid(row = 1, column = 0)self.new_phone = tk.Entry(self.update_window, width=30, font=tkFont.Font(size=14), bg='Ivory')self.new_phone.grid(row = 1, column = 1)Label(self.update_window, text='设置地址', font=tkFont.Font(size=14)).grid(row = 2, column = 0)self.new_adress = tk.Entry(self.update_window, width=30, font=tkFont.Font(size=14), bg='Ivory')self.new_adress.grid(row = 2, column = 1)Label(self.update_window, text='是否设为管理员（0或1）', font=tkFont.Font(size=14)).grid(row = 3, column = 0)self.new_Admin = tk.Entry(self.update_window, width=30, font=tkFont.Font(size=14), bg='Ivory')self.new_Admin.grid(row = 3, column = 1)Button(self.update_window, text="修改", width=40, font=tkFont.Font(size=15), \command=self.comfirm).grid(row = 4, column = 0, columnspan = 2)self.update_window.mainloop()def comfirm(self):res = messagebox.askyesnocancel('确认修改', "确认修改 ？")if res:db = pymssql.connect(server="localhost",database="LibraryManage")cursor = db.cursor()  # 使用cursor()方法获取操作游标if self.new_password.get() != '':sql = "UPDATE Users SET u_password = '%s' WHERE u_id = '%s'" %(self.new_password.get(), self.var_id.get())cursor.execute(sql)  # 执行sql语句db.commit()  # 提交到数据库执行if self.new_phone.get() != '':sql = "UPDATE Users SET user_phone = '%s' WHERE u_id = '%s'" %(self.new_phone.get(), self.var_id.get())cursor.execute(sql)  # 执行sql语句db.commit()  # 提交到数据库执行if self.new_adress.get() != '':sql = "UPDATE Users SET user_adress = '%s' WHERE u_id = '%s'" %(self.new_adress.get(), self.var_id.get())cursor.execute(sql)  # 执行sql语句db.commit()  # 提交到数据库执行if self.new_Admin.get() != '':sql = "UPDATE Users SET u_Admin = '%s' WHERE u_id = '%s'" %(self.new_Admin.get(), self.var_id.get())cursor.execute(sql)  # 执行sql语句db.commit()  # 提交到数据库执行messagebox.showinfo('提示！', '修改成功！')self.update_window.destroy()Users(self.window)def back(self):StartPage(self.window)

 查询记录

class Records:def __init__(self,parent_window):parent_window.destroy()self.window = Tk()  # 初始框的声明self.window.title('借阅记录')#———————————————————————————————————————————————————————————中心列表区域———————————————————————————————————————————self.frame_center = tk.Frame(width=900, height=400)# 定义下方中心列表区域self.center_title = Label(self.frame_center, text="借阅记录", font=('Verdana', 20))self.center_title.grid(row = 0)  # 位置设置 self.columns = ("记录ID", "用户ID", "姓名", "书ID", "书名", "借阅日期", "还书日期")self.tree = ttk.Treeview(self.frame_center, show="headings", height=18, columns=self.columns)self.vbar = ttk.Scrollbar(self.frame_center, orient=VERTICAL, command=self.tree.yview)# 定义树形结构与滚动条self.tree.configure(yscrollcommand=self.vbar.set)# 表格的标题self.tree.column("记录ID", width=100, anchor='center')self.tree.column("用户ID", width=100, anchor='center')  # 表示列,不显示self.tree.column("姓名", width=100, anchor='center')self.tree.column("书ID", width=100, anchor='center')self.tree.column("书名", width=100, anchor='center')self.tree.column("借阅日期", width=200, anchor='center')self.tree.column("还书日期", width=200, anchor='center')# 调用方法获取表格内容插入self.tree.grid(row=1, column=0, sticky=NSEW)self.vbar.grid(row=1, column=1, sticky=NS)self.u_id = []self.u_name = []self.book_id = []self.book_name = []self.Record_id = []self.lend_time = []self.return_time = []# 打开数据库连接try:db = pymssql.connect(server="localhost",database="LibraryManage")cursor = db.cursor()  # 使用cursor()方法获取操作游标sql = """SELECT Record_id, Users.u_id, u_name, Books.book_id, book_name, lend_time, return_timeFROM Records,Users,BooksWHERE Records.u_id = Users.u_id ANDBooks.book_id = Records.book_id"""cursor.execute(sql)results = cursor.fetchall()for row in results:self.Record_id.append(row[0])self.u_id.append(row[1])self.u_name.append(row[2].encode("l1").decode("GBK"))self.book_id.append(row[3])self.book_name.append(row[4].encode("l1").decode("GBK"))self.lend_time.append(row[5])self.return_time.append(row[6])db.close()# 关闭数据库连接except:print("Error: unable to fetch data")messagebox.showinfo('警告！', '数据库连接失败！')# 写入数据   n = len(self.u_id)for i in range(n): self.tree.insert('', i, values=(self.Record_id[i], self.u_id[i], self.u_name[i], self.book_id[i], \self.book_name[i], self.lend_time[i],self.return_time[i]))for col in self.columns:  # 绑定函数，使表头可排序self.tree.heading(col, text=col,\command=lambda _col=col: self.tree_sort_column(self.tree, _col, False))       self.frame_center.grid(row=1, column=0, columnspan=2, padx=4, pady=5)self.frame_center.grid_propagate(0)self.frame_center.tkraise() # 开始显示主菜单self.window.protocol("WM_DELETE_WINDOW", self.back)  # 捕捉右上角关闭点击self.window.mainloop()  # 进入消息循环def back(self):StartPage(self.window) # 显示主窗口 销毁本窗口if __name__ == '__main__':window = tk.Tk()StartPage(window)

 总体就是这样，将以上所有的python代码全放到一起就可以运行，想要运行首先要构建数据库，并打开，还要安装包，需要全部内容的例如PPT，可以加我QQ1486426078

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！