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)
