def lookup_book_author(bookID, authorID): cursor = dbc.get_db_cursor() args = (bookID, authorID) cursor.callproc('find_book_author', args) for result in cursor.stored_results(): book_authors = result.fetchall() return book_authors[0][0]
def insert_book_author(bookID, authorID): cursor = dbc.get_db_cursor() args = (bookID, authorID) try: cursor.callproc('insert_book_author', args) dbc.commit_db() except mysql.connector.IntegrityError as err: print('This author is already associated with this book!')
def get_noOfCopies(bookId, branchId): libDB_cursor= dbc.get_db_cursor() libDB_cursor.callproc('display_book_copies', (bookId, branchId)) for books in libDB_cursor.stored_results():#This loop loops only 1 because of stored_results book_copies=books.fetchone() # book_copies = libDB_cursor.execute(query, (bookId, branchId))#even a single param value needs to be in a tuple libDB_cursor.close() return book_copies
def get_all_books(): libDB_cursor = dbc.get_db_cursor() libDB_cursor.callproc('find_books_by_authors') book_list=None for books in libDB_cursor.stored_results():#This loop loops only 1 because of stored_results book_list=books.fetchall()#fetchall returns a list of results libDB_cursor.close() return book_list
def get_all_branches(): """Simple query to get the information of all branches in DB""" query = "SELECT * FROM tbl_library_branch tbl" libDB_cursor = dbc.get_db_cursor() libDB_cursor.execute(query) branch_list = [] for branch in libDB_cursor: branch_list.append(branch) libDB_cursor.close() return branch_list
def find_books_available_for_return(cardNo, branchId): libDB_cursor = dbc.get_db_cursor() libDB_cursor.callproc('find_books_available_for_return', (cardNo, branchId)) book_list = None for books in libDB_cursor.stored_results( ): #This loop loops only 1 because of stored_results book_list = books.fetchall() #fetchall returns a list of results libDB_cursor.close() return book_list
def display_authors(): cursor = dbc.get_db_cursor() cursor.callproc('display_authors') for result in cursor.stored_results(): authors = result.fetchall() count = 1 for author in authors: print(count, end=') ') print('Name:', author[1]) count += 1 return authors
def display_publishers(): cursor = dbc.get_db_cursor() cursor.callproc('display_publishers') for result in cursor.stored_results(): publishers = result.fetchall() count = 1 for publisher in publishers: print(count, end=') ') print('Name:', publisher[1]) count += 1 return publishers
def display_books(): cursor = dbc.get_db_cursor() cursor.callproc('display_books') for result in cursor.stored_results(): books = result.fetchall() count = 1 for book in books: print(count, end=') ') print('Name:', book[1]) count += 1 return books
def lookup_authors_of_book(bookid): cursor = dbc.get_db_cursor() args = (bookid, ) cursor.callproc('display_authors_of_book', args) for result in cursor.stored_results(): book_Authors = result.fetchall() count = 1 for authors in book_Authors: print(str(count) + '.', authors[0]) count += 1 return book_Authors
def get_card_matches(cardNo): """Searches DB for the passed in card number. As tbl_borrower.cardNo is a is a PK there should be at most one value in the result set""" query = "SELECT tb.cardNo FROM tbl_borrower tb WHERE tb.cardNo=%s" libDB_cursor = dbc.get_db_cursor() libDB_cursor.execute( query, (cardNo, )) #even a single param value needs to be in a tuple result = libDB_cursor.fetchone() #closing connection libDB_cursor.close() return result
def display_branches(): cursor = dbc.get_db_cursor() cursor.callproc('display_branches') for result in cursor.stored_results(): branches = result.fetchall() count = 1 for branch in branches: print(count, end=') ') print('Name:', branch[1], end=' ') print('Address:', branch[2]) count += 1 return branches
def display_borrowers(): cursor = dbc.get_db_cursor() cursor.callproc('display_borrowers') for result in cursor.stored_results(): borrowers = result.fetchall() count = 1 for borrower in borrowers: print(count, end=') ') print('CardNum:', borrower[0], end=' ') print('Name:', borrower[1], end=' ') print('Address:', borrower[2], end=' ') print('Phone:', borrower[3]) count += 1 return borrowers
def display_borrower_books(cardNo): cursor = dbc.get_db_cursor() args = (cardNo, ) result = cursor.callproc('borrower_book_loans', args) for result in cursor.stored_results(): book_loans = result.fetchall() count = 1 for book_loan in book_loans: bkname = cursor.callproc('find_book_name', (book_loan[0], 0)) print(count, end=') ') print(str(bkname[1]), 'DueDate: ', book_loan[4]) count += 1 return book_loans
def proc(proc, args): cursor = dbc.get_db_cursor() cursor.callproc(proc, args) dbc.commit_db()
def return_book(query_tuple): libDB_cursor = dbc.get_db_cursor() libDB_cursor.callproc('return_book', query_tuple) dbc.commit_db() libDB_cursor.close()
def checkout_book(query_tuple): libDB_cursor = dbc.get_db_cursor() loan_info = libDB_cursor.callproc('checkout_book', query_tuple) dbc.commit_db() libDB_cursor.close() return loan_info
def lookup_due_date(cardNo, bookId, branchId): cursor = dbc.get_db_cursor() args = [cardNo, bookId, branchId] result = cursor.callproc('lookup_due_date', args) return result[0]
def edit_branch(branchId, branchName, branchAddress): libDB_cursor = dbc.get_db_cursor() libDB_cursor.callproc('update_branch', (branchId, branchName, branchAddress)) dbc.commit_db() libDB_cursor.close()
def check(proc, args): cursor = dbc.get_db_cursor() results = cursor.callproc(proc, args) return results[1]
def lookup(proc, info): cursor = dbc.get_db_cursor() results = cursor.callproc(proc, info) return results[1]
def reset_database(): cursor = dbc.get_db_cursor() cursor.callproc('reset_database')
def insert_book_copies(bookId, branchId, noOfCopies): cursor = dbc.get_db_cursor() args = (bookId, branchId, noOfCopies) cursor.callproc('insert_book_copies', args) dbc.commit_db()
def insert_borrower(name, address, phone): cursor = dbc.get_db_cursor() args = (name, address, phone, 0) results = cursor.callproc('insert_borrower', args) dbc.commit_db() return results[3]