Troubles with API for bulk uploads to update building attributes

[traveller195]

Hello everybody @tomalrussell @matkoniecz ,

I just try to update some building attributes from a python script via API.
I tried several options for API authentification and - fortunately - I just found this file in one of your issues on GitHub:

https://github.com/colouring-cities/colouring-london/blob/54a40d3d8c07af5b90d0c3f979a0c561e0f931b6/etl/join_building_data/load_csv.py

So, I tried it with your provided way to update buildings but all the time I only get the error response:
500 {'error': 'Server error'}

For all GET requests it worked fine, also from the same python script. But POST for update buildings is not working.
Do you have any nice idea what the problem could be? Maybe the problem is, because our staging server/ test environment is only accessible internally and not from WWW ?

you will find my current script below.
thanks in advanve,
Theodor

---

import csv
import json
import os
import sys
import argparse

import requests
from retrying import retry



@retry(wait_exponential_multiplier=1000, wait_exponential_max=10000)
def update_building(building_id, data, api_key, base_url):
    """Save data to a building
    """
    r = requests.post(
        "{}/api/buildings/{}.json".format(base_url, building_id),
        params={'api_key': api_key},
        json=data
    )
    return r.status_code, r.json()




data_upload = {'building_id': 41106, 'ref_toid': 'DESNALK0q80045s2', 'ref_osm_id': None, 'geometry_id': 41106, 'revision_id': '5784', 'location_name': 'TESTDATA', 'location_number': None, 'location_street': None, 'location_line_two': None, 'location_town': None, 'location_postcode': None, 'location_latitude': None, 'location_longitude': None, 'date_year': 2002, 'date_lower': 2000, 'date_upper': 2002, 'date_source': 'Andere Webseite', 'date_source_detail': None, 'facade_year': 2002, 'facade_upper': None, 'facade_lower': None, 'facade_source': None, 'facade_source_detail': None, 'size_storeys_attic': None, 'size_storeys_core': None, 'size_storeys_basement': None, 'size_height_apex': 21.5, 'size_floor_area_ground': None, 'size_floor_area_total': None, 'size_width_frontage': None, 'likes_total': 0, 'planning_portal_link': '', 'planning_conservation_area_name': '', 'planning_list_date': None, 'planning_list_amend_date': None, 'planning_conservation_area_delegation_date': None, 'planning_conservation_area_update_date': None, 'planning_conservation_area_update_type': 'Unknown', 'planning_in_list': False, 'planning_list_id': None, 'planning_list_cat': 'None', 'planning_list_grade': 'None', 'planning_world_list_id': None, 'planning_glher_url': '', 'planning_local_list_url': '', 'planning_historic_area_assessment_url': '', 'date_link': ['https://de.wikipedia.org/wiki/Altmarkt-Galerie'], 'sust_breeam_rating': None, 'sust_breeam_date': None, 'sust_dec_date': None, 'sust_dec': None, 'sust_aggregate_estimate_epc': None, 'sust_retrofit_date': None, 'sust_embodied_carbon': None, 'sust_life_expectancy': None, 'sust_lifespan_average': None, 'building_attachment_form': None, 'date_change_building_use': None, 'current_landuse_order': None, 'current_landuse_group': None, 'current_landuse_class': None, 'construction_core_material': None, 'construction_secondary_materials': None, 'construction_roof_covering': None, 'demolished_buildings': [], 'dynamics_has_demolished_buildings': None, 'community_local_significance_total': 0, 'community_activities': None, 'community_public_ownership': None, 'community_public_ownership_sources': None, 'community_activities_current': None, 'community_activities_always': None, 'latest_demolish_date': None, 'current_landuse_source': None, 'current_landuse_source_detail': None, 'current_landuse_link': None, 'current_landuse_verified': False, 'has_extension': None, 'extension_year': None, 'developer_type': None, 'developer_name': None, 'developer_source_link': None, 'designers': None, 'designers_source_link': None, 'lead_designer_type': None, 'designer_awards': None, 'awards_source_link': None, 'builder': None, 'builder_source_link': None, 'other_team': None, 'other_team_source_link': None, 'community_expected_planning_application_total': 0, 'landowner': None, 'landowner_source_link': None, 'planning_in_conservation_area_url': '', 'planning_in_conservation_area_id': '', 'planning_in_apa_url': '', 'planning_heritage_at_risk_url': '', 'planning_crowdsourced_site_completion_status': None, 'planning_crowdsourced_site_completion_year': None, 'planning_crowdsourced_planning_id': '', 'is_domestic': 'Nichtwohngebäude', 'community_type_worth_keeping_total': 0, 'use_building_origin': 'Handelsgebäude', 'use_building_origin_text': None, 'use_building_current': None, 'use_building_current_text': None, 'basement_type': None, 'basement_percentage': None, 'basement_use': None, 'ground_storey_use': None, 'upper_storeys_use': None, 'use_number_residential_units': None, 'use_number_businesses': None, 'building_status': None, 'last_renovation': None, 'construction_system_type': None, 'size_roof_shape': None, 'building_owner': None, 'basement_use_source': None, 'ground_storey_use_source': None, 'upper_storeys_use_source': None, 'building_status_source': None, 'last_renovation_source': None, 'construction_system_type_source': None, 'size_roof_shape_source': None, 'building_owner_source': None, 'architectural_style': None, 'architectural_style_source': None, 'heat_facade_roof_features': None, 'heat_facade_roof_features_source': None, 'thermal_stress_objective': [{'date': '2023-06-21', 'time': '14:52', 'floor': 1, 'orientation': 'Ost', 'acquisition_type': 'geschätzt', 'measured_temperature': 24}], 'thermal_stress_subjective': None, 'facade_window_percentage': None, 'direction_of_windows': None, 'heat_adaption_measure': {'sun_protection_inside': True, 'improvement_insulation': True, 'increase_heat_storage_capacity': True}, 'roof_features': None, 'terrain_connection_yesno': 'höher', 'terrain_connection_difference': 120, 'rain_flood_preventive_measures': None, 'rain_flood_historic_incidents': None, 'facade_window_percentage_source': None, 'direction_of_windows_source': None, 'heat_adaption_measure_source': None, 'roof_features_source': None, 'terrain_connection_yesno_source': None, 'terrain_connection_difference_source': 'geschätzt', 'rain_flood_preventive_measures_source': None, 'roof_colour': 'schwarz', 'roof_colour_type': None, 'facade_colour': None, 'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}, 'rain_flood_preventive_measures2': {'option_01': None, 'option_03': True}, 'rain_flood_preventive_measures3': {'option_02': True}, 'edit_history': [{'revision_id': '5784', 'forward_patch': {'roof_colour': 'schwarz'}, 'reverse_patch': {'roof_colour': 'rot'}, 'revision_timestamp': '2023-06-21T13:09:00.000Z', 'username': 'jugendstil_gesucht'}, {'revision_id': '5769', 'forward_patch': {'rain_flood_preventive_measures3': {'option_02': True}}, 'reverse_patch': {'rain_flood_preventive_measures3': None}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5768', 'forward_patch': {'rain_flood_preventive_measures2': {'option_01': None, 'option_03': True}}, 'reverse_patch': {'rain_flood_preventive_measures2': {'option_01': True, 'option_03': True}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5767', 'forward_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': True}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5766', 'forward_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': True}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5765', 'forward_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_02': None, 'option_03': None}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5764', 'forward_patch': {'rain_flood_preventive_measures1': {'option_02': None, 'option_03': None}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5763', 'forward_patch': {'rain_flood_preventive_measures2': {'option_01': True, 'option_03': True}}, 'reverse_patch': {'rain_flood_preventive_measures2': {'option_01': None, 'option_03': True}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5762', 'forward_patch': {'rain_flood_preventive_measures2': {'option_01': None, 'option_03': True}}, 'reverse_patch': {'rain_flood_preventive_measures2': {'option_01': True, 'option_03': True}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5761', 'forward_patch': {'rain_flood_preventive_measures2': {'option_01': True, 'option_03': True}}, 'reverse_patch': {'rain_flood_preventive_measures2': {'option_01': True}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5760', 'forward_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': True}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5759', 'forward_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': True}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5758', 'forward_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': True}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5757', 'forward_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': True}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5756', 'forward_patch': {'rain_flood_preventive_measures2': {'option_01': True}}, 'reverse_patch': {'rain_flood_preventive_measures2': None}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5755', 'forward_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_02': None, 'option_03': None}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5754', 'forward_patch': {'rain_flood_preventive_measures1': {'option_02': None, 'option_03': None}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5753', 'forward_patch': {'rain_flood_preventive_measures1': {'option_02': True, 'option_03': None}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_03': None}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5752', 'forward_patch': {'rain_flood_preventive_measures1': {'option_03': None}}, 'reverse_patch': {'rain_flood_preventive_measures1': {'option_03': True}}, 'revision_timestamp': '2023-06-21T12:59:00.000Z', 'username': 'popcorn'}, {'revision_id': '5751', 'forward_patch': {'rain_flood_preventive_measures1': {'option_03': True}}, 'reverse_patch': {'rain_flood_preventive_measures1': None}, 'revision_timestamp': '2023-06-21T12:58:00.000Z', 'username': 'popcorn'}, {'revision_id': '5750', 'forward_patch': {'terrain_connection_yesno': 'höher', 'terrain_connection_difference': 120, 'terrain_connection_difference_source': 'geschätzt'}, 'reverse_patch': {'terrain_connection_yesno': None, 'terrain_connection_difference': None, 'terrain_connection_difference_source': None}, 'revision_timestamp': '2023-06-21T12:57:00.000Z', 'username': 'popcorn'}, {'revision_id': '5749', 'forward_patch': {'heat_adaption_measure': {'sun_protection_inside': True, 'improvement_insulation': True, 'increase_heat_storage_capacity': True}}, 'reverse_patch': {'heat_adaption_measure': {'improvement_insulation': True, 'increase_heat_storage_capacity': True}}, 'revision_timestamp': '2023-06-21T12:56:00.000Z', 'username': 'popcorn'}, {'revision_id': '5748', 'forward_patch': {'heat_adaption_measure': {'improvement_insulation': True, 'increase_heat_storage_capacity': True}}, 'reverse_patch': {'heat_adaption_measure': {'improvement_insulation': None, 'increase_heat_storage_capacity': True}}, 'revision_timestamp': '2023-06-21T12:56:00.000Z', 'username': 'popcorn'}, {'revision_id': '5747', 'forward_patch': {'heat_adaption_measure': {'improvement_insulation': None, 'increase_heat_storage_capacity': True}}, 'reverse_patch': {'heat_adaption_measure': {'improvement_insulation': None}}, 'revision_timestamp': '2023-06-21T12:56:00.000Z', 'username': 'popcorn'}, {'revision_id': '5746', 'forward_patch': {'heat_adaption_measure': {'improvement_insulation': None}}, 'reverse_patch': {'heat_adaption_measure': {'improvement_insulation': True}}, 'revision_timestamp': '2023-06-21T12:56:00.000Z', 'username': 'popcorn'}, {'revision_id': '5745', 'forward_patch': {'heat_adaption_measure': {'improvement_insulation': True}}, 'reverse_patch': {'heat_adaption_measure': None}, 'revision_timestamp': '2023-06-21T12:56:00.000Z', 'username': 'popcorn'}, {'revision_id': '5744', 'forward_patch': {'thermal_stress_objective': [{'date': '2023-06-21', 'time': '14:52', 'floor': 1, 'orientation': 'Ost', 'acquisition_type': 'geschätzt', 'measured_temperature': 24}]}, 'reverse_patch': {'thermal_stress_objective': None}, 'revision_timestamp': '2023-06-21T12:52:00.000Z', 'username': 'popcorn'}, {'revision_id': '5735', 'forward_patch': {'roof_colour': 'rot'}, 'reverse_patch': {'roof_colour': None}, 'revision_timestamp': '2023-06-21T12:33:00.000Z', 'username': 'jugendstil_gesucht'}, {'revision_id': '1694', 'forward_patch': {'use_building_origin': 'Handelsgebäude'}, 'reverse_patch': {'use_building_origin': None}, 'revision_timestamp': '2023-03-15T13:55:00.000Z', 'username': 'StadtwikiDD'}, {'revision_id': '1206', 'forward_patch': {'is_domestic': 'Nichtwohngebäude'}, 'reverse_patch': {'is_domestic': None}, 'revision_timestamp': '2023-03-11T16:33:00.000Z', 'username': 'WalterBjoern'}, {'revision_id': '1195', 'forward_patch': {'date_link': ['https://de.wikipedia.org/wiki/Altmarkt-Galerie'], 'date_year': 2002, 'date_lower': 2000, 'date_upper': 2002, 'date_source': 'Andere Webseite', 'facade_year': 2002}, 'reverse_patch': {'date_link': None, 'date_year': None, 'date_lower': None, 'date_upper': None, 'date_source': None, 'facade_year': None}, 'revision_timestamp': '2023-03-11T16:22:00.000Z', 'username': 'WalterBjoern'}], 'verified': {}}


api_url = r'https://colouring-test.dresden.ioer.de'
apikey = 'long_secret_api_key'
resp1, resp2 = update_building(41106, data_upload, apikey, api_url)
print(resp1)
print(resp2)

[traveller195]

for the new JSON data_upload I took a previously accessed JSON data via GET /buildings/{}.json and just edited manually the field for 'location_name' into 'TESTDATA'. Everything else I kept from the GET request.

[traveller195]

I tried different formats for input json data, for example only the new value which should be updated. And also I started the script from two different environments but I got always the same error. Also I applied the request again the production environment once, but I it was the same...

import csv
import json
import os
import sys
import argparse

import requests
from retrying import retry



@retry(wait_exponential_multiplier=1000, wait_exponential_max=10000)
def update_building(building_id, data, api_key, base_url):
    """Save data to a building
    """
    r = requests.post(
        "{}/api/buildings/{}.json".format(base_url, building_id),
        params={'api_key': api_key},
        json=data
    )
    return r.status_code, r.json()

data_upload = json.loads('{"location_name": "TESTDATA"}')

api_url = r'https://colouring-test.dresden.ioer.de'
apikey = 'secret_api_key'
resp1, resp2 = update_building(41106, data_upload, apikey, api_url)
print(resp1)
print(resp2)

[traveller195]

@tomalrussell @matkoniecz
I found out, that again it is a problem with http versus https.

Now, I use only http for API requests, and for POST buildings I get now code 200, but the edit of building attribute is not successful. So, the data will not be changed.

If I do a API request /login I get the response
{"error":"Resource not found"}

---

if I use https like I did before, I get for /login the userid as a response.

But the initially described server error 500 for POST /buildings/{id].json

How can I use also POST requests with https API-URL ?

thanks a lot : )

