Ding0 does not run with oedialect anymore with recent versions

[maurerle]

The NetworkDing0 now needs a engine as param - which is not yet changed in the tests.

The oedialect does not seem to work anymore with latest versions (see OpenEnergyPlatform/oedialect#47).

I tried to fix the packages using newer versions, but ran into the issue that NetworkDing0 did not have a engine at first,
it then did not like oedialect and if I catch this error I run into:

Traceback (most recent call last):
  File "/home/admin/ding0webservice/main.py", line 288, in <module>
    service = Ding0Webservice()
              ^^^^^^^^^^^^^^^^^
  File "/home/admin/ding0webservice/main.py", line 46, in __init__
    self.ding0_builder = Ding0Builder()
                         ^^^^^^^^^^^^^^
  File "/home/admin/ding0webservice/utils/ding0_builder.py", line 37, in __init__
    self._ding0 = NetworkDing0(self._session, name='network')
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/admin/ding0/ding0/core/__init__.py", line 167, in __init__
    self._orm = self.import_orm(session)
                ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/admin/ding0/ding0/core/__init__.py", line 1505, in import_orm
    engine = database.get_engine()
             ^^^^^^^^^^^^^^^^^^^^^
  File "/home/admin/ding0/ding0/tools/database.py", line 45, in get_engine
    f"{int(cfg_ding0.get('database_credentials', 'port'))}/{cfg_ding0.get('database_credentials', 'name')}",
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ValueError: invalid literal for int() with base 10: 'port'

I therefore could not get my application to run with new versions.
Could you help with that?

[jh-RLI]

Updating the sql-achemy version in oedialect has not been possible until now. We can take a look at it and maybe update it. My resources to work on it are rather limited at the moment. I will do my best ;)

[nesnoj]

Updating the sql-achemy version in oedialect has not been possible until now. We can take a look at it and maybe update it. My resources to work on it are rather limited at the moment. I will do my best ;)

Thank you @jh-RLI !

[Leonrtg]

Hi @ALL

I'm currently writing a Master Thesis about Open-source tools for georeferenced modelling of synthetic power distribution network topologies and I wanted to use ding0 as the basis for further investigations.
I think I have the same issue with latest ding0 version on python 3.10.

Can someone recommend me a workaround to this issue?

I would be very thankful for every help.

Best, Leon

[jh-RLI]

Hi Leon,

in general, the oedialect still works. Last time i installed ding0 was years ago. Currently, i can't really put time into it to find the root of this error :/

One good thing is that there is work happening to update the SQLAlachemy version. But this might take weeks.

[jh-RLI]

Can you try to use python version 3.8 instead? @Leonrtg

im running into another error if i do it like that. That other error is also kown and i would have to dig for it ... so it does not help much.

[jh-RLI]

Well, today I'm in luck, at least I found the reason for the other error. I need to talk to my colleague about it, maybe there's a solution for it.

Try using Python 3.8 in general and let me know if you get another error than the one described above.

[jh-RLI]

see #405

[Leonrtg]

ding0 ↔️ OEP/OEDB connectivity: TLS fixed, but oedialect advanced endpoints fail (401/500)

Hi, first of all thank you very much for your quick reply! I only took one semester of Python at university, so it took me some time to be able to give you a (hopefully) qualitative feedback with enough information.
As I'm new to programming, I worked with ChatGPT to investigate and summarize the issue as clearly as possible. I hope that is okay and the information below help to find the problem.

Thank you very much again!:)

---

TL;DR

• We fixed HTTPS/TLS to openenergyplatform.org by completing the certificate chain (AIA intermediate → custom CA bundle). Basic OEP schema/table API calls now work (e.g., /schema/grid/tables/.../rows).
• However, ding0 in versioned mode still fails when oedialect calls advanced endpoints (/api/v0/advanced/get_columns, etc.). We consistently see HTTP 401 with {"reason": "Key 'table' not found in {}"} or HTTP 500 (“Answer contains no JSON”).
• We suspect an API contract mismatch or policy/auth change on OEP’s advanced endpoints which the current oedialect client doesn’t satisfy.

---

Context / Setup

