API tutorial 1 - Introduction to the OEP-API and basic table opperations¶

Repository: https://github.com/openego/oedialect

Please report bugs and improvements here: https://github.com/OpenEnergyPlatform/examples/issues
How to get started with Jupyter Notebooks can be found here: https://realpython.com/jupyter-notebook-introduction/
Please ensure you have read the Terms of use here: https://openenergy-platform.org/legal/tou/

In [1]:

                
                    Copied!
                    
__copyright__ = "Reiner Lemoine Institut, Zentrum für nachhaltige Energiesysteme Flensburg"
__license__   = "GNU Affero General Public License Version 3 (AGPL-3.0)"
__url__       = "https://github.com/openego/data_processing/blob/master/LICENSE"
__author__    = "wolfbunke, Ludee"
__copyright__ = "Reiner Lemoine Institut, Zentrum für nachhaltige Energiesysteme Flensburg"
__license__   = "GNU Affero General Public License Version 3 (AGPL-3.0)"
__url__       = "https://github.com/openego/data_processing/blob/master/LICENSE"
__author__    = "wolfbunke, Ludee"

Introduction¶

This is an important information!

This is an information!

This is your task!

This tutorial gives you an overview of the OpenEnergy Platform and how you can work with the REST-ful-HTTP API in Python.
The full API documentaion can be found on ReadtheDocs.io.

Part I - How to work with the OpenEnergy Platform (OEP)¶

Get started - Sign-in and get your own token
0 Setup token
1 Create a table
2 Delete a table
3 Query table columns
4 Insert Data into a table
5 Insert data into a specific row
6 Alter data in a table

Links and further information¶

How to work with Python and Jupyter Notebooks
OPSD wiki
nbviewer example

Overview of Packages:¶

http://geopandas.org/install.html#installing-geopandas

Part I¶

OpenEnergy Platform login

2. Copy your token¶

Click on your Profile Name to see your information. To view your token, click on "Show token"

3a. Paste your token in an environment variable¶

The variable should be named 'OEP_TOKEN'. On linux :

export OEP_TOKEN=<your token>

On windows:

set OEP_TOKEN=<your token>

3b. Paste your token in a file (discouraged)¶

Look for the following line in the file api/token_config.py,

OEP_TOKEN = ''

then paste your token there

OEP_TOKEN = '<paste_your_token_here>'

Save the file but DO NOT PUSH the change to GitHub

0. Setup token¶

Do not push your token to GitHub!

In [ ]:

                
                    Copied!
                    
import requests
import pandas as pd
from IPython.core.display import HTML

from token_config import oep_url, get_oep_token

# token
your_token = get_oep_token()
import requests
import pandas as pd
from IPython.core.display import HTML

from token_config import oep_url, get_oep_token

# token
your_token = get_oep_token()

0.1 About the Database and used Packages¶

Data are stored and ordered by topic under the "schema" tab: https://openenergy-platform.org/dataedit/schemas

The data tables can be accessed by calling them via http request.

The "request" package will handle the HTTP communication with the OEP database. It sends requests that insert (put, post, delete) or read (get) data in/from the databank. For either request the URL of a requested table has to be included. For all "put", "post" and "delete" requests one has to include the authentification token as a header, like in the "delete table " example.

Example:

requests.delete(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table, 
    headers={'Authorization': 'Token %s'%your_token}
)

About the table structure:

The tables are organized in rows and columns that can be adressed via:

