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 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 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 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_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_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 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_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_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 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 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
def update_transfer(self, key, playerid, oldteamid, newteamid, seasonid):
global connection
try:
connection = dbapi2.connect(dsn)
cursor = connection.cursor()
statement = """update transfer set (playerid, oldteamid, newteamid, seasonid) = (%s,%s,%s,%s) where (objectid=(%s))"""
cursor.execute(statement, (playerid, oldteamid, newteamid, seasonid, 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_match(self, key, hometeamid, awayteamid, courtid, matchdate):
global connection
try:
connection = dbapi2.connect(dsn)
cursor = connection.cursor()
statement = """update match set (hometeamid, awayteamid, courtid, matchdate) = (%s,%s,%s,%s) where (objectid=(%s))"""
cursor.execute(statement, (hometeamid, awayteamid, courtid, matchdate, 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_statistic(self, key, assistnumber, blocknumber, score, cardnumber, seasonid, playerid):
global connection
try:
connection = dbapi2.connect(dsn)
cursor = connection.cursor()
statement = """update statistic set (assistnumber, blocknumber, score, cardnumber, seasonid, playerid) = (%s,%s,%s,%s,%s,%s) where (objectid=(%s))"""
cursor.execute(statement, (assistnumber, blocknumber, score, cardnumber, seasonid, playerid, 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_player(self,key):
global connection
try:
connection = dbapi2.connect(dsn)
cursor = connection.cursor()
# statement = """update player set deleted = 1 where (objectid=(%s))"""
statement = """delete from player 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
