def test_connector_with_multi_tenants(self):
set_tenant_map({get_unittest_tenant_name(): 'NC', 'b': 'AB'})
dummy_session = DummySession()
dummy_session.set_tenants([get_unittest_tenant_name(), 'dummyTenant'])
self.__config.testing_securitypolicy(dummy_session)
conn = EdCoreDBConnection(state_code='NC')
self.assertIsInstance(conn, EdCoreDBConnection)
dim_student = conn.get_table('dim_student')
self.assertEqual(dim_student.name, 'dim_student')
def __init__(self, tenant=None, state_code=None, is_public=False):
'''
:params str tenant: Name of tenant
:params str state_code name of state_code
:params bool is_public. True if we want to access de-identified datastore
'''
if is_public:
self.db_conn = PublicDBConnection(tenant=tenant, state_code=state_code)
else:
self.db_conn = EdCoreDBConnection(tenant=tenant, state_code=state_code)
def _prepare_data(param, extract_type):
'''
Prepare record for available pre-query extract
'''
asmt_guid_with_grades = []
dim_asmt = None
fact_table = None
FACT_TABLE = {
AssessmentType.INTERIM_ASSESSMENT_BLOCKS:
Constants.FACT_BLOCK_ASMT_OUTCOME,
AssessmentType.SUMMATIVE: Constants.FACT_ASMT_OUTCOME_VW,
AssessmentType.INTERIM_COMPREHENSIVE: Constants.FACT_ASMT_OUTCOME_VW
}
prequery_func = PREQUERY_FUNCTIONS[param['asmtType']]
available_records = prequery_func(param, extract_type)
for record_by_asmt_type in available_records:
record = (record_by_asmt_type[Constants.ASMT_GUID],
record_by_asmt_type[Constants.ASMT_GRADE])
if (Constants.EFFECTIVE_DATE in record_by_asmt_type):
# IAB records
record_iab = (record_by_asmt_type[Constants.EFFECTIVE_DATE],
record_by_asmt_type[Constants.ASMT_CLAIM_1_NAME])
else:
record_iab = ()
asmt_guid_with_grades.append(record + record_iab)
if asmt_guid_with_grades:
with EdCoreDBConnection(
state_code=param.get(Constants.STATECODE)) as connector:
dim_asmt = connector.get_table(Constants.DIM_ASMT)
fact_table = connector.get_table(FACT_TABLE[param['asmtType']])
# Returns list of asmt guid with grades, and table objects
return asmt_guid_with_grades, dim_asmt, fact_table
def get_extract_assessment_item_and_raw_count_query(params, extract_type):
"""
private method to generate SQLAlchemy object or sql code for extraction of
students for item level/raw data
:param params: for query parameters asmt_year, asmt_type, asmt_subject,
asmt_grade
:param extract_type: Type of extraction: Item Level, Raw Data Level,
Student Assessment
"""
state_code = params.get(Constants.STATECODE)
with EdCoreDBConnection(state_code=state_code) as connector:
fact_asmt_outcome_vw = connector.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
query = select_with_context(
[func.count().label(Constants.COUNT)],
from_obj=[fact_asmt_outcome_vw],
permission=get_required_permission(extract_type),
state_code=state_code)
query = _assessment_item_and_raw_where_clause_builder(
query, fact_asmt_outcome_vw, params)
return query
def get_academic_year_query(academic_year, state_code):
with EdCoreDBConnection(state_code=state_code) as connection:
student_reg = connection.get_table(Constants.STUDENT_REG)
academic_year_query = select_with_context([student_reg.c.state_code,
student_reg.c.state_name,
student_reg.c.district_id,
student_reg.c.district_name,
student_reg.c.school_id,
student_reg.c.school_name,
student_reg.c.sex,
student_reg.c.enrl_grade,
student_reg.c.dmg_eth_hsp,
student_reg.c.dmg_eth_ami,
student_reg.c.dmg_eth_asn,
student_reg.c.dmg_eth_blk,
student_reg.c.dmg_eth_pcf,
student_reg.c.dmg_eth_wht,
student_reg.c.dmg_prg_iep,
student_reg.c.dmg_prg_lep,
student_reg.c.dmg_prg_504,
student_reg.c.dmg_sts_ecd,
student_reg.c.dmg_sts_mig,
student_reg.c.dmg_multi_race,
student_reg.c.academic_year],
from_obj=[student_reg], permission=RolesConstants.SRC_EXTRACTS,
state_code=state_code)\
.where(student_reg.c.academic_year == academic_year)
return academic_year_query
def select_with_context(columns=None,
whereclause=None,
from_obj=[],
permission=RolesConstants.PII,
**kwargs):
'''
Returns a SELECT clause statement with context security attached in the WHERE clause
Note: state_code must be passed in as kwargs for database routing for multi tenant users
'''
# Retrieve state code for db connection routing
state_code = kwargs.get(Constants.STATE_CODE)
kwargs.pop(Constants.STATE_CODE, None)
with EdCoreDBConnection(state_code=state_code) as connector:
# Get user role and guid
user = __get_user_info()
# Build query
query = Select(columns,
whereclause=whereclause,
from_obj=from_obj,
**kwargs)
if permission not in user.get_roles():
raise HTTPForbidden()
context = __get_context_instance(permission, connector)
# Get context security expression to attach to where clause
query = context.add_context(get_tenant_by_state_code(state_code), user,
query)
return query
def get_student_reg_academic_years(state_code, tenant=None):
with EdCoreDBConnection(tenant=tenant,
state_code=state_code) as connection:
student_reg = connection.get_table(Constants.STUDENT_REG)
query = select([distinct(student_reg.c.academic_year).label(Constants.ACADEMIC_YEAR)])\
.where(student_reg.c.state_code == state_code).order_by(student_reg.c.academic_year.desc())
results = connection.get_result(query)
return list(result[Constants.ACADEMIC_YEAR] for result in results)
class ReportingDbConnection():
'''
Used for routing between public and private dbs
'''
def __init__(self, tenant=None, state_code=None, is_public=False):
'''
:params str tenant: Name of tenant
:params str state_code name of state_code
:params bool is_public. True if we want to access de-identified datastore
'''
if is_public:
self.db_conn = PublicDBConnection(tenant=tenant, state_code=state_code)
else:
self.db_conn = EdCoreDBConnection(tenant=tenant, state_code=state_code)
def __enter__(self):
return self.db_conn
def __exit__(self, exc_type, value, tb):
self.db_conn.close_connection()
def _get_asmt_records_iab(param, extract_type):
'''
query all asmt_guid and asmt_grade by given extract request params
'''
asmt_type = param.get(Constants.ASMTTYPE)
asmt_subject = param.get(Constants.ASMTSUBJECT)
state_code = param.get(Constants.STATECODE)
district_id = param.get(Constants.DISTRICTGUID)
school_id = param.get(Constants.SCHOOLGUID)
asmt_grade = param.get(Constants.ASMTGRADE)
asmt_year = param.get(Constants.ASMTYEAR)
student_id = param.get(Constants.STUDENTGUID)
# TODO: remove dim_asmt
with EdCoreDBConnection(state_code=state_code) as connector:
dim_asmt = connector.get_table(Constants.DIM_ASMT)
fact_block_asmt_outcome = connector.get_table(
Constants.FACT_BLOCK_ASMT_OUTCOME)
dim_student = connector.get_table(Constants.DIM_STUDENT)
query = select_with_context([dim_asmt.c.asmt_guid.label(Constants.ASMT_GUID),
dim_asmt.c.effective_date.label(Constants.EFFECTIVE_DATE),
dim_asmt.c.asmt_claim_1_name.label(Constants.ASMT_CLAIM_1_NAME),
fact_block_asmt_outcome.c.asmt_grade.label(Constants.ASMT_GRADE)],
from_obj=[dim_asmt
.join(fact_block_asmt_outcome, and_(dim_asmt.c.asmt_rec_id == fact_block_asmt_outcome.c.asmt_rec_id))], permission=get_required_permission(extract_type), state_code=state_code)\
.where(and_(fact_block_asmt_outcome.c.state_code == state_code))\
.where(and_(fact_block_asmt_outcome.c.asmt_type == asmt_type))\
.where(and_(fact_block_asmt_outcome.c.asmt_subject == asmt_subject))\
.where(and_(fact_block_asmt_outcome.c.rec_status == Constants.CURRENT))\
.group_by(dim_asmt.c.asmt_guid, dim_asmt.c.effective_date, dim_asmt.c.asmt_claim_1_name, fact_block_asmt_outcome.c.asmt_grade)
if district_id is not None:
query = query.where(
and_(fact_block_asmt_outcome.c.district_id == district_id))
if school_id is not None:
query = query.where(
and_(fact_block_asmt_outcome.c.school_id == school_id))
if asmt_grade is not None:
query = query.where(
and_(fact_block_asmt_outcome.c.asmt_grade == asmt_grade))
if asmt_year is not None:
query = query.where(
and_(fact_block_asmt_outcome.c.asmt_year == asmt_year))
if student_id:
query = query.where(
and_(fact_block_asmt_outcome.c.student_id.in_(student_id)))
query = apply_filter_to_query(query, fact_block_asmt_outcome,
dim_student, param)
results = connector.get_result(query)
return results
def check_context(permission, state_code, student_ids):
'''
Given a list of student guids, return true if user has access to see their data, false otherwise
:param student_ids: guids of students that we want to check whether the user has context to
:type student_ids: list
'''
if not student_ids:
return False
with EdCoreDBConnection(state_code=state_code) as connector:
# Get user role and guid
user = __get_user_info()
context = __get_context_instance(permission, connector)
return context.check_context(get_tenant_by_state_code(state_code),
user, student_ids)
def generate_csv(tenant, output_file, task_info, extract_args):
"""
Write data extract to CSV file.
@param tenant: Requestor's tenant ID
@param output_file: File pathname of extract file
@param task_info: Task information for recording stats @param extract_args: Arguments specific to generate_csv
"""
query = extract_args[TaskConstants.TASK_QUERIES][QueryType.QUERY]
with EdCoreDBConnection(tenant=tenant) as connection:
results = connection.get_streaming_result(query) # this result is a generator
header, data = _generate_csv_data(results)
with open(output_file, 'w') as f:
write_csv(f, data, header=header)
insert_extract_stats(task_info, {Constants.STATUS: ExtractStatus.EXTRACTED})
def estimate_extract_total_file_size(params, avg_file_size, extract_type):
"""
returns an estimate of the number of extract files based on query params and extract type
@param params: Extract query params
"""
return_number = -1
state_code = params.get(Constants.STATECODE)
with EdCoreDBConnection(state_code=state_code) as connector:
query = get_extract_assessment_item_and_raw_count_query(
params, extract_type)
results = connector.get_result(query)
return_number = results[0][Constants.COUNT]
if return_number > 0:
if avg_file_size > 0:
return return_number * avg_file_size
return 0
def get_assessment_query(academic_year, state_code):
with EdCoreDBConnection(state_code=state_code) as connection:
student_reg = connection.get_table(Constants.STUDENT_REG)
asmt_outcome = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
assmt_query = select_with_context([asmt_outcome.c.student_id,
asmt_outcome.c.asmt_subject,
asmt_outcome.c.asmt_type,
asmt_outcome.c.asmt_year],
from_obj=[asmt_outcome],
permission=RolesConstants.SRC_EXTRACTS,
state_code=state_code)\
.distinct(asmt_outcome.c.student_id, asmt_outcome.c.asmt_subject, asmt_outcome.c.asmt_type)\
.where(and_(asmt_outcome.c.rec_status == Constants.CURRENT, asmt_outcome.c.asmt_year == academic_year))\
.alias(name=Constants.FACT_ASMT_OUTCOME_VW)
academic_year_query = select_with_context([student_reg.c.state_code,
student_reg.c.state_name,
student_reg.c.district_id,
student_reg.c.district_name,
student_reg.c.school_id,
student_reg.c.school_name,
student_reg.c.sex,
student_reg.c.enrl_grade,
student_reg.c.dmg_eth_hsp,
student_reg.c.dmg_eth_ami,
student_reg.c.dmg_eth_asn,
student_reg.c.dmg_eth_blk,
student_reg.c.dmg_eth_pcf,
student_reg.c.dmg_eth_wht,
student_reg.c.dmg_prg_iep,
student_reg.c.dmg_prg_lep,
student_reg.c.dmg_prg_504,
student_reg.c.dmg_sts_ecd,
student_reg.c.dmg_sts_mig,
student_reg.c.dmg_multi_race,
student_reg.c.academic_year,
assmt_query.c.asmt_subject,
assmt_query.c.asmt_type],
from_obj=[student_reg.join(assmt_query, student_reg.c.student_id == assmt_query.c.student_id)],
permission=RolesConstants.SRC_EXTRACTS,
state_code=state_code)\
.where(student_reg.c.academic_year == academic_year)
return academic_year_query
def get_asmt_administration(state_code,
district_id=None,
school_id=None,
asmt_grade=None,
student_ids=None,
asmt_year=None):
'''
Get asmt administration for a list of students. There is no PII in the results and it can be stored in shortlived cache
'''
with EdCoreDBConnection(state_code=state_code) as connection:
fact_asmt_outcome_vw = connection.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
dim_asmt = connection.get_table(Constants.DIM_ASMT)
query = select([
dim_asmt.c.asmt_period_year, fact_asmt_outcome_vw.c.asmt_type,
fact_asmt_outcome_vw.c.asmt_grade
],
from_obj=[fact_asmt_outcome_vw, dim_asmt])
query = query.where(fact_asmt_outcome_vw.c.asmt_rec_id == dim_asmt.c.asmt_rec_id).\
where(fact_asmt_outcome_vw.c.state_code == state_code).\
where(and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT)).\
where(and_(fact_asmt_outcome_vw.c.asmt_type.in_([AssessmentType.SUMMATIVE, AssessmentType.INTERIM_COMPREHENSIVE]))).\
group_by(dim_asmt.c.asmt_period_year, fact_asmt_outcome_vw.c.asmt_type, fact_asmt_outcome_vw.c.asmt_grade,).\
order_by(fact_asmt_outcome_vw.c.asmt_type.desc(), dim_asmt.c.asmt_period_year.desc())
if district_id:
query = query.where(
and_(fact_asmt_outcome_vw.c.district_id == district_id))
if school_id:
query = query.where(
and_(fact_asmt_outcome_vw.c.school_id == school_id))
if asmt_grade:
query = query.where(
and_(fact_asmt_outcome_vw.c.asmt_grade == asmt_grade))
if student_ids:
query = query.where(
and_(fact_asmt_outcome_vw.c.student_id.in_(student_ids))
) if isinstance(student_ids, list) else query.where(
and_(fact_asmt_outcome_vw.c.student_id == student_ids))
if asmt_year:
query = query.where(
and_(fact_asmt_outcome_vw.c.asmt_year == asmt_year))
results = connection.get_result(query)
return results
def get_match_id_query(academic_year, state_code):
with EdCoreDBConnection(state_code=state_code) as connection:
student_reg = connection.get_table(Constants.STUDENT_REG)
current_sr = student_reg.alias()
prev_sr = student_reg.alias()
match_id_query = select_with_context([current_sr.c.state_code, prev_sr.c.state_code.label('prev_state_code'), current_sr.c.state_name, current_sr.c.district_id,
prev_sr.c.district_id.label('prev_district_id'), current_sr.c.district_name, current_sr.c.school_id,
prev_sr.c.school_id.label('prev_school_id'), current_sr.c.school_name, current_sr.c.sex, prev_sr.c.sex.label('prev_sex'),
current_sr.c.enrl_grade, prev_sr.c.enrl_grade.label('prev_enrl_grade'), current_sr.c.dmg_eth_hsp, prev_sr.c.dmg_eth_hsp.label('prev_dmg_eth_hsp'),
current_sr.c.dmg_eth_ami, prev_sr.c.dmg_eth_ami.label('prev_dmg_eth_ami'), current_sr.c.dmg_eth_asn, prev_sr.c.dmg_eth_asn.label('prev_dmg_eth_asn'),
current_sr.c.dmg_eth_blk, prev_sr.c.dmg_eth_blk.label('prev_dmg_eth_blk'), current_sr.c.dmg_eth_pcf, prev_sr.c.dmg_eth_pcf.label('prev_dmg_eth_pcf'),
current_sr.c.dmg_eth_wht, prev_sr.c.dmg_eth_wht.label('prev_dmg_eth_wht'), current_sr.c.dmg_prg_iep,
prev_sr.c.dmg_prg_iep.label('prev_dmg_prg_iep'), current_sr.c.dmg_prg_lep, prev_sr.c.dmg_prg_lep.label('prev_dmg_prg_lep'),
current_sr.c.dmg_prg_504, prev_sr.c.dmg_prg_504.label('prev_dmg_prg_504'), current_sr.c.dmg_sts_ecd,
prev_sr.c.dmg_sts_ecd.label('prev_dmg_sts_ecd'), current_sr.c.dmg_sts_mig,
prev_sr.c.dmg_sts_mig.label('prev_dmg_sts_mig'), current_sr.c.dmg_multi_race, prev_sr.c.dmg_multi_race.label('prev_dmg_multi_race'), current_sr.c.academic_year],
from_obj=[current_sr.join(prev_sr, and_(current_sr.c.student_id == prev_sr.c.student_id))], permission=RolesConstants.SRS_EXTRACTS, state_code=state_code)\
.where(and_(current_sr.c.academic_year == academic_year, prev_sr.c.academic_year == academic_year - 1))
return match_id_query
def generate_raw_data_xml(tenant, output_paths, task_info, extract_args):
'''
Write raw xml data to output file
@param tenant: Requestor's tenant ID
@param output_paths: list of output path name's to place the selected raw data xml files
@param task_info: Task information for recording stats
@param extract_args: Arguments specific to generate_raw_data_xml
'''
query = extract_args[TaskConstants.TASK_QUERIES][QueryType.QUERY]
root_dir = extract_args[TaskConstants.ROOT_DIRECTORY]
if type(output_paths) is not list:
output_paths = [output_paths]
with EdCoreDBConnection(tenant=tenant) as connection:
# Get results (streamed, it is important to avoid memory exhaustion)
results = connection.get_streaming_result(query)
_copy_files(root_dir, results, output_paths)
# Done
insert_extract_stats(task_info,
{Constants.STATUS: ExtractStatus.EXTRACTED})
def get_extract_assessment_item_and_raw_query(params, extract_type):
"""
private method to generate SQLAlchemy object or sql code for extraction of
students for item level/raw data
:param params: for query parameters asmt_year, asmt_type, asmt_subject,
asmt_grade
:param extract_type: Type of extraction: Item Level, Raw Data Level,
Student Assessment
"""
state_code = params.get(Constants.STATECODE)
with EdCoreDBConnection(state_code=state_code) as connector:
dim_asmt = connector.get_table(Constants.DIM_ASMT)
fact_asmt_outcome_vw = connector.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
# TODO: Look at removing dim_asmt
query = select_with_context(
[
fact_asmt_outcome_vw.c.state_code,
fact_asmt_outcome_vw.c.asmt_year,
fact_asmt_outcome_vw.c.asmt_type, dim_asmt.c.effective_date,
fact_asmt_outcome_vw.c.asmt_subject,
fact_asmt_outcome_vw.c.asmt_grade,
fact_asmt_outcome_vw.c.district_id,
fact_asmt_outcome_vw.c.student_id
],
from_obj=[
fact_asmt_outcome_vw.join(
dim_asmt,
and_(dim_asmt.c.asmt_rec_id ==
fact_asmt_outcome_vw.c.asmt_rec_id))
],
permission=get_required_permission(extract_type),
state_code=state_code)
query = _assessment_item_and_raw_where_clause_builder(
query, fact_asmt_outcome_vw, params)
return query
def prepare_pre_cache(tenant, state_code, batch_guid):
'''
prepare which state and district are pre-cached
:param string tenant: name of the tenant
:param string state_code: stateCode representing the state
:param last_pre_cached: dateTime of the last precached
:rType: list
:return: list of results containing district guids
'''
with EdCoreDBConnection(tenant=tenant) as connector:
fact_asmt_outcome_vw = connector.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
fact_block_asmt_outcome = connector.get_table(
Constants.FACT_BLOCK_ASMT_OUTCOME)
query_fao = select([
distinct(fact_asmt_outcome_vw.c.district_id).label(
Constants.DISTRICT_ID)
],
from_obj=[fact_asmt_outcome_vw])
query_fao = query_fao.where(
fact_asmt_outcome_vw.c.state_code == state_code)
query_fao = query_fao.where(
and_(fact_asmt_outcome_vw.c.batch_guid == batch_guid))
query_fao = query_fao.where(
and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
query_fbao = select([
distinct(fact_block_asmt_outcome.c.district_id).label(
Constants.DISTRICT_ID)
],
from_obj=[fact_block_asmt_outcome])
query_fbao = query_fbao.where(
fact_block_asmt_outcome.c.state_code == state_code)
query_fbao = query_fbao.where(
and_(fact_block_asmt_outcome.c.batch_guid == batch_guid))
query_fbao = query_fbao.where(
and_(fact_block_asmt_outcome.c.rec_status == Constants.CURRENT))
results = connector.get_result(query_fao.union(query_fbao))
return results
def prepare_pre_pdf(tenant, state_code, batch_guid):
'''
prepare which state and district are pre-cached
:param string tenant: name of the tenant
:param string state_code: stateCode representing the state
:param string batch_guid: batch GUID
:rType: list
:return: list of results containing student information used to generate pdf
'''
with EdCoreDBConnection(tenant=tenant) as connector:
fact_asmt_outcome_vw = connector.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
dim_asmt = connector.get_table(Constants.DIM_ASMT)
query = select([
distinct(fact_asmt_outcome_vw.c.student_id).label(
Constants.STUDENT_ID),
dim_asmt.c.asmt_period_year.label(Constants.ASMT_PERIOD_YEAR),
fact_asmt_outcome_vw.c.date_taken.label(Constants.DATETAKEN),
dim_asmt.c.asmt_type.label(Constants.ASMT_TYPE),
fact_asmt_outcome_vw.c.district_id.label(Constants.DISTRICT_ID),
fact_asmt_outcome_vw.c.school_id.label(Constants.SCHOOL_ID),
fact_asmt_outcome_vw.c.asmt_grade.label(Constants.ASMT_GRADE)
],
from_obj=[
fact_asmt_outcome_vw.join(
dim_asmt,
and_(
dim_asmt.c.asmt_rec_id ==
fact_asmt_outcome_vw.c.asmt_rec_id,
dim_asmt.c.rec_status == Constants.CURRENT))
])
query = query.where(fact_asmt_outcome_vw.c.state_code == state_code)
query = query.where(
and_(fact_asmt_outcome_vw.c.batch_guid == batch_guid))
query = query.where(
and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
results = connector.get_result(query)
return results
def generate_json(tenant, output_file, task_info, extract_args):
"""
Write data extract to JSON file.
@param tenant: Requestor's tenant ID
@param output_file: File pathname of extract file
@param task_info: Task information for recording stats
@param extract_args: Arguments specific to generate_json
"""
query = extract_args[TaskConstants.TASK_QUERIES][QueryType.QUERY]
with EdCoreDBConnection(tenant=tenant) as connection, open(output_file, 'w') as json_file:
results = connection.get_result(query)
# There should only be one result in the list
if len(results) is 1:
formatted = format_json(results[0])
json.dump(formatted, json_file, indent=4)
insert_extract_stats(task_info, {Constants.STATUS: ExtractStatus.GENERATED_JSON})
else:
insert_extract_stats(task_info, {Constants.STATUS: ExtractStatus.FAILED, Constants.INFO: "Results length is: " + str(len(results))})
def get_district_level_context_names(tenant, state_code, district_id):
if state_code:
with EdCoreDBConnection(tenant=tenant,
state_code=state_code) as connector:
dim_inst_hier = connector.get_table(Constants.DIM_INST_HIER)
# Limit result count to one
# We limit the results to one since we'll get multiple rows with the same values
# Think of the case of querying for state name and id, we'll get all the schools in that state
query = select([
dim_inst_hier.c.state_code.label(Constants.STATE_CODE),
dim_inst_hier.c.district_id.label(Constants.DISTRICT_ID),
dim_inst_hier.c.district_name.label(Constants.DISTRICT_NAME),
dim_inst_hier.c.school_name.label(Constants.SCHOOL_NAME),
dim_inst_hier.c.school_id.label(Constants.SCHOOL_ID)
],
from_obj=[dim_inst_hier])
query = query.where(
and_(dim_inst_hier.c.rec_status == Constants.CURRENT))
query = query.where(and_(dim_inst_hier.c.state_code == state_code))
query = query.where(
and_(dim_inst_hier.c.district_id == district_id))
# run it and format the results
results = connector.get_result(query)
if results:
schools = {
r[Constants.SCHOOL_ID]: {
Constants.NAME: r[Constants.SCHOOL_NAME],
Constants.SCHOOLGUID: r[Constants.SCHOOL_ID]
}
for r in results
}
return {
Constants.NAME: results[0][Constants.DISTRICT_NAME],
'schools': schools
}
return {}
def get_block_asmt_administration(state_code,
district_id=None,
school_id=None,
asmt_grade=None,
student_ids=None,
asmt_year=None):
'''
Block assessment administration years
'''
with EdCoreDBConnection(state_code=state_code) as connection:
fact_block_asmt = connection.get_table(
Constants.FACT_BLOCK_ASMT_OUTCOME)
dim_asmt = connection.get_table(Constants.DIM_ASMT)
query = select(
[dim_asmt.c.asmt_period_year, fact_block_asmt.c.asmt_type],
from_obj=[fact_block_asmt, dim_asmt])
query = query.where(fact_block_asmt.c.asmt_rec_id == dim_asmt.c.asmt_rec_id).\
where(fact_block_asmt.c.state_code == state_code).\
where(and_(fact_block_asmt.c.asmt_type == AssessmentType.INTERIM_ASSESSMENT_BLOCKS)).\
where(and_(fact_block_asmt.c.rec_status == Constants.CURRENT)).\
group_by(dim_asmt.c.asmt_period_year, fact_block_asmt.c.asmt_type)
if district_id:
query = query.where(
and_(fact_block_asmt.c.district_id == district_id))
if school_id:
query = query.where(and_(fact_block_asmt.c.school_id == school_id))
if asmt_grade:
query = query.where(
and_(fact_block_asmt.c.asmt_grade == asmt_grade))
if student_ids:
query = query.where(
and_(fact_block_asmt.c.student_id.in_(student_ids))
) if isinstance(student_ids, list) else query.where(
and_(fact_block_asmt.c.student_id == student_ids))
if asmt_year:
query = query.where(and_(fact_block_asmt.c.asmt_year == asmt_year))
results = connection.get_result(query)
return results
def generate_items_csv(tenant, output_files, task_info, extract_args):
'''
Write item-level data to CSV file
@param tenant: Requestor's tenant ID
@param output_files: List of output file path's for item extract
@param task_info: Task information for recording stats
@param extract_args: Arguments specific to generate_items_csv
'''
# Get stuff
query = extract_args[TaskConstants.TASK_QUERIES][QueryType.QUERY]
items_root_dir = extract_args[TaskConstants.ROOT_DIRECTORY]
item_ids = extract_args[TaskConstants.ITEM_IDS]
with EdCoreDBConnection(tenant=tenant) as connection:
# Get results (streamed, it is important to avoid memory exhaustion)
results = connection.get_streaming_result(query, fetch_size=10240)
_append_csv_files(items_root_dir, item_ids, results, output_files,
CSV_HEADER)
# Done
insert_extract_stats(task_info,
{Constants.STATUS: ExtractStatus.EXTRACTED})
def get_summary_distribution(state_code,
district_id=None,
school_id=None,
asmt_type=AssessmentType.SUMMATIVE):
'''
Get a bucketed distribution of scores
'''
with EdCoreDBConnection(state_code=state_code) as connection:
fact_asmt_outcome_vw = connection.get_table('fact_asmt_outcome')
# should it be always for summative?
query = select([
label(Constants.SCORE_BUCKET,
(fact_asmt_outcome_vw.c.asmt_score / get_bucket_size()) *
get_bucket_size()),
count(
case([(fact_asmt_outcome_vw.c.asmt_subject
== Constants.MATH, 1)],
else_=0)).label(Constants.TOTAL_MATH),
count(
case([(fact_asmt_outcome_vw.c.asmt_subject == Constants.ELA, 1)
],
else_=0)).label(Constants.TOTAL_ELA)
],
from_obj=[fact_asmt_outcome_vw])
query = query.where(fact_asmt_outcome_vw.c.state_code == state_code)
query = query.where(fact_asmt_outcome_vw.c.asmt_type == asmt_type)
query = query.where(
fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT)
if (district_id is not None):
query = query.where(
fact_asmt_outcome_vw.c.district_id == district_id)
if (school_id is not None):
query = query.where(fact_asmt_outcome_vw.c.school_id == school_id)
query = query.group_by(Constants.SCORE_BUCKET).order_by(
Constants.SCORE_BUCKET)
return connection.get_result(query)
def _get_school_name(state_code, district_id, school_id):
with EdCoreDBConnection(state_code=state_code) as connector:
# Get handle to tables
dim_inst_hier = connector.get_table(Constants.DIM_INST_HIER)
# Build select
query = select(
[dim_inst_hier.c.school_name.label(Constants.SCHOOL_NAME)],
from_obj=[dim_inst_hier])
# Add where clauses
query = query.where(dim_inst_hier.c.state_code == state_code)
query = query.where(and_(dim_inst_hier.c.district_id == district_id))
query = query.where(and_(dim_inst_hier.c.school_id == school_id))
query = query.where(
and_(dim_inst_hier.c.rec_status == Constants.CURRENT))
# Return the result
results = connector.get_result(query)
if len(results) == 1:
return results[0][Constants.SCHOOL_NAME]
else:
logger.error('Bulk PDF : School name not found')
raise InvalidParameterError('School name cannot be found')
def _generate_completion_report_data(tenant, academic_year, queries):
"""
Get all the tenant's student registration completion data for the academic year.
@param tenant: Requestor's tenant ID
@param academic_year: Academic year of report
@param queries: DB queries to extract rows
@return: List of rows to be included in the CSV report.
"""
academic_year_query = queries[QueryType.QUERY]
assessment_outcome_query = queries[QueryType.ASMT_OUTCOME_QUERY]
row_data_processor = RowDataProcessor()
with EdCoreDBConnection(tenant=tenant) as connection:
registered_results = connection.get_streaming_result(academic_year_query) # This result is a generator
row_data_processor.process_yearly_row_data(registered_results)
assessment_outcome_results = connection.get_streaming_result(assessment_outcome_query)
row_data_processor.process_asmt_outcome_row_data(assessment_outcome_results)
return _get_sr_comp_tenant_data_for_academic_year(row_data_processor, academic_year)
def _generate_statistics_report_data(tenant, academic_year, queries):
"""
Get all the tenant's student registration statistics data for the academic year.
@param tenant: Requestor's tenant ID
@param academic_year: Academic year of report
@param queries: DB queries to extract rows
@return: List of rows to be included in the CSV report.
"""
academic_year_query = queries[QueryType.QUERY]
match_id_query = queries[QueryType.MATCH_ID_QUERY]
row_data_processor = RowDataProcessor()
with EdCoreDBConnection(tenant=tenant) as connection:
academic_year_results = connection.get_streaming_result(academic_year_query) # This result is a generator
row_data_processor.process_yearly_row_data(academic_year_results)
match_id_results = connection.get_streaming_result(match_id_query)
row_data_processor.process_matched_ids_row_data(match_id_results)
return _get_sr_stat_tenant_data_for_academic_year(row_data_processor, academic_year)
def test_get_datasource_name_without_tenant(self):
name = EdCoreDBConnection.get_datasource_name()
self.assertEquals(name, None)
def setUpClass(cls):
super().setUpClass(EdCoreDBConnection.get_datasource_name(get_unittest_tenant_name()))
def test_connector(self):
conn = EdCoreDBConnection(tenant=get_unittest_tenant_name())
self.assertIsInstance(conn, EdCoreDBConnection)
dim_student = conn.get_table('dim_student')
self.assertEqual(dim_student.name, 'dim_student')
def get_student_report(params):
'''
Individual Student Report
'''
student_id = params[Constants.STUDENTGUID]
state_code = params[Constants.STATECODE]
academic_year = params.get(Constants.ASMTYEAR)
asmt_type = params.get(Constants.ASMTTYPE)
asmt_type = asmt_type if asmt_type and asmt_type == AssessmentType.INTERIM_ASSESSMENT_BLOCKS else None
with EdCoreDBConnection(state_code=state_code) as connection:
# choose query IAB or other assessment
query_function = {
AssessmentType.INTERIM_ASSESSMENT_BLOCKS: __prepare_query_iab,
None: __prepare_query
}
# choose arrange results for the client IAB or other assessment
arrange_function = {
AssessmentType.INTERIM_ASSESSMENT_BLOCKS: __arrange_results_iab,
None: __arrange_results
}
query = query_function[asmt_type](connection, params)
result = connection.get_result(query)
if not result:
logger.error(
"Individual student report: there are no results for student id : %s",
student_id)
raise NotFoundException(
"There are no results for student id {0}".format(student_id))
records = [
record for record in result
if record['asmt_period_year'] == academic_year
]
first_student = records[0] if len(records) > 0 else result[0]
state_code = first_student[Constants.STATE_CODE]
district_id = first_student[Constants.DISTRICT_ID]
school_id = first_student[Constants.SCHOOL_ID]
asmt_grade = first_student['asmt_grade']
student_name = format_full_name(first_student['first_name'],
first_student['middle_name'],
first_student['last_name'])
context = get_breadcrumbs_context(state_code=state_code,
district_id=district_id,
school_id=school_id,
asmt_grade=asmt_grade,
student_name=student_name)
student_report_asmt_administration = get_asmt_administration_years_isr(
state_code, student_ids=student_id)
# color metadata
custom_metadata_map = get_custom_metadata(
result[0].get(Constants.STATE_CODE), None)
# subjects map
subjects_map = get_subjects_map()
result = arrange_function[asmt_type](result, subjects_map,
custom_metadata_map)
result['context'] = context
result[Constants.METADATA] = {
Constants.BRANDING: custom_metadata_map.get(Constants.BRANDING)
}
result[Constants.SUBJECTS] = {v: k for k, v in subjects_map.items()}
result['asmt_administration'] = student_report_asmt_administration
return result
def generate_isr_report_path_by_student_id(state_code,
date_taken=None,
asmt_year=None,
pdf_report_base_dir='/',
student_ids=None,
asmt_type=AssessmentType.SUMMATIVE,
grayScale=True,
lang='en'):
'''
Get Individual Student Report absolute path by student_id.
If the directory path does not exist, then create it.
For security, the directory will be created with only the owner can read-write.
'''
if date_taken is None and asmt_year is None:
raise AttributeError('Need one of date_taken or asmt_year')
file_paths = {}
if type(student_ids) is not list:
student_ids = [student_ids]
# find state_code, asmt_period_year, district_id, school_id, and asmt_grade from DB
with EdCoreDBConnection(state_code=state_code) as connection:
if asmt_type == AssessmentType.INTERIM_ASSESSMENT_BLOCKS:
query = generate_query_for_iab(connection, student_ids, asmt_year)
else:
query = generate_query_for_summative_or_interim(
connection, asmt_type, student_ids, asmt_year, date_taken)
results = connection.get_result(query)
if len(results) < len(student_ids):
logger.error(
"PDF error: Results count is less than student count.")
raise NotFoundException(
"Result count should be more than or equal to student count")
for result in results:
file_path_by_date = {}
student_id = result[Constants.STUDENT_ID]
state_code = result[Constants.STATE_CODE]
asmt_period_year = str(result[Constants.ASMT_PERIOD_YEAR])
date_taken = str(result[Constants.DATETAKEN]) if result.get(
Constants.DATETAKEN) is not None else None
district_id = result[Constants.DISTRICT_ID]
school_id = result[Constants.SCHOOL_ID]
asmt_grade = result.get(Constants.ASMT_GRADE)
file_path = generate_isr_absolute_file_path_name(
pdf_report_base_dir=pdf_report_base_dir,
state_code=state_code,
asmt_period_year=asmt_period_year,
district_id=district_id,
school_id=school_id,
asmt_grade=asmt_grade,
student_id=student_id,
asmt_type=asmt_type,
grayScale=grayScale,
lang=lang,
date_taken=date_taken)
# get absolute file path name
file_path_by_date[date_taken] = file_path
if student_id in file_paths:
file_path_by_date.update(file_paths[student_id])
file_paths[student_id] = file_path_by_date
return file_paths
def get_asmt_metadata(state_code, asmt_guid):
'''
Generates a query for getting assessment information based on assessment guid
:param str asmt_guid: asessment guid
'''
with EdCoreDBConnection(state_code=state_code) as connector:
dim_asmt = connector.get_table(Constants.DIM_ASMT)
mapping = get_column_mapping(Constants.DIM_ASMT, json_mapping=True)
query = select([
literal("assessment").label("content"),
dim_asmt.c.asmt_guid.label(mapping.get('asmt_guid', 'asmt_guid')),
dim_asmt.c.asmt_type.label(mapping.get('asmt_type', 'asmt_type')),
dim_asmt.c.asmt_period_year.label(
mapping.get('asmt_period_year', 'asmt_period_year')),
dim_asmt.c.asmt_period.label(
mapping.get('asmt_period', 'asmt_period')),
dim_asmt.c.asmt_version.label(
mapping.get('asmt_version', 'asmt_version')),
dim_asmt.c.asmt_subject.label(
mapping.get('asmt_subject', 'asmt_subject')),
dim_asmt.c.effective_date.label(
mapping.get('effective_date', 'effective_date')),
dim_asmt.c.asmt_score_min.label(
mapping.get('asmt_score_min', 'asmt_score_min')),
dim_asmt.c.asmt_score_max.label(
mapping.get('asmt_score_max', 'asmt_score_max')),
dim_asmt.c.asmt_perf_lvl_name_1.label(
mapping.get('asmt_perf_lvl_name_1', 'asmt_perf_lvl_name_1')),
dim_asmt.c.asmt_perf_lvl_name_2.label(
mapping.get('asmt_perf_lvl_name_2', 'asmt_perf_lvl_name_2')),
dim_asmt.c.asmt_perf_lvl_name_3.label(
mapping.get('asmt_perf_lvl_name_3', 'asmt_perf_lvl_name_3')),
dim_asmt.c.asmt_perf_lvl_name_4.label(
mapping.get('asmt_perf_lvl_name_4', 'asmt_perf_lvl_name_4')),
dim_asmt.c.asmt_perf_lvl_name_5.label(
mapping.get('asmt_perf_lvl_name_5', 'asmt_perf_lvl_name_5')),
dim_asmt.c.asmt_cut_point_1.label(
mapping.get('asmt_cut_point_1', 'asmt_cut_point_1')),
dim_asmt.c.asmt_cut_point_2.label(
mapping.get('asmt_cut_point_2', 'asmt_cut_point_2')),
dim_asmt.c.asmt_cut_point_3.label(
mapping.get('asmt_cut_point_3', 'asmt_cut_point_3')),
dim_asmt.c.asmt_cut_point_4.label(
mapping.get('asmt_cut_point_4', 'asmt_cut_point_4')),
dim_asmt.c.asmt_claim_1_name.label(
mapping.get('asmt_claim_1_name', 'asmt_claim_1_name')),
dim_asmt.c.asmt_claim_2_name.label(
mapping.get('asmt_claim_2_name', 'asmt_claim_2_name')),
dim_asmt.c.asmt_claim_3_name.label(
mapping.get('asmt_claim_3_name', 'asmt_claim_3_name')),
dim_asmt.c.asmt_claim_4_name.label(
mapping.get('asmt_claim_4_name', 'asmt_claim_4_name')),
dim_asmt.c.asmt_claim_1_score_min.label(
mapping.get('asmt_claim_1_score_min',
'asmt_claim_1_score_min')),
dim_asmt.c.asmt_claim_2_score_min.label(
mapping.get('asmt_claim_2_score_min',
'asmt_claim_2_score_min')),
dim_asmt.c.asmt_claim_3_score_min.label(
mapping.get('asmt_claim_3_score_min',
'asmt_claim_3_score_min')),
dim_asmt.c.asmt_claim_4_score_min.label(
mapping.get('asmt_claim_4_score_min',
'asmt_claim_4_score_min')),
dim_asmt.c.asmt_claim_1_score_max.label(
mapping.get('asmt_claim_1_score_max',
'asmt_claim_1_score_max')),
dim_asmt.c.asmt_claim_2_score_max.label(
mapping.get('asmt_claim_2_score_max',
'asmt_claim_2_score_max')),
dim_asmt.c.asmt_claim_3_score_max.label(
mapping.get('asmt_claim_3_score_max',
'asmt_claim_3_score_max')),
dim_asmt.c.asmt_claim_4_score_max.label(
mapping.get('asmt_claim_4_score_max',
'asmt_claim_4_score_max')),
dim_asmt.c.asmt_claim_perf_lvl_name_1.label(
mapping.get('asmt_claim_perf_lvl_name_1',
'asmt_claim_perf_lvl_name_1')),
dim_asmt.c.asmt_claim_perf_lvl_name_2.label(
mapping.get('asmt_claim_perf_lvl_name_2',
'asmt_claim_perf_lvl_name_2')),
dim_asmt.c.asmt_claim_perf_lvl_name_3.label(
mapping.get('asmt_claim_perf_lvl_name_3',
'asmt_claim_perf_lvl_name_3'))
],
from_obj=[dim_asmt])
query = query.where(
and_(dim_asmt.c.asmt_guid == asmt_guid,
dim_asmt.c.rec_status == Constants.CURRENT))
return query
def setUpClass(cls, datasource_name=None, metadata=None, resources_dir=None, use_metadata_from_db=True):
if datasource_name is None:
datasource_name = EdCoreDBConnection.get_datasource_name(get_unittest_tenant_name())
super().setUpClass(datasource_name=datasource_name, metadata=metadata,
resources_dir=resources_dir, use_metadata_from_db=use_metadata_from_db)
def test_connector_with_one_tenant(self):
conn = EdCoreDBConnection()
self.assertIsInstance(conn, EdCoreDBConnection)
dim_student = conn.get_table('dim_student')
self.assertEqual(dim_student.name, 'dim_student')
def test_generate_metadata(self):
metadata = EdCoreDBConnection.generate_metadata()
self.assertIsNotNone(metadata)
def test_get_db_config_prefix(self):
name = EdCoreDBConnection.get_db_config_prefix('dummy')
self.assertEqual(name, EdCoreDBConnection.CONFIG_NAMESPACE + '.dummy.')
def test_get_datasource_name(self):
name = EdCoreDBConnection.get_datasource_name('dummy')
self.assertEqual(name, EdCoreDBConnection.CONFIG_NAMESPACE + '.dummy')