def test_select_empty():
query = Query(User)
with pytest.raises(InvalidSelectError):
query.select([]).to_sql()
with pytest.raises(InvalidSelectError):
query.select(()).to_sql()
with pytest.raises(InvalidSelectError):
query.select({}).to_sql()
async def test_select_dict(repo):
user = await repo.insert(User())
pet = await repo.insert(Pet(owner_id=user.id))
query = Query(User).join("pets", "p").select({
"user_id": "id",
"pet_id": "p__id"
})
assert await repo.one(query) == {"user_id": user.id, "pet_id": pet.id}
async def test_all(repo):
await repo.insert(User(name="Foo"))
users = await repo.all(Query(User))
assert users[0].name == "Foo"
users = await repo.all(User)
assert users[0].name == "Foo"
async def test_get_by(repo):
await repo.insert(User(name="Foo"))
user = await repo.get_by(Query(User), name="Foo")
assert user.name == "Foo"
user = await repo.get_by(User, name="Foo")
assert user.name == "Foo"
async def test_one(repo):
await repo.insert(User(name="Foo"))
user = await repo.one(Query(User))
assert user.name == "Foo"
user = await repo.one(User)
assert user.name == "Foo"
async def test_get(repo):
user = await repo.insert(User(name="Foo"))
user_id = user.id
user = await repo.get(Query(User), user_id)
assert user.id == user_id
user = await repo.get(User, user_id)
assert user.id == user_id
async def test_select_nested(repo):
user = await repo.insert(User())
pet = await repo.insert(Pet(owner_id=user.id))
query = Query(User).join("pets", "p").select(("id", {
"pet": [{
"id": "p__id"
}]
}))
assert await repo.one(query) == (user.id, {"pet": [{"id": pet.id}]})
async def test_first(repo):
user = await repo.first(User)
assert user is None
await repo.insert(User(name="Foo"))
user = await repo.first(Query(User))
assert user.name == "Foo"
user = await repo.first(User)
assert user.name == "Foo"
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_select_raw():
query = Query(User).select(raw(100))
assert "SELECT 1" in to_sql(query.to_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_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_select_literal():
query = Query(User).select(text("1"))
assert "SELECT 1" 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_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_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_like():
query = Query(User).where(name__like="Sue")
assert "WHERE users.name LIKE 'Sue'" 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_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())