moving some variables around

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)