This is my first try at Python. It reads the Serial Output from Serial 3 on MotherHub. I had some problems with it randomly throwing exceptions, I seemed to have solved that with some If statements that count the amount of variables. The next 24 hours will be the big test as I just set the script up as a service for uninterrupted SQL insertions 🙂 I’m sure this script will expand over the coming months.
import serial import MySQLdb import datetime from time import sleep ser = serial.Serial('/dev/ttyAMA0', 9600, timeout=0) # This is using the serial GPIO pins on the Raspberry Pi, Pins 14 and 15 conn = MySQLdb.connect( host='Your MySQL server IP', db='SensorDB', user='SensorDBUser', passwd='SensorDBPass' ) #Change these to suit cursor = conn.cursor() count = 0 while True: data = ser.read(9999).strip("\r\n") parsed = data.split(",") sleep(0.5) if len(data) >= 54: print 'Serial Packet Length:', len(data) print 'Got:', data print 'parsed:', parsed templist = data.split(',') print 'Length:', len(templist) # print templist if len(templist) < 12: #If there arent 12 items parsed from the Arduino output string then somethings not right, Go to Jail. Do not pass Go print 'ERROR! BZZZZZZZZZ!' continue else: print 'templist[0]:', templist[0] print 'templist[1]:', templist[1] print 'templist[2]:', templist[2] print 'templist[3]:', templist[3] print 'templist[4]:', templist[4] print 'templist[5]:', templist[5] print 'templist[6]:', templist[6] print 'templist[7]:', templist[7] print 'templist[8]:', templist[8] print 'templist[9]:', templist[9] print 'templist[10]:', templist[10] print 'templist[11]:', templist[11] # Prepare SQL query to INSERT a record into the database. sql = "INSERT INTO tblSensorData(fldCommsMotion, fldCommsTemp, fldCommsBarometer, fldCommsHumidity, fldCommsLux, fldXbee1Temp, fldXbee1Volt, fldXbee1Battery, fldXbee1SolarV, fldTotalPowerUse, fl dHeatuse, fldLightPower) \ VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )" % \ (templist[0], templist[1], templist[2], templist[3], templist[4], templist[5], templist[6], templist[7], templist[8], templist[9], templist[10], templist[11]) print"sql ready for execution" # Execute the SQL command cursor.execute(sql) print"SQL Injected!" count = count + 1 print 'SQL Update Count:', count #This is just so I can easily see how many updates have processed before an exception # Commit your changes in the database conn.commit() #sleep(1) print 'not blocked' conn.close() ser.close() |
MySQL Data is logging! This is massive motivation to get more sensors online. Even though I don’t have a frontend yet I know the data is there to play with.
In my travels with PHPMyAdmin I just noticed it has some basic graphing function! Amazing! When did they add that? Now I’ve noticed a problem with the power records
I have 2 sensors monitoring Power. One on the mains for the entire house and one on the Heating Circuit which does the hot water cylinder at the Heat pump. To calculate the lights and power circuit I simply minus the Heating circuit off the total power circuit. The graph however is showing that every time the heater comes on the power use for the house drops considerably. This is not correct. The difference between the Heating consumption and the total should always be consistent. I’m tempted to look further into this but at the same time I could put those efforts into completing my new energy monitor. I’ll think on it awhile and see if something comes to me