#!/usr/bin/python
# -*- coding: utf8 -*-

import json, os, math
import MySQLdb

os.chdir('/home/pi/tmp')

db = MySQLdb.connect(host="localhost", user="<mein user>",
            passwd="<mein passwd>", db="<meine db>")
cur = db.cursor()

add_wetter = ("insert into wetter "
             "(datum, temp, feuchte, wolken, wind, richtung, regen) "
             "VALUES (%s, %s, %s, %s, %s, %s, %s)")

with open('forecast.json') as fc_file:
  df = json.load(fc_file)

for n in range (0, 2):
  datum = df['list'][n]['dt_txt']
  datum2 = datum[5:7] + datum[8:10]
  zeit = datum[11:13]
#  print datum2 + zeit
  temp = df['list'][n]['main']['temp'] - 273.15
  feuchte = df['list'][n]['main']['humidity']
  desc = df['list'][n]['weather'][0]['description']
  wind = df['list'][n]['wind']['speed'] * 3.6
  wrichtg = df['list'][n]['wind']['deg']
  try:
    nied = df['list'][n]['rain']['3h']
  except KeyError:
    nied = 0
  data_wetter = (datum, temp, feuchte, desc, wind, wrichtg, nied)

  cur.execute(add_wetter, data_wetter)

  if zeit == "09" or zeit == "12" or zeit == "15":
    print zeit
    id_last = cur.lastrowid
    read_wetter = ("select * from wetter where id = %s")
    cur.execute (read_wetter, id_last)
    results = cur.fetchall()
    for row in results:
      recdate = str(row[1])
      datum2 = recdate[5:7] + recdate[8:10]
      zeit = recdate[11:13]
      data_sql = str(row[2]) + "," + str(row[3]) + "," + row[4] + "," + str(row[5]) + "," + str(row[6]) + "," + str(row[7])
      print data_sql
      fname = '/home/pi/wetter/wetter' + datum2 +zeit + '.txt'
      f = open (fname, 'w')
      f.write(data_sql)
      f.close

db.commit()

cur.close()
db.close ()