- 作者简介：一名后端开发人员，每天分享后端开发以及人工智能相关技术，行业前沿信息，面试宝典。
- 座右铭：未来是不可确定的，慢慢来是最快的。
- 个人主页：极客李华-CSDN博客
- 合作方式：私聊+
- 这个专栏内容：BAT等大厂常见后端java开发面试题详细讲解，更新数目100道常见大厂java后端开发面试题。
- 我的CSDN社区：https://bbs.csdn.net/forums/99eb3042821a4432868bb5bfc4d513a8
- 微信公众号，抖音，b站等平台统一叫做：极客李华，加入微信公众号领取各种编程资料，加入抖音，b站学习面试技巧，职业规划
图书目录管理系统(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站也叫极客李华。大家喜欢也可以关注一下
286
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
