Pi-based Domestic Electricity Monitor - Part 6 - First Log - Success and Failure

Summary:

I logged 24hrs of data from my domestic electricity meter, but discovered that writing each event to the db with SQlite seemed to be taking 4 seconds, making the program as it stands no good for logging an event which can happen more than twice a second on occasions of high consumption. Values below 1kW were recorded accurately I believe.

To run the program via SSH I used nohup for the first time,  so that it would run even once I closed the session...and then had to learn how to find and kill said program:

To hunt running program: 

$ ps -e | grep python


To kill running program:

$ sudo kill 3861


Detail:

I used the program below to log the meter for around 24 hrs

(House averages 1kW, or 800 flashes per hour. 24hrs is therefore around 20,000 flashes)

This was to test the logging process and to see the size of the data file that would be created.

However, on analysing the data I soon noticed the data was suspect:
a) not enough readings for 24hrs (~9000, rather than ~20,000)
b) readings which never go above around 1kW:

(graph created in Libre office after exporting data from sqlite. This site was helpful here: http://www.sqlite.org/cli.html)


 Here's a sample of the data: (Time|Consumption|Meter Reading)

Tue Mar 10 07:23:49 2015|0.980246908963771|16791
Tue Mar 10 07:23:54 2015|0.939329897684513|16791
Tue Mar 10 07:23:58 2015|0.978048032653337|16791
Tue Mar 10 07:24:03 2015|0.977999583707987|16791
Tue Mar 10 07:24:08 2015|0.959483188529128|16791
Tue Mar 10 07:24:12 2015|0.955398819345674|16791
Tue Mar 10 07:24:17 2015|0.97396251715844|16791

Every interval is around 4 seconds and 1kW at a time when it should be well above this.

When I remove the lines that write the data to the db, the consumption data becomes correct, so I think that writing the data to the db is taking around 4 seconds.

The size of the data file is not going to be a problem: ~9000 readings is ~500kB.

Here's the code I used:

It features a facility for calculating the meter reading based on initial values and 800 flashes of the LED being 1kWh.

#!/usr/bin/env python
     
#First go at logging data from the meter

import RPi.GPIO as GPIO, time, os
import sqlite3


#initiaise database tools
conn = sqlite3.connect('elecmeter.db')
c =  conn.cursor()

#create database
#what to send to db: ascitime, power consuption, meter reading

c.execute("CREATE TABLE e_meterlog01 (time float, power float, meter_reading integer)")

#initialise pins     
DEBUG = 1
GPIO.setmode(GPIO.BCM)
GPIO.setup(23, GPIO.IN, pull_up_down=GPIO.PUD_DOWN)



#procedure to wait for LED flash front edge
def wait_for_flash():
    try:
        GPIO.wait_for_edge(23,GPIO.RISING)
    except KeyboardInterrupt:
        GPIO.cleanup()       # clean up GPIO on CTRL+C exit 
        conn.close() 

#initialise meter reading tools 

meter_rdg = 16777
impulse_count=0

#timing and kW calcs based on 800 imp / kWh:

start_time=time.time()

for i in range (0,22000):
    wait_for_flash()
    elapsed_time = time.time()-start_time
    start_time=time.time() 

#increment the meter reading every 800 pulses:

    if impulse_count < 800:
        impulse_count += 1
    else:
        meter_rdg +=1
        impulse_count=0 

    kW=((1/elapsed_time)*4.5) 

    print (elapsed_time,kW,meter_rdg,impulse_count)
    c.execute("INSERT into e_meterlog01 VALUES(?,?,?)",(time.asctime(),kW,meter_rdg))
    conn.commit()
    time.sleep(0.3) #to allow for LED to turn off
    
GPIO.cleanup()           # clean up GPIO on normal exit        
conn.close()

print("Ending Program")