def verification_course_results_histogram(self, course_id, instrument_id):
try:
related_course_ids = self._get_contained_course_ids(course_id)
results = self.db.session.query(funcfilter(func.count(1), RequestResult.result>=0, RequestResult.result<0.1).label('<10'),
funcfilter(func.count(1), RequestResult.result>=0.1, RequestResult.result<0.2).label('<20'),
funcfilter(func.count(1), RequestResult.result>=0.2, RequestResult.result<0.3).label('<30'),
funcfilter(func.count(1), RequestResult.result>=0.3, RequestResult.result<0.4).label('<40'),
funcfilter(func.count(1), RequestResult.result>=0.4, RequestResult.result<0.5).label('<50'),
funcfilter(func.count(1), RequestResult.result>=0.5, RequestResult.result<0.6).label('<60'),
funcfilter(func.count(1), RequestResult.result>=0.6, RequestResult.result<0.7).label('<70'),
funcfilter(func.count(1), RequestResult.result>=0.7, RequestResult.result<0.8).label('<80'),
funcfilter(func.count(1), RequestResult.result>=0.8, RequestResult.result<0.9).label('<90'),
funcfilter(func.count(1), RequestResult.result>=0.9, RequestResult.result<=1.0).label('100')
) \
.filter(Request.activity_id==CourseActivity.activity_id,
Request.is_enrolment == False,
CourseActivity.course_id.in_(related_course_ids),
RequestResult.request_id == Request.id,
RequestResult.instrument_id == instrument_id,
RequestResult.status == TESLA_RESULT_STATUS.DONE) \
.one()
except:
self.logger.exception("Error getting users with validation requests")
return []
return list(results)
def get_activity_learner_statistics(self, activity_id):
try:
current_version = self._get_current_informed_consent_version()
version_parts = current_version.split('.')
current_version = '{}.{}.'.format(version_parts[0], version_parts[1])
activity_learner_stats = self.db.session.query(
funcfilter(func.count(1), Learner.consent_rejected != None).label('rejected_ic'),
funcfilter(func.count(1), Learner.consent_rejected == None, Learner.consent_accepted == None).label(
'no_ic'),
funcfilter(func.count(1), Learner.consent_rejected == None,
InformedConsent.version.like('{}%'.format(current_version))).label('valid_ic'),
funcfilter(func.count(1), Learner.consent_rejected == None,
InformedConsent.version.notlike('{}%'.format(current_version))).label('outdated_ic'),
func.count(1).label('total')
).filter(
CourseActivity.activity_id == activity_id,
CourseLearner.course_id==CourseActivity.course_id,
Learner.tesla_id == CourseLearner.tesla_id).group_by(Learner.tesla_id).outerjoin(InformedConsent).one_or_none()
except Exception:
self.logger.exception("Error geting activity learners statistics for activity with ID {}".format(activity_id))
activity_learner_stats = None
return activity_learner_stats
def get_recent_location():
"""
Returns the UTC timestamp of the most recently updated verified location
"""
query = funcfilter(func.max(Entities.updated_on),
Entities.is_verified == True)
data = db.session.query(query).scalar()
response = jsonify(data)
response.headers.add('Access-Control-Allow-Origin', '*')
return response
def get_course_summary(self, course_id):
try:
related_courses = self._get_contained_course_ids(course_id)
summary = self.db.session.query(
RequestResult.instrument_id,
func.avg(Enrollment.percentage).label('enrolment_percentage'),
funcfilter(func.count(1), RequestResult.status ==
TESLA_RESULT_STATUS.DONE).label('valid'),
funcfilter(func.count(1), RequestResult.status ==
TESLA_RESULT_STATUS.PENDING).label('pending'),
funcfilter(
func.count(1),
or_(RequestResult.status == TESLA_RESULT_STATUS.TIMEOUT,
RequestResult.status ==
TESLA_RESULT_STATUS.FAILED)).label('failed'),
funcfilter(func.min(RequestResult.result), RequestResult.status
== TESLA_RESULT_STATUS.DONE).label('min'),
funcfilter(func.max(RequestResult.result), RequestResult.status
== TESLA_RESULT_STATUS.DONE).label('max'),
funcfilter(
func.avg(RequestResult.result), RequestResult.status ==
TESLA_RESULT_STATUS.DONE).label('average')).filter(
CourseActivity.activity_id == Request.activity_id,
CourseActivity.course_id.in_(related_courses),
RequestResult.request_id == Request.id).outerjoin(
Enrollment, Enrollment.instrument_id ==
RequestResult.instrument_id).group_by(
RequestResult.instrument_id).all()
except Exception:
self.logger.exception("Error getting summary for a course")
summary = None
return summary
def get_active_top_course_learner_statistics(self, paginate=None):
current_version = self._get_current_informed_consent_version()
version_parts = current_version.split('.')
current_version = '{}.{}.'.format(version_parts[0],version_parts[1])
try:
current_date = datetime.date.today()
query = Course.query.filter(Course.parent_id==None, or_(Course.start==None, Course.start<=current_date),
or_(Course.end == None, Course.end>=current_date))
if paginate is None:
active_courses = query.paginate()
else:
active_courses = paginate.get_results(query)
learner_stats = schemas.CourseLearnersStatsPagination().dump(active_courses).data
for c in learner_stats['items']:
related_course_ids = self._get_contained_course_ids(c['id'])
course_learner_stats = self.db.session.query(funcfilter(func.count(1), Learner.consent_rejected!=None).label('rejected_ic'),
funcfilter(func.count(1), Learner.consent_rejected==None, Learner.consent_accepted==None).label('no_ic'),
funcfilter(func.count(1), Learner.consent_rejected==None, InformedConsent.version.like('{}%'.format(current_version))).label('valid_ic'),
funcfilter(func.count(1), Learner.consent_rejected==None, InformedConsent.version.notlike('{}%'.format(current_version))).label('outdated_ic'),
func.count(1).label('total')
).filter(
CourseLearner.course_id.in_(related_course_ids),
Learner.tesla_id==CourseLearner.tesla_id).outerjoin(InformedConsent).one_or_none()
c['total'] = course_learner_stats.total
c['no_ic'] = course_learner_stats.no_ic
c['valid_ic'] = course_learner_stats.valid_ic
c['rejected_ic'] = course_learner_stats.rejected_ic
c['outdated_ic'] = course_learner_stats.outdated_ic
except Exception:
self.logger.exception("Error selecting active top courses learner statistics")
learner_stats = None
return learner_stats
def get_learner_instrument_valid_results_histogram(self, tesla_id, instrument_id):
try:
results = self.db.session.query(funcfilter(func.count(1), RequestResult.result>=0, RequestResult.result<0.1).label('<10'),
funcfilter(func.count(1), RequestResult.result>=0.1, RequestResult.result<0.2).label('<20'),
funcfilter(func.count(1), RequestResult.result>=0.2, RequestResult.result<0.3).label('<30'),
funcfilter(func.count(1), RequestResult.result>=0.3, RequestResult.result<0.4).label('<40'),
funcfilter(func.count(1), RequestResult.result>=0.4, RequestResult.result<0.5).label('<50'),
funcfilter(func.count(1), RequestResult.result>=0.5, RequestResult.result<0.6).label('<60'),
funcfilter(func.count(1), RequestResult.result>=0.6, RequestResult.result<0.7).label('<70'),
funcfilter(func.count(1), RequestResult.result>=0.7, RequestResult.result<0.8).label('<80'),
funcfilter(func.count(1), RequestResult.result>=0.8, RequestResult.result<0.9).label('<90'),
funcfilter(func.count(1), RequestResult.result>=0.9, RequestResult.result<=1.0).label('100')
) \
.filter(Request.is_enrolment == False,
Request.tesla_id == tesla_id,
RequestResult.request_id == Request.id,
RequestResult.instrument_id == instrument_id,
RequestResult.status == TESLA_RESULT_STATUS.DONE) \
.one()
except:
self.logger.exception("Error getting learner results histogram")
return []
return list(results)
def get_activity_learner_summary(self, activity_id, tesla_id):
try:
summary = self.db.session.query(
RequestResult.instrument_id,
func.max(Enrollment.percentage).label('enrolment_percentage'),
funcfilter(func.count(1), RequestResult.status ==
TESLA_RESULT_STATUS.DONE).label('valid'),
funcfilter(func.count(1), RequestResult.status ==
TESLA_RESULT_STATUS.PENDING).label('pending'),
funcfilter(
func.count(1),
or_(RequestResult.status == TESLA_RESULT_STATUS.TIMEOUT,
RequestResult.status ==
TESLA_RESULT_STATUS.FAILED)).label('failed'),
funcfilter(func.min(RequestResult.result), RequestResult.status
== TESLA_RESULT_STATUS.DONE).label('min'),
funcfilter(func.max(RequestResult.result), RequestResult.status
== TESLA_RESULT_STATUS.DONE).label('max'),
funcfilter(
func.avg(RequestResult.result), RequestResult.status ==
TESLA_RESULT_STATUS.DONE).label('average')).filter(
Request.tesla_id == tesla_id,
Request.activity_id == activity_id,
RequestResult.request_id == Request.id).outerjoin(
Enrollment,
and_(
Enrollment.tesla_id == tesla_id,
Enrollment.instrument_id ==
RequestResult.instrument_id)).group_by(
RequestResult.instrument_id).all()
except Exception:
self.logger.exception(
"Error getting summary for a learner and activity")
summary = None
return summary
where(user_table.c.name == bindparam('username')).
values(fullname=bindparam('fullname'))
)
connection.execute(
stmt, [{"username": "******", "fullname": "Wendy Smith"},
{"username": "******", "fullname": "Jack Jones"},
]
)
stmt = select([users_table]).where(users_table.c.name == 'Wendy')
result = connection.execute(stmt)
from sqlalchemy import funcfilter
funcfilter(func.count(1), MyClass.name == 'some name')
select([func.count(table.c.id)])
stmt = select([func.count(SourceFuncTable.device_id)])
for result in self._session.execute(stmt):
print(result.items())
from sqlalchemy import text
# bind parameters by name
t = text("SELECT * FROM users WHERE id=:user_id")
result = connection.execute(t, user_id=12)
t = text("SELECT * FROM users WHERE id=:user_id").bindparams(user_id=7).columns(id=Integer, name=String)
for id, name in connection.execute(t):
print(id, name)
# LIKE
