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)