基于python实现图书管理系统

使用了sqlite3库

代码，报告，数据库见百度网盘

https://pan.baidu.com/s/14Nmm6nM0LTMzl4zu18104w?pwd=x74v

提取码：

x74v

import sqlite3
import unicodedata
import datetime
# 连接数据库的通用函数
def getConnection():
    dbstring = "book.db"  # 注意反斜杠需要转义或使用原始字符串
    conn = sqlite3.connect(dbstring)
    cur = conn.cursor()#创建游标对象，这个游标对象可以用来执行SQL语句并操作数据库

    # 创建 bookbase 表SQL语句
    cur.execute('''CREATE TABLE IF NOT EXISTS bookbase (
                    bookid   VARCHAR (20) PRIMARY KEY
                          NOT NULL
                          UNIQUE,
                    bookname VARCHAR (20) UNIQUE NOT NULL,
                    writer   VARCHAR (15) NOT NULL,
                    address  VARCHAR (20) NOT NULL,
                    date     DATE     NOT NULL,
                    price    FLOAT     NOT NULL,
                    copynum  INTEGER ,
                    lend     INTEGER   DEFAULT (0)
                )''')

    # 创建 borrowbooks 表
    cur.execute('''CREATE TABLE IF NOT EXISTS borrowbooks (
                borrowid INTEGER PRIMARY KEY AUTOINCREMENT,
                userid VARCHAR(10) NOT NULL,
                bookid VARCHAR(20) NOT NULL,
                borrowdate TEXT NOT NULL,
                returndate TEXT,
                actualreturndate TEXT,
                overduedate INTEGER,
                FOREIGN KEY (userid) REFERENCES user(userid),
                FOREIGN KEY (bookid) REFERENCES bookbase(bookid)
            )''')

    # 创建 user 表
    cur.execute('''CREATE TABLE IF NOT EXISTS user (
                    username  VARCHAR (20) PRIMARY KEY
                           UNIQUE
                           NOT NULL,
                    password  INTEGER   NOT NULL,
                    character INTEGER  NOT NULL
                           UNIQUE,
                    userid    VARCHAR (10) NOT NULL
                           UNIQUE,
                     number    INTEGER   DEFAULT (0) 
                )''')

    conn.commit()
    return conn

# 登录界面函数
def login():
    print("------------ 登录界面 ------------")
    print("1. 登录")
    print("2. 注册新用户")
    print("3. 退出系统")
    choice = input("请选择操作 (1/2/3): ")
    
    if choice == '1':
        username = input("请输入用户名: ")
        password = input("请输入密码: ")

        conn = getConnection()
        cur = conn.cursor()

        # 查询用户表确定身份
        sqlstr = "SELECT userid, character FROM user WHERE username = ? AND password = ?"
        cur.execute(sqlstr, (username, password))
        user_record = cur.fetchone()

        if not user_record:
            print("用户名或密码错误，请重新登录。")
        else:
            userid, character = user_record
            if character == "admin":  # 管理员身份
                print(f"欢迎管理员 {username} 登录！")
                admin_menu(userid)
            elif character == "user":  # 用户身份
                print(f"欢迎用户 {username} 登录！")
                user_menu(userid)
            else:
                print("未知的用户身份。请联系管理员。")

        conn.close()
        login()
    elif choice == '2':
        register_user()
        login()
    elif choice == '3':
        print("退出系统完成!")
        exit()
    else:
        print("无效的选项，请重新选择。")
        login()

# 实现生成唯一用户角色的逻辑，具体实现
def get_user_role():
    while True:
        character = input("请输入用户角色 (admin/user): ").strip().lower()
        if character == 'admin' or character == 'user':
            return character
        else:
            print("错误：请输入 admin 或 user。")
