Welcome to itucsdb1519’s documentation!¶

• Teams table keeping record of the teams data

  Name	Type	Not Null	Primary K.
  ID	INTEGER	0	1
  NATION	VARCHAR	0	0
  GENDER	VARCHAR	0	0
  FOUNDDATE	VARCHAR	0	0
  TIMESWON	VARCHAR	0	0

• nation keeps the record of nation of the given team.

• gender keeps the record of gender of the given team.

• founddate keeps the record of the found date of the given team.

• timeswon keeps the record of how many times team has won the game.

Sql statement that initialize the teams table:

CREATE TABLE TEAMS (
        ID SERIAL PRIMARY KEY,
        NATION VARCHAR(45),
        GENDER VARCHAR(6),
        FOUNDDATE VARCHAR(20),
        TIMESWON VARCHAR(10)
        )

• Players table keeping record of the players data

  Name	Type	Not Null	Primary K.
  ID	INTEGER	0	1
  NAME	VARCHAR	0	0
  GENDER	VARCHAR	0	0
  NATION	VARCHAR	0	0
  BIRTHDATE	VARCHAR	0	0
  TEAM	INTEGER	0	0

• name keeps the record of name of the given player.

• gender keeps the record of gender of the given player.

• nation keeps the record of nation of the given player.

• birthdate keeps the record of birth date of the given player.

• team references to teams table and on delete and update operations it cascades the operation

Sql statement that initialize the players table:

CREATE TABLE PLAYERS (
        ID SERIAL PRIMARY KEY,
        NAME VARCHAR(45),
        GENDER VARCHAR(6),
        NATION VARCHAR(45),
        BIRTHDATE VARCHAR(10),
        TEAM INTEGER REFERENCES TEAMS ON DELETE CASCADE ON UPDATE CASCADE
        )

• Technic members table keeping record of the technic members data

  Name	Type	Not Null	Primary K.
  ID	INTEGER	0	1
  NAME	VARCHAR	0	0
  GENDER	VARCHAR	0	0
  NATION	VARCHAR	0	0
  BIRTHDATE	VARCHAR	0	0
  COACH	INTEGER	0	0

• name keeps the record of name of the given technic member.

• gender keeps the record of gender of the given technic member.

• nation keeps the record of nation of the given technic member.

• birthdate keeps the record of birth date of the given technic member.

• coach references to coach table and on delete and update operations it cascades the operation

Sql statement that initialize the technic members table:

CREATE TABLE TECHNICMEMBERS (
        ID SERIAL PRIMARY KEY,
        NAME VARCHAR(45),
        GENDER VARCHAR(6),
        NATION VARCHAR(45),
        BIRTHDATE VARCHAR(10),
        COACH INTEGER REFERENCES COACHES ON DELETE CASCADE ON UPDATE CASCADE
        )

• Teams table class :

  class team:
     def __init__(self, nation, gender, foundDate, timesWon):
        self.nation = nation
        self.gender = gender
        self.foundDate = foundDate
        self.timesWon = timesWon
  

• Players table class :

  class player:
     def __init__(self, name, gender, nation, birthDate, team):
        self.name = name
        self.gender = gender
        self.nation = nation
        self.birthDate = birthDate
        self.team = team
  

• Technic members table class :

  class tm:
     def __init__(self, name, gender, nation, birthDate, coach):
        self.name = name
        self.gender = gender
        self.nation = nation
        self.birthDate = birthDate
        self.coach = coach
  

Store classes is implemented in store.py file. In store classes database is handling via some functions. Beginning of store.py is like this:

import psycopg2 as dbapi2

from technicmember import tm
from player import player
from team import team

from config import app

It imports psycopg2 editor as a dbapi2 for using as database api. Classes tm, player and team also imported. From config.py file it imports app object. In config.py file app object implemented in this way:

from flask import Flask

app = Flask(__name__)

app.debug = True

• class StoreTeam init function and createTable function is implemented like this:

  class StoreTeam:
     def __init__(self, dbSettings):
        self.dsn = dbSettings
  
     def createTable(self, dsn):
        try:
           connection = dbapi2.connect(dsn)
           cursor = connection.cursor()
           statement = """ CREATE TABLE TEAMS (
           ID SERIAL PRIMARY KEY,
           NATION VARCHAR(45),
           GENDER VARCHAR(6),
           FOUNDDATE VARCHAR(20),
           TIMESWON VARCHAR(10)
           )"""
           cursor.execute(statement)
           connection.commit()
           cursor.close()
        except dbapi2.DatabaseError:
           connection.rollback()
        finally:
           connection.close()
  

createTable() function makes the connection with database via dbapi2 database api. cursor variable created as a cursor of connection and statement variable keeps the statement of SQL for creating table in database. After cursor execution and connection committing try, except and finally block handles the exceptions. If any error occurs connection rollback else connection closes.

all functions which needs to handle some operations on database uses the with .. as context manager of psycopg2

• addTeam() function of class StoreTeam:

  def addTeam(self, team, dsn):
     with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            cursor.execute("INSERT INTO TEAMS (NATION, GENDER, FOUNDDATE, TIMESWON) VALUES(%s, %s, %s, %s)", (team.nation, team.gender, team.foundDate, team.timesWon))
  

This function gets a team object from teams.py file html-side function. It adds the team object as a tuple into the database. It executes the SQL statement into the database.

• deleteTeam() function of class StoreTeam:

  def deleteTeam(self, id, dsn):
     with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """ DELETE FROM TEAMS WHERE ID = {}""".format(id)
            cursor.execute(query)
  

This function gets the id of the tuple to be deleted. It deletes the tuple from the database.

• updateTeam() function of class StoreTeam:

  def updateTeam(self, team, id, dsn):
     with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """UPDATE TEAMS SET NATION = '{}', GENDER = '{}', FOUNDDATE = '{}', TIMESWON = '{}' WHERE ID = {} """.format(team.nation, team.gender, team.foundDate, team.timesWon, id)
            cursor.execute(query)
  

This function gets the id of the tuple to be updated. It reaches the tuple with its’ id and update the tuple with the team object which it gets.

