def generate_query_for_summative_or_interim(connection, asmt_type, student_ids,
asmt_year, date_taken):
fact_table = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
dim_asmt = connection.get_table(Constants.DIM_ASMT)
query = Select(
[
distinct(fact_table.c.student_id).label(Constants.STUDENT_ID),
fact_table.c.state_code.label(Constants.STATE_CODE),
dim_asmt.c.asmt_period_year.label(Constants.ASMT_PERIOD_YEAR),
fact_table.c.date_taken.label(Constants.DATETAKEN),
fact_table.c.district_id.label(Constants.DISTRICT_ID),
fact_table.c.school_id.label(Constants.SCHOOL_ID),
fact_table.c.asmt_grade.label(Constants.ASMT_GRADE)
],
from_obj=[
fact_table.join(
dim_asmt,
and_(dim_asmt.c.asmt_rec_id == fact_table.c.asmt_rec_id,
dim_asmt.c.rec_status == Constants.CURRENT,
dim_asmt.c.asmt_type == asmt_type,
dim_asmt.c.asmt_period_year == asmt_year))
])
query = query.where(
and_(fact_table.c.rec_status == Constants.CURRENT,
fact_table.c.student_id.in_(student_ids)))
query = query.order_by(fact_table.c.student_id, fact_table.c.date_taken)
if date_taken is not None:
query = query.where(and_(fact_table.c.date_taken == date_taken))
return query
def generate_query_for_iab(connection, student_ids, asmt_year):
fact_table = connection.get_table(Constants.FACT_BLOCK_ASMT_OUTCOME)
dim_asmt = connection.get_table(Constants.DIM_ASMT)
query = Select(
[
distinct(fact_table.c.student_id).label(Constants.STUDENT_ID),
fact_table.c.state_code.label(Constants.STATE_CODE),
dim_asmt.c.asmt_period_year.label(Constants.ASMT_PERIOD_YEAR),
fact_table.c.district_id.label(Constants.DISTRICT_ID),
fact_table.c.school_id.label(Constants.SCHOOL_ID)
],
from_obj=[
fact_table.join(
dim_asmt,
and_(
dim_asmt.c.asmt_rec_id == fact_table.c.asmt_rec_id,
dim_asmt.c.rec_status == Constants.CURRENT,
dim_asmt.c.asmt_type ==
AssessmentType.INTERIM_ASSESSMENT_BLOCKS,
dim_asmt.c.asmt_period_year == asmt_year))
])
query = query.where(
and_(fact_table.c.rec_status == Constants.CURRENT,
fact_table.c.student_id.in_(student_ids)))
return 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_assessments(asmtGuid):
'''Query an assessment batch by assessment guid. Assessments in the
batch are organized as a list with each assessment row as an item.
:param: assessment guid
:return: list of all assessment guids
:return: list of all assessment data
:return: list of column names in the same order as assessment data
'''
with TSBDBConnection() as conn:
tsb_asmt = conn.get_table(Constants.TSB_ASMT)
columns = []
data = []
guids = []
query = Select([tsb_asmt]).where(tsb_asmt.c.AssessmentGuid == asmtGuid)
assessments = conn.get_streaming_result(query)
for i, asmt in enumerate(assessments):
row = []
for j, (column, value) in enumerate(asmt.items()):
if i == 0 and j > 0: # first column is guid
columns.append(column)
if j == 0:
guids.append(value)
else:
row.append(value)
data.append(row)
return guids, data, columns
async def search_by_tag(
tag: List[str] = Query(..., min_length=1),
db: Database = Depends(get_db),
subject_type: SubjectTypeEnum = None,
limit: int = Query(20, le=50, ge=0),
offset: int = Query(0, ge=0),
):
if subject_type is not None:
where = and_(sa.Subject.locked == 0,
sa.Subject.subject_type == subject_type.value)
else:
where = sa.Subject.locked == 0
query: Select = Select([sa.Subject], whereclause=where).order_by(
sa.Subject.id).limit(limit).offset(offset)
for i, t in enumerate(tag):
alias = aliased(sa.Tag, name=f"table_tag_{i}")
query = query.select_from(
sa.join(
query.froms[0],
alias,
and_(alias.subject_id == sa.Subject.id, alias.text == t),
))
return {
"limit": limit,
"offset": offset,
"count": 0,
"subjects": await db.fetch_all(query),
}
def get_all_assessment_guids():
'''
Get all unique assessment guids from `Constants.TSB_METADATA` and `Constants.TSB_ERROR` tables.
If a TSB request being processed successfully, a metadata record will be saved to `Constants.TSB_METADATA`,
while an error record will be saved to `Constants.TSB_ERROR` in case of a request failed. This is the
reason that this function need to look into both tables.
'''
with TSBDBConnection() as conn:
# query guids from metadata table
tsb_metadata = conn.get_table(Constants.TSB_METADATA)
query_metadata = Select(
[tsb_metadata.c.state_code, tsb_metadata.c.asmt_guid])
# query guids from error message table
tsb_error = conn.get_table(Constants.TSB_ERROR)
query_error = Select([tsb_error.c.state_code, tsb_error.c.asmt_guid])
return conn.execute(union(query_metadata, query_error)).fetchall()
def test_query_column_name(self):
# test for bug: http://groups.google.com/group/geoalchemy/browse_thread/thread/6b731dd1673784f9
from sqlalchemy.orm.query import Query
query = Query(Road.road_geom).filter(Road.road_geom == '..').__str__()
ok_('AsBinary(roads.road_geom)' in query,
'table name is part of the column expression (select clause)')
ok_('WHERE Equals(roads.road_geom' in query,
'table name is part of the column expression (where clause)')
query_wkb = Select([Road.road_geom
]).where(Road.road_geom == 'POINT(0 0)').__str__()
ok_('SELECT AsBinary(roads.road_geom)' in query_wkb,
'AsBinary is added')
ok_('WHERE Equals(roads.road_geom' in query_wkb,
'AsBinary is not added in where clause')
# test for RAW attribute
query_wkb = Select([Road.road_geom.RAW]).__str__()
ok_('SELECT roads.road_geom' in query_wkb, 'AsBinary is not added')
ok_(session.query(Road.road_geom.RAW).first())
query_srid = Query(func.SRID(Road.road_geom.RAW))
ok_('SRID(roads.road_geom)' in query_srid.__str__(),
'AsBinary is not added')
ok_(session.scalar(query_srid))
eq_(
session.scalar(
Select([func.SRID(Spot.spot_location)
]).where(Spot.spot_id == 1)), None,
'AsBinary is added and the SRID is not returned')
eq_(
str(
session.scalar(
Select([func.SRID(Spot.spot_location.RAW)
]).where(Spot.spot_id == 1))), '4326',
'AsBinary is not added and the SRID is returned')
spot_alias = aliased(Spot)
query_wkt = Select([func.wkt(spot_alias.spot_location.RAW)]).__str__()
ok_('SELECT wkt(spots_1.spot_location' in query_wkt,
'Table alias is used in select clause')
ok_('FROM spots AS spots_1' in query_wkt,
'Table alias is used in from clause')
def get_metadata(conn, asmtGuid):
'''
Get assessment metadata by assessment guid. The assessment guid is passed in from XML request.
'''
tsb_metadata = conn.get_table(Constants.TSB_METADATA)
query = Select([
tsb_metadata
]).where(tsb_metadata.c.asmt_guid == asmtGuid).with_for_update()
return conn.get_result(query)
def test_get_selectable_by_table_name_for_single_table_query(self):
'''
test get selectable tables names from query
'''
query = Select([
self.__test_fact_table.c.fact_asmt_outcome_vw_rec_id,
self.__test_fact_table.c.inst_hier_rec_id
],
from_obj=self.__test_fact_table)
self.assertEquals({'fact_asmt_outcome_vw'},
set(get_selectable_by_table_name(query).values()))
def test_get_selectable_by_table_name_for_multiple_table_join_query(self):
'''
test get selectable tables names from query
'''
query = Select([
self.__test_fact_table.c.fact_asmt_outcome_vw_rec_id,
self.__test_dim_table.c.inst_hier_rec_id
],
from_obj=self.__test_fact_table.join(
self.__test_dim_table,
and_(self.__test_fact_table.c.inst_hier_rec_id ==
self.__test_dim_table.c.inst_hier_rec_id)))
self.assertEquals({'fact_asmt_outcome_vw', 'dim_inst_hier'},
set(get_selectable_by_table_name(query).values()))
def generate_select(self) -> Select:
table = self.get_table()
for child_table, condition, is_outer in self.joins:
if is_outer:
table = table.outerjoin(child_table, condition)
else:
table = table.join(child_table, condition)
select = Select(
columns=self.columns,
whereclause=self.where,
from_obj=table,
limit=self.limit,
offset=self.offset,
group_by=self.group_by or None,
)
return select
def get_error_message(asmtGuid):
'''
Get all error message within a batch by assessment guid.
:param: assessment guid
:return: list of error record guids
:return: list of errors
'''
with TSBDBConnection() as conn:
tsb_error = conn.get_table(Constants.TSB_ERROR)
error_info = build_error_info_header()
query = Select([tsb_error]).where(tsb_error.c.asmt_guid == asmtGuid)
errors = conn.get_streaming_result(query)
error_guids = []
for error in errors:
error_guids.append(error[Constants.TSB_ERROR_GUID])
err_list = build_err_list_from_object(error)
error_info[ErrorsConstants.TSB_ERROR].append(err_list)
return error_guids, error_info