Pi-based Domestic Electricity Monitor - Part 3 - Writing data to a file

Warning! This post is just my rough notes as I learn how to do this, so I can find the info again. It may not be as cohesive as I would like, in the short term. I'll tidy it up when I'm done, honest I will.  

Now I can collect the data from the light sensor, I need to be able to write it to a database file.

Looking around, SQLite would seem to be a good choice for this. It's lighter weight than MySQL and, I hope, easier to work with. Also, it's recommended for local file hosting, and since the data will always live on the Pi, that's OK.

I wasted too much time trying to install SQLite, before I discovered ITS ALREADY INCLUDED WITH PYTHON!

I think I'll be needing some help with using it, so here's a tutorial or two I'll use

https://docs.python.org/3/library/sqlite3.html

http://www.tutorialspoint.com/sqlite/sqlite_installation.htm



Time-handling info:

https://docs.python.org/3/library/time.html


Before I start using the light sensor to detect the little flashing light on my electricity meter, I want to test writing to a database with live light level data. Which I'm off to try next...

(Later that same day...)

...I captured sunset light levels tonight! (My daughters went into the room to watch TV though, and turned the lights on). Actual sunset is listed as 5:31pm.

The chart was made by copying and pasting the raw text data from the screen to LibreOffice Calc and post-processing it, so that it went down and not up as the sun went down. Raw values for 'light level' went from about 1000 (bright) to 700,000(dark) as the capacitor took longer to charge in the dark.

 Here's the code I used:

#!/usr/bin/env python
     
# Example for RC timing reading for Raspberry Pi
     
import RPi.GPIO as GPIO, time, os
import sqlite3

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

#create database
#c.execute("CREATE TABLE lightlevel (data text, level integer)")



     
#read light level from GPIO
DEBUG = 1
GPIO.setmode(GPIO.BCM)
  
def RCtime (RCpin):
    reading = 0
    GPIO.setup(RCpin, GPIO.OUT)
    GPIO.output(RCpin, GPIO.LOW)
    time.sleep(0.1)
     
    GPIO.setup(RCpin, GPIO.IN)
    # This takes about 1 millisecond per loop cycle
    while (GPIO.input(RCpin) == GPIO.LOW):
        reading += 1
    return reading
     
for i in range (0,90):
    lilev= (RCtime(23)) # Read RC timing using pin #23
    print (lilev) 
    print (time.asctime())
    c.execute("INSERT into lightlevel VALUES(?,?)",(time.asctime(),lilev))
    conn.commit()
    time.sleep(60)

conn.close()

Not a bad day's work play.

Next up, logging flashes of a LED, and converting the interval to a kW consumption value.


PS
I found these commands here which could be very useful later:

• gpio wfi <pin> rising/falling/both

This causes GPIO to perform a non-busy wait on a single GPIO pin until it changes state to that indicated.

• gpio edge <pin> rising/falling/both/none

This enables the given pin for edge interrupt triggering on the rising, falling or both edges. (Or none which disables it)