data_analyze_platform/LIB/FRONTEND/odo/deploy.py

#coding=utf-8
# 计算里程
from math import radians, cos, sin, asin, sqrt
import pandas as pd
import numpy as np
from datetime import datetime
from datetime import timedelta
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from GpsRank import *
from ProcessDfBms import *
from ProcessDfGps import *
from LIB.MIDDLE.odo.CalDist import *
from LIB.BACKEND import DBManager, Log
import pdb
from urllib import parse
import traceback
import os
import time
import pymysql

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'])
    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())[0:10]
    if not os.path.exists('log/' + now_str):
        os.makedirs('log/' + now_str)
    log = Log.Mylog(log_name='odo', 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()))

    
    asset_table_path='asset_table.xlsx'

    asset_sheet_num=2
    usecols_list=[4,5]

    asset_table=pd.read_excel(asset_table_path,sheet_name=asset_sheet_num,skiprows=1,usecols=usecols_list)
    SN_list=asset_table['SN号'].values.tolist()

    asset_table=asset_table.rename(columns={'SN号':'SN','状态':'state'})

    asset_table.set_index(["SN"],inplace=True)
    col_name=asset_table.columns.tolist()
    col_name.extend(['range','accum_soc','day_start_soc','day_end_soc','day_start_time','day_end_time'])
    asset_table=asset_table.reindex(columns=col_name)

    start_hour='00:00:00'#每日查询最早时间
    end_hour='23:59:00'#每日查询最晚时间


    date_index=pd.date_range('2021-07-01','2021-07-31')
    for date in date_index:
        '''遍历日期'''

        str_date=str(date)[:10]
        input_starttime=str_date+' '+start_hour
        input_endtime=str_date+' '+end_hour
        test_day=str_date[5:10]#月-日，用于建立sheet

        drive_info_aday=pd.DataFrame()
        SN_list_short=SN_list#先选择了0:50,50:end

        for SN in SN_list_short:
            '''遍历SN号'''
            SN=SN.strip('\t')
            SN=SN.strip('\n')

            try:
                logger.info("{}---{} START!".format(SN, str(str_date)))
                range=GetDistInfo(SN,input_starttime,input_endtime)
                range_df=pd.DataFrame([range])
                drive_info_aday=pd.concat([drive_info_aday,range_df],axis=0)
                logger.info("{}---{} DONE!".format(SN, str(str_date)))

            except Exception as e:
                logger.error(traceback.format_exc)
                logger.error(u"{} ：{},{} 任务运行错误".format(SN,input_starttime,input_endtime), exc_info=True)
        drive_info_aday['time'] = str_date
        drive_info_aday.columns = ['sn', 'odo', 'accum_soc', 'start_soc', 'end_soc', 'start_time','end_time', 'min_soc', 'time']
        drive_info_aday.to_sql("odo_result",con=db_engine, if_exists="append",index=False)