Create MIDAS SQLAlchemy ORM models

[isedwards]

Create SQLAlchemy 2.0-style ORM models for: MIDAS - requires opencdms-test-data#2 (Oracle Express)

[faysal-ishtiaq]

@isedwards

I have tried to generate the models using sqlacodegen

I followed these steps:

• Cloned https://github.com/opencdms/met-office-midas
• docker-compose up -d --build from project root

  $ docker exec -it midas bash
  $ sqlplus # use metadata as username and password
  $ SQL> @/scripts/2.sql
  $ SQL> @/scripts/3.sql
  $ SQL> @/scripts/4.sql

  The down loaded oracle instant client 19.13 and sqlacodegen oracle+cx_oracle://metadata:metadata@127.0.0.1:1521/?service_name=XE

But it throws

01:51:29 (venv) faysal@pop-os met-office-midas ±|main ✗|→ sqlacodegen oracle+cx_oracle://metadata:metadata@127.0.0.1:1521/?service_name=XE
/home/faysal/PycharmProjects/met-office-midas/venv/lib/python3.8/site-packages/sqlacodegen/main.py:47: SAWarning: Did not recognize type 'SDO_GEOMETRY' of column 'src_geom'
  metadata.reflect(engine, args.schema, not args.noviews, tables)
Traceback (most recent call last):
  File "/home/faysal/PycharmProjects/met-office-midas/venv/bin/sqlacodegen", line 8, in <module>
    sys.exit(main())
  File "/home/faysal/PycharmProjects/met-office-midas/venv/lib/python3.8/site-packages/sqlacodegen/main.py", line 51, in main
    generator = CodeGenerator(metadata, args.noindexes, args.noconstraints, args.nojoined,
  File "/home/faysal/PycharmProjects/met-office-midas/venv/lib/python3.8/site-packages/sqlacodegen/codegen.py", line 424, in __init__
    if isinstance(table.c[colname].type, String):
  File "/home/faysal/PycharmProjects/met-office-midas/venv/lib/python3.8/site-packages/sqlalchemy/sql/base.py", line 1210, in __getitem__
    return self._index[key]
KeyError: '  prime_capability_flag'

Looks like sqlacodegen cannot handle SDO_GEOMETRY

Then I tried this as a test:

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy import Column, Integer

db_engine = create_engine("oracle+cx_oracle://metadata:metadata@127.0.0.1:1521/?service_name=XE")
Session = sessionmaker(autocommit=False, autoflush=False, bind=db_engine)
Base = declarative_base()

class EqptCalibCoeff(Base):
    __tablename__ = "EQPT_CALIB_COEFF"
    CALIB_COEFF_MSRT_ID = Column(Integer, primary_key=True)
    EQPT_TYPE_CALIB_COEFF_ID = Column(Integer)
    EQPT_CALIB_ID = Column(Integer)
    CALIB_COEFF_VAL = Column(Integer)

def test_should_successfully_query_eqpt_calib_coeff():
    session = Session()
    session.query(EqptCalibCoeff).all()
    assert True

It passed.

Should I try writing SQLAlchemy models manually?

[isedwards]

@chinedu117 would you like to pick this up and see if you can create models for the Oracle version of the midas schema?

The earliest issues in this repository, that are now closed, give details of the process, e.g. https://github.com/opencdms/pyopencdms/issues/9