1import pytest
2
3from pg8000.dbapi import convert_paramstyle
4
5#        "(id %% 2) = 0",
6
7
8@pytest.mark.parametrize(
9    "query,statement",
10    [
11        [
12            'SELECT ?, ?, "field_?" FROM t '
13            "WHERE a='say ''what?''' AND b=? AND c=E'?\\'test\\'?'",
14            'SELECT $1, $2, "field_?" FROM t WHERE '
15            "a='say ''what?''' AND b=$3 AND c=E'?\\'test\\'?'",
16        ],
17        [
18            "SELECT ?, ?, * FROM t WHERE a=? AND b='are you ''sure?'",
19            "SELECT $1, $2, * FROM t WHERE a=$3 AND b='are you ''sure?'",
20        ],
21    ],
22)
23def test_qmark(query, statement):
24    args = 1, 2, 3
25    new_query, vals = convert_paramstyle("qmark", query, args)
26    assert (new_query, vals) == (statement, args)
27
28
29@pytest.mark.parametrize(
30    "query,expected",
31    [
32        [
33            "SELECT sum(x)::decimal(5, 2) :2, :1, * FROM t WHERE a=:3",
34            "SELECT sum(x)::decimal(5, 2) $2, $1, * FROM t WHERE a=$3",
35        ],
36    ],
37)
38def test_numeric(query, expected):
39    args = 1, 2, 3
40    new_query, vals = convert_paramstyle("numeric", query, args)
41    assert (new_query, vals) == (expected, args)
42
43
44@pytest.mark.parametrize(
45    "query",
46    [
47        "make_interval(days := 10)",
48    ],
49)
50def test_numeric_unchanged(query):
51    args = 1, 2, 3
52    new_query, vals = convert_paramstyle("numeric", query, args)
53    assert (new_query, vals) == (query, args)
54
55
56def test_named():
57    args = {
58        "f_2": 1,
59        "f1": 2,
60    }
61    new_query, vals = convert_paramstyle(
62        "named", "SELECT sum(x)::decimal(5, 2) :f_2, :f1 FROM t WHERE a=:f_2", args
63    )
64    expected = "SELECT sum(x)::decimal(5, 2) $1, $2 FROM t WHERE a=$1"
65    assert (new_query, vals) == (expected, (1, 2))
66
67
68@pytest.mark.parametrize(
69    "query,expected",
70    [
71        [
72            "SELECT %s, %s, \"f1_%%\", E'txt_%%' "
73            "FROM t WHERE a=%s AND b='75%%' AND c = '%' -- Comment with %",
74            "SELECT $1, $2, \"f1_%%\", E'txt_%%' FROM t WHERE a=$3 AND "
75            "b='75%%' AND c = '%' -- Comment with %",
76        ],
77        [
78            "SELECT -- Comment\n%s FROM t",
79            "SELECT -- Comment\n$1 FROM t",
80        ],
81    ],
82)
83def test_format_changed(query, expected):
84    args = 1, 2, 3
85    new_query, vals = convert_paramstyle("format", query, args)
86    assert (new_query, vals) == (expected, args)
87
88
89@pytest.mark.parametrize(
90    "query",
91    [
92        r"""COMMENT ON TABLE test_schema.comment_test """
93        r"""IS 'the test % '' " \ table comment'""",
94    ],
95)
96def test_format_unchanged(query):
97    args = 1, 2, 3
98    new_query, vals = convert_paramstyle("format", query, args)
99    assert (new_query, vals) == (query, args)
100
101
102def test_py_format():
103    args = {"f2": 1, "f1": 2, "f3": 3}
104
105    new_query, vals = convert_paramstyle(
106        "pyformat",
107        "SELECT %(f2)s, %(f1)s, \"f1_%%\", E'txt_%%' "
108        "FROM t WHERE a=%(f2)s AND b='75%%'",
109        args,
110    )
111    expected = "SELECT $1, $2, \"f1_%%\", E'txt_%%' FROM t WHERE a=$1 AND " "b='75%%'"
112    assert (new_query, vals) == (expected, (1, 2))
113
114
115def test_pyformat_format():
116    """pyformat should support %s and an array, too:"""
117    args = 1, 2, 3
118    new_query, vals = convert_paramstyle(
119        "pyformat",
120        "SELECT %s, %s, \"f1_%%\", E'txt_%%' " "FROM t WHERE a=%s AND b='75%%'",
121        args,
122    )
123    expected = "SELECT $1, $2, \"f1_%%\", E'txt_%%' FROM t WHERE a=$3 AND " "b='75%%'"
124    assert (new_query, vals) == (expected, args)
125