[traveller195]

I also tried the original script https://github.com/colouring-cities/colouring-london/blob/54a40d3d8c07af5b90d0c3f979a0c561e0f931b6/etl/join_building_data/load_csv.py to run it against Colouring Dresden Staging Env (and also against Colouring London API), but I got just status code 200 (OK) but without any changes for builiding attributes.

What could I do better?

cheers

[traveller195]

I also used the origin python script for bulk uploads (load_csv.py) to test API uploads outside of the intranet of the institute. But it is the same result. I get a match for my building_id I want to edit some building attributes. And also 200 status code from API. But within the database there is no change/ edit. Neither in the buildings data, nor in the logs table...

[polly64]

hi @traveller195 sorry we've not been able to get back @mdsimpson42 @tomalrussell @matkoniecz could anyone possibly help IOER out here?

[traveller195]

hi Polly, thanks for your answer.
I know this is such an easy task. And all "read only" requests (GET) against the API are fine, so the problem did not occure in the past.
Now I try to input some data via API (and not just insert it directly to the database without the API, which is not the perfect way), I have those troubles. And now I used the python script from Tom in its origin version and I am wondering why it is not working for me.
I did a lot of tests from different environments, but also against the APIs of Colouring Dreden Staging Env, Colouring Dresden Production Env and also Colouring London Production Env (of course I only use values for buildings which make senses, so it would be not trash data if the edit would succeed).

