diff options
Diffstat (limited to 'tibber_prices2pgsql.py')
-rwxr-xr-x | tibber_prices2pgsql.py | 103 |
1 files changed, 103 insertions, 0 deletions
diff --git a/tibber_prices2pgsql.py b/tibber_prices2pgsql.py new file mode 100755 index 0000000..5c0af5c --- /dev/null +++ b/tibber_prices2pgsql.py @@ -0,0 +1,103 @@ +#!/bin/python3 + +import os +import sys +import json +import psycopg2 +import requests + +from datetime import datetime +from datetime import timedelta +from tzlocal import get_localzone + + +# variables +apiKey = os.environ['tibber_token'] +apiUrl = "https://api.tibber.com/v1-beta/gql" + +pg_db = os.environ['pg_db'] +pg_host = os.environ['pg_host'] +pg_table = "tibber_prices" + +startTime = datetime.now(get_localzone()) - timedelta(days = 1) +startTime = startTime.isoformat('T') + +endTime = datetime.now(get_localzone()).isoformat('T') + +# Get the data +try: + url = apiUrl + + # Request headers + hdr = { + 'Authorization': "Bearer " + apiKey, + 'Content-Type': 'application/json', + } + + body = {"query":"""{ + viewer { + homes { + currentSubscription{ + priceInfo{ + today { total energy tax startsAt level currency } + tomorrow { total energy tax startsAt level currency } + } + } + } + } }"""} + + response = requests.post(url, headers=hdr, json=body) + if response.status_code != 200: + print(response.status_code) + print("Oh shit") + response.raise_for_status() + +except Exception as e: + print("oh lol") + sys.exit(e) + +data = response.json() + +numdata = len(data["data"]["viewer"]["homes"][0]["currentSubscription"]["priceInfo"]["today"]) + len(data["data"]["viewer"]["homes"][0]["currentSubscription"]["priceInfo"]["tomorrow"]) + +print("Got " + str(numdata) + " rows from Tibber") + +### insert data into database + +# 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"])) + +for item in data["data"]["viewer"]["homes"][0]["currentSubscription"]["priceInfo"]["tomorrow"]: + prices.append((item["startsAt"],item["total"],item["energy"],item["tax"],item["level"])) + + +# connect to db +conn = psycopg2.connect(database=pg_db, host=pg_host) +cur = conn.cursor() + +# count rows before we start +cur.execute("SELECT COUNT(*) FROM " + pg_table) +before = cur.fetchone() + +# insert data +try: + cur.executemany("INSERT INTO " + pg_table + " VALUES(%s,%s,%s,%s,%s) ON CONFLICT (startsat) DO NOTHING", prices) + conn.commit() +except Exception as e: + conn.rollback() + raise e + +# count rows after we finnish +cur.execute("SELECT COUNT(*) FROM " + pg_table) +after = cur.fetchone() + +# count *new* rows +newRows = after[0] - before[0] + +# close connection +conn.close() + + +print("Successfully inserted " + str(newRows) + " records into the database") |