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