def load_networks() -> None:
"""
Load the networks fixture
"""
fixture = load_data("networks.json", from_fixture=True)
s = SessionLocal()
for network in fixture:
network_model = s.query(Network).filter_by(
code=network["code"]).one_or_none()
if not network_model:
network_model = Network(code=network["code"])
network_model.label = network["label"]
network_model.country = network["country"]
network_model.timezone = network["timezone"]
network_model.timezone_database = network["timezone_database"]
network_model.offset = network["offset"]
network_model.interval_size = network["interval_size"]
network_model.network_price = network["network_price"]
if "interval_shift" in network:
network_model.interval_shift = network["interval_shift"]
if "export_set" in network:
network_model.export_set = network["export_set"]
try:
s.add(network_model)
s.commit()
except Exception:
logger.error("Have {}".format(network_model.code))
def load_network_regions() -> None:
"""
Load the network region fixture
"""
fixture = load_data("network_regions.json", from_fixture=True)
s = SessionLocal()
for network_region in fixture:
network_region_model = (s.query(NetworkRegion).filter_by(
code=network_region["code"],
network_id=network_region["network_id"]).one_or_none())
if not network_region_model:
network_region_model = NetworkRegion(code=network_region["code"])
network_region_model.network_id = network_region["network_id"]
try:
s.add(network_region_model)
s.commit()
logger.debug("Loaded network region {}".format(
network_region_model.code))
except Exception:
logger.error("Have {}".format(network_region_model.code))
def load_bom_stations_csv() -> None:
"""
Imports the BOM fixed-width stations format
Made redundant with the new JSON
"""
s = SessionLocal()
station_csv = load_data("stations_db.txt", from_fixture=True)
lines = station_csv.split("\n")
for line in lines:
code, state, name, registered, lng, lat = parse_fixed_line(line)
station = s.query(BomStation).filter_by(code=code).one_or_none()
if not station:
station = BomStation(
code=code,
state=state,
name=name,
registered=registered,
)
station.geom = "SRID=4326;POINT({} {})".format(lat, lng)
try:
s.add(station)
s.commit()
except Exception:
logger.error("Have {}".format(station.code))
def store_stats_database(statset: StatsSet) -> int:
s = SessionLocal()
records_to_store = [i.dict() for i in statset.stats]
stmt = insert(Stats).values(records_to_store)
stmt.bind = get_database_engine()
stmt = stmt.on_conflict_do_update(
index_elements=[
"stat_date",
"country",
"stat_type",
],
set_={
"value": stmt.excluded.value,
},
)
try:
s.execute(stmt)
s.commit()
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
return 0
finally:
s.close()
num_records = len(records_to_store)
logger.info("Wrote {} records to database".format(num_records))
return num_records
def wikidata_join():
session = SessionLocal()
wikidata = load_data("wikidata-parsed.json", from_project=True)
# session.add()
for entry in wikidata:
station_name = entry.get("name")
station_lookup = (session.query(Station).filter(
Station.name == station_name).all())
if len(station_lookup) == 0:
logger.info("Didn't find a station for {}".format(station_name))
if len(station_lookup) == 1:
station = station_lookup.pop()
station.description = entry.get("description")
station.wikipedia_link = entry.get("wikipedia")
station.wikidata_id = entry.get("wikidata_id")
session.add(station)
logger.info("Updated station {}".format(station_name))
if len(station_lookup) > 1:
logger.info("Found multiple for station {}".format(station_name))
session.commit()
def load_bom_stations_json() -> None:
"""
Imports BOM stations into the database from bom_stations.json
The json is obtained using scripts/bom_stations.py
"""
session = SessionLocal()
bom_stations = load_data("bom_stations.json", from_project=True)
bom_capitals = load_data("bom_capitals.json", from_project=True)
codes = []
if not bom_stations:
logger.error("Could not load bom stations")
stations_imported = 0
for bom_station in bom_stations:
if "code" not in bom_station:
logger.error("Invalida bom station ..")
continue
if bom_station["code"] in codes:
continue
codes.append(bom_station["code"])
station = session.query(BomStation).filter_by(
code=bom_station["code"]).one_or_none()
if not station:
logger.info("New BOM station: %s", bom_station["name"])
station = BomStation(code=bom_station["code"], )
station.name = bom_station["name_full"]
station.name_alias = bom_station["name"]
station.website_url = bom_station["url"]
station.feed_url = bom_station["json_feed"]
station.priority = 5
station.state = bom_station["state"]
station.altitude = bom_station["altitude"]
if "web_code" in bom_station:
station.web_code = bom_station["web_code"]
if bom_station["code"] in bom_capitals:
station.is_capital = True
station.priority = 1
station.geom = "SRID=4326;POINT({} {})".format(bom_station["lng"],
bom_station["lat"])
stations_imported += 1
session.add(station)
logger.info("Imported {} stations".format(stations_imported))
session.commit()
def process_unit_solution(table):
session = SessionLocal()
engine = get_database_engine()
if "records" not in table:
raise Exception("Invalid table no records")
records = table["records"]
records_to_store = []
records_primary_keys = []
for record in records:
trading_interval = parse_date(
record["SETTLEMENTDATE"], network=NetworkNEM, dayfirst=False
)
facility_code = normalize_duid(record["DUID"])
if not trading_interval or not facility_code:
continue
# Since this can insert 1M+ records at a time we need to
# do a separate in-memory check of primary key constraints
# better way of doing this .. @TODO
_unique_set = (trading_interval, facility_code, "NEM")
if _unique_set not in records_primary_keys:
records_to_store.append(
{
"trading_interval": trading_interval,
"facility_code": facility_code,
"eoi_quantity": float(record["INITIALMW"]),
"network_id": "NEM",
}
)
records_primary_keys.append(_unique_set)
# free
records_primary_keys = []
logger.debug("Saving %d records", len(records_to_store))
stmt = insert(FacilityScada).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
constraint="facility_scada_pkey",
set_={"eoi_quantity": stmt.excluded.eoi_quantity},
)
try:
session.execute(stmt)
session.commit()
except Exception as e:
logger.error("Error: {}".format(e))
return 0
finally:
session.close()
return len(records_to_store)
def store_bom_records_temp_max(recs: List[Dict]) -> None:
# records_to_store = [i for i in recs if i["temp_max"] is not None]
records_to_store = recs
first = recs[0]
update_field = "temp_max"
if "temp_min" in first:
update_field = "temp_min"
engine = get_database_engine()
session = SessionLocal()
# insert
stmt = insert(BomObservation).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["observation_time", "station_id"],
set_={update_field: getattr(stmt.excluded, update_field)},
)
try:
session.execute(stmt)
session.commit()
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
return {"num_records": 0}
finally:
session.close()
return {"num_records": len(records_to_store)}
def import_photos_from_fixtures() -> None:
""" Import photos to stations """
session = SessionLocal()
photo_records = get_import_photo_data()
for photo_record in photo_records:
station = (session.query(Station).filter(
Station.code == photo_record.station_code).one_or_none())
if not station:
logger.error("Could not find station {}".format(
photo_record.station_code))
continue
img = get_image_from_web(photo_record.image_url)
if not img:
logger.error("No image for {}".format(photo_record.image_url))
continue
hash_id = image_get_crypto_hash(img)[-8:]
file_name = "{}_{}_{}.{}".format(hash_id,
station.name.replace(" ", "_"),
"original", "jpeg")
photo = session.query(Photo).filter_by(hash_id=hash_id).one_or_none()
if not photo:
photo = Photo(hash_id=hash_id, )
photo.name = file_name
photo.width = img.size[0]
photo.height = img.size[1]
photo.original_url = photo_record.image_url
photo.license_type = photo_record.license
photo.license_link = photo_record.license_link
photo.author = photo_record.author
photo.author_link = photo_record.author_link
if photo_record.is_primary:
photo.is_primary = True
photo.approved = True
photo.approved_by = "opennem.importer.photos"
photo.approved_at = datetime.now()
img_buff = img_to_buffer(img)
write_photo_to_s3(file_name, img_buff)
if station:
station.photos.append(photo)
session.add(photo)
if station:
session.add(station)
session.commit()
def process_item(self, item, spider=None):
s = SessionLocal()
record_count = 0
csvreader = csv.DictReader(item["content"].split("\n"))
for row in csvreader:
if "PARTICIPANT_CODE" not in row:
logger.error("Invalid row")
continue
participant = None
participant_code = normalize_duid(
normalize_string(row["PARTICIPANT_CODE"])
)
participant_name = participant_name_filter(
row["PARTICIPANT_DISPLAY_NAME"]
) or participant_name_filter(
row.get("PARTICIPANT_FULL_NAME", None)
)
participant = (
s.query(Participant)
.filter(Participant.code == participant_code)
.one_or_none()
)
if not participant:
participant = Participant(
code=participant_code,
name=participant_name,
created_by=spider.name,
)
logger.info(
"Created new WEM participant: {}".format(participant_code)
)
elif participant.name != participant_name:
participant.name = participant_name
participant.updated_by = spider.name
logger.info(
"Updated WEM participant: {}".format(participant_code)
)
try:
s.add(participant)
s.commit()
record_count += 1
except Exception as e:
logger.error(e)
s.close()
return record_count
def rooftop_facilities() -> None:
session = SessionLocal()
for state_map in STATE_NETWORK_REGION_MAP:
state_rooftop_code = "{}_{}_{}".format(
ROOFTOP_CODE,
state_map["network"].upper(),
state_map["state"].upper(),
)
rooftop_station = session.query(Station).filter_by(
code=state_rooftop_code).one_or_none()
if not rooftop_station:
logger.info("Creating new station {}".format(state_rooftop_code))
rooftop_station = Station(code=state_rooftop_code, )
rooftop_station.name = "Rooftop Solar {}".format(state_map["state"])
rooftop_station.description = "Solar rooftop facilities for {}".format(
state_map["state"])
rooftop_station.approved = False
rooftop_station.approved_by = ""
rooftop_station.created_by = "opennem.importer.rooftop"
if not rooftop_station.location:
rooftop_station.location = Location(state=state_map["state"])
rooftop_fac = session.query(Facility).filter_by(
code=state_rooftop_code).one_or_none()
if not rooftop_fac:
logger.info("Creating new facility {}".format(state_rooftop_code))
rooftop_fac = Facility(code=state_rooftop_code)
network = state_map["network"]
# map to separate AEMO rooftop network
if network.upper() == "NEM":
network = "AEMO_ROOFTOP"
rooftop_fac.network_id = network
rooftop_fac.network_region = state_map["network_region"]
rooftop_fac.fueltech_id = "solar_rooftop"
rooftop_fac.status_id = "operating"
rooftop_fac.active = True
rooftop_fac.dispatch_type = DispatchType.GENERATOR
rooftop_fac.approved_by = "opennem.importer.rooftop"
rooftop_fac.created_by = "opennem.importer.rooftop"
rooftop_station.facilities.append(rooftop_fac)
session.add(rooftop_fac)
session.add(rooftop_station)
session.commit()
def process_item(self, item, spider=None):
s = SessionLocal()
record_count = 0
csvreader = csv.DictReader(item["content"].split("\n"))
for row in csvreader:
if "Participant Code" not in row:
logger.error("Invalid row")
continue
participant = None
participant_code = normalize_duid(row["Participant Code"])
participant_name = participant_name_filter(row["Participant Name"])
participant = (
s.query(Participant)
.filter(Participant.code == participant_code)
.one_or_none()
)
if not participant:
participant = Participant(
code=participant_code,
name=participant_name,
# @TODO WEM provides these but nem doesn't so ignore for noe
# address=row["Address"],
# city=row["City"],
# state=row["State"],
# postcode=row["Postcode"],
created_by="pipeline.wem.participant",
)
logger.info(
"Created new WEM participant: {}".format(participant_code)
)
elif participant.name != participant_name:
participant.name = participant_name
participant.updated_by = "pipeline.wem.participant"
logger.info(
"Updated WEM participant: {}".format(participant_code)
)
try:
s.add(participant)
s.commit()
record_count += 1
except Exception as e:
logger.error(e)
s.close()
return record_count
def process_item(self, item, spider=None):
s = SessionLocal()
csvreader = csv.DictReader(item["content"].split("\n"))
records_to_store = []
primary_keys = []
for row in csvreader:
trading_interval = parse_date(row["TRADING_DAY_INTERVAL"],
network=NetworkWEM,
dayfirst=False)
if trading_interval not in primary_keys:
forecast_load = clean_float(row["FORECAST_EOI_MW"])
records_to_store.append({
"created_by": spider.name,
"trading_interval": trading_interval,
"network_id": "WEM",
"network_region": "WEM",
"forecast_load": forecast_load,
# generation_scheduled=row["Scheduled Generation (MW)"],
# generation_total=row["Total Generation (MW)"],
"price": clean_float(row["PRICE"]),
})
primary_keys.append(trading_interval)
stmt = insert(BalancingSummary).values(records_to_store)
stmt.bind = get_database_engine()
stmt = stmt.on_conflict_do_update(
index_elements=[
"trading_interval",
"network_id",
"network_region",
],
set_={
"price": stmt.excluded.price,
"forecast_load": stmt.excluded.forecast_load,
},
)
try:
s.execute(stmt)
s.commit()
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
return 0
finally:
s.close()
return len(records_to_store)
def wikidata_join_mapping() -> None:
"""Attempts to join the wikidata to OpenNEM stations using the
csv file with mappings"""
session = SessionLocal()
wikidata = load_data("wikidata-parsed.json", from_project=True)
wikidata_mappings = None
with open("opennem/data/wikidata_mappings.csv") as fh:
csvreader = csv.DictReader(
fh,
fieldnames=[
"code",
"name",
"network_id",
"network_region",
"fueltech_id",
"wikidata_id",
],
)
wikidata_mappings = {
i["code"]: i["wikidata_id"]
for i in list(csvreader)
if i["wikidata_id"] and i["code"] != "code"
}
for station_code, wikidata_id in wikidata_mappings.items():
wikidata_record_lookup = list(
filter(lambda x: x["wikidata_id"] == wikidata_id, wikidata))
if len(wikidata_record_lookup) == 0:
logger.error("Could not find {}".format(wikidata_id))
continue
wikidata_record = wikidata_record_lookup.pop()
station = session.query(Station).filter(
Station.code == station_code).one_or_none()
if not station:
logger.error("Didn't find a station for {}".format(station_code))
continue
station.description = wikidata_record.get("description")
station.wikipedia_link = wikidata_record.get("wikipedia")
station.wikidata_id = wikidata_record.get("wikidata_id")
session.add(station)
logger.info("Updated station {}".format(station_code))
session.commit()
def update_weather() -> None:
wc = WillyClient()
for bom_code, willyid in WILLY_MAP.items():
r = wc.get_location_temp(willyid, days=3, start_date="2021-10-14")
data_points = r["observationalGraphs"]["temperature"]["dataConfig"][
"series"]["groups"]
records = []
pprint(r)
for pointset in data_points:
# print(p)
for p in pointset["points"]:
r_dict = {
"station_id":
bom_code,
"observation_time":
unix_timestamp_to_aware_datetime(p["x"],
"Australia/Sydney"),
"temp_air":
p["y"],
}
print("{} -> {}".format(r_dict["observation_time"],
r_dict["temp_air"]))
records.append(r_dict)
session = SessionLocal()
engine = get_database_engine()
stmt = insert(BomObservation).values(records)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["observation_time", "station_id"],
set_={
# "temp_apparent": stmt.excluded.temp_apparent,
"temp_air": stmt.excluded.temp_air,
},
)
try:
session.execute(stmt)
session.commit()
except Exception as e:
logger.error("Error: {}".format(e))
finally:
session.close()
def process_pre_ap_price(table):
session = SessionLocal()
engine = get_database_engine()
if "records" not in table:
raise Exception("Invalid table no records")
records = table["records"]
records_to_store = []
for record in records:
trading_interval = parse_date(
record["SETTLEMENTDATE"], network=NetworkNEM
)
if not trading_interval:
continue
records_to_store.append(
{
"network_id": "NEM",
"network_region": record["REGIONID"],
"trading_interval": trading_interval,
"price": record["PRE_AP_ENERGY_PRICE"],
}
)
stmt = insert(BalancingSummary).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
constraint="balancing_summary_pkey",
set_={"price": stmt.excluded.price,},
)
try:
r = session.execute(stmt)
session.commit()
return r
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
finally:
session.close()
return len(records_to_store)
def process_meter_data_gen_duid(table):
session = SessionLocal()
engine = get_database_engine()
if "records" not in table:
raise Exception("Invalid table no records")
records = table["records"]
records_to_store = []
for record in records:
trading_interval = parse_date(
record["INTERVAL_DATETIME"], network=NetworkNEM, dayfirst=False
)
if not trading_interval:
continue
records_to_store.append(
{
"network_id": "NEM",
"trading_interval": trading_interval,
"facility_code": normalize_duid(record["DUID"]),
"eoi_quantity": record["MWH_READING"],
}
)
stmt = insert(FacilityScada).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
constraint="facility_scada_pkey",
set_={"eoi_quantity": stmt.excluded.eoi_quantity,},
)
try:
session.execute(stmt)
session.commit()
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
return 0
finally:
session.close()
return len(records_to_store)
def process_item(self, item, spider=None):
session = SessionLocal()
engine = get_database_engine()
csvreader = csv.DictReader(item["content"].split("\n"))
records_to_store = []
for row in csvreader:
trading_interval = parse_date(
row["Trading Interval"], dayfirst=True, network=NetworkWEM
)
facility_code = normalize_duid(row["Facility Code"])
records_to_store.append(
{
"network_id": "WEM",
"trading_interval": trading_interval,
"facility_code": facility_code,
"eoi_quantity": row["EOI Quantity (MW)"] or None,
"generated": row["Energy Generated (MWh)"] or None,
}
)
stmt = insert(FacilityScada).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
constraint="facility_scada_pkey",
set_={
"eoi_quantity": stmt.excluded.eoi_quantity,
"generated": stmt.excluded.generated,
},
)
try:
session.execute(stmt)
session.commit()
except Exception as e:
logger.error("Error: {}".format(e))
finally:
session.close()
return len(records_to_store)
def process_item(self, item, spider=None):
if "records" not in item:
logger.error("Invalid return response")
records = item["records"]
engine = get_database_engine()
session = SessionLocal()
records_to_store = []
for record in records:
records_to_store.append({
# "network_id": "WEM" if state == "WA" else "NEM",
# "trading_interval": interval_time,
# "facility_code": facility_code,
# "generated": generated,
})
# free
primary_keys = None
stmt = insert(FacilityScada).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=[
"trading_interval", "network_id", "facility_code",
"is_forecast"
],
set_={
"generated": stmt.excluded.generated,
},
)
try:
session.execute(stmt)
session.commit()
except Exception as e:
logger.error("Error: {}".format(e))
finally:
session.close()
return len(records_to_store)
def import_rooftop_aemo_backfills() -> None:
csvrecords = []
backfill_file = Path(__file__).parent / "aemo_rooftop_backfill.csv"
with backfill_file.open() as fh:
fieldnames = fh.readline().strip().split(",")
csvreader = csv.DictReader(fh, fieldnames=fieldnames)
csvrecords = [BackfillRecord(**i) for i in csvreader]
logger.debug("Loaded {} records".format(len(csvrecords)))
export_records = [
i.dict(exclude={"energy_v2", "energy_v3", "network_region"})
for i in csvrecords
]
session = SessionLocal()
records_added = 0
records_updated = 0
for rec in export_records:
model = (session.query(AggregateFacilityDaily).filter_by(
network_id="AEMO_ROOFTOP_BACKFILL").filter_by(
trading_day=rec["trading_day"]).filter_by(
facility_code=rec["facility_code"])).first()
if not model:
model = AggregateFacilityDaily(**rec)
records_added += 1
else:
records_updated += 1
model.energy = rec["energy"]
model.market_value = rec["market_value"]
session.add(model)
session.commit()
logger.debug("Added {} records and updated {}".format(
records_added, records_updated))
def import_aemo_facility_closure_dates() -> bool:
closure_records = parse_aemo_closures_xls()
session = SessionLocal()
for record in closure_records:
facility: Optional[Facility] = (session.query(Facility).filter_by(
network_id="NEM").filter_by(code=record.duid).one_or_none())
if not facility:
logger.info("Could not find facility {} - {}".format(
record.duid, record.station_name))
continue
facility.expected_closure_date = record.expected_closure_date
facility.expected_closure_year = record.expected_closure_year
session.add(facility)
session.commit()
return True
def load_fueltechs() -> None:
"""
Load the fueltechs fixture
"""
fixture = load_data("fueltechs.json", from_fixture=True)
s = SessionLocal()
for ft in fixture:
fueltech = s.query(FuelTech).filter_by(code=ft["code"]).one_or_none()
if not fueltech:
fueltech = FuelTech(code=ft["code"], )
fueltech.label = ft["label"]
fueltech.renewable = ft["renewable"]
try:
s.add(fueltech)
s.commit()
except Exception:
logger.error("Have {}".format(fueltech.code))
def load_facilitystatus() -> None:
"""
Load the facility status fixture
"""
fixture = load_data("facility_status.json", from_fixture=True)
s = SessionLocal()
for status in fixture:
facility_status = s.query(FacilityStatus).filter_by(
code=status["code"]).one_or_none()
if not facility_status:
facility_status = FacilityStatus(code=status["code"], )
facility_status.label = status["label"]
try:
s.add(facility_status)
s.commit()
except Exception:
logger.error("Have {}".format(facility_status.code))
def wikidata_join() -> None:
"""Attempts to join the wikidata to OpenNEM stations based
on the name"""
session = SessionLocal()
wikidata = load_data("wikidata-parsed.json", from_project=True)
# Use a better query
# engine = get_database_engine()
# station_lookup_query = """
# """
for entry in wikidata:
station_name = entry.get("name")
station_lookup = session.query(Station).filter(
Station.name == station_name).all()
if len(station_lookup) == 0:
logger.info("Didn't find a station for {}".format(station_name))
if len(station_lookup) == 1:
station = station_lookup.pop()
station.description = entry.get("description")
station.wikipedia_link = entry.get("wikipedia")
station.wikidata_id = entry.get("wikidata_id")
session.add(station)
logger.info("Updated station {}".format(station_name))
if len(station_lookup) > 1:
logger.info("Found multiple for station {}".format(station_name))
session.commit()
def load_bom_stations_json():
"""
Imports BOM stations into the database from bom_stations.json
The json is obtained using scripts/bom_stations.py
"""
session = SessionLocal()
bom_stations = load_data("bom_stations.json", from_project=True)
bom_capitals = load_data("bom_capitals.json", from_project=True)
for bom_station in bom_stations:
station = (session.query(BomStation).filter_by(
code=bom_station["code"]).one_or_none())
if not station:
logger.info("New station: %s", bom_station["name"])
station = BomStation(code=bom_station["code"], )
station.name = bom_station["name_full"]
station.name_alias = bom_station["name"]
station.website_url = bom_station["url"]
station.feed_url = bom_station["json_feed"]
station.priority = 5
station.state = bom_station["state"]
station.altitude = bom_station["altitude"]
if bom_station["code"] in bom_capitals:
station.is_capital = True
station.priority = 1
station.geom = "SRID=4326;POINT({} {})".format(bom_station["lng"],
bom_station["lat"])
session.add(station)
session.commit()
def process_item(self, item, spider):
s = SessionLocal()
observation = BomObservation(
station_id=item["station_id"],
observation_time=parse_date(item["aifstime_utc"]),
temp_apparent=item["apparent_t"],
temp_air=item["air_temp"],
press_qnh=item["press_qnh"],
wind_dir=item["wind_dir"],
wind_spd=item["wind_spd_kmh"],
)
try:
s.add(observation)
s.commit()
except Exception as e:
logger.error("Error: {}".format(e))
finally:
s.close()
return item
def import_emissions_map(file_name: str) -> None:
"""Import emission factors from CSV files for each network
the format of the csv file is
station_name,network_id,network_region,facility_code,emissions_factor_co2,fueltech_id,emission_factor_source
"""
session = SessionLocal()
content = load_data(file_name, from_project=True, skip_loaders=True)
csv_content = content.splitlines()
csvreader = csv.DictReader(csv_content)
for rec in csvreader:
network_id = rec["network_id"]
facility_code = normalize_duid(rec["facility_code"])
emissions_intensity = clean_float(rec["emissions_factor_co2"])
if not facility_code:
logger.info("No emissions intensity for {}".format(facility_code))
continue
facility = (session.query(Facility).filter_by(
code=facility_code).filter_by(network_id=network_id).one_or_none())
if not facility:
logger.info("No stored facility for {}".format(facility_code))
continue
facility.emissions_factor_co2 = emissions_intensity
session.add(facility)
logger.info("Updated {} to {}".format(facility_code,
emissions_intensity))
session.commit()
return None
def import_osm_ways() -> int:
"""Gets the geometries for each OSM way id and store them against the station geom"""
session = SessionLocal()
stations_with_ways = (session.query(Station).join(Location).filter(
Location.osm_way_id.isnot(None)).all())
if not stations_with_ways or len(stations_with_ways) < 1:
logger.error("No stations with ways!")
return 0
station_count = 0
for station in stations_with_ways:
location: Location = station.location
geom_boundary = None
try:
geom_boundary = get_osm_geom(station.location.osm_way_id)
except Exception as e:
logger.error("get osm wkt error: {}".format(e))
pass
if not geom_boundary:
logger.error("Error getting WKT from OSM")
continue
location.boundary = geom_boundary
session.add(location)
session.commit()
logger.info("Updated boundary geom from OSM for station: {}".format(
station.code))
return station_count
def import_osm_way_data() -> None:
"""Updates the OSM way ids for stations from the CSV file fixture"""
session = SessionLocal()
station_osm_records = get_import_osm_data()
for station_osm_record in station_osm_records:
station = (session.query(Station).filter(
Station.code == station_osm_record.station_code).one_or_none())
if not station:
logger.error("Could not find station {}".format(
station_osm_record.station_code))
continue
if not station.location:
logger.error("Station {} does not have a location".format(
station_osm_record.station_code))
continue
station.location.osm_way_id = station_osm_record.osm_way_id
session.add(station)
session.commit()
logger.info("Updated station: {}".format(station.code))
def process_trading_regionsum(table: Dict[str, Any], spider: Spider) -> Dict:
session = SessionLocal()
engine = get_database_engine()
if "records" not in table:
raise Exception("Invalid table no records")
records = table["records"]
limit = None
records_to_store = []
records_processed = 0
primary_keys = []
for record in records:
trading_interval = parse_date(
record["SETTLEMENTDATE"],
network=NetworkNEM,
dayfirst=False,
date_format="%Y/%m/%d %H:%M:%S",
)
if not trading_interval:
continue
_pk = set([trading_interval, record["REGIONID"]])
if _pk in primary_keys:
continue
primary_keys.append(_pk)
demand_total = None
if "TOTALDEMAND" in record:
demand_total = clean_float(record["TOTALDEMAND"])
records_to_store.append({
"network_id": "NEM",
"created_by": spider.name,
"network_region": record["REGIONID"],
"trading_interval": trading_interval,
"demand_total": demand_total,
})
records_processed += 1
if limit and records_processed >= limit:
logger.info("Reached limit of: {} {}".format(
limit, records_processed))
break
stmt = insert(BalancingSummary).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["trading_interval", "network_id", "network_region"],
set_={
"demand_total": stmt.excluded.demand_total,
},
)
try:
session.execute(stmt)
session.commit()
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
return {"num_records": 0}
finally:
session.close()
return {"num_records": len(records_to_store)}
