Implement writing openweathermap data to database.

diff --git a/owm.py b/owm.py
index 6f7ba24c720e245e908ab718ffe5b3d02322c038..c41c3198039ba9526f8b158a79d87c06c32ccff4 100755 (executable)
--- a/owm.py
+++ b/owm.py
@@ -13,11 +13,13 @@ import os
 import csv
 import datetime
 import math
+import json
+from sqlalchemy import create_engine
 from seeparklib.openweathermap import openweathermap_json
 
 
-def fromtimestamp(timestamp, format):
-    return datetime.datetime.fromtimestamp(timestamp).strftime(format)
+def fromtimestamp(timestamp):
+    return datetime.datetime.fromtimestamp(timestamp)
 
 
 # https://stackoverflow.com/questions/7490660/converting-wind-direction-in-angles-to-text-words
@@ -31,9 +33,9 @@ def degToCompass(num):
 
 def extractweatherdata(w):
     data = dict(
-        datetime = w['dt'],
-        sunrise = w['sys']['sunrise'],
-        sunset = w['sys']['sunset'],
+        datetime = fromtimestamp(w['dt']),
+        sunrise = fromtimestamp(w['sys']['sunrise']),
+        sunset = fromtimestamp(w['sys']['sunset']),
         temp = w['main']['temp'],
         pressure = w['main']['pressure'],
         humidity = w['main']['humidity'],
@@ -43,10 +45,6 @@ def extractweatherdata(w):
         cloudiness = w['clouds']['all'],
     )
 
-    data['sunrise_t'] = fromtimestamp(data['sunrise'], '%H:%M:%S')
-    data['sunset_t'] = fromtimestamp(data['sunset'], '%H:%M:%S')
-    data['date'] = fromtimestamp(data['datetime'], '%Y-%m-%d')
-    data['time'] = fromtimestamp(data['datetime'], '%H:%M:%S')
     data['winddegrees'] = w['wind']['deg'] if 'deg' in w['wind'] else math.nan
     data['winddirection'] = degToCompass(data['winddegrees'])
     data['precipitation'] = w['rain']['3h'] if 'rain' in w else math.nan
@@ -60,10 +58,10 @@ def write_csv(csv_file, weather_data):
     with open(csv_file, "a", newline="") as file:
         writer = csv.writer(file, dialect="excel", delimiter=';')
         writer.writerow([
-            weather_data['date'],
-            weather_data['time'],
-            weather_data['sunrise_t'],
-            weather_data['sunset_t'],
+            weather_data['datetime'].date(),
+            weather_data['datetime'].time(),
+            weather_data['sunrise'].time(),
+            weather_data['sunset'].time(),
             "{:.2f}".format(weather_data['temp']),
             "{:.2f} mm/h".format(weather_data['precipitation']),
             "{:.1f} km/h {}".format(weather_data['windspeed'], weather_data['winddirection']),
@@ -72,6 +70,27 @@ def write_csv(csv_file, weather_data):
         ])
 
 
+def write_db(config, url, weather_json, weather_data):
+    user = config.get('database', 'user')
+    pwd = config.get('database','password')
+    host = config.get('database','hostname')
+    db = config.get('database','database')
+
+    engine = create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db), echo=False)
+    conn = engine.connect()
+    row = dict(cityid=config.get('openweathermap', 'cityid'), url=url, result=json.dumps(weather_json))
+    row.update(weather_data)
+    for key, value in row.items():
+        if isinstance(value, float) and math.isnan(value):
+            row[key] = None
+    sql_columns = list(row.keys())
+    sql_values = list(row.values())
+    sql = 'insert into openweathermap ({}) values ({})'.format(', '.join(sql_columns), ','.join(['%s'] * len(sql_columns)))
+    print(sql)
+    conn.execute(sql, *sql_values)
+    conn.close()
+
+
 def main(configfile, debug):
     config = configparser.ConfigParser()
     config.read(configfile)
@@ -79,18 +98,18 @@ def main(configfile, debug):
     cityid = config.get('openweathermap', 'cityid')
     csvfile = config.get("openweathermap", 'csvfilename')
 
-    weather_raw = openweathermap_json(apikey, cityid)
+    url, weather_json = openweathermap_json(apikey, cityid)
     if debug:
-        pprint(weather_raw)
-    weather = extractweatherdata(weather_raw)
+        pprint(weather_json)
+    weather_data = extractweatherdata(weather_json)
     if debug:
-        pprint(weather)
+        pprint(weather_data)
 
-    # TODO:
     # write to db
+    write_db(config, url, weather_json, weather_data)
 
     # write to csv
-    write_csv(os.path.expanduser(csvfile), weather)
+    write_csv(os.path.expanduser(csvfile), weather_data)
 
 
 if __name__ == '__main__':

diff --git a/seeparklib/openweathermap.py b/seeparklib/openweathermap.py
index d350eb6d7eb57bdccba0e2d0bc599c9ff37316b4..626f77ed319f5e80cd6e518de1b36931c0bdd0de 100644 (file)
--- a/seeparklib/openweathermap.py
+++ b/seeparklib/openweathermap.py
@@ -9,12 +9,12 @@ def openweathermap_json(apikey, cityid):
     """Returns parsed JSON as returned by openweathermap for the given cityid.
     In case of errors, an OpenWeatherMapError is raised."""
     baseurl = 'http://api.openweathermap.org/data/2.5/weather'
-    query = baseurl + '?units=metric&APPID={}&id={}&lang=de'.format(apikey, cityid)
+    url = baseurl + '?units=metric&APPID={}&id={}&lang=de'.format(apikey, cityid)
     try:
-        response = requests.get(query)
+        response = requests.get(url)
         if response.status_code != 200:
             raise OpenWeatherMapError('Got status code {} ({}).'.format(response.status_code, response.reason))
         else:
-            return response.json()
+            return url, response.json()
     except requests.exceptions.RequestException as error:
         raise OpenWeatherMapError('Request not successful: {}'.format(error))

diff --git a/web/seepark_web.py b/web/seepark_web.py
index c19e9b92e177cfac69d57eadd6f9ba9cca5d0a6a..e8b9da11dc6f0b4b20ca6c44841cc22825acb213 100644 (file)
--- a/web/seepark_web.py
+++ b/web/seepark_web.py
@@ -132,7 +132,7 @@ def sensordata(sensor_id=None, sensor_type=None):
 def currentairtemperature(apikey, cityid):
     """Retruns the tuple temperature, datetime (as float, datetime) in case of success, otherwise None, None."""
     try:
-        weatherdata = openweathermap_json(apikey, cityid)
+        url, weatherdata = openweathermap_json(apikey, cityid)
         return weatherdata['main']['temp'], datetime.datetime.fromtimestamp(weatherdata['dt'])
     except OpenWeatherMapError:
         return None, None