OEP-oedialect

# OpenEnergyPlatform¶

## Usage of OpenEnergyPlatform API-Dialect (oedialect)¶

Please report bugs and improvements here: https://github.com/OpenEnergyPlatform/oedialect/issues

In [ ]:
__copyright__ = "Reiner Lemoine Institut"
__author__    = "henhuy, Ludee, oakca"

In [ ]:
import pandas as pd
import getpass
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
import oedialect


## Connection to OEP¶

If we want to upload data to the OEP we first need to connect to it, using our OEP user name and token. Note: You ca view your token on your OEP profile page after logging in.

In [ ]:
# Whitespaces are not a problem for setting up the url!
token = getpass.getpass('Token:')

In [ ]:
# Create Engine:
OEP_URL = 'oep.iks.cs.ovgu.de'
OED_STRING = f'postgresql+oedialect://{user}:{token}@{OEP_URL}'

engine = sa.create_engine(OED_STRING)


## Setup a Table¶

We need to tell the data base what columns and datatypes we are about to upload. In our case we have four columns, two of which are text, one is integer and the last is float.

In [ ]:
table_name = 'example_dialect_tablex'
schema_name = 'sandbox'

ExampleTable = sa.Table(
table_name,
sa.Column('name', sa.VARCHAR(50)),
sa.Column('age', sa.INTEGER),
schema=schema_name
)


## Create the new Table¶

Now we tell our engine to connect to the data base and create the defined table within the chosen schema.

In [ ]:
conn = engine.connect()
print('Connection established')
if not engine.dialect.has_table(conn, table_name, schema_name):
ExampleTable.create()
print('Created table')
else:


## Insert data into Table¶

Uploading the information from our DataFrame is now done with a single command. Uploading data in this way will always delete the content of the table and refill it with new values every time. If you change 'replace' to 'append', the data entries will be added to the preexisting ones. (Connecting and uploading may take a minute.)

In [ ]:
Session = sessionmaker(bind=engine)
session = Session()
try:
insert_statement = ExampleTable.insert().values(
[
]
)
session.execute(insert_statement)
session.commit()
print('Insert successful!')
except Exception as e:
session.rollback()
raise
print('Insert incomplete!')
finally:
session.close()


## Select from Table¶

Now we can query our table to see if the data arrived.

In [ ]:
Session = sessionmaker(bind=engine)
session = Session()
print(session.query(ExampleTable).all())
session.close()

In [ ]:
Session = sessionmaker(bind=engine)
session = Session()
df = pd.DataFrame(session.query(ExampleTable).all())
session.close()
df

In [ ]:



If you find bugs or if you have ideas to improve the Open Energy Platform, you are welcome to add your comments to the existing issues on GitHub.
You can also fork the project and get involved.

Please note that the platform is still under construction and therefore the design of this page is still highly volatile!