Write Temperature and Humidity to Database and JSON file

The article will show you how to write temperature and humidity data to a SQL database as well as to a JSON file. We will simply built the script by expanding the DHT11 example.

If you don't have a database running already, take the steps here first.

Install Maria DB

Install Apache, PHP and PhpMyAdmin

Install PyMySQL

Now the script in all detail.

First, stat by importing all modules. 

#Adafruit Library to communicate with the sensor
import Adafruit_DHT
#PyMySQL library to estabilsh connection with database
import pymysql.cursors
#import library for time functions (for the sleep timer)
import time
#import the json library to dump the values to a json file where other programs can get the infos from
import json
#import time library to store the date of the data
import time

The sensor is limited when it comes to measurable temperatures and humidity.

#define plausibility limits for humidity and temperature (from datasheet)
humidity_min = 20
humidity_max = 90
temperature_min = 0
temperature_max = 50

The sensor seems to throw some nonsense values from time to time. To avoid this noise are we going to perform 10 measurements in a row to even these out. At the end we make sure to wait for at least 2 seconds since that is the minimum allowed cycle time of the sensor.

#Start time for the timer is the current time
starttime = time.time()

#set-up the sensor and acquire values for temperature and humidity
sensor = Adafruit_DHT.DHT11
pin = 10 #enter your pin here
    
humidity_buffer = []
temperature_buffer = []

#Collect ten measurements
for x in range(9):

    humidity, temperature = Adafruit_DHT.read_retry(sensor, pin)       

    humidity_buffer.append(humidity)
    temperature_buffer.append(temperature)

    time.sleep(5)

The timestamp for every measurement is taken

#acquire and format date and time of measurement
sampling_time = time.localtime()
sampling_time_date = time.strftime('%Y-%m-%d', sampling_time)
sampling_time_time = time.strftime('%H:%M:%S', sampling_time)

The mentioned noise cancelling is fairly simple. The ten measurements are sorted from low to high. Then the highest and lowes value is removed and the rest is averaged. 

#sort the temperature and humidity lists from low to high
humidity_buffer.sort()
temperature_buffer.sort()

#calculate the average without the upper and lower 20% of the values
count = 0
humidity_sum = 0
temperature_sum = 0

for x in range(2,7):
    count = count + 1
    humidity_sum = humidity_sum + humidity_buffer[x]
    temperature_sum = temperature_sum + temperature_buffer[x]

humidity_average = humidity_sum / count
temperature_average = temperature_sum / count

humidity = humidity_average
temperature = temperature_average

Finally, the data goes to the database or respectively to the JSON file.

# Connect to the database
# enter your password under password
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='your_password',
                             db='Home Data Viewer',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

#Write temperature and humidity to database
try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO Wohnzimmer (Datum, Uhrzeit, Temperatur, Feuchte) VALUES (%s, %s, %s, %s)"
        cursor.execute(sql, (sampling_time_date, sampling_time_time, temperature, humidity))
        print('{} {} Done'.format(sampling_time_date, sampling_time_time))
        connection.commit()
           
    #write values to JSON file
    with open('/home/pi/Meine_Programme/data.json','w') as write_file:
        data={'temperature':temperature,'humidity':humidity,'time_code': str(time.asctime(sampling_time))}
        json.dump(data,write_file)
        print('abgelegt')
        
finally:
    connection.close()