python链接mysql数据库的图书管理系统

注意，三个文件放在同一文件夹下
文件二中数据库连接的密码需要修改


文件一

import tkinter as tk
from tkinter import messagebox

from 连接数据库 import *

# 登录窗体
def form1():
    windows1 = tk.Tk()
    windows1.title("登录界面")


    photo = tk.PhotoImage(file="QQ图片20240710212428.gif")
    photo = photo.subsample(3, 3)
    l = tk.Label(windows1,  image=photo)

    def us():
        windows1.destroy()
        form2()

    def ad():
        windows1.destroy()
        form3()

    user = tk.Button(windows1, bg='blue', fg='white', width=50, height=4, text='用户登录', font=('Arial', 12), command=us)
    administrator = tk.Button(windows1, bg='green', fg='white', width=50, height=4, text='管理员登录', font=('Arial', 12), command=ad)

    l.pack(fill='x')
    user.pack(fill='x')
    administrator.pack(fill='x')
    windows1.mainloop()

# 用户登录窗体
def form2():
    def Return():
        windows2.destroy()
        form1()

    def Inspect():
        var1 = tk.StringVar()
        var2 = tk.StringVar()
        c = 1
        sql='''SELECT account FROM account_user'''
        Account=selectaccount(sql)

        var1.set(en1.get())
        var2.set(en2.get())
        var3 = var1.get()
        var4 = var2.get()

        for row in Account:
            if row[0] == var3:
                messagebox.showinfo("提示", "账号正确!")
                c = 0
                break
        if c == 1:
            messagebox.showwarning("警告", "账号错误!")
            login1(var3,var4)

            messagebox.showinfo("提示", "账号注册成功!")

        d = 1
        sql='''SELECT password FROM account_user WHERE account=%s'''
        Password=selectpassword(sql,var3)
        if Password is not None:
            for row in Password:
                if row[0] == var4:
                    messagebox.showinfo("提示", "密码正确!")
                    d = 0
                    windows2.destroy()
                    form4(var3)
                    break
            if d == 1:
                messagebox.showwarning("警告", "密码错误!")

    windows2 = tk.Tk()
    windows2.title("用户登录")

    l2 = tk.Label(windows2, width=50, height=2, text='用户登录', bg='blue', fg='white', font=('Arial', 14))
    l2.pack(fill='x')

    l3 = tk.Label(windows2, width=50, height=2, text='账号', font=('Arial', 12))
    l3.pack(fill='x')
    en1 = tk.Entry(windows2)
    en1.pack(fill='x')

    l4 = tk.Label(windows2, width=50, height=2, text='密码', font=('Arial', 12))
    l4.pack(fill='x')
    en2 = tk.Entry(windows2, show='*')
    en2.pack(fill='x')

    button1 = tk.Button(windows2, text='确定', width=50, height=2, font=('Arial', 12), command=Inspect)
    button1.pack(fill='x')
    button2 = tk.Button(windows2, text='返回', width=50, height=2, font=('Arial', 12), command=Return)
    button2.pack(fill='x')

    windows2.mainloop()

