def test_format():
args = 1, 2, 3
new_query, vals = convert(
"format", "SELECT %s, %s, \"f1_%%\", E'txt_%%' "
"FROM t WHERE a=%s AND b='75%%' AND c = '%' -- Comment with %", args)
expected = "SELECT $1, $2, \"f1_%%\", E'txt_%%' FROM t WHERE a=$3 AND " \
"b='75%%' AND c = '%' -- Comment with %"
assert (new_query, vals) == (expected, args)
sql = r"""COMMENT ON TABLE test_schema.comment_test """ \
r"""IS 'the test % '' " \ table comment'"""
new_query, vals = convert("format", sql, args)
assert (new_query, vals) == (sql, args)
def test_numeric():
args = 1, 2, 3
new_query, vals = convert(
"numeric", "SELECT sum(x)::decimal(5, 2) :2, :1, * FROM t WHERE a=:3",
args)
expected = "SELECT sum(x)::decimal(5, 2) $2, $1, * FROM t WHERE a=$3"
assert (new_query, vals) == (expected, args)
def test_qmark_2():
args = 1, 2, 3
new_query, vals = convert(
"qmark", "SELECT ?, ?, * FROM t WHERE a=? AND b='are you ''sure?'",
args)
expected = "SELECT $1, $2, * FROM t WHERE a=$3 AND b='are you ''sure?'"
assert (new_query, vals) == (expected, args)
def test_qmark():
args = 1, 2, 3
new_query, vals = convert(
"qmark", "SELECT ?, ?, \"field_?\" FROM t "
"WHERE a='say ''what?''' AND b=? AND c=E'?\\'test\\'?'", args)
expected = "SELECT $1, $2, \"field_?\" FROM t WHERE " \
"a='say ''what?''' AND b=$3 AND c=E'?\\'test\\'?'"
assert (new_query, vals) == (expected, args)
def test_py_format():
args = {"f2": 1, "f1": 2, "f3": 3}
new_query, vals = convert(
"pyformat", "SELECT %(f2)s, %(f1)s, \"f1_%%\", E'txt_%%' "
"FROM t WHERE a=%(f2)s AND b='75%%'", args)
expected = "SELECT $1, $2, \"f1_%%\", E'txt_%%' FROM t WHERE a=$1 AND " \
"b='75%%'"
assert (new_query, vals) == (expected, (1, 2))
# pyformat should support %s and an array, too:
args = 1, 2, 3
new_query, vals = convert(
"pyformat", "SELECT %s, %s, \"f1_%%\", E'txt_%%' "
"FROM t WHERE a=%s AND b='75%%'", args)
expected = "SELECT $1, $2, \"f1_%%\", E'txt_%%' FROM t WHERE a=$3 AND " \
"b='75%%'"
assert (new_query, vals) == (expected, args)
def test_named():
args = {
"f_2": 1,
"f1": 2,
}
new_query, vals = convert(
"named", "SELECT sum(x)::decimal(5, 2) :f_2, :f1 FROM t WHERE a=:f_2",
args)
expected = "SELECT sum(x)::decimal(5, 2) $1, $2 FROM t WHERE a=$1"
assert (new_query, vals) == (expected, (1, 2))
def test_qmark():
args = 1, 2, 3
new_query, vals = convert(
"qmark",
'SELECT ?, ?, "field_?" FROM t '
"WHERE a='say ''what?''' AND b=? AND c=E'?\\'test\\'?'",
args,
)
expected = ('SELECT $1, $2, "field_?" FROM t WHERE '
"a='say ''what?''' AND b=$3 AND c=E'?\\'test\\'?'")
assert (new_query, vals) == (expected, args)
def test_format_multiline():
args = 1, 2, 3
new_query, vals = convert("format", "SELECT -- Comment\n%s FROM t", args)
assert (new_query, vals) == ("SELECT -- Comment\n$1 FROM t", args)
def test_numeric_default_parameter():
args = 1, 2, 3
new_query, vals = convert("numeric", "make_interval(days := 10)", args)
assert (new_query, vals) == ("make_interval(days := 10)", args)
