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
|
import testbase
import sys
from sqlalchemy import *
from testlib import *
class CaseTest(PersistTest):
def setUpAll(self):
metadata = MetaData(testbase.db)
global info_table
info_table = Table('infos', metadata,
Column('pk', Integer, primary_key=True),
Column('info', String(30)))
info_table.create()
info_table.insert().execute(
{'pk':1, 'info':'pk_1_data'},
{'pk':2, 'info':'pk_2_data'},
{'pk':3, 'info':'pk_3_data'},
{'pk':4, 'info':'pk_4_data'},
{'pk':5, 'info':'pk_5_data'},
{'pk':6, 'info':'pk_6_data'})
def tearDownAll(self):
info_table.drop()
@testing.fails_on('maxdb')
def testcase(self):
inner = select([case([
[info_table.c.pk < 3,
literal('lessthan3', type_=String)],
[and_(info_table.c.pk >= 3, info_table.c.pk < 7),
literal('gt3', type_=String)]]).label('x'),
info_table.c.pk, info_table.c.info],
from_obj=[info_table]).alias('q_inner')
inner_result = inner.execute().fetchall()
# Outputs:
# lessthan3 1 pk_1_data
# lessthan3 2 pk_2_data
# gt3 3 pk_3_data
# gt3 4 pk_4_data
# gt3 5 pk_5_data
# gt3 6 pk_6_data
assert inner_result == [
('lessthan3', 1, 'pk_1_data'),
('lessthan3', 2, 'pk_2_data'),
('gt3', 3, 'pk_3_data'),
('gt3', 4, 'pk_4_data'),
('gt3', 5, 'pk_5_data'),
('gt3', 6, 'pk_6_data')
]
outer = select([inner])
outer_result = outer.execute().fetchall()
assert outer_result == [
('lessthan3', 1, 'pk_1_data'),
('lessthan3', 2, 'pk_2_data'),
('gt3', 3, 'pk_3_data'),
('gt3', 4, 'pk_4_data'),
('gt3', 5, 'pk_5_data'),
('gt3', 6, 'pk_6_data')
]
w_else = select([case([
[info_table.c.pk < 3,
literal(3, type_=Integer)],
[and_(info_table.c.pk >= 3, info_table.c.pk < 6),
literal(6, type_=Integer)]],
else_ = 0).label('x'),
info_table.c.pk, info_table.c.info],
from_obj=[info_table]).alias('q_inner')
else_result = w_else.execute().fetchall()
assert else_result == [
(3, 1, 'pk_1_data'),
(3, 2, 'pk_2_data'),
(6, 3, 'pk_3_data'),
(6, 4, 'pk_4_data'),
(6, 5, 'pk_5_data'),
(0, 6, 'pk_6_data')
]
if __name__ == "__main__":
testbase.main()
|
