X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/d050dc1f022736a735a8cf1215635e90e615ae4a..refs/heads/master:/owm.py?ds=sidebyside

diff --git a/owm.py b/owm.py
index 74fe6c4..e538b2b 100755
--- a/owm.py
+++ b/owm.py
@@ -8,18 +8,18 @@
 
 # needed packaes: python3-mysqldb python3-sqlalchemy
 
-from pprint import pprint
 import argparse
 import configparser
-import os
 import csv
 import datetime
-import math
 import json
-import warnings
+import math
+import os
+from pprint import pprint
+
 import sqlalchemy
-from sqlalchemy import create_engine
-import MySQLdb.cursors
+from sqlalchemy import create_engine, Table, URL
+
 from seeparklib.openweathermap import openweathermap_json
 
 
@@ -28,7 +28,7 @@ def fromtimestamp(timestamp):
 
 
 # https://stackoverflow.com/questions/7490660/converting-wind-direction-in-angles-to-text-words
-def degToCompass(num):
+def deg_to_compass(num):
     if num is None or num is math.nan:
         return 'N/A'
     val=int((num/22.5)+.5)
@@ -36,7 +36,7 @@ def degToCompass(num):
     return arr[(val % 16)]
 
 
-def extractweatherdata(w):
+def extract_weather_data(w):
     data = dict(
         datetime = fromtimestamp(w['dt']),
         sunrise = fromtimestamp(w['sys']['sunrise']),
@@ -51,8 +51,8 @@ def extractweatherdata(w):
     )
 
     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
+    data['winddirection'] = deg_to_compass(data['winddegrees'])
+    data['precipitation'] = w['rain']['3h'] if 'rain' in w and w['rain'].get('3h') else math.nan
     data['visibility'] = w.get('visibility', math.nan)
 
     return data
@@ -67,11 +67,11 @@ def write_csv(csv_file, weather_data):
             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']),
+            f"{weather_data['temp']:.2f}",
+            f"{weather_data['precipitation']:.2f} mm/h",
+            f"{weather_data['windspeed']:.1f} km/h {weather_data['winddirection']}",
             weather_data['weather'],
-            "{}".format(weather_data['cloudiness'])
+            f"{weather_data['cloudiness']}"
         ])
 
 
@@ -81,21 +81,19 @@ def write_db(config, url, weather_json, weather_data):
     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 ignore into openweathermap ({}) values ({})'.format(', '.join(sql_columns), ','.join(['%s'] * len(sql_columns)))
-    with warnings.catch_warnings():
-        # ignore _mysql_exceptions.Warning: Duplicate entry '3319578-2018-08-01 20:50:00' for key 'cityid_datetime'
-        warnings.simplefilter("ignore", category=MySQLdb.cursors.Warning)
-        conn.execute(sql, *sql_values)
-    conn.close()
+    db_url = URL.create(drivername='mysql+mysqldb', username=user, password=pwd, host=host, database=db)
+    engine = create_engine(db_url, echo=False)
+    with engine.connect() as conn:
+        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
+        metadata = sqlalchemy.MetaData()
+        openweathermap_table = Table('openweathermap', metadata, autoload_with=engine)
+        ins = openweathermap_table.insert().prefix_with('IGNORE').values(**row)
+        conn.execute(ins)
+        conn.commit()
 
 
 def main(configfile, debug):
@@ -108,7 +106,7 @@ def main(configfile, debug):
     url, weather_json = openweathermap_json(apikey, cityid)
     if debug:
         pprint(weather_json)
-    weather_data = extractweatherdata(weather_json)
+    weather_data = extract_weather_data(weather_json)
     if debug:
         pprint(weather_data)