Grab Weather Station Data from Mysql and Put Into Thingspeak

Grab Weather Station Data from Mysql and Put Into Thingspeak

Grab weather station data from MySQL and put into thingspeak

Yesterday i started to learn python and decided to move data from local db to thingspeak - it looks like it is a sucessfull attempt .Maybe someone will find use of that script or someone will correct my errors because i'm not a python expert - yet.
Add to pi crontab via "crontab -e" to upload every 5 minutes:

Code: Select all

*/5 * * * * ~/thingspeak.py

thingspeak.py

Code: Select all

#!/usr/bin/python

# thingspeak.py - Send latest row form raspberry pi weather station database to Thingspeak

import MySQLdb

import sys

import httplib, urllib

key = "Put Your Thingspeak write key here"

db_username = 'Put your database username here - default was root'

db_password = 'Put your database password here'

connection = MySQLdb.connect ('localhost',db_username,db_password,'weather')

cursor = connection.cursor ()

cursor.execute ('select AMBIENT_TEMPERATURE,GROUND_TEMPERATURE,AIR_QUALITY, AIR_PRESSURE, HUMIDITY, WIND_DIRECTION, WIND_SPEED, WIND_GUST_SPEED, RAINFALL from WEATHER_MEASUREMENT order by id desc limit 1')

row = cursor.fetchone ()

print row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8]

temp_ambient = row[0]

temp_ground = row[1]

air_quality = row[2]

pressure = row[3]

humidity = row[4]

wind_dir = row[5]

wind_speed = row[6]

wind_gust = row[7]

rainfall = row[8]

print ("BMP180 temperature (air board): %d", temp_ambient)

print ("soil probe DS18B20 external sensor: %d", temp_ground)

print ("air quality (volaitale harmful gases): %d", air_quality)

print ("air pressure: %d", pressure)

print ("humidity: %d", humidity)

print ("wind directiona angle: %d", wind_dir)

print ("wind speed: %d", wind_speed)

print ("wind gust: %d", wind_gust)

print ("rainfall: %d", rainfall)

cursor.close ()

connection.close ()

params = urllib.urlencode({'field1': temp_ambient, 'field2' : temp_ground, 'field3' : air_quality, 'field4' : pressure, 'field5' : humidity, 'field6' : wind_speed, 'field7' : wind_dir, 'field8' : rainfall, 'key':key })

headers = {"Content-type": "application/x-www-form-urlencoded","Accept": "text/plain"}

conn = httplib.HTTPConnection("api.thingspeak.com:80")

try:

conn.request("POST", "/update", params, headers)

response = conn.getresponse()

print response.status, response.reason

data = response.read()

conn.close()

except:

print "connection failed"

sys.exit()

As you can see it works