def test_raw_query():
table1 = Table(name="users", fields=["id", "name", "city_id"], alias="us")
table2 = Table(name="city", fields=["id", "name", "country"], alias="cit")
table3 = Table(name="account", fields=['user_id', "bank"], alias="acc")
query = Query(prettify=False) \
.add(table1) \
.add(table2) \
.add(table3) \
.join(table1, "id", table2, "id") \
.join(table1, "id", table3, "user_id") \
.where(table2, "country", '= "France"') \
.order_by(table1, "-id") \
.limit(100) \
.build()
expected = """SELECT `us`.`id`, `us`.`name`, `us`.`city_id`, `cit`.`id`, `cit`.`name`, `cit`.`country`, """ \
"""`acc`.`user_id`, `acc`.`bank`
FROM `users` AS `us`
INNER JOIN `city` AS `cit`
ON `cit`.`id` = `us`.`id`
INNER JOIN `account` AS `acc`
ON `acc`.`user_id` = `us`.`id`
WHERE `cit`.`country` = "France"
ORDER BY `us`.`id` DESC
LIMIT 100;"""
assert query == expected
def test_prettified_query():
table1 = Table(name="users", fields=["id", "name", "city_id"], alias="us")
table2 = Table(name="city", fields=["id", "name", "country"], alias="cit")
table3 = Table(name="account", fields=['user_id', "bank"], alias="acc")
query = Query(prettify=True) \
.add(table1) \
.add(table2) \
.add(table3) \
.join(table1, "id", table2, "id") \
.join(table1, "id", table3, "user_id") \
.where(table2, "country", '= "France"') \
.order_by(table1, "-id") \
.limit(100) \
.build()
expected = """SELECT us.id, us.name, us.city_id, cit.id, cit.name, cit.country, acc.user_id, acc.bank
FROM users AS us
INNER JOIN city AS cit
ON cit.id = us.id
INNER JOIN account AS acc
ON acc.user_id = us.id
WHERE cit.country = "France"
ORDER BY us.id DESC
LIMIT 100;"""
assert query == expected
def table2(self):
return Table(name="address", fields=["id", "user_id", "city", "country"], alias="ad")
def table1(self):
return Table(name="users", fields=["id", "username", "email", "password"], alias="us")
def test_repr_should_return_tablename_and_alias(self):
table = Table(name="users", alias="us")
from_expression = From(table=table)
assert repr(from_expression) == "<From # users (us)>"
def test_should_return_from_expression_with_alias(self):
table = Table(name="users", alias="us")
from_expression = From(table=table)
assert from_expression.build() == "\nFROM `users` AS `us` "
def test_str_should_return_sql_select_expression(self):
table1 = Table(name="users", fields=["username", "email", "password"], alias="us")
select = Select().add(table=table1)
assert str(select) == "SELECT `us`.`username`, `us`.`email`, `us`.`password` "
def test_table_should_return_custom_alias(self):
table = Table(name="users", alias="aze")
assert table.name == "users"
assert table.alias == "aze"
def test_repr_should_return_where_expression_details(self):
table = Table(name="users", fields=["id", "username", "email", "password"], alias="us")
where = Where(table=table, field="id", predicate="= 12")
assert repr(where) == "<Where: users :: id :: = 12>"
def test_none_fields_returns_void_string(self):
table = Table(name="users", fields="id", alias="us")
order_by = OrderBy(table=table, field=None)
with pytest.raises(AttributeError):
order_by.build()
def test_headers_should_be_a_list():
table1 = Table(name="users", fields=["id", "name", "city_id"], alias="us")
query = Query().add(table1)
assert query.headers == ['id', 'name', 'city_id']
def test_query_as_str():
table1 = Table(name="users", fields=["id", "name", "city_id"], alias="us")
query = Query().add(table1)
expected = """SELECT `us`.`id`, `us`.`name`, `us`.`city_id`
FROM `users` AS `us` ;"""
assert str(query) == expected
def test_table_without_fields_should_return_none(self):
table = Table("name")
assert table.fields is None
def test_should_select_given_fields(self):
table = Table(name="users", fields=["username", "email", "password"], alias="us")
select = Select().add(table=table)
assert select.build() == "SELECT `us`.`username`, `us`.`email`, `us`.`password` "
def test_no_filled_field_should_select_everything(self):
table = Table(name="users")
select = Select().add(table=table)
assert select.build() == "SELECT * "
def test_repr_should_return_both_tables_names(self):
table1 = Table(name="users", fields=["id", "username", "email", "password"], alias="us")
table2 = Table(name="address", fields=["id", "user_id", "city", "country"], alias="ad")
join = Join(table1=table1, field1="id", table2=table2, field2="user_id")
assert repr(join) == "<Join: users and address>"
def test_repr_should_return_selected_tables_names(self):
table1 = Table("users")
table2 = Table("books")
select = Select().add(table=table1).add(table=table2)
assert repr(select) == "<Select: users, books>"
def test_str_should_return_where_expression(self):
table = Table(name="users", fields=["id", "username", "email", "password"], alias="us")
where = Where(table=table, field="id", predicate="= 12")
assert str(where) == "\nWHERE `us`.`id` = 12 "
def test_table_should_generate_an_alias(self, mock_random):
mock_random.return_value = "aze"
table = Table("users")
assert table.name == "users"
assert table.alias == "aze"