• getAllTeams() function of class StoreTeam:

  def getAllTeams(self, dsn):
     with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """ SELECT * FROM TEAMS """
            cursor.execute(query)
            teams = cursor.fetchall()
            return teams
  

This function select all teams and return all teams as an array.

• selectTeams() function of class StoreTeam:

  def selectTeams(self, team, dsn):
     with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """SELECT * FROM TEAMS WHERE(NATION LIKE  '{}%' ) AND (GENDER LIKE '{}%' ) AND (FOUNDDATE LIKE '{}%' ) AND (TIMESWON LIKE '{}%' )""".format(team.nation, team.gender, team.foundDate, team.timesWon)
            cursor.execute(query)
            teams = cursor.fetchall()
            return teams
  

This function select teams with a specific search. It returns the team table tuples which it found as an array.

• createInitTeams() function of class StoreTeam:

  def createInitTeams(self, dsn):
     app.storeT = StoreTeam(app.config['dsn'])
  
     newTeam = team('Turkey', 'Male', '1920', '4')
     app.storeT.addTeam(newTeam, dsn)
     newTeam2 = team('England', 'Male', '1936', '3')
     app.storeT.addTeam(newTeam2, dsn)
     newTeam3 = team('China', 'Male', '1906','5')
     app.storeT.addTeam(newTeam3, dsn)
     newTeam4 = team('Russia', 'Female', '1943','1')
     app.storeT.addTeam(newTeam4, dsn)
  

This function creates initial elements when database has initialized. It uses add function to create initial tuples.

• class StoreP init function and createTable() function:

  def __init__(self, dbSettings):
     self.dsn = dbSettings
  
  def createTable(self, dsn):
     try:
        connection = dbapi2.connect(dsn)
        cursor = connection.cursor()
        statement = """ CREATE TABLE PLAYERS (
        ID SERIAL PRIMARY KEY,
        NAME VARCHAR(45),
        GENDER VARCHAR(6),
        NATION VARCHAR(45),
        BIRTHDATE VARCHAR(10),
        TEAM INTEGER REFERENCES TEAMS ON DELETE CASCADE ON UPDATE CASCADE
        )"""
        cursor.execute(statement)
        connection.commit()
        cursor.close()
     except dbapi2.DatabaseError:
        connection.rollback()
     finally:
        connection.close()
  

createTable() function works like StoreTeam class’ createTable() function.

Also in StoreP functions with .. as context manager of psycopg2 has used.

• addPlayer() function of class StoreP:

  def addPlayer(self, player, dsn):
    with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            cursor.execute("INSERT INTO PLAYERS (NAME, GENDER, NATION, BIRTHDATE, TEAM) VALUES(%s, %s, %s, %s, %s)", (player.name, player.gender, player.nation, player.birthDate, player.team))
  

This function works as same as addTeam function of StoreTeam.

• deletePlayer() function of class StoreP:

  def deletePlayer(self, id, dsn):
    with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """ DELETE FROM PLAYERS WHERE ID = {}""".format(id)
            cursor.execute(query)
  

This function works as same as deleteTeam function of StoreTeam.

• updatePlayer() function of class StoreP:

  def updatePlayer(self, player, id, dsn):
    with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """UPDATE PLAYERS SET NAME = '{}', GENDER = '{}', NATION = '{}', BIRTHDATE = '{}', TEAM = '{}' WHERE ID = {} """.format(player.name, player.gender, player.nation, player.birthDate, player.team, id)
            cursor.execute(query)
  

This function also works as same as updateTeam function of StoreTeam.

• getAllPlayers() function of class StoreP:

  def getAllPlayers (self, dsn):
    with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """ SELECT PLAYERS.ID, PLAYERS.NAME, PLAYERS.GENDER, PLAYERS.NATION, PLAYERS.BIRTHDATE, TEAMS.NATION FROM PLAYERS INNER JOIN TEAMS ON TEAMS.ID = PLAYERS.TEAM """
            cursor.execute(query)
            players = cursor.fetchall()
            return players
  

This function also works as same as getAllTeams() function of StoreTeam.

• selectPlayers() function of class StoreP:

  def selectPlayers(self, player, dsn):
    with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """SELECT PLAYERS.ID, PLAYERS.NAME, PLAYERS.GENDER, PLAYERS.NATION, PLAYERS.BIRTHDATE, TEAMS.NATION
            FROM PLAYERS INNER JOIN TEAMS ON TEAMS.ID = PLAYERS.TEAM
            WHERE(PLAYERS.NAME LIKE  '{}%' ) AND (PLAYERS.GENDER LIKE '{}%' ) AND
            (PLAYERS.NATION LIKE '{}%' ) AND (PLAYERS.BIRTHDATE LIKE '{}%' ) AND
            (TEAMS.NATION LIKE '{}%' ) """.format(player.name, player.gender, player.nation, player.birthDate, player.team)
            cursor.execute(query)
            players = cursor.fetchall()
            return players
  

This function also select players with a specific search. The SQL statement joins the teams and players tables and searches what to search in joined tables. After that it returns the players table tuples which it found as an array.

• createInitPlayers() function of class StoreP:

  def createInitPlayers(self,dsn):
    app.store = StoreP(app.config['dsn'])
  
    newPlayer = player('Hasan', 'Male', 'Turkish', '1994', 1)
    app.store.addPlayer(newPlayer, dsn)
    newPlayer2 = player('Rose', 'Female', 'English', '1995', 2)
    app.store.addPlayer(newPlayer2, dsn)
    newPlayer3 = player('Dimitrov', 'Male', 'Russian', '1993', 4)
    app.store.addPlayer(newPlayer3, dsn)
  

This function creates initial elements when database has initialized. It uses add function to create initial tuples.

• class StoreTM init function and createTable() function:

  def __init__(self, dbSettings):
    self.dsn = dbSettings
  
  def createTable(self, dsn):
    try:
        connection = dbapi2.connect(dsn)
        cursor = connection.cursor()
        statement = """ CREATE TABLE TECHNICMEMBERS (
        ID SERIAL PRIMARY KEY,
        NAME VARCHAR(45),
        GENDER VARCHAR(6),
        NATION VARCHAR(45),
        BIRTHDATE VARCHAR(10),
        COACH INTEGER REFERENCES COACHES ON DELETE CASCADE ON UPDATE CASCADE
        )"""
        cursor.execute(statement)
        connection.commit()
        cursor.close()
     except dbapi2.DatabaseError:
        connection.rollback()
     finally:
        connection.close()
  

