实习程序3
来源：互联网 发布：完美云课堂软件 编辑：程序博客网 时间：2024/06/07 23:09
# coding: utf-8# In[31]:#导入必要模块import pymysql as mysqldbimport pandas as pdimport pymysql.cursorsimport influxdbimport datetimeimport osfrom influxdb import InfluxDBClientfrom influxdb import DataFrameClientfrom sqlalchemy import create_engineimport time# -*- coding: utf-8 -*-#encoding=utf-8# In[32]:def 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,cursordef 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,cursor# In[33]:def update_stream_equipment_status_day():#连接数据库 try: connection,cursor = through_environ_connect() except: connection,cursor = through_direct_connect() #拿到e_area在type=‘设备’上面的相关数据 try: sql = "SELECT eid,id,parent_id,refer_meter,rated_current FROM " + "`" + system_variable['MYSQL_SRC_DB'] + "`" + "." + "e_area WHERE type='设备'" except: sql = "SELECT eid,id,parent_id,refer_meter,rated_current FROM e_area WHERE type='设备'" cursor.execute(sql) e_area = cursor.fetchall() #关闭Mysql数据库 cursor.close() connection.close() # In[34]: 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") # In[35]: def add_status_column(DataFrame): if DataFrame['value']==0: return "停机" elif (DataFrame['value']>0) and (DataFrame['value']<DataFrame['rated_current']*0.1): return "待机" elif DataFrame['value']>= DataFrame['rated_current']*0.1: return "运行" else: return "通讯异常" # In[36]: #定义变换数据结构函数，将输入的数据变成需要的数据结构 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 #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[37]: def get_now_value_range(series): if series['status'] == '待机': range_begin = 0 range_end = 0.1 * series['rated_current'] elif series['status'] == '运行': range_begin = 0.1 * series['rated_current'] range_end = 10000000000000000000 elif series['status'] == '停机': range_begin = -0.000000000000000000000000000000000000001 range_end = 0.000000000000000000000000000000000000000001 else: range_begin = -100000000000000000000000 range_end = 0 return range_begin,range_end # In[30]: append_list = [] for each in e_area: eid = each['eid'] tid = each['id'] rated_current = each['rated_current'] refer_meter = each['refer_meter'] sql = """SELECT time,value FROM s_electric_meter_data WHERE eid='%s' AND tag_name='%s' AND type='IA' ORDER BY time DESC LIMIT 1""" \ % (str(eid), str(refer_meter)) data = client.query(sql) #print(data) if len(data) != 0: data = data['s_electric_meter_data'] append_data = data.head(1).copy() append_data['eid'] = eid append_data['tid'] = tid append_data['rated_current'] = rated_current append_data['status'] = append_data.apply(add_status_column,axis=1) append_data = change_to_right_type(append_data) range_begin,range_end = get_now_value_range(append_data.loc[0]) #timem = time.time() sql = """SELECT time,value FROM s_electric_meter_data WHERE value>=%f AND tag_name='%s' AND type='IA' AND eid='%s' AND time>NOW()-90d ORDER BY time DESC LIMIT 1""" \ % (range_end, str(refer_meter), str(eid)) begin_time_data1 = client.query(sql) sql = """SELECT time,value FROM s_electric_meter_data WHERE value<=%f AND tag_name='%s' AND type='IA' AND eid='%s' AND time>NOW()-90d ORDER BY time DESC LIMIT 1""" \ % ( range_begin , str(refer_meter), str(eid)) begin_time_data2 = client.query(sql) #print("this is the traverse time:",timemm-timem) if len(begin_time_data1) == 0: begin_time_data2 = begin_time_data2['s_electric_meter_data'] begin_time_data2 = change_to_right_type(begin_time_data2) begin_time_str2 = str(begin_time_data2['dt'][0]) begin_time_str = begin_time_str2 append_data = append_data.rename(columns={'dt':'end_time'}) begin_time = datetime.datetime.strptime(begin_time_str, "%Y-%m-%d %H:%M:%S") append_data['begin_time'] = begin_time elif len(begin_time_data2) == 0: begin_time_data1 = begin_time_data1['s_electric_meter_data'] begin_time_data1 = change_to_right_type(begin_time_data1) begin_time_str1 = str(begin_time_data1['dt'][0]) begin_time_str = begin_time_str1 append_data = append_data.rename(columns={'dt':'end_time'}) begin_time = datetime.datetime.strptime(begin_time_str, "%Y-%m-%d %H:%M:%S") append_data['begin_time'] = begin_time else: begin_time_data1 = begin_time_data1['s_electric_meter_data'] begin_time_data1 = change_to_right_type(begin_time_data1) begin_time_str1 = str(begin_time_data1['dt'][0]) begin_time_data2 = change_to_right_type(begin_time_data2) begin_time_str2 = str(begin_time_data2['dt'][0]) begin_time_str = begin_time_str1 if begin_time_str1 > begin_time_str2 else begin_time_str2 append_data = append_data.rename(columns={'dt':'end_time'}) begin_time = datetime.datetime.strptime(begin_time_str, "%Y-%m-%d %H:%M:%S") append_data['begin_time'] = begin_time append_data.drop(['rated_current','value'],axis=1,inplace=True) append_list.append(append_data) # In[38]: last_append = pd.DataFrame(None, columns=['eid','tid','status','begin_time','end_time']) for each in append_list: last_append = last_append.append(each) # In[39]: try: connection,cursor = through_environ_connect() except: connection,cursor = through_direct_connect() sql = "truncate stream_equipment_status_day" cursor.execute(sql) connection.commit() cursor.close() connection.close() # In[40]: 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_equipment_status_day', con=engine, if_exists = 'append', index=False) time1 = time.time()update_stream_equipment_status_day()time2 = time.time()print("running time:", time2-time1) # In[ ]: 阅读全文
0 0
- android实习程序3
- 实习程序3
- 实习程序收藏
- android实习程序
- android实习程序2
- 编程实习程序
- 实习程序1
- 实习程序2
- 实习程序4
- 实习程序5
- 实习程序6
- 菜鸟程序实习日记2
- 我的程序人生之实习生活
- android实习程序 ——短信
- 程序菜鸟的实习日记1
- 《Java程序实习》日记（周三）
- 《Java程序实习》日记（周四）
- 《Java程序实习》日记（周二）
- Superset CodeForces
- string.Fromat
- Vue.js更改调试地址端口号
- m
- 【JZOJ 3773】 小 P 的烦恼
- 实习程序3
- 通过ResultSet和ResultSetMetaData得到object和columnname的起始点问题
- JQuery eval函数 $.getScript()、$(#id)html（‘’）
- React 组件之间如何交流
- jdk配置
- C语言从零学习第一次笔记
- Android内存优化（使用SparseArray和ArrayMap代替HashMap）
- 收集一些nxp i.mx6 系列芯片开发资料论坛
- 选择结构输出（从大到小）
