实习程序5
来源:互联网 发布:游戏程序员待遇 编辑:程序博客网 时间:2024/06/08 06:50
# 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]: try: connection,cursor = through_environ_connect() except: connection,cursor = through_direct_connect() try: sql = "SELECT eid,id,refer_meter,rated_current FROM " + "`" + system_variable['MYSQL_SRC_DB'] + "`" + "." + " e_area WHERE type='设备'" except: 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) # In[24]: 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' GROUP BY time(1d) fill(none) TZ('Asia/Shanghai')""" % (str(eid), str(refer_meter), change_time_in_influxdb_sql(newest_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 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) 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 GROUP BY time(1d) fill(none) TZ('Asia/Shanghai')""" % (str(eid), str(refer_meter), change_time_in_influxdb_sql(newest_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'] data_run = data_run['s_electric_meter_data'] data_on = data_on['s_electric_meter_data'] 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 data_all['effective_operating_rate'] = data_run['count_value'] / data_all['count_value'] * 100 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['run_time'] = data_all['run_time'] / 100 data_all.drop(['dt', 'count_value'],axis=1,inplace=True) 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 = "truncate stream_operating_rate_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)
阅读全文
0 0
- 实习程序5
- 实习程序收藏
- android实习程序
- android实习程序2
- android实习程序3
- 编程实习程序
- 实习程序1
- 实习程序2
- 实习程序3
- 实习程序4
- 实习程序6
- abdroid实习程序5——切换过程、Ball
- 菜鸟程序实习日记2
- 我的程序人生之实习生活
- android实习程序 ——短信
- 程序菜鸟的实习日记1
- 《Java程序实习》日记(周三)
- 《Java程序实习》日记(周四)
- log4j2自定义Appender(输出到文件/RPC服务中)
- 文章标题
- activiti和spring整合配置例子
- poj3616(dp)
- 存储管理
- 实习程序5
- POJ 3259 Wormholes (Bellman-Ford/SPFA 判断是否存在负权环)
- Python中字符串常见操作
- 行政区划官方数据--java对象或json-java爬虫获取
- SwipeRefreshLayout自定义刷新效果MaterialRefreshLayout
- 实习程序6
- Unity文件操作路径
- 洛谷 P1941 飞扬的小鸟 题解
- 关于Google浏览器使用layer弹出层video标签播放视屏无法全屏播放问题