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))
def cal_elec_feature(): processed_rows = 0 session = session_make(engine=meta_engine) mps: List[MeasurePoint] = fetch_mps(session=session) for mp in mps: # Each measure points data = fetch_data( session=session, station_id=mp.station_id, inner_station_id=mp.inner_station_id, ) if len(data) > 0: feature_insert_value = [] for index, row in enumerate(data): # each row fundamental, feature = calculate_feature_row( row=row, station_id=mp.station_id, inner_station_id=mp.inner_station_id, ) feature_insert_value.append(feature) if index == len(data) - 1: statu, hi = health_evaluate(feature, fundamental) session.query(Motor).filter( Motor.asset_id == mp.asset_id).update({ "statu": statu, "health_indicator": hi, }) session.commit() processed_rows += insert_feature(session=session, to_save=feature_insert_value) session.close() return processed_rows
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_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_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_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))
def cal_vib_feature(): processed_rows = 0 session = session_make(engine=meta_engine) mps: List[MeasurePoint] = fetch_mps(session=session) for mp in mps: # Each measure points data = fetch_data( session=session, station_id=mp.station_id, inner_station_id=mp.inner_station_id, ) if len(data) > 0: feature_insert_value = [] for row in data: # each row feature_insert_value.append( calculate_feature_row( row=row, station_id=mp.station_id, inner_station_id=mp.inner_station_id, ) ) processed_rows += insert_feature( session=session, to_save=feature_insert_value ) session.close() return processed_rows
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_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( 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_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_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, 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_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 create( data: MeasurePointInputSchema, session: Session = session_make(engine=None) ): data = jsonable_encoder(data) data_model = VibData if (data["type"] == 0) else ElecData feature_model = VibFeature if (data["type"] == 0) else ElecFeature id = 0 async with Database(META_URL) as conn: id = await conn.execute(query=MeasurePoint.__table__.insert(), values=data) if id: async with Database(META_URL) as conn: data_table = data_model.model(point_id=id) feature_table = feature_model.model(point_id=id) await conn.execute( str(CreateTable(data_table.__table__).compile(meta_engine)) ) await conn.execute( str(CreateTable(feature_table.__table__).compile(meta_engine)) ) mp_change_commit()
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_weathers(conn: Database, session: Session = session_make(engine=None)): query = session.query(Station.id, Station.name, Station.weather).order_by(Station.id) return await conn.fetch_all(query2sql(query))
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))
for base_data in base_data_list: feature_row = [base_data["rms"]] for mp in mps_info[1:]: # 某设备所拥有的测点inner_station_id的排序必须一致 query = "select rms from vib_data_{0}_{1} order by abs(datediff(time,'{2}')) limit 1".format( mp["station_id"], mp["inner_station_id"], str(base_data["time"])) res = await db.fetch_all(query) # feature_row.append(res[0]['rms']) feature_row.append(res[0]["rms"] + random.random()) feature_matrix.append(feature_row) await db.disconnect() return feature_matrix session = session_make(None) query = (session.query( MeasurePoint.station_id, MeasurePoint.inner_station_id).filter( MeasurePoint.asset_id == asset_id, MeasurePoint.type == 0).order_by(MeasurePoint.inner_station_id)) mps_info = asyncio.run(get_data(query)) base_data_model = VibData.model(station_id=mps_info[0]["station_id"], inner_id=mps_info[0]["inner_station_id"]) base_data_query = session.query(base_data_model.time, base_data_model.rms).order_by( base_data_model.time.desc()) base_data_list = asyncio.run(get_data(base_data_query)) feature_matrix = asyncio.run(get_time_relate_data(mps_info, base_data_list))
import random from db import meta_engine from db.db_config import session_make from db_model import Asset, Manufacturer import datetime session = session_make(meta_engine) assets = session.query(Asset).all() manufactures = session.query(Manufacturer).all() for asset in assets: Motor = Asset( name="驱动电机#{0}".format(random.randint(100, 999)), sn="".join(random.sample("zyxwvutsrqponmlkjihgfedcba", 8)), asset_level=1, asset_type=2, st_time=datetime.datetime.fromisoformat("2014-09-09 10:24:25"), memo="N/A", station=asset.station, admin=asset.admin, repairs=random.randint(0, 5), health_indicator=85, parent_id=asset.id, manufacturer=random.choice(manufactures), ) Bearing1 = Asset( name="驱动端轴承#{0}".format(random.randint(100, 999)), sn="".join(random.sample("zyxwvutsrqponmlkjihgfedcba", 8)), asset_level=2,
async def get(conn: Database, id: int, session: Session = session_make(engine=None)): query = session.query(Station).filter(Station.id == id) return await conn.fetch_one(query2sql(query))