requests.post(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/')
# and       
requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/columns')

In the following tutorial the schema will be 'model_draft'and the table 'example_api_table_test'.

Inserted data must have the structure of a dictionary / json with a key for every column of the table and the respective value like in:

Random example from OpenStreetMap with freely chosen capacity.

In [ ]:

                
                    Copied!
                    
                        
                        
                    
                    

            
data = {
    "query": {
        "name": "Windpark, Am Speller Sand", 
        "type":  "wind_onshore",
        "capacity": 20000,
        "lat": 52.40731,
        "lon": 7.46118
    }
}
data = {
    "query": {
        "name": "Windpark, Am Speller Sand", 
        "type":  "wind_onshore",
        "capacity": 20000,
        "lat": 52.40731,
        "lon": 7.46118
    }
}

1. Create a table / Table Architecure¶

You installed all Python packages? Let's create our first database table:

The API is enabled for the following schmemas only: 'model_draft' & 'sandbox'

A table must have a column 'id' of type 'bigserial'!

Change the name of the table!

In [ ]:

                
                    Copied!
                    
                        
                        
                    
                    

            
# create table
schema = 'model_draft'
table = 'example_api_table_test'

data = { 
    "query": { 
        "columns": [
            { "name":"id", "data_type": "bigserial", "is_nullable": "NO" },
            { "name":"name", "data_type": "varchar", "character_maximum_length": "50" },
            { "name":"type", "data_type": "varchar", "character_maximum_length": "20" },
            { "name":"capacity", "data_type": "decimal" },
            { "name":"lat", "data_type": "numeric" },
            { "name":"lon", "data_type": "numeric" }
        ],
        "constraints": [ { "constraint_type": "PRIMARY KEY", "constraint_parameter": "id" } ] 
    }
}

requests.put(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/',
    json=data,
    headers={'Authorization': 'Token %s'%your_token}
)
# create table
schema = 'model_draft'
table = 'example_api_table_test'

data = { 
    "query": { 
        "columns": [
            { "name":"id", "data_type": "bigserial", "is_nullable": "NO" },
            { "name":"name", "data_type": "varchar", "character_maximum_length": "50" },
            { "name":"type", "data_type": "varchar", "character_maximum_length": "20" },
            { "name":"capacity", "data_type": "decimal" },
            { "name":"lat", "data_type": "numeric" },
            { "name":"lon", "data_type": "numeric" }
        ],
        "constraints": [ { "constraint_type": "PRIMARY KEY", "constraint_parameter": "id" } ] 
    }
}

requests.put(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/',
    json=data,
    headers={'Authorization': 'Token %s'%your_token}
)

Response [201] succesfully created table!
Response [400] table already exists! (choose another name for table)

In [ ]:

                
                    Copied!
                    
# check if your table exists
requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table)
# check if your table exists
requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table)

Response [200] table exists!
Response [404] table doesn't exist!

Table can be seen online: https://openenergy-platform.org/dataedit/view/model_draft/example_api_table_test or https://openenergy-platform.org/dataedit/view/model_draft/ + name_of_your_table

2. Delete a table¶

In [ ]:

                
                    Copied!
                    
# Delete your table
requests.delete(oep_url + '/api/v0/schema/' + schema + '/tables/' + table, headers={'Authorization': 'Token %s'%your_token} )
# Delete your table
requests.delete(oep_url + '/api/v0/schema/' + schema + '/tables/' + table, headers={'Authorization': 'Token %s'%your_token} )

Response [200] succesfully deleted table!
Response [404] table doesn't exist and cannot be deleted!

In [ ]:

                
                    Copied!
                    
# check if your table was properly deleted
requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table)
# check if your table was properly deleted
requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table)

Response [404] table does not exists!

Now create the table again! This is important for the following tutorials!

In [ ]:

                
                    Copied!
                    
                        
                        
                    
                    

            
# recreate the same table as above
requests.put(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/',
    json=data, 
    headers={'Authorization': 'Token %s'%your_token}
)
# recreate the same table as above
requests.put(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/',
    json=data, 
    headers={'Authorization': 'Token %s'%your_token}
)

3. Query table columns¶

In [ ]:

                
                    Copied!
                    
result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/columns')
json_result = result.json()
json_result['capacity']

# all:
#json_result
result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/columns')
json_result = result.json()
json_result['capacity']

# all:
#json_result

4. Insert Data into a table¶

4.1 Insert single row¶

Now we insert a power plant with name, type and latitude longitude cooridinates.

In [ ]:

                
                    Copied!
                    
                        
                        
                    
                    

            
data = {
    "query": {
        "name": "Anlage 1", 
        "type": "wind_onshore",
        "capacity": 20000,
        "lat": 52.40731,
        "lon": 7.46118
    }
}

requests.post(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/new',
    json=data,
    headers={'Authorization': 'Token %s'%your_token}
)
data = {
    "query": {
        "name": "Anlage 1", 
        "type": "wind_onshore",
        "capacity": 20000,
        "lat": 52.40731,
        "lon": 7.46118
    }
}

