基于python+MySQL编写图书管理系统

目录

一、功能

二、表

三、代码编写

---

一、功能

二、表

books表：

 reader表：

 

三、代码编写

import pymysqlimport 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 resultdef 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 = pressdef 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_departmentdef 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