createTable() function works like StoreTeam class’ createTable() function.

Also in StoreTM functions with .. as context manager of psycopg2 has used.

• addTm() function of class StoreTM:

  def addTm(self, tm, dsn):
    with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            cursor.execute("INSERT INTO TECHNICMEMBERS (NAME, GENDER, NATION, BIRTHDATE, COACH) VALUES(%s, %s, %s, %s, %s)", (tm.name, tm.gender, tm.nation, tm.birthDate, tm.coach))
  

This function works as same as addTeam function of StoreTeam.

• deleteTm() function of class StoreTM:

  def deleteTm(self, id, dsn):
    with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """ DELETE FROM TECHNICMEMBERS WHERE ID = {}""".format(id)
            cursor.execute(query)
  

This function works as same as deleteTeam function of StoreTeam.

• updateTm() function of class StoreTM:

  def updateTm(self, tm, id, dsn):
    with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """UPDATE TECHNICMEMBERS SET NAME= '{}', GENDER = '{}', NATION = '{}', BIRTHDATE = '{}', COACH = '{}' WHERE ID = {} """.format(tm.name, tm.gender, tm.nation, tm.birthDate, tm.coach, id)
            cursor.execute(query)
  

This function also works as same as updateTeam function of StoreTeam.

• getAllTms() function of class StoreTM:

  def getAllTms (self, dsn):
    with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """ SELECT TECHNICMEMBERS.ID, TECHNICMEMBERS.NAME, TECHNICMEMBERS.GENDER, TECHNICMEMBERS.NATION, TECHNICMEMBERS.BIRTHDATE, COACHES.NAME FROM TECHNICMEMBERS INNER JOIN COACHES ON COACHES.ID = TECHNICMEMBERS.COACH """
            cursor.execute(query)
            tms = cursor.fetchall()
            return tms
  

This function also works as same as getAllTeams() function of StoreTeam.

• selectTms() function of class StoreTM:

  def selectTms(self, tm, dsn):
    with dbapi2.connect(dsn) as connection:
        with connection.cursor() as cursor:
            query = """SELECT TECHNICMEMBERS.ID, TECHNICMEMBERS.NAME, TECHNICMEMBERS.GENDER, TECHNICMEMBERS.NATION, TECHNICMEMBERS.BIRTHDATE, COACHES.NAME
            FROM TECHNICMEMBERS INNER JOIN COACHES ON COACHES.ID = TECHNICMEMBERS.COACH
            WHERE(TECHNICMEMBERS.NAME LIKE  '{}%' ) AND (TECHNICMEMBERS.GENDER LIKE '{}%' ) AND
            (TECHNICMEMBERS.NATION LIKE '{}%' ) AND (TECHNICMEMBERS.BIRTHDATE LIKE '{}%' ) AND
            (COACHES.NAME LIKE '{}%' ) """.format(tm.name, tm.gender, tm.nation, tm.birthDate, tm.coach)
            cursor.execute(query)
            tms = cursor.fetchall()
            return tms
  

This function also select players with a specific search. The SQL statement joins the technicmembers and coaches tables and searches what to search in joined tables. After that it returns the technicmembers table tuples which it found as an array.

• createInitTMs() function of class StoreTM:

  def createInitTMs(self, dsn):
    app.storeT = StoreTM(app.config['dsn'])
  
    newTm = tm('Veli', 'Male', 'Turkish', '1978', 1)
    app.storeT.addTm(newTm, dsn)
    newTm = tm('Ayşe', 'Female', 'Turkish', '1978', 1)
    app.storeT.addTm(newTm, dsn)
    newTm = tm('Jane', 'Female', 'English', '1982', 2)
    app.storeT.addTm(newTm, dsn)
  

This function creates initial elements when database has initialized. It uses add function to create initial tuples.

• Import part of the teams.py file

  from flask import redirect
  from flask import render_template
  from flask import request
  from flask import url_for
  
  from config import app
  from team import team
  from store import StoreTeam
  import psycopg2 as dbapi2
  

redirect, render_template, request and url_for features of Flask web framework have used. Like in store classes app object of Flask has imported from config. team class has imported. StoreTeam class has imported from store file. pyscopg2 has imported as a dbapi2 for use as a database api.

• teams function for rendering teams.html file

  @app.route('/teams', methods = ['GET', 'POST'])
  def teams():
  
  dsn = app.config['dsn']
  
  app.store = StoreTeam(dsn)
  
  
  if request.method == 'GET':
     allTeams = app.store.getAllTeams(dsn)
  

This part of teams function renders the url ‘/teams’ with teams.html file. Uses ‘GET’ and ‘POST’ methods. Uses dsn as a database settings which is implementing in config.py whether ElephantSQL or Vagrant database system. app.store variable assigned to a StoreTeam object. All StoreTeam object methods can be used via app.store variable. If request.method equals to ‘GET’ method getAllTeams function of StoreTeam object has invoked and gets all teams as and array and passed them to html file. After all operations allTeams variable assigned to an array which holds all the teams data. At the end of the function allTeams variable send to html file for listing.

• delete method

  elif 'delete' in request.form:
     ids = request.form.getlist('teams')
     for id in ids:
        app.store.deleteTeam(id, dsn)
     allTeams = app.store.getAllTeams(dsn)
  

If request method from user is delete this part of teams function has invoked. It gets the clicked checkbox ids and passed that ids one by one to the deleteTeam() function of StoreTeam class. After that tuples get deleted.

• add method

  elif 'add' in request.form:
     nation = request.form['nationToAdd']
     gender = request.form['genderToAdd']
     foundDate = request.form['foundDateToAdd']
     timesWon = request.form['timesWonToAdd']
     newTeam = team(nation, gender, foundDate, timesWon)
     app.store.addTeam(newTeam, dsn)
     allTeams = app.store.getAllTeams(dsn)
  

If request method from user is add this part of teams function has invoked. Datas in the add textboxes has passed as a attributes of a team object and addTeam function of StoreTeam class added the tuple using team object attributes as a tuple attributes.

