def findTeamById(self):
connection = None
try:
print('findTeamById: ' + str(self.id))
if self.id is not None:
connection = connection_settings.createConnection()
with connection.cursor() as cursor:
cursor.execute(self.find_by_id, (self.id))
result = cursor.fetchone()
print(result)
if result == None:
raise RuntimeError(
'Error in findTeamById: \'results\' == None for TEAM_ID: '
+ self.id)
self.createTeamFromResults(result)
except Exception as e:
print('Error in findTeamById: ' + str(e))
pass
finally:
if connection is not None:
connection.close()
def findScheduleByDateTeams(self):
try:
if self.game_date != None \
and self.home_team != None \
and self.away_team != None:
connection = connection_settings.createConnection();
with connection.cursor() as cursor:
cursor.execute(self.find_schedule_date_teams,(self.game_date, self.home_team.id, self.away_team.id))
result = cursor.fetchone()
if result == None:
raise RuntimeError('Error in findScheduleByDateTeams: \'results\' == None for HOME_TEAM_ID: ' + str(self.home_team.id) + '. AWAY_TEAM_ID: ' + str(self.away_team.id) + '. GAME_DATE: ' + datetime.datetime.strftime(self.game_date, '%Y/%m/%d'))
print('game updated for schedule table: ' + self.away_team.schedule_name + ' @ ' + self.home_team.schedule_name + ' ' + datetime.datetime.strftime(self.game_date, '%Y/%m/%d'))
except Exception as e:
print('Issue in findScheduleByDateTeams: \n' + str(e) + '\n' + 'home team schedule name: ' + self.home_team.schedule_name)
finally:
if connection is not None:
connection.close()
def getPicks(date):
try:
connection = connection_settings.createConnection()
with connection.cursor() as cursor:
cursor.execute(pick_query, (date))
picks = cursor.fetchall()
print(picks)
return picks
except Exception as e:
print(str(e))
def insertSchedule(self):
try:
if self.game_date != None \
and self.game_time != None \
and self.home_team != None \
and self.away_team != None:
connection = connection_settings.createConnection();
with connection.cursor() as cursor:
cursor.execute(self.insert_initial_schedule_sql,(self.game_date, self.game_time,self.home_team.id, self.away_team.id) )
connection.commit()
print('game inserted into schedule table: ' + self.away_team.schedule_name + ' @ ' + self.home_team.schedule_name + ' ' + datetime.datetime.strftime(self.game_date, '%Y/%m/%d') + ' ' + datetime.time.strftime(self.game_time,'%H:%M') )
connection.close()
except Exception as e:
print('Issue in insertSchedule: \n' + str(e) + '\n' + 'home team schedule name: ' + self.home_team.schedule_name)
def updateTeam(self):
try:
if self.id is not 0 and self.spread_name is not '' and self.schedule_name is not '':
connection = connection_settings.createConnection()
with connection.cursor() as cursor:
cursor.execute(
self.update_team_query,
(self.spread_name, self.schedule_name, self.wins,
self.losses, self.ats_wins, self.ats_losses, self.id))
connection.commit()
except Exception as e:
print('Error in findTeamBySpreadName: ' + str(e))
pass
finally:
connection.close()
def getPicksByDate(self, date):
connectio = None
picks = []
try:
print(date)
connection = connection_settings.createConnection()
if connection is not None and date is not None:
with connection.cursor() as cursor:
cursor.execute(self.select_picks_by_date, (date))
results = cursor.fetchall()
return results
except Exception as e:
print('Issue in getPicksByDate(): ' + str(e))
finally:
if connection is not None:
connection.close()
def updatePick(self):
try:
connection = connection_settings.createConnection()
if connection is not None and self.game_date is not None \
and self.home_team is not None \
and self.away_team is not None:
with connection.cursor() as cursor:
cursor.execute(self.update_pick_by_id,
(self.picked_correctly, self.pick_id))
connection.commit()
print('Update pick complete... picked correctly: ' +
self.favorite_team.spread_name)
except Exception as e:
print('Issue in updatePick(): ' + str(e))
finally:
connection.close()
def insertTeam(self):
try:
if ((self.spread_name is not None and self.spread_name is not '')
and (self.schedule_name is not None
and self.schedule_name is not '')):
connection = connection_settings.createConnection()
with connection.cursor() as cursor:
cursor.execute(
self.insert_team_query,
(self.spread_name, self.schedule_name, self.wins,
self.losses, self.ats_wins, self.ats_losses, self.id))
connection.commit()
except Exception as e:
print('Issue in insertTeam: ' + str(e))
pass
finally:
connection.close()
def selectPick(self):
try:
connection = connection_settings.createConnection()
if connection is not None and self.game_date is not None \
and self.home_team is not None \
and self.away_team is not None:
with connection.cursor() as cursor:
cursor.execute(
self.select_pick_by_date_home_away,
(self.game_date, self.home_team.id, self.away_team.id))
connection.commit()
print('Select pick complete: date: ' + datetime.datetime.strftime(self.game_date,'%Y/%m/%d') + ' time: ' + datetime.time.strftime(self.game_time, '%H:%M') + '. Home team: ' \
+ self.home_team.spread_name + '. Spread: ' + str(self.spread) +'. Away team: ' + self.away_team.spread_name + 'Favorite team: ' + self.favorite_team.spread_name )
except Exception as e:
print('Issue in selectPick(): ' + str(e))
finally:
connection.close()
def updateSchedule(self):
try:
if self.game_date != None \
and self.home_team != None \
and self.away_team != None:
connection = connection_settings.createConnection();
with connection.cursor() as cursor:
cursor.execute(self.update_schedule_sql,(self.home_team_score, self.away_team_score, self.winning_team.id, self.losing_team.id, self.home_team.id, self.away_team.id, self.game_date.date()) )
connection.commit()
print(cursor._last_executed)
#print('game updated for schedule table: ' + self.away_team.schedule_name + ' @ ' + self.home_team.schedule_name + ' ' + datetime.datetime.strftime(self.game_date, '%Y/%m/%d') + str(self.winning_team.id) + str(self.losing_team.id) )
connection.close()
except Exception as e:
print('Issue in updateSchedule: \n' + str(e) + '\n' + 'home team schedule name: ' + self.home_team.schedule_name)
def findTeamBySpreadName(self):
if self.spread_name is not '':
try:
connection = connection_settings.createConnection()
with connection.cursor() as cursor:
cursor.execute(self.find_by_spread_query,
(self.spread_name))
result = cursor.fetchone()
if result == None:
raise RuntimeError(
'Error in findTeamBySpreadName: \'results\' == None for '
+ self.spread_name)
self.createTeamFromResults(result)
except Exception as e:
print('Error in findTeamBySpreadName: ' + str(e))
pass
finally:
connection.close()
def insertPick(self):
insert_pick_query = 'INSERT PICK (GAME_DATE, GAME_TIME, HOME_TEAM_ID, SPREAD, AWAY_TEAM_ID, FAVORITE_TEAM_ID) VALUES (%s, %s, %s, %s, %s, %s)'
try:
connection = connection_settings.createConnection()
if connection is not None and self.game_date is not None \
and self.game_time is not None and self.home_team is not None \
and self.away_team is not None and self.favorite_team is not None:
with connection.cursor() as cursor:
cursor.execute(insert_pick_query,
(self.game_date, self.game_time,
self.home_team.id, self.spread,
self.away_team.id, self.favorite_team.id))
connection.commit()
print('Insert into pick complete: date: ' + datetime.datetime.strftime(self.game_date,'%Y/%m/%d') + '. Home team: ' \
+ self.home_team.spread_name + '. Away team: ' + self.away_team.spread_name )
except Exception as e:
print('Issue in insertPick(): ' + str(e))
finally:
connection.close()
def findTeamByScheduleName(self):
try:
if self.schedule_name is not '':
#TODO - Check if team is in DB.
connection = connection_settings.createConnection()
with connection.cursor() as cursor:
cursor.execute(self.find_by_schedule_query,
(self.schedule_name))
result = cursor.fetchone()
if result == None:
raise RuntimeError(
'Error in findTeamByScheduleName: \'results\' == None for '
+ self.schedule_name)
self.createTeamFromResults(result)
self.printTeam()
except Exception as e:
print('Issue in findTeamByScheduleName: ' + str(e))
pass
finally:
connection.close()
def main():
connection = conn.createConnection()
db_Info = connection.get_server_info()
print("Connected to MySQL database... MySQL Server version on ", db_Info)