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...")