# 注册新用户函数
def register_user():
    print("------------ 用户注册 ------------")
    username = input("请输入用户名: ")
    password = input("请输入密码 (长度必须为6): ")
    confirm_password = input("请确认密码: ")

    if len(password) != 6:
        print("密码长度必须为6，请重新注册。")
        return

    if password != confirm_password:
        print("两次输入的密码不一致，请重新注册。")
        return

    conn = getConnection()
    cur = conn.cursor()

    # 检查用户名是否已存在
    cur.execute("SELECT * FROM user WHERE username = ?", (username,))
    existing_user = cur.fetchone()

    if existing_user:
        print("该用户名已被注册，请尝试其他用户名。")
    else:
        userid = generate_userid()  # 生成唯一的用户ID
        character = get_user_role() #获取用户角色
        
        # 插入新用户信息到user表中
        cur.execute("INSERT INTO user (userid, username, password, character) VALUES (?, ?, ?, ?)",
                    (userid, username, password, character))
        conn.commit()

        print("注册成功！")
        print(f"您的用户ID是: {userid}")

        conn.close()


# 自动生成用户ID（从1001开始，自动和数据库中已有的错开）
def generate_userid():
    conn = getConnection()
    cur = conn.cursor()

    # 查询当前数据库中最大的用户ID
    cur.execute("SELECT MAX(userid) FROM user")
    max_userid = cur.fetchone()[0]
    # print(f"当前数据库中最大的用户ID是: {max_userid}") 调试
    
    conn.close()

    if max_userid and max_userid.startswith("USER"):
        next_userid = int(max_userid[4:]) + 1
    else:
        next_userid = 1001

    new_userid = f"USER{next_userid}"

    # 检测新生成的用户ID是否已存在于数据库中
    while True:
        if not userid_exists(new_userid):
            break
        next_userid += 1
        new_userid = f"USER{next_userid}"

    return new_userid


# 检测新生成的用户ID
def userid_exists(userid):
    conn = getConnection()
    cur = conn.cursor()

    cur.execute("SELECT userid FROM user WHERE userid = ?", (userid,))
    existing_userid = cur.fetchone()

    conn.close()

    return existing_userid is not None

# 管理员菜单
def admin_menu(userid):
    while True:
        print("\n------------ 管理员菜单 ------------")
        print("1. 录入图书")
        print("2. 删除图书")
        print("3. 修改图书信息")
        print("4. 查询图书信息和状态")
        print("5. 总览图书馆图书信息和状态")
        print("6. 查询任意用户借书状态")
        print("0. 退出")
        
        choice = input("请输入选项: ")
        
        if choice == '1':
            addBook()
        elif choice == '2':
            delBook()
        elif choice == '3':
            modifyBook()
        elif choice == '4':
            searchBook_information()
        elif choice == '5':
            showAllData()
        elif choice == '6':
            query_user_books(userid)
        elif choice == '0':
            print("退出管理员菜单。")
            break
        else:
            print("无效的选项，请重新输入。")


# 用户菜单
def user_menu(userid):
    while True:
        print("\n------------ 用户菜单 ------------")
        print("1. 查询图书信息和状态")
        print("2. 借阅图书")
        print("3. 归还图书")
        print("4. 查询已借图书信息与状态")
        print("0. 退出")
        
        choice = input("请输入选项: ")
        
        if choice == '1':
            searchBook_information()
        elif choice == '2':
            showAllData()
            bookid = input("请输入要借阅的图书ID: ")
            borrowBook(userid, bookid)
        elif choice == '3':
            bookid = input("请输入要归还的图书ID: ")
            returnBook(userid, bookid)
        elif choice == '4':
            query_user_books(userid)
        elif choice == '0':
            print("退出用户菜单。")
            break
        else:
            print("无效的选项，请重新输入。")

