阅读量:2
目录
一、功能
二、表
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()