python_mysql_intergartion.py

import time
import mysql.connector
import sql_connector as db
class Books:
    def add_books(self):
       while(1):
            try:
                a=int(input("ENTER NUMBER OF BOOKS YOU WANT TO ADD (max=10): "))
                if 1 <= a <= 10 :
                    break
                else:
                    print("ENTER THE NUMBER FROM 1 TO 10")
            except ValueError:
                    print("ENTER AN INTEGER !!")
       if (a<1 or a> 10):
           print("ENTER BETWEEN 1 TO 10 ONLY !!")
           return
       for i in range(1,a+1):
            while(1):
                    book=input(f"ENTER NAME OF THE BOOK {i}: ").strip().upper()
                    if(book.replace(" ","").isalpha()):
                            break
                    else:
                        print("NAME NOT VALID!!")
            while(1):
                    author=input(f"ENTER NAME OF THE AUTHOR of book {i}: ").strip().upper()
                    if(author.replace(" ","").isalpha()):
                            break
                    else:
                        print("NAME NOT VALID!!")
            while (1):
                try:
                    number=int(input(f"ENTER THE NUMBER OF BOOK {i}: "))
                    if(number >0):
                        break
                except ValueError:
                    print("ENTER AN INTEGER ONLY !!")
            while (1):
                try:
                    copies=int(input(f"ENTER THE COPIES OF BOOK {i}: "))
                    if (copies > 0  ):
                        break 
                except ValueError:
                    print("ENTER AN INTEGER")            
            query = """
            INSERT INTO records
            (Book_Name,Author_Name,Book_Number,Total_Copies,Available_Copies)
            VALUES(%s,%s,%s,%s,%s)
            """
            values=(book,author,number,copies,copies)
            try:
                db.cursor.execute(query, values)
                print("BOOK ADDED SUCCESSFULLY")
            except mysql.connector.IntegrityError:
                print("BOOK NAME OR BOOK NUMBER ALREADY EXISTS!")
       db.conn.commit()
    def view_books(self):
        print("WHAT DO YOU WANT TO KNOW? \n BOOKS NAME(1) \n AUTHOR'S NAME(2) \n BOOK NUMBER (3) \n ALL(4)")
        while(1):
            try:
                t=int(input("ENTER YOUR CHOICE:"))
            except ValueError:
                print("ENTER AN INTEGER ONLY !!")
            if t == 1:
                query= """
                SELECT Book_Name FROM records
                """
                db.cursor.execute(query)
                rows=db.cursor.fetchall()
                for row in rows:
                    print(row)
                break
            elif t == 2:
                query= """
                SELECT Author_Name FROM records
                """
                db.cursor.execute(query)
                rows=db.cursor.fetchall()
                for row in rows:
                    print(row)
                break
            elif t== 3:
                query= """
                SELECT Book_Number FROM records
                """
                db.cursor.execute(query)
                rows=db.cursor.fetchall()
                for row in rows:
                    print(row)
                break
            elif t == 4:
                query= """
                SELECT * FROM records
                """
                db.cursor.execute(query)
                rows=db.cursor.fetchall()
                for row in rows:
                    print(row)
                break
            else:
                print("INVALID CHOICE !!")
