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)
