def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ if not quiet: print("system prm simple elcc") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_system_prm", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "prm_elcc_simple.csv"), "r") as emissions_file: reader = csv.reader(emissions_file) next(reader) # skip header for row in reader: prm_zone = row[0] period = row[1] elcc = row[2] results.append( (scenario_id, prm_zone, period, subproblem, stage, elcc)) insert_temp_sql = """ INSERT INTO temp_results_system_prm{} (scenario_id, prm_zone, period, subproblem_id, stage_id, elcc_simple_mw) VALUES (?, ?, ?, ?, ?, ?);""".format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_system_prm (scenario_id, prm_zone, period, subproblem_id, stage_id, elcc_simple_mw) SELECT scenario_id, prm_zone, period, subproblem_id, stage_id, elcc_simple_mw FROM temp_results_system_prm{} ORDER BY scenario_id, prm_zone, period, subproblem_id, stage_id; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database( scenario_id, subproblem, stage, c, db, results_directory, quiet ): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # TODO: change this to say NPV and have negatives for the cost # components or flag revenue and cost components if not quiet: print("results system cost") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_system_costs", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) df = pd.read_csv(os.path.join(results_directory, "npv.csv")) df["scenario_id"] = scenario_id df["subproblem_id"] = subproblem df["stage_id"] = stage results = df.to_records(index=False) # Register numpy types with sqlite, so that they are properly inserted # from pandas dataframes # https://stackoverflow.com/questions/38753737/inserting-numpy-integer-types-into-sqlite-with-python3 sqlite3.register_adapter(np.int64, lambda val: int(val)) sqlite3.register_adapter(np.float64, lambda val: float(val)) insert_sql = """ INSERT INTO results_system_costs ({}) VALUES ({}); """.format( ", ".join(df.columns), ", ".join(["?"] * (len(df.columns))) ) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=results)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # Hurdle costs if not quiet: print("transmission hurdle costs") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_transmission_hurdle_costs", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "costs_transmission_hurdle.csv"), "r") as tx_op_file: reader = csv.reader(tx_op_file) next(reader) # skip header for row in reader: tx_line = row[0] period = row[1] timepoint = row[2] timepoint_weight = row[3] number_of_hours_in_timepoint = row[4] lz_from = row[5] lz_to = row[6] hurdle_cost_positve_direction = row[7] hurdle_cost_negative_direction = row[8] results.append(( scenario_id, tx_line, period, subproblem, stage, timepoint, timepoint_weight, number_of_hours_in_timepoint, lz_from, lz_to, hurdle_cost_positve_direction, hurdle_cost_negative_direction, )) insert_temp_sql = """ INSERT INTO temp_results_transmission_hurdle_costs{} (scenario_id, transmission_line, period, subproblem_id, stage_id, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone_from, load_zone_to, hurdle_cost_positive_direction, hurdle_cost_negative_direction) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_transmission_hurdle_costs (scenario_id, transmission_line, period, subproblem_id, stage_id, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone_from, load_zone_to, hurdle_cost_positive_direction, hurdle_cost_negative_direction) SELECT scenario_id, transmission_line, period, subproblem_id, stage_id, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone_from, load_zone_to, hurdle_cost_positive_direction, hurdle_cost_negative_direction FROM temp_results_transmission_hurdle_costs{} ORDER BY scenario_id, transmission_line, subproblem_id, stage_id, timepoint; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ if not quiet: print("project dispatch all") # dispatch_all.csv # Delete prior results and create temporary import table for ordering setup_results_import(conn=db, cursor=c, table="results_project_dispatch", scenario_id=scenario_id, subproblem=subproblem, stage=stage) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "dispatch_all.csv"), "r") as \ dispatch_file: reader = csv.reader(dispatch_file) next(reader) # skip header for row in reader: project = row[0] period = row[1] horizon = row[2] timepoint = row[3] operational_type = row[4] balancing_type = row[5] timepoint_weight = row[6] number_of_hours_in_timepoint = row[7] load_zone = row[8] technology = row[9] power_mw = row[10] results.append( (scenario_id, project, period, subproblem, stage, timepoint, operational_type, balancing_type, horizon, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, power_mw)) insert_temp_sql = """ INSERT INTO temp_results_project_dispatch{} (scenario_id, project, period, subproblem_id, stage_id, timepoint, operational_type, balancing_type, horizon, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, power_mw) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_dispatch (scenario_id, project, period, subproblem_id, stage_id, timepoint, operational_type, balancing_type, horizon, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, power_mw) SELECT scenario_id, project, period, subproblem_id, stage_id, timepoint, operational_type, balancing_type, horizon, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, power_mw FROM temp_results_project_dispatch{} ORDER BY scenario_id, project, subproblem_id, stage_id, timepoint; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False) # Load in the required operational modules required_opchar_modules = get_required_opchar_modules(scenario_id, c) imported_operational_modules = \ load_operational_type_modules(required_opchar_modules) # Import module-specific results for op_m in required_opchar_modules: if hasattr(imported_operational_modules[op_m], "import_module_specific_results_to_database"): imported_operational_modules[op_m]. \ import_module_specific_results_to_database( scenario_id, subproblem, stage, c, db, results_directory, quiet ) else: pass
def import_results_into_database( scenario_id, subproblem, stage, c, db, results_directory, quiet ): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # Capacity cost results if not quiet: print("project capacity costs") setup_results_import( conn=db, cursor=c, table="results_project_costs_capacity", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open( os.path.join(results_directory, "costs_capacity_all_projects.csv"), "r" ) as capacity_costs_file: reader = csv.reader(capacity_costs_file) next(reader) # skip header for row in reader: project = row[0] period = row[1] hours_in_period_timepoints = row[2] hours_in_subproblem_period = row[3] technology = row[4] load_zone = row[5] capacity_cost = row[6] results.append( ( scenario_id, project, period, subproblem, stage, hours_in_period_timepoints, hours_in_subproblem_period, technology, load_zone, capacity_cost, ) ) insert_temp_sql = """ INSERT INTO temp_results_project_costs_capacity{} (scenario_id, project, period, subproblem_id, stage_id, hours_in_period_timepoints, hours_in_subproblem_period, technology, load_zone, capacity_cost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format( scenario_id ) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_costs_capacity (scenario_id, project, period, subproblem_id, stage_id, hours_in_period_timepoints, hours_in_subproblem_period, technology, load_zone, capacity_cost) SELECT scenario_id, project, period, subproblem_id, stage_id, hours_in_period_timepoints, hours_in_subproblem_period, technology, load_zone, capacity_cost FROM temp_results_project_costs_capacity{} ORDER BY scenario_id, project, period, subproblem_id, stage_id;""".format( scenario_id ) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False) # Update the capacity cost removing the fraction attributable to the # spinup and lookahead hours update_sql = """ UPDATE results_project_costs_capacity SET capacity_cost_wo_spinup_or_lookahead = capacity_cost * ( SELECT fraction_of_hours_in_subproblem FROM spinup_or_lookahead_ratios WHERE spinup_or_lookahead = 0 AND results_project_costs_capacity.scenario_id = spinup_or_lookahead_ratios.scenario_id AND results_project_costs_capacity.subproblem_id = spinup_or_lookahead_ratios.subproblem_id AND results_project_costs_capacity.stage_id = spinup_or_lookahead_ratios.stage_id AND results_project_costs_capacity.period = spinup_or_lookahead_ratios.period ) ; """ spin_on_database_lock(conn=db, cursor=c, sql=update_sql, data=(), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ if not quiet: print("project simple elcc") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_project_elcc_simple", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open( os.path.join(results_directory, "prm_project_elcc_simple_contribution.csv"), "r") as elcc_file: reader = csv.reader(elcc_file) next(reader) # skip header for row in reader: project = row[0] period = row[1] prm_zone = row[2] technology = row[3] load_zone = row[4] capacity = row[5] elcc_eligible_capacity = row[6] prm_fraction = row[7] elcc = row[8] results.append(( scenario_id, project, period, subproblem, stage, prm_zone, technology, load_zone, capacity, elcc_eligible_capacity, prm_fraction, elcc, )) insert_temp_sql = """ INSERT INTO temp_results_project_elcc_simple{} (scenario_id, project, period, subproblem_id, stage_id, prm_zone, technology, load_zone, capacity_mw, elcc_eligible_capacity_mw, elcc_simple_contribution_fraction, elcc_mw) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_elcc_simple (scenario_id, project, period, subproblem_id, stage_id, prm_zone, technology, load_zone, capacity_mw, elcc_eligible_capacity_mw, elcc_simple_contribution_fraction, elcc_mw) SELECT scenario_id, project, period, subproblem_id, stage_id, prm_zone, technology, load_zone, capacity_mw, elcc_eligible_capacity_mw, elcc_simple_contribution_fraction, elcc_mw FROM temp_results_project_elcc_simple{} ORDER BY scenario_id, project, period, subproblem_id, stage_id; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database( scenario_id, subproblem, stage, c, db, results_directory, quiet ): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # Fuel burned by project and timepoint if not quiet: print("project fuel burn") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_project_fuel_burn", scenario_id=scenario_id, subproblem=subproblem, stage=stage ) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "fuel_burn.csv"), "r") as fuel_burn_file: reader = csv.reader(fuel_burn_file) next(reader) # skip header for row in reader: project = row[0] period = row[1] horizon = row[2] timepoint = row[3] timepoint_weight = row[4] number_of_hours_in_timepoint = row[5] load_zone = row[6] technology = row[7] fuel = row[8] opr_fuel_burn_tons = row[9] startup_fuel_burn_tons = row[10] total_fuel_burn = row[11] results.append( (scenario_id, project, period, subproblem, stage, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, fuel, opr_fuel_burn_tons, startup_fuel_burn_tons, total_fuel_burn) ) insert_temp_sql = """ INSERT INTO temp_results_project_fuel_burn{} (scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, fuel, operations_fuel_burn_mmbtu, startup_fuel_burn_mmbtu, total_fuel_burn_mmbtu) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_fuel_burn (scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, fuel, operations_fuel_burn_mmbtu, startup_fuel_burn_mmbtu, total_fuel_burn_mmbtu) SELECT scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, fuel, operations_fuel_burn_mmbtu, startup_fuel_burn_mmbtu, total_fuel_burn_mmbtu FROM temp_results_project_fuel_burn{} ORDER BY scenario_id, project, subproblem_id, stage_id, timepoint; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ if not quiet: print("market participation") # Delete prior results and create temporary import table for ordering setup_results_import(conn=db, cursor=c, table="results_system_market_participation", scenario_id=scenario_id, subproblem=subproblem, stage=stage) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "market_participation.csv"), "r") as results_file: reader = csv.reader(results_file) next(reader) # skip header for row in reader: lz = row[0] market = row[1] timepoint = row[2] period = row[3] discount_factor = row[4] number_years = row[5] timepoint_weight = row[6] number_of_hours_in_timepoint = row[7] sell_power = row[8] buy_power = row[9] results.append( (scenario_id, lz, market, subproblem, stage, timepoint, period, discount_factor, number_years, timepoint_weight, number_of_hours_in_timepoint, sell_power, buy_power)) insert_temp_sql = """ INSERT INTO temp_results_system_market_participation{} (scenario_id, load_zone, market, subproblem_id, stage_id, timepoint, period, discount_factor, number_years_represented, timepoint_weight, number_of_hours_in_timepoint, sell_power, buy_power) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_system_market_participation (scenario_id, load_zone, market, subproblem_id, stage_id, timepoint, period, discount_factor, number_years_represented, timepoint_weight, number_of_hours_in_timepoint, sell_power, buy_power) SELECT scenario_id, load_zone, market, subproblem_id, stage_id, timepoint, period, discount_factor, number_years_represented, timepoint_weight, number_of_hours_in_timepoint, sell_power, buy_power FROM temp_results_system_market_participation{} ORDER BY scenario_id, load_zone, market, subproblem_id, stage_id, timepoint; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_project_frequency_response", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open( os.path.join(results_directory, "reserves_provision_frequency_response.csv"), "r", ) as reserve_provision_file: reader = csv.reader(reserve_provision_file) next(reader) # skip header for row in reader: project = row[0] period = row[1] horizon = row[2] timepoint = row[3] timepoint_weight = row[4] number_of_hours_in_timepoint = row[5] ba = row[6] load_zone = row[7] technology = row[8] reserve_provision = row[9] partial = row[10] results.append(( scenario_id, project, period, subproblem, stage, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, ba, load_zone, technology, reserve_provision, partial, )) insert_temp_sql = """ INSERT INTO temp_results_project_frequency_response{} (scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, frequency_response_ba, load_zone, technology, reserve_provision_mw, partial) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);""".format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_frequency_response (scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, frequency_response_ba, load_zone, technology, reserve_provision_mw, partial) SELECT scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, frequency_response_ba, load_zone, technology, reserve_provision_mw, partial FROM temp_results_project_frequency_response{} ORDER BY scenario_id, project, subproblem_id, stage_id, timepoint; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database( scenario_id, subproblem, stage, c, db, results_directory, quiet ): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # Capacity cost results if not quiet: print("project capacity costs") setup_results_import(conn=db, cursor=c, table="results_project_costs_capacity", scenario_id=scenario_id, subproblem=subproblem, stage=stage) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "costs_capacity_all_projects.csv"), "r") as capacity_costs_file: reader = csv.reader(capacity_costs_file) next(reader) # skip header for row in reader: project = row[0] period = row[1] hours_in_full_period = row[2] hours_in_subproblem_period = row[3] technology = row[4] load_zone = row[5] capacity_cost = row[6] results.append( (scenario_id, project, period, subproblem, stage, hours_in_full_period, hours_in_subproblem_period, technology, load_zone, capacity_cost) ) insert_temp_sql = """ INSERT INTO temp_results_project_costs_capacity{} (scenario_id, project, period, subproblem_id, stage_id, hours_in_full_period, hours_in_subproblem_period, technology, load_zone, capacity_cost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_costs_capacity (scenario_id, project, period, subproblem_id, stage_id, hours_in_full_period, hours_in_subproblem_period, technology, load_zone, capacity_cost) SELECT scenario_id, project, period, subproblem_id, stage_id, hours_in_full_period, hours_in_subproblem_period, technology, load_zone, capacity_cost FROM temp_results_project_costs_capacity{} ORDER BY scenario_id, project, period, subproblem_id, stage_id;""".format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_module_specific_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param subproblem: :param stage: :param c: :param db: :param results_directory: :param quiet: :return: """ # New build capacity results if not quiet: print("transmission new build") # Delete prior results and create temporary import table for ordering setup_results_import(conn=db, cursor=c, table="results_transmission_capacity_new_build", scenario_id=scenario_id, subproblem=subproblem, stage=stage) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "transmission_new_capacity.csv"), "r") as capacity_file: reader = csv.reader(capacity_file) next(reader) # skip header for row in reader: transmission_line = row[0] period = row[1] load_zone_from = row[2] load_zone_to = row[3] new_build_transmission_capacity_mw = row[4] results.append((scenario_id, transmission_line, period, subproblem, stage, load_zone_from, load_zone_to, new_build_transmission_capacity_mw)) insert_temp_sql = """ INSERT INTO temp_results_transmission_capacity_new_build{} (scenario_id, transmission_line, period, subproblem_id, stage_id, load_zone_from, load_zone_to, new_build_transmission_capacity_mw) VALUES (?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_transmission_capacity_new_build (scenario_id, transmission_line, period, subproblem_id, stage_id, load_zone_from, load_zone_to, new_build_transmission_capacity_mw) SELECT scenario_id, transmission_line, period, subproblem_id, stage_id, load_zone_from, load_zone_to, new_build_transmission_capacity_mw FROM temp_results_transmission_capacity_new_build{} ORDER BY scenario_id, transmission_line, period, subproblem_id, stage_id; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # Tx capacity results if not quiet: print("transmission capacity") # Delete prior results and create temporary import table for ordering setup_results_import(conn=db, cursor=c, table="results_transmission_capacity", scenario_id=scenario_id, subproblem=subproblem, stage=stage) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "transmission_capacity.csv"), "r") as capacity_costs_file: reader = csv.reader(capacity_costs_file) next(reader) # skip header for row in reader: tx_line = row[0] period = row[1] load_zone_from = row[2] load_zone_to = row[3] min_mw = row[4] max_mw = row[5] results.append((scenario_id, tx_line, period, subproblem, stage, load_zone_from, load_zone_to, min_mw, max_mw)) insert_temp_sql = """ INSERT INTO temp_results_transmission_capacity{} (scenario_id, tx_line, period, subproblem_id, stage_id, load_zone_from, load_zone_to, min_mw, max_mw) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_transmission_capacity (scenario_id, tx_line, period, subproblem_id, stage_id, load_zone_from, load_zone_to, min_mw, max_mw) SELECT scenario_id, tx_line, period, subproblem_id, stage_id, load_zone_from, load_zone_to, min_mw, max_mw FROM temp_results_transmission_capacity{} ORDER BY scenario_id, tx_line, period, subproblem_id, stage_id; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False) # Capacity cost results if not quiet: print("transmission capacity costs") # Delete prior results and create temporary import table for ordering setup_results_import(conn=db, cursor=c, table="results_transmission_costs_capacity", scenario_id=scenario_id, subproblem=subproblem, stage=stage) # Load results into the temporary table results = [] with open( os.path.join(results_directory, "costs_transmission_capacity.csv"), "r") as capacity_costs_file: reader = csv.reader(capacity_costs_file) next(reader) # skip header for row in reader: tx_line = row[0] period = row[1] hours_in_full_period = row[2] hours_in_subproblem_period = row[3] load_zone_from = row[4] load_zone_to = row[5] capacity_cost = row[6] results.append((scenario_id, tx_line, period, subproblem, stage, hours_in_full_period, hours_in_subproblem_period, load_zone_from, load_zone_to, capacity_cost)) insert_temp_sql = """ INSERT INTO temp_results_transmission_costs_capacity{} (scenario_id, tx_line, period, subproblem_id, stage_id, hours_in_full_period, hours_in_subproblem_period, load_zone_from, load_zone_to, capacity_cost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_transmission_costs_capacity (scenario_id, tx_line, period, subproblem_id, stage_id, hours_in_full_period, hours_in_subproblem_period, load_zone_from, load_zone_to, capacity_cost) SELECT scenario_id, tx_line, period, subproblem_id, stage_id, hours_in_full_period, hours_in_subproblem_period, load_zone_from, load_zone_to, capacity_cost FROM temp_results_transmission_costs_capacity{} ORDER BY scenario_id, tx_line, period, subproblem_id, stage_id; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False) # Update the capacity cost removing the fraction attributable to the # spinup and lookahead hours update_sql = """ UPDATE results_transmission_costs_capacity SET capacity_cost_wo_spinup_or_lookahead = capacity_cost * ( SELECT fraction_of_hours_in_subproblem FROM spinup_or_lookahead_ratios WHERE spinup_or_lookahead = 0 AND results_transmission_costs_capacity.scenario_id = spinup_or_lookahead_ratios.scenario_id AND results_transmission_costs_capacity.subproblem_id = spinup_or_lookahead_ratios.subproblem_id AND results_transmission_costs_capacity.stage_id = spinup_or_lookahead_ratios.stage_id AND results_transmission_costs_capacity.period = spinup_or_lookahead_ratios.period ) ; """ spin_on_database_lock(conn=db, cursor=c, sql=update_sql, data=(), many=False)
def import_results_into_database( scenario_id, subproblem, stage, c, db, results_directory, quiet ): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ if not quiet: print("system load balance") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_system_load_balance", scenario_id=scenario_id, subproblem=subproblem, stage=stage ) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "load_balance.csv"), "r") as load_balance_file: reader = csv.reader(load_balance_file) next(reader) # skip header for row in reader: ba = row[0] period = row[1] timepoint = row[2] discount_factor = row[3] number_years = row[4] timepoint_weight = row[5] number_of_hours_in_timepoint = row[6] load = row[7] overgen = row[8] unserved_energy = row[9] results.append( (scenario_id, ba, period, subproblem, stage, timepoint, discount_factor, number_years, timepoint_weight, number_of_hours_in_timepoint, load, overgen, unserved_energy) ) insert_temp_sql = """ INSERT INTO temp_results_system_load_balance{} (scenario_id, load_zone, period, subproblem_id, stage_id, timepoint, discount_factor, number_years_represented, timepoint_weight, number_of_hours_in_timepoint, load_mw, overgeneration_mw, unserved_energy_mw) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_system_load_balance (scenario_id, load_zone, period, subproblem_id, stage_id, timepoint, discount_factor, number_years_represented, timepoint_weight, number_of_hours_in_timepoint, load_mw, overgeneration_mw, unserved_energy_mw) SELECT scenario_id, load_zone, period, subproblem_id, stage_id, timepoint, discount_factor, number_years_represented, timepoint_weight, number_of_hours_in_timepoint, load_mw, overgeneration_mw, unserved_energy_mw FROM temp_results_system_load_balance{} ORDER BY scenario_id, load_zone, subproblem_id, stage_id, timepoint; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False) # Update duals duals_results = [] with open(os.path.join(results_directory, "Meet_Load_Constraint.csv"), "r") as load_balance_duals_file: reader = csv.reader(load_balance_duals_file) next(reader) # skip header for row in reader: duals_results.append( (row[2], row[0], row[1], scenario_id, subproblem, stage) ) duals_sql = """ UPDATE results_system_load_balance SET dual = ? WHERE load_zone = ? AND timepoint = ? AND scenario_id = ? AND subproblem_id = ? AND stage_id = ?; """ spin_on_database_lock(conn=db, cursor=c, sql=duals_sql, data=duals_results) # Calculate marginal cost per MW mc_sql = """ UPDATE results_system_load_balance SET marginal_price_per_mw = dual / (discount_factor * number_years_represented * timepoint_weight * number_of_hours_in_timepoint) WHERE scenario_id = ? AND subproblem_id = ? AND stage_id = ?; """.format(scenario_id, subproblem, stage) spin_on_database_lock(conn=db, cursor=c, sql=mc_sql, data=(scenario_id, subproblem, stage), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # First import the capacity_all results; the capacity type modules will # then update the database tables rather than insert (all projects # should have been inserted here) # Delete prior results and create temporary import table for ordering # Capacity results if not quiet: print("project capacity") # Delete prior results and create temporary import table for ordering setup_results_import(conn=db, cursor=c, table="results_project_capacity", scenario_id=scenario_id, subproblem=subproblem, stage=stage) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "capacity_all.csv"), "r") as \ capacity_file: reader = csv.reader(capacity_file) next(reader) # skip header for row in reader: project = row[0] period = row[1] capacity_type = row[2] technology = row[3] load_zone = row[4] capacity_mw = row[5] energy_capacity_mwh = None if row[6] == "" else row[6] results.append((scenario_id, project, period, subproblem, stage, capacity_type, technology, load_zone, capacity_mw, energy_capacity_mwh)) insert_temp_sql = """ INSERT INTO temp_results_project_capacity{} (scenario_id, project, period, subproblem_id, stage_id, capacity_type, technology, load_zone, capacity_mw, energy_capacity_mwh) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_capacity (scenario_id, project, period, subproblem_id, stage_id, capacity_type, technology, load_zone, capacity_mw, energy_capacity_mwh) SELECT scenario_id, project, period, subproblem_id, stage_id, capacity_type, technology, load_zone, capacity_mw, energy_capacity_mwh FROM temp_results_project_capacity{} ORDER BY scenario_id, project, period, subproblem_id, stage_id;""".format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False) # Load in the required capacity type modules required_capacity_type_modules = \ get_required_capacity_types_from_database(db, scenario_id) imported_capacity_type_modules = load_gen_storage_capacity_type_modules( required_capacity_type_modules) # Import module-specific results for op_m in required_capacity_type_modules: if hasattr(imported_capacity_type_modules[op_m], "import_module_specific_results_into_database"): imported_capacity_type_modules[op_m]. \ import_module_specific_results_into_database( scenario_id, subproblem, stage, c, db, results_directory, quiet ) else: pass
def import_results_into_database( scenario_id, subproblem, stage, c, db, results_directory, quiet ): """ :param scenario_id: :param subproblem: :param stage: :param c: :param db: :param results_directory: :param quiet: :return: """ # Capacity if not quiet: print("project new DR") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_project_capacity_dr_new", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open( os.path.join(results_directory, "capacity_dr_new.csv"), "r" ) as capacity_file: reader = csv.reader(capacity_file) next(reader) # skip header for row in reader: project = row[0] period = row[1] technology = row[2] load_zone = row[3] new_build_mw = row[4] new_build_mwh = row[5] results.append( ( scenario_id, project, period, subproblem, stage, technology, load_zone, new_build_mw, new_build_mwh, ) ) insert_temp_sql = """ INSERT INTO temp_results_project_capacity_dr_new{} (scenario_id, project, period, subproblem_id, stage_id, technology, load_zone, new_build_mw, new_build_mwh) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);""".format( scenario_id ) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_capacity_dr_new (scenario_id, project, period, subproblem_id, stage_id, technology, load_zone, new_build_mw, new_build_mwh) SELECT scenario_id, project, period, subproblem_id, stage_id, technology, load_zone, new_build_mw, new_build_mwh FROM temp_results_project_capacity_dr_new{} """.format( scenario_id ) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_module_specific_results_into_database( scenario_id, subproblem, stage, c, db, results_directory, quiet ): """ :param scenario_id: :param subproblem: :param stage: :param c: :param db: :param results_directory: :param quiet: :return: """ # Energy-only and deliverable capacity by project if not quiet: print("project energy-only and deliverable capacities") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_project_prm_deliverability", scenario_id=scenario_id, subproblem=subproblem, stage=stage ) # Load results into the temporary table results = [] with open(os.path.join( results_directory, "project_prm_energy_only_and_deliverable_capacity.csv"), "r") as deliv_file: reader = csv.reader(deliv_file) next(reader) # skip header for row in reader: project = row[0] period = row[1] prm_zone = row[2] total_capacity_mw = row[3] deliverable_capacity = row[4] energy_only_capacity = row[5] results.append( (scenario_id, project, period, subproblem, stage, prm_zone, total_capacity_mw, deliverable_capacity, energy_only_capacity) ) insert_temp_sql = """ INSERT INTO temp_results_project_prm_deliverability{} (scenario_id, project, period, subproblem_id, stage_id, prm_zone, capacity_mw, deliverable_capacity_mw, energy_only_capacity_mw) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_prm_deliverability (scenario_id, project, period, subproblem_id, stage_id, prm_zone, capacity_mw, deliverable_capacity_mw, energy_only_capacity_mw) SELECT scenario_id, project, period, subproblem_id, stage_id, prm_zone, capacity_mw, deliverable_capacity_mw, energy_only_capacity_mw FROM temp_results_project_prm_deliverability{} ORDER BY scenario_id, project, period, subproblem_id, stage_id; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False) # Group capacity cost results if not quiet: print("project prm group deliverability costs") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_project_prm_deliverability_group_capacity_and_costs", scenario_id=scenario_id, subproblem=subproblem, stage=stage ) # Load results into the temporary table results = [] with open(os.path.join( results_directory, "deliverability_group_capacity_and_costs.csv"), "r") as capacity_costs_file: reader = csv.reader(capacity_costs_file) next(reader) # skip header for row in reader: group = row[0] period = row[1] deliverability_group_no_cost_deliverable_capacity_mw = row[2] deliverability_group_deliverability_cost_per_mw = row[3] total_capacity_mw = row[4] deliverable_capacity = row[5] energy_only_capacity_mw = row[6] deliverable_capacity_cost = row[7] results.append( (scenario_id, group, period, subproblem, stage, deliverability_group_no_cost_deliverable_capacity_mw, deliverability_group_deliverability_cost_per_mw, total_capacity_mw, deliverable_capacity, energy_only_capacity_mw, deliverable_capacity_cost) ) insert_temp_sql = """ INSERT INTO temp_results_project_prm_deliverability_group_capacity_and_costs{} (scenario_id, deliverability_group, period, subproblem_id, stage_id, deliverability_group_no_cost_deliverable_capacity_mw, deliverability_group_deliverability_cost_per_mw, total_capacity_mw, deliverable_capacity_mw, energy_only_capacity_mw, deliverable_capacity_cost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_prm_deliverability_group_capacity_and_costs (scenario_id, deliverability_group, period, subproblem_id, stage_id, deliverability_group_no_cost_deliverable_capacity_mw, deliverability_group_deliverability_cost_per_mw, total_capacity_mw, deliverable_capacity_mw, energy_only_capacity_mw, deliverable_capacity_cost) SELECT scenario_id, deliverability_group, period, subproblem_id, stage_id, deliverability_group_no_cost_deliverable_capacity_mw, deliverability_group_deliverability_cost_per_mw, total_capacity_mw, deliverable_capacity_mw, energy_only_capacity_mw, deliverable_capacity_cost FROM temp_results_project_prm_deliverability_group_capacity_and_costs{} ORDER BY scenario_id, deliverability_group, period, subproblem_id, stage_id; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def generic_import_results_to_database( scenario_id, subproblem, stage, c, db, results_directory, reserve_type ): """ :param scenario_id: :param subproblem: :param stage: :param c: :param db: :param results_directory: :param reserve_type: :return: """ # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_system_{}_balance" "".format(reserve_type), scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open( os.path.join(results_directory, reserve_type + "_violation.csv"), "r" ) as violation_file: reader = csv.reader(violation_file) next(reader) # skip header for row in reader: ba = row[0] period = row[1] timepoint = row[2] discount_factor = row[3] number_years_represented = row[4] timepoint_weight = row[5] number_of_hours_in_timepoint = row[6] violation = row[7] results.append( ( scenario_id, ba, period, subproblem, stage, timepoint, discount_factor, number_years_represented, timepoint_weight, number_of_hours_in_timepoint, violation, ) ) insert_temp_sql = """ INSERT INTO temp_results_system_{}_balance{} (scenario_id, {}_ba, period, subproblem_id, stage_id, timepoint, discount_factor, number_years_represented, timepoint_weight, number_of_hours_in_timepoint, violation_mw) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format( reserve_type, scenario_id, reserve_type ) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_system_{}_balance (scenario_id, {}_ba, period, subproblem_id, stage_id, timepoint, discount_factor, number_years_represented, timepoint_weight, number_of_hours_in_timepoint, violation_mw) SELECT scenario_id, {}_ba, period, subproblem_id, stage_id, timepoint, discount_factor, number_years_represented, timepoint_weight, number_of_hours_in_timepoint, violation_mw FROM temp_results_system_{}_balance{} ORDER BY scenario_id, {}_ba, subproblem_id, stage_id, timepoint; """.format( reserve_type, reserve_type, reserve_type, reserve_type, scenario_id, reserve_type, ) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False) # Update duals dual_files = { "lf_reserves_up": "Meet_LF_Reserves_Up_Constraint.csv", "lf_reserves_down": "Meet_LF_Reserves_Down_Constraint.csv", "regulation_up": "Meet_Regulation_Up_Constraint.csv", "regulation_down": "Meet_Regulation_Down_Constraint.csv", "frequency_response": "Meet_Frequency_Response_Constraint.csv", "frequency_response_partial": "Meet_Frequency_Response_Partial_Constraint.csv", "spinning_reserves": "Meet_Spinning_Reserves_Constraint.csv", } duals_results = [] with open( os.path.join(results_directory, dual_files[reserve_type]), "r" ) as reserve_balance_duals_file: reader = csv.reader(reserve_balance_duals_file) next(reader) # skip header for row in reader: duals_results.append( (row[2], row[0], row[1], scenario_id, subproblem, stage) ) duals_sql = """ UPDATE results_system_{}_balance SET dual = ? WHERE {}_ba = ? AND timepoint = ? AND scenario_id = ? AND subproblem_id = ? AND stage_id = ?; """.format( reserve_type, reserve_type ) spin_on_database_lock(conn=db, cursor=c, sql=duals_sql, data=duals_results) # Calculate marginal cost per MW mc_sql = """ UPDATE results_system_{}_balance SET marginal_price_per_mw = dual / (discount_factor * number_years_represented * timepoint_weight * number_of_hours_in_timepoint) WHERE scenario_id = ? AND subproblem_id = ? AND stage_id = ?; """.format( reserve_type ) spin_on_database_lock( conn=db, cursor=c, sql=mc_sql, data=(scenario_id, subproblem, stage), many=False )
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param subproblem: :param stage: :param c: :param db: :param results_directory: :param quiet: :return: """ # Carbon emission imports by transmission line and timepoint if not quiet: print("transmission carbon emissions") # Delete prior results and create temporary import table for ordering setup_results_import(conn=db, cursor=c, table="results_transmission_carbon_emissions", scenario_id=scenario_id, subproblem=subproblem, stage=stage) # Load results into the temporary table results = [] with open( os.path.join(results_directory, "carbon_emission_imports_by_tx_line.csv"), "r") as emissions_file: reader = csv.reader(emissions_file) next(reader) # skip header for row in reader: tx_line = row[0] period = row[1] timepoint = row[2] timepoint_weight = row[3] number_of_hours_in_timepoint = row[4] carbon_emission_imports_tons = row[5] carbon_emission_imports_tons_degen = row[6] results.append( (scenario_id, tx_line, period, subproblem, stage, timepoint, timepoint_weight, number_of_hours_in_timepoint, carbon_emission_imports_tons, carbon_emission_imports_tons_degen)) insert_temp_sql = """ INSERT INTO temp_results_transmission_carbon_emissions{} (scenario_id, tx_line, period, subproblem_id, stage_id, timepoint, timepoint_weight, number_of_hours_in_timepoint, carbon_emission_imports_tons, carbon_emission_imports_tons_degen) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_transmission_carbon_emissions (scenario_id, tx_line, period, subproblem_id, stage_id, timepoint, timepoint_weight, number_of_hours_in_timepoint, carbon_emission_imports_tons, carbon_emission_imports_tons_degen) SELECT scenario_id, tx_line, period, subproblem_id, stage_id, timepoint, timepoint_weight, number_of_hours_in_timepoint, carbon_emission_imports_tons, carbon_emission_imports_tons_degen FROM temp_results_transmission_carbon_emissions{} ORDER BY scenario_id, tx_line, subproblem_id, stage_id, timepoint; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database( scenario_id, subproblem, stage, c, db, results_directory, quiet ): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_system_horizon_energy_target", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open( os.path.join(results_directory, "horizon_energy_target.csv"), "r" ) as energy_target_file: reader = csv.reader(energy_target_file) next(reader) # skip header for row in reader: energy_target_zone = row[0] balancing_type = row[1] horizon = row[2] energy_target = row[3] energy_target_provision = row[4] curtailment = row[5] total = row[6] fraction_met = row[7] fraction_curtailed = row[8] shortage = row[9] results.append( ( scenario_id, energy_target_zone, balancing_type, horizon, subproblem, stage, energy_target, energy_target_provision, curtailment, total, fraction_met, fraction_curtailed, shortage, ) ) insert_temp_sql = """ INSERT INTO temp_results_system_horizon_energy_target{} (scenario_id, energy_target_zone, balancing_type_horizon, horizon, subproblem_id, stage_id, energy_target_mwh, delivered_energy_target_energy_mwh, curtailed_energy_target_energy_mwh, total_energy_target_energy_mwh, fraction_of_energy_target_met, fraction_of_energy_target_energy_curtailed, energy_target_shortage_mwh) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format( scenario_id ) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_system_horizon_energy_target (scenario_id, energy_target_zone, balancing_type_horizon, horizon, subproblem_id, stage_id, energy_target_mwh, delivered_energy_target_energy_mwh, curtailed_energy_target_energy_mwh, total_energy_target_energy_mwh, fraction_of_energy_target_met, fraction_of_energy_target_energy_curtailed, energy_target_shortage_mwh) SELECT scenario_id, energy_target_zone, balancing_type_horizon, horizon, subproblem_id, stage_id, energy_target_mwh, delivered_energy_target_energy_mwh, curtailed_energy_target_energy_mwh, total_energy_target_energy_mwh, fraction_of_energy_target_met, fraction_of_energy_target_energy_curtailed, energy_target_shortage_mwh FROM temp_results_system_horizon_energy_target{} ORDER BY scenario_id, energy_target_zone, balancing_type_horizon, horizon, subproblem_id, stage_id; """.format( scenario_id ) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False) # Update duals duals_results = [] with open( os.path.join(results_directory, "Horizon_Energy_Target_Constraint.csv"), "r" ) as energy_target_duals_file: reader = csv.reader(energy_target_duals_file) next(reader) # skip header for row in reader: duals_results.append( (row[3], row[0], row[1], row[2], scenario_id, subproblem, stage) ) duals_sql = """ UPDATE results_system_horizon_energy_target SET dual = ? WHERE energy_target_zone = ? AND balancing_type_horizon = ? AND horizon = ? AND scenario_id = ? AND subproblem_id = ? AND stage_id = ?; """ spin_on_database_lock(conn=db, cursor=c, sql=duals_sql, data=duals_results)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ if not quiet: print("imports and exports") # Delete prior results and create temporary import table for ordering setup_results_import(conn=db, cursor=c, table="results_transmission_imports_exports", scenario_id=scenario_id, subproblem=subproblem, stage=stage) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "imports_exports.csv"), "r") as tx_op_file: reader = csv.reader(tx_op_file) next(reader) # skip header for row in reader: load_zone = row[0] timepoint = row[1] period = row[2] timepoint_weight = row[3] number_of_hours_in_timepoint = row[4] imports_mw = row[5] exports_mw = row[6] net_imports_mw = row[7] results.append( (scenario_id, load_zone, period, subproblem, stage, timepoint, timepoint_weight, number_of_hours_in_timepoint, imports_mw, exports_mw, net_imports_mw)) insert_temp_sql = """ INSERT INTO temp_results_transmission_imports_exports{} (scenario_id, load_zone, period, subproblem_id, stage_id, timepoint, timepoint_weight, number_of_hours_in_timepoint, imports_mw, exports_mw, net_imports_mw) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_transmission_imports_exports (scenario_id, load_zone, period, subproblem_id, stage_id, timepoint, timepoint_weight, number_of_hours_in_timepoint, imports_mw, exports_mw, net_imports_mw) SELECT scenario_id, load_zone, period, subproblem_id, stage_id, timepoint, timepoint_weight, number_of_hours_in_timepoint, imports_mw, exports_mw, net_imports_mw FROM temp_results_transmission_imports_exports{} ORDER BY scenario_id, load_zone, subproblem_id, stage_id, timepoint; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ if not quiet: print("project costs operations") # costs_operations.csv # Delete prior results and create temporary import table for ordering setup_results_import(conn=db, cursor=c, table="results_project_costs_operations", scenario_id=scenario_id, subproblem=subproblem, stage=stage) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "costs_operations.csv"), "r") as dispatch_file: reader = csv.reader(dispatch_file) next(reader) # skip header for row in reader: project = row[0] period = row[1] horizon = row[2] timepoint = row[3] timepoint_weight = row[4] number_of_hours_in_timepoint = row[5] load_zone = row[6] technology = row[7] variable_om_cost = row[8] fuel_cost = row[9] startup_cost = row[10] shutdown_cost = row[11] results.append( (scenario_id, project, period, subproblem, stage, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, variable_om_cost, fuel_cost, startup_cost, shutdown_cost)) insert_temp_sql = """ INSERT INTO temp_results_project_costs_operations{} (scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, variable_om_cost, fuel_cost, startup_cost, shutdown_cost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);""".format( scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_costs_operations (scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, variable_om_cost, fuel_cost, startup_cost, shutdown_cost) SELECT scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone, technology, variable_om_cost, fuel_cost, startup_cost, shutdown_cost FROM temp_results_project_costs_operations{} ORDER BY scenario_id, project, subproblem_id, stage_id, timepoint; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # Carbon emissions by in-zone projects if not quiet: print("system carbon emissions (project)") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_system_carbon_emissions", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "carbon_cap_total_project.csv"), "r") as emissions_file: reader = csv.reader(emissions_file) next(reader) # skip header for row in reader: carbon_cap_zone = row[0] period = row[1] carbon_cap = row[4] project_carbon_emissions = row[5] results.append(( scenario_id, carbon_cap_zone, period, subproblem, stage, carbon_cap, project_carbon_emissions, )) insert_temp_sql = """ INSERT INTO temp_results_system_carbon_emissions{} (scenario_id, carbon_cap_zone, period, subproblem_id, stage_id, carbon_cap, in_zone_project_emissions) VALUES (?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_system_carbon_emissions (scenario_id, carbon_cap_zone, period, subproblem_id, stage_id, carbon_cap, in_zone_project_emissions) SELECT scenario_id, carbon_cap_zone, period, subproblem_id, stage_id, carbon_cap, in_zone_project_emissions FROM temp_results_system_carbon_emissions{} ORDER BY scenario_id, carbon_cap_zone, period, subproblem_id, stage_id; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # REC provision by project and timepoint if not quiet: print("project recs") # Delete prior results and create temporary import table for ordering setup_results_import(conn=db, cursor=c, table="results_project_rps", scenario_id=scenario_id, subproblem=subproblem, stage=stage) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "rps_by_project.csv"), "r") as rps_file: reader = csv.reader(rps_file) next(reader) # skip header for row in reader: project = row[0] load_zone = row[1] rps_zone = row[2] timepoint = row[3] period = row[4] horizon = row[5] timepoint_weight = row[6] hours_in_tmp = row[7] technology = row[8] scheduled_energy = row[9] scheduled_curtailment = row[10] subhourly_energy = row[11] subhourly_curtailment = row[12] results.append( (scenario_id, project, period, subproblem, stage, horizon, timepoint, timepoint_weight, hours_in_tmp, load_zone, rps_zone, technology, scheduled_energy, scheduled_curtailment, subhourly_energy, subhourly_curtailment)) insert_temp_sql = """ INSERT INTO temp_results_project_rps{} (scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone, rps_zone, technology, scheduled_rps_energy_mw, scheduled_curtailment_mw, subhourly_rps_energy_delivered_mw, subhourly_curtailment_mw) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_rps (scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone, rps_zone, technology, scheduled_rps_energy_mw, scheduled_curtailment_mw, subhourly_rps_energy_delivered_mw, subhourly_curtailment_mw) SELECT scenario_id, project, period, subproblem_id, stage_id, horizon, timepoint, timepoint_weight, number_of_hours_in_timepoint, load_zone, rps_zone, technology, scheduled_rps_energy_mw, scheduled_curtailment_mw, subhourly_rps_energy_delivered_mw, subhourly_curtailment_mw FROM temp_results_project_rps{} ORDER BY scenario_id, project, subproblem_id, stage_id, timepoint; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): # Import only if a results-file was exported results_file = os.path.join(results_directory, "capacity_groups.csv") if os.path.exists(results_file): if not quiet: print("group capacity") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_project_group_capacity", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open(results_file, "r") as f: reader = csv.reader(f) next(reader) # skip header for row in reader: results.append((scenario_id, subproblem, stage) + tuple(row)) insert_temp_sql = """ INSERT INTO temp_results_project_group_capacity{} (scenario_id, subproblem_id, stage_id, capacity_group, period, group_new_capacity, group_total_capacity, capacity_group_new_capacity_min, capacity_group_new_capacity_max, capacity_group_total_capacity_min, capacity_group_total_capacity_max) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_project_group_capacity (scenario_id, subproblem_id, stage_id, capacity_group, period, group_new_capacity, group_total_capacity, capacity_group_new_capacity_min, capacity_group_new_capacity_max, capacity_group_total_capacity_min, capacity_group_total_capacity_max) SELECT scenario_id, subproblem_id, stage_id, capacity_group, period, group_new_capacity, group_total_capacity, capacity_group_new_capacity_min, capacity_group_new_capacity_max, capacity_group_total_capacity_min, capacity_group_total_capacity_max FROM temp_results_project_group_capacity{} ORDER BY scenario_id, subproblem_id, stage_id, capacity_group, period; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_system_period_energy_target", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open(os.path.join(results_directory, "period_energy_target.csv"), "r") as energy_target_file: reader = csv.reader(energy_target_file) next(reader) # skip header for row in reader: energy_target_zone = row[0] period = row[1] discount_factor = row[2] number_years = row[3] energy_target = row[4] energy_target_provision = row[5] curtailment = row[6] total = row[7] fraction_met = row[8] fraction_curtailed = row[9] shortage = row[10] results.append(( scenario_id, energy_target_zone, period, subproblem, stage, discount_factor, number_years, energy_target, energy_target_provision, curtailment, total, fraction_met, fraction_curtailed, shortage, )) insert_temp_sql = """ INSERT INTO temp_results_system_period_energy_target{} (scenario_id, energy_target_zone, period, subproblem_id, stage_id, discount_factor, number_years_represented, energy_target_mwh, delivered_energy_target_energy_mwh, curtailed_energy_target_energy_mwh, total_energy_target_energy_mwh, fraction_of_energy_target_met, fraction_of_energy_target_energy_curtailed, energy_target_shortage_mwh) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_system_period_energy_target (scenario_id, energy_target_zone, period, subproblem_id, stage_id, discount_factor, number_years_represented, energy_target_mwh, delivered_energy_target_energy_mwh, curtailed_energy_target_energy_mwh, total_energy_target_energy_mwh, fraction_of_energy_target_met, fraction_of_energy_target_energy_curtailed, energy_target_shortage_mwh) SELECT scenario_id, energy_target_zone, period, subproblem_id, stage_id, discount_factor, number_years_represented, energy_target_mwh, delivered_energy_target_energy_mwh, curtailed_energy_target_energy_mwh, total_energy_target_energy_mwh, fraction_of_energy_target_met, fraction_of_energy_target_energy_curtailed, energy_target_shortage_mwh FROM temp_results_system_period_energy_target{} ORDER BY scenario_id, energy_target_zone, period, subproblem_id, stage_id; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False) # Update duals duals_results = [] with open( os.path.join(results_directory, "Period_Energy_Target_Constraint.csv"), "r") as energy_target_duals_file: reader = csv.reader(energy_target_duals_file) next(reader) # skip header for row in reader: duals_results.append( (row[2], row[0], row[1], scenario_id, subproblem, stage)) duals_sql = """ UPDATE results_system_period_energy_target SET dual = ? WHERE energy_target_zone = ? AND period = ? AND scenario_id = ? AND subproblem_id = ? AND stage_id = ?; """ spin_on_database_lock(conn=db, cursor=c, sql=duals_sql, data=duals_results) # Calculate marginal energy-target cost per MWh mc_sql = """ UPDATE results_system_period_energy_target SET energy_target_marginal_cost_per_mwh = dual / (discount_factor * number_years_represented) WHERE scenario_id = ? AND subproblem_id = ? and stage_id = ?; """ spin_on_database_lock(conn=db, cursor=c, sql=mc_sql, data=(scenario_id, subproblem, stage), many=False)
def import_results_into_database(scenario_id, subproblem, stage, c, db, results_directory, quiet): """ :param scenario_id: :param c: :param db: :param results_directory: :param quiet: :return: """ if not quiet: print("sim flow limits") # Delete prior results and create temporary import table for ordering setup_results_import( conn=db, cursor=c, table="results_transmission_simultaneous_flows", scenario_id=scenario_id, subproblem=subproblem, stage=stage, ) # Load results into the temporary table results = [] with open( os.path.join(results_directory, "transmission_simultaneous_flow_limits.csv"), "r", ) as f: reader = csv.reader(f) next(reader) # skip header for row in reader: limit = row[0] timepoint = row[1] period = row[2] timepoimt_weight = row[3] flow = row[4] results.append(( scenario_id, limit, subproblem, stage, timepoint, timepoimt_weight, period, flow, )) insert_temp_sql = """ INSERT INTO temp_results_transmission_simultaneous_flows{} (scenario_id, transmission_simultaneous_flow_limit, subproblem_id, stage_id, timepoint, timepoint_weight, period, flow_mw) VALUES (?, ?, ?, ?, ?, ?, ?, ?); """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_temp_sql, data=results) # Insert sorted results into permanent results table insert_sql = """ INSERT INTO results_transmission_simultaneous_flows (scenario_id, transmission_simultaneous_flow_limit, subproblem_id, stage_id, timepoint, timepoint_weight, period, flow_mw) SELECT scenario_id, transmission_simultaneous_flow_limit, subproblem_id, stage_id, timepoint, timepoint_weight, period, flow_mw FROM temp_results_transmission_simultaneous_flows{} ORDER BY scenario_id, transmission_simultaneous_flow_limit, subproblem_id, stage_id, timepoint; """.format(scenario_id) spin_on_database_lock(conn=db, cursor=c, sql=insert_sql, data=(), many=False) # Update duals duals_results = [] with open(os.path.join(results_directory, "Sim_Flow_Constraint.csv"), "r") as duals_file: reader = csv.reader(duals_file) next(reader) # skip header for row in reader: duals_results.append( (row[2], row[0], row[1], scenario_id, subproblem, stage)) duals_sql = """ UPDATE results_transmission_simultaneous_flows SET dual = ? WHERE transmission_simultaneous_flow_limit = ? AND timepoint = ? AND scenario_id = ? AND subproblem_id = ? AND stage_id = ?; """ spin_on_database_lock(conn=db, cursor=c, sql=duals_sql, data=duals_results)