# 管理员登录窗体
def form3():
    def Return():
        windows3.destroy()
        form1()

    def Inspect():
        var1 = tk.StringVar()
        var2 = tk.StringVar()
        c = 1

        sql='''SELECT account FROM account_admi'''
        Account=selectaccount(sql)

        var1.set(en1.get())
        var2.set(en2.get())
        var3 = var1.get()
        var4 = var2.get()

        for row in Account:
            if row[0] == var3:
                messagebox.showinfo("提示", "账号正确!")
                global accountname
                accountname = var3
                c = 0
                break
        if c == 1:
            messagebox.showwarning("警告", "账号错误!")
            login2(var3, var4)
            messagebox.showinfo("提示", "账号注册成功!")


        d = 1
        sql='''SELECT password FROM account_admi WHERE account=%s'''
        Passwor=selectpassword(sql,var3)

        for row in Passwor:
            if row[0] == var4:
                messagebox.showinfo("提示", "密码正确!")
                d = 0
                windows3.destroy()
                form5()
                break
        if d == 1:
            messagebox.showwarning("警告", "密码错误!")

    windows3 = tk.Tk()
    windows3.title("管理员登录")

    l2 = tk.Label(windows3, width=50, height=2, text='管理员登录', bg='blue', fg='white', font=('Arial', 14))
    l2.pack(fill='x')

    l3 = tk.Label(windows3, width=50, height=2, text='账号', font=('Arial', 12))
    l3.pack(fill='x')
    en1 = tk.Entry(windows3)
    en1.pack(fill='x')

    l4 = tk.Label(windows3, width=50, height=2, text='密码', font=('Arial', 12))
    l4.pack(fill='x')
    en2 = tk.Entry(windows3, show='*')
    en2.pack(fill='x')

    button1 = tk.Button(windows3, text='确定', width=50, height=2, font=('Arial', 12), command=Inspect)
    button1.pack(fill='x')
    button2 = tk.Button(windows3, text='返回', width=50, height=2, font=('Arial', 12), command=Return)
    button2.pack(fill='x')

    windows3.mainloop()

# 用户功能窗体
def form4(var3):
    def on_listbox_select(event):
        widget = event.widget
        selection = widget.curselection()
        if selection:
            index = selection[0]
            selected_value.set(widget.get(index))

    def repay_():
        selection = lb.curselection()
        if selection:
            index = selection[0]
            selected_text = lb.get(index)
            name1 = selected_text.split(',')[1].split(':')[1].strip()
            account1 = selected_text.split(',')[0].split(':')[1].strip()


            lb.delete(index)
            deleteborrow(account1,name1)
            messagebox.showinfo("删除成功", f"你还了: {name1}")
            selected_value.set("")
        else:
            messagebox.showwarning("警告", "没有选择任何条目")

    def Return():
        windows4.destroy()
        form2()

    def borrow_k(var3, bookname):
        borrowbook(bookname, var3)
        messagebox.showinfo("提示", "借书成功")
        deleteborrow(var3, ' ')
        userborrow = selectborrow(var3)

        lb.delete(0, tk.END)
        for row in userborrow:
            lb.insert(tk.END,f"账户: {row[0]}, 书名: {row[1]}, 借期: {row[2]}, 还期: {row[3]}")


    def query_book():
        text1 = en1.get()
        name = select1(text1)
        results_str = "\n".join([f"编号: {row[0]}, 名字: {row[1]}, 作者: {row[2]}, 出版社: {row[3]}, 出版日期: {row[4]}, 价格: {row[5]}, 读本数量: {row[6]}" for row in name])
        results_var.set(results_str)

    windows4 = tk.Tk()
    windows4.title("用户功能")
    selected_value = tk.StringVar()
    results_var = tk.StringVar()
    l2 = tk.Label(windows4, width=50, height=2, text='欢迎，用户', bg='green', fg='white', font=('Arial', 14))
    l2.grid(row=0, column=0)

    l3 = tk.Label(windows4, text='输入查询图书的书名', font=('Arial', 12))
    l3.grid(row=1, column=0)
    en1 = tk.Entry(windows4)
    en1.grid(row=1, column=1)

    button2 = tk.Button(windows4, text='借书', font=('Arial', 12), command=lambda: borrow_k(var3, en1.get()))
    button3 = tk.Button(windows4, text='查询图书信息', font=('Arial', 12), command=query_book)
    button4 = tk.Button(windows4, text='返回', width=50, height=2, font=('Arial', 12), command=Return)
    button5 = tk.Button(windows4, text='还书', font=('Arial', 12), command=repay_)

    button2.grid(row=3, column=2)
    button3.grid(row=2, column=1)
    button4.grid(row=7, column=0)
    button5.grid(row=4, column=2)


    userborrow = selectborrow(var3)

    sc = tk.Scrollbar(windows4)
    sc.grid(row=4, column=1, sticky='ns')

    lb = tk.Listbox(windows4, yscrollcommand=sc.set)
    if userborrow is not None:
        for row in userborrow:
            results_str = f"账户: {row[0]}, 书名: {row[1]}, 借期: {row[2]}, 还期: {row[3]}"
            lb.insert(tk.END, results_str)
    else:
        results_str = f"账户: {' '}, 书名: {' '}, 借期: {' '}, 还期: {' '}"
        lb.insert(tk.END, results_str)
    lb.grid(row=4, column=0)
    lb.bind('<<ListboxSelect>>', on_listbox_select)
    sc.config(command=lb.yview)

    l6 = tk.Label(windows4, textvariable=results_var, font=('Arial', 12), wraplength=400, justify="left")
    l6.grid(row=3, column=1)


    windows4.mainloop()

