Error "Multiple definitions of primary key" when creating table

[henhuy]

Currently, I'm getting an error when creating a simple table via oedialect. Did something change in the backend? As this worked two months ago..?!

Creating table fails with error oedialect.engine.ConnectionException: HTTP 400 (Bad Request): Multiple definitions of primary key

Minimal example:

import os
import oedialect
import sqlalchemy as sa

TOKEN = os.environ["OEP_TOKEN"]

CONNECTION_STRING = f"postgresql+oedialect://henhuy:{TOKEN}@openenergy-platform.org"

engine = sa.create_engine(CONNECTION_STRING)
metadata = sa.MetaData(bind=engine)

columns = [
    sa.Column("id", sa.BIGINT, primary_key=True),
    sa.Column("name", sa.VARCHAR)
]
table = sa.Table("test_primary_key", metadata, *columns, schema="model_draft")
table.create(checkfirst=True)

Update: After removing primary_key=True from id column, creation works - but this is strange, as I don't have control over primary key constraint. Bug or Feature?

[henhuy]

Okay I looked at the code in https://github.com/OpenEnergyPlatform/oeplatform/pull/1032/files For me this seems, as setting primary_key=True should work, if column name is id and type is integer. Nevertheless, above error is raised. I think, this is due to the fact that SQLAlchemy Table sets primary_key of column to True AND additionally adds a PrimaryKeyConstraint: (Inspection of table instance from code above)

As your code @wingechr checks for primary keys in columns and constraints, above setup fails... Normally, I'd rather would be able to set primary key implicitly, instead of magically adding it behind scenes. What do you think?

[jh-RLI]

I think this is solved ... will test again soon.

[wingechr]

bei mir geht es.

• oedialect==0.0.10
• SQLAlchemy==1.3.24

import os
import oedialect
import sqlalchemy as sa

TOKEN = os.environ["OEP_API_TOKEN"]
CONNECTION_STRING = f"postgresql+oedialect://:{TOKEN}@openenergy-platform.org"

engine = sa.create_engine(CONNECTION_STRING)
metadata = sa.MetaData(bind=engine)

columns = [
    sa.Column("id", sa.BIGINT, primary_key=True),
    sa.Column("name", sa.VARCHAR)
]
table = sa.Table("test_primary_key_chw", metadata, *columns, schema="sandbox")
table.create(checkfirst=True)

[wingechr]

@henhuy please confirm and close the issue

[henhuy]

Works for me! Thanks!