• update method

  elif 'update' in request.form:
     ids = request.form.getlist('teams')
     id = ids[0]
     nation = request.form['nationToUpdate']
     gender = request.form['genderToUpdate']
     foundDate = request.form['foundDateToUpdate']
     timesWon = request.form['timesWonToUpdate']
     updatedTeam = team(nation, gender, foundDate, timesWon)
     app.store.updateTeam(updatedTeam, id, dsn)
     allTeams = app.store.getAllTeams(dsn)
  

If request method from user is update this part of teams function has invoked. Datas in update textboxes has passed as a attributes of a team object and id of the tuple that needs to be update. After that updateTeam function StoreTeam class updated the necessary tuple using team object attributes as a tuple attributes.

• find method

  elif 'find' in request.form:
     nation = request.form['nationToFind']
     gender = request.form['genderToFind']
     foundDate = request.form['foundDateToFind']
     timesWon = request.form['timesWonToFind']
     findTeam = team(nation, gender, foundDate, timesWon)
     allTeams = app.store.selectTeams(findTeam, dsn)
  
  
  return render_template('teams.html', teams = allTeams)
  

Finally if request method from user is find this part of teams function has invoked. Datas in find textboxes has passed as a attributes of a team object and selectTeams function of StoreTeam find the searched datas with SQL operation in the database and returns an array of teams that found.

At the end of function teams.html file rendered with this python code and allTeams attribute passed to html file to be listed.

• Import part of the players.py file

  from flask import redirect
  from flask import render_template
  from flask import request
  from flask import url_for
  
  from config import app
  from player import player
  from store import StoreP
  from store import StoreTeam
  import psycopg2 as dbapi2
  

Like in teams.py file necessary imports for Flask web framework use has implemented. player class has imported. Both StoreP and StoreTeam classes has imported because of the team foreign key in the players table. After all operations allPlayers variable assigned to an array which holds allPlayers data as well. At the end of the function allPlayers variable send to html file for listing and also allTeams variable too.

• players function for rendering players.html file

  @app.route('/players', methods = ['GET', 'POST'])
  def players():
  
     dsn = app.config['dsn']
  
     app.store = StoreP(dsn)
  
     app.storeT = StoreTeam(dsn)
     allTeams = app.storeT.getAllTeams(dsn)
  
     if request.method == 'GET':
        allPlayers = app.store.getAllPlayers(dsn)
  

This part of players function works as same as teams function in teams.py file. With a little difference it has another app.storeT variable for using StoreTeam class for getting all teams for foreign key of players table team attribute.

• delete method

  elif 'delete' in request.form:
    ids = request.form.getlist('players')
    for id in ids:
        app.store.deletePlayer(id, dsn)
    allPlayers = app.store.getAllPlayers(dsn)
  

This part of players function works as same as teams function of teams.py file.

• add method

  elif 'add' in request.form:
    name = request.form['nameToAdd']
    gender = request.form['genderToAdd']
    nation = request.form['nationToAdd']
    birthDate = request.form['birthDateToAdd']
    team = request.form['teamToAdd']
    newPlayer = player(name, gender, nation, birthDate, team)
    app.store.addPlayer(newPlayer, dsn)
    allPlayers = app.store.getAllPlayers(dsn)
  

This part of players function also works as same as teams function of teams.py file.

• update method

  elif 'update' in request.form:
    ids = request.form.getlist('players')
    id = ids[0]
    name = request.form['nameToUpdate']
    gender = request.form['genderToUpdate']
    nation = request.form['nationToUpdate']
    birthDate = request.form['birthDateToUpdate']
    team = request.form['teamToUpdate']
    updatedPlayer = player(name, gender, nation, birthDate, team)
    app.store.updatePlayer(updatedPlayer, id, dsn)
    allPlayers = app.store.getAllPlayers(dsn)
  

Also this part of players function works as same as teams function in teams.py

• find method

  elif 'find' in request.form:
    name = request.form['nameToFind']
    gender = request.form['genderToFind']
    nation = request.form['nationToFind']
    birthDate = request.form['birthDateToFind']
    team = request.form['teamToFind']
    findPlayer = player(name, gender, nation, birthDate, team)
    allPlayers = app.store.selectPlayers(findPlayer, dsn)
  
  
  return render_template('players.html', players = allPlayers, teams = allTeams )
  

Final part is also same as teams function in teams.py file.

At the end of function players.html file rendered with this python code and allPlayers and allTeams attribute passed to html file to be listed.

• Import part of the technicmembers.py file

  from flask import redirect
  from flask import render_template
  from flask import request
  from flask import url_for
  
  from config import app
  from technicmember import tm
  from store import StoreTM
  import psycopg2 as dbapi2
  
  import coaches
  

Like in teams.py file necessary imports for Flask web framework use has implemented. tm class has imported. StoreTM class has imported and also coaches has imported because of the coach foreign key in the technicmembers table. After all operations allTms variable assigned to an array which holds allTms data as well. At the end of the function allTms variable send to html file for listing and also allCoaches variable too.

• technicmembers function for rendering technicMembers.html file

  @app.route('/technicMembers', methods = ['GET', 'POST'])
  def technicMembers():
  
     dsn = app.config['dsn']
  
     app.store = StoreTM(dsn)
  
     allCoaches = coaches.get_coaches()
  
     if request.method == 'GET':
        allTms = app.store.getAllTms(dsn)
  

This part of technicmembers function works as same as teams function in teams.py file. With a little difference it has allCoaches variable for using getting all coaches from coaches file with coaches.get_coaches function for foreign key of technicmembers table coach attribute.

• delete method

  elif 'delete' in request.form:
    ids = request.form.getlist('tms')
    for id in ids:
        app.store.deleteTm(id, dsn)
    allTms = app.store.getAllTms(dsn)
  

This part of technicmembers function works as same as teams function of teams.py file.

• add method

  elif 'add' in request.form:
    name = request.form['nameToAdd']
    gender = request.form['genderToAdd']
    nation = request.form['nationToAdd']
    birthDate = request.form['birthDateToAdd']
    coach = request.form['coachToAdd']
    newTm = tm(name, gender, nation, birthDate, coach)
    app.store.addTm(newTm, dsn)
    allTms = app.store.getAllTms(dsn)
  