# 管理员功能窗体
def form5():
    def on_listbox_select(event):
        widget = event.widget
        selection = widget.curselection()
        if selection:
            index = selection[0]
            selected_value.set(widget.get(index))

    def repay_1():
        selection = lb.curselection()
        if selection:
            index = selection[0]
            selected_text = lb.get(index)
            name1 = selected_text.split(',')[0].split(':')[1].strip()

            lb.delete(index)
            delete1(name1)
            messagebox.showinfo("删除成功", f"你删除了: {name1}")
            selected_value.set("")
        else:
            messagebox.showwarning("警告", "没有选择任何条目")

    def Return():
        windows5.destroy()
        form3()

    def select_1():
        text1 = en1.get()
        name = select1(text1)
        results_str = "\n".join([f"编号: {row[0]}, 名字: {row[1]}, 作者: {row[2]}, 出版社: {row[3]}, 出版日期: {row[4]}, 价格: {row[5]}, 读本数量: {row[6]}"for row in name])
        results_var.set(results_str)


    def select_2():
        user_info_list, user_borrow_counts = look1()
        new_window = tk.Toplevel(windows5)
        new_window.title("用户借阅信息")
        if not user_info_list:
            results_str = "没有任何用户信息"
        else:
            results_str = "\n\n".join(
                [f"用户账号: {row[0]}, 密码: {row[1]}, 书名: {row[2]}, 借书日期: {row[3]}, 还书日期: {row[4]}" for row in
                 user_info_list])
            results_str += "\n\n借书数量统计:\n"
            results_str += "\n".join(
                [f"用户账号: {account}, 借书数量: {count}" for account, count in user_borrow_counts.items()])

        results_var.set(results_str)
        l12 = tk.Label(new_window, width=100, height=20, textvariable=results_var, wraplength=800, justify="left")
        l12.pack(padx=10, pady=10)

    def yes3():
        if any(en.get() == '' for en in [en2, en3, en4, en5, en6, en7]):
            messagebox.showwarning("警告", "所有字段都必须填写")

            return

        flag = 1

        name = select2()

        for row in name:
            if en3.get() == row[1]:
                change1(en2.get(), en3.get(), en4.get(), en5.get(), en6.get(), en7.get(), en8.get())
                messagebox.showinfo("提示", "修改成功!")
                lb.delete(0, tk.END)
                for row in select2():
                    lb.insert(tk.END, f"名字: {row[1]}, 库存量：{row[6]}")
                flag = 0
                break
        if flag == 1:
            add1(en2.get(), en3.get(), en4.get(), en5.get(), en6.get(), en7.get(), en8.get())
            messagebox.showinfo("提示", "录入成功!")
            lb.delete(0, tk.END)
            for row in select2():
                lb.insert(tk.END, f"名字: {row[1]}, 库存量：{row[6]}")

    windows5 = tk.Tk()
    windows5.title("管理员功能")
    selected_value = tk.StringVar()
    results_var = tk.StringVar()
    l2 = tk.Label(windows5, width=50, height=2, text='欢迎，管理员', bg='blue', fg='white', font=('Arial', 14))
    l2.grid(row=0)

    l3 = tk.Label(windows5, text='输入查询图书的书名或用户姓名', font=('Arial', 12))
    l3.grid(row=1, column=0)
    en1 = tk.Entry(windows5)
    en1.grid(row=1, column=1)

    button1 = tk.Button(windows5, text='查询图书', width=50, height=2, font=('Arial', 12), command=select_1)
    button2 = tk.Button(windows5, text='用户借书信息', width=50, height=2, font=('Arial', 12), command=select_2)
    button1.grid(row=2, column=0)
    button2.grid(row=2, column=1)

    l4 = tk.Label(windows5, text='输入录入书籍的信息：', font=('Arial', 12))
    l4.grid(row=3, column=0)

    labels_texts = ['编号', '书名', '作者', '出版社', '出版日期', '价格', '存入量']
    entries = []

    for i, text in enumerate(labels_texts):
        l = tk.Label(windows5, text=text, font=('Arial', 12))
        l.grid(row=4 + i, column=0)
        en = tk.Entry(windows5)
        en.grid(row=4 + i, column=1)
        entries.append(en)

    en2, en3, en4, en5, en6, en7, en8 = entries

    button3 = tk.Button(windows5, text='确定', width=50, height=2, font=('Arial', 12), command=yes3)
    button3.grid(row=11, column=1)

    button4 = tk.Button(windows5, text='返回', width=50, height=2, font=('Arial', 12), command=Return)
    button4.grid(row=15, column=0)
    button5 = tk.Button(windows5, text='删除', font=('Arial', 12), width=50, height=2, command=repay_1)
    button5.grid(row=15, column=1)

    sc = tk.Scrollbar(windows5)
    sc.grid(row=13, column=1, sticky='ns')

    lb = tk.Listbox(windows5, yscrollcommand=sc.set)
    for row in select2():
        lb.insert(tk.END, f"名字: {row[1]}, 库存量：{row[6]}")
    lb.grid(row=12, column=0)
    lb.bind('<<ListboxSelect>>', on_listbox_select)
    sc.config(command=lb.yview)


    l11 = tk.Label(windows5, width=50, height=2, textvariable=results_var, font=('Arial', 12), wraplength=300, justify="left")
    l11.grid(row=2, column=5)

    windows5.mainloop()