#借书函数              
def borrowBook(userid, bookid):
    conn = getConnection()
    cur = conn.cursor()
    check = conn.cursor()
    check.execute("SELECT borrowid, returndate FROM borrowbooks WHERE userid = ? AND actualreturndate IS NULL", (userid,))
    borrow_record = check.fetchone()
    flag = 0
    if borrow_record:
        borrowid, returndate_str = borrow_record
        returndate = datetime.datetime.now().strptime(returndate_str,"%Y-%m-%d")
        current_date = datetime.datetime.now()
        
        overdue_days = (current_date - returndate).days


        if overdue_days > 0:
            print(f"您有图书逾期未归还！请归还后再借书！逾期天数：{overdue_days}")
            flag = 1
    if flag != 1:
        # 检查用户是否已经借阅了最大数量的书籍
        cur.execute("SELECT number FROM user WHERE userid = ?", (userid,))
        current_borrow_count = cur.fetchone()[0]

        max_borrow_limit = 2  # 每个用户最多借阅两本书，可以根据需要修改

        if current_borrow_count >= max_borrow_limit:
            print(f"您已借阅了最大数量的图书 ({max_borrow_limit} 本)，无法再借阅更多图书。")
        else:
        # 检查图书状态和剩余副本数
            cur.execute("SELECT copynum, lend FROM bookbase WHERE bookid = ?", (bookid,))
            record = cur.fetchone()
            if record:
                copynum, lend = record
                if lend < copynum:
                    # 更新图书表中的 lend 列
                    cur.execute("UPDATE bookbase SET lend = lend + 1 WHERE bookid = ?", (bookid,))

                    # 记录借书交易
                    borrowdate = datetime.datetime.now()
                    # 计算应归还日期为借书日期 + 3天
                    returndate = (borrowdate + datetime.timedelta(days=3)).strftime("%Y-%m-%d")
                    borrowdateq = borrowdate.strftime("%Y-%m-%d")
                    cur.execute("INSERT INTO borrowbooks (userid, bookid, borrowdate, returndate) VALUES (?, ?, ?, ?)", (userid, bookid, borrowdateq, returndate))

                    # 更新用户表中的借书数量
                    cur.execute("UPDATE user SET number = number + 1 WHERE userid = ?", (userid,))

                    conn.commit()
                    print("借书成功！")
                else:
                    print("对不起，该图书已被借完。")
            else:
                print("对不起，该图书不存在。")

    conn.close()

# 还书函数
def returnBook(userid, bookid):
    conn = getConnection()
    cur = conn.cursor()

    # 查询用户借阅记录
    cur.execute("SELECT borrowid, borrowdate FROM borrowbooks WHERE userid = ? AND bookid = ? AND actualreturndate IS NULL", (userid, bookid))
    transaction = cur.fetchone()

    if transaction:
        borrow_id, borrow_date_str = transaction
        borrow_date_str = borrow_date_str.strip()
        borrow_date = datetime.datetime.strptime(borrow_date_str, "%Y-%m-%d")
        current_date = datetime.datetime.now()

        # 计算借阅天数和逾期天数
        borrow_duration = (current_date - borrow_date).days
        over_due = max(borrow_duration - 3, 0)

        # 更新借阅记录
        cur.execute("UPDATE borrowbooks SET actualreturndate = ?, overduedate = ? WHERE borrowid = ?",
                    (current_date.strftime("%Y-%m-%d"), over_due, borrow_id))

        # 更新图书状态
        cur.execute("UPDATE bookbase SET lend = lend - 1 WHERE bookid = ?", (bookid,))

        # 更新用户表中的借书数量
        cur.execute("UPDATE user SET number = number - 1 WHERE userid = ?", (userid,))

        conn.commit()

        if over_due > 0:
            print(f"还书成功！逾期 {over_due} 天。")
        else:
            print("还书成功！")
    else:
        print("您未借阅过该图书或者该图书已归还。")

    conn.close()


#查询图书信息和状态
def query_user_books(userid):
    conn = getConnection()
    cur = conn.cursor()

    # 检查用户角色以确定是否显示所有用户
    cur.execute("SELECT character FROM user WHERE userid = ?", (userid,))
    user_record = cur.fetchone()

    if user_record and user_record[0] == "admin":
        # 对于管理员，列出所有用户
        print("管理员角色：")
        cur.execute("SELECT userid, username FROM user")
        users = cur.fetchall()

        print("用户列表：")
        for user in users:
            print(f"用户ID: {user[0]}, 用户名: {user[1]}")

        target_userid = input("输入要查询的用户ID：")
    else:
        # 对于普通用户，使用他们自己的ID
        target_userid = userid

    # 查询目标用户借阅的所有图书信息
    cur.execute("""
        SELECT b.bookid, b.bookname, b.writer, b.address, b.date, b.price, 
               CASE WHEN b.lend < b.copynum THEN '在库' ELSE '已借出' END AS 状态,
               bb.borrowdate, bb.returndate, bb.overduedate
        FROM borrowbooks bb
        JOIN bookbase b ON bb.bookid = b.bookid
        WHERE bb.userid = ? AND bb.actualreturndate IS NULL
    """, (target_userid,))
    borrowed_books = cur.fetchall()

    if borrowed_books:
        print("查询结果：")
        for book in borrowed_books:
            print(f"图书ID: {book[0]}, 书名: {book[1]}, 作者: {book[2]}, 出版地址: {book[3]}, 出版日期: {book[4]}, 价格: {book[5]}")#此处book[6]存储的是书本状态，修改（删除）繁琐所以保留
            print(f"借书日期: {book[7]}, 应归还日期: {book[8]}, 逾期天数: {book[9]}")
    else:
        if user_record and user_record[0] == "admin":
            print(f"未找到用户ID为 {target_userid} 的借阅记录或用户不存在。")
        else:
            print("该用户当前没有借阅图书。")

    conn.close()



