def test_json_contained_by(self):
q = PostgreSQLQuery.from_(self.table_abc).select('*').where(
self.json_field.contained_by(
'{"dates": "2018-07-10 - 2018-07-17", "imported": "8"}', ), )
self.assertEqual(
'SELECT * FROM "abc" WHERE "json"<@\'{"dates": "2018-07-10 - 2018-07-17", "imported": "8"}\'',
str(q),
)
q = PostgreSQLQuery.from_(self.table_abc).select('*').where(
self.json_field.contained_by(["One", 'Two', "Three"], ), )
self.assertEqual(
'SELECT * FROM "abc" WHERE "json"<@\'["One", "Two", "Three"]\'',
str(q),
)
q = PostgreSQLQuery.from_(self.table_abc).select('*').where(
self.json_field.contained_by('["One", "Two", "Three"]')
& self.table_abc.id == 26)
self.assertEqual(
'SELECT * FROM "abc" WHERE "json"<@\'["One", "Two", "Three"]\' AND "id"=26',
str(q),
)
def test_get_value_by_key(self):
q = PostgreSQLQuery.from_(self.table_abc).select(
self.json_field.get_value_by_key(['dates', 'import']))
self.assertEqual('SELECT "json"->\'dates\'->>\'import\' FROM "abc"',
str(q))
q = PostgreSQLQuery.from_(self.table_abc).select(
self.json_field.get_value_by_key('dates'))
self.assertEqual('SELECT "json"->\'dates\' FROM "abc"', str(q))
def test_json_has_key(self):
q = PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.json_field.has_key("dates"), )
self.assertEqual(
'SELECT * FROM "abc" WHERE "json"?\'dates\'',
str(q),
)
q = PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.json_field.has_key(JSONField("dates")), )
self.assertEqual(
'SELECT * FROM "abc" WHERE "json"?\'dates\'',
str(q),
)
def test_json_has_any_keys(self):
q = PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.json_field.has_any_keys(['dates', 'imported']), )
self.assertEqual(
'SELECT * FROM "abc" WHERE "json"?|ARRAY[\'dates\',\'imported\']',
str(q))
def test_json_contained_by_using_list_arg(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.contained_by(["One", "Two", "Three"])))
self.assertEqual(
'SELECT * FROM "abc" WHERE "json"<@\'["One","Two","Three"]\'',
str(q))
def test_json_has_any_keys(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.has_any_keys(["dates", "imported"])))
self.assertEqual(
"SELECT * FROM \"abc\" WHERE \"json\"?|ARRAY['dates','imported']",
str(q))
def test_distinct_on(self):
q = (PostgreSQLQuery.from_(self.table_abc).distinct_on(
"lname", self.table_abc.fname).select("lname", "id"))
self.assertEqual(
'''SELECT DISTINCT ON("lname","fname") "lname","id" FROM "abc"''',
str(q))
def test_render_alias_in_array_sql(self):
tb = Table("tb")
q = PostgreSQLQuery.from_(tb).select(
Array(tb.col).as_("different_name"))
self.assertEqual(str(q),
'SELECT ARRAY["col"] "different_name" FROM "tb"')
def test_get_text_value_by_index(self):
q = (
PostgreSQLQuery.from_(self.table_abc)
.select("*")
.where(self.table_abc.json.get_text_value(1))
)
self.assertEqual('SELECT * FROM "abc" WHERE "json"->>1', str(q))
def test_get_json_value_by_key(self):
q = (
PostgreSQLQuery.from_(self.table_abc)
.select("*")
.where(self.table_abc.json.get_json_value("dates"))
)
self.assertEqual('SELECT * FROM "abc" WHERE "json"->\'dates\'', str(q))
def test_json_has_key(self):
q = (
PostgreSQLQuery.from_(self.table_abc)
.select("*")
.where(self.table_abc.json.has_key("dates")) # noqa: W601
)
self.assertEqual('SELECT * FROM "abc" WHERE "json"?\'dates\'', str(q))
def test_get_path_text_value(self):
q = (
PostgreSQLQuery.from_(self.table_abc)
.select("*")
.where(self.table_abc.json.get_path_text_value("{a,b}"))
)
self.assertEqual('SELECT * FROM "abc" WHERE "json"#>>\'{a,b}\'', str(q))
def test_subnet_contains_inet(self):
q = (
PostgreSQLQuery.from_(self.table_abc)
.select(self.table_abc.a.lshift(2))
.where(self.table_abc.cidr >> "1.1.1.1")
)
self.assertEqual("SELECT \"a\"<<2 FROM \"abc\" WHERE \"cidr\">>'1.1.1.1'", str(q))
def test_returning_from_missing_table_raises_queryexception(self):
field_from_diff_table = Field('xyz', table=Table('other'))
with self.assertRaisesRegex(QueryException,
"You can't return from other tables"):
(PostgreSQLQuery.from_(self.table_abc).where(
self.table_abc.foo == self.table_abc.bar).delete().returning(
field_from_diff_table))
def test_json_contained_by_with_complex_criterion(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.contained_by(["One", "Two", "Three"])
& (self.table_abc.id == 26)))
self.assertEqual(
'SELECT * FROM "abc" WHERE "json"<@\'["One","Two","Three"]\' AND "id"=26',
str(q),
)
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 test_json_contains(self):
q = PostgreSQLQuery.from_(self.table_abc, ).select("*").where(
self.json_field.contains({"dates": "2018-07-10 - 2018-07-17"}, ), )
self.assertEqual(
'SELECT * '
'FROM "abc" '
'WHERE "json"@>\'{"dates": "2018-07-10 - 2018-07-17"}\'',
str(q),
)
q = PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.json_field.contains('{"dates": "2018-07-10 - 2018-07-17"}'))
self.assertEqual(
'SELECT * '
'FROM "abc" '
'WHERE "json"@>\'{"dates": "2018-07-10 - 2018-07-17"}\'',
str(q),
)
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_json_contains_for_field(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.contains({"dates":
"2018-07-10 - 2018-07-17"})))
self.assertEqual(
"SELECT * "
'FROM "abc" '
'WHERE "json"@>\'{"dates":"2018-07-10 - 2018-07-17"}\'',
str(q),
)
def test_upsert(self):
q = (
PostgreSQLQuery.into("abc")
.insert(1, "b", False)
.as_("aaa")
.on_conflict(self.table_abc.id)
.do_update("abc")
)
self.assertEqual(
'INSERT INTO "abc" VALUES (1,\'b\',false) ON CONFLICT ("id") DO UPDATE SET "abc"=EXCLUDED."abc"',
str(q),
)
def test_json_contained_by_using_str_arg(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.contained_by(
OrderedDict([
("dates", "2018-07-10 - 2018-07-17"),
("imported", "8"),
]))))
self.assertEqual(
'SELECT * FROM "abc" '
'WHERE "json"<@\'{"dates":"2018-07-10 - 2018-07-17","imported":"8"}\'',
str(q),
)
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 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_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 test_alias_set_correctly(self):
table = Table('jsonb_table')
q = PostgreSQLQuery.from_('abc').select(
table.value.get_text_value('a').as_('name'))
self.assertEqual('''SELECT "value"->>'a' "name" FROM "abc"''', str(q))
