"""Python监测户外温度，存入数据库，绘图，得出最高、低、平均温度准备工作：购买智能温度计传感器，安装电脑软件将温度传感器放到户外，USB连接到电脑，注意防范雷电及其他任何影响安全因素安装Mysql 或是 Postgresql等数据库pip安装pyecharts等绘图工具包待收集温度数据(CSV格式）后，temperatusre_tosql(do_dt, file_path, place)存入数据库resample_wm(df, rule_wm='1H')按小时或天聚合数据"""# -*- coding:utf-8 -*-import pandas as pdimport datetimeimport pymysqlfrom sqlalchemy import create_enginepymysql.install_as_MySQLdb()from pyecharts.charts import Lineimport pyecharts.options as opts"""先要建立mysql,postgresql等数据库链接"""engine_mysql = create_engine('mysql+pymysql://用户名:密码@端口:/数据库名?charset=utf8')engine_pg = create_engine("postgresql+psycopg2://用户名:,密码@端口:/数据库名", client_encoding='utf8')def temperatusre_tosql(do_dt, file_path, place):    try:        df = pd.read_csv(file_path, header=0, encoding="utf_8", names=["time", "temperature"])        df['time'] = df['time'].str.replace('年', ':')                             #删除年月日变成标准时间格式        df['time'] = df['time'].str.replace('月', ':')        df['time'] = df['time'].str.replace('日', '')        df['place'] = place                                                        #增加地理位置标签        df_yes = df[(df['time'].str.contains(do_dt))]        print(df_yes.tail())        df.to_sql('temperature', engine_pg, index=False, if_exists='append')        print(place, ' yesterdays temperature to sql is ok . ')    except Exception as err:        print('temperature is err.', err)def df_dt_get(do_date):    df_nv = df.loc[df['time'].str.contains(do_date), :]    df_nv = df_nv.sort_values(by=[item1], ascending=True)                           # 按日期升序排序    df_nv = df_nv.set_index([item1])    df_nv = df_nv.reset_index(drop=False)                                           # 重新设定序号    print(df_nv)    return df_nvdef resample_wm(df, rule_wm='1H'):    """小时或天聚合平均 1H, 1D"""    df['time'] = pd.to_datetime(df['time'], format='%Y:%m:%d %H:%M:%S')    df.drop_duplicates(subset=['time', 'temperature'], keep='first', inplace=True)  # 去重    item1 = 'time'    df = df.sort_values(by=[item1], ascending=True)                                 # 按日期升序排序    df = df.set_index([item1])    df = df.reset_index(drop=False)                                                 # 重新设定序号    print(df)    df = df.set_index(df['time'])    df = df[['temperature']]    df = df.resample(rule=rule_wm).mean()    print(df)    return dfdef tr(x):    return datetime.datetime.strptime(x['time'], "%Y:%m:%d %H:%M:%S")def tr_wm(x):    return datetime.datetime.strftime(x, "%H:%M:%S")if __name__ == '__main__':    file_path1 = "H:\\.......\2022-10-07 8284C6590F10C3EC.csv"                     #温度原始文件存入目录    place_1 = 'outdoors'    temperature_db = 'temperature'    now = datetime.datetime.now()    do_time = int(now.strftime("%H%M%S"))    print(do_time)    temperatusre_tosql_all_normal(file_path1, place_1)                             #加载数据入库    #绘图    yes_dt = datetime.datetime.now() - datetime.timedelta(days=1)                  # 昨天    yes_date = yes_dt.strftime("%Y:%m:%d")    yes_date = '2022:10:09'    dt_sim = yes_date[0:4]+yes_date[5:7]+yes_date[8:]    print('yes_date', yes_date)    file_wm = r"d:\.......\{}.html".format(dt_sim)                                 #绘图存入文件  html格式    query = "select * from temperature where place = 'outdoors';"    print(query)    df = pd.read_sql(r'{}'.format(query), con=pg_dell)    df = df[(df['time'].str.contains(yes_date))]                                   #筛选数据    df.drop_duplicates(subset=['time', 'temperature'], keep='first', inplace=True) # 去重    item1 = 'time'    yes_date = ''    df1 = df_dt_get(yes_date)    print('df1', len(df1))    df_1h = resample_wm(df1, rule_wm='1H')                                          #按小时聚合数据    print(df_1h)    high = df1['temperature'].max()    low = df1['temperature'].min()    mean = df1['temperature'].mean()    df['time'] = df.apply(tr, axis=1)    df_wm = df1    df_wm['time_wm'] = df_wm['time'].apply(lambda x: tr_wm(x))    line = (        Line()        .add_xaxis(df_wm.time_wm.tolist())        .add_yaxis(dt_sim, df_wm.temperature.tolist())        .set_global_opts(title_opts=opts.TitleOpts(title="户外温度", subtitle="北京平谷"))    )    line.render(file_wm)  #    print(dt_sim, '户外温度监测数据量：', len(df1))    print(dt_sim, '最高户外温度时间：', df1[(df1['temperature'] == high)].iloc[-1].time, '温度： ', round(high, 2))    print(dt_sim, '最低户外温度时间：', df1[(df1['temperature'] == low)].iloc[-1].time, '温度： ', round(low, 2))    print(dt_sim, '平均户外温度：', round(mean, 2))    """    运行结果：    2022-10-09 13:00:00    17.803578    2022-10-09 14:00:00    16.364997    2022-10-09 15:00:00    16.633715    2022-10-09 16:00:00    15.120329    2022-10-09 17:00:00    13.687756    2022-10-09 18:00:00    12.673359    2022-10-09 19:00:00    11.576553    2022-10-09 20:00:00    11.166772    2022-10-09 21:00:00    10.640770    2022-10-09 22:00:00     9.999944    2022-10-09 23:00:00     9.780065    20221009 户外温度监测数据量： 105883    20221009 最高户外温度时间： 2022-10-09 11:51:19 温度：  23.25    20221009 最低户外温度时间： 2022-10-09 04:48:18 温度：  7.56    20221009 平均户外温度： 13.09    """    """    Pgabc 2022000008    author : Pgabc        """