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_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_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_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_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")