• System: macOS (Apple Silicon), pyenv + venv, Python 3.8.18, OpenSSL (Python) 3.5.2.
• ding0: dev branch
• Key packages:
  • saio==0.2.1
  • oedialect tested with 0.1.1 (current) and 0.0.10
  • SQLAlchemy==1.4.48 (note: ding0 historically preferred <=1.3.24)
  • requests 2.32.4, urllib3 2.2.3
  • egoio (version attribute not set → “unknown”)
• Engine binding: ding0.tools.database.get_engine() returns the OEP engine via egoio.tools.db.connection(readonly=True), so ding0 uses OEDB (remote) when [input_data_source] input_data = versioned.
• Trust store: Built ~/oep_ca_bundle.pem (certifi + Let’s Encrypt intermediate from AIA) and exported via REQUESTS_CA_BUNDLE so all requests (incl. oedialect) use the same trust.

---

What was broken first (TLS) and how we fixed it

Symptom
requests failed with SSLCertVerificationError: unable to get local issuer certificate. curl worked, so the issue was Python trust, not the server.

Cause
The TLS handshake didn’t include the intermediate certificate needed to chain to a trusted root. Python’s requests (certifi) didn’t auto-fetch it via AIA.

Fix

• Extract AIA “CA Issuers” URL from the leaf cert; download the intermediate; append it to certifi → ~/oep_ca_bundle.pem.
• Set REQUESTS_CA_BUNDLE=~/oep_ca_bundle.pem.
• After that, basic OEP endpoints like
  GET /api/v0/schema/grid/tables/ego_dp_mv_griddistrict/rows?limit=1
  return 200 OK with valid JSON.

(We also verified that oedialect now uses HTTPS without header-type issues by ensuring its internal urlheaders include protocol=https, host=openenergyplatform.org, port="443" as strings—this removed an earlier requests.exceptions.InvalidHeader about a non-str header.)

---

What still fails (current blocker)

When ding0 initializes, it reflects tables via saio/SQLAlchemy using oedialect. That triggers advanced endpoints such as:

• POST /api/v0/advanced/connection/open → works (returns {"content":{"connection_id": ...}}).
• POST /api/v0/advanced/get_columns → fails with 401 and JSON body:

  {"reason": "Key 'table' not found in {}"}

  This happens regardless of payload format tried (form, json, multipart) and even with both param name variants (schema/table and schema_name/table_name).

In ding0, this manifests as:

oedialect.engine.ConnectionException: Answer contains no JSON: <Response [500]>
... (call stack shows advanced/get_columns during reflection)

Conclusion: Advanced endpoints do not behave as oedialect expects on the current OEP deployment (401/500 on metadata calls) → ding0 cannot auto-reflect the needed tables and aborts during init.

---

What does work

• Direct table reads via schema API (not the advanced endpoints), for example:

  GET https://openenergyplatform.org/api/v0/schema/grid/tables/ego_dp_mv_griddistrict/rows/?limit=1
  # → 200 OK + valid JSON (after TLS fix)

• Advanced: connection/open works:

  curl -X POST https://openenergyplatform.org/api/v0/advanced/connection/open
  # → 200 OK, {"content": {"connection_id": 123...}}

---

Reproduction (compact)

curl:

# 1) Open a connection (works)
CONN=$(curl -s -X POST "https://openenergyplatform.org/api/v0/advanced/connection/open" \
  -H "Accept: application/json" \
  | python -c 'import sys,json; print(json.load(sys.stdin)["content"]["connection_id"])')

# 2) Ask for columns (fails – 401)
curl -i -X POST "https://openenergyplatform.org/api/v0/advanced/get_columns" \
  -H "Accept: application/json" \
  --data "connection_id=$CONN&schema=grid&table=ego_dp_mv_griddistrict"
# → HTTP/1.1 401 Unauthorized
#   {"reason": "Key 'table' not found in {}"}

Python:

import requests, json, os
VERIFY = os.path.expanduser("~/oep_ca_bundle.pem")
base = "https://openenergyplatform.org/api/v0"

cid = requests.post(f"{base}/advanced/connection/open",
                    headers={"Accept":"application/json"},
                    verify=VERIFY).json()["content"]["connection_id"]

r = requests.post(f"{base}/advanced/get_columns",
                  headers={"Accept":"application/json"},
                  data={"connection_id":str(cid), "schema":"grid", "table":"ego_dp_mv_griddistrict"},
                  verify=VERIFY)
