library.py

# Library Management System using Python and MySQL

# Import necessary libraries
# It uses the Tkinter library for the GUI and pymysql for database connectivity.
import tkinter as tkAdd 
from tkinter import ttk # Themed widgets (e.g., styled buttons)
import tkinter as tk # Standard GUI components
import pymysql 
from tkinter import messagebox # For showing messages to the user

# The main class 'library' encapsulates all functionalities of the application
# init method initializes the main GUI window with customized appearance, and adds functional buttons 
class library():
    def __init__(self,root): # Th
        self.root = root
        self.root.configure(bg="light gray")

        self.root.title("Library Management")

        self.width = self.root.winfo_screenwidth()
        self.height = self.root.winfo_screenheight()
        self.root.geometry(f"{self.width}x{self.height}+0+0")

        label = tk.Label(self.root, bg=self.clr(200,100,180),bd=3,fg="white", relief="groove", text="Library Management", font=("Arial",50,"bold"))
        label.pack(side="top",fill="x")

        # input frame

        inFrame = tk.Frame(self.root,bg=self.clr(150,100,220),bd=4,relief="ridge")
        inFrame.place(width=self.width/3, height=self.height-180, x=80,y=100)

        regBtn = tk.Button(inFrame, command=self.regFun,text="Register Student",bg="light green",bd=2, font=("Arial",15,"bold"),width=20)
        regBtn.grid(row=0,column=0, padx=80, pady=80)

        resBtn =tk.Button(inFrame, command=self.resBook,text="Reserve Book",bg="light green",bd=2, font=("Arial",15,"bold"),width=20)
        resBtn.grid(row=1, column=0, padx=80, pady=60)

        retBtn = tk.Button(inFrame, command=self.retFun,text="Return Book",bg="light green",bd=2, font=("Arial",15,"bold"),width=20)
        retBtn.grid(row=2, column=0, padx=80, pady=60)

        # list Frame

        self.listFrame = tk.Frame(self.root, bd=4, relief="groove", bg=self.clr(120,220,100))
        self.listFrame.place(width=self.width/2, height=self.height-180,x=self.width/3+130,y=100)
        
        self.tabFun()
        self.showBk()
        
# tabFun displays a book list using a styled Treeview widget with horizontal and vertical scrollbars for easy navigation.
    def tabFun(self):
        tabFrame = tk.Frame(self.listFrame, bd=4,relief="sunken",bg=self.clr(200,100,110))
        tabFrame.place(width=self.width/2-60,height=self.height-280,x=30, y=70)

        x_scrol= tk.Scrollbar(tabFrame,orient="horizontal")
        x_scrol.pack(side="bottom",fill="x")

        y_scrol =tk.Scrollbar(tabFrame,orient="vertical")
        y_scrol.pack(side="right",fill="y")

        self.table = ttk.Treeview(tabFrame,columns=("bId","bname","quant"),xscrollcommand=x_scrol.set, yscrollcommand=y_scrol.set)

        x_scrol.config(command=self.table.xview)
        y_scrol.config(command=self.table.yview)

        self.table.heading("bId", text="Book_ID")
        self.table.heading("bname", text="Book_Name")
        self.table.heading("quant", text="Quantity")
        self.table["show"] = "headings"

        self.table.column("bId", width=170)
        self.table.column("bname",width=170)
        self.table.column("quant", width=170)

        self.table.pack(fill="both", expand=1)
# The regfun method displays student entry form (Roll Number, Name, Subject)
# "OK" button triggers insertFun() to store the data in the database
    def regFun(self):
        self.regFrame = tk.Frame(self.root,bd=3,relief="ridge", bg=self.clr(150,150,150))
        self.regFrame.place(width=self.width/3,height=self.height-180, x=self.width/3+120,y=100)

        rnLbl = tk.Label(self.regFrame, fg="white",bg=self.clr(150,150,150),text="RollNo:",font=("Arial",15,"bold"))
        rnLbl.grid(row=0, column=0, padx=20,pady=30)
        self.rnIn = tk.Entry(self.regFrame, bd=1, font=("Arial",15,"bold"),width=20)
        self.rnIn.grid(row=0,column=1,padx=10, pady=30)

        nameLble = tk.Label(self.regFrame, fg="white",bg=self.clr(150,150,150),text="Name:",font=("Arial",15,"bold"))
        nameLble.grid(row=1,column=0, padx=20,pady=30)
        self.nameIn = tk.Entry(self.regFrame, bd=1, font=("Arial",15,"bold"),width=20)
        self.nameIn.grid(row=1,column=1,padx=10, pady=30)

        subLbl = tk.Label(self.regFrame, fg="white",bg=self.clr(150,150,150),text="Subject:",font=("Arial",15,"bold"))
        subLbl.grid(row=2,column=0,padx=20, pady=30)
        self.subIn = tk.Entry(self.regFrame, bd=1, font=("Arial",15,"bold"),width=20)
        self.subIn.grid(row=2,column=1, padx=10,pady=30)

        okBtn = tk.Button(self.regFrame, command=self.insertFun,text="OK", bd=2,fg="white", bg="gray",width=20,font=("Arial",20,"bold"))
        okBtn.grid(row=3, column=0, padx=30,pady=40, columnspan=2)
