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_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_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_one_to_many(self): class A(self.base_cls): name = Column(String) b_list = relationship('B') @hybrid_property def just_b_list(self): return self.b_list 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']}), ) query = ( ConstructQuery({ 'a_name': A.name, 'b_names': map_(apply_(capitalize, [B.name]), A.just_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_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_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']}], )
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_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_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'] }], )
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_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_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