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.
