================================================================================
  Question 25: Library Management System using SQLite (Full CRUD)
================================================================================

FULL QUESTION:
Develop a Library Management System using Python and SQLite to manage books.

Table Schema: Books
- Book_ID  : Integer, Primary Key, Auto Increment
- Title    : Text, NOT NULL
- Author   : Text, NOT NULL
- Category : Text, NOT NULL
- Copies   : Integer, NOT NULL

Operations to implement:
1. Establish database connection.
2. Create Books table with constraints.
3. Insert a single book record using parameterized queries.
4. Insert multiple book records using executemany().
5. Retrieve and display all book records.
6. Search for a book by Book_ID.
7. Update available copies of a book by Book_ID.
8. Delete a book record by Book_ID.
9. Filter and display books by category.
10. Provide an interactive menu-driven interface to perform all CRUD operations.

SHORT QUESTION:
Complete Library Management System implementing SQLite CRUD operations and an interactive command-line interface.

================================================================================
README & SETUP INSTRUCTIONS:
================================================================================
# Question 25: Library Management System using SQLite (Full CRUD)

- Prerequisites: Python 3.x (Standard library sqlite3 module).
- Setup: None (database 'library.db' is created automatically).
- How to Run: python question_25.py
- Expected Behavior: Seeds initial books into 'library.db' and provides an interactive CRUD menu.

================================================================================
SOURCE CODE FILES:
================================================================================

1. question_25.py
--------------------------------------------------------------------------------
import sqlite3 as sql

conn = sql.connect("BookDB.db")
cursor = conn.cursor()
print("Connected to BookDB.db successfully.")

cursor.execute("""
CREATE TABLE IF NOT EXISTS books (
    book_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    category TEXT NOT NULL,
    copies INTEGER NOT NULL
)
""")
conn.commit()
print("Table created successfully.")

cursor.execute(
    "INSERT INTO books (title, author, category, copies) VALUES (?, ?, ?, ?)",
    ("Book 1", "Author 1", "Comedy", 12)
)
conn.commit()
print("Single employee inserted successfully.")

new_books = [
    ("Book 2", "Author 2", "Horror", 10),
    ("Book 3", "Author 2", "Romance", 20),
    ("Book 4", "Author 1", "Sci-Fi", 15)
]

cursor.executemany(
    "INSERT INTO books (title, author, category, copies) VALUES (?, ?, ?, ?)",
    new_books
)
conn.commit()
print(f"Inserted {cursor.rowcount} records successfully.")

cursor.execute("SELECT * FROM books")
all_rows = cursor.fetchall()

print("\n--- Books in Library ---")
for row in all_rows:
    print(f"ID: {row[0]} | Title: {row[1]} | Author: {row[2]} | Category: {row[3]} | Copies: {row[4]}")

search = int(input("Enter the Book ID to search: "))

cursor.execute("SELECT * FROM books WHERE book_id = ?", (search,))
book = cursor.fetchone()

if book:
    print("\nBook found:")
    print(f"ID: {book[0]} | Title: {book[1]} | Author: {book[2]} | Category: {book[3]} | Copies: {book[4]}")
else:
    print("Book is not found.")

search = int(input("Enter Book ID to update: "))

cursor.execute("SELECT title, copies FROM books WHERE book_id = ?", (search,))
book = cursor.fetchone()

if book:
    print(f"Current details: Title: {book[0]} | Copies: {book[1]}")
    new_copies = int(input("Enter new copies count: "))

    cursor.execute("UPDATE books SET copies = ? WHERE book_id = ?", (new_copies, search))
    conn.commit()

    print("Book updated successfully!")
    print(f"Updated details: Title: {book[0]} | Copies: {new_copies}")
else:
    print("Book ID not found.")

search = int(input("Enter Book ID to delete: "))

cursor.execute("SELECT title FROM books WHERE book_id = ?", (search,))
book = cursor.fetchone()

