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_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 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.table_abc.json.has_any_keys(["dates", "imported"]))) self.assertEqual( "SELECT * FROM \"abc\" WHERE \"json\"?|ARRAY['dates','imported']", 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_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_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.json_field.has_any_keys(['dates', 'imported']), ) self.assertEqual( 'SELECT * FROM "abc" WHERE "json"?|ARRAY[\'dates\',\'imported\']', 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_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_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_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_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_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(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 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_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_array_syntax(self): tb = Table("tb") q = PostgreSQLQuery.from_(tb).select(Array(1, "a", ["b", 2, 3])) self.assertEqual(str(q), "SELECT ARRAY[1,'a',ARRAY['b',2,3]] FROM \"tb\"")
def test_queryexception_if_returning_used_on_invalid_query(self): with self.assertRaisesRegex(QueryException, "Returning can't be used in this query"): PostgreSQLQuery.from_(self.table_abc).select("abc").returning("abc")
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))
def query(self): return Query_.from_(self.table)