__author__ = 'lmstack' #coding=utf-8 import os import datetime import pandas as pd from LIB.BACKEND import DBManager, Log from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker import time, datetime import dateutil.relativedelta import traceback from LIB.MIDDLE.CellStateEstimation.Common import log from pandas.core.frame import DataFrame from LIB.MIDDLE.SaftyCenter.Common import QX_BatteryParam from LIB.MIDDLE.SaftyCenter.diagfault.SC_SamplingSafty import SamplingSafty from LIB.MIDDLE.CellStateEstimation.Common.V1_0_1 import DBDownload from urllib import parse import pymysql import pdb from apscheduler.schedulers.blocking import BlockingScheduler import datacompy from LIB.MIDDLE.SaftyCenter.Liplated import Li_plated from LIB.MIDDLE.SaftyCenter.diagfault.SC_SamplingSafty import SamplingSafty def cell_platd_test(df_sn, df_first_data_time): # 读取结果数据库 host2='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port2=3306 db2='qx_cas' user2='qx_read' password2='Qx@123456' db_engine = create_engine( "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( user, password, host, port, database )) start=time.time() end_time=datetime.datetime.now() start_time=end_time-datetime.timedelta(days=90) start_time=start_time.strftime('%Y-%m-%d %H:%M:%S') end_time=end_time.strftime('%Y-%m-%d %H:%M:%S') logger.info("cycle start !!!!!!!!!!!!!!!!!!!!") for i in range(0, len(df_sn)): try: if df_sn.loc[i, 'imei'][5:9] == 'N640': celltype=1 #6040三元电芯 elif df_sn.loc[i, 'imei'][5:9] == 'N440': celltype=2 #4840三元电芯 elif df_sn.loc[i, 'imei'][5:9] == 'L660': celltype=99 # 6060锂电芯 elif df_sn.loc[i, 'imei'][3:5] == 'LX' and df_sn.loc[i, 'imei'][5:9] == 'N750': celltype=3 #力信 50ah三元电芯 # elif df_sn.loc[i, 'imei'][3:5] == 'CL' and df_sn.loc[i, 'imei'][5:9] == 'N750': # celltype=4 #CATL 50ah三元电芯 else: logger.info("pid-{} celltype-{} SN: {} SKIP!".format(os.getpid(), "未知", sn)) continue sn = df_sn.loc[i, 'sn'] logger.info("pid-{} celltype-{} SN: {} START!".format(os.getpid(), celltype, sn)) param=QX_BatteryParam.BatteryInfo(celltype) #读取原始数据库数据........................................................................................................................................................ dbManager = DBManager.DBManager() df_data = dbManager.get_data(sn=sn, start_time=start_time, end_time=end_time, data_groups=['bms']) df_bms = df_data['bms'] df_Diag_lipltd = pd.DataFrame(columns = ['sn','time','liplated', 'liplated_amount']) df_Diag_lipltd_add = pd.DataFrame(columns = ['sn','time','liplated', 'liplated_amount']) df_diag_sor = pd.DataFrame(columns = ['sn','time','sorout_confr', 'sorout_amplt']) df_diag_vol = pd.DataFrame(columns = ['sn','time','volout_confr', 'volout_amplt']) df_diag_volsor = pd.DataFrame(columns = ['sn','time','volsorout_confr', 'volsorout_amplt']) #析锂诊断................................................................................................................................................................ if not df_bms.empty: Diag_lipltd_temp = Li_plated.Liplated_test(sn,celltype,df_bms)#析锂检测 df_Diag_lipltd_add = Diag_lipltd_temp.liplated_detect() # Diag_sorvol_temp = vol_sor_est.vol_sor_est(sn,celltype,df_bms)#电压内阻估计 # [df_diag_sor_add, df_diag_vol_add, df_diag_sorvol_add] = Diag_sorvol_temp.volsor_cal() if not df_Diag_lipltd_add.empty: df_Diag_lipltd_temp = df_Diag_lipltd.append(df_Diag_lipltd_add) df_Diag_lipltd = df_Diag_lipltd_temp.drop_duplicates(subset = ['sn','time'], keep = 'first', inplace = False) df_Diag_lipltd.reset_index(drop = True) df_Diag_lipltd.sort_values(by = ['sn'], axis = 0, ascending=True,inplace=True)#对故障信息按照时间进行排序 df_Diag_lipltd['add_time'] = datetime.datetime.now() df_Diag_lipltd.to_sql("mechanism_liplated",con=db_engine, if_exists="append",index=False) # if not df_diag_sor_add.empty: # df_diag_sor_temp = df_diag_sor.append(df_diag_sor_add) # df_diag_sor = df_diag_sor_temp.drop_duplicates(subset = ['sn','time'], keep = 'first', inplace = False) # df_diag_sor.reset_index(drop = True) # df_diag_sor.sort_values(by = ['sn'], axis = 0, ascending=True,inplace=True)#对故障信息按照时间进行排序 # df_diag_sor['add_time'] = datetime.datetime.now() # df_diag_sor.to_sql("mechanism_sorout",con=db_engine, if_exists="append",index=False) # if not df_diag_vol_add.empty: # df_diag_vol_temp = df_diag_vol.append(df_diag_vol_add) # df_diag_vol = df_diag_vol_temp.drop_duplicates(subset = ['sn','time'], keep = 'first', inplace = False) # df_diag_vol.reset_index(drop = True) # df_diag_vol.sort_values(by = ['sn'], axis = 0, ascending=True,inplace=True)#对故障信息按照时间进行排序 # df_diag_vol['add_time'] = datetime.datetime.now() # df_diag_vol.to_sql("mechanism_volout",con=db_engine, if_exists="append",index=False) # if not df_diag_sorvol_add.empty: # df_diag_volsor_temp = df_diag_volsor.append(df_diag_sorvol_add) # df_diag_volsor = df_diag_volsor_temp.drop_duplicates(subset = ['sn','time'], keep = 'first', inplace = False) # df_diag_volsor.reset_index(drop = True) # df_diag_volsor.sort_values(by = ['sn'], axis = 0, ascending=True,inplace=True)#对故障信息按照时间进行排序 # df_diag_volsor['add_time'] = datetime.datetime.now() # df_diag_volsor.to_sql("mechanism_volsorout",con=db_engine, if_exists="append",index=False) logger.info("pid-{} celltype-{} SN: {} DONE!".format(os.getpid(), celltype, sn)) except: logger.error(traceback.format_exc) logger.error(u"{} ：{},{} 任务运行错误\n".format(sn,start_time,end_time), exc_info=True) logger.info("cycle DONE !!!!!!!!!!!!!!!!!!!!") db_engine.dispose() if __name__ == "__main__": # 时间设置 # now_time = datetime.datetime.now() # pre_time = now_time + dateutil.relativedelta.relativedelta(days=-1)# 前一日 # end_time=datetime.datetime.strftime(now_time,"%Y-%m-%d 00:00:00") # start_time=datetime.datetime.strftime(pre_time,"%Y-%m-%d 00:00:00") history_run_flag = False # 历史数据运行标志 # 更新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, add_time from app_device") res = cursor.fetchall() df_sn = pd.DataFrame(res, columns=['sn', 'imei', 'add_time']) df_sn = df_sn.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) # 运行历史数据配置 df_first_data_time = pd.read_sql("select * from bat_first_data_time", db_engine) db_engine.dispose() # 日志配置 now_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()).replace(":","_") log_path = 'log_3month/' + now_str if not os.path.exists(log_path): os.makedirs(log_path) log = Log.Mylog(log_name='saftyCenter_liplatedAll', log_level = 'info') log.set_file_hl(file_name='{}/info.log'.format(log_path), log_level='info', size=1024* 1024 * 100) log.set_file_hl(file_name='{}/error.log'.format(log_path), log_level='error', size=1024* 1024 * 100) logger = log.get_logger() logger.info("pid is {}".format(os.getpid())) # 算法参数 # host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' # port=3306 # db='safety_platform' # user='qx_read' # password=parse.quote_plus('Qx@123456') # tablename='all_fault_info' # db_res_engine = create_engine( # "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( # user, password, host, port, db # )) #定时任务....................................................................................................................................................................... cell_platd_test(df_sn, df_first_data_time) # scheduler = BlockingScheduler() # scheduler.add_job(fun, 'interval', seconds=600, id='diag_job') # try: # scheduler.start() # except Exception as e: # scheduler.shutdown() # logger.error(str(e))