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
