[Python] sqlachemy self-referential many-to-many relation with foreign key on other class

I have the following models in sqlalchemy:

class UUIDMixin:
id: Mapped[UUID] = mapped_column(primary_key=True)


class Location(Base, UUIDMixin):
__tablename__ = "locations"

predictions: Mapped[list[Prediction]] = relationship(
back_populates="location",
foreign_keys="Prediction.location_id",
)


prediction_relations = Table(
"prediction_relations",
Base.metadata,
Column("dependant_prediction_id", ForeignKey("predictions.id"), primary_key=True),
Column("underlying_prediction_id", ForeignKey("predictions.id"), primary_key=True),
)


class Prediction(Base, UUIDMixin):
__tablename__ = "predictions"

location_id: Mapped[UUID] = mapped_column(ForeignKey("locations.id"))
location: Mapped[Location] = relationship(
back_populates="predictions", foreign_keys=[location_id]
)
underlying_predictions: Mapped[list["Prediction"]] = relationship(
"Prediction",
secondary=prediction_relations,
primaryjoin="Prediction.id == prediction_relations.c.dependant_prediction_id",
secondaryjoin="Prediction.id == prediction_relations.c.underlying_prediction_id",
back_populates="dependant_predictions",
)
dependant_predictions: Mapped[list["Prediction"]] = relationship(
"Prediction",
secondary=prediction_relations,
primaryjoin="Prediction.id == prediction_relations.c.underlying_prediction_id",
secondaryjoin="Prediction.id == prediction_relations.c.dependant_prediction_id",
back_populates="underlying_predictions",
)


When I try to add a prediction that has an underlying prediction I get the following error: sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: predictions.location_id.

Is it possible to store them both together or do I have to commit the underlying prediction first before the dependant prediction can be commited?

Continue reading...