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