def get_powerplant(name,
fuel_type,
market,
node,
power_hub,
effective_date=datetime.now().date()):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
sql_statement = """
SELECT * FROM powerplant
where name = %s
and
fuel_type = %s
and
market = %s
and
node = %s
and
power_hub = %s
and
effective_start <= %s
and
effective_end >= %s
;
"""
powerplant_df = pd.read_sql(sql_statement,
connection_instance,
params=[
name, fuel_type, market, node, power_hub,
effective_date, effective_date
])
connection_instance.close()
return powerplant_df
def get_historical_lmp(node_id,
start_date,
end_date,
dart,
database=PROD_DATABASE):
connection_instance = config_connection(HOST, USER, PASSWORD, database)
sql_statment = """
SELECT * FROM lmp_new
where
node_id = %s
and
delivery_date >= %s
and
delivery_date <= %s
and
dart = %s
;
"""
raw_lmp_df = pd.read_sql(sql_statment,
connection_instance,
params=[node_id, start_date, end_date, dart])
connection_instance.close()
return raw_lmp_df
def get_technology(project):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
sql_statement = """
SELECT * FROM technology where project = %s;
"""
technology_df = pd.read_sql(sql_statement,
connection_instance,
params=[project])
connection_instance.close()
return technology_df
def get_powerplants(effective_date=datetime.now().date()):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
sql_statement = """
SELECT * FROM powerplant WHERE effective_start <= %s and effective_end >= %s;
"""
powerplants_df = pd.read_sql(sql_statement,
connection_instance,
params=[effective_date, effective_date])
connection_instance.close()
return powerplants_df
def get_powerplants_by_portfolio(portfolio,
effective_date=datetime.now().date()):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
sql_statement = """
SELECT * FROM powerplant WHERE effective_start <= %s and effective_end >= %s and name in (select distinct entity_name from portfolio where name = %s and entity_type='plant');
"""
powerplants_df = pd.read_sql(
sql_statement,
connection_instance,
params=[effective_date, effective_date, portfolio])
connection_instance.close()
return powerplants_df
def put_characteristics(ready_to_kean_pcuc_df, scenario, version):
connection_instance = config_connection(HOST, USER, PASSWORD,
PROD_DATABASE)
delete_sql_statment = """
DELETE FROM plant_characteristics
where
scenario = '""" + scenario + """'
and
version = '""" + version + """';
"""
cursor = connection_instance.cursor()
cursor.execute(delete_sql_statment)
connection_instance.commit()
connection_instance.close()
engine_str = 'mysql+mysqlconnector://' + USER + ':' + PASSWORD + '@' + HOST + '/' + PROD_DATABASE
engine = create_engine(engine_str, encoding='latin1', echo=True)
step = 3000
current_index = 0
while current_index + step < len(ready_to_kean_pcuc_df):
ready_to_kean_pcuc_df.iloc[current_index:current_index + step].to_sql(
name='plant_characteristics',
con=engine,
if_exists='append',
index=False)
current_index += step
ready_to_kean_pcuc_df.iloc[current_index:].to_sql(
name='plant_characteristics',
con=engine,
if_exists='append',
index=False)
version_log_df = pd.DataFrame(
columns=[
'timestamp', 'user', 'table_name', 'scenario', 'version',
'description', 'number_of_records_inserted'
],
data=[[
datetime.now(), '*****@*****.**',
'plant_characteristics', scenario, version,
'loaded from script as of ' + str(datetime.now()),
len(ready_to_kean_pcuc_df)
]])
version_log_df.to_sql(name='version_log',
con=engine,
if_exists='append',
index=False)
def put_technology(ready_to_kean_tech_df):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
delete_sql_statment = """
DELETE FROM technology;
"""
cursor = connection_instance.cursor()
cursor.execute(delete_sql_statment)
connection_instance.commit()
connection_instance.close()
engine_str = 'mysql+mysqlconnector://' + USER + ':' + PASSWORD + '@' + HOST + '/' + DATABASE
engine = create_engine(engine_str, encoding='latin1', echo=True)
ready_to_kean_tech_df.to_sql(name='technology',
con=engine,
if_exists='append',
index=False)
def get_lbo_assumptions(portfolio, scenario, version):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
sql_statement = """
SELECT * FROM lbo_assumptions
where
portfolio = %s
and
scenario = %s
and
version = %s;
"""
lbo_assumptions_df = pd.read_sql(sql_statement,
connection_instance,
params=[portfolio, scenario, version])
connection_instance.close()
return lbo_assumptions_df
def put_powerplant(ready_to_kean_pp_df, id_powerplant=[]):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
engine_str = 'mysql+mysqlconnector://' + USER + ':' + PASSWORD + '@' + HOST + '/' + DATABASE
engine = create_engine(engine_str, encoding='latin1', echo=True)
if id_powerplant != [] and id_powerplant is not None:
sql_statement = """
delete from powerplant where id_powerplant in (""" + ", ".join(
id_powerplant) + """ );
"""
cursor = connection_instance.cursor()
cursor.execute(sql_statement)
connection_instance.commit()
connection_instance.close()
ready_to_kean_pp_df.to_sql(name='powerplant',
con=engine,
if_exists='append',
index=False)
def get_portfolio_with_powerplant(portfolio_name):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
sql_statement = """ select
a.name as portfolio_name,
a.entity_name as powerplant_name,
b.technology as technology_name,
b.fuel_type as fuel_type,
b.market as market,
b.power_hub as power_hub,
b.power_zone as power_zone,
b.power_hub_on_peak as power_hub_on_peak,
b.power_hub_off_peak as power_hub_off_peak,
b.node as node,
b.fuel_zone as fuel_zone,
b.fuel_hub as fuel_hub,
b.summer_fuel_basis as summer_fuel_basis,
b.winter_fuel_basis as winter_fuel_basis,
b.summer_duct_capacity as summer_duct_capacity,
b.summer_base_capacity as summer_base_capacity,
b.winter_duct_capacity as winter_duct_capacity,
b.winter_base_capacity as winter_base_capacity,
b.first_plan_outage_start as first_plan_outage_start,
b.first_plan_outage_end as first_plan_outage_end,
b.second_plan_outage_start as second_plan_outage_start,
b.second_plan_outage_end as second_plan_outage_end,
b.carbon_cost as carbon_cost,
b.source_notes as source_notes,
b.retirement_date as retirement_date,
b.ownership as ownership
from
(select * from portfolio where name = %s and entity_type='plant' ) as a
left join
(select * from powerplant ) as b
on a.entity_name = b.name
where b.effective_start <= CURDATE() and b.effective_end >= CURDATE(); """
portfolio_with_powerplant_df = pd.read_sql(sql_statement,
connection_instance,
params=[portfolio_name])
connection_instance.close()
return portfolio_with_powerplant_df
def delete_scenario_datetime(portfolio, scenario, version, module):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
delete_sql_statment = """
DELETE FROM scenario_datetime
where
portfolio = '""" + portfolio + """'
and
module = '""" + module + """'
and
scenario = '""" + scenario + """'
and
version = '""" + version + """'
;
"""
print(delete_sql_statment)
cursor = connection_instance.cursor()
cursor.execute(delete_sql_statment)
connection_instance.commit()
connection_instance.close()
def delete_scenario_master(output_portfolio, output_scenario_name,
output_version, output_module, output_table):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
delete_sql_statment = """
DELETE FROM scenario_master
where
portfolio = '""" + output_portfolio + """'
and
output_module = '""" + output_module + """'
and
output_scenario = '""" + output_scenario_name + """'
and
output_table = '""" + output_table + """'
and
output_version = '""" + output_version + """'
;
"""
cursor = connection_instance.cursor()
cursor.execute(delete_sql_statment)
connection_instance.commit()
connection_instance.close()
def put_financials_lbo(ready_to_kean_lbo_financials_df,
portfolio,
scenario,
version,
overwrite_option=False):
if overwrite_option:
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
delete_sql_statment = """
DELETE FROM financials_lbo
where
portfolio = '""" + portfolio + """'
and
scenario = '""" + scenario + """'
and
version = '""" + version + """';
"""
cursor = connection_instance.cursor()
cursor.execute(delete_sql_statment)
connection_instance.commit()
connection_instance.close()
engine_str = 'mysql+mysqlconnector://' + USER + ':' + PASSWORD + '@' + HOST + '/' + DATABASE
engine = create_engine(engine_str, encoding='latin1', echo=True)
# prices_df.to_sql(name='prices', con=engine, if_exists='append', index=False)
step = 3000
current_index = 0
while current_index + step < len(ready_to_kean_lbo_financials_df):
ready_to_kean_lbo_financials_df.iloc[current_index:current_index +
step].to_sql(
name='financials_lbo',
con=engine,
if_exists='append',
index=False)
current_index += step
ready_to_kean_lbo_financials_df.iloc[current_index:].to_sql(
name='financials_lbo', con=engine, if_exists='append', index=False)
def put_powerplants(ready_to_kean_pp_df,
portfolio=None,
overwrite_option=False):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
if portfolio is not None and overwrite_option:
print("============================== herer?")
sql_statement = " delete from powerplant where name in (select distinct entity_name from portfolio where name = %s and entity_type = 'plant');"
print(sql_statement, portfolio)
cursor = connection_instance.cursor()
cursor.execute(sql_statement, params=[portfolio])
connection_instance.commit()
connection_instance.close()
engine_str = 'mysql+mysqlconnector://' + USER + ':' + PASSWORD + '@' + HOST + '/' + DATABASE
engine = create_engine(engine_str, encoding='latin1', echo=True)
ready_to_kean_pp_df.to_sql(name='powerplant',
con=engine,
if_exists='append',
index=False)
def get_scenario_master_datetime(portfolio, scenario, version, module):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
sql_statment = """
SELECT * FROM scenario_datetime
where
portfolio = '""" + portfolio + """'
and
module = '""" + module + """'
and
scenario = '""" + scenario + """'
and
version = '""" + version + """'
;
"""
# print (sql_statment)
raw_scenario_master_datetime_df = pd.read_sql(sql_statment,
connection_instance,
params=[])
connection_instance.close()
return raw_scenario_master_datetime_df
def get_scenario_master(output_portfolio, output_scenario_name, output_version,
output_module, output_table):
connection_instance = config_connection(HOST, USER, PASSWORD, DATABASE)
sql_statment = """
SELECT * FROM scenario_master
where
portfolio = '""" + output_portfolio + """'
and
output_module = '""" + output_module + """'
and
output_scenario = '""" + output_scenario_name + """'
and
output_table = '""" + output_table + """'
and
output_version = '""" + output_version + """'
;
"""
raw_scenario_master_df = pd.read_sql(sql_statment,
connection_instance,
params=[])
connection_instance.close()
return raw_scenario_master_df