Sep-09-2018, 09:33 PM
You don't learn if you don't accept criticism. So i own Tesla Solar, and Tesla Power walls, i have the API's to pull data from the power walls direct. So i wrote this python code to pull data from the power walls and insert the data into 4 tables in a mysql database. So i can start running trending. I would ultimately like to run some Machine learning, on power usage to determine which appliances should be run at which time based on power consumption.
So anyway here's the code. Any pointers to clean it up? I load the data into Json, it has 4 tables. Site,Solar,Load,Battery. I'll also include a single entry. I'm just trying to learn, so flame away :)
I wrote this all myself, just doing a crap load of google searches on getting what i want.
Doing the whole Timer was a problem since i wanted the code only to run every 30 seconds at the start, then decided to change it to 15 seconds since it wasnt updating fast enough.
So i put in the while current_seconds <> 30 because when i tried to do <> 00, 15, 30, 45. It just wouldnt run, I also couldn't figure out how to kill a thread if i created a threat to do the if statement to start the "DatePull"
I also had a huge hard time with the json data, it actually contains the table names in the json... which i couldn't figure out how to use so i had to create new lists to pull in the data to the write insert statement...
I also receive an error because i'm bypassing the SSL Cert with =false
C:\anaconda\lib\site-packages\urllib3\connectionpool.py:858: InsecureRequestWarning: Unverified HTTPS request is being made. Adding certificate verification is strongly advised. See: https://urllib3.readthedocs.io/en/latest...l-warnings
InsecureRequestWarning)
{"site": {"last_communication_time": "2018-08-14T07:20:00.683948156-07:00", "instant_power": 259.2799987792969, "instant_reactive_power": -1114.8699951171875, "instant_apparent_power": 1144.6227429942103, "frequency": 60, "energy_exported": 6967.178623577551, "energy_imported": 6018647.222790244, "instant_average_voltage": 242.20000457763672, "instant_total_current": 0, "i_a_current": 0, "i_b_current": 0, "i_c_current": 0}, "battery": {"last_communication_time": "2018-08-14T07:20:00.68998616-07:00", "instant_power": 0, "instant_reactive_power": 800, "instant_apparent_power": 800, "frequency": 60.001000000000005, "energy_exported": 535040, "energy_imported": 618400, "instant_average_voltage": 241.95000000000002, "instant_total_current": -0.4, "i_a_current": 0, "i_b_current": 0, "i_c_current": 0}, "load": {"last_communication_time": "2018-08-14T07:20:00.683732156-07:00", "instant_power": 1666.540970676703, "instant_reactive_power": 64.64843532225848, "instant_apparent_power": 1667.7944199252088, "frequency": 60, "energy_exported": 0, "energy_imported": 9079905.77222222, "instant_average_voltage": 242.20000457763672, "instant_total_current": 6.880846156807139, "i_a_current": 0, "i_b_current": 0, "i_c_current": 0}, "solar": {"last_communication_time": "2018-08-14T07:20:00.683732156-07:00", "instant_power": 1399.9700317382812, "instant_reactive_power": 380.8199920654297, "instant_apparent_power": 1450.8411202202665, "frequency": 60, "energy_exported": 3151754.1563891685, "energy_imported": 168.42833361283363, "instant_average_voltage": 242.11000061035156, "instant_total_current": 0, "i_a_current": 0, "i_b_current": 0, "i_c_current": 0}}
Data Transformed
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
So anyway here's the code. Any pointers to clean it up? I load the data into Json, it has 4 tables. Site,Solar,Load,Battery. I'll also include a single entry. I'm just trying to learn, so flame away :)
I wrote this all myself, just doing a crap load of google searches on getting what i want.
Doing the whole Timer was a problem since i wanted the code only to run every 30 seconds at the start, then decided to change it to 15 seconds since it wasnt updating fast enough.
So i put in the while current_seconds <> 30 because when i tried to do <> 00, 15, 30, 45. It just wouldnt run, I also couldn't figure out how to kill a thread if i created a threat to do the if statement to start the "DatePull"
I also had a huge hard time with the json data, it actually contains the table names in the json... which i couldn't figure out how to use so i had to create new lists to pull in the data to the write insert statement...
I also receive an error because i'm bypassing the SSL Cert with =false
C:\anaconda\lib\site-packages\urllib3\connectionpool.py:858: InsecureRequestWarning: Unverified HTTPS request is being made. Adding certificate verification is strongly advised. See: https://urllib3.readthedocs.io/en/latest...l-warnings
InsecureRequestWarning)
import json import pymysql import requests import os import ssl import csv import sys import pprint import urllib.request ssl._create_default_https_context = ssl._create_unverified_context from urllib.request import urlopen import pymysql.cursors import datetime import threading url = 'https://192.168.0.12/api/meters/aggregates' value = "(`last_communication_time`, `instant_power`, `instant_reactive_power`, `instant_apparent_power`, `frequency`, `energy_exported`, `energy_imported`, `instant_average_voltage`, `instant_total_current`, `i_a_current`, `i_b_current`, `i_c_current`)" def Datapull(): #set thread timer threading.Timer(15.0, Datapull).start() #get the data, then delete the crap we don't want. response = requests.get(url, verify=False) data = response.json() del data['generator'] del data['frequency'] del data['busway'] #building databases/table for insert tables = list(data.keys()) columns = list(data[tables[0]].keys()) # Converting date to something manageable. for y in range (0, 4): end_date = data[tables[y]][columns[0]] end_date = end_date.split(" ") end_date[-1] = end_date[-1][:26] end_date = " ".join(end_date) x = datetime.datetime.strptime(end_date, "%Y-%m-%dT%H:%M:%S.%f") x = x.strftime('%Y/%m/%d %H:%M:%S') data[tables[y]][columns[0]] = x siteentrylist = [] for x in range(0, 1): for y in range (0, 12): siteentrylist.append(data[tables[x]][columns[y]]) batteryentrylist = [] for x in range(1, 2): for y in range (0, 12): batteryentrylist.append(data[tables[x]][columns[y]]) loadentrylist = [] for x in range(2, 3): for y in range (0, 12): loadentrylist.append(data[tables[x]][columns[y]]) solarentrylist = [] for x in range(3, 4): for y in range (0, 12): solarentrylist.append(data[tables[x]][columns[y]]) #site sitestring = str("""INSERT INTO `tesla`.`site`%s VALUES (%s)""" % (value, siteentrylist )) sitestring = sitestring.replace("[", "") sitestring = sitestring.replace("]", "") batterystring = str("""INSERT INTO `tesla`.`battery`%s VALUES (%s)""" % (value, batteryentrylist )) batterystring = batterystring.replace("[", "") batterystring = batterystring.replace("]", "") loadstring = str("""INSERT INTO `tesla`.`load`%s VALUES (%s)""" % (value, loadentrylist )) loadstring = loadstring.replace("[", "") loadstring = loadstring.replace("]", "") solarstring = str("""INSERT INTO `tesla`.`solar`%s VALUES (%s)""" % (value, solarentrylist )) solarstring = solarstring.replace("[", "") solarstring = solarstring.replace("]", "") conn = pymysql.connect(host='localhost', user='root', passwd='somepassword', db='tesla') cur = conn.cursor() cur.execute(sitestring) cur.execute(batterystring) cur.execute(loadstring) cur.execute(solarstring) conn.commit() cur.close() conn.close() curr_time = datetime.datetime.now() formatted_time = curr_time.strftime('%S') while formatted_time != '30': curr_time = datetime.datetime.now() formatted_time = curr_time.strftime('%S') print(formatted_time) Datapull()Data set raw.
{"site": {"last_communication_time": "2018-08-14T07:20:00.683948156-07:00", "instant_power": 259.2799987792969, "instant_reactive_power": -1114.8699951171875, "instant_apparent_power": 1144.6227429942103, "frequency": 60, "energy_exported": 6967.178623577551, "energy_imported": 6018647.222790244, "instant_average_voltage": 242.20000457763672, "instant_total_current": 0, "i_a_current": 0, "i_b_current": 0, "i_c_current": 0}, "battery": {"last_communication_time": "2018-08-14T07:20:00.68998616-07:00", "instant_power": 0, "instant_reactive_power": 800, "instant_apparent_power": 800, "frequency": 60.001000000000005, "energy_exported": 535040, "energy_imported": 618400, "instant_average_voltage": 241.95000000000002, "instant_total_current": -0.4, "i_a_current": 0, "i_b_current": 0, "i_c_current": 0}, "load": {"last_communication_time": "2018-08-14T07:20:00.683732156-07:00", "instant_power": 1666.540970676703, "instant_reactive_power": 64.64843532225848, "instant_apparent_power": 1667.7944199252088, "frequency": 60, "energy_exported": 0, "energy_imported": 9079905.77222222, "instant_average_voltage": 242.20000457763672, "instant_total_current": 6.880846156807139, "i_a_current": 0, "i_b_current": 0, "i_c_current": 0}, "solar": {"last_communication_time": "2018-08-14T07:20:00.683732156-07:00", "instant_power": 1399.9700317382812, "instant_reactive_power": 380.8199920654297, "instant_apparent_power": 1450.8411202202665, "frequency": 60, "energy_exported": 3151754.1563891685, "energy_imported": 168.42833361283363, "instant_average_voltage": 242.11000061035156, "instant_total_current": 0, "i_a_current": 0, "i_b_current": 0, "i_c_current": 0}}
Data Transformed
INSERT INTO
tesla
.site
(last_communication_time
, instant_power
, instant_reactive_power
, instant_apparent_power
, frequency
, energy_exported
, energy_imported
, instant_average_voltage
, instant_total_current
, i_a_current
, i_b_current
, i_c_current
) VALUES ('2018/09/09 11:21:32', 9412.63037109375, 2470.7899780273438, 9731.51651174455, 60, 8819.507802232169, 8377793.437524456, 236.95000457763672, 0, 0, 0, 0)INSERT INTO
tesla
.battery
(last_communication_time
, instant_power
, instant_reactive_power
, instant_apparent_power
, frequency
, energy_exported
, energy_imported
, instant_average_voltage
, instant_total_current
, i_a_current
, i_b_current
, i_c_current
) VALUES ('2018/09/09 11:21:32', 0, 760, 760, 59.982, 726530, 838790, 236.75, -0.5, 0, 0, 0)INSERT INTO
tesla
.load
(last_communication_time
, instant_power
, instant_reactive_power
, instant_apparent_power
, frequency
, energy_exported
, energy_imported
, instant_average_voltage
, instant_total_current
, i_a_current
, i_b_current
, i_c_current
) VALUES ('2018/09/09 11:21:32', 15136.526204172365, 3525.6245632680884, 15541.70048903002, 60, 0, 12623076.386944445, 236.95000457763672, 63.880674875500496, 0, 0, 0)INSERT INTO
tesla
.solar
(last_communication_time
, instant_power
, instant_reactive_power
, instant_apparent_power
, frequency
, energy_exported
, energy_imported
, instant_average_voltage
, instant_total_current
, i_a_current
, i_b_current
, i_c_current
) VALUES ('2018/09/09 11:21:32', 5737.10009765625, 274.9700012207031, 5743.685753251014, 60, 4366534.296111499, 171.83888927744113, 236.8499984741211, 0, 0, 0, 0)