-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathQuerySolar.py
executable file
·334 lines (299 loc) · 14.8 KB
/
QuerySolar.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
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
#!/usr/bin/env python3
'''
Program to read Solar energy production data from inverters.
Install as a Launchctl Agent using:
../MqttUtils/InstallAgent.py QuerySolar.py
QuerySolar optional args are specified at the end of the above line
'''
import time
import datetime
import os
import argparse
import sys
import configparser
import logging
import logging.config
import logging.handlers
import json
import binascii
from binascii import a2b_hex
import pymysql
import pymysql.err as Error
import paho.mqtt.publish as publish
import telnetlib
from telnetlib import DO, DONT, IAC, WILL, WONT, Telnet
import re
####################### GLOBAL DEFINITIONS
# Configuration parameters without which we can do nothing.
RequiredConfigParams = frozenset((
'inserter_host'
, 'inserter_schema'
, 'inserter_port'
, 'inserter_user'
, 'inserter_password'
, 'mqtt_topic'
, 'mqtt_host'
, 'mqtt_port'
, 'solar_host'
, 'solar_port'
, 'solar_table'
))
# GLOBALS
DBConn = None
dontWriteDb = True
SOLAR_WHLIFE = b"whlife?\r"
SOLAR_KWHTODAY = b"kwhtoday?\r"
SOLAR_CUSTOM11 = b"custom11?\r"
SOLAR_MEASIN = b"measin?\r"
SOLAR_MEASOUT = b"measout?\r"
SOLAR_IDN = b"idn?\r"
SOLAR_MODELID = b"modelid?\r"
# Dictionary of messages to inverters and responses.
# Responses are extracted from return message by lambda function for each variable.
# Variable names MUST match database fields in the insert command; order is not important.
clientMessages = { SOLAR_CUSTOM11: { 'Name': lambda m: m.replace(b'\r', b'').decode().strip()},
SOLAR_WHLIFE: {'LifeWattHour': lambda m: float(m)},
SOLAR_KWHTODAY: {'TodayWattHour': lambda m: float(m)*1000},
SOLAR_IDN: {'SerialNumber': lambda m: re.split(b'S:(.*)\r', m)[1].decode().strip(),
'Xid': lambda m: re.split(b'X:(.*?) ', m)[1].decode().strip(),
'ModelId': lambda m: re.split(b'M:(.*?) ', m)[1].decode().strip()},
SOLAR_MEASIN: {'InVoltsNow': lambda m: float(re.split(b'V:([0-9.]*) ?', m)[1]),
'InAmpsNow': lambda m: float(re.split(b'I:([0-9.]*) ?', m)[1]),
'InWattsNow': lambda m: float(re.split(b'P:([0-9.]*) ?', m)[1])},
SOLAR_MEASOUT: {'OutVoltsNow': lambda m: float(re.split(b'V:([0-9.]*) ?', m)[1]),
'OutAmpsNow': lambda m: float(re.split(b'I:([0-9.]*) ?', m)[1]),
'OutWattsNow': lambda m: float(re.split(b'P:([0-9.]*) ?', m)[1])}
}
databaseQuery = """INSERT INTO `{schema}`.`{table}`
(Name, TodayWattHour, LifeWattHour, SerialNumber, Xid, ModelId,
InVoltsNow, InAmpsNow, InWattsNow,
OutVoltsNow, OutAmpsNow, OutWattsNow)
VALUES (%(Name)s, %(TodayWattHour)s, %(LifeWattHour)s, %(SerialNumber)s, %(Xid)s, %(ModelId)s,
%(InVoltsNow)s, %(InAmpsNow)s, %(InWattsNow)s,
%(OutVoltsNow)s, %(OutAmpsNow)s, %(OutWattsNow)s)"""
ProgFile = os.path.basename(sys.argv[0])
ProgName, ext = os.path.splitext(ProgFile)
ProgPath = os.path.dirname(os.path.realpath(sys.argv[0]))
##### Setup logging; first try for file specific, and if it doesn't exist, use a folder setup file.
logConfFileName = os.path.join(ProgPath, ProgName + '_loggingconf.json')
if not os.path.isfile(logConfFileName):
logConfFileName = os.path.join(ProgPath, 'Loggingconf.json')
if os.path.isfile(logConfFileName):
# print('Using logging conf file: %s'%logConfFileName)
try:
with open(logConfFileName, 'r') as logging_configuration_file:
config_dict = json.load(logging_configuration_file)
if 'log_file_path' in config_dict:
logPath = os.path.expandvars(config_dict['log_file_path'])
os.makedirs(logPath, exist_ok=True)
else:
logPath=""
for p in config_dict['handlers'].keys():
if 'filename' in config_dict['handlers'][p]:
fn = os.path.join(logPath, config_dict['handlers'][p]['filename'].replace('<replaceMe>', ProgName))
config_dict['handlers'][p]['filename'] = fn
# print('Setting handler %s filename to: %s'%(p, fn))
# # program specific logging configurations:
# config_dict["handlers"]["console"]["level"] = 'NOTSET'
# print('Setting console handler level to NOTSET')
# # config_dict["handlers"]["debug_file_handler"]["class"] = 'logging.FileHandler'
# config_dict["handlers"]["debug_file_handler"]["mode"] = '\'w\''
logging.config.dictConfig(config_dict)
except Exception as e:
print("loading logger config from file failed.")
print(e)
pass
else:
print("Logging configuration file not found.")
logger = logging.getLogger(__name__)
logger.info('logger name is: "%s"', logger.name)
# Generate a timezone for LocalStandardTime
# Leaving off zone name from timezone creator generates UTC based name which may be more meaningful.
localStandardTimeZone = datetime.timezone(-datetime.timedelta(seconds=time.timezone))
logger.debug('LocalStandardTime ZONE is: %s'%localStandardTimeZone)
#### LOCAL FUNCTIONS
def GetConfigFilePath():
fp = os.path.join(ProgPath, 'secrets.ini')
if not os.path.isfile(fp):
fp = os.environ['PrivateConfig']
if not os.path.isfile(fp):
logger.error('No configuration file found: %s', fp)
sys.exit(1)
logger.info('Using configuration file at: %s', fp)
return fp
def telnet_option_negotiation_cb(tsocket, command, option):
"""
:param tsocket: telnet socket object
:param command: telnet Command
:param option: telnet option
:return: None
"""
if option == telnetlib.SGA:
if command == DO:
logger.debug("CB-send: IAC WILL SGA")
tsocket.sendall(IAC + WILL + option)
if command == DONT:
logger.debug("CB-send: IAC WONT SGA")
tsocket.sendall(IAC + WONT + option)
elif option == telnetlib.BINARY:
if command == DO:
logger.debug("CB-send: IAC WILL BINARY")
tsocket.sendall(IAC + WILL + option)
if command == DONT:
logger.debug("CB-send: IAC DONT BINARY")
tsocket.sendall(IAC + WONT + option)
elif command in (DO, DONT):
logger.debug("CB-send: IAC WONT " + str(ord(option)))
tsocket.sendall(IAC + WONT + option)
elif command in (WILL, WONT):
logger.debug("CB-send: IAC DONT " + str(ord(option)))
tsocket.sendall(IAC + DONT + option)
########################## MAIN
def main():
global DBConn, dontWriteDb, localStandardTimeZone
## Determine the complete file paths for the config file and the graph definitions file.
config = configparser.ConfigParser(interpolation=configparser.ExtendedInterpolation())
configFile = GetConfigFilePath()
# configFileDir = os.path.dirname(configFile)
## Open up the configuration file and extract some parameters.
config.read(configFile)
cfgSection = ProgFile+"/"+os.environ['HOST']
logger.info("INI file cofig section is: %s", cfgSection)
# logger.debug('Config section has options: %s'%set(config.options(cfgSection)))
# logger.debug('Required options are: %s'%RequiredConfigParams)
if not config.has_section(cfgSection):
logger.critical('Config file "%s", has no section "%s".', configFile, cfgSection)
sys.exit(2)
if len( RequiredConfigParams - set(config.options(cfgSection))) > 0:
logger.critical('Config section "%s" does not have all required params:\n"%s"\nit has params: "%s".', cfgSection, RequiredConfigParams, set(config.options(cfgSection)))
logger.debug('The missing params are: %s'%(RequiredConfigParams - set(config.options(cfgSection)),))
sys.exit(3)
cfg = config[cfgSection]
parser = argparse.ArgumentParser(description = 'Read data from solar inverter(s) and send to MQTT and database.')
# parser.add_argument("-m","--meterId", dest="meterId", action="store", default=cfg['meter_id'], help="Numeric Id of EKM meter to read.")
parser.add_argument("-r", "--repeatCount", dest="repeatCount", action="store", default='0', help="Number of times to read meinvertersters; 0 => forever.")
parser.add_argument("-i", "--interval", dest="interval", action="store", default='5', help="The interval in munutes between successive inverter reads.")
parser.add_argument("-W", "--dontWriteToDB", dest="noWriteDb", action="store_true", default=False, help="Don't write to database [during debug defaults to True].")
parser.add_argument("-v", "--verbosity", dest="verbosity", action="count", help="increase output verbosity", default=0)
args = parser.parse_args()
# Verbosity = args.verbosity
dontWriteDb = args.noWriteDb
logger.debug('Write to DB? %s'%(not dontWriteDb))
# Prepare Solar parameters
solarHost = cfg['solar_host']
solarTable = cfg['solar_table']
solarPorts = tuple(cfg['solar_port'].split())
logger.debug('Solar parameters: host="%s"; table="%s"; ports="%s"'%(solarHost, solarTable, solarPorts))
# Prepare MQTT parameters
mqttTopic = cfg['mqtt_topic']
mqttPort = int(cfg['mqtt_port'])
mqttHost = cfg['mqtt_host']
############ setup database connection
user = cfg['inserter_user']
pwd = cfg['inserter_password']
host = cfg['inserter_host']
port = int(cfg['inserter_port'])
schema = cfg['inserter_schema']
logger.info("user %s"%(user,))
logger.info("pwd %s"%(pwd,))
logger.info("host %s"%(host,))
logger.info("port %d"%(port,))
logger.info("schema %s"%(schema,))
# Generate a timezone for LocalStandardTime
# Leaving off zone name from timezone creator generates UTC based name which may be more meaningful.
localStandardTimeZone = datetime.timezone(-datetime.timedelta(seconds=time.timezone))
logger.debug('LocalStandardTime ZONE is: %s'%localStandardTimeZone)
magicQuitPath = os.path.expandvars('${HOME}/.CloseQuerySolar')
intervalSec = int(args.interval) * 60
if intervalSec < 60:
logger.warning('Looping intervals less than 1 minute not supported. Set to 1 minute.')
intervalSec = 60
#### Don't sleep the first time through; just sample data now, then wait for next.
# secSinceEpoch = time.time()
# sleepLength = intervalSec - secSinceEpoch % intervalSec
# logger.debug("Sleep for %s sec."%sleepLength)
# time.sleep(sleepLength)
# logger.debug('Slept for %s seconds. It is now: %s'%(sleepLength, datetime.datetime.now().isoformat()))
loopCount = int(args.repeatCount)
if loopCount == 0: loopCount = 1000000000 # Essentially keep going forever
DBConn = pymysql.connect(host=host, port=port, user=user, password=pwd, database=schema, binary_prefix=True, charset='utf8mb4')
logger.debug('DBConn is: %s'%DBConn)
while loopCount > 0:
try:
for port in solarPorts:
with DBConn.cursor() as cursor, Telnet("192.168.1.112", int(port), timeout=2) as tn:
tn.set_debuglevel(args.verbosity)
logger.debug('Got a telnet object: %s'%tn)
logger.debug('set option negotiator.')
tn.set_option_negotiation_callback(telnet_option_negotiation_cb)
logger.debug('Option negotiator is %s'%tn.option_callback)
tnSocket = tn.get_socket()
logger.debug('Connection socket: %s'%tnSocket)
time.sleep(.1)
# Option negotiation startw when I read the telnet socket
while tn.sock_avail():
msg = tn.read_eager()
logger.debug('Received some data: %s'%msg)
time.sleep(.1)
# Option negotiation completed
outputDict = {"ComputerTime": datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S.%f')}
for k, v in clientMessages.items():
logger.debug('Send a command: "%s"'%str(k))
tnSocket.sendall(k)
time.sleep(.1)
while tn.sock_avail():
msg = tn.read_eager()
logger.debug('Received some data: %s'%msg)
for n, f in v.items():
logger.debug('%s: %s'%(n, f(msg)))
outputDict[n] = f(msg)
time.sleep(.1)
outMsg = json.JSONEncoder().encode(outputDict)
logger.debug('Publishing meter data: "%s"'%outMsg)
publish.single(mqttTopic, payload = outMsg, hostname = mqttHost, port = mqttPort)
query = databaseQuery.format(schema = schema, table = solarTable)
logger.debug('Insertion query is: %s'%query)
if dontWriteDb:
logger.debug('NOT inserting into SolarEnergy table with query: "%s"'%cursor.mogrify(query, outputDict))
else:
logger.debug('Inserting into SolarEnergy table with query: "%s"'%cursor.mogrify(query, outputDict))
cursor.execute(query, outputDict)
DBConn.commit()
logger.debug('No more data availaible for this inverter port.')
except pymysql.Error as e:
logger.exception(e)
time.sleep(10)
else:
# Only close connection when program ends
pass
#### if magic shutdown file exists, exit loop, cleanup and exit
sleepCounter = 0
sleepLength = intervalSec - time.time() % intervalSec
while sleepLength > 20:
sleepCounter += 1
time.sleep(20)
if os.path.exists(magicQuitPath):
logger.debug('Found magic quit file.')
break # break out of check magic file loop
sleepLength = intervalSec - time.time() % intervalSec
if os.path.exists(magicQuitPath):
logger.debug('Quitting because magic file exists.')
logger.debug('Delete magic file.')
os.remove(magicQuitPath)
break # break out of count loop
sleepLength = intervalSec - time.time() % intervalSec
logger.debug("Sleep for %s more sec."%sleepLength)
time.sleep(sleepLength)
logger.debug('Slept for a total of %s seconds. It is now: %s'%(sleepLength + sleepCounter*20, datetime.datetime.now().isoformat()))
loopCount = loopCount - 1
if loopCount == 0:
logger.debug('Loop counter exhausted.')
break # No point in waiting if just going to quit
else:
logger.debug('Keep going %s more times.'%loopCount)
logger.debug('Close the database connection.')
DBConn.close()
logger.info(' ############## QuerySolar All Done #################')
if __name__ == "__main__":
main()
pass