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