Hot Pi_MySQL

In mylast post, I explained how to set up the Pi with the drivers needed to allow it to read temperatures from DS120B sensors. In this post, I’ll show you the code and setup required to make it start taking readings automatically at bootup, and store the information in a Mysql database for future use. I’ll also add a couple of LEDs to the circuit, so that the system can provide some feedback and also to pave the way for controlling a relay or two.

Here’s the updated circuit schematic (click on it for a bigger version).

There are a few things to note about this slightly more complex schematic:

1. I’ve added another temperature sensor. Its pins are simply connected directly to the pins of the first one (and any subsequent ones). The one-wire protocol and the fact that each sensor has a unique ID means that they can share a common bus.
2. I’ve added two LEDs, just so that the system can indicate some state to me. One will be replaced with a relay in future.
3. I’ve added anAdafruit level converter(the blue pcb). This is there to protect the Pi: its GPIOs run at 3.3V, and are not designed to carry much current. overloading them will cause damage to the Pi. The cheap level shifter allows the 3.3V Pi pins to be connected to 5V input or output from other devices, and protects the Pi from accidental over-voltage. It’s not strictly necessary just to drive LEDs, but the relays I want to use later need 5V.

I’m going to use Python as the main language for running the system. The reason for this is that I don’t know it very well, and there’s no better way to learn than to dive in and try and do something with it. Of course, this also means that my Python code may well be what is technically-termed ‘Not Very Good’. Use it at your peril. To make it work, you’ll have o install a couple of other things. One isMySql, the database into which the program will write the recorded temperature values (you’ll also need the associated Python library). The other ispigpiodwhich is, of course, a Pi GPIO library, though the name always looks like pig-pio to me. Pigpio (also available in its useful daemon form pigpiod) is a C library which exposes all the GPIO to C programs. Not useful for Python, I hear you say. True enough, but it also exposes the same functionality through a TCP socket interface, and it’s dead easy to use this from Python without installing any special python libraries at all. We’ll deal with that in my next post.

Installing MySql

In Linux, it down’t get much easier than this:

<code>sudo apt-get updatesudo apt-get install mysql-server</code>

At some point, the installer will ask you for a password for the root MySql user. In any serious situation, this should be a good, secure, password. I just used the same one as I’m using for everything else on this device. It’s only a toy, after all. Once MySql is installed, you can use the command line client to create a new user and to make the ;temperatures’ database and the ‘data’ table in which the readings will be stored.NB: when you are using the MySql command line client, don’t forget the semicolon at the end of a SQL command line. Multi-line commands (such as the CREATE TABLE command below) are fine in SQL, and they don’t get executed until the parser sees a semicolon. It catches me out all the time.

<code>mysql --user=root --password=My5ecurePa$5wordmysql> create user 'pi'@'localhost' identified by 'raspberry';mysql> grant all privileges on *.* to 'pi'@'localhost';mysql> create database temperatures;mysql> use temperatures;mysql> CREATE TABLE 'data' ('id' int(11) NOT NULL AUTO_INCREMENT, 'timestamp' datetime NOT NULL, 'sensor_id' int(11) NOT NULL, 'temperature' float NOT NULL,PRIMARY KEY ('id'));mysql> quit;</code>

When that’s done, you will have a database with an empty table in it, and a user ‘pi’ which you can use to connect to it from python. For that’ we’ll need the python-mysqldb module. This can be installed thus:

<code>sudo apt-get install python-mysqldb</code>

We can check that this works with a small python script:

<code>#!/usr/bin/pythonimport MySQLdb as mdbimport systry:	con = mdb.connect('localhost', 'pi', 'raspberry', 'temperatures');	cur = con.cursor()	cur.execute("SELECT VERSION()")	ver = cur.fetchone()	print ("Database version : %s " % ver)except mdb.Error, e:	print ("Error %d: %s" % (e.args[0],e.args[1]))	sys.exit(1)finally:		if con:			con.close()</code>

Run this, and if all is well you will see something like

<code>Database version 5.5.37-0+wheezy1</code>

Or possibly an error. With unbridled optimism, I’m going to assume that you are not seeing an error. It’s now time to create the code which will actually read the temperature sensors and write the data to the database. Here it is:

<code>#!/usr/bin/env pythonimport datetime, time, sysimport MySQLdb as mdbdef getTemp(chipid):	tfile=open("/sys/bus/w1/devices/"+chipid+"/w1_slave")	text=tfile.read()	tfile.close()	secondline=text.split("/n")[1]	tempdata=secondline.split(" ")[9]	temperature=float(tempdata[2:])	temperature=temperature/1000	return temperaturetry:	con = mdb.connect('localhost','pi','raspberry','temperatures')	with con:		cur = con.cursor()		while(True):			 timestamp = datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S')			 t1 = getTemp("28-000001a9b68a")			 t2 = getTemp("28-0000009bba2b")			 query = "INSERT INTO data(timestamp,sensor_id,temperature) VALUES ('%s',%s,%s)"%(timestamp,'1',str(t1))			 result = cur.execute(query)			 query = "INSERT INTO data(timestamp,sensor_id,temperature) VALUES ('%s',%s,%s)"%(timestamp,'2',str(t2))			 result = cur.execute(query)			 con.commit()			 print ("%s %s %s"%(timestamp, str(t1), str(t2))			 time.sleep(60) finally:	print ("Goodbye")s.close()</code>

There are two main parts to this code. The functiongetTemp(chipid)reads the virtual file created by the 1-wire driver discussed in mylast postfor a specific temperature sensor, and extracts the temperature data from it. There’s nothing clever about it, just python string processing. You’ll need to modify the code further down to use the chip ids of your own sensors, of course. The main body of the program establishes a connection to the database, then enters an unending loop inside which it gets the temperature from each sensor and writes it to the database with a timestamp using a simple SQL INSERT query. Adding atime.sleep(10)means that this process happens every 10 seconds. Assuming you run this without errors, you’ll find that your database will gradually be populated with temperature readings. You can check this using the mysql command line client again:

<code>mysql --user=pi --password=raspberrymysql> use temperatures;mysql> select * from data limit 10;+----+---------------------+-----------+-------------+| id | timestamp | sensor_id | temperature |+----+---------------------+-----------+-------------+|9 | 2014-05-17 17:17:17 | 1 |20.3 || 10 | 2014-05-17 16:45:10 | 1 |22.187 || 11 | 2014-05-17 16:45:10 | 2 |23.875 || 12 | 2014-05-17 16:45:21 | 1 | 23.75 || 13 | 2014-05-17 16:45:21 | 2 |23.875 || 14 | 2014-05-17 16:45:33 | 1 | 23.75 || 15 | 2014-05-17 16:45:33 | 2 |23.875 || 16 | 2014-05-17 16:45:45 | 1 |23.812 || 17 | 2014-05-17 16:45:45 | 2 |23.875 || 18 | 2014-05-17 16:45:56 | 1 |23.812 |+----+---------------------+-----------+-------------+10 rows in set (0.00 sec)</code>

If you have got that far, and are seeing data in the database, that’s excellent. In my next post, I’ll show you how to install pigpio and use it to flash an LED to give confidence that the process is working without having to check the database all the time. The next step is then to create a webserver which will show the data without having to log on to the Pi. Keep watching this space.