Create cursor

cursor = conn.cursor()

User input book information

class books:
def add(self):
name = input("Enter the name of the book : ")
auth = input("Enter the name of the author : ")
price = int(input("Enter the Price : "))
qty = int(input("Enter the Qty Recived : "))
query = f"INSERT INTO books(name, auth, price, qty) VALUES('{name}', '{auth}', {price}, {qty})"
cursor.execute(query)
conn.commit()
print("Book Record Inserted Successfully")

#User input to update the price of the book
def update_price(self):
id = int(input("Enter the id of the book for update in price : "))
query = f"SELECT name, price FROM books WHERE id = {id}"
cursor.execute(query)
result = cursor.fetchone()
if result:
name, price = result
print(f"The Name of the book is : {name}")
print(f"The current price of the book is : {price}")
choice = input("Do you Want to Update the Price [y/n] : ")
if choice.lower() == 'y':
new_price = int(input("Enter the new price : "))
query = f"UPDATE books SET price = {new_price} WHERE id = {id}"
cursor.execute(query)
conn.commit()
print("Book Price Updated Successfully")
else:
print("No changes Made!!")
else:
print("No Book found!!!")

#Checking how many books are left in the inventory
def search(self):
id = int(input("Enter book id for details : "))
query = f"SELECT * FROM books WHERE id = {id}"
cursor.execute(query)
result = cursor.fetchone()
if result:
id, name, auth, price, qty = result
print(f"The Details of Book Id {id}")
print(f"The Name of the book is : {name}")
print(f"THE Author of {name} is {auth}")
print(f"The Price of the book is : {price}")
print(f"The inventory count is {qty}")
else:
print("No record Found")

#Check if orders have been updated
def update(self):
query = "SELECT book_id, qty FROM purchases WHERE recieves = 'T' AND inv IS NULL"
cursor.execute(query)
results = cursor.fetchall()
if results:
for result in results:
book_id, qty = result
query = f"UPDATE books SET qty = {qty} WHERE id = {book_id}"
cursor.execute(query)
conn.commit()
print("The orders recieved have been updated.")
else:
print("No orders recieved.")

#Choosing all of the book information
def display(self):
query = "SELECT * FROM books"
cursor.execute(query)
results = cursor.fetchall()
if results:
for result in results:
id, name, auth, price, qty = result
print(f"Name for book {id}: {name}")
print(f"Name of Author: {auth}")
print(f"Price: {price}")
print(f"Quantity: {qty}")
print()
else:
print("No books found.")

#Input supplier information
class suppliers:
def add_sup(self):
name = input("Enter the Supplier Name : ")
phn = int(input("Enter Phone no. : "))
addr_line1 = input("Enter the address (line 1) : ")
addr_line2 = input("Enter the address (line 2) : ")
addr_city = input("Enter the city : ")
addr_state = input("Enter the state : ")
query = f"INSERT INTO suppliers(name, phone_no, addr1, addr2, addr_city, addr_stat) VALUES('{name}', {phn}, '{addr_line1}', '{addr_line2}', '{addr_city}', '{addr_state}')"
cursor.execute(query)
conn.commit()
print("Supplier Record Inserted Successfully")

#Remove supplier
def remove_supplier(self):
id = int(input("Enter the supplier id to remove the Supplier : "))
query = f"DELETE FROM suppliers WHERE id = {id}"
cursor.execute(query)
conn.commit()
print("Supplier Removed.")

#Look up supplier ID
def search_id(self):
id = int(input("Enter the supplier id to find the Supplier details : "))
query = f"SELECT * FROM suppliers WHERE id = {id}"
cursor.execute(query)
result = cursor.fetchone()
if result:
id, name, phn, addr_line1, addr_line2, addr_city, addr_state = result
print(f"Details of Supplier Id: {id}")
print(f"Name: {name}")
print(f"Phone no.: {phn}")
print(f"Address Line 1: {addr_line1}")
print(f"Address Line 2: {addr_line2}")
print(f"City: {addr_city}")
print(f"State: {addr_state}")
else:
print("No Supplier Found!!")

#Update the order information
class purchases:
def new_ord(self):
book_id = int(input("Enter the book Id : "))
sup_id = int(input("Enter Supplier Id : "))
qty = int(input("Enter the Quantity : "))
eta = int(input("Estimated expected Delivery (in days) : "))
query = f"INSERT INTO purchases (book_id, sup_id, qty, dt_ord, eta) VALUES ({book_id}, {sup_id}, {qty}, CURDATE(), DATE_ADD(CURDATE(), INTERVAL {eta} DAY))"
cursor.execute(query)
conn.commit()
print("New order Added!!")