form1()

文件二

def add1(id,name,writer,lic,licdate,lex,num):
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase')
    cursor = conn.cursor()
    sql="INSERT INTO liberayin(id,name,writer,lic,licdate,lex,num) VALUES(%s,%s,%s,%s,%s,%s,%s)"
    values=(id,name,writer,lic,licdate,lex,num,)
    cursor.execute(sql,values)
    conn.commit()

def select1(name1):#查询一本书
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase', charset='utf8mb4')
    cursor = conn.cursor()
    sql = "SELECT * FROM liberayin WHERE name = %s"

    try:
        # Execute SQL statement with parameter
        cursor.execute(sql, (name1,))
        # Fetch all the records
        results = cursor.fetchall()
        if not results:
            print("没有找到图书")
        else:
           return results
    except Exception as e:
        print(f"Error: unable to fetch data. {e}")
    finally:
        cursor.close()
        conn.close()
def select2():#查询所有书
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase', charset='utf8mb4')
    cursor = conn.cursor()
    sql = "SELECT * FROM liberayin"

    try:
        # Execute SQL statement with parameter
        cursor.execute(sql)
        # Fetch all the records
        results = cursor.fetchall()
        if not results:
            print("没有任何图书信息")
        else:
            return results

    except Exception as e:
        print(f"Error: unable to fetch data. {e}")
    finally:
        cursor.close()
        conn.close()