print(r.status_code, r.text)  # 401 {"reason": "Key 'table' not found in {}"}

---

What we already tried (beyond TLS)

1. oedialect header/URL sanity

   • Ensured protocol=https, host=openenergyplatform.org, port="443" → prevents InvalidHeader and yields the expected base URL https://openenergyplatform.org/api/v0/....

2. Payload variants for advanced/get_columns

   • application/x-www-form-urlencoded (schema/table and schema_name/table_name)
   • application/json
   • multipart/form-data
     → All return 401 with the same {"reason": "Key 'table' not found in {}"}.

3. Diagnostic shims (local only)

   • As a pure diagnostic, we briefly shimmed oedialect.OEConnection.post to fake the shape of responses (connection lifecycle + empty metadata) to see how far reflection goes. It fails later as soon as a real advanced/* call is needed (405/500). This confirms the blocker is strictly the advanced metadata contract, not basic connectivity.

4. ding0 config sanity

   • [input_data_source] input_data = versioned → ensures the OEDB (remote) path is used.
   • Local DB credentials naming (name, not database) corrected for completeness (not used in versioned mode).

---

Hypotheses

• API contract has changed: advanced/get_columns may now require different field names or structure, or a different request type; the 401 with “Key 'table' not found in {}” could reflect a new expected envelope.
• Auth/session coupling: perhaps connection/open returns an ID, but subsequent calls need an additional cookie/token or header that oedialect doesn’t send.
• Server-side bug: The handler may incorrectly parse the payload (hence 401 + empty {} report) or fail internally (500) for valid inputs.

---

Questions

1. Is the oedialect advanced contract still valid on the current OEP instance (URL, version, parameter names)?
   • Example: What is the exact, current expected payload for advanced/get_columns?
2. Auth/Policy: Do advanced endpoints now require authentication or cookies beyond the connection_id? If yes, how should a client obtain and send them?
3. Known-good matrix: Which versions of python / sqlalchemy / oedialect / saio / ding0 are currently known to work against production OEP?
4. Fallback/offline mode: Is there a documented fallback (e.g., populate ORM from /schema/<schema>/tables/<table>/rows endpoints or local CSV/DB) so ding0 stays testable when OEP advanced endpoints are unavailable?

---

Side note: SQLAlchemy SAWarnings (unrelated)

On startup we see warnings like:

SAWarning: Ignoring declarative-like tuple value of attribute 'segment' ...
SAWarning: Ignoring declarative-like tuple value of attribute 'cable' ...

These likely stem from a dangling comma/tuple in a declarative model. They appear unrelated to the OEP advanced endpoint issue but could be cleaned up separately.

---

Thanks

Thanks a lot for any help! I'm happy to run additional tests, capture more logs, or try specific versions/patches. 🙌

[jh-RLI]

Hi @Leonrtg sorry that SSL issue regarding the certificate chain was a bug which i fixed yesterday ... so this was some extra struggle you normally should not worry about. This means you should not need this anymore VERIFY = os.path.expanduser("~/oep_ca_bundle.pem") etc.

ChatGPT seems to overthink here :) You dont need to first open the connection and then access the get_columns API-endpoint.

You should be able to just send a request there like:

import requests

PRODCTION_OEP_URL = "https://openenergyplatform.org"
REST_API = "/api/v0"
ADVANCED_API_HAS_TABLE = "/api/v0/advanced/has_table"

query = {"table": "all_ghg_emissions_by_sector", "schema": "model_draft"}

response = requests.post(
    url=f"{PRODCTION_OEP_URL}{ADVANCED_API_HAS_TABLE}",
    json={"query": query},
)

It is important to send as json data and package everything under this "query" keyword. This would usually be described in a good documentation, which is incomplete currently ....

json={"query": query},

The /advanced/connection/open endpoint is implemented for the oedialect to start connections you could use it like that but it is currently not intended to be used like you did.

To answer your questions:

1. see above :)
2. Yes. Not all API endpoints need authentication. But if they need you add a header to your requests and you have to register on the openenergyplatform.org, navigate to your profile where you find your api token.

# Setup the header, replace ***** with your OEP API token
PRODCTION_HEADER = {"Authorization": "Token ****"}
...

response = requests.post(
    url=f"{PRODCTION_OEP_URL}{ADVANCED_API_HAS_TABLE}",
    headers=PRODCTION_HEADER, # - add the header to the request
    json={"query": query},
)

3. The relevant versions here are python==3.10 & sqlalchemy==1.3 the other package use their latest version and they are limited by the python and sqlalchemy version we use in the OEP ... ding0 only works on python 3.8 with the oedialect (i think). In most cases you can still use more recent python versions but especially when working with sqlalchemy you should stick to <= 3.10

4. No there is no fallback, the advanced endpoints are required to make the sqlalchemy dialect work with the OEP. But you can get the data without using a ORM as JSON data form the API.

[Leonrtg]

Hi @jh-RLI,

wow thank you so much for your quick reply! I think I came closer to make Ding0 work on my computer!
Again, as I'm not an expert, I let ChatGPT summarize the so far progress I could make and the problems I occurred.

Thank you again for your availability and help!

ChatGPT Output:

1. What works

• TLS/HTTPS: Trust is fixed; requests are correctly sent via https://openenergyplatform.org.
• Basic endpoints (Schema/Rows):
  GET /api/v0/schema/grid/tables/ego_dp_mv_griddistrict/rows?limit=1 → 200 OK (after TLS fix).
• Advanced metadata (partially):
  advanced/get_columns and advanced/get_pk_constraint return 200 OK when run without Cursor (see logs).

2. What does NOT work (blocker)

• advanced/cursor/open → 401 "Key 'connection_id' not found in {user: <...>}".
• In ding0 init (SQLAlchemy reflection via oedialect) it crashes as soon as the dialect needs a cursor (OECursor):
  → error: Could not open cursor: Key 'connection_id' not found ...
• Browser-like login via POST /accounts/login/ (username/password) fails in scripts:
  • Login-POST: 404
  • "profile probe" on /user/profile/ → 404

3. New findings / cause hypotheses

• Payload form for Advanced API must (still) be sent as JSON with a "query" wrapper:
  json = {"query": {...}} (confirmed by OEP side).
• Not all advanced endpoints require auth; some do. Auth via header: Authorization: Token <OEP_TOKEN>.
• The cursor workflow of the oedialect advanced API seems session-/login-bound:
  • connection/open does return an ID (200),
  • cursor/open, however, denies (401), apparently because the server session has no valid logged-in user assigned.
• The script login used "next" and probe path /user/profile/ (404). On OEP the profile is at /accounts/profile/.
  → Wrong target path explains the 404 after login and why no logged-in session is created.

4. Where it specifically fails

• Ding0 needs the oedialect cursor path for reflection; this apparently requires a real, cookie-based login session
  (or a server-side session binding), which currently does not succeed.
• Token header alone does not seem sufficient for cursor/open (even though Authorization is sent in session headers).

5. Potential problems (API/client mismatch)

• OEP advanced contract: some handlers (especially cursor) may expect a logged-in session + "query" envelope;
  oedialect uses these endpoints strictly and aborts on 401.
• Login flow in script: POST /accounts/login/ was correct, but "next" and probe URL wrong (/user/profile/ instead of /accounts/profile/).
• If your OEP account uses SSO (RegApp/OIDC), username/password POST technically fails (like in UI: "Sign in through your institution");
  then a pure requests-based OIDC flow is much more complex.

7. Short conclusion

• Basic API works; advanced metadata partly works; cursor/transaction endpoints block without a valid login session.
• Main lever: establish a correct cookie-login (no OIDC) and/or confirm whether cursor endpoints accept token auth.

[jh-RLI]

This Sounds a bit like you have issues to provide your OEP Token to ding0? Im Not on my PC today. Maybe you can Share how you run ding0?

[Leonrtg]

Hi @jh-RLI,

Apologies for the delayed reply.

I originally ran DING0 on my ARM-based Mac. To properly replicate DING0, I have now switched to an x86_64-based terminal (via Rosetta), since otherwise I would have needed to install newer package versions due to missing wheels for the ARM architecture.

Unfortunately, DING0 still does not run successfully.
I am using the example script example_single_grid_district.py located in ding0/ding0/examples/.
I had to modify it slightly — the session argument needs to be passed directly to NetworkDing0(...), while it was originally passed to nd.run_ding0(...):

nd = NetworkDing0(name="network", session=session)

# choose MV Grid Districts to import
mv_grid_districts = [460]

# run DING0 on selected MV Grid District
nd.run_ding0(mv_grid_districts_no=mv_grid_districts)

The OEP token was set via egoio using the keyring module as follows:

# python store_oep_token.py
import keyring
SERVICE = "oep"      # conventionally used by egoio
USERNAME = "xyz"
TOKEN = "xyz"
keyring.set_password(SERVICE, USERNAME, TOKEN)
print("Token stored in keyring.")

When running the example example_single_grid_district.py, I still receive the following error:

AttributeError: 'OEConnection' object has no attribute 'connect'

The traceback terminates at:
.../oedialect/dialect.py", line 273, in execute_with_cursor
→ with connection.connect() as conn:

This suggests that oedialect (version 0.0.10) attempts to call a connect() method on the OEConnection object, which does not exist in the current implementation.

All previous workarounds (stateless patch, Open/Ping/Close stubs) did not change this behavior.
It seems that the incompatibility lies within oedialect itself — specifically in the OEDialect.set_isolation_level() routine, which appears to be incompatible with the current OEP API (v0).

ChatGPT attempted various workarounds, but none of them fully resolved the issue.

Thank you very much in advance for your support!

[jh-RLI]

Hi, for me the thing about the session is not true. I had to pass it to the method and not the class initialization. Then it is running but still fails due to #405.

Have you tried to use a debugger? In pycharm or vscode or whatever IDE you might use? :)

This is what i have installed in my python environment

Package Version

---

argparse 1.4.0
attrs 25.3.0
backports-tarfile 1.2.0
blosc2 2.0.0
certifi 2025.1.31
cffi 1.17.1
charset-normalizer 3.4.1
click 8.1.8
click-plugins 1.1.1
cligj 0.7.2
cryptography 44.0.2
cycler 0.12.1
cython 3.0.12
decorator 4.4.2
descartes 1.1.0
ding0 0.2.1
egoio 0.4.8
fiona 1.10.1
geoalchemy2 0.6.3
geographiclib 1.52
geopandas 0.9.0
geopy 2.1.0
idna 3.10
importlib-metadata 8.5.0
importlib-resources 6.4.5
jaraco-classes 3.4.0
jaraco-context 6.0.1
jaraco-functools 4.1.0
jeepney 0.9.0
keyring 25.5.0
keyrings-alt 5.0.2
kiwisolver 1.4.7
linecache2 1.0.0
matplotlib 3.4.2
more-itertools 10.5.0
msgpack 1.1.0
networkx 2.5.1
numexpr 2.8.6
numpy 1.24.4
oedialect 0.0.10
oemetadata 2.0.2
packaging 24.2
pandas 1.2.4
pillow 10.4.0
ply 3.11
psycopg2-binary 2.9.10
py-cpuinfo 9.0.0
pycparser 2.22
pyomo 6.8.2
pyparsing 3.1.4
pyproj 3.0.1
pypsa 0.17.1
python-dateutil 2.9.0.post0
pytz 2025.2
requests 2.32.3
scipy 1.10.1
seaborn 0.11.1
secretstorage 3.3.3
shapely 1.7.1
six 1.17.0
sqlalchemy 1.3.24
tables 3.8.0
traceback2 1.4.0
unittest2 1.1.0
urllib3 2.2.3
zipp 3.20.2

[jh-RLI]

not sure if this is correct

# python store_oep_token.py
import keyring
SERVICE = "oep"      # conventionally used by egoio
USERNAME = "xyz"
TOKEN = "xyz"
keyring.set_password(SERVICE, USERNAME, TOKEN)
print("Token stored in keyring.")

I think you should have a config file in your user directory. In terminal try to use cd ~ and then look for a directory called .egoio in there should be a config file config.ini with the following:

[oep]
dialect = oedialect
username = Jonas.Huber
host = openenergyplatform.org
port = 80
database = oedb
password = ***********

[Leonrtg]

Thank you again for the quick reply. I now changed the versions of all my packages to the ones you mentioned.

With pip check, however, I get the following output:

ding0 0.2.1 has requirement numpy~=1.21.2, but you have numpy 1.24.4.
ding0 0.2.1 has requirement scipy~=1.7.1, but you have scipy 1.10.1.
ding0 0.2.1 has requirement shapely<2.0.0,>=1.8, but you have shapely 1.7.1.

Bug with the session argument

When I leave the example_single_grid_district.py as it is, I get the following error:

Traceback (most recent call last):
  File "example_single_grid_district_patched.py", line 42, in <module>
    nd = NetworkDing0(name='network')
TypeError: __init__() missing 1 required positional argument: 'session'

With the above patch I don't get this error anymore.

Configuration & Token

Then I found out, that one major problem were this lines in the config_db_tables.cfg file in the config folder:

 [input_data_source]
 input_data = local

I changed it to the following, so Ding0 would not search for a local DB and access the token in the config.ini of .egoio.

[input_data_source]
input_data = versioned

The OEP config for egoio ~/.egoio/config.ini is set as follows:

[oep]
dialect = oedialect
username = <myuser>
host = openenergyplatform.org
port = 80
database = oedb
password = <OEP_API_TOKEN or secret>

But it still didn't worked and I found out, that I had in ~/.ding0/config/config_db_credentials.cfg` the following content:

[database_credentials]
name = name
host: ip_address
port: port
user: username
password: password

This caused int() errors. I got it out of the way by renaming it. This ensures Ding0 won’t try the local DB path unintentionally.

A Sanity check from ChatGPT: from egoio.tools import db; db.connection(readonly=True) → URL postgresql+oedialect://openenergy-platform.org confirmed.

---

Then I'm stuck to this problem:

File ".venv/lib/python3.8/site-packages/oedialect/engine.py", line 45, in __init__
    response = self.post("advanced/connection/open", {})["content"]
  File ".venv/lib/python3.8/site-packages/oedialect/engine.py", line 248, in post
    process_returntype(ans, json_response)
  File ".venv/lib/python3.8/site-packages/oedialect/engine.py", line 262, in process_returntype
    raise ConnectionException(
oedialect.engine.ConnectionException: HTTP 405 (Method Not Allowed):

With ChatGPT i tried to force to https but I didn't find a solution yet. The following is the summary, what I tried with ChatGPT to solve this problem (without finding a soluton).

Repro & Baseline Errors

• With vanilla oedialect (and ding0 loading ORM/reflecting), first errors were:

  • AttributeError: 'OEConnection' object has no attribute 'connect' (SQLAlchemy dialect expected a context manager).
  • HTTP 405 (Method Not Allowed) on advanced/connection/open or follow-up advanced calls.
  • Later, after progressing: HTTP 401 Unauthorized: {"reason": "Key 'connection_id' not found ...}" on advanced/cursor/open and
    {"reason": "Key 'table' not found in {}"} on advanced/get_columns.

• Basic endpoints did work (after TLS trust was fixed upstream):

  • GET /api/v0/schema/grid/tables/ego_dp_mv_griddistrict/rows?limit=1 → 200 OK with JSON.

---

Workarounds / Patches Attempted (local, diagnostic only)

⚠️ These are local monkey patches used for diagnostics to understand where it fails. Not proposed as a final solution.

1. Provide a minimal .connect() context manager on OEConnection:

   def _connect(self):
       class _Ctx:
           def __init__(self, c): self.connection = c
           def __enter__(self):  return self
           def __exit__(self, exc_type, exc, tb): return False
       return _Ctx(self)
   
   oe.OEConnection.connect = _connect

   Why: oedialect (in this version) uses with connection.connect(): ... during reflection.

2. Make isolation-level setter a no-op (compat with SQLAlchemy expectations):

   def _no_isolation(*_a, **_k):
       return None
   
   oedi.OEDialect.set_isolation_level = _no_isolation

3. Force HTTPS + add Authorization header + carry connection_id across advanced calls:

   def _force_https_post(self, endpoint, payload=None, return_type=None, **kw):
       # resolve host from self/base_url; default to openenergyplatform.org
       # load token from ~/.egoio + keyring
       # build: url = f("https://{host}/api/v0/{endpoint}")
       # headers["Authorization"] = f"Token {token}"
       # include self._id as connection_id for non-open calls
       # requests.post(..., json=payload)
       # on open: store self._id = content["connection_id"]
       # return {"content": ...}
   oe.OEConnection.post = _force_https_post

   Effect: This eliminated earlier TLS/header type issues and ensured all advanced calls used HTTPS and the same auth scheme we use for schema endpoints. We also printed helpful debug lines like:

   [FORCE POST] advanced/connection/open auth=yes conn_id_out=no
   [FORCE POST] advanced/cursor/open auth=yes conn_id_out=yes
   

4. Patch get_columns_raw to send a sane payload (schema + table) for reflection:

   def _patched_get_columns_raw(self, connection, table_name, schema, **kw):
       query = {"table": table_name, "schema": schema}
       resp = connection.connection.post("advanced/get_columns", query)
       return resp.get("content", resp)
   
   oedi.OEDialect.get_columns_raw = _patched_get_columns_raw

   Observation: With this patch, the request clearly hit advanced/get_columns, but we still saw:

   • 401 {"reason": "Key 'table' not found in {}"} or
   • 500 Internal Server Error when closing cursors after earlier failures.

5. Key insight from OEP maintainers: Advanced endpoints expect payload wrapped as json={"query": {...}} — i.e. a "query" envelope.

   • Our local shim initially posted {"table": "...", "schema": "..."} without a query wrapper.
   • Adjusting to json={"query": {"table": "...", "schema": "..."}} is the next step to align with the current API contract.
   • Some endpoints (esp. cursor lifecycle) might also require specific session handling or different auth semantics.

---

What the logs looked like during these steps

• Before shaping payloads correctly:

  [FORCE POST] advanced/connection/open auth=yes conn_id_out=no
  [FORCE POST] advanced/connection/rollback auth=yes conn_id_out=yes
  [FORCE POST] advanced/get_columns auth=yes conn_id_out=yes
  ...
  ConnectionException: HTTP 401 Unauthorized: {"reason": "Key 'table' not found in {}"}
  

• Earlier state (before fixing auth/https/carrying conn_id) included:

  • HTTP 405 Method Not Allowed
  • HTTP 401 Unauthorized: Key 'connection_id' not found ...
  • ValueError: dictionary update sequence element #0 has length 1; 2 is required inside oedialect cursor handling after a server 500 on close

These indicate: transport/auth now works, but request shape for the advanced endpoints must follow the current "query": {...} contract, and cursor endpoints may have additional expectations.

---

Current Status

• Good: TLS/HTTPS is fine; basic schema endpoints are fine; egoio engine resolves; reflection starts.
• Blocker: Advanced metadata endpoints from oedialect still fail when used by SQLAlchemy reflection unless the payload is wrapped as json={"query": {...}}. Our preliminary get_columns_raw shim still sent a flat dict; this needs adjustment across all advanced calls used by reflection (columns, PK, FKs, etc.).
• Likely next action: Update (or locally patch) oedialect so every advanced call posts json={"query": {...}} and, where required, includes the connection_id and auth. That would remove the 401/500s seen during reflection.

---

Open Questions

1. Exact payloads for all advanced endpoints used by oedialect reflection (get_columns, get_pk_constraint, get_foreign_keys, cursors, …): Do they all require json={"query": {...}}? Any field name changes?
2. Auth semantics: Is Authorization: Token <...> sufficient for cursor endpoints, or do they require cookie/session auth?
3. Reference matrix: Known-good versions (python/sqlalchemy/oedialect/saio/ding0) that currently work against production OEP.
4. Upstream fix path: Is there an ongoing update of oedialect to the current advanced API? Happy to test a branch or PR.

---

Minimal Repro Snippets

Basic rows (works):

curl -s "https://openenergyplatform.org/api/v0/schema/grid/tables/ego_dp_mv_griddistrict/rows?limit=1"

Advanced get_columns (current contract, per maintainer):

import requests

base = "https://openenergyplatform.org/api/v0"
query = {"table": "ego_dp_mv_griddistrict", "schema": "grid"}

r = requests.post(f"{base}/advanced/get_columns", json={"query": query})
print(r.status_code, r.text)

---

Thank very much again! I hope I can offer some information so its easier to see potential problems.

Best
Leon