Example #1
0
    def init_db(self):

        global connection

        connection = dbapi2.connect(dsn)
        cursor = connection.cursor()
        cursor.execute(open("database/dump.sql", "r").read())
        cursor.close()
        connection.commit()

        connection.close()
Example #2
0
 def get_country(self, key):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """SELECT objectid, name FROM country where (objectid=%s and deleted=0)"""
         cursor.execute(statement, (key,))
         id,name=cursor.fetchone()
         cursor.close()
     except dbapi2.DatabaseError:
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return Country(id, name, 0)
 def get_positions(self):
     positions = []
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """SELECT objectid, name FROM position WHERE position.deleted = 0 ORDER BY objectid"""
         cursor.execute(statement)
         positions = [(key, Position(key, name, 0)) for key, name in cursor]
         cursor.close()
     except dbapi2.DatabaseError:
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return positions
Example #4
0
 def get_genders(self):
     global connection
     genders=[]
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """SELECT objectid, type FROM gender WHERE deleted=0 order by objectid"""
         cursor.execute(statement)
         genders = [(key, Gender(key,type,0)) for key, type in cursor]
         cursor.close()
     except dbapi2.DatabaseError:
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return genders
Example #5
0
 def get_countries(self):
     global connection
     countries=[]
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """SELECT objectid, name FROM country WHERE deleted=0 ORDER BY objectid"""
         cursor.execute(statement)
         countries = [(key, Country(key,name,0)) for key, name in cursor]
         cursor.close()
     except dbapi2.DatabaseError:
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return countries
 def get_statistic(self, key):
     global connection
     storeSeason = season_operations()
     storePlayer = player_operations()
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """SELECT objectid, assistnumber, blocknumber, score, cardnumber, seasonid, playerid FROM statistic where (objectid=%s and deleted=0)"""
         cursor.execute(statement, (key,))
         id,assistnumber,blocknumber, score, cardnumber, seasonid, playerid=cursor.fetchone()
         cursor.close()
     except dbapi2.DatabaseError:
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return Statistic(id, assistnumber, blocknumber, score, cardnumber, seasonid, storeSeason.get_season(seasonid), playerid, storePlayer.get_player(playerid), 0)
Example #7
0
 def get_team(self, key):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """SELECT objectid, name, shirtcolour, foundationdate, countryid, courtid FROM team where (objectid=%s and deleted=0)"""
         cursor.execute(statement, (key,))
         id,name,color,date,countryid,courtid=cursor.fetchone()
         cursor.close()
         storeCountry = country_operations()
         storeCourt = court_operations()
     except dbapi2.DatabaseError:
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return Team(id,name, color, date, countryid, storeCountry.get_country(countryid), courtid, storeCourt.get_court(courtid),0)
Example #8
0
 def get_matches(self):
     matches=[]
     global connection
     storeCourt = court_operations()
     storeTeam = team_operations()
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """SELECT objectid, hometeamid, awayteamid, courtid, matchdate FROM match WHERE deleted = 0 ORDER BY objectid"""
         cursor.execute(statement)
         matches = [(key, Match(key, hometeamid, storeTeam.get_team(hometeamid), awayteamid, storeTeam.get_team(awayteamid), courtid, storeCourt.get_court(courtid), matchdate, 0)) for key, hometeamid, awayteamid, courtid, matchdate in cursor]
         cursor.close()
     except dbapi2.DatabaseError:
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return matches
Example #9
0
 def get_teams(self):
     teams=[]
     global connection
     storeCountry = country_operations()
     storeCourt = court_operations()
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """SELECT team.objectid, team.name, team.shirtcolour, team.foundationdate, team.countryid,team.courtid FROM team WHERE team.deleted = 0 ORDER BY objectid """
         cursor.execute(statement)
         teams = [(key, Team(key,name,color,date,countryid, storeCountry.get_country(countryid),courtid,storeCourt.get_court(courtid) , 0)) for key, name, color, date, countryid, courtid in cursor]
         cursor.close()
     except dbapi2.DatabaseError:
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return teams
Example #10
0
 def get_transfer(self, key):
     global connection
     storeTeam = team_operations()
     storeSeason = season_operations()
     storePlayer = player_operations()
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """SELECT objectid, playerid, oldteamid, newteamid, seasonid FROM transfer where (objectid=%s and deleted=0)"""
         cursor.execute(statement, (key,))
         id,playerid,oldteamid,newteamid,seasonid=cursor.fetchone()
         cursor.close()
     except dbapi2.DatabaseError:
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return Transfer(id, playerid,storePlayer.get_player(playerid), oldteamid, storeTeam.get_team(oldteamid), newteamid, storeTeam.get_team(newteamid), seasonid, storeSeason.get_season(seasonid), 0)
Example #11
0
    def get_match(self, key):
        global connection
        storeCourt = court_operations()
        storeTeam = team_operations()
        try:
            connection = dbapi2.connect(dsn)
            cursor = connection.cursor()
            statement = """SELECT objectid, hometeamid, awayteamid, courtid, matchdate FROM match WHERE (objectid=%s and deleted=0)"""
            cursor.execute(statement, (key,))
            id,hometeamid,awayteamid,courtid,matchdate=cursor.fetchone()
            cursor.close()
        except dbapi2.DatabaseError:
            if connection:
                connection.rollback()
        finally:
            if connection:
                connection.close()

        return Match(id, hometeamid, storeTeam.get_team(hometeamid), awayteamid, storeTeam.get_team(awayteamid), courtid, storeCourt.get_court(courtid), matchdate, 0)
