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.