This part of technicmembers function also works as same as teams function of teams.py file.

• update method

  elif 'update' in request.form:
    ids = request.form.getlist('tms')
    id = ids[0]
    name = request.form['nameToUpdate']
    gender = request.form['genderToUpdate']
    nation = request.form['nationToUpdate']
    birthDate = request.form['birthDateToUpdate']
    coach = request.form['coachToUpdate']
    newTm = tm(name, gender, nation, birthDate, coach)
    app.store.updateTm(newTm, id, dsn)
    allTms = app.store.getAllTms(dsn)
  

Also this part of technicmembers function works as same as teams function in teams.py

• find method

  elif 'find' in request.form:
    name = request.form['nameToFind']
    gender = request.form['genderToFind']
    nation = request.form['nationToFind']
    birthDate = request.form['birthDateToFind']
    coach = request.form['coachToFind']
    findTm = tm(name, gender, nation, birthDate, coach)
    allTms = app.store.selectTms(findTm, dsn)
  
  
  return render_template('technicMembers.html', tms = allTms, coaches = allCoaches )
  

Final part is also same as teams function in teams.py file.

At the end of function technicMembers.html file rendered with this python code and allTms and allCoaches attribute passed to html file to be listed.

• Tournaments table keeping records of the tournaments data

  Name	Type
  ID	INTEGER
  NAME	VARCHAR
  YEAR	VARCHAR
  WINNER	INTEGER
  BEST_PLAYER	INTEGER

• name keeps the record of name of the given tournament.

• year keeps the record of year of the given tournament.

• winner keeps the record of the winner of the given tournament. It references the teams table.

• best_player keeps the record of the best player of the given tournament. It references the players table.

Sql statement that initialize the tournaments table:

CREATE TABLE TOURNAMENTS(
    ID SERIAL PRIMARY KEY,
    NAME VARCHAR(45),
    YEAR VARCHAR(4),
    WINNER INTEGER REFERENCES TEAMS ON DELETE CASCADE ON UPDATE CASCADE,
    BEST_PLAYER INTEGER REFERENCES PLAYERS ON DELETE CASCADE ON UPDATE CASCADE
    )

• Matches table keeping records of the matches data

  Name	Type
  ID	INTEGER
  TOURNAMENT	INTEGER
  TEAM1	INTEGER
  TEAM2	INTEGER
  SCORE	VARCHAR

• tournament keeps the record of name of the tournament for given match. It references the tournaments table.

• team1 keeps the record of name of the team for given match. It references the teams table.

• team2 keeps the record of name of the team for given match. It references the teams table.

• score keeps the record of the score of the given match.

Sql statement that initialize the matches table:

CREATE TABLE MATCHES(
    ID SERIAL PRIMARY KEY,
    TOURNAMENT INTEGER REFERENCES TOURNAMENTS ON DELETE CASCADE ON UPDATE CASCADE,
    TEAM1 INTEGER REFERENCES TEAMS ON DELETE CASCADE ON UPDATE CASCADE,
    TEAM2 INTEGER REFERENCES TEAMS ON DELETE CASCADE ON UPDATE CASCADE,
    SCORE VARCHAR(3)
    )

create_table functions used in order to create tables.

• create_table function in tournaments.py:

  def create_table():
      try:
          cursor = create_connection()
          statement = """ CREATE TABLE TOURNAMENTS(
          ID SERIAL PRIMARY KEY,
          NAME VARCHAR(45),
          YEAR VARCHAR(4),
          WINNER INTEGER REFERENCES TEAMS ON DELETE CASCADE ON UPDATE CASCADE,
          BEST_PLAYER INTEGER REFERENCES PLAYERS ON DELETE CASCADE ON UPDATE CASCADE
          )"""
          cursor.execute(statement)
          cursor.connection.commit()
          close_connection(cursor)
      except psycopg2.DatabaseError:
          cursor.connection.rollback()
      finally:
          cursor.connection.close()
  

• create_table function in matches.py:

  def create_table():
      try:
          cursor = create_connection()
          statement = """ CREATE TABLE MATCHES(
          ID SERIAL PRIMARY KEY,
          TOURNAMENT INTEGER REFERENCES TOURNAMENTS ON DELETE CASCADE ON UPDATE CASCADE,
          TEAM1 INTEGER REFERENCES TEAMS ON DELETE CASCADE ON UPDATE CASCADE,
          TEAM2 INTEGER REFERENCES TEAMS ON DELETE CASCADE ON UPDATE CASCADE,
          SCORE VARCHAR(3)
          )"""
          cursor.execute(statement)
          cursor.connection.commit()
          close_connection(cursor)
       except psycopg2.DatabaseError:
          cursor.connection.rollback()
       finally:
          cursor.connection.close()
  

create_init functions used in order to initiliaze database with some tupples.

• create_init_tournaments function in tournaments.py:

  def create_init_tournaments():
  
    add_new_tournament('World Cup', '2015', 1, 1)
    add_new_tournament('World Cup', '2014', 2, 3)
    add_new_tournament('World Cup', '2013', 3, 2)
  

• create_init_matches function in matches.py:

  def create_init_matches():
  
     add_new_match(1, 1, 2, '5-3')
     add_new_match(1, 3, 4, '4-2')
     add_new_match(1, 3, 2, '2-6')
  

add_new functions used in order to add new tupples to a table. Function gets attribute values as a parameter.

• add_new_tournament function in tournaments.py:

  def add_new_tournament(name, year, winner, best_player):
    cursor = create_connection()
  
    cursor.execute("INSERT INTO tournaments (name, year, winner, best_player) VALUES (%s, %s, %s, %s)", (name, year, winner, best_player))
    cursor.connection.commit()
  
    close_connection(cursor)
  
    return True
  

• add_new_match function in matches.py:

  def add_new_match(tournament, team1, team2, score):
     cursor = create_connection()
  
     cursor.execute("INSERT INTO matches (tournament, team1, team2, score) VALUES (%s, %s, %s, %s)", (tournament, team1, team2, score))
     cursor.connection.commit()
  
     close_connection(cursor)
  
     return True
  

