Documentation

FairCom EDGE IoT Database

Previous Topic

Next Topic

Tutorial for Using Python with FairCom EDGE MQTT

This tutorial demonstrates collecting sensor data in Python on the Raspberry Pi and storing the data in the FairCom DB database utilizing MQTT.

The Eclipse Paho project provides open-source client implementations of MQTT messaging protocols. In this tutorial, we will use the Paho MQTT Publisher to communicate with FairCom EDGE on the Pi. The example uses Python 3 to read a sensor and publish the value via MQTT to FairCom EDGE. For more information about MQTT, see MQTT Broker later in this document.

You will need to install and start FairCom EDGE database, as described in Download and Installation.

To use MQTT for messaging from Python to FairCom EDGE on a Pi, follow these steps:

  1. Install the necessary libs to read the sensors via Python on the Pi.

    For example, to use a DS18b20 sensor, the install command is:

    sudo apt-get install python3-w1thermsensor

  2. Install the Paho MQTT Python publisher on the Pi. It is available from:

    https://pypi.org/project/paho-mqtt/

  3. Type the following:

    pip3 install paho-mqtt

  4. Write your Python 3 example program to read sensors and publish to FairCom EDGE on PC via MQTT with the topic Sensor1. The example code below uses Python 3:

    import time

    import paho.mqtt.client as mqtt

    from w1thermsensor import W1ThermSensor

    sensor = W1ThermSensor()

    client = mqtt.Client(client_id="connectTest")

    client.connect("localhost", 1883, 60)

    while True:

    temp = sensor.get_temperature(W1ThermSensor.DEGREES_F)

    jsonMsg = "{\"temperature_F\":%f}" % (temp)

    client.publish("Sensor1", jsonMsg)

    print("published %s" %(jsonMsg))

    time.sleep(10)

Now let's check to see if the messages are getting to the FairCom EDGE MQTT Broker:

Note: MQTT must be configured as described in Setting up MQTT Persistence on the Edge.

  1. Run the following Python 3 program to subscribe to your published data. You should see your data coming through every 10 seconds:

    import paho.mqtt.client as mqtt

    def on_connect(client, userdata, flags, rc):

    print("Connected with result code "+str(rc))

    def on_message(client, userdata, msg):

    print(msg.topic+" "+str(msg.payload))

    client = mqtt.Client(client_id="connectTest")

    client.on_connect = on_connect

    client.on_message = on_message

    client.connect("localhost", 1883, 60)

    client.subscribe("Sensor1")

    client.loop_forever()

  2. Run the following Python 3 program to start persisting your sensor data. The program simply packages up JSON data to be published on a specific topic. By adjusting the JSON data you can control the database name created, the tags persisted to fields, and the names and types of those fields.

    import paho.mqtt.client as mqtt

    client = mqtt.Client(client_id="connectTest")

    client.connect("localhost", 1883, 60)

    jsonMsg = "{\"operation\": \"CreatePersistenceTopic\", \"persistenceTopic\": \"Sensor1\", \"databaseConnectionString\": \"FAIRCOMS@localhost\", \"tableName\": \"Sensor1Table\", \"tableAutoTimeStamp\": true, \"tableAutoTimeStampIndex\": true, \"mapOfPropertiesToFields\": [ { \"jsonPropertyPath\": \"temperature_F\", \"fieldName\": \"temperature\", \"fieldType\": \"DOUBLE\"}, {\"jsonPropertyPath\": \"humidity\", \"fieldName\": \"humidity\", \"fieldType\": \"DOUBLE\"}, {\"jsonPropertyPath\": \"pressure_Hg\", \"fieldName\": \"pressure\", \"fieldType\": \"DOUBLE\"}]}"

    client.publish("ctreeAdministration", jsonMsg)

    print("published %s" %(jsonMsg))

    The above JSON message is shown below formatted for readability:

    jsonMsg =
    "{
    "operation": "CreatePersistenceTopic",

    "persistenceTopic": "Sensor1",

    "databaseConnectionString": "FAIRCOMS@localhost",

    "tableName": "Sensor1Table",

    "tableAutoTimeStamp": true,

    "tableAutoTimeStampIndex": true,

    "mapOfPropertiesToFields":

    [

    {

    "jsonPropertyPath": "temperature_F",

    "fieldName": "temperature",

    "fieldType": "DOUBLE"

    },

    {

    "jsonPropertyPath": "humidity",

    "fieldName": "humidity",

    "fieldType": "DOUBLE"

    },

    {

    "jsonPropertyPath": "pressure_Hg",

    "fieldName": "pressure",

    "fieldType": "DOUBLE"

    }

    ]

    }"

  3. As soon as an MQTT message comes into FairCom EDGE with a matching topic, your table will be created in server/data/ctreeSQL.dbs/
  4. Open FairCom DB SQL Explorer (or any ODBC SQL query tool) on a PC and perform a query on the data:

    Select * from Sensor1Table where temperature > 72.0

TOCIndex