一个简单的图书管理系统（课程设计）

pycharm图书管理系统的设计，要用MySQL建立一个名为tushuguanli的数据库并在·数据库中建立三个表分别为books和borrow_books和 用户信息 的表
并把密码改为自己的密码




pycharm代码

import tkinter as tk
from tkinter import messagebox


import mysql.connector

class LoginWindow:  #创建第一个库11111
    def __init__(self, root):
        self.root = root
        self.root.title("登录")

        # 创建标签和输入框
        tk.Label(root, text="用户名").grid(row=0, column=0)
        tk.Label(root, text="密码").grid(row=1, column=0)
        self.username_entry = tk.Entry(root)                 #用户名输入框
        self.password_entry = tk.Entry(root, show="*")       #密码输入框带星号
        self.username_entry.grid(row=0, column=1)            #用grid放在第0行1列
        self.password_entry.grid(row=1, column=1)            #用grid放在第1行1列

        # 创建按钮
        self.login_button = tk.Button(root, text="登录", command=self.login)
        self.login_button.grid(row=2, column=0, columnspan=2)   #登录按钮2行0列占两列

    def login(self):
        username = self.username_entry.get()
        password = self.password_entry.get()    #用户名和密码从输入框输入

        # 检查用户名和密码
        if username == "admin" and password == "admin":
            messagebox.showinfo("登录成功", "管理员登录成功")
            self.open_book_app(is_admin=True)
        elif username == "user" and password == "user":
            messagebox.showinfo("登录成功", "用户登录成功")
            self.open_library_app()
        else:
            messagebox.showerror("登录失败", "用户名或密码错误")


    def open_library_app(self):
        self.root.destroy()     #关闭销毁root窗口
        root = tk.Tk()          #创建了一个新的Tkinter窗口
        app = LibraryApp(root)  #root传递
        root.mainloop()         #启动了Tkinter的事件循环

    def open_book_app(self, is_admin=True):
        self.root.destroy()
        root = tk.Tk()
        app = BookManagerApp(root, is_admin=is_admin)
        root.mainloop()

