def display_all_members1(): arz = setu.cursor() arz.execute( "SELECT member_type, member_id, full_name, mobile_number, email_id FROM members" ) allMembers = arz.fetchall() return allMembers
def issue_book(mem_name, book_acn): issue = setu.cursor() print("@issue book:") print(book_acn) print() status = is_book_available(book_acn) print(status) if status: mem_id = get_memid(mem_name) print(mem_id) if mem_id == -1: print('member dont exists') return -1 # wrong member id else: query = "insert into borrows (issue_date, issue_time, issuers_member_id, book_accession_number) values(CURDATE(), CURTIME(), %s, %s)" query2 = "update books set is_available = 0 where accession_number=\'" + book_acn + "\';" val = (mem_id, book_acn) #exceptions issue.execute(query, val) setu.commit() issue.execute(query2) setu.commit() print("Book issued") else: return -2
def return_book(mem_id, acn, return_date, remark): tray = setu.cursor() #validation of parameters mem_status = does_member_exist(mem_id) book_issued = is_book_issued(acn) query = "UPDATE borrows SET return_date = %s, remarks = %s where issuers_id = %s and accession_number = %s;" values = (return_date, remark, mem_id, acn) query1 = "UPDATE books SET is_available = 1 where accession_number = \'" + acn + "\'" if mem_status and book_issued: try: tray.execute(query, values) tray.execute(query1) except ms.IntegrityError as err: print("Intergrity Error: " + str(err)) except ms.Error as err: print("Other Error: " + str(err)) if (tray.rowcount == 1): setu.commit() print(str(tray.rowcount) + " Book Returned") return True elif (tray.rowcount == -1): print("Error ala re") else: print("Jast books returned zhale wattat " + str(tray.rowcount)) else: if mem_status == False: return -1 elif book_issued == False: return -2
def add_new_member(m_type, m_mno, email_id, m_fullname): add = setu.cursor() query = "INSERT into members(member_type,mobile_number,email_id,full_name) values(%s,%s,%s,%s)" val = (m_type, m_mno, email_id, m_fullname) add.execute(query, val) setu.commit() print(add.rowcount, " Member record inserted")
def search_book(searchby, searchfor): arz = setu.cursor() query = "SELECT accession_number, title, author, shelf_no, publisher, price from books where " + searchby + " like \'" + searchfor + "%\';" print(query) arz.execute(query) book = arz.fetchall() if book == []: return -1 else: return book
def get_memid(mem_name): id = setu.cursor() query = "SELECT member_id from members where full_name=\'" + mem_name + "\';" id.execute(query) mem_id = id.fetchall() if mem_id == []: return -1 else: return mem_id[0][0]
def return_book(mem_id, book_acn, remarks=""): wapas = setu.cursor() query = "update borrows set return_date = CURDATE(), return_time=CURTIME(), remarks=%s where issuers_member_id=%s and book_accession_number=%s;" val = (remarks, mem_id, book_acn) wapas.execute(query, val) setu.commit() print(wapas) print(wapas.fetchall) print("Book returned")
def getAllUnreturnedBooks(): arz = setu.cursor() query = "select borrows.book_accession_number, books.title, members.full_name, borrows.issue_date, members.mobile_number from((borrows inner join members on borrows.issuers_member_id = members.member_id and return_date is null) inner join books on borrows.book_accession_number = books.accession_number) order by borrows.issue_date desc;" print(query) arz.execute(query) books = arz.fetchall() print(books) if books == []: return -1 else: return books
def is_book_available(book_acn): status = setu.cursor() print(book_acn) query = "SELECT is_available from books where accession_number =\'" + book_acn + "\';" print(query) #try status.execute(query) state = status.fetchall() print(state) return state[0][0]
def list_all_books(): list = setu.cursor() query = "SELECT * from books" try: list.execute(query) except db.Error as err: print("\n@ list_all_books -> exception") books = list.fetchall() return books
def is_book_available(book_acn): status = setu.cursor() query = "Select return_date from borrows where book_accession_number = \'"+book_acn+"\';" status.execute(query) temp = status.fetchall() if temp == []: return 0; else: return 1;
def add_new_member(mtype, mmno, emailid, fname): add = setu.cursor() query = "insert into members(member_type, mobile_number ,email_id, full_name ) values(%s,%s,%s,%s)" val = (mtype.upper(), mmno, emailid, fname.upper()) try: add.execute(query, val) setu.commit() except ms.IntegrityError as err: return err print(add.rowcount, " Members added into Database") return 1
def getAllMembers(): yadi = setu.cursor() query = "SELECT member_id, full_name, mobile_number, email_id, member_type FROM members" try: yadi.execute(query) except db.Error as err: print(err) return -1 members = yadi.fetchall() #print(members) return members
def add_new_member(m_type, m_mno, email_id, m_fullname): add = setu.cursor() query = "INSERT into members(member_type,mobile_number,email_id,full_name) values(%s,%s,%s,%s)" val = (m_type, m_mno, email_id, m_fullname) try: add.execute(query, val) setu.commit() except ms.IntegrityError as er: return er print(add.rowcount, " Member record inserted") return 1
def add_new_book(acn, title, price, author, publisher): add = setu.cursor() query = "INSERT INTO books(accession_number, title, price, author, publisher) values(%s,%s,%s,%s,%s)" val = (acn, title, price, author, publisher) try: add.execute(query, val) except ms.IntegrityError as err: err_msg = "Error: {}".format(err) return err_msg setu.commit() print(str(add.rowcount) + " Book added Successfully") return 0
def search_member_by_name1(m_name): arz = setu.cursor() query = "SELECT member_type, member_id, full_name, mobile_number, email_id FROM members where full_name like\'" + str( m_name) + "%\'" print(query) arz.execute(query) searchMember = arz.fetchall() print(searchMember) if searchMember == []: return -1 else: return searchMember
def search_member_by_name(m_name): arz = setu.cursor() query = "SELECT * FROM members where full_name like\'" + str( m_name) + "%\'" print(query) arz.execute(query) searchMember = arz.fetchall() print(searchMember) if searchMember == []: return -1 else: return searchMember
def is_book_issued(acn): check = setu.cursor() query = "SELECT * from borrows where accession_number = \'" + acn + "\' and return_date is NULL;" try: check.execute(query) except db.Error as err: print(str(err)) book_count = len(check.fetchall()) if book_count > 1 or book_count < 0: return False else: return book_count
def does_member_exist(mid): shodh = setu.cursor() query = "select * from members where member_id = " + str(mid) try: shodh.execute(query) except db.Error as err: print(err) #print(shodh.statement) results = shodh.fetchall() if len(results) == 1: return True else: return False
def getMemberNameFromID(mem_id): shodh = setu.cursor() query = "SELECT full_name from members where member_id = " + str(mem_id) try: shodh.execute(query) except ms.Error as err: print(err) return -1 mname = shodh.fetchall() if len(mname) == 0: return False else: return str(mname[0][0])
def is_book_registered(acn): search = setu.cursor() query = "select * from books where accession_number = \'" + str(acn) + "\'" try: search.execute(query) except db.Error as err: print(err + " \n@ is_book_registered -> exception ") temp = search.fetchall() if search.rowcount > 0: return True elif search.rowcount == 0: return False
def getUnreturnedBooks(searchBy, searchFor): arz = setu.cursor() query = """select borrows.book_accession_number, books.title, members.full_name, borrows.issue_date, members.mobile_number from((borrows inner join members on borrows.issuers_member_id = members.member_id and return_date is null) inner join books on borrows.book_accession_number = books.accession_number) where """ + searchBy + """ like \'""" + searchFor + """%\' order by borrows.issue_date desc ;""" print(query) arz.execute(query) books = arz.fetchall() print(books) if books == []: return -1 else: return books
def searchMember(searchBy, searchFor): shodh = setu.cursor() query = "SELECT member_id, full_name, mobile_number, email_id, member_type FROM members where " + searchBy + " like \'" + searchFor + "%\'" try: shodh.execute(query) except db.Error as err: print(err) return -1 members = shodh.fetchall() if len(members) == 0: return False else: return members
def search_book(searchby, searchfor): arz = setu.cursor() query = "SELECT accession_number, title, shelf_no, price, authors, publisher, is_available from books where " + searchby + " like \'" + str( searchfor) + "%\';" try: arz.execute(query) except db.error as err: print("\n@ list_all_books -> exception") books = arz.fetchall() if books == []: return -1 else: return books
def getBookName(acn): shodh = setu.cursor() query = "SELECT title from books where accession_number = \'" + str( acn) + "\'" try: shodh.execute(query) except db.Error as err: print(err) return -1 bname = shodh.fetchall() if len(bname) == 0: print("book not found") return False else: return str(bname[0][0])
def delete_member_by_id(mid): nsht = setu.cursor() query = "delete from members where member_id = " + str(mid) + ";" try: nsht.execute(query) setu.commit() except db.Error as err: print(err) print(str(nsht.rowcount) + " member deleted Successfully") if nsht.rowcount == 0: return -1 elif nsht.rowcount == 1: return 1 else: return nsht.rowcount
def insert_new_book(acn, title, shelf_no, price, author, publisher): add = setu.cursor() query = "INSERT INTO books(accession_number, title, shelf_no, price, authors, publisher, is_available) values(%s,%s,%s,%s,%s,%s,%s)" val = (acn, title, shelf_no, price, author, publisher, '1') try: add.execute(query, val) except db.Error as err: print(format(err)) return err except db.IntegrityError as err: err = "Error: {}".format(err) return err setu.commit() print(str(add.rowcount) + " Book added Successfully") return True
def delete_book(acn): delete = setu.cursor() query = "delete from books where accession_number = \'" + str(acn) + "\'" try: delete.execute(query) except db.Error as err: print(err) return err setu.commit() print(str(delete.rowcount) + " Books Deleted Successfully") if delete.rowcount == 0: return -1 elif delete.rowcount == 1: return 1 else: return delete.rowcount
def issue_book(mem_id, acn, issue_date): tray = setu.cursor() #validation of parameters mem_status = does_member_exist(mem_id) # book_status = is_book_available( acn ) # if returns -1 pop error " wrong accession no." (book exists check is implicit) if mem_status and book_status: #print(acn + " " + mem_id) #print(issue_date) query = "INSERT into borrows(issue_date, issuers_id, accession_number) values(%s, %s, %s)" query1 = "UPDATE books set is_available = 0 where accession_number=\'" + str( acn) + "\'" values = (issue_date, mem_id, acn) try: tray.execute(query, values) tray.execute(query1) except ms.IntegrityError as err: print("Intergrity Error: " + str(err)) except ms.Error as err: print("Other Error: " + str(err)) print(str(tray.statement)) if (tray.rowcount == 1): setu.commit() print(str(tray.rowcount) + " Book issues") return True elif (tray.rowcount == -1): print("Error ala re") return False else: print("Jast books issue zhale wattat " + str(tray.rowcount)) return False else: if mem_status == False: return -1 elif book_status == False: return -2
def getUnreturnedBooks(searchBy, searchFor): pustak = setu.cursor() query = """select borrows.accession_number, books.title, members.full_name, borrows.issue_date, members.mobile_number, members.member_id from((borrows inner join members on borrows.issuers_id = members.member_id and return_date is NULL) inner join books on borrows.accession_number = books.accession_number ) where """ + searchBy + """ like \'""" + searchFor + """%\' order by borrows.issue_date desc ;""" try: pustak.execute(query) except ms.Error as err: print(err) return -1 books = pustak.fetchall() if len(books) == 0: return False else: return books