def upsert_stats(self, cityID, stats, userID=False, tdate=False, day=0): """Insert stats dict into database""" cur = self.db.cursor() mstr = "" values, sql_vals = [], "" update = " ON DUPLICATE KEY UPDATE " for i in stats.keys(): mstr += "%s, " % i values.append(stats[i]) sql_vals = sql_vals + i + "=VALUES(" + i + "), " sql_vals, mstr = sql_vals[:-2], mstr[:-2] if tdate: sql = "INSERT INTO %swetterturnier_tdatestats (cityID, tdate, %s) VALUES %s" + update + sql_vals cur.execute( sql % (self.prefix, mstr, str(self.sql_tuple(sum([[cityID], [tdate], values], []))))) elif userID: sql = "INSERT INTO %swetterturnier_userstats (userID, cityID, %s) VALUES %s" + update + sql_vals cur.execute( sql % (self.prefix, mstr, str(self.sql_tuple(sum([[userID], [cityID], values], []))))) elif cityID: sql = "INSERT INTO %swetterturnier_citystats (cityID, %s) VALUES %s" + update + sql_vals cur.execute(sql % (self.prefix, mstr, str(self.sql_tuple(sum([[cityID], values], []))))) else: utils.exit("Wrong usage of upsert_stats in database.py!")
def input_to_list(x, msg): """ Inputs to this script can be lists of numeric values. Therefore we have to convert them into numpy arrays which will be done by this script. If input is NONE, an empty array will be returned. Inputs: x required string (e.g., '13.3' or '13.3,14.4') msg required short message in case the function calls exit. Output: numpy.array object containing either nothing (empty) or the values from input x as float numbers. """ if not x: return np.asarray([]) tmp = x.split(",") res = [] for elem in tmp: try: res.append(float(elem)) except: utils.exit("Problems: %s have to be numeric!" % msg) return np.asarray(res)
is_latest_tournament = False if config['input_tdate'] == None: config['input_tdate'] = current_tdate print ' * Using latest tournament date: %d' % config['input_tdate'] is_latest_tournament = True else: #utils.exit('Sorry, need explicit -t/--tdate input for this script') print ' * Using input tdate: %d' % config['input_tdate'] # - If input_user was given as string we have to find the # corresponding userID first! if type(config['input_user']) == type(str()): config['input_user'] = db.get_user_id(config['input_user']) if not config['input_user']: utils.exit( 'SORRY could not convert your input -u/--user to corresponding userID. Check name.' ) # - Compute the Points for all the dudes first import subprocess as sub if not config['input_alldates']: if is_latest_tournament: scripts = [ 'ComputePetrus.py', 'ComputeMoses.py', 'ComputePersistenzen.py', 'ComputeMeanBets.py', 'ComputePoints.py', 'ComputeSleepy.py' ] if today == current_tdate: print "Today is a tournament day!" else: scripts = [
# - Loading tdate (day since 1970-01-01) for the tournament. # Normaly Friday-Tornament (tdate is then Friday) while # the bet-dates are for Saturday and Sunday. if config['input_tdate'] == None: tdates = [db.current_tournament()] print ' * Current tournament is %s' % utils.tdate2string( tdates[0] ) else: tdates = [config['input_tdate']] # - If input_user was given as string we have to find the # corresponding userID first! if type(config['input_user']) == type(str()): config['input_user'] = db.get_user_id( config['input_user'] ) if not config['input_user']: utils.exit('SORRY could not convert your input -u/--user to corresponding userID. Check name.') # - Loading all parameters params = db.get_parameter_names(False) # ---------------------------------------------------------------- # - Because of the observations we have to compute the # points city by city. Loading city data here first. # ---------------------------------------------------------------- # - Loading all different cities (active cities) cities = db.get_cities() # - If input city set, then drop all other cities. if not config['input_city'] == None: tmp = [] for elem in cities: if elem['name'] == config['input_city']: tmp.append( elem )
# - Loading tdate (day since 1970-01-01) for the tournament. # Normaly Friday-Tornament (tdate is then Friday) while # the bet-dates are for Saturday and Sunday. if config['input_tdate'] == None: tdates = [db.current_tournament()] print(' * Current tournament is %s' % utils.tdate2string(tdates[0])) else: tdates = [config['input_tdate']] # - If input_user was given as string we have to find the # corresponding userID first! if type(config['input_user']) == type(str()): config['input_user'] = db.get_user_id(config['input_user']) if not config['input_user']: utils.exit( 'SORRY could not convert your input -u/--user to corresponding userID. Check name.' ) # - Loading all parameters params = db.get_parameter_names(False) # ---------------------------------------------------------------- # - Because of the observations we have to compute the # points city by city. Loading city data here first. # ---------------------------------------------------------------- # - Loading all different cities (active cities) cities = db.get_cities() # - If input city set, then drop all other cities. if not config['input_city'] == None: tmp = [] for i in cities:
print(" Run computation for city {0:s} ({1:d})".format(city,cityID)) # Run the scripts in the correct order for script in scripts: # - Create next python command. Append ALL input arguments # we hade in this script plus the tdate (if not set in input # arguments). cmd = ['python',script] # Append tournamentdate and city to the arglist cmd.append('-t'); cmd.append("{0:d}".format(tdate)) cmd.append('-c'); cmd.append(city) p1 = sub.Popen(cmd,stderr=sub.PIPE) err = p1.communicate() if not p1.returncode == 0: for line in err: print('%s\n' % line) utils.exit('ERROR WHILE RUNNING %s AS SUBPROCESS FOR DATE %d CITY %s' % (script,tdate,city)) # update database ans set these jobs to 'done' cur = db.cursor() now = dt.now().strftime("%Y-%m-%d %H:%M:%S") sql = "UPDATE {0:s}{1:s} SET done = '{2:s}' WHERE cityID = {3:d} AND tdate = {4:d}".format( config['mysql_prefix'],'wetterturnier_rerunrequest',now,cityID,tdate) cur = db.cursor() cur.execute(sql) db.commit() db.close()
else: tdates = [config['input_tdate']] # - Loading all different cities (active cities) cities = db.get_cities() # - If input city set, then drop all other cities. if not config['input_city'] == None: tmp = [] for i in cities: if i['name'] == config['input_city']: tmp.append(i) cities = tmp # - If moses directory does not exist: if not os.path.isdir(config['data_moses']): utils.exit("Cannot find directory \"{0:s}\" which should contain ".format(config["data_moses"]) + \ "the necessary coefficient-files for Moses! Stop!") # - Reading parameter list params = db.get_parameter_names(active=True, sort=True) # ---------------------------------------------------------------- # - Prepare the Moses # ---------------------------------------------------------------- username = '******' #db.create_user( username ) moses_userID = db.get_user_id(username) # ---------------------------------------------------------------- # - A small helper function to find the correct coefficient # file. Klaus sends them once a week, normally on # Tuesday. The name of the file contains a date
def compute_stats(self, cityID, measures, userID=False, tdate=False, day=0, last_tdate=None, referenz=True, mitteltips=True, aliases=None, pout=25, pmid=100, midyear=2010, span=None, dates=None, verbose=False): """ Computes all kinds of statistics e.g. the points for the eternal list plus min/max/median/mean of points for each user, city, tdate and so on. """ res = { } #results will be saved here with measures as keys, tdate as subkeys import numpy as np cur = self.db.cursor() day_strs = ["", "_d1", "_d2"] day_str = day_strs[0] if day != 0: day_str = day_strs[day] if last_tdate: last_tdate_str = " AND tdate<=" + str(last_tdate) else: last_tdate_str = "" sql = "SELECT points" + day_str + " FROM %swetterturnier_betstat WHERE " if tdate or (not tdate and not userID and cityID): # We don't want Sleepy in our tdatestats! #exclude = [self.get_user_id("Sleepy")] exclude = [] if not referenz: groupID = self.get_group_id("Referenztipps") for j in self.get_participants_in_group(groupID, cityID, tdate, playing=False): exclude.append(j) if not mitteltips: #include no groups sql2 = "SELECT ID FROM %susers WHERE user_login LIKE \"%s\"" cur.execute(sql2 % (self.prefix, "GRP_%")) data2 = cur.fetchall() for j in data2: exclude.append(int(j[0])) if tdate: #tdatestats #only include users who really played on tdate (no sleepy points!) #played = self.sql_tuple( self.get_participants_in_city( cityID, tdate ) ) sql += "cityID=%d AND tdate=%d AND userID NOT IN%s" + last_tdate_str cur.execute(sql % (self.prefix, cityID, tdate, self.sql_tuple(exclude))) elif cityID: #citystats sql2 = "SELECT part FROM %swetterturnier_tdatestats WHERE cityID=%d" + last_tdate_str cur.execute(sql2 % (self.prefix, cityID)) data = cur.fetchall() for i in measures: parts = [] for j in data: parts.append(int(j[0])) if i == "mean_part": res[i] = np.mean(parts) elif i == "max_part": res[i] = np.max(parts) elif i == "min_part": res[i] = np.min(parts) elif i == "tdates": res[i] = len(parts) sql += "cityID=%d AND userID NOT IN%s" + last_tdate_str cur.execute(sql % (self.prefix, cityID, self.sql_tuple(exclude))) elif userID: #userstats userIDs = [userID] #if we are using an alias dict we merge all aliases of a user with his/her other identities if aliases: username = self.get_username_by_id(userID) if username in aliases.keys() or username in sum( aliases.values(), []): if verbose: print(username) for j in aliases.keys(): if username == j: for k in aliases[j]: userIDs.append(self.get_user_id(k)) break else: if username in aliases[j]: userIDs.append(self.get_user_id(j)) for k in aliases[j]: userID = self.get_user_id(k) if userID not in userIDs: userIDs.append(userID) if verbose: print userIDs sql += "userID IN%s" if cityID != 0: sql += " AND cityID=%d" + last_tdate_str cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), cityID)) else: sql += last_tdate_str cur.execute(sql % (self.prefix, self.sql_tuple(userIDs))) else: utils.exit("Wrong usage of compute_stats!") data = cur.fetchall() points = [] for i in data: #sleepy has NULL points on d1 and d2, skip him! if i[0] == None: continue else: points.append(float(i[0])) if not points: points.append(0) if cityID == 0: """ first calculate sd_ind for all tournaments played. Pretend as cities were one so if a user participated in multiple cities on the same day it counts like he played multiple tournaments & for each of these tournaments we look up the sd_upp and calculate a mean (sd_ind) """ sql = "SELECT cityID, tdate, points FROM %swetterturnier_betstat WHERE userID IN%s AND tdate <= %d" cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), last_tdate)) points = {} for j in cur.fetchall(): if j[0] not in points: points[j[0]] = {} points[j[0]][j[1]] = j[2] sd_upp = [] for cityID in points.keys(): for tdate in points[cityID].keys(): sd_upp.append( self.get_stats(cityID, measure="sd_upp", tdate=tdate)) if not sd_upp: return {"points_adj": 0} #remove None values sd_upp = [x for x in sd_upp if x] sd_ind = np.nanmean(sd_upp) res["sd_ind"] = sd_ind #now we get the individual points for each tournament played points_adj = [] parts = {} for cityID in points.keys(): parts[cityID] = 0 for cityID in points.keys(): for tdate in points[cityID].keys(): median = self.get_stats(cityID, measure="median", tdate=tdate) points_adj.append(points[cityID][tdate] - median) parts[cityID] += 1 #get mean participations for every city a user ever played parts_all = np.sum(parts.values()) #actually parts_mean, we will use this variable in calculation parts = parts_all / len(parts.values()) res["part"] = parts_all sql = "SELECT points FROM wp_wetterturnier_betstat WHERE userID IN%s" cur.execute(sql % self.sql_tuple(userIDs)) points_all = [j[0] for j in cur.fetchall() if j[0]] if points_all: res["max"] = np.max(points_all) res["mean"] = np.mean(points_all) #norm by sd_ind, scale by mean participations in all cities if parts < pout: f = 0 elif parts < pmid: f = np.sqrt(float(parts) / pmid) else: f = 1 #average points above median res["points_med"] = np.mean(points_adj) #final adjusted points res["points_adj"] = f * (res["points_med"] / sd_ind) * 1000 if np.isnan(res["points_adj"]): res["points_adj"] = 0 sql = """ SELECT rank, count(rank) AS count FROM %swetterturnier_betstat WHERE userID IN%s AND rank <= 3 %s GROUP BY rank ORDER BY rank """ cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), last_tdate_str)) ranks = {1: "0", 2: "0", 3: "0"} for j in cur.fetchall(): ranks[j[0]] = str(j[1]) res["ranks_weekend"] = ",".join(ranks.values()) return res elif cityID == 6: #weighted variant sum(p_adj_i * part_i) / sum(part_i) #which cities did the user play? would be better to call userstat but then we need to get rid of zero/null rows... sql = "SELECT cityID FROM wp_wetterturnier_betstat WHERE userID IN%s" cur.execute(sql % self.sql_tuple(userIDs)) cityIDs = [] for i in cur.fetchall(): if i[0] not in cityIDs: cityIDs.append(i[0]) #get points_adj from userstats points_adj, parts, sd_ind = ([] for _ in range(3)) for cityID in cityIDs: measures = self.get_stats( cityID=cityID, userID=userIDs[0], measures=["points_adj", "part", "sd_ind"]) if measures: points_adj.append(measures[0]) parts.append(measures[1]) sd_ind.append(measures[2]) if not points_adj or not parts or not sd_ind or None in points_adj: res["points_adj"] = 0 res["part"] = 0 res["sd_ind"] = 0 return res else: sql = "SELECT points FROM wp_wetterturnier_betstat WHERE userID IN%s" cur.execute(sql % self.sql_tuple(userIDs)) points_all = [j[0] for j in cur.fetchall() if j[0]] if points_all: res["max"] = np.max(points_all) res["mean"] = np.mean(points_all) sql = """ SELECT rank, count(rank) AS count FROM %swetterturnier_betstat WHERE userID IN%s AND rank <= 3 %s GROUP BY rank ORDER BY rank """ cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), last_tdate_str)) ranks = {1: "0", 2: "0", 3: "0"} for j in cur.fetchall(): ranks[j[0]] = str(j[1]) res["ranks_weekend"] = ",".join(ranks.values()) parts_all = np.sum(parts) res["part"] = parts_all res["points_adj"] = np.dot(points_adj, parts) / parts_all res["sd_ind"] = np.dot(sd_ind, parts) / parts_all return res for i in measures: i += day_str if i == "points" + day_str: res[i] = sum(points) elif "sd_ind" in i and "_d" not in i: #get tdates where the user participated sql = "SELECT tdate FROM %swetterturnier_betstat WHERE userID IN%s AND cityID=%d" if "1" in i or "2" in i or "X" in i: spanstr = i[-1] if midyear: middle_tdate = str( utils.string2tdate(str(midyear) + "-01-01")) #print "Middle of tournament (tdate): %s" % middle_tdate if "1" in i: sql += " AND tdate<=" + middle_tdate elif "2" in i: sql += " AND tdate>" + middle_tdate elif span: if verbose: print span sql += " AND tdate BETWEEN " + str( span[0]) + " AND " + str(span[1]) cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), cityID)) tdates = [j[0] for j in cur.fetchall()] sql = "SELECT sd_upp from %swetterturnier_tdatestats WHERE cityID=%d AND tdate IN%s" cur.execute(sql % (self.prefix, cityID, self.sql_tuple(tdates))) #print sql % (self.prefix, cityID, self.sql_tuple(tdates) ) sd_upp = [j[0] for j in cur.fetchall()] res[i] = np.mean(sd_upp) #print(res[i]) if res[i] == None or np.isnan(res[i]): res[i] = 0 elif "points_adj" in i and "_d" not in i: if verbose: print self.get_username_by_id(userIDs[0]) print i, "\n" #skip Sleepy #if self.get_user_id("Sleepy") in userIDs: continue parts = len(points) if not parts: continue """ find all dates where the user actually played for each date calculate: (points-points) / (ymax-median*) sum up and divide by number of tdates * daily median and median fitted by PlotStats should be calculated earlier in ComputeStats with other citystats! """ tdates = {} sql = "SELECT tdate, points FROM %swetterturnier_betstat WHERE userID IN%s AND cityID=%d" if "1" in i or "2" in i or "X" in i: if midyear: middle_tdate = str( utils.string2tdate(str(midyear) + "-01-01")) if verbose: print "Middle of tournament (tdate): %s" % middle_tdate if "1" in i: sql += " AND tdate<=" + middle_tdate elif "2" in i: sql += " AND tdate>" + middle_tdate elif span: if verbose: print span sql += " AND tdate BETWEEN " + str( span[0]) + " AND " + str(span[1]) #print sql cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), cityID)) data = cur.fetchall() for j in data: tdates[j[0]] = {} tdates[j[0]]["points"] = j[1] #get the actual median for each tdate sql = "SELECT tdate, median from %swetterturnier_tdatestats WHERE cityID=%d AND tdate IN%s" cur.execute(sql % (self.prefix, cityID, self.sql_tuple(tdates.keys()))) data = cur.fetchall() for j in data: tdates[j[0]]["median"] = j[1] if "1" in i: timestr = "1" elif "2" in i: timestr = "2" elif "X" in i: timestr = "X" else: timestr = "" sd_ind = self.get_stats(cityID, measure="sd_ind" + timestr, userID=userIDs[0]) if sd_ind in [0, None] or np.isnan(sd_ind): continue if verbose: print "tdate points median sd points_adj" points_adj = [] for t in sorted(tdates.keys()): if {"points", "median"} <= set(tdates[t]): perc = tdates[t]["points"] - tdates[t]["median"] if verbose: print utils.tdate2string(t), str( tdates[t]["points"]).ljust(6), str( tdates[t]["median"]).ljust(6), str( round(sd_ind, 2)).ljust(7), str(round(perc, 2)).ljust(10) else: continue points_adj.append(perc) if not points_adj: points_adj.append(0) if verbose: print "" #if someone has less than "pout" parts, just kick him out! if parts < pout: f = 0 #in between pout and pmid the points get adjusted by the sqrt() function elif parts < pmid: f = np.sqrt(float(parts) / pmid) else: #if parts >= pmid f = 1 #average points above median res["points_med"] = np.mean(points_adj) #final adjusted points res["points_adj"] = f * (res["points_med"] / sd_ind) * 1000 if np.isnan(res["points_adj"]): res["points_adj"] = 0 elif i == "mean" + day_str: res[i] = round(np.mean(points), 1) elif i == "median" + day_str: res[i] = np.median(points) elif i == "Qlow" + day_str: res[i] = np.percentile(points, 25, interpolation="midpoint") elif i == "Qupp" + day_str: res[i] = np.percentile(points, 75, interpolation="midpoint") elif i == "max" + day_str: res[i] = max(points) elif i == "min" + day_str: res[i] = min(points) elif i == "sd" + day_str: #standard deviation sd = np.std(points) if np.isnan(sd): res[i] = 0 else: res[i] = sd elif i == "part": #important for part count, otherwise could be 1 if a player/date actually has 0 part if len(points) == 1 and points[0] == 0: res[i] = 0 else: res[i] = len(points) elif i == "sd_upp" + day_str: median = self.get_stats(tdate=tdate, cityID=cityID, measure="median" + day_str) if not median: median = res["median" + day_str] if not median: continue sql = "SELECT points" + day_str + " from %swetterturnier_betstat WHERE tdate=%d AND cityID=%d AND points" + day_str + " > %f" #print sql % (self.prefix, tdate, cityID, median) cur.execute(sql % (self.prefix, tdate, cityID, median)) sd = np.mean([j[0] - median for j in cur.fetchall()]) if np.isnan(sd): res[i] = 0 else: res[i] = sd elif i == "ranks_weekend": sql = """ SELECT rank, count(rank) AS count FROM %swetterturnier_betstat WHERE cityID = %d AND userID IN%s AND rank <= 3 %s GROUP BY rank ORDER BY rank """ cur.execute( sql % (self.prefix, cityID, self.sql_tuple(userIDs), last_tdate_str)) ranks = {1: "0", 2: "0", 3: "0"} for j in cur.fetchall(): ranks[j[0]] = str(j[1]) res[i] = ",".join(ranks.values()) elif i == "ranks_season": pass else: continue if not res: utils.exit("No results!") return res
tmp = [] for elem in cities: if elem['name'] == config['input_city']: tmp.append(elem) cities = tmp # - Loading all active users from the database active_groups = db.get_active_groups() # - Checking input user if set # If input_user was given as string we have to find the # corresponding userID first! if type(config['input_user']) == type(int()): config['input_user'] = db.get_username_by_id(config['input_user']) if not config['input_user']: utils.exit( 'SORRY could not convert your input -u/--user to corresponding username. Check name.' ) # - Check if input_user is an active group. if not config['input_user'] == None: if not config['input_user'] in active_groups: print ' Some help for you: currently active groups:' for grp in active_groups: print ' - %s' % grp utils.exit( 'Sorry, but %s is not a name of an active group. Stop.' % config['input_user']) else: active_groups = [config['input_user']] # - Create new user
'/archivefiles_processed_%s.txt' % city): aid = open( config['rawdir'] + '/archivefiles_processed_%s.txt' % city, 'r') aco = aid.readlines() aid.close() skip = False for aline in aco: if file in aline: print ' - ALLREADY PROCESSED. Skip' skip = True if skip: continue print '* Loading: %s' % (file) if counter >= 5: utils.exit('dev stop, counter >= 5 here') # - Import first file obj = importbets.importbets(config, file_forced) obj.loadfilecontent(file) obj.identify_city() # - Getting data from raw files obj.takedata(obj.raw_lines) obj.extract_sum_points() obj.extract_obs(1) obj.extract_obs(2)
"observed wind speed, single value, or comma separated list.") # - Quiet mode parser.add_option( "-q", "--quiet", action="store_true", dest="quiet", default=False, help= "Optional, logical. If set, output will be more quiet. Default False.") inputs, args = parser.parse_args() # - Missing input -o if not inputs.param: print parser.usage utils.exit("Missing required input -p/--param.") if not inputs.obs: print parser.usage utils.exit("Missing required input -o/--obs.") if not inputs.values: print parser.usage utils.exit("Missing required input -v/--values.") # ---------------------------------------------------------------- # ---------------------------------------------------------------- # - Reading config file # ---------------------------------------------------------------- config = utils.readconfig('config.conf') # ---------------------------------------------------------------- # - Checking if parameter is allowed
# - Only start if called as main routine if __name__ == '__main__': import subprocess as sub import getopt import sys # - Wetterturnier specific modules from pywetterturnier import utils from pywetterturnier import importbets try: opts, args = getopt.getopt(sys.argv[1:],'-o',['--obs']) except getopt.GetoptError as err: print(err) utils.exit('Wrong input options set') obsonly = False for o, a in opts: if o in ['-o','--obs']: obsonly = True # - Reading configuration file first config = utils.readconfig() # - The url needed to download the data url_template = 'http://wetterturnier.de/wertungen/uebersicht_%s.php?p=ue0&stadt=%s' # --------------------------------------------------------------- # - No tags? No downloading shit. if config['migrate_citytags'] == None: print ' * No citytags from config file: nothing will be done.'
def CSP(db,config,cities,tdates): import sys, os import numpy as np from pywetterturnier import utils print('\n * Compute sum points to fill betstat table') # ---------------------------------------------------------------- # - Now going over the cities and compute the points. # ---------------------------------------------------------------- for city in cities: # ------------------------------------------------------------- # - If aldates, take all tdates from database # ------------------------------------------------------------- if config['input_alldates']: tdates = db.all_tournament_dates( city['ID'] ) # ------------------------------------------------------------- # - Looping trough dates # ------------------------------------------------------------- for tdate in tdates: # ---------------------------------------------------------------- # - Check if we are allowed to perform the computation of the # mean bets on this date # ---------------------------------------------------------------- check = utils.datelock(config,tdate) if check: print(' Date is \'locked\' (datelock). Dont execute, skip.') continue # ---------------------------------------------------------- # - Which judgingclass do we have to take? # It is possible that the scoring system changed. # ---------------------------------------------------------- # Take the latest judgingclass changed in 2002-12-06 if tdate < 12027: if config['input_ignore']: print('[!] Judginglcass not defined - but started in ignore mode. Skip.') continue else: utils.exit('I dont know which judgingclass I should use for this date. Stop.') print(' For %s tournament is %s' % (city['name'], utils.tdate2string( tdate ))) # - If config['input_user'] is an integer value this # is a userID. Compute the sum points for this user # only. This is for some replay purposes. if type(config['input_user']) == type(int()): extra = ' AND userID = %d ' % config['input_user'] else: extra = '' sqlP = 'SELECT userID, cityID, tdate, ' + \ 'round(sum(points),1) AS points, max(placed) AS submitted ' + \ 'FROM %swetterturnier_bets ' + \ 'WHERE cityID = %d AND tdate = %d %s ' + \ 'GROUP BY userID, cityID, tdate ' sqlP = sqlP % (db.prefix,city['ID'], tdate, extra) sqlX = 'SELECT userID, cityID, tdate, sum(points) AS points ' + \ 'FROM %swetterturnier_bets ' + \ 'WHERE cityID = %d AND tdate = %d ' + \ 'AND betdate = %d %s ' + \ 'GROUP BY userID, cityID, tdate' sql1 = sqlX % (db.prefix,city['ID'], tdate, tdate+1, extra) sql2 = sqlX % (db.prefix,city['ID'], tdate, tdate+2, extra) sql_full = 'SELECT p.points AS points, d1.points AS points_d1, d2.points AS points_d2, ' + \ 'p.userID, p.cityID, p.tdate ' + \ 'FROM ('+sqlP+') AS p ' + \ 'LEFT OUTER JOIN ' + \ '('+sql1+') AS d1 ON p.userID=d1.userID AND p.tdate=d1.tdate ' + \ 'AND p.cityID = d1.cityID ' + \ 'LEFT OUTER JOIN ' + \ '('+sql2+') AS d2 ON p.userID=d2.userID AND p.tdate=d2.tdate ' + \ 'AND p.cityID = d2.cityID ' + \ '' print(' - Reading data from database') cur = db.cursor() cur.execute( sql_full ) desc = cur.description data = cur.fetchall() # Now compute if len(data) == 0: print(' - Sorry, got no data to compute sum points') else: # Else: we have data, update database print(' - Upserting database (%d lines)' % len(data)) # Require the index of the "points" variable points_idx = None for di in range(0,len(desc)): if str(desc[di][0]) == 'points': points_idx = di break if points_idx is None: sys.exit("ERROR: could not find variable \"points\" in data. Stop.") # - Prepare the data sql = 'INSERT INTO '+db.prefix+'wetterturnier_betstat ' + \ 'SET rank=%s, points=%s, points_d1=%s, points_d2=%s, ' + \ 'userID=%s, cityID=%s, tdate=%s ON DUPLICATE KEY UPDATE points=VALUES(points), ' + \ 'points_d1 = VALUES(points_d1), points_d2 = VALUES(points_d2)' #for d in data: # if not int(d[0]) == 1130: continue # print d # Compute rank points = [] for pi in range(0,len(data)): points.append(data[pi][points_idx]) print(points) try: points = np.sort(points)[::-1] except: print("ERROR: error while computing ranks") continue #sys.exit(0) rank = [] data = list(data) for pi in range(0,len(data)): this = data[pi][points_idx] # if points is empty # Append empty rank (IN FRONT) and skip if not this: data[pi] = (None,) + data[pi] continue # else search rank # and append rank IN FRONT (check sql command) rank = np.where(points == this)[0] if not len(rank): data[pi] = (None,) + data[pi] else: data[pi] = (rank[0] + 1,) + data[pi] cur.executemany( sql , data ) db.commit()
from pywetterturnier import database # - Reading configuration file first config = utils.readconfig() # - Migrate mitspieler.txt file with # passwords ans shit? if config['migrate_mitspieler']: db = database.database(config) print ' * Migrating mitspieler list' try: fid = open(config['migrate_mitspielerfile'], 'r') except: utils.exit('Cannot read file %s' % config['migrate_mitspielerfile']) # - Write changed usernames to file # A local file to store all users where # the username changed because of special characters. try: ufile = open('changed_usernames.txt', 'w+') except: utils.exit( 'Problems creating file changed_usernames.txt write mode') # - Else loop trough file and convert usernames and # passwords to prepare for database res = [] for line in fid.readlines(): line = line.strip()