Datbase initiation:
import [Link]
from datetime import datetime, timedelta
DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = "arnabsqlopen#2008"
DB_NAME = "sanjulas_library"
def connect_db():
conn = [Link](
host=DB_HOST, user=DB_USER, password=DB_PASSWORD
cursor = [Link]()
[Link](f"CREATE DATABASE IF NOT EXISTS {DB_NAME}")
[Link]()
[Link]()
conn = [Link](
host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME
return conn
def setup_schema(conn):
cursor = [Link]()
[Link]("""
CREATE TABLE IF NOT EXISTS users (
Username VARCHAR(50) PRIMARY KEY,
Password VARCHAR(50),
Role VARCHAR(20)
)
""")
[Link]("""
CREATE TABLE IF NOT EXISTS books (
ISBN VARCHAR(20) PRIMARY KEY,
Title VARCHAR(200),
Author VARCHAR(100),
Genre VARCHAR(50),
Total_Copies INT,
Available_Copies INT
""")
[Link]("""
CREATE TABLE IF NOT EXISTS members (
Member_ID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100),
Contact_Info VARCHAR(100),
Membership_Status VARCHAR(20),
Username VARCHAR(50)
""")
[Link]("""
CREATE TABLE IF NOT EXISTS transactions (
Transaction_ID INT PRIMARY KEY AUTO_INCREMENT,
ISBN VARCHAR(20),
Member_ID INT,
Issue_Date DATE,
Due_Date DATE,
Return_Date DATE,
Fine_Amount DECIMAL(10, 2),
FOREIGN KEY (ISBN) REFERENCES books(ISBN),
FOREIGN KEY (Member_ID) REFERENCES members(Member_ID)
""")
[Link]()
[Link]()
def seed_data(conn):
cursor = [Link]()
# Insert Admins
[Link]("INSERT IGNORE INTO users VALUES ('admin1','admin123','Admin')")
[Link]("INSERT IGNORE INTO users VALUES ('admin2','admin456','Admin')")
# Insert Members
members = [
('Alice Johnson', 'alice@[Link]', 'Active', 'alicej', 'alicepass'),
('Bob Smith', 'bob@[Link]', 'Active', 'bobsmith', 'bobpass'),
('Cathy Brown', 'cathy@[Link]', 'Active', 'cathyb', 'cathypass')
for name, contact, status, username, password in members:
[Link]("INSERT IGNORE INTO users VALUES (%s,%s,'Member')", (username, password))
[Link]("""
INSERT IGNORE INTO members (Name, Contact_Info, Membership_Status, Username)
VALUES (%s, %s, %s, %s)
""", (name, contact, status, username))
# Insert Books
books = [
('9780141439518', 'Pride and Prejudice', 'Jane Austen', 'Romance / Classic Literature', 5, 5),
('9780316769488', 'The Catcher in the Rye', 'J.D. Salinger', 'Coming-of-Age Fiction', 5, 5),
('9780618640157', 'The Lord of the Rings', 'J.R.R. Tolkien', 'Epic Fantasy', 5, 5),
('9780747532699', 'Harry Potter and the Philosopher’s Stone', 'J.K. Rowling', 'Fantasy / Young
Adult', 5, 5),
('9780061122415', 'The Alchemist', 'Paulo Coelho', 'Philosophical Fiction', 5, 5),
('9780553380163', 'A Brief History of Time', 'Stephen Hawking', 'Popular Science / Cosmology',
5, 5),
('9780553296983', 'The Diary of a Young Girl', 'Anne Frank', 'Biography / Historical Nonfiction',
5, 5),
('9780062316097', 'Sapiens: A Brief History of Humankind', 'Yuval Noah Harari', 'Anthropology /
History', 5, 5),
('9789356291192', 'The Immortals of Meluha', 'Amish Tripathi', 'Mythological Fiction / Fantasy',
5, 5)
[Link]("""
INSERT IGNORE INTO books (ISBN, Title, Author, Genre, Total_Copies, Available_Copies)
VALUES (%s, %s, %s, %s, %s, %s)
""", books)
# Create Sample Transaction
[Link]("SELECT Member_ID FROM members WHERE Username='alicej'")
member_id = [Link]()[0]
[Link]("""
INSERT IGNORE INTO transactions (ISBN, Member_ID, Issue_Date, Due_Date, Fine_Amount)
VALUES ('9780141439518', %s, %s, %s, 0)
""", (member_id, [Link]().date(), [Link]().date() + timedelta(days=14)))
[Link]()
[Link]()
def main():
print("\nInitializing Sanjula’s Library Database...")
conn = connect_db()
setup_schema(conn)
seed_data(conn)
[Link]()
print("Database initialized with sample data.\n")
if __name__ == "__main__":
main()
main:
import [Link]
from datetime import datetime, timedelta
import sys
import time
# Database connection details
DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = "arnabsqlopen#2008"
DB_NAME = "sanjulas_library"
current_user = None
current_role = None
def show_logo():
print("\n" * 2)
print("=" * 85)
print("=" * 85)
print()
print(r"""
██╗ ██╗██╗ ██╗███████╗ ██╗ █████╗ ███╗
██╗████████╗███████╗██████╗
██║ ██║██║ ██║██╔════╝ ██║ ██╔══██╗████╗
██║╚══██╔══╝██╔════╝██╔══██╗
███████║███████║███████╗ ██║ ███████║██╔██╗ ██║ ██║
█████╗ ██████╔╝
██╔══██║██╔══██║╚════██║ ██║ ██╔══██║██║╚██╗██║ ██║
██╔══╝ ██╔══██╗
██║ ██║██║ ██║███████║ ███████╗██║ ██║██║ ╚████║ ██║
███████╗██║ ██║
╚═╝ ╚═╝╚═╝ ╚═╝╚══════╝ ╚══════╝╚═╝ ╚═╝╚═╝ ╚═══╝ ╚═╝
╚══════╝╚═╝ ╚═╝
The Lantern of Sanjula
""")
print()
print("=" * 85)
print("=" * 85)
print("\n")
def connect_db():
try:
conn = [Link](
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
database=DB_NAME
return conn
except [Link] as err:
print(f"\nERROR connecting to database: {err}")
print("Run 'db_init.py' first to initialize database.")
[Link](1)
def login(conn):
global current_user, current_role
print("\n--- LOGIN ---")
username = input("Enter Username: ")
password = input("Enter Password: ")
cursor = [Link]()
[Link]("SELECT Password, Role FROM users WHERE Username = %s", (username,))
result = [Link]()
[Link]()
if result and result[0] == password:
current_user = username
current_role = result[1]
print(f"\nLogin successful! Welcome {username} ({current_role})")
[Link](1)
return True
else:
print("\nInvalid username or password!")
return False
# ======================= ADMIN MENU =======================
def admin_menu(conn):
while True:
print("\n" + "=" * 50)
print("ADMIN MENU")
print("=" * 50)
print("1. Add Book")
print("2. Update Book")
print("3. Delete Book")
print("4. Register New Member")
print("5. Search Books")
print("6. View Issued Books")
print("7. View Overdue Fines")
print("8. Logout")
print("=" * 50)
choice = input("\nEnter your choice: ")
if choice == "1":
add_book(conn)
elif choice == "2":
update_book(conn)
elif choice == "3":
delete_book(conn)
elif choice == "4":
register_member(conn)
elif choice == "5":
search_books(conn)
elif choice == "6":
view_issued_books(conn)
elif choice == "7":
view_overdue_fines(conn)
elif choice == "8":
print("\nLogging out...")
break
else:
print("\nInvalid choice! Please try again.")
# ============== ADMIN FUNCTIONS ==============
def add_book(conn):
print("\n--- ADD BOOK ---")
isbn = input("Enter ISBN: ")
title = input("Enter Title: ")
author = input("Enter Author: ")
genre = input("Enter Genre: ")
total_copies = int(input("Enter Total Copies: "))
cursor = [Link]()
try:
[Link]("""
INSERT INTO books (ISBN, Title, Author, Genre, Total_Copies, Available_Copies)
VALUES (%s, %s, %s, %s, %s, %s)
""", (isbn, title, author, genre, total_copies, total_copies))
[Link]()
print("\nBook added successfully!")
except [Link] as err:
print(f"\nError: {err}")
[Link]()
def update_book(conn):
print("\n--- UPDATE BOOK ---")
isbn = input("Enter ISBN of book to update: ")
cursor = [Link]()
[Link]("SELECT * FROM books WHERE ISBN = %s", (isbn,))
book = [Link]()
if not book:
print("\nBook not found!")
[Link]()
return
print(f"\nCurrent Details: Title={book[1]}, Author={book[2]}, Genre={book[3]}, Total
Copies={book[4]}")
title = input("Enter new Title (press Enter to skip): ")
author = input("Enter new Author (press Enter to skip): ")
genre = input("Enter new Genre (press Enter to skip): ")
total_copies = input("Enter new Total Copies (press Enter to skip): ")
if title:
[Link]("UPDATE books SET Title = %s WHERE ISBN = %s", (title, isbn))
if author:
[Link]("UPDATE books SET Author = %s WHERE ISBN = %s", (author, isbn))
if genre:
[Link]("UPDATE books SET Genre = %s WHERE ISBN = %s", (genre, isbn))
if total_copies:
[Link]("UPDATE books SET Total_Copies = %s WHERE ISBN = %s", (int(total_copies),
isbn))
[Link]()
print("\nBook updated successfully!")
[Link]()
def delete_book(conn):
print("\n--- DELETE BOOK ---")
isbn = input("Enter ISBN of book to delete: ")
cursor = [Link]()
[Link]("SELECT Title FROM books WHERE ISBN = %s", (isbn,))
book = [Link]()
if not book:
print("\nBook not found!")
[Link]()
return
confirm = input(f"\nAre you sure you want to delete '{book[0]}'? (yes/no): ")
if [Link]() == "yes":
[Link]("DELETE FROM books WHERE ISBN = %s", (isbn,))
[Link]()
print("\nBook deleted successfully!")
else:
print("\nDeletion cancelled.")
[Link]()
def register_member(conn):
print("\n--- REGISTER NEW MEMBER ---")
name = input("Enter Name: ")
contact = input("Enter Contact Info (phone/email): ")
username = input("Create Username: ")
password = input("Create Password: ")
cursor = [Link]()
[Link]("SELECT * FROM users WHERE Username = %s", (username,))
if [Link]():
print("\nUsername already exists!")
[Link]()
return
[Link]("""
INSERT INTO users (Username, Password, Role)
VALUES (%s, %s, 'Member')
""", (username, password))
[Link]("""
INSERT INTO members (Name, Contact_Info, Membership_Status, Username)
VALUES (%s, %s, 'Active', %s)
""", (name, contact, username))
[Link]()
print(f"\nMember '{name}' registered successfully!")
[Link]()
# ============== COMMON FUNCTIONS ==============
def search_books(conn):
print("\n--- SEARCH BOOKS ---")
print("1. Search by Title")
print("2. Search by Author")
print("3. Search by ISBN")
choice = input("Enter choice: ")
cursor = [Link]()
if choice == "1":
title = input("Enter title (or part of it): ")
[Link]("SELECT * FROM books WHERE Title LIKE %s", (f"%{title}%",))
elif choice == "2":
author = input("Enter author name: ")
[Link]("SELECT * FROM books WHERE Author LIKE %s", (f"%{author}%",))
elif choice == "3":
isbn = input("Enter ISBN: ")
[Link]("SELECT * FROM books WHERE ISBN = %s", (isbn,))
else:
print("\nInvalid choice!")
[Link]()
return
results = [Link]()
if results:
print("\n" + "-" * 100)
print(f"{'ISBN':<15} {'Title':<30} {'Author':<25} {'Genre':<15} {'Available':<10}")
print("-" * 100)
for book in results:
print(f"{book[0]:<15} {book[1]:<30} {book[2]:<25} {book[3]:<15} {book[5]:<10}")
print("-" * 100)
else:
print("\nNo books found!")
[Link]()
def view_issued_books(conn):
print("\n--- CURRENTLY ISSUED BOOKS ---")
cursor = [Link]()
[Link]("""
SELECT t.Transaction_ID, [Link], [Link], t.Issue_Date, t.Due_Date
FROM transactions t
JOIN books b ON [Link] = [Link]
JOIN members m ON t.Member_ID = m.Member_ID
WHERE t.Return_Date IS NULL
""")
results = [Link]()
if results:
print("\n" + "-" * 90)
print(f"{'ID':<8} {'Book Title':<30} {'Member Name':<20} {'Issue Date':<12} {'Due Date':<12}")
print("-" * 90)
for row in results:
print(f"{row[0]:<8} {row[1]:<30} {row[2]:<20} {str(row[3]):<12} {str(row[4]):<12}")
print("-" * 90)
else:
print("\nNo books currently issued!")
[Link]()
def view_overdue_fines(conn):
print("\n--- OVERDUE FINES ---")
cursor = [Link]()
[Link]("""
SELECT [Link], m.Contact_Info, SUM(t.Fine_Amount) as Total_Fine
FROM transactions t
JOIN members m ON t.Member_ID = m.Member_ID
WHERE t.Fine_Amount > 0
GROUP BY m.Member_ID
""")
results = [Link]()
if results:
print("\n" + "-" * 70)
print(f"{'Member Name':<30} {'Contact':<25} {'Total Fine':<15}")
print("-" * 70)
for row in results:
print(f"{row[0]:<30} {row[1]:<25} ${row[2]:<14.2f}")
print("-" * 70)
else:
print("\nNo overdue fines!")
[Link]()
# ======================= MEMBER MENU =======================
def member_menu(conn):
while True:
print("\n" + "=" * 50)
print("MEMBER MENU")
print("=" * 50)
print("1. Search Books")
print("2. Issue Book")
print("3. Return Book")
print("4. View My Books")
print("5. Logout")
print("=" * 50)
choice = input("\nEnter your choice: ")
if choice == "1":
search_books(conn)
elif choice == "2":
issue_book(conn)
elif choice == "3":
return_book(conn)
elif choice == "4":
view_my_books(conn)
elif choice == "5":
print("\nLogging out...")
break
else:
print("\nInvalid choice! Please try again.")
def get_member_id(conn, username):
cursor = [Link]()
[Link]("SELECT Member_ID FROM members WHERE Username = %s", (username,))
result = [Link]()
[Link]()
return result[0] if result else None
def issue_book(conn):
print("\n--- ISSUE BOOK ---")
isbn = input("Enter ISBN of book to issue: ")
cursor = [Link]()
[Link]("SELECT Title, Available_Copies FROM books WHERE ISBN = %s", (isbn,))
book = [Link]()
if not book:
print("\nBook not found!")
[Link]()
return
if book[1] <= 0:
print(f"\nSorry, '{book[0]}' is not available right now!")
[Link]()
return
member_id = get_member_id(conn, current_user)
[Link]("""
SELECT COUNT(*) FROM transactions
WHERE Member_ID = %s AND Return_Date IS NULL
""", (member_id,))
issued_count = [Link]()[0]
if issued_count >= 3:
print("\nYou already have 3 books issued! Return one first.")
[Link]()
return
issue_date = [Link]().date()
due_date = issue_date + timedelta(days=14)
[Link]("""
INSERT INTO transactions (ISBN, Member_ID, Issue_Date, Due_Date, Fine_Amount)
VALUES (%s, %s, %s, %s, 0)
""", (isbn, member_id, issue_date, due_date))
[Link]("""
UPDATE books SET Available_Copies = Available_Copies - 1
WHERE ISBN = %s
""", (isbn,))
[Link]()
print(f"\nBook '{book[0]}' issued successfully until {due_date}")
[Link]()
def return_book(conn):
print("\n--- RETURN BOOK ---")
isbn = input("Enter ISBN of book to return: ")
cursor = [Link]()
member_id = get_member_id(conn, current_user)
[Link]("""
SELECT Transaction_ID, Due_Date FROM transactions
WHERE ISBN = %s AND Member_ID = %s AND Return_Date IS NULL
""", (isbn, member_id))
transaction = [Link]()
if not transaction:
print("\nNo active record found!")
[Link]()
return
return_date = [Link]().date()
due_date = transaction[1]
fine = max(0, (return_date - due_date).days)
[Link]("""
UPDATE transactions
SET Return_Date = %s, Fine_Amount = %s
WHERE Transaction_ID = %s
""", (return_date, fine, transaction[0]))
[Link]("UPDATE books SET Available_Copies = Available_Copies + 1 WHERE ISBN = %s",
(isbn,))
[Link]()
if fine == 0:
print("\nBook returned successfully! No fine.")
else:
print(f"\nBook returned late. Fine: ${fine:.2f}")
[Link]()
def view_my_books(conn):
print("\n--- MY ISSUED BOOKS ---")
cursor = [Link]()
member_id = get_member_id(conn, current_user)
[Link]("""
SELECT [Link], [Link], t.Issue_Date, t.Due_Date, [Link]
FROM transactions t
JOIN books b ON [Link] = [Link]
WHERE t.Member_ID = %s AND t.Return_Date IS NULL
""", (member_id,))
results = [Link]()
if results:
print("\n" + "-" * 90)
print(f"{'Title':<30} {'Author':<25} {'Issue Date':<12} {'Due Date':<12} {'ISBN':<15}")
print("-" * 90)
for row in results:
print(f"{row[0]:<30} {row[1]:<25} {str(row[2]):<12} {str(row[3]):<12} {row[4]:<15}")
print("-" * 90)
else:
print("\nYou have no books issued.")
[Link]()
# ======================= MAIN =======================
def main():
show_logo()
print("Connecting to Sanjula’s Library Database...")
conn = connect_db()
print("Connected successfully.\n")
while True:
if login(conn):
if current_role == "Admin":
admin_menu(conn)
elif current_role == "Member":
member_menu(conn)
again = input("\nDo you want to login again? (yes/no): ")
if [Link]() != "yes":
print("\nGoodbye from The Lantern of Sanjula!\n")
break
[Link]()
if __name__ == "__main__":
main()