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:
https://docs.sqlalchemy.org/en/14/orm/relationship_api.html?highlight=passive_deletes#sqlalchemy.orm.relationship.params.passive_deletes On Sun, Mar 14, 2021, at 10:27 AM, Mike Bayer wrote: > > > 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 >> >> http://www.sqlalchemy.org/ >> >> 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+unsubscr...@googlegroups.com. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sqlalchemy/1581af13-9037-4ded-84c7-1585964e69f3n%40googlegroups.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/1581af13-9037-4ded-84c7-1585964e69f3n%40googlegroups.com?utm_medium=email&utm_source=footer>. > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/f0e450eb-1544-45b7-887f-f317bc35d827%40www.fastmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/f0e450eb-1544-45b7-887f-f317bc35d827%40www.fastmail.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2280e503-95fb-48fe-8ede-2d1e908ad054%40www.fastmail.com.
