def plot_inflow_of_all_main_river(title="四川当月水库蓄水量信息.html", day_delta=30, path="source"): """ 绘制四川当月所有河流的蓄水量信息。 :param title: :return: """ engine = init_engine(db="power_supply") datetime_now = datetime.datetime.now() last_month = ( datetime_now - datetime.timedelta(days=day_delta)).strftime("%Y-%m-%d 00:00:00") df1 = pd.read_sql( "SELECT * FROM sc_main_river_storage WHERE official_update_time> '{last_month}'" .format(last_month=last_month), engine) line2 = Line("全流域蓄水量", width="1600px", height="800px") df1_1 = df1.groupby(axis=0, by=["official_update_time" ]).aggregate({"water_storage_capacity": "sum"}) df1_2 = df1_1.reset_index() line2.add( "全流域河流蓄水量图", df1_2["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_2["water_storage_capacity"]) line2.render(title)
def get_basic_trade_table(): engine = init_engine() basic_table = settings["TRADE_TABLE"] df = pd.read_sql( "select * from {basic_trade_table}".format( basic_trade_table=basic_table), engine) del engine return df
def plot_in_out_of_all_main_river(title="全流域信息来水出水.html", day_delta=30): """ 将河流的入水量,出水量信息按时间统计求和。 :param day_delta: :return: """ engine1 = init_engine(db="power_supply", ) datetime_now = datetime.datetime.now() last_month = ( datetime_now - datetime.timedelta(days=day_delta)).strftime("%Y-%m-%d 00:00:00") df1 = pd.read_sql( "SELECT * FROM sc_main_river_inflow WHERE official_update_time> '{last_month}'" .format(last_month=last_month), engine1) df2 = pd.read_sql( "SELECT * FROM sc_main_river_outflow WHERE official_update_time> '{last_month}'" .format(last_month=last_month), engine1) print(df2["river_system"].unique()) line2 = Line("全流域水流量", width="1600px", height="800px") df1_1 = df1.groupby(axis=0, by=["official_update_time" ]).aggregate({"water_inflow": "sum"}) df1_2 = df1_1.reset_index() df2_1 = df2.groupby(axis=0, by=["official_update_time" ]).aggregate({"water_outflow": "sum"}) df2_2 = df2_1.reset_index() line2.add( "全流域河流水量图-来", df1_2["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_2["water_inflow"]) line2.add( "全流域河流水量图-出", df2_2["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df2_2["water_outflow"]) line2.render(title)
def get_detail_trade_table(basic_table_id=None, detail_table_id=None, trade_type=None): """ 用来获得某场交易的详细信息! :param basic_table_id: 某批次的所有交易信息 :param detail_table_id: 具体某批次中某场交易的详细信息 :param trade_type: 交易的详细 :return: """ assert basic_table_id or detail_table_id, "至少必须指明一个交易id" assert not (basic_table_id and detail_table_id), "只能指明一个id" if detail_table_id: assert trade_type, "必须指明交易类型" assert trade_type in ["fsch", "jzzr", "gpjy"], "交易必须是‘复式撮合’, ‘集中转让’, ‘挂牌交易’中的某一个" engine = init_engine() basic_table = settings["TRADE_TABLE"] map_relation = settings["TRADE_TYPE_MAP"] map_relation2 = settings["TRADE_TYPE_MAP2"] detail_table = map_relation2[trade_type] if basic_table_id: df1 = pd.read_sql( "select * from {basic_trade_table} WHERE trade_id ='{trade_id}'". format(basic_trade_table=basic_table, trade_id=basic_table_id), engine) if not df1.empty: trade_type = df1["trade_type"].values[0] table_name = map_relation[str(trade_type)] if trade_type != "0": detail_table = pd.read_sql( "select * from {detail_table} where `GROUP_ID`='{basic_table_id}' " .format(detail_table=table_name, basic_table_id=basic_table_id), engine) else: detail_table = pd.read_sql( "select * from {detail_table} where `KEY`='{basic_table_id}' " .format(detail_table=table_name, basic_table_id=basic_table_id), engine) return detail_table else: raise Exception( "交易_{trade_id}__不存在!!".format(trade_id=basic_table_id)) else: try: if detail_table: if trade_type != "gpjy": df_detail = pd.read_sql( "select * from {detail_table} where `TRID`='{trade_id}'" .format(detail_table=detail_table, trade_id=detail_table_id), engine) else: df_detail = pd.read_sql( "select * from {detail_table} where `KEY`='{trade_id}'" .format(detail_table=detail_table, trade_id=detail_table_id), engine) return df_detail except Exception as e: print(e)
def get_trade_data(start_time, end_time, trade_id): engine = init_engine() df = pd.read_sql( "select * from fsch_declare_loadDataByTime where trade_id = {trade_id}" .format(trade_id=trade_id), engine) return df
def __init__(self): self.settings = Settings() self.settings.setmodule("PowerShare.setting", priority="project") self.engine = init_engine()
def plot_storage_8_river(pic_name="8大流域蓄水量图.html"): """ 画8条河域的蓄水量图 :param pic_name: "picture——name" :return: None """ engine = init_engine(db="power_supply") df1 = get_data(engine=engine, table_name="sc_main_river_storage") df1_tail = "" columns_name1 = "water_storage_capacity" df1_mj = df1[df1["river_system"] == "岷江"] df1_jlj = df1[df1["river_system"] == "嘉陵江"] df1_ddh = df1[df1["river_system"] == "大渡河"] df1_fj = df1[df1["river_system"] == "涪江"] df1_qj = df1[df1["river_system"] == "渠江"] df1_jsj = df1[df1["river_system"] == "金沙江"] df1_ylj = df1[df1["river_system"] == "雅砻江"] df1_qyj = df1[df1["river_system"] == "青衣江"] print(df1["river_system"].unique()) grid = Grid(width="1600px", height="800px", page_title="来水量信息图") line1 = Line( "嘉陵江", title_text_size="80%", title_pos="2%", title_top="2%", ) line1.add( "嘉陵江{}".format(df1_tail), df1_jlj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_jlj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30") line2 = Line("岷江", title_text_size="80%", title_pos="25%", title_top="2%") line2.add( "岷江{}".format(df1_tail), df1_mj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_mj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) line3 = Line("大渡河", title_text_size="80%", title_pos="50%", title_top="2%") line3.add( "大渡河{}".format(df1_tail), df1_ddh["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_ddh[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, # yaxis_force_interval="vertical" xaxis_rotate="30", yaxis_rotate="30", ) line4 = Line("涪江", title_text_size="80%", title_pos="75%", title_top="2%") line4.add( "涪江{}".format(df1_tail), df1_fj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_fj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, # yaxis_force_interval="vertical" xaxis_rotate="30", yaxis_rotate="30", ) line5 = Line("渠江", title_text_size="80%", title_pos="2%", title_top="50%") line5.add( "渠江{}".format(df1_tail), df1_qj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_qj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) line6 = Line("金沙江", title_text_size="80%", title_pos="25%", title_top="50%") line6.add( "金沙江{}".format(df1_tail), df1_jsj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_jsj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, # yaxis_force_interval="vertical" xaxis_rotate="30", yaxis_rotate="30", ) line7 = Line("雅砻江", title_text_size="80%", title_pos="50%", title_top="50%") line7.add( "雅砻江{}".format(df1_tail), df1_ylj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_ylj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) line8 = Line("青衣江", title_text_size="80%", title_pos="75%", title_top="50%") line8.add( "青衣江{}".format(df1_tail), df1_qyj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_qyj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) grid.add(line1, grid_right="75%", grid_bottom="55%", grid_left="2.5%") grid.add(line2, grid_right="50%", grid_bottom="55%", grid_left="27.5%") grid.add(line3, grid_right="25%", grid_bottom="55%", grid_left="52.5%") grid.add(line4, grid_right="0%", grid_bottom="55%", grid_left="77.5%") grid.add(line5, grid_right="75%", grid_top="55%", grid_left="2.5%") grid.add(line6, grid_right="50%", grid_top="55%", grid_left="27.5%") grid.add(line7, grid_right="25%", grid_top="55%", grid_left="52.5%") grid.add(line8, grid_right="0%", grid_top="55%", grid_left="77.5%") grid.render(pic_name)
def plot_in_out_8_river(pic_name="8大流域来水量出水量图.html"): """ 1. 画8张图,每个小图两条线,为入水量和出水量 :param df1: 入水量的dataframe :param df2: 出水量的dataframe :param columns_name1: 列名1 :param columns_name2: 列名2 ,如果画蓄水量,这个列名变量不会被使用 :param pic_name: "picture——name" :param df1_tail: "legend的尾巴,出/入" :return: None """ df1_tail = "-进" df2_tail = "-出" engine1 = init_engine(db="power_supply") df1 = get_data(engine1, "sc_main_river_inflow") df2 = get_data(engine1, "sc_main_river_outflow") columns_name1 = "water_inflow" columns_name2 = "water_outflow" df1_mj = df1[df1["river_system"] == "岷江"] df1_jlj = df1[df1["river_system"] == "嘉陵江"] df1_ddh = df1[df1["river_system"] == "大渡河"] df1_fj = df1[df1["river_system"] == "涪江"] df1_qj = df1[df1["river_system"] == "渠江"] df1_jsj = df1[df1["river_system"] == "金沙江"] df1_ylj = df1[df1["river_system"] == "雅砻江"] df1_qyj = df1[df1["river_system"] == "青衣江"] print(df1["river_system"].unique()) grid = Grid(width="1600px", height="800px", page_title="来水量信息图") line1 = Line( "嘉陵江", title_text_size="80%", title_pos="2%", title_top="2%", ) line1.add( "嘉陵江{}".format(df1_tail), df1_jlj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_jlj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30") line2 = Line("岷江", title_text_size="80%", title_pos="25%", title_top="2%") line2.add( "岷江{}".format(df1_tail), df1_mj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_mj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) line3 = Line("大渡河", title_text_size="80%", title_pos="50%", title_top="2%") line3.add( "大渡河{}".format(df1_tail), df1_ddh["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_ddh[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, # yaxis_force_interval="vertical" xaxis_rotate="30", yaxis_rotate="30", ) line4 = Line("涪江", title_text_size="80%", title_pos="75%", title_top="2%") line4.add( "涪江{}".format(df1_tail), df1_fj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_fj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, # yaxis_force_interval="vertical" xaxis_rotate="30", yaxis_rotate="30", ) line5 = Line("渠江", title_text_size="80%", title_pos="2%", title_top="50%") line5.add( "渠江{}".format(df1_tail), df1_qj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_qj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) line6 = Line("金沙江", title_text_size="80%", title_pos="25%", title_top="50%") line6.add( "金沙江{}".format(df1_tail), df1_jsj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_jsj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, # yaxis_force_interval="vertical" xaxis_rotate="30", yaxis_rotate="30", ) line7 = Line("雅砻江", title_text_size="80%", title_pos="50%", title_top="50%") line7.add( "雅砻江{}".format(df1_tail), df1_ylj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_ylj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) line8 = Line("青衣江", title_text_size="80%", title_pos="75%", title_top="50%") line8.add( "青衣江{}".format(df1_tail), df1_qyj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df1_qyj[columns_name1], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) df2_mj = df2[df2["river_system"] == "岷江"] df2_jlj = df2[df2["river_system"] == "嘉陵江"] df2_ddh = df2[df2["river_system"] == "大渡河"] df2_fj = df2[df2["river_system"] == "涪江"] df2_qj = df2[df2["river_system"] == "渠江"] df2_jsj = df2[df2["river_system"] == "金沙江"] df2_ylj = df2[df2["river_system"] == "雅砻江"] df2_qyj = df2[df2["river_system"] == "青衣江"] line8.add( "青衣江-{}".format(df2_tail), df2_qyj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df2_qyj[columns_name2], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) line7.add( "雅砻江-{}".format(df2_tail), df2_ylj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df2_ylj[columns_name2], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) line6.add( "金沙江-{}".format(df2_tail), df2_jsj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df2_jsj[columns_name2], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, # yaxis_force_interval="vertical" xaxis_rotate="30", yaxis_rotate="30", ) line5.add( "渠江-{}".format(df2_tail), df2_qj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df2_qj[columns_name2], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) line4.add( "涪江-{}".format(df2_tail), df2_fj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df2_fj[columns_name2], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, # yaxis_force_interval="vertical" xaxis_rotate="30", yaxis_rotate="30", ) line3.add( "大渡河-{}".format(df2_tail), df2_ddh["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df2_ddh[columns_name2], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, # yaxis_force_interval="vertical" xaxis_rotate="30", yaxis_rotate="30", ) line2.add( "岷江-{}".format(df2_tail), df2_mj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df2_mj[columns_name2], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30", ) line1.add( "嘉陵江-{}".format(df2_tail), df2_jlj["official_update_time"].map(lambda x: x.strftime("%Y-%m-%d")), df2_jlj[columns_name2], yaxis_label_textsize="50%", xaxis_label_textsize="50%", is_legend_show=False, yaxis_max=12000, xaxis_rotate="30", yaxis_rotate="30") grid.add(line1, grid_right="75%", grid_bottom="55%", grid_left="2.5%") grid.add(line2, grid_right="50%", grid_bottom="55%", grid_left="27.5%") grid.add(line3, grid_right="25%", grid_bottom="55%", grid_left="52.5%") grid.add(line4, grid_right="0%", grid_bottom="55%", grid_left="77.5%") grid.add(line5, grid_right="75%", grid_top="55%", grid_left="2.5%") grid.add(line6, grid_right="50%", grid_top="55%", grid_left="27.5%") grid.add(line7, grid_right="25%", grid_top="55%", grid_left="52.5%") grid.add(line8, grid_right="0%", grid_top="55%", grid_left="77.5%") grid.render(pic_name)