### 三、实现基于Python+MySQL的图书管理系统 以下是一个完整的基于Python和MySQL的图书管理系统的实现方案，包含用户登录验证、图书增删改查以及借阅归还等功能。 --- #### ### 1. 数据库设计与初始化 数据库表结构已在上一次回答中详细描述。以下是创建表的SQL语句[^1]： ```sql -- 图书表 CREATE TABLE books ( book_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100) NOT NULL, author VARCHAR(50) NOT NULL, total_quantity INT DEFAULT 0, borrowed_quantity INT DEFAULT 0 ); -- 用户表 CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, password VARCHAR(100) NOT NULL, age INT ); -- 借阅表 CREATE TABLE borrow_records ( record_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, book_id INT NOT NULL, borrow_quantity INT DEFAULT 1, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (book_id) REFERENCES books(book_id) ); ``` --- #### ### 2. Python代码实现 以下是系统的核心功能实现，包括数据库连接、用户登录、图书管理、借阅归还等。 --- ##### ### 2.1 数据库连接工具 ```python import pymysql def get_db_connection(): return pymysql.connect( host='localhost', user='root', password='your_password', database='library', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor ) ``` --- ##### ### 2.2 用户管理模块 ```python import hashlib def register_user(username, password, age): hashed_pw = hashlib.sha256(password.encode()).hexdigest() try: conn = get_db_connection() with conn.cursor() as cursor: sql = "INSERT INTO users (username, password, age) VALUES (%s, %s, %s)" cursor.execute(sql, (username, hashed_pw, age)) conn.commit() return True except pymysql.IntegrityError: return False finally: conn.close() def login_user(username, password): hashed_pw = hashlib.sha256(password.encode()).hexdigest() try: conn = get_db_connection() with conn.cursor() as cursor: sql = "SELECT user_id FROM users WHERE username=%s AND password=%s" cursor.execute(sql, (username, hashed_pw)) result = cursor.fetchone() return result['user_id'] if result else None finally: conn.close() ``` --- ##### ### 2.3 图书管理模块 ```python def add_book(title, author, quantity): try: conn = get_db_connection() with conn.cursor() as cursor: # 检查是否存在该图书 cursor.execute("SELECT * FROM books WHERE title=%s AND author=%s", (title, author)) book = cursor.fetchone() if book: # 更新数量 cursor.execute("UPDATE books SET total_quantity=total_quantity+%s WHERE book_id=%s", (quantity, book['book_id'])) else: # 插入新图书 cursor.execute("INSERT INTO books (title, author, total_quantity, borrowed_quantity) VALUES (%s, %s, %s, 0)", (title, author, quantity)) conn.commit() return True finally: conn.close() def delete_book(book_id): try: conn = get_db_connection() with conn.cursor() as cursor: cursor.execute("SELECT borrowed_quantity FROM books WHERE book_id=%s", (book_id,)) book = cursor.fetchone() if book and book['borrowed_quantity'] > 0: return False # 存在未归还记录 cursor.execute("DELETE FROM books WHERE book_id=%s", (book_id,)) conn.commit() return True finally: conn.close() def update_book(book_id, **kwargs): set_clause = ", ".join([f"{key}=%s" for key in kwargs.keys()]) values = list(kwargs.values()) values.append(book_id) try: conn = get_db_connection() with conn.cursor() as cursor: sql = f"UPDATE books SET {set_clause} WHERE book_id=%s" cursor.execute(sql, values) conn.commit() return True finally: conn.close() def search_books(keyword=None): try: conn = get_db_connection() with conn.cursor() as cursor: if keyword: sql = "SELECT * FROM books WHERE title LIKE %s OR author LIKE %s" cursor.execute(sql, (f"%{keyword}%", f"%{keyword}%")) else: cursor.execute("SELECT * FROM books") return cursor.fetchall() finally: conn.close() ``` --- ##### ### 2.4 借阅管理模块 ```python def borrow_book(user_id, book_id, quantity=1): try: conn = get_db_connection() with conn.cursor() as cursor: cursor.execute("SELECT total_quantity, borrowed_quantity FROM books WHERE book_id=%s FOR UPDATE", (book_id,)) book = cursor.fetchone() if not book or book['total_quantity'] - book['borrowed_quantity'] < quantity: return False cursor.execute("UPDATE books SET borrowed_quantity=borrowed_quantity+%s WHERE book_id=%s", (quantity, book_id)) cursor.execute("INSERT INTO borrow_records (user_id, book_id, borrow_quantity) VALUES (%s, %s, %s)", (user_id, book_id, quantity)) conn.commit() return True finally: conn.close() def return_book(record_id): try: conn = get_db_connection() with conn.cursor() as cursor: cursor.execute("SELECT book_id, borrow_quantity FROM borrow_records WHERE record_id=%s", (record_id,)) record = cursor.fetchone() if not record: return False cursor.execute("UPDATE books SET borrowed_quantity=borrowed_quantity-%s WHERE book_id=%s", (record['borrow_quantity'], record['book_id'])) cursor.execute("DELETE FROM borrow_records WHERE record_id=%s", (record_id,)) conn.commit() return True finally: conn.close() ``` --- #### ### 3. 系统使用示例 ```python # 注册用户 register_user("john_doe", "secure123", 25) # 登录用户 user_id = login_user("john_doe", "secure123") if user_id: print(f"用户登录成功，ID: {user_id}") # 添加图书 add_book("Python编程", "Guido van Rossum", 10) # 借阅图书 borrow_book(user_id, 1) # 查询图书 books = search_books("Python") for book in books: print(f"书名: {book['title']}, 作者: {book['author']}") # 归还图书 return_book(1) ``` --- ###