def etl_import_sackmann(year): con_postgres, meta = connect() path = atp_path sackmann_starting_year = year sackmann_player_path = path + "atp_players.csv" sackmann_atp_matchs = path + "atp_matches_{}.csv" sackmann_chall_matchs = path + "atp_matches_qual_chall_{}.csv" sackmann_futures_matchs = path + "atp_matches_futures_{}.csv" ## Testing etl_import_sackmann_player(con_postgres, sackmann_player_path) etl_import_sackmann_matchlist(con_postgres, sackmann_starting_year, sackmann_atp_matchs, sackmann_chall_matchs, sackmann_futures_matchs)
def etl_te_get_missing_players(): con, meta = connect() # conn = sqlite3.connect('te_data.db') # # cursor = conn.cursor() # cursor.execute('''TRUNCATE TABLE tmp_te_player;''') # conn.commit() players = con.execute( 'SELECT te_link FROM public.tbl_player where te_link is not null and plays is null;' ) for link_list in players: link = link_list[0] #print(link) link = link.replace('â', 'a') df = get_te_player(link) store_player_to_database(df)
def searchSurebet(): """ Search for open events with more then one matching bookie Args: - Returns: - """ con, meta = connect() tbl_surebet = meta.tables['tbl_surebet'] events = con.execute( 'Select event_id from tbl_events WHERE pinnacle_event_id is not null and betbtc_event_id is not null and "StartDateTime" >= now()' ) for event in events: log.debug(event[0]) searchSurebetEvent(event[0], tbl_surebet) log.info("no more events for a surebet")
def etl_te_get_matchesdetails(): con, meta = connect() conn = sqlite3.connect('te_data.db') sql = """ SELECT te_link FROM public.tbl_match where surface is null; """ match_list = con.execute(sql) i = 0 for match in match_list: link = match[0] df = get_te_match(link) store_matches_to_database(df, conn) i = i + 1 if i % 100 == 0: print(i, "done")
def etl_te_get_matchesdetails_all(): con, meta = connect() conn = sqlite3.connect('te_data.db') sql = """ SELECT te_link, "MatchDate" FROM public.tbl_match where "MatchDate" > '2017-01-01'::date """ match_list = con.execute(sql) i = 0 for match in match_list: link = match[0] df = get_te_match(link) store_matches_to_database(df, conn) i = i + 1 if i % 100 == 0: print(i, "done")
def searchLayBetOffer(): concurrent_open_bets = cfg['laybet']['concurrent_open_bets'] con, meta = connect() #tbl_surebet = meta.tables['tbl_surebet'] open_offers = con.execute( 'SELECT count (offer_id) FROM tbl_offer WHERE status = 1').fetchone() open_bets = open_offers[0] log.info("Number of open offers " + str(open_bets)) if open_bets < concurrent_open_bets: odds = con.execute( 'Select odds_id from tbl_odds o INNER JOIN tbl_events e using(event_id) WHERE e.pinnacle_event_id is not null and e.betbtc_event_id is not null and e."StartDateTime" >= now() and o.bookie_id = 1 and odds_id not in (select hedge_odds_id from tbl_offer where status = 1)' ) for odds_id in odds: if open_bets >= concurrent_open_bets: break log.info(odds_id[0]) placeLayBet(odds_id[0]) open_offers = con.execute( 'SELECT count (offer_id) FROM tbl_offer WHERE status = 1' ).fetchone() open_bets = open_offers[0] log.info("Number of bet places " + str(open_bets)) log.info("no more laybet offer")
def etl_import_te_ranking(conn_sqllite3, con_postgres, days=10000): print("Load Tennis Explorer ranking list") conn_sqllite3 = sqlite3.connect('te_data_ranking.db') con_postgres, meta = connect() try: df_ranking = pd.read_sql( 'select * from tmp_te_ranking where "StartDate" > date("now","-' + str(days) + ' days") ', conn_sqllite3) except: return None df_ranking = df_ranking.drop_duplicates() df_ranking = df_ranking.drop('index', axis=1) df_ranking['StartDate'] = pd.to_datetime(df_ranking.StartDate) df_ranking['StartDate'] = pd.to_datetime(df_ranking.StartDate) df_ranking['rank'] = df_ranking['rank'].str[:-1] df_ranking['rank'] = df_ranking['rank'].astype('int') print("Store Tennis Explorer ranking list") df_ranking.to_sql('tbl_te_ranking', con_postgres, if_exists='replace')
# -*- coding: utf-8 -*- import pandas as pd import math import sqlite3 from sqlalchemy import create_engine, MetaData, select from comeon_common import connect from datetime import datetime, timedelta #from comeon_common import connet conn, meta = connect() #import argparse #parser = argparse.ArgumentParser(description='Calculate SwissTennis Model') # ##parser.add_argument('--start_value', dest='accumulate', action='store_const', ## const=sum, default=max, ## help='sum the integers (default: find the max)') #parser.add_argument('--start_value', type=float, default=1, help='Start Value for the model') #parser.add_argument('--days', type=int, default=7, help='Days between runs') #parser.add_argument('--weeks_back', type=int, default=52, help='How many weeks back') #parser.add_argument('--name', type=str, default="SW1YALL", help='Name of the Model') #parser.add_argument('--startDate', type=str, default="01/01/01", help='Model Start Date') # # ##parser.print_help() #args = parser.parse_args() ### Parameters #print(args)
# -*- coding: utf-8 -*- """ Created on Sun Oct 15 17:08:25 2017 @author: chrhae """ import pandas as pd import numpy as np from sqlalchemy import create_engine, MetaData, select, update from datetime import datetime from comeon_common import connect import psycopg2 from sqlalchemy.dialects.postgresql import insert con_postgres, meta = connect() tbl_player = meta.tables['tbl_player'] tbl_match = meta.tables['tbl_match'] def updateSackmannPlayer(row): dt = datetime.now() #print("update player", row['firstname'], row['lastname'] ) ## searching for existing player in the table: #print("Select player_id from tbl_player WHERE firstname = '" + row['firstname'] +"' AND lastname = '" + row['lastname'] +"' ") player_id = con_postgres.execute( "Select player_id from tbl_player WHERE firstname = '" + row['firstname'] + "' AND lastname = '" + row['lastname'] + "' ").fetchone()
def etl_import_ta(): conn_sqllite3 = sqlite3.connect('ta_data.db') con_postgres, meta = connect() etl_import_ta_current(conn_sqllite3, con_postgres)
def etl_transform_te_matchdetails(): con_postgres, meta = connect() transform_te_matchdetail(con_postgres)
def searchSurebetEvent(event_id, tbl_surebet): """ Search for a surebet event and if found, place it Args: event_id (int): The event, on which the surebet is placed tbl_surebet (table): the table object for the tbl_surebet Returns: True: if successful False: if there was an error """ dt = datetime.now() con, meta = connect() tbl_odds = meta.tables['tbl_odds'] #event_id = 362 surebet_numbers = 0 bettyps = [1] stake_total = cfg['surebet']['stake_total'] margin = cfg['surebet']['margin'] high_risk_margin = cfg['surebet']['high_risk_margin'] betbtc_margin = cfg['surebet']['betbtc_margin'] #ways = [1,2] bookies = [1, 2] # Searching for Back surebets for bettyp in bettyps: for bookie in bookies: h = select([tbl_odds.columns.odds, tbl_odds.columns.odds_id, tbl_odds.columns.max_stake])\ .where(tbl_odds.columns.event_id == event_id)\ .where(tbl_odds.columns.bettyp_id == bettyp)\ .where(tbl_odds.columns.bookie_id == bookie)\ .where(tbl_odds.columns.backlay == 1)\ .where(tbl_odds.columns.way == 1) h_odd = con.execute(h).fetchone() if h_odd == None or np.isnan(h_odd[0]): continue for check_bookie in bookies: a = select([tbl_odds.columns.odds, tbl_odds.columns.odds_id, tbl_odds.columns.max_stake])\ .where(tbl_odds.columns.event_id == event_id)\ .where(tbl_odds.columns.bettyp_id == bettyp)\ .where(tbl_odds.columns.bookie_id == check_bookie)\ .where(tbl_odds.columns.backlay == 1)\ .where(tbl_odds.columns.way == 2) a_odd = con.execute(a).fetchone() if a_odd == None or np.isnan(a_odd[0]): continue home_odds = h_odd[0] away_odds = a_odd[0] home_odds_id = h_odd[1] away_odds_id = a_odd[1] if not isinstance(home_odds, float): home_odds = np.nan if not isinstance(away_odds, float): away_odds = np.nan surebet = (1 / home_odds) + (1 / away_odds) if surebet < 1: log.info("surebet on event" + str(event_id)) log.info("home odds " + str(h_odd[0]) + " " + str(bookie)) log.info("away odds " + str(a_odd[0]) + " " + str(check_bookie)) log.info("sure bet" + str((1 - surebet) * 100)) if ((1 - surebet) * 100) > margin: home_prob = (1 / surebet) / home_odds away_prob = (1 / surebet) / away_odds home_stake = round(stake_total * home_prob, 1) away_stake = round(stake_total * away_prob, 1) home_return = home_stake * home_odds away_return = away_stake * away_odds if bookie == 2: home_stake, away_stake = checkStake( home_stake, away_stake, h_odd[2]) home_return = (home_return) * ( 1 - (betbtc_margin / 100)) if check_bookie == 2: away_stake, home_stake = checkStake( away_stake, home_stake, a_odd[2]) away_return = (away_return) * ( 1 - (betbtc_margin / 100)) log.info("home stake " + str(home_stake)) log.info("away stake " + str(away_stake)) log.info("home max btcbet stake " + str(h_odd[2])) log.info("away max btcbet stake " + str(a_odd[2])) log.info("home return " + str(home_return)) log.info("away return " + str(away_return)) log.info("home prop " + str(home_prob)) log.info("away prop " + str(away_prob)) log.info("Home Odds " + str(home_odds_id)) log.info("Away Odds " + str(away_odds_id)) stake_total = home_stake + away_stake theoretical_winnings = (home_return * home_prob) + ( away_return * away_prob) log.info("Theoretical Winnings " + str(theoretical_winnings)) if min(home_return, away_return) - (stake_total) > 0: log.info("min profit " + str( min(home_return, away_return) - (stake_total))) log.info("max profit " + str( max(home_return, away_return) - (stake_total))) surebet_sql = select([ tbl_surebet.c.event_id ]).where(tbl_surebet.columns.event_id == event_id ).where(tbl_surebet.columns.status == 1) db_surebet_id = con.execute(surebet_sql).fetchone() surebet_typ = 1 if db_surebet_id == None: clause = insert(tbl_surebet).returning(tbl_surebet.columns.surebet_id).values(event_id=event_id, \ home_bookie_id=bookie, \ away_bookie_id=check_bookie, \ home_odds=home_odds, \ away_odds=away_odds, \ min_profit=round((min(home_return, away_return) - (stake_total) ),2), \ max_profit=round((max(home_return, away_return) - (stake_total) ),2), \ status=1,\ theoretical_winnings=theoretical_winnings,\ surebet_typ=surebet_typ,\ update=dt) log.info("store to database") result = con.execute(clause) for id in result: surebet_id = id[0] log.info("Surebet ID " + str(surebet_id)) surebetStatus = placeSureBet( surebet_typ, event_id, surebet_id, home_odds_id, home_odds, home_stake, away_odds_id, away_odds, away_stake, bookie, check_bookie) log.info("SureBet place? " + str(surebetStatus)) if surebetStatus: clause = update(tbl_surebet).where( tbl_surebet.columns.surebet_id == surebet_id).values(status=2) con.execute(clause) log.warning("SureBet place in the event " + str(event_id)) else: clause = update(tbl_surebet).where( tbl_surebet.columns.surebet_id == surebet_id).values(status=6) con.execute(clause) else: log.info("already exists in the database") elif (theoretical_winnings - (stake_total)) / ( stake_total) * 100 > high_risk_margin: log.info("high risk surebet found " + str(theoretical_winnings)) log.info("min profit " + str( min(home_return, away_return) - (stake_total))) log.info("max profit " + str( max(home_return, away_return) - (stake_total))) surebet_sql = select( [tbl_surebet.c.event_id]).where( tbl_surebet.columns.event_id == event_id ).where(tbl_surebet.columns.status == 1).where( tbl_surebet.columns.surebet_typ == 2) db_surebet_id = con.execute(surebet_sql).fetchone() surebet_typ = 2 if db_surebet_id == None: clause = insert(tbl_surebet).returning(tbl_surebet.columns.surebet_id).values(event_id=event_id, \ home_bookie_id=bookie, \ away_bookie_id=check_bookie, \ home_odds=home_odds, \ away_odds=away_odds, \ min_profit=round((min(home_return, away_return) - (stake_total) ),2), \ max_profit=round((max(home_return, away_return) - (stake_total) ),2), \ status=1,\ theoretical_winnings=theoretical_winnings,\ surebet_typ=surebet_typ,\ update=dt) log.info("store to database") result = con.execute(clause) for id in result: surebet_id = id[0] log.info("Surebet ID " + str(surebet_id)) surebetStatus = placeSureBet( surebet_typ, event_id, surebet_id, home_odds_id, home_odds, home_stake, away_odds_id, away_odds, away_stake, bookie, check_bookie) log.info("SureBet place? " + str(surebetStatus)) if surebetStatus: clause = update(tbl_surebet).where( tbl_surebet.columns.surebet_id == surebet_id).values(status=2) con.execute(clause) log.warning("SureBet place in the event " + str(event_id)) else: clause = update(tbl_surebet).where( tbl_surebet.columns.surebet_id == surebet_id).values(status=6) con.execute(clause) else: log.info("already exists in the database") else: surebet_sql = select([ tbl_surebet.c.event_id ]).where(tbl_surebet.columns.event_id == event_id ).where(tbl_surebet.columns.status == 5) db_surebet_id = con.execute(surebet_sql).fetchone() log.info("No winnings after commissions !") if db_surebet_id == None: clause = insert(tbl_surebet).values(event_id=event_id, \ home_bookie_id=bookie, \ away_bookie_id=check_bookie, \ home_odds=home_odds, \ away_odds=away_odds, \ min_profit=round((min(home_return, away_return) - (stake_total) ),2), \ max_profit=round((max(home_return, away_return) - (stake_total) ),2), \ status=5,\ theoretical_winnings=theoretical_winnings,update=dt) con.execute(clause) log.info("store to database") else: log.info("already exists in the database") surebet_numbers = surebet_numbers + 1 else: log.info("sure bet to small !") if surebet_numbers == 0: log.info("no surebet found for event " + str(event_id))
def etl_import_te_weekly_ranking(days=10000): conn_sqllite3 = sqlite3.connect('te_data.db') con_postgres, meta = connect() etl_import_te_ranking(conn_sqllite3, con_postgres, days=days)
def etl_import_te_daily_matchdetails(days=10000): conn_sqllite3 = sqlite3.connect('te_data.db') con_postgres, meta = connect() etl_import_te_matchdetails(conn_sqllite3, con_postgres, days=days)
def etl_import_te(days=100): conn_sqllite3 = sqlite3.connect('te_data.db') con_postgres, meta = connect() ## Testing etl_import_te_matchlist(conn_sqllite3, con_postgres, days=days)