图书目录管理系统(python+mysql数据库)

亦凉 2024-03-27 11:01 87阅读 0赞

## 图书目录管理系统(python+mysql数据库) ##

### 简介 ###

本文章是讲解图书目录管理系统，在原来的基础之上，加上了数据库的功能，使得这个项目更加的完善，然后参考文章为这两篇，[图书目录管理系统（Python）][Python]，[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;

数据库建表成功之后的样子  
![在这里插入图片描述][8abcb8419cf6435fbeea4ce9a7af7697.png]

#### 完整代码 ####

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)

[Python]: https://blog.csdn.net/qq_51447496/article/details/128816007
[python]: https://blog.csdn.net/qq_51447496/article/details/128752271
[8abcb8419cf6435fbeea4ce9a7af7697.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/222ed8177b064a10b1a0d00375e69058.png