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