# The insertfun method inserts student data into 'reg' table and show success/error message based on the operation result.
    def insertFun(self):
        rn = int(self.rnIn.get())
        name = self.nameIn.get()
        subVal = self.subIn.get()
        totalBk=0
        if rn and name and subVal:
            try:
                con = pymysql.connect(host="localhost", user="root", passwd="W1@sendedroots", database="lib")
                cur = con.cursor()
                
                cur.execute("insert into reg(rollNo,sName,sub,total_book) values(%s,%s,%s,%s)",(rn,name,subVal,totalBk))
                con.commit()
                tk.messagebox.showinfo("Success","Student Registered in Library Management System")
                self.desRegFrame()
                cur.close()
                con.close()
            except Exception as e:
                tk.messagebox.showerror("Error",f"Error: {e}")
                self.desRegFrame()
        else:
            tk.messagebox.showerror("Erorr","Please Fill All Input Fields")

# The desRegFrame method destroys the registration frame after the operation is completed.
    def desRegFrame(self):
        self.regFrame.destroy()
# The showBk method retrieves book data from the 'lib' table and displays it in a Treeview widget.    
    def showBk(self):
        try:
            con = pymysql.connect(host="localhost", user="root", passwd="W1@sendedroots", database="lib")
            cur = con.cursor()
            cur.execute("select * from lib")
            data = cur.fetchall()

            self.tabFun()
            self.table.delete(*self.table.get_children())
            for i in data:
                self.table.insert('',tk.END,values=i)
            cur.close()
            con.close()
        except Exception as ex:
            tk.messagebox.showerror("Error",f"Error: {ex}")
# The resBook method displays a book reservation form with fields for Roll Number and Book ID.
    def resBook(self):
        self.resFrame = tk.Frame(self.root, bg=self.clr(150,200,80),bd=4,relief="ridge")
        self.resFrame.place(width=self.width/3,height=self.height-180, x=self.width/3+120,y=100)

        rnLbl = tk.Label(self.resFrame, fg="white",bg=self.clr(150,200,80),text="RollNo:",font=("Arial",15,"bold"))
        rnLbl.grid(row=0, column=0, padx=20,pady=50)
        self.rnIn = tk.Entry(self.resFrame, bd=1, font=("Arial",15,"bold"),width=20)
        self.rnIn.grid(row=0,column=1,padx=10, pady=50)

        bkLbl = tk.Label(self.resFrame, fg="white",bg=self.clr(150,200,80),text="Book_Id:",font=("Arial",15,"bold"))
        bkLbl.grid(row=1, column=0, padx=20,pady=50)
        self.bkIn = tk.Entry(self.resFrame, bd=1, font=("Arial",15,"bold"),width=20)
        self.bkIn.grid(row=1,column=1,padx=10, pady=50)

        okBtn = tk.Button(self.resFrame,command=self.resFun,text="OK", bd=2,fg="white", bg="gray",width=20,font=("Arial",20,"bold"))
        okBtn.grid(row=2, column=0, padx=30,pady=50, columnspan=2)

