MySQL

To log the sensor data my Raspberry Pi Zero W collects, I am using a MySQL Table with three columns for each data type:

Date

Time

Collected Data (in this example "randNumber")

---

Note: The following code runs on the Raspberry Pi 2 which receives and processes the data sent by the Raspi Zero W

To upload the data into the table I am using "MySQL Connector"

~$ python pip3 install mysql-connector

In the file I use the following code to login:

import mysql.connector

mydb = mysql.connector.connect(
        host="188.**.**.***",
        port="3306",
        user="***063_pi",
        passwd="******",
        database="***063_test"
)

and to upload the data I received through mqtt (msg.payload, random Number to simulate sensor data):

sql = "INSERT INTO test (Date, Time, randNumber) VALUES (%s, %s, %s)"   #Tabel: test; Columns: Date,Time,randNumber
val = (date, timeNow, int(msg.payload))       #Date: YYYY/MM/DD
mycursor.execute(sql, val)
mydb.commit()

The program results in the following output on the Raspberry Pi 2:

[test_channel] 17:45:01 -  4
[test_channel] 17:45:16 -  5
[test_channel] 17:45:31 -  4
[test_channel] 17:45:47 -  10
[test_channel] 17:46:02 -  4
[test_channel] 17:46:17 -  10
[test_channel] 17:46:32 -  5
[test_channel] 17:46:47 -  10
[test_channel] 17:47:02 -  1
[test_channel] 17:47:17 -  8
[test_channel] 17:47:32 -  8
[test_channel] 17:47:47 -  4

and therefore the matching input into the MySQL table: