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

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

​

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

简介

本文章是讲解图书目录管理系统，在原来的基础之上，加上了数据库的功能，使得这个项目更加的完善，然后参考文章为这两篇，图书目录管理系统（Python），python连接数据库

题目

请设计一个简单的图书目录管理系统(初级版)。

图书目录信息包括：

统一书号(不超过1字符)

书名(不超过0字符)

作者(不超过20字符)

出版社(不超过0字符)

出版日期(包括：年、月、日)

价格(精确到分)

注意：为了保证运行窗口能正确地显示，请右击运行窗口，修改“属性”：

在“选项”选项卡中，选中“使用旧版本控制台”

在“字体”选项卡中，选择“8×16”“点阵字体”

在“布局”选项卡中，将屏幕窗口宽度均设为“120”。

具体需求如下：

显示如下的主菜单：

Append Find Remove Modify Show Quit > _

如果用户输入 A 或 a、F 或 f、R 或 r、M 或 m、S 或 s，则可完成相应的操作。如果用户输入其它字符，则显示错误信息。

程序将反复显示主菜单，让用户持续工作。如果用户输入 Q 或 q，则程序结束。

代码语言：javascript代码运行次数：0运行复制

Append Find Remove Modify Show Quit > Q
Thank you! Goodbye!

若用户输入其它字符，则显示错误信息。

代码语言：javascript代码运行次数：0运行复制

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，则可以输入新书的信息，将其添加到图书目录中。

代码语言：javascript代码运行次数：0运行复制

Append Find Remove Modify Show Quit > a
     ISB: 978049227148
    Title: The Call of the Wild
   Author: Jack London
Publisher: Scholastic Press
 Pub date: 2001/1/1
    Price: 9.4
Append Find Remove Modify Show Quit > A
     ISB: 9781772262902
    Title: Oliver Twist
   Author: Charles Dickens
Publisher: Engage Books
 Pub date: 2016/9/15
    Price: 648
Append Find Remove Modify Show Quit > a
     ISB: 9787515911076
    Title: The Call of the Wild
   Author: Jack London
Publisher: Aerospace Publishing House
 Pub date: 2016/5/1
    Price: 29.8

若用户输入的日期信息不正确，则显示错误信息，并要求用户重新输入。

代码语言：javascript代码运行次数：0运行复制

Append Find Remove Modify Show Quit > A
     ISB: 9787501592401
    Title: The Old Man and the Sea
   Author: Ernest Hemingway
Publisher: Knowledge Press
 Pub date: 2016/2/0
Incorrect date! Please reenter: 2016/6/1
Incorrect date! Please reenter: 2016/8/1
    Price: 25.8

若用户输入 S 或 s，则按书号升序排序，然后列表显示全部图书。

代码语言：javascript代码运行次数：0运行复制

Append Find Remove Modify Show Quit > S
ISB--------- Title------------------------- Author-------------- Publisher--------------------- Pub-date-- Price---
978049227148 The Call of the Wild           Jack London          Scholastic Press               2001/01/01    9.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，则输入书名，然后显示该书名的图书。如果没有对应的图书，则显示错误信息。

代码语言：javascript代码运行次数：0运行复制

Append Find Remove Modify Show Quit > F
Title: The Call of the Wild
ISB--------- Title------------------------- Author-------------- Publisher--------------------- Pub-date-- Price---
978049227148 The Call of the Wild           Jack London          Scholastic Press               2001/01/01    9.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
ot found!

说明：输出查结果时，不作排序操作。

若用户输入 R 或 r，则输入书号，然后将删除该书号的图书。如果没有对应的图书，则显示错误信息。

代码语言：javascript代码运行次数：0运行复制

Append Find Remove Modify Show Quit > r
ISB: 9781772262902
Remove(y/n)? n
Append Find Remove Modify Show Quit > S
ISB--------- Title------------------------- Author-------------- Publisher--------------------- Pub-date-- Price---
978049227148 The Call of the Wild           Jack London          Scholastic Press               2001/01/01    9.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
ISB: 978049227148
Remove(y/n)? Y
Append Find Remove Modify Show Quit > s
ISB--------- 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
ISB: 9787515914145
ot found!
Append Find Remove Modify Show Quit > s
ISB--------- 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 或 (大小写均可)。如果是其它字符，则显示错误信息，要求用户重新回答。

代码语言：javascript代码运行次数：0运行复制

Append Find Remove Modify Show Quit > R
ISB: 9781772262902
Remove(y/n)? k
Incorrect answer!
Remove(y/n)? $
Incorrect answer!
Remove(y/n)? 

若用户输入 M 或 m，则可以修改图书信息。首先按书号查，然后重新输入该图书的信息。

代码语言：javascript代码运行次数：0运行复制

Append Find Remove Modify Show Quit > m
ISB: 9787515911076
Modify(y/n)? y
     ISB: 9787544724968
    Title: The House on Mango Street
   Author: Sandra Heathneros
Publisher: Yilin Press
 Pub date: 2012/1/1
    Price: 0
Append Find Remove Modify Show Quit > M
ISB: 9787501592401
Modify(y/n)? n
Append Find Remove Modify Show Quit > m
ISB: 9787515914145
ot found!
Append Find Remove Modify Show Quit > S
ISB--------- 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    0.00

