API Tutorial 02 - Upload data 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¶

To execute the following code you will need to need to be a registered user on the OEP and

• have your OEP API token at hand. You find this in settings tab in your profile page

You will also need to have the following installed on your computer:

• Python

• Python packages:

  • requests
  • jupyter notebook, e.g. notebook or jupyterlab

• Ideally you exhibit the following skills:

  • use python with jupyter notebooks

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://openenergy-platform.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://openenergy-platform.org/api/v0/schema/{topic}/tables/{table}/" print(table_api_url)

https://openenergy-platform.org/api/v0/schema/sandbox/tables/tutorial_example_table_22087/

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 [3]:

# 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 currentlynot work:
print(f"you can see the data on the platform here: https://openenergy-platform.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 currentlynot work: print(f"you can see the data on the platform here: https://openenergy-platform.org/dataedit/view/{topic}/{table}")

you can see the data on the platform here: https://openenergy-platform.org/dataedit/view/sandbox/tutorial_example_table_22087

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

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 [6]:

# 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",
                        "description": "numerical id of this data point"
                    },
                    {
                        "name": "name",
                        "type": "string",
                        "data_type": "varchar(128)",
                        "description": "name of installation"
                    },
                    {
                        "name": "is_active",
                        "type": "boolean",
                        "description": "true/false if installation is active"
                    },
                    {
                        "name": "capacity_mw",
                        "type": "number",
                        "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 [7]:

# 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 [8]:

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

In [ ]: