基于 Python 和 MySQL 的图书管理系统设计与实现

（二）图书业务管理模块（BookManager）

核心功能：图书增删、借阅归还，精简核心代码如下：

from datetime import datetime, timedelta

class BookManager:
    def __init__(self, db):
        self.db = db

    def add_book(self, title, author, isbn, stock=1):
        """添加图书"""
        sql = "INSERT INTO books (title, author, isbn, stock, available) VALUES (%s,%s,%s,%s,%s)"
        self.db.execute(sql, (title, author, isbn, stock, stock))

    def borrow_book(self, book_id, borrower_name):
        """借书：校验库存 + 更新可借数量 + 生成记录"""
        # 校验可借数量
        book = self.db.execute("SELECT available FROM books WHERE id=%s", (book_id,), fetch=True)
        if not book or book[0][0] <= 0:
            return False
        # 生成借阅记录
        borrow_date = datetime.now().date()
        return_date = borrow_date + timedelta(days=30)
        self.db.execute("INSERT INTO borrow_records (book_id, borrower_name, borrow_date, return_date) VALUES (%s,%s,%s,%s)",
                        (book_id, borrower_name, borrow_date, return_date))
        # 更新可借数量
        self.db.execute("UPDATE books SET available = available -1 WHERE id=%s", (book_id,))
        return True

    def return_book(self, record_id):
        """还书：更新状态 + 恢复可借数量"""
        # 获取图书 ID
        book_id = self.db.execute("SELECT book_id FROM borrow_records WHERE id=%s", (record_id,), fetch=True)[0][0]
        # 更新借阅状态
        self.db.execute("UPDATE borrow_records SET status='已还', actual_return_date=%s WHERE id=%s",
                        (datetime.now().date(), record_id))
        # 恢复可借数量
        self.db.execute("UPDATE books SET available = available +1 WHERE id=%s", (book_id,))
        return True

（三）GUI 界面模块（LibraryApp）

核心功能：搭建三大标签页（图书管理、借阅管理、统计分析），精简核心代码如下：

import tkinter as tk
from tkinter import ttk, messagebox

class LibraryApp:
    def __init__(self, root):
        self.root = root
        self.root.title("图书管理系统")
        self.root.geometry("1000x700")
        # 初始化数据库
        self.db_config = {
            "host": "localhost",
            "user": "root",
            "password": "root",
            "database": "library_system"
        }
        self.db = DatabaseManager(self.db_config)
        self.book_manager = BookManager(self.db)
        # 搭建标签页
        self.notebook = ttk.Notebook(root)
        self.notebook.pack(fill="both", expand=True)
        # 三个核心标签页
        ttk.Frame(self.notebook, name="book_frame")
        ttk.Frame(self.notebook, name="borrow_frame")
        ttk.Frame(self.notebook, name="stats_frame")
        self.notebook.add(self.root.nametowidget("book_frame"), text="图书管理")
        self.notebook.add(self.root.nametowidget("borrow_frame"), text="借阅管理")
        self.notebook.add(self.root.nametowidget("stats_frame"), text="统计分析")
        # 图书管理界面（精简版）
        self._setup_book_ui()

    def _setup_book_ui(self):
        """图书管理界面：搜索 + 列表 + 添加按钮"""
        frame = self.root.nametowidget("book_frame")
        # 搜索框
        tk.Entry(frame, name="search_entry").pack(fill="x", padx=10, pady=5)
        # 图书列表
        tree = ttk.Treeview(frame, columns=("ID","书名","作者"), show="headings")
        tree.heading("ID", text="ID")
        tree.heading("书名", text="书名")
        tree.heading("作者", text="作者")
        tree.pack(fill="both", expand=True, padx=10, pady=5)
        # 添加按钮
        tk.Button(frame, text="添加图书", command=self._show_add_book).pack(pady=5)

    def _show_add_book(self):
        """显示添加图书弹窗（简易版）"""
        # 此处省略表单细节，核心是调用 book_manager.add_book
        messagebox.showinfo("提示", "打开添加图书表单")

主函数（程序启动）

def main():
    root = tk.Tk()
    LibraryApp(root)
    root.mainloop()

if __name__ == "__main__":
    main()

四、系统核心特性

1. 高可用性：自动建库建表，首次启动即可使用
2. 数据一致性：借阅/归还自动同步可借数量，避免数据混乱
3. 操作友好：界面布局清晰，关键操作有提示
4. 可扩展性强：分层架构，便于后续扩展功能

五、系统运行步骤

1. 环境准备：安装 Python 3.8+、PyMySQL、启动 MySQL 服务
2. 配置修改：修改 db_config 中的 MySQL 密码
3. 启动程序：直接运行代码，自动创建数据库和表
4. 功能使用：进行图书添加、借阅、归还等操作

六、全部实现源代码

1. 创建数据库

-- 创建数据库
CREATE DATABASE IF NOT EXISTS library_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 使用数据库
USE library_system;

程序会自动创建表结构，无需手动建表

2. 修改数据库配置 在代码中修改这部分配置：

self.db_config = {
    "host": "localhost",      # 数据库主机
    "user": "root",           # 数据库用户名
    "password": "your_password",# 你的数据库密码，按照自己的 MySQL 数据用户名和密码填写
    "database": "library_system",# 数据库名称
    "port": 3306,             # 端口号
    "charset": "utf8mb4",     # 字符集
}

3. 全部程序源代码：

import tkinter as tk
from tkinter import messagebox, ttk
from datetime import datetime, timedelta
import pymysql
from typing import Dict, List, Optional, Tuple
from dataclasses import dataclass
import threading

