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 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_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 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_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 __init__(self):
Connection.__init__(self,
host=DB_config['host'],
username=DB_config['username'],
password=DB_config['password'],
database=DB_config['database'])
self.sql = ''
self.fields = ''
self.table = ''
self.where_condition = ''
self.placeholders = []
self.limit = ''
self.orderby = ''
def list_event_by_name(self, event_type):
search_string = '' + event_type
con = Connection()
statement = 'select distinct * from soccer02.matchevent ' \
'where upper(event_type) like \'%' + search_string.upper() + '%\' ' \
'and pos_x is not null and pos_y is not null'
con.cur.execute(statement)
res = con.cur.fetchall()
return res
def specific_event(self, event_id):
con = Connection()
statement = 'select pos_x, pos_y, event_type, elapsed, "comment" from soccer02.matchevent ' \
'where matchevent_id = ' + event_id + ' ' \
'and pos_x is not null and pos_y is not null and event_type is not null ' \
'and elapsed is not null and "comment" is not null'
con.cur.execute(statement)
res = con.cur.fetchall()
return res
def player_heatmap(self, player_id, event_type):
con = Connection()
statement = 'select pos_x, pos_y, sub_type, ' \
'team_team_id, player_player_id, ' \
'player_playerfouled, player_playerasst ' \
'from soccer02.matchevent where player_player_id = \'' + player_id + '\' and event_type = \'' + event_type + '\' ' \
'and pos_x is not null and pos_y is not null and player_player_id is not null'
con.cur.execute(statement)
res = con.cur.fetchall()
return res
def credential_manager(self, username):
guest = Connection("guest", "guest")
query = f"SELECT * FROM credentials"
data = None
with guest.cursor.execute(query):
row = guest.cursor.fetchone()
while row:
if username == row[1]:
# Returns a List (if username found)
data = row
row = guest.cursor.fetchone()
# OR returns a boolean if username not found
return data if data != None else False
def all_foulcommits(self):
con = Connection()
statement = 'select * from matchevent ' \
'where event_type like \'foulcommit\' ' \
'and team_team_id is not null '\
'and player_player_id is not null '\
'and match_match_id is not null '\
'and matchevent_id is not null '\
'and pos_x is not null '\
'and pos_y is not null '\
'and elapsed is not null '\
'and rownum <= 80000'
con.cur.execute(statement)
res = con.cur.fetchall()
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('========================================')
[1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 4, 1],
[1, 0, 1, 0, 0, 0, 1, 0, 1, 1, 1, 0, 0, 1, 1, 1],
[1, 0, 0, 0, 1, 1, 1, 4, 0, 0, 0, 0, 0, 0, 3, 1],
[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]],
[[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1],
[1, 2, 0, 0, 1, 0, 5, 0, 0, 0, 0, 4, 0, 1, 3, 1],
[1, 0, 0, 0, 1, 0, 1, 1, 1, 1, 0, 0, 0, 1, 0, 1],
[1, 0, 0, 0, 1, 0, 1, 0, 0, 1, 1, 1, 0, 1, 0, 1],
[1, 1, 1, 0, 1, 0, 1, 1, 0, 1, 0, 0, 0, 1, 0, 1],
[1, 0, 0, 0, 1, 4, 0, 1, 0, 1, 0, 0, 0, 1, 0, 1],
[1, 0, 1, 1, 1, 0, 0, 1, 0, 1, 0, 0, 0, 1, 0, 1],
[1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1],
[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]]]
CELL_SIZE = 50
DB_CONNECTION = Connection("state.db")
class Main:
"""The main game class, in charge of loading levels and handling
features which are outside the game loop
Attributes:
display: a pygame display object
level_list: list of levels
level_id: id of the current level
level: Level object
event_queue: EventQueue object
clock: a pygame clock object
mixer: a mixer object
"""
def test_conn(self):
# Checking if the object is instantiated correctly, and therefore that the connection was established
test = Connection("jake", "jake")
self.assertIsInstance(test, Connection)
def get_user_comments(comment_user_id): connection = Connection() query = "select title, comment from askreddit.comments_by_user where comment_user_id = " + comment_user_id + ";" df = pd.DataFrame(list(connection.session.execute(query))) return df
#!/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('========================================')
def get_player_by_id(self, player_id):
con = Connection()
statement = 'select * from soccer02.player where player_id = ' + player_id
con.cur.execute(statement)
res = con.cur.fetchall()
return res
