1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
from .helpers import eq_
from _mssql import substitute_params
def test_single_param():
res = substitute_params(
'SELECT * FROM employees WHERE id = %s',
13)
eq_(res, b'SELECT * FROM employees WHERE id = 13')
res = substitute_params(
'SELECT * FROM empl WHERE name = %s',
b'John Doe')
eq_(res, b"SELECT * FROM empl WHERE name = 'John Doe'")
res = substitute_params(
'SELECT * FROM empl WHERE name = %s',
u'John Doe')
eq_(res, b"SELECT * FROM empl WHERE name = N'John Doe'")
def test_param_quote():
res = substitute_params(
'SELECT * FROM empl WHERE name = %s',
b"John's Doe")
eq_(res, b"SELECT * FROM empl WHERE name = 'John''s Doe'")
res = substitute_params(
'SELECT * FROM empl WHERE name = %s',
u"John's Doe")
eq_(res, b"SELECT * FROM empl WHERE name = N'John''s Doe'")
def test_unicode_params():
res = substitute_params(
u'SELECT * FROM \u0394 WHERE name = %s',
u'\u03A8'
)
eq_(res, b"SELECT * FROM \xce\x94 WHERE name = N'\xce\xa8'")
res = substitute_params(u"testing ascii (\u0105\u010D\u0119) 1=%d 'one'=%s", (1, u'str'))
eq_(res, b"testing ascii (\xc4\x85\xc4\x8d\xc4\x99) 1=1 'one'=N'str'")
def test_single_param_with_d():
res = substitute_params(
'SELECT * FROM employees WHERE id = %d',
13)
eq_(res, b'SELECT * FROM employees WHERE id = 13')
def test_keyed_param_with_d():
res = substitute_params(
'SELECT * FROM employees WHERE id = %(emp_id)d',
{'emp_id': 13})
eq_(res, b'SELECT * FROM employees WHERE id = 13')
def test_percent_not_touched_with_no_params():
sql = "SELECT COUNT(*) FROM employees WHERE name LIKE 'J%'"
res = substitute_params(sql, None)
eq_(res, sql)
def test_tuple_with_in():
res = substitute_params(
'SELECT * FROM empl WHERE id IN %s',
((5, 6),))
eq_(res, b"SELECT * FROM empl WHERE id IN (5,6)")
res = substitute_params(
'SELECT * FROM empl WHERE id IN %s',
((b'foo', b'bar'),))
eq_(res, b"SELECT * FROM empl WHERE id IN ('foo','bar')")
res = substitute_params(
'SELECT * FROM empl WHERE id IN %s',
((u'foo', u'bar'),))
eq_(res, b"SELECT * FROM empl WHERE id IN (N'foo',N'bar')")
# single item
res = substitute_params(
'SELECT * FROM empl WHERE id IN %s',
((b'foo',),))
eq_(res, b"SELECT * FROM empl WHERE id IN ('foo')")
res = substitute_params(
'SELECT * FROM empl WHERE id IN %s',
((u'foo',),))
eq_(res, b"SELECT * FROM empl WHERE id IN (N'foo')")
def test_percent_in_param():
res = substitute_params(
'SELECT * FROM empl WHERE name LIKE %s',
b'J%')
eq_(res, b"SELECT * FROM empl WHERE name LIKE 'J%'")
res = substitute_params(
'SELECT * FROM empl WHERE name LIKE %s',
u'J%')
eq_(res, b"SELECT * FROM empl WHERE name LIKE N'J%'")
def test_single_dict_params():
res = substitute_params(
'SELECT * FROM cust WHERE salesrep = %(name)s',
{'name': b'John Doe'})
eq_(res, b"SELECT * FROM cust WHERE salesrep = 'John Doe'")
res = substitute_params(
'SELECT * FROM cust WHERE salesrep = %(name)s',
{'name': u'John Doe'})
eq_(res, b"SELECT * FROM cust WHERE salesrep = N'John Doe'")
def test_weird_key_names_dict_params():
res = substitute_params(
'SELECT * FROM cust WHERE salesrep = %(n %s ##ame)s',
{'n %s ##ame': b'John Doe'})
eq_(res, b"SELECT * FROM cust WHERE salesrep = 'John Doe'")
res = substitute_params(
'SELECT * FROM cust WHERE salesrep = %(n %s ##ame)s',
{'n %s ##ame': u'John Doe'})
eq_(res, b"SELECT * FROM cust WHERE salesrep = N'John Doe'")
def test_multi_dict_params():
res = substitute_params(
'SELECT * FROM empl '
'WHERE (name = %(name)s AND city = %(city)s) '
'OR supervisor = %(name)s',
{'name': b'John Doe', 'city': b'Nowhere'})
eq_(res, b"SELECT * FROM empl "
b"WHERE (name = 'John Doe' AND city = 'Nowhere') "
b"OR supervisor = 'John Doe'")
res = substitute_params(
'SELECT * FROM empl '
'WHERE (name = %(name)s AND city = %(city)s) '
'OR supervisor = %(name)s',
{'name': u'John Doe', 'city': u'Nowhere'})
eq_(res, b"SELECT * FROM empl "
b"WHERE (name = N'John Doe' AND city = N'Nowhere') "
b"OR supervisor = N'John Doe'")
def test_single_and_tuple():
res = substitute_params(
'SELECT * FROM cust '
'WHERE salesrep = %s AND id IN %s',
(b'John Doe', (1, 2, 3)))
eq_(res, b"SELECT * FROM cust "
b"WHERE salesrep = 'John Doe' AND id IN (1,2,3)")
res = substitute_params(
'SELECT * FROM cust '
'WHERE salesrep = %s AND id IN %s',
(u'John Doe', (1, 2, 3)))
eq_(res, b"SELECT * FROM cust "
b"WHERE salesrep = N'John Doe' AND id IN (1,2,3)")
def test_bare_percent_position():
res = substitute_params('select 5 % %s', 3)
eq_(res, b"select 5 % 3")
def test_bare_percent_dict():
res = substitute_params('select 5 % %(divisor)s', {'divisor': 3})
eq_(res, b"select 5 % 3")
def test_missing_dict_param():
expected_err = 'params dictionary did not contain value for placeholder'
try:
substitute_params(
'SELECT * FROM cust WHERE salesrep = %(name)s',
{'foobar': 'John Doe'})
assert False, \
'expected exception b/c dict did not contain replacement value'
except ValueError as exc:
if expected_err not in str(exc):
raise
def test_too_many_params():
try:
substitute_params(
'SELECT * FROM cust WHERE salesrep = %s and foo = %s',
('bar',))
assert False, 'expected exception b/c too many params in sql'
except ValueError as exc:
if 'more placeholders in sql than params available' not in str(exc):
raise
|