Example #12
0
    def get_coach(self, key):
        global connection
        storeCountry = country_operations()
        storeTeam = team_operations()
        storeGender = gender_operations()
        try:
            connection = dbapi2.connect(dsn)
            cursor = connection.cursor()
            statement = """SELECT objectid, name, surname,  countryid, teamid, birthday, genderid FROM coach where (objectid=%s and deleted=0)"""
            cursor.execute(statement, (key,))
            id,name,surname,countryid,teamid,birthyear,genderid=cursor.fetchone()
            cursor.close()
        except dbapi2.DatabaseError:
            if connection:
                connection.rollback()
        finally:
            if connection:
                connection.close()

        return Coach(id,name,surname,countryid, storeCountry.get_country(countryid), teamid, storeTeam.get_team(teamid), birthyear, genderid, storeGender.get_gender(genderid), 0)
    def get_statistics(self):
        global connection
        storeSeason = season_operations()
        storePlayer = player_operations()
        statistics=[]
        try:
            connection = dbapi2.connect(dsn)
            cursor = connection.cursor()
            statement = """SELECT statistic.objectid, statistic.assistnumber, statistic.blocknumber, statistic.score, statistic.cardnumber, statistic.seasonid, statistic.playerid FROM statistic where statistic.deleted=0 ORDER BY objectid"""
            cursor.execute(statement)
            statistics = [(key, Statistic(key, assistnumber, blocknumber,score, cardnumber, seasonid, storeSeason.get_season(seasonid), playerid, storePlayer.get_player(playerid), 0)) for key, assistnumber, blocknumber, score, cardnumber, seasonid, playerid in cursor]
            cursor.close()
        except dbapi2.DatabaseError:
            if connection:
                connection.rollback()
        finally:
            if connection:
                connection.close()

        return statistics
