Xiaomi Mi Flora collect data, store in SQLite and send to MQTT
Example of Xiaomi Mi Flora integration with a Raspberry Pi.
Collect data form a Xiaomi Mi Flora and publish to a MQTT broker and make them available to other aplications.
The collected data are stored in a local SQLite database.
Before insert data, create a SQLite database and push the inject node to create the table with the right format to insert the data.
All stored data can be recovered just pushing the inject node to show them in debug panel
[{"id":"a41622066f8cef7f","type":"tab","label":"Xiaomi Mi Flora","disabled":false,"info":"","env":[]},{"id":"345cd4c5064417ad","type":"Xiaomi BLE","z":"a41622066f8cef7f","name":"Flora","address":"80:EA:CA:60:05:05","scanningTimeout":60,"x":290,"y":100,"wires":[["9a3e64b08beb9fa0","4091eecea0330704","90e5b83236071410"]]},{"id":"8d0034fbf43a9182","type":"inject","z":"a41622066f8cef7f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"300","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":100,"wires":[["345cd4c5064417ad"]]},{"id":"9a3e64b08beb9fa0","type":"split","z":"a41622066f8cef7f","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"topic","x":450,"y":100,"wires":[["81c54697f7134491","fb64491859a117e3"]]},{"id":"81c54697f7134491","type":"debug","z":"a41622066f8cef7f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":570,"y":140,"wires":[]},{"id":"fb64491859a117e3","type":"change","z":"a41622066f8cef7f","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"\"cursomqtt/enrique/miflora/\"&topic","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":620,"y":100,"wires":[["3266f2685f1c87b7"]]},{"id":"3266f2685f1c87b7","type":"mqtt out","z":"a41622066f8cef7f","name":"","topic":"","qos":"","retain":"","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"33467520.52c7ca","x":790,"y":100,"wires":[]},{"id":"4091eecea0330704","type":"change","z":"a41622066f8cef7f","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"\"INSERT INTO datos_flora ('fecha','battery','temperature','light','moisture','conductivity') VALUES ('\"&\t$now(\t '[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]',\t '+0200'\t)&\"','\"&payload.battery&\"','\"&payload.temperature&\"','\"&payload.light&\"','\"&payload.moisture&\"','\"&payload.conductivity&\t\"')\"","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":140,"y":340,"wires":[["fdceac6b34fa58e7","d1cbe91895e87822"]]},{"id":"fdceac6b34fa58e7","type":"sqlite","z":"a41622066f8cef7f","mydb":"2542c44b886edf9f","sqlquery":"msg.topic","sql":"","name":"","x":430,"y":240,"wires":[["2a6a88060302c640"]]},{"id":"2a6a88060302c640","type":"debug","z":"a41622066f8cef7f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":650,"y":240,"wires":[]},{"id":"ecbcc6082b6fb88a","type":"comment","z":"a41622066f8cef7f","name":"Create table to store data","info":"","x":150,"y":180,"wires":[]},{"id":"282a0ae3ccfb76bb","type":"comment","z":"a41622066f8cef7f","name":"Insert data in SQLite","info":"","x":150,"y":300,"wires":[]},{"id":"b79664abb7b6fe53","type":"comment","z":"a41622066f8cef7f","name":"Collect data from Xiaomi Mi Flora and publish in MQTT","info":"","x":240,"y":60,"wires":[]},{"id":"fe776551465170e3","type":"inject","z":"a41622066f8cef7f","name":"Create Table","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE 'datos_flora' ( 'id'\tINTEGER UNIQUE, 'fecha' TEXT, 'battery' INTEGER, 'temperature' NUMERIC, 'light' INTEGER, 'moisture' INTEGER, 'conductivity' INTEGER, PRIMARY KEY('id' AUTOINCREMENT) )","x":130,"y":220,"wires":[["fdceac6b34fa58e7"]]},{"id":"d1cbe91895e87822","type":"debug","z":"a41622066f8cef7f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":360,"y":340,"wires":[]},{"id":"90e5b83236071410","type":"debug","z":"a41622066f8cef7f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":570,"y":180,"wires":[]},{"id":"002d75c0cd30e121","type":"inject","z":"a41622066f8cef7f","name":"Recover Data","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM datos_flora","x":150,"y":440,"wires":[["fdceac6b34fa58e7"]]},{"id":"d8305464be74f0db","type":"comment","z":"a41622066f8cef7f","name":"Show all data stored in database","info":"","x":210,"y":400,"wires":[]},{"id":"33467520.52c7ca","type":"mqtt-broker","name":"","broker":"aprendiendonodered.com","port":"8883","tls":"","clientid":"","autoConnect":true,"usetls":true,"compatmode":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"sessionExpiry":""},{"id":"2542c44b886edf9f","type":"sqlitedb","db":"/home/pi/BBDD/datos_flora.db","mode":"RWC"}]
