async def get_data(): db = Database(META_URL) await db.connect() res = await db.fetch_all(query2sql(query)) modelName = await db.fetch_one(query2sql(model_name_query)) await db.disconnect() return res, modelName
async def get_info(session: Session, conn: Database, id: int): pre_query_res = await conn.fetch_one( query2sql(session.query(Asset.asset_type).filter(Asset.id == id)) ) model = info_models_mapper[ enum_mapper[pre_query_res["asset_type"]] ] # int -> asset_type -> model class query = session.query(model).filter(model.asset_id == id) return await conn.fetch_one(query2sql(query))
async def get_tree(conn: Database, session: Session = session_make(engine=None)): assets = await conn.fetch_all( query2sql( session.query(Asset.id, Asset.name, Asset.station_id.label("parent_id")).filter( Asset.asset_level == 0))) stations = await conn.fetch_all( query2sql( session.query(Station.id, Station.name, Station.bc_id.label("parent_id")))) bcs = await conn.fetch_all( query2sql( session.query( BranchCompany.id, BranchCompany.name, BranchCompany.rc_id.label("parent_id"), ))) rcs = await conn.fetch_all( query2sql(session.query(RegionCompany.id, RegionCompany.name))) tree = Tree() tree.create_node(tag="root", identifier="root") def item_maker(item, parent_type, self_type, color, value): temp = dict(item) if parent_type: temp["parent_id"] = parent_type + str(temp["parent_id"]) if self_type == "asset": temp["value"] = 1 temp["id"] = self_type + str(temp["id"]) temp["itemStyle"] = {"color": color} temp["am_value"] = value return temp color = ["#1a8bff", "#51a2f7", "#79b8ff", "#93ccff"] assets = [item_maker(row, "st", "asset", color[3], 40) for row in assets] stations = [item_maker(row, "bc", "st", color[2], 60) for row in stations] bcs = [item_maker(row, "rc", "bc", color[1], 80) for row in bcs] rcs = [item_maker(row, None, "rc", color[0], 130) for row in rcs] for item in rcs + bcs + stations + assets: tree.create_node( data=item, identifier=item["id"], parent=item["parent_id"] if "parent_id" in item else "root", ) return tree.to_dict(with_data=True)["children"]
async def get_multi(conn: Database, skip: int, limit: int, session: Session = session_make(engine=None)): query = session.query(Pipeline).order_by( Pipeline.id).offset(skip).limit(limit) return await conn.fetch_all(query2sql(query))
async def get_cards( conn: Database, skip: int, limit: int, session: Session = session_make(engine=None), ): query = ( session.query( Asset.id, Asset.name, Asset.sn, Asset.st_time, Asset.health_indicator, Asset.statu, Asset.repairs, Asset.mp_configuration, Station.name.label("station_name"), PumpUnit.is_domestic, PumpUnit.oil_type, PumpUnit.design_output, ) .join(Station, Station.id == Asset.station_id) .join(PumpUnit, PumpUnit.asset_id == Asset.id) .order_by(Asset.id) .filter(Asset.asset_type == 0) .offset(skip) .limit(limit) ) return await conn.fetch_all(query2sql(query))
async def get_rc(conn: Database, skip: int, limit: int, session: Session = session_make(engine=None)): query = (session.query(RegionCompany).order_by( RegionCompany.id).offset(skip).limit(limit)) return await conn.fetch_all(query2sql(query))
async def get_multi( conn: Database, session: Session = session_make(engine=None), ): query = session.query(Threshold) return await conn.fetch_all(query2sql(query))
async def get_detail_by_id( conn: Database, id: int, session: Session = session_make(engine=None), ): query = ( session.query( Asset.id, Asset.name, Asset.sn, Asset.lr_time, Asset.cr_time, Asset.md_time, Asset.st_time, Asset.memo, Asset.health_indicator, Asset.statu, Asset.asset_type, Asset.repairs, Station.name.label("station_name"), Pipeline.name.label("pipeline_name"), BranchCompany.name.label("branch_name"), RegionCompany.name.label("region_name"), PumpUnit.is_domestic, PumpUnit.oil_type, PumpUnit.design_output, ) .join(Station, Station.id == Asset.station_id) .join(PumpUnit, PumpUnit.asset_id == Asset.id) .join(Pipeline, Pipeline.id == PumpUnit.pipeline_id) .join(BranchCompany, BranchCompany.id == Station.bc_id) .join(RegionCompany, RegionCompany.id == Station.rc_id) .filter(Asset.id == id) ) return await conn.fetch_one(query2sql(query))
async def get_data_join_feature_by_id( conn: Database, data_model, feature_model, mp_id: int, require_mp_type: int, data_id: int, data_fileds: tuple, feature_fileds: tuple, session: Session = session_make(engine=None), ): data_model = get_shard_model( data_model, mp_id=mp_id, require_mp_type=require_mp_type ) feature_model = get_shard_model( feature_model, mp_id=mp_id, require_mp_type=require_mp_type ) feature_attr = [] for feature in feature_fileds: feature_attr.append(getattr(feature_model, feature)) data_attr = [] for data in data_fileds: data_attr.append(getattr(data_model, data)) query = ( session.query(*tuple(data_attr), *tuple(feature_attr)) .select_from(data_model) .join(feature_model, feature_model.data_id == data_model.id) .filter(data_model.id == data_id) ) return await conn.fetch_one(query2sql(query))
async def get_statu_stat( conn: Database, session: Session = session_make(engine=meta_engine) ): query = ( session.query(MaintenanceRecord.statu, func.count("*")) .select_from(MaintenanceRecord) .group_by(MaintenanceRecord.statu) ) res = await conn.fetch_all(query2sql(query)) return format_single_grouped_result(res=res, group_names=MaintenanceRecord.STATUS)
async def get_by_id( conn: Database, orm_model, mp_id: int, require_mp_type: int, data_id: int, session: Session = session_make(engine=None), ): model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type) query = session.query(model).filter(model.id == data_id) return await conn.fetch_one(query2sql(query))
async def get( conn: Database, id: int, session: Session = session_make(engine=meta_engine) ): query = ( session.query(MaintenanceRecord, Asset.name.label("asset_name")) .join(Asset, Asset.id == MaintenanceRecord.asset_id) .filter(MaintenanceRecord.id == id) ) res = await conn.fetch_one(query2sql(query)) return res
async def get_estimated_value_by_id( conn: Database, asset_id: int, data_id: int, session: Session = session_make(engine=None), ): hi_model = AssetHI.model(point_id=asset_id) query = session.query(hi_model.id, hi_model.est).filter(hi_model.id == data_id) res = await conn.fetch_one(query2sql(query)) dic = {"id": res["id"], "est": json.loads(res["est"])} return dic
async def get_latest( conn: Database, orm_model, mp_id: int, require_mp_type: int, session: Session = session_make(engine=None), ): model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type) query = ( session.query(model).order_by(model.id.desc()).limit(1) ) # query all the defined fields return await conn.fetch_one(query2sql(query))
async def get_mp_mapper(): if len(measure_point_router) == 0: async with Database(META_URL) as conn: session = session_make(engine=None) query = session.query(MeasurePoint) res = await conn.fetch_all(query2sql(query)) for row in res: measure_point_router[row["id"]] = { "sid": row["station_id"], "iid": row["inner_station_id"], "type": row["type"], } return measure_point_router
async def get_similarity_threshold_recently( conn: Database, asset_id: int, limit: int, session: Session = session_make(engine=None), ): hi_model = AssetHI.model(point_id=asset_id) query = (session.query(hi_model.id, hi_model.time, hi_model.similarity, hi_model.threshold).order_by( hi_model.time.desc()).limit(limit)) res = await conn.fetch_all(query2sql(query)) res.reverse() dic = multi_result_to_array(res) return dic
async def get_multi( conn: Database, mp_id: int, require_mp_type: int, orm_model, fileds: List[str], time_before: str, time_after: str, limit: int, with_estimated: bool = False, session: Session = session_make(engine=None), ): model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type) query = session.query(model) for filed in fileds + ["id", "time", "data_id"]: query = query.options(load_only(filed)) if with_estimated: for filed in fileds: query = ( query.options(load_only("est_" + filed)) if filed != "similarity" else query ) if time_before != "None": query = query.filter(model.time.between(str(time_after), str(time_before))) query = query.order_by(model.time.desc()) if limit: query = query.order_by(model.time.desc()).limit(limit) res = await conn.fetch_all(query2sql(query)) res.reverse() if len(res) == 0: raise HTTPException( status_code=400, detail="No signal collected between the time range" ) dic = {} keys = res[0].keys() for row in res: for key in keys: if key == "time": dic.setdefault(key, []).append(str(row[key])) else: dic.setdefault(key, []).append(row[key]) return dic
async def get_similarity_threshold_during_time( conn: Database, asset_id: int, time_before: str, time_after: str, session: Session = session_make(engine=None), ): hi_model = AssetHI.model(point_id=asset_id) query = session.query(hi_model.id, hi_model.time, hi_model.similarity, hi_model.threshold).filter( hi_model.time.between(str(time_after), str(time_before))) res = await conn.fetch_all(query2sql(query)) dic = multi_result_to_array(res) return dic
async def get_multi( conn: Database, orm_model, mp_id: int, require_mp_type: int, time_before: datetime, time_after: datetime, session: Session = session_make(engine=None), ): model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type) query = ( session.query(model.id, model.time) .filter(model.time.between(str(time_before), str(time_after))) .order_by(model.id) ) return await conn.fetch_all(query2sql(query))
async def get_multi( conn: Database, skip: int, limit: int, brief: bool, session: Session = session_make(engine=None), **kwargs ): if brief: query = session.query( MeasurePoint.id, MeasurePoint.name, MeasurePoint.type, MeasurePoint.health_indicator, ) else: query = session.query( MeasurePoint.id, MeasurePoint.name, MeasurePoint.type, MeasurePoint.md_time, MeasurePoint.statu, MeasurePoint.health_indicator, MeasurePoint.sample_freq, MeasurePoint.sample_interval, Station.id.label("staion_id"), Station.name.label("station_name"), Asset.id.label("asset_id"), Asset.name.label("asset_name"), ) query = ( query.order_by(MeasurePoint.id.asc()) .offset(skip) .limit(limit) .join(Station, MeasurePoint.station_id == Station.id) .join(Asset, MeasurePoint.asset_id == Asset.id) ) if kwargs["station_id"]: query = query.filter(MeasurePoint.station_id == kwargs["station_id"]) if kwargs["asset_id"]: query = query.filter(MeasurePoint.asset_id == kwargs["asset_id"]) if kwargs["type"] is not None: query = query.filter(MeasurePoint.type == kwargs["type"]) query = query.order_by( MeasurePoint.name ) # important, relate to the order display in asset detail page. return await conn.fetch_all(query2sql(query))
async def get_multi( conn: Database, skip: int, limit: int, type: int, station_name: str, level: int, station_id: int, session: Session = session_make(engine=None), ): if level is None: query = ( session.query( Asset.id, Asset.name, Asset.sn, Asset.lr_time, Asset.cr_time, Asset.md_time, Asset.st_time, Asset.asset_level, Asset.memo, Asset.health_indicator, Asset.statu, Asset.parent_id, Asset.station_id, Asset.repairs, Station.name.label("station_name"), ) .join(Station, Station.id == Asset.station_id) .order_by(Asset.id) .offset(skip) .limit(limit) ) else: query = session.query(Asset.id, Asset.name).filter( Asset.asset_level == level ) # short query when the level filed is given, for relate asset dropdown if station_id is not None: query = query.filter(Asset.station_id == station_id) if type is not None: query = query.filter(Asset.asset_type == type) if station_name is not None: query = query.filter(Station.name == station_name) return await conn.fetch_all(query2sql(query))
async def get_latest( conn: Database, mp_id: int, require_mp_type: int, orm_model, fileds: List[str], session: Session = session_make(engine=None), ): model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type) query = session.query(model) for filed in fileds: query = query.options(load_only(filed)) query = query.order_by(model.id.desc()).limit(1) res = await conn.fetch_one(query2sql(query)) return res
async def get_multi( conn: Database, skip: int, limit: int, asset_id: int, session: Session = session_make(engine=None), ): query = ( session.query(MaintenanceRecord, Asset.name.label("asset_name")) .join(Asset, Asset.id == MaintenanceRecord.asset_id) .order_by(MaintenanceRecord.statu.desc()) .offset(skip) .limit(limit) ) if asset_id: query = query.filter(Asset.id == asset_id) return await conn.fetch_all(query2sql(query))
async def get( conn: Database, mp_id: int, require_mp_type: int, orm_model, fileds: List[str], data_id: int, session: Session = session_make(engine=None), ): model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type) query = session.query(model) for filed in fileds + ["id", "time"]: query = query.options(load_only(filed)) query = query.filter(model.data_id == data_id) res = await conn.fetch_one(query2sql(query)) return res
async def get_stat( conn: Database, rule: str, session: Session = session_make(engine=None) ): if rule == "station": query = session.query( MeasurePoint.station_id, func.count("*").label("cnt") ).group_by(MeasurePoint.station_id) elif rule == "asset": query = session.query( MeasurePoint.asset_id, func.count("*").label("cnt") ).group_by(MeasurePoint.asset_id) elif rule == "statu": query = session.query( MeasurePoint.statu, func.count("*").label("cnt") ).group_by(MeasurePoint.statu) else: return None return await conn.fetch_all(query2sql(query))
async def get(conn: Database, id: int, session: Session = session_make(engine=None)): query = ( session.query( MeasurePoint.id, MeasurePoint.name, MeasurePoint.type, MeasurePoint.md_time, MeasurePoint.statu, MeasurePoint.sample_sensitive, MeasurePoint.sample_freq, MeasurePoint.asset_id, Station.id.label("staion_id"), Station.name.label("station_name"), MeasurePoint.sample_interval, ) .join(Station, MeasurePoint.station_id == Station.id) .order_by(MeasurePoint.id) .filter(MeasurePoint.id == id) ) return await conn.fetch_one(query2sql(query))
async def get(conn: Database, id: int, session: Session = session_make(engine=None)): query = ( session.query( Asset.id, Asset.name, Asset.sn, Asset.lr_time, Asset.cr_time, Asset.md_time, Asset.st_time, Asset.asset_level, Asset.memo, Asset.health_indicator, Asset.statu, Station.name.label("station_name"), ) .join(Station, Station.id == Asset.station_id) .filter(Asset.id == id) ) return await conn.fetch_one(query2sql(query))
async def get_estimated_value_multi( conn: Database, asset_id: int, time_before: str, time_after: str, session: Session = session_make(engine=None), ): hi_model = AssetHI.model(point_id=asset_id) query = session.query(hi_model.id, hi_model.time, hi_model.est).filter( hi_model.time.between(str(time_after), str(time_before))) res = await conn.fetch_all(query2sql(query)) dic = {} for row in res: dic.setdefault("id", []).append(row["id"]) dic.setdefault("time", []).append(str(row["time"])) serialized = json.loads(row["est"]) for index, fileds in enumerate(serialized["label"]): dic.setdefault(fileds + "—原始值", []).append(serialized["raw"][index]) dic.setdefault(fileds + "-估计值", []).append(serialized["est"][index]) return dic
async def get_total_length(conn: Database, session: Session = session_make(engine=None)): query = session.query(func.sum( Pipeline.length).label("value")).select_from(Pipeline) return await conn.fetch_one(query2sql(query))
async def get_data(query): db = Database(META_URL) await db.connect() res = await db.fetch_all(query2sql(query)) await db.disconnect() return res