Bulk CSV upload to ThingSpeak with Python
23 views (last 30 days)
Show older comments
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 Comments
Accepted Answer
Christopher Stapels
on 6 Apr 2020
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 Comments
Christopher Stapels
on 8 Apr 2020
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.
More Answers (2)
Christopher Stapels
on 1 Apr 2020
Edited: Christopher Stapels
on 1 Apr 2020
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)
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?
Christopher Stapels
on 2 Apr 2020
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.
Communities
More Answers in the ThingSpeak Community
See Also
Categories
Find more on REST API in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!