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 -- 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.