Psycopg is the most popular PostgreSQL adapter for Python, and it can be used to create Python scripts that send SQL commands to PostGIS. In this recipe, you created a Python script that queries weather data from the https://openweathermap.org/ web server using the popular JSON format to get the output data and then used that data to update two PostGIS layers.

For one of the layers, cities, the weather data is used to update the temperature field using the temperature data of the weather station closest to the city. For this purpose, you used an UPDATE SQL command. The other layer, wstations, is updated every time a new weather station is identified from the weather data and inserted in the layer. In this case, you used an INSERT SQL statement.

This is a quick overview of the script's behavior (you can find more details in the comments within the Python code). In the beginning, a PostgreSQL connection is created using the Psycopg connection object. The connection object is created using the main connection parameters (dbname, user, and password, while default values for server name and port are not specified; instead, localhost and 5432 are used). The connection behavior is set to auto commit so that any SQL performed by Psycopg will be run immediately and will not be embedded in a transaction.

Using a cursor, you first iterate all of the records in the cities PostGIS layer; for each of the cities, you need to get the temperature from the https://openweathermap.org/ web server. For this purpose, for each city you make a call to the GetWeatherData method, passing the coordinates of the city to it. The method queries the server using the requests library and parses the JSON response using the simplejson Python library.

You should send the URL request to a try...catch block. This way, if there is any issue with the web service (internet connection not available, or any HTTP status codes different from 200, or whatever else), the process can safely continue with the data of the next city (iteration).

The JSON response contains, as per the request, the information about the 10 weather stations closest to the city. You will use the information of the first weather station, the closest one to the city, to set the temperature field for the city.

You then iterate all of the station JSON objects, and by using the AddWeatherStation method, you create a weather station in the wstation PostGIS layer, but only if a weather station with the same id does not exist.