import mysql.
connector
import qrcode
from datetime import datetime
db = [Link](
host="localhost",
user="root",
password= "bluewhale",
database="bookstore")
cursor = [Link]()
#ADMIN FUNCTIONS
#create table(0)
def create_books_table():
[Link]("""
CREATE TABLE IF NOT EXISTS books
(book_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(100),
price DECIMAL(10,2),
stock INT)
""")
[Link]()
print("Books table created successfully!")
#add book(1)
def add_book():
title = input("Title: ")
author = input("Author: ")
price = float(input("Price: "))
stock = int(input("Stock: "))
[Link]( "INSERT INTO books (title, author, price, stock) VALUES (%s,%s,%s,%s)",
(title, author, price, stock))
[Link]()
print("Book added successfully!")
#view books(2)
def view_books():
[Link]("SELECT * FROM books")
for book in [Link]():
print(book)
#update books(3)
def update_book():
book_id = int(input("Enter Book ID to update: "))
print("1. Update Price")
print("2. Update Stock")
choice = input("Enter choice: ")
if choice == "1":
new_price = float(input("Enter new price: "))
[Link](
"UPDATE books SET price=%s WHERE book_id=%s",
(new_price, book_id)
)
[Link]()
print("Price updated successfully!")
elif choice == "2":
new_stock = int(input("Enter new stock: "))
[Link]("UPDATE books SET stock=%s WHERE book_id=%s",
(new_stock, book_id))
[Link]()
print("Stock updated successfully!")
else:
print("Invalid choice")
#delete books(4)
def delete_book():
book_id = int(input("Enter Book ID to delete: "))
# First delete records from child table
[Link]("DELETE FROM purchases WHERE book_id=%s", (book_id,))
# Then delete from parent table
[Link]("DELETE FROM books WHERE book_id=%s", (book_id,))
[Link]()
if [Link] == 0:
print("No book found with that ID.")
else:
print("Book deleted successfully (along with related purchases).")
#search books(5)
def search_book():
keyword = input("Enter book title or author: ")
[Link](
"SELECT * FROM books WHERE title LIKE %s OR author LIKE %s",
('%' + keyword + '%', '%' + keyword + '%')
)
records = [Link]()
if records:
for book in records:
print(book)
else:
print("No matching book found.")
#total sales(6)
def total_sales():
[Link]("SELECT SUM(total_price) FROM purchases")
total = [Link]()[0]
if total:
print(f"Total Sales = ₹{total}")
else:
print("No sales yet.")
#book by stocks(7)
def books_by_stock():
[Link]("SELECT title, stock FROM books ORDER BY stock ")
for book in [Link]():
print(book)
#stock alert(8)
def stock_alert():
[Link]("SELECT title, stock FROM books WHERE stock < 5")
books = [Link]()
if books:
print("⚠ LOW STOCK ALERT:")
for b in books:
print(f"{b[0]} → Stock left: {b[1]}")
else:
print("All books are sufficiently stocked.")
#books sorted by price(9)
def books_by_price():
[Link]("SELECT title, price FROM books ORDER BY price")
for book in [Link]():
print(book)
#Admin menu
def admin_menu():
while True:
print("\n--- ADMIN MENU ---")
print("0. Create table")
print("1. Add Book")
print("2. View Books")
print("3. Update Book")
print("4. Delete Book")
print("5. Search Book")
print("6. Total Sales")
print("7. Books_by_Stock")
print("8. Stock_alert")
print("9. Books by Price")
print("10. Back")
ch = input("Enter choice: ")
if ch=="0":
create_books_table()
elif ch == "1":
add_book()
elif ch == "2":
view_books()
elif ch == "3":
update_book()
elif ch == "4":
delete_book()
elif ch == "5":
search_book()
elif ch == "6":
total_sales()
elif ch == "7":
books_by_stock()
elif ch == "8":
stock_alert()
elif ch == "9":
books_by_price()
elif ch == "10":
break
else:
print("Invalid choice.")
#BUYER FUNCTIONS
#create table purchases(0)
def create_purchases_table():
[Link]("""
CREATE TABLE IF NOT EXISTS purchases (
purchase_id INT AUTO_INCREMENT PRIMARY KEY,
buyer_name VARCHAR(100),
book_id INT,
quantity INT,
total_price DECIMAL(10,2),
purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (book_id) REFERENCES books(book_id)
)
""")
[Link]()
print("Purchases table created successfully!")
#view the available books(1)
def view_available_books():
[Link]("SELECT book_id, title, author, price, stock FROM books WHERE stock > 0")
records = [Link]()
if records:
print("\nAvailable Books:")
for r in records:
print(r)
else:
print("No books available.")
#search book by its title(2)
def search_book_by_title():
title = input("Enter book title: ")
[Link](
"SELECT * FROM books WHERE title LIKE %s",
('%' + title + '%',)
)
records = [Link]()
if records:
for r in records:
print(r)
else:
print("Book not found.")
#search book by author(3)
def search_book_by_author():
author = input("Enter author name: ")
[Link](
"SELECT * FROM books WHERE author LIKE %s",
('%' + author + '%',)
)
records = [Link]()
if records:
for r in records:
print(r)
else:
print("No books by this author.")
#buy multiple books with bill produced(4)
def buy_books():
name = input("Buyer Name: ")
cart = [] # to store purchased books
grand_total = 0
while True:
view_available_books() # show book_id to customer
book_id = int(input("Enter Book ID: "))
qty = int(input("Enter Quantity: "))
[Link](
"SELECT title, price, stock FROM books WHERE book_id=%s",
(book_id,)
)
book = [Link]()
if not book:
print("Invalid Book ID")
continue
title, price, stock = book
if qty > stock:
print("Insufficient stock")
continue
total = price * qty
grand_total += total
# save book details for bill
[Link]((title, price, qty, total))
# insert purchase
[Link](
"INSERT INTO purchases (buyer_name, book_id, quantity, total_price) VALUES
(%s,%s,%s,%s)",
(name, book_id, qty, total)
)
# update stock
[Link](
"UPDATE books SET stock = stock - %s WHERE book_id=%s",
(qty, book_id)
)
[Link]()
more = input("Buy another book? (y/n): ")
if [Link]() != 'y':
break
# 🔹 NESTED BILL FUNCTION
def generate_qr_bill(name, cart, grand_total):
bill_no = [Link]().strftime("%Y%m%d%H%M%S")
bill_text = f"""
BOOK STORE BILL
Bill No: {bill_no}
Customer: {name}
"""
for item in cart:
bill_text += f"{item[0]} | ₹{item[1]} x {item[2]} = ₹{item[3]}\n"
bill_text += f"""
------------------------
TOTAL: ₹{grand_total}
Thank you for shopping!
"""
qr = [Link](bill_text)
filename = f"bill_{bill_no}.png"
[Link](filename)
import os
[Link](filename)
📱 QR Bill generated: {filename}")
print(f"
generate_qr_bill(name, cart, grand_total)
#purchase history of the buyer(5)
def buyer_purchase_history():
name = input("Enter buyer name: ")
[Link]("""
SELECT [Link], [Link], p.total_price, p.purchase_date
FROM purchases p
JOIN books b ON p.book_id = b.book_id
WHERE p.buyer_name=%s
""", (name,))
records = [Link]()
if records:
print("\nYour Purchase History:")
for r in records:
print(r)
else:
print("No purchase history found.")
#total amount spent by the buyer(6)
def total_spent_by_buyer():
name = input("Enter buyer name: ")
[Link](
"SELECT SUM(total_price) FROM purchases WHERE buyer_name=%s",
(name,)
)
total = [Link]()[0]
if total:
print("Total Amount Spent = ₹", total)
else:
print("No purchases yet.")
#BUYER MENU
def buyer_menu():
while True:
print("\n--- BUYER MENU ---")
print("0. Create table purchases")
print("1. View Available Books")
print("2. Search Book by Title")
print("3. Search Book by Author")
print("5. Buy Multiple Books")
print("6. View Purchase History")
print("7. View Total Amount Spent")
print("8. Back")
choice = input("Enter choice: ")
if choice == "0":
create_purchases_table()
elif choice == "1":
view_available_books()
elif choice == "2":
search_book_by_title()
elif choice == "3":
search_book_by_author()
elif choice == "4":
buy_books()
elif choice == "5":
buyer_purchase_history()
elif choice == "6":
total_spent_by_buyer()
elif choice == "7":
break
else:
print("Invalid choice")
#main menu
def main():
while True:
print("\n=== BOOK STORE MANAGEMENT SYSTEM ===")
print("1. Admin")
print("2. Buyer")
print("3. Exit")
choice = input("Enter choice: ")
if choice == "1":
admin_menu() # function call
elif choice == "2":
buyer_menu() # function call
elif choice == "3":
break
main()