Motion Sensor (PIR) with ESP8266 and MQTT

SQL Storage and HTML Display

It is 2018. Only turning the LEDs on automatically is not sexy enough. In this part, I will collect the motion status from my working table, and stored the data into an SQL database, and display the data on the browser using Flask and Plotly. The photo below is the summary:

flow
A final version of PIR and Data Storage

The concept is similar to Step 3, but instead of an LED light, this is the case I use a Python script to listen to the topic. One modification on the circuit was I use a pre-made 3.3V regulator such as this one , costs $0.88 on Aliexpress rather making 3.3 voltage regulator from AMS1117 IC.

pub-sub
A PIR version to Publish Data

First, I created a database and a table by SQLite3. Install sqlite3 if you not done so:

pip3 install sqlite3
CREATE TABLE pirone (thetime DATETIME, pirstatus INT);
setup database
Set up Database

Then, we need to write a script to listen to the topic. I used Paho-MQTT Python . Starting by install the library

pip install paho-mqtt
or pip3 install paho-mqtt --user

And here is the full Python script:

#! /usr/bin/python3
import time
import sqlite3, json
import paho.mqtt.client as mqtt
mqtt_topic = "sensor/door/pir"
mqtt_username = "janedoe"
mqtt_password = "johndoe"
dbFile = "pir.db"  # this assumes that the database file and the script are in the same folder
mqtt_broker_ip = '192.168.1.50'

def takeTime():
    return time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

    # The callback for when the client receives a CONNACK response from the server.
def on_connect(client, userdata, flags, rc):
    print("Connected with result code "+str(rc))
    client.subscribe(mqtt_topic, 0)
# The callback for when a PUBLISH message is received from the server.

def on_message(client, userdata, msg):
    time_ = takeTime()
    topic = msg.topic
    payload = json.dumps(msg.payload.decode('utf-8'))
    sql_cmd = sql_cmd = "INSERT INTO {1[sensor]} VALUES ('{0}', {1[motion]})".format(time_, payload)
    writeToDB(sql_cmd)
    print(sql_cmd)
    return None

def writeToDB(sql_command):
    conn = sqlite3.connect(dbFile)
    cur = conn.cursor()
    cur.execute(sql_command)
    conn.commit()

client = mqtt.Client()
client.username_pw_set(username=mqtt_username, password=mqtt_password)                                                                                                                   
client.connect(mqtt_broker_ip, 1883, 60)
client.on_connect = on_connect
client.on_message = on_message
time.sleep(1)
client.loop_forever()

Third, to get the data back from the SQL database, you need a sql command like this one.

sql_command = """ SELECT * from pirone ORDER BY thetime DESC LIMIT 2000;"""
        

A literal meaning is 'select everything from table pirone, then sort the data by thetime column in descending order, and limits the number of rows by 2000'.

The final package of the Flask web server and database and Python script are posted on GitHub as well.

The recommended to run a Flask web server to use virtualenv; running on the system libraries are fine as well.

First, install the required libraries for the web server:

pip3 install flask
pip3 install plotly

To run the web server, change the port as desired, and,

python3 app_pi.py

Access the web server in the same computer by URL:

localhost:9999

or from other computers through the IP address of the one running the webserver such as:

192.168.1.49:9999
Concept
A screenshot of the folder structure
barcode
Well, finally we can see some parterns.

To stop the web server, press Control + C.

That is about it. I hope you this tutorial is helpful.

▣ ▣ ▣