def get_top_game_last_entry(game_id): """ Get the last stats for a given game :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT current_position FROM twitch_top_games WHERE game_id = "{}" ORDER BY insert_time DESC LIMIT 0,1 """.format(game_id) print(sql) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() if rows: return rows[0] return False
def get_fb_shares_by_domain(domain: str, threshold: int = 1, limit: int = 0): """ Get links and shares for a given domain :param domain: :param threshold: :param limit: :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT site_link, site_link_title, fb_shares FROM facebook_most_shared WHERE site_link LIKE "%{}%" AND fb_shares > {} ORDER BY fb_shares """.format(domain, threshold) if limit > 0: sql += " LIMIT 0,{}".format(limit) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_all_sites(category: str = None, domain: str = None): """ Get all site urls :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT site_url, sitemap_url FROM scrapy_sites """ if category: sql += " WHERE category='{}'".format(category) if domain: sql += " WHERE site_url LIKE '%{}%'".format(domain) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_allowed_domains(): """ Get all domains :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT site_url, sitemap_url FROM scrapy_sites """ cursor.execute(sql) conn.commit() rows = dictfecth(cursor) # extract domains domains = list() for r in rows: d = get_domain(r['site_url']) domains.append(d) cursor.close() return domains
def get_fb_link_title(site_link): """ Get FB link by the site_url :param site_link: :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT * FROM facebook_most_shared WHERE site_link = "{}" """.format(site_link) try: cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() except Exception as e: print("\nERROR! ({})".format(str(e))) conn.rollback() return return rows
def is_fb_link_viral(site_link: str) -> bool: conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT is_viral FROM facebook_most_shared WHERE site_link = "{}" """.format(site_link) try: cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() if rows: v = rows[0].get('is_viral', 'unknown') if v == 'unknown' or v == 'yes': print("{} is VIRAL!".format(site_link)) return True else: print("{} is NOT VIRAL :(".format(site_link)) return False else: print("{} is NEW, it COULD BE VIRAL 8)".format(site_link)) return True except Exception as e: print("\nERROR! ({})".format(str(e))) conn.rollback() return False
def get_top_stream_last_entry(stream_id): """ Get the last stats for a given stream :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT viewer_count FROM twitch_top_streams WHERE stream_id = "{}" ORDER BY insert_time DESC LIMIT 0,1 """.format(stream_id) print(sql) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() if rows: return rows[0] return False
def word_weight_upsert(word, weight): conn = get_mysql_conn() cursor = conn.cursor() # check if the word exists sql = """ SELECT idprediction_blog_titles FROM prediction_blog_titles WHERE word = "{}"; """.format(word) r = cursor.execute(sql) if r > 0: conn.commit() rows = dictfecth(cursor) # update shares id = rows[0]["idprediction_blog_titles"] sql = """ UPDATE prediction_blog_titles SET weight = {} WHERE idprediction_blog_titles = '{}' """.format(weight, id) try: cursor.execute(sql) print("{} = {} weight updated.".format(word, weight)) conn.commit() except Exception as e: print("ERROR! ({})\n".format(str(e))) conn.rollback() return False cursor.close() return True else: # insert new link with shares sql = """ INSERT INTO prediction_blog_titles(word, weight) VALUES (%s, %s) """.format() try: cursor.execute(sql, (word, weight)) print("{} = {} weight inserted.".format(word, weight)) conn.commit() except Exception as e: print("\nERROR! ({})".format(str(e))) conn.rollback() return False cursor.close() return True
def get_site_links_by_category(category: str): conn = get_mysql_conn() cursor = conn.cursor() sql = "SELECT A.site_link as site_link FROM scrapy_sites_links as A " \ "INNER JOIN scrapy_sites as B ON A.site_url = B.site_url " \ "WHERE B.category = '{}'".format(category) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_top_tweets(retweet_threshold: int = 5, limit: int = 100): conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT * FROM twitter_most_retweeted WHERE retweet_count > {} ORDER BY retweet_count DESC LIMIT 0,{}; """.format(retweet_threshold, limit) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_members_by_country(): conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT country_code as country, COUNT(*) as total from mailchimp_members WHERE country_code <> '' GROUP BY country_code; """ cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_open_rate_by_country(): conn = get_mysql_conn() cursor = conn.cursor() sql = """ select country_code as country, AVG(open_rate) as total from mailchimp_members WHERE country_code <> '' GROUP BY country_code; """ cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_top_open_rate(limit: int = 10): conn = get_mysql_conn() cursor = conn.cursor() sql = """ select email_subject, unique_opens, open_rate, emails_sent from mailchimp_reports where emails_sent > 100 order by open_rate desc limit 0,{}; """.format(limit) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_word_weight(word): conn = get_mysql_conn() cursor = conn.cursor() sql = """ select weight from prediction_blog_titles WHERE word = "{}"; """.format(word) r = cursor.execute(sql) if r > 0: conn.commit() rows = dictfecth(cursor) return rows[0]["weight"] return 0
def get_domains(): """ Get all domains :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT * FROM domain_stats; """ cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_retweets_by_domain(domain: str, threshold: int = 10, limit: int = 0): conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT * FROM twitter_most_retweeted WHERE query LIKE "%{}%" AND retweet_count > {} ORDER BY retweet_count DESC """.format(domain, threshold) if limit > 0: sql += " LIMIT 0,{}".format(limit) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_words_wo_emotion(limit=0): """ Get words without emotion :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT * from prediction_blog_titles WHERE emotion IS NULL """ cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_title_by_link(link: str): conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT title FROM scrapy_sites_links WHERE site_link = "{}"; """.format(link) r = cursor.execute(sql) if r > 0: conn.commit() rows = dictfecth(cursor) cursor.close() title = rows[0]["title"] return title else: cursor.close() return ""
def get_top_page_time(limit: int = 10): conn = get_mysql_conn() cursor = conn.cursor() sql = """ select page_title, avg_time_on_page from ga_reports where users >= 5 and page_title NOT like 'Contact me%' and page_title NOT like 'Dashboard%' and page_title NOT like 'About me%' and page_title NOT like 'Home -%' order by avg_time_on_page desc limit 0,{}; """.format(limit) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_subjects_open_rate(): """ Get all the email subjects and their open rates :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ select email_subject, open_rate from mailchimp_reports """ cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_max_shares(): """ Get post with max shares :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT MAX(shares_total) as max_shares FROM links_shares """ r = cursor.execute(sql) if r > 0: conn.commit() rows = dictfecth(cursor) return rows[0]["max_shares"] return 0
def get_sharethis_stats_by_domain(domain: str, threshold: int = 10, limit: int = 0): conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT * FROM sharethis_stats WHERE site_link LIKE "%{}%" AND total > {} ORDER BY total DESC """.format(domain, threshold) if limit > 0: sql += " LIMIT 0,{}".format(limit) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_all_site_links(domain: str = None, keyword: str = None, limit: str = ""): """ Get all links for a given domain :param domain: :param keyword: :return: """ conn = get_mysql_conn() cursor = conn.cursor() select = "SELECT site_link FROM scrapy_sites_links" where = "" if domain: where = "WHERE site_url like '%{}%'".format(domain) if keyword: if not where: where = "WHERE site_link like '%{}%'".format(keyword) else: where += "AND site_link like '%{}%'".format(keyword) order = "ORDER BY idscrapy_sites_links DESC" if limit != "": limit = " LIMIT 0,{}".format(limit) sql = "{} {} {} {}".format(select, where, order, limit) print(sql) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_shares_by_domain(domain: str, threshold: int = 1, limit: int = 0): """ Get total shares for a given domain """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT link_url, link_title, shares_total FROM links_shares WHERE link_url LIKE "%{}%" AND shares_total > {} ORDER BY shares_total """.format(domain, threshold) if limit > 0: sql += " LIMIT 0,{}".format(limit) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_links_count(site_url: str): """ How many links per domain :param site_url: :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT COUNT(*) as count FROM scrapy_sites_links WHERE site_url = %s; """ cursor.execute(sql, site_url) conn.commit() rows = dictfecth(cursor) cursor.close() count = rows[0].get('count', 0) return count
def get_top_streamer(): """ Get the top streamer with most concurrent-viwers :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT user_name, viewer_count FROM twitch_top_streams ORDER BY viewer_count DESC LIMIT 0,1; """ cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() if rows: return rows[0]['user_name'], rows[0]['viewer_count'] return 0
def get_words_shares(limit=0): """ Get words and shares :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ select idprediction_blog_titles, word, shares, weight from prediction_blog_titles """ if limit: sql += " ORDER BY shares desc LIMIT 0,{}".format(limit) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_links_shares(threshold=1, limit=0): """ Get all link shares """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT idlinks_shares, link_url, link_title, shares_total, sentiment, sharing_score FROM links_shares WHERE shares_total > {} ORDER BY shares_total DESC """.format(threshold) if limit > 0: sql += " LIMIT 0,{}".format(limit) log.info(sql) cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() return rows
def get_top_game(): """ Get the game with most times in the Top 1 position :return: """ conn = get_mysql_conn() cursor = conn.cursor() sql = """ SELECT COUNT(*) as count_number, game_name FROM ada.twitch_top_games WHERE current_position = 1 GROUP BY game_name ORDER BY count_number DESC LIMIT 0,5; """ cursor.execute(sql) conn.commit() rows = dictfecth(cursor) cursor.close() if rows: return rows[0]['game_name'] return ""
def link_shares_update_or_insert(link, title, shares): """ Update the shares and title as seen from the social network :param link: :param title: :param shares: :return: """ conn = get_mysql_conn() cursor = conn.cursor() # check if the link exists sql = """ SELECT idlinks_shares FROM links_shares WHERE link_url = "{}"; """.format(link) r = cursor.execute(sql) if r > 0: conn.commit() rows = dictfecth(cursor) # update shares id = rows[0]["idlinks_shares"] sql = """ UPDATE links_shares SET shares_total = {} WHERE idlinks_shares = '{}' """.format(shares, id) try: cursor.execute(sql) print("{} shares updated.".format(link)) conn.commit() except Exception as e: print("ERROR! ({})\n".format(str(e))) conn.rollback() return False cursor.close() return True else: # insert new link with shares sql = """ INSERT INTO links_shares(link_url, link_title, shares_total) VALUES (%s, %s, %s) """.format() try: cursor.execute(sql, (link, title, shares)) print("{} shares inserted.".format(link)) conn.commit() except Exception as e: print("\nERROR! ({})".format(str(e))) conn.rollback() return False cursor.close() return True return False