#!/usr/bin/env python # # This is a Python module for The Energy Detective: A low-cost whole # house energy monitoring system. For more information on TED, see # http://theenergydetective.com # # This module was not created by Energy, Inc. nor is it supported by # them in any way. It was created using information from two sources: # David Satterfield's TED module for Misterhouse, and my own reverse # engineering from examining the serial traffic between TED Footprints # and my RDU. # # I have only tested this module with the model 1001 RDU, with # firmware version 9.01U. The USB port is uses the very common FTDI # USB-to-serial chip, so the RDU will show up as a serial device on # Windows, Mac OS, or Linux. # # The most recent version of this module can be obtained at: # http://svn.navi.cx/misc/trunk/python/ted.py # # Copyright (c) 2008 Micah Dowty # # Permission is hereby granted, free of charge, to any person obtaining a copy # of this software and associated documentation files (the "Software"), to deal # in the Software without restriction, including without limitation the rights # to use, copy, modify, merge, publish, distribute, sublicense, and/or sell # copies of the Software, and to permit persons to whom the Software is # furnished to do so, subject to the following conditions: # # The above copyright notice and this permission notice shall be included in # all copies or substantial portions of the Software. # # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR # IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, # FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE # AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER # LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, # OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN # THE SOFTWARE. # import time from time import gmtime, strftime import binascii import sys import struct import sqlite3 import MySQLdb #EDIT THIS TO THE PATH TO YOUR SQLITE DATABASE sqlite3path = '/Users/mini/Documents/TED_LIVE/ted.sqlite' #Now your mySQL Connection details mySQLhostname = 'murkyview.com' mySQLusername = 'chrisale_remote' mySQLpassword = '6l3m6ny' mySQLdbname = 'chrisale_ted' #Finally, your two table names, one for the live data, one for hilo... they must be unique. livetablename = 'tedlivearchive' hilotablename = 'tedhiloarchive' try: connmySQL = MySQLdb.connect (host = mySQLhostname,user = mySQLusername,passwd = mySQLpassword,db = mySQLdbname) cursor = connmySQL.cursor() except MySQLdb.Error, e: errorMSG() ###THANK YOU USER EDITABLE SECTION IS DONE#### #global hiloperiod #hiloperiod = 60 # Set this to the interval you want hiloarchives to be stored default is 1 minute #global runninghiloperiod #runninghiloperiod = time.time() #Now we need to figure out what time it is and when the next "top of minute, five minute, hour, or whatever" will begin. #currentseconds = strftime("%S", gmtime()) #if currentseconds != 0: # wait = 60 - float(currentseconds) #waiting = wait - 2 #print 'waiting ' + str(waiting) + ' seconds.' #time.sleep(waiting) # We will wait this long before starting so everything starts at zero mySQLsuccess = 1 checkDone = 0 def mySQLcursor(): time.sleep(30.0) global mySQLsuccess mySQLsuccess = 1 global checkDone checkDone = 1 def errorMSG(): print "mySQL connection unavailable. Will try later. Error %d: %s Waiting 60 seconds" % (e.args[0], e.args[1]) global mySQLsuccess mySQLsuccess = 0 global checkDone checkDone = 0 def mySQLconnect(): try: mySQLcursor() cursor = connmySQL.cursor() except MySQLdb.Error, e: errorMSG() mySQLconnect() conn = sqlite3.connect(sqlite3path) #creating the remote mysql connection #create the cursors c = conn.cursor() if (mySQLsuccess != 0): cursor.execute('CREATE TABLE IF NOT EXISTS ' + hilotablename + ' (RecordTime datetime NOT NULL PRIMARY KEY, liveRate float, MTUHourCode smallint, lowVoltsDay float, highVoltsDay float, lowVoltsMonth float, highVoltsMonth float, peakKWHDay float, peakKWHMonthToDate float, peakDollarDay float, peakDollarMonthToDate float, totalDollarDay float, totalKWHDay float, totalKWHMonthtoDate float, totalDollarMonthtoDate float, totalDollarMonthtoDateValue float, projectedDollarsMonth float, projectedKWhMonthProjected float, rateArray varchar(20))') # Create tables in both if it isn't there already. cursor.execute('CREATE TABLE IF NOT EXISTS ' + livetablename + ' (RecordTime datetime NOT NULL PRIMARY KEY, liveRate float, liveKWH float, liveDollar float, liveVolts real, liveLEDStatus char(5))') # Get the latest row in the remote mySQL database for comparison if the database is found to be out of date compared to the just fetched date or if there are no records in the database we'll just start from scratch def inserttopupRows(lastdatemySQLpass,selectedtablename,liveorhi): #We're now going to find all the records in the sqlite database after the last record in the mySQL database. We're getting two because it will get the first, same, record, and then the first new record, we want to start from the new record or we get a duplicate error. c.execute('SELECT date from ' + selectedtablename + ' WHERE date > ' + str(lastdatemySQLpass) + ' ORDER BY date ASC limit 2;') limitermySQL = c.fetchone() allRows = c.fetchall() # print(limitermySQL[0]) # print(allRows[0][0]) # got the records if limitermySQL != None: c.execute('SELECT * from ' + selectedtablename + ' WHERE date > ' + str(limitermySQL[0]) + ' ORDER BY date ASC;') allRows = c.fetchall() # selecting all the records in sqlite from the latest one onwards #inserting all of those into the database if liveorhi != 'high': for value1, value2, value3, value4, value5, value6 in allRows: cursor.execute('INSERT INTO ' + selectedtablename + ' VALUES (FROM_UNIXTIME(' + str(value1) + '),' + str(value2) + ',' + str(value3) + ',' + str(value4) + ',' + str(value5) + ',' + str(value6) + ');') else: for value1, value2, value3, value4, value5, value6, value8, value9, value10, value11, value12, value13, value14, value15, value16, value17, value18 in allRows: if (mySQLsuccess != 0): cursor.execute('INSERT INTO ' + selectedtablename + ' VALUES (FROM_UNIXTIME(' + str(value1) + '),' + str(value2) + ',' + str(value3) + ',' + str(value4) + ',' + str(value5) + ',' + str(value6) + ',' + str(value7)+ ',' + str(value8)+ ',' + str(value9)+ ',' + str(value10)+ ',' + str(value11)+ ',' + str(value12)+ ',' + str(value13)+ ',' + str(value14)+ ',' + str(value15)+ ',' + str(value16) + ',' + str(value17) + ',' + str(value18) + ');') def doallRows(): #This function populates the mysql database when no records exit in it (ie. it is new) but the local database has data. #First we populate the Live mysqld database c.execute('SELECT * from ' + livetablename + ' ORDER BY date ASC;') allRows = c.fetchall() for value1, value2, value3, value4, value5, value6 in allRows: cursor.execute('INSERT INTO ' + livetablename + ' VALUES (FROM_UNIXTIME(' + str(value1) + '),' + str(value2) + ',' + str(value3) + ',' + str(value4) + ',' + str(value5) + ',' + str(value6) + ');') #Now we populate the hilo mysql database c.execute('SELECT * from ' + hilotablename + ' ORDER BY date ASC;') allRows = c.fetchall() for value1, value2, value3, value4, value5, value6, value8, value9, value10, value11, value12, value13, value14, value15, value16, value17, value18 in allRows: cursor.execute('INSERT INTO ' + hilotablename + ' VALUES (FROM_UNIXTIME(' + str(value1) + '),' + str(value2) + ',' + str(value3) + ',' + str(value4) + ',' + str(value5) + ',' + str(value6) + ',' + str(value7)+ ',' + str(value8)+ ',' + str(value9)+ ',' + str(value10)+ ',' + str(value11)+ ',' + str(value12)+ ',' + str(value13)+ ',' + str(value14)+ ',' + str(value15)+ ',' + str(value16) + ',' + str(value17) + ',' + str(value18) + ');') def topupRows(lastdatesqlite,lastdatesqlitehilo): #This function populates the mysql database when records are out of date from the local database. It will only populate records that are missed since the last record in the mySQL database. If you need to fill in missed records then you need to do so with a mySQL dump. cursor.execute('SELECT UNIX_TIMESTAMP(RecordTime) from ' + livetablename + ' ORDER BY RecordTime DESC LIMIT 1;') lastdatemySQL = cursor.fetchone() if lastdatesqlite != lastdatemySQL[0]: inserttopupRows(lastdatemySQL[0],livetablename,'live') cursor.execute('SELECT UNIX_TIMESTAMP(RecordTime) from ' + hilotablename + ' ORDER BY RecordTime DESC LIMIT 1;') lastdatemySQLhilo = cursor.fetchone() lastdatemysSQL = round(lastdatemySQL[0],0) if lastdatesqlitehilo != lastdatemysSQL: inserttopupRows(lastdatemySQLhilo[0],hilotablename,'high') def checkConsistency(mySQLsuccess,checkDone): # Check the last record in the local sqlite database for later comparison to remote c.execute('SELECT date from ' + livetablename + ' ORDER BY date DESC LIMIT 1;') lastdate = c.fetchone() if lastdate != None: lastdatesqlite = round(float(lastdate[0]),0) # Now do the same for the hilo database c.execute('SELECT date from ' + livetablename + ' ORDER BY date DESC LIMIT 1;') lastdatehilo = c.fetchone() if lastdatehilo != None: lastdatesqlitehilo = round(float(lastdatehilo[0]),0) #Check for any records in the mySQL database first in case we need to recreate it. cursor.execute('SELECT * from ' + livetablename + ' order by RecordTime desc LIMIT 1;') mySQLrow = cursor.fetchone() if mySQLrow == None: doallRows() else: topupRows(lastdatesqlite,lastdatesqlitehilo) # We can also close the cursor if we are done with it c.close() time.sleep(5.0) def main(): global mySQLsuccess global checkDone while True: if (mySQLsuccess == 0): cursor = mySQLconnect() else: checkConsistency(mySQLsuccess,checkDone) if __name__ == "__main__": main()