-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdb_update_currentcost6.py
193 lines (175 loc) · 6.22 KB
/
db_update_currentcost6.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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
#!/usr/bin/python
#updates database trend table with data from EnviR CurrentCost meter
#Greg Fiske Feb 2013
import serial,sys,MySQLdb
import xml.etree.ElementTree as ET # for XML parsing
import json
import urllib2
import time
import gspread
import base64
import ConfigParser
import time
###############################################################
config = ConfigParser.RawConfigParser()
config.read('/home/pi/gfiske.cfg')
db_user = config.get('section1', 'db_user')
db_passwd = config.get('section1', 'db_passwd')
g_user = config.get('section1', 'g_user')
g_passwd = config.get('section1', 'g_passwd')
db_user = db_user.decode('base64','strict')
db_passwd = db_passwd.decode('base64','strict')
email = g_user.decode('base64','strict')
password = g_passwd.decode('base64','strict')[0:15]
spreadsheet_name = 'home_dials'
###############################################################
try:
ser = serial.Serial(port='/dev/ttyUSB0',baudrate=57600)
line = ser.readline()
except Exception,msg:
filename = "/home/pi/db_error_log.txt"
f = open(filename,"r+")
f.readlines()
now = time.localtime(time.time())
curtime = time.asctime(now)
f.write(curtime + "\n")
f.write("Ser error: " + str(msg) + "\n")
f.write("\n")
f.close()
#set up blank variables
totalwatts = 0
temp = 40
pv = 0
hvac = 0
enphasedata = 0
#########################################
# PULL FROM CURRENT COST #
#########################################
def pullFromCurrentCost():
# Read XML from Current Cost. Try again if nothing is returned.
watts1 = None
watts2 = None
sensor = None
while watts1 == None:
line2 = ser.readline()
try:
tree = ET.XML(line2)
watts1 = tree.findtext("ch1/watts")
watts2 = tree.findtext("ch2/watts")
temp = tree.findtext("tmprF")
sensor = tree.findtext("sensor")
except Exception, inst: # Catch XML errors (occasionally the current cost outputs malformed XML)
sys.stderr.write("XML error: " + str(inst) + "\n")
line2 = None
#filename = "/home/pi/db_error_log.txt"
#f = open(filename,"r+")
#f.readlines()
#now = time.localtime(time.time())
#curtime = time.asctime(now)
#f.write(curtime + "\n")
#f.write("XML error : " + str(inst) + "\n")
#f.write("\n")
#f.close()
ser.flushInput()
return temp, watts1, watts2, sensor
## Continuously call energy data from the current cost meter
while True:
try:
#########################################
# Organize sensor data #
#########################################
totalwatts = totalwatts
temp = temp
pv = pv
hvac = hvac
enphasedata = enphasedata
# run for first sensor
data = pullFromCurrentCost()
if int(data[3]) == 0:
#then sensor 0
totalwatts = int(data[1]) + int(data[2])
temp = float(data[0]) - 5.0
if int(data[3]) == 1:
#then sensor 1
pv = int(data[1]) + int(data[2])
temp = float(data[0]) - 5.0
if int(data[3]) == 2:
#then sensor 2
hvac = int(data[1]) + int(data[2])
temp = float(data[0]) - 5.0
# run again for second sensor
data = pullFromCurrentCost()
if int(data[3]) == 0:
#then sensor 0
totalwatts = int(data[1]) + int(data[2])
temp = float(data[0]) - 5.0
if int(data[3]) == 1:
#then sensor 1
pv = int(data[1]) + int(data[2])
temp = float(data[0]) - 5.0
if int(data[3]) == 2:
#then sensor 2
hvac = int(data[1]) + int(data[2])
temp = float(data[0]) - 5.0
# run again for third sensor
data = pullFromCurrentCost()
if int(data[3]) == 0:
#then sensor 0
totalwatts = int(data[1]) + int(data[2])
temp = float(data[0]) - 5.0
if int(data[3]) == 1:
#then sensor 1
pv = int(data[1]) + int(data[2])
temp = float(data[0]) - 5.0
if int(data[3]) == 2:
#then sensor 2
hvac = int(data[1]) + int(data[2])
temp = float(data[0]) - 5.0
#################################
#get enphase system values
#url to fiske enphase system
url = "https://api.enphaseenergy.com/api/systems/62345/summary?&key=1dad77b9676fa86345f48482179e5835"
try:
#create a dictionary out of the enphase json return
enphase = json.load(urllib2.urlopen(url))
enphasedata = enphase["current_power"]
except:
enphasedata = enphasedata
#################################
# calc energy flow values
#add in enphase data
pvtotal = pv + enphasedata
#correct pv data
if pvtotal <= 15:
pvtotal = 0
#check for flow direction to get net
if pvtotal > totalwatts:
net = totalwatts * -1
else:
net = (totalwatts - pvtotal) - pvtotal
#update db
db = MySQLdb.connect("127.0.0.1", db_user, db_passwd, "energy")
myquery2 = "insert into trend values (DEFAULT, NOW()," + str(temp) + "," + str(net) + "," + str(pv) + "," + str(enphasedata) + "," + str(pvtotal) + "," + str(hvac) + ");"
mycleanupquery = "delete from trend order by id limit 1;"
cursor = db.cursor()
cursor.execute(myquery2)
cursor.execute(mycleanupquery)
db.commit()
db.close()
g = gspread.login(email, password)
worksheet = g.open('home_dials').get_worksheet(0)
worksheet.update_cell(2,1,str(net))
worksheet.update_cell(2,2,str(pvtotal))
worksheet.update_cell(2,3,str(hvac))
#set in a rest period
time.sleep(10)
except Exception,msg:
filename = "/home/pi/db_error_log.txt"
f = open(filename,"r+")
f.readlines()
now = time.localtime(time.time())
curtime = time.asctime(now)
f.write(curtime + "\n")
f.write("The main function error is : " + str(msg) + "\n")
f.write("\n")
f.close()