1
from sqlalchemy import Table, Column, String, func, MetaData, select, TypeDecorator, cast
2
from sqlalchemy.testing import fixtures, AssertsCompiledSQL
3
from sqlalchemy import testing
4
from sqlalchemy.testing import eq_
7
class _ExprFixture(object):
9
class MyString(String):
10
def bind_expression(self, bindvalue):
11
return func.lower(bindvalue)
13
def column_expression(self, col):
14
return func.lower(col)
18
MetaData(), Column('x', String), Column('y', MyString)
22
class SelectTest(_ExprFixture, fixtures.TestBase, AssertsCompiledSQL):
23
__dialect__ = 'default'
25
def test_select_cols(self):
26
table = self._fixture()
30
"SELECT test_table.x, lower(test_table.y) AS y FROM test_table"
33
def test_anonymous_expr(self):
34
table = self._fixture()
36
select([cast(table.c.y, String)]),
37
"SELECT CAST(test_table.y AS VARCHAR) AS anon_1 FROM test_table"
40
def test_select_cols_use_labels(self):
41
table = self._fixture()
44
select([table]).apply_labels(),
45
"SELECT test_table.x AS test_table_x, "
46
"lower(test_table.y) AS test_table_y FROM test_table"
49
def test_select_cols_use_labels_result_map_targeting(self):
50
table = self._fixture()
52
compiled = select([table]).apply_labels().compile()
53
assert table.c.y in compiled.result_map['test_table_y'][1]
54
assert table.c.x in compiled.result_map['test_table_x'][1]
56
# the lower() function goes into the result_map, we don't really
57
# need this but it's fine
59
compiled.result_map['test_table_y'][1][2],
62
# then the original column gets put in there as well.
63
# it's not important that it's the last value.
65
compiled.result_map['test_table_y'][1][-1],
69
def test_insert_binds(self):
70
table = self._fixture()
74
"INSERT INTO test_table (x, y) VALUES (:x, lower(:y))"
78
table.insert().values(y="hi"),
79
"INSERT INTO test_table (y) VALUES (lower(:y))"
82
def test_select_binds(self):
83
table = self._fixture()
85
select([table]).where(table.c.y == "hi"),
86
"SELECT test_table.x, lower(test_table.y) AS y FROM "
87
"test_table WHERE test_table.y = lower(:y_1)"
90
class DerivedTest(_ExprFixture, fixtures.TestBase, AssertsCompiledSQL):
91
__dialect__ = 'default'
93
def test_select_from_select(self):
94
table = self._fixture()
96
table.select().select(),
97
"SELECT x, lower(y) AS y FROM (SELECT test_table.x "
98
"AS x, test_table.y AS y FROM test_table)"
101
def test_select_from_alias(self):
102
table = self._fixture()
104
table.select().alias().select(),
105
"SELECT anon_1.x, lower(anon_1.y) AS y FROM (SELECT "
106
"test_table.x AS x, test_table.y AS y "
107
"FROM test_table) AS anon_1"
110
def test_select_from_aliased_join(self):
111
table = self._fixture()
112
s1 = table.select().alias()
113
s2 = table.select().alias()
114
j = s1.join(s2, s1.c.x == s2.c.x)
116
self.assert_compile(s3,
117
"SELECT anon_1.x, lower(anon_1.y) AS y, anon_2.x, "
118
"lower(anon_2.y) AS y "
119
"FROM (SELECT test_table.x AS x, test_table.y AS y "
120
"FROM test_table) AS anon_1 JOIN (SELECT "
121
"test_table.x AS x, test_table.y AS y "
122
"FROM test_table) AS anon_2 ON anon_1.x = anon_2.x"
125
class RoundTripTestBase(object):
126
def test_round_trip(self):
128
self.tables.test_table.insert(),
129
{"x": "X1", "y": "Y1"},
130
{"x": "X2", "y": "Y2"},
131
{"x": "X3", "y": "Y3"},
134
# test insert coercion alone
137
"select * from test_table order by y").fetchall(),
145
# conversion back to upper
148
select([self.tables.test_table]).\
149
order_by(self.tables.test_table.c.y)
158
def test_targeting_no_labels(self):
160
self.tables.test_table.insert(),
161
{"x": "X1", "y": "Y1"},
163
row = testing.db.execute(select([self.tables.test_table])).first()
165
row[self.tables.test_table.c.y],
169
def test_targeting_by_string(self):
171
self.tables.test_table.insert(),
172
{"x": "X1", "y": "Y1"},
174
row = testing.db.execute(select([self.tables.test_table])).first()
180
def test_targeting_apply_labels(self):
182
self.tables.test_table.insert(),
183
{"x": "X1", "y": "Y1"},
185
row = testing.db.execute(select([self.tables.test_table]).
186
apply_labels()).first()
188
row[self.tables.test_table.c.y],
192
def test_targeting_individual_labels(self):
194
self.tables.test_table.insert(),
195
{"x": "X1", "y": "Y1"},
197
row = testing.db.execute(select([
198
self.tables.test_table.c.x.label('xbar'),
199
self.tables.test_table.c.y.label('ybar')
202
row[self.tables.test_table.c.y],
206
class StringRoundTripTest(fixtures.TablesTest, RoundTripTestBase):
208
def define_tables(cls, metadata):
209
class MyString(String):
210
def bind_expression(self, bindvalue):
211
return func.lower(bindvalue)
213
def column_expression(self, col):
214
return func.upper(col)
219
Column('x', String(50)),
220
Column('y', MyString(50)
225
class TypeDecRoundTripTest(fixtures.TablesTest, RoundTripTestBase):
227
def define_tables(cls, metadata):
228
class MyString(TypeDecorator):
230
def bind_expression(self, bindvalue):
231
return func.lower(bindvalue)
233
def column_expression(self, col):
234
return func.upper(col)
239
Column('x', String(50)),
240
Column('y', MyString(50)
244
class ReturningTest(fixtures.TablesTest):
245
__requires__ = 'returning',
248
def define_tables(cls, metadata):
249
class MyString(String):
250
def column_expression(self, col):
251
return func.lower(col)
255
metadata, Column('x', String(50)),
256
Column('y', MyString(50), server_default="YVALUE")
259
@testing.provide_metadata
260
def test_insert_returning(self):
261
table = self.tables.test_table
262
result = testing.db.execute(
263
table.insert().returning(table.c.y),