要求：用户回答是否修改时，必须回答 Y 或 (大小写均可)。如果是其它字符，则显示错误信息，要求用户重新回答。

代码语言：javascript代码运行次数：0运行复制

Append Find Remove Modify Show Quit > M
ISB: 9787544724968
Modify(y/n)? K
Incorrect answer!
Modify(y/n)? *
Incorrect answer!
Modify(y/n)? n

相关习题：图书目录管理系统(高级版)。

代码语言：javascript代码运行次数：0运行复制

Append Find Remove Modify Show Quit > Thank you! Goodbye!
q

我们现在希望在此基础之上，进行一定的修改，加上一个数据库。

代码部分

数据库建表语句

代码语言：javascript代码运行次数：0运行复制

/*
 avicat MySQL Data Transfer

 Source Server         : localhost_06
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : localhost:06
 Source Schema         : projectdatabase

 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001

 Date: 1/01/202 14:0:06
*/

SET AMES utf8mb4;
SET FOREIG_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 OT ULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci OT ULL,
  `author` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci OT ULL,
  `publisher` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci OT ULL,
  `pubdate` datetime OT ULL,
	  `price` decimal(10, 2) OT ULL,
  PRIMARY KEY (`isbn`) USIG BTREE
) EGIE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYAMIC;

-- ----------------------------
-- Records of book
-- ----------------------------
ISERT ITO `book` VALUES ('9787501592401', 'The Old Man and the Sea', 'Ernest Hemingway', 'Knowledge Press', '202-01-0 00:00:00', 25.80);

SET FOREIG_KEY_CHECKS = 1;

数据库建表成功之后的样子

完整代码

代码语言：javascript代码运行次数：0运行复制

import pymysql

class Book:
    def __init__(self, ISB, title, author, publisher, pubdate, price):
        self.ISB = ISB
         = title
        self.author = author
        self.publisher = publisher
        self.pubdate = pubdate
        self.price = price
    def Print(self):
        # 打印book的各个属性
        print(self.ISB + "|" +  + "|" + self.author + "|" + self.publisher + "|" + str(self.pubdate) + "|" + str(self.price))

class BookLibrary:
    # 初始化第一步先链接数据库
    def __init__(self):
        DBHOST = 'localhost'
        DBUSER = 'root'
        DBPASS = '12456'
        DBAME = 'projectdatabase'
        try:
            self.db = (host=DBHOST, user=DBUSER, password=DBPASS, database=DBAME)
            print('数据库连接成功!')
        except pymysql.Error as e:
            print('数据库连接失败' + str(e))
    def add(self):
        cur = self.()
        sqlQuery = " ISERT ITO book (isbn, title, author,publisher,pubdate,price) VALUE (%s,%s,%s,%s,%s,%s)"
        print("请输入你需要添加的书的信息：")
        # 创建Book对象
        book = Book(one, one, one, one, one, one)
        book.ISB = input("ISB:")
         = input("Title:")
        book.author = input("Author:")
        book.publisher = input("Publisher:")
        book.pubdate = input("Pubdate:")
        book.price = input("Price:")
        value = (book.ISB, , book.author, book.publisher, book.pubdate, book.price)
        # 执行sql语句
        (sqlQuery, value)

    def selectAll(self):
        cur = self.()

        # 这里输入sql语句
        sqlQuery = "SELECT * FROM book"
        col = (sqlQuery)  # 查询之前需要先运行sql语句
        results = cur.fetchall()  # 只有上面先运行了sql语句这句话才可以运行成功
        print("ISB----------Title----------Author----------Publisher----------PubDate----------Price")
        # print(col)  # 结果为影响的条数
        # print(results)
        book = Book(one, one, one, one, one, one)

        for row in results:
            book.ISB = row[0]
             = row[1]
            book.author = row[2]
            book.publisher = row[]
            book.pubdate = row[4]
            book.price = row[5]
            book.Print()
        return results

    def update(self):
        cur = self.()

        ISB = input("请输入你需要更新的书本的ISB:")
        book = self.findByISB(ISB)
        if book == one:
            print("没有到这本书")
            return

        book = Book(one, one, one, one, one, one)
        print("输入新的书本的信息：")
        book.ISB = input("ISB:")
         = 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.ISB, , book.author, book.publisher, book.pubdate, book.price, ISB)
        col = (sqlQuery, value)

    def delete(self):
        cur = self.()
        ISB = input("请输入你需要删除的书本的ISB:")
        book = self.findByISB(ISB)
        if book == one:
            print("没有到这本书")
            return
            # 这里输入sql语句
        sqlQuery = "delete from book where isbn=%s"
        value = (ISB)
        (sqlQuery, value)

    # 按照ISB查询
    def findByISB(self, ISB):
        cur = self.()

        # 这里输入sql语句
        sqlQuery = "SELECT * FROM book where isbn = %s"
        value=(ISB)
        col = (sqlQuery, value)  # 查询之前需要先运行sql语句
        if (col == 0):
            print("没有查询到")
            return one
        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)

​

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2024-02-01，如有侵权请联系 cloudcommunity@tencent 删除前往查看findmysqlselfpython数据库