class LibraryApp:
        def __init__(self, root,is_admin=False):
            self.root = root
            self.is_admin = is_admin
            self.root.title("图书借阅系统")

            # 创建数据库连接和游标
            self.db = mysql.connector.connect(
                host="localhost",
                user="root",
                password="在里面输入自己的密码",
                database="tushuguanli"
            )
            self.cursor = self.db.cursor()

            # 创建标签和输入框
            tk.Label(root, text="书名").grid(row=0, column=0)
            tk.Label(root, text="作者").grid(row=1, column=0)
            tk.Label(root, text="姓名").grid(row=2, column=0)
            tk.Label(root, text="电话号码").grid(row=3, column=0)
            self.title_entry = tk.Entry(root)
            self.author_entry = tk.Entry(root)
            self.name_entry = tk.Entry(root)
            self.phone_entry = tk.Entry(root)
            self.title_entry.grid(row=0, column=1)
            self.author_entry.grid(row=1, column=1)
            self.name_entry.grid(row=2, column=1)
            self.phone_entry.grid(row=3, column=1)

            # 创建按钮
            self.borrow_button = tk.Button(root, text="查询书籍", command=self.search)
            self.borrow_button.grid(row=6, column=0, columnspan=2)

            self.borrow_button = tk.Button(root, text="借阅书籍", command=self.borrow_book)
            self.borrow_button.grid(row=7, column=0, columnspan=2)

            self.return_button = tk.Button(root, text="归还书籍", command=self.return_book)
            self.return_button.grid(row=8, column=0, columnspan=2)

        def search(self):
            title = self.title_entry.get()
            if not title:
                messagebox.showerror("错误", "请输入要查询的书籍名称")
                return
            query = "SELECT * FROM books WHERE title = %s"
            self.cursor.execute(query, (title,))
            result = self.cursor.fetchall()
            if result:
                book_title = result[0][1]
                book_author = result[0][2]
                book_summary = result[0][3]
                book_location = result[0][4]
                messagebox.showinfo("查询结果",
                                    f"书名: {book_title}\n作者: {book_author}\n简介: {book_summary}\n位置: {book_location}")
            else:
                messagebox.showinfo("查询结果", "未找到该书籍")

        def borrow_book(self):
            title = self.title_entry.get()
            author = self.author_entry.get()
            name = self.name_entry.get()
            phone = self.phone_entry.get()

            if not (title and author and name and phone):
                messagebox.showerror("错误", "请填写完整信息")
                return

            if not title:
                messagebox.showerror("错误", "请输入要借阅的书籍名称")
                return

            # 检查书籍是否存在
            book_exists_query = "SELECT * FROM books WHERE title = %s"
            self.cursor.execute(book_exists_query, (title,))    #建立游标
            book = self.cursor.fetchone()                              #fetchone方法获取结果

            if not book:
                messagebox.showerror("错误", "书籍不存在")
                return

            # 从表books中查询特定书名的数据并插入到表borrow_books中
            insert_query = "INSERT INTO borrow_books (title, author, summary, location) " \
                           "SELECT title, author, summary, location FROM books WHERE title = %s"
            self.cursor.execute(insert_query, (title,))

            # 删除books表中的对应书籍
            book_query = "DELETE FROM books WHERE title = %s"
            self.cursor.execute(book_query, (title,))

            # 插入用户信息
            user_query = "INSERT INTO 用户信息 (name, phone) VALUES (%s, %s)"
            self.cursor.execute(user_query, (name, phone))  #建立游标

            # 提交事务
            self.db.commit()

            messagebox.showinfo("成功", "成功借阅书籍")
        def return_book(self):
            title = self.title_entry.get()  # 从界面中获取用户输入的书名
            author = self.author_entry.get()  # 从界面中获取用户输入的作者名
            name = self.name_entry.get()  # 从界面中获取用户输入的名字
            phone = self.phone_entry.get()  # 从界面中获取用户输入的电话号码
            if not (title and author and name and phone):
                messagebox.showerror("错误", "请填写完整信息")
                return
                # 检查用户信息是否存在
            user_exists_query = "SELECT * FROM 用户信息 WHERE name = %s AND phone = %s"
            self.cursor.execute(user_exists_query, (name,phone))
            user = self.cursor.fetchone()
            if not user:
                messagebox.showerror("错误", "用户信息不存在")
                return
            book_exists_query = "SELECT * FROM books WHERE title = %s"
            self.cursor.execute(book_exists_query, (title,))
            book = self.cursor.fetchone()
            if book:
                messagebox.showerror("错误", "书籍已存在库中")
                return
                # 从表borrow_books中查询特定书名的数据并插入到表books中
            insert_query = "INSERT INTO books (title, author, summary, location)" \
                           "SELECT title, author, summary, location FROM borrow_books WHERE title = %s"
            self.cursor.execute(insert_query, (title,))

            user_query = "DELETE FROM 用户信息 WHERE name = %s AND phone = %s"  # 构建一个SQL插入语句，向名为用户信息的表中去除用户信息

            delete_query = "DELETE FROM borrow_books WHERE title = %s AND author = %s AND summary = %s AND location = %s"  # 构建一个SQL插入语句，向名为borrow_books的表中去除用借书信息
            try:

                self.cursor.execute(user_query, (name,phone))  # 执行SQL查询，将占位符%s替换为实际的书名，并执行插入操作
                self.db.commit()  # 提交保存
                messagebox.showinfo("成功", "书籍已归还")  # 弹出消息
            except Exception as e:
                messagebox.showinfo("错误",f"操作失败:{str(e)}")