delete functions used in order to delete tupples. Function takes primary key value as a parameter.

• delete_tournament function in tournaments.py:

def delete_tournament(id):
 cursor = create_connection()
 statement = """DELETE FROM TOURNAMENTS WHERE ID={}""".format(id)
 cursor.execute(statement)
 cursor.connection.commit()

 close_connection(cursor)

• delete_match function in matches.py:

def delete_match(id):
 cursor = create_connection()
 statement = """DELETE FROM MATCHES WHERE ID={}""".format(id)
 cursor.execute(statement)
 cursor.connection.commit()

 close_connection(cursor)

update functions used in order to update selected tupples. Function takes primary key as a parameter to find

selected tupple and takes attributes values as a paramater to update tupple.

• update_tournament function in tournaments.py:

def update_tournament(id, nameUpdate, yearUpdate, winnerUpdate, best_playerUpdate):
 cursor = create_connection()
 statement = """UPDATE TOURNAMENTS SET NAME = '{}', YEAR = '{}', WINNER = '{}', BEST_PLAYER = {} WHERE ID={} """.format(nameUpdate, yearUpdate, winnerUpdate, best_playerUpdate, id)
 cursor.execute(statement)
 cursor.connection.commit()

 close_connection(cursor)

• update_match function in matches.py:

def update_match(id, tournamentUpdate, team1Update, team2Update, scoreUpdate):
 cursor = create_connection()
 statement = """UPDATE MATCHES SET TOURNAMENT = '{}', TEAM1 = '{}', TEAM2 = '{}', SCORE = '{}' WHERE ID={} """.format(tournamentUpdate, team1Update, team2Update, scoreUpdate, id)
 cursor.execute(statement)
 cursor.connection.commit()

 close_connection(cursor)

findInJointTables functions used in order to query tupples. Function takes attribute values as a parameter and returns tupples as an array. If an empty search made, all the tupples will be returned.

• findInJointTables function in tournaments.py:

def findInJointTables(nameFind, yearFind, winnerFind, best_playerFind):
 statement= """ SELECT TOURNAMENTS.ID, TOURNAMENTS.NAME, YEAR, TEAMS.NATION , PLAYERS.NAME FROM TOURNAMENTS INNER JOIN PLAYERS ON PLAYERS.ID=TOURNAMENTS.BEST_PLAYER INNER JOIN TEAMS ON TEAMS.ID=TOURNAMENTS.WINNER WHERE(TOURNAMENTS.NAME LIKE  '{}%' ) AND (YEAR LIKE '{}%' ) AND (TEAMS.NATION LIKE '{}%' )  AND (PLAYERS.NAME LIKE '{}%' )""".format(nameFind, yearFind, winnerFind, best_playerFind)

 cursor = create_connection()
 cursor.execute(statement)
 tournaments = cursor.fetchall()
 cursor.connection.commit()

 close_connection(cursor)

 return tournaments

• findInJointTables function in matches.py:

def findInJointTables(tournamentFind, team1Find, team2Find, scoreFind):
 statement= """ SELECT MATCHES.ID, TOURNAMENTS.NAME, t1.NATION, t2.NATION, MATCHES.SCORE FROM MATCHES INNER JOIN TOURNAMENTS ON TOURNAMENTS.ID=MATCHES.TOURNAMENT INNER JOIN TEAMS t1 ON t1.ID=MATCHES.TEAM1 INNER JOIN TEAMS t2 ON t2.ID=MATCHES.TEAM2 WHERE(TOURNAMENTS.NAME LIKE  '{}%' ) AND (t1.NATION LIKE '{}%' ) AND (t2.NATION LIKE '{}%' )  AND (MATCHES.SCORE LIKE '{}%' )""".format(tournamentFind, team1Find, team2Find, scoreFind)

 cursor = create_connection()
 cursor.execute(statement)
 matches = cursor.fetchall()
 cursor.connection.commit()

 close_connection(cursor)

 return matches

showJointTables functions used in order to fetch all the tupples. Function returns tupples as an array.

• showJointTables function in tournaments.py:

def showJointTables():
 cursor = create_connection()
 statement= """ SELECT TOURNAMENTS.ID, TOURNAMENTS.NAME, YEAR, TEAMS.NATION , PLAYERS.NAME FROM TOURNAMENTS INNER JOIN PLAYERS ON PLAYERS.ID=TOURNAMENTS.BEST_PLAYER INNER JOIN TEAMS ON TEAMS.ID=TOURNAMENTS.WINNER  """
 cursor.execute(statement)
 tournaments = cursor.fetchall()
 cursor.connection.commit()

 close_connection(cursor)
 return tournaments

• showJointTables function in matches.py:

def showJointTables():
 cursor = create_connection()
 statement= """ SELECT MATCHES.ID, TOURNAMENTS.NAME, t1.NATION, t2.NATION, MATCHES.SCORE FROM MATCHES INNER JOIN TOURNAMENTS ON TOURNAMENTS.ID=MATCHES.TOURNAMENT INNER JOIN TEAMS t1 ON t1.ID = MATCHES.TEAM1 INNER JOIN TEAMS t2 ON t2.ID=MATCHES.TEAM2 """
 cursor.execute(statement)
 matches = cursor.fetchall()
 cursor.connection.commit()

 close_connection(cursor)
 return matches

In the add block, add_new functions called with the parameters from HTML.

• add block in tournaments.py:

elif 'add' in request.form:
     # ----------------------------------------------
     name = request.form['name']
     year = request.form['year']
     winner = request.form['winner']
     best_player = request.form['best_player']
     # ----------------------------------------------

     add_new_tournament(name, year, winner, best_player) # save to db


     all_tournaments = showJointTables()
     queriedTournaments = findInJointTables('?','?','?','?')

• add block in matches.py:

elif 'add' in request.form:
    # ----------------------------------------------
    tournament = request.form['tournament']
    team1 = request.form['team1']
    team2 = request.form['team2']
    score = request.form['score']
    # ----------------------------------------------

    add_new_match(tournament, team1, team2, score) # save to db

    all_matches = showJointTables() # get all matches
    queriedMatches = findInJointTables('?','?','?','?')

In the delete block, delete functions called with the parameters from HTML.

