MATLAB Answers

Bulk CSV upload to ThingSpeak with Python

52 ビュー (過去 30 日間)
Jenna R
Jenna R 2020 年 4 月 1 日
編集済み: Jenna R 2020 年 4 月 20 日
Hello all,
For context to my issue, I have a Raspberry Pi successfully uploading pH, turbidity, and temperature sensor data to ThingSpeak using a basic program. I was asked to implement a way for the results to be uploaded even if there was no internet connectivity, so in Python, the results should save to a CSV file which will later be automatically uploaded when the program runs and has internet connectivity again. So, the logic is:
  • If the Pi has internet, upload the sensor readings to ThingSpeak using the POST method. That's all.
  • If the Pi does not have internet, save the readings to a CSV file.
  • When the program is ran again, if the internet is still disconnected, it will save the readings to the CSV again by amending the file (not overwriting it).
  • When the program is ran again, if the internet is connected, it will automatically upload the CSV file to ThingSpeak using the POST bulk-write CSV method.
I have been searching the web for example code on how to get the bulk CSV upload for ThingSpeak working in Python. Most of the people asking for help are using the JSON method or Arduino code, but the ones who are using POST and Python (like I will be) have fixed their own issue without really going into detail on how they fixed it. I have checked the API thoroughly, but it is still confusing to me, even after checking out the examples that were in the MATLAB (such as this one: https://uk.mathworks.com/help/thingspeak/continuously-collect-data-and-bulk-update-a-thingspeak-channel-using-a-raspberry-pi-board.html )
Let me say that I am still quite new to Python/ThingSpeak. I've done my best to try to understand how the single write process works and apply it to the bulk write process, but I'm just drawing a blank. I guess my main question is that the URL in the API doesn't make sense to me: https://api.thingspeak.com/channels/channel_id/bulk_update.csv
I understand I'm supposed to put my channel ID into the URL, but how does my CSV file data apply to the "bulk_update.csv" part of the URL? Am I supposed to read in the results of the CSV and insert them into the URL? Am I thinking about this the wrong way? For reference, here's the Python code I use to upload single entries (I cut out the parts of code that don't apply). Any help is greatly appreciated.
import http.client
import urllib
import time
import serial
#Assign ThingSpeak read API key to a variable
apiKey = 'XXXXXXXXX'
def readSensors(): #Read sensor data
(Code omitted, but it basically just grabs sensor data from the Arduino)
return pH, turbidity, temperature
def uploadReadings(): #ThingSpeak upload
pH, turbidity, temperature = readSensors()
params = urllib.parse.urlencode({'field1': pH, 'field2': turbidity, 'field3': temperature, 'key':apiKey})
headers = {"Content-type": "application/x-www-form-urlencoded","Accept": "text/plain"}
conn = http.client.HTTPConnection("api.thingspeak.com:80")
try:
print("Attempting to contact ThingSpeak")
conn.request("POST", "/update", params, headers)
response = conn.getresponse()
print ("Upload status: ", response.status, response.reason)
conn.close()
except:
print("Connection failed")
while True:
uploadReadings()
break

  0 件のコメント

サインインしてコメントする。

採用された回答

Christopher Stapels
Christopher Stapels 2020 年 4 月 6 日
When I write
write_api_key=xxxxxxxxxxxxxxxx&time_format=absolute&updates=2020-04-03T10:26:23+0100,1.2,2.3,3,4,5,6,7,8,42.0,0.5,50,falling|2020-04-04T12:12:22+0100,1,,3,0.4,1.5,1.6,,1.8,40.0,5.4,0,wet
I get
"feeds": [{"created_at": "2020-04-03T06:26:23-04:00","entry_id": 1,"field1": "1.2","field2": "2.3","field3": "3",....etc
I read the top write statement to say the first event happened at 10:26 in gmt+1. In Eastern standatrd Time (US) thats 6:26 am.
When I change my time zone to utc+1, for the channel data I get:
"created_at": "2020-04-03T12:26:23+02:00","entry_id": 1,"field1": "1.2",...etc
Which is 10:26 in UTC.
Can you provide the exact syntax of your body for the POST? (except the api key). Then I can try it, becasue +0100 seems to work for me.

  7 件のコメント

表示 4 件の古いコメント
Christopher Stapels
Christopher Stapels 2020 年 4 月 6 日
I'm using POSTMAN, but Ive also tested this in curl and on arduino based devices.
I think you dropped the url encoding from your python code way at the top:
urllib.parse.urlencode({'f....
If you dont URL encode the POST string, the http client will send information that our server cant understand (such as the + character)
Jenna R
Jenna R 2020 年 4 月 6 日
Bingo!! That fixed it. Christopher, thank you so much for your help. I took out the urllib encoding because at first, I didn't think the bulk-write CSV API needed it. Faulty logic, I know, but I was just experimenting with all sorts of things to get it to work. I didn't give it a second thought once I got the bulk-write CSV to work without the url encoding and without timezone offsetting. Then, since the minus timezone offsets were working, I thought there was a bug with the + instead. Never even thought about putting the encoding back in. I feel so silly!
I sincerely apologize for wasting so much of your time with this issue, but if any good can come from it, I really hope this helps someone else in the future.
Also, to help anyone else in the future, I'll share more of my code now (at least the parts that were discussed in this thread). It's far from being final and very much still a work-in-progress from a beginner in Python who threw it together in a hurry to see if it all works. I can at least confirm it does work and hope that it helps anyone else trying to achieve similar results.
import http.client
import urllib
import time
import os
import csv
#Assign ThingSpeak API key to a variable
apiKey = 'XXXXXXXXXXXXX' #Put your API key here
def checkForFile():
if os.path.isfile("/home/pi/Desktop/CSV Testing/resultWrite.csv"): #If the CSV file exists
try: #Read the CSV results in and prepare for upload
print("A previous results file was found. Processing...")
csvResults = ""
with open('resultWrite.csv', 'r') as readFile:
reader = csv.DictReader(readFile)
for row in reader: #for each row in the CSV
print(row['datetime'], row['field1'], row['field2'], row['field3'], row['field4']) #See the rows in CSV file
timestamp = row['datetime']
pHResult = row['field1']
turbidityResult = row['field2']
temperatureResult = row['field3']
qualityResult = row['field4']
csvResults += (f"{timestamp}, {pHResult}, {turbidityResult}, "
f"{temperatureResult}, {qualityResult}|") #This will append each row to a string
print(csvResults) #See the results after they have been formatted for the upload, they should be formatted like this: datetime, field1, field2, field3, field4 (but with actual data)
csvParams = urllib.parse.urlencode({'write_api_key': apiKey,
'time_format': "absolute", 'updates': csvResults})
csvHeaders = {"Content-Type": "application/x-www-form-urlencoded"}
csvConn = http.client.HTTPConnection("api.thingspeak.com:80")
print("Attempting to write CSV to ThingSpeak")
csvConn.request("POST", "/channels/CHANNELID/bulk_update.csv", csvParams, csvHeaders) #Use your own channel ID
csvResponse = csvConn.getresponse()
print("Upload status: ", csvResponse.status, csvResponse.reason)
print("CSV upload successful!")
os.remove("/home/pi/Desktop/CSV Testing/resultWrite.csv") #Delete the CSV file after successful upload
except: #If ThingSpeak cannot be reached
print("Error with trying to write CSV data to ThingSpeak. Continuing with test...")
else: #If no CSV file exists
print("No file found. Moving on to the rest of the program...")
Christopher Stapels
Christopher Stapels 2020 年 4 月 8 日
This is the final post in the thread! I was just reading through things sequentially and it was really hard to follow. Im just putting this here in case anyone is reading for the solution. Look at the dates on the posts.
@Jenna R- Im totally happy to help, and I appreciate you pointing out how complex these things get (and helping me work through my test strategy) . It will help us at design time when we get a chance to improve the feature.

サインインしてコメントする。

その他の回答 (2 件)

Christopher Stapels
Christopher Stapels 2020 年 4 月 1 日
編集済み: Christopher Stapels 2020 年 4 月 1 日
The documentation page explains where to put the channel ID. The Hackster article you mentioned on the forum is the correct one "Solar Powered Cellular Weather Station".
Your HTTP request is misformatting the body. The data goes in the section where you are writing the params.
conn.request("POST", "/update", params, headers)
The format of the csv data is specified in the documentation page for bulk CSV.
Here is an example
write_api_key=XXXXXXXXXXXXXXXX&time_format=absolute&updates=2018-06-14T12:12:22-0500,1,,3,0.4,1.5,1.6,,1.8,40.0,5.4,0,wet|2018-01-30T10:26:23-0500,1.2,2.3,3,4,5,6,7,8,42.0,0.5,50,falling
What are you seeing when you run this code?

  1 件のコメント

Jenna R
Jenna R 2020 年 4 月 1 日
Hi Christopher, thanks for responding so quickly!
The code I shared was just to show how I'm doing the single data uploads for ThingSpeak with Python, I have not yet modified it to do the CSV uploads because I'm confused with how the API is telling me to write it, since it seems to be much different than how I'm doing it now (and maybe it's not... beginner here, still trying to get my head around it).
The example you suggested is what I've been seeing to use, but I don't know where I'm supposed to put that information... I understand it goes along with the URL, so does it just go into params? For example:
params = "write_api_key=XXXXXXXXXXXXXXXX&time_format=absolute&updates=2018-06-14T12:12:22-0500,1,,3,0.4,1.5,1.6,,1.8,40.0,5.4,0,wet|2018-01-30T10:26:23-0500,1.2,2.3,3,4,5,6,7,8,42.0,0.5,50,falling"
Once I figure out how to format it, I'd be glad to try it out and let you know what happens when I run the code.
Also, this may sound silly, but I'm still not grasping the concept of how the CSV file gets imported into ThingSpeak, even after reading through the API. Everything is just showing how you can import bulk entries into ThingSpeak, but doesn't Python first have to read the CSV file values in, then upload those values into ThingSpeak? I hope this question makes sense.
Thanks again for your help!

サインインしてコメントする。


Christopher Stapels
Christopher Stapels 2020 年 4 月 2 日
You put the values from the csv file into the params.
so if your csv file says
date, field1, field 2,
2020-01-01 12:00:00,10, 20,
2020-01-01 13:00:00,11,12
then you make params="write_api_key=XXXXXXXXXXXXXXXX&time_format=absolute&updates=2020-01-01 12:00:00,10,20,,,,,,,,,,|2020-01-01 13:00:00,11,12.,,,,,,,,,,"
not 100% sure you need the trailing commas if there is nothing in those fields- check the doc page to be sure.

  1 件のコメント

Jenna R
Jenna R 2020 年 4 月 5 日
Hi there Christopher,
Thanks for your help and sorry for a delayed response! I have actually been working on this the past few days, I was having trouble, but finally got my head around it and it is working. I'm still working on the code so that I can post it here and help anyone else who might need some assistance. However, I am stuck on something.
When my CSV data gets uploaded into ThingSpeak, it's showing the wrong timezone on the data points when I look at the charts. The data charts say that they are GMT+1:00, which is my timezone (Dublin and we are on +1 now because of the summer time change, otherwise we are equal to GMT), but they are an hour ahead of what they should be. For example, if my data was recorded at 4:15:30, it will show as 5:15:30. Here's what I've done to try to fix the issue:
Made sure my profile is set to the correct timezone.
When writing to the CSV, I had my datetime going in as this format: 2020-01-01 12:00:00
So then I changed it to 2020-01-01 12:00:00+01:00 to see if that helped. It did not make any difference, so I thought it must be wrong, but I tried 2020-01-01 12:00:00-01:00 and that made the datapoint show up 2 hours ahead instead of 1 hour ahead. So the syntax is right, and I can do all the UTC offsets I want if I do a minus amount (-01:00, -02:00, and so on), but if I try to do one with a plus (+01:00, +02:00), it does not work. I've even tried "UTC+01:00" and "GMT+01:00" to no avail. I checked all of ThingSpeak's documentation on timezone formatting, but it looks like it should work... and it's not.
I have also taken a CSV file and uploaded it directly through the web browser with the 2020-01-01 12:00:00+01:00 format and it WORKS! So is it a bug with the bulk-write CSV API? Again, I'm able to do 2020-01-01 12:00:00-01:00 through the bulk-write CSV API, but I can't do 2020-01-01 12:00:00+01:00. It doesn't error out or anything, it just ignores it and acts like it's still 2020-01-01 12:00:00 without an offset.
Any advice on how to get it to recognize that I need a +01:00 offset on the timestamp?

サインインしてコメントする。

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by