Integrate oedialect

[ulfmueller]

Wouldn't it be useful to include the oedialect as a install_require to the setup.py of ego.io?

@MGlauer you said the setup is complete. But I was thinking about it a bit more and since the connection maker (e.g.) are enabled to use the option of the dialect I would think it made sense to include it.

Now, the following applications like eTraGo, eDisGo (...) include the oedialect), so it is ok for now. But in general I think it would be cleaner because if s.o. would only use the ego.io the connection could not be established by the oedialect...

[nesnoj]

Good point, added in f73be51.

Another annoying issue is that the DBconnection-config-keyring procedure is required, even if a user just wants to make use of ego.io to read data via oedialect. In ding0, we could do without the config+keyring stuff.

As a simple solution, what about adding another arg to connection() e.g. use_oedialect_read_only which lets the function skip this part and creates the engine straightforward?
@ulfmueller @gplssm

[gplssm]

Another annoying issue is that the DBconnection-config-keyring procedure is required, even if a user just wants to make use of ego.io to read data via oedialect. In ding0, we could do without the config+keyring stuff.

As a simple solution, what about adding another arg to connection() e.g. use_oedialect_read_only which lets the function skip this part and creates the engine straightforward?
@ulfmueller @gplssm

I did some research and trial&error on passwordless data retrieval using oedialect. No sucess! The oedialect uses request.post which requires a user and a password.

One bigger bypass to overcome forcing the user for example in ding0 to use a OEP user and token, would be to replace data queries (currently in sqlalchemy) by requests queries. See the example that would replace the query for mv_grid_districts. It's possible, but not a superquick solution.

[nesnoj]

Thanks for quick response!
Yea, guess the no-pass option only works via requests. Unfortunately, I can't contribute to this at the present time..

I'm wondering whether other devs are interested in using the OEP via requests (without pass) too?

[MGlauer]

I did some research and trial&error on passwordless data retrieval using oedialect. No sucess! The oedialect uses request.post which requires a user and a password.

@gplssm Which version of oedialect did you use?

[gplssm]

Hey @MGlauer, thanks for the quick reaction!
With

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
License: UNKNOWN
Location: /home/guido/.local/lib/python3.6/site-packages
Requires: requests, sqlalchemy
Required-by: ding0

Name: SQLAlchemy
Version: 1.2.0
Summary: Database Abstraction Library
Home-page: http://www.sqlalchemy.org
Author: Mike Bayer
Author-email: mike_mp@zzzcomputing.com
License: MIT License
Location: /home/guido/.local/lib/python3.6/site-packages
Requires:
Required-by: GeoAlchemy2

I tried the following

from sqlalchemy import create_engine
import oedialect

OED_STRING_PWLESS = 'postgresql+oedialect://openenergy-platform.org'

engine = create_engine(OED_STRING_PWLESS)
conn = engine.connect()

and get the response

Traceback (most recent call last):
  File "/home/guido/.PyCharmCE2019.1/config/scratches/oedialect_passwordless.py", line 11, in <module>
    conn = engine.connect()
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2102, in connect
    return self._connection_cls(self, **kwargs)
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 90, in __init__
    if connection is not None else engine.raw_connection()
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2188, in raw_connection
    self.pool.unique_connection, _connection)
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 345, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 782, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 532, in checkout
    rec = pool._do_get()
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1186, in _do_get
    self._dec_overflow()
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1183, in _do_get
    return self._create_connection()
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 667, in __connect
    connection = pool._invoke_creator(self)
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 105, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/guido/.local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 410, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/guido/.local/lib/python3.6/site-packages/oedialect/dbapi.py", line 7, in connect
    return OEConnection(**kwargs)
  File "/home/guido/.local/lib/python3.6/site-packages/oedialect/engine.py", line 37, in __init__
    response = self.post('advanced/connection/open', {})['content']
  File "/home/guido/.local/lib/python3.6/site-packages/oedialect/engine.py", line 227, in post
    raise ConnectionException(json_response['reason'] if 'reason' in json_response else 'No reason returned')
oedialect.engine.ConnectionException: No reason returned

[MGlauer]

Oh, right. This was fixed already but there we did not issue a new release yet. I will do that asap.

[gplssm]

An update:
I was using version 0.0.5dev oedialect, but obviously it wasn't the tip of the master branch.

After installing oedialect (branch master, latest commit 112ab588e46a3bbaddcfe0b152cf5f57d71ce207), I can query data without supplying a passwort/token. 🎉

A new release of oedialect would be great! 👏 I've heard this from several people that are interested in passwordless OEP queries.

[nesnoj]

@gplssm, do you want to submit the no-pass functionality prior to the release #77?

[gplssm]

@gplssm, do you want to submit the no-pass functionality prior to the release #77?

Yes, tonight!

[nesnoj]

Done in release v0.4.6 (also available on PYPI)