def mark_reciv(self):
    ord_id = int(input("Enter the order id for order recieved : "))
    query = f"UPDATE purchases SET recieved = 'T' WHERE ord_id = {ord_id}"
    cursor.execute(query)
    conn.commit()
    print("Recieved Marked successfully")

def mar_cancel(self):
    ord_id = int(input("Enter the order id for order cancelled : "))
    query = f"UPDATE purchases SET recieved = 'C' WHERE ord_id = {ord_id}"
    cursor.execute(query)
    conn.commit()
    print("Cancelled Marked successfully")

def view(self):
    c = int(input("Select an Option\n1. View orders not Recieved\n2. View orders Cancelled\n3. View orders Recieved\nEnter Your choice : "))
    if c == 1:
        received = 'F'
    elif c == 2:
        received = 'C'
    elif c == 3:
        received = 'T'
    else:
        return
    query = f"SELECT * FROM purchases WHERE recieved = '{received}'"
    cursor.execute(query)
    results = cursor.fetchall()
    if results:
        if c == 1:
            print("Orders not recieved are")
        elif c == 2:
            print("Orders Cancelled are")
        elif c == 3:
            print("Orders recieved are")
        for result in results:
            ord_id, book_id, sup_id, qty, dt_ord, eta = result
            print()
            print(f"Order Id: {ord_id}")
            print(f"Book Id: {book_id}")
            print(f"Supplier Id: {sup_id}")
            print(f"Quantity: {qty}")
            print(f"Date Ordered: {dt_ord}")
            print(f"Estimated Delivery date: {eta}")
            print()
    else:
        print("No orders found.")

class employees:
def add_emp(self):
id = int(input("Enter Your Id for verification : "))
query = f"SELECT mgr_stat FROM employees WHERE id = {id}"
cursor.execute(query)
result = cursor.fetchone()
if result:
mgr_status = result[0]
if mgr_status == "T":
print("You Do Not have Manager Rights!!!")
return
else:
print("Employee Not Found!!")
return
name = input("Enter The name of the employee : ")
addr_line1 = input("Enter the Address (line 1) : ")
addr_line2 = input("Enter the Address (line 2) : ")
addr_city = input("Enter the city : ")
addr_state = input("Enter the state : ")
phn = int(input("Enter phone no. : "))
salary = int(input("Enter the salary : "))
query = f"INSERT INTO employees (name, addr1, addr2, addr_city, addr_stat, phone_no, doj, salary) VALUES ('{name}', '{addr_line1}', '{addr_line2}', '{addr_city}', '{addr_state}', {phn}, CURDATE(), {salary})"
cursor.execute(query)
conn.commit()
print("Employee Added Succesfully!")

def assign_mgr_stat(self):
    id = int(input("Enter Your Id for verification : "))
    query = f"SELECT mgr_stat FROM employees WHERE id = {id}"
    cursor.execute(query)
    result = cursor.fetchone()
    if result:
        mgr_status = result[0]
        if mgr_status == "T":
            print("You Do Not have Manager Rights!!!")
            return
    else:
        print("Employee Not Found!!")
        return
    id = int(input("Enter the employee id to grant Manager status : "))
    query = f"UPDATE employees SET mgr_stat = 'T' WHERE id = {id}"
    cursor.execute(query)
    conn.commit()
    print("Manager Status granted")

def search_emp(self):
    id = int(input("Enter the id for searching an employee : "))
    query = f"SELECT * FROM employees WHERE id = {id}"
    cursor.execute(query)
    result = cursor.fetchone()
    if result:
        id, name, addr_line1, addr_line2, addr_city, addr_state, phn, date_of_joining, salary = result
        print("Employees Details")
        print(f"Name: {name}")
        print("Address:")
        print(addr_line1)
        print(addr_line2)
        print(addr_city)
        print(f"State: {addr_state}")
        print(f"Contact no.: {phn}")
        print(f"Date of Joining: {date_of_joining}")
        print(f"Salary: {salary}")
    else:
        print("No Employee Found!!")

def display(self):
    query = "SELECT * FROM employees"
    cursor.execute(query)
    results = cursor.fetchall()
    if results:
        i = 0
        for result in results:
            i += 1
            id, name, addr_line1, addr_line2, addr_city, addr_state, phn, date_of_joining, salary = result
            print(f"Employees Details of Emp no.{i}")
            print(f"Name: {name}")
            print("Address:")
            print(addr_line1)
            print(addr_line2)
            print(addr_city)
            print(f"State: {addr_state}")
            print(f"Contact no.: {phn}")
            print(f"Date of Joining: {date_of_joining}")
            print(f"Salary: {salary}")
            print()
    else:
        print("Employees Not found!")

