def test_no_queryexception_if_returning_used_on_valid_query_type(self):
# No exceptions for insert, update and delete queries
with self.subTest('DELETE'):
PostgreSQLQuery.from_(self.table_abc).where(self.table_abc.foo == self.table_abc.bar).delete().returning(
"id"
)
with self.subTest('UPDATE'):
PostgreSQLQuery.update(self.table_abc).where(self.table_abc.foo == 0).set("foo", "bar").returning("id")
with self.subTest('INSERT'):
PostgreSQLQuery.into(self.table_abc).insert('abc').returning('abc')
def update_has_fifa_stat(self, player_id, value=False):
"""
UPDATE player_stats
SET has_fifa_stat = false
WHERE sc_player_id = 2867;
"""
self._q.put(
str(
PostgreSQLQuery.update(tables.players_stats).set(
tables.players_stats.has_fifa_stat, value).where(
tables.players_stats.sc_player_id == player_id)))
def test_return_field_from_join_table(self):
new_table = Table('xyz')
q = (PostgreSQLQuery.update(self.table_abc).join(new_table).on(
new_table.id == self.table_abc.xyz).where(
self.table_abc.foo == 0).set("foo",
"bar").returning(new_table.a))
self.assertEqual(
'UPDATE "abc" '
'JOIN "xyz" ON "xyz"."id"="abc"."xyz" '
'SET "foo"=\'bar\' '
'WHERE "abc"."foo"=0 '
'RETURNING "xyz"."a"',
str(q),
)
def update_fifa_stat(self, player_id, match_id, fifa_stat):
"""
sc_player_id integer,
match_id integer,
sc_stat blob,
fifa_stat blob,
has_sc_stat boolean,
has_fifa_stat boolean,
"""
has_fifa_stat = True if fifa_stat is not None else False
self._q.put(
str(
PostgreSQLQuery.update(tables.players_stats).set(
tables.players_stats.fifa_stat,
JSON(fifa_stat) if fifa_stat is not None else
None).set(tables.players_stats.has_fifa_stat,
has_fifa_stat).
where((tables.players_stats.sc_player_id == player_id)
& (tables.players_stats.match_id == match_id))))
def update_match_statistic(self, match_id, football_df):
"""
match_id INTEGER PRIMARY KEY,
sc_statistics JSON,
fd_statistics JSON,
sc_forms JSON,
sc_votes JSON,
sc_manager_duels JSON,
sc_h2h JSON,
home_score FLOAT,
away_score FLOAT,
"""
tempdict = {}
list_of_cols = [
'HS', 'AS', 'HST', 'AST', 'HHW', 'AHW', 'HC', 'AC', 'HF', 'AF',
'HFKC', 'AFKC', 'HO', 'AO', 'HY', 'AY', 'HR', 'AR', 'HBP',
'ABP', 'Time', 'HTHG', 'HTAG'
]
pd_cols = football_df.keys()
try:
filtered_df = football_df[intersection(list_of_cols, pd_cols)]
filtered_df = filtered_df.dropna(axis='columns')
tempdict = filtered_df.to_dict(orient='records')
tempdict = tempdict[0]
except Exception:
pass
home_score = safe_cast(football_df.iloc[0]['FTHG'], int)
away_score = safe_cast(football_df.iloc[0]['FTAG'], int)
self._q.put(
str(
PostgreSQLQuery.update(tables.statistics).set(
tables.statistics.home_score, home_score).set(
tables.statistics.away_score, away_score).set(
tables.statistics.fd_statistics,
JSON(tempdict)
if len(tempdict.keys()) > 0 else None).where(
tables.statistics.match_id == match_id)))
def update_match_odds(self, match_id, football_df):
"""
match_id INTEGER,
sc_odds JSON,
fd_odds JSON
"""
tempdict = {}
list_of_cols = [
'B365H',
'B365D',
'B365A',
'BSH',
'BSD',
'BSA',
'BWH',
'BWD',
'BWA',
'GBH',
'GBD',
'GBA',
'IWH',
'IWD',
'IWA',
'LBH',
'LBD',
'LBA',
'PSH' and 'PH',
'PSD' and 'PD',
'PSA' and 'PA',
'SOH',
'SOD',
'SOA',
'SBH',
'SBD',
'SBA',
'SJH',
'SJD',
'SJA',
'SYH',
'SYD',
'SYA',
'VCH',
'VCD',
'VCA',
'WHH',
'WHD',
'WHA',
'Bb1X2',
'BbMxH',
'BbAvH',
'BbMxD',
'BbAvD',
'BbMxA',
'BbAvA',
'MaxH',
'MaxD',
'MaxA',
'AvgH',
'AvgD',
'AvgA',
'BbOU',
'BbMx>2.5',
'BbAv>2.5',
'BbMx<2.5',
'BbAv<2.5',
'GB>2.5',
'GB<2.5',
'B365>2.5',
'B365<2.5',
'P>2.5',
'P<2.5',
'Max>2.5',
'Max<2.5',
'Avg>2.5',
'Avg<2.5',
'BbAH',
'BbAHh',
'AHh',
'BbMxAHH',
'BbAvAHH',
'BbMxAHA',
'BbAvAHA',
'GBAHH',
'GBAHA',
'GBAH',
'LBAHH',
'LBAHA',
'LBAH',
'B365AHH',
'B365AHA',
'B365AH',
'PAHH',
'PAHA',
'MaxAHH',
'MaxAHA',
'AvgAHH',
'AvgAHA',
]
pd_cols = football_df.keys()
try:
filtered_df = football_df[intersection(list_of_cols, pd_cols)]
filtered_df = filtered_df.dropna(axis='columns')
tempdict = filtered_df.to_dict(orient='records')
tempdict = tempdict[0]
except Exception:
pass
self._q.put(
str(
PostgreSQLQuery.update(tables.odds).set(
tables.odds.fd_odds,
JSON(tempdict) if len(tempdict.keys()) > 0 else
None).where(tables.odds.match_id == match_id)))
def update(self):
return Query_.update(self.table)
