def create_counts_for_subreddit(subreddit_name, start, end): db = SQLite3Database('posts.db') post_count = db.get_post_count_for_subreddit( subreddit_name, start, end ) db.close() db = SQLite3Database('comments.db') comment_count = db.get_comment_count_for_subreddit( subreddit_name, start, end ) db.close() datetime_string = unix_timestamp_to_datetime_string(start) post_response = server.create_post_count( subreddit_name, post_count, datetime_string ) comment_response = server.create_comment_count( subreddit_name, comment_count, datetime_string ) print(post_response) print(comment_response) return 'errors' not in post_response and 'errors' not in comment_response
def create_post_count_for_subreddit(subreddit_name, praw_subreddit, start, end): posts_generator = praw_subreddit.submissions(start, end) posts = list(posts_generator) db = SQLite3Database('posts.db') success_count = 0 for post in posts: try: post_id = post.id self_text = post.selftext created_utc = post.created_utc if db.insert_post(subreddit_name, post_id, self_text, created_utc): success_count += 1 except sqlite3.IntegrityError: pass db.close() logger.info('Synced %s posts for subreddit %s' % (success_count, subreddit_name)) post_count = len(posts) datetime_string = unix_timestamp_to_datetime_string(start) return server.create_post_count(subreddit_name, post_count, datetime_string)
def insert_comments(subreddit_name, comments): success_count = 0 db = SQLite3Database('comments.db') for comment in comments: # Same logic as in backfill_comments.py - refactor? comment_id = comment.id parent_id = comment.parent_id if isinstance(comment, MoreComments): link_id = None body = 'MORE_COMMENTS' created_utc = last_created_utc else: link_id = comment.link_id body = comment.body created_utc = comment.created_utc last_created_utc = created_utc try: response = db.insert_comment( subreddit_name, comment_id, parent_id, link_id, body, created_utc ) success_count += 1 except sqlite3.IntegrityError: pass db.close() return success_count
def create_comment_count_for_subreddit(subreddit_name, start, end): db = SQLite3Database('comments.db') comment_count = db.get_comment_count_for_subreddit(subreddit_name, start, end) db.close() datetime_string = unix_timestamp_to_datetime_string(start) return server.create_comment_count(subreddit_name, comment_count, datetime_string)
def run(self): success_count = 0 db = SQLite3Database('market_tickers.db') for _, summary in self.client.market_summaries.items(): if db.insert_market_ticker(summary['MarketName'], summary['Last'], summary['TimeStamp'], dt.now()): success_count += 1 db.close() logger.info('Synced {} market tickers'.format(success_count)) if self.debug: print('[INFO] Data committed to sqlite database {}'.format( 'market_tickers.db'))
def create_mention_counts_for_subreddit(subreddit_name, keyword_id_to_words, start, end): datetime_string = unix_timestamp_to_datetime_string(start) for keyword_id, word in keyword_id_to_words.items(): db = SQLite3Database('comments.db') mention_count = db.get_keyword_count_for_subreddit( subreddit_name, word, start, end) db.close() logger.info('Keyword {} mentioned {} times in subreddit {}'.format( word, mention_count, subreddit_name)) response = server.create_mention_count(subreddit_name, keyword_id, mention_count, datetime_string)
def update_subreddit_counts(subreddit_name, praw_subreddit, start, end): posts = praw_subreddit.submissions(start, end) post_count = len(list(posts)) db = SQLite3Database('comments.db') comment_count = db.get_comment_count_for_subreddit( subreddit_name, start, end ) db.close() active_user_count = praw_subreddit.active_user_count subscriber_count = praw_subreddit.subscribers return server.update_subreddit_counts( subreddit_name, post_count, comment_count, active_user_count, subscriber_count )
def _get_tickers(self): target_url = 'https://api.coinmarketcap.com/v1/ticker/?limit=0' result = self._curl_result(target_url) if result == None: logger.info('Something went wrong with fetching market tickers') return db = SQLite3Database('cmc_tickers.db') # [{'id': 'bitcoin', 'name': 'Bitcoin', ... }, { ... }] step = 128 for i in range(0, len(result), step): tokens = result[i:i + step] tokens_string = json.dumps(tokens) tokens_string = tokens_string.replace('"', '\\"') response = self.api.update_tokens(tokens_string) time.sleep(24) if 'errors' in response: print('Something went wrong with saving market ticker: %s' % response['errors']) logger.info( 'Something went wrong with saving market ticker: %s' % response['errors']) for token_dict in result: str_timestamp = token_dict['last_updated'] if str_timestamp is None: continue else: timestamp = int(str_timestamp) datetime_string = unix_timestamp_to_datetime_string(timestamp) db.insert_cmc_ticker(str(token_dict), timestamp) db.close()
from subreddits import SUBREDDITS from utils import unix_timestamp_now, datetime_string_now server = Api() logger.info('Starting active user and subscriber counts script...') for subreddit_name in SUBREDDITS: praw_subreddit = reddit.subreddit(subreddit_name) active_user_count = praw_subreddit.active_user_count subscriber_count = praw_subreddit.subscribers unix_timestamp = unix_timestamp_now() datetime_string = datetime_string_now() active_user_counts_db = SQLite3Database('active_user_counts.db') result = active_user_counts_db.cursor.execute( ''' INSERT INTO active_user_counts (subreddit_name, count, timestamp) VALUES (?, ?, ?) ''', (subreddit_name, active_user_count, unix_timestamp)) active_user_counts_db.conn.commit() active_user_counts_db.close() response = server.create_active_user_count(subreddit_name, active_user_count, datetime_string) subscriber_counts_db = SQLite3Database('subscriber_counts.db') result = subscriber_counts_db.cursor.execute( ''' INSERT INTO subscriber_counts (subreddit_name, count, timestamp)
from database import SQLite3Database db = SQLite3Database('active_user_counts.db') db.execute(''' CREATE TABLE IF NOT EXISTS active_user_counts ( subreddit_name string, count int, timestamp int ) ''') db.execute(''' CREATE INDEX IF NOT EXISTS active_user_counts_subreddit_name_and_timestamp ON active_user_counts (subreddit_name, timestamp) ''') db.close() ######################################## db = SQLite3Database('cmc_tickers.db') db.execute(''' CREATE TABLE IF NOT EXISTS cmc_tickers ( blob string, last_updated int ) ''') db.close() ######################################## db = SQLite3Database('comments.db') db.execute('''
def backfill_posts_and_comments(subreddit_name, praw_subreddit, start, end): posts = praw_subreddit.submissions(start, end) post_success_count = 0 comment_success_count = 0 datetime_string = unix_timestamp_to_datetime_string(start) logger.info('Backfilling subreddit %s posts and comments from %s' % (subreddit_name, datetime_string)) for post in posts: db = SQLite3Database('posts.db') try: post_id = post.id self_text = post.selftext created_utc = post.created_utc if db.insert_post(subreddit_name, post_id, self_text, created_utc): post_success_count += 1 except sqlite3.IntegrityError: pass db.close() for i in range(10): try: post_comments = post.comments.list() break except RequestException: time.sleep(10) if i >= 9: logger.info('Request exception after 10 tries') raise Exception('Request exception after 10 tries') db = SQLite3Database('comments.db') last_created_utc = None # Note we count an instance of MoreComments as one comment. for comment in post_comments: comment_id = comment.id parent_id = comment.parent_id if isinstance(comment, MoreComments): link_id = None body = 'MORE_COMMENTS' created_utc = last_created_utc else: link_id = comment.link_id body = comment.body created_utc = comment.created_utc last_created_utc = created_utc try: if db.insert_comment(subreddit_name, comment_id, parent_id, link_id, body, created_utc): comment_success_count += 1 except sqlite3.IntegrityError: continue db.close() time.sleep(2) logger.info('Backfilled %s posts for subreddit %s' % (post_success_count, subreddit_name)) logger.info('Backfilled %s comments for subreddit %s' % (comment_success_count, subreddit_name)) print('Backfilled %s posts for subreddit %s' % (post_success_count, subreddit_name)) print('Backfilled %s comments for subreddit %s' % (comment_success_count, subreddit_name))
def _get_global(self): global_target_url = 'https://api.coinmarketcap.com/v1/global/' global_result = self._curl_result(global_target_url) if global_result == None: logger.info('Something went wrong with fetching globals') raise Exception('Something went wrong with fetching globals') elif 'total_market_cap_usd' not in global_result: logger.info('Globals response different than expected: {}'.format( global_result)) raise Exception( 'Globals response different than expected: {}'.format( global_result)) bitcoin_target_url = 'https://api.coinmarketcap.com/v1/ticker/bitcoin' bitcoin_result = self._curl_result(bitcoin_target_url) if bitcoin_result == None or len(bitcoin_result) <= 0: raise Exception( 'Something went wrong with fetching bitcoin ticker') ethereum_target_url = 'https://api.coinmarketcap.com/v1/ticker/ethereum' ethereum_result = self._curl_result(ethereum_target_url) if ethereum_result == None or len(ethereum_result) <= 0: raise Exception( 'Something went wrong with fetching bitcoin ticker') total_market_cap_usd = int(float( global_result['total_market_cap_usd'])) global_timestamp = global_result['last_updated'] datetime_string = unix_timestamp_to_datetime_string(global_timestamp) db = SQLite3Database('total_market_caps.db') db.insert_total_ticker(str(global_result), global_timestamp) db.close() ### Total market caps ### response = self.api.create_market_ticker_by_market_name( 'TOTAL', str(total_market_cap_usd), datetime_string) if 'errors' in response: logger.info('Something went wrong with saving total market ticker') raise Exception( 'Something went wrong with saving total market ticker') ### Market caps ### bitcoin_market_cap_usd = int(float( bitcoin_result[0]['market_cap_usd'])) bitcoin_timestamp = bitcoin_result[0]['last_updated'] datetime_string = unix_timestamp_to_datetime_string( int(bitcoin_timestamp)) response = self.api.create_market_ticker_by_market_name( 'TOTAL_BITCOIN', str(bitcoin_market_cap_usd), datetime_string) ethereum_market_cap_usd = int( float(ethereum_result[0]['market_cap_usd'])) response = self.api.create_market_ticker_by_market_name( 'TOTAL_ETHEREUM', str(ethereum_market_cap_usd), datetime_string) altcoins_market_cap_usd = int(total_market_cap_usd) - int( bitcoin_market_cap_usd) - int(ethereum_market_cap_usd) response = self.api.create_market_ticker_by_market_name( 'TOTAL_ALTCOINS', str(altcoins_market_cap_usd), datetime_string) print('totals') print(datetime_string) print(bitcoin_market_cap_usd, ethereum_market_cap_usd, altcoins_market_cap_usd) assert total_market_cap_usd == bitcoin_market_cap_usd + ethereum_market_cap_usd + altcoins_market_cap_usd ### Percent dominance ### bitcoin_percent = round( bitcoin_market_cap_usd * 100 / total_market_cap_usd, 2) response = self.api.create_market_ticker_by_market_name( 'PERCENT_BITCOIN', str(bitcoin_percent), datetime_string) ethereum_percent = round( ethereum_market_cap_usd * 100 / total_market_cap_usd, 2) response = self.api.create_market_ticker_by_market_name( 'PERCENT_ETHEREUM', str(ethereum_percent), datetime_string) altcoins_percent = round(100.0 - bitcoin_percent - ethereum_percent, 2) response = self.api.create_market_ticker_by_market_name( 'PERCENT_ALTCOINS', str(altcoins_percent), datetime_string) print('percents') print(datetime_string) print(bitcoin_percent, ethereum_percent, altcoins_percent) assert bitcoin_percent + ethereum_percent + altcoins_percent == 100.0