-
Notifications
You must be signed in to change notification settings - Fork 0
/
show_historical_data.py
98 lines (83 loc) · 4.54 KB
/
show_historical_data.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
# Raven imports energy usage, storage, and export data from Tesla Powerwall 2,
# stores it, and offers to display it in a variety of formats.
#
# Copyright (C) 2018 Eric H. Christensen, Jared Smith
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
#
#!/usr/bin/python3
import json
import requests
import pymysql.cursors
import time
# Open database connection
connection = pymysql.connect(host='localhost',user='sol',password='TheSun',db='Energy_Usage',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
# Compute amount of energy captured from solar panels.
sql = "SELECT min(solar_energy_exported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
start_solar_odometer = cursor.fetchone()
sql = "SELECT max(solar_energy_exported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
current_solar_odometer = cursor.fetchone()
print("Energy captured by the solar panels: ")
print((current_solar_odometer['max(solar_energy_exported)']) - (start_solar_odometer['min(solar_energy_exported)']))
# Compute amount of energy used from the grid.
sql = "SELECT min(grid_energy_imported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
start_solar_odometer = cursor.fetchone()
sql = "SELECT max(grid_energy_imported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
current_solar_odometer = cursor.fetchone()
print("Energy used from the grid: ")
print((current_solar_odometer['max(grid_energy_imported)']) - (start_solar_odometer['min(grid_energy_imported)']))
# Compute amount of energy sent to the grid.
sql = "SELECT min(grid_energy_exported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
start_solar_odometer = cursor.fetchone()
sql = "SELECT max(grid_energy_exported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
current_solar_odometer = cursor.fetchone()
print("Energy sent to the grid: ")
print((current_solar_odometer['max(grid_energy_exported)']) - (start_solar_odometer['min(grid_energy_exported)']))
# Compute amount of energy sent to Powerwall
sql = "SELECT min(battery_energy_imported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
start_solar_odometer = cursor.fetchone()
sql = "SELECT max(battery_energy_imported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
current_solar_odometer = cursor.fetchone()
print("Energy sent to Powerwall: ")
print((current_solar_odometer['max(battery_energy_imported)']) - (start_solar_odometer['min(battery_energy_imported)']))
# Compute amount of energy retrieved from Powerwall
sql = "SELECT min(battery_energy_exported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
start_solar_odometer = cursor.fetchone()
sql = "SELECT max(battery_energy_exported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
current_solar_odometer = cursor.fetchone()
print("Energy retrieved from Powerwall: ")
print((current_solar_odometer['max(battery_energy_exported)']) - (start_solar_odometer['min(battery_energy_exported)']))
# Compute amount of energy used by the house
sql = "SELECT min(load_energy_imported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
start_solar_odometer = cursor.fetchone()
sql = "SELECT max(load_energy_imported) FROM `Gateway_Data` WHERE DATE(`date_time`) = CURDATE()"
cursor.execute(sql)
current_solar_odometer = cursor.fetchone()
print("Energy used by the house: ")
print((current_solar_odometer['max(load_energy_imported)']) - (start_solar_odometer['min(load_energy_imported)']))
finally:
connection.close()