• delete block in tournaments.py:

elif 'delete' in request.form:
    ids = request.form.getlist('tournaments_to_delete')
    for id in ids:
        delete_tournament(id)
    all_tournaments = showJointTables()
    queriedTournaments = findInJointTables('?','?','?','?')

• delete block in matches.py:

elif 'delete' in request.form:
     ids = request.form.getlist('matches_to_delete')
     for id in ids:
         delete_match(id)
     all_matches = showJointTables()
     queriedMatches = findInJointTables('?','?','?','?')

In the find block, findInJointTables functions called with the parameters from HTML.

• find block in tournaments.py:

elif 'find' in request.form:
     nameFind = request.form['nameFind']
     yearFind = request.form['yearFind']
     winnerFind = request.form['winnerFind']
     best_playerFind = request.form['best_playerFind']

     all_tournaments = showJointTables()
     queriedTournaments = findInJointTables(nameFind,yearFind,winnerFind,best_playerFind)

• find block in matches.py:

elif 'find' in request.form:
 tournamentFind = request.form['tournamentFind']
 team1Find = request.form['team1Find']
 team2Find = request.form['team2Find']
 scoreFind = request.form['scoreFind']

 all_matches = showJointTables()
 queriedMatches = findInJointTables(tournamentFind, team1Find, team2Find, scoreFind)

In the update block, update functions called with the parameters from HTML.

• update block in tournaments.py:

elif 'update' in request.form:
   ids = request.form.getlist('update')
   for id in ids:
       nameUpdate = request.form['nameUpdate'+id]
       yearUpdate = request.form['yearUpdate'+id]
       winnerUpdate = request.form['winnerUpdate'+id]
       best_playerUpdate = request.form['best_playerUpdate'+id]
       update_tournament(id, nameUpdate, yearUpdate, winnerUpdate, best_playerUpdate)

   all_tournaments = showJointTables()
   queriedTournaments = findInJointTables('?','?','?','?')

• update block in matches.py:

elif 'update' in request.form:
   ids = request.form.getlist('update')
   for id in ids:
       tournamentUpdate = request.form['tournamentUpdate'+id]
       team1Update = request.form['team1Update'+id]
       team2Update = request.form['team2Update'+id]
       scoreUpdate = request.form['scoreUpdate'+id]
       update_match(id, tournamentUpdate, team1Update, team2Update, scoreUpdate)

• Coaches table keeping record of the coaches data

  Name	Type	Not Null	Primary K.
  ID	INTEGER	0	1
  NAME	VARCHAR	0	0
  GENDER	VARCHAR	0	0
  NATIONALITY	VARCHAR	0	0
  BIRTH_DATE	VARCHAR	0	0
  CURRENT_TEAM	INTEGER	0	0

• name keeps the record of name of the given coach.

• gender keeps the record of gender of the given coach.

• nationality keeps the record of the nationality of the given coach.

• birth_date keeps the record of birth date of the given coach.

• current_team keeps the record of current team of the given coach which refers to teams table.

Sql statement that initialize the coaches table:

CREATE TABLE COACHES(
    ID SERIAL PRIMARY KEY,
    NAME VARCHAR(45),
    GENDER VARCHAR(6),
    NATIONALITY VARCHAR(45),
    BIRTH_DATE  VARCHAR(10),
    CURRENT_TEAM INTEGER REFERENCES TEAMS ON DELETE CASCADE ON UPDATE CASCADE
    )

• Player Statistics table keeping record of the statistics data of players.

  Name	Type	Not Null	Primary K.
  ID	INTEGER	0	1
  PLAYER	INTEGER	0	0
  MATCHES_PLAYED	VARCHAR	0	0
  MATCHES_WON	VARCHAR	0	0
  WIN_RATE	VARCHAR	0	0
  AVERAGE_SCORE	VARCHAR	0	0

• player keeps the record of name of the given player refers to players table on delete and on update cascades

• matches_played keeps the count of played matches of given player.

• matches_won keeps the count of won matches of given player.

• win_rate keeps the record of win rate of the given player.

• average_score keeps the record of average score of the given player according to matches he/she played.

Sql statement that initialize the playerstatistics table:

CREATE TABLE PLAYERSTATISTICS(
    ID SERIAL PRIMARY KEY,
    matches_played VARCHAR(10),
    matches_won VARCHAR(10),
    win_rate VARCHAR(10),
    average_score  VARCHAR(10),
    player INTEGER REFERENCES players ON DELETE CASCADE ON UPDATE CASCADE
    )

• Users table keeping record of the users data

  Name	Type	Not Null	Primary K.
  ID	INTEGER	0	1
  USERNAME	VARCHAR	0	0
  PASSWORD	VARCHAR	0	0

• username keeps the record of name of the given user.

• password keeps the record of gender of the given user.

Sql statement that initialize the technic members table:

CREATE TABLE USERS(
    ID SERIAL PRIMARY KEY,
    USERNAME VARCHAR(45),
    PASSWORD VARCHAR(6)
    )

• Import part of the coaches.py file

  from flask import request
  from flask import render_template
  from config import app
  from config import create_connection, close_connection
  import psycopg2
  import teams
  from store import StoreTeam
  

render_template and request features of Flask web framework have used. app object of Flask has imported from config. team class and StoreTeam class has imported. Since “corrent_team” attribure refers to teams table pyscopg2 has imported as a dbapi2 for use as a database api.

• route function of coaches for rendering coaches.html file

  @app.route("/coaches/", methods=['GET', 'POST'])
  def coaches():
  dsn = app.config['dsn']
  
  app.store = StoreTeam(dsn)
  all_teams = app.store.getAllTeams(dsn)
  
  if request.method == 'GET':
    all_coaches = join_tables()
  

This part of coaches function renders the url ‘/teams’ with coaches.html file. Uses ‘GET’ and ‘POST’ methods. Uses dsn as a database settings which is implementing in config.py whether ElephantSQL or Vagrant database system. To get all teams for joint tables first getAllTeams function used and teams assigned to all_teams variable. after that join_tables function used to join teams and coaches tables to get meaningful data and assigned to all_coaches variable.

