Log data from openweathermap.org with Python

The weather in Hamburg is always terrible. Cold, foggy, rainy. Really?

Recently I moved from southern Germany to Hamburg, to the “north”. Everybody said the weather is terrible there. Really? Even the people living in Hamburg are always complaining about the weather. But to be honest, I personally did not sense any negative differences. Really, I had the feeling the weather is better than in Bavaria. When I have told my colleagues about my personal assumptions, they always said: “No, you’re wrong. The weather is better in the south”.

Let’s create some facts, not believes, I thought.

I decided to log weather data from openweathermap.org from various cities. I used Python to to download and extract json data. The data will be inserted into into a MariaDB database. For the visualization and analysing part I use Metabase to create some nice dashboards. Here is how the design of the database looks like:

CREATE TABLE `weather_city` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `weather_data` (
  `id_city` int(11) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `temp` double NOT NULL,
  `pressure` double NOT NULL,
  `humidity` double NOT NULL,
  `windspeed` double NOT NULL,
  `clouds` double NOT NULL,
  `rain3h` double DEFAULT NULL,
  `snow3h` int(11) DEFAULT NULL,
  `sunrise` timestamp NULL DEFAULT NULL,
  `sunset` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Now here is the next part, how to extract the data from openweathermap.org with a python script and insert the data into the database:

import sys
import requests
import MySQLdb

def get_weather(api_key, city_id):
    url = "https://api.openweathermap.org/data/2.5/weather?id={}&units=metric&appid={}".format(city_id, api_key)
    print url
    r = requests.get(url)
    return r.json()
                          
def main():

    conn = MySQLdb.connect(
        user='USERNAME',
        passwd='PASSWORD',
        db='DATABASENAME',
        host='HOSTNAME',
        charset="utf8",
        use_unicode=True)
    cursor = conn.cursor()

    api_key = "YOURAPIKEYFROMOPENWEATHER" 

    sql = "SELECT * FROM `weather_city`"
    cursor.execute(sql) 
    cities = cursor.fetchall()

    city_ids = []

    for city in cities:
        city_ids.append (city[0])

    for city_id in city_ids:
        weather = get_weather(api_key, city_id)

        temp = weather["main"]["temp"]
        humidity = weather["main"]["humidity"]
        pressure = weather["main"]["pressure"]
        windspeed = weather["wind"]["speed"]
        clouds = weather["clouds"]["all"]
        sunrise = weather["sys"]["sunrise"]
        sunset = weather["sys"]["sunset"]
        timestamp = weather["dt"]

        sql = "INSERT INTO weather_data (id_city, timestamp, temp, pressure, humidity, windspeed, clouds, sunrise, sunset) VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s, %s, %s, FROM_UNIXTIME(%s), FROM_UNIXTIME(%s))"
        val = (city_id, timestamp, temp, pressure, humidity, windspeed, clouds, sunrise, sunset)
        cursor.execute(sql, val)
        conn.commit()

if __name__ == '__main__':
    main()

And here are the results: In Hamburg it is warmer than in Munich, which is 600 km more south!

And: Munich is much more cloudy than Hamburg. So, I was right, the rest is just fake news :-)