requests.post(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/new',
    json=data,
    headers={'Authorization': 'Token %s'%your_token}
)

Response [201] succesfully inserted data!
Response [404] table doesn't exist!

4.1 Insert multiple rows¶

If you want to insert multiple lines of data like two power plants in this case, you can insert a list of dictionaries with as many entries as the table has columns.

In [ ]:

                
                    Copied!
                    
                        
                        
                    
                    

            
data = {
    "query": [
        {
            "name": "Anlage 2", 
            "type": "photovoltaics",
            "capacity": 10,
            "lat": 51.804783,
            "lon": 11.686346
        },
        {
            "name": "Anlage 3", 
             "type": "photovoltaic", 
             "capacity": 5.5,
             "lat": 51.804783,
             "lon": 11.6341573
        }
    ]
}

requests.post(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/new', 
    json=data,
    headers={'Authorization': 'Token %s'%your_token}
)
data = {
    "query": [
        {
            "name": "Anlage 2", 
            "type": "photovoltaics",
            "capacity": 10,
            "lat": 51.804783,
            "lon": 11.686346
        },
        {
            "name": "Anlage 3", 
             "type": "photovoltaic", 
             "capacity": 5.5,
             "lat": 51.804783,
             "lon": 11.6341573
        }
    ]
}

requests.post(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/new', 
    json=data,
    headers={'Authorization': 'Token %s'%your_token}
)

Response [201] succesfully inserted data!
Response [404] table doesn't exist!

5. Insert data into a specific row¶

In [ ]:

                
                    Copied!
                    
                        
                        
                    
                    

            
# insert data to row 11
rownumber = '11'

data = {
    "query": {
        "name": "Anlage 11",
        "type": "photovoltaic",
        "capacity": 5.5,
        "lat": 51.804783,
        "lon": 10.41573
    }
}

requests.put(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/' + rownumber, 
    json=data,
    headers={'Authorization': 'Token %s'%your_token}
)
# insert data to row 11
rownumber = '11'

data = {
    "query": {
        "name": "Anlage 11",
        "type": "photovoltaic",
        "capacity": 5.5,
        "lat": 51.804783,
        "lon": 10.41573
    }
}

requests.put(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/' + rownumber, 
    json=data,
    headers={'Authorization': 'Token %s'%your_token}
)

Response [201] succesfully inserted data!
Response [200] row already taken!
Response [404] table doesn't exist!

Insert some powerplants!

In [ ]:

                
                    Copied!
                    
# insert some data
# insert some data

6. Alter data in a table¶

In [ ]:

                
                    Copied!
                    
requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/') # Load the names via GET
requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/') # Load the names via GET

Response [200] table exists!
Response [404] table doesn't exist!

In [ ]:

                
                    Copied!
                    
                        
                        
                    
                    

            
row = 2
data = {"query": {"capacity": "2", "type": "pv"}}

requests.post(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/{id}'.format(id=row),
    json=data,
    headers={'Authorization': 'Token %s'%your_token}
)
row = 2
data = {"query": {"capacity": "2", "type": "pv"}}

requests.post(
    oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/{id}'.format(id=row),
    json=data,
    headers={'Authorization': 'Token %s'%your_token}
)

Response [200] succesfully changed data!
Response [404] table doesn't exist!

In [ ]:

API tutorial 1 - Introduction to the OEP-API and basic table opperations¶

Introduction¶

Part I - How to work with the OpenEnergy Platform (OEP)¶

Links and further information¶

Overview of Packages:¶

Part I¶

0. Get started - Sign-in and get your own token¶

1. Click on the login button and sign in¶

2. Copy your token¶

3a. Paste your token in an environment variable¶

3b. Paste your token in a file (discouraged)¶

0. Setup token¶

0.1 About the Database and used Packages¶

1. Create a table / Table Architecure¶

2. Delete a table¶

3. Query table columns¶

4. Insert Data into a table¶

4.1 Insert single row¶

4.1 Insert multiple rows¶

5. Insert data into a specific row¶

6. Alter data in a table¶