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_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_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)