def update_sal(self):
    id = int(input("Enter the id to update the salary of an employee : "))
    salary = int(input("Enter the updated salary : "))
    query = f"UPDATE employees SET salary = {salary} WHERE id = {id}"
    cursor.execute(query)
    conn.commit()
    print("Salary update Successfully")

class members:
def add_mem(self):
name = input("Enter the name of the member : ")
phn = int(input("Enter phone no. : "))
addr_line1 = input("Enter address (line 1) : ")
addr_line2 = input("Enter address (line 2) : ")
addr_city = input("Enter the city : ")
addr_state = input("Enter the state : ")
query = f"INSERT INTO members(name, addr1, addr2, addr_city, addr_stat, phone_no, beg_date, end_date) VALUES ('{name}', '{addr_line1}', '{addr_line2}', '{addr_city}', '{addr_state}', {phn}, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR))"
cursor.execute(query)
conn.commit()
query = "SELECT id FROM members WHERE phone_no = {phn}"
cursor.execute(query)
result = cursor.fetchone()
if result:
id = result[0]
print("Member Added successfully")
print(f"Member Id: {id}")
else:
print("Member not found")

def refresh(self):
    query = "UPDATE members SET valid = 'invalid' WHERE end_date <= CURDATE()"
    cursor.execute(query)
    conn.commit()

def search_mem(self):
    id = int(input("Enter member id : "))
    query = f"SELECT * FROM members WHERE id = {id}"
    cursor.execute(query)
    result = cursor.fetchone()
    if result:
        id, name, addr_line1, addr_line2, addr_city, addr_state, phn, beg_date, end_date, valid = result
        print("Member Details")
        print(f"Name: {name}")
        print("Address:")
        print(addr_line1)
        print(addr_line2)
        print(addr_city)
        print(f"State: {addr_state}")
        print(f"Contact no.: {phn}")
        print(f"Membership begin date: {beg_date}")
        print(f"Membership end date: {end_date}")
        print(f"Membership Status: {valid}")
    else:
        print("No Member Found!!")

class sales:
def add(self):
member_id = int(input("Enter Menber id :"))
book_id = int(input("Enter the book id : "))
qty = int(input("Enter the quantity : "))
query = f"SELECT price*{qty} FROM books WHERE id = {book_id}"
cursor.execute(query)
result = cursor.fetchone()
if result:
amount = result[0]
print(f"The bill amount : {amount}")
query = f"INSERT INTO sales(mem_id, book_id, qty, amt, sales_date) VALUES ({member_id}, {book_id}, {qty}, {amount}, CURDATE())"
cursor.execute(query)
conn.commit()
query = f"SELECT inv_id FROM sales WHERE mem_id = {member_id} AND book_id = {book_id} AND qty = {qty} AND sales_date = CURDATE()"
cursor.execute(query)
result = cursor.fetchone()
if result:
inv_id = result[0]
print(f"The Invoice id for the bill is {inv_id}")
else:
print("The entered details maybe wrong.")
print("Please Recheck and Enter again")
else:
print("Book Id invalid!!")

def find_total_sales(self):
    query = "SELECT SUM(amt) FROM sales WHERE YEAR(sales_date) = YEAR(CURDATE())"
    cursor.execute(query)
    result = cursor.fetchone()
    if result:
        total_sales = result[0]
        print(f"Total sales this year : {total_sales}")

Functions

def book_menu():
book = books()
while True:
print("1. Add Book")
print("2. Update Book Price")
print("3. Search Book")
print("4. Update Book Quantity")
print("5. Display Books")
print("6. Go Back")
choice = int(input("Enter your choice: "))
if choice == 1:
book.add()
elif choice == 2:
book.update_price()
elif choice == 3:
book.search()
elif choice == 4:
book.update()
elif choice == 5:
book.display()
elif choice == 6:
break
else:
print("Invalid choice. Please try again.")

def sup_menu():
sup = suppliers()
while True:
print("1. Add Supplier")
print("2. Remove Supplier")
print("3. Search Supplier")
print("4. Go Back")
choice = int(input("Enter your choice: "))
if choice == 1:
sup.add_sup()
elif choice == 2:
sup.remove_supplier()
elif choice == 3:
sup.search_id()
elif choice == 4:
break
else:
print("Invalid choice. Please try again.")

def pur_menu():
pur = purchases()
while True:
print("1. New Order")
print("2. Mark Order Received")
print("3. Mark Order Cancelled")
print("4. View Orders")
print("5. Go Back")
choice = int(input("Enter your choice: "))
if choice == 1:
pur.new_ord()
elif choice == 2:
pur.mark_reciv()
elif choice == 3:
pur.mar_cancel()
elif choice == 4:
pur.view()
elif choice == 5:
break
else:
print("Invalid choice. Please try again.")

