def upsert_characters(character: Dict, endpoint: str) -> None: """ Inserts values into `characters` table, updates on duplicate key. Args: character (dict): endpoint (str): Returns: """ connection = get_sql_db_connection() # retrieving keys and values from an OrderedDict into list so as to maintain relative order character = OrderedDict(character) char_id = get_url_ids([endpoint]) keys_ = [] values_ = [] for key_, val_ in character.items(): keys_.append(key_) if isinstance(val_, list): values_.append(get_url_ids(val_)) else: values_.append(val_) # data validation layer using pydantic model. If endpoint yields no result then return. try: if character is not OrderedDict([('detail', 'Not found')]): Character(**character) else: print(f"\n\n[ WARNING ] Endpoint - {endpoint} - yields nothing!!") return None except ValidationError as ve: print( f"[ Error ] fetched character record does not meet validations. Perhaps, type" f"conversions required. More details on error - {ve}") try: with connection.cursor() as cursor: sql = build_upsert_sql_query("starwarsDB.characters", "INSERT INTO", "char_id", char_id, "ON DUPLICATE KEY UPDATE", keys_, values_) # print(f"\n see here the SQL query :: \n\n{sql}") cursor.execute(sql) connection.commit() finally: connection.close()
def get_people_film_mapping() -> List: """ Returns (list): All the characters present in the database. """ connection = get_sql_db_connection() try: with connection.cursor() as cursor: # Read a single record sql = "SELECT char_id, films FROM starwarsDB.characters" cursor.execute(sql) result = cursor.fetchall() return result finally: connection.close()
def print_character() -> None: """Prints single character present in the database. Use this function for testing purposes. """ connection = get_sql_db_connection() try: with connection.cursor() as cursor: # Read all the record sql = "SELECT * FROM starwarsDB.characters" cursor.execute(sql) result = cursor.fetchone() print(result) finally: connection.close()
def format_output(people_id) -> Dict: """ Args: people_id (int): user selected character id for which results to be displayed Returns: dict formatted result """ connection = get_sql_db_connection() try: with connection.cursor() as cursor: sql1 = r" SELECT starwarsDB.characters.name, " \ r"starwarsDB.characters.homeworld, " \ r"starwarsDB.characters.gender " \ r"FROM starwarsDB.characters " \ r"where starwarsDB.characters.char_id={};".format(people_id) cursor.execute(sql1) sql1_result = cursor.fetchall() sql2 = r"SELECT starwarsDB.film.title " \ r"FROM starwarsDB.film " \ r"INNER JOIN starwarsDB.CharFilmRelation " \ r"ON starwarsDB.film.film_id = starwarsDB.CharFilmRelation.film_id " \ r"WHERE starwarsDB.CharFilmRelation.film_id " \ r"IN" \ r" (select starwarsDB.CharFilmRelation.film_id " \ r"FROM starwarsDB.CharFilmRelation " \ r"WHERE starwarsDB.CharFilmRelation.char_id = {});".format(people_id) cursor.execute(sql2) sql2_result = cursor.fetchall() unique_movies = list(set([v['title'] for v in sql2_result])) final_result = [] for movie in unique_movies: mid_result = {} mid_result.setdefault("film", movie) mid_result.setdefault("characters", []) mid_result["characters"].extend(sql1_result) final_result.append(mid_result) return final_result finally: connection.close()
def upsert_films(film: Dict, endpoint: str) -> None: """ Inserts values into `films` table, updates on duplicate key. Args: film (dict): endpoint (str): Returns: """ connection = get_sql_db_connection() # retrieving keys and values from an OrderedDict into list so as to maintain relative order character = OrderedDict(film) film_id = get_url_ids([endpoint]) keys_ = [] values_ = [] for key_, val_ in film.items(): keys_.append(key_) if isinstance(val_, list): values_.append(get_url_ids(val_)) else: values_.append(val_) try: Film(**film) except ValidationError as ve: print( f"[ Error ] fetched character record does not meet validations. Perhaps, type" f" conversions required. More details on error - {ve}") try: with connection.cursor() as cursor: sql = build_upsert_sql_query("starwarsDB.film", "INSERT INTO", "film_id", film_id, "ON DUPLICATE KEY UPDATE", keys_, values_) cursor.execute(sql) connection.commit() finally: connection.close()
def upsert_people_film_rels(mapping: Dict) -> None: """ Inserts values into `CharFilmRelation` table, updates on duplicate key. Args: mapping (dict): Returns: """ connection = get_sql_db_connection() try: with connection.cursor() as cursor: for key_ in mapping.keys(): for val_ in mapping[key_]: sql = r"INSERT INTO " \ r"CharFilmRelation(char_id, film_id)" \ r" VALUES({}, {})" \ r" ON DUPLICATE KEY UPDATE char_id={}, film_id={}" \ r";".format(key_, val_, key_, val_) cursor.execute(sql) connection.commit() finally: connection.close()