def test_query_with_implicit_join_lt_09(self):
from sqlalchemy.exc import InvalidRequestError
with self.assertRaises(InvalidRequestError) as e1:
(self.session.query(
Construct({
'a_id': self.a_cls.id,
'a_name': self.a_cls.name,
'b_id': self.b_cls.id,
'b_name': self.b_cls.name
}), ).join(self.a_cls))
if SQLA_ge_08:
self.assertIn("Don't know how to join", e1.exception.args[0])
else:
self.assertEqual(e1.exception.args[0],
"Could not find a FROM clause to join from")
with self.assertRaises(InvalidRequestError) as e2:
(self.session.query(
Construct({
'a_id': self.a_cls.id,
'a_name': self.a_cls.name,
'b_id': self.b_cls.id,
'b_name': self.b_cls.name
}), ).join(self.b_cls))
if SQLA_ge_08:
self.assertIn("Don't know how to join", e2.exception.args[0])
else:
self.assertEqual(e2.exception.args[0],
"Could not find a FROM clause to join from")
def test_query_count(self):
query = self.session.query(
Construct({
'a_id': self.a_cls.id,
'a_name': self.a_cls.name
}), )
self.assertEqual(query.count(), 2)
def test_query_row(self):
query = self.session.query(
self.a_cls.id,
Construct({
'a_id': self.a_cls.id,
'a_name': self.a_cls.name
}),
self.a_cls.name,
)
r1, r2 = query.all()
self.assertTrue(isinstance(r1, KeyedTuple), type(r1))
self.assertEqual(r1.id, 1)
self.assertEqual(r1.name, 'a1')
self.assertTrue(isinstance(r1[1], Object), type(r1[1]))
self.assertEqual(r1[1].a_id, 1)
self.assertEqual(r1[1].a_name, 'a1')
self.assertTrue(isinstance(r2, KeyedTuple), type(r2))
self.assertEqual(r2.id, 2)
self.assertEqual(r2.name, 'a2')
self.assertTrue(isinstance(r2[1], Object), type(r2[1]))
self.assertEqual(r2[1].a_id, 2)
self.assertEqual(r2[1].a_name, 'a2')
def test_query_labeled_columns(self):
a1_cls = aliased(self.a_cls, name='A1')
a2_cls = aliased(self.a_cls, name='A2')
query = (self.session.query(
Construct({
'a1_id': a1_cls.id.label('__a1_id__'),
'a1_name': a1_cls.name.label('__a1_name__'),
'a2_id': a2_cls.id.label('__a2_id__'),
'a2_name': a2_cls.name.label('__a2_name__')
}), ).select_from(a1_cls).join(a2_cls, a2_cls.id == a1_cls.id + 1))
statement = str(query)
self.assertIn('"A1".id AS __a1_id__', statement)
self.assertIn('"A1".name AS __a1_name__', statement)
self.assertIn('"A2".id AS __a2_id__', statement)
self.assertIn('"A2".name AS __a2_name__', statement)
s, = query.all()
self.assertTrue(isinstance(s, Object), type(s))
self.assertEqual(s.a1_id, 1)
self.assertEqual(s.a1_name, 'a1')
self.assertEqual(s.a2_id, 2)
self.assertEqual(s.a2_name, 'a2')
def test_basic_construct(self):
struct = Construct({
'a_id': self.a_cls.id,
'a_name': self.a_cls.name,
})
self.assertEqual(set(struct._columns), {
self.a_cls.__table__.c.id,
self.a_cls.__table__.c.name,
})
result = {
self.a_cls.__table__.c.id: 1,
self.a_cls.__table__.c.name: 'a1',
}
row = [result[col] for col in struct._columns]
s = struct._from_row(row)
self.assertEqual(s.a_id, 1)
self.assertEqual(s.a_name, 'a1')
def test_nested_construct(self):
struct = Construct({
'a_id': apply_(operator.add, [self.a_cls.id, 5]),
'a_name': apply_(operator.concat, [self.a_cls.name, '-test']),
})
self.assertEqual(set(struct._columns), {
self.a_cls.__table__.c.id,
self.a_cls.__table__.c.name,
})
result = {
self.a_cls.__table__.c.id: 1,
self.a_cls.__table__.c.name: 'a1',
}
row = [result[col] for col in struct._columns]
s = struct._from_row(row)
self.assertEqual(s.a_id, 1 + 5)
self.assertEqual(s.a_name, 'a1' + '-test')
def test_basic_construct(self):
struct = Construct({
'a_id': self.a_cls.id,
'a_name': self.a_cls.name,
})
self.assertEqual(set(struct._columns), {
self.a_cls.__table__.c.id,
self.a_cls.__table__.c.name,
})
result = {
self.a_cls.__table__.c.id: 1,
self.a_cls.__table__.c.name: 'a1',
}
row = [result[col] for col in struct._columns]
s = struct._from_row(row)
self.assertEqual(s.a_id, 1)
self.assertEqual(s.a_name, 'a1')
def test_pipe_operator(self):
struct = Construct({
'a_name_hash':
apply_(capitalize, [self.a_cls.name]) | hash,
})
s1, s2 = self.session.query(struct).order_by(self.a_cls.name).all()
self.assertEqual(s1.a_name_hash, hash('A1'))
self.assertEqual(s2.a_name_hash, hash('A2'))
def test_nested_construct(self):
struct = Construct({
'a_id':
apply_(operator.add, [self.a_cls.id, 5]),
'a_name':
apply_(operator.concat, [self.a_cls.name, '-test']),
})
self.assertEqual(set(struct._columns), {
self.a_cls.__table__.c.id,
self.a_cls.__table__.c.name,
})
result = {
self.a_cls.__table__.c.id: 1,
self.a_cls.__table__.c.name: 'a1',
}
row = [result[col] for col in struct._columns]
s = struct._from_row(row)
self.assertEqual(s.a_id, 1 + 5)
self.assertEqual(s.a_name, 'a1' + '-test')
def test_query_expr(self):
query = self.session.query(
Construct({
'a_id': self.a_cls.id,
'a_name': func.upper(self.a_cls.name)
}), )
s1, s2 = query.order_by(self.a_cls.name.asc()).all()
self.assertEqual(s1.a_name, 'A1')
self.assertEqual(s2.a_name, 'A2')
def get_vacancy4json(name_in_url):
vacancy_struct = Construct({
'title': Vacancy.title,
'city': City.name,
'salary': Vacancy.salary,
'text': Vacancy.text
})
vacancy = (db.session.query(vacancy_struct)
.outerjoin(Vacancy.city)
.filter(Vacancy.name_in_url == name_in_url).one()
)
return vacancy
def test_query_single_entity(self):
query = self.session.query(
Construct({
'a_id': self.a_cls.id,
'a_name': self.a_cls.name
}), )
s1, s2 = query.all()
self.assertTrue(isinstance(s1, Object), type(s1))
self.assertEqual(s1.a_id, 1)
self.assertEqual(s1.a_name, 'a1')
self.assertTrue(isinstance(s2, Object), type(s2))
self.assertEqual(s2.a_id, 2)
self.assertEqual(s2.a_name, 'a2')
def get_actual_vacancies_list():
vacancies_struct = Construct({
'title': Vacancy.title,
'city': City.name,
'category': Category.name,
'username': User.name,
'hide': Vacancy.is_hidden,
'salary': Vacancy.salary,
'short_description': Vacancy.short_description,
'updated_at': Vacancy.updated_at,
'visits': Vacancy.visits,
'id': Vacancy.id,
})
vacancies = (db.session.query(vacancies_struct).outerjoin(
Vacancy.city, Vacancy.category,
Vacancy.who_updated).filter(~Vacancy.condition_is_deleted).all())
return vacancies
def get_visible_vacancies_list():
vacancies_struct = Construct({
'category_id': Vacancy.category_id,
'city_id': Vacancy.city_id,
'name_in_url': Vacancy.name_in_url,
'title': Vacancy.title,
'salary': Vacancy.salary,
'short_description': Vacancy.short_description,
'id': Vacancy.id,
})
vacancies = (
db.session.query(vacancies_struct)
.filter(
~Vacancy.condition_is_deleted,
~Vacancy.condition_is_hidden,
).all()
)
return vacancies
def test_query_with_explicit_join(self):
query = (self.session.query(
Construct({
'a_id': self.a_cls.id,
'a_name': self.a_cls.name,
'b_id': self.b_cls.id,
'b_name': self.b_cls.name
}), ).join(self.b_cls.a))
s1, s2 = query.all()
self.assertTrue(isinstance(s1, Object), type(s1))
self.assertEqual(s1.a_id, 1)
self.assertEqual(s1.a_name, 'a1')
self.assertEqual(s1.b_id, 1)
self.assertEqual(s1.b_name, 'b1')
self.assertTrue(isinstance(s2, Object), type(s2))
self.assertEqual(s2.a_id, 2)
self.assertEqual(s2.a_name, 'a2')
self.assertEqual(s2.b_id, 2)
self.assertEqual(s2.b_name, 'b2')
def test_performance(self):
@define
def test_func(a, b):
def body(a_id, a_name, b_id, b_name):
pass
return body, [a.id, a.name, b.id, b.name]
struct = Construct({
'r1':
if_(self.a_cls.id, then_=test_func.defn(self.a_cls, self.b_cls)),
'r2':
if_(self.a_cls.name, then_=test_func.defn(self.a_cls, self.b_cls)),
'r3':
if_(self.b_cls.id, then_=test_func.defn(self.a_cls, self.b_cls)),
'r4':
if_(self.b_cls.name, then_=test_func.defn(self.a_cls, self.b_cls)),
})
row = (self.session.query(*struct._columns).join(self.b_cls.a).first())
# warm-up
for _ in _range(5000):
struct._from_row(row)
profile1 = Profile()
profile1.enable()
for _ in _range(5000):
struct._from_row(row)
profile1.disable()
out1 = StringIO()
stats1 = Stats(profile1, stream=out1)
stats1.strip_dirs()
stats1.sort_stats('calls').print_stats(10)
print(out1.getvalue().lstrip())
out1.close()
row = (self.session.query(
self.a_cls.id.label('a_id'),
self.a_cls.name.label('a_name'),
self.b_cls.id.label('b_id'),
self.b_cls.name.label('b_name'),
).join(self.b_cls.a).first())
def make_object(row):
Object(
dict(
r1=(test_func.func(row.a_id, row.a_name, row.b_id,
row.b_name) if row.a_id else None),
r2=(test_func.func(row.a_id, row.a_name, row.b_id,
row.b_name) if row.a_name else None),
r3=(test_func.func(row.a_id, row.a_name, row.b_id,
row.b_name) if row.b_id else None),
r4=(test_func.func(row.a_id, row.a_name, row.b_id,
row.b_name) if row.b_name else None),
))
# warm-up
for _ in _range(5000):
make_object(row)
profile2 = Profile()
profile2.enable()
for _ in _range(5000):
make_object(row)
profile2.disable()
out2 = StringIO()
stats2 = Stats(profile2, stream=out2)
stats2.strip_dirs()
stats2.sort_stats('calls').print_stats(10)
print(out2.getvalue().lstrip())
out2.close()
self.assertEqual(stats1.total_calls, stats2.total_calls)
def test_scalar_construct(self):
struct = Construct({'foo': 1, 'bar': '2'})
s = struct._from_row([])
self.assertEqual(s.foo, 1)
self.assertEqual(s.bar, '2')
def test_performance(self):
@define
def test_func(a, b):
def body(a_id, a_name, b_id, b_name):
pass
return body, [a.id, a.name, b.id, b.name]
struct = Construct({
'r1': if_(self.a_cls.id,
then_=test_func.defn(self.a_cls, self.b_cls)),
'r2': if_(self.a_cls.name,
then_=test_func.defn(self.a_cls, self.b_cls)),
'r3': if_(self.b_cls.id,
then_=test_func.defn(self.a_cls, self.b_cls)),
'r4': if_(self.b_cls.name,
then_=test_func.defn(self.a_cls, self.b_cls)),
})
row = (
self.session.query(*struct._columns)
.join(self.b_cls.a)
.first()
)
# warm-up
for _ in _range(5000):
struct._from_row(row)
profile1 = Profile()
profile1.enable()
for _ in _range(5000):
struct._from_row(row)
profile1.disable()
out1 = StringIO()
stats1 = Stats(profile1, stream=out1)
stats1.strip_dirs()
stats1.sort_stats('calls').print_stats(10)
print(out1.getvalue().lstrip())
out1.close()
row = (
self.session.query(
self.a_cls.id.label('a_id'),
self.a_cls.name.label('a_name'),
self.b_cls.id.label('b_id'),
self.b_cls.name.label('b_name'),
)
.join(self.b_cls.a)
.first()
)
def make_object(row):
Object(dict(
r1=(
test_func.func(row.a_id, row.a_name, row.b_id, row.b_name)
if row.a_id else None
),
r2=(
test_func.func(row.a_id, row.a_name, row.b_id, row.b_name)
if row.a_name else None
),
r3=(
test_func.func(row.a_id, row.a_name, row.b_id, row.b_name)
if row.b_id else None
),
r4=(
test_func.func(row.a_id, row.a_name, row.b_id, row.b_name)
if row.b_name else None
),
))
# warm-up
for _ in _range(5000):
make_object(row)
profile2 = Profile()
profile2.enable()
for _ in _range(5000):
make_object(row)
profile2.disable()
out2 = StringIO()
stats2 = Stats(profile2, stream=out2)
stats2.strip_dirs()
stats2.sort_stats('calls').print_stats(10)
print(out2.getvalue().lstrip())
out2.close()
self.assertEqual(stats1.total_calls, stats2.total_calls)
def test_scalar_construct(self):
struct = Construct({'foo': 1, 'bar': '2'})
s = struct._from_row([])
self.assertEqual(s.foo, 1)
self.assertEqual(s.bar, '2')