def emp_menu():
emp = employees()
while True:
print("1. Add Employee")
print("2. Assign Manager Status")
print("3. Search Employee")
print("4. Display Employees")
print("5. Update Salary")
print("6. Go Back")
choice = int(input("Enter your choice: "))
if choice == 1:
emp.add_emp()
elif choice == 2:
emp.assign_mgr_stat()
elif choice == 3:
emp.search_emp()
elif choice == 4:
emp.display()
elif choice == 5:
emp.update_sal()
elif choice == 6:
break
else:
print("Invalid choice. Please try again.")

def mem_menu():
mem = members()
while True:
print("1. Add Member")
print("2. Refresh Members")
print("3. Search Member")
print("4. Go Back")
choice = int(input("Enter your choice: "))
if choice == 1:
mem.add_mem()
elif choice == 2:
mem.refresh()
elif choice == 3:
mem.search_mem()
elif choice == 4:
break
else:
print("Invalid choice. Please try again.")

def sal_menu():
sal = sales()
while True:
print("1. Add Sale")
print("2. Find Total Sales")
print("3. Go Back")
choice = int(input("Enter your choice: "))
if choice == 1:
sal.add()
elif choice == 2:
sal.find_total_sales()
elif choice == 3:
break
else:
print("Invalid choice. Please try again.")

Main menu

while True:
print("1. Books")
print("2. Suppliers")
print("3. Purchases")
print("4. Employees")
print("5. Members")
print("6. Sales")
print("7. Exit")
choice = int(input("Enter your choice: "))
if choice == 1:
book_menu()
elif choice == 2:
sup_menu()
elif choice == 3:
pur_menu()
elif choice == 4:
emp_menu()
elif choice == 5:
mem_menu()
elif choice == 6:
sal_menu()
elif choice == 7:
break
else:
print("Invalid choice. Please try again.")
def main_menu():
c = int(input("Enter Your Choice : "))
if c == 1:
book_menu()
elif c == 2:
sup_menu()
elif c == 3:
pur_menu()
elif c == 4:
emp_menu()
elif c == 5:
mem_menu()
elif c == 6:
sal_menu()
elif c == 7:
exit(1)
else:
print("Wrong Input\n\nInvalid input")

def book_menu():
c = int(input("Enter Your Choice : "))
b = books()
if c == 1:
b.add()
elif c == 2:
b.update_price()
elif c == 3:
b.search()
elif c == 4:
b.update()
elif c == 5:
b.display()
elif c == 6:
return
else:
print("Wrong Input\nInvalid input")

def sup_menu():
c = int(input("Enter Your Choice : "))
s = suppliers()
if c == 1:
s.add_sup()
elif c == 2:
s.remove_supplier()
elif c == 3:
s.search_id()
elif c == 4:
return
else:
print("Wrong Input\nInvalid input")

def pur_menu():
c = int(input("Enter Your Choice : "))
p = purchases()
if c == 1:
p.new_ord()
elif c == 2:
p.view()
elif c == 3:
p.mar_cancel()
elif c == 4:
p.mark_reciv()
elif c == 5:
return
else:
print("Wrong Input\nInvalid input")

def emp_menu():
c = int(input("Enter Your Choice : "))
e = employees()
if c == 1:
e.add_emp()
elif c == 2:
e.search_emp()
elif c == 3:
e.assign_mgr_stat()
elif c == 4:
e.display()
elif c == 5:
e.update_sal()
elif c == 6:
return
else:
print("Wrong Input\nInvalid input")

def mem_menu():
c = int(input("Enter Your Choice : "))
m = members()
m.refresh()
if c == 1:
m.add_mem()
elif c == 2:
m.search_mem()
elif c == 3:
return
else:
print("Wrong Input\nInvalid input")

def sal_menu():
c = int(input("Enter Your Choice : "))
s = sales()
if c == 1:
s.add()
elif c == 2:
s.find_total_sales()
elif c == 3:
return
else:
print("Wrong Input\nInvalid input")

def pass():
num = 0
print("Enter password : ")
for i in range(4):
num = num * 10 + (getch() - 48)
print("*")
if num == PASSWORD:
print("\n\nCorrect Password\n\n")
print("Press any key...")
getch()
else:
print("\n\nIncorrect Password\n\n")
print("Press any key...")
getch()
exit(1)

pass()
conn = mysql_init(0)
conn = mysql_real_connect(conn, HOST, USER, PASS, DATABASE, PORT, None, 0)
choice = 0
if conn:
while True:
main_menu()
else:
print("Error While connection to database.\nContact Tech Expert.")
getch()

Close cursor and connection

cursor.close()
conn.close()