def test_select_invalid():
query = Query(User).select(100)
message = "`int` is not a valid query expression"
with pytest.raises(InvalidExpressionError, match=message):
query.to_sql()
with pytest.raises(InvalidExpressionError, match=message):
query._deserialize({})
def test_join():
query = Query(User).join("pets")
sql = to_sql(query.to_sql())
assert "JOIN pets AS p0 ON p0.owner_id = users.id" in sql
def test_where_gte():
query = Query(User).where(id__gte=1)
assert "WHERE users.id >= 1" in to_sql(query.to_sql())
def test_where_endswith():
query = Query(User).where(name__endswith="e")
assert "WHERE (users.name LIKE '%%' || 'e')" in to_sql(query.to_sql())
def test_where_contains():
query = Query(User).where(name__contains="u")
assert "WHERE (users.name LIKE '%%' || 'u' || '%%')" in to_sql(query.to_sql())
def test_where_like():
query = Query(User).where(name__like="Sue")
assert "WHERE users.name LIKE 'Sue'" in to_sql(query.to_sql())
def test_select_raw():
query = Query(User).select(raw(100))
assert "SELECT 1" in to_sql(query.to_sql())
def test_outerjoin_duplicate():
query = Query(User).join("pets").outerjoin("pets")
sql = to_sql(query.to_sql())
assert "JOIN pets AS p0 ON p0.owner_id = users.id" in sql
assert "LEFT OUTER JOIN pets AS p1 ON p1.owner_id = users.id" in sql
def test_join_alias():
query = Query(User).join("pets", "p")
sql = to_sql(query.to_sql())
assert "JOIN pets AS p ON p.owner_id = users.id" in sql
def test_missing_join():
query = Query(User).join("pets.owner")
with pytest.raises(MissingJoinError):
query.to_sql()
def test_outerjoin_with_join():
query = Query(User).join("pets").outerjoin("pets.owner")
sql = to_sql(query.to_sql())
assert "JOIN pets AS p0 ON p0.owner_id = users.id" in sql
assert "LEFT OUTER JOIN users AS u0 ON u0.id = p0.owner_id" in sql
def test_limit():
query = Query(User).limit(29)
assert "LIMIT 29" in to_sql(query.to_sql())
def test_nested_join_back():
query = Query(User).join("pets").join("pets.owner").join("pets.owner.pets")
sql = to_sql(query.to_sql())
assert "JOIN pets AS p0 ON p0.owner_id = users.id" in sql
assert "JOIN users AS u0 ON u0.id = p0.owner_id" in sql
assert "JOIN pets AS p1 ON p1.owner_id = u0.id" in sql
def test_duplicate_join():
query = Query(User).join("pets").join("pets")
sql = to_sql(query.to_sql())
assert sql.count("JOIN") == 1
def test_select_dict():
query = Query(User).join("pets", "p").select({"user_id": "id", "pet_id": "p__id"})
assert "SELECT users.id, p.id" in to_sql(query.to_sql())
def test_join_duplicate_with_alias():
query = Query(User).join("pets", "p").join("pets")
sql = to_sql(query.to_sql())
assert "JOIN pets AS p ON p.owner_id = users.id" in sql
assert "JOIN pets AS p0 ON p0.owner_id = users.id" in sql
def test_select_nested():
query = Query(User).join("pets", "p").select(("id", {"pet": [{"id": "p__id"}]}))
assert "SELECT users.id, p.id" in to_sql(query.to_sql())
def test_select_literal():
query = Query(User).select(text("1"))
assert "SELECT 1" in to_sql(query.to_sql())
def test_select_call():
query = Query(User).select(call(lambda x: x, "id"))
assert "SELECT users.id" in to_sql(query.to_sql())
def test_select_name():
query = Query(User).select("id")
assert "SELECT users.id" in to_sql(query.to_sql())
def test_where_not_eq():
query = Query(User).where(name__not_eq="Sue")
assert "WHERE users.name != 'Sue'" in to_sql(query.to_sql())
def test_offset():
query = Query(User).offset(29)
assert "OFFSET 29" in to_sql(query.to_sql())
def test_where_not_ilike():
query = Query(User).where(name__not_ilike="sue")
assert "WHERE users.name NOT ILIKE 'sue'" in to_sql(query.to_sql())
def test_select_join_name():
query = Query(User).join("pets", "p").select("p__id")
assert "SELECT p.id" in to_sql(query.to_sql())
def test_where_startswith():
query = Query(User).where(name__startswith="S")
assert "WHERE (users.name LIKE 'S' || '%%')" in to_sql(query.to_sql())
def test_select_list():
query = Query(User).join("pets", "p").select(["id", "p__id"])
assert "SELECT users.id, p.id" in to_sql(query.to_sql())
def test_where_in():
query = Query(User).where(id__in=[1, 2])
assert "WHERE users.id IN (1, 2)" in to_sql(query.to_sql())
def test_select_tuple():
query = Query(User).join("pets", "p").select(("id", "p__id"))
assert "SELECT users.id, p.id" in to_sql(query.to_sql())
def test_where_lt():
query = Query(User).where(id__lt=1)
assert "WHERE users.id < 1" in to_sql(query.to_sql())
def test_order_by_invalid():
query = Query(User).order_by(1)
message = "`int` is not a valid query expression"
with pytest.raises(InvalidExpressionError, match=message):
query.to_sql()