MQTT

mqtt.py

import uuid
import datetime

## Query Insert into MQTT Table store unformatted data
insertIntoMQTT = (
    "insert into mqtt"
    "(channel, sf, time, gwip, gwid, repeater, systype, rssi, snr, snr_max, snr_min, macAddr, data, frameCnt, fport, devEUI)"
    "values (%(channel)s, %(sf)s, %(time)s, %(gwip)s, %(gwid)s, %(repeater)s, %(systype)s, %(rssi)s, %(snr)s, %(snr_max)s, %(snr_min)s, %(macAddr)s, %(data)s, %(frameCnt)s, %(fport)s, %(devEUI)s)")
## Query Insert into Parsed Table store formatted data
insertIntoParsed = (
  "insert into parsed"
  "(id, volume, debit, timestamp, device_eui)"
  "value (%(id)s, %(volume)s, %(debit)s, %(timestamp)s, %(device_eui)s"
)

## Parsing raw message payload from MQTT 
def parsingMessage(msg_json):
  for json_object in msg_json:
    # Prepare data (formatting)
    idData = str(uuid.uuid4())
    debit = int(json_object['data'], 16)
    volume = (debit / 60) * 1000
    timestamp = time.mktime(datetime.datetime.strptime(json_object['time'][:19], "%Y-%m-%dT%H:%M:%S").timetuple())

    # Data for MQTT Table
    rawData = json_object
    # Data for Parsed Table
    parsedDataRaw = '{"id":%d, "volume":%d, "debit":%d, "timestamp":%d, "device_eui":%d}' % (idData, volume, debit, timestamp, json_object['devEUI'])
    parsedData = json.loads(parsedDataRaw)
    # Insert data into Database 
    insertIntoDatabase(rawData, parsedData)

## Insert Data into Database 
def insertIntoDatabase(rawData, parsedData):
  # Execute query 
  thisCursor.execute(insertIntoMQTT, rawData)
  thisCursor.execute(insertIntoParsed, insertIntoParsed)
  # DB Commit
  db.commit()