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)
