Library management System Code
in Python
import mysql.connector
from datetime import date
# ---------------- CONNECT TO MYSQL ----------------
con = mysql.connector.connect(
host="localhost",
user="root", # change this
password="2009" # change this
)
cur = con.cursor()
# ---------------- CREATE DATABASE ----------------
cur.execute("CREATE DATABASE IF NOT EXISTS library_db")
cur.execute("USE library_db")
# ---------------- CREATE TABLES ----------------
cur.execute("""
CREATE TABLE IF NOT EXISTS Book(
Book_ID INT AUTO_INCREMENT PRIMARY KEY,
Book_Name VARCHAR(100),
Book_Author VARCHAR(100),
Book_Publisher VARCHAR(100),
Book_Quantity INT,
Book_Price DECIMAL(8,2)
)
""")
cur.execute("""
CREATE TABLE IF NOT EXISTS Student(
Adm_ID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100),
Class VARCHAR(20),
Section VARCHAR(10)
)
""")
# ✅ Here both Book_ID and Adm_ID are foreign keys
cur.execute("""
CREATE TABLE IF NOT EXISTS Issue(
Booking_ID INT AUTO_INCREMENT PRIMARY KEY,
Book_ID INT,
Adm_ID INT,
Issue_Date DATE,
Return_Date DATE,
Fine DECIMAL(8,2),
Paid ENUM('Yes','No') DEFAULT 'No',
FOREIGN KEY (Book_ID) REFERENCES Book(Book_ID) ON
DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (Adm_ID) REFERENCES Student(Adm_ID) ON
DELETE CASCADE ON UPDATE CASCADE
)
""")
con.commit()
print("✅ Database and tables created successfully!")
# ---------------- MENU LOOP ----------------
while True:
print("\n========== LIBRARY MANAGEMENT SYSTEM
==========")
print("1. Add Book")
print("2. Add Student")
print("3. Issue Book")
print("4. Return Book")
print("5. Pay Fine")
print("6. Exit")
ch = input("Enter your choice: ")
# ---------------- ADD BOOK ----------------
if ch == '1':
name = input("Enter book name: ")
author = input("Enter author: ")
publisher = input("Enter publisher: ")
qty = int(input("Enter quantity: "))
price = float(input("Enter price: "))
cur.execute("INSERT INTO Book(Book_Name, Book_Author,
Book_Publisher, Book_Quantity, Book_Price) VALUES (%s,%s,%s,
%s,%s)",
(name, author, publisher, qty, price))
con.commit()
print("✅ Book added successfully!")
# ---------------- ADD STUDENT ----------------
elif ch == '2':
sname = input("Enter student name: ")
sclass = input("Enter class: ")
ssection = input("Enter section: ")
cur.execute("INSERT INTO Student(Name, Class, Section)
VALUES (%s,%s,%s)",
(sname, sclass, ssection))
con.commit()
print("✅ Student added successfully!")
# ---------------- ISSUE BOOK ----------------
elif ch == '3':
book_id = int(input("Enter Book ID: "))
adm_id = int(input("Enter Student Admission ID: "))
# Check if book exists and available
cur.execute("SELECT Book_Quantity FROM Book WHERE
Book_ID=%s", (book_id,))
data = cur.fetchone()
if not data:
print("❌ Invalid Book ID.")
elif data[0] <= 0:
print("❌ Book not available.")
else:
cur.execute("INSERT INTO Issue(Book_ID, Adm_ID,
Issue_Date, Fine, Paid) VALUES (%s,%s,%s,%s,%s)",
(book_id, adm_id, date.today(), 0, 'No'))
cur.execute("UPDATE Book SET Book_Quantity =
Book_Quantity - 1 WHERE Book_ID=%s", (book_id,))
con.commit()
print("✅ Book issued successfully!")
# ---------------- RETURN BOOK ----------------
elif ch == '4':
booking_id = int(input("Enter Booking ID: "))
return_date = date.today()
cur.execute("SELECT Issue_Date, Book_ID FROM Issue
WHERE Booking_ID=%s", (booking_id,))
record = cur.fetchone()
if not record:
print("❌ Invalid Booking ID.")
else:
issue_date, book_id = record
days_diff = (return_date - issue_date).days
fine = 0
if days_diff > 15:
fine = (days_diff - 15) * 50 # ₹50/day fine
cur.execute("UPDATE Issue SET Return_Date=%s, Fine=
%s WHERE Booking_ID=%s",
(return_date, fine, booking_id))
cur.execute("UPDATE Book SET Book_Quantity =
Book_Quantity + 1 WHERE Book_ID=%s", (book_id,))
con.commit()
print(f"✅ Book returned successfully! Fine: ₹{fine}")
# ---------------- PAY FINE ----------------
elif ch == '5':
booking_id = int(input("Enter Booking ID to mark fine as
paid: "))
cur.execute("UPDATE Issue SET Paid='Yes' WHERE
Booking_ID=%s", (booking_id,))
con.commit()
print("✅ Fine marked as paid.")
# ---------------- EXIT ----------------
elif ch == '6':
print("👋 Exiting... Goodbye!")
break
else:
print("❌ Invalid choice, try again.")
