def query(self, mapper_or_class, entity_name=None, **kwargs): """return a new Query object corresponding to this Session and the mapper, or the classes' primary mapper.""" if isinstance(mapper_or_class, type): return query.Query( _class_mapper(mapper_or_class, entity_name=entity_name), self, **kwargs) else: return query.Query(mapper_or_class, self, **kwargs)
def query_list_marks_all(session): return query.Query([ Student, Teacher, Subject, Exam, ], session).all()
def test_create_user_with_non_existing_referral(self): request = testing.DummyRequest() request.params['email'] = '*****@*****.**' test_uuid = str(uuid.uuid4()) request.params['referral'] = test_uuid mock_referral = mock(Referral) mock_referral.num_referrals = 0 mock_referral.user_id = 1 request.dbsession = mock(sqlalchemy.orm.session.Session) mock_user = mock(User) mock_user.id = 1 # TODO: take another look at mocking this constructor when(referral_program.models.user).User( email=ANY(str)).thenReturn(mock_user) when(request.dbsession).add(ANY(User)) mock_referral_query = query.Query([]) when(request.dbsession).query(Referral).thenReturn(mock_referral_query) err = IntegrityError('', params=None, orig=None) err.args = [ '''(psycopg2.errors.ForeignKeyViolation) insert or update on table "referral" violates foreign key constraint "fk_referral_user_id_user"\nDETAIL: Key (user_id)=(360) is not present in table "user".\n ''' ] when(mock_referral_query).filter_by(id=ANY).thenRaise(err) view_being_tested = ReferralView(request) response = view_being_tested.create_user() self.assertEqual(response.status_code, 400)
def test_get_valid_user(self): # Setup our dummy request before using it request = testing.DummyRequest() request.matchdict['user_id'] = '12' request.dbsession = mock(sqlalchemy.orm.session.Session) mock_user = mock(User) mock_user.id = 12 mock_user.email = '*****@*****.**' mock_user.referral = None mock_user.balance = 0 mock_user.total_referrals = 0 mock_query = query.Query([]) when(request.dbsession).query(User).thenReturn(mock_query) when(mock_query).filter_by(id=ANY).thenReturn(mock_query) when(mock_query).first().thenReturn(mock_user) view_being_tested = ReferralView(request) response = view_being_tested.get_user() self.assertEqual( response, { 'id': '12', 'email': '*****@*****.**', 'referral': 'None', 'balance': '0.0', 'total_referrals': '0' })
def query_find_4(session): '''result = jinja2.Template("""SELECT AVG(punkts_by_subject) FROM exam; """) return result.render() ''' return query.Query([func.avg(Exam.punkts_by_subject)], session).select_from(Exam).all()
def query_find_12(session): '''result = jinja2.Template("""SELECT AVG(punkts_by_subject),teacher.last_name FROM exam INNER JOIN teacher ON exam.id_teacher==teacher.id_teacher GROUP BY teacher.last_name; """) return result.render() ''' return query.Query([func.avg(Exam.punkts_by_subject), Teacher.last_name], session).select_from(Exam).join(Teacher).group_by( Teacher.last_name).all()
def query_find_2(session, name): '''result = jinja2.Template("""SELECT student,MAX(avg_mark),subject FROM (SELECT AVG(punkts_by_subject) as avg_mark,student.last_name as student,subject.name_subject as subject FROM exam INNER JOIN student ON exam.id_student==student.id_student INNER JOIN subject ON exam.id_subject==subject.id_subject WHERE subject.name_subject = '{{name}}' GROUP BY student.last_name); """) return result.render(name=name) ''' sub_query = query.Query([ func.avg(Exam.punkts_by_subject).label("avg_mark"), Student.last_name.label("student"), Subject.name_subject.label("subject") ], session).select_from(Exam).join(Student).join(Subject).filter( Subject.name_subject == name).group_by(Student.last_name) return query.Query( [column("student"), func.max(column("avg_mark")), column("subject")], session).select_from(sub_query).all()
def init(self): super(LazyLoader, self).init() (self.lazywhere, self.lazybinds, self.lazyreverse) = self._create_lazy_clause( self.polymorphic_primaryjoin, self.polymorphic_secondaryjoin, self.remote_side) # determine if our "lazywhere" clause is the same as the mapper's # get() clause. then we can just use mapper.get() self.use_get = not self.uselist and query.Query( self.mapper)._get_clause.compare(self.lazywhere)
def test_update_args(self): Data = self.classes.Data session = testing.mock.Mock(wraps=Session()) update_args = {"mysql_limit": 1} query.Query(Data, session).update({Data.cnt: Data.cnt + 1}, update_args=update_args) eq_(session.execute.call_count, 1) args, kwargs = session.execute.call_args eq_(len(args), 1) update_stmt = args[0] eq_(update_stmt.dialect_kwargs, update_args)
def test_create_user_with_referral_increment_referrer_balance_with_previous_bonus( self): # Setup our dummy request before using it request = testing.DummyRequest() request.params['email'] = '*****@*****.**' test_uuid = str(uuid.uuid4()) request.params['referral'] = test_uuid mock_referral = mock(Referral) mock_referral.num_referrals = 0 mock_referral.user_id = 1 request.dbsession = mock(sqlalchemy.orm.session.Session) mock_user = mock(User) mock_user.id = 1 # TODO: take another look at mocking this constructor when(referral_program.models.user).User( email=ANY(str)).thenReturn(mock_user) when(request.dbsession).add(ANY(User)) mock_referral_query = query.Query([]) when(request.dbsession).query(Referral).thenReturn(mock_referral_query) when(mock_referral_query).filter_by( id=ANY).thenReturn(mock_referral_query) when(mock_referral_query).first().thenReturn(mock_referral) mock_user_query = query.Query([]) mock_referring_user = mock(User) mock_referring_user.total_referrals = 4 mock_referring_user.referral = uuid.uuid4() mock_referring_user.balance = 1000 # The referrer previously earned $10 by signing up using a referral when(request.dbsession).query(User).thenReturn(mock_user_query) when(mock_user_query).filter_by(id=ANY).thenReturn(mock_user_query) when(mock_user_query).first().thenReturn(mock_referring_user) when(request.dbsession).flush() when(request.dbsession).refresh(ANY(User)) view_being_tested = ReferralView(request) response = view_being_tested.create_user() self.assertEqual(mock_referral.num_referrals, 1) self.assertEqual(mock_referring_user.total_referrals, 5) self.assertEqual(mock_referring_user.balance, 2000) self.assertEqual(response, {'id': None})
def query_find_3(session, name): '''result = jinja2.Template("""SELECT AVG(punkts_by_subject),name_group,subject.name_subject FROM exam INNER JOIN subject ON exam.id_subject==subject.id_subject WHERE name_group = '{{name}}' GROUP BY subject.name_subject; """) return result.render(name=name) ''' return query.Query([ func.avg(Exam.punkts_by_subject), Exam.name_group, Subject.name_subject ], session).select_from(Exam).join(Subject).filter( Exam.name_group == name).group_by(Subject.name_subject).all()
def query_find_6(session, name): '''result = jinja2.Template("""SELECT student.last_name,student.first_name,student.second_name,name_group FROM exam INNER JOIN student ON exam.id_student==student.id_student WHERE name_group = '{{name}}' ORDER BY student.last_name; """) return result.render(name=name) ''' return query.Query([ Student.last_name, Student.first_name, Student.second_name, Exam.name_group ], session).select_from(Exam).join(Student).filter( Exam.name_group == name).order_by(Student.last_name).all()
def query_find_11(session, id): '''result = jinja2.Template("""SELECT AVG(punkts_by_subject),student.last_name,teacher.last_name FROM exam INNER JOIN student ON exam.id_student==student.id_student INNER JOIN teacher ON exam.id_teacher==teacher.id_teacher WHERE teacher.id_teacher = {{id}} GROUP BY student.last_name; """) return result.render(id=id) ''' return query.Query([ func.avg(Exam.punkts_by_subject), Student.last_name, Teacher.last_name ], session).select_from(Exam).join(Student).join(Teacher).filter( Teacher.id_teacher == id).group_by(Student.last_name).all()
def query_find_10(session): '''result = jinja2.Template("""SELECT subject.name_subject,student.last_name,teacher.last_name FROM exam INNER JOIN student ON exam.id_student==student.id_student INNER JOIN subject ON exam.id_subject==subject.id_subject INNER JOIN teacher ON exam.id_teacher==teacher.id_teacher ORDER BY student.last_name; """) return result.render() ''' return query.Query( [Subject.name_subject, Student.last_name, Teacher.last_name], session).select_from(Exam).join(Student).join(Subject).join( Teacher).order_by(Student.last_name).all()
def query_find_5(session): '''result = jinja2.Template("""SELECT teacher.last_name,teacher.first_name,teacher.second_name,subject.name_subject FROM exam INNER JOIN subject ON exam.id_subject==subject.id_subject INNER JOIN teacher ON exam.id_teacher==teacher.id_teacher ORDER BY teacher.last_name; """) return result.render() ''' return query.Query([ Teacher.last_name, Teacher.first_name, Teacher.second_name, Subject.name_subject ], session).select_from(Exam).join(Subject).join(Teacher).order_by( Teacher.last_name).all()
def test_get_invalid_user(self): # Setup our dummy request before using it request = testing.DummyRequest() request.matchdict['user_id'] = '12' request.dbsession = mock(sqlalchemy.orm.session.Session) mock_query = query.Query([]) when(request.dbsession).query(User).thenReturn(mock_query) when(mock_query).filter_by(id=ANY).thenReturn(mock_query) when(mock_query).first().thenRaise( DBAPIError(statement='', params=[], orig='')) view_being_tested = ReferralView(request) response = view_being_tested.get_user() self.assertEqual(response.status_code, 400)
def query_find_7(session, name, subject): '''result = jinja2.Template("""SELECT student.last_name,student.first_name,student.second_name,name_group,subject.name_subject,punkts_by_subject FROM exam INNER JOIN student ON exam.id_student==student.id_student INNER JOIN subject ON exam.id_subject==subject.id_subject WHERE name_group = '{{name}}' AND subject.name_subject = '{{subject}}' ORDER BY student.last_name; """) return result.render(name=name, subject=subject) ''' return query.Query([ Student.last_name, Student.first_name, Student.second_name, Exam.name_group, Subject.name_subject, Exam.punkts_by_subject ], session).select_from(Exam).join(Student).join(Subject).filter( Exam.name_group == name, Subject.name_subject == subject).order_by(Student.last_name).all()
def query_list_students(session): return query.Query([ Student, ], session).all()
def query_list_teachers(session): return query.Query([ Teacher, ], session).all()
def main(table_name, db_url): # DB program + :// + path from this file to the database # db_url = "sqlite:///participants.db" # db_url = "mysql://*****:*****@localhost/goals_vs_rewards" # Boilerplate sqlalchemy setup engine = create_engine(db_url) metadata = MetaData() metadata.bind = engine table = Table(table_name, metadata, autoload=True) # Make a query and loop through selection = table.select() rows = selection.execute() # Status codes of subjects who completed experiment # ToDo: Check these against goals_vs_rewards status settings. statuses = [3, 4, 5, 7] # Hardcode any worker exclusion exclude = [] # Extract table headers column_names = [] column_descs = query.Query(table).column_descriptions for metadatum in column_descs: column_names.append(metadatum['name']) # Initialize data objects data_aux = [] data_strs = [] # Extract table rows. One row should corrospond to one worker's data. for row in rows: if row['status'] in statuses and row['uniqueid'] not in exclude: # Save the data strings to be unpacked data_strs.append(row['datastring']) # Save the auxiliary data. for key in column_names: data_aux.append(row[key]) # Now we have all participant datastrings in a list. # Let's make it a bit easier to work with: # parse each participant's datastring as json object # and take the 'data' sub-object # data_alt = [] # for part in data_strs: # data_alt.append(json.loads(part)['data']) # data_alt.append(json.loads(part)['questiondata']) # data_strs = data_alt # data_qs = [json.loads(part)['questiondata'] for part in data_strs] data_strs = [json.loads(part)['data'] for part in data_strs] # insert uniqueid field into trialdata in case it wasn't added # in experiment: for part in data_strs: for record in part: record['trialdata']['uniqueid'] = record['uniqueid'] # flatten nested list so we just have a list of the trialdata recorded # each time psiturk.recordTrialData(trialdata) was called. data_strs = [record['trialdata'] for part in data_strs for record in part] # Put all subjects' trial data into a dataframe object from the # 'pandas' python library: one option among many for analysis return {'main_data': pd.DataFrame(data_strs), 'aux_data': pd.DataFrame(data_aux)}
def query_list_subjects(session): return query.Query([ Subject, ], session).all()