# 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__    = "christian-rli, oakca"

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


Pandas has a read_csv function which makes importing a csv-file rather comfortable. It reads csv into a DataFrame. By default, it assumes that the fields are comma-separated. Our example file has columns with semicolons as separators, so we have to specify this when reading the file.

The example file for this tutorial ('DataTemplate.csv') is in a 'data' directory, one level above the file for this tutorial. Make sure to adapt the path to the file you're using if your file is located elsewhere.

In [ ]:
example_df = pd.read_csv('../data/TemplateData.csv', encoding='utf8', sep=';')


Looking at the first three lines of our dataframe:

In [ ]:
example_df[:3]


## 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 [ ]:
# White spaces in the username are fine!
token = getpass.getpass('Token:')


Now we'll create an sql-alchemy-engine. The engine is what 'speaks' oedialect to the data base api. We need to tell it where the data base is and pass our credentials.

In [ ]:
# Create Engine:
OEP_URL = 'openenergy-platform.org' #'193.175.187.164' #'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_table'
schema_name = 'sandbox'

ExampleTable = sa.Table(
table_name,
sa.Column('variable', sa.VARCHAR(50)),
sa.Column('unit', sa.VARCHAR(50)),
sa.Column('year', sa.INTEGER),
sa.Column('value', sa.FLOAT(50)),
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:
example_df.to_sql(table_name, conn, schema='sandbox', if_exists='replace')
print('Inserted to ' + table_name)
except Exception as e:
session.rollback()
raise
print('Insert incomplete!')
finally:
session.close()


You can also insert data manually into the table.

In [ ]:
Session = sessionmaker(bind=engine)
session = Session()
try:
insert_statement = ExampleTable.insert().values(
[
dict(variable='fairy dust', unit='t', year=2020, value=200),
dict(variable='mana', unit='kg', year=1999, value=120),
dict(variable='the force', unit='l', year=1998, value=1100)
]
)
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()


## Storing Query Result in DataFrame¶

We can write the results of the query back into a DataFrame, where it's easier to handle.

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

In [12]:
pip show sqlalchemy

Name: SQLAlchemy
Version: 1.2.18
Summary: Database Abstraction Library
Home-page: http://www.sqlalchemy.org
Author: Mike Bayer
Author-email: mike_mp@zzzcomputing.com
Location: c:\anaconda3\envs\py36-oep\lib\site-packages
Requires:
Required-by: GeoAlchemy2
Note: you may need to restart the kernel to use updated packages.

In [13]:
pip show oedialect

Name: oedialect
Version: 0.0.5.dev0
Summary: SQL-Alchemy dialect for the OpenEnergy Platform
Home-page: https://github.com/openego/oedialect
Author: MGlauer
Author-email: martinglauer89@gmail.com