def list_all_players(self):
con = Connection()
statement = 'select player_id, name from soccer02.player where name is not null'
con.cur.execute(statement)
res = con.cur.fetchall()
con.close()
return res
def list_all_teams(self):
con = Connection()
# String translation in SQL Query
statement = 'select distinct * from soccer02.team'
con.cur.execute(statement)
res = con.cur.fetchall()
con.close()
return res
def list_all_events(self):
con = Connection()
statement = 'select distinct event_type from soccer02.matchevent where event_type is not null and pos_x is not null and pos_y is not null'
con.cur.execute(statement)
res_tupel = con.cur.fetchall()
res_string_list = [str(i[0]) for i in res_tupel]
con.close()
return res_string_list
def get_players_with_event(self, event_type):
event_type_str = '\'' + event_type + '\''
con = Connection()
statement = 'select player_id, name from soccer02.player where name is not null and player_id in (select player_player_id from soccer02.matchevent where player_player_id = player_id and pos_x is not null and pos_y is not null and event_type = ' + event_type_str + ')'
con.cur.execute(statement)
res_tupel = con.cur.fetchall()
con.close()
return res_tupel
def list_all_matches(self):
con = Connection()
statement = 'select distinct "date", result, home_team_goal, away_team_goal from soccer02.match ' \
'where "date" is not null and result is not null and home_team_goal is not null ' \
'and away_team_goal is not null'
con.cur.execute(statement)
res_tupel = con.cur.fetchall()
res_string_list = [str(i[0]) for i in res_tupel]
con.close()
return res_string_list
def list_teams_with_events(self):
con = Connection()
# String translation in SQL Query
statement = 'select distinct * from soccer02.team ' \
'where team_id in ' \
'(select team_team_id from soccer02.matchevent where pos_x is not null and pos_y is not null group by team_team_id)'
con.cur.execute(statement)
res = con.cur.fetchall()
con.close()
return res
def search_player_with_event(self, player_name, event_type):
search_string = '' + player_name
event_type_str = '\'' + event_type + '\''
con = Connection()
statement = 'select name, player_id from soccer02.player where upper(name) like \'%' + search_string.upper() + '%\' ' \
'and name is not null and player_id in (select player_player_id from soccer02.matchevent where player_player_id = player_id and pos_x is not null and pos_y is not null and event_type = '+ event_type_str +') and rownum <= 30'
con.cur.execute(statement)
res_tupel = con.cur.fetchall()
con.close()
return res_tupel
def match_details(self, home_team_name, away_team_name, date):
con = Connection()
statement = 'select player.name, match.result from soccer02.player, soccer02.match ' \
'inner join soccer02.match on team.team_id = match.team_awayteam_id ' \
'where upper(long_name) like \'%' + home_team_name + '%\' and date = \'' + date + '\' ' \
'and player.name is not null and match.result is not null and team.team_id is not null ' \
'and match.team_awayteam_id is not null'
con.cur.execute(statement)
res = con.cur.fetchall()
con.close()
return res
def search_team(self, team_name):
search_string = '' + team_name
con = Connection()
statement = 'select long_name, short_name from soccer02.team where upper(long_name) like \'%' + search_string.upper() + '%\' ' \
'and long_name is not null and short_name is not null'
con.cur.execute(statement)
res_tupel = con.cur.fetchall()
print("#### Type: ", type(res_tupel))
print("#### Result: ", res_tupel)
print("#### 1. Element of the tuple: ", res_tupel[0][0])
con.close()
return res_tupel
def search_matches_by_team_id(self, team_id, event_type):
str_event_type = '\'' + event_type + '\''
con = Connection()
statement = 'select match_id, team_hometeam_id, team_awayteam_id, home_team_goal, away_team_goal, "date" from soccer02.match ' \
'where (team_hometeam_id = '+ team_id +' or team_awayteam_id = ' + team_id + ') ' \
'and team_hometeam_id is not null and team_awayteam_id is not null and home_team_goal is not null ' \
'and away_team_goal is not null ' \
'and match_id in (select match_match_id from soccer02.matchevent where match_match_id = match_id and event_type = '+ str_event_type +' and pos_x is not null and pos_y is not null)'
con.cur.execute(statement)
res = con.cur.fetchall()
con.close()
return res
def insert_pomodoro(user_id, start_time, end_time, task, category):
formatted_start_time = unix_time_millis(start_time)
formatted_end_time = unix_time_millis(end_time)
try:
connection = Connection()
output = connection.session.execute(
"INSERT INTO pomodoro_by_user (user_id, start_time, end_time, task, category) VALUES (%s,%s,%s,%s,%s)",
[
user_id, formatted_start_time, formatted_end_time, task,
category
])
except Exception as e:
print(e)
print('Failure')
else:
print('Pomodoro recorded')
print('Start Time: {}'.format(start_time))
print('End Time: {}'.format(end_time))
print('Closing connection (up to 10s)')
finally:
connection.close()
print('========================================')
#!/usr/bin/env python3
from db_connection import Connection
import uuid
print('========================================')
print('Start exercise')
spacecraft_name = 'Crew Dragon Endeavour,SpaceX'
journey_id = uuid.UUID('84121060-c66e-11ea-a82e-f931183227ac')
try:
connection = Connection()
output = connection.session.execute(
"select * from spacecraft_speed_over_time where spacecraft_name=%s AND journey_id=%s",
[spacecraft_name, journey_id]
)
offset = 0
for row in output:
print("idx=", offset, "time=", row.reading_time, "value=", row.speed)
offset = offset + 1
except Exception as e:
print(e)
print('Failure')
else:
print('Success')
print('Closing connection (up to 10s)')
finally:
connection.close()
print('========================================')