def where_latest_partition( # pylint: disable=too-many-arguments
cls,
table_name: str,
schema: Optional[str],
database: "Database",
query: Select,
columns: Optional[List[Dict[str, str]]] = None,
) -> Optional[Select]:
try:
col_names, values = cls.latest_partition(table_name,
schema,
database,
show_first=True)
except Exception: # pylint: disable=broad-except
# table is not partitioned
return None
if values is None:
return None
column_type_by_name = {
column.get("name"): column.get("type")
for column in columns or []
}
for col_name, value in zip(col_names, values):
if col_name in column_type_by_name:
if column_type_by_name.get(col_name) == "TIMESTAMP":
query = query.where(Column(col_name, TimeStamp()) == value)
elif column_type_by_name.get(col_name) == "DATE":
query = query.where(Column(col_name, Date()) == value)
else:
query = query.where(Column(col_name) == value)
return query
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 where_latest_partition( # pylint: disable=too-many-arguments
cls,
table_name: str,
schema: Optional[str],
database,
query: Select,
columns: Optional[List] = None,
) -> Optional[Select]:
try:
col_names, values = cls.latest_partition(table_name,
schema,
database,
show_first=True)
except Exception: # pylint: disable=broad-except
# table is not partitioned
return None
if values is None:
return None
column_names = {column.get("name") for column in columns or []}
for col_name, value in zip(col_names, values):
if col_name in column_names:
query = query.where(Column(col_name) == value)
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 apply_filters(self, q: Select) -> Select:
for col, val in self.filters:
if is_listish(val):
q = q.where(self.get_column(col).in_(val))
else:
q = q.where(self.get_column(col) == val)
for col, val in self.filters_not:
if is_listish(val):
q = q.where(self.get_column(col).notin_(val))
else:
q = q.where(self.get_column(col) != val)
# not sure this is a great idea:
# if self.data.get('where'):
# q = q.where(sql_text(self.data.get('where')))
for join in self.joins:
left = self.get_column(join.get("left"))
right = self.get_column(join.get("right"))
q = q.where(left == right)
return q
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 apply_search(cls, statement: Select, query: str) -> Select:
looking_for = f"%{query}%"
statement = statement.where(
or_(
cls.username_lower.ilike(
looking_for), # type: ignore[attr-defined]
cls.email_lower.ilike(
looking_for), # type: ignore[attr-defined]
cls.student_id.ilike(
looking_for), # type: ignore[attr-defined]
cls.real_name.ilike(looking_for), # type: ignore[attr-defined]
))
return statement
def follow_conditions(self, query: Select) -> Select:
"Append WHERE clauses to restrict the rows depending on the data already extracted"
foreign_key: Column
primary_key: Column
# For each foreign_key, build a where clause in the form of:
# WHERE sometable_id in (<select id from sometable>)
# The subquery is executed on the key database and its results are
# parametrized into the final query.
for foreign_key in self.foreign_keys:
primary_key = iter(foreign_key.foreign_keys).__next__().column
pk_data = self.key_database.execute_return_list(
select([primary_key]))
source_fk = getattr(self.source_table.c, foreign_key.name)
if pk_data:
query = query.where(
source_fk.in_(pk_data) | source_fk.is_(None)).order_by(
source_fk.nullslast())
else:
query = query.where(source_fk.is_(None))
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 where_latest_partition( # pylint: disable=too-many-arguments
cls,
table_name: str,
schema: Optional[str],
database: "Database",
query: Select,
columns: Optional[List[Dict[str, str]]] = None,
) -> Optional[Select]:
try:
col_names, values = cls.latest_partition(
table_name, schema, database, show_first=True
)
except Exception: # pylint: disable=broad-except
# table is not partitioned
return None
if values is not None and columns is not None:
for col_name, value in zip(col_names, values):
for clm in columns:
if clm.get("name") == col_name:
query = query.where(Column(col_name) == value)
return query
return None
def where_latest_partition(
cls,
table_name: str,
schema: Optional[str],
database,
qry: Select,
columns: Optional[List] = None,
) -> Optional[Select]:
try:
col_names, values = cls.latest_partition(table_name,
schema,
database,
show_first=True)
except Exception:
# table is not partitioned
return None
if values is not None and columns is not None:
for col_name, value in zip(col_names, values):
for c in columns:
if c.get("name") == col_name:
qry = qry.where(Column(col_name) == value)
return qry
return None
def where_latest_partition(
cls,
table_name: str,
schema: str,
database,
query: Select,
columns: Optional[List] = None,
) -> Optional[Select]:
try:
col_names, values = cls.latest_partition(
table_name, schema, database, show_first=True
)
except Exception:
# table is not partitioned
return False
if values is None:
return False
column_names = {column.get("name") for column in columns or []}
for col_name, value in zip(col_names, values):
if col_name in column_names:
query = query.where(Column(col_name) == value)
return query