if book:
    cursor.execute("DELETE FROM books WHERE book_id = ?", (search,))
    conn.commit()
    print(f"Successfully deleted: {book[0]}")

    print("\n--- Remaining Books ---")
    cursor.execute("SELECT * FROM books")
    for row in cursor.fetchall():
        print(f"ID: {row[0]} | Title: {row[1]} | Author: {row[2]} | Category: {row[3]} | Copies: {row[4]}")
else:
    print("Book ID not found.")

search = input("Enter the category to display: ").strip()

cursor.execute("SELECT * FROM books WHERE lower(category) = ?", (search.lower(),))
category_books = cursor.fetchall()

if category_books:
    print(f"\n--- Books in '{search.title()}' Category ---")
    for row in category_books:
        print(f"ID: {row[0]} | Title: {row[1]} | Author: {row[2]} | Category: {row[3]} | Copies: {row[4]}")
else:
    print("Category not found.")

import sqlite3

conn = sqlite3.connect("library.db")
cursor = conn.cursor()

cursor.execute("""
  CREATE TABLE IF NOT EXISTS books (
    book_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    category TEXT NOT NULL,
    copies INTEGER NOT NULL
  )
""")
conn.commit()

def insert():
    try:
        user_input = input("Enter (Title, Author, Category, Copies) separated using comma(,): ")
        title, author, category, copies_str = map(str.strip, user_input.split(","))
        copies = int(copies_str)

        cursor.execute("INSERT INTO books (title, author, category, copies) VALUES (?, ?, ?, ?)",
                       (title, author, category, copies))
        conn.commit()
        print("New Book Inserted.")

    except ValueError:
        print("Error: Invalid input format. Ensure you have 3 commas and a valid number for copies.")
    except sqlite3.Error as e:
        print(f"SQL Error occurred: {e}")

def display():
    try:
        cursor.execute("SELECT * FROM books")
        all_rows = cursor.fetchall()

        if not all_rows:
            print("\nNo books found in the library.")
            return

        print("\n--- Books in Library ---")
        for row in all_rows:
            print(f"ID: {row[0]} | Title: {row[1]} | Author: {row[2]} | Category: {row[3]} | Copies: {row[4]}")
    except sqlite3.Error as e:
        print(f"SQL Error occurred: {e}")

def update():
    try:
        search = int(input("Enter Book ID to update: "))

        cursor.execute("SELECT title, copies FROM books WHERE book_id = ?", (search,))
        row = cursor.fetchone()

        if row:
            print(f"Current details: Title: {row[0]} | Copies: {row[1]}")
            new_copies = int(input("Enter new copies count: "))

            cursor.execute("UPDATE books SET copies = ? WHERE book_id = ?", (new_copies, search))
            conn.commit()

            print("Book updated successfully!")
            print(f"Updated details: Title: {row[0]} | Copies: {new_copies}")
        else:
            print("Book ID not found.")
    except ValueError:
        print("Error: Please enter a valid numerical ID or copy count.")
    except sqlite3.Error as e:
        print(f"SQL Error occurred: {e}")

def delete():
    try:
        search = int(input("Enter Book ID to delete: "))

        cursor.execute("SELECT title FROM books WHERE book_id = ?", (search,))
        row = cursor.fetchone()

        if row:
            cursor.execute("DELETE FROM books WHERE book_id = ?", (search,))
            conn.commit()
            print(f"Successfully deleted: {row[0]}")
            display()
        else:
            print("Book ID not found.")
    except ValueError:
        print("Error: Please enter a valid numerical ID.")
    except sqlite3.Error as e:
        print(f"SQL Error occurred: {e}")

menu = "\n1. Insert\n2. Display\n3. Update\n4. Delete\n5. Exit"
choice = -1

while choice != 5:
    print(menu)
    try:
        choice = int(input("Enter your choice: "))

        if choice == 1:
            insert()
        elif choice == 2:
            display()
        elif choice == 3:
            update()
        elif choice == 4:
            delete()
        elif choice == 5:
            print("Exiting program")
            conn.close()
        else:
            print("Invalid choice! Please choose a number from 1 to 5.")
    except ValueError:
        print("Please enter a valid number for the menu option.")
--------------------------------------------------------------------------------
