class BookConditions(Resource): def __init__(self): self.connection = Connect( 'ora-scsp.srv.mst.edu', 1521, os.environ['DBEAVER_USERNAME'], os.environ['DBEAVER_PASSWORD'], 'scsp.mst.edu' ) self.connection.establish_connection() def get(self): order_by = ' ORDER BY BOOK_CONDITION.JACKET_CONDITION ' req = { 'jacket_condition':request.args.get("jacket_condition"), } where_cmds = [] if req['jacket_condition'] != None: where_cmds.append(' BOOK_CONDITION.TITLE = \'{}\' '.format(req['title'])) where_cmds.append(' BOOK_CONDITION.AUTHOR = \'{}\' '.format(req['author'])) where_cmds.append(' BOOK_CONDITION.EDTION = \'{}\' '.format(req['edtion'])) where_cmds.append(' BOOK_CONDITION.JACKET_CONDITION = \'{}\' '.format(req['jacket_condition'])) command = 'SELECT TITLE, AUTHOR, EDITION, JACKET_CONDITION FROM GCWZF4.BOOK_CONDITION {} {} {}'.format('WHERE' if len(where_cmds)>0 else '','AND'.join(where_cmds), order_by) data = self.connection.get_query_data( command ) jacket_conditions = [] return_val = [] return_code = 200 if data != []: for row in data: jacket_conditions.append({ "title":row[0], "author":row[1], "edition":row[2], "jacket_condition":row[3], }) return_val = jacket_conditions else: return_val = 'ERROR' return_code = 500 self.connection.close_connection() return return_val, return_code def post(self, name): pass def put(self, name): pass def delete(self, name): pass
class Authors(Resource): def __init__(self): self.connection = Connect( 'ora-scsp.srv.mst.edu', 1521, os.environ['DBEAVER_USERNAME'], os.environ['DBEAVER_PASSWORD'], 'scsp.mst.edu' ) self.connection.establish_connection() def get(self): order_by = ' ORDER BY AUTHORS.AUTHOR ' req = { 'author':request.args.get("author"), } where_cmds = [] if req['author'] != None: where_cmds.append(' AUTHORS.AUTHOR = \'{}\' '.format(req['author'])) command = 'SELECT * FROM GCWZF4.AUTHORS {} {} {}'.format('WHERE' if len(where_cmds)>0 else '','AND'.join(where_cmds), order_by) data = self.connection.get_query_data( command ) authors = [] return_val = [] return_code = 200 if data != []: for row in data: authors.append({ "author":row[0], "image_path":row[1]}) return_val = authors else: return_val = 'ERROR' return_code = 500 self.connection.close_connection() return return_val, return_code def post(self, name): pass def put(self, name): pass def delete(self, name): pass
class JacketConditions(Resource): def __init__(self): self.connection = Connect('ora-scsp.srv.mst.edu', 1521, os.environ['DBEAVER_USERNAME'], os.environ['DBEAVER_PASSWORD'], 'scsp.mst.edu') self.connection.establish_connection() def get(self): order_by = ' ORDER BY JACKET_CONDITIONS.JACKET_CONDITION ' req = { 'jacket_condtion': request.args.get("jacket_condtion"), } where_cmds = [] if req['jacket_condtion'] != None: where_cmds.append( ' JACKET_CONDITIONS.JACKET_CONDITION = \'{}\' '.format( req['jacket_condtion'])) command = 'SELECT * FROM GCWZF4.JACKET_CONDITIONS {} {} {}'.format( 'WHERE' if len(where_cmds) > 0 else '', 'AND'.join(where_cmds), order_by) data = self.connection.get_query_data(command) jacket_conditions = [] return_val = [] return_code = 200 if data != []: for row in data: jacket_conditions.append({"jacket_conditions": row[0]}) return_val = jacket_conditions else: return_val = 'ERROR' return_code = 500 self.connection.close_connection() return return_val, return_code def post(self, name): pass def put(self, name): pass def delete(self, name): pass
class Grades(Resource): def __init__(self): self.connection = Connect( 'ora-scsp.srv.mst.edu', 1521, os.environ['DBEAVER_USERNAME'], os.environ['DBEAVER_PASSWORD'], 'scsp.mst.edu' ) self.connection.establish_connection() def get(self): order_by = ' ORDER BY GRADE.BOOK_GRADE ' req = { 'grade':request.args.get("grade"), } where_cmds = [] if req['grade'] != None: where_cmds.append(' GRADE.BOOK_GRADE = \'{}\' '.format(req['grade'])) command = 'SELECT * FROM GCWZF4.GRADE {} {} {}'.format('WHERE' if len(where_cmds)>0 else '','AND'.join(where_cmds), order_by) data = self.connection.get_query_data( command ) grades = [] return_val = [] return_code = 200 if data != []: for row in data: grades.append({"grade":row[0]}) return_val = grades else: return_val = 'ERROR' return_code = 500 self.connection.close_connection() return return_val, return_code def post(self, name): pass def put(self, name): pass def delete(self, name): pass
class AuthorBooks(Resource): def __init__(self): self.connection = Connect('ora-scsp.srv.mst.edu', 1521, os.environ['DBEAVER_USERNAME'], os.environ['DBEAVER_PASSWORD'], 'scsp.mst.edu') self.connection.establish_connection() def get(self): order_by = ' ORDER BY BOOK_EDITIONS.PUBLISH_YEAR, BOOK_EDITIONS.TITLE ' req = { 'author': request.args.get("author"), } where_cmds = [] if req['author'] != None: where_cmds.append(' BOOK_EDITIONS.AUTHOR = \'{}\' '.format( req['author'])) command = 'SELECT AUTHORS.AUTHOR, AUTHORS.IMAGE_PATH, TITLE, PUBLISH_YEAR FROM GCWZF4.BOOK_EDITIONS JOIN GCWZF4.AUTHORS ON AUTHORS.AUTHOR = BOOK_EDITIONS.AUTHOR {} {} {}'.format( 'WHERE' if len(where_cmds) > 0 else '', 'AND'.join(where_cmds), order_by) print(command) data = self.connection.get_query_data(command) authors = {} return_val = [] return_code = 200 if data != []: authors['author'] = data[0][0] authors['image_path'] = data[0][1] authors['books'] = [] for row in data: authors['books'].append({ "title": row[2], "publish_year": row[3] }) return_val = authors else: return_val = 'ERROR' return_code = 500 self.connection.close_connection() return return_val, return_code
class Years(Resource): def __init__(self): self.connection = Connect('ora-scsp.srv.mst.edu', 1521, os.environ['DBEAVER_USERNAME'], os.environ['DBEAVER_PASSWORD'], 'scsp.mst.edu') self.connection.establish_connection() def get(self): order_by = ' ORDER BY BOOK_EDITIONS.PUBLISH_YEAR, BOOK_EDITIONS.TITLE ' req = { 'author': request.args.get("author"), } where_cmds = [] if req['author'] != None: where_cmds.append(' BOOK_EDITIONS.AUTHOR = \'{}\' '.format( req['author'])) command = 'SELECT BOOK_EDITIONS.PUBLISH_YEAR, BOOK_EDITIONS.TITLE FROM GCWZF4.BOOK_EDITIONS {}'.format( order_by) data = self.connection.get_query_data(command) years = {} years['publish_year'] = {} return_val = [] return_code = 200 if data != []: for row in data: print(row[0]) if row[0] not in years['publish_year']: years['publish_year'][row[0]] = [row[1]] else: years['publish_year'][row[0]].append(row[1]) return_val = years else: return_val = 'ERROR' return_code = 500 self.connection.close_connection() return return_val, return_code
class AddBook(Resource): def __init__(self): self.connection = Connect( 'ora-scsp.srv.mst.edu', 1521, os.environ['DBEAVER_USERNAME'], os.environ['DBEAVER_PASSWORD'], 'scsp.mst.edu' ) self.connection.establish_connection() def post(self): req = { 'author':request.args.get("author"), 'title':request.args.get("title"), 'edition':request.args.get("edition"), 'img_path':request.args.get("img_path"), 'gutenberg_path':request.args.get("gutenberg_path"), 'publisher':request.args.get("publisher"), 'notes':request.args.get("notes"), 'isbn':request.args.get("isbn"), 'publish_year':request.args.get("publish_year"), 'pages':request.args.get("pages"), 'book_grade':request.args.get('book_grade'), 'binding_type':request.args.get("binding_type"), 'jacket_condition':request.args.get("jacket_condition") } if req['author'] == None or \ req['title'] == None or \ req['edition'] == None: return_val = 'ERROR: author or title or edition is missing and must be present' return_code = 400 else: authors = [] jacket_conditions = [] book_grades = [] publishers = [] book_editions = [] # check if author is in the database if not we'll add the author command = 'SELECT AUTHOR FROM GCWZF4.AUTHORS WHERE AUTHOR = \'{}\''.format(req['author']) authors = self.connection.get_query_data(command) if authors == []: columns = ','.join([ x for x in [ 'AUTHOR', 'IMAGE_PATH' if req['img_path'] != None else 'NULL' ] if x != 'NULL' ]) values = '\',\''.join([ x for x in [ req['author'], req['img_path'] if req['img_path'] != None else 'NULL' ] if x != 'NULL' ]) command = 'INSERT INTO GCWZF4.AUTHORS({}) \nVALUES (\'{}\')'.format(columns,values) debugger.log(command) self.connection.run_command(command) # check if book_edition is in the database if not we'll add the book_edition command = 'SELECT TITLE, EDITION, AUTHOR FROM GCWZF4.BOOK_EDITIONS WHERE TITLE = \'{}\' AND AUTHOR = \'{}\' AND EDITION = \'{}\''.format( req['title'], req['author'], req['edition'] ) book_editions = self.connection.get_query_data(command) if book_editions == []: columns = ','.join([x for x in [ 'TITLE', 'EDITION', 'AUTHOR', 'ISBN' if req['isbn'] != None else 'NULL', 'PAGES' if req['pages'] != None else 'NULL', 'PUBLISH_YEAR' if req['publish_year'] != None else 'NULL', 'NOTES' if req['notes'] != None else 'NULL', 'GUTENBERG_PATH' if req['gutenberg_path'] != None else 'NULL'] if x != 'NULL']) values = '\',\''.join([x for x in [ req['title'], req['edition'], req['author'], req['isbn'] if req['isbn'] != None else 'NULL', req['pages'] if req['pages'] != None else 'NULL', req['publish_year'] if req['publish_year'] != None else 'NULL', req['notes'] if req['notes'] != None else 'NULL', req['gutenberg_path'] if req['gutenberg_path'] != None else 'NULL'] if x != 'NULL']) command = 'INSERT INTO GCWZF4.BOOK_EDITIONS({}) \nVALUES (\'{}\')'.format(columns, values) debugger.log(command) self.connection.run_command(command) if req['jacket_condition'] != None: # check if jacket_condition is in the database if not we'll add the jacket_condition command = 'SELECT JACKET_CONDITION FROM GCWZF4.JACKET_CONDITIONS WHERE JACKET_CONDITION = \'{}\''.format(req['jacket_condition']) jacket_conditions = self.connection.get_query_data(command) # debugger.log(command) if jacket_conditions == []: command = 'INSERT INTO GCWZF4.JACKET_CONDITIONS(JACKET_CONDITION) \nVALUES (\'{}\')'.format(req['jacket_condition']) debugger.log(command) self.connection.run_command(command) command = 'INSERT INTO GCWZF4.BOOK_CONDITION(TITLE, AUTHOR, EDITION, JACKET_CONDITION) \nVALUES (\'{}\')'.format('\',\''.join([req['title'],req['author'],req['edition'],req['jacket_condition']])) debugger.log(command) self.connection.run_command(command) if req['book_grade'] != None: # check if book_grade is in the database if not we'll add the book_grade command = 'SELECT BOOK_GRADE FROM GCWZF4.GRADE WHERE BOOK_GRADE = \'{}\''.format(req['book_grade']) book_grades = self.connection.get_query_data(command) # debugger.log(command) if book_grades == []: command = 'INSERT INTO GCWZF4.GRADE(BOOK_GRADE) \nVALUES (\'{}\')'.format(req['book_grade']) debugger.log(command) self.connection.run_command(command) command = 'INSERT INTO GCWZF4.BOOK_GRADE(TITLE, AUTHOR, EDITION, BOOK_GRADE) \nVALUES (\'{}\')'.format('\',\''.join([req['title'],req['author'],req['edition'],req['book_grade']])) debugger.log(command) self.connection.run_command(command) if req['publisher'] != None: # check if publisher is in the database if not we'll add the publisher command = 'SELECT PUBLISHER FROM GCWZF4.PUBLISHERS WHERE PUBLISHER = \'{}\''.format(req['publisher']) publishers = self.connection.get_query_data(command) if publishers == []: command = 'INSERT INTO GCWZF4.PUBLISHERS(PUBLISHER) \nVALUES (\'{}\')'.format(req['publisher']) # debugger.log(command) self.connection.run_command(command) command = 'INSERT INTO GCWZF4.PUBLISHER_BOOKS(TITLE, AUTHOR, EDITION, PUBLISHER) \nVALUES (\'{}\')'.format('\',\''.join([req['title'],req['author'],req['edition'],req['publisher']])) # debugger.log(command) self.connection.run_command(command) if req['binding_type'] != None: # check if binding_type is in the database if not we'll add the binding_type command = 'SELECT BINDING_TYPE FROM GCWZF4.BINDING_TYPES WHERE BINDING_TYPE = \'{}\''.format(req['binding_type']) binding_types = self.connection.get_query_data(command) if binding_types == []: command = 'INSERT INTO GCWZF4.BINDING_TYPES(BINDING_TYPE) \nVALUES (\'{}\')'.format(req['binding_type']) # debugger.log(command) self.connection.run_command(command) command = 'INSERT INTO GCWZF4.BOOK_BINDING_TYPES(TITLE, AUTHOR, EDITION, BINDING_TYPE) \nVALUES (\'{}\')'.format('\',\''.join([req['title'],req['author'],req['edition'],req['binding_type']])) # debugger.log(command) self.connection.run_command(command) return_val = 'Success' return_code = 200 self.connection.close_connection() return return_val, return_code
class BookEditions(Resource): def __init__(self): self.connection = Connect( 'ora-scsp.srv.mst.edu', 1521, os.environ['DBEAVER_USERNAME'], os.environ['DBEAVER_PASSWORD'], 'scsp.mst.edu' ) self.connection.establish_connection() def get(self): order_by = ' ORDER BY AUTHORS.AUTHOR ' req = { 'author':request.args.get("author"), 'edition':request.args.get("edition"), 'title':request.args.get("title"), } where_cmds = [] if req['author'] != None: where_cmds.append(' AUTHORS.AUTHOR = \'{}\' '.format(req['author'])) if req['edition'] != None: where_cmds.append(' EDITION = \'{}\' '.format(req['edition'])) if req['title'] != None: where_cmds.append(' TITLE = \'{}\' '.format(req['title'])) command = 'SELECT TITLE, EDITION, AUTHORS.AUTHOR, PUBLISH_YEAR, IMAGE_PATH FROM GCWZF4.BOOK_EDITIONS JOIN GCWZF4.AUTHORS ON AUTHORS.AUTHOR = BOOK_EDITIONS.AUTHOR {} {} {}'.format('WHERE' if len(where_cmds)>0 else '','AND'.join(where_cmds), order_by) data = self.connection.get_query_data( command ) return_val = [] return_code = 200 if data != []: books = [] i = 0 for row in data: if row[4] and ((row[2].split(' ')[0] not in row[4]) or (len(row[2].split(' ')) > 2 and row[2].split(' ')[1] not in row[4])): books.append({ "id": i, "title":row[0], "edition":row[1], "author": row[2], "year": row[3], "image_path": 'null' }) else: books.append({ "id": i, "title":row[0], "edition":row[1], "author": row[2], "year": row[3], "image_path": row[4] }) i+=1 return_val = books else: return_val = 'ERROR' return_code = 500 self.connection.close_connection() return return_val, return_code def post(self, name): pass def put(self, name): pass def delete(self, name): pass