diff options
| -rw-r--r-- | scripts/common/__init__.py | 66 | ||||
| -rw-r--r-- | scripts/elvia.py | 16 | ||||
| -rw-r--r-- | scripts/elvia_gridtariff.py | 18 | ||||
| -rw-r--r-- | scripts/entsoe.py | 15 | ||||
| -rw-r--r-- | scripts/nb.py | 14 | ||||
| -rw-r--r-- | scripts/neohub.py | 26 | ||||
| -rw-r--r-- | scripts/tibber_consumption.py | 16 | ||||
| -rw-r--r-- | scripts/tibber_prices.py | 23 | ||||
| -rw-r--r-- | scripts/yr.py | 23 |
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) |