Welcome to Part 2 of this guide. To store our temperature values we will need a database, we will use MySQL for this. To access the database online we will need a web server and a script to run on it, Apache and PHP respectively.
The original guide that this post is taken from can be located below.
Install the Database and Web Server
To install Apache and PHP on the raspberry pi enter this command, when prompted type “y”.
sudo apt-get install apache2 php libapache2-mod-php
Once complete you can test the web server by simply entering the IP address of the Pi into a web browser of a device that is on the same local network as the Pi. You will get the following page if successful:
Now install MySQL by entering the following, press “y” again when prompted.
sudo apt-get install mysql-server mysql-client php-mysql
We will also be editing our MySQL database from our Python script in the future so download the corresponding Python library:
sudo apt-get install python-mysqldb
Now open MySQL:
sudo mysql --user=root
This logs us in to MySQL as the root user
We will now delete the default mysql root user and create a new mysql root user, because the default one can only be used with Linux root account, and so not available for the webserver and php scripts.
To do so, once you connect to MySQL, simply run thoses commands (replace password with the password you want to use) :
DROP USER 'root'@'localhost'; CREATE USER 'root'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *_.*_ TO 'root'@'localhost';
So you now have a web server, connected to PHP and MySQL. That’s all it takes.
On your next connection to MySQL, you will be able to do so without using sudo, with the command
mysql --user=root --password=yourmysqlpassword
We are now going to create a database, I called mine temp_database which is a bit unimaginative. Here is a cheat sheet of MySQL commands too.
CREATE DATABASE temp_database;
We can check this has worked by entering the below. A list of the databases currently held by MySQL will be displayed. Don’t be alarmed if there is more than the one you just created.
You should see something similar to:
+——————–+ | Database | +——————–+ | information_schema | | mysql | | performance_schema | | temp_database | +——————–+ 4 rows in set (0.00 sec)
Now we want to make a new table in the temp_database. To this we firstly have to tell MySQL that we wish to use the temp_database:
We now create a table in MySQL using the following commands. MySQL commands are out of the scope of this blog but essentially we are making a table called tempLog that has two fields; datetime (of type DATETIME) and temperature(of type FLOAT). Both must have values (i.e. not null).
CREATE TABLE tempLog(datetime DATETIME NOT NULL, temperature FLOAT(5,2) NOT NULL);
To check that our table is correct we can check by entering the following:
You will get the following output, describing the table’s fields.
+————-+————+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +————-+————+——+—–+———+——-+ | datetime | datetime | NO | | NULL | | | temperature | float(5,2) | NO | | NULL | | +————-+————+——+—–+———+——-+
Exit MySQL by pressing ctrl z.
Now go to your tempLog directory and add another Python script in nano called readTempSQL.py:
cd tempLog sudo nano readTempSQL.py
Copy the following code into your new Python script.
import os import time import datetime import glob import MySQLdb from time import strftime os.system('modprobe w1-gpio') os.system('modprobe w1-therm') temp_sensor = '/sys/bus/w1/devices/28-00000622fd44/w1_slave' # Variables for MySQL db = MySQLdb.connect(host="localhost", user="root",passwd="password", db="temp_database") cur = db.cursor() def tempRead(): t = open(temp_sensor, 'r') lines = t.readlines() t.close() temp_output = lines.find('t=') if temp_output != -1: temp_string = lines.strip()[temp_output+2:] temp_c = float(temp_string)/1000.0 return round(temp_c,1) while True: temp = tempRead() print temp datetimeWrite = (time.strftime("%Y-%m-%d ") + time.strftime("%H:%M:%S")) print datetimeWrite sql = ("""INSERT INTO tempLog (datetime,temperature) VALUES (%s,%s)""",(datetimeWrite,temp)) try: print "Writing to database..." # Execute the SQL command cur.execute(*sql) # Commit your changes in the database db.commit() print "Write Complete" except: # Rollback in case there is any error db.rollback() print "Failed writing to database" cur.close() db.close() break
This is a modification of our original Python script but we are adding code to handle the MySQL functionality. Firstly, at the top of the script we add an import for the MySQLdb Python library we downloaded earlier. A bit further down you will see variables that will be used when communicating with MySQL (password, user, host etc) – remember to change them to your variables!
“sql = …” is a string that we will send to MySQL as a command that adds our datetime and temperature values to the tempLog database. There is then a try statement that executes the sql command. If for some reason that fails the except code will run, printing a fail message and discarding attempted changes to the database. Play about with this code and try to change stuff, its the only way I learnt how to it works.
Now run the Python script we just made a few times.
sudo python readTempSQL.py
You should see the following output on your terminal window if all has worked:
pi@raspberrypi ~/tempLog $ sudo python readTempSQL.py 18.6 2015-01-04 22:29:24 Writing to database… Write Complete
Now lets check that the Python script actually entered data into the MySQL database. Log back into MySQL and USE the temp_database. We can then query the tempLog table:
mysql --user=root --password=yourmysqlpassword USE temp_database; SELECT * FROM tempLog;
In the terminal window you will see the following output.
+———————+————-+ | datetime | temperature | +———————+————-+ | 2014-12-28 17:26:20 | 18.90 | | 2014-12-28 17:27:05 | 18.90 | | 2014-12-28 17:27:52 | 18.90 | | 2014-12-28 17:30:39 | 19.00 | | 2014-12-28 17:31:02 | 18.90 | +———————+————-+ 5 rows in set (0.00 sec)
If all has worked, well done! Next we will look at getting the data from the database for our app.