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 or jupyterlab
  • 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.

In [1]:

# 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)

# 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/

Create table¶

This part of the code creates the table you will later upload to the OEP.

To be able to execute this part you will need to have executed the setup

In [2]:

# 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"},
    ]
}

# 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"}, ] }

In [10]:

# 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}"
)

# 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.

In [4]:

# 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))

# 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)"
    }
]

In [5]:

# 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

# 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

In [6]:

# 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))

# 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.

In [7]:

# 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))

# 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"
                    }
                ]
            }
        }
    ]
}

In [8]:

# 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)

# 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.

In [9]:

# 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)

# 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-18