FairCom Edge IoT Database

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 FairCom DB utilizing MQTT (Message Queuing Telemetry Transport).

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 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