def get_all_games(): start_time = time.time() all_games = [] connection = pymysql.connect(host=host, port=port, user=user, passwd=password, db=db_schema, charset='utf8') cursor = connection.cursor() cursor.execute('SELECT * FROM Games') data = cursor.fetchall() for row in data: # (game.game_name, game.game_link, game.value, game.steam_score, game.num_of_reviews) game_data = GameData(row[0], row[1], row[2], steam_score=row[3], num_of_reviews=row[4]) all_games.append(game_data) cursor.close() connection.close() LogUtils.log_info('Get list of all games took ' + str(time.time() - start_time) + ' seconds') return all_games
def get_existing_games_data(games_list): connection = pymysql.connect(host=host, port=port, user=user, passwd=password, db=db_schema, charset='utf8') cursor = connection.cursor() existing_games_data = dict() cursor.execute("SELECT * FROM Games WHERE Name IN (" + parse_list(games_list) + ")") data = cursor.fetchall() for row in data: # (game.game_name, game.game_link, game.value, game.steam_score, game.num_of_reviews) game_name = row[0] game_data = GameData(game_name, row[1], row[2], steam_score=row[3], num_of_reviews=row[4]) existing_games_data[game_name] = game_data cursor.close() connection.close() LogUtils.log_info('Out of total ' + str(len(games_list)) + ' games in group, already exist in DB: ' + str(len(existing_games_data))) return existing_games_data
def get_game_data(game_name): game_data = None connection = pymysql.connect(host=host, port=port, user=user, passwd=password, db=db_schema, charset='utf8') cursor = connection.cursor() cursor.execute('SELECT * FROM Games WHERE Name = "' + game_name + '"') data = cursor.fetchone() if data: # (game.game_name, game.game_link, game.value, game.steam_score, game.num_of_reviews) game_data = GameData(game_name, data[1], data[2], steam_score=data[3], num_of_reviews=data[4]) cursor.close() connection.close() return game_data
def update_all_db_games_data(): #Load all games from DB games = MySqlConnector.get_all_games() #Go over all games, and update their data changed_games = [] removed_games = [] for game_data in games: new_game_data = GameData(game_data.game_name, game_data.game_link, game_data.value) update_game_data(new_game_data) if game_data.num_of_reviews == -1 and game_data.steam_score == -1 and game_data.equals(new_game_data): removed_games.append(new_game_data) elif not game_data.equals(new_game_data): changed_games.append(new_game_data) # Save changed games to the DB if changed_games: MySqlConnector.update_existing_games(changed_games) # Delete from DB Games with no available data if removed_games: MySqlConnector.remove_games(removed_games)
def get_group_giveaways(group_webpage, cookies, existing_giveaways=None, force_full_run=False, start_date=None, end_date=None): if existing_giveaways is None: existing_giveaways = dict() LogUtils.log_info('Starting to process giveaways for group ' + group_webpage) group_giveaways = dict() games = dict() reached_end = False giveaways_changed = True reached_ended_giveaways = False page_index = 1 while not reached_end and (giveaways_changed or not reached_ended_giveaways or force_full_run): giveaways_changed = False reached_ended_giveaways = False giveaways_page_url = group_webpage + SteamGiftsConsts.STEAMGIFTS_SEARCH_PAGE + str( page_index) html_content = WebUtils.get_html_page(giveaways_page_url) if html_content is None: LogUtils.log_error('Cannot process page: ' + giveaways_page_url) break current_page_num = WebUtils.get_item_by_xpath( html_content, u'.//a[@class="is-selected"]/span/text()') if current_page_num and current_page_num != str(page_index): break if current_page_num: LogUtils.log_info('Processing giveaways page ' + get_page_num_str(current_page_num)) giveaway_elements = WebUtils.get_items_by_xpath( html_content, u'.//div[@class="giveaway__summary"]') if end_date: earliest_end_time = datetime.utcfromtimestamp( StringUtils.normalize_float( WebUtils.get_items_by_xpath( giveaway_elements[-1], u'.//span/@data-timestamp')[0])) if earliest_end_time and earliest_end_time > datetime.strptime( end_date, '%Y-%m-%d'): page_index += 1 continue for giveaway_elem in giveaway_elements: giveaway_not_started_yet = False for end_time_text in WebUtils.get_items_by_xpath( giveaway_elem, u'.//div/text()'): if end_time_text == ' Begins in ': giveaway_not_started_yet = True partial_giveaway_link = WebUtils.get_item_by_xpath( giveaway_elem, u'.//a[@class="giveaway__heading__name"]/@href') giveaway_link = SteamGiftsConsts.get_giveaway_link( partial_giveaway_link) LogUtils.log_info('Processing ' + giveaway_link) game_name = WebUtils.get_item_by_xpath( giveaway_elem, u'.//a[@class="giveaway__heading__name"]/text()', default='').encode('utf-8') winners = WebUtils.get_items_by_xpath( giveaway_elem, u'.//div[@class="giveaway__column--positive"]/a/text()') poster = WebUtils.get_item_by_xpath( giveaway_elem, u'.//a[@class="giveaway__username"]/text()') timestamps = WebUtils.get_items_by_xpath( giveaway_elem, u'.//span/@data-timestamp') creation_time = None end_time = None if len(timestamps) >= 2: if giveaway_not_started_yet: creation_time = datetime.utcfromtimestamp( StringUtils.normalize_float(timestamps[0])) else: end_time = datetime.utcfromtimestamp( StringUtils.normalize_float(timestamps[0])) creation_time = datetime.utcfromtimestamp( StringUtils.normalize_float(timestamps[1])) if winners: reached_ended_giveaways = True if len(winners) >= 3: winners_content = WebUtils.get_html_page( SteamGiftsConsts.get_giveaway_winners_link( partial_giveaway_link), cookies=cookies) if winners_content is not None: error_message = WebUtils.get_item_by_xpath( winners_content, u'.//div[@class="page__heading__breadcrumbs"]/text()') if not error_message or error_message != 'Error': current_winners_page_num = WebUtils.get_item_by_xpath( winners_content, u'.//a[@class="is-selected"]/span/text()') LogUtils.log_info( 'Processing ' + giveaway_link + ' winners page ' + get_page_num_str(current_winners_page_num)) winners.extend( WebUtils.get_items_by_xpath( winners_content, u'.//p[@class="table__column__heading"]/a/text()' )) if current_winners_page_num: winners_page_index = 2 while True: winners_url = SteamGiftsConsts.get_giveaway_winners_link( partial_giveaway_link ) + SteamGiftsConsts.STEAMGIFTS_SEARCH_PAGE + str( winners_page_index) winners_content = WebUtils.get_html_page( winners_url, cookies=cookies) if winners_content is None: LogUtils.log_error( 'Cannot process page: ' + winners_url) break current_winners_page_num = WebUtils.get_item_by_xpath( winners_content, u'.//a[@class="is-selected"]/span/text()') if current_winners_page_num and current_winners_page_num != str( winners_page_index): break LogUtils.log_info( 'Processing ' + giveaway_link + ' winners page ' + get_page_num_str(current_winners_page_num)) winners.extend( WebUtils.get_items_by_xpath( winners_content, u'.//p[@class="table__column__heading"]/a/text()' )) winners_page_index += 1 giveaway_entries = dict() entries_content = None if not giveaway_not_started_yet: entries_content = WebUtils.get_html_page( SteamGiftsConsts.get_giveaway_entries_link( partial_giveaway_link), cookies=cookies) if entries_content is not None: error_message = WebUtils.get_item_by_xpath( entries_content, u'.//div[@class="page__heading__breadcrumbs"]/text()') if not error_message or error_message != 'Error': current_entries_page_num = WebUtils.get_item_by_xpath( entries_content, u'.//a[@class="is-selected"]/span/text()') LogUtils.log_info( 'Processing ' + giveaway_link + ' entries page ' + get_page_num_str(current_entries_page_num)) process_entries(entries_content, giveaway_entries, winners) if current_entries_page_num: entries_page_index = 2 while True: entries_url = SteamGiftsConsts.get_giveaway_entries_link( partial_giveaway_link ) + SteamGiftsConsts.STEAMGIFTS_SEARCH_PAGE + str( entries_page_index) entries_content = WebUtils.get_html_page( entries_url, cookies=cookies) if entries_content is None: LogUtils.log_error('Cannot process page: ' + entries_url) break current_entries_page_num = WebUtils.get_item_by_xpath( entries_content, u'.//a[@class="is-selected"]/span/text()') if current_entries_page_num and current_entries_page_num != str( entries_page_index): break LogUtils.log_info( 'Processing ' + giveaway_link + ' entries page ' + get_page_num_str(current_entries_page_num)) process_entries(entries_content, giveaway_entries, winners) entries_page_index += 1 else: LogUtils.log_warning('Unable to process entries for ' + giveaway_link) # We can't access the GA data, but we can still know who won for entry_user in winners: giveaway_entries[entry_user] = GiveawayEntry( entry_user, datetime.utcfromtimestamp(0), winner=True) giveaway_groups = [] giveaway_groups_content = WebUtils.get_html_page( SteamGiftsConsts.get_giveaway_groups_link( partial_giveaway_link), cookies=cookies) if giveaway_groups_content is not None: giveaway_groups = WebUtils.get_items_by_xpath( giveaway_groups_content, u'.//a[@class="table__column__heading"]/@href') else: LogUtils.log_warning('Unable to process groups for ' + giveaway_link) group_giveaway = GroupGiveaway(giveaway_link, game_name, poster, creation_time, end_time, giveaway_entries, giveaway_groups) steam_game_link = WebUtils.get_item_by_xpath( giveaway_elem, u'.//a[@class="giveaway__icon"]/@href') game_value = float( WebUtils.get_items_by_xpath( giveaway_elem, u'.//span[@class="giveaway__heading__thin"]/text()')[-1] [1:-2]) games[game_name] = GameData(game_name, steam_game_link, game_value) if giveaway_link not in existing_giveaways or not group_giveaway.equals( existing_giveaways[giveaway_link]): giveaways_changed = True if giveaway_link in existing_giveaways and existing_giveaways[ giveaway_link].start_time != group_giveaway.start_time: group_giveaway.start_time = existing_giveaways[ giveaway_link].start_time group_giveaways[giveaway_link] = group_giveaway if start_date: if end_time and end_time < datetime.strptime( start_date, '%Y-%m-%d'): reached_end = True break if not current_page_num or reached_end: break page_index += 1 LogUtils.log_info('Finished processing giveaways for group ' + group_webpage) return group_giveaways, games