Python怎么连接Mysql实现图书借阅系统

使用Python连接MySQL实现图书借阅系统，需要完成环境配置、数据库设计、连接封装、功能开发这几个核心步骤，下面逐步介绍完整的实现过程。

环境准备

首先需要安装必要的依赖库，这里使用pymysql作为Python连接MySQL的驱动，执行以下命令安装：

pip install pymysql

同时需要提前安装好MySQL数据库，并创建好用于存放系统数据的数据库，比如命名为library_db。

数据库表结构设计

图书借阅系统核心需要三张表，分别是用户表、图书表、借阅记录表，建表语句如下：

-- 创建用户表
CREATE TABLE user (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    phone VARCHAR(20)
);

-- 创建图书表
CREATE TABLE book (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    book_name VARCHAR(100) NOT NULL,
    author VARCHAR(50),
    total_num INT NOT NULL DEFAULT 1,
    remain_num INT NOT NULL DEFAULT 1
);

-- 创建借阅记录表
CREATE TABLE borrow_record (
    record_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    book_id INT NOT NULL,
    borrow_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    return_time DATETIME,
    status TINYINT NOT NULL DEFAULT 0 COMMENT '0未归还 1已归还',
    FOREIGN KEY (user_id) REFERENCES user(user_id),
    FOREIGN KEY (book_id) REFERENCES book(book_id)
);

MySQL连接封装

为了避免重复编写连接代码，我们可以封装一个数据库连接工具类，方便后续调用：

import pymysql

class DBUtil:
    def __init__(self, host='localhost', user='root', password='your_password', db='library_db', port=3306):
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        self.port = port
        self.conn = None
        self.cursor = None

    def get_conn(self):
        # 建立数据库连接
        self.conn = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            db=self.db,
            port=self.port,
            charset='utf8mb4'
        )
        self.cursor = self.conn.cursor()
        return self.conn, self.cursor

    def close_conn(self):
        # 关闭连接和游标
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()

    def execute_query(self, sql, params=None):
        # 执行查询语句
        conn, cursor = self.get_conn()
        try:
            cursor.execute(sql, params)
            result = cursor.fetchall()
            return result
        finally:
            self.close_conn()

    def execute_update(self, sql, params=None):
        # 执行增删改语句
        conn, cursor = self.get_conn()
        try:
            cursor.execute(sql, params)
            conn.commit()
            return cursor.rowcount
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            self.close_conn()

核心功能实现

用户注册与登录

用户注册需要校验用户名是否已存在，登录需要校验用户名和密码是否匹配：

def user_register(username, password, phone):
    db = DBUtil()
    # 先检查用户名是否存在
    check_sql = "SELECT user_id FROM user WHERE username = %s"
    exist_user = db.execute_query(check_sql, (username,))
    if exist_user:
        return False, "用户名已存在"
    # 插入新用户
    insert_sql = "INSERT INTO user (username, password, phone) VALUES (%s, %s, %s)"
    row = db.execute_update(insert_sql, (username, password, phone))
    if row > 0:
        return True, "注册成功"
    return False, "注册失败"

def user_login(username, password):
    db = DBUtil()
    sql = "SELECT user_id FROM user WHERE username = %s AND password = %s"
    result = db.execute_query(sql, (username, password))
    if result:
        return True, result[0][0]
    return False, "用户名或密码错误"

图书借阅功能

借阅图书需要先检查图书剩余数量，再插入借阅记录，同时更新图书剩余数量：

def borrow_book(user_id, book_id):
    db = DBUtil()
    # 检查图书剩余数量
    check_sql = "SELECT remain_num FROM book WHERE book_id = %s"
    remain_result = db.execute_query(check_sql, (book_id,))
    if not remain_result or remain_result[0][0] <= 0:
        return False, "图书剩余数量不足"
    # 插入借阅记录
    insert_sql = "INSERT INTO borrow_record (user_id, book_id) VALUES (%s, %s)"
    # 更新图书剩余数量
    update_sql = "UPDATE book SET remain_num = remain_num - 1 WHERE book_id = %s"
    try:
        conn, cursor = db.get_conn()
        cursor.execute(insert_sql, (user_id, book_id))
        cursor.execute(update_sql, (book_id,))
        conn.commit()
        return True, "借阅成功"
    except Exception as e:
        conn.rollback()
        return False, f"借阅失败: {str(e)}"
    finally:
        db.close_conn()

图书归还功能

归还图书需要更新借阅记录状态，同时恢复图书剩余数量：

def return_book(record_id):
    db = DBUtil()
    # 查询借阅记录对应的图书ID和状态
    check_sql = "SELECT book_id, status FROM borrow_record WHERE record_id = %s"
    record = db.execute_query(check_sql, (record_id,))
    if not record:
        return False, "借阅记录不存在"
    if record[0][1] == 1:
        return False, "该图书已归还"
    # 更新借阅记录状态为已归还，设置归还时间
    update_record_sql = "UPDATE borrow_record SET status = 1, return_time = NOW() WHERE record_id = %s"
    # 恢复图书剩余数量
    update_book_sql = "UPDATE book SET remain_num = remain_num + 1 WHERE book_id = %s"
    try:
        conn, cursor = db.get_conn()
        cursor.execute(update_record_sql, (record_id,))
        cursor.execute(update_book_sql, (record[0][0],))
        conn.commit()
        return True, "归还成功"
    except Exception as e:
        conn.rollback()
        return False, f"归还失败: {str(e)}"
    finally:
        db.close_conn()

常见问题处理

• 连接MySQL失败：检查MySQL服务是否启动，用户名密码、端口是否正确，pymysql是否安装成功
• 中文乱码问题：连接数据库时指定charset为utf8mb4，数据库表也使用utf8mb4字符集
• SQL注入问题：所有参数都使用参数化查询，不要直接拼接SQL字符串

PythonMySQL图书借阅系统pymysql修改时间：2026-07-23 23:33:36