def search_for_blogger(dictionary: ImmutableMultiDict, booked_package) -> list: track_search('blog', booked_package) sql_stmt = """ SELECT DISTINCT influencer.influencer_identifier, last_name, first_name, email, phone_number, price, gender, homebase, birthyear, pwd_hash, joined_at, blog_domain, blog_follower_amount, blog_post_amount, blog_rhythm, blog_page_views_amount FROM influencer JOIN is_listed_on_personal_blog ON influencer.influencer_identifier = is_listed_on_personal_blog.influencer_identifier LEFT OUTER JOIN influencer_covers_topic ON influencer.influencer_identifier = influencer_covers_topic.influencer_identifier LEFT OUTER JOIN influencer_deal ON influencer.influencer_identifier = influencer_deal.influencer_identifier LEFT OUTER JOIN content_of_channel ON channel_identifier = 5 AND influencer.influencer_identifier = content_of_channel.influencer_identifier WHERE is_listed_on_personal_blog.listing_on = 1""" parameters = [] if dictionary.get("birthyear_from") != "UNDEFINED" and dictionary.get( "birthyear_from") is not None: sql_stmt += """ AND birthyear >= %s """ parameters.append(dictionary.get("birthyear_from", 0)) if dictionary.get("birthyear_to") != "UNDEFINED" and dictionary.get( "birthyear_to") is not None: sql_stmt += """ AND birthyear <= %s """ parameters.append(dictionary.get("birthyear_to", 0)) if dictionary.get( "personal_blogFollowerFrom") != "UNDEFINED" and dictionary.get( "personal_blogFollowerFrom") is not None: sql_stmt += """ AND blog_follower_amount >= %s """ parameters.append(dictionary.get("personal_blogFollowerFrom", 0)) if dictionary.get( "personal_blogFollowerTo") != "UNDEFINED" and dictionary.get( "personal_blogFollowerTo") is not None: sql_stmt += """ AND blog_follower_amount <= %s """ parameters.append(dictionary.get("personal_blogFollowerTo", 0)) if dictionary.get( "personal_blog_page_calls_From") != "UNDEFINED" and dictionary.get( "personal_blog_page_calls_From") is not None: sql_stmt += """ AND blog_post_amount >= %s """ parameters.append(dictionary.get("personal_blog_page_calls_From", 0)) if dictionary.get("personal_blog_post_amount_from" ) != "UNDEFINED" and dictionary.get( "personal_blog_post_amount_from") is not None: sql_stmt += """ AND blog_post_amount >= %s """ parameters.append(dictionary.get("personal_blog_post_amount_from", 0)) if dictionary.get( "personal_blog_post_amount_to") != "UNDEFINED" and dictionary.get( "personal_blog_post_amount_to") is not None: sql_stmt += """ AND blog_post_amount <= %s """ parameters.append(dictionary.get("personal_blog_post_amount_to", 0)) if dictionary.getlist("personal_blog_rhythm_types") is not None and len( dictionary.getlist("personal_blog_rhythm_types")) > 0: sql_stmt += """ and blog_rhythm in """ + tuple_of_list( dictionary.getlist( "personal_blog_rhythm_types")) # TODO: WARNING: RISKY! if dictionary.getlist("topics") is not None and len( dictionary.getlist("topics")) > 0: sql_stmt += """ and topic_identifier in """ + tuple_of_list( dictionary.getlist("topics")) # TODO: WARNING: RISKY! if dictionary.getlist("personal_blog_content") is not None and len( dictionary.getlist("personal_blog_content")) > 0: sql_stmt += """ and content_type_identifier in """ + tuple_of_list( dictionary.getlist( "personal_blog_content")) # TODO: WARNING: RISKY! if dictionary.getlist("deals") is not None and len( dictionary.getlist("deals")) > 0: sql_stmt += """ and deal_identifier in """ + tuple_of_list( dictionary.getlist("deals")) # TODO: WARNING: RISKY! if dictionary.get("homebase") is not "": sql_stmt += """ and homebase = %s""" parameters.append(dictionary.get("homebase")) sql_stmt += """ LIMIT 20 """ if dictionary.get("offset"): if int(dictionary.get("offset")) >= 0 and int( dictionary.get("offset")) % 20 == 0: sql_stmt += """ OFFSET """ + dictionary.get("offset") else: return sql_stmt += ";" print("PARAMS>>" + str(parameters)) dbconnection = get_database_connection() cursor = dbconnection.cursor() prm_tuple = tuple(x for x in parameters) print("TUPLE>>" + str(prm_tuple)) try: cursor.execute(sql_stmt, prm_tuple) except: print(sql_stmt) print(prm_tuple) print(cursor.statement) result = parse_list_tuples_to_list_dict(cursor.fetchall(), cursor.column_names) cursor.close() dbconnection.close() return result
def search_instagrammer(dictionary: ImmutableMultiDict, booked_package) -> list: track_search('instagram', booked_package) dbconnection = get_database_connection() cursor = dbconnection.cursor() sql_stmt = """ SELECT DISTINCT * FROM ( SELECT DISTINCT searchable_instagram_profiles.influencer_identifier, instagram_username, instagram_follower_amount, instagram_post_amount, instagram_rhythm, instagram_gender_distribution_male, instagram_gender_distribution_female, instagram_age_distribution_min, instagram_age_distribution_max, instagram_engagement_rate_min, instagram_engagement_rate_max, instagram_follower_ratio_min, instagram_follower_ratio_max FROM searchable_instagram_profiles LEFT OUTER JOIN content_of_channel on channel_identifier = 1 and content_of_channel.influencer_identifier = searchable_instagram_profiles.influencer_identifier LEFT OUTER JOIN countries_of_channel on content_of_channel.channel_identifier = 1 and countries_of_channel.influencer_identifier = searchable_instagram_profiles.influencer_identifier """ params = [] where_clause = [] if dictionary.get( "instagramFollowerFrom") != 'UNDEFINED' and dictionary.get( "instagramFollowerFrom") is not None: where_clause.append("""instagram_follower_amount >= %s """) params.append(dictionary.get("instagramFollowerFrom")) if dictionary.get("instagramFollowerTo") != 'UNDEFINED' and dictionary.get( "instagramFollowerTo") is not None: where_clause.append(""" instagram_follower_amount <= %s """) params.append(dictionary.get("instagramFollowerTo")) if dictionary.get("instagram_age_distribution_from" ) != 'UNDEFINED' and dictionary.get( "instagram_age_distribution_from") is not None: where_clause.append(""" instagram_age_distribution_min >= %s """) params.append(dictionary.get("instagram_age_distribution_from")) if dictionary.get( "instagram_age_distribution_to") != 'UNDEFINED' and dictionary.get( "instagram_age_distribution_to") is not None: where_clause.append(""" instagram_age_distribution_max <= %s """) params.append(dictionary.get("instagram_age_distribution_to")) if dictionary.get("instagram_gender_distribution_female_from" ) != 'UNDEFINED' and dictionary.get( "instagram_gender_distribution_female_from" ) is not None: where_clause.append(""" instagram_gender_distribution_male >= %s """) params.append( dictionary.get("instagram_gender_distribution_female_from")) if dictionary.get("instagram_gender_distribution_female_to" ) != 'UNDEFINED' and dictionary.get( "instagram_gender_distribution_female_to" ) is not None: where_clause.append( """ instagram_gender_distribution_female <= %s """) params.append( dictionary.get("instagram_gender_distribution_female_to")) if dictionary.get("instagram_gender_distribution_male_from" ) != 'UNDEFINED' and dictionary.get( "instagram_gender_distribution_male_from" ) is not None: where_clause.append(""" instagram_gender_distribution_male >= %s """) params.append( dictionary.get("instagram_gender_distribution_male_from")) if dictionary.get("instagram_gender_distribution_male_to" ) != 'UNDEFINED' and dictionary.get( "instagram_gender_distribution_male_to") is not None: where_clause.append(""" instagram_gender_distribution_male <= %s """) params.append(dictionary.get("instagram_gender_distribution_male_to")) if dictionary.get("engagementRateFrom") != 'UNDEFINED' and dictionary.get( "engagementRateFrom") is not None: where_clause.append(""" instagram_engagement_rate_min >= %s """) params.append(dictionary.get("engagementRateFrom")) if dictionary.get("engagementRateTo") != 'UNDEFINED' and dictionary.get( "engagementRateTo") is not None: where_clause.append(""" instagram_engagement_rate_max <= %s """) params.append(dictionary.get("engagementRateTo")) if dictionary.get( "instagramfollowerRatioFrom") != 'UNDEFINED' and dictionary.get( "instagramfollowerRatioFrom") is not None: where_clause.append(""" instagram_follower_ratio_min >= %s """) params.append(dictionary.get("instagramfollowerRatioFrom")) if dictionary.get( "instagramfollowerRatioTo") != 'UNDEFINED' and dictionary.get( "instagramfollowerRatioTo") is not None: where_clause.append(""" instagram_follower_ratio_max <= %s """) params.append(dictionary.get("instagramfollowerRatioTo")) if dictionary.get( "instagram_post_amount_from") != 'UNDEFINED' and dictionary.get( "instagram_post_amount_from") is not None: where_clause.append(""" instagram_post_amount >= %s """) params.append(dictionary.get("instagram_post_amount_from")) if dictionary.get( "instagram_post_amount_to") != 'UNDEFINED' and dictionary.get( "instagram_post_amount_to") is not None: where_clause.append(""" instagram_post_amount <= %s """) params.append(dictionary.get("instagram_post_amount_to")) # TODO: Implement content-types if len(where_clause) > 0: sql_stmt += "WHERE " for item in where_clause: if item is not where_clause[-1]: sql_stmt += item + " and " else: sql_stmt += item tempory = 0 if len(where_clause) > 0: if dictionary.getlist("instagram_content") is not None and len( dictionary.getlist("instagram_content")) > 0: sql_stmt += """ and content_type_identifier in """ + tuple_of_list( dictionary.getlist("instagram_content")) tempory += 1 else: if dictionary.getlist("instagram_content") is not None and len( dictionary.getlist("instagram_content")) > 0: sql_stmt += """ WHERE content_type_identifier in """ + tuple_of_list( dictionary.getlist("instagram_content")) tempory += 1 if dictionary.getlist("instagram_rhythm_types") is not None and len( dictionary.getlist("instagram_rhythm_types")) > 0: tuple_of_list(dictionary.getlist("instagram_rhythm_types")) if len(where_clause) > 0 or tempory == 1: sql_stmt += """ and instagram_rhythm in """ + tuple_of_list( dictionary.getlist("instagram_rhythm_types")) else: sql_stmt += """ WHERE instagram_rhythm in """ + tuple_of_list( dictionary.getlist("instagram_rhythm_types")) sql_stmt += """ ) as c1 INNER JOIN ( SELECT DISTINCT influencer.influencer_identifier, birthyear, email, gender, homebase, first_name, last_name FROM influencer LEFT OUTER JOIN influencer_covers_topic on influencer.influencer_identifier = influencer_covers_topic.influencer_identifier LEFT OUTER JOIN influencer_channel_language on influencer.influencer_identifier = influencer_channel_language.influencer_identifier LEFT OUTER JOIN influencer_deal on influencer_deal.influencer_identifier = influencer.influencer_identifier WHERE birthyear >= %s and birthyear <= %s """ params.append(dictionary.get("birthyear_from")) params.append(dictionary.get("birthyear_to")) if dictionary.getlist("genders") is not None and len( dictionary.getlist("genders")) > 0: sql_stmt += """ and gender in """ + tuple_of_list( dictionary.getlist("genders")) if dictionary.get("homebase") is not "": sql_stmt += """ and homebase = %s""" params.append(dictionary.get("homebase")) if dictionary.getlist("deals") is not None and len( dictionary.getlist("deals")) > 0: sql_stmt += """ and deal_identifier in """ + tuple_of_list( dictionary.getlist("deals")) # TODO: WARNING: RISKY! if dictionary.getlist("topics") is not None and len( dictionary.getlist("topics")) > 0: sql_stmt += """ and topic_identifier in """ + tuple_of_list( dictionary.getlist("topics")) # TODO: WARNING: RISKY! # if dictionary.get("usedLanguages") is not None: # sql_stmt += """ and language_identifer in """+str(tuple_of_list(dictionary.getlist("usedLanguages"))) sql_stmt += """) as c2 on c1.influencer_identifier = c2.influencer_identifier LIMIT 20""" if dictionary.get("offset"): if int(dictionary.get("offset")) >= 0 and int( dictionary.get("offset")) % 20 == 0: sql_stmt += """ OFFSET """ + dictionary.get("offset") sql_stmt += ";" prm_tuple = tuple(x for x in params) try: cursor.execute(sql_stmt, prm_tuple) except mysql.connector.errors.ProgrammingError: print(sql_stmt) print(cursor.statement) result = cursor.fetchall() column_names = cursor.column_names cursor.close() dbconnection.close() return parse_list_tuples_to_list_dict(result, column_names)
def search_youtubers(dictionary: ImmutableMultiDict, booked_package) -> list: track_search('youtube', booked_package) sql_stmt = """ SELECT DISTINCT influencer.influencer_identifier, last_name, first_name, email, phone_number, price, gender, homebase, birthyear, pwd_hash, joined_at, confirmed, youtube_username, youtube_follower_amount, youtube_post_amount, youtube_rhythm, youtube_gender_distribution_male, youtube_gender_distribution_female, youtube_age_distribution_min, youtube_age_distribution_max, youtube_page_views, youtube_impressions_amount, youtube_click_rate FROM influencer JOIN is_listed_on_youtube on influencer.influencer_identifier = is_listed_on_youtube.influencer_identifier LEFT OUTER JOIN influencer_covers_topic on influencer.influencer_identifier = influencer_covers_topic.influencer_identifier LEFT OUTER JOIN influencer_deal on influencer.influencer_identifier = influencer_deal.influencer_identifier LEFT OUTER JOIN countries_of_channel on influencer.influencer_identifier = countries_of_channel.influencer_identifier and channel_identifier = 3 WHERE is_listed_on_youtube.listing_on = 1 """ parameters = [] if dictionary.get("birthyear_from") != "UNDEFINED" and dictionary.get( "birthyear_from") is not None: sql_stmt += """ AND birthyear >= %s """ parameters.append(dictionary.get("birthyear_from", 0)) if dictionary.get("birthyear_to") != "UNDEFINED" and dictionary.get( "birthyear_to") is not None: sql_stmt += """ AND birthyear <= %s """ parameters.append(dictionary.get("birthyear_to", 0)) if dictionary.get("youtubeFollowerFrom") != "UNDEFINED" and dictionary.get( "youtubeFollowerFrom") is not None: sql_stmt += """ AND youtube_follower_amount >= %s """ parameters.append(dictionary.get("youtubeFollowerFrom", 0)) if dictionary.get("youtubeFollowerTo") != "UNDEFINED" and dictionary.get( "youtubeFollowerTo") is not None: sql_stmt += """ AND youtube_follower_amount <= %s """ parameters.append(dictionary.get("youtubeFollowerTo", 0)) if dictionary.get( "youtube_age_distribution_from") != "UNDEFINED" and dictionary.get( "youtube_age_distribution_from") is not None: sql_stmt += """ AND youtube_age_distribution_min >= %s """ parameters.append(dictionary.get("youtube_age_distribution_from", 0)) if dictionary.get( "youtube_age_distribution_to") != "UNDEFINED" and dictionary.get( "youtube_age_distribution_to") is not None: sql_stmt += """ AND youtube_age_distribution_max <= %s """ parameters.append(dictionary.get("youtube_age_distribution_to", 0)) if dictionary.get("youtube_gender_distribution_female_from" ) != "UNDEFINED" and dictionary.get( "youtube_gender_distribution_female_from" ) is not None: sql_stmt += """ AND youtube_gender_distribution_female >= %s """ parameters.append( dictionary.get("youtube_gender_distribution_female_from", 0)) if dictionary.get("youtube_gender_distribution_female_to" ) != "UNDEFINED" and dictionary.get( "youtube_gender_distribution_female_to") is not None: sql_stmt += """ AND youtube_gender_distribution_female <= %s """ parameters.append( dictionary.get("youtube_gender_distribution_female_to", 0)) if dictionary.get("youtube_gender_distribution_male_from" ) != "UNDEFINED" and dictionary.get( "youtube_gender_distribution_male_from") is not None: sql_stmt += """ AND youtube_gender_distribution_male >= %s """ parameters.append( dictionary.get("youtube_gender_distribution_male_from", 0)) if dictionary.get("youtube_gender_distribution_male_to" ) != "UNDEFINED" and dictionary.get( "youtube_gender_distribution_male_to") is not None: sql_stmt += """ AND youtube_gender_distribution_male <= %s """ parameters.append( dictionary.get("youtube_gender_distribution_male_to", 0)) if dictionary.get( "youtube_click_rate_From") != "UNDEFINED" and dictionary.get( "youtube_click_rate_From") is not None: sql_stmt += """ AND youtube_click_rate >= %s """ parameters.append(dictionary.get("youtube_click_rate_From", 0)) if dictionary.get( "youtube_click_rate_To") != "UNDEFINED" and dictionary.get( "youtube_click_rate_To") is not None: sql_stmt += """ AND youtube_click_rate <= %s """ parameters.append(dictionary.get("youtube_click_rate_To", 0)) if dictionary.get( "youtube_impressions_from") != "UNDEFINED" and dictionary.get( "youtube_impressions_from") is not None: sql_stmt += """ AND youtube_impressions_amount >= %s """ parameters.append(dictionary.get("youtube_impressions_from", 0)) if dictionary.get( "youtube_impressions_to") != "UNDEFINED" and dictionary.get( "youtube_impressions_to") is not None: sql_stmt += """ AND youtube_impressions_amount <= %s """ parameters.append(dictionary.get("youtube_impressions_to", 0)) if dictionary.get( "youtube_post_amount_from") != "UNDEFINED" and dictionary.get( "youtube_post_amount_from") is not None: sql_stmt += """ AND youtube_post_amount >= %s """ parameters.append(dictionary.get("youtube_post_amount_from", 0)) if dictionary.get( "youtube_post_amount_to") != "UNDEFINED" and dictionary.get( "youtube_post_amount_to") is not None: sql_stmt += """ AND youtube_post_amount <= %s """ parameters.append(dictionary.get("youtube_post_amount_to", 0)) if dictionary.get("youtube_views_From") != "UNDEFINED" and dictionary.get( "youtube_views_From") is not None: sql_stmt += """ AND youtube_page_views >= %s """ parameters.append(dictionary.get("youtube_views_From", 0)) if dictionary.get("youtube_views_To") != "UNDEFINED" and dictionary.get( "youtube_views_To") is not None: sql_stmt += """ AND youtube_page_views <= %s """ parameters.append(dictionary.get("youtube_views_To", 0)) if dictionary.getlist("youtube_rhythm_types") is not None and len( dictionary.getlist("youtube_rhythm_types")) > 0: sql_stmt += """ and youtube_rhythm in """ + tuple_of_list( dictionary.getlist( "youtube_rhythm_types")) # TODO: WARNING: RISKY! if dictionary.getlist("youtube_countries") is not None and len( dictionary.getlist("youtube_countries")) > 0: sql_stmt += """ and country_identifier in """ + tuple_of_list( dictionary.getlist("youtube_countries")) # TODO: WARNING: RISKY! if dictionary.get("homebase") is not "": sql_stmt += """ and homebase = %s""" parameters.append(dictionary.get("homebase")) if dictionary.getlist("topics") is not None and len( dictionary.getlist("topics")) > 0: sql_stmt += """ and topic_identifier in """ + tuple_of_list( dictionary.getlist("topics")) # TODO: WARNING: RISKY! if dictionary.getlist("deals") is not None and len( dictionary.getlist("deals")) > 0: sql_stmt += """ and deal_identifier in """ + tuple_of_list( dictionary.getlist("deals")) # TODO: WARNING: RISKY! if dictionary.getlist("genders") is not None and len( dictionary.getlist("genders")) > 0: sql_stmt += """ and gender in """ + tuple_of_list( dictionary.getlist("genders")) # TODO: WARNING: RISKY! sql_stmt += """ LIMIT 20 """ if dictionary.get("offset"): if int(dictionary.get("offset")) >= 0 and int( dictionary.get("offset")) % 20 == 0: sql_stmt += """ OFFSET """ + dictionary.get("offset") else: return sql_stmt += ";" dbconnection = get_database_connection() cursor = dbconnection.cursor() prm_tuple = tuple(x for x in parameters) try: cursor.execute(sql_stmt, prm_tuple) except: print(sql_stmt) print(prm_tuple) print(cursor.statement) result = parse_list_tuples_to_list_dict(cursor.fetchall(), cursor.column_names) cursor.close() dbconnection.close() return result
def search_influencer_related(dictionary: ImmutableMultiDict, booked_package) -> list: track_search('global', booked_package) dbconnection = get_database_connection() cursor = dbconnection.cursor() sql_stmt = """ SELECT DISTINCT influencer.influencer_identifier, email, birthyear, gender, homebase, first_name, last_name FROM influencer LEFT OUTER JOIN influencer_covers_topic on influencer.influencer_identifier = influencer_covers_topic.influencer_identifier LEFT OUTER JOIN influencer_channel_language on influencer.influencer_identifier = influencer_channel_language.influencer_identifier LEFT OUTER JOIN influencer_deal on influencer_deal.influencer_identifier = influencer.influencer_identifier WHERE birthyear >= %s and birthyear <= %s """ params = [dictionary.get("birthyear_from"), dictionary.get("birthyear_to")] if dictionary.getlist("genders") is not None and len( dictionary.getlist("genders")) > 0: sql_stmt += """ and gender in """ + tuple_of_list( dictionary.getlist("genders")) if dictionary.get("homebase") is not "": sql_stmt += """ and homebase = %s""" params.append(dictionary.get("homebase")) if dictionary.getlist("deals") is not None and len( dictionary.getlist("deals")) > 0: sql_stmt += """ and deal_identifier in """ + tuple_of_list( dictionary.getlist("deals")) # TODO: WARNING: RISKY! if dictionary.getlist("topics") is not None and len( dictionary.getlist("topics")) > 0: sql_stmt += """ and topic_identifier in """ + tuple_of_list( dictionary.getlist("topics")) # TODO: WARNING: RISKY! # if dictionary.get("usedLanguages") is not None: # sql_stmt += """ and language_identifer in """ + str(tuple_of_list(dictionary.getlist("usedLanguages"))) sql_stmt += """ LIMIT 20 """ if dictionary.get("offset"): sql_stmt += """ OFFSET """ + dictionary.get("offset") sql_stmt += ";" prm_tuple = tuple(x for x in params) try: cursor.execute(sql_stmt, prm_tuple) except mysql.connector.errors.ProgrammingError: print(sql_stmt) print(cursor.statement) result = cursor.fetchall() column_names = cursor.column_names cursor.close() dbconnection.close() return parse_list_tuples_to_list_dict(result, column_names)
def search_facebook_user(dictionary: ImmutableMultiDict, booked_package) -> list: track_search('facebook', booked_package) sql_stmt = """ SELECT DISTINCT influencer.influencer_identifier, last_name, first_name, email, price, gender, homebase, birthyear, facebook_username, facebook_follower_amount, facebook_page_views, facebook_post_amount, facebook_rhythm, facebook_gender_distribution_male, facebook_gender_distribution_female, facebook_page_activity_amount, facebook_likes_amount, facebook_reach_value, facebook_post_interaction FROM influencer JOIN is_listed_on_facebook on is_listed_on_facebook.influencer_identifier = influencer.influencer_identifier LEFT OUTER JOIN content_of_channel on content_of_channel.influencer_identifier = influencer.influencer_identifier and channel_identifier = 2 LEFT OUTER JOIN influencer_covers_topic on influencer.influencer_identifier = influencer_covers_topic.influencer_identifier LEFT OUTER JOIN influencer_deal on influencer.influencer_identifier = influencer_deal.influencer_identifier LEFT OUTER JOIN countries_of_channel on influencer.influencer_identifier = countries_of_channel.influencer_identifier and countries_of_channel.channel_identifier = 2 WHERE influencer.listing_on = 1 """ parameters = [] if dictionary.get( "facebookFollowerFrom") != "UNDEFINED" and dictionary.get( "facebookFollowerFrom") is not None: sql_stmt += """ and facebook_follower_amount >= %s """ parameters.append(dictionary.get("facebookFollowerFrom", 0)) if dictionary.get("facebookFollowerTo") != "UNDEFINED" and dictionary.get( "facebookFollowerTo") is not None: sql_stmt += """ and facebook_follower_amount <= %s """ parameters.append(dictionary.get("facebookFollowerTo", 0)) if dictionary.get("birthyear_from") != "UNDEFINED" and dictionary.get( "birthyear_from") is not None: sql_stmt += """ and birthyear >= %s """ parameters.append(dictionary.get("birthyear_from", 0)) if dictionary.get("birthyear_to") != "UNDEFINED" and dictionary.get( "birthyear_to") is not None: sql_stmt += """ and birthyear <= %s """ parameters.append(dictionary.get("birthyear_to", 0)) # if dictionary.get("facebook_reach_from") != "UNDEFINED" and dictionary.get("facebook_reach_from") is not None: # sql_stmt += """ and facebook_reach_value >= %s """ # parameters.append(dictionary.get("facebook_reach_from", 0)) # # if dictionary.get("facebook_reach_to") != "UNDEFINED" and dictionary.get("facebook_reach_to") is not None: # sql_stmt += """ and facebook_reach_value <= %s """ # parameters.append(dictionary.get("facebook_reach_to", 0)) if dictionary.get("facebook_post_interaction_From" ) != "UNDEFINED" and dictionary.get( "facebook_post_interaction_From") is not None: sql_stmt += """ and facebook_post_interaction >= %s """ parameters.append(dictionary.get("facebook_post_interaction_From", 0)) if dictionary.get( "facebook_post_interaction_To") != "UNDEFINED" and dictionary.get( "facebook_post_interaction_To") is not None: sql_stmt += """ and facebook_post_interaction <= %s """ parameters.append(dictionary.get("facebook_post_interaction_To", 0)) if dictionary.get( "facebook_post_amount_from") != "UNDEFINED" and dictionary.get( "facebook_post_amount_from") is not None: sql_stmt += """ and facebook_post_amount >= %s """ parameters.append(dictionary.get("facebook_post_amount_from", 0)) if dictionary.get( "facebook_post_amount_to") != "UNDEFINED" and dictionary.get( "facebook_post_amount_to") is not None: sql_stmt += """ and facebook_post_amount <= %s """ parameters.append(dictionary.get("facebook_post_amount_to", 0)) if dictionary.get( "facebook_page_calls_From") != "UNDEFINED" and dictionary.get( "facebook_page_calls_From") is not None: sql_stmt += """ and facebook_page_views >= %s """ parameters.append(dictionary.get("facebook_page_calls_From", 0)) if dictionary.get( "facebook_page_calls_To") != "UNDEFINED" and dictionary.get( "facebook_page_calls_To") is not None: sql_stmt += """ and facebook_page_views <= %s """ parameters.append(dictionary.get("facebook_page_calls_To", 0)) if dictionary.get( "facebook_page_activity_From") != "UNDEFINED" and dictionary.get( "facebook_page_activity_From") is not None: sql_stmt += """ and facebook_page_activity_amount >= %s """ parameters.append(dictionary.get("facebook_page_activity_From", 0)) if dictionary.get( "facebook_page_activity_To") != "UNDEFINED" and dictionary.get( "facebook_page_activity_To") is not None: sql_stmt += """ and facebook_page_activity_amount <= %s """ parameters.append(dictionary.get("facebook_page_activity_To", 0)) if dictionary.get("facebook_likes_From") != "UNDEFINED" and dictionary.get( "facebook_likes_From") is not None: sql_stmt += """ and facebook_likes_amount >= %s """ parameters.append(dictionary.get("facebook_likes_From", 0)) if dictionary.get("facebook_likes_To") != "UNDEFINED" and dictionary.get( "facebook_likes_To") is not None: sql_stmt += """ and facebook_likes_amount <= %s """ parameters.append(dictionary.get("facebook_likes_To", 0)) if dictionary.get("facebook_gender_distribution_female_from" ) != "UNDEFINED" and dictionary.get( "facebook_gender_distribution_female_from" ) is not None: sql_stmt += """ and facebook_gender_distribution_female >= %s """ parameters.append( dictionary.get("facebook_gender_distribution_female_from", 0)) if dictionary.get("facebook_gender_distribution_female_to" ) != "UNDEFINED" and dictionary.get( "facebook_gender_distribution_female_to" ) is not None: sql_stmt += """ and facebook_gender_distribution_female <= %s """ parameters.append( dictionary.get("facebook_gender_distribution_female_to", 0)) if dictionary.get("facebook_gender_distribution_male_from" ) != "UNDEFINED" and dictionary.get( "facebook_gender_distribution_male_from" ) is not None: sql_stmt += """ and facebook_gender_distribution_male >= %s """ parameters.append( dictionary.get("facebook_gender_distribution_male_from", 0)) if dictionary.get("facebook_gender_distribution_male_to" ) != "UNDEFINED" and dictionary.get( "facebook_gender_distribution_male_to") is not None: sql_stmt += """ and facebook_gender_distribution_male <= %s """ parameters.append( dictionary.get("facebook_gender_distribution_male_to", 0)) if dictionary.getlist("facebook_rhythm_types") is not None and len( dictionary.getlist("facebook_rhythm_types")) > 0: sql_stmt += """ and facebook_rhythm in """ + tuple_of_list( dictionary.getlist( "facebook_rhythm_types")) # TODO: WARNING: RISKY! if dictionary.getlist("facebook_content") is not None and len( dictionary.getlist("facebook_content")) > 0: sql_stmt += """ and content_type_identifier in """ + tuple_of_list( dictionary.getlist("facebook_content")) # TODO: WARNING: RISKY! if dictionary.getlist("genders") is not None and len( dictionary.getlist("genders")) > 0: sql_stmt += """ and gender in """ + tuple_of_list( dictionary.getlist("genders")) # TODO: WARNING: RISKY! if dictionary.getlist("topics") is not None and len( dictionary.getlist("topics")) > 0: sql_stmt += """ and topic_identifier in """ + tuple_of_list( dictionary.getlist("topics")) # TODO: WARNING: RISKY! if dictionary.get("homebase") is not "": sql_stmt += """ and homebase = %s""" parameters.append(dictionary.get("homebase")) if dictionary.getlist("deals") is not None and len( dictionary.getlist("deals")) > 0: sql_stmt += """ and deal_identifier in """ + tuple_of_list( dictionary.getlist("deals")) # TODO: WARNING: RISKY! if dictionary.getlist("facebook_countries") is not None and len( dictionary.getlist("facebook_countries")) > 0: sql_stmt += """ and country_identifier in """ + tuple_of_list( dictionary.getlist("facebook_countries")) # TODO: WARNING: RISKY! sql_stmt += """ LIMIT 20 """ if dictionary.get("offset"): if int(dictionary.get("offset")) >= 0 and int( dictionary.get("offset")) % 20 == 0: sql_stmt += """ OFFSET """ + dictionary.get("offset") else: return False sql_stmt += ";" dbconnection = get_database_connection() cursor = dbconnection.cursor() prm_tuple = tuple(x for x in parameters) try: cursor.execute(sql_stmt, prm_tuple) print(cursor.statement) except: print(sql_stmt) print(prm_tuple) print(cursor.statement) result = parse_list_tuples_to_list_dict(cursor.fetchall(), cursor.column_names) cursor.close() dbconnection.close() return result