class BookManagerApp:   #第二个库
    def __init__(self, root, is_admin):
        self.root = root
        self.is_admin = is_admin
        if is_admin:
            self.root.title("图书管理系统 - 管理员")
        else:
            self.root.title("图书管理系统 - 用户")



        # 创建标签和输入框
        tk.Label(root, text="书名").grid(row=0, column=0)
        tk.Label(root, text="作者").grid(row=1, column=0)
        tk.Label(root, text="简介").grid(row=2, column=0)
        tk.Label(root, text="位置").grid(row=3, column=0)
        self.title_entry = tk.Entry(root)
        self.author_entry = tk.Entry(root)
        self.summary_entry = tk.Entry(root)
        self.location_entry = tk.Entry(root)
        self.title_entry.grid(row=0, column=1,sticky="nsew")    #居中
        self.author_entry.grid(row=1, column=1,sticky="nsew")
        self.summary_entry.grid(row=2, column=1,sticky="nsew")
        self.location_entry.grid(row=3, column=1,sticky="nsew")



        # 创建按钮
        self.add_book_button = tk.Button(root, text="添加书籍", command=self.add_book)
        self.add_book_button.grid(row=4, column=0, columnspan=2,sticky="nsew")

        self.delete_book_button = tk.Button(root, text="删除书籍", command=self.delete_book)
        self.delete_book_button.grid(row=5, column=0, columnspan=2,sticky="nsew")

        self.search_book_button = tk.Button(root, text="查询书籍", command=self.search_book)
        self.search_book_button.grid(row=6, column=0, columnspan=2,sticky="nsew")

        self.update_book_button = tk.Button(root, text="更新书籍", command=self.update_book)
        self.update_book_button.grid(row=7, column=0, columnspan=2,sticky="nsew")

        self.root.geometry("300x300")  # 设置窗口大小为宽400像素，高300像素

        # 连接到数据库
        self.db = mysql.connector.connect(
            host="localhost",
            user="root",
            password="在里面输入自己的密码",
            database="tushuguanli"
        )
        self.cursor = self.db.cursor()

    def add_book(self):
        title = self.title_entry.get()  #从界面中获取用户输入的书名
        author = self.author_entry.get()    #从界面中获取用户输入的作者名
        summary = self.summary_entry.get()    #从界面中获取用户输入的简介
        location = self.location_entry.get()   #从界面中获取用户输入的位置
        if not all([title, author, summary, location]):
            messagebox.showerror("错误", "请填写完整信息")
            return
        query = "INSERT INTO books (title, author,summary,location) VALUES (%s, %s, %s, %s)" #构建一个SQL插入语句，向名为books的表中插入书名和作者名
        self.cursor.execute(query, (title, author,summary,location)) #执行SQL查询，将占位符%s替换为实际的书名和作者名，并执行插入操作
        self.db.commit()    #提交保存
        messagebox.showinfo("成功", "书籍已添加")  #弹出消息

    def delete_book(self):
        title = self.title_entry.get()
        if not title:
            messagebox.showerror("错误", "请输入要删除的书籍名称")
            return
        query = "DELETE FROM books WHERE title = %s"
        self.cursor.execute(query, (title,))
        self.db.commit()
        messagebox.showinfo("成功", "书籍已删除")


    def search_book(self):
            title = self.title_entry.get()
            if not title:
                messagebox.showerror("错误", "请输入要查询的书籍名称")
                return
            query = "SELECT * FROM books WHERE title = %s"
            self.cursor.execute(query, (title,))
            result = self.cursor.fetchall()
            if result:
                book_title = result[0][1]
                book_author = result[0][2]
                book_summary = result[0][3]  # 假设书籍介绍在第三列
                book_location = result[0][4]
                messagebox.showinfo("查询结果", f"书名: {book_title}\n作者: {book_author}\n简介: {book_summary}\n位置: {book_location}")
            else:
                messagebox.showinfo("查询结果", "未找到该书籍")

    def update_book(self):
        title = self.title_entry.get()
        author = self.author_entry.get()
        summary = self.summary_entry.get()  # 假设summary对应的Entry为self.summary_entry
        location = self.location_entry.get()  # 假设location对应的Entry为self.location_entry
        if not all([title, author, summary, location]):
            messagebox.showerror("错误", "请填写完整信息")
            return
        query = "UPDATE books SET author = %s, summary = %s, location = %s WHERE title = %s"
        self.cursor.execute(query, (author, summary, location, title))
        self.db.commit()
        messagebox.showinfo("成功", "书籍信息已更新")





if __name__ == "__main__":
    root = tk.Tk()
    login_window = LoginWindow(root)
    root.mainloop()

创建数据库和表

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    summary TEXT,
    location VARCHAR(255)
);

CREATE TABLE borrow_books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    summary TEXT,
    location VARCHAR(255)
);

CREATE TABLE 用户信息 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20)
);