程序猿大战Python——实现简单的图书馆系统操作
步骤1：安装和导入库
首先，确保已经安装了 pymysql 库。如果没有安装，请执行以下命令：
pip install pymysql
然后，导入必要的库：
import pymysql
步骤2：创建数据库和表的函数
编写一个函数来创建数据库和表，并在表中插入一些数据：
CREATE DATABASE IF NOT EXISTS library;
USE library;
CREATE TABLE IF NOT EXISTS books (id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键，自增',title VARCHAR(255) NOT NULL COMMENT '书名',author VARCHAR(255) NOT NULL COMMENT '作者',isbn VARCHAR(13) NOT NULL UNIQUE COMMENT '国际标准书号，唯一'
) COMMENT='图书表';
USE library;
INSERT INTO books (title, author, isbn) VALUES
('红楼梦', '曹雪芹', '9787539999999'),
('西游记', '吴承恩', '9787539988888'),
('水浒传', '施耐庵', '9787539977777'),
('三国演义', '罗贯中', '9787539966666');
步骤3：定义 Book 类
定义一个简单的 Book 类来表示书籍：
class Book(object):def __init__(self, title, author, isbn):self.title = titleself.author = authorself.isbn = isbn
def __str__(self):return f"书名: {self.title}, 作者: {self.author}, ISBN: {self.isbn}"
解释：
-
__init__：初始化书籍对象。 -
__str__：定义对象的字符串表示形式。
步骤4：定义 Library 类
编写一个 Library 类来管理书籍：
class Library(object):def __init__(self, db_config):self.conn = pymysql.connect(**db_config)self.cursor = self.conn.cursor()self.cursor.execute("USE library;")
def __del__(self):self.cursor.close()self.conn.close()
def _execute_query(self, query, params=None):self.cursor.execute(query, params)self.conn.commit()
def add_book(self, book):query = "INSERT INTO books (title, author, isbn) VALUES (%s, %s, %s)"params = (book.title, book.author, book.isbn)self._execute_query(query, params)print(f"书籍《{book.title}》已添加到图书馆。")
def remove_book(self, isbn):query = "DELETE FROM books WHERE isbn = %s"params = (isbn,)self._execute_query(query, params)print(f"ISBN为 {isbn} 的书籍已从图书馆删除。")
def find_book(self, isbn):query = "SELECT title, author, isbn FROM books WHERE isbn = %s"self.cursor.execute(query, (isbn,))result = self.cursor.fetchone()if result:return Book(*result)return None
def list_books(self):query = "SELECT title, author, isbn FROM books"self.cursor.execute(query)results = self.cursor.fetchall()if results:for row in results:print(Book(*row))else:print("图书馆中没有书籍。")def print_menu():print("\n图书管理系统")print("1. 添加书籍")print("2. 删除书籍")print("3. 查找书籍")print("4. 列出所有书籍")print("5. 退出")
解释：
-
__init__和__del__：初始化和销毁对象时的操作，分别是连接和关闭数据库。 -
_execute_query：执行 SQL 查询。 -
add_book、remove_book、find_book和list_books：管理书籍的增、删、查、列操作。
步骤5：编写主程序
编写主程序来实现交互式菜单，让用户选择不同的操作：
if __name__ == "__main__":db_config = {'host': 'localhost','user': 'root', # 替换为你的MySQL用户名'password': '123456', # 替换为你的MySQL密码'charset': 'utf8mb4'}
library = Library(db_config)
while True:print_menu()choice = input("请选择操作(1-5): ")
if choice == '1':title = input("请输入书名: ")author = input("请输入作者: ")isbn = input("请输入ISBN: ")book = Book(title, author, isbn)library.add_book(book)elif choice == '2':isbn = input("请输入要删除的书籍ISBN: ")library.remove_book(isbn)elif choice == '3':isbn = input("请输入要查找的书籍ISBN: ")found_book = library.find_book(isbn)if found_book:print(f"找到书籍: {found_book}")else:print("未找到书籍。")elif choice == '4':library.list_books()elif choice == '5':print("退出系统。")breakelse:print("无效的选择，请重新输入。")
解释：
-
print_menu：打印菜单。 -
主程序：根据用户输入调用不同的图书管理方法。
完整代码
将所有部分结合在一起，形成完整的程序：
import pymysql
class Book(object):def __init__(self, title, author, isbn):self.title = titleself.author = authorself.isbn = isbndef __str__(self):return f"书名: {self.title}, 作者: {self.author}, ISBN: {self.isbn}"class Library(object):def __init__(self, db_config):self.conn = pymysql.connect(**db_config)self.cursor = self.conn.cursor()self.cursor.execute("USE library;")def __del__(self):self.cursor.close()self.conn.close()def _execute_query(self, query, params=None):self.cursor.execute(query, params)self.conn.commit()def add_book(self, book):query = "INSERT INTO books (title, author, isbn) VALUES (%s, %s, %s)"params = (book.title, book.author, book.isbn)self._execute_query(query, params)print(f"书籍《{book.title}》已添加到图书馆。")def remove_book(self, isbn):query = "DELETE FROM books WHERE isbn = %s"params = (isbn,)self._execute_query(query, params)print(f"ISBN为 {isbn} 的书籍已从图书馆删除。")def find_book(self, isbn):query = "SELECT title, author, isbn FROM books WHERE isbn = %s"self.cursor.execute(query, (isbn,))result = self.cursor.fetchone()if result:return Book(*result)return Nonedef list_books(self):query = "SELECT title, author, isbn FROM books"self.cursor.execute(query)results = self.cursor.fetchall()if results:for row in results:print(Book(*row))else:print("图书馆中没有书籍。")@staticmethoddef print_menu():print("\n图书管理系统")print("1. 添加书籍")print("2. 删除书籍")print("3. 查找书籍")print("4. 列出所有书籍")print("5. 退出")if __name__ == "__main__":db_config = {'host': 'localhost','user': 'root', # 替换为你的MySQL用户名'password': '123456', # 替换为你的MySQL密码'charset': 'utf8mb4'}library = Library(db_config)while True:library.print_menu()choice = input("请选择操作(1-5): ")if choice == '1':title = input("请输入书名: ")author = input("请输入作者: ")isbn = input("请输入ISBN: ")book = Book(title, author, isbn)library.add_book(book)elif choice == '2':isbn = input("请输入要删除的书籍ISBN: ")library.remove_book(isbn)elif choice == '3':isbn = input("请输入要查找的书籍ISBN: ")found_book = library.find_book(isbn)if found_book:print(f"找到书籍: {found_book}")else:print("未找到书籍。")elif choice == '4':library.list_books()elif choice == '5':print("退出系统。")breakelse:print("无效的选择，请重新输入。")