0

For the sake of this example I have four tables:

  1. ModelType
  2. ModelTypeA
  3. ModelTypeB
  4. Model

I am trying to model the following relationship among these tables: enter image description here

To do that I am defining the classes using SQL Alchemy v1.4 in the following way:

Base = declarative_base()

class ModelType(Base):
    __tablename__ = "modeltype"

    id = Column(Integer, primary_key=True)
    algorithm = Column(String)

    models = relationship("Model", back_populates="modeltype")

    __mapper_args__ = {
        "polymorphic_identity": "modeltype",
        "polymorphic_on": algorithm
    }

    def __repr__(self):
        return f"{self.__class__.__name__}({self.algorithm!r})"

class ModelTypeA(ModelType):
    __tablename__ = "modeltypea"

    id = Column(Integer, ForeignKey("modeltype.id"), primary_key=True)
    parameter_a = Column(Integer)

    __mapper_args__ = {
        "polymorphic_identity": "Model Type A"
    }
    
class ModelTypeB(ModelType):
    __tablename__ = "modeltypeb"

    id = Column(Integer, ForeignKey("modeltype.id"), primary_key=True)
    parameter_a = Column(Integer)

    __mapper_args__ = {
        "polymorphic_identity": "Model Type B"
    }

class Model(Base):
    __tablename__ = "model"

    id = Column(Integer, primary_key=True)
    trainingtime = Column(Integer)
    modelversionid = Column(Integer, ForeignKey("modeltype.id"))
    
    modeltype = relationship("ModelType", back_populates="models")

    def __repr__(self) -> str:
        return f"Model(id={self.id!r}, trainingtime={self.trainingtime!r})"

I am able to create the tables with:

with Session(engine) as session:
    Base.metadata.create_all(engine)

This emits the following create table sql statements:

CREATE TABLE modeltype (
        id INTEGER NOT NULL AUTOINCREMENT, 
        algorithm VARCHAR, 
        PRIMARY KEY (id)
)

CREATE TABLE modeltypea (
        id INTEGER NOT NULL, 
        parameter_a INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(id) REFERENCES modeltype (id)
)

CREATE TABLE modeltypeb (
        id INTEGER NOT NULL, 
        parameter_b INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(id) REFERENCES modeltype (id)
)

CREATE TABLE model (
        id INTEGER NOT NULL AUTOINCREMENT, 
        trainingtime INTEGER, 
        modelversionid INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(modelversionid) REFERENCES modeltype (id)
)

This looks good to me. But now when I try to actually create a model with reference to a modeltype like so:

model_type_a = ModelTypeA(parameter_a=3)

model = Model(trainingtime=10, modeltype=model_type_a)

session.add(model)
session.commit()

I get the following warning:

SAWarning: Column 'modeltypea.id' is marked as a member of the primary key for table 'modeltypea', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed. Primary key columns typically may not store NULL.

and then the following error:

sqlalchemy.exc.IntegrityError: (snowflake.connector.errors.IntegrityError) 100072 (22000): NULL result in a non-nullable column [SQL: INSERT INTO modeltypea (parameter_a) VALUES (%(parameter_a)s)] [parameters: {'parameter_a': 3}] (Background on this error at: https://sqlalche.me/e/14/gkpj)

So it seems like when I create an instance of the inherited class ModelTypeA, it is not creating a row in ModelType and thus it has no id in ModelType to refer to and is instead trying to use NULL. How am I supposed to add rows when I have this sort of inheritance?

1 Answer 1

0

With help from someone on Github discussions, it turned out that this is a Snowflake specific issue. To make it work with Snowflake, I needed to add Sequence("<name>") to my id Columns. That looked like this:

from sqlalchemy import Sequence

class ModelType(Base):
    __tablename__ = "modeltype"

    id = Column(Integer, Sequence("modeltype_id_seq"), primary_key=True)
    algorithm = Column(String(50))

    models = relationship("Model", back_populates="modeltype")

    __mapper_args__ = {
        "polymorphic_identity": "modeltype",
        "polymorphic_on": algorithm
    }

    def __repr__(self):
        return f"{self.__class__.__name__}({self.algorithm!r})"

class ModelTypeA(ModelType):
    __tablename__ = "modeltypea"

    id = Column(Integer, ForeignKey("modeltype.id"), primary_key=True)
    parameter_a = Column(Integer)

    __mapper_args__ = {
        "polymorphic_identity": "Model Type A"
    }

class Model(Base):
    __tablename__ = "model"

    id = Column(Integer, Sequence("model_id_seq"), primary_key=True)
    trainingtime = Column(Integer)
    modelversionid = Column(Integer, ForeignKey("modeltype.id"))

    modeltype = relationship("ModelType", back_populates="models")

    def __repr__(self) -> str:
        return f"Model(id={self.id!r}, trainingtime={self.trainingtime!r})"

This is documented here https://github.com/snowflakedb/snowflake-sqlalchemy#auto-increment-behavior

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.