def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
# connectable = engine_from_config(
# config.get_section(config.config_ini_section),
# prefix="sqlalchemy.",
# # poolclass=pool.Pool(),
# )
engine = db_connect(timeout=600)
with engine.connect() as connection:
context.configure(
include_object=include_object,
connection=connection,
target_metadata=target_metadata,
)
try:
with context.begin_transaction():
context.run_migrations()
finally:
connection.close()
def load_facility_fueltech_map():
"""
Only do this for DUIDs that don't have a fueltech
"""
engine = db_connect()
session = sessionmaker(bind=engine)
s = session()
facility_fueltech_map = load_data_json("facility_fueltech_map.json")
update_count = 0
for facility_duid, facility_fueltech in facility_fueltech_map.items():
facilities = (s.query(Facility).filter(
Facility.network_code == facility_duid).all())
for f in facilities:
if f.fueltech_id:
logger.debug(
"Skipping {} because it already has a fueltech of {}".
format(f.code, f.fueltech_id))
continue
f.fueltech_id = facility_fueltech
s.add(f)
update_count += 1
s.commit()
logger.info("Updated {} fueltechs for facilities".format(update_count))
def opennem_geocode(limit=None):
engine = db_connect()
session = sessionmaker(bind=engine)
s = session()
records = s.query(Station).filter(Station.geom == None).filter(Station.geocode_skip == False)
count = 0
skipped = 0
records_added = 0
for r in records:
geo_address_string = build_address_string(r)
logger.info("Geocoding record: {}".format(geo_address_string))
continue
google_result = place_search(geo_address_string)
pprint(google_result)
if google_result and type(google_result) is list and len(google_result) > 0:
result = google_result[0]
r.place_id = result["place_id"]
lat = result["geometry"]["location"]["lat"]
lng = result["geometry"]["location"]["lng"]
r.geom = "SRID=4326;POINT({} {})".format(lng, lat)
r.geocode_processed_at = datetime.now()
r.geocode_by = "google"
r.geocode_approved = False
try:
s.add(r)
s.commit()
records_added += 1
except IntegrityError as e:
logger.error(e)
skipped += 1
pass
except Exception as e:
skipped += 1
logger.error("Error: {}".format(e))
else:
skipped += 1
count += 1
if limit and count >= limit:
break
print("Geocode of opennem records done. Added {} records. Couldn't match {}".format(records_added, skipped))
def get_mysql_engine() -> Engine:
"""Get a database connection to the mysql database, requires mysqlclient
$ pip install mysqlclient
and a connection string with the driver set as:
```mysql+pymysql://```
"""
dbconnstr = os.environ.get("NEMWEB_MIGRATE_DB")
if not dbconnstr:
raise Exception(
"Require a db connection string set at NEMWEB_MIGRATE_DB")
engine = db_connect(db_conn_str=dbconnstr)
return engine
def __init__(self):
engine = db_connect()
self.session = sessionmaker(bind=engine)
import decimal
import json
from datetime import datetime
from sqlalchemy.orm import sessionmaker
from opennem.db import db_connect
from opennem.db.models.opennem import FuelTech
from opennem.db.models.wem import WemFacility, metadata
engine = db_connect()
session = sessionmaker(bind=engine)
PERIOD_MAP = {
"all": 0,
# current year
"year": "date_trunc('year', current_date))",
# current month
"month": "date_trunc('month', current_date))",
}
def bom_observation(station_id, region="wa"):
__query = """
SELECT
observation_time,
temp_air as value
FROM bom_observation
WHERE
station_id = '{station_id}'
AND observation_time > now() - interval '7 days'
from opennem.core.compat.energy import trading_energy_data
from opennem.core.compat.loader import load_statset_v2
from opennem.core.compat.schema import OpennemDataSetV2
from opennem.core.energy import _energy_aggregate, energy_sum
from opennem.core.parsers.aemo import parse_aemo_urls
from opennem.db import db_connect, get_database_engine
from opennem.pipelines.files import _fallback_download_handler
from opennem.schema.network import NetworkNEM
from opennem.settings import settings
from opennem.utils.http import http
from opennem.utils.numbers import sigfig_compact
from opennem.utils.series import series_are_equal, series_joined
from opennem.workers.energy import get_generated, get_generated_query
engine = db_connect(settings.db_url)
engine_local = db_connect(
"postgresql://*****:*****@127.0.0.1:15433/opennem")
def run() -> None:
aemo = parse_aemo_urls([
"http://nemweb.com.au/Reports/Current/Next_Day_Dispatch/PUBLIC_NEXT_DAY_DISPATCH_20210304_0000000337689171.zip",
"http://nemweb.com.au/Reports/Current/Next_Day_Dispatch/PUBLIC_NEXT_DAY_DISPATCH_20210305_0000000337748278.zip",
"http://nemweb.com.au/Reports/Current/Next_Day_Dispatch/PUBLIC_NEXT_DAY_DISPATCH_20210306_0000000337800035.zip",
])
meter_data = aemo.get_table("METER_DATA_GEN_DUID")
if meter_data:
df = pd.DataFrame(meter_data.records)
def load_opennem_facilities():
station_fixture = load_data("facility_registry.json", from_fixture=True)
stations = [{"station_code": k, **v} for k, v in station_fixture.items()]
engine = db_connect()
session = sessionmaker(bind=engine)
s = session()
for station_data in stations:
station = None
facilities = [{
"code": k,
**v
} for k, v in station_data["duid_data"].items()]
# update facilities
for facility_data in facilities:
facility_duid = facility_data["code"]
station_name = station_name_cleaner(station_data["display_name"])
# station_code = normalize_duid(station_data["station_code"])
station_code = facility_map_station(
facility_duid, normalize_duid(station_data["station_code"]))
station_state = map_compat_facility_state(
station_data["status"]["state"])
station_network = "WEM" if station_data["location"][
"state"] == "WA" else "NEM"
station = s.query(Station).filter(
Station.network_code == station_code).one_or_none()
if not station:
station = Station(
network_id=station_network,
code=station_code,
network_code=station_code,
name=station_name,
network_name=station_data["display_name"],
created_by="opennem.load_facilities",
)
logger.info("Created station: {} {} ".format(
station_name, station_code))
s.add(station)
s.commit()
facility_status = station_state
# Network region
facility_network_region = map_compat_network_region(
station_data["region_id"])
# Fueltech
facility_fueltech = None
if "fuel_tech" in facility_data and facility_data["fuel_tech"]:
facility_fueltech = map_compat_fueltech(
facility_data["fuel_tech"])
# Capacity
facility_capacity = None
if "registered_capacity" in facility_data and facility_data[
"registered_capacity"]:
facility_capacity = clean_capacity(
facility_data["registered_capacity"])
facility = None
try:
facility = (s.query(Facility).filter(
Facility.network_code == facility_duid).one_or_none())
except MultipleResultsFound:
logger.error("Multiple facilities found for duid {}".format(
facility_duid))
# facility = (
# s.query(Facility)
# .filter(Facility.network_code == facility_duid)
# .first()
# )
continue
if not facility:
facility = Facility(
code=facility_duid,
network_code=facility_duid,
network_region=facility_network_region,
created_by="opennem.load_facilities",
)
logger.info("Created facility: {} {} to station {} ".format(
facility_duid, facility_fueltech, station_code))
if not facility.unit_id:
facility.unit_id = 1
facility.unit_number = 1
if facility_capacity and not facility.unit_capacity:
facility.unit_capacity = facility_capacity
if facility_capacity and not facility.capacity_registered:
facility.capacity_registered = facility_capacity
if facility_fueltech and not facility.fueltech_id:
facility.fueltech_id = facility_fueltech
if not facility.status_id:
facility.status_id = facility_status
if not facility.station:
facility.station = station
s.add(facility)
s.commit()
