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

一、功能

二、表

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()