API tutorial 2 - Advanced OEP-API data queries using query parameters¶

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

__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-full-HTTP API in Python.
The full API documentaion can be found on ReadtheDocs.io.

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

0 Setup token
1 Select data from a table
2 Include filters
3 More Options

Part II¶

0. Setup token¶

The token is used to verify the API interaction for your OEP user.

Do not push your token to GitHub!

In [ ]:

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

1. Select data from a table¶

In [ ]:

# select data
schema = 'model_draft'
table = 'example_api_table_test'

get_data = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/')
get_data.status_code

# select data schema = 'model_draft' table = 'example_api_table_test' get_data = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/') get_data.status_code

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

In [ ]:

# Convert to dataframe
plant_df = pd.DataFrame(get_data.json())
plant_df

# Convert to dataframe plant_df = pd.DataFrame(get_data.json()) plant_df

2. Include filters¶

A filter option is applied at the end of the result URL like this ".../rows/?where=filter_statement" and is processed as HTTP/get request by the API.

2.1 where¶

In [ ]:

# insert 'columnname=value'
where = 'type=wind_onshore'

result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/?where=' + where)
print(result.status_code)

# Convert to dataframe
df = pd.DataFrame(result.json())
df

# insert 'columnname=value' where = 'type=wind_onshore' result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/?where=' + where) print(result.status_code) # Convert to dataframe df = pd.DataFrame(result.json()) df

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

2.2 column¶

Name of a column to include in the results. If not present, all columns are returned.

In [ ]:

# select columns by columnname
# select multiple columns using &
column = 'column=name&column=id&column=type'

result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/?' + column)
print(result.status_code)

# Convert to dataframe
df = pd.DataFrame(result.json())
df

# select columns by columnname # select multiple columns using & column = 'column=name&column=id&column=type' result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/?' + column) print(result.status_code) # Convert to dataframe df = pd.DataFrame(result.json()) df

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

2.3 limit¶

The limit option is used to limit the number of results returned by the API. This is useful when querying a large data set with many results for the specific filter request.

In [ ]:

# set the number of results
limit = '2'

result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table  +  '/rows/?limit='  +  limit)
print(result.status_code)

# Convert to dataframe
df = pd.DataFrame(result.json())
df

# set the number of results limit = '2' result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/?limit=' + limit) print(result.status_code) # Convert to dataframe df = pd.DataFrame(result.json()) df

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

2.4 orderby¶

The ORDER BY keyword is used to sort the result-set in ascending order.

In [ ]:

# insert column name
order_by = 'id'

result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/?orderby=' + order_by)
result.status_code
print(result)

# Convert to dataframe
df = pd.DataFrame(result.json())
df

# insert column name order_by = 'id' result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/?orderby=' + order_by) result.status_code print(result) # Convert to dataframe df = pd.DataFrame(result.json()) df

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

It is possible to combine filters by appending a "&" to the end of the URL then adding another filteroption like "/rows/?limit=somelimit&orderby=someorder"

In [ ]:

# insert column name
order_by = 'id'
# set the number of results
limit = '2'

result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/?limit=' + limit + '&orderby=' + order_by)
print(result.status_code)

# Convert to dataframe
df = pd.DataFrame(result.json())
df

# insert column name order_by = 'id' # set the number of results limit = '2' result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/?limit=' + limit + '&orderby=' + order_by) print(result.status_code) # Convert to dataframe df = pd.DataFrame(result.json()) df

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

2.5 Combine filters¶

In [ ]:

# insert 'columnname=value'
where = 'type=photovoltaics'

# select columns by columnname
# select multiple columns using &
column = 'column=name&column=id&column=type'

result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/?where=' + where + '&' + column)
print(result.status_code)

# Convert to dataframe
df = pd.DataFrame(result.json())
df

# insert 'columnname=value' where = 'type=photovoltaics' # select columns by columnname # select multiple columns using & column = 'column=name&column=id&column=type' result = requests.get(oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/rows/?where=' + where + '&' + column) print(result.status_code) # Convert to dataframe df = pd.DataFrame(result.json()) df

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

3. More Options¶

There are also optional parameters for these GET-queries:

offset: Ignore the specified amount of rows
where: Constraint fourmulated as VALUE+OPERATOR+VALUE with
1 VALUE: Constant or name of a column
2 OPERATOR: One of the following:

• EQUALS or =,
  
• GREATER or >,
  
• LOWER or <,
  
• NOTEQUAL or != or <>,
  
• NOTGREATER or <=,
  
• NOTLOWER or >=
  

Try some selects!

In [ ]: