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
