Welcome to itucsdb1507’s documentation!¶

def transfer_determine():
   if request.method=='GET':
      return redirect(url_for('transfers'))
   if request.form['id']=="0":
      return redirect(url_for('transfers'))
   if request.form['submit'] == "Delete":
      id = request.form['id']
      form = request.form
      form_data={id: form['id']}
      return redirect(url_for('transfer_delete'), code=307 )
   elif request.form['submit'] == "Update":
      return render_template('transfer_update.html', id = request.form['id'],
                              teams=app.teams.select_teams(),
                              season=app.seasons.select_seasons(),
                              players=app.players.select_players())
   else:
      return redirect(url_for('transfers'))

def initialize_tables(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS OFFICIALS (
                    OFFICIAL_ID SERIAL  NOT NULL PRIMARY KEY,
                    NAME varchar(100)  NOT NULL,
                    AGE INT NOT NULL
                );""")

            connection.commit()

def add_official(self, name, age):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             cursor.execute("""
                 INSERT INTO OFFICIALS (NAME, AGE)
                 VALUES (%s, %s) """,
                 (name, age))
             connection.commit()

def delete_official(self, id):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             cursor.execute("""
                 DELETE FROM OFFICIALS
                 WHERE OFFICIAL_ID = %s""",
                 id)
             connection.commit()

def update_official(self, id, official):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ UPDATE OFFICIALS
                     SET NAME = %s, AGE = %s
                     WHERE OFFICIAL_ID = %s"""
             cursor.execute(query, (official.name, official.age, id))
             connection.commit()

def get_official(self, id):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query="""SELECT * FROM OFFICIALS WHERE OFFICIAL_ID = %s"""
             cursor.execute(query, [id])
             key,name,age = cursor.fetchone();
             return name

def get_officials(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT * FROM OFFICIALS
                 ORDER BY OFFICIAL_ID ASC"""
        cursor.execute(query)
        connection.commit()

        officials = [(key, Official(name, age))
                    for key, name, age in cursor]

        return officials

def search_officials(self, name, id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT * FROM OFFICIALS
                WHERE NAME LIKE '%s' AND CAST(AGE as VARCHAR(30)) LIKE '%s'
                ORDER BY OFFICIAL_ID ASC""" % (('%'+name+'%',id+'%'))
        cursor.execute(query)
        connection.commit()
        print(name)

        officials = [(key, Official(name, age))
                    for key, name, age in cursor]

        return officials

def initialize_tables(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS MATCHES (
                    MATCH_ID SERIAL NOT NULL PRIMARY KEY,
                    SEASON_ID int NOT NULL REFERENCES SEASONS(SEASON_ID),
                    HOME_ID int NOT NULL REFERENCES TEAMS(TEAM_ID),
                    VISITOR_ID int NOT NULL REFERENCES TEAMS(TEAM_ID),
                    OFFICIAL_ID int REFERENCES OFFICIALS(OFFICIAL_ID),
                    RESULT VARCHAR(30) NOT NULL
                    );""")

            connection.commit()

def add_match(self, match):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("""
                INSERT INTO MATCHES (SEASON_ID, HOME_ID,
                VISITOR_ID, OFFICIAL_ID, RESULT)
                VALUES (%s, %s, %s, %s, %s) """,
                (match.season_id, match.home_id, match.away_id,
                 match.official_id, match.result))
            connection.commit()

def delete_match(self, id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("""
                DELETE FROM MATCHES
                WHERE MATCH_ID = %s""",
                id)
            connection.commit()

def update_match(self, id, match):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ UPDATE MATCHES
                    SET SEASON_ID = %s, HOME_ID = %s,
                    VISITOR_ID = %s, OFFICIAL_ID = %s,
                    RESULT = %s
                    WHERE MATCH_ID = %s """
            cursor.execute(query, (match.season_id, match.home_id,
                                   match.away_id, match.official_id,
                                   match.result, id))
            connection.commit()

def get_matches(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT * FROM MATCHES
                ORDER BY MATCH_ID ASC"""
        cursor.execute(query)
        connection.commit()

        matches = [(key, Match(season_id, official_id, home_id, away_id, result,
                         season=self.app.seasons.get_season(season_id),
                         official_name=self.app.officials.get_official(official_id),
                         home_team=self.app.teams.get_team_name(home_id),
                         away_team=self.app.teams.get_team_name(away_id)))
                for key, season_id, home_id, away_id, official_id, result in cursor]

        return matches

def initialize_tables(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS TRANSFERS (
                    TRANSFER_ID SERIAL NOT NULL PRIMARY KEY,
                    SEASON_ID int NOT NULL REFERENCES SEASONS(SEASON_ID),
                    OLD_ID int NOT NULL REFERENCES TEAMS(TEAM_ID),
                    NEW_ID int NOT NULL REFERENCES TEAMS(TEAM_ID),
                    PLAYER_ID int REFERENCES PLAYERS(PLAYER_ID),
                    FEE VARCHAR(30) NOT NULL
                    );""")

            connection.commit()

def add_transfer(self, transfer):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("""
                INSERT INTO TRANSFERS (SEASON_ID, OLD_ID,
                NEW_ID, PLAYER_ID, FEE)
                VALUES (%s, %s, %s, %s, %s) """,
                (transfer.season_id, transfer.old_id, transfer.new_id,
                transfer.player_id, transfer.fee))
            connection.commit()

def delete_transfer(self, id):
  with dbapi2.connect(self.app.config['dsn']) as connection:
         cursor = connection.cursor()
         cursor.execute("""
             DELETE FROM TRANSFERS
             WHERE TRANSFER_ID = %s""",
             id)
         connection.commit()

def update_transfer(self, id, transfer):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ UPDATE TRANSFERS
                    SET SEASON_ID = %s, OLD_ID = %s,
                    NEW_ID = %s, PLAYER_ID = %s,
                    FEE = %s
                    WHERE TRANSFER_ID = %s """
            cursor.execute(query, (transfer.season_id, transfer.old_id,
                                   transfer.new_id, transfer.player_id,
                                   transfer.fee, id))
            connection.commit()

def get_transfers(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT T.TRANSFER_ID, T1.NAME, T2.NAME, S.YEAR, P.NAME, T.FEE
                FROM TRANSFERS T
                JOIN TEAMS T1 ON T1.TEAM_ID=T.OLD_ID
                JOIN TEAMS T2 ON T2.TEAM_ID=T.NEW_ID
                JOIN SEASONS S ON S.SEASON_ID=T.SEASON_ID
                JOIN PLAYERS P ON P.PLAYER_ID=T.PLAYER_ID"""
        cursor.execute(query)
        connection.commit()

        transfers = [(key, Transfer("1", "1", "1", "1", fee, season,
                                   player_name, old_team, new_team))
                    for key, old_team, new_team, season, player_name, fee in cursor]

        return transfers

def initialize_tables(self):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             cursor.execute("""CREATE TABLE IF NOT EXISTS COACHES
             (
             COACH_ID SERIAL PRIMARY KEY,
             NAME VARCHAR(50) NOT NULL,
             BIRTHDAY INTEGER NOT NULL
             ) """)
             connection.commit()

def select_coaches(self):
     with dbapi2.connect(self.app.config['dsn']) as connection:
          cursor = connection.cursor()
          query = """ SELECT * FROM COACHES ORDER BY COACH_ID ASC"""
          cursor.execute(query)
          result = cursor.fetchall()
          return result

def add_coach(self, name, birthday):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ INSERT INTO COACHES (NAME, BIRTHDAY) VALUES (%s, %s) """
             cursor.execute(query, (name, birthday))
             connection.commit()

def search_coach(self, name):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query="""SELECT * FROM COACHES c WHERE c.NAME LIKE '%s'"""% (('%'+name+'%'))
             cursor.execute(query)
             connection.commit()
             result = [(key, name,birth)
                     for key, name,birth in cursor]
             return result

def delete_coach(self, id):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ DELETE FROM COACHES WHERE COACH_ID =%s """
             cursor.execute(query, [id])
             connection.commit()

def update_coach(self, coach_id, name, birthday):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ UPDATE COACHES SET NAME = %s, BIRTHDAY= %s WHERE COACH_ID = %s """
            cursor.execute(query, (name,birthday,coach_id))
            connection.commit()

def initialize_tables(self):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             cursor.execute("""CREATE TABLE IF NOT EXISTS SEASONS
             (
             SEASON_ID SERIAL PRIMARY KEY,
             YEAR INTEGER NOT NULL
             ) """)
             connection.commit()

def select_seasons(self):
     with dbapi2.connect(self.app.config['dsn']) as connection:
          cursor = connection.cursor()
          query = """ SELECT * FROM SEASONS ORDER BY SEASON_ID ASC"""
          cursor.execute(query)
          result = cursor.fetchall()
          return result

def get_season(self, id):
     with dbapi2.connect(self.app.config['dsn']) as connection:
          cursor = connection.cursor()
          query = """ SELECT * FROM SEASONS
                      WHERE SEASON_ID = %s"""
          cursor.execute(query,[id])
          season_id,year = cursor.fetchone()
          return year

def add_season(self, year):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ INSERT INTO SEASONS (year) VALUES (%s) """
             cursor.execute(query, [year])
             connection.commit()

def search_season(self, year1):
     with dbapi2.connect(self.app.config['dsn']) as connection:
          cursor = connection.cursor()
          query="""SELECT * FROM SEASONS WHERE YEAR = %s"""
          cursor.execute(query,[year1])
          connection.commit()
          result = [(key, year)
                     for key, year in cursor]
          return result

def delete_season(self, id):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ DELETE FROM SEASONS WHERE SEASON_ID =%s """
             cursor.execute(query, [id])
             connection.commit()

def update_season(self, season_id, year):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ UPDATE SEASONS SET YEAR = %s WHERE SEASON_ID = %s """
             cursor.execute(query, (year,season_id))
             connection.commit()

def initialize_tables(self):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             cursor.execute("""CREATE TABLE IF NOT EXISTS COACHING
             (
             COACHING_ID SERIAL NOT NULL PRIMARY KEY,
             TEAM_ID INT NOT NULL REFERENCES TEAMS(TEAM_ID),
             COACH_ID INT NOT NULL REFERENCES COACHES(COACH_ID),
             SEASON_ID INT NOT NULL REFERENCES SEASONS(SEASON_ID)
             ) """)
             connection.commit()

def select_coaching(self):
     with dbapi2.connect(self.app.config['dsn']) as connection:
          cursor = connection.cursor()
          query = """SELECT * FROM COACHING"""
          cursor.execute(query)
          result = cursor.fetchall()
          return result

def get_coaching(self):
     with dbapi2.connect(self.app.config['dsn']) as connection:
          cursor = connection.cursor()
          query = """ select coaching_id, teams.name, coaches.name, seasons.year
                 from coaching
                 inner join teams on teams.team_id=coaching.team_id
                 inner join coaches on coaches.coach_id=coaching.coach_id
                 inner join seasons on seasons.season_id=coaching.season_id"""
          cursor.execute(query)
          result = cursor.fetchall()
          return result

def add_coaching(self,team_id,coach_id,season_id):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ INSERT INTO COACHING (TEAM_ID, COACH_ID, SEASON_ID) VALUES (%s, %s, %s) """
             cursor.execute(query, (team_id, coach_id, season_id))
             connection.commit()

def search_coaching(self, term):
     with dbapi2.connect(self.app.config['dsn']) as connection:
         cursor = connection.cursor()
         query="""select coaching_id, teams.name, coaches.name, seasons.year
                 from coaching
                 inner join teams on teams.team_id=coaching.team_id
                 inner join coaches on coaches.coach_id=coaching.coach_id
                 inner join seasons on seasons.season_id=coaching.season_id
                 WHERE coaches.name LIKE '%s' OR teams.name LIKE '%s'""" % (('%'+term+'%'),('%'+term+'%'))
         cursor.execute(query)
         connection.commit()
         coachlist = [(key, team, name, year)
                     for key, team, name, year in cursor]

         return coachlist

def delete_coaching(self, id):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor=connection.cursor()
             query ="""
                 DELETE FROM COACHING
                 WHERE COACHING_ID = %s"""
             cursor.execute(query,[id])
             connection.commit()

def update_coaching(self, coaching_id, team_id, coach_id, season_id):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ UPDATE COACHING
                     SET TEAM_ID = %s,
                     COACH_ID = %s,
                     SEASON_ID = %s
                     WHERE COACHING_ID = %s"""
             cursor.execute(query, [team_id, coach_id, season_id, coaching_id])
             connection.commit()

def add_country(self, country):
   with dbapi2.connect(self.app.config['dsn']) as connection:
                cursor = connection.cursor()
                cursor.execute("""
                    INSERT INTO COUNTRIES (NAME, ABBREVIATION)
                    VALUES (%s, %s) """, (country.name,country. abbreviation))
                connection.commit()

def delete_country(self, id):
   with dbapi2.connect(self.app.config['dsn']) as connection:
       cursor = connection.cursor()
       query = """ DELETE FROM COUNTRIES WHERE COUNTRY_ID =%s """
       cursor.execute(query, [id])
       connection.commit()

def update_country(self, country_id, country):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ UPDATE COUNTRIES
                             SET NAME = %s, ABBREVIATION = %s
                         WHERE COUNTRY_ID = %s """
             cursor.execute(query, (country.name,country. abbreviation,country_id ))
             connection.commit()

def get_country(self, id):
   with dbapi2.connect(self.app.config['dsn']) as connection:
          cursor = connection.cursor()
          query = """ SELECT * FROM COUNTRIES WHERE COUNTRY_ID =%s """
          cursor.execute(query, [id])

          connection.commit()
          result = cursor.fetchone()
          country = Country(result[1], result[2])
          return country

def get_countries(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT * FROM COUNTRIES ORDER BY NAME"""
        cursor.execute(query)
        connection.commit()
        countries = [(key, Country(name, abbreviation))
                    for key, name, abbreviation in cursor]

        return countries

def search_countries(self, search_terms):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT * FROM COUNTRIES WHERE NAME LIKE '%s' ORDER BY NAME"""
                                                % (('%'+search_terms+'%'))
        cursor.execute(query)
        connection.commit()
        countries = [(key, Country(name, abbreviation))
                    for key, name, abbreviation in cursor]
        return countries

def add_league(self, league):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("""
                INSERT INTO LEAGUES (NAME, ABBREVIATION, COUNTRY_ID)
                VALUES (%s, %s, %s) """,
                (league.name, league.abbreviation, league.countryID))
            connection.commit()

def delete_league(self, id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ DELETE FROM LEAGUES WHERE LEAGUE_ID =%s """
            cursor.execute(query, [id])
            connection.commit()

def update_league(self, league_id, name, abbreviation, country_id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ UPDATE LEAGUES
                            SET NAME = %s, ABBREVIATION = %s, COUNTRY_ID = %s
                        WHERE LEAGUE_ID = %s """
            cursor.execute(query, (name, abbreviation, country_id, league_id))
            connection.commit()

def get_league(self, league_id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT * FROM LEAGUES
                    WHERE LEAGUE_ID =%s """
        cursor.execute(query, [league_id])
        connection.commit()

        league_id, name, abbreviation, country_id = cursor.fetchone()
        return league_id, name, abbreviation, country_id

def get_leagues(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT L.LEAGUE_ID, L.NAME, L.ABBREVIATION, C.NAME
                    FROM LEAGUES L
                    LEFT JOIN COUNTRIES C ON (L.COUNTRY_ID = C.COUNTRY_ID)
                    """

        cursor.execute(query)
        connection.commit()

        leagues = [(league_id, name, abbreviation, country_name)
                    for league_id, name, abbreviation, country_name in cursor]

        return leagues

def search_leagues(self, search_terms):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT L.LEAGUE_ID, L.NAME, L.ABBREVIATION, C.NAME
                    FROM LEAGUES L
                    LEFT JOIN COUNTRIES C ON (L.COUNTRY_ID = C.COUNTRY_ID)
                    WHERE L.NAME LIKE '%s' ORDER BY L.NAME"""
                                % (('%'+search_terms+'%'))

        cursor.execute(query)
        connection.commit()

        leagues = [(league_id, name, abbreviation, country_name)
                    for league_id, name, abbreviation, country_name in cursor]

        return leagues

def add_stadium(self, name, capacity, country_id, team_id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query ="""
                INSERT INTO STADIUMS (NAME, CAPACITY, COUNTRY_ID, TEAM_ID)
                    VALUES (%s, %s, %s, %s) """
            cursor.execute(query, (name, capacity, country_id, team_id))
            connection.commit()

def delete_stadium(self, stadium_id):
 with dbapi2.connect(self.app.config['dsn']) as connection:
         cursor = connection.cursor()
         query = """ DELETE FROM STADIUMS WHERE STADIUM_ID =%s """
         cursor.execute(query, [stadium_id])
         connection.commit()

def update_stadium(self, stadium_id, name, capacity, country_id, team_id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ UPDATE STADIUMS
                            SET NAME=%s, CAPACITY=%s, COUNTRY_ID=%s, TEAM_ID=%s
                        WHERE STADIUM_ID = %s """
            cursor.execute(query, (name, capacity, country_id, team_id, stadium_id))
            connection.commit()

def get_stadium(self, stadium_id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT * FROM STADIUMS
                    WHERE STADIUM_ID =%s """
        cursor.execute(query, (stadium_id))
        connection.commit()

        stadium_id, name, capacity, country_id, team_id = cursor.fetchone()
        return stadium_id, name, capacity, country_id, team_id

def get_stadiums(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT S.STADIUM_ID, S.NAME, S.CAPACITY, C.NAME, T.NAME
                    FROM  STADIUMS S
                    LEFT JOIN COUNTRIES C ON (S.COUNTRY_ID = C.COUNTRY_ID)
                    LEFT JOIN TEAMS T ON (S.TEAM_ID = T.TEAM_ID)
                    """
        cursor.execute(query)
        connection.commit()

        stadiums = [(key, name, capacity, country, team)
                    for key, name, capacity, country, team in cursor]

        return stadiums

def search_stadiums(self, search_terms):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT S.STADIUM_ID, S.NAME, S.CAPACITY, C.NAME, T.NAME
                    FROM  STADIUMS S
                    LEFT JOIN COUNTRIES C ON (S.COUNTRY_ID = C.COUNTRY_ID)
                    LEFT JOIN TEAMS T ON (S.TEAM_ID = T.TEAM_ID)
                    WHERE S.NAME LIKE '%s' ORDER BY S.NAME"""
                                        % (('%'+search_terms+'%'))
        cursor.execute(query)
        connection.commit()
        stadiums = [(key, name, capacity, country, team)
                    for key, name, capacity, country, team in cursor]

        return stadiums

def add_fixture(self, season_id, team_id, points):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ INSERT INTO FIXTURES (SEASON_ID, TEAM_ID, POINTS) VALUES
              (%s, %s, %s) """
            cursor.execute(query, (season_id, team_id, points))
            connection.commit()

def delete_fixture(self, id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("""
                DELETE FROM FIXTURES
                WHERE FIXTURE_ID = %s""",
                id)
            connection.commit()

def update_fixture(self, fixture_id, season_id, team_id, points):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ UPDATE FIXTURES
                    SET SEASON_ID = %s,
                    TEAM_ID = %s,
                    POINTS = %s
                    WHERE FIXTURE_ID = %s"""
            cursor.execute(query, (season_id, team_id, points, fixture_id))
            connection.commit()

def search_fixture(self, id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT FIXTURE_ID, SEASONS.YEAR, TEAMS.NAME, POINTS
                FROM FIXTURES
                INNER JOIN SEASONS ON SEASONS.SEASON_ID=FIXTURES.SEASON_ID
                INNER JOIN TEAMS ON TEAMS.TEAM_ID=FIXTURES.TEAM_ID
                WHERE TEAMS.NAME LIKE '%s'""" % ('%'+id+'%')
        cursor.execute(query)
        connection.commit()

        result = cursor.fetchall()
        return result

def get_fixtures(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT F.FIXTURE_ID, S.YEAR, T.NAME, F.POINTS
                    FROM FIXTURES F
                    LEFT JOIN SEASONS S ON (F.SEASON_ID = S.SEASON_ID)
                    LEFT JOIN TEAMS T ON (F.TEAM_ID = T.TEAM_ID)
                    ORDER BY S.YEAR ASC"""
        cursor.execute(query)
        connection.commit()

        fixtures = [(key, season, team, points)
                    for key, season, team, points in cursor]
        return fixtures

def add_statistic_player(self, season_id, player_id, tackles, penalties):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ INSERT INTO STATISTICSP (SEASON_ID, PLAYER_ID, tackles,
              penalties) VALUES (%s, %s, %s, %s) """
            cursor.execute(query, (season_id, player_id, tackles, penalties))
            connection.commit()

def delete_statistic_player(self, id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("""
                DELETE FROM STATISTICSP
                WHERE STATISTIC_ID = %s""",
                id)
            connection.commit()

def update_statistic_player(self, statistic_id, season_id, player_id, tackles,
  penalties):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ UPDATE STATISTICSP
                    SET SEASON_ID = %s,
                    PLAYER_ID = %s,
                    TACKLES = %s,
                    PENALTIES = %s
                    WHERE STATISTIC_ID = %s"""
            cursor.execute(query, (season_id, player_id, tackles, penalties,
              statistic_id))
            connection.commit()

def search_statistic_player(self, id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT STATISTIC_ID, SEASONS.YEAR, PLAYERS.NAME, TACKLES, PENALTIES
                FROM STATISTICSP
                INNER JOIN SEASONS ON SEASONS.SEASON_ID=STATISTICSP.SEASON_ID
                INNER JOIN PLAYERS ON PLAYERS.PLAYER_ID=STATISTICSP.PLAYER_ID
                WHERE PLAYERS.NAME LIKE '%s'""" % ('%'+id+'%')
        cursor.execute(query)
        connection.commit()

        result = cursor.fetchall()
        return result

def get_statistics_player(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT S.STATISTIC_ID, SS.YEAR, P.NAME, S.TACKLES, S.PENALTIES
                    FROM STATISTICSP S
                    LEFT JOIN SEASONS SS ON (S.SEASON_ID = SS.SEASON_ID)
                    LEFT JOIN PLAYERS P ON (S.PLAYER_ID = P.PLAYER_ID)
                    ORDER BY SS.YEAR ASC"""
        cursor.execute(query)
        connection.commit()

        statisticsp = [(key, season, player, tackles, penalties)
                    for key, season, player, tackles, penalties in cursor]
        return statisticsp

def add_statistic_team(self, season_id, team_id, touchdowns, rushingYards):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ INSERT INTO STATISTICST (SEASON_ID, TEAM_ID, touchdowns,
              rushingYards) VALUES (%s, %s, %s, %s) """
            cursor.execute(query, (season_id, team_id, touchdowns, rushingYards))
            connection.commit()

def delete_statistic_team(self, id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("""
                DELETE FROM STATISTICST
                WHERE STATISTIC_ID = %s""",
                id)
            connection.commit()

def update_statistic_team(self, statistic_id, season_id, team_id, touchdowns,
  rushingYards):
    with dbapi2.connect(self.app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """ UPDATE STATISTICST
                    SET SEASON_ID = %s,
                    TEAM_ID = %s,
                    TOUCHDOWNS = %s,
                    RUSHINGYARDS = %s
                    WHERE STATISTIC_ID = %s"""
            cursor.execute(query, (season_id, team_id, touchdowns, rushingYards,
              statistic_id))
            connection.commit()

def search_statistic_team(self, id):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT STATISTIC_ID, SEASONS.YEAR, TEAMS.NAME, TOUCHDOWNS,
           RUSHINGYARDS
                FROM STATISTICST
                INNER JOIN SEASONS ON SEASONS.SEASON_ID=STATISTICST.SEASON_ID
                INNER JOIN TEAMS ON TEAMS.TEAM_ID=STATISTICST.TEAM_ID
                WHERE TEAMS.NAME LIKE '%s'""" % ('%'+id+'%')
        cursor.execute(query)
        connection.commit()

        result = cursor.fetchall()
        return result

def get_statistics_team(self):
    with dbapi2.connect(self.app.config['dsn']) as connection:
        cursor = connection.cursor()
        query="""SELECT S.STATISTIC_ID, SS.YEAR, T.NAME, S.TOUCHDOWNS,
           S.RUSHINGYARDS
                    FROM STATISTICST S
                    LEFT JOIN SEASONS SS ON (S.SEASON_ID = SS.SEASON_ID)
                    LEFT JOIN TEAMS T ON (S.TEAM_ID = T.TEAM_ID)
                    ORDER BY SS.YEAR ASC"""
        cursor.execute(query)
        connection.commit()

        statisticst = [(key, season, team, touchdowns, rushingYards)
                    for key, season, team, touchdowns, rushingYards in cursor]
        return statisticst

CREATE TABLE IF NOT EXISTS PLAYERS
                    ( PLAYER_ID serial NOT NULL PRIMARY KEY,
                      NAME varchar(100) NOT NULL,
                      BIRTHDAY date NOT NULL,
                      POSITION varchar(100) NOT NULL
                    )

@app.route('/players', methods=['GET', 'POST'])
def players():
    if request.method == 'GET':
        return render_template('players.html', players=app.players.select_players())
    else:
        name = request.form['name']
        birthday = request.form['birthday']
        position = request.form['position']
        app.players.add_player(name, birthday, position)
    return redirect(url_for('players'))

def select_players(self):
     with dbapi2.connect(self.app.config['dsn']) as connection:
          cursor = connection.cursor()
          query = """ SELECT * FROM PLAYERS ORDER BY PLAYER_ID"""
          cursor.execute(query)
          players = cursor.fetchall()
          return players

@app.route('/players/add', methods=['GET', 'POST'])
def add_players():
 return render_template('players_add.html')

@app.route('/players', methods=['GET', 'POST'])
def players():
    if request.method == 'GET':
        return render_template('players.html', players=app.players.select_players())
    else:
        name = request.form['name']
        birthday = request.form['birthday']
        position = request.form['position']
        app.players.add_player(name, birthday, position)
    return redirect(url_for('players'))

def add_player(self, name, birthday, position):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ INSERT INTO PLAYERS (NAME, BIRTHDAY, POSITION)
                     VALUES (%s, %s, %s) """
             cursor.execute(query, (name, birthday, position))
             connection.commit()

@app.route('/players/update/<player_id>', methods=['GET', 'POST'])
def update_players(player_id):
    if request.method == 'GET':
        return render_template('players_edit.html',
        player = app.players.get_player(player_id))
    else:
        name = request.form['name']
        birthday = request.form['birthday']
        position = request.form['position']
        app.players.update_player(player_id, name, birthday, position)
        return redirect(url_for('players'))

def get_player(self, player_id):
     with dbapi2.connect(self.app.config['dsn']) as connection:
          cursor = connection.cursor()
          query = """ SELECT * FROM PLAYERS WHERE PLAYER_ID = %s """
          cursor.execute(query, [player_id])
          player = cursor.fetchall()
          return player

def update_player(self, player_id, name, birthday, position):
     with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ UPDATE PLAYERS
                     SET NAME = %s,
                     BIRTHDAY = %s,
                     POSITION = %s
                     WHERE
                     PLAYER_ID = %s """
             cursor.execute(query, (name, birthday, position, player_id))
             connection.commit()

@app.route('/players/delete/<player_id>', methods=['GET', 'POST'])
def delete_players(player_id):
    app.players.delete_player(player_id)
    return redirect(url_for('players'))

def delete_player(self, player_id):
      with dbapi2.connect(self.app.config['dsn']) as connection:
             cursor = connection.cursor()
             query = """ DELETE FROM PLAYERS
                     WHERE PLAYER_ID = %s """
             cursor.execute(query, [player_id])
             connection.commit()

@app.route('/players/search', methods = ['GET', 'POST'])
def search_players():
 if request.method == 'GET':
     return redirect(url_for('players_search.html'))
 else:
     searchname = request.form['nametosearch']
     return render_template('players_search.html',
     players = app.players.search_player(searchname))