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
