diff options
| -rw-r--r-- | scripts/common/__init__.py | 69 | ||||
| -rw-r--r-- | scripts/common/postgres.py | 2 | ||||
| -rw-r--r-- | scripts/elvia.py | 6 | ||||
| -rw-r--r-- | scripts/elvia_gridtariff.py | 9 | ||||
| -rw-r--r-- | scripts/entsoe.py | 10 | ||||
| -rw-r--r-- | scripts/mqtt_listener.py | 5 | ||||
| -rw-r--r-- | scripts/mqtt_watch.py | 8 | ||||
| -rw-r--r-- | scripts/nb.py | 5 | ||||
| -rw-r--r-- | scripts/neohub.py | 5 | ||||
| -rw-r--r-- | scripts/tibber_consumption.py | 8 | ||||
| -rw-r--r-- | scripts/tibber_prices.py | 9 | ||||
| -rw-r--r-- | scripts/yr.py | 17 |
12 files changed, 92 insertions, 61 deletions
diff --git a/scripts/common/__init__.py b/scripts/common/__init__.py index f6c02d3..440b9a0 100644 --- a/scripts/common/__init__.py +++ b/scripts/common/__init__.py @@ -2,9 +2,78 @@ ''' common functions and stuff ''' import os +import sys QUEUE = bool(os.environ.get('el_QUEUE', False)) +name = os.path.splitext(os.path.split(sys.argv[0])[-1])[0] + +sql = { + 'elvia': """ + INSERT INTO + elvia + VALUES(%s, %s, %s, %s, %s, %s) + ON CONFLICT (startTime,endTime) DO NOTHING""", + + 'elvia_gridtariff': """ + INSERT INTO + elvia_gridtariff + VALUES(%s, %s, %s, %s, %s, %s, %s) + ON CONFLICT (meteringPointId,startTime,endTime) DO NOTHING""", + + 'entsoe': """ + INSERT INTO + entsoe + VALUES(%s, %s, %s) + ON CONFLICT (starttime, zone) DO NOTHING""", + + 'nb': """ + INSERT INTO + nbex + VALUES(%s, %s, %s, %s) + ON CONFLICT (startdate,base_cur,quote_cur) DO NOTHING""", + + 'neohub': """ + INSERT INTO + neohub + (timestamp, + time, + device_id, + away, + heat_mode, + heat_on, + current_floor_temperature, + target_temperature, + temperature) + VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)""", + + 'tibber_consumption': """ + INSERT INTO + tibber_consumption + VALUES(%s, %s, %s, %s, %s, %s, %s) + ON CONFLICT (startTime,endTime) DO NOTHING""", + + + 'tibber_prices': """ + INSERT INTO + tibber_prices + VALUES(%s, %s, %s, %s, %s) + ON CONFLICT (startsat) DO NOTHING""", + + 'yr': """ + INSERT INTO + yr + VALUES(%s, %s, %s, %s, %s, %s, %s) + ON CONFLICT (time) DO UPDATE SET + air_temperature=EXCLUDED.air_temperature, + air_pressure_at_sea_level=EXCLUDED.air_pressure_at_sea_level, + cloud_area_fraction=EXCLUDED.cloud_area_fraction, + relative_humidity=EXCLUDED.relative_humidity, + wind_from_direction=EXCLUDED.wind_from_direction, + wind_speed=EXCLUDED.wind_speed, + updated=now()""", +} + # Initialize queue if QUEUE is True: from .queue import dbi diff --git a/scripts/common/postgres.py b/scripts/common/postgres.py index bff0720..bff4a84 100644 --- a/scripts/common/postgres.py +++ b/scripts/common/postgres.py @@ -24,5 +24,5 @@ def dbi(sql, values, **kwargs): print('`values` is a', type(values), 'but it needs to be tuple or list') sys.exit(1) if verbose is True: - print("Inserted and/or changed", cur.rowcount, "rows into db") + print("Inserted and/or changed", cur.rowcount, "rows in db") return True diff --git a/scripts/elvia.py b/scripts/elvia.py index 6e4bc9f..2248238 100644 --- a/scripts/elvia.py +++ b/scripts/elvia.py @@ -8,13 +8,11 @@ from datetime import timedelta from tzlocal import get_localzone import requests -from common import dbi +import common apiKey = os.environ['el_elvia_token'] apiUrl = "https://elvia.azure-api.net/customer/metervalues/api/v1/metervalues" -pg_table = "elvia" - startTime = datetime.now(get_localzone()) - timedelta(days = 2) startTime = startTime.isoformat('T') endTime = datetime.now(get_localzone()).isoformat('T') @@ -53,4 +51,4 @@ for item in data['meteringpoints'][0]['metervalue']['timeSeries']: # Count new rows and insert -dbi("INSERT INTO " + pg_table + " VALUES(%s,%s,%s,%s,%s,%s) ON CONFLICT (startTime,endTime) DO NOTHING", values, verbose=True) +common.dbi(common.sql[common.name], values, verbose=True) diff --git a/scripts/elvia_gridtariff.py b/scripts/elvia_gridtariff.py index 28810b2..60692b4 100644 --- a/scripts/elvia_gridtariff.py +++ b/scripts/elvia_gridtariff.py @@ -8,7 +8,7 @@ from datetime import timedelta from tzlocal import get_localzone import requests -from common import dbi +import common # API documentation: https://elvia.portal.azure-api.net/docs/services/gridtariffapi/operations/post-digin-api-v-tariffquery-meteringpointsgridtariffs? @@ -16,11 +16,6 @@ apiKey = os.environ['el_elvia_grid_api_key'] apiUrl = "https://elvia.azure-api.net/grid-tariff/digin/api/1/tariffquery/meteringpointsgridtariffs" meteringPointId = os.environ['el_meteringPointId'] -pg_db = os.environ['el_pg_db'] -pg_host = os.environ['el_pg_host'] -pg_table = "elvia_gridtariff" - - startTime = datetime.now(get_localzone()) - timedelta(days = 2) startTime = startTime.strftime('%Y-%m-%d') @@ -66,4 +61,4 @@ values = [] for item in data['gridTariffCollections'][0]['gridTariff']['tariffPrice']['hours']: values.append((meteringPointId, item['startTime'], item['expiredAt'], item['shortName'], item['isPublicHoliday'], item['energyPrice']['total'], item['energyPrice']['totalExVat'])) -dbi("INSERT INTO " + pg_table + " VALUES(%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (meteringPointId,startTime,endTime) DO NOTHING", values, verbose=True) +common.dbi(common.sql[common.name], values, verbose=True) diff --git a/scripts/entsoe.py b/scripts/entsoe.py index 85785dc..2a7d6f7 100644 --- a/scripts/entsoe.py +++ b/scripts/entsoe.py @@ -10,7 +10,7 @@ import xmltodict from tzlocal import get_localzone from dateutil import tz -from common import dbi +import common # variables @@ -29,16 +29,14 @@ apiKey = os.environ['el_entsoe_token'] # https://transparency.entsoe.eu/content/static_content/Static%20content/web%20api/Guide.html apiUrl = "https://transparency.entsoe.eu/api?securityToken=" + apiKey -pg_db = os.environ['el_pg_db'] -pg_host = os.environ['el_pg_host'] -pg_table = "entsoe" - startTime = datetime.now(get_localzone()) - timedelta(days = 7) startTime = startTime.strftime('%Y%m%d') endTime = datetime.now(get_localzone()) + timedelta(days = 1) endTime = endTime.strftime('%Y%m%d') +jobname = os.path.splitext(os.path.basename(__file__))[0] + # https://transparency.entsoe.eu/content/static_content/Static%20content/web%20api/Guide.html#_areas areas = [ {"name": "NO-0", "code": "10YNO-0--------C"}, {"name": "NO-1", "code": "10YNO-1--------2"}, @@ -85,4 +83,4 @@ for area in areas: values.append((time, area["name"], item["price.amount"])) print("Got " + str(items) + " records") -dbi("INSERT INTO " + pg_table + " VALUES(%s,%s,%s) ON CONFLICT (starttime, zone) DO NOTHING", values, verbose=True) +common.dbi(common.sql[jobname], values, verbose=True) diff --git a/scripts/mqtt_listener.py b/scripts/mqtt_listener.py index 6e9a307..85d27a1 100644 --- a/scripts/mqtt_listener.py +++ b/scripts/mqtt_listener.py @@ -6,7 +6,7 @@ import json from datetime import datetime import paho.mqtt.client as mqtt -from common import dbi +import common mqtt_server = os.environ['el_mqtt_server'] mqtt_port = int(os.environ['el_mqtt_port']) @@ -37,8 +37,7 @@ def on_message(client, userdata, msg): else: return - dbi(sql, values, verbose=True) - + common.dbi(sql, values, verbose=True) # mqtt diff --git a/scripts/mqtt_watch.py b/scripts/mqtt_watch.py index e460f13..775b84a 100644 --- a/scripts/mqtt_watch.py +++ b/scripts/mqtt_watch.py @@ -2,11 +2,9 @@ ''' Listen for mqtt-events, and trigger for some ''' import os -import json -from datetime import datetime import paho.mqtt.client as mqtt -from common import dbi +import common mqtt_server = os.environ['el_mqtt_server'] mqtt_port = int(os.environ['el_mqtt_port']) @@ -23,8 +21,6 @@ def on_connect(client, userdata, flags, rc): # The callback for when a PUBLISH message is received from the server. def on_message(client, userdata, msg): - name = msg.topic.split('/')[1] - #data = json.loads(msg.payload) print(msg.topic, msg.payload) @@ -39,7 +35,7 @@ def on_message(client, userdata, msg): #else: # return - #dbi(sql, values, verbose=True) + #common.dbi(sql, values, verbose=True) diff --git a/scripts/nb.py b/scripts/nb.py index 7cb753f..793b8a5 100644 --- a/scripts/nb.py +++ b/scripts/nb.py @@ -10,7 +10,7 @@ from datetime import timedelta from tzlocal import get_localzone import requests -from common import dbi +import common # I'm not sure I understand Norges Banks json-model. It seems a lot easier to just get the CSV, and convert it to JSON. apiUrl = "https://data.norges-bank.no/api/data/EXR/B.EUR.NOK.SP?format=csv&locale=en" @@ -54,5 +54,4 @@ with open(temp.name, encoding="utf-8") as csvfile: values.append((item["TIME_PERIOD"],item["BASE_CUR"],item["QUOTE_CUR"],item["OBS_VALUE"])) temp.close() - -dbi("INSERT INTO " + pg_table + " VALUES(%s,%s,%s,%s) ON CONFLICT (startdate,base_cur,quote_cur) DO NOTHING", values, verbose=True) +common.dbi(common.sql[common.name], values, verbose=True) diff --git a/scripts/neohub.py b/scripts/neohub.py index 7cc1160..2a61a21 100644 --- a/scripts/neohub.py +++ b/scripts/neohub.py @@ -6,7 +6,7 @@ import asyncio from datetime import datetime import neohubapi.neohub as neohub -from common import dbi +import common neohub_ip = os.environ['el_neohub_ip'] neohub_port = os.environ['el_neohub_port'] @@ -21,8 +21,7 @@ async def run(): hub_data, devices = await hub.get_live_data() for device in devices['thermostats']: print(f"Temperature in zone {device.name}: {device}") - sql = "INSERT INTO neohub (timestamp, time, device_id, away, heat_mode, heat_on, current_floor_temperature, target_temperature, temperature) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)" values = (datetime.utcnow(), device.time, device.device_id, device.away, device.heat_mode, device.heat_on, device.current_floor_temperature, device.target_temperature, device.temperature) - dbi(sql, values) + common.dbi(common.sql[common.name], values) asyncio.run(run()) diff --git a/scripts/tibber_consumption.py b/scripts/tibber_consumption.py index fa5169e..ea38bcb 100644 --- a/scripts/tibber_consumption.py +++ b/scripts/tibber_consumption.py @@ -8,17 +8,13 @@ from datetime import timedelta from tzlocal import get_localzone import requests -from common import dbi +import common # variables apiKey = os.environ['el_tibber_token'] apiUrl = "https://api.tibber.com/v1-beta/gql" -pg_db = os.environ['el_pg_db'] -pg_host = os.environ['el_pg_host'] -pg_table = "tibber_consumption" - startTime = datetime.now(get_localzone()) - timedelta(days = 1) startTime = startTime.isoformat('T') @@ -73,4 +69,4 @@ for item in data["data"]["viewer"]["homes"][0]["consumption"]["nodes"]: if item["consumption"] is not None: values.append((item["from"],item["to"],item["consumption"],item["consumptionUnit"],item["cost"],item["unitPrice"],item["unitPriceVAT"])) -dbi("INSERT INTO " + pg_table + " VALUES(%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (startTime,endTime) DO NOTHING", values, verbose=True) +common.dbi(common.sql[common.name], values, verbose=True) diff --git a/scripts/tibber_prices.py b/scripts/tibber_prices.py index a2e9dda..985a068 100644 --- a/scripts/tibber_prices.py +++ b/scripts/tibber_prices.py @@ -8,17 +8,13 @@ from datetime import timedelta from tzlocal import get_localzone import requests -from common import dbi +import common # variables apiKey = os.environ['el_tibber_token'] apiUrl = "https://api.tibber.com/v1-beta/gql" -pg_db = os.environ['el_pg_db'] -pg_host = os.environ['el_pg_host'] -pg_table = "tibber_prices" - startTime = datetime.now(get_localzone()) - timedelta(days = 1) startTime = startTime.isoformat('T') @@ -72,5 +68,4 @@ for item in data["data"]["viewer"]["homes"][0]["currentSubscription"]["priceInfo for item in data["data"]["viewer"]["homes"][0]["currentSubscription"]["priceInfo"]["tomorrow"]: prices.append((item["startsAt"],item["total"],item["energy"],item["tax"],item["level"])) - -dbi("INSERT INTO " + pg_table + " VALUES(%s,%s,%s,%s,%s) ON CONFLICT (startsat) DO NOTHING", prices, verbose=True) +common.dbi(common.sql[common.name], prices, verbose=True) diff --git a/scripts/yr.py b/scripts/yr.py index 3a6c8a7..deb248b 100644 --- a/scripts/yr.py +++ b/scripts/yr.py @@ -5,15 +5,11 @@ import os import sys import requests -from common import dbi +import common lat = str(os.environ['el_yr_lat']) lon = str(os.environ['el_yr_lon']) -pg_db = os.environ['el_pg_db'] -pg_host = os.environ['el_pg_host'] -pg_table = "yr" - apiUrl = "https://api.met.no/weatherapi/locationforecast/2.0/compact?lat=" + lat + "&lon=" + lon ### Get the data @@ -46,13 +42,4 @@ for item in data["properties"]["timeseries"]: details = item["data"]["instant"]["details"] values.append((item["time"],details["air_temperature"],details["air_pressure_at_sea_level"],details["cloud_area_fraction"],details["relative_humidity"],details["wind_from_direction"],details["wind_speed"])) -sql = "INSERT INTO " + pg_table + """ VALUES(%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (time) DO UPDATE SET - air_temperature=EXCLUDED.air_temperature, - air_pressure_at_sea_level=EXCLUDED.air_pressure_at_sea_level, - cloud_area_fraction=EXCLUDED.cloud_area_fraction, - relative_humidity=EXCLUDED.relative_humidity, - wind_from_direction=EXCLUDED.wind_from_direction, - wind_speed=EXCLUDED.wind_speed, - updated=now()""" - -dbi(sql, values, verbose=True) +common.dbi(common.sql[common.name], values, verbose=True) |