def __get_a10_2018_targets_from_view(self, descr_obj, ok_ges_ids, muids): t = sql2018.t_V_ART10_Targets_2018 descriptor = descr_obj.id sess = db.session() q = sess.query(t).filter(t.c.MarineReportingUnit.in_(muids)) ges_filtered = [] for row in q: ges_comps = getattr(row, 'GESComponents', ()) ges_comps = set([g.strip() for g in ges_comps.split(',')]) if ges_comps.intersection(ok_ges_ids): ges_filtered.append(row) if descriptor.startswith('D1.'): feature_filtered = [] ok_features = set([f.name for f in get_features(descriptor)]) for row in ges_filtered: feats = set(row.Features.split(',')) if feats.intersection(ok_features): feature_filtered.append(row) ges_filtered = feature_filtered return ges_filtered
def __get_a10_2012_targets(self, ok_ges_ids, muids): sess = db.session() T = sql.MSFD10Target dt = sql.t_MSFD10_DESCrit D_q = sess.query(dt).join(T) D_a = aliased(dt, alias=D_q.subquery()) targets = sess\ .query(T)\ .order_by(T.ReportingFeature)\ .filter(T.MarineUnitID.in_(muids))\ .filter(T.Topic == 'EnvironmentalTarget')\ .join(D_a)\ .filter(D_a.c.GESDescriptorsCriteriaIndicators.in_(ok_ges_ids))\ .distinct()\ .all() res = [ Target( r.ReportingFeature.replace(' ', '_').lower(), r.ReportingFeature, r.Description, '2012') for r in targets ] # sort Targets and make them distinct res_sorted = sorted(set(res), key=lambda _x: natural_sort_key(_x.id)) return res_sorted
def get_ges_extent_data(self): sess = db.session() rep_info_mem = sql.MSFD13ReportingInfoMemberState rep_info = sql.MSFD13ReportingInfo info = sql.MSFD13MeasuresInfo columns = [ rep_info_mem.MemberState, info.InfoType, info.InfoText, rep_info.ReportingDate ] country_codes = self.country_code if not hasattr(country_codes, '__iter__'): country_codes = [country_codes] conditions = [ rep_info_mem.MemberState.in_(country_codes), rep_info.ReportType == 'Exceptions', info.InfoType == 'RelevantGESDescriptors', ] res = sess.query(*columns) \ .join(info, rep_info_mem.ReportID == info.ReportID) \ .join(rep_info, rep_info.ID == rep_info_mem.ReportID) \ .filter(*conditions).distinct() return res
def items(cls, descriptor, muids): sess = db.session() N = cls.primary_mapper A = cls.asses_mapper AI = cls.asses_ind_mapper AC = cls.asses_crit_mapper pk = cls._get_mapper_pk(N) print 'Started to query data!' filters = [] filters.append(N.MarineUnitID.in_(muids)) # TODO should we filter by cls.param_topics? filters.append(N.Topic != 'InfoGaps') # TODO should we filter by crit_topics? crit_topics = getattr(cls, 'crit_topics', None) if crit_topics: filters.append(A.Topic.in_(crit_topics)) # Acidification does not have '_Assessment' tables if A: q = sess.query(N, A, AI, AC)\ .select_from(N)\ .filter(*filters)\ .outerjoin(A)\ .outerjoin(AI)\ .outerjoin(AC)\ .order_by(pk) print 'Started to setup data!' print q.count() for tup in q: print 'Will yield one item!' yield cls(descriptor, *tup) # special case for Acidification else: q = sess.query(N) \ .select_from(N) \ .filter(*filters) \ .order_by(pk) print 'Started to setup data!' print q.count() for tup in q: print 'Will yield one item!' yield cls(descriptor, *(tup, None, None, None))
def get_monitored_elements(countryids): MS = sql.MSFD11MONSub EM = sql.MSFD11Q9aElementMonitored SP = sql.MSFD11SubProgramme sess = db.session() q = sess.query(EM)\ .filter(EM.SubProgramme == SP.ID)\ .filter(SP.ID == MS.SubProgramme)\ .filter(MS.MemberState.in_(countryids)) return q.all()
def __get_a10_2018_targets_from_table(self, ok_ges_ids, muids): T = sql2018.ART10TargetsTarget MU = sql2018.ART10TargetsMarineUnit t_MRU = T.ART10_Targets_MarineUnit G = sql2018.ART10TargetsTargetGESComponent sess = db.session() q = sess \ .query(T) \ .filter(t_MRU.has(MU.MarineReportingUnit.in_(muids))) \ .join(G) \ .filter(G.GESComponent.in_(ok_ges_ids)) res = [x for x in q] return res
def get_data_from_view_Art8(self): sess = db.session() t = sql2018.t_V_ART8_GES_2018 conditions = [ t.c.Region.in_(self._countryregion_folder._subregions), t.c.GESComponent.in_(self.all_descriptor_ids), or_(t.c.Element.isnot(None), t.c.Criteria.isnot(None)), ] # groupby IndicatorCode q = sess\ .query(t)\ .filter(*conditions)\ .distinct() res = [row for row in q] return res
def _related_activities(self, rec): # return [] sess = db.session() # TODO: concurent ongoing sessions # will be a problem? A = self.activity_mapper AD = self.act_descr_mapper fk = self._get_mapper_fk(AD) pk = self._get_mapper_pk(rec) res = sess.query(A.Activity) \ .join(AD) \ .filter(fk == pk) \ .distinct() \ .all() related_activities = res and res[0] or [] return related_activities
def _muids_2018(country, region): # this method needs "raw" access because the shapefile column slows things t = sql2018.MarineReportingUnit sess = db.session() q = sess\ .query(t.MarineReportingUnitId, t.nameTxtInt, t.Description)\ .filter( t.CountryCode == country, t.Region == region, # t.MarineReportingUnitId.isnot(None), # t.localId.isnot(None), # TODO: this suits NL, check others ) res = [MarineReportingUnit(m.MarineReportingUnitId, m.nameTxtInt or m.Description) for m in q] return sorted(res)
def get_features_pressures_data(self): sess = db.session() esa_mru = sql2018.ART8ESAMarineUnit rep_info = sql2018.ReportedInformation esa_feat = sql2018.ART8ESAFeature esa_uses = sql2018.ART8ESAUsesActivity esa_uses_p = sql2018.ART8ESAUsesActivitiesPressure columns = [ rep_info.CountryCode, esa_mru.MarineReportingUnit, esa_feat.Feature, esa_uses_p.PressureCode ] country_codes = self.country_code if not hasattr(country_codes, '__iter__'): country_codes = [country_codes] conditions = [ rep_info.Schema == 'ART8_ESA', rep_info.CountryCode.in_(country_codes) ] res = sess.query(*columns) \ .join(rep_info, esa_mru.IdReportedInformation == rep_info.Id) \ .join(esa_feat, esa_feat.IdMarineUnit == esa_mru.Id) \ .join(esa_uses, esa_uses.IdFeature == esa_feat.Id) \ .join(esa_uses_p, esa_uses_p.IdUsesActivities == esa_uses.Id) \ .filter(*conditions).distinct() out = defaultdict(set) self.features_pressures_data = [x for x in res] for row in res: activ_feat = row.Feature press_code = row.PressureCode out[activ_feat].add(press_code) return out
def _query_mru(self): sess = db.session() return sess.query(sql2018.MarineReportingUnit).options(defer('SHAPE'))
def items(cls, descriptor, muids): sess = db.session() N = cls.primary_mapper P = cls.pres_mapper A = cls.ast_mapper I = cls.indic_mapper C = cls.crit_mapper # M = cls.metadata_table pk = cls._get_mapper_pk(N) # fk = cls._get_table_fk(M) # m_q = sess.query(M) \ # .join(N, pk == fk) \ # .filter(M.c.Topic == 'Assessment', # M.c.MarineUnitID.in_(muids))\ # .distinct() print 'Started to query data!' if P: # .filter(A.Topic == cls.ast_topic) a_q = sess.query(A).join(N).subquery() a_A = aliased(A, alias=a_q, adapt_on_names=True) # .filter(A.Topic.in_(cls.criteria_types))\ c_q = sess.query(C).join(A).subquery() c_A = aliased(C, alias=c_q, adapt_on_names=True) # TODO how to filter by topics # .filter(N.Topic.in_(cls.ast_topic + cls.criteria_types))\ q = sess.query(N, P, a_A, I, c_A)\ .select_from(N) \ .filter(N.Topic != 'InfoGaps')\ .filter(N.MarineUnitID.in_(muids)) \ .outerjoin(a_A)\ .outerjoin(I)\ .outerjoin(c_A)\ .outerjoin(P) \ .distinct()\ .order_by(N.ReportingFeature, pk) # asd = q.statement.compile(compile_kwargs={"literal_binds": True}) print 'Started to setup data!' print q.count() for item in q: print 'Will yield one item!' yield cls(descriptor, *item) # A8aPhysical only has the primary mapper, needs different query else: q = sess.query(N)\ .select_from(N)\ .filter(N.Topic != 'InfoGaps')\ .filter(N.MarineUnitID.in_(muids)) \ .order_by(N.Topic, pk) print q.count() for item in q: yield cls(descriptor, *(item, None, None, None, None))
def get_data_from_view_Art8(self): sess = db.session() t = sql2018.t_V_ART8_GES_2018 descr_class = get_descriptor(self.descriptor) all_ids = list(descr_class.all_ids()) if self.descriptor.startswith('D1.'): all_ids.append('D1') # muids = [x.id for x in self.muids] conditions = [ t.c.CountryCode == self.country_code, # t.c.Region == self.country_region_code, # t.c.MarineReportingUnit.in_(muids), # t.c.GESComponent.in_(all_ids) ] # Handle the case of Romania that submitted duplicate data, # where Element is empty, but Criteria has data if self.country_code != 'RO': conditions.append( or_(t.c.Element.isnot(None), t.c.Criteria.isnot(None))) else: conditions.append(t.c.Element.isnot(None)) if self.country_code != 'DK': conditions.insert(1, t.c.Region == self.country_region_code) else: # Handle the case of Denmark that have submitted a lot of # information under the DK-TOTAL MRU, which doesn't have a region # attached. conditions.insert( 1, or_(t.c.Region == 'NotReported', t.c.Region == self.country_region_code)) orderby = [ getattr(t.c, x) for x in self._get_order_cols_Art8(self.descriptor) ] # groupby IndicatorCode q = sess\ .query(t)\ .filter(*conditions)\ .order_by(*orderby)\ .distinct() # For the following countries filter data by features # for other countries return all data country_filters = ('BE', ) if self.country_code not in country_filters: return q ok_features = set([f.name for f in get_features(self.descriptor)]) out = [] for row in q: if not self.descriptor.startswith('D1.'): out.append(row) continue feats = set((row.Feature, )) if feats.intersection(ok_features): out.append(row) return out