# The resFun method reserves a book for a student by updating the 'reg' and 'lib' tables in the database.
# Updates the student's total books and the book's quantity in the database.
# Displays success or error messages.
    def resFun(self):
        rn = int(self.rnIn.get())
        bk = int(self.bkIn.get())
        try:
            con = pymysql.connect(host="localhost", user="root", passwd="W1@sendedroots", database="lib")
            cur = con.cursor()
            query = f"select sName,total_book from reg where rollNo={rn}"
            cur.execute(query)
            std = cur.fetchone()
            query2 = f"select bName,quant from lib where bookId={bk}"
            cur.execute(query2)
            bkName= cur.fetchone()

            if std and bkName:
                if bkName[1] > 0:
                    totalBooks = std[1]+1
                    query3 = f"update reg set total_book={totalBooks} where rollNo={rn}"
                    cur.execute(query3)
                    con.commit()
                    remQuant= bkName[1] -1
                    query4 = f"update lib set quant={remQuant} where bookId={bk}"
                    cur.execute(query4)
                    con.commit()
                    tk.messagebox.showinfo("Success",f"Book. {bkName[0]} Reserved for student. {std[0]}")

                    cur.execute("select * from lib")
                    newVal = cur.fetchall()

                    self.tabFun()
                    self.table.delete(*self.table.get_children())
                    for j in newVal:
                        self.table.insert('',tk.END,values=j)

                    self.desResFrame()    

                else:
                    tk.messagebox.showerror("Error","This Book is out of Stock")
                    self.desResFrame()

            else:
                tk.messagebox.showerror("Error","Invalid RollNo or Book ID")
                self.desResFrame()

            

        except Exception as e:
            tk.messagebox.showerror("Error",f"Error: {e}") 

# The desResFrame method destroys the reservation frame after the operation is completed.
    def desResFrame(self):
        self.resFrame.destroy()  

# The retFun method displays a book return form with fields for Roll Number and Book ID.
    def retFun(self):
        self.resFrame = tk.Frame(self.root, bg=self.clr(150,200,80),bd=4,relief="ridge")
        self.resFrame.place(width=self.width/3,height=self.height-180, x=self.width/3+120,y=100)

        rnLbl = tk.Label(self.resFrame, fg="white",bg=self.clr(150,200,80),text="RollNo:",font=("Arial",15,"bold"))
        rnLbl.grid(row=0, column=0, padx=20,pady=50)
        self.rnIn = tk.Entry(self.resFrame, bd=1, font=("Arial",15,"bold"),width=20)
        self.rnIn.grid(row=0,column=1,padx=10, pady=50)

        bkLbl = tk.Label(self.resFrame, fg="white",bg=self.clr(150,200,80),text="Book_Id:",font=("Arial",15,"bold"))
        bkLbl.grid(row=1, column=0, padx=20,pady=50)
        self.bkIn = tk.Entry(self.resFrame, bd=1, font=("Arial",15,"bold"),width=20)
        self.bkIn.grid(row=1,column=1,padx=10, pady=50)

        okBtn = tk.Button(self.resFrame,command=self.retBk,text="OK", bd=2,fg="white", bg="gray",width=20,font=("Arial",20,"bold"))
        okBtn.grid(row=2, column=0, padx=30,pady=50, columnspan=2)

# The retBk method returns a book from a student by updating the 'reg' and 'lib' tables in the database.
    def retBk(self):
        rn = int(self.rnIn.get())
        bk = int(self.bkIn.get())

        con = pymysql.connect(host="localhost", user="root", passwd="W1@sendedroots", database="lib")
        cur = con.cursor()
        query = f"select bName,quant from lib where bookId={bk}"
        cur.execute(query)
        rowBk = cur.fetchone()

        query2 = f"select sName, total_book from reg where rollNo={rn}"
        cur.execute(query2)
        rowStd = cur.fetchone()

        if rowBk and rowStd:
            stdBook = rowStd[1] -1
            bkQuant = rowBk[1]+1

            query3 = f"update lib set quant={bkQuant} where bookId={bk}"
            cur.execute(query3)
            con.commit()

            query4 = f"update reg set total_book={stdBook} where rollNo={rn}"
            cur.execute(query4)
            con.commit()

            cur.execute("select * from lib")
            data = cur.fetchall()

            self.tabFun()
            self.table.delete(*self.table.get_children())
            for i in data:
                self.table.insert('',tk.END,values=i)
            tk.messagebox.showinfo("Suceess",f"Book.{rowBk[0]} returend from Student.{rowStd[0]}")
            self.desResFrame()
        else:
            tk.messagebox.showrror("Error","Invalid Book or Student ID")

# The clr method returns a hexadecimal color code based on RGB values.
    def clr(self,r,g,b):
        return f"#{r:02x}{g:02x}{b:02x}"

# Main execution block to run the application
root = tk.Tk()
obj = library(root)
root.mainloop()