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()
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
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
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)
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)
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
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
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)
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)
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
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
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
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
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
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
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
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
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
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
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
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
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)
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
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
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