def save_results(self):
querystart = """INSERT INTO tbl_Results (weekID, matchID, """
queryvalues = """VALUES ("%s", "%d", """ %\
(self.weekID, self.matchID)
rnames = ""
rvalues = ""
for i in range(len(self.results)):
rnames += "r%d, " % (i + 1)
rvalues += "%d, " % self.results[i]
querystart += rnames
querystart = querystart[:-2] + ") "
queryvalues += rvalues
queryvalues = queryvalues[:-2] + ")"
query = querystart + queryvalues
try:
DB_CONN.cursor().execute(query)
DB_CONN.commit()
# if INSERT succeeds
# check all related coupons
except Exception, e:
return
def update_coupons(self):
"""
it checks all coupons related to this match
to see if they won
"""
query = """SELECT bet_index, couponID from tbl_Coupons """ +\
"""WHERE weekID=%d AND matchID=%d AND won IS NULL""" % (self.weekID, self.matchID)
x = DB_CONN.cursor()
x.execute(query)
coupon_rows = x.fetchall()
loser_coupons = []
for row in coupon_rows:
bet_index = row[0]
couponID = row[1]
won = self.results[bet_index]
query = """UPDATE tbl_Coupons SET won=%d WHERE couponID=%d AND weekID=%d AND matchID=%d""" % \
(won, couponID, self.weekID, self.matchID)
x.execute(query)
DB_CONN.commit()
if not won:
loser_coupons.append(couponID)
for couponID in loser_coupons:
query = """UPDATE tbl_UserCoupon SET won=false WHERE couponID=%d""" % couponID
x.execute(query)
DB_CONN.commit()
def save_matchinfo(self):
fields = ['datetime', 'league', 'team_1', 'team_2',
'weekID', 'matchID', 'mbs', 'iy_goals_1', 'iy_goals_2',
'ms_goals_1', 'ms_goals_2', 'h1', 'h2', 'was_played']
querystart = """REPLACE INTO tbl_MatchInfo ("""
queryvalues = """VALUES ("""
rnames = ""
rvalues = ""
for i, f in enumerate(fields):
val = self.__getattribute__(f)
if val is not None:
rnames += "%s, " % f
if i < 4:
rvalues += '"%s", ' % val
else:
rvalues += "%d, " % val
querystart += rnames
querystart = querystart[:-2] + ") "
queryvalues += rvalues
queryvalues = queryvalues[:-2] + ")"
query = querystart + queryvalues
DB_CONN.cursor().execute(query)
DB_CONN.commit()
def update_user_coupon_ratios():
x = DB_CONN.cursor()
query = """SELECT couponID FROM tbl_UserCoupon WHERE ratio IS NULL"""
x.execute(query)
user_coupon_rows = x.fetchall()
for row in user_coupon_rows:
couponID = row[0]
query = """SELECT weekID, matchID, bet_index FROM tbl_Coupons WHERE couponID=%d""" % couponID
x.execute(query)
results = x.fetchall()
ratio = 1
for row in results:
weekID, matchID, bet_index = row
query = """SELECT r%d FROM tbl_Ratios WHERE weekID=%d AND matchID=%d""" %\
(bet_index + 1, weekID, matchID)
x.execute(query)
match_ratio = x.fetchone()[0]
ratio *= match_ratio
query = """UPDATE tbl_Coupons SET ratio=%f WHERE couponID=%d AND weekID=%d AND matchID=%d""" %\
(match_ratio, couponID, weekID, matchID)
x.execute(query)
DB_CONN.commit()
query = """UPDATE tbl_UserCoupon SET ratio=%f WHERE couponID=%d""" %\
(ratio, couponID)
x.execute(query)
DB_CONN.commit()
def update_winner_coupons():
x = DB_CONN.cursor()
query = """SELECT couponID from tbl_UserCoupon WHERE won IS NULL"""
x.execute(query)
user_coupon_rows = x.fetchall()
# TODO: check this
for row in user_coupon_rows:
couponID = row[0]
query = """SELECT * from tbl_Coupons WHERE won IS NULL AND couponID=%d""" % couponID
x.execute(query)
results = x.fetchall()
if len(results) == 0:
query = """UPDATE tbl_UserCoupon SET won=true WHERE couponID=%d""" % couponID
x.execute(query)
DB_CONN.commit()
def save_ratios(self):
querystart = """REPLACE INTO tbl_Ratios (weekID, matchID, """
queryvalues = """VALUES ("%d", "%d", """ %\
(self.weekID, self.matchID)
rnames = ""
rvalues = ""
has_ratios = False
for i in range(len(self.ratios)):
if self.ratios[i]:
has_ratios = True
rnames += "r%d, " % (i + 1)
rvalues += "%.3f, " % self.ratios[i]
if has_ratios:
querystart += rnames
querystart = querystart[:-2] + ") "
queryvalues += rvalues
queryvalues = queryvalues[:-2] + ")"
query = querystart + queryvalues
DB_CONN.cursor().execute(query)
DB_CONN.commit()
match_ratio = x.fetchone()[0]
ratio *= match_ratio
query = """UPDATE tbl_Coupons SET ratio=%f WHERE couponID=%d AND weekID=%d AND matchID=%d""" %\
(match_ratio, couponID, weekID, matchID)
x.execute(query)
DB_CONN.commit()
query = """UPDATE tbl_UserCoupon SET ratio=%f WHERE couponID=%d""" %\
(ratio, couponID)
x.execute(query)
DB_CONN.commit()
today_id = get_today_id()
x = DB_CONN.cursor()
query = """DELETE FROM tbl_MatchInfo WHERE weekID<>%d AND weekID<>%d""" % (today_id, (today_id -1))
x.execute(query)
query = """DELETE FROM tbl_Results WHERE weekID<>%d AND weekID<>%d""" % (today_id, (today_id -1))
x.execute(query)
query = """DELETE FROM tbl_Ratios WHERE weekID<>%d AND weekID<>%d""" % (today_id, (today_id -1))
x.execute(query)
DB_CONN.commit()
query = """SELECT matchID FROM tbl_MatchInfo WHERE weekID=%d AND was_played=False""" % (today_id -1)
results = x.execute(query)
matches_previous = {}
if results:
j = get_json_data(URL_MATCHES % (today_id - 1))
matches_previous = {j['m'][i]['d']: j['m'][i]['m'] for i in range(len(j['m']))}
def update_user_coupon_ratios():
x = DB_CONN.cursor()
query = """SELECT couponID FROM tbl_UserCoupon WHERE ratio IS NULL"""
x.execute(query)
user_coupon_rows = x.fetchall()
for row in user_coupon_rows:
couponID = row[0]
query = """SELECT weekID, matchID, bet_index FROM tbl_Coupons WHERE couponID=%d""" % couponID
x.execute(query)
results = x.fetchall()
ratio = 1
for row in results:
weekID, matchID, bet_index = row
query = """SELECT r%d FROM tbl_Ratios WHERE weekID=%d AND matchID=%d""" %\
(bet_index + 1, weekID, matchID)
x.execute(query)
match_ratio = x.fetchone()[0]
ratio *= match_ratio
query = """UPDATE tbl_Coupons SET ratio=%f WHERE couponID=%d AND weekID=%d AND matchID=%d""" %\
(match_ratio, couponID, weekID, matchID)
x.execute(query)
DB_CONN.commit()
query = """UPDATE tbl_UserCoupon SET ratio=%f WHERE couponID=%d""" %\
(ratio, couponID)
x.execute(query)
DB_CONN.commit()
def update_coupons(self):
"""
it checks all coupons related to this match
to see if they won
"""
query = """SELECT bet_index, couponID from tbl_Coupons """ +\
"""WHERE weekID=%d AND matchID=%d AND won IS NULL""" % (self.weekID, self.matchID)
x = DB_CONN.cursor()
x.execute(query)
coupon_rows = x.fetchall()
loser_coupons = []
for row in coupon_rows:
bet_index = row[0]
couponID = row[1]
won = self.results[bet_index]
query = """UPDATE tbl_Coupons SET won=%d WHERE couponID=%d AND weekID=%d AND matchID=%d""" % \
(won, couponID, self.weekID, self.matchID)
x.execute(query)
DB_CONN.commit()
if not won:
loser_coupons.append(couponID)
for couponID in loser_coupons:
query = """UPDATE tbl_UserCoupon SET won=false WHERE couponID=%d""" % couponID
x.execute(query)
DB_CONN.commit()
def save_results(self):
querystart = """INSERT INTO tbl_Results (weekID, matchID, """
queryvalues = """VALUES ("%s", "%d", """ %\
(self.weekID, self.matchID)
rnames = ""
rvalues = ""
for i in range(len(self.results)):
rnames += "r%d, " % (i + 1)
rvalues += "%d, " % self.results[i]
querystart += rnames
querystart = querystart[:-2] + ") "
queryvalues += rvalues
queryvalues = queryvalues[:-2] + ")"
query = querystart + queryvalues
try:
DB_CONN.cursor().execute(query)
DB_CONN.commit()
# if INSERT succeeds
# check all related coupons
except Exception, e:
return
def save_matchinfo(self):
fields = [
'datetime', 'league', 'team_1', 'team_2', 'weekID', 'matchID',
'mbs', 'iy_goals_1', 'iy_goals_2', 'ms_goals_1', 'ms_goals_2',
'h1', 'h2', 'was_played'
]
querystart = """REPLACE INTO tbl_MatchInfo ("""
queryvalues = """VALUES ("""
rnames = ""
rvalues = ""
for i, f in enumerate(fields):
val = self.__getattribute__(f)
if val is not None:
rnames += "%s, " % f
if i < 4:
rvalues += '"%s", ' % val
else:
rvalues += "%d, " % val
querystart += rnames
querystart = querystart[:-2] + ") "
queryvalues += rvalues
queryvalues = queryvalues[:-2] + ")"
query = querystart + queryvalues
DB_CONN.cursor().execute(query)
DB_CONN.commit()
def update_winner_coupons():
x = DB_CONN.cursor()
query = """SELECT couponID from tbl_UserCoupon WHERE won IS NULL"""
x.execute(query)
user_coupon_rows = x.fetchall()
# TODO: check this
for row in user_coupon_rows:
couponID = row[0]
query = """SELECT * from tbl_Coupons WHERE won IS NULL AND couponID=%d""" % couponID
x.execute(query)
results = x.fetchall()
if len(results) == 0:
query = """UPDATE tbl_UserCoupon SET won=true WHERE couponID=%d""" % couponID
x.execute(query)
DB_CONN.commit()
def save_ratios(self):
querystart = """REPLACE INTO tbl_Ratios (weekID, matchID, """
queryvalues = """VALUES ("%d", "%d", """ %\
(self.weekID, self.matchID)
rnames = ""
rvalues = ""
has_ratios = False
for i in range(len(self.ratios)):
if self.ratios[i]:
has_ratios = True
rnames += "r%d, " % (i + 1)
rvalues += "%.3f, " % self.ratios[i]
if has_ratios:
querystart += rnames
querystart = querystart[:-2] + ") "
queryvalues += rvalues
queryvalues = queryvalues[:-2] + ")"
query = querystart + queryvalues
DB_CONN.cursor().execute(query)
DB_CONN.commit()
ratio *= match_ratio
query = """UPDATE tbl_Coupons SET ratio=%f WHERE couponID=%d AND weekID=%d AND matchID=%d""" %\
(match_ratio, couponID, weekID, matchID)
x.execute(query)
DB_CONN.commit()
query = """UPDATE tbl_UserCoupon SET ratio=%f WHERE couponID=%d""" %\
(ratio, couponID)
x.execute(query)
DB_CONN.commit()
today_id = get_today_id()
x = DB_CONN.cursor()
query = """DELETE FROM tbl_MatchInfo WHERE weekID<>%d AND weekID<>%d""" % (
today_id, (today_id - 1))
x.execute(query)
query = """DELETE FROM tbl_Results WHERE weekID<>%d AND weekID<>%d""" % (
today_id, (today_id - 1))
x.execute(query)
query = """DELETE FROM tbl_Ratios WHERE weekID<>%d AND weekID<>%d""" % (
today_id, (today_id - 1))
x.execute(query)
DB_CONN.commit()
query = """SELECT matchID FROM tbl_MatchInfo WHERE weekID=%d AND was_played=False""" % (
today_id - 1)
results = x.execute(query)
matches_previous = {}