Add all OEP tables as SQLA classes in oedialect

[Ludee]

It would make sense to have all OEP tables as SQLAlchemy classes in a package. In open_eGo we used ego.io to have a shared collection.

I also remember a script to create all existing ones: https://github.com/openego/ego.io/blob/dev/egoio/tools/sqlacodegen_oedb.sh

[MGlauer]

I don't think this is a good idea as the OEP landscape is way too volatile and potentially big. I think it would be better to autogenerate the corresponding python class and provide it in the data view for each table.

[Ludee]

That's even better. I got pointed at these SQLA extension for auto-generated classes: https://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html https://docs.sqlalchemy.org/en/latest/core/reflection.html#reflecting-database-objects

[jh-RLI]

This is helpful to auto generate the table model Python code if needed (SqlAlchemy ORM Class): https://pypi.org/project/sqlacodegen/ example: https://simpletutorials.com/c/2220/SQLAlchemy+Code+Generation

[coroa]

I just had the same problem and combined the autoloading recommendation from Hendricks sqlalchemy tutorial

# table declaration: read from DB
class POI(Base):
    __tablename__ =  'lis_charging_poi'
    __table_args__ = {'schema': 'model_draft', 'autoload': True}

with a small hack taking advantage of python's module loading internals to get a very intuitively usable table loading tool https://github.com/coroa/saio .

Can anyone of you, who actually knows sqlalchemy judge whether that is a good or bad idea?

[Ludee]

In general, this looks like a brilliant idea. I tried to test and implement the solution, but it didn't work yet:

• installed saio with pip -e
• import import saio saio.register_schema("boundaries", engine)
• create class class VG250GEM(Base): __tablename__ = 'bkg_vg250_6_gem' __table_args__ = {'schema': 'boundaries', 'autoload': True}

NameError: name 'Base' is not defined

Is it because of the schema? Or what did I do wrong?

[MGlauer]

I have not looked into it, yet, but from a glance I guess you want to reflect tables into a declarative schemy. Thus, you need a declarative base: from sqlalchemy.ext.declarative import declarative_base Base = declarative_base()

[Ludee]

Yes, I just found that as well.

from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() metadata = Base.metadata metadata.bind=engine

class VG250STA(Base): __tablename__ = 'bkg_vg250_20160101_1_sta' __table_args__ = {'schema': 'model_draft', 'autoload': True}

from saio.model_draft import VG250STA

ImportError: cannot import name 'VG250STA'

[coroa]

you misunderstood how it was intended to be used. Instead:

import saio
saio.register_schema('model_draft', engine)
from saio.model_draft import BkgVg250201601011Sta as VG250STA

(edit: that's how it worked yesterday, see next post for today!)

and then you can do queries like your used to

session.query(VG250STA).all()

[coroa]

The strange name BkgVg250201601011Sta is generated automatically from the table name bkg_vg250_20160101_1_sta, by rendering it into PascalCase. Unfortunately the name is quite unreadable then.

It would also be easy to change this to: (edit: This is how it works now!)

saio.register_schema('model_draft', engine)
from saio.model_draft import bkg_vg250_20160101_1_sta as VG250STA

Do you think that would be better? (edit: I did!)

Note also that in jupyter after running saio.register_schema you can type from saio.model_draft import <TAB> to complete the available names.

[coroa]

I have not looked into it, yet, but from a glance I guess you want to reflect tables into a declarative schemy. Thus, you need a declarative base: from sqlalchemy.ext.declarative import declarative_base Base = declarative_base()

In the background saio takes care of setting up the declarative base for each schema. After register_schema('model_draft', engine), the base can be accessed by importing it, ie from saio.model_draft import Base.

[coroa]

* installed saio with pip -e

It is not necessary to use -e for installing a package, which is not registered in pypi.

1. For normal installation from the latest commit on master, use:

   pip install git+https://github.com/coroa/saio.git

2. In an environment.yaml or requirements.txt file put it as: git+https://github.com/coroa/saio.git#egg=saio

The use-case for -e is when you actively develop a package yourself. So, I have a directory where I develop saio, where I commit and push, and so that my python environment takes note of what I am doing there I install it with

pip install -e .

or actually since I am using conda

conda develop .

[jh-RLI]

Can I close this? @coroa @Ludee

[christian-rli]

saio seems to close this @jh-RLI . Thanks for having a look out. I'll go ahead with closing this issue. If anyone feels differently about this, you may revert it.