Relationship between two models without constraints or cascades

17 views
Skip to first unread message

Jack Matthews

unread,
Mar 14, 2021, 7:49:21 AM3/14/21
to sqlalchemy
The database I am trying to maintain is a representation of a configuration file I have scraped from a network device. I have two models that when both exist in the configuration are related to each other, but it is also possible that one or the other may not be present. The schemas are included below.

These models have compound primary keys for node_id, interface and unit_num. Where these 3 values are the same on both objects, there is a one to one relationship between them. However, it's possible that a Unit object could exist without a matching CosInterface and vice versa.

class Unit(Base):
    __tablename__ = 'units_juniper'

    id = Column(Integer, autoincrement=True, nullable=False, unique=True)
    node_id = Column(Integer, primary_key=True, nullable=False, index=True)
    interface = Column(String(32), primary_key=True, nullable=False, index=True)
    unit_num = Column(Integer, primary_key=True, nullable=False, index=True)
    ....

class CosInterface(Base):
    __tablename__ = "cos_interfaces_juniper"
    id = Column(Integer,autoincrement=True)
    node_id = Column(Integer, ForeignKey("nodes_juniper.id"), primary_key=True, nullable=False, index=True)
    interface = Column(String(15), primary_key=True, unique=False, nullable=False)
    unit_num = Column(String(15), primary_key=True, unique=False, nullable=False)
    ....

    unit = relationship('Unit', foreign_keys=[Unit.node_id,Unit.interface,Unit.unit_num], uselist=False, lazy='select', backref="cos_interface",
                            primaryjoin=and_(Unit.node_id == node_id, Unit.interface == Interface, Unit.unit_num == unit_num))

The scenario I am having an issue is when I have a Unit and CosInterface in the database with a working one to one relationship. I would like to delete the CosInterface object, and leave the Unit object in the database. Currently I get the following error:

AssertionError: Dependency rule tried to blank-out primary key column 'units_juniper.node_id' on instance '<Unit at 0x7f97e3557f28>'

I understand why this is happening, and to solve this I should have a cascade delete rule to remove the corresponding Unit object. However, as I explained, the Unit object can exist without the CosInterface, so I don't want to delete the Unit object.

All I'm looking to achieve is for these models to have an attribute I can call (e.g. Unit.cos_interface), that when present returns the corresponding model object. If there isn't one in the database then it can just return None. I suspect I'm using Relationship() incorrectly here, but I haven't been able to find the proper solution to this online. I guess essentially I would just like to have an attribute on the model that returns the result of a query, e.g.

class Unit(Base):
    __tablename__ = 'units_juniper'

    id = Column(Integer, autoincrement=True, nullable=False, unique=True)
    node_id = Column(Integer, primary_key=True, nullable=False, index=True)
    interface = Column(String(32), primary_key=True, nullable=False, index=True)
    unit_num = Column(Integer, primary_key=True, nullable=False, index=True)
    ....
    cos_interface = CosInterface.filter(CosInterface.node_id == node_id, CosInterface.interface == interface, CosInterface.unit_num == unit_num).first()

Is there a way to do this?

Cheers,
Jack




Mike Bayer

unread,
Mar 14, 2021, 10:28:20 AM3/14/21
to noreply-spamdigest via sqlalchemy


On Sun, Mar 14, 2021, at 7:49 AM, Jack Matthews wrote:
The database I am trying to maintain is a representation of a configuration file I have scraped from a network device. I have two models that when both exist in the configuration are related to each other, but it is also possible that one or the other may not be present. The schemas are included below.

These models have compound primary keys for node_id, interface and unit_num. Where these 3 values are the same on both objects, there is a one to one relationship between them. However, it's possible that a Unit object could exist without a matching CosInterface and vice versa.

class Unit(Base):
    __tablename__ = 'units_juniper'

    id = Column(Integer, autoincrement=True, nullable=False, unique=True)
    node_id = Column(Integer, primary_key=True, nullable=False, index=True)
    interface = Column(String(32), primary_key=True, nullable=False, index=True)
    unit_num = Column(Integer, primary_key=True, nullable=False, index=True)
    ....

class CosInterface(Base):
    __tablename__ = "cos_interfaces_juniper"
    id = Column(Integer,autoincrement=True)
    node_id = Column(Integer, ForeignKey("nodes_juniper.id"), primary_key=True, nullable=False, index=True)
    interface = Column(String(15), primary_key=True, unique=False, nullable=False)
    unit_num = Column(String(15), primary_key=True, unique=False, nullable=False)
    ....

    unit = relationship('Unit', foreign_keys=[Unit.node_id,Unit.interface,Unit.unit_num], uselist=False, lazy='select', backref="cos_interface",
                            primaryjoin=and_(Unit.node_id == node_id, Unit.interface == Interface, Unit.unit_num == unit_num))

The scenario I am having an issue is when I have a Unit and CosInterface in the database with a working one to one relationship. I would like to delete the CosInterface object, and leave the Unit object in the database. Currently I get the following error:

AssertionError: Dependency rule tried to blank-out primary key column 'units_juniper.node_id' on instance '<Unit at 0x7f97e3557f28>'

I understand why this is happening, and to solve this I should have a cascade delete rule to remove the corresponding Unit object. However, as I explained, the Unit object can exist without the CosInterface, so I don't want to delete the Unit object.

that would mean you have the foreign_keys setting backwards.   the columns on CosInterface would be foreign, if those are the ones that can go away first.

However, the notion that either Unit of CosInterface might exist but neither are "dependent" on the other, yet they are being matched by a series of primary key columns that mirror on both sides is actually not a pattern I've ever seen before.   reasonable relational design would capture these node_id/interface/unit_num in some primary canonical set of rows and I would recommend moving in that direction if the schema is open to design.






All I'm looking to achieve is for these models to have an attribute I can call (e.g. Unit.cos_interface), that when present returns the corresponding model object. If there isn't one in the database then it can just return None. I suspect I'm using Relationship() incorrectly here, but I haven't been able to find the proper solution to this online. I guess essentially I would just like to have an attribute on the model that returns the result of a query, e.g.

class Unit(Base):
    __tablename__ = 'units_juniper'

    id = Column(Integer, autoincrement=True, nullable=False, unique=True)
    node_id = Column(Integer, primary_key=True, nullable=False, index=True)
    interface = Column(String(32), primary_key=True, nullable=False, index=True)
    unit_num = Column(Integer, primary_key=True, nullable=False, index=True)
    ....
    cos_interface = CosInterface.filter(CosInterface.node_id == node_id, CosInterface.interface == interface, CosInterface.unit_num == unit_num).first()

Is there a way to do this?

Cheers,
Jack




--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

Mike Bayer

unread,
Mar 14, 2021, 10:35:23 AM3/14/21
to noreply-spamdigest via sqlalchemy
I also intended to mention there's an option that might work here, though im not sure, which is to set passive_deletes='all' on both sides , which disables this "nulling out" operation, not sure if it will go all the way for the primary key columns here but it's worth a try:

Reply all
Reply to author
Forward
0 new messages