[BUG] Import fails after Update in the toep

[joda9]

Describe the bug

The oedb_legacy generator import function fails when querying the OpenEnergyPlatform (OEP) database due to a schema naming mismatch. The generated SQL references tables in the data schema, but the remote database expects tables in the supply schema (or vice versa depending on environment configuration).

To Reproduce

Steps to reproduce the behavior:

1. Run generator import tests:

   pytest tests/io/test_generators_import.py::TestGeneratorsImportOEDB::test_oedb_legacy_without_timeseries -v

2. Observe the error:

   oedialect.engine.ConnectionException: HTTP 400 (Bad Request): Table data.ego_dp_conv_powerplant_nep2035_mview not found
   

3. The issue occurs in edisgo/io/generators_import.py in the _import_conv_generators and _import_res_generators nested functions when executing SQL queries against the OEP database.

Expected behavior

The generator import should:

• Successfully query the OEP database regardless of which schema name the tables are stored under
• Automatically detect and use the correct schema (data vs supply) for the current environment
• Complete without HTTP 400 errors

Root Cause Analysis

1. Schema Mismatch: The SQLAlchemy ORM table definitions (from egoio.db_tables.supply) specify tables with schema supply (e.g., supply.ego_dp_conv_powerplant_nep2035_mview), but the compiled SQL generates queries referencing the data schema.

2. Pandas/SQLAlchemy Compatibility: The code previously used pd.read_sql_query(..., session.bind) which fails with AttributeError: 'Connection' object has no attribute 'exec_driver_sql' on connections that don't implement this method (common with custom dialects like oedialect).

3. Naive String Replacement Fails: Attempting to fix this by simple string replacement (sql.replace("data.", "supply.")) on the compiled SQL text causes TypeError in the OEP dialect's compiler when it tries to process bind parameters in a TextClause.

Additional Context

• The issue affects all oedb_legacy import tests in the test suite
• The ORM tables are imported from egoio.db_tables.supply and egoio.db_tables.model_draft
• The code needs to work across different database configurations (OEP remote, local egon-data, etc.)
• Investigation showed that egoio table objects have .fullname returning supply.ego_dp_conv_powerplant_nep2035_mview, confirming the schema is defined as supply in the ORM

Proposed Solution

Implement schema detection and reflection:

1. Before executing queries, detect which schema the target tables exist under on the remote engine
2. Either:
   • Use SQLAlchemy's Table(..., autoload_with=engine, schema=...) to reflect the correct table definition, or
   • Programmatically set the ORM table's .schema attribute to the detected schema before building queries
3. Use session.execute(statement) directly instead of pd.read_sql_query to avoid pandas/connection compatibility issues
4. Convert SQLAlchemy result sets to DataFrames manually using pd.DataFrame(result.mappings().all())

This approach will make the importer robust across different database environments without brittle string manipulation of SQL.

Related Files

• edisgo/io/generators_import.py (lines ~110-230 in oedb_legacy function)
• edisgo/edisgo.py (line ~984 where generators_import.oedb_legacy is called)
• edisgo/tools/config.py (database alias mapping configuration)

Test Cases Affected

• tests/io/test_generators_import.py::TestGeneratorsImportOEDB::test_oedb_legacy_without_timeseries
• tests/io/test_generators_import.py::TestGeneratorsImportOEDB::test_oedb_legacy_with_worst_case_timeseries
• tests/io/test_generators_import.py::TestGeneratorsImportOEDB::test_oedb_legacy_with_timeseries_by_technology
• tests/io/test_generators_import.py::TestGeneratorsImportOEDB::test_target_capacity

[joda9]

The legacy Ding0 grid import currently fails.
All oedb_legacy-based generator import tests are affected:

• tests/io/test_generators_import.py::TestGeneratorsImportOEDB::test_oedb_legacy_without_timeseries
• tests/io/test_generators_import.py::TestGeneratorsImportOEDB::test_oedb_legacy_with_worst_case_timeseries
• tests/io/test_generators_import.py::TestGeneratorsImportOEDB::test_oedb_legacy_with_timeseries_by_technology
• tests/io/test_generators_import.py::TestGeneratorsImportOEDB::test_target_capacity
• tests/test_edisgo.py::TestEDisGo::test_generator_import
• tests/test_examples.py::TestExamples::test_edisgo_simple_example_ipynb