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]
