-
-
Notifications
You must be signed in to change notification settings - Fork 1.8k
Expand file tree
/
Copy pathtest_tstrings_py314.py
More file actions
441 lines (376 loc) · 14.5 KB
/
Copy pathtest_tstrings_py314.py
File metadata and controls
441 lines (376 loc) · 14.5 KB
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
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
"""Test the TString construct for Python 3.14+ template strings."""
from itertools import zip_longest
from sqlalchemy import column
from sqlalchemy import exc
from sqlalchemy import Integer
from sqlalchemy import JSON
from sqlalchemy import literal
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy import testing
from sqlalchemy import tstring
from sqlalchemy.engine.interfaces import CacheStats
from sqlalchemy.sql import table
from sqlalchemy.sql.elements import ColumnClause
from sqlalchemy.sql.sqltypes import TypeEngine
from sqlalchemy.testing import AssertsCompiledSQL
from sqlalchemy.testing import eq_
from sqlalchemy.testing import fixtures
from sqlalchemy.testing.assertions import expect_raises_message
table1 = table(
"mytable",
column("myid", Integer),
column("name", String),
column("description", String),
)
table2 = table(
"myothertable", column("otherid", Integer), column("othername", String)
)
class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = "default"
def test_basic_literal_interpolation(self):
a = 5
b = 10
stmt = tstring(t"select {a}, {b}")
self.assert_compile(
stmt,
"select :param_1, :param_2",
checkparams={"param_1": 5, "param_2": 10},
)
def test_no_strings(self):
with expect_raises_message(
exc.ArgumentError, r"pep-750 Tstring \(e.g. t'...'\) expected"
):
tstring("select * from table") # type: ignore
def test_tstring_literal_passthrough(self):
stmt = tstring(t"select * from foo where lala = bar")
self.assert_compile(stmt, "select * from foo where lala = bar")
def test_sqlalchemy_expression_interpolation(self):
subq = select(literal(1)).scalar_subquery()
stmt = tstring(t"SELECT {subq}")
self.assert_compile(
stmt,
"SELECT (SELECT :param_1 AS anon_1)",
checkparams={"param_1": 1},
)
def test_column_interpolation(self):
stmt = tstring(t"SELECT {table1.c.myid}, {table1.c.name} FROM mytable")
self.assert_compile(
stmt, "SELECT mytable.myid, mytable.name FROM mytable"
)
def test_column_interpolation_labeled(self):
# Labels are not supported inside tstring as they're ambiguous
# (should they render with AS in all contexts?)
label1 = table1.c.myid.label("label1")
label2 = table1.c.name.label("label2")
with expect_raises_message(
exc.CompileError,
"Using label\\(\\) directly inside tstring is not supported",
):
tstring(t"SELECT {label1}, {label2} FROM mytable").compile()
def test_arithmetic_expression(self):
# Python arithmetic is evaluated before being passed to tstring
a = 1
stmt = tstring(t"SELECT {a + 7}")
self.assert_compile(
stmt, "SELECT :param_1", checkparams={"param_1": 8}
)
def test_embed_tstring_as_select_criteria(self):
user_id = 123
stmt = select(table1).where(tstring(t"{table1.c.myid} = {user_id}"))
self.assert_compile(
stmt,
"SELECT mytable.myid, mytable.name, mytable.description FROM "
"mytable WHERE mytable.myid = :param_1",
checkparams={"param_1": 123},
)
def test_embed_tstring_as_fromclause(self):
status = "some status"
stmt = select(column("x")).select_from(
tstring(
t"foobar left outer join lala on foobar.foo = lala.foo "
t"AND foobar.status = {status}"
)
)
self.assert_compile(
stmt,
"SELECT x FROM foobar left outer join "
"lala on foobar.foo = lala.foo AND foobar.status = :param_1",
checkparams={"param_1": "some status"},
)
def test_and_operator(self):
stmt = tstring(t"1 = 1") & tstring(t"2 = 2")
self.assert_compile(stmt, "1 = 1 AND 2 = 2")
def test_multiple_literals(self):
a, b, c, d = 1, 2, 3, 4
stmt = tstring(t"SELECT {a}, {b}, {c}, {d}")
self.assert_compile(
stmt,
"SELECT :param_1, :param_2, :param_3, :param_4",
checkparams={
"param_1": 1,
"param_2": 2,
"param_3": 3,
"param_4": 4,
},
)
def test_nested_tstring_execution(self):
inner = tstring(t"(SELECT {'some value'} AS anon_1)")
self.assert_compile(
tstring(t"select {inner}"),
"select (SELECT :param_1 AS anon_1)",
checkparams={"param_1": "some value"},
)
def test_nested_scalar_subquery_execution(self):
inner = select(literal("some value")).scalar_subquery()
self.assert_compile(
tstring(t"select {inner}"),
"select (SELECT :param_1 AS anon_1)",
checkparams={"param_1": "some value"},
)
def test_nested_subquery_execution(self):
inner = select(literal("some value")).subquery()
self.assert_compile(
tstring(t"select * from {inner}"),
"select * from (SELECT :param_1 AS anon_2) AS anon_1",
checkparams={"param_1": "some value"},
)
class ColumnsTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = "default"
def _assert_columns(self, stmt, columns):
"""Assert that stmt.selected_columns matches the given columns.
Also verifies that the result map structure matches what we'd get
from a regular select() statement with the same columns.
"""
# Check that selected_columns matches
eq_(
[c.name for c in stmt.selected_columns],
[c.name for c in columns],
)
for stmt_col, expected_col in zip(stmt.selected_columns, columns):
eq_(stmt_col.type._type_affinity, expected_col.type._type_affinity)
# Verify result map structure matches what select() would produce
stmt_compiled = stmt.compile()
select_compiled = select(*columns).compile()
stmt_map = stmt_compiled._create_result_map()
select_map = select_compiled._create_result_map()
# Compare result map structure using recursive comparison
eq_(list(stmt_map.keys()), list(select_map.keys()))
for key in stmt_map:
stmt_entry = stmt_map[key]
select_entry = select_map[key]
# Use recursive comparison for the entire entry tuple
assert self._compare_recursive(
stmt_entry, select_entry
), f"Result map entries differ:\n {stmt_entry}\n {select_entry}"
def _compare_recursive(self, left, right):
if isinstance(left, ColumnClause) and isinstance(right, ColumnClause):
return (
left.name == right.name
and left.type._type_affinity == right.type._type_affinity
)
elif isinstance(left, TypeEngine) and isinstance(right, TypeEngine):
return left._type_affinity == right._type_affinity
elif isinstance(left, (tuple, list)) and isinstance(
right, (tuple, list)
):
return all(
self._compare_recursive(l, r)
for l, r in zip_longest(left, right)
)
else:
return left == right
def test_columns_positional(self):
cols = [column("id", Integer), column("name", String)]
stmt = tstring(t"SELECT id, name FROM users").columns(*cols)
self.assert_compile(stmt, "SELECT id, name FROM users")
self._assert_columns(stmt, cols)
def test_columns_keyword(self):
stmt = tstring(t"SELECT id, name FROM users").columns(
id=Integer, name=String
)
self.assert_compile(stmt, "SELECT id, name FROM users")
cols = [column("id", Integer), column("name", String)]
self._assert_columns(stmt, cols)
def test_columns_mixed(self):
cols = [
column("id", Integer),
column("name", String),
column("age", Integer),
]
stmt = tstring(t"SELECT id, name, age FROM users").columns(
cols[0], name=String, age=Integer
)
self.assert_compile(stmt, "SELECT id, name, age FROM users")
self._assert_columns(stmt, cols)
def test_columns_subquery(self):
stmt = (
tstring(t"SELECT id, name FROM users")
.columns(column("id", Integer), column("name", String))
.subquery("st")
)
outer = select(table1).select_from(
table1.join(stmt, table1.c.name == stmt.c.name)
)
self.assert_compile(
outer,
"SELECT mytable.myid, mytable.name, mytable.description FROM "
"mytable JOIN (SELECT id, name FROM users) AS st ON "
"mytable.name = st.name",
)
class ExecutionTest(fixtures.TestBase):
__backend__ = True
def test_basic_execution(self, connection):
a = 1
b = 2
result = connection.execute(tstring(t"select {a + 7}, {b}"))
eq_(result.all(), [(8, 2)])
@testing.requires.json_type
def test_json_literal_execution(self, connection):
some_json = {"foo": "bar"}
stmt = tstring(t"select {literal(some_json, JSON)}").columns(
column("jj", JSON)
)
result = connection.execute(stmt)
row = result.scalar()
eq_(row, {"foo": "bar"})
@testing.requires.json_type
def test_statement_caching(self, connection):
"""Test that tstring statements are properly cached."""
some_json = {"foo": "bar"}
stmt1 = tstring(t"select {literal(some_json, JSON)}").columns(
column("jj", JSON)
)
result1 = connection.execute(stmt1)
eq_(result1.scalar(), {"foo": "bar"})
# Execute same structure with different value
some_json = {"foo": "newbar", "bat": "hoho"}
stmt2 = tstring(t"select {literal(some_json, JSON)}").columns(
column("jj", JSON)
)
result2 = connection.execute(stmt2)
# Should hit cache
if hasattr(result2.context, "cache_hit"):
eq_(result2.context.cache_hit, CacheStats.CACHE_HIT)
eq_(result2.scalar(), {"foo": "newbar", "bat": "hoho"})
def test_nested_scalar_subquery_execution(self, connection):
inner = select(literal("some value")).scalar_subquery()
result = connection.execute(tstring(t"select {inner}"))
eq_(result.all(), [("some value",)])
def test_nested_subquery_execution(self, connection):
inner = select(literal("some value")).subquery()
result = connection.execute(tstring(t"select * from {inner}"))
eq_(result.all(), [("some value",)])
def test_multiple_values(self, connection):
values = [1, 2, 3, 4, 5]
result = connection.execute(
tstring(
t"select {values[0]}, {values[1]}, {values[2]}, "
t"{values[3]}, {values[4]}"
)
)
eq_(result.all(), [(1, 2, 3, 4, 5)])
class IntegrationTest(fixtures.TablesTest):
__backend__ = True
@classmethod
def define_tables(cls, metadata):
from sqlalchemy import Column
from sqlalchemy import Table
Table(
"users",
metadata,
Column("id", Integer, primary_key=True),
Column("name", String(50)),
)
@classmethod
def insert_data(cls, connection):
connection.execute(
cls.tables.users.insert(),
[
{"id": 1, "name": "alice"},
{"id": 2, "name": "bob"},
{"id": 3, "name": "charlie"},
],
)
def test_select_from_real_table(self, connection):
user_id = 2
stmt = tstring(t"SELECT * FROM users WHERE id = {user_id}").columns(
column("id", Integer), column("name", String)
)
result = connection.execute(stmt)
row = result.one()
eq_(row.id, 2)
eq_(row.name, "bob")
def test_where_clause_with_real_table(self, connection):
users = self.tables.users
name_filter = "alice"
stmt = select(users).where(
tstring(t"{users.c.name} = {literal(name_filter)}")
)
result = connection.execute(stmt)
row = result.one()
eq_(row.id, 1)
eq_(row.name, "alice")
def test_complex_query(self, connection):
min_id = 1
max_id = 2
stmt = tstring(
t"SELECT id, name FROM users WHERE id >= {min_id} "
t"AND id <= {max_id}"
).columns(column("id", Integer), column("name", String))
result = connection.execute(stmt)
rows = result.all()
eq_(len(rows), 2)
eq_(rows[0].name, "alice")
eq_(rows[1].name, "bob")
class CacheKeyTest(fixtures.CacheKeyFixture, fixtures.TestBase):
"""Test cache key generation for tstring constructs."""
@fixtures.CacheKeySuite.run_suite_tests
def test_tstring_cache_key(self):
def stmt1():
# Basic tstring with literal
a = 5
return tstring(t"SELECT {a}")
def stmt2():
# Different structure - two literals
a = 5
b = 10
return tstring(t"SELECT {a}, {b}")
def stmt3():
# With column reference
return tstring(t"SELECT {table1.c.myid}")
def stmt4():
# Different column - different cache key
return tstring(t"SELECT {table1.c.name}")
def stmt5():
# With .columns()
a = 5
return tstring(t"SELECT {a}").columns(column("val", Integer))
def stmt6():
# String literal passthrough
return tstring(t"SELECT * FROM users")
def stmt7():
# Different string literal
return tstring(t"SELECT id FROM users")
def stmt8():
# With SQLAlchemy scalar subquery
return tstring(t"SELECT {select(literal(1)).scalar_subquery()}")
def stmt9():
# Mixed: text and literal
user_id = 42
return tstring(t"SELECT * FROM users WHERE id = {user_id}")
def stmt10():
# Mixed: text and column
return tstring(t"SELECT * FROM users WHERE id = {table1.c.myid}")
return lambda: [
stmt1(),
stmt2(),
stmt3(),
stmt4(),
stmt5(),
stmt6(),
stmt7(),
stmt8(),
stmt9(),
stmt10(),
]