def change1(new_id, name1, new_writer, new_lic, new_licdate, new_lex,new_num):#改变图书信息
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase', charset='utf8mb4')
    cursor = conn.cursor()
    sql = "UPDATE liberayin SET id = %s, writer = %s, lic = %s, licdate = %s, lex= %s ,num=%s WHERE name = %s"

    try:
        # Execute SQL statement with parameters
        cursor.execute(sql, (new_id, new_writer, new_lic, new_licdate, new_lex,new_num, name1))
        # Commit the changes to the database
        conn.commit()
        if cursor.rowcount > 0:
            print("更新成功")
        else:
            print("没有找到匹配的图书")
    except Exception as e:
        print(f"Error: unable to update data. {e}")
        # Rollback in case of error
        conn.rollback()
    finally:
        cursor.close()
        conn.close()


def look1():  # 查询用户借阅图书信息
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase', charset='utf8mb4')
    cursor = conn.cursor()
    sql = """
    SELECT 
        account_user.account AS user_account,
        account_user.password AS user_password,
        user_borrow.name AS user_name,
        user_borrow.borrow_date,
        user_borrow.return_date
    FROM 
        account_user
    JOIN 
        user_borrow ON account_user.account = user_borrow.account
    JOIN 
        liberayin ON user_borrow.name = liberayin.name
    WHERE 
        account_user.account IN (
            SELECT account
            FROM user_borrow
            GROUP BY account
            HAVING COUNT(name) <= 2
        );
    """
    try:
        # Execute SQL statement
        cursor.execute(sql)
        # Fetch all the records
        results = cursor.fetchall()
        if not results:
            print("没有任何用户信息")
            return [], {}
        else:
            user_borrow_counts = {}
            user_info = []

            for row in results:
                user_account = row[0]
                user_password = row[1]
                user_name = row[2]
                borrow_date = row[3]
                return_date = row[4]

                if user_account not in user_borrow_counts:
                    user_borrow_counts[user_account] = 0
                user_borrow_counts[user_account] += 1

                # Append user information and borrow details as a tuple
                user_info.append((user_account, user_password, user_name, borrow_date, return_date))

            # Update borrow counts in the database
            for user_account, borrow_count in user_borrow_counts.items():
                sql1 = "UPDATE account_user SET borrownum = %s WHERE account = %s "
                try:
                    # Execute SQL statement with parameters
                    cursor.execute(sql1, (borrow_count, user_account))
                    # Commit the changes to the database
                    conn.commit()
                except Exception as e:
                    print(f"Error: unable to update data. {e}")
                    # Rollback in case of error
                    conn.rollback()

            # Return the list of user information and borrow details and borrow counts
            return user_info, user_borrow_counts

    except Exception as e:
        print(f"Error: unable to fetch data. {e}")
        return [], {}
    finally:
        cursor.close()
        conn.close()


def delete1(name1):#删除书
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase')
    cursor = conn.cursor()
    sql = "DELETE FROM liberayin WHERE name=%s"
    try:

        values = (name1,)
        cursor.execute(sql, values)
        conn.commit()

        print(f"成功删除图书信息: {name1}")
    except pymysql.Error as e:
        print(f"出现错误，图书不存在！{e}")
    finally:
        cursor.close()
        conn.close()
def selectname(name1):#查询用户信息
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase', charset='utf8mb4')
    cursor = conn.cursor()
    sql = "SELECT * FROM account_user WHERE account = %s"

    try:
        # Execute SQL statement with parameter
        cursor.execute(sql, (name1,))
        # Fetch all the records
        results = cursor.fetchall()
        if not results:
            print("没有数据")
        else:
           return results
    except Exception as e:
        print(f"Error: unable to fetch data. {e}")
    finally:
        cursor.close()
        conn.close()
