class Record: """Preside over a single record in the database including updating it""" def __init__(self, form_dict): """initialize variables and bring in column metadata""" self.book_id = form_dict["book_id"] self.activity = form_dict["activity"] self.connection = getDictConnection() self.columns = loadYaml("columns") self.author = Author() self.record_dict = dict.copy(form_dict) self.added_new_rec = False del self.record_dict["book_id"] del self.record_dict["activity"] if self.activity == "submit_new": start_sql = 'insert into book (title) values ("starter")' start = execute(self.connection, start_sql) find_sql = "select book_id from book where title like 'starter'" find = execute(self.connection, find_sql) self.book_id = find[0]["book_id"] self.activity = "update" self.added_new_rec = True def debug(self): return self.record_dict, self.processRecordDict() def updateRecord(self): """ given a dict of column value pairs figure out which should be updated update tables with new vals if necessary return two lists: changes made, values added""" book_items = {} update_dict = {} when_read_items = {} series_items = {} updated = {} added = {} removed = {} # sort and pre-process dict update_dict, author_items = self.processRecordDict() if update_dict: for column, value in update_dict.items(): record_table = "" # figure out which table needs to be updated, amend that dict{} record_table = self.columns[column][0]["from"] if record_table == "book": book_items.update({column: value}) if record_table == "when_read": when_read_items.update({column: value}) if record_table == "series": series_items.update({column: value}) if book_items: book_updates, book_adds = self.updateBook(book_items) updated.update(book_updates) added.update(book_adds) if series_items: series_updates, series_adds = self.updateSeries(series_items) updated.update(series_updates) added.update(series_adds) if when_read_items: when_updates, when_adds = self.updateWhenRead(when_read_items) updated.update(when_updates) added.update(when_adds) # send author_items out to be handled # author_updates = self.updateAuthor(author_items) # updated.update(author_updates) if self.added_new_rec: return self.book_id else: return updated, added def updateBook(self, book_items): updates = {} adds = {} if self.activity == "update": for item in book_items: sql = "update book set %s = %s where book.book_id = %s" % (item, book_items[item], self.book_id) results = execute(self.connection, sql) updates[item] = book_items[item] return updates, adds def updateSeries(self, series_items): series_id = "NULL" series = series_items["series"] updates = {} adds = {} # if the series recieved isn't blank, check if it is in the DB yet # if not add it and append message with that info # retrieve the series_id for the requested series if series != "''": searchSQL = "select series from series where series like %s" % series searchResults = execute(self.connection, searchSQL) if not searchResults: addSQL = "insert into series (series) values (%s)" % series addResults = execute(self.connection, addSQL) adds["series"] = series IdSQL = "select series_id from series where series like %s" % series IdResults = execute(self.connection, IdSQL) series_id = IdResults[0]["series_id"] # set the series_id in the book table to the correct series updateSQL = "update book set series_id = %s where book_id = %s" % (series_id, self.book_id) updateReults = execute(self.connection, updateSQL) updates["series"] = series return updates, adds def updateWhenRead(self, when_read_items): updates = {} adds = {} when = when_read_items["when_read"] if when != "": when = (datetime.strptime(when, "%m/%d/%Y")).date() when = when.isoformat() sql = "insert into when_read (when_read, book_id) values (%s, %s)" % (when, self.book_id) results = execute(self.connection, sql) adds["when_read"] = when return updates, adds def updateAuthor(self, author_items): firstLast = author_items.copy() fullNames = [] listofAuthors = [] authorIdDict = self.author.getAuthorIdDict() add = [] remove = [] update = {} # make a list of full names and a seperate dict for first and last name for item in author_items: if "full" in str(item): fullNames.append(author_items[item]) del firstLast[item] # If any item in firstLast has a value set newName to true newName = False for key, value in firstLast.items(): if value: newName = True # bring in the data currently in the DB authorsOnRecord = self.author.getAuthors(self.book_id, "concat") # make a list of full names in the DB for count in range(len(authorsOnRecord)): listofAuthors.append(authorsOnRecord[count]["name"]) # compare the list of full names currently in the DB to the # full names in author items to see what has to be added or removed for item in listofAuthors: if item not in fullNames: remove.append(item) for item in fullNames: if item not in listofAuthors: add.append(item) # check if the new first and last name is in the DB yet if newName: sql = """ select author_id from author where last_name like '%s' and first_name like '%s' """ % ( author_items["author_last_name"], author_items["author_first_name"], ) matches = execute(self.connection, sql) # if the name is not in the DB add it and refresh the authorIdDict if not matches: sql = """ insert into author (last_name, first_name) values ('%s', '%s') """ % ( author_items["author_last_name"], author_items["author_first_name"], ) results = execute(self.connection, sql) authorIdDict = self.author.getAuthorIdDict() # concat the first and last name add it to add list name = "%s, %s" % (author_items["author_last_name"], author_items["author_first_name"]) add.append(name) # add the book_id/author_id pairs to the book_author table for item in add: author_id = authorIdDict[item] sql = """insert into book_author (author_id, book_id) values (%s, %s)""" % ( author_id, self.book_id, ) results = execute(self.connection, sql) update[item] = "added to record" # remove the book_id/author_id pairs from the book_author table for item in remove: author_id = authorIdDict[item] sql = """delete from book_author where book_id = %s and author_id = %s """ % ( self.book_id, author_id, ) results = execute(self.connection, sql) update[item] = "removed from record" return update def processRecordDict(self): """given a dict of record items to update prepare them for DB insertion by: segregating author items from dict, calling selectDiffColumns, prepping the formats of the remaining values for insertion. Returns update_dict and author_items""" process_dict = dict.copy(self.record_dict) author_items = {} # find all keys with author in them, add those to author items for item in process_dict: if "author" in str(item): author_items[item] = process_dict[item] # remove all the stuff in author items from record dict for item in author_items: del process_dict[item] # if the acitvity is 'edit'call selectDiffCols on the remaining dict # if that returns any values format them for the DB and return them # otherwise all values need to be processed if self.activity == "update": update_dict = self.selectDiffColumns(process_dict) else: update_dict = process_dict if update_dict: for column, value in update_dict.items(): colType = self.columns[column][0]["type"] # prep dic vlaues for database update if colType == "string" or colType == "int": if value: update_dict[column] = "'" + value + "'" else: update_dict[column] = "'" + "'" return update_dict, author_items def selectDiffColumns(self, recievedData): """ given a dic of data recieve about a record compare it to the data in the DB remove entries in the recieved dictionary that already exist return truncated dict""" query = Query() where = "book.book_id =" + str(self.book_id) recordData = query.getData("edit", where) remove = [] for col, val in recordData[0].items(): if val == "": recordData[0][col] = None else: recordData[0][col] = str(val) for col in recievedData: if recievedData[col] == recordData[0][col]: remove.append(col) for col in remove: del recievedData[col] return recievedData