Automated Python Adafruit_DHT script

The plan

The plan is an old fashioned web application: Get data with whatever language (in my case with Python and the Adafruit_DHT library), save it in a database (MySQL) and retrieve & display it via PHP / HTML. In the end I want to display the sensor data in a small self-made web application.

MySQL connector

If you followed the other two guides dutifull, there is not much left do do: Just enhance the python3 library with the mysql-connector package and you're ready to query the database via python.

sudo pip3 install mysql-connector-python

Script automation

I am going to use two file parameters to get this script a little bit dynamic. Pin number and the sensor name are going to be prompted via command line because of obvious reasons: Data pin can change due to setup changes (more sensors, different setup) and data is going to be aggregated on an attribute - the sensor name. I am also going to implement an automated delete script - the delete query behind "rem" will get rid of all the old unwanted data (data older than 7 days).

#!/usr/bin/python3
import sys
from datetime import datetime
import mysql.connector
import board
import Adafruit_DHT

# write file parameters into script variables
pin = sys.argv[1]
name = sys.argv[2]

# date assignment
date = datetime.now()

# get sensor data with read_retry() Adafruit_DHT lib function
sensor = Adafruit_DHT.AM2302
humidity, temperature = Adafruit_DHT.read_retry(sensor, pin)

# mysql login information
mydb = mysql.connector.connect(
  host="YOUR_SERVER",
  user="YOUR_USER",
  password="YOUR_PASSWORD",
  database="YOUR_DATABASE"
)

mycursor = mydb.cursor()

# data insert query
sql = "INSERT INTO sensor_data (pin, name, date, temperature, humidity) VALUES (%s, %s, %s, %s, %s)"
val = (pin, name, date, temperature, humidity)

# delete query for data older than 7 days
rem = "DELETE FROM sensor_data WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY)"

# query execution
mycursor.execute(sql, val)
mycursor.execute(rem)
mydb.commit()

# closing mysql connection
mycursor.close()

Final steps

After the insert query is working, I want to schedule it with crontab. Every 5 minutes should be good enough:

sudo crontab -e
*/5 * * * * sudo python3 /home/pi/YOUR_SCRIPT_NAME.py YOUR_PIN_NUMBER YOUR_SENSOR_NAME

Finally, query the MySQL database via PHP PDO: The code below can be used (edit the database settings and the prepare function to meet your system requirements) as a starting help.

<?php

/* SERVER CONNECTION */
const SERVER_NAME 		= 'YOUR_HOST';
const SERVER_DATABASE	= 'YOUR_DATABASE';
const SERVER_USERNAME	= 'YOUR_USERNAME';
const SERVER_PASSWORD 	= 'YOUR_PASSWORD';

class database{

	public $connection;

	function __construct(){
		$this->connection = new PDO('mysql:host='.SERVER_NAME.';dbname='.SERVER_DATABASE.'', SERVER_USERNAME, SERVER_PASSWORD, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
	}

	// GET FUNCTIONS
	public function getData(){
		$stmt = $this->connection->prepare("SELECT * FROM view_data ORDER BY name ASC;");
		$stmt->execute();
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return $result;
	}
}

$db = new database();
$data = $db->gatData();

// DEIFNE VARIABLES HERE

?>

If everything is working like intended, you should now have information in the $data array.

With a little effort made, the page could look like this:

Have fun playing with the snippets!