def selectborrow(name1):#查询用户借书信息
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase', charset='utf8mb4')
    cursor = conn.cursor()
    sql = "SELECT * FROM user_borrow WHERE account = %s"
    try:
        # Execute SQL statement with parameter
        cursor.execute(sql, (name1,))
        # Fetch all the records
        results = cursor.fetchall()
        if not results:
            print("没有数据")
        else:
           return results
    except Exception as e:
        print(f"Error: unable to fetch data. {e}")
    finally:
        cursor.close()
        conn.close()
def borrowbook(name1, name2):
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase')
    cursor = conn.cursor()

    def borrowbook1():
        sql_check_user = "SELECT account FROM account_user WHERE account = %s"
        sql_insert_borrow = "INSERT INTO user_borrow(account, name,borrow_date,return_date) VALUES(%s, %s,%s,%s)"

        try:
            cursor.execute(sql_check_user, (name2,))
            user_exists = cursor.fetchone()
            if not user_exists:
                print(f"用户 {name2} 不存在，请先创建用户。")
            else:
                name3='2022'
                name4='2024'
                cursor.execute(sql_insert_borrow, (name2, name1,name3,name4))
                conn.commit()
        except Exception as e:
            print(f"Error: unable to insert data. {e}")

    try:
        sql_check_book = "SELECT * FROM liberayin WHERE name = %s"
        cursor.execute(sql_check_book, (name1,))
        results = cursor.fetchall()
        if not results:
            print("没有找到图书")
        else:
            borrowbook1()
    except Exception as e:
        print(f"Error: unable to fetch data. {e}")
    finally:
        cursor.close()
        conn.close()
def deleteborrow(account1,name1):#删除书
    import pymysql

    connn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase')
    cursor = connn.cursor()
    try:

        sql = "DELETE FROM user_borrow WHERE account=%s AND name=%s"

        values = (account1,name1,)
        cursor.execute(sql, values)

        connn.commit()


    except pymysql.Error as e:

        print(f"出现错误，图书不存在！{e}")
    finally:

        cursor.close()
        connn.close()


def login1(var3, var4):
    import pymysql
    conn = pymysql.connect(
        host='localhost', user='root', password='mjxt2004', db='mydatabase'
    )
    cursor = conn.cursor()
    try:
        cursor.execute('INSERT INTO account_user (account, password ) VALUES (%s, %s)', [var3, var4])
        conn.commit()
    except Exception as e:
        print(f"Error: unable to fetch data. {e}")
    finally:
        cursor.close()
        conn.close()
def login2(var3, var4):
    import pymysql
    conn = pymysql.connect(
        host='localhost', user='root', password='mjxt2004', db='mydatabase'
    )
    cursor = conn.cursor()
    try:
        cursor.execute('INSERT INTO account_admi (account, password ) VALUES (%s, %s)', [var3, var4])
        conn.commit()
    except Exception as e:
        print(f"Error: unable to fetch data. {e}")
    finally:
        cursor.close()
        conn.close()

def selectaccount(sql):#名字登录验证
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase', charset='utf8mb4')
    cursor = conn.cursor()

    try:
        # Execute SQL statement with parameter
        cursor.execute(sql)
        # Fetch all the records
        Account = cursor.fetchall()
        if not  Account:
            print("没有数据")
        else:
           return  Account
    except Exception as e:
        print(f"Error: unable to fetch data. {e}")
    finally:
        cursor.close()
        conn.close()


def selectpassword(sql,var3):  # 查询密码信息
    import pymysql
    conn = pymysql.connect(host='localhost', user='root', password='mjxt2004', db='mydatabase', charset='utf8mb4')
    cursor = conn.cursor()

    try:
        # Execute SQL statement with parameter
        value=(var3,)
        cursor.execute(sql,value)
        Password = cursor.fetchall()
        if not Password:
            print("没有数据")
        else:
            return Password
    except Exception as e:
        print(f"Error: unable to fetch data. {e}")
    finally:
        cursor.close()
        conn.close()

文件三

一个gif类型的图片 命名为QQ图片20240710212428.gif

另

mysql数据库建表

效果参考