def test_result(self): Data = self.classes.Data sess = Session() b1 = Bundle('b1', Data.d1, Data.d2) eq_( sess.query(b1).filter(b1.c.d1.between('d3d1', 'd5d1')).all(), [(('d3d1', 'd3d2'),), (('d4d1', 'd4d2'),), (('d5d1', 'd5d2'),)] )
def test_same_named_col_in_fetch(self): Data, Other = self.classes("Data", "Other") bundle = Bundle("pk", Data.id, Other.id) sess = Session() eq_( sess.query(bundle).filter( Data.id == Other.id).filter(Data.id < 3).all(), [((1, 1),), ((2, 2),)] )
def test_join_relationship(self): Data = self.classes.Data sess = Session() b1 = Bundle('b1', Data.d1, Data.d2) q = sess.query(b1).join(Data.others) self.assert_compile(q, "SELECT data.d1 AS data_d1, data.d2 AS data_d2 FROM data " "JOIN other ON data.id = other.data_id" )
def test_result(self): Data = self.classes.Data sess = Session() b1 = Bundle("b1", Data.d1, Data.d2) eq_( sess.query(b1).filter(b1.c.d1.between("d3d1", "d5d1")).all(), [(("d3d1", "d3d2"), ), (("d4d1", "d4d2"), ), (("d5d1", "d5d2"), )], )
def test_single_entity(self): Data = self.classes.Data sess = Session() b1 = Bundle('b1', Data.d1, Data.d2, single_entity=True) eq_( sess.query(b1).filter(b1.c.d1.between('d3d1', 'd5d1')).all(), [('d3d1', 'd3d2'), ('d4d1', 'd4d2'), ('d5d1', 'd5d2')] )
def test_single_entity(self): Data = self.classes.Data sess = Session() b1 = Bundle("b1", Data.d1, Data.d2, single_entity=True) eq_( sess.query(b1).filter(b1.c.d1.between("d3d1", "d5d1")).all(), [("d3d1", "d3d2"), ("d4d1", "d4d2"), ("d5d1", "d5d2")], )
def test_non_mapped_columns_single_entity(self): data_table = self.tables.data b1 = Bundle("b1", data_table.c.d1, data_table.c.d2, single_entity=True) sess = fixture_session() eq_( sess.query(b1).filter(b1.c.d1.between("d3d1", "d5d1")).all(), [("d3d1", "d3d2"), ("d4d1", "d4d2"), ("d5d1", "d5d2")], )
def test_labeled_cols_non_single_entity_legacy_query(self): Data = self.classes.Data sess = fixture_session() b1 = Bundle("b1", Data.d1.label("x"), Data.d2.label("y")) eq_( sess.query(b1).filter(b1.c.x.between("d3d1", "d5d1")).all(), [(("d3d1", "d3d2"),), (("d4d1", "d4d2"),), (("d5d1", "d5d2"),)], )
def test_filter_by(self): Data = self.classes.Data b1 = Bundle('b1', Data.id, Data.d1, Data.d2) sess = Session() self.assert_compile( sess.query(b1).filter_by(d1='d1'), "SELECT data.id AS data_id, data.d1 AS data_d1, " "data.d2 AS data_d2 FROM data WHERE data.d1 = :d1_1")
def test_labeled_cols_as_scalars_future(self): Data = self.classes.Data sess = fixture_session() b1 = Bundle("b1", Data.d1.label("x"), Data.d2.label("y")) stmt = select(b1).filter(b1.c.x.between("d3d1", "d5d1")) eq_( sess.scalars(stmt).all(), [("d3d1", "d3d2"), ("d4d1", "d4d2"), ("d5d1", "d5d2")], )
def test_single_entity_future(self): Data = self.classes.Data sess = Session(testing.db, future=True) b1 = Bundle("b1", Data.d1, Data.d2, single_entity=True) stmt = select(b1).filter(b1.c.d1.between("d3d1", "d5d1")) eq_( sess.execute(stmt).scalars().all(), [("d3d1", "d3d2"), ("d4d1", "d4d2"), ("d5d1", "d5d2")], )
def test_as_scalars_future(self): Data = self.classes.Data sess = Session(testing.db) b1 = Bundle("b1", Data.d1, Data.d2) stmt = select(b1).filter(b1.c.d1.between("d3d1", "d5d1")) eq_( sess.scalars(stmt).all(), [("d3d1", "d3d2"), ("d4d1", "d4d2"), ("d5d1", "d5d2")], )
def test_join_selectable(self): Data = self.classes.Data Other = self.classes.Other sess = Session() b1 = Bundle('b1', Data.d1, Data.d2) q = sess.query(b1).join(Other) self.assert_compile( q, "SELECT data.d1 AS data_d1, data.d2 AS data_d2 " "FROM data " "JOIN other ON data.id = other.data_id")
def test_bundle_w_annotation(self): A = self.classes.A s = Session() q = s.query(Bundle("ASdf", A.data), A).select_from(A) @profiling.function_call_count(warmup=1) def go(): for i in range(100): q.all() go()
def test_bundle_wo_annotation(self): A = self.classes.A a = self.tables.a s = Session() q = s.query(Bundle("ASdf", a.c.data), A).select_from(A) @profiling.function_call_count() def go(): for i in range(100): q.all() go()
def test_labeled_cols_as_rows_future(self): Data = self.classes.Data sess = Session() b1 = Bundle("b1", Data.d1.label("x"), Data.d2.label("y")) stmt = select(b1).filter(b1.c.x.between("d3d1", "d5d1")) eq_( sess.execute(stmt).all(), [(("d3d1", "d3d2"), ), (("d4d1", "d4d2"), ), (("d5d1", "d5d2"), )], )
def get_response_data(self, name, ensemble_name): """Load lightweight "bundle" objects using the ORM.""" bundle = Bundle("response", Response.id, Response.values, Realization.index) ensemble = self.get_ensemble(ensemble_name) response_definition = self._get_response_definition( name=name, ensemble_id=ensemble.id) for row in (self._session.query(bundle).filter_by( response_definition_id=response_definition.id, ).join( Realization).yield_per(1)): yield row.response
def test_bundle_qualification(self): Employee, JuniorEngineer, Manager, Engineer = ( self.classes.Employee, self.classes.JuniorEngineer, self.classes.Manager, self.classes.Engineer, ) session, m1, e1, e2 = self._fixture_one() m1id, e1id, e2id = m1.employee_id, e1.employee_id, e2.employee_id def scalar(q): return [x[0] for x, in q] eq_( scalar(session.query(Bundle("name", Employee.employee_id))), [m1id, e1id, e2id], ) eq_( scalar(session.query(Bundle("name", Engineer.employee_id))), [e1id, e2id], ) eq_(scalar(session.query(Bundle("name", Manager.employee_id))), [m1id]) # this currently emits "WHERE type IN (?, ?) AND type IN (?, ?)", # so no result. eq_( session.query( Bundle("name", Manager.employee_id, Engineer.employee_id) ).all(), [], ) eq_( scalar(session.query(Bundle("name", JuniorEngineer.employee_id))), [e2id], )
def test_multi_bundle(self): Data = self.classes.Data Other = self.classes.Other d1 = aliased(Data) b1 = Bundle('b1', d1.d1, d1.d2) b2 = Bundle('b2', Data.d1, Other.o1) sess = Session() q = sess.query(b1, b2).join(Data.others).join(d1, d1.id == Data.id).\ filter(b1.c.d1 == 'd3d1') eq_( q.all(), [ (('d3d1', 'd3d2'), ('d3d1', 'd3o0')), (('d3d1', 'd3d2'), ('d3d1', 'd3o1')), (('d3d1', 'd3d2'), ('d3d1', 'd3o2')), (('d3d1', 'd3d2'), ('d3d1', 'd3o3')), (('d3d1', 'd3d2'), ('d3d1', 'd3o4'))] )
def test_same_named_col_in_orderby(self): Data, Other = self.classes("Data", "Other") bundle = Bundle("pk", Data.id, Other.id) sess = fixture_session() self.assert_compile( sess.query(Data, Other).order_by(bundle), "SELECT data.id AS data_id, data.d1 AS data_d1, " "data.d2 AS data_d2, data.d3 AS data_d3, " "other.id AS other_id, other.data_id AS other_data_id, " "other.o1 AS other_o1 " "FROM data, other ORDER BY data.id, other.id", )
def test_bundle_w_annotation(self): A = self.classes.A s = fixture_session() q = s.query(Bundle("ASdf", A.data), A).select_from(A) @profiling.function_call_count(warmup=1) def go(): for i in range(100): # test counts assume objects remain in the session # from previous run r = q.all() # noqa F841 go()
def test_multi_bundle(self): Data = self.classes.Data Other = self.classes.Other d1 = aliased(Data) b1 = Bundle("b1", d1.d1, d1.d2) b2 = Bundle("b2", Data.d1, Other.o1) sess = Session() q = (sess.query(b1, b2).join(Data.others).join( d1, d1.id == Data.id).filter(b1.c.d1 == "d3d1")) eq_( q.all(), [ (("d3d1", "d3d2"), ("d3d1", "d3o0")), (("d3d1", "d3d2"), ("d3d1", "d3o1")), (("d3d1", "d3d2"), ("d3d1", "d3o2")), (("d3d1", "d3d2"), ("d3d1", "d3o3")), (("d3d1", "d3d2"), ("d3d1", "d3o4")), ], )
def test_single_entity_flag_is_legacy_w_future(self): Data = self.classes.Data sess = Session(testing.db, future=True) # flag has no effect b1 = Bundle("b1", Data.d1, Data.d2, single_entity=True) stmt = select(b1).filter(b1.c.d1.between("d3d1", "d5d1")) rows = sess.execute(stmt).all() eq_( rows, [(("d3d1", "d3d2"), ), (("d4d1", "d4d2"), ), (("d5d1", "d5d2"), )], )
def test_orm_order_by_bundle(dburl): Scorecard = Bundle( 'scorecard', # CW: existential horror Book.score.label('popularity'), Book.popularity.label('score')) with S(dburl, echo=ECHO) as s: q = s.query(Book).order_by(Scorecard, Book.id) check_paging_orm(q=q) q = s.query(Book, Scorecard).order_by(Book.id) check_paging_orm(q=q) q = s.query(Scorecard).order_by(Scorecard.c.popularity, Book.id) check_paging_orm(q=q)
def test_bundle_nesting_unions(self): Data = self.classes.Data sess = Session() b1 = Bundle('b1', Data.d1, Bundle('b2', Data.d2, Data.d3)) q1 = sess.query(b1).\ filter(b1.c.d1.between('d3d1', 'd7d1')).\ filter(b1.c.b2.c.d2.between('d4d2', 'd5d2')) q2 = sess.query(b1).\ filter(b1.c.d1.between('d3d1', 'd7d1')).\ filter(b1.c.b2.c.d2.between('d5d2', 'd6d2')) eq_( q1.union(q2).all(), [(('d4d1', ('d4d2', 'd4d3')), ), (('d5d1', ('d5d2', 'd5d3')), ), (('d6d1', ('d6d2', 'd6d3')), )]) # naming structure is preserved row = q1.union(q2).first() eq_(row.b1.d1, 'd4d1') eq_(row.b1.b2.d2, 'd4d2')
def test_multi_bundle_future(self): Data = self.classes.Data Other = self.classes.Other d1 = aliased(Data) b1 = Bundle("b1", d1.d1, d1.d2) b2 = Bundle("b2", Data.d1, Other.o1) sess = Session(testing.db, future=True) stmt = (select(b1, b2).join(Data.others).join( d1, d1.id == Data.id).filter(b1.c.d1 == "d3d1")) eq_( sess.execute(stmt).all(), [ (("d3d1", "d3d2"), ("d3d1", "d3o0")), (("d3d1", "d3d2"), ("d3d1", "d3o1")), (("d3d1", "d3d2"), ("d3d1", "d3o2")), (("d3d1", "d3d2"), ("d3d1", "d3o3")), (("d3d1", "d3d2"), ("d3d1", "d3o4")), ], )
def test_clause_expansion(self): Data = self.classes.Data b1 = Bundle('b1', Data.id, Data.d1, Data.d2) sess = Session() self.assert_compile( sess.query(Data).order_by(b1), "SELECT data.id AS data_id, data.d1 AS data_d1, " "data.d2 AS data_d2, data.d3 AS data_d3 FROM data " "ORDER BY data.id, data.d1, data.d2") self.assert_compile( sess.query(func.row_number().over(order_by=b1)), "SELECT row_number() OVER (ORDER BY data.id, data.d1, data.d2) " "AS anon_1 FROM data")
def test_joins_from_adapted_entities(self): Data = self.classes.Data # test for #1853 in terms of bundles # specifically this exercises adapt_to_selectable() b1 = Bundle("b1", Data.id, Data.d1, Data.d2) session = fixture_session() first = session.query(b1) second = session.query(b1) unioned = first.union(second) subquery = session.query(Data.id).subquery() joined = unioned.outerjoin(subquery, subquery.c.id == Data.id) joined = joined.order_by(Data.id, Data.d1, Data.d2) self.assert_compile( joined, "SELECT anon_1.data_id AS anon_1_data_id, " "anon_1.data_d1 AS anon_1_data_d1, " "anon_1.data_d2 AS anon_1_data_d2 FROM " "(SELECT data.id AS data_id, data.d1 AS data_d1, " "data.d2 AS data_d2 FROM " "data UNION SELECT data.id AS data_id, data.d1 AS data_d1, " "data.d2 AS data_d2 FROM data) AS anon_1 " "LEFT OUTER JOIN (SELECT data.id AS id FROM data) AS anon_2 " "ON anon_2.id = anon_1.data_id " "ORDER BY anon_1.data_id, anon_1.data_d1, anon_1.data_d2", ) # tuple nesting still occurs eq_( joined.all(), [ ((1, "d0d1", "d0d2"),), ((2, "d1d1", "d1d2"),), ((3, "d2d1", "d2d2"),), ((4, "d3d1", "d3d2"),), ((5, "d4d1", "d4d2"),), ((6, "d5d1", "d5d2"),), ((7, "d6d1", "d6d2"),), ((8, "d7d1", "d7d2"),), ((9, "d8d1", "d8d2"),), ((10, "d9d1", "d9d2"),), ], )
def test_single_entity_flag_is_legacy_w_future(self): Data = self.classes.Data sess = Session(testing.db, future=True) # flag has no effect b1 = Bundle("b1", Data.d1, Data.d2, single_entity=True) stmt = select(b1).filter(b1.c.d1.between("d3d1", "d5d1")) with testing.expect_deprecated_20( "The Bundle.single_entity flag has no effect when " "using 2.0 style execution."): rows = sess.execute(stmt).all() eq_( rows, [(("d3d1", "d3d2"), ), (("d4d1", "d4d2"), ), (("d5d1", "d5d2"), )], )
def test_query_count(self): Data = self.classes.Data b1 = Bundle("b1", Data.d1, Data.d2) eq_(fixture_session().query(b1).count(), 10)