:param cursor: PYODBC cursor
:return:
"""
for (_, object_name,
_) in [i for i in db_competes_data['objects'] if i[0] == 'Nset']:
sql_query = 'INSERT INTO [Nset] ([n2]) VALUES (?);'
cursor.execute(sql_query, (object_name, ))
print('===== Starting COMPETES SpineDB to MS Access script =====')
config_url = sys.argv[3]
print('Config DB path: ' + config_url)
print('Reading current tick...')
db_emlab = SpineDB(sys.argv[2])
db_config = SpineDB(config_url)
try:
db_config_parameters = db_config.query_object_parameter_values_by_object_class(
'Coupling Parameters')
start_simulation_year = next(
int(i['parameter_value']) for i in db_config_parameters
if i['object_name'] == 'Start Year')
end_simulation_year = next(
int(i['parameter_value']) for i in db_config_parameters
if i['object_name'] == 'End Year')
look_ahead = next(
int(i['parameter_value']) for i in db_config_parameters
if i['object_name'] == 'Look Ahead')
current_emlab_tick, current_competes_tick, current_competes_tick_rounded = get_current_ticks(
db_emlab, start_simulation_year)
def execute_export_to_competes():
"""
This function runs all the scripts in this file.
"""
print('Establishing Database Connections...')
db_emlab = SpineDB(sys.argv[1])
db_competes = SpineDB(sys.argv[2])
db_config = SpineDB(sys.argv[3])
print('Done establishing connections')
try:
print('Querying databases...')
db_emlab_marketclearingpoints = db_emlab.query_object_parameter_values_by_object_class(
'MarketClearingPoints')
db_emlab_powerplantdispatchplans = db_emlab.query_object_parameter_values_by_object_class(
'PowerPlantDispatchPlans')
db_emlab_powerplants = db_emlab.query_object_parameter_values_by_object_class(
'PowerPlants')
db_config_parameters = db_config.query_object_parameter_values_by_object_class(
'Coupling Parameters')
print('Done querying Databases')
time_step = next(
int(i['parameter_value']) for i in db_config_parameters
if i['object_name'] == 'Time Step')
start_simulation_year = next(
int(i['parameter_value']) for i in db_config_parameters
if i['object_name'] == 'Start Year')
look_ahead = next(
int(i['parameter_value']) for i in db_config_parameters
if i['object_name'] == 'Look Ahead')
current_emlab_tick, current_competes_tick, current_competes_tick_rounded = get_current_ticks(
db_emlab, start_simulation_year)
print('Current EMLAB Tick: ' + str(current_emlab_tick))
print('Current COMPETES Tick: ' + str(current_competes_tick))
co2_object_class_name = 'EU_ETS_CO2price'
months = [
'Jan', 'Feb', 'March', 'April', 'May', 'June', 'July', 'Aug',
'Sep', 'Oct', 'Nov', 'Dec'
]
initialize_co2_spine_structure(db_competes, current_competes_tick,
co2_object_class_name, months,
look_ahead)
export_co2_market_clearing_price(
db_competes, db_emlab_marketclearingpoints, current_emlab_tick,
co2_object_class_name, current_competes_tick, months, look_ahead)
export_capacity_market_revenues(db_competes, current_emlab_tick,
db_emlab_powerplantdispatchplans,
db_emlab_marketclearingpoints,
current_competes_tick, time_step,
db_emlab_powerplants, look_ahead)
print('Committing...')
db_competes.commit(
'Committing EMLAB to COMPETES script. EMLab tick: ' +
str(current_emlab_tick) + ', COMPETES tick: ' +
str(current_competes_tick))
print('Done!')
except Exception as e:
print('Exception occurred: ' + str(e))
raise
finally:
print('Closing database connections...')
db_emlab.close_connection()
db_competes.close_connection()
db_config.close_connection()
def generate_plots():
# Select what years you want to generate plots for
path_to_competes_results = '../../COMPETES/Results/Run 20210730 10M CO2 Cap, discountr 2.5, hedging 1, no exports, no VOLL'
filename_to_load_dispatch = 'Output_Dynamic_Gen&Trans_?_Dispatch.xlsx'
filename_to_load_investment = 'Output_Dynamic_Gen&Trans_?_Investments.xlsx'
# Create plots directory if it does not exist yet
path_to_plots = path_to_competes_results + '/plots'
if not os.path.exists(path_to_plots):
os.makedirs(path_to_plots)
years_to_generate = [2020, 2021, 2022, 2023, 2024, 2025]
start_simulation_year = 2020
years_emlab = [i - start_simulation_year for i in years_to_generate]
look_ahead = 7
static_fuel_technology_legend = sorted(['GAS, CCGT', 'COAL, PC (D)', 'LIGNITE, PC (D)', 'RESE, others (D)', 'GAS, CCS CCGT', 'NUCLEAR, -', 'BIOMASS, Cofiring (D)', 'BIOMASS, Standalone (D)', 'Derived GAS, CHP (D)', 'Derived GAS, IC (D)', 'GAS, CCGT (D)', 'GAS, CHP (D)', 'OIL, - (D)', 'GAS, GT (D)', 'GAS, CCS CCGT (D)', 'NUCLEAR, - (D)'])
co2_emission_sums = dict()
vre_nl_installed_capacity = dict()
nl_investment_sums = {i: [0] * len(range(years_to_generate[0], years_to_generate[-1] + look_ahead + 1)) for i in static_fuel_technology_legend}
investment_sums = {i: [0] * len(range(years_to_generate[0], years_to_generate[-1] + look_ahead + 1)) for i in static_fuel_technology_legend}
annual_balance = dict()
annual_installed_capacity = dict()
residual_load_curves = pd.DataFrame()
load_duration_curves = pd.DataFrame()
price_duration_curves = pd.DataFrame()
# EMLab Plots
print('Establishing and querying SpineDB...')
sqlite_prepend = "sqlite:///"
emlab_spinedb = SpineDB(sqlite_prepend + path_to_competes_results + '/db.sqlite')
competes_spinedb = SpineDB(sqlite_prepend + path_to_competes_results + '/db competes.sqlite')
try:
emlab_spine_powerplants = emlab_spinedb.query_object_parameter_values_by_object_class('PowerPlants')
competes_spine_powerplants = competes_spinedb.query_object_parameter_values_by_object_classes(
['Installed Capacity Abroad', 'Installed Capacity-RES Abroad'])
spine_powerplants_tech_dict = dict(list({str(i['object_name']): str(i['parameter_value']) for i in
emlab_spine_powerplants if
i['parameter_name'] == 'TECHTYPENL'}.items()) +
list({str(i['object_name']): str(i['parameter_value'].decode()).replace("\"",
'')
for i in
competes_spine_powerplants
if i['parameter_name'] == 'TECHTYPEU'}.items()))
spine_powerplants_fuel_dict = dict(list({str(i['object_name']): str(i['parameter_value']) for i in
emlab_spine_powerplants if i['parameter_name'] == 'FUELNL'}.items()) +
list({str(i['object_name']): str(i['parameter_value'].decode()).replace("\"",
'')
for i in
competes_spine_powerplants
if i['parameter_name'] == 'FUELEU'}.items()))
emlab_spine_technologies = emlab_spinedb.query_object_parameter_values_by_object_class(
'PowerGeneratingTechnologies')
db_mcps = emlab_spinedb.query_object_parameter_values_by_object_class('MarketClearingPoints')
db_emlab_powerplantdispatchplans = emlab_spinedb.query_object_parameter_values_by_object_class(
'PowerPlantDispatchPlans')
capacity_market_participating_technologies_peryear = get_participating_technologies_in_capacity_market(
db_emlab_powerplantdispatchplans, years_to_generate, years_emlab, emlab_spine_powerplants)
finally:
competes_spinedb.close_connection()
emlab_spinedb.close_connection()
print('Done')
# Generate plots
print('Start generating plots per year')
for year in years_to_generate:
print('Preparing and plotting for year ' + str(year))
path_and_filename_dispatch = path_to_competes_results + '/' + filename_to_load_dispatch.replace('?', str(year))
path_and_filename_investments = path_to_competes_results + '/' + filename_to_load_investment.replace('?',
str(year + look_ahead))
# Preparing Data
investment_sums, nl_investment_sums = prepare_investment_and_decom_data(path_and_filename_investments,
investment_sums,
years_to_generate, year,
spine_powerplants_tech_dict,
spine_powerplants_fuel_dict,
emlab_spine_technologies,
look_ahead, nl_investment_sums)
vre_nl_installed_capacity = prepare_vre_investment_data(path_and_filename_investments,
vre_nl_installed_capacity,
years_to_generate, year)
co2_emission_sums = prepare_co2_emission_data(path_and_filename_dispatch, co2_emission_sums, years_to_generate,
year)
annual_installed_capacity = prepare_annual_installed_capacity(path_and_filename_dispatch,
spine_powerplants_tech_dict,
annual_installed_capacity, year,
years_to_generate)
# Plots
hourly_nl_balance_df, hourly_nl_balance_demand = plot_hourly_nl_balance(path_and_filename_dispatch,
path_to_plots, year)
# Another prepare data
annual_balance = prepare_annual_nl_balance(hourly_nl_balance_df, annual_balance, years_to_generate, year)
# More plots
load_duration_curves = plot_and_prepare_load_duration_curve(hourly_nl_balance_demand, year, path_to_plots,
load_duration_curves)
residual_load_curves = plot_and_prepare_residual_load_duration_curve(hourly_nl_balance_demand,
hourly_nl_balance_df, year,
path_to_plots, residual_load_curves)
hourly_nodal_prices_df = plot_hourly_nodal_prices(path_and_filename_dispatch, year, path_to_plots)
price_duration_curves = plot_and_prepare_hourly_nodal_price_duration_curve(hourly_nodal_prices_df, year,
path_to_plots,
price_duration_curves)
# plot_nl_unit_generation(path_and_filename_dispatch, year, path_to_plots)
plt.close('all')
print('Plotting prepared data')
plot_co2_emissions(co2_emission_sums, years_to_generate, path_to_plots)
plot_nl_investments(nl_investment_sums, years_to_generate, path_to_plots, look_ahead)
plot_investments(investment_sums, years_to_generate, path_to_plots, look_ahead)
plot_vre_nl_installed_capacity(vre_nl_installed_capacity, years_to_generate, path_to_plots)
plot_annual_balances(annual_balance, years_to_generate, path_to_plots)
plot_annual_installed_capacity(annual_installed_capacity, years_to_generate, path_to_plots)
plot_mcps_with_filter(db_mcps, 'CO2Auction', years_to_generate, path_to_plots, 'NL CO2 Market Clearing Prices',
'NL CO2 Market Clearing Prices.png', 'Price (Euro / ton CO2)', None)
plot_mcps_with_filter(db_mcps, 'DutchCapacityMarket', years_to_generate, path_to_plots, 'NL Capacity Market Prices',
'NL Capacity Market Prices.png', 'Price (Euro / MW)', [0, 75e3])
plot_combined_curves(residual_load_curves, 'NL Residual Load Duration Curves', 'Residual Load (MWh)', path_to_plots,
2000000)
plot_combined_curves(load_duration_curves, 'NL Load Duration Curves', 'Load (MWh)', path_to_plots, 2000000)
plot_combined_curves(price_duration_curves, 'NL Hourly Market Price Duration Curves', 'Price (Euro / MWh)',
path_to_plots, 250)
plot_capacity_market_technologies(capacity_market_participating_technologies_peryear, path_to_plots,
years_to_generate, 'NL Capacity Market technologies',
'NL Capacity Market Technologies.png', 'Awarded capacity (MW)')
plot_capacity_market_revenues(capacity_market_participating_technologies_peryear, db_mcps, path_to_plots,
years_to_generate, years_emlab, 'NL Capacity Market Revenues',
'NL Capacity Market revenues.png', 'CM Revenues (Million Euro)')
# print('Showing plots...')
# plt.show()
plt.close('all')
import sys
import csv
from spinedb import SpineDB
input_db_url = sys.argv[1]
output_db_url = sys.argv[2]
translation_file = sys.argv[3]
input_db = SpineDB(input_db_url)
output_db = SpineDB(output_db_url, create=True)
# Export data from input db
input_data = input_db.export_data()
renames = dict()
# Open and read renames file
with open(translation_file) as csvfile:
for row in csv.reader(csvfile):
renames[row[0].strip()] = row[1].strip()
def translate_spine_data(input_data: dict, renames: dict) -> dict:
"""Translate Spine data renaming classes and parameters
"""
def rename(old_label):
return renames.get(old_label, old_label)
# Create new data by renames
translated = {
Arg1: path to EMLAB SpineDB
Arg2: AIMMS Service Name given to Procedure that runs COMPETES
@author: hernandezsernar
@author: Jim Hommes
"""
import requests
import time
import sys
from spinedb import SpineDB
from helper_functions import get_current_ticks
print('===== Starting COMPETES Execution =====')
print('Read current year from SpineDB...')
db_emlab = SpineDB(sys.argv[1])
db_config = SpineDB(sys.argv[2])
try:
db_config_parameters = db_config.query_object_parameter_values_by_object_class('Coupling Parameters')
start_simulation_year = next(int(i['parameter_value']) for i in
db_config_parameters if i['object_name'] == 'Start Year')
look_ahead = next(int(i['parameter_value']) for i in db_config_parameters if i['object_name'] == 'Look Ahead')
current_emlab_tick, current_competes_tick, current_competes_tick_rounded = get_current_ticks(db_emlab,
start_simulation_year)
finally:
db_emlab.close_connection()
db_config.close_connection()
aimms_service_name = sys.argv[3]
print('Running AIMMS service ' + aimms_service_name)
port = 8080
def export_all_competes_results():
"""
This is the main export function
"""
print('Establishing Database Connections...')
db_emlab = SpineDB(sys.argv[1])
db_competes = SpineDB(sys.argv[2])
db_config = SpineDB(sys.argv[3])
print('Done')
try:
db_config_parameters = db_config.query_object_parameter_values_by_object_class(
'Coupling Parameters')
start_simulation_year = next(
int(i['parameter_value']) for i in db_config_parameters
if i['object_name'] == 'Start Year')
look_ahead = next(
int(i['parameter_value']) for i in db_config_parameters
if i['object_name'] == 'Look Ahead')
current_emlab_tick, current_competes_tick, current_competes_tick_rounded = get_current_ticks(
db_emlab, start_simulation_year)
print('Current EM-Lab tick: ' + str(current_emlab_tick))
print('Current COMPETES tick: ' + str(current_competes_tick))
db_emlab_powerplants, db_emlab_ppdps, db_competes_powerplants, db_emlab_mcps, db_competes_vre_capacities, \
db_emlab_technologies, db_competes_new_technologies = query_databases(db_emlab, db_competes)
print('Staging next SpineDB alternative...')
step = next(
int(i['parameter_value']) for i in db_config_parameters
if i['object_name'] == 'Time Step')
db_emlab.import_alternatives([str(current_emlab_tick + step)])
path_to_competes_results = sys.argv[4]
file_name_gentransinv = sys.argv[5].replace(
'?', str(current_competes_tick + look_ahead))
file_name_gentransdisp = sys.argv[6].replace(
'?', str(current_competes_tick))
print('Loading sheets...')
hourly_nodal_prices_df, unit_generation_df, new_generation_capacity_df, decommissioning_df, vre_investment_df, \
hourly_nl_balance_df, yearly_emissions_df = read_excel_sheets(path_to_competes_results, file_name_gentransinv,
file_name_gentransdisp)
new_generation_capacity_df = crop_dataframe_until_first_empty_row(
new_generation_capacity_df)
vre_investment_df = crop_dataframe_until_first_empty_row(
vre_investment_df)
decommissioning_df = crop_dataframe_until_first_empty_row(
decommissioning_df)
# VRE Plants are added to unit_generation sheet
hourly_nl_balance_df = hourly_nl_balance_df.rename(
columns={
'Sun': 'SunPV',
'Wind Onshore': 'WindOn',
'Wind Offshore': 'WindOff'
})
unit_generation_df = unit_generation_df.transpose().join(
hourly_nl_balance_df[['SunPV', 'WindOn',
'WindOff']]).replace(np.nan, 0)
print('Done loading sheets')
hourly_nodal_prices_nl = get_hourly_nodal_prices(
hourly_nodal_prices_df)
export_vre_investment_decisions(db_emlab, db_competes,
current_emlab_tick,
current_competes_tick,
vre_investment_df,
db_emlab_technologies,
db_competes_vre_capacities, step)
export_market_clearing_points_to_emlab(db_emlab, current_emlab_tick,
hourly_nodal_prices_nl,
db_emlab_mcps)
export_power_plant_dispatch_plans_to_emlab(
db_emlab, current_emlab_tick, unit_generation_df, db_emlab_ppdps,
hourly_nodal_prices_nl, db_emlab_powerplants)
export_investment_decisions_to_emlab_and_competes(
db_emlab, db_competes, current_emlab_tick,
new_generation_capacity_df, current_competes_tick,
db_emlab_technologies, db_competes_new_technologies)
export_decommissioning_decisions_to_emlab_and_competes(
db_competes, db_emlab, db_competes_powerplants, decommissioning_df,
current_competes_tick, current_emlab_tick, look_ahead)
export_total_sum_exports_to_emlab(db_emlab, hourly_nl_balance_df,
current_emlab_tick)
export_yearly_emissions_to_emlab(db_emlab, yearly_emissions_df,
current_emlab_tick)
print('Committing...')
db_emlab.commit('Imported from COMPETES run ' +
str(current_competes_tick))
db_competes.commit('Imported from COMPETES run ' +
str(current_competes_tick))
print('Done')
except Exception as e:
print('Exception occurred: ' + str(e))
raise
finally:
print('Closing database connection...')
db_emlab.close_connection()
db_competes.close_connection()
db_config.close_connection()
def execute_all_preprocessing():
"""
This function executes all steps of this script.
"""
print('Creating connection to SpineDB...')
db_emlab = SpineDB(sys.argv[1])
db_config = SpineDB(sys.argv[2])
print('Querying SpineDB...')
start_simulation_year = next(
int(i['parameter_value'])
for i in db_config.query_object_parameter_values_by_object_class(
'Coupling Parameters') if i['object_name'] == 'Start Year')
db_emlab_powerplants = db_emlab.query_object_parameter_values_by_object_class(
'PowerPlants')
current_emlab_tick, current_competes_tick, current_competes_tick_rounded = get_current_ticks(
db_emlab, start_simulation_year)
print('Done querying')
print('Current EMLAB Tick: ' + str(current_emlab_tick))
print('Current COMPETES Tick: ' + str(current_competes_tick))
try:
set_correct_power_plant_statuses(db_emlab, db_emlab_powerplants,
current_competes_tick)
hotfix_disable_double_vre_plants(db_emlab, current_emlab_tick)
print('Committing...')
db_emlab.commit('DB EMLAB Preprocessing tick ' +
str(current_competes_tick))
except Exception as e:
print('Exception thrown: ' + str(e))
raise
finally:
db_emlab.close_connection()
db_config.close_connection()
def execute_all_initialization_preprocessing():
"""
This function executes all steps of this script.
"""
print('Creating connection to SpineDB...')
db_emlab = SpineDB(sys.argv[1])
db_competes = SpineDB(sys.argv[2])
db_config = SpineDB(sys.argv[3])
try:
print('Querying SpineDB...')
db_emlab_fuelmap = db_emlab.query_object_parameter_values_by_object_class(
'FuelMap')
db_emlab_technologies_fuel = db_emlab.query_object_parameter_values_by_object_class(
'PowerGeneratingTechnologyFuel')
db_emlab_technologies = db_emlab.query_object_parameter_values_by_object_class(
'PowerGeneratingTechnologies')
db_competes_vre_capacities = db_competes.query_object_parameter_values_by_object_class(
'VRE Capacities')
db_competes_vre_technologies = db_competes.query_object_parameter_values_by_object_class(
'VRE Technologies')
db_competes_technologies = db_competes.query_object_parameter_values_by_object_class(
'Technologies')
print('Done querying')
start_simulation_year = next(
int(i['parameter_value'])
for i in db_config.query_object_parameter_values_by_object_class(
'Coupling Parameters') if i['object_name'] == 'Start Year')
replace_power_generating_technology_fuel_names(
db_emlab, db_emlab_fuelmap, db_emlab_technologies_fuel)
import_initial_vre(db_emlab, db_competes_vre_capacities,
start_simulation_year)
import_initial_fixed_oc_start_values(db_emlab,
db_competes_technologies,
db_emlab_technologies)
import_initial_vre_fixed_oc_start_values(db_emlab,
db_competes_vre_technologies,
db_emlab_technologies,
start_simulation_year)
print('Committing...')
db_emlab.commit('DB EMLAB Initialization Preprocessing')
except Exception as e:
print('Exception thrown: ' + str(e))
raise
finally:
db_emlab.close_connection()
db_competes.close_connection()
db_config.close_connection()