sql-variables in common was stupid. put them where they are used

9 files changed, 131 insertions, 86 deletions

diff --git a/scripts/common/__init__.py b/scripts/common/__init__.py
index 440b9a0..3c3ab1e 100644
--- a/scripts/common/__init__.py
+++ b/scripts/common/__init__.py
@@ -8,72 +8,6 @@ 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/elvia.py b/scripts/elvia.py
index 2248238..61bc692 100644
--- a/scripts/elvia.py
+++ b/scripts/elvia.py
@@ -47,8 +47,18 @@ values = []
 for item in data['meteringpoints'][0]['metervalue']['timeSeries']:
     # Only deal with verified items.
     if item['verified']:
-        values.append((data['meteringpoints'][0]['meteringPointId'], item['startTime'], item['endTime'], item['value'], item['uom'], item['production']))
+        values.append((
+            data['meteringpoints'][0]['meteringPointId'],
+            item['startTime'],
+            item['endTime'],
+            item['value'],
+            item['uom'],
+            item['production']))
 
 
-# Count new rows and insert
-common.dbi(common.sql[common.name], values, verbose=True)
+# SQL
+sql = """ INSERT INTO elvia
+          VALUES(%s, %s, %s, %s, %s, %s)
+          ON CONFLICT (startTime,endTime) DO NOTHING;"""
+
+common.dbi(sql, values, verbose=True)
diff --git a/scripts/elvia_gridtariff.py b/scripts/elvia_gridtariff.py
index 60692b4..2e398b3 100644
--- a/scripts/elvia_gridtariff.py
+++ b/scripts/elvia_gridtariff.py
@@ -59,6 +59,18 @@ print("Got "+ str(len(data['gridTariffCollections'][0]['gridTariff']['tariffPric
 
 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']))
-
-common.dbi(common.sql[common.name], values, verbose=True)
+    values.append((
+        meteringPointId,
+        item['startTime'],
+        item['expiredAt'],
+        item['shortName'],
+        item['isPublicHoliday'],
+        item['energyPrice']['total'],
+        item['energyPrice']['totalExVat']))
+
+# SQL
+sql = """ INSERT INTO elvia_gridtariff
+          VALUES(%s, %s, %s, %s, %s, %s, %s)
+          ON CONFLICT (meteringPointId,startTime,endTime) DO NOTHING"""
+
+common.dbi(sql, values, verbose=True)
diff --git a/scripts/entsoe.py b/scripts/entsoe.py
index 2a7d6f7..4a136af 100644
--- a/scripts/entsoe.py
+++ b/scripts/entsoe.py
@@ -80,7 +80,18 @@ for area in areas:
                 time = str(cettime + timedelta(hours = int(item["position"]) - 1))
 
                 # append values
-                values.append((time, area["name"], item["price.amount"]))
+                values.append((
+                    time,
+                    area["name"],
+                    item["price.amount"]))
+
     print("Got " + str(items) + " records")
 
-common.dbi(common.sql[jobname], values, verbose=True)
+# SQL
+sql = """ INSERT INTO entsoe
+          VALUES(%s, %s, %s)
+          ON CONFLICT (starttime, zone) DO NOTHING"""
+
+
+
+common.dbi(sql, values, verbose=True)
diff --git a/scripts/nb.py b/scripts/nb.py
index 793b8a5..88d40c5 100644
--- a/scripts/nb.py
+++ b/scripts/nb.py
@@ -51,7 +51,17 @@ with open(temp.name, encoding="utf-8") as csvfile:
     csvReader = csv.DictReader(csvfile, delimiter=';')
 
     for item in csvReader:
-        values.append((item["TIME_PERIOD"],item["BASE_CUR"],item["QUOTE_CUR"],item["OBS_VALUE"]))
+        values.append((
+            item["TIME_PERIOD"],
+            item["BASE_CUR"],
+            item["QUOTE_CUR"],
+            item["OBS_VALUE"]))
+
 temp.close()
 
-common.dbi(common.sql[common.name], values, verbose=True)
+# SQL
+sql = """ INSERT INTO nbex
+          VALUES(%s, %s, %s, %s)
+          ON CONFLICT (startdate,base_cur,quote_cur) DO NOTHING"""
+
+common.dbi(sql, values, verbose=True)
diff --git a/scripts/neohub.py b/scripts/neohub.py
index 2a61a21..9ee471e 100644
--- a/scripts/neohub.py
+++ b/scripts/neohub.py
@@ -21,7 +21,29 @@ async def run():
     hub_data, devices = await hub.get_live_data()
     for device in devices['thermostats']:
         print(f"Temperature in zone {device.name}: {device}")
-        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)
-        common.dbi(common.sql[common.name], values)
+        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)
+
+        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)"""
+
+        common.dbi(sql, values)
 
 asyncio.run(run())
diff --git a/scripts/tibber_consumption.py b/scripts/tibber_consumption.py
index ea38bcb..3df48fe 100644
--- a/scripts/tibber_consumption.py
+++ b/scripts/tibber_consumption.py
@@ -67,6 +67,18 @@ print("Got " + str(numdata) + " rows from Tibber")
 values = []
 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"]))
+        values.append((
+            item["from"],
+            item["to"],
+            item["consumption"],
+            item["consumptionUnit"],
+            item["cost"],
+            item["unitPrice"],
+            item["unitPriceVAT"]))
 
-common.dbi(common.sql[common.name], values, verbose=True)
+# SQL
+sql = """ INSERT INTO tibber_consumption
+          VALUES(%s, %s, %s, %s, %s, %s, %s)
+          ON CONFLICT (startTime,endTime) DO NOTHING"""
+
+common.dbi(sql, values, verbose=True)
diff --git a/scripts/tibber_prices.py b/scripts/tibber_prices.py
index 985a068..05db9a5 100644
--- a/scripts/tibber_prices.py
+++ b/scripts/tibber_prices.py
@@ -63,9 +63,24 @@ print("Got " + str(numdata) + " rows from Tibber")
 # prices
 prices = []
 for item in data["data"]["viewer"]["homes"][0]["currentSubscription"]["priceInfo"]["today"]:
-    prices.append((item["startsAt"],item["total"],item["energy"],item["tax"],item["level"]))
+    prices.append((
+        item["startsAt"],
+        item["total"],
+        item["energy"],
+        item["tax"],
+        item["level"]))
 
 for item in data["data"]["viewer"]["homes"][0]["currentSubscription"]["priceInfo"]["tomorrow"]:
-    prices.append((item["startsAt"],item["total"],item["energy"],item["tax"],item["level"]))
-
-common.dbi(common.sql[common.name], prices, verbose=True)
+    prices.append((
+        item["startsAt"],
+        item["total"],
+        item["energy"],
+        item["tax"],
+        item["level"]))
+
+# SQL
+sql = """ INSERT INTO tibber_prices
+          VALUES(%s, %s, %s, %s, %s)
+          ON CONFLICT (startsat) DO NOTHING""",
+
+common.dbi(sql, prices, verbose=True)
diff --git a/scripts/yr.py b/scripts/yr.py
index deb248b..f6b654c 100644
--- a/scripts/yr.py
+++ b/scripts/yr.py
@@ -40,6 +40,25 @@ data = response.json()
 values = []
 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"]))
+    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"]))
 
-common.dbi(common.sql[common.name], values, verbose=True)
+# SQL
+sql = """ 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()"""
+
+common.dbi(sql, values, verbose=True)