def get_new_players(): ''' Obtiene los fichajes y ventas de la liga ''' session = requests.session() session.get('http://stats.comunio.es/transfers.php', headers=headers) soup = bs4(session.get('http://stats.comunio.es/transfers.php', headers=headers).content) new_members = True for table in soup.find_all('table', {'class': 'rangliste'}): if new_members: for row in table.find_all('tr', re.compile(r"r[1-2]"))[1:]: nuna = re.search("([0-9]+)-(.*)", row.find('a', {'class': 'nowrap'})['href']) number = nuna.group(1) name = nuna.group(2).replace("+", " ").strip() club = row.find('td', {'class': 'clubPic'}).a['href'] club_id = re.search("([0-9]+)-(.*)", club).group(1) club_name = re.search("([0-9]+)-(.*)", club).group(2).replace("+", " ") position = _position_translation(row.contents[6].text) db.commit_query('INSERT IGNORE INTO players (idp, name, position, idcl) VALUES (%s, "%s", %s, %s)' % ( number, name, position, club_id)) get_all_prices(name, incremental=True) print 'Alta jugador %s (%s) en el club %s (%s) como %s (%s)' % ( name, number, club_name, club_id, row.contents[6].text, position) new_members = False else: for row in table.find_all('tr', re.compile(r"r[1-2]"))[1:]: nuna = re.search("([0-9]+)-(.*)", row.find('a', {'class': 'nowrap'})['href']) number = nuna.group(1) name = nuna.group(2).replace("+", " ").strip() club = row.find('td', {'class': 'clubPic'}).a['href'] club_id = re.search("([0-9]+)-(.*)", club).group(1) club_name = re.search("([0-9]+)-(.*)", club).group(2).replace("+", " ") db.commit_query('UPDATE players SET idcl=NULL WHERE idp=%s' % (number)) print 'Baja jugador %s (%s) del club %s (%s)' % (name, number, club_name, club_id)
def set_new_player(player_id, playername, position, team_id): """ Set new player in the database. :param player_id: :param playername: :param position: :param team_id: """ db.commit_query('INSERT IGNORE INTO players (idp,name,position,idcl) VALUES (%s,"%s","%s",%s)' % (player_id, playername, position, team_id))
def _get_gameday(n_jornada): idg = db.simple_query('SELECT idg FROM gamedays WHERE gameday="%s" AND season="%s"' % (n_jornada, SEASON)) try: idg = idg[0][0] except IndexError: db.commit_query('INSERT INTO gamedays (gameday,season) VALUES ("%s","%s")' % (n_jornada, SEASON)) idg = \ db.simple_query('SELECT idg FROM gamedays WHERE gameday="%s" AND season="%s" LIMIT 1' % (n_jornada, SEASON))[0][ 0] return idg
def set_transactions(): """ Save to database all the transactions. """ print 'Updating transactions =>', last_transaction = db.simple_query('SELECT MAX(date) FROM transactions')[0][0] if last_transaction: until_date = last_transaction - timedelta(days=10) else: until_date = date.today() - timedelta(days=10) news = com.get_news(until_date) for new in news: ndate, title, text = new if 'Fichajes' not in title: continue pattern = re.compile( ur'(?:(?:\\n)?([(\S+ )]+?)(?: cambia por )([0-9\.,]*?)(?: .*? de )(.+?) a (.+?)\.)', re.UNICODE) transactions = re.findall(pattern, text) for trans in transactions: playername, value, fr, to = trans value = int(value.replace('.', '')) playername = playername.strip() try: player_id = db.simple_query('SELECT idp FROM players WHERE name LIKE "%%%s%%"' % playername)[0][0] if 'Computer' in fr: kind = 'Buy' user_id = db.simple_query('SELECT idu FROM users WHERE name LIKE "%%%s%%"' % to)[0][0] db.commit_query( 'INSERT IGNORE INTO transactions (idp, idu, type, price, date) VALUES (%s,%s,"%s",%s,"%s")' % (player_id, user_id, kind, value, ndate)) elif 'Computer' in to: kind = 'Sell' user_id = db.simple_query('SELECT idu FROM users WHERE name LIKE "%%%s%%"' % fr)[0][0] db.commit_query( 'INSERT IGNORE INTO transactions (idp, idu, type, price, date) VALUES (%s,%s,"%s",%s,"%s")' % (player_id, user_id, kind, value, ndate)) else: kind = 'Buy' user_id = db.simple_query('SELECT idu FROM users WHERE name LIKE "%%%s%%"' % to)[0][0] db.commit_query( 'INSERT IGNORE INTO transactions (idp, idu, type, price, date) VALUES (%s,%s,"%s",%s,"%s")' % (player_id, user_id, kind, value, ndate)) user_id = db.simple_query('SELECT idu FROM users WHERE name LIKE "%%%s%%"' % fr)[0][0] kind = 'Sell' db.commit_query( 'INSERT IGNORE INTO transactions (idp, idu, type, price, date) VALUES (%s,%s,"%s",%s,"%s")' % (player_id, user_id, kind, value, ndate)) except IndexError: # Player selled before having in database pass print '%sdone%s.' % (GREEN, ENDC)
def get_all_players(): ''' Get all players ''' req = requests.get('http://stats.comunio.es/squad', headers=headers).content soup = bs4(req) clubs = soup.find('td', {'class': 'clubPics'}).find_all('a') for club in clubs: link = club['href'] club_num = re.search("([0-9]+)-(.*)", link).group(1) club_name = re.search("([0-9]+)-(.*)", link).group(2).replace("+", " ") db.commit_query('INSERT IGNORE INTO clubs (idcl, name) VALUES (%s, "%s")' % (club_num, club_name)) soup = bs4(requests.get('http://stats.comunio.es' + link, headers=headers).content) for row in soup.find('table', {'class': 'rangliste'}).find_all('tr', re.compile(r"r[1-2]"))[1:-1]: nuna = re.search("([0-9]+)-(.*)", row.find('a', {'class': 'nowrap'})['href']) number = nuna.group(1) name = nuna.group(2).replace("+", " ").strip() position = _position_translation(row.contents[5].text) db.nocommit_query(' INSERT INTO players (idp, name, position, idcl) VALUES (%s, "%s", %s, %s) ' % ( number, name, position, club_num)) db.commit()
def get_matches(incremental=True): ''' Obtiene todos los partidos del calendario''' calendario = bs4(requests.get(CALENDARIO, headers=headers).content) for jornada in calendario.find_all('div', class_='fecha-jor-cal'): n_jornada = re.search("Jornada ([0-9]+)", jornada.p.text).group(1) id_gameday = _get_gameday(n_jornada) for match in jornada.find_all('tr', itemtype='http://schema.org/SportsEvent'): clubs = match.find_all('td', itemtype='http://schema.org/SportsTeam') left_team = clubs[0].text.strip().split()[-1] left_team = db.simple_query('SELECT idcl FROM clubs WHERE name LIKE "%%%s%%" LIMIT 1' % left_team)[0][0] right_team = clubs[1].text.strip().split()[-1] right_team = db.simple_query('SELECT idcl FROM clubs WHERE name LIKE "%%%s%%" LIMIT 1' % right_team)[0][0] dia, hora = _translate_xml_date(match.time['content']) db.commit_query('INSERT INTO matches (idclh,idcla,idg,date,time) VALUES (%s,%s,%s,"%s","%s") \ ON DUPLICATE KEY UPDATE date="%s",time="%s" ' % ( left_team, right_team, id_gameday, dia, hora, dia, hora)) print "Jornada %s guardada (idg:%s)" % (n_jornada, id_gameday)
def main(): parser = argparse.ArgumentParser(description='Helps you to play in Comunio.') parser.add_argument('-i', '--init', action='store_true', dest='init', help='Initialize the database with users, clubs, players and transactions.') parser.add_argument('-u', '--update', action='store_true', dest='update', help='Update all data of all players and users.') parser.add_argument('-b', '--buy', action='store_true', dest='buy', help='Check all the players to buy.') parser.add_argument('-s', '--sell', action='store_true', dest='sell', help='Players that you should sell.') parser.add_argument('-m', '--mail', action='store_true', dest='mail', help='Send email with the results.') args = parser.parse_args() sleep(1) if not com.logged: print "Not logged in Comunio, existing." exit(0) # INIT if args.init: print '\n[*] Initializing the database.' if db.rowcount('SELECT * FROM users'): res = raw_input( '\tDatabase contains data, %sdo you want to remove%s it and load data again? (y/n) ' % (RED, ENDC)) if res == 'y': db.commit_query('SET FOREIGN_KEY_CHECKS=0;') queries = db.simple_query('SELECT Concat("DELETE FROM ",table_schema,".",TABLE_NAME, " WHERE 1;") \ FROM INFORMATION_SCHEMA.TABLES WHERE table_schema in ("tradunio");') for query in queries: print query[0], db.commit_query(query[0]) print '%sdone%s.' % (GREEN, ENDC) db.commit_query('SET FOREIGN_KEY_CHECKS=1;') else: print "\tExecution aborted." exit(0) users = set_users_data() set_transactions() for user_id in users: username, points, teamvalue, money, maxbid = users[user_id] players = set_user_players(user_id, username) for player in players: player_id, playername, club_id, clubname, value, points, position = player set_player_data(player_id=player_id, playername=playername) # UPDATE if args.update: print '\n[*] Updating money, team value, save players, prices and transactions.' users = set_users_data() set_transactions() max_players_text = '' for user_id in users: username, userpoints, teamvalue, money, maxbid = users[user_id] players = set_user_players(user_id, username) for player in players: player_id, playername, club_id, clubname, value, points, position = player if club_id == 25: # Player is not in Primera División continue set_player_data(player_id=player_id, playername=playername) if user_id == com.get_myid(): _ = [player.pop(0) for player in players] _ = [player.pop(1) for player in players] if args.mail: if len(players) < max_players - 4: num_players = '<font color="%s">%s</font>' % (GREEN_HTML, len(players)) elif len(players) < max_players - 2: num_players = '<font color="%s">%s</font>' % (YELLOW_HTML, len(players)) else: num_players = '<font color="%s">%s</font>' % (RED_HTML, len(players)) text = 'User: %s #Players: %s<br/>' % (username, num_players) text += u'Teamvalue: %s € - Money: %s € - Max bid: %s € - Points: %s<br/>' % ( format(teamvalue, ",d"), format(money, ",d"), format(maxbid, ",d"), format(userpoints, ",d")) text = text.encode('utf8') headers = ['Name', 'Club', 'Value', 'Points', 'Position'] text += tabulate(players, headers, tablefmt="html", numalign="right", floatfmt=",.0f").encode( 'utf8') send_email(fr_email, to_email, 'Tradunio update %s' % today, text) else: print_user_data(username, teamvalue, money, maxbid, userpoints, players) if len(players) > max_players: max_players_text += 'User %s has reached the max players allowed with #%s<br/>'\ % (username, len(players)) if max_players_text: send_email(fr_email, admin_email, 'User max players reached %s' % today, max_players_text) # BUY if args.buy: print '\n[*] Checking players to buy in the market.' max_gameday = db.simple_query('SELECT MAX(gameday) from points')[0][0] players_on_sale = sorted(com.players_onsale(com.community_id, only_computer=False), key=itemgetter(2), reverse=True) gamedays = [('%3s' % gameday) for gameday in range(max_gameday - 4, max_gameday + 1)] bids = check_bids_offers(kind='bids') table = list() for player in players_on_sale: player_id, playername, team_id, team, min_price, market_price, points, dat, owner, position = player to_buy = colorize_boolean(check_buy(player_id, playername, min_price, market_price)) last_points = db.simple_query( 'SELECT p.gameday,p.points \ FROM players pl INNER JOIN points p ON p.idp=pl.idp AND pl.idp = "%s" \ ORDER BY p.gameday DESC LIMIT 5' % player_id)[::-1] if not last_points and not db.rowcount('SELECT idp FROM players WHERE idp = %s' % player_id): set_new_player(player_id, playername, position, team_id) _, last_points = set_player_data(player_id=player_id, playername=playername) last_points = last_points[-5:] elif not last_points: _, last_points = set_player_data(player_id=player_id, playername=playername) last_points = last_points[-5:] elif team_id == 25: continue streak = sum([int(x[1]) for x in last_points]) last_points = {gameday: points for (gameday, points) in last_points} last_points_array = list() for gameday in range(max_gameday - 4, max_gameday + 1): points = last_points.get(gameday, 0) points = colorize_points(points) last_points_array.append(points) prices = db.simple_query( 'SELECT p.date,p.price \ FROM players pl INNER JOIN prices p ON p.idp=pl.idp AND pl.idp = "%s" \ ORDER BY p.date ASC' % player_id) day, week, month = 0, 0, 0 try: day = colorize_profit(calculate_profit(prices[-2][1], market_price)) week = colorize_profit(calculate_profit(prices[-8][1], market_price)) month = colorize_profit(calculate_profit(prices[-30][1], market_price)) except IndexError: pass bid, extra_price = 0.0, colorize_profit(0.0) if player_id in bids: bid = bids[player_id][2] extra_price = colorize_profit(bids[player_id][3]) table.append([playername, position, to_buy, owner, month, week, day, market_price, min_price, bid, extra_price, ' '.join(last_points_array), streak]) headers = ['Name', 'Position', 'To Buy?', 'Owner', 'Month ago', 'Week ago', 'Day ago', 'Mkt. price', 'Min. price', 'Bid', 'Extra', ' '.join(gamedays), 'Streak'] table = sorted(table, key=itemgetter(12), reverse=True) if args.mail: text = tabulate(table, headers, tablefmt="html", numalign="right", floatfmt=",.0f").encode('utf8') send_email(fr_email, to_email, 'Tradunio players to buy %s' % today, text) else: print tabulate(table, headers, tablefmt="psql", numalign="right", floatfmt=",.0f") # SELL if args.sell: print '\n[*] Checking players to sell.' max_gameday = db.simple_query('SELECT MAX(gameday) from points')[0][0] gamedays = [('%3s' % gameday) for gameday in range(max_gameday - 4, max_gameday + 1)] console, table = list(), list() players = get_user_players(user_id=com.myid) offers = check_bids_offers(kind='offers') for player in players: player_id, playername, club_id, club_name, position = player bought_date, bought_price, market_price, to_sell, profit = check_sell(player_id) last_points = db.simple_query( 'SELECT p.gameday,p.points \ FROM players pl INNER JOIN points p ON p.idp=pl.idp AND pl.idp = "%s" \ ORDER BY p.gameday DESC LIMIT 5' % player_id)[::-1] if not last_points and not db.rowcount('SELECT idp FROM players WHERE idp = %s' % player_id): set_new_player(player_id, playername, position, club_id) _, last_points = set_player_data(player_id=player_id, playername=playername) last_points = last_points[-5:] elif not last_points: _, last_points = set_player_data(player_id=player_id, playername=playername) last_points = last_points[-5:] streak = sum([int(x[1]) for x in last_points]) last_points = {gameday: points for (gameday, points) in last_points} last_points_array = list() for gameday in range(max_gameday - 4, max_gameday + 1): points = last_points.get(gameday, 0) points = colorize_points(points) last_points_array.append(points) prices = db.simple_query( 'SELECT p.date,p.price \ FROM players pl INNER JOIN prices p ON p.idp=pl.idp AND pl.idp = "%s" \ ORDER BY p.date ASC' % player_id) day, week, month = 0, 0, 0 try: day = colorize_profit(calculate_profit(prices[-2][1], market_price)) week = colorize_profit(calculate_profit(prices[-8][1], market_price)) month = colorize_profit(calculate_profit(prices[-30][1], market_price)) except IndexError: pass to_sell = colorize_boolean(to_sell) profit_color = colorize_profit(profit) offer, extra_price, who = 0.0, colorize_profit(0.0), '-' if player_id in offers: who = offers[player_id][1] offer = offers[player_id][2] extra_price = colorize_profit(offers[player_id][3]) table.append( [playername, position, to_sell, bought_date, month, week, day, ' '.join(last_points_array), streak, bought_price, market_price, profit_color, offer, who, extra_price]) table = sorted(table, key=itemgetter(8), reverse=True) headers = ['Name', 'Position', 'To sell?', 'Purchase date', 'Month ago', 'Week ago', 'Day ago', ' '.join(gamedays), 'Streak', 'Purchase price', 'Mkt price', 'Profit', 'Offer', 'Who', 'Profit'] if args.mail: text = tabulate(table, headers, tablefmt="html", numalign="right", floatfmt=",.0f").encode('utf8') send_email(fr_email, to_email, 'Tradunio players to sell %s' % today, text) else: print tabulate(table, headers, tablefmt="psql", numalign="right", floatfmt=",.0f") com.logout() db.close_connection()
def get_points(name=None, incremental=True): ''' Obtenemos todos los puntos de la jornada actual ''' jornadas = list() if not incremental: jornada = JORNADA_VARIABLE for index in range(1, 39): jornadas.append(jornada % index) else: jornadas.append(JORNADA_ACTUAL) for jornada_actual in jornadas: soup = bs4(requests.get(jornada_actual, headers=headers).content) n_jornada = soup.find('h3', class_='tit-module-internal s-m-b').text[-2:].strip() id_gameday = _get_gameday(n_jornada) for match in soup.find_all('a', class_='resultado resul_post'): partido = dict() match_link = match['href'] if 'resultados.as.com' not in match_link: match_link = URL_AS + match_link web_match = bs4(requests.get(match_link, headers=headers).content) # Nos quedamos unicamente con la parte derecha del nombre del equipo team_left = web_match.find('h2', class_='s-Ltext-nc rlt-title gap-3 s-left').text.strip().split()[-1] team_right = web_match.find('h2', class_='s-Ltext-nc rlt-title gap-3 s-right').text.strip().split()[-1] # Recorremos jugadores TitHome,ResHome,TitAway,ResAway for jugadores in ['gap-3 s-left', 'gap-3 s-left s-mm-t', 'gap-3 s-right', 'gap-3 s-right s-mm-t']: equipo = team_left if 'right' in jugadores: equipo = team_right for team in web_match.find_all('li', class_=jugadores): for player in team.find_all('div', class_='cf'): name = player.p.text.strip() picas = player.div.text.count('punto') if picas == 0 and player.div.text.count('-') == 0: picas = -1 partido[name] = {'picas': picas, 'goles': 0, 'penalty': 0, 'd_amarilla': 0, 'roja': 0, 'equipo': equipo} # Recogemos los goles for lineas in web_match.find_all('section', class_='fld-ftr-section')[1:2]: for linea in lineas.find_all('li'): name = linea.find('p', class_='s-inb-sm s-stext-nc s-stext-link-2').text.strip().replace("\n(p)", "") gol = len(linea.strong) if linea.text.count('(p)'): partido[name].update({'penalty': partido[name]['penalty'] + gol}) elif '(p.p.)' not in linea.text: partido[name].update({'goles': partido[name]['goles'] + gol}) # Buscamos los expulsados por roja directa o doble amarilla for lineas in re.findall('Expulsado:(.*)', web_match.text): try: name = lineas.strip() for red in web_match.find_all('strong', class_='cmt-red'): if red.parent.text.title().count(name) and len( re.findall("[Aa][Mm][Aa][Rr][Ii][Ll][Ll][Aa]", red.parent.text)) and not partido[name][ 'roja']: partido[name].update({'d_amarilla': 1}) elif red.parent.text.title().count(name) and not partido[name]['d_amarilla']: partido[name].update({'roja': 1}) except: continue # Procesamos lo recogido for name, values in partido.items(): try: search_name = _check_exceptions(name, partido) player = db.simple_query( 'SELECT pl.idp,pl.name,pl.position,cl.name FROM players pl, clubs cl WHERE cl.idcl=pl.idcl AND pl.name REGEXP ".*%s.*" AND cl.name LIKE "%%%s%%" ' % ( search_name, values['equipo'])) if len(player) != 1: player = db.simple_query( 'SELECT pl.idp,pl.name,pl.position,cl.name FROM players pl, clubs cl WHERE cl.idcl=pl.idcl AND pl.name REGEXP "%s" AND cl.name LIKE "%%%s%%" ' % ( search_name, values['equipo'])) if len(player) != 1: player = db.simple_query( 'SELECT pl.idp,pl.name,pl.position,cl.name FROM players pl, clubs cl WHERE cl.idcl=pl.idcl AND pl.name = "%s" AND cl.name LIKE "%%%s%%" ' % ( search_name, values['equipo'])) if len(player) != 1: # Pablo Hernández es "Hernández" en comunio player = db.simple_query( 'SELECT pl.idp,pl.name,pl.position,cl.name FROM players pl, clubs cl WHERE cl.idcl=pl.idcl AND pl.name REGEXP "%s" AND cl.name LIKE "%%%s%%" ' % ( search_name.split(" ")[1], values['equipo'])) if len(player) != 1: player = db.simple_query( 'SELECT pl.idp,pl.name,pl.position,cl.name FROM players pl, clubs cl WHERE cl.idcl=pl.idcl AND pl.name = "%s" AND cl.name LIKE "%%%s%%" ' % ( search_name.split(" ")[0], values['equipo'])) if len(player) != 1: print "No se ha podido recuperar de la base de datos el nombre del AS '%s' del '%s' " % ( name, values['equipo']) continue points = _compute_points(position=player[0][2], partido=values) db.commit_query('INSERT IGNORE INTO points (idp,idg,points) VALUES (%s,%s,%s)' % ( player[0][0], id_gameday, points)) except IndexError as e: print "No se ha podido recuperar de la base de datos el nombre del AS '%s' del '%s' " % ( name, values['equipo']) except Exception as e: print 'Error:', e