目录

一、功能

二、表

三、代码编写

一、功能

二、表

books表：

 reader表：

三、代码编写

import pymysql import datetime  # 建立数据库连接 connection = pymysql.connect(     host='localhost',  # 数据库主机名     port=3306,               # 数据库端口号，默认为3306     user='root',             # 数据库用户名     passwd='123456',         # 数据库密码     db='tushuguanlixitong',  # 数据库名称     charset='utf8'           # 字符编码 )   def query(sql, one=False):     cursor = connection.cursor()     cursor.execute(sql)     if one:         return cursor.fetchone()     else:         return cursor.fetchall()   def update(sql):     cursor = connection.cursor()     result = cursor.execute(sql)     # 提交事务     connection.commit()     return result   def select_book():     # 创建一个数据库游标对象     cursor = connection.cursor() # 定义SQL查询语句，从图书信息表表中选择所有数据     sql = 'select*from books;'     # 执行SQL查询语句     cursor.execute(sql)     # 提交事务，确保数据被正确写入数据库     connection.commit()     # 从游标中获取查询结果，保存到data变量中     data = cursor.fetchall()     # 导入pandas库中的DataFrame类     from pandas import DataFrame     # 显示DataFrame的前5行数据     df = DataFrame(data, columns=['book_name', 'book_id', 'book_status', 'book_ISBN', 'author', 'press', 'borrower',                                  'loan_time'])     df.head()     print(df.head())   class Book:      def __init__(self, book_name, book_id, book_status, book_isbn, author, press):         self.book_name = book_name         self.author = author         self.book_id = book_id         self.book_status = book_status         self.book_ISBN = book_isbn         self.press = press   def add_book():     cursor = connection.cursor()     sql = ('INSERT INTO books (book_name,book_id,book_status,book_ISBN,author,press)'            'VALUES(%s,%s,%s,%s,%s,%s)')     print('请输入添加图书信息：')     book = Book(None, None, None, None, None, None)     book.book_name = input('请输入图书名：')     book.book_id = input('请输入图书编号：')     book.book_status = input('请输入图书状态：')     book.book_ISBN = input('请输入图书ISBN码：')     book.author = input('请输入图书作者：')     book.press = input('请输入图书出版社：')     values = (book.book_name, book.book_id, book.book_status, book.book_ISBN, book.author, book.press)     cursor.execute(sql, values)     connection.commit()     print('图书添加成功')   def delete_book():     cursor = connection.cursor()     book_id = input("输入需要删除的图书号：")     result = query("select  * FROM books where book_id = {}".format(book_id), one=True)      if result:         print("图书信息：".format(result))         chooice = input("是否删除? 1.yes,2.no")         if chooice == '1':             update("DELETE FROM books where book_id = {}".format(book_id))             print("成功删除")          else:             print("放弃删除")      else:         print("未查询到相关书籍信息~")      num = input("继续删除请输入1， 回车退回主菜单")     if num == "1":         delete_book()   def update_book():     cursor = connection.cursor()     book_id = input("输入需要删除的图书号：")     result = query("select  * FROM books where book_id = {}".format(book_id), one=True)     if result:         print("图书信息：".format(result))         book_name = input("请输入修改书名：")         book_status = input("请输入图书状态：")         author = input("请输入修改作者：")         press = input("请输入修改出版社：")         update("update books set book_name = '{}',book_status = '{}',author = '{}',press = '{}' where book_id = {};"                .format(book_name, book_status, author, press, book_id))         print("更新成功")     else:         print("未查询到相关书籍信息~")      num = input("继续更新请输入1， 回车退回主菜单")     if num == "1":         update_book()   def select_reader():     # 创建一个数据库游标对象     cursor = connection.cursor() # 定义SQL查询语句，从图书信息表表中选择所有数据     sql = 'select*from reader;'     # 执行SQL查询语句     cursor.execute(sql)     # 提交事务，确保数据被正确写入数据库     connection.commit()     # 从游标中获取查询结果，保存到data变量中     data = cursor.fetchall()     # 导入pandas库中的DataFrame类     from pandas import DataFrame     # 显示DataFrame的前5行数据     df = DataFrame(data, columns=['姓名', '编号', '身份', '部门'])     df.head()     print(df.head()) # 查询读者名单   class Read:      def __init__(self, read_name, read_id, read_identity, read_department):         self.read_name = read_name         self.read_id = read_id         self.read_identity = read_identity         self.read_department = read_department   def add_reader():     cursor = connection.cursor()     sql = 'INSERT INTO reader (read_name,read_id,read_identity,read_department) VALUES(%s,%s,%s,%s)'     print('添加人员：')     read = Read(None, None, None, None)     read.read_name = input('请输入读者姓名')     read.read_id = input('请输入读者编号')     read.read_identity = input('请输入读者身份')     read.read_department = input('请输入读者所在部门')     values = (read.read_name, read.read_id, read.read_identity, read.read_department)     cursor.execute(sql, values)     connection.commit()     print('读者添加成功！')   def borrow_book():     cursor = connection.cursor()     book_id = input("请输入需要借阅的图书号：")     result = query("select * from books where book_id={};".format(book_id), one=True)     print(result)     if result:         if result[2] == "出借":             print("抱歉，该书已经借出！")          else:             while True:                 borrower = input("请输入借阅者的名字：")                 if result:                     return_time = input("请输入还书的时间；")                     update("update books set return_time='{}' where book_id={};".format(return_time, book_id))                     if borrower:                         update("update books set borrower='{}' where book_id={};".format(borrower, book_id))                         update("update books set book_status='出借' where book_id={};".format(book_id))                         print("图书借阅成功~")                         break                  else:                     print("没有这个读者，请重新输入")     else:         print("未查询到相关书籍信息~")      num = input("继续借阅请输入1， 回车退回主菜单")     if num == "1":         borrow_book()   def back_book():     cursor = connection.cursor()     book_id = input('请输入要归还的图书书号：')     result = query("select * from books where book_id={};".format(book_id), one=True)     if result:         if result[2] == '在架':             print("抱歉，该书在架请确认编号是否正确！")         else:             update("update books set borrower='' where book_id={};".format(book_id))             update("update books set book_status='在架' where book_id={};".format(book_id))             print("归还成功~")     else:         print("未查询到相关书籍信息~")      num = input("继续还书请输入1， 回车退回主菜单")     if num == "1":         back_book()   def time():     cursor = connection.cursor()     now = datetime.datetime.now()     cursor .execute("SELECT * FROM books WHERE return_time < %s", (now,))     book_name = cursor.fetchall()     print("图书归还超期：")     print(book_name) # 查询临期图书(距离当前时间一周内到期)     cursor.execute("SELECT * FROM books WHERE return_time BETWEEN %s AND %s",                    (now, now + datetime.timedelta(days=10)))     due_soon_books = cursor.fetchall()     print("距离图书(到期还有10天):")   def menu():  # 图书管理系统菜单      while True:         print("""                    图书管理系统            1.查询图书            2.增加图书            3.借阅图书            4.归还图书            5.修改图书            6.删除图书            7.导入读者名单            8.查看读者名单            9.超期和临期查询            10.退出系统            """)         choice = input('请选择：')          if choice == '1':             select_book()         elif choice == '2':             add_book()         elif choice == '3':             borrow_book()         elif choice == '4':             back_book()         elif choice == '5':             update_book()         elif choice == '6':             delete_book()         elif choice == '7':             add_reader()         elif choice == '8':             select_reader()         elif choice == '9':             time()         elif choice == '10':             print('欢迎下次使用~~~~~~~')             break         else:             print('请输入正确序号')  menu()