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