One problem could be perhaps an upgrade from HTTP to HTTPS in the past: do we/ I need to change some settings for server/ API that POST requests are possible again?
Did you run some bulk uploads to Colouring London in the last months?
Do you use http or https for basis API URL?

Another problem could be the shape of my textfile providing my testdata in the CSV format (but I tried a lot of different formats directly in the python script, so it should not be the problem here...)
here my text.txt file to testing bulk upload against Colouring Dresden Staging Env:

building_id,location_name,location_number
41106,TESTDATA,13

Do I should adapt some settings in the backend of the API to enable POST requests? but it seemed to be alright...

Or perhaps my understanding of the data structure is not the best, concerning the new input data I provide for the buildings. I know from the source code that the API is comparing all new given building attributes with the current existing building attributes and its values. And only for those, which are different, the changeset/ edit will be generated. But maybe there is the need for a different data format is input, than I use currently...

thanks for your help : ) I guess I/ we will solve this problem soon : )

[polly64]

we have done an upgrade to https but not that recently but we haven't done any bulk uploads. def best to talk to the team re this. I think everybody might be more available next week but you could try also emailing Mike tomorrow just in case there is a quick fix. I'm away til Tuesday but will catch up with this then

[traveller195]

thanks for your quick reply :)
alright, so perhaps this could be the problem. Because obviously also Colouring London is affected.
Alright, I try to mail Mike. but it is totally fine that wie discuss it in the next time when all are available again : )
greetings

[traveller195]

so, I got the solution:

I was wrong when a wrote that POST requests (with read-access) could be out of work because of upgrade from http to https --> because all saved edits by the users are POST requests, as well. Not only bulk uploads...

thats why, I analyzed those successful requests made by the frontend within the developer tools of the browser.

so, I found out that the JSON structure for the POST request is different, indeed. It is divided into 'attributes' and 'user_attributes' and fits so the structure of the datafields of the API.

the second change I made is to include a /login API request first in a session.

Both changes together worked now for me, so I am really happy to update building attributes from a python script using the API