You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
110 lines
3.0 KiB
110 lines
3.0 KiB
|
|
import requests
|
|
import mysql.connector
|
|
from mysql.connector import Error
|
|
import datetime
|
|
import json
|
|
import os
|
|
from dotenv import load_dotenv
|
|
|
|
load_dotenv()
|
|
|
|
# Configuratievariabelen uit .env bestand
|
|
ENERVER_API_URL = os.getenv("ENERVER_API_URL_DAILY")
|
|
ENERVER_API_TOKEN = os.getenv("ENERVER_API_TOKEN")
|
|
|
|
# Verkrijg de Telegram configuraties uit het .env bestand
|
|
TELEGRAM_API_URL = (
|
|
f"https://api.telegram.org/bot{os.getenv('TELEGRAM_BOT_TOKEN')}/sendMessage"
|
|
)
|
|
TELEGRAM_CHAT_ID = os.getenv("TELEGRAM_CHAT_ID")
|
|
|
|
MYSQL_CONFIG = {
|
|
"host": os.getenv("MYSQL_HOST"),
|
|
"user": os.getenv("MYSQL_USER"),
|
|
"password": os.getenv("MYSQL_PASSWORD"),
|
|
"database": os.getenv("MYSQL_DATABASE"),
|
|
}
|
|
|
|
|
|
# SQL to create the table
|
|
CREATE_TABLE_SQL = """
|
|
CREATE TABLE IF NOT EXISTS energy_costs (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
kwh_usage FLOAT NOT NULL,
|
|
starttime DATETIME NOT NULL,
|
|
endtime DATETIME NOT NULL,
|
|
kwhprice FLOAT NOT NULL,
|
|
energy_hour DATETIME NOT NULL UNIQUE,
|
|
el_costs FLOAT NOT NULL
|
|
);
|
|
"""
|
|
|
|
# SQL to insert data
|
|
INSERT_DATA_SQL = """
|
|
INSERT INTO energy_costs (kwh_usage, starttime, endtime, kwhprice, energy_hour, el_costs)
|
|
VALUES (%s, %s, %s, %s, %s, %s)
|
|
ON DUPLICATE KEY UPDATE
|
|
kwh_usage = VALUES(kwh_usage),
|
|
starttime = VALUES(starttime),
|
|
endtime = VALUES(endtime),
|
|
kwhprice = VALUES(kwhprice),
|
|
el_costs = VALUES(el_costs);
|
|
"""
|
|
|
|
# SQL to fetch query results
|
|
SELECT_QUERY = """
|
|
SELECT Z.kwh as kwh_usage,
|
|
Z.starttime, Z.endtime,
|
|
dp.price_fr as kwhprice,
|
|
dp.timestamp as energy_hour,
|
|
(Z.kwh * dp.price_fr) as el_costs
|
|
FROM (
|
|
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d %H:00:00') AS hour_group,
|
|
MAX(net_value) - MIN(net_value) AS kwh,
|
|
FROM_UNIXTIME(MIN(timestamp)) as starttime,
|
|
FROM_UNIXTIME(MAX(timestamp)) as endtime,
|
|
MIN(net_value) as minnet_value,
|
|
MAX(net_value) as maxnet_value
|
|
FROM energy_usage
|
|
GROUP BY DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d %H:00:00')
|
|
) Z
|
|
INNER JOIN daillyprices dp
|
|
ON dp.timestamp = Z.hour_group;
|
|
"""
|
|
|
|
|
|
def main():
|
|
try:
|
|
# Connect to the database
|
|
connection = mysql.connector.connect(**MYSQL_CONFIG)
|
|
if connection.is_connected():
|
|
cursor = connection.cursor()
|
|
|
|
# Create the table if it doesn't exist
|
|
cursor.execute(CREATE_TABLE_SQL)
|
|
connection.commit()
|
|
|
|
# Fetch the query results
|
|
cursor.execute(SELECT_QUERY)
|
|
results = cursor.fetchall()
|
|
|
|
# Insert data into the energy_costs table
|
|
for row in results:
|
|
cursor.execute(INSERT_DATA_SQL, row)
|
|
|
|
connection.commit()
|
|
print("calculate_energycosts: Data inserted successfully.")
|
|
|
|
except Error as e:
|
|
print(f"Error: {e}")
|
|
finally:
|
|
if connection.is_connected():
|
|
cursor.close()
|
|
connection.close()
|
|
print("MySQL connection is closed.")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|