data_analyze_platform/LIB/FRONTEND/DrivingRange/deploy_sn_factor.py

#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
from sqlalchemy.dialects.mysql import insert
import time, datetime
import traceback
from LIB.MIDDLE.DrivingRange import UpdtFct
from urllib import parse
import pymysql
import dateutil.relativedelta
from create_table1 import DrivingRangeSnFactorNewest

dbManager = DBManager.DBManager()
if __name__ == "__main__":
    
    # 时间设置
    now_time = datetime.datetime.now()
    pre_time = now_time + dateutil.relativedelta.relativedelta(days=-50)
    end_time=datetime.datetime.strftime(now_time,"%Y-%m-%d 23:59:00")
    start_time=datetime.datetime.strftime(pre_time,"%Y-%m-%d 23:59:00")


    # # 更新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
    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)
    db_qx = pymysql.connect(
        host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com',
        user='qx_read',
        password='Qx@123456',#Qx@123456
        database='qx_cas',
        charset='utf8'
    )
    
    # 日志配置
    now_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()).replace(":","_")
    log_path = 'log/sn_factor/' + now_str
    if not os.path.exists(log_path):
        os.makedirs(log_path)
    log = Log.Mylog(log_name='driving_range', 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()))
    
    # SNdata_6060 = pd.read_excel('骑享资产梳理-20210621.xlsx', sheet_name='6060')
    # SNnums_6060=SNdata_6060['SN号']
    # # SNnums_6060 = ['PK504B00100004019','PK504B00100004029', 'PK504B00100004080', 'PK504B00100004264', 'PK504B10100004331']
    # now_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # now_time = "2021-08-01 00:00:00" # 定时任务开启时，需要删除改行！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

    # now_time=datetime.datetime.strptime(now_time,'%Y-%m-%d %H:%M:%S')
    # start_time=now_time-datetime.timedelta(days=1000)
    # end_time=str(now_time)
    # start_time=str(start_time)
    try:
        #调度周期：每天运行一次。
        #更新所有sn，连读多日的factor，如果start_date和end_date相隔一天，代表更新start_date的factor。
        logger.info(u"{},{} 任务运行开始\n".format(start_time,end_time), exc_info=True)
        # UpdtFct.updtAllSnFct(start_time[0:10],end_time[0:10], db_engine, db_qx, db_qx, sn_table_name='driving_range_sn_factor')
        logger.info(u"{},{} sn 参数更新完成\n".format(start_time,end_time), exc_info=True)
        logger.info(u"{},{} sn 最新参数更新开始\n".format(start_time,end_time), exc_info=True)
        df_res = UpdtFct.updtNewestFctTb(now_time, db_qx, sn_table_name='driving_range_sn_factor')
        df_res = df_res.reset_index(drop=True)
        session = DbSession()
        
        for i in range(0, len(df_res)-1):
            obj = session.query(DrivingRangeSnFactorNewest).filter_by(sn=df_res.loc[i,'sn']).first()
            if not obj:
                obj = DrivingRangeSnFactorNewest(df_res.loc[i,'sn'],df_res.loc[i,'date'],df_res.loc[i,'a0'],df_res.loc[i,'a1'],df_res.loc[i,'a2'],df_res.loc[i,'a3'],df_res.loc[i,'a4'])               
            else:
                obj.date = df_res.loc[i,'date']
                obj.a0 = df_res.loc[i,'a0']
                obj.a1 = df_res.loc[i,'a1']
                obj.a2 = df_res.loc[i,'a2']
                obj.a3 = df_res.loc[i,'a3']
                obj.a4 = df_res.loc[i,'a4']
            session.add(obj)
        session.commit()
        session.close()

        logger.info(u"{},{} sn 最新参数更新完成\n".format(start_time,end_time), exc_info=True)
    except:
        logger.error(traceback.format_exc)
        logger.error(u"{},{} 任务运行错误\n".format(start_time,end_time), exc_info=True)