c-treeEDGE IoT Database

Tutorial for Using Python with c-treeEDGE MQTT

This tutorial demonstrates collecting sensor data in Python on the Raspberry Pi and storing the data in the c-treeACE 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 c-treeEDGE on the Pi. The example uses Python 3 to read a sensor and publish the value via MQTT to c-treeEDGE. For more information about MQTT, see MQTT Broker later in this document.

You will need to install and start c-treeEDGE database, as described in Download and Installation.

To use MQTT for messaging from Python to c-treeEDGE 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 c-treeEDGE 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.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 c-treeEDGE 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.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.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 c-treeEDGE with a matching topic, your table will be created in server/data/ctreeSQL.dbs/
4. Open c-treeACE 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