Insert DHT11 readings into a SQL database with Python

This guide will explain how to insert temperature and humidity readings by a sensor into a MySQL database by a python script.

You have to create a database and a table, if you don’t know how to do that check my previous guide.

For example, I have a DHT11 temperature and humidity sensor and I want to put the readings into a MySQL database using a python script to detect data and insert them into the database. After that I can take my data from the database and make whatever I want (put in a web page, etc).

The script: (dht11 mysql with python)

Check my guide that explain how to connect the sensor to the Raspberry and install the right library.

Create a new file:

sudo nano temp_sql.py

Then copy this inside:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import RPi.GPIO as GPIO
import time
import sys
import Adafruit_DHT
import MySQLdb
import datetime

conn = MySQLdb.connect(host= "localhost",user= "***",passwd=***",db="readings")

c=conn.cursor()

def dhtreading_witesql():

     sensor_args = { '11': Adafruit_DHT.DHT11,
                                '22': Adafruit_DHT.DHT22,
                                '2302': Adafruit_DHT.AM2302 }
     if len(sys.argv) == 3 and sys.argv[1] in sensor_args:
                                sensor = sensor_args[sys.argv[1]]
                                pin = sys.argv[2]
     else:
                                print('usage: sudo ./Adafruit_DHT.py [11|22|2302] GPIOpin#')
                                print('example: sudo ./Adafruit_DHT.py 2302 4 - Read from an AM2302 connected to GPIO #4')
                                sys.exit(1)

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

     if humidity is not None and temperature is not None:
                               print('Temperature={0:0.1f}°C Humidity={1:0.1f}%'.format(temperature, humidity))
     else:
                               print('Failed to get reading. Try again!')
                               sys.exit(1)

     unix = int(time.time())
     date = str(datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))

     c.execute("INSERT INTO tempdata (Data, Temperatura, Umidita) VALUES (%s, %s, %s)",(date, temperature, humidity))

     conn.commit()

for i in range(1):
     dhtreading_witesql()

c.close
conn.close()

Explanation of the code

This is the connection to the database.

conn = MySQLdb.connect(host= "localhost",user= "***",passwd="***",db="readings")

• User: the user to connect to the database (you can use the root user)
• Passwd: the password of the user that you used
• Db: the database that you want to use (ex. “readings”)

This is the query to the database.

c.execute("INSERT INTO tempdata (Data, Temperature, Humidity) VALUES (%s, %s, %s)",(date, temperature, humidity))

• “tempdata” is the table’s name;
• “Data”, “Temperature” and “Humidity” is the columns’ name;
• “date”, “temperature” and “humidity” are the variables.

The script print into the MySQL database the data for 1 times; you can change the numer of times by changing the number in the script.

for i in range(1):

To run the script you have to write:

sudo python temp_sql.py 11 4

• 11: the type of sensor (DHT11) because you can use this script with the DHT22 (just write 22 instead of 11);
• 4: the GPIO port; if you connect the sensor to another GPIO you have to change this number.

This is the final results:

Using the shell

Using PhpMyAdmin

As you can see the temperature and the humidity are now into our SQL database.

Links:

PhpMyAdmin; Adafruit; Python