#coding=utf-8 import os import datetime import pandas as pd from LIB.BACKEND import DBManager, Log from LIB.MIDDLE import SignalMonitor from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker import time, datetime import traceback import pymysql from urllib import parse dbManager = DBManager.DBManager() if __name__ == "__main__": # 更新sn列表 host='rm-bp10j10qy42bzy0q7.mysql.rds.aliyuncs.com' port=3306 db='qixiang_oss' user='qixiang_oss' password='Qixiang2021' conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db) cursor = conn.cursor() cursor.execute("select sn, imei from app_device") res = cursor.fetchall() df_sn = pd.DataFrame(res, columns=['sn', 'imei']) df_sn = df_sn.reset_index(drop=True) conn.close(); # 读取结果 host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port=3306 db='qx_cas' user='qx_read' password='Qx@123456' conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db) cursor = conn.cursor() cursor.execute("select sn, end_time from signal_monitor_bms") res = cursor.fetchall() df_res_old = pd.DataFrame(res, columns=["sn", "end_time"]) df_res_old = df_res_old.reset_index(drop=True) conn.close(); # 数据库配置 host = 'rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port = 3306 user = 'qx_cas' password = parse.quote_plus('Qx@123456') database = 'qx_cas' db_engine = create_engine( "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( user, password, host, port, database )) DbSession = sessionmaker(bind=db_engine) # 日志配置 now_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()).replace(":","_") if not os.path.exists('log/' + now_str): os.makedirs('log/' + now_str) log = Log.Mylog(log_name='signal_monitor_新增sn统一运行至0812', log_level = 'info') log.set_file_hl(file_name='log/{}/info.log'.format(now_str), log_level='info', size=1024* 1024 * 100) log.set_file_hl(file_name='log/{}/error.log'.format(now_str), log_level='error', size=1024* 1024 * 100) logger = log.get_logger() logger.info("pid is + {}".format(os.getpid())) # 读取sn列表 df_sn_excel = pd.read_csv('./sn_list.csv') df_sn_excel = df_sn_excel.reset_index(drop=True) # df_sn['StartTime'] = pd.to_datetime(df_sn['StartTime']) # df_sn['EndTime'] = pd.to_datetime(df_sn['EndTime']) # df_sn['ExitTime'] = pd.to_datetime(df_sn['ExitTime']) signalMonitor = SignalMonitor.SignalMonitor() cal_period = 24 # 计算间隔，单位h count = 0 for i in range(0, len(df_sn['sn'])): # 遍历SN号 sn = [df_sn.loc[i,'sn']] if sn[0] not in df_sn_excel['sn'].tolist(): print (count) count = count + 1 st = datetime.datetime.strptime("2018-08-12 00:00:00", "%Y-%m-%d %H:%M:%S") et = datetime.datetime.strptime("2021-08-12 00:00:00", "%Y-%m-%d %H:%M:%S") else: continue # else: # count = count + 1 # continue # st = datetime.datetime.strptime("2018-08-12 00:00:00", "%Y-%m-%d %H:%M:%S") # et = datetime.datetime.strptime("2021-08-12 00:00:00", "%Y-%m-%d %H:%M:%S") # st = time.strftime("%Y-%m-%d 00:00:00", time.localtime(time.time()-86400)) # et = time.strftime("%Y-%m-%d 00:00:00", time.localtime(time.time())) # st = time.strftime("%Y-%m-%d 00:00:00", time.localtime(time.time()-86400)) # et = time.strftime("%Y-%m-%d 00:00:00", time.localtime(time.time())) # if df_sn.loc[i, 'Service'] == 0: # if pd.isnull(df_sn.loc[i, 'EndTime']) and pd.isnull(df_sn.loc[i, 'ExitTime']): # continue # elif pd.isnull(df_sn.loc[i, 'ExitTime']): # et = df_sn.loc[i, 'EndTime'] # else: # et = df_sn.loc[i, 'ExitTime'] # elif pd.isnull(df_sn.loc[i, 'EndTime']): # otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())) # et = pd.to_datetime(otherStyleTime) # else: # et = df_sn.loc[i, 'EndTime'] df_last_state = pd.DataFrame( columns=['sn', 'current', 'Timestamp', 'PackState', 'LineState']) # 每日最后BMS数据 df_last_state_gps = pd.DataFrame( columns=['sn', 'Timestamp', 'PackState', 'LineState', 'latitude', 'longitude']) # 每日最后GPS数据 df_res = pd.DataFrame(columns=[ 'sn', 'PackState', 'LineState', 'StartTime', 'EndTime', 'OfflineTime']) # 初始化BMS信号统计数据 df_res_gps = pd.DataFrame(columns=[ 'sn', 'PackState', 'LineState', 'StartTime', 'EndTime', 'OfflineTime', 'latitude', 'longitude']) # 初 logger.info("{} {} START!".format(sn, str(st))) while st < et: try: df_res, df_state, df_last_state = signalMonitor.get_bms_offline_stat( sn, st, et, df_res, df_last_state, cal_period) # 计算每日BMS信号统计数据 df_res_gps, df_last_state_gps = signalMonitor.get_gps_offline_stat( sn, st, et, df_state, df_res_gps, df_last_state_gps, cal_period) # 计算每日GPS信号统计数据 st = st + datetime.timedelta(hours=cal_period) except Exception as e: st = st + datetime.timedelta(hours=cal_period) logger.error(traceback.format_exc) logger.error(u"{} ：{},{} 任务运行错误".format(sn,st,et), exc_info=True) # 数据入库 df_tosql = df_res_gps.copy() df_tosql.columns = ['sn', 'pack_state', 'line_state', 'start_time', 'end_time', 'offline_time','latitude', 'longitude'] df_tosql.to_sql("signal_monitor_gps",con=db_engine, if_exists="append",index=False) df_tosql = df_res.copy() df_tosql.columns = ['sn', 'pack_state', 'line_state', 'start_time', 'end_time', 'offline_time'] df_tosql.to_sql("signal_monitor_bms",con=db_engine, if_exists="append",index=False) # 数据入库 df_tosql = df_last_state.copy() df_tosql.columns = ['sn', 'current', 'time_stamp', 'pack_state', 'line_state'] df_tosql.to_sql("signal_monitor_bms_last_day",con=db_engine, if_exists="append",index=False) df_tosql = df_last_state_gps.copy() df_tosql.columns = ['sn', 'time_stamp', 'pack_state', 'line_state','latitude', 'longitude'] df_tosql.to_sql("signal_monitor_gps_last_day",con=db_engine, if_exists="append",index=False) logger.info("{} {} DONE!".format(sn, str(st))) # print(count)