Pi in the sky, recording just how hot this is!

Tada! I've got my Raspberry Pi measuring and storing temperature data in Google Cloud SQL.

And I'm pretty excited that it was possible (even simple) for a simple soul like myself.

For context: I've been working on automating my home with home-grown technology, mostly Arduino (or Atmega328) based, but got more and more frustrated with the pain to connect these different Arduinos to get it anything close to "smart". So, I recently turned my attention to Raspberry Pi: The cost and complexity of Arduino + networking (of any sort) quickly exceeded the price of a Pi.

In moving to Pi, I decided to also build my stuff around OpenHab because it gives me the abstraction I'm looking for to deal with the many different things I want to control. It also gives, for free, Android and Web UIs to help control things. But the action above does not leverage OpenHab yet.

BTW - My electricity bill is extortionately high (because of poor home insulation and design), so anything I build now is aimed at reducing electricity costs, and that is also how I justify spending hours on this :)

Pi can't do analogue (easily), so all my analogue sensors have to go or require different approaches. The first, most crucial one is my thermometers. I used to use the TM36, but after waiting 6+ weeks (!) for the delivery of my DS18B20 sensors, they eventually turned up yesterday. (OK, @ £2.59 for 4 delivered, I probably can't complain, but my next batch will rather be these.)

Here's what I did to make it so:

1. Had Raspberry Pi B+ (not essential to be B+) at hand, running Wheezy, connected to my wifi network with ssh enabled.
2. Wired the thermometer up like this (skip step 1, and just do step 2 & 3) and checked that it worked. The biggest hurdle was that I assumed I could use any of the GPIO pins, but it had to be GPIO 4. (In the picture, I'm using a 40-pin Pi breakout board, but it would have worked just as well just directly connecting to the Pi.) I used the 3.3V supply to ensure that I don't fry the Pi GPIO. I also had a dodgy connection, but my brand new mini oscilloscope (see picture, updated to BenF firmware) did wonders to show me where I had no juice.
3. I signed up for Google Cloud SQL and the free trial. Set-up was quick and simple. I'm sure I need to improve my security set-up, but considering I had ZERO MySQL experience, it was relatively simple. I also installed Sequeal Pro to help me create tables in a GUI.
4. On the Pi, I installed a Python MySQL library: apt-get install python-mysqldb
5. In order to permanently add the "modprobe" stuff (to load the kernel drivers for the thermometer), I modified the modules file (sudo nano /etc/modules) and added w1-gpiow1-thermtc/m
6. And then cobbled together (my first) Python script that would
1. set up the connection to Cloud SQL and the thermometer
2. read and extract the temperature values
3. and insert a record in the Cloud SQL database every second.

The net result is that I now have the bare bones of how I can have dozens of these running in and around my house, and then act smartly on the data.

Python code:

import glob

import time

import MySQLdb

base_dir = '/sys/bus/w1/devices/'

device_folder = glob.glob(base_dir + '28*')[0]

device_file = device_folder + '/w1_slave'

cnx = MySQLdb.connect (host='173.someIP.address.of.my.CloudSQLServer.21',port=3306,user='supersecure',passwd='notyet',db='homelog')

cursor = cnx.cursor()

def read_temp_raw():

  f = open(device_file, 'r')

  lines = f.readlines()

  f.close()

  return lines

def read_temp():

  lines = read_temp_raw()

  while lines[0].strip()[-3:] != 'YES':

    time.sleep(0.2)

    lines = read_temp_raw()

  equals_pos = lines[1].find('t=')

  if equals_pos != -1:

    temp_string = lines[1][equals_pos+2:]

    temp_c = float(temp_string) / 1000.0

    return temp_c

while True:

  temp_now = read_temp()

  print(temp_now)

  cursor.execute(("INSERT INTO temperature (sensorid,temperature) VALUES (1, " + str(temp_now) +  ")"))

  cnx.commit()

  time.sleep(1)