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)
