def test_many_to_many(self):
ab_table = Table('a_b', self.base_cls.metadata,
Column('a_id', Integer, ForeignKey('a.id')),
Column('b_id', Integer, ForeignKey('b.id')))
class A(self.base_cls):
name = Column(String)
b_list = relationship('B', secondary=ab_table)
class B(self.base_cls):
name = Column(String)
a_list = relationship('A', secondary=ab_table)
session = self.init()
a1, a2, a3, a4 = A(name='a1'), A(name='a2'), A(name='a3'), A(name='a4')
b1, b2, b3, b4 = B(name='b1'), B(name='b2'), B(name='b3'), B(name='b4')
a1.b_list = [b2, b3, b4]
a2.b_list = [b1, b3, b4]
a3.b_list = [b1, b2, b4]
a4.b_list = [b1, b2, b3]
session.add_all([a1, a2, a3, a4])
session.commit()
q1 = (ConstructQuery({
'a_name':
A.name,
'b_names':
map_(apply_(capitalize, [B.name]), A.b_list),
}).with_session(session.registry()).order_by(A.name))
self.assertEqual(
tuple((obj.a_name, set(obj.b_names)) for obj in q1.all()), (
('a1', {'B2', 'B3', 'B4'}),
('a2', {'B1', 'B3', 'B4'}),
('a3', {'B1', 'B2', 'B4'}),
('a4', {'B1', 'B2', 'B3'}),
))
q2 = (ConstructQuery({
'b_name':
B.name,
'a_names':
map_(apply_(capitalize, [A.name]), B.a_list),
}).with_session(session.registry()).order_by(B.name))
self.assertEqual(
tuple((obj.b_name, set(obj.a_names)) for obj in q2.all()),
(
('b1', {'A2', 'A3', 'A4'}),
('b2', {'A1', 'A3', 'A4'}),
('b3', {'A1', 'A2', 'A4'}),
('b4', {'A1', 'A2', 'A3'}),
),
)
def test_with_scoped_session(self):
class A(self.base_cls):
name = Column(String)
session = self.init()
session.add_all([A(name='a1'), A(name='a2'), A(name='a3')])
session.commit()
q1 = ConstructQuery({'name': A.name}, session)
self.assertEqual({obj.name for obj in q1.all()}, {'a1', 'a2', 'a3'})
q2 = ConstructQuery({'name': A.name})
with self.assertRaises(AttributeError):
q2.all()
def test_query_entities_modification(self):
class A(self.base_cls):
name = Column(String)
session = self.init()
session.add_all([A(name='a1'), A(name='a2'), A(name='a3')])
session.commit()
count = ConstructQuery({'name': A.name}, session).count()
self.assertEqual(count, 3)
with self.assertRaises(NotImplementedError):
ConstructQuery({'id': A.id}).add_columns(A.name)
with self.assertRaises(NotImplementedError):
ConstructQuery({'id': A.id}).add_entity(A)
def test_with_define(self):
class A(self.base_cls):
name = Column(String)
b_id = Column(Integer, ForeignKey('b.id'))
b = relationship('B')
class B(self.base_cls):
name = Column(String)
@define
def full_name(a, b):
def body(a_name, b_name):
return ' '.join((a_name.capitalize(), b_name.capitalize()))
return body, [a.name, b.name]
session = self.init()
b1, b2, b3 = B(name='b1'), B(name='b2'), B(name='b3')
session.add_all([
A(name='a1', b=b1),
A(name='a2', b=b1),
A(name='a3', b=b1),
A(name='a4', b=b2),
A(name='a5', b=b2),
A(name='a6', b=b2),
A(name='a7', b=b3),
A(name='a8', b=b3),
A(name='a9', b=b3),
])
session.commit()
query = (ConstructQuery({
'full_name': full_name.defn(A, A.b),
}).with_session(session.registry()).order_by(A.name))
self.assertEqual(
tuple(dict(obj) for obj in query.all()),
({
'full_name': 'A1 B1'
}, {
'full_name': 'A2 B1'
}, {
'full_name': 'A3 B1'
}, {
'full_name': 'A4 B2'
}, {
'full_name': 'A5 B2'
}, {
'full_name': 'A6 B2'
}, {
'full_name': 'A7 B3'
}, {
'full_name': 'A8 B3'
}, {
'full_name': 'A9 B3'
}),
)
def test_bound_to_query_reference(self):
class A(self.base_cls):
name = Column(String)
b_list = relationship('B')
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
ref = Column(Integer)
class C(self.base_cls):
name = Column(String)
ref = Column(Integer)
session = self.init()
session.add_all([
A(name='a1', b_list=[B(name='b1', ref=-1),
B(name='b2', ref=2)]),
A(name='a2', b_list=[B(name='b3', ref=-3),
B(name='b4', ref=4)]),
C(name='c1', ref=1),
C(name='c2', ref=-1),
C(name='c3', ref=2),
C(name='c4', ref=-2),
C(name='c5', ref=3),
C(name='c6', ref=-3),
C(name='c7', ref=4),
C(name='c8', ref=-4),
])
session.commit()
sq1 = (RelativeCollectionSubQuery.from_relation(A.b_list).order_by(
B.name.asc()))
sq2 = (RelativeCollectionSubQuery(bind(func.abs(B.ref), sq1),
func.abs(C.ref)).order_by(
C.name.asc()))
query = (ConstructQuery({
'a_name': A.name,
'c_names': map_(map_(C.name, sq2), sq1),
}).order_by(A.name.asc()).with_session(session.registry()))
self.assertEqual(
[dict(obj) for obj in query.all()],
[
{
'a_name': 'a1',
'c_names': [['c1', 'c2'], ['c3', 'c4']]
},
{
'a_name': 'a2',
'c_names': [['c5', 'c6'], ['c7', 'c8']]
},
],
)
def test_with_custom_queries(self):
class A(self.base_cls):
name = Column(String)
class B(self.base_cls):
name = Column(String)
session = self.init()
session.add_all([
A(name='a1'),
A(name='a2'),
A(name='a3'),
B(name='b1'),
B(name='b2'),
B(name='b3'),
])
session.commit()
sq1 = CollectionSubQuery(B).order_by(B.name.desc())
q1 = (ConstructQuery({
'a_name': A.name,
'b_list': map_(B.name, sq1)
}, session).order_by(A.name))
self.assertEqual({(obj.a_name, tuple(obj.b_list))
for obj in q1.all()}, {('a1', ('b3', 'b2', 'b1')),
('a2', ('b3', 'b2', 'b1')),
('a3', ('b3', 'b2', 'b1'))})
sq2 = ObjectSubQuery(B).order_by(B.name.desc())
q2 = (ConstructQuery({
'a_name': A.name,
'b_name': get_(B.name, sq2)
}, session).order_by(A.name))
self.assertEqual({(obj.a_name, obj.b_name)
for obj in q2.all()}, {('a1', 'b3'), ('a2', 'b3'),
('a3', 'b3')})
def test_with_scoped_session(self):
class A(self.base_cls):
name = Column(String)
session = self.init()
session.add_all([A(name='a1'), A(name='a2'), A(name='a3')])
session.commit()
q1 = ConstructQuery({'name': A.name}, session)
self.assertEqual({obj.name for obj in q1.all()}, {'a1', 'a2', 'a3'})
q2 = ConstructQuery({'name': A.name})
with self.assertRaises(AttributeError):
q2.all()
def test_one_to_many(self):
class A(self.base_cls):
name = Column(String)
b_list = relationship('B')
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
session = self.init()
session.add_all([
A(name='a1', b_list=[B(name='b1'),
B(name='b2'),
B(name='b3')]),
A(name='a2', b_list=[B(name='b4'),
B(name='b5'),
B(name='b6')]),
A(name='a3', b_list=[B(name='b7'),
B(name='b8'),
B(name='b9')]),
])
session.commit()
query = (ConstructQuery({
'a_name':
A.name,
'b_names':
map_(apply_(capitalize, [B.name]), A.b_list),
}).with_session(session.registry()))
self.assertEqual(
tuple(dict(obj) for obj in query.all()),
({
'a_name': 'a1',
'b_names': ['B1', 'B2', 'B3']
}, {
'a_name': 'a2',
'b_names': ['B4', 'B5', 'B6']
}, {
'a_name': 'a3',
'b_names': ['B7', 'B8', 'B9']
}),
)
def test_non_empty_in_op_in_relative_subqueries(self):
in_op = ColumnOperators.in_
class EmptyInOpError(Exception):
pass
def wrapper(self, values):
if not values:
raise EmptyInOpError
return in_op(self, values)
patcher = patch.object(ColumnOperators, 'in_', wrapper)
class A(self.base_cls):
value = Column(String)
class B(self.base_cls):
value = Column(String)
session = self.init()
session.add_all([A(), A(), A()])
session.commit()
obj_sq = RelativeObjectSubQuery(A.value, B.value)
obj_query = ConstructQuery({
'a_id': A.id,
'b_id': get_(B.id, obj_sq)
}, session)
with patcher:
obj_query.all()
list_sq = RelativeCollectionSubQuery(A.value, B.value)
list_query = ConstructQuery({
'a_id': A.id,
'b_id': map_(B.id, list_sq)
}, session)
with patcher:
list_query.all()
def test_one_to_one(self):
class A(self.base_cls):
name = Column(String)
b = relationship('B', uselist=False)
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
session = self.init()
session.add_all([
A(name='a1', b=B(name='b1')),
A(name='a2'),
B(name='b2'),
A(name='a3', b=B(name='b3')),
])
session.commit()
query = (ConstructQuery({
'a_name':
A.name,
'b_name':
get_(if_(B.id, apply_(capitalize, [B.name]), '~'), A.b),
}).with_session(session.registry()))
self.assertEqual(
tuple(dict(obj) for obj in query.all()),
({
'a_name': 'a1',
'b_name': 'B1'
}, {
'a_name': 'a2',
'b_name': '~'
}, {
'a_name': 'a3',
'b_name': 'B3'
}),
)
def test_bound_to_query_expressions(self):
class A(self.base_cls):
name = Column(String)
b_list = relationship('B')
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
session = self.init()
session.add_all([
A(name='a1', b_list=[B(name='b1')]),
A(name='a2', b_list=[B(name='b4'), B(name='b5')]),
A(name='a3', b_list=[B(name='b7'),
B(name='b8'),
B(name='b9')]),
])
session.commit()
sq = (RelativeObjectSubQuery.from_relation(A.b_list).group_by(B.a_id))
query = (ConstructQuery({
'a_name': A.name,
'b_count': get_(bind(func.count(), sq), sq),
}).order_by(A.name.asc()).with_session(session.registry()))
self.assertEqual(
[dict(obj) for obj in query.all()],
[{
'a_name': 'a1',
'b_count': 1
}, {
'a_name': 'a2',
'b_count': 2
}, {
'a_name': 'a3',
'b_count': 3
}],
)
def test_non_empty_in_op_in_relative_subqueries(self):
in_op = ColumnOperators.in_
class EmptyInOpError(Exception):
pass
def wrapper(self, values):
if not values:
raise EmptyInOpError
return in_op(self, values)
patcher = patch.object(ColumnOperators, 'in_', wrapper)
class A(self.base_cls):
value = Column(String)
class B(self.base_cls):
value = Column(String)
session = self.init()
session.add_all([A(), A(), A()])
session.commit()
obj_sq = RelativeObjectSubQuery(A.value, B.value)
obj_query = ConstructQuery({'a_id': A.id,
'b_id': get_(B.id, obj_sq)},
session)
with patcher:
obj_query.all()
list_sq = RelativeCollectionSubQuery(A.value, B.value)
list_query = ConstructQuery({'a_id': A.id,
'b_id': map_(B.id, list_sq)},
session)
with patcher:
list_query.all()
def test_nested(self):
"""
A <- B -> C -> D <- E
"""
class A(self.base_cls):
name = Column(String)
class B(self.base_cls):
name = Column(String)
a_id = Column('a_id', Integer, ForeignKey('a.id'))
a = relationship('A', backref='b_list')
c_id = Column('c_id', Integer, ForeignKey('c.id'))
c = relationship('C', backref='b_list')
class C(self.base_cls):
name = Column(String)
d_id = Column('d_id', Integer, ForeignKey('d.id'))
d = relationship('D', backref='c_list')
class D(self.base_cls):
name = Column(String)
class E(self.base_cls):
name = Column(String)
d_id = Column('d_id', Integer, ForeignKey('d.id'))
d = relationship('D', backref='e_list')
session = self.init()
a1, a2, a3 = A(name='a1'), A(name='a2'), A(name='a3')
d1 = D(name='d1',
c_list=[
C(name='c1',
b_list=[
B(name='b1'),
B(name='b2', a=a2),
B(name='b3', a=a3)
]),
C(name='c2',
b_list=[
B(name='b4', a=a1),
B(name='b5'),
B(name='b6', a=a3)
]),
C(name='c3',
b_list=[
B(name='b7', a=a1),
B(name='b8', a=a2),
B(name='b9')
])
],
e_list=[E(name='e1'), E(name='e2'),
E(name='e3')])
session.add_all([a1, a2, a3, d1])
session.commit()
# A <- B -> C
r1 = tuple(
dict(obj) for obj in ConstructQuery(
{
'a_name': A.name,
'b_names': map_(B.name, A.b_list),
'c_names': map_(get_(C.name, B.c), A.b_list)
}).with_session(session.registry()).order_by(A.name).all())
self.assertEqual(r1, (
{
'a_name': 'a1',
'b_names': ['b4', 'b7'],
'c_names': ['c2', 'c3']
},
{
'a_name': 'a2',
'b_names': ['b2', 'b8'],
'c_names': ['c1', 'c3']
},
{
'a_name': 'a3',
'b_names': ['b3', 'b6'],
'c_names': ['c1', 'c2']
},
))
# B -> C -> D
r2 = tuple(
dict(obj)
for obj in ConstructQuery({
'b_name': B.name,
'c_name': get_(C.name, B.c),
'd_name': get_(get_(D.name, C.d), B.c),
}).with_session(session.registry()).order_by(B.name).all())
self.assertEqual(r2, (
{
'b_name': 'b1',
'c_name': 'c1',
'd_name': 'd1'
},
{
'b_name': 'b2',
'c_name': 'c1',
'd_name': 'd1'
},
{
'b_name': 'b3',
'c_name': 'c1',
'd_name': 'd1'
},
{
'b_name': 'b4',
'c_name': 'c2',
'd_name': 'd1'
},
{
'b_name': 'b5',
'c_name': 'c2',
'd_name': 'd1'
},
{
'b_name': 'b6',
'c_name': 'c2',
'd_name': 'd1'
},
{
'b_name': 'b7',
'c_name': 'c3',
'd_name': 'd1'
},
{
'b_name': 'b8',
'c_name': 'c3',
'd_name': 'd1'
},
{
'b_name': 'b9',
'c_name': 'c3',
'd_name': 'd1'
},
))
# C -> D <- E
r3 = tuple(
dict(obj) for obj in ConstructQuery(
{
'c_name': C.name,
'd_name': get_(D.name, C.d),
'e_names': get_(map_(E.name, D.e_list), C.d),
}).with_session(session.registry()).order_by(C.name).all())
self.assertEqual(r3, (
{
'c_name': 'c1',
'd_name': 'd1',
'e_names': ['e1', 'e2', 'e3']
},
{
'c_name': 'c2',
'd_name': 'd1',
'e_names': ['e1', 'e2', 'e3']
},
{
'c_name': 'c3',
'd_name': 'd1',
'e_names': ['e1', 'e2', 'e3']
},
))
# D <- C <- B
r4 = dict(
ConstructQuery({
'd_name': D.name,
'c_names': map_(C.name, D.c_list),
'b_names': map_(map_(B.name, C.b_list), D.c_list),
}).with_session(session.registry()).order_by(D.name).one())
self.assertEqual(r4['d_name'], 'd1')
self.assertEqual(set(r4['c_names']), {'c1', 'c2', 'c3'})
self.assertEqual(
set(map(frozenset, r4['b_names'])), {
frozenset({'b1', 'b2', 'b3'}),
frozenset({'b4', 'b5', 'b6'}),
frozenset({'b7', 'b8', 'b9'}),
})
def test_performance(self):
class A(self.base_cls):
name = Column(String)
b_list = relationship('B')
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
session = self.init()
session.add_all([
A(name='a_%d' % i,
b_list=[B(name='b_%d_%d' % (i, j)) for j in range(10)])
for i in _range(20)
])
session.commit()
session.remove()
@define
def b_name(a, b):
def body(a_id, a_name, b_id, b_name):
return '%d - %s - %d - %s' % (a_id, a_name, b_id, b_name)
return body, [a.id, a.name, b.id, b.name]
tuples_query = session.query(A.id, A.name)
tuples_subquery = session.query(B.id, B.a_id, B.name)
def perform_tuples_query():
a_rows = tuples_query.all()
_tuples_subquery = (tuples_subquery.filter(
B.a_id.in_({a_row.id
for a_row in a_rows})))
b_rows_map = collections.defaultdict(list)
for row in _tuples_subquery.all():
b_rows_map[row.a_id].append(row)
return [{
'a_name':
a_row.name,
'b_names': [
b_name.func(a_row.id, a_row.name, b_row.id, b_row.name)
for b_row in b_rows_map[a_row.id]
]
} for a_row in a_rows]
construct_query = (ConstructQuery(
{
'a_name': A.name,
'b_names': map_(b_name.defn(A, B), A.b_list),
}, session))
def perform_construct_query():
return construct_query.all()
def perform_objects_query():
session.remove()
query = session.query(A).options(subqueryload(A.b_list))
return [{
'a_name': a.name,
'b_names': [b_name(a, b) for b in a.b_list]
} for a in query.all()]
def do(impl, count):
# warm-up
_res = [impl() for _ in _range(count)]
profile = Profile()
profile.enable()
res = [impl() for _ in _range(count)]
profile.disable()
out = StringIO()
stats = Stats(profile, stream=out)
stats.strip_dirs()
stats.sort_stats('calls').print_stats(10)
print(out.getvalue().lstrip())
out.close()
return _res, res
res = []
res.extend(do(perform_tuples_query, 1000))
res.extend(do(perform_construct_query, 1000))
res.extend(do(perform_objects_query, 1000))
1 / 0
def test_bound_to_query_entities(self):
class A(self.base_cls):
name = Column(String)
c_id = Column(Integer, ForeignKey('c.id'))
c = relationship('C')
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
c_id = Column(Integer, ForeignKey('c.id'))
a = relationship('A', backref='b_list')
c = relationship('C')
class C(self.base_cls):
name = Column(String)
session = self.init()
session.add_all([
A(name='a1',
c=C(name='c11'),
b_list=[
B(name='b1', c=C(name='c21')),
B(name='b2', c=C(name='c22')),
]),
A(name='a2',
c=C(name='c12'),
b_list=[
B(name='b3', c=C(name='c23')),
B(name='b4', c=C(name='c24')),
]),
A(name='a3',
c=C(name='c13'),
b_list=[
B(name='b5', c=C(name='c25')),
B(name='b6', c=C(name='c26')),
]),
])
session.commit()
@define
def concat_names(a, c1, b, c2):
def body(a_name, c1_name, b_name, c2_name):
return ' '.join((a_name, c1_name, b_name, c2_name))
return body, [a.name, c1.name, b.name, c2.name]
sq = (RelativeCollectionSubQuery.from_relation(A.b_list).outerjoin(
B.c).order_by(B.name.asc()))
query = (ConstructQuery({
'names':
map_(concat_names.defn(A, bind(C, None), B, C), sq),
}).outerjoin(A.c).order_by(A.name.asc()).with_session(
session.registry()))
self.assertEqual(
[dict(obj) for obj in query.all()],
[{
'names': ['a1 c11 b1 c21', 'a1 c11 b2 c22']
}, {
'names': ['a2 c12 b3 c23', 'a2 c12 b4 c24']
}, {
'names': ['a3 c13 b5 c25', 'a3 c13 b6 c26']
}],
)