@dataclass
class Book:
    """图书数据类"""
    id: Optional[int] = None
    title: str = ""
    author: str = ""
    isbn: str = ""
    category: str = ""
    publisher: str = ""
    publish_date: str = ""
    price: float = 0.0
    stock: int = 0
    available: int = 0
    status: str = "正常"

@dataclass
class BorrowRecord:
    """借阅记录数据类"""
    id: Optional[int] = None
    book_id: int = 0
    book_title: str = ""
    borrower_name: str = ""
    borrower_phone: str = ""
    borrow_date: str = ""
    return_date: str = ""
    actual_return_date: str = ""
    status: str = "借出"

class DatabaseManager:
    """数据库管理类"""
    def __init__(self, config: Dict):
        self.config = config
        self.conn = None
        self.ensure_connection()
        self.init_database()

    def ensure_connection(self) -> None:
        """确保数据库连接"""
        try:
            if self.conn and self.conn.open:
                try:
                    self.conn.ping(reconnect=False)
                    return
                except:
                    pass
        except:
            pass
        try:
            # 先连接到 MySQL，如果数据库不存在则创建
            temp_config = self.config.copy()
            database = temp_config.pop('database')
            # 先用默认数据库连接，创建目标数据库
            temp_config['database'] = 'mysql'
            temp_conn = pymysql.connect(**temp_config)
            with temp_conn.cursor() as cursor:
                cursor.execute(f"CREATE DATABASE IF NOT EXISTS {database} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            temp_conn.commit()
            temp_conn.close()
            # 现在用真实的配置连接
            self.config['database'] = database
            self.conn = pymysql.connect(**self.config)
            print("数据库连接成功")
        except Exception as e:
            print(f"数据库连接失败：{e}")
            raise

    def init_database(self) -> None:
        """初始化数据库表"""
        try:
            with self.conn.cursor() as cursor:
                # 先删除旧表（可选，用于完全重置）
                # cursor.execute("DROP TABLE IF EXISTS borrow_records")
                # cursor.execute("DROP TABLE IF EXISTS books")
                # cursor.execute("DROP TABLE IF EXISTS categories")
                # 创建图书表
                cursor.execute(""" CREATE TABLE IF NOT EXISTS books (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    title VARCHAR(200) NOT NULL,
                    author VARCHAR(100) NOT NULL,
                    isbn VARCHAR(20) UNIQUE KEY,
                    category VARCHAR(50) NOT NULL,
                    publisher VARCHAR(100),
                    publish_date DATE,
                    price DECIMAL(10,2) DEFAULT 0.00,
                    stock INT DEFAULT 0,
                    available INT DEFAULT 0,
                    status VARCHAR(20) DEFAULT '正常',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX idx_category (category),
                    INDEX idx_status (status)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """)
                print("图书表创建成功")
                # 创建借阅记录表
                cursor.execute(""" CREATE TABLE IF NOT EXISTS borrow_records (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    book_id INT NOT NULL,
                    borrower_name VARCHAR(50) NOT NULL,
                    borrower_phone VARCHAR(20),
                    borrow_date DATE NOT NULL,
                    return_date DATE NOT NULL,
                    actual_return_date DATE,
                    status VARCHAR(20) DEFAULT '借出',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
                    INDEX idx_book_id (book_id),
                    INDEX idx_status (status),
                    INDEX idx_borrower (borrower_name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """)
                print("借阅记录表创建成功")
                # 创建图书分类表
                cursor.execute(""" CREATE TABLE IF NOT EXISTS categories (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(50) UNIQUE NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_name (name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """)
                print("分类表创建成功")
                self.conn.commit()
                self.init_sample_data()
                print("数据库表初始化完成")
        except pymysql.err.ProgrammingError as e:
            if "already exists" in str(e):
                print("表已存在，跳过创建")
                self.init_sample_data()
            else:
                print(f"数据库初始化失败：{e}")
                raise
        except Exception as e:
            print(f"数据库初始化失败：{e}")
            raise

    def init_sample_data(self) -> None:
        """初始化示例数据"""
        try:
            with self.conn.cursor() as cursor:
                # 检查是否已有数据
                cursor.execute("SELECT COUNT(*) FROM books")
                if cursor.fetchone()[0] > 0:
                    print("示例数据已存在，跳过初始化")
                    return
                # 初始化分类
                categories = ["计算机科学","文学","历史","哲学","数学","物理","化学","生物","经济学","管理学"]
                for category in categories:
                    try:
                        cursor.execute("INSERT INTO categories (name) VALUES (%s)", (category,))
                    except pymysql.err.IntegrityError:
                        pass
                # 分类已存在
                # 初始化图书数据
                books = [("Python 编程从入门到实践","埃里克·马瑟斯","9787115428028","计算机科学","人民邮电出版社","2016-07-01",89.00,5),("算法导论","Thomas H. Cormen","9787111407010","计算机科学","机械工业出版社","2012-12-01",128.00,3),("深度学习","Ian Goodfellow","9787115461476","计算机科学","人民邮电出版社","2017-07-01",168.00,2),("JavaScript 高级程序设计","Matt Frisbie","9787115583864","计算机科学","人民邮电出版社","2020-09-01",139.00,4),("设计模式","Gang of Four","9787111075776","计算机科学","机械工业出版社","2000-09-01",45.00,3),("红楼梦","曹雪芹","9787020002207","文学","人民文学出版社","1996-12-01",39.50,10),("三国演义","罗贯中","9787020008735")