def file_index(file, file_des, return_type): """ 生成文件索引表内容, 地区返回内容表, 任务返回内容表插入数据库表中 """ des_result = xml_parser(file_des, return_type) file_result = get_file_info(file) if return_type == 'file_index': result = (des_result[0], des_result[1], des_result[6], des_result[2], des_result[5], file_result[0], file_result[1], des_result[3], des_result[4], file_result[3], '/data/ftp/' + os.path.basename(file), file_result[2]) sql = "insert into table file_index values ('{0}','{1}','{2}','{3}','{4}'," \ "'{5}','{6}','{7}','{8}','{9}','{10}','{11}')".format(*result) elif return_type == 'device_info': result = (des_result[0], des_result[5], des_result[1], des_result[2], des_result[3], des_result[4]) sql = "insert into table deviceinfo values ('{0}','{1}','{2}','{3}','{4}','{5}')".format( *result) sql_filter = 'select * from deviceinfo where mfid="{0}" and equid="{1}"'.format( des_result[5], des_result[2]) elif return_type == 'task_info': result = (des_result[0], des_result[1], des_result[9], des_result[2], des_result[3], des_result[4], des_result[5], des_result[6], des_result[7], des_result[8], des_result[9]) if des_result[9] == 'finished': sql = "insert into table taskinfo partition (status=1) values ('{0}','{1}','{2}','{3}','{4}'," \ "'{5}','{6}','{7}','{8}','{9}', '{10}')".format(*result) else: result = list(result) result[5] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') sql = "insert into table taskinfo partition (status=0) values ('{0}','{1}','{2}','{3}','{4}'," \ "'{5}','{6}','{7}','{8}','{9}', '{10}')".format(*result) sql_filter = 'select * from taskinfo where taskid="{0}" and equid="{1}"'.format( des_result[0], des_result[2]) cursor = hc.get_hive_cursor('172.39.8.60', 'db_data_store') # 去重 if return_type == 'device_info' or return_type == 'task_info': res = hc.execute_sql(cursor, sql_filter) if not res: hc.execute_sql_insert(cursor, sql) else: return 0 else: hc.execute_sql_insert(cursor, sql) return 1
def get_businessid(start_freq, stop_freq): """ 通过起始结束频率查询表获取监测业务编号 单位 hz """ cursor = hc.get_hive_cursor('172.18.140.8', 'rmdsd') sql = 'select servicedid from rmbt_service_freqdetail where startfreq=={0} and endfreq = {1}'.format( start_freq, stop_freq) res = hc.execute_sql(cursor, sql) # flag: 表中是否有当前业务编号 flag = True # 若表中无对应数据,生成自定义频段 if not res: res = uuid.uuid1() flag = False #sql = 'insert into table rmbt_service_freqdetail ' \ # 'values ("{0}","00000000-0000-0000-0000-000000000000","{1}-{2}Mhz",{1},{2},25.0)'.format( # res, start_freq, stop_freq) #hc.execute_sql_insert(cursor, sql) return res, flag
def update_tasktime(): """ 更新波尔接口中status不为finished的taskinfo """ cursor = hc.get_hive_cursor('172.39.8.60', 'db_data_store') sql = 'select * from taskinfo where status=0' task_list = hc.execute_sql(cursor, sql) hc.execute_sql_insert(cursor, 'alter table taskinfo drop partition (status=0)') for task in task_list: paramxml, start_time, stop_time, status = ws.query_tasks(task[0]) if status == 'finished': task = list(task) task[4] = start_time task[5] = stop_time sql = "insert into table taskinfo partition (status=1) values ('{0}','{1}','{2}','{3}','{4}'," \ "'{5}','{6}','{7}','{8}','{9}') ".format(*task) hc.execute_sql_insert(cursor, sql) else: sql = "insert into table taskinfo partition (status=0) values ('{0}','{1}','{2}','{3}','{4}'," \ "'{5}','{6}','{7}','{8}','{9}') ".format(*task) hc.execute_sql_insert(cursor, sql)
# coding=utf-8 import hive_connector as hc from math import pow import sys cursor = hc.get_hive_cursor('172.39.8.60', 'spectrum_evaluation') sql = 'select * from ampdict' res = hc.execute_sql(cursor, sql) length = len(res) count = 0 illegal = 0 for i in res: count += 1 for k, v in eval(i[4]).items(): if v/i[3] > 0.03: if (pow(10, (eval(i[5])[k]/10 - 107)/10)) / 1000 > i[-1]: with open('/illegal', 'a') as f: f.write(str(k)+','+'1'+','+i[0]+','+i[2]+','+i[1]) f.write('\n') illegal += 1 sys.stdout.write('\r当前扫描: '+str(count)+' / '+str(length)+'\t违规频点数量: '+str(illegal)) sys.stdout.flush()
import numpy as np import hive_connector as hc import datetime import csv file = open('test.csv', 'a') writer = csv.writer(file) index = 0 dtype = [('index', int), ('time_start', datetime.datetime), ('time_stop', datetime.datetime), ('freq', float), ('unknown1', float), ('unknown2', float), ('unknown3', float), ('unknown4', int), ('mfid', np.int64)] cursor = hc.get_hive_cursor('172.39.8.60', 'analysesystem') values = hc.execute_sql( cursor, 'select * from combined_signal order by mfid,freq,firsttime') signal_array = np.array(values, dtype=dtype) res = np.sort(signal_array, order=['mfid', 'freq', 'time_start']) for i in range(len(res) - 1): # 同mfid做比较 first = res[i] second = res[i + 1] if first['mfid'] == second['mfid']: # 判断频率差值是否在指定条件内 if second['freq'] - first['freq'] > 50: index += 1 first[0] = index print(first) writer.writerow(first)