Пример #1
0
class FantasyDB(object):
    def __init__(self, dbname='SOS.db', sheet_name='', drop=False):
        self.drive = Drive(sheet_name)
        self.con = sqlite3.connect(dbname)
        self.con.row_factory = sqlite3.Row

        class Median:
            def __init__(self):
                self.values = []

            def step(self, value):
                self.values.append(value)

            def finalize(self):
                count = len(self.values)
                values = sorted(self.values)
                if count % 2 == 1:
                    return values[math.ceil(count / 2)]
                else:
                    return (values[int(count / 2) - 1] +
                            values[int(count / 2)]) / 2

        self.con.create_aggregate("median", 1, Median)

        self.c = self.con.cursor()
        self.diff_table = False
        self.median_table = False

        self.cats_conf = OrderedDict({
            "R": {
                "precision": 1,
                "close_limit": 3,
                "batting": True,
                "real": True
            }
        })
        self.cats_conf["HR"] = {
                "precision": 1,
                "close_limit": 1,
                "batting": True,
                "real": True
        }
        self.cats_conf["RBI"] = {
                "precision": 1,
                "close_limit": 3,
                "batting": True,
                "real": True
        }
        self.cats_conf["SB"] = {
                "precision": 1,
                "close_limit": 1,
                "batting": True,
                "real": True
        }
        self.cats_conf["H"]=  {
                "precision": 0,
                "batting": True,
                "real": False
        }
        self.cats_conf["AB"] = {
                "precision": 0,
                "batting": True,
                "real": False
        }
        self.cats_conf["AVG"] = {
                "precision": 3,
                "close_limit": 0.020,
                "batting": True,
                "real": True,
                "expr": "ifnull(1.0 * $(H) / $(AB), 0)",
        }
        self.cats_conf["W"] = {
                "precision": 1,
                "close_limit": 1,
                "batting": False,
                "real": True
        }
        self.cats_conf["SV"] = {
                "precision": 1,
                "close_limit": 1,
                "batting": False,
                "real": True
        }
        self.cats_conf["K"] = {
                "precision": 0,
                "close_limit": 5,
                "batting": False,
                "real": True
        }
        self.cats_conf["IP"] = {
                "precision": 1,
                "batting": False,
                "real": False
        }
        self.cats_conf["ER"] = {
                "precision": 1,
                "batting": False,
                "real": False
        }
        self.cats_conf["WH"] = {
                "precision": 0,
                "batting": False,
                "real": False
        }
        self.cats_conf["ERA"] = {
                "precision": 2,
                "close_limit": 0.50,
                "batting": False,
                "real": True,
                "invert": True,
                "expr": "ifnull($(ER) * 9 / $(IP), 0)"
        }
        self.cats_conf["WHIP"] = {
                "precision": 2,
                "close_limit": 0.10,
                "batting": False,
                "real": True,
                "invert": True,
                "expr": "ifnull($(WH) / $(IP), 0)"
        }

        if drop:
            self.drop_db()
            self.create_db()

    def drop_db(self):
        self.c.executescript("""
            DROP TABLE IF EXISTS team;
            DROP TABLE IF EXISTS matchup;
            DROP TABLE IF EXISTS week_stats;
        """)

    def create_db(self):
        self.c.executescript("""
            CREATE TABLE team (team_id integer, team_key text,
                               label text, manager text);

            CREATE TABLE matchup (team_id1 integer, team_id2 integer,
                                  week integer);

            CREATE TABLE week_stats
                (team_id integer, opponent_id integer, week integer, R integer,
                 HR integer, RBI integer, SB integer, H integer, AB integer,
                 W integer, SV integer, K integer, IP real, ER integer,
                 WH integer);
        """)

    def create_tmp_stats_table(self, table_name):
        self.c.executescript("""
            DROP TABLE IF EXISTS %s;
            CREATE TABLE %s
                (team_id integer, opponent_id integer, week integer,
                 R integer, HR integer, RBI integer, SB integer, AVG real,
                 W integer, SV integer, K integer, ERA real, WHIP real);
        """ % (table_name, table_name))

    def create_tmp_matchup_table(self, table_name):
        self.c.executescript("""
            DROP TABLE IF EXISTS %s;
            CREATE TABLE %s (team_id integer, week integer,
                R integer, HR integer, RBI integer, SB integer, AVG real,
                W integer, SV integer, K integer, ERA real, WHIP real);
        """ % (table_name, table_name))

    def insert(self, sql, data=None):
        if data is None:
            self.c.execute(sql)
        else:
            for line in data:
                self.c.execute(sql, line)
        self.con.commit()

    def add_teams(self, teams):
        self.insert("INSERT INTO team VALUES (?, ?, ?, ?)", teams)

    def add_matchups(self, matchups):
        self.insert("INSERT INTO matchup VALUES(?, ?, ?)", matchups)

    def add_stats(self, stats):
        sql = "INSERT INTO week_stats ("
        sql += ", ".join(stats.keys())
        sql += ") VALUES("
        sql += ", ".join(["?"] * len(stats)) + ")"
        self.insert(sql, [tuple(stats.values())])

    def save_data(self, data, name):
        # Save to file
        f = open("export/" + name + ".txt", "w", encoding="utf-8")
        f.write(data)
        f.close()

        # Save to Google Drive
        if self.drive is not None:
            self.drive.login()
            self.drive.create_sheet(name, data)

    def nb_weeks(self):
        sql = "SELECT DISTINCT COUNT(*) FROM week_stats GROUP BY team_id"
        return self.c.execute(sql).fetchone()[0]

    def get_select(self, aggregate="AVG", real_only=True, use_alias=True,
                   use_round=True, as_string=True, table_alias=""):
        columns = OrderedDict({})
        if len(table_alias):
            table_alias += "."
        for cat, conf in self.cats_conf.items():
            if real_only and not conf["real"]:
                continue
            expr = "$(" + cat + ")" if "expr" not in conf else conf["expr"]
            col = expr.replace("$(", aggregate + "(" + table_alias)
            if use_round:
                col = self.apply_round(cat, col)
            if use_alias:
                col = self.apply_alias(cat, col)

            columns[cat] = col

        return ", ".join(columns.values()) if as_string else columns

    def apply_round(self, cat, expr):
        precision = str(self.cats_conf[cat]["precision"])
        return "round(" + expr + "," + precision + ")"

    def apply_alias(self, cat, expr):
        return expr + " AS " + cat

    def stats_avg_query(self):
        sql = "SELECT 'Moyenne' as Moyenne, "
        sql += self.get_select()
        sql += " FROM week_stats w"
        # sql += " WHERE team_id <> 6 OR week < 7 "
        return sql

    def stats_query(self):
        sql = "SELECT t.label AS Team, "
        sql += self.get_select()
        sql += " FROM week_stats w JOIN team t ON(w.team_id = t.team_id) "
        sql += "GROUP BY t.team_id"
        print(sql)
        return sql

    def stats_against_query(self):
        sql = "SELECT t.label AS Team, "
        sql += self.get_select()
        sql += " FROM week_stats w JOIN team t ON(w.opponent_id = t.team_id) "
        sql += "GROUP BY t.team_id"
        return sql

    def stats_diff_query(self):
        sql = "SELECT t.label AS Team"
        cols = self.get_select("AVG", table_alias="for", use_alias=False,
                               use_round=False, as_string=False)

        for cat, col in cols.items():
            col = col + " - " + col.replace("for.", "against.")
            col = self.apply_round(cat, col)
            if "invert" in self.cats_conf[cat]:
                col = "- (" + col + ")"
            col = self.apply_alias(cat, col)
            sql += ", " + col

        sql += """
            FROM week_stats against
            JOIN team t ON(against.opponent_id = t.team_id)
            JOIN week_stats for ON(t.team_id = for.team_id)
            GROUP BY t.team_id
        """
        return sql

    def create_diff_table(self):
        if self.diff_table:
            return

        select = "SELECT for.team_id, against.team_id, for.week"
        cols = self.get_select("AVG", table_alias="for", use_alias=False,
                               use_round=False, as_string=False)

        for cat, col in cols.items():
            col = col + " - " + col.replace("for.", "against.")
            col = self.apply_round(cat, col)
            if "invert" in self.cats_conf[cat]:
                col = "- (" + col + ")"
            col = self.apply_alias(cat, col)
            select += ", " + col

        select += """
            FROM week_stats against
            JOIN week_stats for ON(against.opponent_id = for.team_id
                 AND against.week = for.week)
            GROUP BY for.team_id, for.week
        """

        # Temp table creation
        table = "tmp_stats"
        self.create_tmp_stats_table(table)
        self.insert("INSERT INTO tmp_stats " + select)
        self.diff_table = True

    def create_median_table(self, win=True):
        # if self.median_table:
        #     return

        select = "SELECT team_id, week"
        cols = self.get_select("", use_alias=False,
                               use_round=False, as_string=False)

        for cat, expr in cols.items():
            col = "(SELECT COUNT(*) FROM week_stats "
            col += "WHERE team_id = w1.team_id AND week = w1.week "
            query = "SELECT median(%s) FROM week_stats w3 " % expr
            query += "WHERE w3.week = w1.week AND w1.team_id != w3.team_id"
            invert = "invert" in self.cats_conf[cat]
            lt = invert if win else not invert
            op = "<" if lt else ">"
            col += "AND %s %s (%s))" % (expr, op, query)
            select += ", " + col

        select += " FROM week_stats w1"
        select += " GROUP BY team_id, week"

        # Temp table creation
        table = "tmp_medium_stats_%s" % ("W" if win else "L")
        self.create_tmp_matchup_table(table)
        self.insert("INSERT INTO %s %s" % (table, select))
        self.median_table = True

    def stat_diff_win_query(self, win=True):
        self.create_diff_table()

        op = ">" if win else "<"
        sql = "SELECT t.label AS Team"
        for cat, conf in self.cats_conf.items():
            if not conf["real"]:
                continue
            expr = "AVG(" + cat + ")"
            query = "SELECT ifnull(" + self.apply_round(cat, expr) + ", '') FROM tmp_stats "
            query += "WHERE " + cat + " " + op + " 0 AND team_id = tmp.team_id"
            sql += self.apply_alias(cat, ", (" + query + ")")

        sql += " FROM tmp_stats AS tmp"
        sql += " JOIN team t ON(tmp.team_id = t.team_id)"
        sql += " GROUP BY tmp.team_id"

        return sql

    def close_diff_win_query(self, win=True):
        self.create_diff_table()

        sql = "SELECT t.label AS Team"
        for cat, conf in self.cats_conf.items():
            if not conf["real"]:
                continue

            if win:
                cond = cat + " > 0 AND " + cat + " <= " + str(conf["close_limit"])
            else:
                cond = cat + " >= -" + str(conf["close_limit"]) + " AND " + cat + " < 0"

            query = "SELECT COUNT(*) FROM tmp_stats"
            query += " WHERE " + cond + " AND team_id = tmp.team_id"
            sql += self.apply_alias(cat, ", (" + query + ")")

        sql += " FROM tmp_stats AS tmp"
        sql += " JOIN team t ON(tmp.team_id = t.team_id)"
        sql += " GROUP BY tmp.team_id"

        return sql

    def standings(self, sql, filename):
        self.c.execute(sql)

        results = "\t".join(map(lambda x: str(x[0]), self.c.description)) + "\n"

        for row in self.c.fetchall():
            results += "\t".join(map(str, row)) + "\n"

        self.c.execute(self.stats_avg_query())
        results += "\n" + "\t".join(map(str, self.c.fetchone())) + "\n"

        self.save_data(results, filename)

    def close_standings(self, filename):
        wins = self.con.execute(self.close_diff_win_query())
        losses = self.con.execute(self.close_diff_win_query(False))

        results = "\t".join(map(lambda x: str(x[0]), wins.description))
        results += "\tTotal\tPct\n"

        while True:
            win = wins.fetchone()
            loss = losses.fetchone()
            if win is None:
                break

            results += win[0]
            total_win = 0
            total_loss = 0
            for cat, W in OrderedDict(win).items():
                if cat in self.cats_conf:
                    results += '\t="%d-%d"' % (W, loss[cat])
                    total_win += W
                    total_loss += loss[cat]
            results += '\t="%d-%d"' % (total_win, total_loss)
            if total_win + total_loss == 0:
                results += '\n'
            else:
                results += '\t%.2f\n' % (total_win / (total_win + total_loss))

        results += "\nMargin"
        for cat, conf in self.cats_conf.items():
            if conf["real"]:
                results += "\t%s" % str(conf["close_limit"])

        self.save_data(results, filename)

    def median_standings(self, filename):
        self.create_median_table()
        self.create_median_table(False)

        sql = "SELECT t.label AS Team"
        wins = []
        losses = []

        for cat, conf in self.cats_conf.items():
            if not conf["real"]:
                continue

            win = "SUM(W.%s) / MAX(W.week)" % cat
            loss = "SUM(L.%s) / MAX(W.week)" % cat
            expr = "printf('=\"%%d-%%d\"', %s, %s)" % (win, loss)
            sql += ", " + self.apply_alias(cat, expr)
            wins.append(win)
            losses.append(loss)

        wins_expr = " + ".join(wins)
        losses_expr = " + ".join(losses)
        sql += ", printf('=\"%%d-%%d\"', %s, %s) AS Total" % (wins_expr, losses_expr)
        sql += ", round((%s) / (%s + %s + 0.0), 3) AS Pct" % (wins_expr, wins_expr, losses_expr)
        sql += " FROM tmp_medium_stats_W W JOIN team t USING (team_id)"
        sql += " JOIN tmp_medium_stats_L L USING (team_id)"
        sql += " GROUP BY W.team_id"
        sql += " ORDER BY Pct DESC"

        print(sql)
        self.c.execute(sql)
        results = "\t".join(map(lambda x: str(x[0]), self.c.description)) + "\n"

        for row in self.c.fetchall():
            results += "\t".join(map(str, row)) + "\n"

        self.save_data(results, filename)

    def value_standings(self, sql, filename):
        # Diff par rapport à la moyenne de la league
        avg = self.c.execute(self.stats_avg_query()).fetchone()
        results = self.con.execute(sql)
        min = {}
        max = {}
        data = []

        while(True):
            stats = results.fetchone()
            if stats is None:
                break
            stats = OrderedDict(stats)

            # Calculate min and max values
            for cat, value in stats.items():
                if cat not in min:
                    min[cat] = value
                elif value < min[cat]:
                    min[cat] = value
                if cat not in max:
                    max[cat] = value
                elif value > max[cat]:
                    max[cat] = value

            data.append(stats)

        # Second pass to calculate variations
        variations = []
        for stats in data:
            total = 0
            for cat, value in stats.items():
                if cat == "Team":
                    continue

                if value > avg[cat]:
                    value = 1 - (max[cat] - value) / (max[cat] - avg[cat])
                elif value < avg[cat]:
                    value = (value - min[cat]) / (avg[cat] - min[cat]) - 1
                else:
                    value = 0

                if cat in ["ERA", "WHIP"]:
                    value = 0 - value

                total += value
                stats[cat] = "%.2f" % value

            stats["total"] = "%.2f" % total
            variations.append(stats)

        results = "\t".join(map(lambda x: str(x[0]), results.description)) + "\tTotal\n"

        # Order by total value
        def getTotal(item):
            return float(item["total"])
        variations = sorted(variations, key=getTotal, reverse=True)

        for stats in variations:
            results += "\t".join(stats.values()) + "\n"

        self.save_data(results, filename)

    def calculate_stats_ratio(self):
        avg = self.calculate_row(self.c.execute(self.stats_avg_query()).fetchone(), False)
        R = avg["R"]
        del avg["R"]

        ratio = {}
        for cat, value in avg.items():
            ratio[cat] = R / value

        ratio["R"] = 1
        print(ratio)
        return ratio