class Library(Books):
    def search(self):
            while(1):
                try:
                    a=int(input("ENTER THE BOOK NUMBER YOU WANT TO SEARCH: "))
                    break
                except ValueError:
                    print("ENTER AN INTEGER ONLY")
            query="""
                    SELECT Book_Name,Author_Name FROM records where Book_Number= %s
                    """
            db.cursor.execute(query,(a,))
            row= db.cursor.fetchone()
            if row:
                print(row)
            else:
                print("BOOK NOT FOUND !!")
    def issue(self):
        while(1):
            try:
                i=int(input("ENTER THE BOOK NUMBER YOU WANT TO ISSUE: "))
                break
            except ValueError:
                print("ENTER A VALID BOOK NUMBER !!")
        query="""
        UPDATE records SET Available_Copies= Available_Copies -1 where Book_Number =%s AND Available_Copies > 0
            """
        db.cursor.execute(query,(i,))
        db.conn.commit()  
        if db.cursor.rowcount > 0:
            print("BOOK ISSUED SUCCESSFULLY") 
        else:
            print("BOOK NOT FOUND or OUT OF COPIES !!")
    def return_book(self):
        while(True):
            try:
                a=int(input("ENTER THE BOOK NUMBER YOU WANT TO RETURN: "))
                break
            except ValueError:
                print("ENTER A VALID BOOK NUMBER !!")
        query="""  
        UPDATE records SET Available_Copies= Available_Copies +1 where Book_Number =%s AND Available_Copies < Total_Copies
        """
        db.cursor.execute(query,(a,))
        db.conn.commit()
        if db.cursor.rowcount >0 :
            print("BOOK RETURNED SUCCESSFULLY !!")
        else:
            print("BOOK HAS NO PRIOR RECORD IN THE LIBRARY !!")
            a=input("DO YOU WANT TO ADD A BOOK(y/n): ").upper()
            if a == "Y" :
                    self.add_books()
            elif a == "N":
                return
            else:
                print("INVALID CHOICE !!")
    def remove_books(self):
        while(1):
            r=input("DO YOU WANT TO REMOVE ALL BOOKS FROM LIBRARY? (ALL/SOME/NONE): ").upper()
            if(r=="ALL"):
                query= """  
                DELETE FROM records
                """
                db.cursor.execute(query)
                db.conn.commit()
                print("THE LIBRARY IS FULLY EMPTY !! ALL THE BOOKS ARE REMOVED WITH THEIR DETAILS.")
                break
            elif(r=="SOME"):
                try:
                    a=int(input("ENTER THE NUMBER OF BOOKS YOU WANT TO DELETE(max= 5):"))
                    if 1<= a <= 5:
                        for i in range(a):
                            b = input("ENTER THE BOOK NAME YOU WANT TO DELETE: ").upper()
                            query ="""  
                            DELETE FROM records WHERE Book_Name = %s
                            """
                            db.cursor.execute(query,(b,))
                            db.conn.commit()
                            if db.cursor.rowcount > 0 :
                                print("BOOK REMOVED SUCCESSFULLY !!")
                            else :
                                print("BOOK NOT FOUND !!")
                    else :
                        print("ENTER INTEGER BETWEEN 1 AND 5 !!")
                except ValueError:
                    print("INVALID !!")
            elif (r=="NONE"):
                break
            else:
                print("INVALID CHOICE !!")
books=Library()             
def greet():
    a=int(time.strftime("%I"))
    p=(time.strftime("%p"))     
    if(p=="AM" ):
            print("GOOD MORNING SIR!")
    elif p == "PM" and (a == 12 or 1 <= a <= 3):
            print("GOOD AFTERNOON SIR!")
    else:
            print("GOOD EVENING SIR!")
    print("---------LIBRARY MANAGEMENT SYSTEM---------")
    while(True):
        try:
            CHOICE= int(input("ENTER YOUR CHOICE \n 1. ADD BOOK(s) \n 2. VIEW BOOK(s) \n 3. SEARCH BOOK(s) \n 4. ISSUE BOOK(s) \n 5. RETURN BOOK(s) \n 6.REMOVE BOOK(s) \n 7. EXIT  "))   
        except ValueError:
            print("INVALID CHOICE !!")
            continue
        match CHOICE :
            case 1 :
                books.add_books()
            case 2 :
                books.view_books()
            case 3:
                books.search()
            case 4:
                books.issue()
            case 5:
                books.return_book()
            case 6 :
                books.remove_books() 
            case 7 :
                print("EXITING...............") 
                break
if __name__ == "__main__":
    greet()
    db.cursor.close()
    db.conn.close()