linkml / linkml

Linked Open Data Modeling Language
https://linkml.io/linkml
Other
297 stars 91 forks source link

Generation of SQLAlchemy classes fails when handling enums #273

Open deepakunni3 opened 2 years ago

deepakunni3 commented 2 years ago

When working with a model that has one (or more) enums defined:

id: https://w3id.org/test-schema
name: test-schema
description: >-
  A test schema

imports:
- linkml:types

default_prefix: EX

prefixes:
  EX: www.example.com/
  linkml: https://w3id.org/linkml/

classes:
  foo:
    slots:
      - prop1
      - prop2
      - prop3

slots:
  prop1:
  prop2:
  prop3:
    range: val_enum

enums:
  val_enum:
    permissible_values:
      1:
      2:
      3:

Generation of SQLAlchemy classes for the postgres:psycopg2 dialect via,

gen-sqlddl --sqla-file  test-schema-models.py --dialect postgresql+psycopg2 test-schema.yaml > test-schema.sql

Yields the following stacktrace,

Traceback (most recent call last):
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/bin/gen-sqlddl", line 8, in <module>
    sys.exit(cli())
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/linkml/generators/sqlddlgen.py", line 461, in cli
    print(gen.serialize(**args))
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/linkml/utils/generator.py", line 132, in serialize
    self.end_schema(**kwargs)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/linkml/generators/sqlddlgen.py", line 209, in end_schema
    self.generate_ddl()
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/linkml/generators/sqlddlgen.py", line 364, in generate_ddl
    schema_metadata.create_all(engine)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 4740, in create_all
    bind._run_ddl_visitor(
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/engine/mock.py", line 55, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 520, in traverse_single
    return meth(obj, **kw)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 833, in visit_metadata
    metadata.dispatch.before_create(
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 343, in __call__
    fn(*args, **kw)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 875, in __call__
    return getattr(self.target, self.name)(*arg, **kw)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/sql/sqltypes.py", line 1210, in _on_metadata_create
    t._on_metadata_create(target, bind, **kw)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2015, in _on_metadata_create
    self.create(bind=bind, checkfirst=checkfirst)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1908, in create
    bind._run_ddl_visitor(self.EnumGenerator, self, checkfirst=checkfirst)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/engine/mock.py", line 55, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 520, in traverse_single
    return meth(obj, **kw)
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1949, in visit_enum
    self.connection.execute(CreateEnumType(enum))
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/linkml/generators/sqlddlgen.py", line 347, in <lambda>
    executor= lambda sql, *multiparams, **params: print(f'{str(sql).rstrip()};'))
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 557, in __str__
    return str(self.compile())
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 480, in compile
    if self.stringify_dialect == "default":
  File "/Users/unni/GIT/ghga-metadata-schema-deepakunni3/env/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 398, in stringify_dialect
    return self.element.create_drop_stringify_dialect
AttributeError: '_PGEnum' object has no attribute 'create_drop_stringify_dialect'

Unsure if this has been observed before?

@hsolbrig @cmungall

Version of relevant dependencies,

SQLAlchemy==1.4.20
linkml==1.0.1
linkml-runtime==1.0.10
cmungall commented 2 years ago

it seems to be an issue with dialect='postgresql+psycopg2'

E.g with 'postgresql+pygresql' I get:

from dataclasses import dataclass
from dataclasses import field
from typing import List

from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy import Text
from sqlalchemy import Integer
from sqlalchemy.orm import registry
from sqlalchemy.orm import relationship

mapper_registry = registry()
metadata = MetaData()

from test_schema import *

tbl_foo = Table('foo', metadata, 
    Column('prop1', Text, primary_key=True),
    Column('prop2', Text, primary_key=True),
    Column('prop3', Text, primary_key=True),
)
mapper_registry.map_imperatively(Foo, tbl_foo, properties={
})
cmungall commented 2 years ago

my mistake.. all postgres dialects fail

mssql dialects succeed however...

cmungall commented 2 years ago

OK, it's not in the sqla mapping logic itself,

it's this:

engine = create_mock_engine(
            f'{self.dialect}://./MyDb',
            strategy='mock',
            executor= lambda sql, *multiparams, **params: print(f'{str(sql).rstrip()};'))
deepakunni3 commented 2 years ago

Yes, I did stumble upon that code block while debugging. It looks like the str(sql) is raising an error when its called for an object that is an instance of _PGEnum.

I wonder if this is an upstream (SQLAlchemy) bug or if we should be careful when we stringify the sql.

nlharris commented 2 weeks ago

Is this still needed? If so, is @cmungall really the only person who can fix it?

cmungall commented 2 weeks ago

I'm definitely not the only person who can do this!

The code Deepak highlights comes from the SQLA docs:

https://docs.sqlalchemy.org/en/20/faq/metadata_schema.html#how-can-i-get-the-create-table-drop-table-output-as-a-string

They show some different ways of doing this, also in the top answer here:

https://stackoverflow.com/questions/2128717/sqlalchemy-printing-raw-sql-from-create

If this issue persists then we file an upstream

deepakunni3 commented 2 weeks ago

Thank you @cmungall

Given that this ticket is 3 years old and there is no immediate use-case, perhaps we can close this ticket.

If it comes up again, we can reopen or create a new issue for tracking this.