def test_json_contains_for_json(self):
q = PostgreSQLQuery.select(JSON({"a": 1, "b": 2}).contains({"a": 1}))
# gotta split this one up to avoid the indeterminate order
sql = str(q)
start, end = 9, -13
self.assertEqual("SELECT '{}'@>'{\"a\":1}'", sql[:start] + sql[end:])
members_set = set(sql[start:end].split(","))
self.assertSetEqual({'"a":1', '"b":2'}, members_set)
def test_json_value_from_dict_recursive(self):
q = PostgreSQLQuery.select(JSON({"a": "z", "b": {"c": "foo"}, "d": 1}))
# gotta split this one up to avoid the indeterminate order
sql = str(q)
start, end = 9, -2
self.assertEqual("SELECT '{}'", sql[:start] + sql[end:])
members_set = set(sql[start:end].split(","))
self.assertSetEqual({'"a":"z"', '"b":{"c":"foo"}', '"d":1'}, members_set)
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 test_json_value_from_array_str(self):
q = PostgreSQLQuery.select(JSON(["a", "b", "c"]))
self.assertEqual('SELECT \'["a","b","c"]\'', str(q))
def test_json_value_from_array_num(self):
q = PostgreSQLQuery.select(JSON([1, 2, 3]))
self.assertEqual("SELECT '[1,2,3]'", str(q))
def test_json_value_from_dict(self):
q = PostgreSQLQuery.select(JSON({"a": "foo"}))
self.assertEqual('SELECT \'{"a":"foo"}\'', str(q))
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)))