# 总览图书馆图书信息和状态
def showAllData():
    print("-----------总览图书馆图书信息和状态------------")
    print("图书编号     书名\t      作者\t\t    出版地址 \t\t出版日期\t价格   副本数  借出数")
    dbinfo=getConnection()
    cur=dbinfo.cursor()
    cur.execute("select * from bookbase")
    records=cur.fetchall()
    for record in records:
        bookid, bookname, writer, address, date, price, copynum, lend = record
        bookname_width = len(bookname) + (sum(1 for c in bookname if unicodedata.east_asian_width(c) in ('W', 'F')) - len(bookname))
        writer_width = len(writer)+(sum(1 for d in writer if unicodedata.east_asian_width(d) in ('W', 'F')) - len(writer) )
        address_width = len(address)+(sum(1 for e in address if unicodedata.east_asian_width(e) in('W','F')) - len(address))
        price_width = len(str(price))+(sum(1 for m in str(price) if unicodedata.east_asian_width(m) in ('W','F'))-len(str(price)))
        # 使用字符串的格式化来控制每列的宽度，确保对齐
        print(f"{bookid.ljust(12,' ')} {bookname.ljust(8 + (8 - bookname_width),' ')} {writer.ljust(10+(10-writer_width),' ')} {address.ljust(10+(10-address_width),' ')} {date.ljust(16,' ')} {str(price).ljust(5+(5-price_width),' ')} {str(copynum).ljust(6,' ')} {str(lend).ljust(7,' ')}")
    cur.close()


# 获取图书信息
def getBookInfo():
    bookname = input("请输入书名：")
    writer = input("请输入作者：")
    address = input("请输入出版地址：")
    while True:
        date_str = input("请输入出版日期(格式为YYYY-MM-DD)：")
        try:
            date = datetime.datetime.strptime(date_str, "%Y-%m-%d")
            break
        except ValueError:
            print("日期格式错误，请重新输入。")
    while True:
        try:
            price = float(input("请输入价格："))
            break
        except ValueError:
            print("价格格式错误，请重新输入。")
    return bookname, writer, address, date_str, price, 3  # 3 是副本数，可以根据实际情况设定

# 录入图书
def addBook():
    sepline = "--------------录入图书----------------"
    print(sepline)
    
    # 获取图书信息，不包括书本编号
    record = getBookInfo()
    
    dbinfo = getConnection()
    
    # 查询当前数据库中最大的书本编号
    max_bookid = getMaxBookID(dbinfo)
    
    # 确定新书籍的编号
    if max_bookid is None:
        new_bookid = 1001  # 如果数据库中没有书籍，则从 1001 开始
    else:
        new_bookid = max_bookid + 1
    
    # 将新书本编号加入到记录中
    record = (new_bookid,) + record  # 在元组前面添加书本编号
    
    sqlstr = "INSERT INTO bookbase (bookid, bookname, writer, address, date, price, copynum) " \
             "VALUES (?, ?, ?, ?, ?, ?, ?)"
    
    try:
        cur = dbinfo.cursor()
        cur.execute(sqlstr, record)
        dbinfo.commit()
        print("-------------录入成功------------")
        showAllData()
    except sqlite3.Error as e:
        print(f"数据库错误：{e}")
    finally:
        dbinfo.close()

