SOURCE CODE
MySQL
CREATE DATABASE IF NOT EXISTS library;
USE library;
-- BOOK TABLE
CREATE TABLE IF NOT EXISTS book(
BookID INT AUTO_INCREMENT PRIMARY KEY,
BookName VARCHAR(100) NOT NULL,
Author VARCHAR(50) NOT NULL,
Edition VARCHAR(20),
Copies INT NOT NULL,
Rem_Copies INT NOT NULL
);
-- MEMBER TABLE
CREATE TABLE IF NOT EXISTS member(
MemberID INT AUTO_INCREMENT PRIMARY KEY,
MemName VARCHAR(50) NOT NULL,
Email VARCHAR(50),
Phone VARCHAR(15)
);
-- ISSUE TABLE
CREATE TABLE IF NOT EXISTS issue_table(
IssueID INT AUTO_INCREMENT PRIMARY KEY,
IssueDate DATE NOT NULL,
BookID INT NOT NULL,
MemberID INT NOT NULL,
Copies INT NOT NULL,
FOREIGN KEY (BookID) REFERENCES book(BookID)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (MemberID) REFERENCES member(MemberID)
ON UPDATE CASCADE ON DELETE RESTRICT
);
-- RETURNS TABLE
CREATE TABLE IF NOT EXISTS returns(
ReturnID INT AUTO_INCREMENT PRIMARY KEY,
ReturnDate DATE NOT NULL,
BookID INT NOT NULL,
MemberID INT NOT NULL,
Copies INT NOT NULL,
FOREIGN KEY (BookID) REFERENCES book(BookID)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (MemberID) REFERENCES member(MemberID)
ON UPDATE CASCADE ON DELETE RESTRICT
);
PYTHON
import [Link] as sqlt
import pandas as pd
from tabulate import tabulate
import [Link] as plt
import datetime
import sys
# ---------- DB CONNECTION ----------
def get_connection():
return [Link](
host="localhost",
user="root",
password="password",
database="library",
autocommit=False
con = get_connection()
cursor = [Link](buffered=True, dictionary=True)
# ---------- UTILITIES ----------
def safe_int(prompt, min_val=None, max_val=None):
while True:
s = input(prompt).strip()
try:
v = int(s)
if min_val is not None and v < min_val:
print(f"Value must be >= {min_val}. Try again.")
continue
if max_val is not None and v > max_val:
print(f"Value must be <= {max_val}. Try again.")
continue
return v
except ValueError:
print("Please enter a valid integer.")
def safe_date(prompt):
while True:
s = input(prompt).strip()
try:
# Basic check for YYYY-MM-DD
d = [Link](s, "%Y-%m-%d").date()
return [Link]()
except ValueError:
print("Please enter a valid date in YYYY-MM-DD format.")
def print_df_from_query(sql, params=None):
try:
df = pd.read_sql(sql, con, params=params)
if [Link]:
print("No records found.")
else:
print(tabulate(df, headers="keys", tablefmt="psql", showindex=False))
except Exception as e:
print("Error reading data:", e)
# ---------- BOOK FUNCTIONS ----------
def book_input():
try:
bookName = input("Enter Book Name: ").strip()
author = input("Enter Author Name: ").strip()
edition = input("Enter Edition: ").strip()
copies = safe_int("Enter No. of Copies: ", min_val=0)
qry = ("INSERT INTO book (BookName, Author, Edition, Copies, Rem_Copies) "
"VALUES (%s, %s, %s, %s, %s)")
[Link](qry, (bookName, author, edition, copies, copies))
[Link]()
print("Book Added Successfully. BookID:", [Link])
except Exception as e:
[Link]()
print("Error adding book:", e)
def book_edit():
try:
x = safe_int("Enter Book ID to Edit: ", min_val=1)
[Link]("SELECT * FROM book WHERE BookID=%s", (x,))
r = [Link]()
if not r:
print("Book ID Not Found")
return
print("Current record:")
print(tabulate([r], headers="keys", tablefmt="psql", showindex=False))
new_edition = input("Enter New Edition (leave blank to keep current): ").strip()
new_copies = input("Enter New Total Copies (leave blank to keep current): ").strip()
if new_edition == "" and new_copies == "":
print("No changes made.")
return
if new_copies != "":
try:
new_copies_int = int(new_copies)
if new_copies_int < 0:
print("Copies cannot be negative.")
return
except ValueError:
print("Copies must be an integer.")
return
# Adjust rem_copies proportionally: rem_new = rem_old + (new_total - old_total)
diff = new_copies_int - r['Copies']
new_rem = r['Rem_Copies'] + diff
if new_rem < 0:
print("Cannot reduce total copies below number currently issued.")
return
[Link]("UPDATE book SET Edition=%s, Copies=%s, Rem_Copies=%s WHERE
BookID=%s",
(new_edition if new_edition != "" else r['Edition'],
new_copies_int,
new_rem,
x))
else:
[Link]("UPDATE book SET Edition=%s WHERE BookID=%s",
(new_edition if new_edition != "" else r['Edition'], x))
[Link]()
print("Book Edited Successfully")
except Exception as e:
[Link]()
print("Error editing book:", e)
def book_delete():
try:
x = safe_int("Enter Book ID to Delete: ", min_val=1)
[Link]("SELECT * FROM book WHERE BookID=%s", (x,))
r = [Link]()
if not r:
print("Book ID Not Found")
return
# Prevent delete if any issue/return records exist due to FK RESTRICT
[Link]("SELECT COUNT(*) AS cnt FROM issue_table WHERE BookID=%s",
(x,))
if [Link]()['cnt'] > 0:
print("Cannot delete book: It has issue records. Remove related transactions
first.")
return
[Link]("DELETE FROM book WHERE BookID=%s", (x,))
[Link]()
print("Book Deleted Successfully")
except Exception as e:
[Link]()
print("Error deleting book:", e)
def book_search():
try:
x = safe_int("Enter Book ID: ", min_val=1)
print_df_from_query("SELECT * FROM book WHERE BookID=%s", params=(x,))
except Exception as e:
print("Error:", e)
# ---------- MEMBER FUNCTIONS ----------
def member_input():
try:
MemName = input("Enter Name: ").strip()
Email = input("Enter Email: ").strip()
Phone = input("Enter Phone No.: ").strip()
qry = "INSERT INTO member (MemName, Email, Phone) VALUES (%s, %s, %s)"
[Link](qry, (MemName, Email, Phone))
[Link]()
print("Member Added Successfully. MemberID:", [Link])
except Exception as e:
[Link]()
print("Error adding member:", e)
def member_edit():
try:
x = safe_int("Enter Member ID: ", min_val=1)
[Link]("SELECT * FROM member WHERE MemberID=%s", (x,))
r = [Link]()
if not r:
print("Member ID Not Found")
return
print("Current record:")
print(tabulate([r], headers="keys", tablefmt="psql", showindex=False))
new_email = input("Enter New Email (leave blank to keep current): ").strip()
new_phone = input("Enter New Phone (leave blank to keep current): ").strip()
if new_email == "" and new_phone == "":
print("No changes made.")
return
[Link]("UPDATE member SET Email=%s, Phone=%s WHERE MemberID=%s",
(new_email if new_email != "" else r['Email'],
new_phone if new_phone != "" else r['Phone'],
x))
[Link]()
print("Member Edited Successfully")
except Exception as e:
[Link]()
print("Error editing member:", e)
def member_delete():
try:
x = safe_int("Enter Member ID to Delete: ", min_val=1)
[Link]("SELECT * FROM member WHERE MemberID=%s", (x,))
r = [Link]()
if not r:
print("Member ID Not Found")
return
# Prevent delete if member has transactions
[Link]("SELECT COUNT(*) AS cnt FROM issue_table WHERE MemberID=%s",
(x,))
if [Link]()['cnt'] > 0:
print("Cannot delete member: member has issue records. Remove related
transactions first.")
return
[Link]("DELETE FROM member WHERE MemberID=%s", (x,))
[Link]()
print("Member Deleted Successfully")
except Exception as e:
[Link]()
print("Error deleting member:", e)
def member_search():
try:
x = safe_int("Enter Member ID: ", min_val=1)
print_df_from_query("SELECT * FROM member WHERE MemberID=%s", params=(x,))
except Exception as e:
print("Error:", e)
# ---------- ISSUE / RETURN ----------
def book_issue():
try:
member = safe_int("Enter Member ID: ", min_val=1)
[Link]("SELECT 1 FROM member WHERE MemberID=%s", (member,))
if not [Link]():
print("Invalid Member ID")
return
book = safe_int("Enter Book ID: ", min_val=1)
[Link]("SELECT BookID, Rem_Copies FROM book WHERE BookID=%s", (book,))
data = [Link]()
if not data:
print("Invalid Book ID")
return
if data['Rem_Copies'] <= 0:
print("Book Not Available")
return
copies = safe_int("Enter Number of Copies to Issue: ", min_val=1)
if copies > data['Rem_Copies']:
print("Not Enough Copies Available")
return
issuedate = safe_date("Enter Issue Date (YYYY-MM-DD): ")
# Insert into issue_table; DB will auto-generate IssueID
[Link](
"INSERT INTO issue_table (IssueDate, BookID, MemberID, Copies) VALUES (%s,
%s, %s, %s)",
(issuedate, book, member, copies)
# Update remaining copies
new_rem = data['Rem_Copies'] - copies
[Link]("UPDATE book SET Rem_Copies=%s WHERE BookID=%s", (new_rem,
book))
[Link]()
print("Book Issued Successfully. IssueID:", [Link])
except Exception as e:
[Link]()
print("Error issuing book:", e)
def book_return():
try:
member = safe_int("Enter Member ID: ", min_val=1)
[Link]("SELECT 1 FROM member WHERE MemberID=%s", (member,))
if not [Link]():
print("Invalid Member ID")
return
book = safe_int("Enter Book ID: ", min_val=1)
[Link]("SELECT BookID, Rem_Copies, Copies FROM book WHERE BookID=%s",
(book,))
r = [Link]()
if not r:
print("Invalid Book ID")
return
# Calculate net issued to this member for this book
[Link]("SELECT COALESCE(SUM(Copies),0) AS issued_sum FROM issue_table
WHERE MemberID=%s AND BookID=%s", (member, book))
issued_sum = [Link]()['issued_sum']
[Link]("SELECT COALESCE(SUM(Copies),0) AS return_sum FROM returns
WHERE MemberID=%s AND BookID=%s", (member, book))
return_sum = [Link]()['return_sum']
net_issued = issued_sum - return_sum
if net_issued <= 0:
print("This member has no outstanding copies of this book to return.")
return
print(f"Member has {net_issued} copy(ies) outstanding for BookID {book}.")
copies = safe_int("Enter Number of Copies to Return: ", min_val=1)
if copies > net_issued:
print("Return count exceeds outstanding issued copies.")
return
returndate = safe_date("Enter Return Date (YYYY-MM-DD): ")
[Link](
"INSERT INTO returns (ReturnDate, BookID, MemberID, Copies) VALUES (%s,
%s, %s, %s)",
(returndate, book, member, copies)
# Update rem_copies
new_rem = r['Rem_Copies'] + copies
[Link]("UPDATE book SET Rem_Copies=%s WHERE BookID=%s", (new_rem,
book))
[Link]()
print("Book Returned Successfully. ReturnID:", [Link])
except Exception as e:
[Link]()
print("Error returning book:", e)
# ---------- REPORT SECTION ----------
def book_output():
print_df_from_query("SELECT * FROM book")
def member_output():
print_df_from_query("SELECT * FROM member")
def issue_output():
print_df_from_query("SELECT * FROM issue_table")
def return_output():
print_df_from_query("SELECT * FROM returns")
def col_chart():
try:
q = "SELECT BookID, SUM(Copies) AS totalcopies FROM issue_table GROUP BY
BookID;"
df = pd.read_sql(q, con)
if [Link]:
print("No issue records to plot.")
return
[Link]()
[Link](df['BookID'], df['totalcopies'])
[Link]("Most Issued Books (by BookID)")
[Link]("Book ID")
[Link]("Copies Issued")
[Link]()
except Exception as e:
print("Error creating chart:", e)
# ---------- MAIN MENU ----------
def main_menu():
try:
while True:
print("="*80)
print("\t\tLIBRARY MANAGEMENT SYSTEM")
print("="*80)
print("(1) Book Details\n(2) Member Details\n(3) Transactions\n(4) Reports\n(5)
Exit")
try:
choice = safe_int("Enter Choice: ", min_val=1, max_val=5)
except KeyboardInterrupt:
print("\nExiting...")
break
if choice == 1:
while True:
print("(1) Add Book\n(2) Edit Book\n(3) Delete Book\n(4) Search Book\n(5)
Back")
ch = safe_int("Enter Choice: ", min_val=1, max_val=5)
if ch == 1: book_input()
elif ch == 2: book_edit()
elif ch == 3: book_delete()
elif ch == 4: book_search()
elif ch == 5: break
elif choice == 2:
while True:
print("(1) Add Member\n(2) Edit Member\n(3) Delete Member\n(4) Search
Member\n(5) Back")
ch = safe_int("Enter Choice: ", min_val=1, max_val=5)
if ch == 1: member_input()
elif ch == 2: member_edit()
elif ch == 3: member_delete()
elif ch == 4: member_search()
elif ch == 5: break
elif choice == 3:
while True:
print("(1) Issue Book\n(2) Return Book\n(3) Back")
ch = safe_int("Enter Choice: ", min_val=1, max_val=3)
if ch == 1: book_issue()
elif ch == 2: book_return()
elif ch == 3: break
elif choice == 4:
while True:
print("(1) Book Report\n(2) Member Report\n(3) Issue Report\n(4) Return
Report\n(5) Chart\n(6) Back")
ch = safe_int("Enter Choice: ", min_val=1, max_val=6)
if ch == 1: book_output()
elif ch == 2: member_output()
elif ch == 3: issue_output()
elif ch == 4: return_output()
elif ch == 5: col_chart()
elif ch == 6: break
elif choice == 5:
print("Goodbye.")
break
finally:
try:
[Link]()
[Link]()
except:
pass
if __name__ == "__main__":
main_menu()