def __init__(self, Session, table, select, **kw):
self.table = table
self.select = select
self.parent = None
self.child = None
self._bind = Session.bind
self._whereclause = select._whereclause
self.fk_type = None
self.reverse = kw.pop('reverse', False)
# we need to find the relation within the same table
for ev in self.table.foreign_keys:
if ev.column.table.name == ev.parent.table.name:
if not self.reverse:
self.parent = ev.parent.name
self.child = ev.column.name
else:
self.parent = ev.column.name
self.child = ev.parent.name
break
if self.parent is None or self.child is None:
raise MissingForeignKeyError(self.table.name)
self.starting_node = kw.pop('starting_node', None)
self.ordering_colname = kw.pop('ordering_colname', 'ordering')
# if starting node does not exist or it's null, we add starting_node=0
# by default
if not hasattr(self, 'starting_node') or self.starting_node is None:
self.fk_type = self.table.columns.get(self.parent).type._type_affinity
# sqlalchemy cast "0" as 0 (I don't know how to fix it) so you will
# get errors if the parent-child relation is not an integer. We
# identify this situation and use "a" for comparison
if self.fk_type == String:
setattr(self, 'starting_node', "a")
self.type_length = self.table.columns.get(self.parent).type.length
else:
setattr(self, 'starting_node', "0")
elif self.starting_node is False:
pass
else:
# we need to be sure the starting_node value is an String,
# otherwise we might get an error
self.starting_node = str(self.starting_node)
columns = select.columns + [
ColumnClause('level', type_=Integer()),
ColumnClause('connect_path', type_=ARRAY(self.fk_type)),
ColumnClause('is_leaf', type_=Boolean())
]
if self.ordering_colname in select.columns:
columns.append(ColumnClause('%s_path' % self.ordering_colname,
type_=ARRAY(select.c[self.ordering_colname].type)))
Select.__init__(self, columns, **kw)
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 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: "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( # 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 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