# 获取最大的书本编号
def getMaxBookID(db):
    try:
        cur = db.cursor()
        cur.execute("SELECT MAX(bookid) FROM bookbase")
        result = cur.fetchone()[0]
        return int(result) if result is not None else 0
    except sqlite3.Error as e:
        print(f"数据库错误：{e}")
        return None


# 查询图书是否存在
def checkBookExists(book_id):
    try:
        dbinfo = getConnection()
        cursor = dbinfo.cursor()
        
        # 查询是否存在符合条件的记录
        cursor.execute("SELECT COUNT(*) FROM bookbase WHERE BookID = ?", (book_id,))
        count = cursor.fetchone()[0]
        
        return count > 0
    
    except sqlite3.Error as e:
        print("查询图书是否存在时出错:", e)
        return False
    finally:
        if dbinfo:
            dbinfo.close()

# 删除图书函数
def delBook():
    print("-----------------删除图书----------------")
    try:
        dbinfo = getConnection()
        cursor = dbinfo.cursor()
        
        # 获取用户输入的 BookID
        choice = input("请输入要删除的图书的 BookID:")
        
        # 首先检查图书是否存在
        if not checkBookExists(choice):
            print(f"编号为 {choice} 的图书不存在。")
            return
        
        # 使用参数化查询来执行删除操作
        sqlstr = "DELETE FROM bookbase WHERE BookID = ?"
        cursor.execute(sqlstr, (choice,))
        
        # 提交事务
        dbinfo.commit()
        
        # 检查是否删除成功
        if cursor.rowcount > 0:
            print("------------图书删除成功----------")
        else:
            print("图书删除失败。")
        
        # 显示更新后的数据
        showAllData()
        
    except sqlite3.Error as e:
        print("删除图书时出现错误:", e)
    finally:
        if dbinfo:
            dbinfo.close()

