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"
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.
0 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
How to work with Python and Jupyter Notebooks
OPSD wiki
nbviewer example
http://geopandas.org/install.html#installing-geopandas
http://docs.python-requests.org/en/master/
The registration has to be done in the Openmod Wiki.
Login to the OpenEnergy Platform.
In [ ]:
import requests
import pandas as pd
from IPython.core.display import HTML
#
#
# oedb
oep_url= 'http://oep.iks.cs.ovgu.de/'
# token
your_token = ''
Data are stored and ordered by topic under the "schema" tab: https://oep.iks.cs.ovgu.de/dataedit/schemas </div>
The data tables can be accessed by calling them via http request. </div>
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.</div>
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
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 [ ]:
data = {"query":
{"name": "Windpark, Am Speller Sand",
"type": "wind_onshore",
"capacity": 20000,
"lat": 52.40731,
"lon": 7.46118
}}
In [ ]:
# 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} )
In [ ]:
# check if exists
schema = 'model_draft'
table = 'example_api_table_test'
requests.get(oep_url+'/api/v0/schema/'+schema+'/tables/'+table)
Table can be seen online: http://oep.iks.cs.ovgu.de/dataedit/view/model_draft/example_api_table_test
In [ ]:
# Delete your table
schema = 'model_draft'
table = 'example_api_table_test'
requests.delete(oep_url+'/api/v0/schema/'+schema+'/tables/'+table, headers={'Authorization': 'Token %s'%your_token} )
In [ ]:
# check if exists
schema = 'model_draft'
table = 'example_api_table_test'
requests.get(oep_url+'/api/v0/schema/'+schema+'/tables/'+table)
In [ ]:
# table columns
schema = 'model_draft'
table = 'example_api_table_test'
result = requests.get(oep_url+'/api/v0/schema/'+schema+'/tables/'+table+'/columns')
json_result = result.json()
json_result['capacity']
# all:
#json_result
In [ ]:
# insert data
schema = 'model_draft'
table = 'example_api_table_test'
data = {"query":
{"name": "Windpark, Am Speller Sand",
"type": "wind_onshore",
"capacity": 20000,
"lat": 52.40731,
"lon": 7.46118
}}
result = requests.post(oep_url+'/api/v0/schema/'+schema+'/tables/'+table+'/rows/new',
json=data, headers={'Authorization': 'Token %s'%your_token} )
result.status_code
In [ ]:
# show the id of the new row
json_result = result.json()
json_result['data']
In [ ]:
# insert data
schema = 'model_draft'
table = 'example_api_table_test'
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
}]}
result = requests.post(oep_url+'/api/v0/schema/'+schema+'/tables/'+table+'/rows/new',
json=data, headers={'Authorization': 'Token %s'%your_token} )
result.status_code
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 [ ]:
# show the id of the new rows
json_result = result.json()
json_result['data']
In [ ]:
# insert data to row 11
schema = 'model_draft'
table = 'example_api_table_test'
rownumber = '11'
data = {"query":
{"name": "Anlage 11",
"type": "photovoltaic",
"capacity": 5.5,
"lat": 51.804783,
"lon": 10.41573
}}
result = requests.put(oep_url+'/api/v0/schema/'+schema+'/tables/'+table+'/rows/'+rownumber,
json=data, headers={'Authorization': 'Token %s'%your_token} )
result.status_code
In [ ]:
# insert some data
In [ ]:
# alter data
schema = 'model_draft'
table = 'example_api_table_test'
result = requests.get(oep_url+'/api/v0/schema/'+schema+'/tables/'+table+'/rows/') # Load the names via GET
result.status_code
In [ ]:
row = "1"
data = {"query": {"capacity": "2", "type": "pv"}}
result = requests.post(oep_url+'/api/v0/schema/'+schema+'/tables/'+table+'/rows/{id}'.format(id=row), json=data, headers={'Authorization': 'Token %s'%your_token})
result.status_code