API Tutorial 02 - Upload data to the Open Energy Platform using the API¶
Learnings¶
This tutorials will enable you to:
- Create a table on the OEP
- Upload data to that table
- Upload metadata accompanying the data
- Delete the table and the corresponding metadata
Requirements¶
- have your OEP API token at hand. You find this in settings tab in your profile page. (If you don't have a token see Things you only need to do once.)
- Python
- Python packages:
requests
(See the packages documentation for more help)- jupyter notebook, e.g.
notebook
orjupyterlab
shapely
(only for geometry data conversion)
- Skills:
- use python with jupyter notebooks
- some understanding of JSON encoded data
- Internet: obviously, but also make sure your network settings allow https traffic from python
Feedback¶
You can provide feedback on this tutorial in this GitHub issue
Setup¶
This part of the code sets up all you need to execute the further code below. It imports the required packages into python, sets up the test table and asks you for the OEP API token so that you can identify with the OEP, which is mandatory if you want to upload to the OEP. The setup is a necessary step.
# install required packages with: pip install requests
# import required packages
import json
from random import randint
from getpass import getpass
from os import environ
import requests as req
topic = "sandbox"
table = f"tutorial_example_table_{randint(0, 100000)}"
token = environ.get("OEP_API_TOKEN") or getpass("Enter your OEP API token:")
# for read/write, we need to add authorization header
auth_headers = {"Authorization": "Token %s" % token}
table_api_url = f"https://openenergyplatform.org/api/v0/schema/{topic}/tables/{table}/"
print(table_api_url)
https://openenergyplatform.org/api/v0/schema/sandbox/tables/tutorial_example_table_61248/
# TODO: explain / link to data types
table_schema = {
"columns": [
# NOTE: first column should be numerical column named `id` .
# Use `bigserial` if you want the database to create the re
{"name": "id", "data_type": "bigserial", "primary_key": True},
{"name": "name", "data_type": "varchar(18)", "is_nullable": False},
{"name": "is_active", "data_type": "boolean"},
{"name": "capacity_mw", "data_type": "float"},
{"name": "installation_datetime_utc", "data_type": "datetime"},
{"name": "location", "data_type": "geometry"},
]
}
# Creating table needs authentication headers
# Creating table needs table schema json data
res = req.put(table_api_url, json={"query": table_schema}, headers=auth_headers)
# raise Exception if request fails
if not res.ok:
raise Exception(res.text)
# NOTE: in the sandbox, this link will not work:
print(
f"you can see the data on the platform here: https://openenergyplatform.org/dataedit/view/{topic}/{table}"
)
you can see the data on the platform here: https://openenergyplatform.org/dataedit/view/sandbox/tutorial_example_table_61248
Upload data¶
This part of the code uploads the table that you have created above to the OEP.
To be able to execute this part you will need to have executed the setup and you will need to have created a table.
# TODO: explain required data structure
# get example data
data = req.get(
"https://raw.githubusercontent.com/OpenEnergyPlatform/academy/production/docs/data/tutorial_example_table.data.json"
).json()
# show results in notebook
print(json.dumps(data, indent=4))
[ { "name": "unit1", "is_active": true, "capacity_mw": 1.2, "installation_datetime_utc": "2010-02-03 00:00:00", "location": "POINT(53.12 8.345)" }, { "name": "unit2", "is_active": false, "capacity_mw": 2.1, "installation_datetime_utc": "2010-01-08", "location": null }, { "name": "unit3", "is_active": true, "capacity_mw": 100.0, "installation_datetime_utc": "2010-01-02 10:30:00", "location": "Point(55.34 7.34)" } ]
# Upload data needs authentication headers
# Upload data needs data records in json query
res = req.post(table_api_url + "rows/new", json={"query": data}, headers=auth_headers)
# raise Exception if request fails
if not res.ok:
raise Exception(res.text)
# TODO: maybe batches, append, not overwrite existing
Notes on data types¶
The api expects json serializable data, which is only bool, numerical, or string.
In the example, the value of installation_datetime_utc
is a string representation
of a datetime object, the value of location
a
WKB
string representation of a geometry object.
Depending on the use case, the data needs to be converted before upload
# example of data conversion
from datetime import datetime
from shapely import Point, wkt
example_row = {
"name": "unit1",
"is_active": True,
"capacity_mw": 1.2,
"installation_datetime_utc": datetime(2010, 2, 3),
"location": Point(53.12, 8.345),
}
converted_data_row = example_row.copy()
# convert datetime object to datetime string
converted_data_row["installation_datetime_utc"] = converted_data_row[
"installation_datetime_utc"
].strftime("%Y-%m-%dT%H:%M:%S")
# convert shapely geoemtry object to wkt string
converted_data_row["location"] = wkt.dumps(converted_data_row["location"])
# show results in notebook
print(json.dumps(converted_data_row, indent=4))
{ "name": "unit1", "is_active": true, "capacity_mw": 1.2, "installation_datetime_utc": "2010-02-03T00:00:00", "location": "POINT (53.1199999999999974 8.3450000000000006)" }
Upload metadata¶
This part of the code will upload metadata that described the data in your table to the OEP.
To be able to execute this part you will need to have executed the setup and you will need to have created a table.
# get metadata (from example file)
metadata = req.get(
"https://raw.githubusercontent.com/OpenEnergyPlatform/academy/production/docs/data/tutorial_example_table.metadata.json"
).json()
# show results in notebook
print(json.dumps(metadata, indent=4))
{ "id": "test_table", "keywords": [ "energy", "installations", "geo" ], "languages": [ "EN" ], "resources": [ { "name": "test_table", "schema": { "fields": [ { "name": "id", "type": "integer", "data_type": "bigserial", "description": "numerical id of this data point", "primary_key": true }, { "name": "name", "type": "string", "data_type": "varchar(18)", "description": "name of installation", "is_nullable": false }, { "name": "is_active", "type": "boolean", "description": "true/false if installation is active" }, { "name": "capacity_mw", "type": "number", "data_type": "float", "description": "installed capacity in MW" }, { "name": "installation_datetime_utc", "type": "string", "data_type": "datetime", "description": "date (and time) when installation was build" }, { "name": "location", "type": "geojson", "data_type": "geometry", "description": "point location of installation" } ] } } ] }
# Upload metadata needs authentication headers
# Upload metadata needs metadata json
res = req.post(table_api_url + "meta/", json=metadata, headers=auth_headers)
# raise Exception if request fails
if not res.ok:
raise Exception(res.text)
Delete table¶
This part of the code deletes the table (including the metadata) that you have uploaded to the OEP.
To be able to execute this part you will need to have executed the setup and you will need to have created a table.
# Deleting tables needs authentication headers
res = req.delete(table_api_url, headers=auth_headers)
# raise Exception if request fails
if not res.ok:
raise Exception(res.text)
About this tutorial¶
- Author: Christian Winger
- Copyright: Öko-Institut (2024)
- License: CC BY 4.0
- Attribution: Öko-Institut (2024): API Tutorial 02 - Upload data to the Open Energy Platform using the API.
- Last update 2024-11-07