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))