# 修改图书信息
def modifyBook():
    sepline = "--------- 修改图书信息 ---------------"
    print(sepline)
    
    dbinfo = getConnection()
    bookname = input("请输入要修改的图书名称: ")
    
    # 查询要修改的图书信息
    sqlstr = "SELECT bookid, bookname, writer, address, date, price, lend "\
             "FROM bookbase "\
             "WHERE bookname = ?"
    
    cur = dbinfo.cursor()
    cur.execute(sqlstr, (bookname,))
    record = cur.fetchone()  
    
    if not record:
        print(f"未找到书名为 {bookname} 的图书。修改失败。")
        cur.close()
        dbinfo.close()
        return
    else:
        bookid, bookname, writer, address, date, price, lend = record
        print(f"当前图书信息：\n图书编号：{bookid}\n书名：{bookname}\n作者：{writer}\n出版社：{address}\n出版日期：{date}\n价格：{price}\n借出数：{lend}")
        
        # 让用户选择要修改的部分，包括图书编号
        print("\n请选择要修改的部分:")
        print("1. 修改图书编号")
        print("2. 修改作者")
        print("3. 修改出版社")
        print("4. 修改出版日期")
        print("5. 修改价格")
        print("6. 修改借出数")
        print("7. 修改全部信息")
        print("直接回车可退出")

        choice = input("请输入选项 (1-7): ")
        
        # 根据用户选择进行相应的修改
        if choice == '1':
            new_bookid = input(f"新的图书编号 ({bookid}): ").strip()
            if not new_bookid:  # 如果新图书编号为空，则修改失败
                print("修改失败。")
                cur.close()
                dbinfo.close()
                return
            cur.execute("UPDATE bookbase SET bookid = ? WHERE bookname = ?", (new_bookid, bookname))
        elif choice == '2':
            new_writer = input(f"新的作者 ({writer}): ").strip()
            if not new_writer:  # 如果新作者为空，则修改失败
                print("修改失败。")
                cur.close()
                dbinfo.close()
                return
            cur.execute("UPDATE bookbase SET writer = ? WHERE bookname = ?", (new_writer, bookname))
        elif choice == '3':
            new_address = input(f"新的出版社 ({address}): ").strip()
            if not new_address:  # 如果新的address为空，则修改失败
                print("修改失败。")
                cur.close()
                dbinfo.close()
                return
            cur.execute("UPDATE bookbase SET address = ? WHERE bookname = ?", (new_address, bookname))
        elif choice == '4':
            new_date = input(f"新的出版日期 ({date}): ").strip()
            if not new_date:  # 如果新出版日期为空，则修改失败
                print("修改失败。")
                cur.close()
                dbinfo.close()
                return
            cur.execute("UPDATE bookbase SET date = ? WHERE bookname = ?", (new_date, bookname))
        elif choice == '5':
            new_price = input(f"新的价格 ({price}): ").strip()
            if not new_price:  # 如果新图书价格为空，则修改失败
                print("修改失败。")
                cur.close()
                dbinfo.close()
                return
            new_price = float(new_price)
            cur.execute("UPDATE bookbase SET price = ? WHERE bookname = ?", (new_price, bookname))
        elif choice == '6':
            new_lend = input(f"新的借出数 ({lend}): ").strip()
            if not new_lend:  # 如果新图书借出数为空，则修改失败
                print("修改失败。")
                cur.close()
                dbinfo.close()
                return
            new_lend = int(new_lend)
            cur.execute("UPDATE bookbase SET lend = ? WHERE bookname = ?", (new_lend, bookname))
        elif choice == '7':
            new_bookid = input(f"新的图书编号 ({bookid}): ").strip()
            if not new_bookid:  # 如果新图书编号为空，则修改失败
                print("修改失败。")
            new_bookname, new_writer, new_address, new_date, new_price,new_copynum = getBookInfo()
            new_lend = input(f"新的借出数 ({lend}): ").strip()
            if not new_lend:  # 如果新图书借出数为空，则修改失败
                print("修改失败。")
            cur.execute("UPDATE bookbase SET bookid = ?, bookname = ?, writer = ?, address = ?, date = ?, price = ?, lend = ? "\
                        "WHERE bookname = ?", (new_bookid, new_bookname, new_writer, new_address, new_date, new_price, new_lend, bookname))
        else:
            print("无效的选项。修改失败。")
            cur.close()
            dbinfo.close()
            return
        
        dbinfo.commit()
        print("-------------- 图书信息修改成功 -------------")
    
    cur.close()
    dbinfo.close()
    
    # 显示更新后的所有记录
    showAllData()

# 查询某本图书信息和状态
def searchBook_information():
    sepline = "------------查询图书信息和状态-----------------"
    print(sepline)
    conn = getConnection()
    
    search_option = input("请选择查询方式（1. 根据图书ID查询，2. 根据图书名称查询）: ")
    choice = input("请输入查询内容: ")

    if search_option == '1':
        sqlstr = "SELECT bookid, bookname, writer, address, date, price, copynum, lend, "\
                 "CASE WHEN copynum > lend THEN '在库' ELSE '借出' END AS 状态 "\
                 "FROM bookbase "\
                 "WHERE bookid = ?"
    elif search_option == '2':
        sqlstr = "SELECT bookid, bookname, writer, address, date, price, copynum, lend, "\
                 "CASE WHEN copynum > lend THEN '在库' ELSE '借出' END AS 状态 "\
                 "FROM bookbase "\
                 "WHERE bookname = ?"
    else:
        print("无效的选项，请重新运行程序并输入有效的选项。")
        conn.close()
        return
    
    cur = conn.cursor()
    cur.execute(sqlstr, (choice,))
    record = cur.fetchone()  # 获取唯一的查询结果
    
    if not record:  # 如果结果集为空
        print(f"未找到查询内容为 '{choice}' 的图书。")
    else:
        bookid, bookname, writer, address, date, price, copynum, lend, status = record
        print(f"图书编号：{bookid}\n书名：{bookname}\n作者：{writer}\n出版社：{address}\n出版日期：{date}\n价格：{price}\n状态：{status}")
        if status == '在库':  # 如果状态为在库，计算并输出剩余可借阅的本数
            remaining_copies = copynum - lend
            print(f"剩余可借阅的本数为: {remaining_copies}")
    
    cur.close()
    conn.close()
    print("--------------查询完成！----------")




#程序入口
if __name__ == "__main__":
    login()