• delete method

  elif 'delete' in request.form:
    ids = request.form.getlist('coaches_to_delete')
    for id in ids:
        delete_coach(id)
  
    all_coaches = join_tables()
  

If request method is delete which means if delete button has clicked on html page. Code requests checked box information and assign this to a list called ids. Then each element in ids list sent to delete_coach function which deletes tuple from table. Then all_coaches variable renewed with remaining tuples.

• add method

  elif 'add' in request.form:
    # ----------------------------------------------
    name = request.form['name']
    gender = request.form['gender']
    nationality = request.form['nationality']
    birth_date = request.form['birth_date']
    current_team = request.form['current_team']
    # ----------------------------------------------
  
    add_new_coach(name, gender, nationality, birth_date, current_team) # save to db
  
    all_coaches = join_tables()
  

If request method is add which means add button has clicked on html page. Code requests the values entered in textboxes. These values sent to add_new_coach function which adds the tuple with according values to the table. New all tuples fetched to print to the screen

• update method

  elif 'update' in request.form:
    ids = request.form.getlist('update')
    for id in ids:
        name_update = request.form['name_update'+id]
        gender_update = request.form['gender_update'+id]
        nationality_update = request.form['nationality_update'+id]
        birth_date_update = request.form['birth_date_update'+id]
        current_team_update = request.form['current_team_update'+id]
  
        update_coach(id,name_update,gender_update,nationality_update,birth_date_update,current_team_update)
  
    all_coaches = join_tables()
  

If request method is update which means if update button clicked on html page. Code requests the values entered in textboxes and gets the ids with checkedboxes. for each id tuples changed with new values. New all tuples fetched to print to the screen.

• find method

  elif 'find' in request.form:
    para_1 = request.form['name_find']
    para_2 = request.form['gender_find']
    para_3 = request.form['nationality_find']
    para_4 = request.form['birth_date_find']
    para_5 = request.form['current_team_find']
  
    all_coaches = find_coach(para_1,para_2,para_3,para_4,para_5)
  

Else if request method is find which means find button clicked on html page. Code requests the values from textboxes for attributes. values sent to find_coach function to select according tuples from table and print.

• showall method

  elif 'showall' in request.form:
  
    all_coaches = join_tables()
  

Else if request method is showall which means Show All button has clicked on html page. Coaches table and teams table joint to show all coaches tables tuples with according curent_team value.

return render_template("coaches.html", coaches=all_coaches, teams_select=all_teams)

Route function returns render_template function which gets all coaches and send them to html page to print to the screen.

Same things did as did in coaches.py But playerstatistics table has a different foreign key which refers to players table

Same things did as did in coaches.py

• get_coaches function

  def get_coaches():
      cursor = create_connection()
  
      cursor.execute("SELECT * FROM coaches;")
      coaches = cursor.fetchall()
  
      close_connection(cursor)
  
      return coaches
  

This function selects all tuples from table without condition.

• create_init_coaches function

  def create_init_coaches():
  
      add_new_coach('Zehra', 'female', 'turkish', '1964', 1)
      add_new_coach('Mike', 'male', 'english', '1954', 2)
      add_new_coach('Chan', 'male', 'chinese', '1962', 3)
  

This function adds 3 initial tuple when initialize database function called using add_new_coach function.

• update_coach function

  def update_coach(id, name_update, gender_update, nationality_update, birth_date_update, current_team_update):
      cursor = create_connection()
      statement = """UPDATE COACHES SET NAME = '{}', GENDER = '{}', NATIONALITY = '{}', BIRTH_DATE = '{}', CURRENT_TEAM = '{}' WHERE ID = {}""".format(name_update, gender_update, nationality_update, birth_date_update, current_team_update,id)
      cursor.execute(statement)
      cursor.connection.commit()
  
      close_connection(cursor)
  

This function updates tuples with values coming from html page.

• find_coach function

  def find_coach(name_find, gender_find, nationality_find, birth_date_find, current_team_find):
      statement= """ SELECT COACHES.ID, COACHES.NAME, COACHES.GENDER, NATIONALITY, BIRTH_DATE, TEAMS.NATION FROM COACHES INNER JOIN TEAMS ON TEAMS.ID=COACHES.CURRENT_TEAM WHERE(COACHES.NAME LIKE  '{}%' ) AND (COACHES.GENDER LIKE '{}%' ) AND (NATIONALITY LIKE '{}%' ) AND (BIRTH_DATE LIKE '{}%' ) AND (TEAMS.NATION LIKE '{}%' )""".format(name_find, gender_find, nationality_find, birth_date_find, current_team_find)
  
      cursor = create_connection()
      cursor.execute(statement)
      coaches = cursor.fetchall()
      cursor.connection.commit()
  
      close_connection(cursor)
  
      return coaches
  

This function finds tuples with values coming from html page.

• add_new_coach function

  def add_new_coach(name, gender, nationality, birth_date, current_team):
      cursor = create_connection()
  
      cursor.execute("INSERT INTO coaches (name, gender, nationality, birth_date, current_team) VALUES (%s, %s, %s, %s, %s)", (name, gender, nationality, birth_date, current_team))
      cursor.connection.commit()
  
      close_connection(cursor)
  
      return True
  

This function adds a new tuple to table with given values from html page.

• delete_coach function

  def delete_coach(id):
      cursor = create_connection()
      statement = """DELETE FROM COACHES WHERE ID={}""".format(id)
      cursor.execute(statement)
      cursor.connection.commit()
  
      close_connection(cursor)
  

This function deletes the tuples from table which selected with checkboxes from html page.

• join_tables function

  def join_tables():
      cursor = create_connection()
      statement= """ SELECT COACHES.ID, COACHES.NAME, COACHES.GENDER, NATIONALITY, BIRTH_DATE, TEAMS.NATION FROM COACHES INNER JOIN TEAMS ON TEAMS.ID=COACHES.CURRENT_TEAM  """
      cursor.execute(statement)
      coaches = cursor.fetchall()
      cursor.connection.commit()
  
      close_connection(cursor)
      return coaches
  

This function joins coaches and teams table for current_team value of coaches table can be printed with the vale it refers.

Same things did as did in coaches.py But playerstatistics table has a different foreign key which refers to players table

Same things did as did in coaches.py