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.
Acknowledgement
The original guide that this post is taken from can be located below.
wingoodharry.wordpress.com/2015/01/05/raspberry-pi-temperature-sensor-web-server-part-2-setting-up-and-writing-to-a-mysql-database/
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 mariadb-server 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-mysql.connector
Run the following command to begin the MySQL securing process.
sudo mysql_secure_installation
Just follow the prompts to set a password for the root user and to secure your MySQL installation.For a more secure installation, you should answer “Y” to all prompts when asked to answer “Y” or “N“. These prompts will remove features that allows someone to gain access to the server easier.Make sure you write down the password you set during this process as we will need to use it to access the MySQL server and create databases and users for software such as WordPress or PHPMyAdmin.Now open MySQL:
sudo mysql -u root -p
You will be prompted to enter the password that we just created for MySQL’s root user.
This logs us in to MySQL as the root userWe 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) :
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;
Next, we will create a MySQL user that we will assign to our new database. We can create this user by running the following command.For this example, we will be calling the user “tempUser” and giving it the password “tempPassword“. When creating your own, make sure you replace both of these.
CREATE USER 'tempUser'@'localhost' IDENTIFIED BY 'tempPassword';
With the user created, we can now go ahead and grant all privileges to the user so that it can interact with the database.
This command will grant all permissions to our “tempUser” for all tables within our “temp_database” database.
GRANT ALL PRIVILEGES ON temp_database.* TO 'tempUser'@'localhost';
The final thing we need to do for both our MySQL database and user to be finalized is to flush the privilege table. Without flushing the privilege table, the new user won’t be able to access the database.We can do this by running the following command.
FLUSH PRIVILEGES;
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.
SHOW DATABASES;
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:
USE 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:
DESCRIBE tempLog;
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 mysql.connector
from time import strftime
os.system('modprobe w1-gpio')
os.system('modprobe w1-therm')
temp_sensor = '/sys/bus/w1/devices/28-0300a2798953/w1_slave'
# Variables for MySQL
db = mysql.connector.connect(user='tempUser', password='tempPassword', host='localhost', database='temp_database')
cur = db.cursor()
def tempRead():
t = open(temp_sensor, 'r')
lines = t.readlines()
t.close()
temp_output = lines[1].find('t=')
if temp_output != -1:
temp_string = lines[1].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
addTemp = ("INSERT INTO tempLog "
"(datetime,temperature) "
"VALUES (%s,%s)")
dataTemp = (datetimeWrite, temp)
try:
print "Writing to database..."
# Execute the SQL command
cur.execute(addTemp, dataTemp)
# 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.
