· 6 years ago · Aug 21, 2019, 04:10 PM
1For some time now, I have been able to use the pubsub arduino client to send data to a mosquitto MQTT broker to a node red server, and see a "live feed" using node red dashboard with no problems as sent to the brokerfrom the arduino, (but there is no storage, if I reboot the server, the time related graphs start over. I was hoping to expand my learning by getting into flask or another web framework in python, and thought I could just use a gardening MQTT feed I use with node red dashboard as the basis.
2
3I assumed my first step should be to store the stream data into a database, using the paho mqtt library and for my learning, I figured SQLlite3 would be just as good as any. I cannot seem to upload to (INSERT INTO) the database (database is created correctly,table is made ok in my code, and if I manually assign data to it, it completes the insert into table just fine. If I am trying to use the stream as sent by the arduino code above, it will create the database, make the table, and not actually insert into the table, so I am sure on the python end, I am not doing something right. (I am a hobbyist) No error messages, and the paho. On the SQL lite I have tried various data types, BLOB, NUMERIC, STRING, REAL, nothing mattered, unless I simply told the program You will see the commented out items where I did it succesfully. I just can't seem to get this done by the stream from the arduino. I have to be doing something wrong on data types?
4
5ARDUINO PART using the pubsub client arduino library:
6
7 client.publish("TDS1", String(tdsValue).c_str(), true);
8 client.publish("Temp1", String(temperaturef).c_str(), true);
9 client.publish("PH", String(phValue).c_str(), true);
10
11PYTHON PART using the paho mqtt library for python. In desperation, I just tried it making everything a string, but it has not worked when I make the variables a float, create the table with REAL, etc... This is a last try I did trying with just data as strings.
12
13conn = sqlite3.connect('hydroponics.db')
14c=conn.cursor()
15print ("opened database successfully")
16#conn.execute("CREATE TABLE IF NOT EXISTS HYDRO(UNIX TEXT, TIMESTAMP TEXT, PH1 TEXT, TEMP1 TEXT, TDS1 TEXT, PH2 TEXT, TEMP2 TEXT, TDS2 TEXT)")
17conn.execute("CREATE TABLE IF NOT EXISTS HYDRO(TDS1 REAL, TEMP1 REAL, PH1, REAL)")
18print("Table HYDRO created successfully") # This works
19c.close
20conn.close()
21
22# The callback for when a PUBLISH message is received from the server.
23def on_message(client, userdata, msg):
24 #print(msg.topic+" "+ str(msg.payload)) #WHen I do this, the data comes in as b 'some number with a decimal'
25
26 if msg.topic==("TDS1"):
27 #TD1=BytesIO(TD1) just something I tried
28 #TD1=str(TD1)
29 #TD1=float(TD1)
30 print("TDS1: " + str(msg.payload)+ " PPM")
31 TD1 = str(msg.payload.decode('utf-8')).strip()#('utf-8') #assumed I had to 'Decode somehow, and did a strip in case #there was some inadventent carriage return or something I couldn't see?
32 TD1=float(TD1)
33 print("TDS1: " + TD1 + " PPM")
34 if msg.topic==("Temp1"):
35 TMP1 = str(msg.payload.decode('utf-8')).strip()
36 #TMP1=BytesIO(TMP1)
37 #TMP1=str(TMP1)
38 TMP1=float(TMP1)
39 if msg.topic==("PH"):
40 PH01= str(msg.payload.decode('utf-8')).strip()
41 #PH01=BytesIO(PH01)
42 #PH01=str(PH01)
43 PH01=float(PH01)
44 print("PH1: " + PH01)
45 if msg.topic==("TDS2"):
46 TD2 = msg.payload.decode('ascii')
47 #TD2=str(TD2)
48 #TD2=float(TD2)
49 print("TDS2: " + TD2 +" PPM")
50 if msg.topic==("Temp2"):
51 TMP2 = msg.payload.decode('ascii')
52 #TMP2=str(TMP2)
53 #TMP2=float(TMP2)
54 print("TEMP2: " + TMP2 + " F")
55 if msg.topic==("PH2"):
56 PH02= msg.payload.decode('ascii')
57 #PH02=str(PH02)
58 #PH02=float(PH02)
59 print("PH2: " + PH02)
60 TS = time.time()
61 TS =datetime.datetime.fromtimestamp(TS).strftime('%Y-%m-%d %H:%M:%S')
62 TS=str(TS)
63 print("TIMESTAMP " + TS)
64 #UNI='12345'
65 #TS='2016-01-11 13:53:39'
66 #PH01=6.18
67 #TMP1=76.4
68 #TD1=1328
69 #PH02=7.1
70 #TMP2=67.9
71 #TD2=1148
72 print("Uploading to Database")
73 conn = sqlite3.connect('hydroponics.db')
74 print ("opened database successfully") #Program stops here..no error message
75 c=conn.cursor()
76 #c.execute("INSERT INTO HYDRO VALUES(12345,'2016-01-11 13:53:39',6.18,76.4,1328,7.1,67.9,1148)")
77 #c.execute("INSERT INTO HYDRO VALUES(?,?,?,?,?,?,?,?)",(UNI,TS,PH01,TMP1,TD1,PH02,TMP2,TD2))
78 c.execute("INSERT INTO HYDRO VALUES(?,?,?)",(TD1,TMP1,PH01))
79 conn.commit()
80 print ("Records created successfully")
81 c.close()
82 conn.close()
83
84
85This code does run all the way down on the if statements to "PH2" and I get the print ("opened database successfully"), nothing more, unless I manuyally uncomment the variables dierectly above it.