def bookOption(cardNum): inp = 0 while inp != 3: # Display User Options print("\nWhat would you like to do?") print("\n1) Check out a book\n2) Return a book\n3) Quit to main menu\n") inp = input("Please enter a number between 1 and 3: ") # Take User Input if check.validInput(inp, 1, 3): inp = int(inp) print("\nYou selected", inp) if inp == 1: selectBranch(cardNum, True) # Check out book elif inp == 2: selectBranch(cardNum, False) # Return a book elif inp == 3: print("\nMoving to main menu...")
def selectBorBook(branchId, cardNum): # Option 2, Return a book loans = getBorrowedBooks(branchId, cardNum) numLoans = len(loans) bookInp = 0 if numLoans == 0: print("\nYou do not have any outstanding book loans.\n") else: while numLoans != 0: loans = getBorrowedBooks(branchId, cardNum) numLoans = len(loans) print(f"\nHere are the books owned by borrower #{cardNum}.") print("\nPlease select a book to return:\n") for i in range(0, numLoans): # Allows for change in number of books bookID = loans[i][0] title = getBookTitle(bookID) authorID = getAuthorID(bookID) author = getAuthorName(authorID) dueDate = getDueDate(bookID, branchId, cardNum).strftime("%a, %m/%d/%Y") print(f"{i+1}) {title} by {author} | Due Date: {dueDate})") print(f"{numLoans + 1}) Quit to previous page\n") # Take input from user and take appropriate action bookInp = input( f"Please enter a number between 1 and {numLoans + 1}: ") if check.validInput(bookInp, 1, numLoans + 1): bookInp = int(bookInp) # If quit not selected if bookInp != (numLoans + 1): myBook = loans[bookInp - 1] myBookTitle = getBookTitle(myBook[0]) print(f"\nYou picked {myBookTitle}") print( f"\nYou are returning 1 copy of {myBookTitle} to {getBranchName(branchId)}." ) myBookID = myBook[0] processReturn(myBookID, branchId, cardNum) else: break print("\nMoving to previous page...")
def selectLibBook(branchId, branchName, branchLocation, cardNum): bookInp = 0 numBooks = -2 while bookInp != numBooks + 1: #**THIS WILL NEED A SQL QUERY TO POPULATE** #Will be based on library branch id # books = {1: ["The Lost Tribe", "Sidney Sheldon", 5], 2: ["The Haunting", "Stephen King", 3], # 3: ["Microtrends", "Sidney Sheldon", 10], 4: ["Test Book", "John Jackson", 0]} books = getTableData("tbl_book") numBooks = len(books) print("\nHere are the books at {0} in {1}.".format( branchName, branchLocation)) print("\nPlease select a book to check out:\n") for bId, bInfo in books.items(): #Allows for change in number of books print("{0}) {1} copies of {2} by {3}".format( bId, books[bId][2], bInfo[0], bInfo[1])) print("{0}) Quit to previous page\n".format(numBooks + 1)) #Take input from user and take appropriate action bookInp = input( "Please enter a number between 1 and {0}: ".format(numBooks + 1)) if check.validInput(bookInp, 1, numBooks + 1): bookInp = int(bookInp) #If quit not selected if bookInp != (numBooks + 1): myBook = books[bookInp] print("\nYou picked {0}".format(myBook[0])) #CHECK FOR 0 COPIES if myBook[2] == 0: print("\nSorry we have no more copies of", myBook[0]) else: print("\nYou are checking out 1 copy of", myBook[0]) #***Needs to subtract 1 from no_book_copies in the database*** #Include in book loans the date out (curdate), and due date (add a week) ''' ADD SQL CODE HERE ''' #This is merely cosmetic, will get actual value from DB print("\nNew number of copies: {0}".format(myBook[2] - 1)) else: print("\nMoving to previous page...")
def delete(tableName): inp = -1 numRows = -1 #List rows in table while inp != numRows + 1: table = getTableData(tableName) tIds = getIds(tableName) numRows = len(table) print(f"\nPlease select a row in {tableName} to delete:\n") for i in range(0, numRows): print(f"{i+1}) {table[i]}") print(f"{numRows+1}) To quit to previous menu") inp = input(f"\nPlease enter a number between 1 and {numRows+1}: ") if check.validInput(inp, 1, numRows + 1): #Grab specific row based on selection inp = int(inp) if inp != numRows + 1: row = table[inp - 1] #Let the user make sure of their decision print(f"\nAre you sure you want to delete:\n{row}") backout = input("\nType 'y' if you want to proceed: ") if backout in ('y', 'Y', 'yes', 'Yes', 'YES'): print(f"\nDeleting {row}...") pKey = tIds[inp - 1] #Gonna have to do specific delete based on table if tableName == 'tbl_publisher': #Delete the chosen row from the database stmt = f"DELETE FROM tbl_publisher WHERE publisherId = {pKey};" elif tableName == 'tbl_library_branch': #Delete the chosen row from the database stmt = f"DELETE FROM tbl_library_branch WHERE branchId = {pKey};" elif tableName == 'tbl_borrower': #Delete the chosen row from the database stmt = f"DELETE FROM tbl_borrower WHERE cardNo = {pKey};" #Send SQL statement to the database try: mycursor.execute(stmt) mydb.commit() except Exception as e: mydb.rollback() raise e else: print("\nMoving to previous page...")
def selectAction(tableId): #1 Books & authors #2 Publishers #3 Library Branches #4 Borrowers #5 Override Due Date in Book Loan tableNames = {1:['tbl_book', 'tbl_book_authors', 'tbl_author'], 2:['tbl_publisher'], 3:['tbl_library_branch'], 4:['tbl_borrower'], 5:['tbl_book_loans']} if tableId == 5: overrideDueDate() #Just send to a function else: inp = 0 while inp != 4: print("\nMenu for Adjusting:") for tbl in tableNames[tableId]: print(tbl) print("\nPlease select your type of adjustment.\n\n1) Add\n2) Update\n3) Delete\n4) Quit to previous menu") inp = input("\nPlease enter a number between 1 and 4: ") if check.validInput(inp, 1, 4): inp = int(inp) #Updating singular tables can share a function if tableId != 1: if inp == 1: add(tableNames[tableId][0]) elif inp == 2: update(tableNames[tableId][0]) elif inp == 3: delete(tableNames[tableId][0]) else: print("\nMoving to previous menu...") #Updating books and authors requires consistency checks #They are in multiple tables else: if inp == 1: addBookAuthor() elif inp == 2: updateBookAuthor() elif inp == 3: deleteBookAuthor() else: print("\nMoving to previous menu...")
def main(): print("\nWelcome to the GCIT Library Management System.") inp = 0 # Main menu loop while inp != 4: print( "\nWhich category of a user are you?:\n\n1) Librarian\n2) Administrator\n3) Borrower\n4) To Quit" ) inp = input("\nPlease enter a number between 1 and 4: ") # Check for valid input, then proceed if check.validInput(inp, 1, 4): inp = int(inp) if inp == 1: lib.runLibrarian() elif inp == 2: ad.runAdministrator() elif inp == 3: bor.runBorrower() # If inp = 4 else: print("\nGoodbye!")
def adjustLibrary(branchId, branchName, branchLocation): #Same thing as above inp = 0 #In Lib3, for picking library actions while inp != 3: print("\nLibrary functions of library #{:d}".format(branchId)) print("\nAt location: {0}, {1}".format(branchName, branchLocation)) #List Lib3 options print( "\n1) Update the details of the library\n2) Add copies of book to the branch\n3) Quit to previous\n" ) inp = input("Please enter a number between 1 and 3: ") if check.validInput(inp, 1, 3): inp = int(inp) #Option 1, Update Library Branch if inp == 1: changeBranchInfo(branchId, branchName, branchLocation) #Option 2, Update Book count elif inp == 2: updateBookCount(branchId, branchName, branchLocation) else: print("\nMoving to previous page...")
def selectBranch(): #IMPORTANT NOTE #**Probably need function to grab libraries from database, this will change** libraries = { 1: ["University Library", "Boston"], 2: ["State Library", "New York"], 3: ["Federal Library", "Washington DC"], 4: ["County Library", "McLean VA"], 5: ["Test Library", "Maryland"] } #numBranches + 1 will be our quit option numBranches = len(libraries) #This input will be for the library selection libInp = 0 #Lib 2, Select library branch while libInp != (numBranches + 1): print("\nWhich branch do you manage?\n") #Print out options for bId, location in libraries.items(): #Allows for change in number of libraries print("{0}) {1}, {2}".format(bId, location[0], location[1])) print("{0}) Quit to previous page\n".format(numBranches + 1)) #Take input from user and take appropriate action libInp = input( "Please enter a number between 1 and {0}: ".format(numBranches + 1)) if check.validInput(libInp, 1, numBranches + 1): libInp = int(libInp) if libInp == (numBranches + 1): #Quit Lib2 to Lib1 print("\nMoving to previous page...") else: #Move to Lib 3 adjustLibrary(libInp, libraries[libInp][0], libraries[libInp][1])
def selectBranch(cardNum, checkout): #IMPORTANT NOTE libraries = getTableData("tbl_library_branch") #numBranches + 1 will be our quit option numBranches = len(libraries) #This input will be for the library selection libInp = 0 #If checking out a book if checkout: print("\nPick the branch you want to check out from:\n") #If returning a book else: print("\nPick the branch you want to return book to:\n") #Print out options # for bId, location in libraries.items(): for j in range(0, len(libraries)): #Allows for change in number of libraries print(libraries[j]) # print("{0}) {1}, {2}".format(i, j[0], j[1])) print("{0}) Quit to previous page\n".format(numBranches + 1)) #Take input from user and take appropriate action libInp = input( "Please enter a number between 1 and {0}: ".format(numBranches + 1)) if check.validInput(libInp, 1, numBranches + 1): libInp = int(libInp) if libInp == (numBranches + 1): #Quit to Borr1 print("\nMoving to previous page...") elif checkout: #Move to book menu selectLibBook(libInp, libraries[libInp][0], libraries[libInp][1], cardNum) else: selectBorBook(libInp, libraries[libInp][0], libraries[libInp][1], cardNum)
def overrideDueDate(): inp = -1 numBors = -1 while inp != numBors + 1: # WHILE YOU DIDNT SLECT QUIT #Get borrower data from database borrowers = getTableData('tbl_borrower') numBors = len( borrowers) #DISPLAY OPTION FOR THE USERS BY ITERATIING THROUGH cardNums = getIds('tbl_borrower') print( f"\nPlease select a borrower to override due date of:\n" ) #WHICH BORROWER DO YOU WANT TO UPDATE THE DUE DATE FOR IN THE LIST for i in range(0, numBors): print(f"{i+1}) {borrowers[i]}") print(f"{numBors+1}) To quit to previous menu") inp = input(f"\nPlease enter a number between 1 and {numBors+1}: " ) # IF INPUT IS VALID THEN if check.validInput(inp, 1, numBors + 1): #CHECK INPUT #Grab specific borrower based on selection inp = int(inp) if inp != numBors + 1: #REMEMEBR WHAT BOR THE USER CHOSE borrower = borrowers[inp - 1] #ASSIGN THE SPECFIC BROOW cardNum = cardNums[inp - 1] # ASSIGN THE CARD NUMBER #Find borrower's loans in tbl_book_loans loanCount = 0 try: #LIST ALL THE LOANS THE BOR HAS, GET ALL THE LOANS FROM BOOK LOAN WITH THE CARDNUMBER mycursor.execute( f"SELECT * FROM tbl_book_loans WHERE cardNo = {cardNum}" ) borLoans = mycursor.fetchall() #SHOW ALL LOAN loanCount = len( borLoans ) #WHAT WE WILL ITERATE THROUGH, NUMBER OF LOANS ONE BOR HAS FETCH ALL BOOK LOAN FOR ONE USER except Exception as e: raise e #If there aren't any loans, then leave page if loanCount == 0: #WE KNOW THE BOR CARD NUMBER WE CAN USE THE CARD NUMBER TO LOOK IN BOOKLOANS, DOENS'T MATTER WHAT BRANCH #IT WILL SHOW EVERY LOAN FROM BORROW, LIST OF TUPLES, IF IT DOENS'T RETURN ANY, A BORROW CAN GET RID OF A BOOK BY RETURNING A LOAN print("\nThis borrower has no outstanding loans.") print("\nMoving to previous page ...") return #Otherwise, show the user their loans else: print(f"\nOverriding due dates for {borrower[1]}.") loanInp = -1 while loanInp != loanCount + 1: print( f"\nPlease select a loan to override due date of:\n" ) #Will refresh the due dates after they are changed try: #THESE ARE ALL THE LOANS THE BOR HAS UNDER HIS CARD NUMBER, WE RERUN SO WE CAN SEE IT ON THE SCREEN AGAIN #WHEN WE COME THIS AGAIN THE CURSOR WILL THE NEW DATA IF IT WAS UPDATED IN A PREVOIUS MENU. mycursor.execute( f"SELECT * FROM tbl_book_loans WHERE cardNo = {cardNum}" ) borLoans = mycursor.fetchall() #IMMUTABLE, TUPLE except Exception as e: raise e #Print options to user for i in range(0, loanCount): print( f"{i+1}) {borLoans[i]}" ) #REFORMATE 1 FOR THE USER, SHOW THE BOOK LOANS print(f"{loanCount+1}) To quit to previous menu") loanInp = input( f"\nPlease enter a number between 1 and {loanCount+1}: " ) #GRAB INPUT FROM THE USER, AFTER WE DISPLAYED ALL THE LOANS if check.validInput(loanInp, 1, loanCount + 1): #Grab specific row based on selection loanInp = int( loanInp ) #SAVE THE INPUT, AS LONG AS IT'S NOT QUIT if loanInp != loanCount + 1: print( f"borLoans:{borLoans}" ) # OUT OF ALL THE LOANS THIS IS THE ONE THE USER SELECTED loan = borLoans[ loanInp - 1] #SAVE THE LOAN, SAVE LOAN TRUPLE THIS IS CALLING A NEW 2D ARRAY NAMED LOAN #Show user current due date #**Maybe clean up how the date is displayed print( f"\nThis borrower has a due date of: {loan[4]}" ) #SHOW DUE DATE, FROM THE 2D ARRAY FORM THE TUPLE, THE 4TH IN #BOR IS A 2D ARRAY AND SO WE CAN GET A 4TH POS OF THE DUE DATE #User inputs date or quits newDate = input( "Please input new date (yyyy-mm-dd), enter 'quit' if no change:\n" ) print(f"\nnewDate:{newDate}\n") print(f"\nloan:{loan}\n") #IF QUIT THEN QUIT OTHERWISE UPDATE if newDate in ('quit', 'QUIT', 'Quit', 'Q', 'q'): print("\nMoving to previous menu ...") else: #**Could use a decent date format validation try: #MAIN UPDATE mycursor.execute( f"UPDATE tbl_book_loans SET dueDate = '{newDate}' WHERE bookId = {loan[0]} AND branchId = {loan[1]} AND cardNo = {loan[2]};" ) mydb.commit() except Exception as e: mydb.rollback() raise e else: print("\nMoving to previous page ...") else: print("Moving to previous page ...")
def deleteBookAuthor(): inp = -1 numBooks = -1 #List rows in table while inp != numBooks + 1: books = getTableData('tbl_book') bIds = getIds('tbl_book') numBooks = len(books) print(f"\nPlease select a book to delete:\n") for i in range(0, numBooks): print(f"{i+1}) {books[i]}") print(f"{numBooks+1}) To quit to previous menu") inp = input(f"\nPlease enter a number between 1 and {numBooks+1}: ") if check.validInput(inp, 1, numBooks + 1): #Grab specific row based on selection inp = int(inp) if inp != numBooks + 1: book = books[inp - 1] #Let the user make sure of their decision print(f"\nAre you sure you want to delete:\n{book}") backout = input("\nType 'y' if you want to proceed: ") if backout in ('y', 'Y', 'yes', 'Yes', 'YES'): print(f"\nDeleting {book}...") pKey = bIds[inp - 1] #Delete book from tbl_book and tbl_book_authors stmt = "DELETE FROM tbl_book_authors WHERE bookId = %s;" data = (pKey, ) try: connection = getConnection() cursor = connection.cursor() cursor.execute(stmt, data) connection.commit() except Exception as e: connection.rollback() raise e finally: connection.close() cursor.close() stmt = "DELETE FROM tbl_book WHERE bookId = %s;" try: connection = getConnection() cursor = connection.cursor() cursor.execute(stmt, data) connection.commit() except Exception as e: connection.rollback() raise e finally: connection.close() cursor.close() #Send SQL statements to the database authorIds = getIds('tbl_author') #Find all author ids in tbl_book_author connection = getConnection() try: cursor = connection.cursor() sql = "SELECT authorId FROM tbl_book_authors;" cursor.execute(sql) result = cursor.fetchall() connection.commit() bAuthorIds = [item[0] for item in result] finally: connection.close() cursor.close() badId = -1 #If author Id is in tbl_author but not in tbl_book_authors, then delete that author #In other words, delete the author with no books for aId in authorIds: if aId not in bAuthorIds: badId = aId stmt = "DELETE FROM tbl_author WHERE authorId = %s;" data = (badId, ) try: connection = getConnection() cursor = connection.cursor() cursor.execute(stmt, data) connection.commit() except Exception as e: connection.rollback() raise e finally: connection.close() cursor.close()
def overrideDueDate(): inp = -1 numBors = -1 #Unless the user wants to quit while inp != numBors + 1: #Get borrower data from database borrowers = getTableData('tbl_borrower') numBors = len(borrowers) cardNums = getIds('tbl_borrower') print(f"\nPlease select a borrower to override due date of:\n") #Display borrowers for i in range(0, numBors): print(f"{i+1}) {borrowers[i]}") print(f"{numBors+1}) To quit to previous menu") inp = input(f"\nPlease enter a number between 1 and {numBors+1}: ") if check.validInput(inp, 1, numBors + 1): #Grab specific borrower based on selection inp = int(inp) if inp != numBors + 1: borrower = borrowers[inp - 1] cardNum = cardNums[inp - 1] #Find borrower's loans in tbl_book_loans loanCount = 0 connection = getConnection() try: cursor = connection.cursor() cursor.execute( "SELECT * FROM tbl_book_loans WHERE cardNo = %s", (cardNum, )) borLoans = cursor.fetchall() loanCount = len(borLoans) connection.commit() except Exception as e: raise e finally: connection.close() cursor.close() #If there aren't any loans, then leave page if loanCount == 0: print("\nThis borrower has no outstanding loans.") print("\nMoving to previous page ...") return #Otherwise, show the user their loans else: print(f"\nOverriding due dates for {borrower[1]}.") loanInp = -1 while loanInp != loanCount + 1: print( f"\nPlease select a loan to override due date of:\n" ) #Will refresh the due dates after they are changed connection = getConnection() try: cursor = connection.cursor() cursor.execute( "SELECT * FROM tbl_book_loans WHERE cardNo = %s", (cardNum, )) borLoans = cursor.fetchall() #This will show the loans with the book title and branch name stmt = "SELECT bor.name, lb.branchName, b.title, bl.dateOut, bl.dueDate FROM tbl_book AS b, tbl_book_loans AS bl, tbl_borrower AS bor, tbl_library_branch AS lb WHERE b.bookId = bl.bookId AND bor.cardNo = bl.cardNo AND lb.branchId = bl.branchId AND bor.cardNo = %s;" cursor.execute(stmt, (cardNum, )) borLoanNames = cursor.fetchall() except Exception as e: raise e finally: connection.close() cursor.close() #Print show user what loans the borrower has for i in range(0, loanCount): dateOut = borLoanNames[i][3].strftime("%m-%d-%y") dueDate = borLoanNames[i][4].strftime("%m-%d-%y") print( f"{i+1}) Branch: {borLoanNames[i][1]}, Book: {borLoanNames[i][2]}, Date Out: {dateOut}, Due Date: {dueDate}" ) print(f"{loanCount+1}) To quit to previous menu") loanInp = input( f"\nPlease enter a number between 1 and {loanCount+1}: " ) if check.validInput(loanInp, 1, loanCount + 1): #Grab specific row based on selection loanInp = int(loanInp) if loanInp != loanCount + 1: loan = borLoans[loanInp - 1] #Show user current due date print( f"\nThis borrower has a due date of: {loan[4]}" ) #User inputs date or quits newDate = input( "Please input new date (yyyy-mm-dd), enter 'quit' if no change:\n" ) if newDate in ('quit', 'QUIT', 'Quit', 'Q', 'q'): print("\nMoving to previous menu ...") else: stmt = "UPDATE tbl_book_loans SET dueDate = %s WHERE bookId = %s AND branchId = %s AND cardNo = %s;" data = (newDate, loan[0], loan[1], loan[2]) try: connection = getConnection() cursor = connection.cursor() cursor.execute(stmt, data) connection.commit() except Exception as e: connection.rollback() raise e finally: connection.close() cursor.close() else: print("\nMoving to previous page ...") else: print("Moving to previous page ...")
def updateBookAuthor(): #Have user choose to add just a book, or add a new book and auth print( "\nWould you like to update:\n\n1) Books\n2) Authors\n3) To quit to previous" ) choice = input("\nPlease input an input between 1 and 3: ") #If add type choice isn't to quit if check.validInput(choice, 1, 3): choice = int(choice) if choice != 3: inp = -1 numRows = -1 #List rows in table while inp != numRows + 1: #Grab table data, primary keys, and count the tuples for the entire table #Should refresh after every update if choice == 1: tableName = 'tbl_book' else: tableName = 'tbl_author' table = getTableData(tableName) tIds = getIds(tableName) numRows = len(table) #Have user select row tuple to update print(f"\nPlease select a row in {tableName} to update:\n") for i in range(0, numRows): print(f"{i+1}) {table[i]}") print(f"{numRows+1}) To quit to previous menu") inp = input( f"\nPlease enter a number between 1 and {numRows+1}: ") if check.validInput(inp, 1, numRows + 1): #Grab specific row based on selection inp = int(inp) #If not quit if inp != numRows + 1: #Based on selection, grab data tuple, and it's primary key row = table[inp - 1] pKey = tIds[inp - 1] #Gonna have to do specific update based on table #But all tables using this function have name and address print("\nHere is the current name:", row[1]) nameInput = input( "\nWhat would you like to change it to?(Limit to 45 characters), Type 'N/A' if you do not wish to change:\n" ) if nameInput in ('N/A', 'n/a', 'NA', 'na'): nameInput = row[1] #Tailor update statement to match the table we're updating if tableName == 'tbl_book': #Give choice of publisher publishers = getTableData('tbl_publisher') pubIds = getIds('tbl_publisher') numPubs = len(publishers) #List publishers in tbl_publisher, and have user choose one for i in range(0, numPubs): print(f"{i+1}) {publishers[i]}") print(f"{numPubs+1}) Keep the same publisher") pubInp = input( f"\nPlease enter a number between 1 and {numPubs+1}: " ) if check.validInput(pubInp, 1, numPubs + 1): pubInp = int(pubInp) #If changing the publisher if pubInp != numPubs + 1: pubId = pubIds[pubInp - 1] else: #Use the publisher Id of the book we selected pubId = row[2] stmt = "UPDATE tbl_book SET title = %s, pubId = %s WHERE bookId = %s;" data = (nameInput, pubId, pKey) elif tableName == 'tbl_author': stmt = "UPDATE tbl_author SET authorName = %s WHERE authorId = %s;" data = (nameInput, pKey) #Send SQL statements to the database try: connection = getConnection() cursor = connection.cursor() cursor.execute(stmt, data) connection.commit() except Exception as e: connection.rollback() raise e finally: connection.close() cursor.close() else: print("\nMoving to previous page ...")
def addBookAuthor(): #Have user choose to add just a book, or add a new book and auth print( "\nWould you like to add:\n\n1) Just a book, with existing author\n2) A book and an author\n3) To quit to previous" ) choice = input("\nPlease input an input between 1 and 3: ") #If add type choice isn't to quit if check.validInput(choice, 1, 3): choice = int(choice) #If user didn't select quit if choice != 3: #get book title to input into db print("\nPlease limit to 45 characters.") #If title is 'quit' then go to previous page #tileInp going into titleInp = input( "Please enter the title of the new book (type 'quit' to cancel):\n" ) #If user types quit if titleInp in ('q', 'Q', 'quit', 'Quit', 'QUIT'): print("\nMoving to previous page...") return #Give choice of publisher publishers = getTableData('tbl_publisher') pubIds = getIds('tbl_publisher') numPubs = len(publishers) #List publishers in tbl_publisher, and have user choose one for i in range(0, numPubs): print(f"{i+1}) {publishers[i]}") print(f"{numPubs+1}) Quit to previous page") pubInp = input( f"\nPlease enter a number between 1 and {numPubs+1}: ") if check.validInput(pubInp, 1, numPubs + 1): pubInp = int(pubInp) #If not quitting, remeber publisher Id of selection if pubInp != numPubs + 1: #pubID is going into the database #Grab publisher Id based on user's selection pubId = pubIds[pubInp - 1] #Add title and pubLisherID to tbl_book sql = "Insert into tbl_book values (NULL, %s, %s);" data = (titleInp, pubId) try: connection = getConnection() cursor = connection.cursor() #Had to insert here so there is an existing Id for the book cursor.execute(sql, data) connection.commit() print("\nBook added to database.") except Exception as e: connection.rollback() raise e finally: connection.close() cursor.close() #If quitting else: print("\nMoving to previous page...") return #Want to assign an author that is already in the database if choice == 1: #Select an exiting author #This will go in tbl_book_authors, with the book_id authors = getTableData('tbl_author') authIds = getIds('tbl_author') numAuths = len(authors) #Listing authors for i in range(0, numAuths): print(f"{i+1}) {authors[i]}") print(f"{numAuths+1}) Quit to previous page") authInp = input( f"\nPlease enter a number between 1 and {numAuths+1}: " ) if check.validInput(authInp, 1, numAuths + 1): authInp = int(authInp) #If not quitting, remeber publisher Id of selection if authInp != numAuths + 1: #authorId is going to be based on user's selection authId = authIds[authInp - 1] #If quitting else: print("\nMoving to previous page...") return #If choice = 2 else: #Ask user for a new author print("\nPlease limit to 45 characters.") #If nameInp is 'quit' then go to previous page nameInp = input( "Please enter the name of the new author (type 'quit' to cancel):\n" ) #If user types quit if nameInp in ('q', 'Q', 'quit', 'Quit', 'QUIT'): print("\nMoving to previous page...") return #Add title and pubLisherID to tbl_book sql = "Insert into tbl_author values (NULL, %s);" data = (nameInp, ) try: connection = getConnection() cursor = connection.cursor() #Had to insert here so there is an existing Id for the book cursor.execute(sql, data) connection.commit() print("\nAuthor added to database.") except Exception as e: connection.rollback() raise e finally: connection.close() cursor.close() #For tbl_book_authors, get author id of author we just added authIds = getIds('tbl_author') print(f"authIds(from getIds):{authIds}") authId = authIds[-1] #Now we need the book Id, need this for both instances #This is the book id of our newly added book bookIds = getIds('tbl_book') bookId = bookIds[-1] #Add bookId and authorId together in tbl_book_authors sql = "Insert into tbl_book_authors values (%s, %s);" data = (bookId, authId) try: connection = getConnection() cursor = connection.cursor() #Had to insert here so there is an existing Id for the book cursor.execute(sql, data) connection.commit() except Exception as e: connection.rollback() raise e finally: connection.close() cursor.close() #If input was invalid print("\nMoving to previous page ...") #If quit is selected else: print("\nMoving to previous page ...")
def update(tableName): inp = -1 numRows = -1 #List rows in table while inp != numRows + 1: #Grab table data, primary keys, and count the tuples for the entire table #Should refresh after every update table = getTableData(tableName) tIds = getIds(tableName) numRows = len(table) #Have user select row tuple to update print(f"\nPlease select a row in {tableName} to update:\n") for i in range(0, numRows): print(f"{i+1}) {table[i]}") print(f"{numRows+1}) To quit to previous menu") inp = input(f"\nPlease enter a number between 1 and {numRows+1}: ") if check.validInput(inp, 1, numRows + 1): #Grab specific row based on selection inp = int(inp) if inp != numRows + 1: #Based on selection, grab data tuple, and it's primary key row = table[inp - 1] pKey = tIds[inp - 1] #Gonna have to do specific update based on table #But all tables using this function have name and address print("\nHere is the current name:", row[1]) nameInput = input( "\nWhat would you like to change it to?(Limit to 45 characters), Type 'N/A' if you do not wish to change:\n" ) if nameInput in ('N/A', 'n/a', 'NA', 'na'): #Since all tables her have data in the same order (id, name, addr), we leverage that to simplify these statements #We could potentially use a conditional to tailor what index the data came from if that wasn't the case nameInput = row[1] print("\nHere is the current address:", row[2]) addrInput = input( "\nWhat would you like to change it to?(Limit to 45 characters), Type 'N/A' if you do not wish to change:\n" ) if addrInput in ('N/A', 'n/a', 'NA', 'na'): addrInput = row[2] #Publisher and borrower need a phone number, not the library branch if tableName == 'tbl_publisher' or tableName == 'tbl_borrower': print("\nHere is the phone number:", row[3]) phoneInput = input( "\nWhat would you like to change it to?(Limit to 45 characters), Type 'N/A' if you do not wish to change:\n" ) if phoneInput in ('N/A', 'n/a', 'NA', 'na'): phoneInput = row[3] #Tailor update statement to match the table we're updating if tableName == 'tbl_publisher': stmt = "UPDATE tbl_publisher SET publisherName = %s, publisherAddress = %s, publisherPhone = %s WHERE publisherId = %s;" data = (nameInput, addrInput, phoneInput, pKey) elif tableName == 'tbl_library_branch': stmt = "UPDATE tbl_library_branch SET branchName = %s, branchAddress = %s WHERE branchId = %s;" data = (nameInput, addrInput, pKey) elif tableName == 'tbl_borrower': stmt = "UPDATE tbl_borrower SET name = %s, address = %s, phone = %s WHERE cardNo = %s;" data = (nameInput, addrInput, phoneInput, pKey) #Send SQL statements to the database try: connection = getConnection() cursor = connection.cursor() cursor.execute(stmt, data) connection.commit() except Exception as e: connection.rollback() raise e finally: connection.close() cursor.close() #If quit is chosen else: print("\nMoving to previous page...")
def overrideDueDate(): inp = -1 numBors = -1 #Unless the user wants to quit while inp != numBors + 1: #Get borrower data from database borrowers = getTableData('tbl_borrower') numBors = len(borrowers) cardNums = getIds('tbl_borrower') print(f"\nPlease select a borrower to override due date of:\n") #Display borrowers for i in range(0, numBors): print(f"{i+1}) {borrowers[i]}") print(f"{numBors+1}) To quit to previous menu") inp = input(f"\nPlease enter a number between 1 and {numBors+1}: ") if check.validInput(inp, 1, numBors + 1): #Grab specific borrower based on selection inp = int(inp) if inp != numBors + 1: borrower = borrowers[inp-1] cardNum = cardNums[inp-1] #Find borrower's loans in tbl_book_loans loanCount = 0 try: mycursor.execute("SELECT * FROM tbl_book_loans WHERE cardNo = %s", (cardNum,)) borLoans = mycursor.fetchall() loanCount = len(borLoans) except Exception as e: raise e #If there aren't any loans, then leave page if loanCount == 0: print("\nThis borrower has no outstanding loans.") print("\nMoving to previous page ...") return #Otherwise, show the user their loans else: print(f"\nOverriding due dates for {borrower[1]}.") loanInp = -1 while loanInp != loanCount + 1: print(f"\nPlease select a loan to override due date of:\n") #Will refresh the due dates after they are changed try: mycursor.execute("SELECT * FROM tbl_book_loans WHERE cardNo = %s", (cardNum,)) borLoans = mycursor.fetchall() except Exception as e: raise e #Print options to user for i in range(0, loanCount): print(f"{i+1}) {borLoans[i]}") print(f"{loanCount+1}) To quit to previous menu") loanInp = input(f"\nPlease enter a number between 1 and {loanCount+1}: ") if check.validInput(loanInp, 1, loanCount + 1): #Grab specific row based on selection loanInp = int(loanInp) if loanInp != loanCount + 1: print(f"borLoans:{borLoans}") loan = borLoans[loanInp-1] #Show user current due date #**Maybe clean up how the date is displayed print(f"\nThis borrower has a due date of: {loan[4]}") #User inputs date or quits newDate = input("Please input new date (yyyy-mm-dd), enter 'quit' if no change:\n") print(f"\nnewDate:{newDate}\n") print(f"\nloan:{loan}\n") if newDate in ('quit', 'QUIT', 'Quit', 'Q', 'q'): print("\nMoving to previous menu ...") else: #**Could use a decent date format validation try: mycursor.execute("UPDATE tbl_book_loans SET dueDate = %s WHERE bookId = %s AND branchId = %s AND cardNo = %s;", (newDate, loan[0], loan[1], loan[2])) mydb.commit() except Exception as e: mydb.rollback() raise e else: print("\nMoving to previous page ...") else: print("Moving to previous page ...")
def update(tableName): inp = -1 numRows = -1 #List rows in table while inp != numRows + 1: #Grab table data, primary keys, and count the tuples for the entire table #Should refresh after every update table = getTableData(tableName) tIds = getIds(tableName) numRows = len(table) #Have user select row tuple to update print(f"\nPlease select a row in {tableName} to update:\n") for i in range(0, numRows): print(f"{i+1}) {table[i]}") print(f"{numRows+1}) To quit to previous menu") inp = input(f"\nPlease enter a number between 1 and {numRows+1}: ") if check.validInput(inp, 1, numRows + 1): #Grab specific row based on selection inp = int(inp) if inp != numRows + 1: #Based on selection, grab data tuple, and it's primary key, cursors #every id, the users, for the key, genrates a table key, pass tabel name to getID, and returns a list of id to whatever those tables are #genarates for the selection, a list of a truble where the data would come , based on your selection, i'm going to find your id. #will go ahead and assign the id based on selection, book, publisher, branches, same order that they come in the database, they arlready #what data, user is given a row to update, and in the back round havea list of ros and ids in the same order, same order that they apear in the user. #substrting the -1 gives us the postion, for us is + 1, and for the user is -1, they are picking bettween 0-1, 1-4 row = table[inp - 1] pKey = tIds[inp - 1] #Gonna have to do specific update based on table #But all tables using this function have name and address print("\nHere is the current name:", row[1]) nameInput = input( "\nWhat would you like to change it to?(Limit to 45 characters), Type 'N/A' if you do not wish to change:\n" ) if nameInput in ('N/A', 'n/a', 'NA', 'na'): nameInput = row[1] #row name print("\nHere is the current address:", row[2]) addrInput = input( "\nWhat would you like to change it to?(Limit to 45 characters), Type 'N/A' if you do not wish to change:\n" ) if addrInput in ('N/A', 'n/a', 'NA', 'na'): addrInput = row[2] #row 2 is addresses #Publisher and borrower need a phone number, not the library branch if tableName == 'tbl_publisher' or tableName == 'tbl_borrower': print("\nHere is the phone number:", row[3]) phoneInput = input( "\nWhat would you like to change it to?(Limit to 45 characters), Type 'N/A' if you do not wish to change:\n" ) if phoneInput in ('N/A', 'n/a', 'NA', 'na'): phoneInput = row[3] #Tailor update statement to match the table we're updating if tableName == 'tbl_publisher': stmt = f"UPDATE tbl_publisher SET publisherName = '{nameInput}', publisherAddress = '{addrInput}', publisherPhone = '{phoneInput}' WHERE publisherId = {pKey};" elif tableName == 'tbl_library_branch': stmt = f"UPDATE tbl_library_branch SET branchName = '{nameInput}', branchAddress = '{addrInput}' WHERE branchId = {pKey};" elif tableName == 'tbl_borrower': stmt = f"UPDATE tbl_borrower SET name = '{nameInput}', address = '{addrInput}', phone = '{phoneInput}' WHERE cardNo = {pKey};" #Send SQL statements to the database try: mycursor.execute(stmt) mydb.commit() except Exception as e: mydb.rollback() raise e #If quit is chosen else: print("\nMoving to previous page...")