实习程序4

# coding: utf-8# In[1]:import pymysql as mysqldbimport pandas as pdimport pymysql.cursorsimport influxdbimport datetimeimport osfrom influxdb import InfluxDBClientfrom influxdb import DataFrameClientfrom sqlalchemy import create_engineimport time# In[2]:system_variable = os.environdef through_environ_connect():    connection = pymysql.connect(host=system_variable['MYSQL_HOST'],                                 port=int(system_variable['MYSQL_PORT']),                                 user=system_variable['MYSQL_USER'],                                 password=system_variable['MYSQL_PASSWORD'],                                 db=system_variable['MYSQL_DST_DB'],                                 charset='utf8',                                 cursorclass=pymysql.cursors.DictCursor)    cursor = connection.cursor()    print("environment way")    return connection,cursordef through_direct_connect():    connection = pymysql.connect(host='120.77.86.1',                                 port=6091,                                 user='root',                                 password='poi-t#admin',                                 db='poit_cloud2.0',                                 charset='utf8',                                 cursorclass=pymysql.cursors.DictCursor)    cursor = connection.cursor()    print("normal local way")    return connection,cursor# In[3]:try:    client = DataFrameClient(host=system_variable['INFLUXDB_HOST'], port=int(system_variable['INFLUXDB_PORT'])                                 ,username=system_variable['INFLUXDB_USER'],                                 password=system_variable['INFLUXDB_PASSWORD'],                                 database='original_data_db')    print("Influxdb environment way")except:    client = DataFrameClient(host='120.77.86.1', port=6093, username='root', password='poi-t#admin', database='original_data_db')    print("Influxdb normal local way")def update_operating_rate_day():# In[4]:    def change_to_right_type(DataFrame):        DataFrame = DataFrame.reset_index()        DataFrame = DataFrame.rename(columns={'index':'dt'})            def change_right_type(time):            return str(time).rsplit('+')[0]            DataFrame['dt'] = DataFrame['dt'].apply(change_right_type)            def convert_str_to_datetime(time_str):            #time_str = datetime.datetime.strptime(time_str, "%Y-%m-%d %H:%M:%S")            #return time_str                time_str = datetime.datetime.strptime(time_str, "%Y-%m-%d %H:%M:%S")            time_str =  time_str + datetime.timedelta(hours=8)            return time_str             DataFrame['dt'] = DataFrame['dt'].apply(convert_str_to_datetime)            def get_the_year(time_str):            time_year = time_str.year            return time_year        def get_the_month(time_str):            time_month = time_str.month            return time_month        def get_the_day(time_str):            time_day = time_str.day            return time_day        def get_the_hour(time_str):            time_hour = time_str.hour            return time_hour            DataFrame['year'] = DataFrame['dt'].apply(get_the_year)        DataFrame['month'] = DataFrame['dt'].apply(get_the_month)        DataFrame['day'] = DataFrame['dt'].apply(get_the_day)        return DataFrame            # In[9]:        def change_time_in_influxdb_sql(time):        time = str(time)        time = time.split(' ')[0] + 'T'+time.split(' ')[1] + '+08:00'        return time            # In[6]:        connection,cursor = through_direct_connect()    sql = "SELECT eid,id,refer_meter,rated_current FROM e_area WHERE type='设备'"    cursor.execute(sql)    e_area = cursor.fetchall()    cursor.close()    connection.close()            # In[7]:        sql = "SELECT * FROM s_electric_meter_data ORDER BY time DESC LIMIT 1"    newest_time = client.query(sql)['s_electric_meter_data'].reset_index()['index'][0]    newest_time = str(newest_time).rsplit('+')[0]    newest_time = datetime.datetime.strptime(newest_time, "%Y-%m-%d %H:%M:%S")    newest_time = newest_time + datetime.timedelta(hours=8)                try:        INTERVAL_DAYS = int(system_variable['INTERVAL_DAYS'])    except:        INTERVAL_DAYS = 30            before_time = datetime.datetime.strptime(newest_time.strftime('%Y-%m-%d'), \                                             '%Y-%m-%d') - datetime.timedelta(days=INTERVAL_DAYS)        append_list = []    for each in e_area:        eid = each['eid']        tid = each['id']        rated_current = each['rated_current']        refer_meter = each['refer_meter']                if rated_current and  refer_meter and  eid and tid :            sql_all = """SELECT COUNT(*) FROM s_electric_meter_data WHERE eid='%s' AND                     tag_name='%s' AND type='IA' AND time<'%s' AND time>'%s' GROUP BY time(1d) fill(none) TZ('Asia/Shanghai')""" % (str(eid), str(refer_meter), change_time_in_influxdb_sql(newest_time), change_time_in_influxdb_sql(before_time))            sql_run = """SELECT COUNT(*) FROM s_electric_meter_data WHERE eid='%s' AND                     tag_name='%s' AND type='IA' AND time<'%s' AND value>%d  AND time>'%s' GROUP BY time(1d) fill(none) TZ('Asia/Shanghai')""" % (str(eid), str(refer_meter), change_time_in_influxdb_sql(newest_time),rated_current*0.1, change_time_in_influxdb_sql(before_time))            sql_on = """SELECT COUNT(*) FROM s_electric_meter_data WHERE eid='%s' AND                     tag_name='%s' AND type='IA' AND time<'%s' AND value>0  AND time>'%s' GROUP BY time(1d) fill(none) TZ('Asia/Shanghai')""" % (str(eid), str(refer_meter), change_time_in_influxdb_sql(newest_time), change_time_in_influxdb_sql(before_time))                    data_all = client.query(sql_all)            data_run = client.query(sql_run)            data_on = client.query(sql_on)                        if len(data_all) != 0 and len(data_run) != 0 and len(data_on) != 0:                            data_all = data_all['s_electric_meter_data']                #print("this is the length of data_all:",len(data_all))                data_run = data_run['s_electric_meter_data']                #print("this is the length of data_run:",len(data_run))                data_on = data_on['s_electric_meter_data']                #print("this is the length of data_on:",len(data_on))                data_all = change_to_right_type(data_all)                data_run = change_to_right_type(data_run)                data_on = change_to_right_type(data_on)                                                data_all['operating_rate'] = data_on['count_value'] / data_all['count_value'] * 100                #print("this is data_all operating_rate:", data_all['operating_rate'])                data_all['effective_operating_rate'] = data_run['count_value'] / data_all['count_value'] * 100                #print("this is data_all effective_operating_rate:", data_all['effective_operating_rate'])                data_all['eid'] = eid                data_all['tid'] = tid                data_all['run_time'] = data_all['effective_operating_rate'].map(lambda x : x * 86400)                now_time = datetime.datetime.now()                data_all.loc[data_all['dt'] > datetime.datetime.strptime(now_time.strftime('%Y-%m-%d'), '%Y-%m-%d'),                          'run_time'] = (now_time.hour * 3600 + now_time.minute * 60 + now_time.second) * data_all.loc[data_all['dt'] > datetime.datetime.strptime(now_time.strftime('%Y-%m-%d'), '%Y-%m-%d'),                          'effective_operating_rate']                data_all.drop(['dt', 'count_value'],axis=1,inplace=True)                data_all['run_time'] = data_all['run_time'] / 100                append_list.append(data_all)            # In[25]:                # In[26]:        last_append = pd.DataFrame(None, columns=['eid','tid','operating_rate','effective_operating_rate','run_time','year','month','day'])            # In[27]:        for each in append_list:        last_append = last_append.append(each)            try:        connection,cursor = through_environ_connect()      except:        connection,cursor = through_direct_connect()        sql = "delete from stream_operating_rate_day where year>%d" % (before_time.year)    cursor.execute(sql)    connection.commit()    cursor.close()    connection.close()        try:        connection,cursor = through_environ_connect()      except:        connection,cursor = through_direct_connect()            sql = "delete from stream_operating_rate_day where year=%d and month>%d" % (before_time.year,before_time.month)    cursor.execute(sql)    connection.commit()    cursor.close()    connection.close()        try:        connection,cursor = through_environ_connect()      except:        connection,cursor = through_direct_connect()            sql = "delete from stream_operating_rate_day where year=%d and month=%d and day>=%d" % (before_time.year,before_time.month,before_time.day)    cursor.execute(sql)    connection.commit()    cursor.close()    connection.close()            # In[29]:        try:        engine = create_engine("mysql+pymysql://"+system_variable['MYSQL_USER']+":"+system_variable['MYSQL_PASSWORD']                             +"@"+system_variable['MYSQL_HOST']+":"+str(system_variable['MYSQL_PORT'])+                             "/" + system_variable['MYSQL_DST_DB'] + "?charset=utf8",echo=True)        print("engine environment way")    except:        engine = create_engine("mysql+pymysql://root:poi-t#admin@120.77.86.1:6091/poit_cloud2.0?charset=utf8",echo=True)        print("engine normal local way")            # In[30]:        last_append.to_sql(name='stream_operating_rate_day', con=engine, if_exists = 'append', index=False, chunksize=1000)begin_time = time.time()update_operating_rate_day()end_time = time.time()print('runing time is:', end_time-begin_time)