Example #14
0
 def get_coaches(self):
     coachs=[]
     global connection
     storeCountry = country_operations()
     storeTeam = team_operations()
     storeGender = gender_operations()
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """SELECT coach.objectid, coach.name, coach.surname, coach.countryid, coach.teamid, coach.birthday, coach.genderid FROM coach WHERE coach.deleted = 0 ORDER BY objectid"""
         cursor.execute(statement)
         coachs = [(key, Coach(key,name,surname,countryid, storeCountry.get_country(countryid), teamid, storeTeam.get_team(teamid), birthyear, genderid, storeGender.get_gender(genderid), 0)) for key, name,surname, countryid, teamid, birthyear, genderid in cursor]
         cursor.close()
     except dbapi2.DatabaseError:
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return coachs
Example #15
0
 def add_player(self,Player):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         cursor.execute("""INSERT INTO player (name, surname, birthdate, height, weight, startdate, teamid, countryid, genderid, positionid, handid, number) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",(Player.name, Player.surname, Player.birthdate, Player.height, Player.weight, Player.startdate, Player.teamid, Player.countryid, Player.genderid, Player.positionid, Player.handid, Player.number))
         cursor.close()
         connection.commit()
         result = 'success'
     except dbapi2.IntegrityError:
         result = 'integrityerror'
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = 'databaseerror'
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result
Example #16
0
 def add_team(self,Team):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         cursor.execute("""INSERT INTO team (name, shirtcolour, foundationdate, countryid, courtid) VALUES (%s, %s, %s, %s, %s)""",(Team.name,Team.color,Team.date,Team.countryid,Team.courtid))
         cursor.close()
         connection.commit()
         result = 'success'
     except dbapi2.IntegrityError:
         result = 'integrityerror'
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = 'databaseerror'
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result
 def add_statistic(self,Statistic):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         cursor.execute("""INSERT INTO statistic (assistnumber, blocknumber, score, cardnumber, seasonid, playerid) VALUES (%s, %s, %s, %s, %s, %s)""",(Statistic.assistnumber,Statistic.blocknumber,Statistic.score,Statistic.cardnumber,Statistic.seasonid,Statistic.playerid))
         cursor.close()
         connection.commit()
         result = 'success'
     except dbapi2.IntegrityError:
         result = 'integrityerror'
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = 'databaseerror'
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result
Example #18
0
 def delete_court(self,key):
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """delete from court where (objectid=(%s))"""
         cursor.execute(statement, (key,))
         connection.commit()
         cursor.close()
         result = 'success'
     except dbapi2.IntegrityError:
         result = 'integrityerror'
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = 'databaseerror'
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result
Example #19
0
 def add_transfer(self,Transfer):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         cursor.execute("""INSERT INTO transfer (playerid, oldteamid, newteamid, seasonid) VALUES (%s, %s, %s, %s)""",(Transfer.playerid,Transfer.oldteamid,Transfer.newteamid,Transfer.seasonid))
         cursor.close()
         connection.commit()
         result = 'success'
     except dbapi2.IntegrityError:
         result = 'integrityerror'
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = 'databaseerror'
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result
Example #20
0
    def get_transfers(self):
        global connection
        storeTeam = team_operations()
        storeSeason = season_operations()
        storePlayer = player_operations()
        transfers=[]
        try:
            connection = dbapi2.connect(dsn)
            cursor = connection.cursor()
            statement = """SELECT transfer.objectid, transfer.playerid, transfer.oldteamid, transfer.newteamid, transfer.seasonid FROM transfer where transfer.deleted=0 ORDER BY objectid"""
            cursor.execute(statement)
            transfers = [(key, Transfer(key, playerid,storePlayer.get_player(playerid), oldteamid,storeTeam.get_team(oldteamid), newteamid, storeTeam.get_team(newteamid), seasonid, storeSeason.get_season(seasonid), 0)) for key, playerid, oldteamid, newteamid, seasonid in cursor]
            cursor.close()
        except dbapi2.DatabaseError:
            if connection:
                connection.rollback()
        finally:
            if connection:
                connection.close()

        return transfers
 def add_user(self,User):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         cursor.execute("""INSERT INTO usertable (username, password, userroleid) VALUES (%s, %s, 1)""",(User.username,User.password))
         cursor.close()
         connection.commit()
         result = 'success'
     except dbapi2.IntegrityError:
         result = 'integrityerror'
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = 'databaseerror'
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return result
Example #22
0
 def add_match(self,Match):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         cursor.execute("""INSERT INTO match (hometeamid, awayteamid, courtid, matchdate) VALUES (%s, %s, %s, %s)""",(Match.hometeamid, Match.awayteamid, Match.courtid, Match.matchdate))
         cursor.close()
         connection.commit()
         result = 'success'
     except dbapi2.IntegrityError:
         result = 'integrityerror'
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = 'databaseerror'
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result
Example #23
0
 def add_coach(self,Coach):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         cursor.execute("""INSERT INTO coach (name, surname, countryid, teamid, birthday, genderid) VALUES (%s, %s, %s, %s, %s, %s)""",(Coach.name,Coach.surname,Coach.countryid,Coach.teamid, Coach.birthyear, Coach.genderid))
         cursor.close()
         connection.commit()
         result = 'success'
     except dbapi2.IntegrityError:
         result = 'integrityerror'
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = 'databaseerror'
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result
Example #24
0
 def add_country(self,Country):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         cursor.execute("""INSERT INTO country (name) VALUES (%s)""",(Country.name,))
         cursor.close()
         connection.commit()
         result = 'success'
     except dbapi2.IntegrityError:
         result = 'integrityerror'
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = 'databaseerror'
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result
Example #25
0
 def add_hand(self, Hand):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         cursor.execute("""INSERT INTO hand (name) VALUES (%s)""", (Hand.name,))
         cursor.close()
         connection.commit()
         result = "success"
     except dbapi2.IntegrityError:
         result = "integrityerror"
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = "databaseerror"
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result
Example #26
0
 def get_players(self):
     players=[]
     global connection
     storeCountry = country_operations()
     storeTeam = team_operations()
     storeGender = gender_operations()
     storePosition = position_operations()
     storeHand = hand_operations()
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """SELECT objectid, name, surname, birthdate, height, weight, startdate, teamid, countryid, genderid, positionid, handid, number FROM player WHERE deleted = 0 ORDER BY objectid"""
         cursor.execute(statement)
         players = [(key, Player(key, name, surname, birthdate, height, weight, startdate, teamid, storeTeam.get_team(teamid),countryid, storeCountry.get_country(countryid), genderid, storeGender.get_gender(genderid), positionid, storePosition.get_position(positionid), handid, storeHand.get_hand(handid), number, 0)) for key, name, surname, birthdate, height, weight, startdate, teamid, countryid, genderid, positionid, handid, number in cursor]
         cursor.close()
     except dbapi2.DatabaseError:
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
     return players
Example #27
0
    def get_player(self, key):
        global connection
        storeCountry = country_operations()
        storeTeam = team_operations()
        storeGender = gender_operations()
        storePosition = position_operations()
        storeHand = hand_operations()
        try:
            connection = dbapi2.connect(dsn)
            cursor = connection.cursor()
            statement = """SELECT objectid,name, surname, birthdate, height, weight, startdate, teamid, countryid, genderid, positionid, handid, number FROM player WHERE (objectid=%s and deleted=0)"""
            cursor.execute(statement, (key,))
            id,name,surname,birthdate,height,weight,startdate,teamid,countryid,genderid,positionid,handid,number=cursor.fetchone()
            cursor.close()
        except dbapi2.DatabaseError:
            if connection:
                connection.rollback()
        finally:
            if connection:
                connection.close()

        return Player(id, name, surname, birthdate, height, weight, startdate, teamid, storeTeam.get_team(teamid), countryid, storeCountry.get_country(countryid), genderid, storeGender.get_gender(genderid), positionid, storePosition.get_position(positionid), handid, storeHand.get_hand(handid), number, 0)
Example #28
0
 def update_country(self, key, name):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """update country set (name) = (%s) where (objectid=(%s))"""
         cursor.execute(statement, (name, key,))
         connection.commit()
         cursor.close()
         result = 'success'
     except dbapi2.IntegrityError:
         result = 'integrityerror'
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = 'databaseerror'
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result
Example #29
0
 def delete_hand(self, key):
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         #             statement = """update hand set deleted = 1 where (objectid=(%s))"""
         statement = """delete from hand where (objectid=(%s))"""
         cursor.execute(statement, (key,))
         connection.commit()
         cursor.close()
         result = "success"
     except dbapi2.IntegrityError:
         result = "integrityerror"
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = "databaseerror"
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result
Example #30
0
 def update_player(self, key, name, surname, birthdate, height, weight, startdate, teamid, countryid, genderid, positionid, handid, number):
     global connection
     try:
         connection = dbapi2.connect(dsn)
         cursor = connection.cursor()
         statement = """update player set (name, surname, birthdate, height, weight, startdate, teamid, countryid, genderid, positionid, handid, number) = (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) where (objectid=(%s))"""
         cursor.execute(statement, (name, surname, birthdate, height, weight, startdate, teamid, countryid, genderid, positionid, handid, number, key,))
         connection.commit()
         cursor.close()
         result = 'success'
     except dbapi2.IntegrityError:
         result = 'integrityerror'
         if connection:
             connection.rollback()
     except dbapi2.DatabaseError:
         result = 'databaseerror'
         if connection:
             connection.rollback()
     finally:
         if connection:
             connection.close()
         return result