图书目录管理系统(python+mysql数据库)
简介
本文章是讲解图书目录管理系统，在原来的基础之上，加上了数据库的功能，使得这个项目更加的完善，然后参考文章为这两篇，图书目录管理系统（Python），python连接数据库
题目
请设计一个简单的图书目录管理系统(初级版)。
图书目录信息包括：
统一书号(不超过13字符)
书名(不超过30字符)
作者(不超过20字符)
出版社(不超过30字符)
出版日期(包括：年、月、日)
价格(精确到分)
注意：为了保证运行窗口能正确地显示，请右击运行窗口，修改“属性”：
在“选项”选项卡中，选中“使用旧版本控制台”
在“字体”选项卡中，选择“8×16”“点阵字体”
在“布局”选项卡中，将屏幕窗口宽度均设为“120”。
具体需求如下：
显示如下的主菜单：
Append Find Remove Modify Show Quit > _
如果用户输入 A 或 a、F 或 f、R 或 r、M 或 m、S 或 s，则可完成相应的操作。如果用户输入其它字符，则显示错误信息。
程序将反复显示主菜单，让用户持续工作。如果用户输入 Q 或 q，则程序结束。
Append Find Remove Modify Show Quit > Q Thank you! Goodbye!
若用户输入其它字符，则显示错误信息。
Append Find Remove Modify Show Quit > B Incorrect choice! Append Find Remove Modify Show Quit > 9 Incorrect choice! Append Find Remove Modify Show Quit > + Incorrect choice!
若用户输入 A 或 a，则可以输入新书的信息，将其添加到图书目录中。
Append Find Remove Modify Show Quit > a ISBN: 9780439227148 Title: The Call of the Wild Author: Jack London Publisher: Scholastic Press Pub date: 2001/1/1 Price: 39.4 Append Find Remove Modify Show Quit > A ISBN: 9781772262902 Title: Oliver Twist Author: Charles Dickens Publisher: Engage Books Pub date: 2016/9/15 Price: 648 Append Find Remove Modify Show Quit > a ISBN: 9787515911076 Title: The Call of the Wild Author: Jack London Publisher: Aerospace Publishing House Pub date: 2016/5/1 Price: 29.8
若用户输入的日期信息不正确，则显示错误信息，并要求用户重新输入。
Append Find Remove Modify Show Quit > A ISBN: 9787501592401 Title: The Old Man and the Sea Author: Ernest Hemingway Publisher: Knowledge Press Pub date: 2016/2/30 Incorrect date! Please reenter: 2016/6/31 Incorrect date! Please reenter: 2016/8/1 Price: 25.8
若用户输入 S 或 s，则按书号升序排序，然后列表显示全部图书。
Append Find Remove Modify Show Quit > S ISBN--------- Title------------------------- Author-------------- Publisher--------------------- Pub-date-- Price--- 9780439227148 The Call of the Wild Jack London Scholastic Press 2001/01/01 39.40 9781772262902 Oliver Twist Charles Dickens Engage Books 2016/09/15 648.00 9787501592401 The Old Man and the Sea Ernest Hemingway Knowledge Press 2016/08/01 25.80 9787515911076 The Call of the Wild Jack London Aerospace Publishing House 2016/05/01 29.80
若用户输入 F 或 f，则输入书名，然后显示该书名的图书。如果没有对应的图书，则显示错误信息。
Append Find Remove Modify Show Quit > F Title: The Call of the Wild ISBN--------- Title------------------------- Author-------------- Publisher--------------------- Pub-date-- Price--- 9780439227148 The Call of the Wild Jack London Scholastic Press 2001/01/01 39.40 9787515911076 The Call of the Wild Jack London Aerospace Publishing House 2016/05/01 29.80 Append Find Remove Modify Show Quit > f Title: Gulliver's Travels Not found!
说明：输出查找结果时，不作排序操作。
若用户输入 R 或 r，则输入书号，然后将删除该书号的图书。如果没有对应的图书，则显示错误信息。
Append Find Remove Modify Show Quit > r ISBN: 9781772262902 Remove(y/n)? n Append Find Remove Modify Show Quit > S ISBN--------- Title------------------------- Author-------------- Publisher--------------------- Pub-date-- Price--- 9780439227148 The Call of the Wild Jack London Scholastic Press 2001/01/01 39.40 9781772262902 Oliver Twist Charles Dickens Engage Books 2016/09/15 648.00 9787501592401 The Old Man and the Sea Ernest Hemingway Knowledge Press 2016/08/01 25.80 9787515911076 The Call of the Wild Jack London Aerospace Publishing House 2016/05/01 29.80 Append Find Remove Modify Show Quit > R ISBN: 9780439227148 Remove(y/n)? Y Append Find Remove Modify Show Quit > s ISBN--------- Title------------------------- Author-------------- Publisher--------------------- Pub-date-- Price--- 9781772262902 Oliver Twist Charles Dickens Engage Books 2016/09/15 648.00 9787501592401 The Old Man and the Sea Ernest Hemingway Knowledge Press 2016/08/01 25.80 9787515911076 The Call of the Wild Jack London Aerospace Publishing House 2016/05/01 29.80 Append Find Remove Modify Show Quit > r ISBN: 9787515914145 Not found! Append Find Remove Modify Show Quit > s ISBN--------- Title------------------------- Author-------------- Publisher--------------------- Pub-date-- Price--- 9781772262902 Oliver Twist Charles Dickens Engage Books 2016/09/15 648.00 9787501592401 The Old Man and the Sea Ernest Hemingway Knowledge Press 2016/08/01 25.80 9787515911076 The Call of the Wild Jack London Aerospace Publishing House 2016/05/01 29.80
要求：用户回答是否删除时，必须回答 Y 或 N (大小写均可)。如果是其它字符，则显示错误信息，要求用户重新回答。
Append Find Remove Modify Show Quit > R ISBN: 9781772262902 Remove(y/n)? k Incorrect answer! Remove(y/n)? $ Incorrect answer! Remove(y/n)? N
若用户输入 M 或 m，则可以修改图书信息。首先按书号查找，然后重新输入该图书的信息。
Append Find Remove Modify Show Quit > m ISBN: 9787515911076 Modify(y/n)? y ISBN: 9787544724968 Title: The House on Mango Street Author: Sandra Heathneros Publisher: Yilin Press Pub date: 2012/1/1 Price: 30 Append Find Remove Modify Show Quit > M ISBN: 9787501592401 Modify(y/n)? n Append Find Remove Modify Show Quit > m ISBN: 9787515914145 Not found! Append Find Remove Modify Show Quit > S ISBN--------- Title------------------------- Author-------------- Publisher--------------------- Pub-date-- Price--- 9781772262902 Oliver Twist Charles Dickens Engage Books 2016/09/15 648.00 9787501592401 The Old Man and the Sea Ernest Hemingway Knowledge Press 2016/08/01 25.80 9787544724968 The House on Mango Street Sandra Heathneros Yilin Press 2012/01/01 30.00
要求：用户回答是否修改时，必须回答 Y 或 N (大小写均可)。如果是其它字符，则显示错误信息，要求用户重新回答。
Append Find Remove Modify Show Quit > M ISBN: 9787544724968 Modify(y/n)? K Incorrect answer! Modify(y/n)? * Incorrect answer! Modify(y/n)? n
相关习题：图书目录管理系统(高级版)。
Append Find Remove Modify Show Quit > Thank you! Goodbye! q
我们现在希望在此基础之上，进行一定的修改，加上一个数据库。
代码部分
数据库建表语句
/* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Type : MySQL Source Server Version : 80028 Source Host : localhost:3306 Source Schema : projectdatabase Target Server Type : MySQL Target Server Version : 80028 File Encoding : 65001 Date: 31/01/2023 14:03:06 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for book -- ---------------------------- DROP TABLE IF EXISTS `book`; CREATE TABLE `book` ( `isbn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `author` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `publisher` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `pubdate` datetime NOT NULL, `price` decimal(10, 2) NOT NULL, PRIMARY KEY (`isbn`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of book -- ---------------------------- INSERT INTO `book` VALUES ('9787501592401', 'The Old Man and the Sea', 'Ernest Hemingway', 'Knowledge Press', '2023-01-30 00:00:00', 25.80); SET FOREIGN_KEY_CHECKS = 1;
数据库建表成功之后的样子
完整代码
import pymysql class Book: def __init__(self, ISBN, title, author, publisher, pubdate, price): self.ISBN = ISBN self.title = title self.author = author self.publisher = publisher self.pubdate = pubdate self.price = price def Print(self): # 打印book的各个属性 print(self.ISBN + "|" + self.title + "|" + self.author + "|" + self.publisher + "|" + str(self.pubdate) + "|" + str(self.price)) class BookLibrary: # 初始化第一步先链接数据库 def __init__(self): DBHOST = 'localhost' DBUSER = 'root' DBPASS = '123456' DBNAME = 'projectdatabase' try: self.db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME) print('数据库连接成功!') except pymysql.Error as e: print('数据库连接失败' + str(e)) def add(self): cur = self.db.cursor() sqlQuery = " INSERT INTO book (isbn, title, author,publisher,pubdate,price) VALUE (%s,%s,%s,%s,%s,%s)" print("请输入你需要添加的书的信息：") # 创建Book对象 book = Book(None, None, None, None, None, None) book.ISBN = input("ISBN:") book.title = input("Title:") book.author = input("Author:") book.publisher = input("Publisher:") book.pubdate = input("Pubdate:") book.price = input("Price:") value = (book.ISBN, book.title, book.author, book.publisher, book.pubdate, book.price) # 执行sql语句 cur.execute(sqlQuery, value) def selectAll(self): cur = self.db.cursor() # 这里输入sql语句 sqlQuery = "SELECT * FROM book" col = cur.execute(sqlQuery) # 查询之前需要先运行sql语句 results = cur.fetchall() # 只有上面先运行了sql语句这句话才可以运行成功 print("ISBN----------Title----------Author----------Publisher----------PubDate----------Price") # print(col) # 结果为影响的条数 # print(results) book = Book(None, None, None, None, None, None) for row in results: book.ISBN = row[0] book.title = row[1] book.author = row[2] book.publisher = row[3] book.pubdate = row[4] book.price = row[5] book.Print() return results def update(self): cur = self.db.cursor() ISBN = input("请输入你需要更新的书本的ISBN:") book = self.findByISBN(ISBN) if book == None: print("没有找到这本书") return book = Book(None, None, None, None, None, None) print("输入新的书本的信息：") book.ISBN = input("ISBN:") book.title = input("Title:") book.author = input("Author:") book.publisher = input("Publisher:") book.pubdate = input("Pubdate:") book.price = input("Price:") book.Print() # 这里输入sql语句 sqlQuery = "UPDATE book SET isbn=%s, title=%s, author=%s, publisher=%s, pubdate=%s, price=%s WHERE isbn=%s" value = (book.ISBN, book.title, book.author, book.publisher, book.pubdate, book.price, ISBN) col = cur.execute(sqlQuery, value) def delete(self): cur = self.db.cursor() ISBN = input("请输入你需要删除的书本的ISBN:") book = self.findByISBN(ISBN) if book == None: print("没有找到这本书") return # 这里输入sql语句 sqlQuery = "delete from book where isbn=%s" value = (ISBN) cur.execute(sqlQuery, value) # 按照ISBN查询 def findByISBN(self, ISBN): cur = self.db.cursor() # 这里输入sql语句 sqlQuery = "SELECT * FROM book where isbn = %s" value=(ISBN) col = cur.execute(sqlQuery, value) # 查询之前需要先运行sql语句 if (col == 0): print("没有查询到") return None else: return cur.fetchall() def testAdd(booklibrary): booklibrary.add() booklibrary.selectAll() def testSelectAll(booklibrary): booklibrary.selectAll() def testUpdate(booklibrary): booklibrary.update() booklibrary.selectAll() def testDelete(booklibrary): booklibrary.delete() booklibrary.selectAll() if __name__=="__main__": booklibrary = BookLibrary() testSelectAll(booklibrary) testAdd(booklibrary) testUpdate(booklibrary) testDelete(booklibrary)
如果大家觉得有用的话，可以关注我下面的微信公众号，极客李华，我会在里面更新更多行业资讯，企业面试内容，编程资源，如何写出可以让大厂面试官眼前一亮的简历等内容，让大家更好学习编程，我的抖音，B站也叫极客李华。大家喜欢也可以关注一下
