def test_unary_operators(op, left, expected):
q = Query()
q.select_from(User)
q.where(op(left))
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE ' + expected
def test_query_basics():
q = Query()
q.select_from(User)
q.where(User.id == 42)
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE "t0"."id" = $1'
assert params == (42, )
def test_call():
q = Query()
q.select_from(User)
q.where(func.DATE_FORMAT(User.created_time, "%Y-%m-%d") == "2019-01-01")
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == f'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE DATE_FORMAT("t0"."created_time", $1) = $2'
assert params == ("%Y-%m-%d", "2019-01-01")
def test_op_invert(op, original, inverted):
q = Query()
q.select_from(User)
q.where(op(User.id, User.email))
q.where(~op(User.id, User.email))
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == f'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE "t0"."id" {original} "t0"."email" AND "t0"."id" {inverted} "t0"."email"'
def test_query_alias():
sq = Query().select_from(User).columns(User.email).where(User.id == 42)
q = Query()
q.select_from(User).columns(User.id, User.id.alias("id2"),
sq.alias("xyz_email")).where(User.id == 24)
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."id" as "id2", (SELECT "t1"."email" FROM "User" "t1" WHERE "t1"."id" = $1) as "xyz_email" FROM "User" "t0" WHERE "t0"."id" = $2'
assert params == (42, 24)
def test_in_eq():
q = Query()
q.select_from(User)
q.where(User.id.in_(1, 2, 3) == True)
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE "t0"."id" IN ($1, $2, $3) IS TRUE'
assert params == (1, 2, 3)
def test_having():
q = Query()
q.select_from(User) \
.having(User.id == 42) \
.having(~User.name.is_null()) \
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" HAVING "t0"."id" = $1 AND "t0"."name" IS NOT NULL'
assert params == (42, )
def test_is_true():
q = Query()
q.select_from(User)
q.where(User.id.is_true())
q.where(~User.id.is_true())
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE "t0"."id" IS TRUE AND "t0"."id" IS NOT TRUE'
assert params == ()
def test_group_by():
q = Query()
q.select_from(User) \
.group(User.id) \
.group(User.name, User.email) \
.group(User.name == 42)
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" GROUP BY "t0"."id", "t0"."name", "t0"."email", "t0"."name" = $1'
assert params == (42, )
def test_query_and_or():
q = Query()
q.select_from(User)
q.where(or_(User.id == 1, User.id == 2, User.id == 3))
q.where(User.email == "email")
q.where(or_(and_(User.id == 1, User.id == 2), User.id == 3))
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE ("t0"."id" = $1 OR "t0"."id" = $2 OR "t0"."id" = $3) AND "t0"."email" = $4 AND (("t0"."id" = $1 AND "t0"."id" = $2) OR "t0"."id" = $3)'
assert params == (1, 2, 3, "email")
def test_order_by():
q = Query()
q.select_from(User) \
.order(User.id.asc()) \
.order(User.name) \
.order(User.email.desc()) \
.order(User.email == "email")
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" ORDER BY "t0"."id" ASC, "t0"."name" ASC, "t0"."email" DESC, "t0"."email" = $1 ASC'
assert params == ("email", )
def test_in():
q = Query()
q.select_from(User)
q.where(User.id.in_(1, 2, 3))
q.where(~User.id.in_(1, 2, 3))
q.where(User.id.in_([1, 2, 3]))
q.where(User.id.in_([1, User.email, 3]))
q.where(User.id.in_(1, User.email, 3))
sql, params = dialect.create_query_compiler().compile_select(q)
# assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE "t0"."id" IN ($1, $2, $3) AND "t0"."id" IN ($1, $2, $3) AND "t0"."id" IN ($1, "t0"."email", $3) AND "t0"."id" IN ($1, "t0"."email", $3)'
assert sql == """SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE "t0"."id" IN ($1, $2, $3) AND "t0"."id" NOT IN ($1, $2, $3) AND "t0"."id" IN ($1, $2, $3) AND "t0"."id" IN ($1, "t0"."email", $3) AND "t0"."id" IN ($1, "t0"."email", $3)"""
assert params == (1, 2, 3)
def test_field_alias():
q = Query()
q.select_from(User).columns(User.id, User.id.alias("id2"))
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."id" as "id2" FROM "User" "t0"'
def test_limit_offset():
q = Query()
q.select_from(User).limit(20).offset(0)
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" FETCH FIRST 20 ROWS ONLY'
q = Query()
q.select_from(User).offset(0).limit(20)
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" FETCH FIRST 20 ROWS ONLY'
q = Query()
q.select_from(User).limit(20).offset(1)
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" OFFSET 1 FETCH FIRST 20 ROWS ONLY'
q = Query()
q.select_from(User).offset(1).limit(20)
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" OFFSET 1 FETCH FIRST 20 ROWS ONLY'
q = Query()
q.select_from(User).limit(20)
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" FETCH FIRST 20 ROWS ONLY'
q = Query()
q.select_from(User).limit(1)
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" FETCH FIRST ROW ONLY'
q = Query()
q.select_from(User).offset(20)
sql, params = dialect.create_query_compiler().compile_select(q)
assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" OFFSET 20'
