def get_qe_data(self, dynamic_path, device_id_list_path): database_section = 'MYSQL-SENSOR1' if self.is_split(dynamic_path): sql = self.get_qe_sql(dynamic_path, device_id_list_path)[0] df = mysql_connector.mysql_export_data_to_df(sql, database_section) else: sql1, sql2 = self.get_qe_sql(dynamic_path, device_id_list_path) df1 = mysql_connector.mysql_export_data_to_df(sql1, database_section) df2 = mysql_connector.mysql_export_data_to_df(sql2, database_section) df = pd.concat([df1, df2], axis=0, ignore_index=True) return df
def permit_sql_handle(self, permit_lyst): p_id_lyst = str(permit_lyst)[1:-1] sql = "select b.SENSOR_ID,a.P_ID,a.PNAME from PRIVILEGE_INFO a , PRIVILEGE_SENSOR_MAP b where a.P_ID in ({}) and a.P_ID=b.PRIVILEGE_ID;".format( p_id_lyst) sql_data = mysql_connector.mysql_export_data_to_df( sql, 'MYSQL-SENSOR1') return sql_data
def query_site_data_within_one_month(self, site_id_list, starttime, endtime): # 根据site_id, starttime, endtime 获取子站数据 sql_statement = su.compose_site_query_statement( site_id_list, starttime, endtime) site_data = mc.mysql_export_data_to_df(sql_statement, self.section) site_data = site_data.replace([None], np.nan) return site_data
def query_adj_data_within_one_month(self, device_list, start_time, end_time): """ 获取当月的ADJ数据 """ sql_statement = su.compose_adj_data_query_statement( start_time, end_time, device_list) adj_data = mc.mysql_export_data_to_df(sql_statement, self.section) return adj_data
def query_site_latitude_longitude(self, city_id=None, site_id=None): # 获取一个城市的子站经纬度 sql = su.compose_site_id_query_statement(city_id, site_id) site_info = mc.mysql_export_data_to_df(sql, self.section) if site_info is None: raise NoneDfError('未返回df数据,可能数据库连接有异常') elif site_info.empty: raise EmptyDfError('查询数据库获取的df数据为空') return site_info
def query_qualitycontrol_version(self): # 获取质控版本 sql_statement = su.compose_version_query_statement() version_df = mc.mysql_export_data_to_df(sql_statement, self.section) if version_df is None: raise NoneDfError('未返回df数据,可能数据库连接有异常') elif version_df.empty: raise EmptyDfError('查询数据库获取的df数据为空') return version_df
def city_name_sql_handle(self): """ 获取城市名字和城市id的映射 :return: 城市名字和城市id的映射 """ sql = "SELECT PARENTNAME,CITYID FROM DIC_ZONE_ALL WHERE CITYID>0 AND DICLEVEL = 1" sql_data = mysql_connector.mysql_export_data_to_df( sql, 'MYSQL-SENSOR1') return sql_data
def var_name_sql(self): """ 获取var_id和污染物的映射 :return: var_id和污染物的映射 """ sql = "SELECT AQ_TYPE,NAME FROM T_DICT_AQ_TYPE" sql_data = mysql_connector.mysql_export_data_to_df( sql, 'MYSQL-SENSOR1') return sql_data
def query_channels(self): # 根据VARGROUP_ID,获取出数通道 sql_statement = su.compose_transducerlist_query_statement() transducerlist_df = mc.mysql_export_data_to_df(sql_statement, self.section) if transducerlist_df is None: raise NoneDfError('未返回df数据,可能数据库连接有异常') elif transducerlist_df.empty: raise EmptyDfError('查询数据库获取的df数据为空') transducerlist_df = transducerlist_df.dropna().reset_index(drop=True) return transducerlist_df
def query_aq_type_in_dict(self): # 获取污染物类型对应的VAR_TYPE_ID sql_statement = su.compose_aq_type() df = mc.mysql_export_data_to_df(sql_statement, self.section) if df is None: raise NoneDfError('未返回df数据,可能数据库连接有异常') elif df.empty: raise EmptyDfError('查询数据库获取的df数据为空') my_dict = utility.two_column_df_to_dict(df, 'VAR_TYPE_ID', 'VAR_NAME') my_dict[1] = utility.exclude_dot_in_var_name(my_dict[1]) return my_dict
def query_field_name(self): # 获取capture表列名字段 # 获取需要的字段,并传递给函数compose_field_name_statement,在sql中会过滤不需要的字段 field_tuple = utility.list_to_tuple(self.field_name_full_set) sql_statement = su.compose_field_name_statement(field_tuple=field_tuple) field_name_df = mc.mysql_export_data_to_df(sql_statement, self.section) if field_name_df is None: raise NoneDfError('未返回df数据,可能数据库连接有异常') elif field_name_df.empty: raise EmptyDfError('查询数据库获取的df数据为空') field_name_df.set_index("DEV_TYPE_ID", inplace=True) return field_name_df
def var_name_handle(table): """ 数据库操作函数,通过SQL语句调用mysql_export_data_to_df函数,获取对应的df数据 :param table_sub: 查询配置项的数值表 :param table_sup: 查询配置项的配置项表 :return: 返回对应的配置项df数据 """ sql = 'SELECT AQ_TYPE,NAME from ' + table sql_data = mysql_connector.mysql_export_data_to_df(sql, 'MYSQL-SENSOR1A') if sql_data is None: raise NoneDfError('未返回df数据,可能数据库连接有异常') elif sql_data.empty: raise EmptyDfError('查询数据库获取的df数据为空') return sql_data
def query_all_active_devices_info(self, hour): # 获取sql语句 sql_statement = su.compose_sensor_query_statement(hour) # print(sql_statement) # 获取所有的设备编号 ( DEV_ID,SEN_DEV_TYPE_ID,RELATE_SITE_ID, # VARGROUP_ID,LATITUDE,LONGITUDE,CITYID) active_devices_df = mc.mysql_export_data_to_df(sql_statement,self.section) if active_devices_df is None: raise NoneDfError('未返回df数据,可能数据库连接有异常') elif active_devices_df.empty: raise EmptyDfError('查询数据库获取的df数据为空') active_devices_df = su.strip_column(active_devices_df, 'RELATE_SITE_ID') active_devices_df['RELATE_SITE_ID'] = active_devices_df['RELATE_SITE_ID'].astype(int) return active_devices_df
def adj_data_handle(self, table, target_time, var, dev_lyst): """ 查询当小时当污染物的数量 :param table:数据库表名称 :param target_time:目标时间 :param var:污染物名称 :param dev_lyst:设备范围 :return:当小时当污染物的数量 """ sql = "SELECT COUNT(VAR_TYPE_ID) FROM {} WHERE ADJ_TIME = '{}' and VAR_TYPE_ID= '{}' AND DEV_ID IN ({})".format( table, target_time, var, dev_lyst) sql_data = mysql_connector.mysql_export_data_to_df( sql, 'MYSQL-SENSOR1') return sql_data
def sql_handle(table, kind, start_hour, end_hour, var='', dev_lyst=''): if dev_lyst and var: sql_str = "and VAR_TYPE_ID=" + str( var) + " AND DEV_ID IN (" + dev_lyst + ")" elif dev_lyst: sql_str = " AND DEV_ID IN (" + dev_lyst + ")" elif var: sql_str = "and VAR_TYPE_ID=" + str(var) else: sql_str = '' sql = "SELECT COUNT(VAR_TYPE_ID),ADJ_TIME FROM {} WHERE ADJ_TIME >= '{}' and ADJ_TIME<= '{}' {} group by ADJ_TIME".format( table, start_hour, end_hour, sql_str) sql_data = mysql_connector.mysql_export_data_to_df(sql, kind) return sql_data
def query_org_data_by_org_x(self, org_x, device_list, endtime, starttime=None): # org_x(1,2,35,6,7), device_list(设备编号), starttime, endtime # 根据org_x, device_list, starttime, endtime获取org数据 sql_statement = su.compose_org_data_query_statement( sen_dev_type_id=org_x, device_list=device_list, starttime=starttime, endtime=endtime ) org_data = mc.mysql_export_data_to_df(sql_statement, self.section) if org_data is None: logger.info('org_%s不存在', org_x) else: logger.info('org_%s的数据长度为%s', org_x, len(org_data)) return org_data
def get_base_data(self, dynamic_path, option, static_path): database_section = 'MYSQL-SENSOR1' sql_list = self.get_base_sql(dynamic_path, option, static_path) df = pd.DataFrame(data=None, columns=['DEV_ID', 'CAP_TIME', 'PM25_1', 'PM25_2', 'PM25_3', 'PM10_1', 'PM10_2', 'PM10_3', 'LONGITUDE', 'LATITUDE', 'TEMPERATURE', 'HUMIDITY']) for sql in sql_list: result = mysql_connector.mysql_export_data_to_df(sql, database_section) # print(sql) df = pd.concat([df, result], axis=0, ignore_index=True, sort=False) if not df.empty: print(df.shape[0]) # print(df.columns) # print(df["DEV_ID"].unique()) return df
def city_dev_sql(self, target_time): """ 获取城市和设备的对应列表 :param target_time: 目标时间 :return: 城市和设备的对应列表 """ if self.permit_dev_df.empty: pid = '' else: self.permit_dev_lyst = self.permit_dev_df['SENSOR_ID'].tolist() pid = str(self.permit_dev_lyst)[1:-1] sql = "select s.SENSOR_ID,CITYID from SENSOR_INFO s,MEASURE_POINT m where s.MEASURE_POINT_ID=m.ID and CITYID>0 and STATE like '1%' and MEASURE_POINT_ID>0 and RELATE_SITE_ID<0 and SENSOR_ID not in (select SENSOR_ID from T_ABNORM_DEVICE where TIMESTAMP ='{}') and SENSOR_ID not in ({})".format( target_time, pid) sql_data = mysql_connector.mysql_export_data_to_df( sql, 'MYSQL-SENSOR1') return sql_data
def query_capture_data_by_capture_x(self, starttime, endtime, capture_x, field_name, device_list): # 根据hour(时间), capture_x(哪个capture表), field_name(要获取的字段), # device_list(设备编号),获取到分钟原始数据 # compose_capture_data_query_statement 拼接sql sql_statement = su.compose_capture_data_query_statement( field_name=field_name, sen_dev_type_id=capture_x, device_list=device_list, starttime=starttime, endtime=endtime) capture_data = mc.mysql_export_data_to_df(sql_statement, self.section) if capture_data is None: logger.info('capture_%s不存在', capture_x) else: logger.info('capture_%s的数据长度为%s', capture_x, len(capture_data)) return capture_data
def sql_handle_cityid_tvoc(self, target_time): """ 获取标准数据的df :param target_time: 目标时间 :return: 标准数据的df """ if self.permit_dev_df.empty: pid = '' else: self.permit_dev_lyst = self.permit_dev_df['SENSOR_ID'].tolist() pid = str(self.permit_dev_lyst)[1:-1] sql = "select sen.CITYID,abbr.VAR_TYPE_ID, count(sen.SENSOR_ID) from(select s.SENSOR_ID,CITYID,STATE from SENSOR_INFO s,MEASURE_POINT m where s.MEASURE_POINT_ID=m.ID and CITYID>0 and STATE = '1100' and MEASURE_POINT_ID>0 and RELATE_SITE_ID<0 and SENSOR_ID not in (select SENSOR_ID from T_ABNORM_DEVICE where TIMESTAMP ='{}' ) and SENSOR_ID not in ({})) sen inner join (select SENSOR_ID,VAR_TYPE_ID from T_SENSOR_TRANSDUCER_ABBR a join T_SENSOR_VARGROUP_MAP b on a.ABBR_CODE=b.ABBR_CODE) abbr on sen.SENSOR_ID=abbr.SENSOR_ID group by sen.CITYID,abbr.VAR_TYPE_ID;".format( target_time, pid) sql_data = mysql_connector.mysql_export_data_to_df( sql, 'MYSQL-SENSOR1') sql_data.dropna(axis=0, how='any', inplace=True) return sql_data
def sql_handle(table_sub, table_sup, item=''): """ 数据库操作函数,通过SQL语句调用mysql_export_data_to_df函数,获取对应的df数据 :param table_sub: 查询配置项的数值表 :param table_sup: 查询配置项的配置项表 :param item: 附加参数,例如:city_id,var_type :return: 返回对应的配置项df数据 """ if item: sql = 'SELECT config_item,value,parse_type,' + item + ' from ' + table_sub + \ ' INNER JOIN ' + table_sup + ' on ' + table_sub + '.config_item_id = '\ + table_sup + '.id' else: sql = 'SELECT config_item,value,parse_type from ' + table_sub + \ ' INNER JOIN ' + table_sup + ' on ' + table_sub + '.config_item_id = ' \ + table_sup + '.id' sql_data = mysql_connector.mysql_export_data_to_df(sql, 'MYSQL-SENSOR1A') if sql_data is None: raise NoneDfError('未返回df数据,可能数据库连接有异常') elif sql_data.empty: raise EmptyDfError('查询数据库获取的df数据为空') return sql_data
def sql_handle_cityid(kind, cityid): sql = "SELECT SENSOR_ID FROM MEASURE_POINT INNER JOIN SENSOR_INFO ON MEASURE_POINT.ID=SENSOR_INFO.MEASURE_POINT_ID WHERE MEASURE_POINT.CITYID='" + str( cityid) + "'" sql_data = mysql_connector.mysql_export_data_to_df(sql, kind) return sql_data
def query_consistency_model(self): # 获取所有在线设备一致性模型 sql_statement = su.compose_consistency_model_query_statement() consistency_model = mc.mysql_export_data_to_df( sql_statement, self.section) return consistency_model
def query_abbr(self): sql = su.compose_abbr_query_statement() abbr_df = mc.mysql_export_data_to_df(sql, self.section) return abbr_df